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 file location
You can use remote cloud storage (Amazon S3, Google Cloud Platform, etc.) to store the exported CSV data. Alternatively, you can use an HTTP server accessible from all nodes.
For simplicity's sake, it's strongly recommended to use cloud/remote storage for the data you want to export. Local files are supported; however, they must be accessible identically from all nodes in the cluster.
After the export has been initiated, you can cancel it with
EXPORTstatement cannot be used within a transaction.
Only members of the
admin role can run
EXPORT. By default, the
root user belongs to the
||Specify the URL of the file location where you want to store the exported CSV data.|
||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
URLs for the file directory location you want to export to must use the following format:
||N/A (see Example file URLs||
|Google Cloud 1||
|S3-compatible services 5||
If you write to
nodelocal storage in a multi-node cluster, individual data files will be written to the
extern directories of arbitrary nodes and will likely not work as intended. To work correctly, each node must have the
--external-io-dir flag point to the same NFS mount or other network-backed, shared storage.
If your environment requires an HTTP or HTTPS proxy server for outgoing connections, you can set the standard
HTTPS_PROXY environment variables when starting CockroachDB.
AUTHparameter is not specified, the
cloudstorage.gs.default.keycluster setting will be used if it is non-empty, otherwise the
implicitbehavior is used. If the
implicit, all GCS connections use Google's default authentication strategy. If the
cloudstorage.gs.default.keycluster setting must be set to the contents of a service account file which will be used during authentication. New in v19.1: If the
specified, GCS connections are authenticated on a per-statement basis, which allows the JSON key object to be sent in the
CREDENTIALSparameter. The JSON key object should be base64-encoded (using the standard encoding in RFC 4648).
2 You can create your own HTTP server with Caddy or nginx. A custom root CA can be appended to the system's default CAs by setting the
cloudstorage.http.custom_cacluster setting, which will be used when verifying certificates from HTTPS URLs.
3 The file system backup location on the NFS drive is relative to the path specified by the
--external-io-dirflag set while starting the node. If the flag is set to
disabled, then imports from local directories and NFS drives are disabled.
4 New in v19.1: The host component of NFS/Local can either be empty or the
nodeID. If the
nodeIDis specified, it is currently ignored (i.e., any node can be sent work and it will look in its local input/output directory); however, the
nodeIDwill likely be required in the future.
5 A custom root CA can be appended to the system's default CAs by setting the
cloudstorage.http.custom_cacluster setting, which will be used when verifying certificates from an S3-compatible service.
AWS_REGIONparameter is optional since it is not a required parameter for most S3-compatible services. Specify the parameter only if your S3-compatible service requires it.
Example file URLs
Note: If you write to
You can specify the base directory where you want to store the exported .csv files. CockroachDB will create several files in the specified directory with programmatically generated names (e.g., n1.1.csv, n1.2.csv, n2.1.csv, ...).
You can control the
EXPORT process's behavior using any of the following key-value pairs as a
If not using comma as your column delimiter, you can specify another ASCII character as the delimiter.
|Value||The ASCII character that delimits columns in your rows|
|Example||To use tab-delimited values:
Convert SQL NULL values so they match the specified string.
|Value||The string that should be used to represent NULL values. To avoid collisions, it is important to pick
|Example||To use empty columns as NULL:
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
View a running export
View running exports by using
> SHOW QUERIES;
Cancel a running export
> CANCEL QUERY '14dacc1f9a781e3d0000000000000001';
EXPORT may fail with an error if the SQL statements are incompatible with DistSQL. In that case, use the non-enterprise feature to export tabular data in CSV format.