Amazon Redshift is a hosted data warehouse project that is part of the larger cloud computing platform Amazon Web Services. The Amazon Redshift SSH Writeback connector allows you to easily and securely export your data from a Domo DataSet to the provided Amazon Redshift database using SSH tunneling. For more information about the Redshift API, visit their website. ( http://docs.aws.amazon.com/redshift/..._commands.html )
You configure your Domo-Redshift connection in the Data Center. This topic discusses the fields and menus that are specific to the Amazon Redshift SSH Writeback Connector user interface. General information for adding DataSets, setting update schedules, and editing DataSet information is discussed in Adding a DataSet Using a Data Connector.
To configure this Connector, you must have the following:
The hostname of the UNIX server you are SSH-tunneling through.
The SSH username and password for your UNIX account.
The SSH port number.
The SSH private key.
The Redshift database hostname or IP address.
The Redshift database name.
The username and password you use to log into your Redshift database.
The port number for the Redshift database.
Your AWS access key. It is available in the AWS Console in the Security Credentials section or under Users, if you are using IAM.
Your AWS secret key. It was provided when you created your Access Key. You can generate a new one in the AWS Console.
The name of the AWS S3 bucket.
The Client ID and Client Secret associated with your Domo Developer account.
The CA certificate text or URL path is required only if you select Certificate String or URL Path, respectively, in the Certificate Format menu.
To create a Domo client ID and client secret:
- Log into the Domo developer account.
- In the top right corner under My Account click New Client.
- Enter the application name and description.
- Provide the application scope by selecting the checkboxes for Data and User.
- Click Create.
- Once you have created a client, you can manage the client by clicking on Manage Client.
Your Client Secret will appear in the Manage Client section.
Configuring the Connection
This section enumerates the options in the Credentials and Details panes in the Amazon Redshift SSH Writeback Connector page. The components of the other panes in this page, Scheduling and Name & Describe Your DataSet, are universal across most Connector types and are discussed in greater length in Adding a DataSet Using a Data Connector.
This pane contains fields for entering credentials to connect to your Domo developer account as well as the Amazon Redshift database. The following table describes what is needed for each field:
|SSH Server Hostname||Enter the hostname of the UNIX server you are SSH-tunneling through.|
|SSH Username||Enter the SSH username for your UNIX account.|
|SSH Password||Enter the SSH password for your UNIX account.|
|SSH Port||Enter the SSH port number.|
|SSH Private Key||Enter the SSH private key.|
|Database Hostname||Enter the Redshift database hostname or IP address.|
|Database Name||Enter the Redshift database name.|
|Database Username||Enter the username you use to log into your Redshift database.|
|Database Password||Enter the password you use to log into your Redshift database.|
|Database Port||Enter the port number for the Redshift database.|
|Certificate Format||Select a certificate format. If you do not want to include a certificate, select No certificate. If you select Certificate text, you must paste the text for your certificate in the Certificate field. If you select URL path, you must enter the URL where your certificate is in the Certificate field.|
|Paste the text for your CA certificate or enter the URL where your certificate is located. This is optional. If you do not want to include a certificate, select No certificate in the Certificate Type menu.|
|Access Key||Enter your AWS access key. It is available in the AWS Console in the Security Credentials section or under Users, if you are using IAM.|
|Secret Key||Enter your AWS secret key. It was provided when you created your Access Key. You can generate a new one in the AWS Console.|
|Region||Select your Amazon S3 region.|
|Bucket||Enter the name of the AWS S3 bucket.|
|Domo Client ID||Enter your Domo client ID.|
|Domo Client Secret||Enter your Domo client secret.|
Once you have entered valid credentials, you can use the same account any time you go to set up a new Domo-Amazon Redshift SSH connection. You can manage connector accounts in the Accounts tab in the Data Center. For more information about this tab, see Managing User Accounts for Connectors.
This pane contains a number of fields for specifying your data and indicating where it's going.
|Input DataSet ID||Enter your Domo dataset ID(GUID) located in the dataset URL. You can find the ID by opening the details view for the DataSet in the Data Center and looking at the portion of the URL following datasources/. For example, in the URL https://mycompany.domo.com/datasources/845305d8-da3d-4107-a9d6-13ef3f86d4a4/details/overview, the DataSet ID is 845305d8-da3d-4107-a9d6-13ef3f86d4a4.|
|Table Name Source||Choose how you would like to name the Redshift table.
|Custom Table Name||Enter the name for the Redshift table.|
|Schema Name||Select the Schema to write the dataset data to.|
|Operation Type||Select whether you want to create a new table or update an existing table.|
|Create New Table||Select this option to create a new table for every execution. The table name will be the name specified in the Table Name Source field, plus a numeric counter.|
|Update an Existing Table||Select this option to update an existing table only if the table name matches the existing one in the Redshift Server; otherwise, the connector will create a new table in the first run.|
|Append Or Overwrite||Select how you like to update the database table. Note: The connector may create a new table if the table does not exist in the first run; then update the table as per the selected option in the subsequent execution/schedule run.|
|Append Data||The connector will append the fetched data to the existing table.|
|Overwrite With New Data||The connector will overwrite the existing data with newly fetched data to the existing table.|
For information about the remaining sections of the connector interface, including how to configure scheduling, retry, and update options, see Adding a DataSet Using a Data Connector.
What kind of credentials do I need to power up this Connector?
You need your Redshift SSH server hostname, port number, private key, username, and password as well as your database hostname, port number, database name, username, and password. You also need your AWS access key and secret key, AWS S3 bucket name, and your Domo Developer account credentials (client ID and Client secret).
Why can't I connect to my Redshift database?
Before you can connect to a Redshift database, you must also whitelist a number of IP addresses on your database server on the port you want to connect to. For the full list of IP addresses, see Whitelisting IP Addresses for Connectors.
Are there any API limits I should be aware of?
Limits depend on your server configuration.
- Make sure your authentication remains valid.
- Review the configuration to make sure that all required items have been selected.
- Review the Connector history for error messages.
- In rare cases, you may be requesting too much information and reaching API limitations or timeouts. If this is the case, you can review the history of the Connector run to see the error message and duration. If this is the case, you can reduce the number of accounts that are being pulled, choose a smaller number of metrics for the report that you are pulling, or reduce the timeframe that you are trying to pull.