INSERT INTO EXTERNAL CSV Examples
1. Write the contents of the sales table to a file named sales_fact.csv in HDFS location. Indicate null values with the text NULL, separate fields with a comma, and separate records with \n.
Linux:
INSERT INTO EXTERNAL CSV 'Actian/tmp/sales_fact.csv' SELECT * FROM sales WITH NULL_MARKER='NULL', FIELD_SEPARATOR=',', RECORD_SEPARATOR='\n' FLOAT_FORMAT=’e38.6’
2. Export data in the MY_TABLE table to a file named "mytable" using relative path, and insert the text THIS IS NULL for null values:
Linux:
INSERT INTO EXTERNAL CSV '/tmp/mytable.csv' SELECT * FROM my_table WITH NULL_MARKER='"THIS IS NULL"' FLOAT_FORMAT=’f79.38’
Windows:
INSERT INTO EXTERNAL CSV '\temp\mytable.csv' SELECT * FROM my_table WITH NULL_MARKER='"THIS IS NULL"'
3. Export data in the supplier table to a file named supplier.csv and insert the text NULL for null values. The csv file will be written to the directory specified on WORK_DIR parameter:
Linux:
INSERT INTO EXTERNAL CSV 'supplier.csv' SELECT * FROM supplier WITH NULL_MARKER='NULL', WORK_DIR='/home/users/temp'
Windows:
INSERT INTO EXTERNAL CSV 'supplier.csv' SELECT * FROM supplier WITH NULL_MARKER='NULL', WORK_DIR='C:\temp'
Last modified date: 03/21/2024