Viewing MySQL data in Snowflake¶

Note

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

The connector replicates data to the destination database, which 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 tables contain the additional metadata columns:

Column name	Data type	Description
`_SNOWFLAKE_INSERTED_AT`	TIMESTAMP_NTZ	Timestamp of when the row was inserted into the destination table, in UTC.
`_SNOWFLAKE_UPDATED_AT`	TIMESTAMP_NTZ	Timestamp of when the row was last updated in the destination table, in UTC.
`_SNOWFLAKE_DELETED`	BOOLEAN	Value is `true` if the row has been deleted from the source table.

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

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