Will HANA supports Bulkload

[Former Member]

Hi,

Will HANA support Bulkload like oracle has bulkload and vertica has copy command

Regards,

Manoj.

[Former Member]

Hi all,

I want to ask if there is some other solution for bulk load (insert) except than import because if I can not access (exactly in my case) to the server what should I do ?

Cheers,

Aryan

[lbreddemann]

Aryan,

what exactly is the requirement you have?

You can e.g. use BO DataServices to bulk load data in to HANA.

Other than that, you can use JDBC to run batch inserts.

You can also switch off table redo logging during the insert, but this makes it necessary to take a data backup afterwards as otherwise you cannot recover the database.

As usual it depends on the use case...

regards, Lars    

[Former Member]

Thank you Lars for the immidiate answer,

actually I have millions of insert statements or better to say comma (or any delimiter) seperated values ready to be inserted.

At the moment I use the JDBC batch insert but it is still too slow for what I need, could you please give me a clue about this BO DataServices or switchng off table redo logging ? considering that I have no access to the server to transfer a file or something like that I can just use hana studio or run SQL queries.

Cheers,

Aryan

[lbreddemann]

Do yo need to import this once or on a regular basis?

If it is only once, you could even consider using the HANA studio import from client functionality.

For regular imports, Business Objects Data Services might suit your requirements.

For detailed information on DS I'm unfortunately the wrong person to ask - I only worked in projects where they used it, but never used it productively myself.

Cheers, Lars

[Former Member]

Thanks for the information. Basically I need to test my program frequently but what do you mean by import from client ? is there any oportunity to import data from client side ? and even from hana studio ? if you know something please tell me because I couldn't find.

And about business Objects data services I'll search a little bit but I still think there should be a simpler way because I've seen fore example mass data generation (https://wiki.wdf.sap.corp/wiki/display/inmematle/Mass+Data+Generation) which is fast enough.

Cheers,

Aryan

[tomas-krojzl]

Hello,

Oracle bulk load command:

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-load.html

...is very similar to IMPORT command..

In a shortcut:

1.- create comma separated CSV file with your data - <source cvs>.csv

2.- upload CVS file to HANA server

3.- create the control file <filename>.ctl:

import data
into table <schema>."<table name>" 
from '<source cvs>.csv'
record delimited by '
' 
fields delimited by ','
optionally enclosed by '"'
error log '<file name>.err'

4.- execute command:

IMPORT FROM '/<path>/<filename>.ctl'

Tomas

[Vitaliy-R]

Tomas, you can make

IMPORT FROM '/<path>/<filename>.ctl'

faster by adding WITH THREADS <thread_num> at the end, but in my understanding it is still INSERT-based command. The bulk loader utilities usually bypass SQL layer as a performance bottle-neck and insert data directly into the data pages or at least directly into table partitions, or using some similar performance-improvement techniques. And this is the secret sauce I am looking for in HANA db as well.

Regards,

-Vitaliy

[tomas-krojzl]

Hello,

.. you can make IMPORT FROM '/<path>/<filename>.ctl' faster by adding WITH THREADS <thread_num> at the end

Yes - I know - there are more options like BATCH - all described in SAP HANA SQL Reference Guide - page 119:

https://service.sap.com/~sapidb/011000358700000604922011

.. but in my understanding it is still INSERT-based command. The bulk loader utilities usually bypass SQL layer as a performance bottle-neck and insert data directly into the data pages or at least directly into table partitions, or using some similar performance-improvement techniques

Aha... you mean Direct Path Load functionality in Oracle:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci13obn.htm

I am not sure how exactly IMPORT FROM command is coded but following syntax seems to be interesting (also explained in SAP HANA SQL Reference Guide - page 119):

WITH TABLE LOCK
Locks table for fast import into column store tables. If WITHOUT TYPE CHECK option
is specified, then the record is inserted without checking the type of each field.

Probably not as fast as streamed load into data segment but probably best HANA can offer...

Would be interesting to ask SAP for details how exactly this is implemented.

Tomas

[Former Member]

Hi Tomas,

Thank you, I am very Curiosity is it SAP provided any other way like Pro* C interface to manipulate HANA database other then SQL commands like Oracle?

Manoj : really good Question.

thanks

Rao

[Vitaliy-R]

Good question - I am trying to get answer for the same for awhile, and getting different responses from SAP, depending who you talk to... Let's wait for answers here... -VR