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

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 namehanahdb.onprem.sap.server and 30241: These are the example server name and port. Replace them with the required HANA On-Prem server detailsUSERNAME and PaSsWoRd: Replace these with valid credentialsSCC-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 nameREMOTE_SERVER: Replace this with the name of the newly created remote serverSCHEMA_NAME: Replace it with the schema name of the table in the HANA On-Prem databaseTABLE_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 dataFor 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-hana-cloud-db/ba-p/13894067

 

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 DatabaseLogin 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 SourceProvide the necessary detailsSource 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 automaticallyProvide: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 modeProvide valid Username and Password

Step 6: Save the Remote Source

After entering all the details, click SaveAlternatively, 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 InstanceRight-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 tableClick Search to display the list of available remote objects (tables)

Step 11: Create Virtual Object

Select the desired table from the listClick on Create Virtual Object(s)

Step 12: Define Virtual Table Details

Provide a name for the virtual tableSelect the target schema in your HANA Cloud DatabaseClick 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 dataFor 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-hana-cloud-db/ba-p/13894067 

​ 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 EngineStep 1: Open SQL ConsoleFrom the Database Explorer of SAP HANA Datalake Relational Engine, open the SQL Console.Step 2: Create HANA On-Prem Remote ServerExecute 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 followingREMOTE_SERVER: This is an example name. Replace it with the actual source namehanahdb.onprem.sap.server and 30241: These are the example server name and port. Replace them with the required HANA On-Prem server detailsUSERNAME and PaSsWoRd: Replace these with valid credentialsSCC-LOC-01: Replace it with the valid Cloud Connector Location nameStep 3: Verify the Remote Server ConnectionRun 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 successfullyStep 4: Check the Remote Server DetailsTo 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 TableCreate a Existing (Virtual) TableTo 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 followingVT_TESTMYTABLE: This is an example virtual table name. Replace it with the required nameREMOTE_SERVER: Replace this with the name of the newly created remote serverSCHEMA_NAME: Replace it with the schema name of the table in the HANA On-Prem databaseTABLE_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 FilesystemExport Virtual Table DataOnce the virtual table is created in HANA Datalake Relational Engine, you can use SQL commands or tools to export its dataFor 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-hana-cloud-db/ba-p/13894067 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 CloudStep 1: Login to the HANA Cloud DatabaseOpen Database Explorer of your SAP HANA Cloud DatabaseLogin to your HANA Cloud Database Instance and expand the Catalog to locate Remote SourcesStep 2: Add a Remote SourceRight-click on Remote Sources and select Add Remote SourceProvide the necessary detailsSource 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 ConfigurationDefault driver libodbcHDB.so will be selected automaticallyProvide:Server: hanahdb.onprem.sap.server (example, replace with your required server).Port: 30241 (example, replace with the correct port number).Step 4: Extra Adapter PropertiesEnter the configuration: useHaasSocksProxy=true;sccLocationId=SCC-LOC-01;encrypt=yes;sslValidateCertificate=FalseNote: SCC-LOC-01 is an example Cloud Connector name. Replace it with the correct oneStep 5: Credentials SetupSelect Technical User as the credentials modeProvide valid Username and PasswordStep 6: Save the Remote SourceAfter entering all the details, click SaveAlternatively, 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 SourceRun 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 SourceExpand the Catalog of the HANA Cloud Database InstanceRight-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 TableStep 9: Open Remote SourceRight-click on the newly created Remote Source (REMOTE_SOURCE_NAME) and select OpenStep 10: Search for On-Prem Table (Remote Objects)Use the Schema and Object filters to search for the required On-Prem tableClick Search to display the list of available remote objects (tables)Step 11: Create Virtual ObjectSelect the desired table from the listClick on Create Virtual Object(s)Step 12: Define Virtual Table DetailsProvide a name for the virtual tableSelect the target schema in your HANA Cloud DatabaseClick Create to finish the processThe 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 FilesystemExport Virtual Table DataOnce the virtual table is created in HANA Datalake Relational Engine, you can use SQL commands or tools to export its dataFor 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-hana-cloud-db/ba-p/13894067   Read More Technology Blogs by Members articles 

#SAP

#SAPTechnologyblog

Avatar for

You May Also Like

More From Author