ALTER SOURCE
ALTER SOURCE changes certain characteristics of a source.
Syntax
alter_source_add_clause
alter_source_set_retain_history_clause
alter_source_reset_retain_history_clause
with_options
| Field | Use |
|---|---|
| name | The identifier of the source you want to alter. |
| ADD SUBSOURCE … | Add the identified tables from the upstream database (table_name) to the named PostgreSQL/MySQL/SQL Server source, with the option of choosing the name for the subsource in Materialize (subsrc_name). Supports additional options. When you add a new subsource to an existing source ( ALTER SOURCE ... ADD SUBSOURCE ...), Materialize starts the snapshotting
process for the new subsource. During this snapshotting, the data ingestion for
the existing subsources for the same source is temporarily blocked. As such, if
possible, you can resize the cluster to speed up the snapshotting process and
once the process finishes, resize the cluster for steady-state. |
| retention_period | Private preview. This option has known performance or stability issues and is under active development. Duration for which Materialize retains historical data, which is useful to implement durable subscriptions. Accepts positive interval values (e.g. '1hr'). Default: 1s. |
ADD SUBSOURCE with_options
| Field | Value | Description |
|---|---|---|
TEXT COLUMNS |
A list of names | Decode data as text for specific columns that contain PostgreSQL types that are unsupported in Materialize. |
Context
Adding subsources to a PostgreSQL/MySQL/SQL Server source
Note that using a combination of dropping and adding subsources lets you change the schema of the PostgreSQL/MySQL/SQL Server tables that are ingested.
ALTER SOURCE … ADD SUBSOURCE …), Materialize starts the snapshotting
process for the new subsource. During this snapshotting, the data ingestion for
the existing subsources for the same source is temporarily blocked. As such, if
possible, you can resize the cluster to speed up the snapshotting process and
once the process finishes, resize the cluster for steady-state.
Dropping subsources from a PostgreSQL/MySQL/SQL Server source
Dropping a subsource prevents Materialize from ingesting any data from it, in addition to dropping any state that Materialize previously had for the table (such as its contents).
If a subsource encounters a deterministic error, such as an incompatible schema change (e.g. dropping an ingested column), you can drop the subsource. If you want to ingest it with its new schema, you can then add it as a new subsource.
You cannot drop the “progress subsource”.
Examples
Adding subsources
ALTER SOURCE pg_src ADD SUBSOURCE tbl_a, tbl_b AS b WITH (TEXT COLUMNS [tbl_a.col]);
ALTER SOURCE … ADD SUBSOURCE …), Materialize starts the snapshotting
process for the new subsource. During this snapshotting, the data ingestion for
the existing subsources for the same source is temporarily blocked. As such, if
possible, you can resize the cluster to speed up the snapshotting process and
once the process finishes, resize the cluster for steady-state.
Dropping subsources
To drop a subsource, use the DROP SOURCE command:
DROP SOURCE tbl_a, b CASCADE;
Privileges
The privileges required to execute this statement are:
- Ownership of the source being altered.