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

# Export Data with Changefeeds

When you create a changefeed, you can include the [`initial_scan = 'only'`](https://docs.cockroachlabs.com/docs/stable/create-changefeed) option to specify that the changefeed should only complete a table scan. The changefeed emits messages for the table scan and then the job completes with a `succeeded` status. As a result, you can create a changefeed with `initial_scan = 'only'` to [`EXPORT`](https://docs.cockroachlabs.com/docs/stable/export) data out of your database.

You can also [schedule a changefeed](#create-a-scheduled-changefeed-to-export-filtered-data) that is emitting messages to a downstream sink, which allows you to use a changefeed initial scan for exporting data on a regular cadence.

The benefits of using changefeeds for this use case instead of [export](https://docs.cockroachlabs.com/docs/stable/export.md), include:

* Changefeeds are jobs, which can be [paused](https://docs.cockroachlabs.com/docs/stable/pause-job.md), [resumed](https://docs.cockroachlabs.com/docs/stable/resume-job.md), [cancelled](https://docs.cockroachlabs.com/docs/stable/cancel-job.md), [scheduled](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-changefeed.md), and [altered](https://docs.cockroachlabs.com/docs/stable/alter-changefeed.md).
* There is observability into a changefeed job using [`SHOW CHANGEFEED JOBS`](https://docs.cockroachlabs.com/docs/stable/show-jobs#show-changefeed-jobs) and the [Changefeeds Dashboard](https://docs.cockroachlabs.com/docs/stable/ui-cdc-dashboard.md) in the DB Console.
* Changefeed jobs have built-in [checkpointing](https://docs.cockroachlabs.com/docs/stable/how-does-a-changefeed-work.md) and [retries](https://docs.cockroachlabs.com/docs/stable/monitor-and-debug-changefeeds.md#changefeed-retry-errors).
* [Changefeed sinks](https://docs.cockroachlabs.com/docs/stable/changefeed-sinks.md) provide additional endpoints for your data.
* You can use the [`format=csv`](https://docs.cockroachlabs.com/docs/stable/create-changefeed) option with `initial_scan= 'only'` to emit messages in CSV format.

Changefeeds emit the same CSV format as [`EXPORT`](https://docs.cockroachlabs.com/docs/stable/export). In v22.1, changefeeds emitted CSV data that wrapped some values in single quotes, which were not wrapped when exporting data with the `EXPORT` statement.

## Message formats

By default, changefeeds emit messages in JSON format. You can use a different format by [creating a changefeed](https://docs.cockroachlabs.com/docs/stable/create-changefeed.md) with the [`format`](https://docs.cockroachlabs.com/docs/stable/create-changefeed) option and specifying one of the following:

* `json`
* `csv`
* `avro`
* `parquet`
* `protobuf`

## Examples

### Export data with a changefeed

To create a changefeed that will only complete an initial scan of a table(s), run the following:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE CHANGEFEED FOR TABLE movr.users INTO '{scheme}://{host}:{port}?{query_parameters}' WITH initial_scan = 'only', format=csv;
```

Or, use [CDC queries](https://docs.cockroachlabs.com/docs/stable/cdc-queries.md) to filter the data that your changefeed emits:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE CHANGEFEED INTO '{scheme}://{host}:{port}?{query_parameters}'
  WITH initial_scan = 'only', format=csv AS SELECT name, city FROM movr.users;
```

The job will return a job ID once it has started. You can use `SHOW CHANGEFEED JOBS` to check on the status:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CHANGEFEED JOB {job ID};
```

When the scan has completed you will find the output shows `succeeded` in the `status` field.

### Create a scheduled changefeed to export filtered data

This example creates a nightly export of some filtered table data with a [scheduled changefeed](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-changefeed.md) that will run just after midnight every night. The changefeed uses [CDC queries](https://docs.cockroachlabs.com/docs/stable/cdc-queries.md) to query the table and filter the data it will send to the sink:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEDULE sf_skateboard FOR CHANGEFEED INTO 'external://cloud-sink' WITH format=csv
  AS SELECT current_location, id, type, status FROM vehicles
  WHERE city = 'san francisco' AND type = 'skateboard'
  RECURRING '1 0 * * *' WITH SCHEDULE OPTIONS on_execution_failure=retry, on_previous_running=start;
```

The [schedule options](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-changefeed.md#schedule-options) control the schedule's behavior:

* If it runs into a failure, `on_execution_failure=retry` will ensure that the schedule retries the changefeed immediately.
* If the previous scheduled changefeed is still running, `on_previous_running=start` will start a new changefeed at the defined cadence.

### Video demo

For a demo on how to use changefeeds to export large amounts of data, watch the following video:

[YouTube video player](https://www.youtube.com/embed/UXsKuXbkjws)

## See also

* [Changefeed Messages](https://docs.cockroachlabs.com/docs/stable/changefeed-messages.md)
* [`CREATE CHANGEFEED`](https://docs.cockroachlabs.com/docs/stable/create-changefeed)
* [`CREATE SCHEDULE FOR CHANGEFEED`](https://docs.cockroachlabs.com/docs/stable/create-schedule-for-changefeed)