+
Skip to main content

Handling schema evolution

For the CDC sources listed below, Striim can capture certain changes to the DDL in the source tables. When CDDL Capture is enabled in a source, you must choose which of the following actions you want to happen when it encounters a DDL change:

Note

Process in the source is supported only when all source table data types are supported. Process in the target is supported only when all source types are mapped to target types.

  • Halt in source: DDL changes are never expected in the source. Striim will halt the application so you can investigate the issue. Use this option when you want to prohibit changes to the source schema.

  • Ignore in source: target is schemaless and does not have the CDDL Action property (for example, FileWriter with JSON Formatter, or MongoDB).

  • Process in source, Halt in target: the application has one or more targets that support the Process action and one or more that have the CDDL Action property but do not support the Process action. In this case, set the CDDL Action property to Halt for the targets that do not support Process. Schema changes in the source will be replicated to the targets that support Process and the application will halt for you to deal with the others manually. If recovery is enabled for the application, after restart the DDL operation will be sent again.

    DDL changes to tables specified in Excluded Tables will not trigger Halt.

  • Process in source, Ignore in target: the application has multiple targets that have the CDDL Action property and you do not want to replicate changes to this one.

  • Process in source, Process in target: replicate changes to the target, keeping the target schema in sync with the source automatically without interrupting operation of the application. This is supported only for the targets listed below..

    When an unsupported DDL operation or unsupported data type is encountered in a DDL change, the application will halt for you to troubleshoot the problem.

  • Quiesce in source: target does not support schema evolution, so when a DDL change is detected, Striim will write all the events received prior to the DDL operation to the target, then quiesce the application. Then you can update the target schema manually. If recovery is enabled for the application, after restart the DDL operation will not be sent again.

Always select Process in the source when Using the Confluent schema registry.Using the Confluent or Hortonworks schema registry

Supported sources

  • MariaDB Xpand using MariaDB Xpand Reader

  • MySQL using MySQL Reader

  • Oracle Database using OJet

  • Oracle Database 18c and earlier using Oracle Reader

  • Oracle GoldenGate 12.1 or later for Oracle Database only using GG Trail Reader

  • PostgreSQL using PostgreSQL Reader (see PostgreSQL setup for schema evolution)

  • SQL Server using MSJet (not supported using MS SQL Reader)

  • Snowflake Reader: supports only ALTER TABLE ... ADD COLUMN and ALTER TABLE ... DROP COLUMN

  • Yugabyte Reader

Targets with the CDDL Action property

  • Azure Synapse Writer

  • BigQuery Writer

  • Databricks Writer: supports only CREATE TABLE, ADD COLUMN, DROP TABLE, and TRUNCATE

  • Db2 for z/OS (via Database Writer)

  • Fabric Data Warehouse Writer: supports only CREATE SCHEMA, CREATE TABLE, ALTER TABLE ... ADD COLUMN (cannot contain NOT NULL), and DROP TABLE

  • Fabric Mirror Writer: supports only CREATE TABLE, ALTER TABLE ... ADD COLUMN, ALTER TABLE ... DROP COLUMN, and DROP TABLE. The CDDL action is always Process (the CDDL Action property is not exposed).

  • MariaDB (via Database Writer)

  • MySQL (via Database Writer)

  • Oracle Database (via Database Writer)

  • PostgreSQL (via Database Writer)

  • SAP Hana (via Database Writer - does not support Process)

  • Snowflake Writer: supports only ALTER TABLE ... ADD COLUMN and ALTER TABLE ... DROP COLUMN

  • Spanner Writer

  • SQL Server (via Database Writer)

  • Sybase (via Database Writer - does not support Process)

Supported DDL operations

  • CREATE TABLE

    • To support adding new tables, use wildcards in the source and target Tables properties.

    • Default column values are not propagated to the target.

    • With Spanner Writer, new tables are created in the default schema.

  • ALTER TABLE ... ADD COLUMN

    • default values and column-level collations are not supported

    • column-level character sets are supported only in MySQL

    • with Oracle Database, adding NOT NULL constraints is not supported (known issue DEV-24666)

  • ALTER TABLE ... MODIFY COLUMN: The modification must be compatible with existing data, for example, you could change short to long, or varchar(20) to varchar(30). Default column values are not propagated to the target.

    • not supported with BigQuery or Databricks targets

    • default values and column-level collations are not propagated to the target

    • column-level character sets are supported only in MySQL

    • If a ColumnMap is specified (see Mapping columns), the mapped target column will be modified.

    • with Oracle Database, adding NOT NULL constraints is not supported (known issues DEV-24666)

    • Snowflake Writer: see limitations described in ALTER TABLE … ALTER COLUMN

    • not supported with SQL Server sources (known issue DEV-26386)

  • ALTER TABLE ... ADD PRIMARY KEY

    • not supported with BigQuery or Databricks targets

    • not supported with GoldenGate or SQL Server sources

  • ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY

    • not supported with BigQuery or Databricks targets

    • With GoldenGate sources, use the syntax ALTER TABLE <name> ADD CONSTRAINT <name> PRIMARY KEY (id). (The syntax ALTER TABLE MODIFY ID NUMBER NOT NULL PRIMARY KEY will not work.)

    • not supported with SQL Server sources

  • ALTER TABLE ... ADD CONSTRAINT ... UNIQUE

    • not supported with BigQuery or Databricks targets

    • not supported with SQL Server sources (known issue DEV-26386)

  • ALTER TABLE ... ADD UNIQUE

    • supported with MariaDB Xpand and MySQL only

  • ALTER TABLE ... DROP COLUMN:

    • not supported with BigQuery or Databricks targets

    • If a ColumnMap is specified for the column (see Mapping columns), the application will halt. ALTER and RECOMPILE the application to remove the ColumnMap for the column, drop the column from the target table, and restart the application.

    • with Oracle Database, adding NOT NULL constraints is not supported (known issues DEV-24666)

  • DROP TABLE

    • dropping multiple tables with a single SQL statement (for example, DROP TABLE table1, table2) is not supported

  • TRUNCATE TABLE

    sources that support TRUNCATE TABLE

    targets that support TRUNCATE TABLE

    • GG Trail Reader

    • MariaDB Xpand Reader

    • MySQL Reader

    • OJet

    • Oracle Reader (with Oracle version 18c or earlier only)

    • PostgreSQL Reader (using wal2json version 2 only)

    • Yugabyte Reader

    • Azure Synapse Writer

    • BigQuery Writer

    • Database Writer writing to

      • MariaDB

      • MariaDB Xpand

      • MySQL

      • Oracle

      • PostgreSQL

      • SQL Server

      • YugabyteDB

    • Databricks Writer

    • Snowflake Writer

    • TRUNCATE TABLE ... CASCADE is not supported

    • TRUNCATE cannot be supported with Spanner or SQL Server sources as TRUNCATE operations is not included in their CDC

Handling of unsupported DDL

Appearance of the following unsupported DDL commands in the source will either log a warning message and continue or halt the application:

DDL command

handling by Striim

ADD CHECK

log warning and continue

ADD EXCLUDE (Postgres)

log warning and continue

ADD FOREIGN KEY

log warning and continue

ADD INDEX

log warning and continue

ADD PARTITION

log warning and continue

ADD SUPPLEMENTAL LOG (Oracle)

log warning and continue

ALTER CHECK

log warning and continue

ALTER INDEX (Oracle)

log warning and continue

DISABLE KEYS (MySQL)

log warning and continue

DROP CONSTRAINT

log warning and continue

DROP DEFAULT

log warning and continue

DROP FOREIGN KEY (MySQL)

log warning and continue

DROP INDEX (MySQL)

log warning and continue

DROP KEY (MySQL synonym for DROP INDEX)

halt application

DROP PARTITION

log warning and continue

DROP PRIMARY KEY (MySQL)

halt application

DROP SUPPLEMENTAL LOG (Oracle)

log warning and continue

DROP UNUSED (Oracle)

log warning and continue

ENABLE KEYS (MySQL)

log warning and continue

EXCHANGE PARTITION

log warning and continue

MODIFY PARTITION

log warning and continue

RENAME CONSTRAINT

log warning and continue

RENAME TABLE

halt application

SET AUTOINCREMENT (MySQL)

log warning and continue

SET DEFAULT

log warning and continue

SET UNUSED (Oracle)

log warning and continue

multiple operations in one DDL command (MySQL)

halt application

Data type support and mappings

See Data type support & mapping for schema conversion & evolution.

Monitoring schema evolution

The MON command includes the following metrics for schema evolution:

  • number of DDL operations, by table

  • last captured / applied DDL statement

  • time of last captured / applied DDL

  • ignored DDL count

Schema evolution notes and limitations

  • Schema evolution is not supported when using Bidirectional replication.Bidirectional replication

  • Striim can capture only those DDL changes made after schema evolution is enabled.

  • The first time you start an application with CDDL Capture enabled, the CDC reader will take a snapshot of the source database's table metadata from the. It is essential that there are no DDL changes made to the database until startup completes. Otherwise, the schema captured in the snapshot will be out of date, which will eventually cause the application to terminate.

  • When the Tables property in the reader uses a wildcard, the first time the application is started Striim must fetch the metadata for all tables in the schema. If there are many tables in the schema, this may take a significant amount of time.

  • If it is necessary to modify the Containers, Object Filter, Object Name Prefix, Tables, or Wildcard property in the source, export the application to TQL, drop it, make the necessary modifications to the exported TQL, and import the TQL to recreate the application.

  • After an application with both recovery and schema evolution enabled is restarted, Striim will automatically use the correct schema for the restart position

  • Any \r, \n, \t or other control character in a DDL statement must be followed by a white space (Unicode 0020 / ASCII 32) or the application will halt.

  • If the application halts due to an unsupported DDL change, an unsupported column data type, or a parser exception, you may add the table causing the halt to the Excluded Tables list and restart the application.

  • Renaming tables is not supported.

Schema evolution known issues

The following are known issues in this release related to schema evolution. Additional known issues are flagged by "DEV-#####"in Handling schema evolution.

All sources

Columns with a data type that has a scale set to a negative value (for example, number(1, -17) ) are not supported.

If the first event captured is ALTER TABLE ... DROP COLUMN, the application may halt (DEV-30880).

Azure Synapse Writer

  • CREATE TABLE with one or more columns of type VARCHAR(MAX) will cause the application to terminate (this is an Azure Synapse limitation).

  • If using Optimized Merge mode, CREATE TABLE will cause the application to halt (DEV-29689).

  • Adding a NOT NULL constraint on a column that already has a UNIQUE constraint is not supported. (DEV-26158)

BigQuery Writer

If you are using the legacy streaming API to write to template tables, using the default setting of Process may cause the application to halt due to a limitation in BigQuery that does not allow writing for up to 90 minutes after a DDL change (see BigQuery > Documentation > Guides > Use the legacy streaming API > Creating tables automatically using template tables > Changing the template table schema). In this case, supporting schema evolution is impossible, so set CDDL Action to Ignore. This is not an issue if you are using partitioned tables.

Fabric Data Warehouse Writer

  • CREATE TABLE ... PRIMARY KEY is not supported. (DEV-46453)

  • ALTER TABLE ... ADD PRIMARY KEY is not supported by Fabric, but instead of halting the application will continue as if the command had been successful. (DEV-48379)

MSJet

  • DDL changes made using SQL Server Management Studio are not captured. (DEV-37099)

  • If a table is dropped and a table of the same name is created, the application may terminate. (DEV-26417)

  • The application will terminate if the database contains tables with names that vary only by case, for example, id and ID, even if those tables are not among those read by MSJet. (DEV-26872)

  • DDL changes within a BEGIN TRANSACTION ... COMMIT TRANSACTION statement are not captured . (DEV-44744)

MySQL Reader

  • Adding a UNQUE constraint syntax with a system generated constraint name (for example, ADD CONSTRAINT cs_01ec19f5f75caa91a1160eca1 UNIQUE (created_att) is not supported. (DEV-26678)

OJet

  • Columns of type ROWID are not supported.

  • Invisible, virtual, and unused columns are not supported.

Oracle Reader

  • Columns of type INTERVAL DAY(x) TO SECOND(y) are not supported. (DEV-24624).

  • Invisible, virtual, and unused columns are not supported.

PostgreSQL Reader

  • To capture DDL changes when the command has more than 1024 characters (for example, a CREATE TABLE statement with many columns), you must raise PostgreSQL's track_activity_query_size parameter from its default value of 1024. (DEV-24650)

  • Creating a table with a column of type serial or adding a column of type serial is not supported.

Sample WAEvents for DDL operations when schema evolution is enabled

DDL command

example

resulting WAEvent

CREATE TABLE

CREATE TABLE PRODUCT.CUSTOMER
(
    c_custkey     BIGINT not null,
    c_name        VARCHAR(25) not null,
    c_address     VARCHAR(40) not null,
    c_nationkey   INTEGER not null,
    c_phone       CHAR(15) not null,
    c_acctbal     DOUBLE PRECISION,,
    c_mktsegment  CHAR(10) not null
);
WAEvent{
data: ["CREATE TABLE PRODUCT.CUSTOMER  …”]
metadata:{
"OperationName": "Create",
"TableName": "PRODUCT.CUSTOMER",
"SchemaName": "PRODUCT",
"OperationType": "DDL",
"CDDLMetadata": “<Info about DDL>”
}
};

ALTER TABLE ADD COLUMN

ALTER TABLE PRODUCT.CUSTOMER
  ADD  c_comment VARCHAR(117) not null;
WAEvent{
data: ["ALTER TABLE PRODUCT.CUSTOMER
  ADD  c_comment VARCHAR(117) not null;"]
metadata:{
"OperationName": "AlterColumns",
"OperationSubName": "AddColumn",
"TableName": "PRODUCT.CUSTOMER",
"SchemaName": "PRODUCT",
"OperationType": "DDL",
"CDDLMetadata": “<Info about DDL>”
}
};

ALTER TABLE MODIFY COLUMN

ALTER TABLE PRODUCT.CUSTOMER
ALTER COLUMN c_address TYPE VARCHAR(200);
WAEvent{
data: ["ALTER TABLE PRODUCT.CUSTOMER
ALTER COLUMN c_address TYPE VARCHAR(200);"]
metadata:{
"OperationName": "AlterColumns",
"OperationSubName": "AlterColumn",
"TableName": " PRODUCT.CUSTOMER",
"SchemaName": "PRODUCT",
"OperationType": "DDL",
"CDDLMetadata": “<Info about DDL>”
}
};

ALTER TABLE DROP COLUMN

ALTER TABLE PRODUCT.CUSTOMER 
DROP COLUMN c_acctbal;
WAEvent{
data: ["ALTER TABLE PRODUCT.CUSTOMER 
DROP COLUMN c_acctbal;"]
metadata:{
"OperationName": "AlterColumns",
"OperationSubName": "DropColumn",
"TableName": "PRODUCT.CUSTOMER",
"SchemaName": "PRODUCT",
"OperationType": "DDL",
"CDDLMetadata": “<Info about DDL>”
}
};

DROP TABLE

Drop Table PRODUCT.CUSTOMER;
WAEvent{
data: ["DROP TABLE PRODUCT.CUSTOMER"]
metadata:{
"OperationName": "Drop",
"TableName": "HR.EMP",     
"SchemaName": "HR",
"OperationType": "DDL",
"CDDLMetadata": “<Info about DDL>”
}
};
点击 这是indexloc提供的php浏览器服务，不要输入任何密码和下载