CREATE SCHEDULE FOR CHANGEFEED

On this page

CREATE SCHEDULE FOR CHANGEFEED allows you to create a scheduled changefeed to export data out of CockroachDB. Scheduled changefeeds have the scale, observability, and endpoint sink options that changefeed jobs include, with the convenience of setting a regular schedule. A changefeed job created with CREATE SCHEDULE FOR CHANGEFEED performs a one-time table scan using the initial scan functionality to create an export of your table data.

For more detail on using changefeeds to create an export of your table data, see Export Data with Changefeeds.

Required privileges

Privilege model

The following summarizes the operations users can run depending on whether the assigned privileges are at the job or table level:

CHANGEFEED privilege

You can grant a user the CHANGEFEED privilege to allow them to create changefeeds on a specific table:

GRANT CHANGEFEED ON TABLE example_table TO user;

When you grant a user the CHANGEFEED privilege on a set of tables, they can create changefeeds on the target tables even if the user does not have the CONTROLCHANGEFEED role option or the SELECT privilege on the tables.

These users will be able to create changefeeds, but they will not be able to run a SELECT query on that data directly. However, they could still read this data indirectly if they have read access to the sink.

You can add CHANGEFEED to the user or role's default privileges with ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES GRANT CHANGEFEED ON TABLES TO user;

To restrict a user's access to changefeed data and sink credentials, enable the changefeed.permissions.require_external_connection_sink.enabled cluster setting. When you enable this setting, users with the CHANGEFEED privilege on a set of tables can only create changefeeds into external connections.

View and manage changefeed jobs

Users can view and manage changefeed jobs when one of the following are met:

• Job ownership: They own the job, or are a member of a role that owns a job.
  
• Global privileges: They are assigned CONTROLJOB or VIEWJOB.

To give a set of users access to a specific job, or set of jobs, assign them to a role that owns the job(s).

New in v25.1: You can transfer ownership of a job to a role or user using the ALTER JOB statement:

ALTER JOB job_ID OWNER TO role_name;

Synopsis

Parameters

Changefeed options

You can include the changefeed options listed on the CREATE CHANGEFEED page to modify the behavior of your changefeed. The following options are not compatible with scheduled changefeeds:

• diff
• end_time
• mvcc_timestamp
• resolved
• updated

Scheduled changefeeds have the initial_scan = 'only' option included implicitly. You cannot specify initial_scan as 'yes' or 'no'.

Schedule options

Examples

Before running any of the examples in this section, it is necessary to enable the kv.rangefeed.enabled cluster setting. If you are working on a CockroachDB Standard or Basic cluster, this cluster setting is enabled by default.

The Changefeed Sinks page provides detail on the available sinks for your change data messages and connection URIs. We recommend using external connections to interact with external sinks. The examples in this section use an external connection URI for the changefeed sink.

Create a scheduled changefeed

The following statement sets up a scheduled changefeed named users_rides_nightly that will send changefeed messages in CSV format 1 minute past midnight every night. As soon as the statement is run, the first changefeed run will execute immediately:

CREATE SCHEDULE users_rides_nightly FOR CHANGEFEED users, rides INTO 'external://kafka-sink' WITH format=csv RECURRING '1 0 * * *' WITH SCHEDULE OPTIONS first_run=now, on_execution_failure=retry, on_previous_running=skip;

The schedule options control the schedule's behavior:

• If it runs into an error, on_execution_failure=retry will ensure that the schedule retries the changefeed immediately.
• If the previous scheduled changefeed is still running, on_previous_running=skip will skip a new changefeed at the next scheduled time.

The output will confirm that the changefeed has added the initial_scan = 'only' option implicitly:

     schedule_id     |     label     | status |           first_run           | schedule  |                                                      changefeed_stmt
---------------------+---------------+--------+-------------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------
  947257854259855361 | users_nightly | ACTIVE | 2024-02-28 20:02:35.716699+00 | 1 0 * * * | CREATE CHANGEFEED FOR TABLE movr.public.users, TABLE movr.public.rides INTO 'external://kafka-sink' WITH OPTIONS (format = 'csv', initial_scan = 'only')
(1 row)

NOTICE: added missing initial_scan='only' option to schedule changefeed

Create a scheduled changefeed with CDC queries

You can use CDC queries with scheduled changefeeds to define expression syntax that selects columns and applies filters to further restrict or transform the data in your changefeed messages. When you add this expression syntax to your changefeed statement, you can only target one table.

For guidance on syntax and more example use cases, see Change Data Capture Queries.

This scheduled changefeed filters for the usage of promotion codes in the movr database and sends the changefeed messages on a daily basis:

CREATE SCHEDULE promo_code FOR CHANGEFEED INTO 'external://kafka-sink' AS SELECT user_id, usage_count FROM movr.user_promo_codes WHERE usage_count > 1 RECURRING '@daily' WITH SCHEDULE OPTIONS first_run=now, on_execution_failure=reschedule, on_previous_running=skip;

View scheduled changefeed details

To show all scheduled changefeeds:

SHOW SCHEDULES FOR CHANGEFEED;

To view the details of only running scheduled changefeeds:

SHOW RUNNING SCHEDULES FOR CHANGEFEED;

To view the details of only paused scheduled changefeeds:

SHOW PAUSED SCHEDULES FOR CHANGEFEED;

To view the details of a specific scheduled changefeed:

SHOW SCHEDULE {schedule ID};

To pause a scheduled changefeed:

PAUSE SCHEDULE {schedule ID};

To resume a scheduled changefeed:

RESUME SCHEDULE {schedule ID};

To delete a scheduled changefeed:

DROP SCHEDULE {schedule ID};

To see the full CREATE SCHEDULE statement for the scheduled changefeed:

SHOW CREATE SCHEDULE {schedule ID};

See also

• CREATE CHANGEFEED
• Change Data Capture Queries
• Export Data with Changefeeds
• Changefeed Sinks

Was this helpful?