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 Amazon Redshift Connection
- Configuring Amazon Redshift Connection
- Customer Data Hub Whitelisting
- SSL Encryption
Creating Amazon Redshift Connection
- Go to your Totango instance and click on Global Settings > Data Management > Customer Data Hub > Click 'Data Warehouse' and select Amazon Redshift
(note: you will need Totango admin privileges).
- Create a new connection (or review your existing connections)
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:
- Create a read-only user with access to the desired schema
- Create connector in Totango
Create Amazon Redshift User
Follow these steps to create a read-only user:
- Connect to your Amazon Redshift DB
- Create a read-only group:
CREATE GROUP totango_ro_group;
- Create a user for Totango (assume read-only):
CREATE USER totango_ro_user PASSWORD 'A Secure Password of your choosing';
- Add user to the read-only group:
ALTER GROUP totango_ro_group ADD USER totango_ro_user;
- Grant Usage permission to Read-Only Group to specific Schema:
GRANT USAGE ON SCHEMA "your_schema" TO GROUP totango_ro_group;
- Grant Select permission to Read-Only Group to specific Schema:
GRANT SELECT ON ALL TABLES IN SCHEMA "your_schema" TO GROUP totango_ro_group;
- 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;
- Revoke CREATE Privileges:
REVOKE CREATE ON SCHEMA "your_schema" FROM GROUP totango_ro_group;
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:
- Go to: Amazon console -> Amazon Redshift -> Clusters -> Your cluster name
- Select <Your cluster name>
- Go to Propetries -> Connection details
- The hostname is located in the connection details section under Endpoint.
Use the “Copy” button to copy the hostname.
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.