SAP HANA – SQL Data Import Export Statements

IMPORT FROM

BATCH:

  • The number of records to be inserted in each commit.

THREADS and BATCH can be used to achieve high loading performance by enabling parallel loading and also by committing many records at once. In general, for column tables, a good setting to use is 10 parallel loading threads, with a commit frequency of 10,000 records or greater.

TABLE LOCK:

  • Uses table locks for fast import of column store tables.

NO TYPE CHECK:

  • Specifies that the record will be inserted without checking the type of each field.

SKIP FIRST <number_of_rows_to_skip> ROW:

  • Skips the specified number of rows in the import file.

COLUMN LIST IN FIRST ROW:

  • Indicates that the column list is stored in the first row of the CSV import file.

COLUMN LIST ( <column_name_list> ):

  • The column list for the data being imported.
  • The name list has one or more column names.
  • The ordering of the column names has to match the order of the column data in the CSV file and the columns in the target table.

RECORD DELIMITED BY:

  • The record delimiter used in the CSV file being imported.

FIELD DELIMITED BY:

  • The field delimiter of the CSV file.

OPTIONALLY ENCLOSED BY:

  • The optional enclosure character used to delimit field data.

DATE FORMAT:

  • The format that date strings are encoded with in the import data:
    • Y : year
    • MM : month
    • MON : name of month
    • DD : day
  • Examples:
    • ‘YYYYMMDD’ = 20120520
    • ‘YYYY-MM-DD’ = 2012-05-20
    • ‘YYYY-MON-DD’ = 2012-MAY-20

TIME FORMAT:

  • The format that time strings are encoded with in the import data:
    • HH24 : hour
    • MI : minute
    • SS : second
  • Examples:
    • ‘HH24MISS’ : 143025
    • ‘HH24:MI:SS’ : 14:30:25

TIMESTAMP FORMAT:

  • The format that timestamp strings are encoded with in the import data.
  • Example:
    • ‘YYYY-MM-DD HH24:MI:SS’ : 2012-05-20 14:30:25

ERROR LOG:

  • When specified a log file of errors generated will be stored in this file.
  • Please ensure the file path we use is writeable by the database.

FAIL ON INVALID DATA:

  • When specified the IMPORT FROM command will fail unless all the entries have imported successfully

WITH SCHEMA FLEXIBILITY:

  • This option automatically creates missing columns based on the column names in the header row of CSV files or the column names specified in a column name list.

Examples:

  1. Import data from csv file to ORDERS_DATA table under SAP_STUDENT schema in SAP HANA.

Ans:

First, Create a column table in SAP HANA using below statement.

CREATE TABLE SAP_STUDENT.ORDERS_DATA (ORD_NUMBER INTEGER, CUST_NO NVARCHAR(10), ORD_DATE DATE, ORD_TIME TIME, QUANTITY DECIMAL);

Create a csv file with below content and store it in folder /data/data.csv

30011,”C_91″,”2012-05-20″,”14:30:25″,130
30012,”C_92″,”2012-05-21″,”15:30:25″,180
30013,”C_92″,”2012-05-22″,”16:30:25″,20
30014,”C_93″,”2012-05-23″,”17:30:25″,70

Second, Execute the below statement to load the content into ORDERS_DATA table in SAP_STUDENT schema.

IMPORT FROM CSV FILE ‘/data/data.csv’ INTO “SAP_STUDENT.”ORDERS_DATA”
WITH RECORD DELIMITED BY ‘\n’
FIELD DELIMITED BY ‘,’;

  1. Import the data from csv file to ORDERS_DATA table under SAP_STUDENT schema in SAP HANA using control file.

Ans:

First, Create a control file with below content and keep it folder /data/data.ctl

IMPORT DATA INTO TABLE “SAP_STUDENT”.”ORDERS_DATA” FROM ‘/data/data.csv’
RECORD DELIMITED BY ‘\n’
FIELD DELIMITED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
ERROR LOG ‘/data/data.err’

Next, Run the below statement to import data into table ORDERS_DATA

IMPORT FROM CONTROL FILE ‘/data/data.ctl’;

  1. Import the data from csv file to SAP HANA using DATE FORMATS and COLUMN LIST options.

Ans:

First, Create a csv file with below content and store it in folder /data/data.csv

30011,”C_91″,”05-20-2012″,”14:30:25″,130
30012,”C_92″,”05-21-2012″,”15:30:25″,180
30013,”C_92″,”05-22-2012″,”16:30:25″,20
30014,”C_93″,”05-23-2012″,”17:30:25″,70

Second, Execute the below statement to load the content into ORDERS_DATA table in SAP_STUDENT schema where specify date format as ‘MM_DD_YYYY’ because this is not SAP HANA default date format.

IMPORT FROM CSV FILE ‘/data/data_different_date.csv’ INTO “SAP_STUDENT.”ORDERS_DATA” WITH RECORD DELIMITED BY ‘\n’
FIELD DELIMITED BY ‘,’
DATE FORMAT ‘MM-DD-YYYY’;

Lastly, Run the below statement to execute the same statement by specifying column names as well.

IMPORT FROM CSV FILE ‘/data/data_col_list.csv’ INTO “SAP_STUDENT”.”ORDERS_DATA”
WITH RECORD DELIMITED BY ‘\n’
FIELD DELIMITED BY ‘,’
DATE FORMAT ‘MM-DD-YYY’
COLUMN LIST (“ORD_NUMBER”, “CUST_NO”, “ORD_DATE”, “ORD_TIME”,”ORD_QUANTITY”);

  1. Import the data from csv file to SAP HANA using SCHEMA FLEXIBILITY.

Ans:

First, Create a column table ORDERS_DATA with below columns in SAP HANA.

CREATE TABLE SAP_STUDENT.ORDERS_DATA (ORD_NUMBER INTEGER, CUST_NO NVARCHAR(10), ORD_DATE DATE, ORD_TIME TIME)

Now run the below statement to import data for 5 columns by specifying schema flexibility and column names.

IMPORT FROM CSV FILE ‘/data/data_col_list.csv’ INTO “SAP_STUDENT”.”ORDERS_DATA”
WITH RECORD DELIMITED BY ‘\n’
FIELD DELIMITED BY ‘,’
DATE FORMAT ‘MM-DD-YYY’
COLUMN LIST (“ORD_NUMBER”, “CUST_NO”, “ORD_DATE”, “ORD_TIME”,”ORD_QUANTITY”)
WITH SCHEMA FLEXIBILITY;

 

Check Also

Capture

SAP HANA – SQL Session Management Statements

Hello Everyone, In this article, we are going to look at different SQL Session Management …

Leave a Reply

Your email address will not be published. Required fields are marked *

18 − 17 =