INSERT INTO EXTERNAL CSV
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The INSERT INTO EXTERNAL CSV statement writes a table to a local file system. The result is either a single CSV file or a collection of CSV files, depending on whether the query is run in parallel. The number of files produced cannot be specified, but can be indirectly influenced by setting [engine] max_parallelism_level.
This statement has the following format:
INSERT INTO EXTERNAL CSV 'filename' SELECT ... [WITH options]
filename
Specifies the output location, which can be either a local file system path or a HDFS location URL (only on HDFS installations). If multiple files are created, they use the filename with suffixes in the form '.nnn' where nnn is the file number.
The default output path is the value of II_TEMPORARY (if the path is not specified).
The server must have permissions to write to the specified path.
For security reasons, the server is not allowed to overwrite existing files. The query will end with an error if any of the output files already exist.
Note: For VectorH, the files are written by the server processes on all the cluster nodes. Therefore, the file path must be accessible from the perspective of every cluster node. If it is a local path, it must be a path that exists on all the nodes, for example, /tmp.
SELECT
Specifies a SELECT statement that selects the table data to be written.
WITH options
Specifies optional WITH clause options separated by a comma. Valid options are:
NULL_MARKER='null_value'
Specifies the text to use for the NULL valued attributes. When a parameter contains commas or spaces, double quotation marks must be used. Default: 'null'
FIELD_SEPARATOR='field_separator'
Specifies the character to use to separate fields. The delimiter must be a single character. Default: , (comma)
RECORD_SEPARATOR='record_separator'
Specifies the character to use to separate records. The delimiter must be a single character. To specify a control character, use an escape sequence. Default: \n
WORK_DIR='file_directory'
Specifies the directory in which the files are created if the filename is relative. Default: Value of II_TEMPORARY
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.
INSERT INTO EXTERNAL CSV 'hdfs://mydatanode:8020/Actian/tmp/sales_fact.csv' SELECT * FROM sales WITH NULL_MARKER='NULL', FIELD_SEPARATOR=',', RECORD_SEPARATOR='\n'
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:
INSERT INTO EXTERNAL CSV '/tmp/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 in HDFS location, and insert the text NULL for null values:
INSERT INTO EXTERNAL CSV 'hdfs://your.domain.com:8020/tmp/supplier.csv' SELECT * FROM supplier WITH NULL_MARKER='NULL'