Use Snowflake with Connect CDC - Connect_CDC - connect_cdc_mimix_share - 6.x

Connect CDC Getting Started Guide
Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
6.x
Language
English
Product name
Connect CDC
Title
Connect CDC Getting Started Guide
Copyright
2024
First publish date
2003
Last updated
2024-10-15
Published on
2024-10-15T20:38:41.117981

Before changed data is replicated (applied) in batches to the Snowflake target, it is extracted from the data source and pushed to and bundled in an internal or external staging area. You create remote staging-specific file server connections to cloud storage web services and solutions, including Amazon S3 and Windows Azure.

When you create a Snowflake DBMS server for staging, you select an existing remote file server, create a new remote file server, or specify a local directory path where data will be staged before being sent to Snowflake’s internal staging area.

The following diagram shows data flow from various source databases to Snowflake using Connect CDC.

Data is by default encrypted at rest, except when Local disk is used as staging.

Note: To create remote file servers, see Create a New Remote File Server for Snowflake.

  1. On the Servers Properties dialog, select Snowflake from the dropdown list in the DBMS type section.

  2. Enter information for each field on the Server Properties dialog. See Server Properties dialog for Snowflake for field information.

Table 1. Server Properties dialog for Snowflake

Parameter

Description

Name

Use alphanumeric characters to specify a unique server name for use in the model.

Note: The slash (/), backslash (\), colon (:), left caret (<), right caret (>), and spaces cannot be used in a Server name.

DBMS Type

Select Snowflake from the dropdown list.

JDBC Driver

 Snowflake is preselected in this field.

Driver Version

View the JDBC Driver version. This information is obtained each time a connection to the database is made (including a test connection).

DBMS logon IDs and DBMS logon password

Specify the Default and Rep user IDs.

  1. In DBMS logon password, specify the logon password for the ID.

  2. Input the user login ID for accessing this server.

  3. Select Default and Rep user (they may be different).

  • The Default user is what Connect CDC Director uses to connect to the server.

  • The Rep user is what the kernel uses to connect to the server.

Specify additional IDs.

  1. In DBMS logon IDs, for the second user ID, select Type in a new ID.

  2. Specify an ID that can access and query the source or target tables on this server for testing or informational purposes.

  3. Specify the ID password in DBMS logon password.

JDBC url
Enter the JDBC driver connection string used to connect to Snowflake. URL strings have the following syntax:
jdbc:snowflake://account_name.snowflakecomputing.com
/?parameter&parameter
Where:
  • account_name is the full name of your Snowflake account.
  • parameter represents one or more server parameters added after the

    Snowflake account name.

Parameters have the following syntax: name=value. Each name=value pair is separated by an ampersand (&). For example: 
jdbc:snowflake://mycompany.us.east.1.snowflakecomputing.com
/?warehouse=mycluster&db=my_database&schema=my_schema
Note: The following guidelines:
Staging
A stage is an internal (local) or external location where data files are stored (staged) before data in the files is loaded into Snowflake tables. When staging on a remote server, the activity of staging the files involves batching and sending them to the remote file system. The storage location can be either private (protected) or public.
Note: Batch record size and duration are configured as synchronization and replication request properties.
Specify the following staging properties:
  • Stage schema. Specify the name of the schema where the staging table will be created. Note: This option is unavailable if you did not specify a database in the URL string or the ID and Password you entered are incorrect.
    • Storage integration. (Valid for external staging locations.) Enter the string used to connect to the storage location.
    • Storage integrations are Snowflake objects that store a generated identity and access management (IAM) entity for your external cloud storage and include an optional set of allowed or blocked Amazon S3 or Window Azure storage locations. This allows you to avoid supplying credentials when loading or unloading staged data.
      Note: When using Azure as a target, storage integration is required.
  • Select one of the following to specify a storage server to use when staging the data before it is applied to the Snowflake target.
    • External. Click the dropdown list and select an existing remote file server created for Amazon S3 or Window Azure Storage. To create a new remote file server, click New. For more information, see Create a new remote file server.
    • Internal - local path - local path. Select to use an internal (native) staging location managed by Snowflake. When selected, enter a local staging path where the records will be staged before being loaded to Snowflake. For example, C:\Temp.
Note: This path needs to be a windows path if Snowflake is hosted on a Windows system and a Unix path if hosted on a Linux system.