设置 Openflow Connector for SQL Server¶

先决条件¶

Before setting up the connector, ensure that you have completed the following prerequisites:

1. 确保您已查看 关于 Openflow Connector for SQL Server。

2. 确保您已查看 支持的 SQL 服务器版本。

3. Ensure that you have set up your runtime deployment. For more information, see the following topics:

   • 设置 Openflow - BYOC

   • Set up Openflow - Snowflake Deployments.

4. If you use Openflow - Snowflake Deployments, ensure that you have reviewed configuring required domains and have granted access to the required domains for the SQL Server connector.

Set up your SQL Server instance¶

Before setting up the connector, perform the following tasks in your SQL Server environment:

1. Enable change tracking on the databases (https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server?view=sql-server-ver16#enable-change-tracking-for-a-database) and tables (https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server?view=sql-server-ver16#enable-change-tracking-for-a-table) that you plan to replicate, as shown in the following SQL Server example:

   CopyExpand

   ALTER DATABASE <database>
     SET CHANGE_TRACKING = ON
     (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
   
   ALTER TABLE <schema>.<table>
     ENABLE CHANGE_TRACKING;
   

   Show lessSee more

   Scroll to top

   备注

   Run these commands for every database and table that you plan to replicate.

   The connector requires that change tracking is enabled on the databases and tables before replication starts. Ensure that every table that you plan to replicate has enabled change tracking. You can also enable change tracking on additional tables while the connector is running.

2. Create a login for the SQL Server instance:

   CopyExpand

   CREATE LOGIN <user_name> WITH PASSWORD = '<password>';
   

   Show lessSee more

   Scroll to top

   This login is used to create users for the databases you plan to replicate.

3. Create a user for each database you are replicating by running the following SQL Server command in each database:

   CopyExpand

   USE <source_database>;
   CREATE USER <user_name> FOR LOGIN <user_name>;
   

   Show lessSee more

   Scroll to top

4. Grant the SELECT and VIEW CHANGE TRACKING permissions to the user for each database that you are replicating:

   CopyExpand

   GRANT SELECT ON <database>.<schema>.<table> TO <user_name>;
   GRANT VIEW CHANGE TRACKING ON <database>.<schema>.<table> TO <user_name>;
   

   Show lessSee more

   Scroll to top

   Run these commands in each database for every table that you plan to replicate. These permissions must be granted to the user of each database that you created in a previous step.

5. (Optional) Grant the VIEW DEFINITION privilege on the User Defined Data Types (UDDT).

   If your tables contain columns that use User Defined Data Types (UDDT), and the UDDT is owned by a different user than the connector user, you must grant the VIEW DEFINITION permission to the connector user as shown in the following SQL Server example:

   CopyExpand

   GRANT VIEW DEFINITION TO <user_name>;
   

   Show lessSee more

   Scroll to top

   Without this permission, columns using UDDT are silently excluded from replication.

6. (Optional) Configure SSL connection.

   If you use an SSL connection to connect SQL Server, create the root certificate for your database server. This is required when configuring the connector.

Set up your Snowflake environment¶

As a Snowflake administrator, perform the following tasks:

1. Create a destination database in Snowflake to store the replicated data:

   CopyExpand

   CREATE DATABASE <destination_database>;
   

   Show lessSee more

   Scroll to top

2. Create a Snowflake service user:

   CopyExpand

   CREATE USER <openflow_user>
     TYPE = SERVICE
     COMMENT='Service user for automated access of Openflow';
   

   Show lessSee more

   Scroll to top

3. Create a Snowflake role for the connector and grant the required privileges:

   CopyExpand

   CREATE ROLE <openflow_role>;
   GRANT ROLE <openflow_role> TO USER <openflow_user>;
   GRANT USAGE ON DATABASE <destination_database> TO ROLE <openflow_role>;
   GRANT CREATE SCHEMA ON DATABASE <destination_database> TO ROLE <openflow_role>;
   

   Show lessSee more

   Scroll to top

   Use this role to manage the connector's access to the Snowflake database.

   To create objects in the destination database, you must grant the USAGE and CREATE SCHEMA privileges on the database to the role used to manage access.

4. Create a Snowflake warehouse for the connector and grant the required privileges:

   CopyExpand

   CREATE WAREHOUSE <openflow_warehouse> WITH
     WAREHOUSE_SIZE = 'XSMALL'
     AUTO_SUSPEND = 300
     AUTO_RESUME = TRUE;
   GRANT USAGE, OPERATE ON WAREHOUSE <openflow_warehouse> TO ROLE <openflow_role>;
   

   Show lessSee more

   Scroll to top

   Snowflake recommends starting with a XSMALL warehouse size, then experimenting with size depending on the number of tables being replicated and the amount of data transferred. Large numbers of tables typically scale better with multi-cluster warehouses, rather than a larger warehouse size. For more information, see multi-cluster warehouses.

5. Set up the public and private keys for key pair authentication:

   1. Create a pair of secure keys (public and private).

   2. Store the private key for the user in a file to supply to the connector's configuration.

   3. Assign the public key to the Snowflake service user:

      CopyExpand

      ALTER USER <openflow_user> SET RSA_PUBLIC_KEY = 'thekey';
      

      Show lessSee more

      Scroll to top

      有关更多信息，请参阅 密钥对身份验证和密钥对轮换。

安装连接器¶

To install the connector, do the following as a data engineer:

1. Navigate to the Openflow overview page. In the Featured connectors section, select View more connectors.

2. 在 Openflow 连接器页面上，找到连接器并选择 Add to runtime。

3. In the Select runtime dialog, select your runtime from the Available runtimes drop-down list and click Add.

   备注

   在安装连接器之前，请确保在 Snowflake 中为连接器创建了数据库和架构，用于存储引入的数据。

4. 使用您的 Snowflake 账户凭据对部署进行身份验证，并在系统提示时选择 Allow，以允许运行时应用程序访问您的 Snowflake 账户。连接器安装过程需要几分钟才能完成。

5. 使用您的 Snowflake 账户凭据进行运行时身份验证。

此时将显示 Openflow 画布，其中添加了连接器进程组。

配置连接器¶

To configure the connector, do the following as a data engineer:

1. 右键点击导入的进程组并选择 Parameters。

2. Populate the required parameter values as described in 流参数.

[
    {
        "schema": "<source table schema>",
        "table" : "<source table name>",
        "included": ["<column name>", "<column name>"],
        "excluded": ["<column name>", "<column name>"],
        "includedPattern": "<regular expression>",
        "excludedPattern": "<regular expression>",
    }
]

customers_1234_1
customers_1234_2
orders_5678_1
orders_5678_2

运行流¶

1. 右键点击“飞机”图标并选择 Enable all Controller Services。

2. 右键点击导入的进程组并选择 Start。连接器开始数据引入。