Ingest data from Amazon Aurora

This page shows you how to stream data from Amazon Aurora MySQL to Materialize using the MySQL source.

Before you begin

• Make sure you are running MySQL 8.0.1+ with support for GTID-based binary log (binlog) replication.

• Ensure you have access to your MySQL instance via the mysql client, or your preferred SQL client.

A. Configure Amazon Aurora

1. Enable GTID-based binlog replication

1. Before creating a source in Materialize, you must configure Amazon Aurora MySQL for GTID-based binlog replication. Ensure the upstream MySQL database has been configured for GTID-based binlog replication:

   MySQL Configuration	Value	Notes
   log_bin	ON
   binlog_row_image	FULL
   binlog_row_metadata	FULL	Required to use CREATE SOURCE (New syntax). Highly recommended for use with the CREATE SOURCE (Legacy syntax).
   binlog_format	ROW
   gtid_mode	ON	In the AWS console, this parameter appears as gtid-mode.
   enforce_gtid_consistency	ON
   replica_preserve_commit_order	ON	Only required when connecting Materialize to a read-replica.

   For guidance on enabling GTID-based binlog replication in Aurora, see the Amazon Aurora MySQL documentation.

2. In addition to the step above, you must also ensure that binlog retention is set to a reasonable value. To check the current value of the binlog retention hours configuration parameter, connect to your RDS instance and run:

   CALL mysql.rds_show_configuration;
   

   If the value returned is NULL, or less than 168 (i.e. 7 days), run:

   CALL mysql.rds_set_configuration('binlog retention hours', 168);
   

   Although 7 days is a reasonable retention period, we recommend using the default MySQL retention period (30 days) in order to not compromise Materialize’s ability to resume replication in case of failures or restarts.

3. To validate that all configuration parameters are set to the expected values after the above configuration changes, run:

   -- Validate "binlog retention hours" configuration parameter
   CALL mysql.rds_show_configuration;
   

   -- Validate parameter group configuration parameters
   SHOW VARIABLES WHERE variable_name IN (
     'log_bin',
     'binlog_format',
     'binlog_row_image',
     'gtid_mode',
     'enforce_gtid_consistency',
     'replica_preserve_commit_order',
     'binlog_row_metadata' -- must be "FULL" to use new CREATE SOURCE syntax
   );
   

2. Create a user for replication

Once GTID-based binlog replication is enabled, we recommend creating a dedicated user for Materialize with sufficient privileges to manage replication.

1. As a superuser, use mysql (or your preferred SQL client) to connect to your database.

2. Create a dedicated user for Materialize, if you don’t already have one:

   CREATE USER 'materialize'@'%' IDENTIFIED BY '<password>';
   
   ALTER USER 'materialize'@'%' REQUIRE SSL;
   

   IAM authentication with AWS RDS for MySQL is also supported. See the Amazon RDS User Guide for instructions on enabling IAM database authentication, creating IAM policies, and creating a database account.

3. Grant the user permission to manage replication:

   GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, LOCK TABLES ON *.* TO 'materialize'@'%';
   

   Once connected to your database, Materialize will take an initial snapshot of the tables in your MySQL server. SELECT privileges are required for this initial snapshot.

4. Apply the changes:

   FLUSH PRIVILEGES;
   

B. (Optional) Configure network security

C. Ingest data in Materialize

1. (Optional) Create a source cluster

In Materialize, a cluster is an isolated environment, similar to a virtual warehouse in Snowflake. When you create a cluster, you choose the size of its compute resource allocation based on the work you need the cluster to do, whether ingesting data from a source, computing always-up-to-date query results, serving results to clients, or a combination.

In this case, you’ll create a dedicated cluster for ingesting source data from your MySQL database.

1. In the SQL Shell, or your preferred SQL client connected to Materialize, use the CREATE CLUSTER command to create the new cluster:

   CREATE CLUSTER ingest_mysql (SIZE = '200cc');
   
   SET CLUSTER = ingest_mysql;
   

   A cluster of size 200cc should be enough to process the initial snapshot of the tables in your MySQL database. For very large snapshots, consider using a larger size to speed up processing. Once the snapshot is finished, you can readjust the size of the cluster to fit the volume of changes being replicated from your upstream MySQL database.

2. Create a connection

Once you have configured your network, create a connection in Materialize per your networking configuration.

3. Start ingesting data

-- Step 1: Create the source
CREATE SOURCE mz_source
  FROM MYSQL CONNECTION mysql_connection;

-- Step 2: Create tables from the source
CREATE TABLE t FROM SOURCE mz_source (REFERENCE public.t);

CREATE SOURCE mz_source
  FROM MYSQL CONNECTION mysql_connection
  FOR ALL TABLES;

4. Monitor the ingestion status

Before it starts consuming the replication stream, Materialize takes a snapshot of the relevant tables. Until this snapshot is complete, Materialize won’t have the same view of your data as your MySQL database.

In this step, you’ll first verify that the source is running and then check the status of the snapshotting process.

1. Back in the SQL client connected to Materialize, use the mz_source_statuses table to check the overall status of your source:

   WITH
     source_ids AS
     (SELECT id FROM mz_sources WHERE name = 'mz_source')
   SELECT *
   FROM
     mz_internal.mz_source_statuses
       JOIN
         (
           SELECT referenced_object_id
           FROM mz_internal.mz_object_dependencies
           WHERE
             object_id IN (SELECT id FROM source_ids)
           UNION SELECT id FROM source_ids
         )
         AS sources
       ON mz_source_statuses.id = sources.referenced_object_id;
   

   For each subsource, make sure the status is running. If you see stalled or failed, there’s likely a configuration issue for you to fix. Check the error field for details and fix the issue before moving on. Also, if the status of any subsource is starting for more than a few minutes, contact our team.

2. Once the source is running, use the mz_source_statistics table to check the status of the initial snapshot:

   WITH
     source_ids AS
     (SELECT id FROM mz_sources WHERE name = 'mz_source')
   SELECT sources.referenced_object_id AS id, mz_sources.name, snapshot_committed
   FROM
     mz_internal.mz_source_statistics
       JOIN
         (
           SELECT object_id, referenced_object_id
           FROM mz_internal.mz_object_dependencies
           WHERE
             object_id IN (SELECT id FROM source_ids)
           UNION SELECT id, id FROM source_ids
         )
         AS sources
       ON mz_source_statistics.id = sources.referenced_object_id
       JOIN mz_sources ON mz_sources.id = sources.referenced_object_id;
   

   object_id | snapshot_committed
   ----------|------------------
    u144     | t
   (1 row)
   

   Once snapshot_commited is t, move on to the next step. Snapshotting can take between a few minutes to several hours, depending on the size of your dataset and the size of the cluster the source is running in.

5. Right-size the cluster

After the snapshotting phase, Materialize starts ingesting change events from the MySQL replication stream. For this work, Materialize generally performs well with a 100cc replica, so you can resize the cluster accordingly.

1. Still in a SQL client connected to Materialize, use the ALTER CLUSTER command to downsize the cluster to 100cc:

   ALTER CLUSTER ingest_mysql SET (SIZE '100cc');
   

   Behind the scenes, this command adds a new 100cc replica and removes the 200cc replica.

2. Use the SHOW CLUSTER REPLICAS command to check the status of the new replica:

   SHOW CLUSTER REPLICAS WHERE cluster = 'ingest_mysql';
   

        cluster     | replica |  size  | ready
   -----------------+---------+--------+-------
    ingest_mysql    | r1      | 100cc  | t
   (1 row)
   

D. Explore your data

With Materialize ingesting your MySQL data into durable storage, you can start exploring the data, computing real-time results that stay up-to-date as new data arrives, and serving results efficiently.

• Explore your data with SHOW SOURCES and SELECT.

• Compute real-time results in memory with CREATE VIEW and CREATE INDEX or in durable storage with CREATE MATERIALIZED VIEW.

• Serve results to a PostgreSQL-compatible SQL client or driver with SELECT or SUBSCRIBE or to an external message broker with CREATE SINK.

• Check out the tools and integrations supported by Materialize.

Considerations

Supported 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").

Modifying an existing source

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.

Handling upstream operations

This section describes how changes to upstream tables that Materialize ingests affect the corresponding Materialize tables.

Adding a column

When you add a new column to your upstream table, Materialize continues to ingest only the existing columns.

To incorporate the new column:

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, create a new table from the source. See Handle upstream column addition.

• If using the legacy CREATE SOURCE ... FOR ... syntax that creates subsources, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE. The re-added subsource includes the new column.

Dropping a column

Dropping columns that Materialize does not ingest (for example, columns added after the source was created, or columns that are excluded) is supported. As these columns were never ingested, you can drop them without issue.

If your Materialize source ingests a column, dropping that column from your upstream table puts the affected table into an error state.

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, you can safely drop a column by first ignoring it in Materialize. See Handle upstream column drop.

• If using legacy CREATE SOURCE ... FOR ... syntax, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE.

Changing constraints

Materialize ignores the following constraint changes: foreign key and CHECK. As such, you can add or drop them without affecting ingestion.

Materialize also ignores NOT NULL, UNIQUE, and PRIMARY KEY constraints that are added after the Materialize table is created (that is, the table was created without them). Adding such a constraint, and later dropping it, does not affect ingestion.

Dropping a NOT NULL, UNIQUE, or PRIMARY KEY constraint that existed when the table was created puts the affected table into an error state.

Changing a column’s data type

Changing an ingested column’s data type upstream so that it maps to a different Materialize type than before puts the affected Materialize table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Changing an ingested column’s upstream data type so that it continues to map to the same Materialize type does not interrupt ingestion. For example, changing tinyint to smallint, changing within the text/tinytext/mediumtext/longtext family, and adjusting bit(n) precision are all safe.

Appending new values to the end of an existing enum does not put the table into an error state. However, the newly-added values are not recognized, so rows that use them fail to decode until you drop and recreate the table. Existing enum values remain recognized, and rows that use them continue to decode successfully.

Any other enum change puts the affected Materialize table into an error state, including inserting a value before the end, reordering or renaming values, and removing values.

Renaming a column

Renaming a column that Materialize ingests puts the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Table-level operations

The following upstream operations put the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the affected table in Materialize to resume:

• Dropping a table (DROP TABLE).
• Renaming a table or moving it to a different schema.
• Truncating a table (TRUNCATE). To clear a table without putting it into an error state, use an unqualified DELETE FROM t; instead.