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
• Configuring PostgreSQL Connection
• Configuring MSSQL Server Connection
• Configuring Google BigQuery Connection
• Customer Data Hub Whitelisting
• SSL Encryption

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 table_name.* 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 table_name.* 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 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
• 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 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:  

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.

 

Configure PostgreSQL Connection 

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

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

Create a PostgreSQL User

Follow these steps to create a read-only user:

1. Connect to your PostgreSQL DB

2. . To create a read-only user for a particular table/tables:

   CREATE ROLE 'totango_ro_user' WITH LOGIN PASSWORD password NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL ‘infinity’;

3. To give the user access to the database from any host: 
   

   GRANT SELECT ON table_name.* TO 'totango_ro_user';

4. 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 table_name.* to 'totango_ro_user'@'hostname or IP_address';

   More information about user creation can be found here.

 

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 PostgreSQL instance.
  
• 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
• Database: The database where this connector data should be fetched from

 

Important note

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

PostgreSQL Connection Q & A

• Q: How does Totango connect to the PostgreSQL database?
  A: Totango connects via JDBC (Java Database Connectivity) using PostgreSQL JDBC 42.2.12 driver.
• Q: JDBC supports a read-only connection mode, is that being used for the PostgreSQL connector?
  A: Yes. Totango uses a read-only connection mode. 
• Q: Can I change the PostgreSQL database port?
  A: Yes. you can define any port number.

 

 

Configure MSSQL Server Connection

The MSSQL Server connector is a pure DB connection. It means that it is built as a connector and an SQL query which powers this connection.

 

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 MSSQL Server connector includes 2 steps:

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

Create an MSSQL Server User

MS SQL doesn’t support a read-only user by default, so we should explicitly create it.
Follow these steps to create a read-only user:

1. Connect to your MSSQL Server DB
2. Create a user with authentication within Database (read more)

   CREATE USER ‘totango_ro_user’ WITH PASSWORD = ‘password’;

3. Create a role under your database with read-only authorization (read more):
   

   CREATE ROLE ‘role_name’ AUTHORIZATION 'totango_ro_user';

4. Give the user access to the database from any host (read more): 
   

   GRANT SELECT ON your_database.table_name TO 'totango_ro_user';

5. 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.table_name to 'totango_ro_user'@'hostname or IP_address';

   More information about user creation can be found here.

 

Create Connector in Totango 

 

• Name: a meaningful connection name to the MSSQL Server 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 MSSQL Server instance.
  
• 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
• Database: The database where this connector data should be fetched from

 

Important note

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

 

 

MSSQL Server Connection Q & A

• Q: How does Totango connect to the MSSQL Server database?
  A: Totango connects via JDBC (Java Database Connectivity) using JDBC 8.2 driver (read more).
  
  
• Q: JDBC supports a read-only connection mode, is that being used for the MSSQL Server connector?
  A: Yes. Totango Customer Data Hub is using a read-only mode on the connector level (we do not allow any queries with delete, insert, update, ...). We advise setting a read-only role for the user used to access the DB.
  
  
• Q: Can I change the MSSQL Server database port?
  A: Yes. you can define any port number.
  
  
• Q: Which MSSQL Server version are supported?
  A: Totango is connected to MSSQL Server via JDBC 8.2 driver. JDBC 8.2 driver supports these MSSQL Server versions: SQL Server 2012, Azure SQL Database, PDW 2008R2 AU3, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019.  
  
  
  
  

Configure Google BigQuery Connection

The Google BigQuery 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 Google BigQuery can be found here.

Creating a Google BigQuery connector includes 2 steps:

1. Create a New GCP service account and download a Google BigQuery key file 
2. Create connector in Totango

Create a New GCP service account and download a Google BigQuery key file

1. In the Cloud Console, go to the Create service account key page.
   Go to the Create Service Account Key page
2. Select a project
   
3. From the Service account list, select New service account.
   Service Account is a user with only access to applications and APIs. Read more.
4. In the Service account name field, enter a name.
5. From the Role list, select Project > Viewer   (Read access to resources which enable Totango connector to test the connection).
6. Select additional Role, select BigQuery > BigQuery Data Viewer (Access to only view datasets). 
   
   
7. Click Create to create a Google Service Account JSON Key File file that contains your key downloads to your computer.

 

Create Connector in Totango 

 

• Name: a meaningful connection name to the connection, For example, “Company DWH - Customer and Contacts Data”
• Description: a meaningful description to help you and others understand the essence of this connection.
• Google Service Account Key File: upload the key file downloaded.

 

 

Google BigQuery Connection Q & A

• Q: How does Totango connect to the Google BigQuery database?
  A: Totango connector is using Google BigQuery API.
  
  
• Q: Does the Google BigQuery connector supports a read-only connection mode?
  A: Yes. Totango Customer Data Hub is using a read-only mode on the connector level (we do not allow any queries with delete, insert, update, ...).
  This is part of the service account user roles described above.
  
  
• Q: Can I change the Google BigQuery database port?
  A: No, there is no database port.
  
  
• Q: Does Totango's Google BigQuery connector supports shared tables?
  A: Yes. you have access according to configured permissions.
  
  
• Q: Does GoogleBigQuery connector supports Cloud SQL federated tables or logical views?
  A: Yes, it supports federated queries. Read more here.
  

• Q: Does GoogleBigQuery connector supports querying Nested Objects or Array Objects?
  A: Yes, by using SQL JOIN as part of the query.
  
• Q: Does GoogleBigQuery connector supports querying partitioned tables?
  A: No. Currently, we do not support querying partitioned tables because it requires admin access to BigQuery. Read more here.
  
  

 

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.

 

 

SSL Encryption

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.