Load data into tables in HANA IQ store from files in Azure Blob store

[swati_karoor]

Hello Community!!

In this series of blog posts I will share with you all how to connect to SAP HANA IQ store from a CAP (Cloud Application Programming Model) JAVA application.

This will be series of blogs containing following topics:

1. Introduction to HANA IQ store and how to load data into table in HANA IQ store from files in Azure Blob store. (This Blog Post)

1. Creation of HANA Virtual Tables in HANA database Instance and connecting to IQ store using remote so...

1. Create a CAP application and read data from HANA IQ store using virtual tables.

Prerequisites

• Global Account and Sub account should have been created in SAP Business Technology Platform (BTP)

• Hana Data Lake (HDL) instance with relational engine should be created in BTP sub account and it should be up and running.

         

• Have a Azure Blob store account in place and keep your data ready in a Parquet file that needs to be loaded in IQ store. More details about this can be found here.

Introduction to HANA IQ store

HANA IQ store is a columnar store based relational database on top of HANA data lake file system. It is used for data warehousing and analytics.

Create Tables in HANA IQ store

Table can be created in IQ store using CREATE TABLE sql command. In this example I will be creating a table called KPI under schema DEMANDPLANNER.

Login to your BTP account. Goto your sub account ->Dev spaces -> SAP HANA cloud

Goto your Hana Datalake instance and click on Actions-> Open in SAP HANA Database Explorer. Enter the login credentials for your HDB user. This will open HANA database explorer.

In HANA database explorer right click on your HANA IQ instance and select 'Open SQL Console'.

In SQL console add the following CREATE TABLE statement and run it to create the table KPI.

 

Load Data into IQ from Azure Blob store

In order to load data into the table from Azure Blob store following SQL statement needs to be executed in SQL console.

Load Table <table name>(
<field1>,<field2>
)
from '<Azureblobstore URL>'
CONNECTION_STRING 'DefaultEndpointsProtocol=https;AccountName=<storageaccountname>;AccountKey=<StorageAccountKey>;EndpointSuffix=core.windows.net'
escapes off
format parquet
;
commit;

 

Please note that the number and order of columns in your parquet file in blob store should be same as that of table in IQ store.

Conclusion

In this blog I have tried to explain how to create tables in HANA IQ store and load data into it from Azure bob Store. In the next blog we will see  Creation of HANA Virtual Tables in HANA database Instance and connecting to IQ store using remote so.... Hope you found this blog useful, see you in the next one.

If you have further questions, feel free to leave me a comment here.

 

Best Regards,

Swati.