Setting up the Snowflake Connector for PostgreSQL using Snowsight

Note

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

Note

For accounts where the AUTOCOMMIT parameter set to false, it should be set at the sessions level during configuration to true using the SQL command ALTER SESSION SET AUTOCOMMIT=TRUE.

The process of configuring up the Snowflake Connector for PostgreSQL using Snowsight includes the following steps:

Configuring logging for the connector

The Snowflake Connector for PostgreSQL uses event table to store events and logs generated by the connector code. Setting up an event table is a mandatory step.

Note

If the event table is already configured for the account used for the connector, skip this step.

To create an event table, do the following:

CREATE EVENT TABLE IF NOT EXISTS <fully_qualified_event_table_name> CHANGE_TRACKING = TRUE;
ALTER ACCOUNT SET EVENT_TABLE = <fully_qualified_event_table_name>;
Copy

Where:

fully_qualified_event_table_name

Specifies the name of the event table.

More information about an event table can be found here.

Installing the Snowflake Connector for PostgreSQL

The following procedure describes how to install the connector:

  1. Sign in to Snowsight as a user with the ACCOUNTADMIN role.

  2. In the left navigation, select Data Products » Apps.

  3. Search for the Snowflake Connector for PostgreSQL, then select the tile for the connector.

  4. In the page for the Snowflake Connector for PostgreSQL, select Get.

    This displays a dialog that you use to begin the initial part of the installation process.

    In the dialog configure the following:

    1. In the Warehouse used for installation field, select the warehouse that you want to use for installing the connector.

      Note

      This is not the same warehouse that is used by the connector to synchronize data from MySQL database. In a later step, you will create a separate warehouse for this purpose.

    2. Optionally, under Options » Application name you can change the name of the application.

    3. Select Get.

  5. A dialog appears with the notification: Successfully Installed. To continue configuration, select Configure.

    The dialog closes, and the Snowflake Connector for PostgreSQL page displays the UI for configuring and managing the connector.

Configuring the Snowflake Connector for PostgreSQL

  1. Sign in to Snowsight as a user with the ACCOUNTADMIN role.

  2. In the navigation menu, select Data Products » Apps.

  3. Search for the Snowflake Connector for PostgreSQL, then select it. You will be now moved to the installation wizard page, that will take you through the configuration process.

Configure the application as follows:

Step 1: Complete prerequisites

Complete the following prerequisite steps to set up your database and agent:

Step

Description

Provide access to the source database

Prerequisites for Snowflake Connector for PostgreSQL datasources

Download and install the Agent

Setting up the Snowflake Connector for PostgreSQL Agent container

Open traffic between the Agent source database and Snowflake

Opening required ports for Snowflake Connector for PostgreSQL

Select Mark as done for each completed step.

Select Start configuration.

Step 2: Configure

In the configuration dialog, enter values for the following fields:

Field

Description

Compute Warehouse

Identifier for a new, dedicated virtual warehouse for the connector. This warehouse will be used to process data gained from the agent and put them into target table.

Specify a name that is unique for your account. The name of the warehouse must be a valid object identifier.

Note

Do not specify the same warehouse that you selected at the beginning of the connector installation.

The configuration process creates a new X-Small warehouse with the specified name.

Operational Warehouse

Identifier for a new, dedicated virtual warehouse for the connector. This warehouse will be used to manage the activities of connector and its agents.

Specify a name that is unique for your account. The name of the warehouse must be a valid object identifier.

Note

Do not specify the same warehouse that you selected at the beginning of the connector installation.

The configuration process creates a new X-Small warehouse with the specified name.

Role

Identifier for a new custom role for the agent.

Specify a name that is unique for your account. The name of the role must be a valid object identifier.

The configuration process creates a new role with the specified name.

User

Identifier for a new user that agent will use to authenticate to Snowflake.

Specify a name that is unique within the selected database. The name of the user must be a valid object identifier.

The configuration process creates a new user with the specified name and of type SERVICE.

Note

By default, the fields are set to the names of objects that are created when you configure the connector. Snowflake recommends using new objects for these fields. However, you can specify the names of existing objects, if needed (e.g. if you are reinstalling the connector).

Select Configure.

Step 3: Verify agent connection

Check the connection of the agent to Snowflake as follows:

Select Generate file to generate initial configuration file for the agent.

Note

You may generate the file again, but it will generate a new temporary private key for the user used by the agent. This will invalidate the key in previously generated file.

Use the generated file to configure previously downloaded agent.

Select Refresh to check connectivity with the agent.

If connection is successful, a dialog will appear. Select Define data to sync to navigate to connector home page.

Next steps

After completing these procedures, follow the steps in Setting up the Snowflake Connector for PostgreSQL Agent container

Language: English