备注
|POSTGRES6|和 |MYSQL6|受 Connector 条款 的约束。
使用 Snowflake 的 MySQL 和 PostgreSQL 连接器¶
简介¶
欢迎学习 Snowflake Database Connector 使用教程。本指南将帮助您将数据从关系数据库无缝传输至 Snowflake。
在本教程中,您将学习以下技能:
使用引入所用的示例数据在 Docker 中完成 MySQL 和 PostgreSQL 设置。
安装和配置两个原生应用程序,每个对应一个数据库。
设置并微调一个代理,每个代理对应一个数据库。
启动和管理数据引入流程。
监控数据引入工作流程。
让我们开始吧!
先决条件¶
在开始本教程之前,请确保您满足以下要求:
您已在本地计算机上安装并运行了 Docker。
您有可用于连接数据库的工具。该工具可以是数据库专用工具,也可以是通用工具,如 IntelliJ 或 Visual Studio Code。
创建 MySQL 和 PostgreSQL 源数据库¶
在本节,我们将指导您完成以下步骤:
启动数据库实例 - 了解如何使用 Docker 启动 MySQL 和 PostgreSQL 实例。
连接数据库 - 有关与数据库建立连接的说明。
加载示例数据 - 有关如何使用示例数据填充数据库的演示。
启动数据库实例¶
要使用 Docker 开启 MySQL 和 PostgreSQL 数据库配置,请创建文件 docker-compose.yaml
。文件内容应该类似于以下示例:
services:
mysql:
container_name: mysql8
restart: always
image: mysql:8.0.28-oracle
command: --log-bin=/var/lib/mysql/mysql-bin
--max-binlog-size=4096
--binlog-format=ROW
--binlog-row-image=FULL
--binlog-row-metadata=FULL
--sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH"
environment:
MYSQL_ROOT_PASSWORD: 'mysql'
volumes:
- ./mysql-data:/var/lib/mysql
ports:
- "3306:3306"
postgres:
image: "postgres:11"
container_name: "postgres11"
environment:
POSTGRES_USER: 'postgres'
POSTGRES_PASSWORD: 'postgres'
ports:
- "5432:5432"
command:
- "postgres"
- "-c"
- "wal_level=logical"
volumes:
- ./postgres-data:/var/lib/postgresql/data
docker-compose.yaml
文件创建完成后,请按照以下步骤操作:
打开一个终端。
导航至包含
docker-compose.yaml
文件的目录。执行以下命令,启动容器中的源数据库:
docker compose up -d
运行此命令后,您应该会看到两个容器正在运行源数据库。
连接数据库¶
要使用 IntelliJ 或 Visual Studio Code 数据库连接来连接预配置数据库,请使用提供的凭据执行以下步骤:
打开您选择的工具,连接 MySQL。
点击“+”符号或类似符号,添加数据源。
填写连接详细信息:
用户:
root
密码:
mysql
URL:
jdbc:mysql://localhost:3306
测试连接并保存。
打开您选择的工具,连接 PostgreSQL。
点击“+”符号或类似符号,添加数据源。
填写连接详细信息:
用户:
postgres
密码:
postgres
数据库:
postgres
URL:
jdbc:postgresql://localhost:5432
测试连接并保存。
加载示例数据¶
要初始化和加载示例,请在这些连接中执行这些脚本。
执行脚本,生成示例数据
CREATE DATABASE mysql_ingest_database;
USE mysql_ingest_database;
CREATE TABLE mysql_rows(
id INT AUTO_INCREMENT PRIMARY KEY,
random_string VARCHAR(255),
random_number INT);
INSERT INTO mysql_rows (random_string, random_number) VALUES
('fukjxyiteb', 100),
('ndhbbipodi', 37),
('laebpztxzh', 83);
SELECT * FROM mysql_ingest_database.mysql_rows;
执行脚本,生成示例数据
CREATE SCHEMA psql_rows_schema;
SET search_path TO psql_rows_schema;
CREATE TABLE psql_rows_schema.postgres_rows (
id SERIAL PRIMARY KEY,
a_text TEXT,
a_boolean BOOLEAN,
a_number INTEGER,
a_decimal DOUBLE PRECISION);
INSERT INTO psql_rows_schema.postgres_rows (a_text, a_boolean, a_number, a_decimal) VALUES
('QfJhyWwFuC', True, 37, 15.46),
('GwmIFgwvFy', True, 14, 13.21),
('jYvqOSEtam', True, 25, 20.85);
-- The publication is required to start the replication progress as the Connector is based on PostgreSQL Logical Replication
CREATE PUBLICATION agent_postgres_publication FOR ALL TABLES;
SELECT * FROM psql_rows_schema.postgres_rows;
您应该会在每个填充的数据库中看到三行数据。
安装和配置原生应用程序¶
在本步骤的操作中,您将:
安装原生应用程序¶
按照以下步骤从 Snowflake Native Apps Marketplace 安装应用程序
登录 Snowsight。
在导航菜单中,选择 Data Products » Marketplace。
安装 Snowflake Connector for MySQL 和 Snowflake Connector for PostgreSQL 应用程序。
安装以上两个应用程序。
安装后,您将看到 Data Products » Apps 中列出的新应用程序。
配置原生应用程序¶
登录 Snowsight。
在导航菜单中,选择 Data Products » Apps。
打开每个应用程序并执行以下操作:
选择 Download Driver 并保存文件。文件名类似于
mariadb-java-client-3.4.1.jar
或采用更新的版本(如有)。保存该文件,以便用于代理配置。选择 Mark all as done,因为我们将从头开始创建和填充源数据库。
备注
此时无需添加其他网络配置,因为我们将在稍后步骤中配置代理。
点击 Start configuration。
在 Configure Connector 屏幕上,选择 Configure。 将显示 Verify Agent Connection 页面。
选择 Generate file 生成代理配置文件。文件名应该类似于
snowflake.json
。保存该文件,以便稍后在代理配置部分使用。
选择 Mark all as done,因为我们将从头开始创建和填充源数据库。
备注
此时无需添加其他网络配置,因为我们将在稍后步骤中配置代理。
点击 Start configuration
在 配置连接器 屏幕上,选择 配置。
在 Verify Agent Connection 页面中选择 Generate file 以生成代理配置文件。文件名应该类似于
snowflake.json
。保存该文件,以便在代理配置部分使用。
配置代理¶
在本节,我们将配置将与源数据库一起运行的代理。
第一步,创建目录 agent-mysql
和 agent-postgresql
。
在每个目录中,创建子目录 agent-keys
和 configuration
。目录结构应该类似于:
.
├── agent-mysql
│ ├── agent-keys
│ └── configuration
└── agent-postgresql
├── agent-keys
└── configuration
创建配置文件¶
在本节,我们将在配置文件中添加内容,以便每个代理都能正确运行。配置文件包括:
连接到 Snowflake 的
snowflake.json
文件。连接到源数据库的
datasources.json
文件。具有其他代理环境变量的
postgresql.conf/mysql.conf
文件。MySQL 代理的 JDBC 驱动程序文件。
在终端中,导航至
agent-mysql
目录。创建 Docker Compose 文件:file:
docker-compose.yaml
,其内容如下:services: mysql-agent: container_name: mysql-agent image: snowflakedb/database-connector-agent:latest volumes: - ./agent-keys:/home/agent/.ssh - ./configuration/snowflake.json:/home/agent/snowflake.json - ./configuration/datasources.json:/home/agent/datasources.json - ./configuration/mariadb-java-client-3.4.1.jar:/home/agent/libs/mariadb-java-client-3.4.1.jar env_file: - configuration/mysql.conf mem_limit: 6g
将以前下载的
snowflake.json
文件移动至configuration
目录中。将以前下载的
mariadb-java-client-3.4.1.jar
文件移动至configuration
目录中。在
configuration
目录中创建datasources.json
,内容如下:{ "MYSQLDS1": { "url": "jdbc:mariadb://host.docker.internal:3306/?allowPublicKeyRetrieval=true&useSSL=false", "username": "root", "password": "mysql", "ssl": false } }
在
configuration
目录中创建mysql.conf
,内容如下:JAVA_OPTS=-Xmx5g MYSQL_DATASOURCE_DRIVERPATH=/home/agent/libs/mariadb-java-client-3.4.1.jar
使用以下命令启动代理。不应该出现任何错误消息,且代理应该生成公钥和私钥对,用于向 Snowflake 进行身份验证。
docker compose stop # stops the previous container in case you've launched it before docker compose rm -f # removes the agent container to recreate it with the latest image in case you had one before docker compose pull # refresh remote latest tag in case you have cached previous version docker compose up -d # run the agent
请注意,驱动程序 jar 文件 名称应与
docker-compose.yaml
和mysql.conf
文件中下载和使用的名称 相同。
在命令行上,导航至
agent-postgresql
目录。创建 Docker Compose 文件:file:
docker-compose.yaml
,其内容如下:services: postgresql-agent: container_name: postgresql-agent image: snowflakedb/database-connector-agent:latest volumes: - ./agent-keys:/home/agent/.ssh - ./configuration/snowflake.json:/home/agent/snowflake.json - ./configuration/datasources.json:/home/agent/datasources.json env_file: - configuration/postgresql.conf mem_limit: 6g
将以前下载的
snowflake.json
文件移动至configuration
目录中。在
configuration
目录中创建datasources.json
,内容如下:{ "PSQLDS1": { "url": "jdbc:postgresql://host.docker.internal:5432/postgres", "username": "postgres", "password": "postgres", "publication": "agent_postgres_publication", "ssl": false } }
在
configuration
目录中,创建postgresql.conf
,内容如下:JAVA_OPTS=-Xmx5g
使用以下命令启动代理。不应该出现任何错误消息,且代理应该生成公钥和私钥对,用于向 Snowflake 进行身份验证。
docker compose up -d
完成后,目录结构应该类似于以下内容。请注意,代理密钥目录中包含了自动生成的私钥和公钥。
.
├── agent-mysql
│ ├── agent-keys
│ │ ├── database-connector-agent-app-private-key.p8
│ │ └── database-connector-agent-app-public-key.pub
│ ├── configuration
│ │ ├── datasources.json
│ │ ├── mariadb-java-client-3.4.1.jar
│ │ ├── mysql.conf
│ │ └── snowflake.json
│ └── docker-compose.yaml
└── agent-postgresql
├── agent-keys
│ ├── database-connector-agent-app-private-key.p8
│ └── database-connector-agent-app-public-key.pub
├── configuration
│ ├── datasources.json
│ ├── postgresql.conf
│ └── snowflake.json
└── docker-compose.yaml
验证与 Snowflake 的连接¶
返回至以前创建的原生应用程序。点击“Agent Connection”中的 Refresh 按钮。
成功配置后,您应该会看到:
Agent is fully set up and connected. To select data to ingest Open Worksheet.
配置和监控数据引入过程¶
在此步骤,我们将指示连接器开始复制所选表。首先,在 Snowflake 中创建一个共享下沉数据库。
CREATE DATABASE CONNECTORS_DEST_DB;
GRANT CREATE SCHEMA ON DATABASE CONNECTORS_DEST_DB TO APPLICATION SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL;
GRANT CREATE SCHEMA ON DATABASE CONNECTORS_DEST_DB TO APPLICATION SNOWFLAKE_CONNECTOR_FOR_MYSQL;
数据库就绪后,可以进入配置过程。
要开始表复制,必须首先添加要复制的数据源,然后指定要复制的表。
CALL SNOWFLAKE_CONNECTOR_FOR_MYSQL.PUBLIC.ADD_DATA_SOURCE('MYSQLDS1', 'CONNECTORS_DEST_DB'); CALL SNOWFLAKE_CONNECTOR_FOR_MYSQL.PUBLIC.ADD_TABLES('MYSQLDS1', 'mysql_ingest_database', ARRAY_CONSTRUCT('mysql_rows'));
要监控复制,请执行以下查询:
SELECT * FROM SNOWFLAKE_CONNECTOR_FOR_MYSQL.PUBLIC.REPLICATION_STATE; SELECT * FROM SNOWFLAKE_CONNECTOR_FOR_MYSQL.PUBLIC.CONNECTOR_STATS;
要开始表复制,必须首先添加要复制的数据源,然后指定要复制的表。
CALL SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL.PUBLIC.ADD_DATA_SOURCE('PSQLDS1', 'CONNECTORS_DEST_DB'); CALL SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL.PUBLIC.ADD_TABLES('PSQLDS1', 'psql_rows_schema', ARRAY_CONSTRUCT('postgres_rows'));
要监控复制,可以执行以下查询:
SELECT * FROM SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL.PUBLIC.REPLICATION_STATE; SELECT * FROM SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL.PUBLIC.CONNECTOR_STATS;
了解连接器状态¶
REPLICATION_STATE
视图对于监控表复制的状态至关重要。这一过程包括三个不同阶段:
SCHEMA_INTROSPECTION
:确保源表的架构被准确复制。INITIAL_LOAD
:将源表中的现有数据传输到目标表中。INCREMENTAL_LOAD
:持续复制源中的持续变化。
成功复制后,状态显示将如下所示:
REPLICATION_PHASE
SCHEMA_INTROSPECTION_STATUS
SNAPSHOT_REPLICATION_STATUS
INCREMENTAL_REPLICATION_STATUS
INCREMENTAL_LOAD
DONE
DONE
IN PROGRESS
查看数据¶
执行以下命令查看数据,每个数据库应包括大约 3 行数据。
SELECT * FROM CONNECTORS_DEST_DB."psql_rows_schema"."postgres_rows";
SELECT * FROM CONNECTORS_DEST_DB."mysql_ingest_database"."mysql_rows";
清理和其他资源¶
恭喜!您已成功完成本教程。
要清理您的环境,请执行下列命令。否则,连接器将会一直运行并产生成本。
移除原生应用程序¶
DROP APPLICATION SNOWFLAKE_CONNECTOR_FOR_POSTGRESQL CASCADE;
DROP APPLICATION SNOWFLAKE_CONNECTOR_FOR_MYSQL CASCADE;
移除仓库、角色和用户¶
在安装过程中,创建了多个仓库、角色和用户。执行以下查询,删除这些对象。
DROP ROLE MYSQL_ADMINISTRATIVE_AGENT_ROLE;
DROP ROLE MYSQL_AGENT_ROLE;
DROP USER MYSQL_AGENT_USER;
DROP WAREHOUSE MYSQL_COMPUTE_WH;
DROP WAREHOUSE MYSQL_OPS_WH;
DROP ROLE POSTGRESQL_ADMINISTRATIVE_AGENT_ROLE;
DROP ROLE POSTGRESQL_AGENT_ROLE;
DROP USER POSTGRESQL_AGENT_USER;
DROP WAREHOUSE POSTGRESQL_COMPUTE_WH;
DROP WAREHOUSE POSTGRESQL_OPS_WH;
停止数据库容器运行¶
要停止运行包含 MySQL 和 PostgreSQL 的容器,请导航至包含 docker-compose.yaml
文件的目录,并执行 docker compose down -v
。
其他资源¶
请使用以下资源继续了解连接器: