Skip to main content

Snowflake

Integrating Snowflake with Osano​

Connecting to Snowflake​

In order for Osano to discover data stored in your systems, we must connect to those data providers via an API. We require, at minimum, read-only access to establish a connection to these providers. Each provider you connect may require different information. Please see the linked vendor documentation for instructions on where and how to obtain this data.

Prerequisites​

  • Ensure you and the Snowflake user being modified have read access to the database or tables you want to discover.
    • The ALTER USER command requires account permissions higher than the user you are trying to alter.
  • Create your public-private key pair.
    • Create a folder to store your public-private key pair. e.g.) mkdir snowflake_keys
    • Go to the created folder in the terminal. cd snowflake_keys
    • Generate a private key. openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
    • Check the generated rsa_key.p8 file. It should look something like: 
      • -----BEGIN PRIVATE KEY-----
        MIIE6T...
        -----END PRIVATE KEY-----
    • Generate a public key. openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub (This example assumes the private key is in the file named rsa_key.p8)
      • Check the generated rsa_key.pub file. It should look something like:
      • -----BEGIN PUBLIC KEY-----
        MIIBIj...
        -----END PUBLIC KEY-----
    • Assign the public key to a Snowflake user
      • ALTER USER <user_name> SET RSA_PUBLIC_KEY='<public_key>'
      • Ensure this user_name is the same that will be used in the Osano connection.
    • Verify the user's public key fingerprint. Compare the following outputs: 
      • DESC USER jsmith; 
        SELECT TRIM((SELECT "value" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        WHERE "property" = 'RSA_PUBLIC_KEY_FP'), 'SHA256:');
      • openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64
      • The output should be the same.

Step by step guide​

  1. Access Data Mapping Settings:

    • Navigate to the Data Mapping page in the Osano application.
    • Select Sources from the left-hand navigation menu.

  2. Select Cloud DB as Source type

  3. From the available SSO sources, select Snowflake.

  4. Configure Snowflake Integration:

    • Follow the on-screen instructions to configure your Snowflake integration.
    • Enter your private key in the Private Key field. This should be the entire file contents of the rsa_key.p8 file.
    • Enter your Snowflake account name in the Account Name field. This is in the format of: <orgname>-<account_name>. Ensure there is a dash - between the orgname and account name, rather than a period ..
    • Enter your Snowflake username in the Username field. This should be the same username you used to assign the public key within Snowflake.
    • Enter your Snowflake database you want to be queried for data in the Database field.
    • Click Save.

  5. View Snowflake Integration:

    • Once saved, you will see your Snowflake integration in the list of configured SSO sources.
    • Your integration will include tables that already exist in your Snowflake environment.

  6. Click into the Discovered Data to view the tables that synced from Snowflake.

  7. Create a new data store:

    • To proceed with creating a new data store, click on Review.
    • Follow the prompts to create a new data store.

Troubleshooting​

  • If Osano's systems discover 0 tables in your database, without any error messages, please attempt the following:
    • In your Snowflake query console, ensure you are using the role assigned to Osano.
      • Attempt to do the following SQL statements, and ensure you are able to see the expected results:
        • SHOW SCHEMAS IN DATABASE <INSERT YOUR DATABASE NAME HERE>
        • SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE (t.TABLE_TYPE = 'BASE TABLE' OR t.TABLE_TYPE = 'VIEW') AND t.TABLE_SCHEMA = '<INSERT YOUR SCHEMA HERE>'
        • where <INSERT YOUR SCHEMA HERE> is the schema you want to discover within the database passed in to osano.
        • If you encounter any issues with the above queries, please double-check your role's permissions.
  • If you encounter any other issues during the integration process, please reach out to Osano support for assistance.