When you create an Enterprise changefeed, you can include the initial_scan = 'only' 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 data out of your database.
The benefits of using changefeeds for this function instead of export, include:
- Changefeeds are jobs, which can be paused, resumed, and cancelled.
- There is observability into a changefeed job using
SHOW CHANGEFEED JOBSand the Changefeeds Dashboard in the DB Console. - Changefeed jobs have built-in checkpointing and retries.
- Changefeed sinks provide additional endpoints for your data.
- You can use the
format=csvoption withinitial_scan= 'only'to emit messages in CSV format.
New in v22.2:
Changefeeds emit the same CSV format as 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.
Examples
Export data with changefeeds
To create a changefeed that will only complete an initial scan of a table(s), run the following:
CREATE CHANGEFEED FOR TABLE movr.users INTO '{scheme}://{host}:{port}?{query_parameters}' WITH initial_scan = 'only', format=csv;
The job will return a job ID once it has started. You can use SHOW CHANGEFEED JOBS to check on the status:
SHOW CHANGEFEED JOB {job ID};
When the scan has completed you will find the output shows succeeded in the status field.
Export filtered data with changefeeds
CDC transformations are in preview.
Use CDC transformations with the initial_scan = 'only' option to run a changefeed to export specific columns from your table:
CREATE CHANGEFEED INTO '{scheme}://{host}:{port}?{query_parameters}'
WITH initial_scan = 'only', format=csv, schema_change_policy = 'stop'
AS SELECT name, city FROM movr.users;
See the Change Data Capture Transformations page for more examples.
See also
Was this helpful?
