Create Redshift Connections

This section provides information on how to enable Redshift connectivity and create one or more connections to Redshift sources. 

• Amazon Redshift is a hosted data warehouse available through Amazon Web Services. It is frequently used for hosting of datasets used by downstream analytic tools such as Tableau and Qlik. For more information, see https://aws.amazon.com/redshift/. 

Pre-requisites

Before you begin, please verify that your Alteryx® environment meets the following requirements:

1. S3 base storage layer: Redshift access requires use of S3 as the base storage layer, which must be enabled. See Set Base Storage Layer. 
   
2. Same region: The Redshift cluster must be in the same region as the default S3 bucket.
3. Integration: Your  Alteryx instance is connected to a running environment supported by your product edition.
   

   
   

4. Deployment: Designer Cloud Powered by Trifacta platform is deployed either on-premises or in EC2.

Permissions

Access to Redshift requires:

• Each user is able to access S3 
• S3 is the base storage layer 

If the credentials used to connect to S3 do not provide access to Redshift, you can create an independent IAM role to provide access from Redshift to S3. If this separate role is available, the Redshift connection uses it instead. 

Steps:

1. The IAM role must contain the required S3 permissions. See Required AWS Account Permissions.
2. The Redshift cluster should be assigned this IAM role. For more information, see https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html.

Limitations

• You can publish any specific job once to Redshift through the export window. See Publishing Dialog.

• When publishing to Redshift through the Publishing dialog, output must be in Avro or JSON format. This limitation does not apply to direct writing to Redshift. 
• Management of nulls:
  • Nulls are displayed as expected in the Designer Cloud application.
  • When Redshift jobs are run, the UNLOAD SQL command in Redshift converts all nulls to empty strings. Null values appear as empty strings in generated results, which can be confusing. This is a known issue with Redshift.

Create Connection

You can create Redshift connections through the following methods.

Create through application

Any user can create a Redshift connection through the application.

Steps:

1. Login to the application.
2. In the menu, click the Connections icon.
3. In the Create Connection page, click the Redshift connection card.

4. Specify the properties for your Redshift database connection:

   Property	Description
   Host	Hostname of the Redshift cluster NOTE: This value must be the full hostname of the cluster, which may include region information.
   Port	Port number used to access the Redshift cluster. Default is 5439.
   Connect String Options	Please insert any connection options as a string here. See below.
   Database	The Redshift database to which to connect on the cluster
   Credential Type	Options: Basic authentication with optional IAM role ARN: Basic authentication credentials specified in this window are used to connect to the Redshift database. Additional permissions may be governed by any ARN specified in the IAM role used for the account. Use this option if you are planning to specify a database username/password combination as part of the connection. IAM Role: Connection to Redshift is governed by the IAM role associated with the user's account.
   Username	Username with which to connect to the Redshift database
   Password	Password associated with the Redshift username
   IAM Role ARN for Redshift/S3 connectivity	(Optional) You can specify an IAM role ARN that enables role-based connectivity between Redshift and the S3 bucket that is used as intermediate storage during Redshift bulk COPY/UNLOAD operations. Example: arn:aws:iam::1234567890:role/MyRedshiftRole For more information, see Configure for EC2 Role-Based Authentication.

   
   For more information on the other options, see Create Connection Window.

5. Click Save. 

Enable SSL connections

To enable SSL connections to Redshift, you must enable them first on your Redshift cluster. For more information, see https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-ssl-support.html.

In your connection to Redshift, please add the following string to your Connect String Options:

;ssl=true

Save your changes.

Create via API

For more information, see  https://api.trifacta.com/ee/8.2/index.html#operation/createConnection

Reference

Connection URL

The properties that you provide are inserted into the following URL, which connects  Designer Cloud Enterprise Edition to the connection:

jdbc:redshift://<host>:<port>/<database><connect-string-options>

Connect string options

The connect string options are optional. If you are passing additional properties and values to complete the connection, the connect string options must be structured in the following manner:

;<prop1>=<val1>&<prop2>=<val2>;...

where:

• <prop> : the name of the property
• <val> : the value for the property

Delimiters:

• ; : any set of connect string options must begin and end with a semi-colon.
• ; : all additional property names must be prefixed with a semi-colon.
• = : property names and values must be separated with an equal sign (=).

Example

Access through AWS key-secret

The following example connection URL uses an AWS key/secret combination (IAM user) to access Redshift:

jdbc:redshift:iam://<redshift_clustername:region_name>:<port_number>/<database_name>?AccessKeyID=<access_key_value>&SecretAccessKey=<secret_key_value>&DBUser=<database_user_name>

where:

• <redshift_clustername>: the name of the redshift cluster
• <region_name>: region identifier where the cluster is located
• <port_number>: port number to use to access the cluster
• <database_name>: name of the Redshift database to which to connect
• <access_key_value>: identifier for the AWS key
• <secret_key_value>: identifier for the AWS secret
• <database_user_name>: user identifier for connecting to the database

Access through IAM role and temporary credentials

The following example connection URL uses an AWS/Key secret combination using temporary credentials:

jdbc:redshift:iam://<redshift_clustername:region_name>:<port_number>/<database_name>?AccessKeyID=<access_key_value>&SecretAccessKey=<secret_key_value>&SessionToken=<session_token>&DBUser=<database_user_name>

where:

• See previous.

• <session_token>: the AWS session token retrieved when using temporary credentials. The session token is requested by Designer Cloud Enterprise Edition when using AWS temporary credentials.For more information, see Configure AWS Per-User Auth for Temporary Credentials.

Driver Information

This connection uses the following driver:

• Driver name: com.amazon.redshift.jdbc41.Driver

• Driver version: com.amazon.redshift:redshift-jdbc41-no-awssdk:1.2.45.1069
• Driver documentation: https://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html

Troubleshooting

For more information, see https://docs.aws.amazon.com/redshift/latest/mgmt/troubleshooting-connections.html.

Use

For more information, see Redshift Browser.

For more information about interacting with data on Redshift, see Using Redshift.

Testing

Import a dataset from Redshift. Add it to a flow, and specify a publishing action. Run a job.