Exporting Data with SQL

COPY TO

COPY ( <SELECT statement> ) TO '<file path>' [WITH (<property> = value, ...)];

<file path> must be a path on the server. This command exports the results of any SELECT statement to the file. There is a special mode when <file path> is empty. In that case, the server automatically generates a file in <OmniSci Directory>/omnisci_export that is the client session id with the suffix .txt.

Available properties in the optional WITH clause are described in the following table.

Parameter Description Default Value
delimiter A single-character string for the delimiter between column values; most commonly:
  • , for CSV files
  • \t for tab-delimited files

Other delimiters include | ,~, ^, and;.

Note: OmniSci does not use file extensions to determine the delimiter.

 ',' (CSV file)
escape A single-character string for escaping quotes. ' (quote)
header Either 'true' or 'false', indicating whether to output a header line for all the column names. 'true'
line_delimiter A single-character string for terminating each line. '\n'
nulls A string pattern indicating that a field is NULL. An empty string, 'NA', or \N
quote A single-character string for quoting a column value. " (double quote)
quoted Either 'true' or 'false', indicating whether all the column values should be output in quotes. 'true'
Note When using the COPY TO command, you might encounter the following error: 
Query couldn’t keep the entire working set of columns in GPU Memory.
To avoid this error, use the omnisql command \cpu to put your OmniSci server in CPU mode before using the COPY TO command. See Configuration.

Example:

COPY (SELECT * FROM tweets) TO '/tmp/tweets.csv';
COPY (SELECT * tweets ORDER BY tweet_time LIMIT 10000) TO
  '/tmp/tweets.tsv' WITH (delimiter = '\t', quoted = 'true', header = 'false');