Amazon Redshift

You can use Amazon Redshift as a data platform to land and store datasets in Qlik Cloud Data Integration.

For information about connecting to Amazon Redshift, see Create an Amazon Redshift connection.

Configuring a connection to Amazon Redshift also requires you to set up connectivity to an Amazon S3 cloud staging area where data and changes are staged before being applied and stored. For more information, see Amazon S3.

Considerations and limitations

• The Amazon Redshift connector does not allow using the "disable" SSL mode.

• The Amazon Redshift connector supports standard authentication (user name and password) only.

Qlik Data Gateway - Data Movement prerequisites

You need to fulfill the following prerequisites on the Data Movement gateway machine.

Driver setup

You can install the driver using the driver installation utility (recommended) or manually. Manual installation should only be attempted in the unlikely event that you encounter an issue with the driver installation utility.

Using the driver installation utility to install the driver

This section describes how to install the required driver. The process involves running a script that will automatically download, install and configure the required driver. You can also run scripts to update and uninstall the driver as needed.

Preparing the installation

• Make sure that Python 3.6 or later is installed on the Data Movement gateway server.

  Python comes preinstalled on most Linux distributions. You can check which Python version is installed on your system, by running the following command:

  python --version

Installing the driver

To download and install the driver:

1. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

2. Run the following command:

   Syntax:

   ./install redshift

   If the driver cannot be downloaded (due to access restrictions or technical issues), a message will be displayed instructing you where to download the driver and where to copy it on the Data Movement gateway machine. Once you have done that, run the install redshift command again.

   Otherwise, the EULA for the driver will be displayed.

3. Do one of the following:

   • Press [Enter] repeatedly to slowly scroll through the EULA.
   • Press the Spacebar repeatedly to quickly scroll through the EULA.
   • Press q to quit the license text and be presented with the EULA acceptance options.

4. Do one of the following:

   • Type "y" and press [Enter] to accept the EULA and begin the installation.
   • Type "n" and press [Enter] to reject the EULA and exit the installation.

   • Type "v" and press [Enter] to view the EULA again.

The driver will be installed.

Updating the driver

Run the update command if you want to uninstall previous versions of the driver before installing the provided driver.

To download and update the driver:

1. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

2. Run the following command:

   Syntax:

   ./update redshift

   If the driver cannot be downloaded (due to access restrictions or technical issues), a message will displayed instructing you where to download the driver and where to copy it on the Data Movement gateway machine. Once you have done that, run the install redshift command again.

   Otherwise, the EULA for the driver will be displayed.

3. Do one of the following:

   • Press [Enter] repeatedly to slowly scroll through the EULA .
   • Press the Spacebar repeatedly to quickly scroll through the EULA.
   • Press q to quit the license text and be presented with the EULA acceptance options.

4. Do one of the following:

   • Type "y" and press [Enter] to accept the EULA and begin the installation.
   • Type "n" and press [Enter] to reject the EULA and exit the installation.
   • Type "v" and press [Enter] to review the EULA from the beginning.

The old driver will be uninstalled and the new driver will be installed.

Uninstalling the driver

Run the uninstall command if you want to uninstall the driver.

To uninstall the driver:

1. If Data Movement gateway is being used to land data, stop all of your landing tasks. If a different Data Movement gateway is being used to perform ELT processes on the target, stop all related tasks (Data Registration, Storage, Transformations, and Data Marts). If a single Data Movement gateway is being used for landing data and performing ELT tasks on the target, then stop all of the aforementioned tasks.

2. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

3. Run the following command:

   Syntax:

   ./uninstall redshift

   The driver will be uninstalled.

Manually installing the driver

You should only attempt to install the driver manually if the automated driver installation did not complete successfully.

1. Stop the Data Movement gateway service:

   sudo service repagent stop

2. Optionally, confirm that the service has stopped:

   sudo service repagent status

   The output should be as follows:

   not running: /opt/qlik/gateway/movement/bin/agentctl -d /opt/qlik/gateway/movement/data service host

3. Download and install the Linux SQL client tools necessary to connect to the Amazon Redshift cluster. Qlik Data Gateway - Data Movement requires Amazon Redshift ODBC Driver (x64) 1.4.65.1000 only.

   Download the driver from:

   https://s3.amazonaws.com/redshift-downloads/drivers/odbc/1.4.65.1000/AmazonRedshiftODBC-64-bit-1.4.65.1000-1.x86_64.rpm

   After installing the ODBC driver, edit the /etc/odbcinst.ini file as follows:

   [ODBC Drivers]

   Amazon Redshift (x64)=Installed

   And:

   [Amazon Redshift (x64)]

   Description=Amazon Redshift ODBC Driver (64-bit)

   Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

4. Start the Data Movement gateway service:

   sudo service repagent start

5. Optionally, confirm that the service has started:

   sudo service repagent status

   The output should be as follows:

   running: /opt/qlik/gateway/movement/bin/agentctl -d /opt/qlik/gateway/movement/data service host

For a list of drivers supported by Amazon Redshift, see http://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-connection.html.

Port

Port 5439 (Amazon Redshift Cluster) needs to be opened for outbound communication.

CA certificate

Data Movement gateway connects to AWS using SSL. This requires an appropriate CA certificate to reside on the Data Movement gateway machine; otherwise, the connection will fail. The purpose of the CA certificate is to authenticate the ownership of the AWS server certificate.

Make sure that the required CA certificate exists in the following location on the Linux machine:

/etc/pki/tls/certs/ca-bundle.crt

If it does not exist, the simplest solution is to copy the certificates bundle from another Linux machine.

Required permissions

Permissions required for landing data to Amazon S3

• You must have an Amazon S3 bucket that is accessible from the Data Movement gateway machine.

  For information on signing up for Amazon S3, see http://aws.amazon.com/s3/.

• Bucket access credentials: Make a note of the bucket name, access key and secret access key - you will need to provide them in the Amazon S3 connector settings.

• Bucket access permissions: The following bucket access permissions are required:

  {
  	"Version": "2023-10-17",
  	"Statement": [
  	    {
  	     "Sid": "Stmt1497347821000",
  	     "Effect": "Allow",
  	     "Action": [
                  "s3:GetBucketLocation",
                  "s3:ListBucket"
              ],
              "Resource": [
                  "arn:aws:s3:::YOUR_BUCKET_NAME"
              ]
          },
          {
              "Sid": "Stmt1497344984000",
              "Effect": "Allow",
              "Action": [
                  "s3:PutObject",
                  "s3:GetObject",
                  "s3:DeleteObject"
              ],
              "Resource": [
                  "arn:aws:s3:::YOUR_BUCKET_NAME/target_path",
                  "arn:aws:s3:::YOUR_BUCKET_NAME/target_path/*"
              ]
          }
      ]
  }
  

Where YOUR_BUCKET_NAME is the name of your bucket and target_path is the intended location of the target files in your bucket.

Permissions required on the Redshift database

Grant the following permissions on the Redshift database:

• Grant COPY
• Grant INSERT
• Grant UPDATE
• Grant DELETE
• Grant SELECT
• Create Schema (only required if schemas do not exist and you want Replicate to create them)
• Grant CREATE TABLE
• Grant ALTER TABLE
• Grant DROP TABLE

General prerequisites and considerations

• If your data source has 4-byte emoji characters, use a transformation to convert the data type from WSTRING(n ) to WSTRING ( n*2 ).

• Make sure the time and time zone settings on the Data Movement gateway machine are accurate. This is required in order to ensure:

  • Proper synchronization of Full Load and CDC tasks
  • Correlation of the transaction log time with the actual time

Data types

The following table shows the Amazon Redshift data types that are supported when using Qlik Cloud and the default mapping from Qlik Cloud data types.