Working with the MySQL and PostgreSQL connectors for Snowflake¶

1. Open your tool of choice for connecting to the MySQL.

2. Click the ‘+’ sign or similar to add data source.

3. Fill in the connection details:

   • User: root

   • Password: mysql

   • URL: jdbc:mysql://localhost:3306

4. Test the connection and save.

1. Open your tool of choice for connecting to the PostgreSQL.

2. Click the ‘+’ sign or similar to add data source.

3. Fill in the connection details:

   • User: postgres

   • Password: postgres

   • Database: postgres

   • URL: jdbc:postgresql://localhost:5432

4. Test the connection and save.

Execute the script to generate sample data

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;

Execute the script to generate sample data

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;

1. Select Download Driver and save the file. The file name will resemble mariadb-java-client-3.4.1.jar or with newer version when available. Save this file for use during agent configuration.

2. Select Mark all as done as we will create and populate source databases from scratch.

   Note

   No addition additional network configuration is required at this point as we’ll configure the agent later in the tutorial.

3. Click Start configuration.

4. On the Configure Connector screen, select Configure. The Verify Agent Connection page will display.

5. Select Generate file to generate an agent configuration file. The file name should resemble snowflake.json. Save this file for later use in the Agent Configuration section.

1. Select Mark all as done as we will create and populate source databases from scratch.

   Note

   No addition additional network configuration is required at this point as we’ll configure the agent later in the tutorial.

2. Click Start configuration

3. On the Configure Connector screen, select Configure.

4. In the Verify Agent Connection page select Generate file to generate the agent configuration file. The file name should resemble snowflake.json. Save this file for use during the Agent Configuration section.

1. In a terminal, navigate to the agent-mysql directory.

2. Create the Docker Compose file docker-compose.yaml with the following content:

   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
   

   Copy

3. Move the previously downloaded snowflake.json file into the configuration directory.

4. Move the previously downloaded mariadb-java-client-3.4.1.jar file into the configuration directory.

5. In the configuration directory create datasources.json with content:

   {
     "MYSQLDS1": {
       "url": "jdbc:mariadb://host.docker.internal:3306/?allowPublicKeyRetrieval=true&useSSL=false",
       "username": "root",
       "password": "mysql",
       "ssl": false
     }
   }
   

   Copy

6. In the configuration directory create mysql.conf with content:

   JAVA_OPTS=-Xmx5g
   MYSQL_DATASOURCE_DRIVERPATH=/home/agent/libs/mariadb-java-client-3.4.1.jar
   

   Copy

7. Start the agent using the following command. There shouldn’t be any error message and the agent should generate a public and private key pair for authentication to 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
   

   Copy

8. Please note that the driver jar file name should be identical to the one downloaded and used in the docker-compose.yaml and mysql.conf files.

1. On the command line, navigate to the agent-postgresql directory.

2. Create the Docker Compose file docker-compose.yaml with the following content:

   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
   

   Copy

3. Move the previously downloaded snowflake.json file into the configuration directory.

4. In the configuration directory create datasources.json with content:

   {
     "PSQLDS1": {
       "url": "jdbc:postgresql://host.docker.internal:5432/postgres",
       "username": "postgres",
       "password": "postgres",
       "publication": "agent_postgres_publication",
       "ssl": false
     }
   }
   

   Copy

5. In the configuration directory, create postgresql.conf with the following content:

   JAVA_OPTS=-Xmx5g
   

   Copy

6. Start the agent using the following command. There shouldn’t be any error message and the agent should generate a public and private key pair for authentication to Snowflake.

   docker compose up -d
   

   Copy