ALTER CHANGEFEED

The ALTER CHANGEFEED statement modifies an existing . 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 a changefeed before running the ALTER CHANGEFEED statement against it. For an example of a changefeed modification using ALTER CHANGEFEED, see Modify a changefeed.

Required privileges

To alter a changefeed, the user must have one of the following:

• CHANGEFEED privilege on the table.
• admin role.
• CONTROLCHANGEFEED role option + SELECT on the table. ( Deprecated ) The CONTROLCHANGEFEED role option will be removed in a future release. We recommend using the system-level privilege .

For more details on the required privileges for changefeeds generally, refer to the page.

Synopsis

Parameters

Parameter	Description
job\_ID	Specify the changefeed job\_ID to modify.
WITH	Use ADD {tables} WITH initial\_scan = 'yes' to perform a scan when adding a target table or multiple target tables. The ALTER CHANGEFEED statement does not perform an initial scan by default, regardless of whether was set with the originalCREATE CHANGEFEED statement. It is also possible to explicitly state ADD {tables} WITH initial\_scan = 'no', although the default makes this unnecessary. See further details in the Options section.
ADD	Add a new target table to a changefeed. See the example.
DROP	Drop a target table from a changefeed. It is not possible to drop all target tables from a changefeed. See the example.
SET	Set new options on a changefeed. ALTER CHANGEFEED ... SET ... uses the options with some exceptions. See the example.
UNSET	Remove options that were set with the original CREATE CHANGEFEED statement with some exceptions. See the example.

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 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 options are compatible with SET/UNSET. This excludes the following options, which you cannot use in an ALTER CHANGEFEED statement:

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

• To use 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 = 'yes' SET updated UNSET resolved;
  

  Setting initial_scan = 'yes' will trigger an initial scan on the newly added table. You may also explicitly define initial_scan = 'no', 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.

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.

For more information on enabling changefeeds, see .

1. 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 , , and 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 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 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 .

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

Known limitations

• It is necessary to the changefeed before performing any statement.

• CockroachDB does not keep track of the option applied to tables when it is set to yes or only. For example:

  ALTER CHANGEFEED {job_ID} ADD table WITH initial_scan = 'yes';
  

  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.

• ALTER CHANGEFEED is not fully supported with changefeeds that use . You can alter the options that a changefeed uses, but you cannot alter the changefeed target tables.

See also