EXPORT statement exports tabular data or the results of arbitrary
SELECT statements to CSV files.
Using the CockroachDB distributed execution engine,
EXPORT parallelizes CSV creation across all nodes in the cluster, making it possible to quickly get large sets of data out of CockroachDB in a format that can be ingested by downstream systems. If you do not need distributed exports, you can use the non-enterprise feature to export tabular data in CSV format.
EXPORT no longer requires an enterprise license.
After the export has been initiated, you can cancel it with
EXPORTstatement cannot be used within a transaction.
||Specify the URL of the file location where you want to store the exported CSV data.
Note: It is best practice to use a unique destination for each export, to avoid mixing files from different exports in one directory.
||Control your export's behavior with these options.|
||Specify the query whose result you want to export to CSV format.|
||Specify the name of the table you want to export to CSV format.|
Export file URL
You can specify the base directory where you want to store the exported .csv files. CockroachDB will create the export file(s) in the specified directory with programmatically generated names (e.g.,
exportabc123-n2.1.csv, ...). Each export should use a unique destination directory to avoid collision with other exports.
EXPORT command returns the list of files to which the data was exported. You may wish to record these for use in subsequent imports.
A hexadecimal hash code (
abc123... in the file names above) uniquely identifies each export run; files sharing the same hash are part of the same export. If you see multiple hash codes within a single destination directory, then the directory contains multiple exports, which will likely cause confusion (duplication) on import. We recommend that you manually clean up the directory, to ensure that it contains only a single export run.
For more information, see the following:
You can control the
EXPORT process's behavior using any of the following key-value pairs as a
||The ASCII character that delimits columns in your rows. If not using comma as your column delimiter, you can specify another ASCII character as the delimiter. Default:
To use tab-delimited values:
||The string that should be used to represent NULL values. To avoid collisions, it is important to pick
To use empty columns as NULL:
||This instructs export to write
See the example below.
||The number of rows to be converted to CSV and written to a single CSV file. Default:
EXPORT returns a table of (perhaps multiple) files to which the data was exported:
||The file to which the data was exported.|
||The number of rows exported to this file.|
||The file size in bytes.|
Export a table
> EXPORT INTO CSV 'azure://acme-co/customer-export-data?AZURE_ACCOUNT_KEY=hash&AZURE_ACCOUNT_NAME=acme-co' WITH delimiter = '|' FROM TABLE bank.customers;
Export using a
> EXPORT INTO CSV 'azure://acme-co/customer-export-data?AZURE_ACCOUNT_KEY=hash&AZURE_ACCOUNT_NAME=acme-co' FROM SELECT * FROM bank.customers WHERE id >= 100;
Non-distributed export using the SQL shell
$ cockroach sql -e "SELECT * from bank.customers WHERE id>=100;" --format=csv > my.csv
Export gzip compressed CSV files
> EXPORT INTO CSV 'azure://acme-co/customer-export-data?AZURE_ACCOUNT_KEY=hash&AZURE_ACCOUNT_NAME=acme-co' WITH compression = 'gzip' FROM TABLE bank.customers;
filename | rows | bytes ---------------------------------------------------+------+-------- export16808a04292505c80000000000000001-n1.0.csv.gz | 17 | 824 (1 row)
View a running export
View running exports by using
> SHOW STATEMENTS;
Cancel a running export
> CANCEL QUERY '14dacc1f9a781e3d0000000000000001';
EXPORT may fail with an error if the SQL statements are incompatible with DistSQL. In that case, export tabular data in CSV format.