EXPORT

On this page

The EXPORT statement exports tabular data or the results of arbitrary SELECT statements to the following:

• CSV files
• Parquet files

Using the CockroachDB distributed execution engine, EXPORT parallelizes file 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.

Cancelling export

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

Synopsis

Required privileges

The user must have the SELECT privilege on the table being exported, unless the destination URI requires admin privileges.

Parameters

Export file URL

You can specify the base directory where you want to store the exported 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.

For more information, see the following:

• Use Cloud Storage for Bulk Operations
• Use a Local File Server for Bulk Operations

Export options

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

Success responses

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

Parquet types

CockroachDB types map to Parquet types as per the following:

Exports and AS OF SYSTEM TIME

The AS OF SYSTEM TIME clause is not required in EXPORT statements, even though they are long-running queries. If it is omitted, AS OF SYSTEM TIME is implicitly set to the start of the statement's execution. The risk of contention is low because other transactions would need to have exactly the same transaction start time as the EXPORT statement's start time.

Examples

The following examples make use of:

• Amazon S3 connection strings. For guidance on connecting to other storage options or using other authentication parameters instead, read Use Cloud Storage for Bulk Operations.
• The default AUTH=specified parameter. For guidance on using AUTH=implicit authentication with Amazon S3 buckets instead, 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 into CSV

This example uses the delimiter option to define the ASCII character that delimits columns in your rows:

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

This examples uses the nullas option to define the string that represents NULL values:

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

Export a table into Parquet

> EXPORT INTO PARQUET
  's3://{BUCKET NAME}/{customer-export-data}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
  FROM TABLE bank.customers;

Export using a SELECT statement

> 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

$ 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 compressed files

gzip compression is supported for both PARQUET and CSV file formats:

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

PARQUET data also supports snappy compression:

> EXPORT INTO PARQUET
  's3://{BUCKET NAME}/{customer-export-data}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
  WITH compression = 'snappy' FROM TABLE bank.customers;

filename                                                   | rows | bytes
-----------------------------------------------------------+------+--------
export16808a04292505c80000000000000001-n1.0.parquet.snappy |   17 |   824
(1 row)

Export tabular data with an S3 storage class

New in v22.1: To associate your export objects with a specific storage class in your Amazon S3 bucket, use the S3_STORAGE_CLASS parameter with the class. For example, the following S3 connection URI specifies the INTELLIGENT_TIERING storage class:

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

Use the parameter to set one of these storage classes listed in Amazon's documentation. For more general usage information, see Amazon's Using Amazon S3 storage classes documentation.

Export data out of CockroachDB Cloud

Using EXPORT with userfile is not recommended. You can either export data to cloud storage or to a local CSV file by using cockroach sql --execute:

$ cockroach sql \
--url 'postgres://{username}:{password}@{host}:26257?sslmode=verify-full&sslrootcert={path/to/certs_dir}/cc-ca.crt' \
--execute "SELECT * FROM bank.customers" --format=csv > /Users/{username}/{path/to/file}/customers.csv

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;

View a running export

View running exports by using SHOW STATEMENTS:

> SHOW STATEMENTS;

Cancel a running export

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

> 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

• IMPORT
• IMPORT INTO
• Use a Local File Server for Bulk Operations
• Use Cloud Storage for Bulk Operations

Was this helpful?