Intro
Amazon Redshift is a hosted data warehouse project that is part of the larger cloud computing platform Amazon Web Services. Redshift handles analytics workloads on large scale DataSets stored by a column-oriented DBMS principle. With the Amazon Redshift Upsert Connector you have the ability to schedule your data using the merge (upsert) option. You connect to your Amazon Redshift Upsert Account in the Data Center. This topic discusses the fields and menus that are specific to the Amazon Redshift Upsert connector interface. General information for adding DataSets and editing DataSet information is discussed in Adding a DataSet Using a Data Connector.Prerequisites
To connect to Amazon Redshift so you can begin creating Amazon Redshift Upsert DataSets, you must have the following:- JDBC Driver
- The host name for the Redshift database
- The database name for the Redshift database
- Your Redshift username and password
- The port number of your Redshift database
- The format of your certificate. This must match what you enter in the Certificate field.
- The text for your CA certificate or enter the URL where your certificate is located. By default, no certificate is required.
Whitelisting
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.Connecting To Your Amazon Redshift Upsert Account
This section enumerates the options in the Credentials and Details panes in the Amazon Redshift Upsert 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.Credentials Pane
This pane contains fields for entering credentials to connect to your Amazon Redshift Upsert account. The following table describes what is needed for each field:Field | Description |
|---|---|
JDBC Driver | Select the JDBC driver to use. |
Host | Enter the host name for the SQL database. If you do not know the host name, contact your Amazon Redshift Database Administrator. |
Database | Enter the name of the SQL database. If you do not know the database name, contact your Amazon Redshift Database Administrator. |
Username | Enter your Redshift username. If you do not know your username, contact your Amazon Redshift Database Administrator. |
Password | Enter your Redshift password. If you do not know your username, contact your Amazon Redshift Database Administrator. |
Database Port | Enter the port number of your Redshift database. If you do not know the port number, contact your Amazon Redshift Database Administrator. |
Certificate Format | Select a certificate format. This must match what you enter in the Certificate field. If you are not aware of a special certificate, select no certificate. |
Certificate | Paste the text for your CA certificate or enter the URL where your certificate is located. By default, no certificate is required. |
Details Pane
This pane contains various options for specifying the data you want to pull into Domo| Menu | Description |
|---|---|
| Query Type | Select a query type Query: Regular SQL query without parameters. Query Parameter: SQL query with parameters. |
| Query | Enter the SQL query to use in selecting the data you want. For example, SELECT * FROM Employee |
| Fetch Size | Enter the fetch size for memory performance. If it is blank, the default value will be 1000. If it throws out of memory for a value, decrease fetch size. |
| Query Parameter | Enter the query parameter value. It is the initial value for query parameter. The last run date is optional and by default is ‘02/01/1700’ if it is not provided. For example, !{lastvalue:_id}!=1,!{lastrundate:start_date}!=02/01/1944 |
| Validate Type | Select the validation type No Validation: It will not do any validation. Validate Schema: It will validate the previous schema with current schema, and it will fail the job run if the schema is not the same. |
| Validate By | Select the validation cases Column Case Sensitive: If previous and current column names are the same but it is different by case sensitivity, it will not consider them as the same column name. Column by Order: If previous columns and current columns are not in the same order, it will not consider them as the same column name. |
| Boolean Format | Select the boolean format for the data to return 1 = true, 0 = false: It will return 1 for true, otherwise 0 for all the boolean data column. t = true, f = false: It will return t for true, otherwise f for all the boolean data column. T = true, F = false: It will return T for true, otherwise F for all the boolean data column. true = true, false = false: It will return true for true, otherwise false for the boolean data column. |
| Timeout Query (Optional) | Enter the desired query timeout value. |
Scheduling
The unique scheduling feature of this connector is the ability to merge (upsert) your data. This method will replace all updated rows, while any new rows will be appended.