Exporting Data

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'

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');