> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# EXPORT

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

<Note>
  **Cockroach Labs recommends using <InternalLink path="export-data-with-changefeeds">changefeeds to export data</InternalLink>** because they provide better performance for growing workloads. Additionally, changefeeds operate as jobs, which offer <InternalLink path="monitor-and-debug-changefeeds">observability</InternalLink>, <InternalLink path="export-data-with-changefeeds#create-a-scheduled-changefeed-to-export-filtered-data">scheduling</InternalLink>, and <InternalLink path="create-and-configure-changefeeds">job management</InternalLink>.
</Note>

The `EXPORT` <InternalLink path="sql-statements">statement</InternalLink> exports tabular data or the results of arbitrary `SELECT` statements to the following:

* CSV files
* Parquet files

Using the <InternalLink path="architecture/sql-layer#distsql">CockroachDB distributed execution engine</InternalLink>, `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](#non-distributed-export-using-the-sql-client).

## Cancelling export

After the export has been initiated, you can cancel it with <InternalLink path="cancel-query">`CANCEL QUERY`</InternalLink>.

For detail on handling partially complete exports at the destination, refer to [Export File URL](#export-file-url).

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/tGEtnfk6iFpQGcmj/images/sql-diagrams/v26.1/export.svg?fit=max&auto=format&n=tGEtnfk6iFpQGcmj&q=85&s=3f8e96f7a5dc8a3eb7cbc1c3d161863e" alt="export syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="679" height="167" data-path="images/sql-diagrams/v26.1/export.svg" />

<Note>
  The `EXPORT` statement cannot be used within a <InternalLink path="transactions">transaction</InternalLink>.
</Note>

## Required privileges

The user must have the `SELECT` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the table being exported, unless the <InternalLink path="import-into#source-privileges">destination URI requires `admin` privileges</InternalLink>.

### Destination privileges

You can grant a user the `EXTERNALIOIMPLICITACCESS` <InternalLink path="security-reference/authorization#supported-privileges">system-level privilege</InternalLink> to interact with external resources that require implicit access.

Either the `EXTERNALIOIMPLICITACCESS` <InternalLink path="security-reference/authorization#supported-privileges">system-level privilege</InternalLink> or the <InternalLink path="security-reference/authorization#admin-role">`admin`</InternalLink> role is required for the following scenarios:

* Interacting with a cloud storage resource using <InternalLink path="cloud-storage-authentication">`IMPLICIT` authentication</InternalLink>.
* Using a [custom endpoint](https://docs.aws.amazon.com/sdk-for-go/api/aws/endpoints/) on S3.
* Using the <InternalLink path="cockroach-nodelocal-upload">`cockroach nodelocal upload`</InternalLink> command.
* Using <InternalLink path="use-a-local-file-server">HTTP</InternalLink> or HTTPS.

No special privilege is required for:

* Interacting with an Amazon S3 and Google Cloud Storage resource using `SPECIFIED` credentials. Azure Storage is always `SPECIFIED` by default.
* Using <InternalLink path="use-userfile-storage">Userfile</InternalLink> storage.

We recommend using <InternalLink path="use-cloud-storage">cloud storage</InternalLink>. You also need to ensure that the permissions at your storage destination are configured for the operation. See <InternalLink path="use-cloud-storage#storage-permissions">Storage Permissions</InternalLink> for a list of the necessary permissions that each bulk operation requires.

<Danger>
  While Cockroach Labs actively tests Amazon S3, Google Cloud Storage, and Azure Storage, we **do not** test S3-compatible services  <InternalLink path="cloud-storage-authentication">S3-compatible services</InternalLink>  (e.g., [MinIO](https://min.io/), [Red Hat Ceph](https://docs.ceph.com/en/pacific/radosgw/s3/)).
</Danger>

## Parameters

| Parameter          | Description                                                                                                                                                                                                                                          |
| ------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `file_location`    | Specify the [URL of the file location](#export-file-url) where you want to store the exported data.<br /><br />Note: It is best practice to use a unique destination for each export, to avoid mixing files from different exports in one directory. |
| `opt_with_options` | Control your export's behavior with [these options](#export-options).                                                                                                                                                                                |
| `select_stmt`      | Specify the query whose result you want to export.                                                                                                                                                                                                   |
| `table_name`       | Specify the name of the table you want to export.                                                                                                                                                                                                    |

### 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](#success-responses) the list of files to which the data was exported. You may wish to record these for use in subsequent imports.

If an export encounters some kind of failure or [cancellation](#cancel-a-running-export), it will leave any written files behind in the destination. To run a new export and avoid collision with previously written export files, consider doing the following:

* Change the destination (or destination prefix) that you are exporting to.
* Remove the partial results at the destination from any previously attempted exports.
* Use a <InternalLink path="export-data-with-changefeeds">changefeed export</InternalLink> instead because it operates as a job, offering more observability into failures.

<Note>
  A hexadecimal hash code (`abc123...` in the file names) 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.
</Note>

For more information, see the following:

* <InternalLink path="use-cloud-storage">Use Cloud Storage</InternalLink>
* <InternalLink path="use-a-local-file-server">Use a Local File Server</InternalLink>

You can create an external connection to represent an external storage or sink URI. This allows you to specify the external connection's name in statements rather than the provider-specific URI. For detail on using external connections, see the <InternalLink path="create-external-connection">`CREATE EXTERNAL CONNECTION`</InternalLink> page.

### Export options

You can control the <InternalLink path="export">`EXPORT`</InternalLink> 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:** `,`. \<br /><br />To use tab-delimited values: `WITH delimiter = e'\t'` \<br /><br />See the [example](#export-a-table-into-csv).                                                                                                                            |
| `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. \<br /><br />To use empty columns as `NULL`: `WITH nullas = ''` \<br /><br />See the [example](#export-a-table-into-csv).                                                                                                                                               |
| `compression` | `CSV DATA`, `PARQUET DATA`   | This instructs export to write compressed files to the specified destination.<br /><br />For `CSV DATA`, `gzip` compression is supported. For `PARQUET DATA`, both `gzip` and `snappy` compression is supported. \<br /><br />See the [example](#export-compressed-files).                                                                                                                                                               |
| `chunk_rows`  | `CSV DATA`, `PARQUET DATA`   | The number of rows to be converted  and written to a single file. **Default:** `100000`. \<br />For example, `WITH chunk_rows = '5000'` for a table with 10,000 rows would produce two files. \<br /><br />**Note**:`EXPORT` will stop and upload the file whether the configured limit for `chunk_rows` or `chunk_size` is reached first.                                                                                               |
| `chunk_size`  | `CSV DATA`, `PARQUET DATA`   | A target size per file that you can specify during an `EXPORT`. Once the target size is reached, the file is uploaded before processing further rows. **Default:** `32MB`. \<br />For example, to set the size of each file uploaded during the export to 10MB: `WITH chunk_size = '10MB'`. \<br /><br />**Note**:`EXPORT` will stop and upload the file whether the configured limit for `chunk_rows` or `chunk_size` is reached first. |

## 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.                   |

## Parquet types

CockroachDB types map to [Parquet types](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md) listed in the following table. All columns witten to Parquet files will be nullable, therefore the Parquet repetition level is `optional`.

| CockroachDB Type                                                                                     | Parquet Type                                                                                | Parquet Logical Type                                                                        |
| ---------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------- |
| <InternalLink path="bool">`BOOL`</InternalLink>                                                      | `BOOLEAN`                                                                                   | `nil`                                                                                       |
| <InternalLink path="string">`STRING`</InternalLink>                                                  | byte array                                                                                  | `STRING`                                                                                    |
| <InternalLink path="collate">`COLLATE`</InternalLink>                                                | byte array                                                                                  | `STRING`                                                                                    |
| <InternalLink path="inet">`INET`</InternalLink>                                                      | byte array                                                                                  | `STRING`                                                                                    |
| <InternalLink path="jsonb">`JSONB`</InternalLink>                                                    | byte array                                                                                  | `JSON`                                                                                      |
| <InternalLink path="int">`INT`</InternalLink> <InternalLink path="int">`INT8`</InternalLink>         | `INT64`                                                                                     | `nil`                                                                                       |
| <InternalLink path="int">`INT2`</InternalLink> <InternalLink path="int">`INT4`</InternalLink>        | `INT32`                                                                                     | `nil`                                                                                       |
| <InternalLink path="float">`FLOAT`</InternalLink> <InternalLink path="float">`FLOAT8`</InternalLink> | `FLOAT64`                                                                                   | `nil`                                                                                       |
| <InternalLink path="float">`FLOAT4`</InternalLink>                                                   | `FLOAT32`                                                                                   | `nil`                                                                                       |
| <InternalLink path="decimal">`DECIMAL`</InternalLink>                                                | byte array                                                                                  | `DECIMAL` \<br />Note: scale and precision data are preserved in the Parquet file.          |
| <InternalLink path="uuid">`UUID`</InternalLink>                                                      | `fixed_len_byte_array`                                                                      | `nil`                                                                                       |
| <InternalLink path="bytes">`BYTES`</InternalLink>                                                    | byte array                                                                                  | `nil`                                                                                       |
| <InternalLink path="bit">`BIT`</InternalLink>                                                        | byte array                                                                                  | `nil`                                                                                       |
| <InternalLink path="enum">`ENUM`</InternalLink>                                                      | byte array                                                                                  | `ENUM`                                                                                      |
| <InternalLink path="data-types#data-type-conversions-and-casts">`Box2D`</InternalLink>               | byte array                                                                                  | `STRING`                                                                                    |
| <InternalLink path="data-types#data-type-conversions-and-casts">`GEOGRAPHY`</InternalLink>           | byte array                                                                                  | `nil`                                                                                       |
| <InternalLink path="data-types#data-type-conversions-and-casts">`GEOMETRY`</InternalLink>            | byte array                                                                                  | `nil`                                                                                       |
| <InternalLink path="date">`DATE`</InternalLink>                                                      | byte array                                                                                  | `STRING`                                                                                    |
| <InternalLink path="time">`TIME`</InternalLink>                                                      | `INT64`                                                                                     | `TIME` \<br />Note: microseconds after midnight; \<br />exporting to microsecond precision. |
| <InternalLink path="time">`TIMETZ`</InternalLink>                                                    | byte array                                                                                  | `STRING` \<br />Note: exporting to microsecond precision.                                   |
| <InternalLink path="interval">`INTERVAL`</InternalLink>                                              | byte array                                                                                  | `STRING` \<br />Note: specifically represented as ISO8601.                                  |
| <InternalLink path="timestamp">`TIMESTAMP`</InternalLink>                                            | byte array                                                                                  | `STRING` \<br />Note: exporting to microsecond precision.                                   |
| <InternalLink path="timestamp">`TIMESTAMPTZ`</InternalLink>                                          | byte array                                                                                  | `STRING` \<br />Note: exporting to microsecond precision.                                   |
| <InternalLink path="array">`ARRAY`</InternalLink>                                                    | Encoded as a repeated field; \<br />each array value is encoded as per the preceding types. | `nil`                                                                                       |

## Exports and `AS OF SYSTEM TIME`

The <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> 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 <InternalLink path="performance-best-practices-overview#transaction-contention">contention</InternalLink> 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 <InternalLink path="use-cloud-storage#example-file-urls">Use Cloud Storage</InternalLink>.
* The **default** `AUTH=specified` parameter. For guidance on using `AUTH=implicit` authentication with Amazon S3 buckets instead, read <InternalLink path="cloud-storage-authentication">Cloud Storage Authentication</InternalLink>.

Also, note the following features for connecting and authenticating to cloud storage:

* External connections, which allow you to represent an external storage or sink URI. You can then specify the external connection's name in statements rather than the provider-specific URI. For detail on using external connections, see the <InternalLink path="create-external-connection">`CREATE EXTERNAL CONNECTION`</InternalLink> page.
* Assume role authentication, which allows you to limit the control specific users have over your storage buckets. See <InternalLink path="cloud-storage-authentication">Assume role authentication</InternalLink> for more information.

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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 <InternalLink path="selection-queries">selection queries</InternalLink>.

### Non-distributed export using the SQL client

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql -e "SELECT * from bank.customers WHERE id>=100;" --format=csv > my.csv
```

For more information about the SQL client, see <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink>.

### Export compressed files

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

To associate your export objects with a <InternalLink path="use-cloud-storage#amazon-s3-storage-classes">specific storage class</InternalLink> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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](https://docs.aws.amazon.com/AmazonS3/latest/API/API_PutObject.html#AmazonS3-PutObject-request-header-StorageClass) listed in Amazon's documentation. For more general usage information, see Amazon's [Using Amazon S3 storage classes](https://docs.aws.amazon.com/AmazonS3/latest/userguide/storage-class-intro.html) documentation.

### Export data out of CockroachDB Cloud

Using `EXPORT` with <InternalLink path="use-userfile-storage">`userfile`</InternalLink> is not recommended. You can either export data to <InternalLink path="use-cloud-storage">cloud storage</InternalLink> or to a local CSV file by using <InternalLink path="cockroach-sql#general">`cockroach sql --execute`</InternalLink>:

<Tabs>
  <Tab title="local CSV">
    The following example exports the `customers` table from the `bank` database into a local CSV file:

    ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    $ 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
    ```
  </Tab>

  <Tab title="Cloud storage">
    The following example exports the `customers` table from the `bank` database into a cloud storage bucket in CSV format:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    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;
    ```
  </Tab>
</Tabs>

### View a running export

View running exports by using <InternalLink path="show-statements">`SHOW STATEMENTS`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW STATEMENTS;
```

### Cancel a running export

Use <InternalLink path="show-statements">`SHOW STATEMENTS`</InternalLink> to get a running export's `query_id`, which can be used to <InternalLink path="cancel-query">cancel the export</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CANCEL QUERY '14dacc1f9a781e3d0000000000000001';
```

For detail on handling partially complete exports at the destination, refer to [Export File URL](#export-file-url).

## 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](#non-distributed-export-using-the-sql-client).

## See also

* <InternalLink path="import-into">`IMPORT INTO`</InternalLink>
* <InternalLink path="use-a-local-file-server">Use a Local File Server</InternalLink>
* <InternalLink path="use-cloud-storage">Use Cloud Storage</InternalLink>