SAP Hana and loading of data from CSV filesUse case: There is one machine with CSV file and another machine with SAP Hana database. You want to import data from the CSV file into the SAP Hana table. That's it.
There are at least 4 options how to import CSV files into SAP Hana.
- Import using SAP Hana Studio
- Import using control file and import statement
- Parse the file on your own and insert values using JDBC driver
- Import using Smart Data Integration
Lets compare all listed methods.
SAP Hana Studio
Import using SAP Hana Studio is manual approach of loading CSV file into SAP Hana. It is very easy and straight forward. The file is on your local machine, the studio will transfer the data into Hana table. The biggest disadvantages are the speed of the process and the disability of triggering the load automatically.
Import statement and control filesThis method seems to be the fastest and can be automatized but... your file has to reside on Hana server. That means that you have to transfer your data first. If you deal with big amount of data then your data file might have 20 GB or even 100+ times more. You can use FTP to transfer the data but you will waste disk resources on Hana server. Other option is to share file system and this option might not be the ideal for you.
So you think that all previous options are bad and you want to import your data using JDBC which is standard and easy to use. You can read your CSV file and push/insert the data using multiple threads. You will use batches, that means you will commit after few thousands of inserts. You can disable persistent merge on the target table and the import will be much faster. There is always one problem with this approach and it is wasting of database resources - connections, transactions. Wasting of such resources might cause some problems for your enterprise application.
Smart Data Integration
Smart Data Integration is probably very good solution for loading any data into SAP Hana. Your source data can be stored in different types of databases or even in CSV file but.. You will have to run SDI Agent on the machine which will read your source CSV data. Your account has to be connected with license that is eligible to download the client:) as it is mentioned here in this post https://scn.sap.com/thread/3707101