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

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

Rating: 5 / 5 (1 votes)