SAP HANA Cloud, Data Lake Files

It’s also possible to use SQL commands to import and export data directly from SAP HANA Cloud data lake file storage. This requires the path to the file store, along with the necessary credentials for users who need to access data lake.

Overview

A valid credential name and the path to the file store are required in the import and export statements. To access SAP HANA Cloud data lake file storage the use of the WITH CREDENTIAL clause is mandatory for execution of Import and Export statements. The credential uses the public certificate key of the CA used for access to HANA Cloud and this is uploaded to a PSE certificate store. This avoids the need to repeatedly enter the certificate as plain text for each Import/Export statement.

Note

The process of setting up the secure store and configuring access to the Data Lake is outside the scope of this workshop and has already been completed for you. Further details on this process can be found

The general syntax for exporting a table in SAP HANA Cloud is as follows:

Code Snippet

123
export <TABLE_NAME> as parquet
into 'hdlfs://<HDLFS endpoint>/hdb_export_directory'
WITH CREDENTIAL 'myCredential'

The security certificates and credentials have been pre-configured, and these will be accessed via the ‘myCredential’ tag.

Try it out!

The following exercise demonstrates how to import and export objects from SAP HANA Cloud to the HANA Data Lake file system.

Configure Data Lake Connection

The first step is to create the connection to the HANA Data Lake. A REST API endpoint, along with client certificate and key will be required for this.

In the Database Explorer, select the highlighted ‘+’ icon to add a new instance.

In the Add Instance dialogue box that pops up, click the drop-down and select Data Lake Files.

Paste the following string into the REST API Endpoint field:

{placeholder|api_endpt_dl}

Paste the following certificate details into the Client Certificate box:

Paste the following key certificate details into the Client Key box:

Change the Display Name to -HDLFS and click on OK.

The connection to the HANA Data Lake is now visible from within the Database Explorer:

Export table to Parquet Files

In the following examples, the GX_SALES table from the schema is used, but the same process can be applied to any object in the database.

In the Database Explorer window, open a new SQL console window.

Note

: If the SQL console icon is greyed out, just click on the HANA Cloud database hostname to enable it.

Now export the table into the SAP HANA Data Lake as a parquet file.

Copy the below SQL query, paste it into the console and click the green play icon to execute it.

Code Snippet

1234567
-- Create required Credential
CREATE CREDENTIAL FOR USER {placeholder|userid} COMPONENT 'SAPHANAIMPORTEXPORT' PURPOSE 'myCredential' TYPE 'X509' PSE 'MYPSE';

-- Export table as parquet file
export GX_SALES as parquet
into 'hdlfs://{placeholder|api_endpt_dl}/{placeholder|userid}_hdl_file_export'
WITH CREDENTIAL 'myCredential';

Verify that the table is now in the data lake. Expand the -HDLFS --> _hdl_file_export --> index --> --> GX and select the GX_SALES table. The parquet file, along with some others, should be visible in the lower pane.

In the same (or a new) SQL console window, paste the following query and run it to check the number of records currently in the local GX_SALES table.

Code Snippet

12
-- Get No. of records from GX_SALES table
select count(*) from GX_SALES;

Now delete all records from this table. Run the following query:

Code Snippet

12
-- Truncate all records in GX_SALES
truncate table GX_SALES;

Check the number of records now in the table. The result should be 0.

Code Snippet

12
-- Get No. of records from GX_SALES table
select count(*) from GX_SALES;

Now import the GX_SALES table from HANA Data Lake back into HANA Cloud. Run the following SQL:

Code Snippet

1234
-- Import the table from HANA Data Lake
import from parquet file 'hdlfs://{placeholder|api_endpt_dl}/{placeholder|userid}_hdl_file_export/index/{placeholder|userid}/GX/GX_SALES/parquetData'
into GX_SALES
WITH CREDENTIAL 'myCredential';

Run the count statement again to check the number of records in the table now. The records should have been re-populated from the data lake table.

Finally, let’s remove the data lake connection and clean up the files created there.

Right-click on the file export folder _hdl_file_export and select Delete.

Confirm deletion in the pop-up by clicking Delete.

Well done!! This lesson demonstrates how to export objects and their data to the HANA Data Lake in parquet file format, and how to import these objects into HANA Cloud.

Next unit