EXPORT

On this page Carat arrow pointing down
Warning:
As of May 10, 2022, CockroachDB v20.2 is no longer supported. For more details, refer to the Release Support Policy.

The 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 export tabular data in CSV format.

Note:

New in v20.2: EXPORT no longer requires an Enterprise license.

Cancelling export

After the export has been initiated, you can cancel it with CANCEL QUERY.

Synopsis

EXPORT INTO CSV file_location opt_with_options FROM select_stmt TABLE table_name
Note:
The EXPORT statement cannot be used within a transaction.

Required privileges

Only members of the admin role can run EXPORT. By default, the root user belongs to the admin role.

Parameters

Parameter Description
file_location 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.
WITH kv_option Control your export's behavior with these options.
select_stmt Specify the query whose result you want to export to CSV format.
table_name 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-n1.1.csv, exportabc123-n1.2.csv, exportabc123-n2.1.csv, ...). Each export should use a unique destination directory to avoid collision with other exports.

The EXPORT command returns the list of files to which the data was exported. You may wish to record these for use in subsequent imports.

Note:

New in v20.2: 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:

Export options

You can control the EXPORT process's behavior using any of the following key-value pairs as a kv_option.

Key
Context
Value
delimiter CSV DATA 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: WITH delimiter = e'\t'
nullas CSV DATA, DELIMITED DATA The string that should be used to represent NULL values. To avoid collisions, it is important to pick nullas values that do not appear in the exported data.

To use empty columns as NULL: WITH nullas = ''
compression CSV DATA This instructs export to write gzip compressed CSV files to the specified destination.

See the example below.
chunk_rows CSV DATA The number of rows to be converted to CSV and written to a single CSV file. Default: 100000.

For example, WITH chunk_rows = '5000' for a table with 10,000 rows would produce two CSV files.

Success responses

Successful EXPORT returns a table of (perhaps multiple) files to which the data was exported:

Response Description
filename The file to which the data was exported.
rows The number of rows exported to this file.
bytes The file size in bytes.

Examples

The following provide connection examples to cloud storage providers. For more information on connecting to different storage options, read Use Cloud Storage for Bulk Operations.

Note:

The examples in this section use the default AUTH=specified parameter. For more detail on how to use implicit authentication with Amazon S3 buckets, read Use Cloud Storage for Bulk Operations — Authentication.

Each of these examples use the bank database and the customers table; customer-export-data is the demonstration path to which we're exporting our customers' data in this example.

Export a table

icon/buttons/copy
> EXPORT INTO CSV
  's3://{BUCKET NAME}/{customer-export-data}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
  WITH delimiter = '|' FROM TABLE bank.customers;

Export using a SELECT statement

icon/buttons/copy
> EXPORT INTO CSV
  's3://{BUCKET NAME}/{customer-export-data}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
  FROM SELECT * FROM bank.customers WHERE id >= 100;

For more information, see selection queries.

Non-distributed export using the SQL client

icon/buttons/copy
$ cockroach sql -e "SELECT * from bank.customers WHERE id>=100;" --format=csv > my.csv

For more information, about the SQL client, see cockroach sql.

Export gzip compressed CSV files

icon/buttons/copy
> EXPORT INTO CSV
  's3://{BUCKET NAME}/{customer-export-data}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
  WITH compression = 'gzip' FROM TABLE bank.customers;
filename                                           | rows | bytes
---------------------------------------------------+------+--------
export16808a04292505c80000000000000001-n1.0.csv.gz |   17 |   824
(1 row)

Each of these examples use the bank database and the customers table; customer-export-data is the demonstration path to which we're exporting our customers' data in this example.

Export a table

icon/buttons/copy
> EXPORT INTO CSV
  'azure://{CONTAINER NAME}/{customer-export-data}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}'
  WITH delimiter = '|' FROM TABLE bank.customers;

Export using a SELECT statement

icon/buttons/copy
> EXPORT INTO CSV
  'azure://{CONTAINER NAME}/{customer-export-data}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}'
  FROM SELECT * FROM bank.customers WHERE id >= 100;

For more information, see selection queries.

Non-distributed export using the SQL client

icon/buttons/copy
$ cockroach sql -e "SELECT * from bank.customers WHERE id>=100;" --format=csv > my.csv

For more information, about the SQL client, see cockroach sql.

Export gzip compressed CSV files

icon/buttons/copy
> EXPORT INTO CSV
  'azure://{CONTAINER NAME}/{customer-export-data}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}'
  WITH compression = 'gzip' FROM TABLE bank.customers;
filename                                           | rows | bytes
---------------------------------------------------+------+--------
export16808a04292505c80000000000000001-n1.0.csv.gz |   17 |   824
(1 row)
Note:

The examples in this section use the AUTH=specified parameter, which will be the default behavior in v21.2 and beyond for connecting to Google Cloud Storage. For more detail on how to pass your Google Cloud Storage credentials with this parameter, or, how to use implicit authentication, read Use Cloud Storage for Bulk Operations — Authentication.

Each of these examples use the bank database and the customers table; customer-export-data is the demonstration path to which we're exporting our customers' data in this example.

Export a table

icon/buttons/copy
> EXPORT INTO CSV
  'gs://{BUCKET NAME}/{customer-export-data}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
  WITH delimiter = '|' FROM TABLE bank.customers;

Export using a SELECT statement

icon/buttons/copy
> EXPORT INTO CSV
  'gs://{BUCKET NAME}/{customer-export-data}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
  FROM SELECT * FROM bank.customers WHERE id >= 100;

For more information, see selection queries.

Non-distributed export using the SQL client

icon/buttons/copy
$ cockroach sql -e "SELECT * from bank.customers WHERE id>=100;" --format=csv > my.csv

For more information, about the SQL client, see cockroach sql.

Export gzip compressed CSV files

icon/buttons/copy
> EXPORT INTO CSV
  'gs://{BUCKET NAME}/{customer-export-data}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
  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 QUERIES:

icon/buttons/copy
> SHOW QUERIES;

Cancel a running export

Use SHOW QUERIES to get a running export's query_id, which can be used to cancel the export:

icon/buttons/copy
> CANCEL QUERY '14dacc1f9a781e3d0000000000000001';

Known limitation

EXPORT may fail with an error if the SQL statements are incompatible with DistSQL. In that case, export tabular data in CSV format.

See also


Yes No
On this page

Yes No