Viewing MySQL data in Snowflake

Note

The Snowflake Connector for MySQL is subject to the Connector Terms.

Connector replicates data to destination database that was defined while setting up the connector and calling PUBLIC.ADD_DATA_SOURCE('<data_source_name>', '<dest_db>').

Data tables contain the replicated data and are available under identifier dest_db.schema_name.table_name where:

  • dest_db is the name of the destination database.

  • schema_name is the schema name in which the original MySQL table resides.

  • table_name is the name of the original MySQL table.

Note

dest_db, schema_name and table_name needs to be double quoted in case their names are mixed-case.

The replicated data types are mapped to match the Snowflake types. For more information, see PostgreSQL to Snowflake data type mapping.

The replicated tables contain the additional metadata columns:

  • _SNOWFLAKE_INSERTED_AT

  • _SNOWFLAKE_UPDATED_AT

  • _SNOWFLAKE_DELETED

Note

If the source table includes at least one of these column names, the table cannot be replicated and the replication status is set to PERMANENTLY_FAILED.

Replicated data access control

To control access to replicated data use DATA_READER application role. More on connector application roles: Application roles in the Snowflake Connector for MySQL For more granular control over specific destination objects, use ACCOUNTADMIN role to grant proper privileges or create database roles.

MySQL to Snowflake data type mapping

In Snowflake, column names of replicated tables are capitalized and types are mapped to match the Snowflake types.

The following table shows how connector data types are mapped to Snowflake types.

MySQL Type

Snowflake Type

Notes

DECIMAL / NUMERIC

NUMBER

The maximum number of digits in DECIMAL format for MySQL is 65. For Snowflake, the maximum is 38.

Supported up to the maximum allowed digits in Snowflake. When exceeded, precision is lost. For more information, see Numeric Data Types.

INT / INTEGER

INT

TINYINT / BOOL

INT

SMALLINT

INT

MEDIUMINT

INT

BIGINT

INT

YEAR

INT

FLOAT

FLOAT

DOUBLE

FLOAT

VARCHAR

VARCHAR

TINYTEXT

VARCHAR

TEXT

VARCHAR

ENUM

VARCHAR

Stored as a string. For example, for ENUM(‘one’, ‘two’) the possible values are: ‘one’, ‘two’.

SET

VARCHAR

Stored as a comma-joined string in column declaration order. For example, for SET(‘one’, ‘two’) the possible values are: ‘ ‘, ‘one’, ‘two’, ‘one,two’.

MEDIUMTEXT

VARCHAR

Supported up to the maximum entry size in Snowflake (16MB).

LONGTEXT

VARCHAR

Supported up to the maximum entry size in Snowflake (16MB).

CHAR

VARCHAR

Sent to Snowflake without the trailing spaces.

BIT

VARCHAR

Represented in hexadecimal, for example: ‘83060c183060c183’.

DATE

DATE

Stored in target tables as strings, for example ‘1971-01-31’. In flattened views, date is converted to DATE.

DATETIME

DATETIME / TIMESTAMP_NTZ

TIMESTAMP

TIMESTAMP_TZ

Stored in target tables as strings in UTC, for example ‘2000-12-30 23:59:59.001009+00:00’. In flattened views, timestamps are converted to TIMESTAMP_TZ.

TIME

TIME

Stored in target tables as strings, for example ‘23:59:59’. In flattened views, time values are converted to TIME.

BINARY

BINARY

MEDIUMBLOB

BINARY

Supported up to the maximum entry size in Snowflake, which is 16MB.

LONGBLOB

BINARY

Supported up to the maximum entry size in Snowflake, which is 16MB.

BLOB

BINARY

VARBINARY

BINARY

TINYBLOB

BINARY

JSON

VARIANT

JSON can be stored in the MySQL BinLog as a complete document or as a partial update. By default, it is stored as a complete document. Partial updates are currently not supported.

JSONs are sent to Snowflake as strings, but Snowpipe Streaming converts them to a VARIANT data type and stores them internally as ARRAY, OBJECT, etc.

Supported up to the maximum entry size in Snowflake, which is 16MB.

Viewing data from deleted columns

If a column is deleted in the source table, it will not be deleted in the destination table. Instead, a soft-delete approach is followed, and the column will be renamed to <previous name>__SNOWFLAKE_DELETED so that historical values can still be queried.

For example, if a column A is deleted, it will be renamed to A__SNOWFLAKE_DELETED in the destination table and can be queried as

SELECT A__SNOWFLAKE_DELETED FROM <TABLE_NAME>;
Copy

Viewing data from renamed columns

Renaming a column is equal to deleting the column and creating a new one with the new name. The deletion follows the soft-delete approach explained in the previous section.

For example, if column A was renamed to B - in the destination table A was renamed to A__SNOWFLAKE_DELETED and a new column B is added. All rows existing before the change keep the values of the column in the A__SNOWFLAKE_DELETED column while new rows added after the change have the values in the B column. Values from the renamed column can be viewed as a single column with a simple query:

SELECT
     CASE WHEN B IS NULL THEN A__SNOWFLAKE_DELETED ELSE B END AS A_RENAMED_TO_B
FROM <TABLE_WITH_RENAMED_COLUMN>;
Copy

A view can be created to simplify the usage after a column is renamed.

Next steps

After completing these procedures, review the processes in Snowflake Connector for MySQL ongoing tasks