In this blog we will see how to read data from tables in HANA IQ store into a virtual table using a remote source in HANA database instance.

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.
2. Creation of HANA Virtual Tables in HANA database Instance and connecting to IQ store using remote source. (This Blog Post)
3. 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.
• A database table should be present HANA IQ store with data. In this blog I am taking an example of table called KPI under schema DEMANDPLANNER. If you have not gone through my previous blog Introduction to HANA IQ store and how to load data into table in HANA IQ store from files in Azure Blob store, request you to go through it first.
• Hana Database instance should be created in BTP sub account and it should be up and running.

Remote Sources and Virtual Tables

Remote sources are connections to other databases, here these connections are made to SAP IQ store. Virtual tables represent tables in the remote data source. This makes it possible to access data in remote sources without having to replicate it to the SAP HANA database beforehand.

Virtual tables use a remote source to create a local table that points to data stored in IQ Store.

In this blog we will see how to create a Virtual Table KPI in HANA Database instance, this will access the data stored in KPI table in HANA IQ store by establishing connection through remote source PDPSOURCE1. Applications access the data in IQ store through virtual tables.This can be visualised as follows:

Create Remote Source

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

Goto your Hana Database 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.

Remote source can be created by executing following statement in SQL console.

where host is the host name of your IQ store instance (can be got by right clicking on IQ store instance in HANA Database explorer and then selecting ‘Properties’). User and Passwords are the login credentials of HANA IQ store.

Create Virtual Table

In order to create Virtual table follow the below mentioned steps.

• Goto HANA Database instance in your database explorer, expand ‘Catalogue’ node and click on Remote Sources.

• Click and open your remote source.
• Select the schema and object for which virtual table needs to be created.
• Click on ‘Create Virtual objects’.

Display Data in Virtual Table

in order to display data in virtual table follow the below mentioned steps.

• Goto HANA Database instance in your database explorer, expand ‘Catalogue’ node and click on ‘Tables’.
• Select the virtual table created in previous step.
• Click on ‘Open Data’ button as shown below.

Summary

In this blog post we saw how to read data from IQ store into a virtual table in HANA database instance without replicating the data in HANA Database instance. In the next blog post I will explain how to Create a CAP application and read data from HANA IQ store using virtual tables, So stay tuned for the same.

Hope you enjoyed reading this blog.

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