Import CSV into SAP HANA, express edition using IMPORT FROM SQL command

For security reasons, only CSV files located at paths defined in the csv_import_path_filter configuration parameter can be loaded by using the IMPORT FROM SQL statement.

This feature is controlled by the enable_csv_import_path_filter configuration parameter.

Connect to the HXE tenant using the ML_USER user credentials and execute the following SQL statement:

SELECT
  *
FROM
  M_INIFILE_CONTENTS
WHERE
    SECTION = 'import_export'
AND KEY     = 'enable_csv_import_path_filter'

By default, the value is true, which means that the IMPORT FROM SQL command will only work for CSV files located at the path configured by csv_import_path_filter which is not set by default.

You can disable this restriction using the following SQL statement when connected as the SYSTEM user:

ALTER SYSTEM
  ALTER CONFIGURATION ('indexserver.ini', 'database')
  SET ('import_export', 'enable_csv_import_path_filter') = 'false'
  WITH RECONFIGURE;

Execute the following SQL statement:

SELECT
  *
FROM
  M_INIFILE_CONTENTS
WHERE
    SECTION = 'import_export'
AND KEY     = 'csv_import_path_filter';

By default, the configuration returns an empty value which implies that the following locations are used by default:

• $DIR_INSTANCE/work
• $DIR_INSTANCE/backup
• $SAP_RETRIEVAL_PATH/trace

The $DIR_INSTANCE value for most SAP HANA, express edition installation is /usr/sap/HXE/HDB90.

This is detailed in SAP Note 2109565.

You can enable additional path using the following SQL statement when connected as the SYSTEM user:

ALTER SYSTEM
  ALTER CONFIGURATION ('indexserver.ini', 'database')
  SET ('import_export', 'csv_import_path_filter') = '/mypath1;/mypath2'
  WITH RECONFIGURE;

Connect to your SAP HANA, express edition system using a SSH client or a FTP client connected as the hxeadm user.

Save the following data set in /usr/sap/HXE/HDB90/work/data_1.csv on the remote host:

A,B,C,D,E
1,"DATA1","2012/05/20","14:30:25","123456789"
2,"DATA2","2012/05/21","15:30:25","234567890"
3,"DATA3","2012/05/22","16:30:25","345678901"
4,"DATA4","2012/05/23","17:30:25","456789012"

Save the following data set in /usr/sap/HXE/HDB90/work/data_2.csv on the remote host:

5;"DATA5";"01-20-2015";"223025";"123456.789"
6;"DATA6";"01-21-2015";"153025";"234567.890"
7;"DATA7";"01-22-2015";"163025";"345678.901"
8;"DATA8";"01-23-2015";"173025";"456789.012"

As you can notice the date format are different and one uses a European format when the other uses a US format.

For the time format, only the HH24 hour formation is supported.

For the number format, the supported decimal separator is a dot and there is no thousand separator supported nor scientific notation.

Connect to the HXE tenant using the ML_USER user credentials and execute the following SQL statement:

CREATE TABLE ML_DATA.MYTABLE_IMPORTSQL (
    A INT,
    B VARCHAR(10),
    C DATE,
    D TIME,
    E DECIMAL
);

The IMPORT FROM command support a series of options that can be useful when addressing specific file or data format.

It also supports the use of Control Files, where you can store the import configuration, instead of passing the details directly in the command.

Check the following documentation for more details: SAP HANA SQL and System Views Reference > IMPORT FROM Statement (Data Import Export).

Connect to the HXE tenant using the ML_USER user credentials using your SQL query tool.

Import the data 1 CSV

Execute the following SQL statement:

IMPORT FROM CSV FILE '/usr/sap/HXE/HDB90/work/data_1.csv' INTO ML_DATA.MYTABLE_IMPORTSQL
WITH
   RECORD DELIMITED BY '\n'
   FIELD DELIMITED BY ','
   OPTIONALLY ENCLOSED BY '"'
   SKIP FIRST 1 ROW
   FAIL ON INVALID DATA
   ERROR LOG '/usr/sap/HXE/HDB90/work/data_1.csv.err'
;

Import the data 2 CSV

Execute the following SQL statement:

IMPORT FROM CSV FILE '/usr/sap/HXE/HDB90/work/data_2.csv' INTO ML_DATA.MYTABLE_IMPORTSQL
WITH
   RECORD DELIMITED BY '\n'
   FIELD DELIMITED BY ';'
   OPTIONALLY ENCLOSED BY '"'
   DATE FORMAT 'MM-DD-YYYY'
   TIME FORMAT 'HHMISS'
   FAIL ON INVALID DATA
   ERROR LOG '/usr/sap/HXE/HDB90/work/data_2.csv.err'
;

Note: insufficient privilege: Not authorized

If you receive this error, it probably mean that the user is not granted the IMPORT role.
Connect to the HXE tenant using the SYSTEM user credentials and execute the following SQL statement:

GRANT IMPORT TO ML_USER;

To verify that the data were properly imported, you can run the following query:

SELECT * FROM ML_DATA.MYTABLE_IMPORTSQL;

Provide an answer to the question below then click on Validate.