Tutorial: Snowflake Native SDK for Connectors example Java connector¶

Prerequisites¶

Before getting started please make sure that you meet the following requirements:

• Basic knowledge of Java

• Basic knowledge of Snowflake Native Apps

• Basic knowledge of Streamlit UI

• Access to a Snowflake account with an ACCOUNTADMIN role

• GitHub account, which can create GitHub apps

• MacOS or Linux machine to build the project and run deployment scripts

Java installation¶

Snowflake Native SDK for Connectors requires Java LTS (Long-Term Support) version 11 or higher. If the minimum required version of Java is not installed on your machine, you must install either Oracle Java or OpenJDK.

Repository cloning¶

Clone the connectors-native-sdk repository to your machine.

Snowflake CLI configuration¶

The Snowflake CLI tool is required to build, deploy and install the connector. If you do not have Snowflake CLI on your machine - install it as per instructions available here.

After the tool is installed - you need to configure a connection to Snowflake in your configuration file.

If you do not have any connections configured - create a new one named native_sdk_connection. You can find an example connection in the deployment/snowflake.toml file.

If you already have a connection configured and would like to use it with the connector - use its name instead of native_sdk_connection whenever this connection is used in this tutorial.

Connectors Native SDK Java¶

The connectors-native-sdk-java directory contains all the Snowflake Native SDK for Connectors Java code, with unit and integration tests for the SDK components. Because of the nature of Native Apps inside Snowflake, this means not only Java code, but also SQL code, which is necessary to create a working application. The definitions of the database objects can be found inside src/main/resources directory. Those files are used while creating an application to customize which objects will be available inside the application. In the example connector we use the all.sql file, which creates objects for all available features. This file will be executed during the installation process of the application instance.

Connectors Native SDK Test Java¶

The connectors-native-sdk-test-java directory contains source code of a helper library used in unit tests, e.g. objects used to mock particular components, custom assertions etc. Those files are not a part of the connector application.

Example Java GitHub connector¶

The actual example connector is located inside examples/connectors-native-sdk-example-java-github-connector directory. The app/ directory contains all the files needed to run the Native App:

• The app/streamlit/ directory contains source files necessary to run the Streamlit UI of the connector.

• The setup.sql file is run during the application installation and is responsible for creating the necessary database objects. This file will execute the all.sql file mentioned before, as well as some custom sql code.

• The manifest.yml file is the manifest of the Native App. It is required to create an application package and then the application instance itself. This file specifies application properties, as well as permissions needed by the application.

Additionally the examples/connectors-native-sdk-example-java-github-connector directory contains the src/ subdirectory, which contains custom connector logic, such as implementation of the required classes and customizations of the default SDK components.

Connectors Native SDK Template¶

A template Gradle Java project which uses Snowflake Native SDK for Connectors as a dependency to help you quickly build a new connector. You can read more about in Tutorial: Snowflake Native SDK for Connectors Java connector template.

Build the connector¶

Building a connector created using Snowflake Native SDK for Connectors is a bit different from building a typical Java application. There are some things which must be done besides just building the .jar archives from the sources. Building the application consists of the following steps:

1. Copying custom internal components to the build directory

2. Copying SDK components to the build directory

Deploy the connector¶

To deploy a Native App an application package needs to be created inside Snowflake. After that all the files from the sf_build directory need to be uploaded to Snowflake.

Please note - for development purposes version creation is optional, an application instance can be created directly from staged files. This approach allows you to see changes in most of the connector files without recreating the version and application instance.

The following operations will be performed:

1. Create a new application package, if it does not already exist

2. Create a schema and file stage inside the package

3. Upload files from the sf_build directory to the stage (this step may take some time)

To deploy the connector execute the command: snow app deploy --connection=native_sdk_connection.

For more information about the snow app deploy command see snow app deploy.

The created application package will now be visible in the App packages tab, in the Data products category, in the Snowflake UI of your account.

Install the connector¶

Installation of the application is the last step of the process. It creates an application from the application package created previously.

To install the connector execute the command: snow app run --connection=native_sdk_connection.

For more information about the snow app run command see snow app run.

The installed application will now be visible in the Installed apps tab, in the Data products category, in the Snowflake UI of your account.

Update connector files¶

If at any point you wish to modify any of the connector files - you can easily upload the modified files into the application package stage. The upload command depends on which files were updated.

Before any of the update commands are run - you have to copy the new files of your connector to the sf_build directory by running: ./gradlew copyInternalComponents

Privileges¶

Application requires two account level permissions to operate: CREATE DATABASE and EXECUTE TASK.

The first privilege is needed to create a destination database for the ingested data. This database should be created outside the application, so that the ingested data can be left intact if the application is uninstalled. However, this example does not support this feature, a new database is always created.

The second privilege is needed to schedule periodic tasks that will fetch the data from GitHub and save it in the destination database.

Granting those privileges can be done using the security tab or by pressing the Grant privileges button in the connector configuration screen. The latter one will result in a popup appearing on the screen.

Warehouse reference¶

The connector requires a warehouse to run and schedule ingestion tasks. Application will use the warehouse through a reference. Warehouse reference is defined in the manifest.yml file:

references:
  - WAREHOUSE_REFERENCE:
      label: "Warehouse used for ingestion"
      description: "Warehouse which will be used to schedule ingestion tasks"
      privileges: [USAGE]
      object_type: WAREHOUSE
      register_callback: PUBLIC.REGISTER_REFERENCE

The reference can set using the security tab, the same as the privileges above, or by pressing the Choose warehouse button.

Destination database and schema¶

As mentioned before, the connector requires a database to store the ingested data. This database will be created, during a later step, with the schema specified by the user. Name of the database is up to the user, as long as the provided database does not already exist.

The completed connector configuration screen will look similar to this one:

Permission SDK setup¶

OAuth2 authentication requires a security integration, secret and external access integration to be created in the user’s account. Our connector uses the Native Apps Permission SDK to request the creation of those objects.

References for the external access integration and secret, which are needed by the connector, are defined in the manifest.yml file:

references:
  - GITHUB_EAI_REFERENCE:
      label: "GitHub API access integration"
      description: "External access integration that will enable connection to the GitHub API using OAuth2"
      privileges: [USAGE]
      object_type: "EXTERNAL ACCESS INTEGRATION"
      register_callback: PUBLIC.REGISTER_REFERENCE
      configuration_callback: PUBLIC.GET_REFERENCE_CONFIG
  - GITHUB_SECRET_REFERENCE:
      label: "GitHub API secret"
      description: "Secret that will enable connection to the GitHub API using OAuth2"
      privileges: [READ]
      object_type: SECRET
      register_callback: PUBLIC.REGISTER_REFERENCE
      configuration_callback: PUBLIC.GET_REFERENCE_CONFIG

In addition, a special procedure needs to be added in the setup.sql file. It is referenced in the configuration_callback property for each of the references presented above:

CREATE OR REPLACE PROCEDURE PUBLIC.GET_REFERENCE_CONFIG(ref_name STRING)
    RETURNS STRING
    LANGUAGE SQL
    AS
        BEGIN
            CASE (ref_name)
                WHEN 'GITHUB_EAI_REFERENCE' THEN
                    RETURN OBJECT_CONSTRUCT(
                        'type', 'CONFIGURATION',
                        'payload', OBJECT_CONSTRUCT(
                            'host_ports', ARRAY_CONSTRUCT('api.github.com'),
                            'allowed_secrets', 'LIST',
                            'secret_references', ARRAY_CONSTRUCT('GITHUB_SECRET_REFERENCE')
                        )
                    )::STRING;
                WHEN 'GITHUB_SECRET_REFERENCE' THEN
                    RETURN OBJECT_CONSTRUCT(
                        'type', 'CONFIGURATION',
                        'payload', OBJECT_CONSTRUCT(
                            'type', 'OAUTH2',
                            'security_integration', OBJECT_CONSTRUCT(
                                'oauth_scopes', ARRAY_CONSTRUCT('repo'),
                                'oauth_token_endpoint', 'https://github.com/login/oauth/access_token',
                                'oauth_authorization_endpoint', 'https://github.com/login/oauth/authorize'
                            )
                        )
                    )::STRING;
                ELSE
                    RETURN '';
            END CASE;
        END;

For the external access integration reference the procedure provides:

• host_ports - hostnames of the external data source, which will be accessed during ingestion

• secret_references - array of names of references to OAuth secrets

• allowed_secrets - LIST, telling the Permission SDK to use secrets specified in the secret_references field

For the secret reference the procedure provides:

• type - OAUTH2 in case of our secret

• security_integration - properties of the created security integration:

  • oauth_scopes - a list of OAuth scopes requested by the connector (if using a GitHub app - this field is optional)

  • oauth_token_endpoint - endpoint from which the refresh and access token will be acquired

  • oauth_authorization_endpoint - endpoint to which the authorization request will be sent

GitHub app setup¶

The next step is the setup of a GitHub app in the user’s account. This app will be used to grant limited access to the account, so that data can be ingested.

The first step is to press the Request access button in the connector UI.

The first screen allows you to review the endpoints, for which external connectivity will be allowed.

After pressing Next - you will see a second screen. Select OAuth2 to create a new integration and secret, and copy the provided redirect URL - it will contain your organization name and the region of your account.

Next go to your GitHub account settings page, then into Developer settings > GitHub Apps and press the New GitHub App button:

1. Enter the name and homepage URL of your app

2. Paste the redirect URL you copied into the Callback URL field

3. Make sure the Expire user authorization tokens option is selected

4. Make sure the Request user authorization (OAuth) during installation is not selected

5. If you do not need it - deselect the Active option in the Webhook section

6. Select the permissions needed for the connector to work:

   1. Repository permissions > Issues with the Read-only access

   2. Repository permissions > Metadata with the Read-only access

7. If the app will only be used by you, with this example connector - it is best to select Only on this account in the installation access section

After the app is created - press the Install app option in the left sidebar and install the application in your account. You can choose which repositories the app (and by extension the connector) will be able to access. Without this installation - the connector will only be able to access public repositories.

OAuth integration setup¶

After installation return to your GitHub app and generate a new client secret. Make sure to copy it immediately, as it will not be shown again. Paste the client secret in the OAuth configuration popup of your connector. Finally, copy the client ID (not app ID) of your application and also paste it in the OAuth configuration popup of your connector.

After pressing Connect a GitHub window will pop up, asking you for app authorization on your GitHub account. After authorizing - you will be automatically redirected back to the connector UI. After successful authorization (it may take a couple seconds to finish and close the popup) the page will be populated with the IDs of external access integration and secret references.

Pressing the Connect button will trigger the TEST_CONNECTION procedure inside the connector. This procedure will try to access the GitHub API octocat endpoint, to check if external connectivity was configured correctly, and the OAuth access token obtained correctly.

When the test succeeds - application will proceed into the finalization step.