Access data from AWS S3 in SAP HANA Cloud, SAP HANA Database

Use multi-source pre-configured landscape and learn how to import data from an Amazon S3 bucket to SAP HANA Cloud, SAP HANA database.

Import and Export data with S3 to SAP HANA Cloud, SAP HANA database

One can use the IMPORT FROM and EXPORT INTO commands to work with single database objects in CSV and parquet formats.

IMPORT/EXPORT of files does not require additional temporary space as these commands buffer I/O directly from/into AWS S3.

Amazon Simple Storage Service (Amazon S3) An object storage service offering industry-leading scalability, data availability, security, and performance. S3 is built to store and retrieve any amount of data from anywhere. Data is stored as objects within resources called buckets, and a single object can be up to 5 terabytes in size.
Parquet Files Apache Parquet is an open source, column-oriented data file format designed for efficient data storage and retrieval. It provides efficient data compression and encoding schemes with enhanced performance to handle complex data in bulk.
Comma-Separated Value (CSV) Files CSV files are plain-text files, making them easier to read. Since they’re plain text, they’re easier to import into a spreadsheet or another storage database, regardless of the specific software you’re using.

Try it out!

Use the IMPORT FROM and EXPORT INTO commands to work with single database objects in CSV and Parquet formats. The basic syntax of the commands is shown here (using CSV format), the examples below illustrate the usage further:

Code Snippet

1
IMPORT FROM CSV FILE '<S3_path.csv>' INTO <TARGET_TABLE_NAME> [with options];

Code Snippet

1
EXPORT INTO CSV FILE '<S3_path.csv>' FROM <SOURCE_TABLE_NAME> [with options];

Start by creating a table in the local schema. Click on the SQL icon in the top left corner of Database Explorer to open a new SQL Console.

Copy and paste the following SQL statements and execute them by clicking on the green Run icon or by pressing the F8 function key.

Code Snippet

1
CREATE COLUMN TABLE S3_GX_CUSTOMERS AS (select * from  HC_DEV.GX_CUSTOMERS);

Verify that table is now available in the local schema by right-clicking on the table and select Open Data, it should contain data.

Export the newly created S3_GX_CUSTOMERS table in parquet format to an S3 bucket. Copy and paste the following SQL statements and execute them by clicking on the green Run icon or by pressing the F8 function key.

Code Snippet

12
EXPORT S3_GX_CUSTOMERS AS PARQUET INTO
 's3-eu-west-1://s3-ta-hc/{placeholder|userid}_GX_CUSTOMERS' WITH CREDENTIAL 'AWS_S3';

Note

that the credential

The IMPORT SCAN statement searches a given path for objects exported with the EXPORT statement. It stores the results in the session-local temporary table #IMPORT_SCAN_RESULT in the current schema. Copy and paste the following SQL statements and execute them by clicking on the green Run icon or by pressing the F8 function key.

Code Snippet

12
import scan 's3-eu-west-1://s3-ta-hc/{placeholder|userid}_GX_CUSTOMERS' 
WITH CREDENTIAL 'AWS_S3';

Select the data in the #IMPORT_SCAN_RESULT table to access the result of the scan:

Code Snippet

1
select * from #IMPORT_SCAN_RESULT;

To import data from the S3 bucket, drop the S3_GX_CUSTOMERS table in local schema. The following SQL definition statement will either drop or rename source table to target table S3_GX_CUSTOMERS.

Note

Remove the comments “

Copy and paste the following SQL statements and execute them by clicking on the green Run icon or by pressing the F8 function key. Refresh the table catalog view to verify.

Code Snippet

123
-- drop table or rename table
-- drop table S3_GX_CUSTOMERS;
rename table S3_GX_CUSTOMERS to S3_GX_CUSTOMERS_OLD;

In the SQL Console and import the S3 based data into the newly cloned target table S3_GX_CUSTOMERS using the SQL below.

Code Snippet

12345
-- import s3 data
IMPORT S3_GX_CUSTOMERS AS PARQUET FROM 's3-eu-west-1://s3-ta-hc/{placeholder|userid}_GX_CUSTOMERS' WITH CREDENTIAL 'AWS_S3';

-- check row count
select count(*) from S3_GX_CUSTOMERS;

Well done!! This completes the lesson on the different options in SAP HANA Cloud, importing and exporting data from S3 cloud storage.

Next lesson