Connecting a Snowflake Database

CONTENTS

Home

Connect to Snowflake

Here is what you need to connect:

  • Read-only user (Username field)
  • Add the bipp IP address to your Network Policy.
  • Account URL received from Snowflake
  • Database connection information
  • Credentials

Create a Snowflake read-only User

bipp recommends using a read-only user for connecting to your Snowflake databases.

You must have the ACCOUNTADMIN role to add a new user and role. Refer to your Snowflake documentation.

On a Snowflake command line or shell, enter:

CREATE ROLE IF NOT EXISTS READ_ONLY;

Snowflake-CreateRole

GRANT privileges for the Warehouse, Database and Schema. Run the GRANT for each schema.

GRANT USAGE ON WAREHOUSE your_warehouse TO ROLE READ_ONLY;
GRANT USAGE ON DATABASE your_database TO ROLE READ_ONLY;
GRANT USAGE ON SCHEMA your_schema1 TO ROLE READ_ONLY;
GRANT USAGE ON SCHEMA your_schema2 TO ROLE READ_ONLY;

Grant SELECT on each of the tables in the schema you want accessible to the read-only user:

GRANT SELECT ON TABLE your_database.your_schema.your_table TO ROLE READ_ONLY;

If you want to Grant SELECT on all the tables in the schema:

GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO ROLE READ_ONLY;

Create the read-only user and grant the read-only role:

CREATE USER IF NOT EXISTS bipp_db_user PASSWORD = 'Secret@123' MUST_CHANGE_PASSWORD = FALSE DEFAULT_ROLE = READ_ONLY;
GRANT ROLE READ_ONLY TO USER bipp_db_user;
Assign your own user name and strong password for bipp_db_user and Secret@123.

Add the bipp IP address to your Network Policies

If you use Network Policies to restrict the IP access to your Snowflake instance, you need to add the bipp IP Address 34.123.9.91 to the policy. Refer to network policies for details.

There are two types of Snowflake network policies:

Account Level policies apply to all users unless the policy is overridden by a user level policy. Refer to the Snowflake documentation for information on modifying account level network policies to allow incoming connections from bipp’s IP address

User Level policies apply to assigned users, and override account level policies. If your organisation uses user level network policies, create a new policy to apply to the read-only user to allow incoming connections from bipp’s IP address. Refer to the Snowflake documentation for information on modifying user level network policies.

Use Direct Secure Connection as the Connection type when you create the datasource.

Create a Snowflake Datasource in bipp

  1. Select Datasources from the Home Navigation pane to open the Datasources page.

    Datasource Page

  2. Click New Datasource on the Datasources page.

    New Datasource

  3. Enter a Name. For example, TestSnowflake.

  4. Select Snowflake for SQL Dialect for your database. bipp creates and enforces the rules for the selected dialect.

    Datasource Snowflake

  5. Enter the information for your Snowflake database instance:

    • Url: full account URL received from Snowflake. For example, https://abc123.snowflakecomputing.com
    • AuthMech: authentication method for the connection. bipp only supports Basic authentication.
      • Username: user name for the connection. bipp recommends using a read-only user account.
      • Password: password for the user.
    • DbName: name of the database for the connection. The name is case-sensitive.
    • Schema: name of the schema for the connection. The name is case-sensitive.
    • Warehouse: name of the warehouse for the connection.
    • Connection: connection type
      • Direct Connection: database directly available over internet or your bipp platform is deployed on-premise in a private subnet.
      • Direct Secure Connection: secured encrypted TCP connection if your database is behind a firewall. Add the bipp IP address 34.123.9.91 to your network policy. TLS is not supported in this mode. The encrypted data is transmitted to prevent in-transit eavesdropping.
  6. Click Test to verify the datasource connectivity. If you have an error, fix the connection or credentials problem and click Test again.

  7. Click Create to add the datasource.

Congratulations! You have successfully created a Snowflake datasource in bipp.