ALTER SINK
ALTER SINK
allows cutting a sink over to a new upstream relation without
causing disruption to downstream consumers. This is useful in the context
of blue/green deployments.
Syntax
ALTER SINK <sink_name> SET FROM <relation_name>;
Details
Valid schema changes
For ALTER SINK
to be successful, the newly specified relation must lead to a
valid sink definition with the same conditions as the original CREATE SINK
statement.
When using the Avro format with a schema registry, the generated Avro
schema for the new relation must be compatible with the previously published
schema. If that’s not the case, the ALTER SINK
command will succeed, but the
subsequent execution of the sink will result in errors and will not be able to
make progress.
To monitor the status of a sink after an ALTER SINK
command, navigate to the
respective object page in the Materialize console,
or query the mz_internal.mz_sink_statuses
system catalog view.
Cutover timestamp
To alter the upstream relation a sink depends on while ensuring continuity in
data processing, Materialize must pick a consistent cutover timestamp. When you
execute an ALTER SINK
command, the resulting output will contain:
- all updates that happened before the cutover timestamp for the old relation, and
- all updates that happened after the cutover timestamp for the new relation.
ALTER
an
unhealthy sink that can’t make progress will result in the command timing out.
Cutover scenarios and workarounds
Because Materialize emits updates from the new relation only if they occur after the cutover timestamp, the following scenarios may occur:
Scenario 1: Topic contains stale value for a key
Since cutting over a sink to a new upstream relation using ALTER SINK
does not
emit a snapshot of the new relation, all keys will appear to have the old value
for the key in the previous relation until an update happens to them. At that
point, the current value will be published to the topic.
Consumers of the topic must be prepared to handle an old value for a key, for example by filling in additional columns with default values.
Workarounds:
-
Use an intermediary, temporary view to handle the cutover scenario difference. See Example: Handle cutover scenarios.
-
Alternatively, forcing an update to all the keys after
ALTER SINK
will force the sink to re-emit all the updates.
Scenario 2: Topic is missing a key that exists in the new relation
As a consequence of not re-emitting a snapshot after ALTER SINK
, if additional
keys exist in the new relation that are not present in the old one, these will
not be visible in the topic after the cutover. The keys will remain absent until
an update occurs for the keys, at which point Materialize will emit a record to
the topic containing the new value.
Workarounds:
-
Use an intermediary, temporary view to handle the cutover scenario difference. See Example: Handle cutover scenarios.
-
Alternatively, ensure that both the old and the new relations have identical keyspaces to avoid the scenario.
Scenario 3: Topic contains a key that does not exist in the new relation
Materialize does not compare the contents of the old relation with the new relation when cutting a sink over. This means that, if the old relation contains additional keys that are not present in the new one, these records will remain in the topic without a corresponding tombstone record. This may cause readers to assume that certain keys exist when they don’t.
Workarounds:
-
Use an intermediary, temporary view to handle the cutover scenario difference. See Example: Handle cutover scenarios.
-
Alternatively, ensure that both the old and the new relations have identical keyspaces to avoid the scenario.
Catalog objects
A sink cannot be created directly on a catalog object. As a workaround, you can create a materialized view on a catalog object and create a sink on the materialized view.
Privileges
The privileges required to execute this statement are:
- Ownership of the sink being altered.
SELECT
privileges on the new relation being written out to an external system.CREATE
privileges on the cluster maintaining the sink.USAGE
privileges on all connections and secrets used in the sink definition.USAGE
privileges on the schemas that all connections and secrets in the statement are contained in.
Examples
Alter sink
The following example alters a sink originally created from matview_old
to use
matview_new
instead.
That is, assume you have a Kafka sink avro_sink
created from matview_old
(See CREATE SINK
:Kafka/Redpanda for more
information):
CREATE SINK avro_sink
FROM matview_old
INTO KAFKA CONNECTION kafka_connection (TOPIC 'test_avro_topic')
KEY (key_col)
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION csr_connection
ENVELOPE UPSERT
;
To have the sink read from matview_new
instead of matview_old
, you can
use ALTER SINK
to change the FROM <relation>
:
matview_new
must be compatible with the previously published
schema. Otherwise, the ALTER SINK
command will succeed, but the
subsequent execution of the sink will result in errors and will not be able
to make progress. See Valid schema changes for
details.
ALTER SINK avro_sink
SET FROM matview_new
;
Because Materialize emits updates from the newly specified relation only if they happen after the cutover timestamp, you might observe the following scenarios:
- Topic contains stale value for a key
- Topic is missing a key that exists in the new relation
- Topic contains a key that does not exist in the new relation
For workaround, see Example: Handle cutover scenarios
Handle cutover scenarios
Because Materialize emits updates from the newly specified relation only if they happen after the cutover timestamp, you might observe the following scenarios:
- Topic contains stale value for a key
- Topic is missing a key that exists in the new relation
- Topic contains a key that does not exist in the new relation
To handle these scenarios, you can first alter sink to an intermediary
materialized view. The intermediary materialized view uses a temporary table
switch
that switches the view’s contents from old relation content to new
relation content. At the time of the switch, Materialize emits the diff of
the changes. Then, after the sink upper has advanced beyond the time of the
switch, you can ALTER SINK
to the new relation (and remove the temporary
intermediary materialized view and table).
-
For example, create a table
switch
and a temporary materialized viewtransition
that contains either:- the
matview_old
content ifswitch.value
isfalse
. - the
matview_new
content ifswitch.value
istrue
.
At first, the
switch.value
isfalse
, so thetransition
materialized view contains thematview_old
content.CREATE TABLE switch (value bool); INSERT INTO switch VALUES (false); -- controls whether we want the new or the old materialized view. CREATE MATERIALIZED VIEW transition AS (SELECT matview_old.* FROM matview_old JOIN switch ON switch.value = false) UNION ALL (SELECT matview_new.* FROM matview_new JOIN switch ON switch.value = true) ;
- the
-
ALTER SINK
to usetransition
, which currently containsmatview_old
content:ALTER SINK avro_sink SET FROM transition;
-
Update
switch.value
totrue
, which causes thetransition
materialized view to containmatview_new
content:UPDATE switch SET value = true;
-
Wait for the sink’s upper frontier
(
mz_frontiers
) to advance beyond the time of the switch update. Once advanced, alter sink to usematview_new
:-- After sink upper has advanced beyond the time of the switch UPDATE. ALTER SINK avro_sink SET FROM matview_new;
-
Drop the
transition
materialized view and theswitch
table:DROP MATERIALIZED VIEW transition; DROP TABLE switch;