Setting up the Snowflake Connector for MySQL Agent container¶

Choose a container orchestration system¶

The agent is packaged as a standard Docker container image, and can be run on any orchestration system that supports the standard. This can be a stand-alone Docker (https://www.docker.com/) instance, Kubernetes (https://kubernetes.io/), RedHat OpenShift (https://www.redhat.com/en/technologies/cloud-computing/openshift), a cloud-managed solution, such as AWS Fargate (https://aws.amazon.com/fargate/), and others. Your organization will often have a preferred, existing system for you to use.

Pay attention to the agent configuration section of this document, because different orchestration systems come with different constraints. Your system, or specific setup, may not permit you to mount writable volumes (as is required with the agent’s primary configuration option).

Later examples will focus on Kubernetes as the most popular orchestration system. The approach will often be similar in other systems, and you will need to adjust the examples for your setup.

Confirm required system resources¶

• The agent is a memory-intensive application, and requires a minimum 6GB of RAM to operate correctly.

• The optimal number of CPUs is 4. Fewer CPUs can decrease performance. More CPUs will not improve performance.

Set up connectivity¶

The agent needs to connect to every source database that you intend to read data from. Configure your network and firewalls, so that direct connections are possible, and MySQL’s classic client port is reachable. Typically that’s port 3306. For more information see MySQL’s Port Reference Tables (https://dev.mysql.com/doc/mysql-port-reference/en/mysql-port-reference-tables.html).

If your source databases reside in isolated networks, and connecting from a single agent won’t be possible, you will need to install additional instances of the connector’s native application, each one with its own agent. This feature is currently in private preview. Please contact Snowflake Support to request access.

The agent also connects directly to your Snowflake deployment. For information on which hostnames need to be available see Allowing Hostnames.

If any of the agent’s connections pass through a proxy, you will need to pass additional configuration to the agent. See Review optional configuration environment variables.

Download the MariaDB JDBC driver¶

The agent uses this driver to connect to, and interact with MySQL databases. Despite it nominally being a driver for MariaDB, they’re compatible.

Due to licensing limitations, the JDBC driver cannot be distributed together with the agent, and you’ll have to provide it. Download the MariaDB JDBC Connector 3.4.1 (https://repo1.maven.org/maven2/org/mariadb/jdbc/mariadb-java-client/3.4.1/mariadb-java-client-3.4.1.jar) and save it in a directory from which you can mount it to the agent’s container.

[Optional] Obtain and prepare SSL certificates for source databases¶

When the agent connects to source databases via SSL, it requires their certificates to validate the connections. These certificates must be available in the Java Truststore inside the agent’s container, under the path /opt/java/openjdk/lib/security/cacerts.

The simplest way to supply certificates to the agent is to add them to the host machine’s existing cacerts file, and then mount that file to the running container.

openssl x509 -outform der -in ca-root.pem -out ca-root.der
keytool -import -alias server-root \
    -keystore $JAVA_HOME/jre/lib/security/cacerrts -file ca-root.der

Prepare agent configuration¶

The agent can be configured via container-mounted JSON files, or environment variables, or a mix of both. The access keys required to connect to Snowflake can be mounted from the host’s file system, supplied as container secrets, or as environment variables.

The following sections describe different configuration options, from the most straightforward, to the most comprehensive. Choose an approach based on the specifics of your infrastructure.

{
    "<data_source_name_1>": {
        "url": "jdbc:mariadb://<host>:<port>/[?<key1>=<value1>[&<key2>=<value2>]]",
        "username": "<mysql_db_username>",
        "password": "<mysql_db_password>"
    },
    "<data_source_name_2>": {
        "url": "jdbc:mariadb://<host>:<port>/[?<key1>=<value1>[&<key2>=<value2>]]",
        "username": "<mysql_db_username>",
        "password": "<mysql_db_password>"
    }
}

docker run -d \
   --restart unless-stopped \
   --name database-connector-agent \
   --volume </path/to/ssh/keys/directory>:/home/agent/.ssh \
   --volume </path/to/mariadb/jdbc/jar>:/home/agent/libs/mariadb-jdbc-driver \
   --volume </path/to/snowflake/json/file>:/home/agent/snowflake.json \
   --volume </path/to/datasources/json/file>:/home/agent/datasources.json \
   -m 6g \
   snowflakedb/database-connector-agent:latest

docker run \
  -e SNOWFLAKE_USERNAME="MYSQL_AGENT_USER" \
  -e SNOWFLAKE_APPLICATION_NAME="SNOWFLAKE_CONNECTOR_FOR_MYSQL" \
  -e SNOWFLAKE_ALLOWLIST_0_HOST="example_account.us-west-2.aws.snowflakecomputing.cn" \
  -e SNOWFLAKE_ALLOWLIST_0_PORT=443 \
  -e SNOWFLAKE_ALLOWLIST_0_TYPE="SNOWFLAKE_DEPLOYMENT" \
  ...

{
  "userName": "MYSQL_AGENT_USER",
  "applicationName": "SNOWFLAKE_CONNECTOR_FOR_MYSQL",
  "allowlist": [
  {
    "host": "example_account.us-west-2.aws.snowflakecomputing.cn",
    "port": 443,
    "type": "SNOWFLAKE_DEPLOYMENT"
  }
  ]
}

docker run \
  -e SNOWFLAKE_USERNAME="CONNECTOR_MYSQL_AGENT" \
  -e SNOWFLAKE_APPLICATION_NAME="CONNECTOR_MYSQL_INSTANCE" \
  -e SNOWFLAKE_ENFORCEDURL="example_account.us-west-2.aws.snowflakecomputing.cn:443" \
  ...

docker run \
  -e SNOWFLAKE_DATASOURCES_MYSQLDS1_URL="jdbc:mariadb://example.internal:3306/" \
  -e SNOWFLAKE_DATASOURCES_MYSQLDS1_USERNAME="example_user" \
  -e SNOWFLAKE_DATASOURCES_MYSQLDS1_PASSWORD="example_password" \
  ...

{
    "MYSQLDS1": {
        "url": "jdbc:mariadb://example.internal:3306/",
        "username": "example_user",
        "password": "example_password"
    }
}

Review optional configuration environment variables¶

The agent supports the following, optional settings, available by setting additional environment variables on the container:

SNOWFLAKE_PRIVATEKEYPATH

Specifies the absolute path to the file with the agent user’s private key. This is used when mounting your own private key to the container, usually via an orchestration system’s secret.

SNOWFLAKE_PRIVATEKEYPASSWORD

Specifies the password for agent user’s the private key. If you let the agent generate the keys, this password will be set on the private key. If you reuse existing keys, this password will be used to access the existing private key.

SNOWFLAKE_PRIVATEKEY

Specifies the content of the agent user’s private key. This can be set, when mounting the private key as a file in the container is not an option.

SNOWFLAKE_ENFORCEDURL

Specifies the URL to connect to Snowflake, overriding the agent’s own discovery mechanism. This is primarily used to connect to PrivateLink deployments.

JAVA_OPTS

Specifies additional Java settings or properties that will be passed to the agent’s process.

The most commonly used are:

• The -Xmx option to set the maximum Java heap size. Snowflake recommends setting this value to the amount of memory available to the container, minus 1GB.

  For example, if the container has 6GB of RAM available, set the following:

  JAVA_OPTS=-Xmx5g
  

  Copy

• When the connection from agent to Snowflake requires a proxy, set the following:

  JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port>
  

  Copy

• To bypass the proxy for some hosts or IP addresses, for instance, source databases, set the additonal http.nonProxyHosts property. Use a pipe symbol (|) to separate multiple host names. Use an asterisk (*) as a wildcard character.

  JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port>
    -Dhttp.nonProxyHosts='*.my_company.com|localhost|myorganization-myaccount.snowflakecomputing.cn|192.168.91.*'
  

  Copy

• To pass credentials for the proxy, set the http.proxyUser and http.proxyPassword system properties.

  JAVA_OPTS=-Dhttp.useProxy=true -Dhttp.proxyHost=<proxy-host> -Dhttp.proxyPort=<proxy-port>
    -Dhttp.proxyUser=<proxy-user> -Dhttp.proxyPassword=<proxy-pass>
  

  Copy

Set up PrivateLink connectivity¶

If you’re connecting to a PrivateLink deployment, you must provide the URL for the agent to connect to explicitly by setting the SNOWFLAKE_ENFORCEDURL environment variable.

To determine the PrivateLink URL of your account, you can either:

• Open the snowflake.json file that you obtained during the configuration process. Find the array named allowlistPrivatelink, and then the entry with the type of SNOWFLAKE_DEPLOYMENT.

• Use the SYSTEM$GET_PRIVATELINK_CONFIG function.

Understanding Snowflake access keys¶

The agent authenticates with Snowflake as a service account, created by the connector’s setup wizard in Snowsight, using a set of access keys. The setup wizard creates temporary access keys, and adds the private key to the snowflake.json file in a field named temporaryPrivateKey.

During its initial startup, the agent replaces these temporary keys by:

1. Generating a new set of access keys, and storing them under /home/agent/.ssh as database-connector-agent-app-private-key.p8 and database-connector-agent-app-public-key.pub inside the container. This directory should be mounted as an external, writable volume to the container, so that the keys persist when the container shuts down.

2. Altering its service account to use the new keys.

3. Removing the temporaryPrivateKey field from the snowflake.json file.

After the initial key replacement, the agent never rotates access keys.

You can use the keys generated by the agent. Or you can create your own set, alter the service account, and provide the private key to the agent.

The agent’s private key discovery order is:

1. Any key passed using the SNOWFLAKE_PRIVATEKEY environment variable. If this value is found, the connector will ignore the temporary key from snowflake.json.

2. Keys found on mounted volumes in /home/agent/.ssh/database-connector-agent-app-private-key.p8. If this file is found, the connector will ignore the temporary key from snowflake.json.

3. The value of the temporaryPrivateKey field from the snowflake.json file.

Accessing the health check endpoint¶

The agent exposes a HTTP endpoint with health information. You can use this endpoint when running the agent in an orchestration system to determine when the agent has fully launched and whether it is healthy. The endpoint is available under port 8080 and path /actuator/health.

To use the endpoint as a liveness probe in Kubernetes, add the following to your Pod configuration:

apiVersion: v1
kind: Pod
spec:
  containers:
  - ...
    livenessProbe:
      httpGet:
        path: /actuator/health
        port: 8080
      initialDelaySeconds: 5
      periodSeconds: 10