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 have administrator access to your Snowflake account.
- 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 namedrsa_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.
- Create a folder to store your public-private key pair. e.g.)
Step by step guide
Access Data Mapping Settings:
- Navigate to the Data Mapping page in the Osano application.
- Select Sources from the left-hand navigation menu.
Select Cloud DB as Source type
From the available SSO sources, select
Snowflake
.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.
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.
Click into the Discovered Data to view the tables that synced from Snowflake.
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.
- To proceed with creating a new data store, click on