In modern technology, there is a clear need to reuse objects and let the user configure each object once and repeatedly use it over and over again.
This is the basic assumption of the “Connection” configuration in Totango Customer Data Hub.
Connection to a data source is usually done by Ops or Data teams, and the main idea is that it will be configured once and you can reuse the same connection for several integrations.
Important note, the Data Warehouse integration family is a closed system, therefore, the data source connectors which are created can be re-used for only Data Warehouse integrations and not for other integration families.
Topics covered in this article:
- Creating Snowflake Connection
- Configuring Snowflake Connection
- Customer Data Hub Whitelisting
- SSL Encryption
Creating Snowflake Connection
- Go to your Totango instance and click on Global Settings > Data Management > Customer Data Hub > Click 'Data Warehouse' and select Snowflake
(note: you will need Totango admin privileges).
- Create a new connection (or review your existing connections)
Configuring Snowflake Connection
The Snowflake connector is a pure DB connection. It means that it is built as a connector and an SQL query which powers this connection.
Common information regarding the Snowflake data warehouse can be found here
Note: It is recommended to create a specific user for fetching data from DB with proper DB access permissions (e.g. user with only-read access to a specific schema or a table).
Creating a Snowflake connector include 2 steps:
- Create a read-only user with access to the desired schema
- Create connector in Totango
Create a Snowflake User
Follow these steps to create a read-only user:
- Connect to you Snowflake DB
- Make sure you use the SECURITYADMIN role (or higher).
Change roles, you can click your current role and select the role you need.
- Create read-only role:
create role totango_ro_role;
grant role totango_ro_role to role sysadmin;
- Ensure the default role of the Totango user is set to this read-only role
- Grant read-only role by privileges:
grant usage on database <your database name> to role totango_ro_role;
grant usage on schema <your database name>.<your schema name> to role totango_ro_role;
grant select on all tables in schema <your database name>.<your schema name> to role totango_ro_role;
grant usage on warehouse <your warehouse name> to role totango_ro_role;
Create Connector in Totango (Username/Password)
- Name: a meaningful connection name to the Snowflake connection, For example, “Company DWH - Customer data”
- Description: a meaningful description to help you and others understand the essence of this connection.
- Hostname: Hostname of your Snowflake instance. The hostname you can get from the browser URL when you are logged in to Snowflake web app. It must be a full domain name including the “snowflakecomputing.com”, For example, “dfr432245.us-west.snowflakecomputing.com”.
See the section below for more details.
- Username: username of the user you plan to give access to your DB for Totango App
- Password: password of your user
- Warehouse: The warehouse where this connector data should be fetched from
- Schema: The schema where this connector data should be fetched from
- Database: The database where this connector data should be fetched from
How To Find Snowflake Information?
Follow these steps to find it:
- Login any worksheet of your Snowflake data warehouse
- The hostname is part of the URL
- Warehouse, Database, and Schema appear on the right side of the Snowflake tab.
Create Connector in Totango (Key file)
See here the Snowflake documentation for Key Pair (Kry File) Authentication
$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
-----BEGIN ENCRYPTED PRIVATE KEY----- MIIE6TAbBgkqhkiG9w0BBQMwDgQILYPyCppzOwECAggABIIEyLiGSpeeGSe3xHP1 wHLjfCYycUPennlX2bd8yX8xOxGSGfvB+99+PmSlex0FmY9ov1J8H1H9Y3lMWXbL ... -----END ENCRYPTED PRIVATE KEY-----
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
-----BEGIN PUBLIC KEY----- MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAy+Fw2qv4Roud3l6tjPH4 zxybHjmZ5rhtCz9jppCV8UTWvEXxa88IGRIHbJ/PwKW/mR8LXdfI7l/9vCMXX4mk ... -----END PUBLIC KEY-----
alter user jsmith set rsa_public_key='MIIBIjANBgkqh...';
- Choose Snowflake connector
- Choose Key File
- Enter Name
- Enter Host Name
- Enter Username for which key files was altered
- Enter Path phrase if key file was encrypted
- Choose private key
Snowflake Connection Q & A
- Q: How does Totango connect to the Snowflake database?
A: Totango connects via JDBC (Java Database Connectivity) using snowflake-jdbc v3.12.4 driver.
- Q: JDBC supports a read-only connection mode, is that being used for the Snowflake connector?
A: Currently Snowflake does not support a read-only connection mode. Once they will do, it will be enabled in the Totango connection.
- Q: Can I change the Snowflake database port?
A: It is not the recommended practice to change the Snowflake port and Totango does not support custom port (support only default 443)
In Snowflake, the port can be changed using proxy servers. Read more.
- Q: Can I use Snowflake Shared Databases?
A: Yes. You just need to select the desired Shared Database from the list.
More info about Snowflake Shared Database.
Customer Data Hub Whitelisting
In the event that your network is behind a firewall, you will need to whitelist our servers so that we may retrieve information from your data warehouse.
Please follow the instructions in this Customer Data Hub IP Whitelisting article to configure your IP whitelist.
All data warehouse connectors support SSL encryption out-of-the-box (no need to configure anything to enable SSL encryption).
Totango connectors will use an SSL encrypted connection in case it is activated and use a non-SSL encrypted connection otherwise.
The implementation logic is as follows:
by default, SSL encryption is used during connecting, if the data warehouse server does not support SSL encryption, then the connection will using an unencrypted connection to stream data from the data warehouse.