Read \ Write Data between HANA Datalake and HANA On-Prem DB

[KarthikKumar31]

A simple guide to Read \ Write table data between SAP HANA Datalake and SAP HANA On-Premises DB. 

Key topics include:

• Export from HANA On-Prem DB and Import to HANA Datalake Filesystem in CSV and PARQUET Formats using HANA Cloud.
• Export from HANA On-Prem DB and import to the HANA Datalake Filesystem in CSV and PARQUET formats using HANA Datalake Relational Engine.

Export from HANA On-Prem DB and import to the HANA Datalake Filesystem in CSV and PARQUET formats using HANA Datalake Relational Engine

1. Creation of HANA On-Prem Remote Server in HANA Datalake Relational Engine

Step 1: Open SQL Console

From the Database Explorer of SAP HANA Datalake Relational Engine, open the SQL Console.

Step 2: Create HANA On-Prem Remote Server

Execute the following SQL query to create the remote server for the HANA On-Prem system

CREATE SERVER REMOTE_SERVER CLASS 'HANAODBC' USING
'Driver=libodbcHDB.so;
ConnectTimeout=0;
CommunicationTimeout=15000;
RECONNECT=0;
ServerNode= hanahdb.onprem.sap.server:30241;
ENCRYPT=TRUE;
sslValidateCertificate=False;
UID=USERNAME;
PWD=PaSsWoRd;
UseCloudConnector=ON;
LocationID=SCC-LOC-01';

Please note the following

• REMOTE_SERVER: This is an example name. Replace it with the actual source name
• hanahdb.onprem.sap.server and 30241: These are the example server name and port. Replace them with the required HANA On-Prem server details
• USERNAME and PaSsWoRd: Replace these with valid credentials
• SCC-LOC-01: Replace it with the valid Cloud Connector Location name

Step 3: Verify the Remote Server Connection

Run the following SQL query to check if the newly created remote source is functioning correctly

CALL sp_remote_tables('REMOTE_SERVER');

If the output lists all the tables of the HANA On-Prem database, the remote server has been created successfully

Step 4: Check the Remote Server Details

To view the details of the newly created remote server, execute the following query:

SELECT * FROM SYSSERVER;

2. Create a Virtual Table in HANA Datalake Relational Engine for HANA On-Prem Table

Create a Existing (Virtual) Table

To create a existing table (virtual table) that points to a table in the HANA On-Prem database, execute the following SQL query

CREATE EXISTING TABLE VT_TESTMYTABLE AT 'REMOTE_SERVER..SCHEMA_NAME.TABLE_NAME';

Please note the following

• VT_TESTMYTABLE: This is an example virtual table name. Replace it with the required name
• REMOTE_SERVER: Replace this with the name of the newly created remote server
• SCHEMA_NAME: Replace it with the schema name of the table in the HANA On-Prem database
• TABLE_NAME: Replace this with the actual table name in the HANA On-Prem database

3. Export / Import Operations from HANA Datalake Relational Engine to HANA Datalake Filesystem

Export Virtual Table Data

• Once the virtual table is created in HANA Datalake Relational Engine, you can use SQL commands or tools to export its data
• For a detailed guide on performing the export and import operations in CSV or PARQUET formats, refer to the blog https://community.sap.com/t5/technology-blogs-by-members/read-write-data-between-hana-datalake-and-h...

Export from HANA On-Prem and Import to HANA Datalake Filesystem in CSV and PARQUET Formats using HANA Cloud

1. Creation of HANA On-Prem Remote Source in HANA Cloud

Step 1: Login to the HANA Cloud Database

• Open Database Explorer of your SAP HANA Cloud Database
• Login to your HANA Cloud Database Instance and expand the Catalog to locate Remote Sources

Step 2: Add a Remote Source

• Right-click on Remote Sources and select Add Remote Source
• Provide the necessary details
  • Source Name: REMOTE_SOURCE_NAME (This is an example, replace it with the appropriate name).
  • Adapter Name: HANA (ODBC).
  • Source Location: indexserver.

Step 3: Adapter Properties Configuration

• Default driver libodbcHDB.so will be selected automatically
• Provide:
  • Server: hanahdb.onprem.sap.server (example, replace with your required server).
  • Port: 30241 (example, replace with the correct port number).

Step 4: Extra Adapter Properties

• Enter the configuration: useHaasSocksProxy=true;sccLocationId=SCC-LOC-01;encrypt=yes;sslValidateCertificate=False

Note: SCC-LOC-01 is an example Cloud Connector name. Replace it with the correct one

Step 5: Credentials Setup

• Select Technical User as the credentials mode
• Provide valid Username and Password

Step 6: Save the Remote Source

• After entering all the details, click Save
• Alternatively, you can use the SQL query below to create the remote source:

CREATE REMOTE SOURCE REMOTE_SOURCE_NAME
ADAPTER "hanaodbc"
CONFIGURATION 'ServerNode=hanahdb.onprem.sap.server:30241;useHaasSocksProxy=true;sccLocationId=SCC-LOC-01;encrypt=yes;sslValidateCertificate=False;'
WITH CREDENTIAL TYPE 'PASSWORD'
USING 'user=Username;password=Password';

Step 7: Verify the Remote Source

• Run the following SQL command to check if the newly created remote source is working

CALL PUBLIC.CHECK_REMOTE_SOURCE('REMOTE_SOURCE_NAME');

• If the command executes successfully without errors, the remote source is functional.

Step 8: View the Remote Source

• Expand the Catalog of the HANA Cloud Database Instance
• Right-click on Remote Sources and select Show Remote Sources to confirm your connection

2. Create a Virtual Table in HANA Cloud for HANA On-Prem Table

Step 9: Open Remote Source

• Right-click on the newly created Remote Source (REMOTE_SOURCE_NAME) and select Open

Step 10: Search for On-Prem Table (Remote Objects)

• Use the Schema and Object filters to search for the required On-Prem table
• Click Search to display the list of available remote objects (tables)

Step 11: Create Virtual Object

• Select the desired table from the list
• Click on Create Virtual Object(s)

Step 12: Define Virtual Table Details

• Provide a name for the virtual table
• Select the target schema in your HANA Cloud Database
• Click Create to finish the process

The newly created virtual table in HANA Cloud can now be used for operations, including exporting data to the HANA Datalake Filesystem.

3. Export / Import Operations from HANA Cloud to HANA Datalake Filesystem

Export Virtual Table Data

• Once the virtual table is created in HANA Datalake Relational Engine, you can use SQL commands or tools to export its data
• For a detailed guide on performing the export and import operations in CSV or PARQUET formats, refer to the blog https://community.sap.com/t5/technology-blogs-by-members/read-write-data-between-hana-datalake-and-h...