Overview

 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 onlyData Warehouse integrations and not for other integration families.

Topics covered in this article: 

• Creating Data Warehouse Connections
• Configuring MySQL Connection
• Configuring Amazon Redshift Connection
• Configuring Snowflake Connection

Creating Data Warehouse Connections

1. Go to your Totango instance and click on Global Settings > Data Management > Customer Data Hub > find the Data Warehouse source you wish to upload data from by searching or filtering connectors.
   
   (note: you will need Totango admin privileges). 
   
   
2. Create a new connection (or review your existing connections)
   
   
   
   
   

Configuring MySQL Connection

The MySQL 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 MySQL 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 MySQL connector include 2 steps:

1. Create a read-only user with access to the desired schema
2. Create connector in Totango
   
   

Create MySQL User 

Follow these steps to create a read-only user:

1. Connect to your MySQL DB
   
   
2. To give the user access to the database from any host:
   

   GRANT SELECT ON your_database.* to 'totango_ro_user'@'%' identified by 'A Secure Password of your choosing';

If you know the hostname or IP address of the host that the collector is will be installed on, type the following command:

GRANT SELECT ON your_database.* to 'totango_ro_user'@'hostname or IP_address' identified by 'A Secure Password of your choosing';

More information about user creation can be found here.

Create Connector in Totango

• Name: a meaningful connection name to the MySQL Database, For example, “Company DWH - Customer data”
• Description: a meaningful description to help you and others understand the essence of this connection.
• Type: select the MySQL connector from the list.
• Username: username of user you plane to give access to your DB for Totango App
• Password: password of your user
• Schema: The schema where this connector data should be fetched from
• Hostname: Your DB hostname
• Port: Enter the port your company uses for MySQL. By default, it is 3306. 

Important note

In case your company uses VPN to allow access to your MySQL database, the Totango system will not be able to access it. If you want the Totango system to retrieve data from a MySQL database, please create an accessible MySQL database outside your network and push only the relevant information to this MySQL database in a secure manner.

Configuring Amazon Redshift Connection

The Amazon Redshift 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 Amazon Redshift 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 an Amazon Redshift connector include 2 steps:

1. Create a read-only user with access to the desired schema
2. Create connector in Totango

Create Amazon Redshift User 

Follow these steps to create a read-only user:

1. Connect to your Amazon Redshift DB
   
   
2. Create a read-only group:
   

   CREATE GROUP totango_ro_group;
   

3. Create a user for Totango (assume read-only):
   

   CREATE USER totango_ro_user PASSWORD 'A Secure Password of your choosing';
   

4.  Add user to the read-only group:
   

   ALTER GROUP totango_ro_group ADD USER totango_ro_user;
   

5. Grant Usage permission to Read-Only Group to specific Schema:
   

   GRANT USAGE ON SCHEMA "your_schema" TO GROUP totango_ro_group;
   

6. Grant Select permission to Read-Only Group to specific Schema:
   

   GRANT SELECT ON ALL TABLES IN SCHEMA "your_schema" TO GROUP totango_ro_group;
   

7. Alter Default Privileges to maintain the permissions on new tables:
   

   ALTER DEFAULT PRIVILEGES IN SCHEMA "your_schema" GRANT SELECT ON TABLES TO GROUP totango_ro_group;
   

8.  Revoke CREATE Privileges:
   

   REVOKE CREATE ON SCHEMA "your_schema" FROM GROUP totango_ro_group;

Common information about defining and managing users in Amazon Redshift can be found here, read this article about creating a user in Amazon Redshift.

Create Connector in Totango

• Name: a meaningful connection name to the Redshift Schema, For example, “Company DWH - Customer data”
• Description: a meaningful description to help you and others understand the essence of this connection.
• Type: select the Redshift connector from the list.
• Username: username of the user you plan to give access to your DB for Totango App
• Password: password of your user
• Schema: The schema where this connector data should be fetched from
• Host name: Your Amazon Redshift hostname. The hostname can be found in this path:
  Amazon console -> Amazon Redshift -> Clusters -> Your cluster name -> Propetries -> Connection details.
  See the section below for more details.
• Port: Enter the port your company uses for Redshift. By default, it is 5439.

How To FInd Redshift Cluster Host Name?

Hostname is the address of your Amazon Redshift DB server.

Follow these steps to find it:  

1. Go to: Amazon console -> Amazon Redshift -> Clusters -> Your cluster name 
2. Select  <Your cluster name> 
3. Go to Propetries -> Connection details 
4. The hostname is located in the connection details section under Endpoint.
   Use the “Copy” button to copy the hostname.
   
   

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:

1. Create a read-only user with access to the desired schema
2. Create connector in Totango

Create a Snowflake User

Follow these steps to create a read-only user:

1. Connect to you Snowflake DB
   
   
2. Make sure you use the SECURITYADMIN role (or higher).
   
   
   Change roles, you can click your current role and select the role you need.
   
   
   
   
   
   
3. Create read-only role:
   

   create role totango_ro_role;
   grant role totango_ro_role to role sysadmin;

   
   
   
4. 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 

• 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 plane 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:  

1. Login any worksheet of your Snowflake data warehouse
2. The hostname is part of the URL
3. Warehouse, Database, and Schema appear on the right side of the Snowflake tab.

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.