Connecting a Snowflake Database
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
bipp recommends using a read-only user for connecting to your Snowflake databases.
On a Snowflake command line or shell, enter:
CREATE ROLE IF NOT EXISTS READ_ONLY;
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;
If you use Network Policies to restrict the IP access to your Snowflake instance, you need to add the bipp IP Address 220.127.116.11 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.
Select Datasources from the Home Navigation pane to open the Datasources page.
Click New Datasource on the Datasources page.
Enter a Name. For example, TestSnowflake.
Select Snowflake for SQL Dialect for your database. bipp creates and enforces the rules for the selected dialect.
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 18.104.22.168 to your network policy. TLS is not supported in this mode. The encrypted data is transmitted to prevent in-transit eavesdropping.
Click Test to verify the datasource connectivity. If you have an error, fix the connection or credentials problem and click Test again.
Click Create to add the datasource.
Congratulations! You have successfully created a Snowflake datasource in bipp.
IN THIS PAGE