How to Load SAP HANA into a Cloud Data Warehouse Using the Database Query Component

Many businesses today are adopting a hybrid data warehouse model that spans both on-premises IT resources and the cloud. Matillion recognizes that many of our customers exist somewhere along the continuum of this hybrid model. 

That’s why with Matillion ETL Release 1.42, we extend our support for on-premises databases by supporting SAP HANA data via the Database Query Component. With this new feature, users can load their SAP HANA data into their chosen cloud data warehouse. From there, the data can be combined with transformed with other data sources for analytics. 

Getting Started with the SAP HANA in Matillion

In order to connect to SAP HANA with Matillion, you need to upload your own SAP HANA JDBC driver into Matillion. First, acquire this driver in the .jar file format either by a straight download or by decompressing it from a package.

Note: You can download ngdbc-2.4.67.jar here. 

To upload the driver, click on the Admin -> Manage Database Drivers menu in the top right-hand side.

Simply click ‘New’, select SAP HANA from the drop-down and then click ‘OK’. You can then upload the driver from your laptop or desktop via the browser. 

You may click ‘Test’ to confirm that the driver has uploaded successfully and is available to use in an Orchestration Job. In this job, you can load data from your SAP HANA database into your cloud data warehouse.  

Build your orchestration job

To build the Orchestration Job, first create a new Orchestration Job and drag the database Query Component onto the canvas. This component will use the JDBC driver uploaded in the previous step to load data from your SAP HANA database into your target database.

Under the component ‘Properties’, select ‘Database Type’ and then click SAP HANA. Set the rest of the properties to connect and pull data.

Authenticate SAP HANA

Next, provide a connection URL that identifies the server, port, and database name from your SAP HANA instance. Here’s a sample connection URL:

jdbc:sap://ServerIP:Port/?databaseName=<dbname>

Complete the Username and Password fields as appropriate. We recommend using Password Manager where possible.

Select your data

Using the SQL Query field, specify the data you want to load into your cloud data warehouse. You can create dynamic queries by using different variables. For example, you can use a WHERE, such as where last_updated >= ‘$(v_last_ts)’, to load new data from the last load based on the timestamp. Matillion will replace this variable value as needed at runtime and pull in the new data.  

Connection options provide you with additional parameters for extracting and loading data. Refer to SAP documentation for the available options.

Bring your SAP HANA data into your cloud data warehouse

There are two final required configurations. 

First, select your Target Table and a Staging Area. The Target Table is the table on your cloud data warehouse into which you want to load your data. Before you load the data, however, you will need to stage it.  Depending on your cloud data warehouse, this Staging Area may be an Amazon S3 bucket, Azure Blob Storage, GCP Cloud Storage, or Snowflake Internal Staging.  

When your Database Query Component has been configured to load your SAP HANA data, you can run the Orchestration Job or get a quick preview via the Sample tab next to properties. 

Extending your job

Depending on your use case, you may want to extend this job by adding additional components to enhance the workflow.

As an example, we can use the `Query to Scalar` component to initialize variables. The Database Query Components use these variables to build Parameterized SQL statements and to load data from SAP HANA and write to stage tables in Snowflake. The Transformation Job works with the staged data, transforming it and populating dimensions, facts, or other tables for Reporting and Analysis.

And that’s how you can bring your SAP HANA data into your cloud data warehouse to incorporate it into your analytics and insights. To learn more about the Database Query component and what other database types you can connect to with Matillion ETL, take a look at our Database Query component support article.