Ensure to use the Redshift Event handler as a downstream Event handler connected to the output of the S3 Event handler. The S3 Event handler loads files generated by the File Writer Handler into Amazon S3.

Redshift Event handler uses the COPY SQL to bulk load operation data available in S3 into temporary Redshift staging tables. The staging table data is then used to update the target table. All the SQL operations are performed in batches providing better throughput.

• Aggregation In Memory
  Before loading the operation data into S3, the operations in the trail file are aggregated. Operation aggregation is the process of aggregating (merging/compressing) multiple operations on the same row into a single output operation based on a threshold.
• Aggregation using SQL post loading data into the staging table
  In this aggregation operation, the in-memory operation aggregation need not be performed. The operation data loaded into the temporary staging table is aggregated using SQL queries, such that the staging table contains just one row per key.

The following operations are not supported by the Redshift Handler:

• DDL changes are not supported.
• Timestamp and Timestamp with Time zone data types: The maximum precision supported is up to microseconds, the nanoseconds portion will be truncated. This is a limitation we have observed with the Redshift COPY SQL.
• Redshift COPY SQL has a limitation on the maximum size of a single input row from any source is 4MB.

It is mandatory that the trail files used to apply to Redshift contain uncompressed UPDATE operation records, which means that the UPDATE operations contain full image of the row being updated.

If UPDATE records have missing columns, then such columns are updated in the target as null. By setting the parameter gg.abend.on.missing.columns=true, replicat can fail fast on detecting a compressed update trail record. This is the recommended setting.

Staging operation data from AWS S3 onto temporary staging tables and updating the target table occurs inside a single transaction. In case of any error(s), the entire transaction is rolled back and the replicat process will ABEND.

If there are errors with the COPY SQL, then the Redshift system table stl_load_errors is also queried and the error traces are made available in the handler log file.

• Connectivity issues to Redshift
  • Validate JDBC connection URL, user name and password.
  • Check if http/https proxy is enabled. Generally, Redshift endpoints cannot be accessed via proxy.
• DDL and Truncate operations not applied on the target table: The Redshift handler will ignore DDL and truncate records in the source trail file.
• Target table existence: It is expected that the Redshift target table exists before starting the apply process. Target tables need to be designed with primary keys, sort keys, partition distribution key columns. Approximations based on the column metadata in the trail file may not be always correct. Therefore, Redshift apply will ABEND if the target table is missing.
• Operation aggregation in-memory (gg.aggregagte.operations=true) is memory intensive where as operation aggregation using SQL(gg.eventhandler.name.aggregateStagingTableRows=true) requires more SQL processing on the Redshift database. These configurations are mutually exclusive and only one of them should be enabled at a time. Tests within Oracle have revealed that operation aggregation in memory delivers better apply rate. This may not always be the case on all the customer deployments.
• Diagnostic information on the apply process is logged onto the handler log file.
  • Operation aggregation time (in milli-seconds) in-memory:

INFO 2018-10-22 02:53:57.000980 [pool-5-thread-1] - Merge statistics ********START*********************************
INFO 2018-10-22 02:53:57.000980 [pool-5-thread-1] - Number of update operations merged into an existing update operation: [232653]
INFO 2018-10-22 02:53:57.000980 [pool-5-thread-1] - Time spent aggregating operations : [22064]
INFO 2018-10-22 02:53:57.000980 [pool-5-thread-1] - Time spent flushing aggregated operations : [36382]
INFO 2018-10-22 02:53:57.000980 [pool-5-thread-1] - Merge statistics ********END***********************************

• Stage and load processing time (in milli-seconds) for SQL queries

INFO 2018-10-22 02:54:19.000338 [pool-4-thread-1] - Stage and load statistics ********START*********************************
INFO 2018-10-22 02:54:19.000338 [pool-4-thread-1] - Time spent for staging process [277093]
INFO 2018-10-22 02:54:19.000338 [pool-4-thread-1] - Time spent for load process [32650]
INFO 2018-10-22 02:54:19.000338 [pool-4-thread-1] - Stage and load statistics ********END***********************************

• Stage time (in milli-seconds) will also include additional statistics if operation aggregation using SQL is enabled.
• Co-existence of the components: The location/region of the machine where replicat process is running, AWS S3 bucket region and the Redshift cluster region would impact the overall throughput of the apply process. Data flow is as follows: GoldenGate => AWS S3 => AWS Redshift. For best throughput, the components need to be located as close as possible.

Redshift apply relies on the upstream File Writer handler and the S3 Event handler.

Include the required jars needed to run the S3 Event handler in gg.classpath. See Using the S3 Event Handler. Redshift Event handler uses the Redshift JDBC driver. Ensure to include the jar file in gg.classpath as shown in the following example:

gg.classpath=aws_sdk_2.28.11/lib/:aws_sdk_2.28.11/third-party/lib/:./redshift-jdbc42-2.1.0.29.jar

The Redshift event handler uses COPY SQL to read staged files in Amazon Web Services (AWS) S3 buckets. The COPY SQL query may need authorization credentials to access files in AWS S3.

{
  "Version": "2012-10-17",
  "Statement": [
  {
   "Effect": "Allow",
   "Principal": {
   "Service": [
    "redshift.amazonaws.com"
   ]
  },
  "Action": "sts:AssumeRole"
 }
 ]
}