CREATE TABLE: MySQL source table

In Materialize, you can create read-only tables from MySQL sources created using the new syntax.

Syntax

To create a read-only table from a source connected (via native connector) to an external MySQL database:

CREATE TABLE [IF NOT EXISTS] <table_name> FROM SOURCE <source_name> (REFERENCE <upstream_schema>.<upstream_table>)
[WITH (
    TEXT COLUMNS (<column_name> [, ...])
  | EXCLUDE COLUMNS (<column_name> [, ...])
  | PARTITION BY (<column_name> [, ...])
  [, ...]
)]
;

SELECT refs.*
FROM mz_internal.mz_source_references refs, mz_sources s
WHERE s.name = '<source_name>' -- substitute with your source name
AND refs.source_id = s.id;

Details

DDL transaction block

For performance, when issuing multiple CREATE TABLE FROM SOURCE... statements, use within a transaction block.

Source-populated tables and snapshotting

Creating the tables from sources starts the snapshotting process. Snapshotting syncs the currently available data into Materialize. Because the initial snapshot is persisted in the storage layer atomically (i.e., at the same ingestion timestamp), you are not able to query the table until snapshotting is complete.

Supported data types

Materialize natively supports the following MySQL types:

• bigint
• binary
• bit
• blob
• boolean
• char
• date
• datetime
• decimal
• double
• float
• int
• json
• longblob
• longtext
• mediumblob
• mediumint
• mediumtext
• numeric
• real
• smallint
• text
• time
• timestamp
• tinyblob
• tinyint
• tinytext
• varbinary
• varchar

When replicating tables that contain the unsupported data types, you can:

• Use TEXT COLUMNS option for the following unsupported MySQL types:

  • enum
  • year

  The specified columns will be treated as text and will not offer the expected MySQL type features.

• Use the EXCLUDE COLUMNS option to exclude any columns that contain unsupported data types.

Zero values for date, datetime, and timestamp

MySQL allows the special “zero” values 0000-00-00, 0000-00-00 00:00:00 in date, datetime, and timestamp columns when the server sql_mode does not include NO_ZERO_DATE or NO_ZERO_IN_DATE. These values are not representable in Materialize’s corresponding native types, so they will cause ingestion to fail for the affected column.

To ingest columns that contain zero values, use TEXT COLUMNS to decode the affected columns as text. The zero values for date, datetime, timestamp, and year are preserved verbatim as strings (e.g. "0000-00-00 00:00:00", "0000").

Handling table schema changes

The use of CREATE SOURCE (new syntax) with CREATE TABLE FROM SOURCE allows for the handling of the upstream DDL changes, specifically adding or dropping columns in the upstream tables, without downtime. For details, see MySQL: Handling upstream schema changes with zero downtime.

Privileges

The privileges required to execute this statement are:

• CREATE privileges on the containing schema.
• USAGE privileges on all types used in the table definition.
• USAGE privileges on the schemas that all types in the statement are contained in.

Examples

Create a table

To create new read-only tables from a source table, use the CREATE TABLE ... FROM SOURCE ... (REFERENCE <upstream_schema>.<upstream_table>) statement in a DDL transaction block. The following example creates read-only tables items and orders from the MySQL source’s mydb.items and mydb.orders tables.

/* This example assumes:
  - In the upstream MySQL, you have configured:
    - GTID-based binary log replication.
    - `binlog_row_metadata = FULL`.
    - A replication user and password with the appropriate access.
  - In Materialize:
    - You have created a secret for the MySQL password.
    - You have defined the connection to the upstream MySQL.
    - You have used the connection to create a source.

   For example (substitute with your configuration):
      CREATE SECRET mysqlpass AS '<replication user password>'; -- substitute
      CREATE CONNECTION mysql_connection TO MYSQL (
        HOST '<hostname>',          -- substitute
        PORT 3306,
        USER <replication user>,    -- substitute
        PASSWORD SECRET mysqlpass
        -- [, <network security configuration> ]
      );

      CREATE SOURCE mysql_source
      FROM MYSQL CONNECTION mysql_connection;
*/

BEGIN;
CREATE TABLE items
FROM SOURCE mysql_source (REFERENCE mydb.items)
;
CREATE TABLE orders
FROM SOURCE mysql_source (REFERENCE mydb.orders)
;
COMMIT;

Creating the tables from sources starts the snapshotting process. Snapshotting syncs the currently available data into Materialize. Because the initial snapshot is persisted in the storage layer atomically (i.e., at the same ingestion timestamp), you are not able to query the table until snapshotting is complete.

Related pages

• CREATE SOURCE: MySQL (New Syntax)
• DROP TABLE