importing test data into SAP HANA

Today I learned how to import data from a CSV file into HANA:

1. look at your CSV file

In my case it contained 9 columns and each of them did not have more than 50 characters

2. create a table in HANA

So I created a table with 9 columns, each a varchar(50) in HANA:

hdbsql -i 00 -u system -p password -d SystemDB "create table world (c1 varchar(50),c2 varchar(50), c3 varchar(50), c4 varchar(50), c5 varchar(50), c6 varchar(50),c7 varchar(50), c8 varchar(50), c9 varchar(50))" 

You issue this command on Operating System level as sidadm, please replace 00 with your instance number and password with your password. -d SystemDB is needed if you have an MDC installation and wish to work in the System DB.

3. try to import your CVS file

hdbsql -i 00 -u system -p password -d SystemDB "import from csv file 'https://learntips.b-cdn.net/usr/sap/SID/home/testdata.csv' into world"

Remember to replace SID with your SID. When I tried this I got the response

* 2: general error: Cannot load table from the specified path - /usr/sap/SID/home. Please check configuration: csv_import_path_filter. SQLSTATE: HY000

4. change the HANA configuration

When I got the above error message I changed HANA’s configuration like this:

hdbsql -i 00 -u system -p password -d SystemDB "alter system alter configuration ( 'nameserver.ini','SYSTEM' ) set ( 'import_export','enable_csv_import_path_filter' ) = 'false' with reconfigure" 

Then it worked. However it had taken me some time to get there and I had done another change that may have lead to the success in the end. This other change was the same change in the indexserver:

hdbsql -i 00 -u system -p password -d SystemDB "alter system alter configuration ( 'indexserver.ini','SYSTEM' ) set ( 'import_export','enable_csv_import_path_filter' ) = 'false' with reconfigure" 

5. redo the import

In case it has failed before now it should work:

hdbsql -i 00 -u system -p password -d SystemDB "import from csv file 'https://learntips.b-cdn.net/usr/sap/SID/home/testdata.csv' into world"