How to: Getting started with the Amazon Redshift connector in Qlik Application Automation

[MarkGeurtsen]

This article is intended to get started with the Amazon Redshift connector in Qlik Application Automation.

Authentication and Authorization

To authenticate with Amazon Redshift, you will have to gather the following information:

Host: You can find this in the AWS Redshift console when you click on your cluster. Here one of the fields shows endpoint. Only keep the hostname part of the URL.
Database Name: The name of the database you have provided.
Username: Username that you use to connect to Amazon Redshift
Password: Password that you use to connect to Amazon Redshift

Available blocks:

Do Query: This block takes a string as parameter. Provide a SQL query to be executed against Redshift.

List tables: This block returns a list of all tables in your database.
Insert Record: Requires a table name to be given and a JSON object. The inputs will be transformed into an insert statement and executed.

Upsert Record: Requires a table name to be given as well as a key value pair to be compared against. The key value pair consists of a column and tests if the value matches. If a match is found, the fields provided in record will be updated. Otherwise it will perform an insert.

Update Record by One Field: Takes a table name as parameter and a key value pair to be compared against. All matches will be updated with the values provided in the Data object.

This example will update all records in table my_test where column1 equals value. Every value in column2 will be given the value newvalue.

List Records: Provide a table name, the columns you want to be returned. It is possible to filter the data through the where clause by providing a column name and value. Furthermore it is possible to implement paging inside a loop when offset and limit parameters are provided.

The example below will return a list of records from my_test table with all columns where column1 equals value1.

Insert Record: This block will insert a single row into the table provided by tableName. It takes a JSON object that will be inserted as the row.

Example:

Delete Record: Deletes records from a table. Provide the table name and a where condition to indicate which records have to be deleted:

Import S3 File: This block takes a file stored on S3 storage and imports it into Redshift. Further information will be added later to this article.

Insert Bulk: This block inserts multiple rows into a table. You have to specify the table name and provide a list of JSON objects.

Example:

Upsert Bulk: This block does an upsert of rows, identical to the Insert bulk block. A key has to be provided. If the value of the key of a record matches an already existing record then it will be updated, otherwise an insert will happen.

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.