Use SAP HANA Cloud, data lake Events to Schedule Data Movement
- How to create and schedule events in a SAP HANA Cloud, data lake instance
- How scheduling data movement from SAP HANA Cloud, HANA database (HDB) to SAP HANA Cloud, data lake (HDLRE) can be achieved through SQL
Prerequisites
- A SAP HANA Cloud, HANA database instance
- A SAP HANA Cloud, data lake instance
- Step 1
Download and import the
customer.tbl
TPCH data file from this GitHub repository.Once the data file is downloaded, create a table to import the data to. Navigate to the SAP HANA Database Explorer from the SAP BTP Cockpit and open a SQL console. Inside the SQL console, create the table using the following SQL. The SQL script creates a new schema called TPCH and then creates a table under that schema called “CUSTOMER”.
SQLCopyCREATE SCHEMA TPCH; CREATE TABLE TPCH.CUSTOMER ( C_CUSTKEY integer not null, C_NAME varchar(25) not null, C_ADDRESS varchar(40) not null, C_NATIONKEY integer not null, C_PHONE varchar(15) not null, C_ACCTBAL decimal(15,2) not null, C_MKTSEGMENT varchar(10) not null, C_COMMENT varchar(117) not null, primary key (C_CUSTKEY) );
Next, right click the HANA database instance in the left side menu and select “Import Data”.
On the “Import Type” page select “Import Data” and proceed to the “Import Source” step. On the “Import Source” step select “Local” and then uncheck the “File has header in first row” box. Then select the
customer.tbl
file that was downloaded earlier. You may need to rename this file tocustomer.csv
.On the “Import Target” step, select “Add to an existing table” and fill in the information for the
TPCH.CUSTOMER
table that was created.Verify the table mapping and go to step 5 to finish the import.
- Step 2
Throughout this tutorial you will need a user that has certain permissions. Log into an HDLRE SQL console as HDLADMIN and execute the following SQL to create a user and grant them the permissions needed to complete this tutorial. Comments in the SQL below will explain the permissions being granted.
SQLCopy-- First you need a user to give the permissions to -- Provide a customer USER name in place of <HDL_USER> and password in place of <PASSWORD> CREATE USER <HDL_USER> IDENTIFIED BY <PASSWORD> FORCE PASSWORD CHANGE OFF; -- to create a `REMOTE SERVER`, the HDLRE user needs the `MANAGE ANY REMOTE SERVER` privilege. GRANT MANAGE ANY REMOTE SERVER TO <HDL User>; -- to create an `EXTERNLOGIN` the HDLRE user needs the `MANAGE ANY USER` privilege. GRANT MANAGE ANY USER TO <HDL User>; -- to create an `EVENT` the HDLRE user needs the `MANAGE ANY EVENT` privilege. GRANT MANAGE ANY EVENT TO <HDL User>;
Once the user is created, create a database connection to HDLRE with that user. Right click the HDLRE database in Database Explorer and click properties.
Copy the host value in the properties modal.
Close the modal and click the add connection icon in SAP Database Explorer.
Now, choose Data Lake Relational Engine and fill in the values with the host and user credentials from above.
Click OK and then there should be a new database in the left side menu in the Database Explorer. Right click that instance and open a new SQL console. The rest of the tutorial should be completed from there.
- Step 3
The first task in preparing the HDLRE instance is creating a remote server that connects HDLRE to the HDB instance that contains the data you want to access. First, right click the HANA database in Database Explorer and select properties.
In the properties modal, copy the “Host” value and use it in the below SQL to create a remote server to the HANA database.
Since you’ve already imported your data into your HANA database, you can proceed to preparing HDLRE for scheduling data movement. Use a SQL console that is connected directly to the HDLRE instance.
Run this SQL against the HDLRE instance using a user with the
MANAGE ANY REMOTE SERVER
privilege to create a remote server. Notice, you are naming the remote serverHDB_SERVER
. Replace the<HANA Host Name>
with the host copied from the properties modal.SQLCopyCREATE SERVER HDB_SERVER CLASS 'HANAODBC' USING 'Driver=libodbcHDB.so; ConnectTimeout=0; CommunicationTimeout=15000000; RECONNECT=0; ServerNode=<HANA Host Name>:443; ENCRYPT=TRUE; ssltruststore=<HANA Host Name>; ssltrustcert=Yes;';
Now that the remote server is created, you must create the
EXTERNLOGIN
that will map your HDLRE user to the HANA user credentials and allow access to the HANA database. Notice below in theCREATE EXTERNLOGIN
statement you are granting your HDLRE user permission to use the HANA user for theHDB_SERVER
that was created above. Ensure that the HANA user used in the SQL below has access to the objects that need to be referenced. It would be a good idea to use the HANA user that was used in Step 1 to create the CUSTOMER table.Replace
<HDL USER NAME>
with the current HDLRE user that is being used and replace<HANA USER NAME>
and<HANA PASSWORD>
with the HANA user credentials.SQLCopyCREATE EXTERNLOGIN <HDL USER NAME> to HDB_SERVER REMOTE LOGIN <HANA USER NAME> IDENTIFIED BY <HANA PASSWORD>;
Do a quick test to ensure everything has been set up successfully. You will create a temporary table that points to your customer table in HANA. Then run a select against that table to ensure you are getting data back.
SQLCopyCREATE EXISTING LOCAL TEMPORARY TABLE HDLRE_CUSTOMER ( C_CUSTKEY integer not null, C_NAME varchar(25) not null, C_ADDRESS varchar(40) not null, C_NATIONKEY integer not null, C_PHONE varchar(15) not null, C_ACCTBAL decimal(15,2) not null, C_MKTSEGMENT varchar(10) not null, C_COMMENT varchar(117) not null, primary key (C_CUSTKEY) ) AT 'HDB_SERVER..TPCH.CUSTOMER'; SELECT * FROM HDLRE_CUSTOMER; DROP TABLE HDLRE_CUSTOMER;
Run the above SQL and expect a result set to be returned.
- Step 4
HDLRE has a built-in event and event scheduler. You can take advantage of the remote service and the event scheduler to copy data from your HANA database to your HDLRE. Start by creating a destination table for your data. In this case this is just a customer table that is inside HDLRE.
SQLCopyCREATE TABLE HDLRE_CUSTOMER ( C_CUSTKEY integer not null, C_NAME varchar(25) not null, C_ADDRESS varchar(40) not null, C_NATIONKEY integer not null, C_PHONE varchar(15) not null, C_ACCTBAL decimal(15,2) not null, C_MKTSEGMENT varchar(10) not null, C_COMMENT varchar(117) not null, primary key (C_CUSTKEY) );
Here I will break down creating an event in HDLRE. In the following SQL you create an event called
PullCustomerDataFromHANA
. Immediately after you create a scheduleSchedulePullCustomerDataFromHANA
. The schedule is scheduled to start at 12:00am and repeat the event every Sunday. Below the “HANDLER” you define the SQL script to be executed. The script creates a local temporary table (this table will be lost once the connection is dropped) and then inserts the data from that the temporary table into yourHDLRE_CUSTOMER
table which persists inside of your HDLRE instance. So, every Sunday the event is copying the data from your HANA table to your HDLRE table.SQLCopyCREATE EVENT PullCustomerDataFromHANA SCHEDULE SchedulePullCustomerDataFromHANA START TIME '12:00am' ON ('Sun') HANDLER BEGIN CREATE EXISTING LOCAL TEMPORARY TABLE HDLRE_CUSTOMER_TT ( C_CUSTKEY integer not null, C_NAME varchar(25) not null, C_ADDRESS varchar(40) not null, C_NATIONKEY integer not null, C_PHONE varchar(15) not null, C_ACCTBAL decimal(15,2) not null, C_MKTSEGMENT varchar(10) not null, C_COMMENT varchar(117) not null, primary key (C_CUSTKEY) ) AT 'HDB_SERVER..TPCH.CUSTOMER'; INSERT INTO HDLRE_CUSTOMER SELECT * FROM HDLRE_CUSTOMER_TT; END;
You can trigger the event manually to test if it is working. Trigger the event and then select from the HDLRE table to ensure that the data has been copied.
SQLCopyTRIGGER EVENT PullCustomerDataFromHANA; SELECT * FROM HDLRE_CUSTOMER;
Now you have successfully created an event that recurs and moves data from a HANA database to a data lake database. Events can contain any valid SQL operations, so get creative and automate any repetitive SQL workloads with SAP HANA Cloud, data lake events!
What privilege are required for a user to create an event?