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 » Marketplace.

  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.

Optional: Installing multiple instances of Snowflake Connector for PostgreSQL

You can install multiple instances of the same connector application on your Snowflake account.

To install an additional application instance, do the following:

  1. Sign in to Snowsight.

  2. Navigate to Snowflake Marketplace and select the application for which you want to install another instance. The application details page appears.

  3. Click Add instance. The installation dialog appears.

  4. Provide the instance name and select the warehouse to be used during the installation.

  5. Select Get to begin the installation process.

Adding connector instances can take several minutes. When the installation process completes, you get an email notification.

Attention

To avoid ingested data corruption, during connector configuration, always use a database schema that is different from all other native applications.

To access your installed connector application instances, do the following:

  1. Sign in to Snowsight.

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

  3. Select your application instance to access it.

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

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:

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

    Caution

    Every time you click Generate file a new file is generated, with a new set of temporary access keys for the agent’s user. The user is automatically altered to use these new keys for authentication. If you already have the agent running with another set of keys, it will disconnect from Snowflake and stop working.

  2. Using the generated snowflake.json file, proceed to configure the agent, as described in Setting up the Snowflake Connector for PostgreSQL Agent container. Then return to Snowsight.

  3. Select Refresh to check connectivity with the agent. The application will confirm that the agent is successfully connected, and a confirmation dialog displays.

  4. Select Define data to sync to continue.

Next steps

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

Language: English