Using “Import” inside a Procedure using SAP HANA SDI

This blog will show you how it is possible to use the IMPORT statement inside a Procedure using SAP HANA SDI.

As you know it is currently not possible to use the IMPORT function inside a procedure using the standard HANA SQL script. This can be done by using SAP HANA Data Provisioning Agent Configuration.

Once you have the DP Agent downloaded, open it. You should see a screen like this:

Here you can see various opens such as Connecting to the HANA System, configuring SSL, etc.

To connect the DP Agent to HANA, click on “Connect to HANA” and enter the credentials it request below:

Once you have successfully enter the credentials you will see the available adapters appear here:

Now click on “Register Agent” to register the new agent and enter in the new Agent Name and the Agent’s hostname or IP Address:

To be able to register and use the file adapter you need to provide the source location for the files and control files as well as the access token.

Click Configure on the upper left corner and choose Preferences:

The switch to Adapters and expand the list and choose FileAdapter:

Here you enter in the Root Directory and also the File Format for the Root Directory (They are the same).

To get the Access Token open the CMD or DOS window:

Type whoiam /user, this will then give you the SID, this acts as the AccessToken:

Copy and Paste the SID as the Access Token.

Now you can register the adapter, in the adapter list choose FileAdapter and click Register Adapter:

The value in the file adapter where it says “Registered with HANA?” should now change to “Yes”.

To be able to read the files HANA needs file formats (similar to the CTL files used with HANA IMPORT utility). These file formats can be written manually or we can use the utility that comes with DP agent installation.

Currently the data folder doesn’t include any file formats, so we need to create these files.

If it’s closed, open another Command Prompt and change directory to
C:\usr\sap\dataprovagent\agentutils by using cd command.

cd C:\usr\sap\dataprovagent\agentutils *This could also be D:\, make sure to check.

Execute the following command to create the file formats (type the command instead of copy/paste):

createfileformat.bat -file C:\<AgentHostName> -cfgdir C:\<AgentHostName:

The utility will create .cfg files for every file that exists in the specified folder.

These are very similar to the CTL files used with IMPORT utility

Now we are ready to use provisioning data using the File Adapter

To use Provisioning Data use the File Adapter we need to use the SAP HANA Web IDE

About SAP Web IDE Editor

The SAP Web IDE Editor is a browser based development tool.  With powerful tools such as drag and drop components, templates and wizards, business analysts and designers can build their own models and applications without writing code.

The Editor allows technical users to create new or modify existing models of data.

Start the SAP HANA Web Development Workbench by first launching Google Chrome.

Using the URL below connect to Web IDE and enter the address below:

http:///sap/hana/ide/

Make sure your user has the correct privilege to launch the Web IDE:

1: sap.hana.uis.db::SITE_DESIGNER
2: sap.hana.im.dp.monitor.roles::Monitoring

Once SAP HANA Web Development Workbench starts, it will look like this image , with options for Editor, Catalog, Security and Traces:

Editor – Includes information models (Calculation Views, Flow Graphs and Replication Tasks) that we will use to access the HANA database

Catalog – Includes schemas, schema objects (i.e. tables, stored procedures, functions, etc.) as well as data.  Catalog also includes Provisioning for Smart Data Access Virtual tables from other data sources for query federation.
Security – Includes definition of SAP HANA users and roles. As a database user with privileges for user management, you can set up security and other users to work with the SAP HANA database.

Traces – Includes the various traces available within HANA and the ability to browse the trace log files.

Clicking on Catalog, you should now see the following folder structure.

Click on the arrow next to Catalog to expand the contents of Catalog.  You will now see a list of schemas within the HANA Instance

Close the Catalog folder and expand Provisioning, then Remote Source and right click and choose New Remote Source

Provide the necessary information for the new remote source (choose File Adapter as the Adapter Name):

When the screen opens it will have your root directory entered that you previously maintained along with the Access Token. (Make sure the credentials mode is “Technical User”)

Save the file and refresh Remote Sources folder by right clicking and choosing Refresh. The new remote source should appear in the list.

Expand the new file and verify all files appear in the list. Additional remote sources which are used by the HANA internal processes are created as well. We can ignore them for now.

Right click  and choose New Virtual Table. Then give a name, choose your schema and click OK.

Now go to the Catalog on the Web IDE and find the table you want to make a Virtual table from:

Now go into HANA and create the Procedure you want to use.

Here is an example of a procedure I used whilst using the Virtual table:

drop procedure <SCHEMA>.<PROCEDURE_NAME>

create procedure <SCHEMA>.<PROCEDURE_NAME>()
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER
as begin

drop TABLE “<SCHEMA>”.<TABLE> ;
CREATE column TABLE “SCHEMA”.<TABLE>
(
DATA varchar (8) ,
COD_NETWORK varchar (4) ,
ORA_INIZIO varchar (8) ,
ORA_FINE varchar (8) ,
TEST_ONE decimal(12, 6) ,
CODICE_TARGET INT ,
TEST bigint ,
TEST_TWO bigint ,
SHARE float
);

insert into “<SCHEMA>”.<TABLE>
SELECT
“DATA”,
“COD_NETWORK”,
“ORA_INIZIO”,
“ORA_FINE”,
“TEST_ONE”,
“CODICE_TARGET”,
“TEST”,
“TEST_TWO”,
“SHARE”
FROM “SCHEMA”.”<VIRTUAL_TABLE>”
);

)

This will now allow you to create an IMPORT inside of a procedure whilst using HANA SDI.