How to use IBM App Connect with Amazon Redshift

Connecting to Amazon Redshift

Complete the connection fields that you see in the App Connect Designer Catalog page or flow editor. If necessary, work with your Amazon Redshift administrator to obtain these values.

To connect to an Amazon Redshift endpoint from the App Connect Designer Catalog page for the first time, expand Amazon Redshift, then click Connect.

General considerations

Before you use App Connect Designer with Amazon Redshift, take note of the following considerations:

• Amazon Redshift connector supports dynamic discovery and is developed by using the JDBC framework.
• The metadata fields and their associated descriptions are dynamically loaded on runtime and displayed in a tabular format. The display, order, and case of these fields cannot be controlled, as they are presented as is from the endpoint.
• The table names in the catalog page also have dynamic behavior, and their display order cannot be controlled at the App Connect level.
• The target binding field has an optional field, Schema. When the Schema is not given, a public schema is used, and its tables are listed under that account. When you provide a valid value for the Schema field, it would be taken to list the tables.
• You can create as many tables, schemas, and databases in the Amazon Redshift UI.
• Some privileges or access grants need to be given to newly created tables, schemas, and databases to use them in the App Connect flows.
• All the commands or grants must be given at the table and schema level, which automatically enables you to access the new database created. The following are some examples of a set of commands:

  - GRANT USAGE ON SCHEMA "cctforappconnect" TO dev;
  - GRANT CREATE ON SCHEMA "cctforappconnect" TO dev;
  - GRANT SELECT on "cctdata"."cctforappconnect"."author" TO dev;
  - GRANT INSERT on "cctdata"."cctforappconnect"."author" TO dev;
  - GRANT UPDATE on "cctdata"."cctforappconnect"."author" TO dev;

  
  
  Tip: In the above grant statements, cctdata is the new database, cctforappconnect is the schema under that database, and the author is the table to which you are associating all the needed permissions. Here, dev is a database user to whom you are granting all these permissions.

  Ensure that you use awsuser as a super user to grant these permissions, as it has all the privileges to grant these permissions to any user created. For more information, see GRANT on the AWS documentation page.

• If limited permissions are given to a particular table, only those operations are allowed to be performed on that table. For example, if a table is being given only SELECT permissions, and if you try an INSERT or UPDATE operation, the operation fails with a permission error.

  Example of a sample error message:

  {
    "message": "The query execution on the \"redshift\" database failed with an error: \"[IBM App Connect][Redshift JDBC Driver][Redshift]permission denied for relation test111. \".",
    "action": "Take the corrective action for your database type and then retry the operation.",
    "detail": "",
    "url": "",
    "data": {
      "errorDetail": "{\"type\":\"SQLException\",\"message\":\"[IBM App Connect][Redshift JDBC Driver][Redshift]permission denied for relation test111. \",\"SQLState\":\"42501\",\"errorCode\":\"0\",\"state\":\"425010\",\"inserts\":[\"amazonredshift\",\"redshift\",\"[IBM App Connect][Redshift JDBC Driver][Redshift]permission denied for relation test111. \"]}"
    }
  }

  The above error is thrown at run time, and all the operations will still be visible to you on the catalog page even with limited permissions (for example, only SELECT has been granted for the table, but you are still able to see other operations (INSERT or UPDATE) on the catalog page).
• Amazon Redshift does not make the primary key a unique field, but a non null field as a result of which duplicate records are allowed.
• It is observed that for DELETE and UPDATE operations when multiple records match the where condition, all the records are impacted by the operation. For example, if there are 25 records with ID=10, and when you try to UPDATE with ID=10, all the 25 records are updated in the database and the count in the output shows the same. A similar behavior is seen in DELETE operations.
• For UPSERT operations, if where condition matches multiple records, the following error is returned:

  { "message": "The update or create action found multiple records.", "action": "Ensure that the 'where' expression on the update or create action matches a maximum of one record.", "detail": "The 'where' clause for the update or create action matched multiple records in the application, but it can only update or create one record at a time.", "url": "", "data": { "errorDetail": "{\"statusCode\":400,\"message\":\"There are multiple instances found. UpsertWithWhere Operation will not be performed!\"}" } }

• Amazon Redshift supports two data types, which have predefined functions:
  • GeometryType: GeometryType returns the subtype of an input geometry as a string.

    Syntax: GeometryType(geom)

  • HyperLogLog: The HLL_CREATE_SKETCH function returns an HLLSKETCH data type that encapsulates the input expression values.

    Syntax: HLL_CREATE_SKETCH (aggregate_expression)

    Note:

    • When you pass the function through the App Connect UI, it is taken as a STRING, and not a function. You can get errors in that condition.
    • Amazon Redshift UI expects both data type values in hexadecimal format, but note that App Connect does not convert those functions in hexadecimal format.

      In such scenarios, you need to convert the supported function into hexadecimal format and then pass it through the App Connect UI. For more information about the supported functions for these data types, see GeometryType and HLL_CREATE_SKETCH function on the AWS documentation page.

• Amazon Redshift (JDBC framework-based connector) supports additional driver properties that can be provided during connection in the Additional parameters field. For more information about the connection properties that are supported by the driver for Amazon Redshift and their default values, see Connection property descriptions.

• (General consideration) You can see lists of the trigger events and actions that are available on the Catalog page of the App Connect Designer.

  For some applications, the events and actions in the catalog depend on the environment and whether the connector supports configurable events and dynamic discovery of actions. If the application supports configurable events, you see a Show more configurable events link under the events list. If the application supports dynamic discovery of actions, you see a Show more link under the actions list.

• (General consideration) If you are using multiple accounts for an application, the set of fields that is displayed when you select an action for that application can vary for different accounts. In the flow editor, some applications always provide a curated set of static fields for an action. Other applications use dynamic discovery to retrieve the set of fields that are configured on the instance that you are connected to. For example, if you have two accounts for two instances of an application, the first account might use settings that are ready for immediate use. However, the second account might be configured with extra custom fields.

Events and actions

Amazon Redshift events

These events are for changes in this application that trigger a flow to start completing the actions in the flow.

Show more configurable events: Events that are shown by default are pre-configured by using optimized connectivity. More items are available after you configure events that can trigger a flow by polling this application for new or updated objects. For more information about configurable events, see Configuring polled events to trigger flows.

Amazon Redshift actions

Your flow completes these actions on this application.

Other available items are listed after you connect App Connect to Amazon Redshift.