This is an enterprise-only feature. Request a 30-day trial license to try it out.
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
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 initial_scan = 'yes' was set with the original CREATE 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 CREATE CHANGEFEED 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 sink URI for an existing changefeed with the
sink
option. It is not possible to change the sink type. For example, you can useSET sink = 'gs://{BUCKET NAME}?AUTH=IMPLICIT'
to use a different Google Cloud Storage bucket. However, you cannot use thesink
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 withSET
/UNSET
. This excludes the following options, which you cannot use in anALTER 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
withALTER CHANGEFEED
, it is necessary to define aWITH
clause when runningADD
. 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 defineinitial_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 aSHOW 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.
For more information on enabling changefeeds, see Create and Configure Changefeeds.
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
, andschema_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)
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;
In preparation for modifying the created changefeed, use
PAUSE JOB
:PAUSE JOB 745448689649516545;
With the changefeed paused, run the
ALTER CHANGEFEED
statement withADD
,DROP
,SET
, orUNSET
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 theCREATE CHANGEFEED
statement with the options you've defined. After modifying a changefeed withALTER CHANGEFEED
, theCREATE
description will show the fully qualified table name.For an explanation on each of these options, see the
CREATE CHANGEFEED
options.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, useFAMILY
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 theFAMILY
keyword.Or, if you originally added the whole table and its column families with
split_column_families
, then remove it without using theFAMILY
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 anyALTER CHANGEFEED
statement. Tracking GitHub Issue ALTER CHANGEFEED
will accept duplicate targets without sending an error. Tracking GitHub Issue- You cannot alter a changefeed that uses CDC transformations. Tracking GitHub issue
CockroachDB does not keep track of the
initial_scan
option applied to tables when it is set toyes
oronly
. 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 trackinitial_scan
specified as an option, so it will not display in the output or after aSHOW CHANGEFEED JOB
statement.