How to import data from .CSV into SAP HANA at a scheduled interval?

[Former Member]

Dear SAP HANA Experts:

I have a scenario  in which I want to load the data from one or more .csv files ( from the Windows box where HANA studio is installed)  into SAP HANA database at a regular interval. what are my options?

1. Create a windows batch file to invoke "import ..... .csv into XYZ table" at a scheduled interval. If it is possible, how do I connect to HANA database from command prompt and invoke the import command ( the way we import the data from the command prompt in Oracle/SQLPLUS

2. Is it possible to get this scheduled job done from within SAP HANA Studio?

Please direct me to a similar case study or code example.

Thanks in advance.

Jag

[richard_bremer]

Hi Jag,

the simple answer first: there is no scheduling functionality in SAP HANA Studio.

Meaning, you have to make use of something else that can schedule, e.g. cron in linux or an equivalent tool in windows. Then, the easiest way I know of is to create a small batch program that invokes a command named hdbsql (hdbsql.exe on windows), which comes together with the HANA client package.

However, the csv import options that work via SQL syntax require the csv export to be on the HANA database server (or on a network share locally mounted in the HANA DB Server, with the <sid>adm user of the HANA DB instance having access to that share. If you cannot facilitate this, I can only suggest the solution of looping in a batch script (other other program (JAVA, C#, ... -> several options are described in the "SAP HANA Database - SQL Script Guide" in the public documentation, direct link to avoid confusion with a similarly named guide: http://help.sap.com/hana/hana_dev_sqlscript_en.pdf)) over the lines of the csv export and inserting into the table directly with insert into <table> (<list of fields>) values (<list of values>). The reason of course is that SQL statements run within the DB server and the DB server cannot access files on your client PC.

If you want to go with hdbsql (running on the HANA DB Server), this is what you'll need to start:

The syntax of hdbsql is explained in the SAP HANA Database Administration Guide on https://help.sap.com/hana_appliance, in Appendix A. You'll typically invoke it with options

• -n <hostname>
• -i <instance_number>
• -c <separator> to explicitly define the statement separator (I'd always do this, even if you want to use the semicolon ';' as separator)
• And then either options -u <user> and -p <password>
• Or better: -U <userstore_key>
• and -I <file_with_sql_statements_to_run> (the l in -l is capital i, not lower case L)

The <userstore_key> is a way to securely authenticate against the DB when running for example hdbsql in batch mode. You have to once enter DBinformation, user name and password together with a unique key into the userstore. That userstore is tied to an OS account. Afterwards, anyone logged on with that OS account can authenticate against HANA by using the userstore key, without having to explicitly give the DB password. hdbuserstore is described in the SAP HANA Database Security Guide, always available with the public documentation.

The import itself uses the syntax as described in the SQL reference guide, also part of the public documentation. There are two options. These two options are different with respect

• the "import <table> as csv from <path>" option requires the csv export to exist with the same file system and file layout as it would if you ran the "inverse" statement "export <table> as binary into <path>" (which is also UI supported via SAP HANA Studio -> menu "File" -> export -> SAP HANA Studio -> Tables).
• the "import from <file_type> <fully_qualified_file_name>" option can use either a csv file (and then you specify some details of the csv file in further statement options) or a control file (which contains the path to the actual csv file and information on csv file layout).

Best regards,

Richard

--

Dr. Richard Bremer

Customer Solution Adoption (CSA), SAP AG

[Former Member]

Thanks I was able to load .csv from hdbsql

[Former Member]

Hello Jag,

Can you please elaborate how to achieve this?

Our scenario is: We need to load CSV files daily to HANA Studio and hence we need to schedule this. Our HANA box is on a Linux system.

Appreciate your help.

Thanks & regards,

Jomy

[vanama1]

Hi Guys,

I am trying to schedule the upload of a .csv file from windows batch file .bat

I am able to connect to HANA using HDBSECUSER store login and can execute every single SQL command through batch file, but not IMPORT or IMPORT FROM command.

Here is what I am doing.

1) I created a Batch file xxx.bat with the following

"C:\Program Files\sap\hdbclient\hdbsql" -U LSSxxx -c ";"  -I "C:\Users\LSSRBD\Desktop\xxx\MBEW Upload\xxx.sql"

2) xxx.sql file contains:

DELETE FROM "XXX_CUSTOM"."XXX_UPLOAD";

IMPORT FROM CONTROL FILE 'C:\Users\LSSxxx\Desktop\xxx\XXX Upload\xxx.ctl';

3) xxx.ctl file contains:

IMPORT DATA INTO TABLE "XXX_CUSTOM"."XXX_UPLOAD" FROM 'C:\Users\LSSXXX\Desktop\xxx\xxx Upload\xxx201502.csv'

RECORD DELIMITED BY '\n'

FIELD DELIMITED BY ','

OPTIONALLY ENCLOSED BY '"'

when I try executing xxx.bat file from the command line, I am getting the following message.

general error: Cannot open Control file

So I gave the command: icacls "xxx.ctl" /grant Everyone:F and it worked. So I am assuming this gave the permission to every one on the pc.

I am still getting the error: general error: Cannot open Control file.

What exactly is wrong that I am doing here? HDBSQL works with every SQL command on HANA DB content, but IMPORT command is failing. How do I solve this issue?

[former_member185671]

Hi Richard,

Could you please help via email on this process?

Best,

Christoforos