In this tutorial you will learn how to build a native Snowflake push based connector. In the following steps we will cover what constitutes a connector, how to build and deploy it and how to build an application UI using Streamlit.

This connector consist of Java Agent and Native Application. Java Agent acts as an application which is close to the data source, it fetches data from the data source and pushes it to Snowflake.

overview.svg

Native Application

Only selected objects will be visible to customer who installed the app. See: docs.

Java Agent

Let's take a look at the structure of this connector.

├── Makefile
├── example-push-based-java-connector-agent
│    ├── build.gradle
│    └── src
├── example-push-based-java-connector-native-app
│    ├── environment.yml
│    ├── manifest.yml
│    └── scripts
│    │   ├── deploy.sql
│    │   └── install.sql
│    ├── setup.sql
│    └── streamlit_app.py
├── gradle
├── gradlew
├── gradlew.bat
├── imgs
├── integration-test
├── README.md
├── settings.gradle
└── sf_build.sh

Native Application module

Contains files which are needed to create a Snowflake Native App:

Java Agent module

Contains Java files that constitute the Agent application and gradle files that are needed to build this application.

sf_build.sh script

Simple script to collect all files needed to deploy Snowflake Native App into sf_build folder. This directory will be used in deployment script.

imgs directory

Images used in README file.

Example application logs various operations during runtime. By default, those logs are not stored anywhere. To enable log storing please refer to enable loging documentation.

Snowsql configuration

This quickstart uses some convenience scripts for running necessary commands. Those scripts use snowsql. Before proceeding you need to configure snowsql connection to your Snowflake account according to documentation.

Generating Pubic and Private Keys

Java Agent uses snowflake-ingestion-sdk library which uses key pair authentication. In order to set up the connection you need to generate Public and Private Keys. To achieve it, run the following commands:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

The commands will create 2 files with a public key (rsa_key.pub) and a private key (rsa_key.p8). The keys will be used in the following steps.

Configure user in Snowflake

Configure a public key for your user by running the following sql command in your Snowflake worksheet. Use the public key generated in the previous step. Key inside the query should not have any whitespaces and should be a one-line string.

ALTER USER <your_user> SET RSA_PUBLIC_KEY='<Your Public Key>';

If your user does not have a password configured, run the following command to set the password.

ALTER USER <your_user> SET PASSWORD = '<Your Password>' MUST_CHANGE_PASSWORD = FALSE;

The password will be needed for the Java Agent to connect to the Native Application using snowflake-jdbc.

Native application configuration

In order to create a native application you need to adjust the value in the Makefile script for:

You can also change the rest of the properties:

Those values will be used by all scripts used in this quickstart.

Note: After changing APP_NAME you will need to adjust native_application.database_name property in the Java agent configuration step below.

Java agent configuration

In order to build the Java Agent and connect it to Snowflake you need to edit the following properties in /example-push-based-java-connector-agent/src/main/resources/connector.properties:

Example file with all the necessary properties looks like this:

account=myaccount
user=myuser
role=accountadmin
warehouse=mywarehouse

native_application.database_name=EXAMPLE_PUSH_BASED_CONNECTOR_TEST_INSTANCE
native_application.schema_name=PUBLIC
destination_database.database_name=EXAMPLE_PUSH_BASED_CONNECTOR_DATA
destination_database.schema_name=PUBLIC

ingestion.host=myaccount.snowflakecomputing.com
ingestion.scheme=https
ingestion.port=443
ingestion.private_key="MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQCsm5SOTkt/I0K5(...)"
jdbc.password=mypassword
jdbc.url=jdbc:snowflake://myaccount.snowflakecomputing.com

upload.initial.record-count=100
upload.periodical.record-count=20
upload.periodical.interval-seconds=10
upload.scheduler.pool-size=3

When user starts the Java Agent application, it connects to the Native Application and runs the INIT_DESTINATION_DATABASE procedure. Then the CLI is launched and user can enable and disable resources using appropriate commands. When a resource is enabled, Java Agent performs the following steps:

In the meantime, on the Native App side, the merge task is invoked. It merges the data from the delta to the base table.

Simplified sequence diagram

simplified.svg

Detailed sequence diagram

detailed.svg

Overview

Build step for the app consist of:

  1. Creating a new sf_build directory on the local machine for the Native App artifacts
  2. Creating a new sf_build_java directory on local machine for the Java Agent artifacts
  3. Copying of the Agent.jar to the sf_build_java folder
  4. Copying of the manifest.yml to the sf_build folder
  5. Copying of the setup.sql to the sf_build folder
  6. Copying of the streamlit_app.py to the sf_build folder

The sf_build directory serves as the source of truth about the Native Application definition.

Building

To build the connector execute a convenience script:

make build

In this step we will deploy the connector to the Snowflake account.

Overview

Deployment step consists of:

  1. Creating a database and stage for the app artifacts
  2. Uploading the sf_build contents to the newly created stage
  3. Creating an application package using the data from the stage

Deploy the app

To deploy the connector execute a convenience script:

make deploy

In this step you will install the connector. The installation is encapsulated in a convenience script install.sql.

Overview

The installation step consists of:

  1. Creating a new application using the application package which was created in the previous step
  2. Granting the necessary privileges to the application

Running the installation script

To install the connector using the convenience script run the following command:

make install

To configure the connector and grant the rest of the necessary privileges log into Snowflake and go to the Apps tab.

Grant privileges

When accessing the Streamlit dashboard of the connector for the first time a permissions pop-up will be displayed. The requested privileges must be granted for the connector to work properly.

privileges1.png

Create a sink database

Agent that provides the data to the Native App requires a database to work. This database is created by the Native App. To create it just enter the database name in the input box and press Configure button.

configure1.png

To run the Java Agent run the following command:

make run_agent

This command runs the agent's command line interface. The following commands are available to use:

streamlit.png

You can check the Refresh automatically checkbox to enable a periodical refresh of the page - this way you will see rising charts when the data is being ingested.

Example-push-based-java-connector repository contains a module with an integration test.

Overview

This test checks if the whole application flow works as expected. It performs the following steps:

Running the test

The integration test can be run using a simple make command:

make test

Congratulations! You have successfully completed these Labs

In this guide, you have learned how to: