ALTER CHANGEFEED

On this page

New in v22.1: The ALTER CHANGEFEED statement modifies an existing changefeed. You can use ALTER CHANGEFEED to do the following:

• Add new target tables to a changefeed.
• Remove target tables from a changefeed.
• Set new options on a changefeed.
• Remove existing options from a changefeed.

The statement will return a job ID and the new job description.

It is necessary to pause a changefeed before running the ALTER CHANGEFEED statement against it. For an example of a changefeed modification using ALTER CHANGEFEED, see Modify a changefeed.

Synopsis

Parameters

When the listed parameters are used together in the same statement, all changes will apply at the same time with no particular order of operations.

Options

Consider the following when specifying options with ALTER CHANGEFEED:

• You can set a different sink URI for an existing changefeed with the sink option. It is not possible to change the sink type. For example, you can use SET sink = 'gs://{BUCKET NAME}?AUTH=IMPLICIT' to use a different Google Cloud Storage bucket. However, you cannot use the sink option to move to Amazon S3 (s3://) or Kafka (kafka://). See the Set options on a changefeed example.

• The majority of CREATE CHANGEFEED options are compatible with SET/UNSET. This excludes the following options, which you cannot use in an ALTER CHANGEFEED statement:

  • cursor
  • end_time
  • full_table_name: This option will not apply to existing tables. To use the fully qualified table name, it is necessary to create a new changefeed.
  • initial_scan_only

• To use initial_scan with ALTER CHANGEFEED, it is necessary to define a WITH clause when running ADD. This will set these options on the specific table(s):

  ALTER CHANGEFEED {job ID} ADD movr.rides, movr.vehicles WITH initial_scan SET updated UNSET resolved;
  

  Setting initial_scan will trigger an initial scan on the newly added table. You may also explicitly define no_initial_scan, though this is already the default behavior. The changefeed does not track the application of this option post scan. This means that you will not see the option listed in output or after a SHOW CHANGEFEED JOB statement.

Required privileges

To alter a changefeed, the user must be a member of the admin role or have the CREATECHANGEFEED parameter set.

Examples

Modify a changefeed

To use the ALTER CHANGEFEED statement to modify a changefeed, it is necessary to first pause the running changefeed. The following example demonstrates creating a changefeed, pausing the changefeed, modifying it, and then resuming the changefeed.

1. First, create the changefeed. This example changefeed will emit change messages to a cloud storage sink on two watched tables. The emitted messages will include the resolved, updated, and schema_change_policy options:

   CREATE CHANGEFEED FOR TABLE movr.users, movr.vehicles INTO 's3://{BUCKET_NAME}?AWS_ACCESS_KEY_ID={ACCESS_KEY_ID}&AWS_SECRET_ACCESS_KEY={SECRET_ACCESS_KEY}'
       WITH resolved, updated, schema_change_policy = backfill;
   

          job_id
   ----------------------
   745448689649516545
   (1 row)
   

2. Use SHOW CHANGEFEED JOB with the job_ID to view the details of a changefeed:

   SHOW CHANGEFEED JOB 745448689649516545;
   

   job_id             |                                                                                                          description                                                                                       | user_name | status  |              running_status              |          created          |          started           | finished |          modified          |      high_water_timestamp      | error |                                                sink_uri                                                |             full_table_names             | topics | format
   -------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+------------------------------------------+---------------------------+----------------------------+----------+----------------------------+--------------------------------+-------+--------------------------------------------------------------------------------------------------------+------------------------------------------+--------+---------
   745448689649516545 | CREATE CHANGEFEED FOR TABLE movr.users, movr.vehicles INTO 's3://{BUCKET_NAME}?AWS_ACCESS_KEY_ID={ACCESS_KEY_ID}&AWS_SECRET_ACCESS_KEY=redacted' WITH resolved, schema_change_policy = 'backfill', updated | root      | running | running: resolved=1647563286.239010012,0 | 2022-03-18 00:28:06.24559 | 2022-03-18 00:28:06.276709 | NULL     | 2022-03-18 00:28:37.250323 | 1647563313622679573.0000000000 |       | s3://{BUCKET_NAME}?AWS_ACCESS_KEY_ID={ACCESS_KEY_ID}&AWS_SECRET_ACCESS_KEY=redacted                    | {movr.public.vehicles,movr.public.users} | NULL   | json
   (1 row)
   

   To output a list of all changefeeds on the cluster, run the following:

   SHOW CHANGEFEED JOBS;
   

3. In preparation for modifying the created changefeed, use PAUSE JOB:

   PAUSE JOB 745448689649516545;
   

4. With the changefeed paused, run the ALTER CHANGEFEED statement with ADD, DROP, SET, or UNSET to change the target tables or options:

   ALTER CHANGEFEED 745448689649516545 DROP movr.vehicles UNSET resolved SET diff;
   

   job_id             |                                                                     job_description
   -------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   745448689649516545 | CREATE CHANGEFEED FOR TABLE movr.public.users INTO 's3://{BUCKET_NAME}?AWS_ACCESS_KEY_ID={ACCESS_KEY_ID}&AWS_SECRET_ACCESS_KEY=redacted' WITH diff, schema_change_policy = 'backfill', updated
   (1 row)
   

   The output from ALTER CHANGEFEED will show the CREATE CHANGEFEED statement with the options you've defined. After modifying a changefeed with ALTER CHANGEFEED, the CREATE description will show the fully qualified table name.

   For an explanation on each of these options, see the CREATE CHANGEFEED options.

5. Resume the changefeed job with RESUME JOB:

   RESUME JOB 745448689649516545;
   

Add targets to a changefeed

The following statement adds the vehicles and rides tables as new table targets to the changefeed:

 ALTER CHANGEFEED {job_ID} ADD movr.rides, movr.vehicles;

To add a table that has column families, see the example.

Drop targets from a changefeed

The following statement removes the rides table from the changefeed's table targets:

 ALTER CHANGEFEED {job_ID} DROP movr.rides;

Set options on a changefeed

Use SET to add a new option(s) to a changefeed:

ALTER CHANGEFEED {job_ID} SET resolved='10s', envelope=key_only;

ALTER CHANGEFEED ... SET can implement the CREATE CHANGEFEED options with some exceptions.

Use the sink option to change the sink URI to which the changefeed emits messages:

ALTER CHANGEFEED {job_ID}
    SET sink = 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={ACCESS_KEY_ID}&AWS_SECRET_ACCESS_KEY={SECRET_ACCESS_KEY}'
    UNSET resolved;

The type (or scheme) of the sink cannot change. That is, if the changefeed was originally sending messages to kafka://, for example, then you can only change to a different Kafka URI. Similarly, for cloud storage sinks, the cloud storage scheme must remain the same (e.g., s3://), but you can change to a different storage sink on the same cloud provider.

To change the sink type, create a new changefeed.

Unset options on a changefeed

To remove options from a changefeed, use UNSET:

ALTER CHANGEFEED {job_ID} UNSET resolved, diff;

Modify a changefeed targeting tables with column families

To add a table with column families when modifying a changefeed, perform one of the following:

• Use the FAMILY keyword to define specific families:

  ALTER CHANGEFEED {job_ID} ADD database.table FAMILY f1, database.table FAMILY f2;
  

• Or, set the split_column_families option:

  ALTER CHANGEFEED {job_ID} ADD database.table SET split_column_families;
  

To remove a table with column families as a target from the changefeed, you must DROP it in the same way that you added it originally as a changefeed target. For example:

• If you used FAMILY to add the table to the changefeed, use FAMILY when removing it:

  ALTER CHANGEFEED {job_ID} DROP database.table FAMILY f1, database.table FAMILY f2;
  

  When using the FAMILY keyword, it is possible to remove only one family at a time as needed. You will receive an error if you try to remove a table without specifying the FAMILY keyword.

• Or, if you originally added the whole table and its column families with split_column_families, then remove it without using the FAMILY keyword:

  ALTER CHANGEFEED {job_ID} DROP database.table;
  

For further discussion on using the FAMILY keyword and split_column_families, see Tables with column families in changefeeds.

Known limitations

• It is necessary to PAUSE the changefeed before performing any ALTER CHANGEFEED statement. Tracking GitHub Issue
• ALTER CHANGEFEED will accept duplicate targets without sending an error. Tracking GitHub Issue

• CockroachDB does not keep track of the initial_scan or initial_scan_only options applied to tables. For example:

  ALTER CHANGEFEED {job_ID} ADD table WITH initial_scan;
  

  This will trigger an initial scan of the table and the changefeed will track table. The changefeed will not track initial_scan specified as an option, so it will not display in the output or after a SHOW CHANGEFEED JOB statement.

• You cannot use the new initial_scan = "yes"/"no"/"only" syntax with ALTER CHANGEFEED in v22.1. To ensure that you can modify a changefeed with the initial_scan options, use the previous syntax of initial_scan, no_initial_scan, and initial_scan_only.

See also

• Change Data Capture Overview
• CREATE CHANGEFEED
• Create and Configure Changefeeds
• Changefeed Sinks
• SHOW JOBS

Was this helpful?