Striim 4.0.4 documentation

Table of Contents

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:

  • 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 or Hortonworks schema registry.

Supported CDC sources

  • MySQL

  • Oracle Database 18c and earlier

  • Oracle GoldenGate 12.1 or later for Oracle Database only

  • PostgreSQL

  • SQL Server (with MSJet only)

Targets with the CDDL Action property

  • Azure Synapse

  • Google BigQuery

  • Google Cloud Spanner

  • MySQL (via Database Writer)

  • Oracle Database (via Database Writer)

  • PosgtgreSQL (via Database Writer)

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

  • Snowflake

  • SQL Server (via Database Writer)

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

Supported DDL operations:

  • CREATE TABLE

  • ALTER TABLE ... ADD COLUMN

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

  • 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).

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

    • not supported with BigQuery targets

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

    • 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 targets

    • supported with GoldenGate sources only when table has no primary key (known issues DEV-26575, DEV-26814)

    • not supported with SQL Server sources

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

    • not supported with BigQuery 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

    • supported with GoldenGate sources only when table has a primary key column and the constraint is not added to that column (known issues DEV-26575, DEV-26814)

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

  • ALTER TABLE ... DROP COLUMN:

    • If a ColumnMap is specified for the column (see Mapping columns), the application will halt.

    • not supported with BigQuery targets

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

  • DROP TABLE

Data type support and mappings

The supported data types for each source and target and the mappings from one to the other are the same as for the schema conversion utility.

Data type support and mapping tables are coming soon. Check docs.striim.com for updates.

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.

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

Notes and limitations

  • 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 crash.

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

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

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

Limitations

  • Renaming tables is not supported.

  • TRUNCATE TABLE is not supported. Use DELETE FROM <table name>; or some other method for deleting all rows from a table.

See also Schema evolution known issues.