By using AWS re:Post, you agree to the Terms of Use

Can Redshift copy function take partial CDC 'Change data capture' data ? Namely, only primary key(s) and any fields that have changed


We have a need to start streaming oracle golden gate changed data capture data into S3 then Redshift. However, the cdc data we received will be partial, namely, only primary key(s) and any fields that have changed. It will not a full record. Is it possible to stream these data into S3 and Redshift? can Redshift copy function take partially cdc data?

1 Answers

You can specify a comma-separated list of column names to load source data fields into specific target columns. The columns can be in any order in the COPY statement, but when loading from flat files, such as in an Amazon S3 bucket, their order must match the order of the source data.

When loading from an Amazon DynamoDB table, order doesn't matter. The COPY command matches attribute names in the items retrieved from the DynamoDB table to column names in the Amazon Redshift table. For more information, see Loading data from an Amazon DynamoDB table

The format for a column list is as follows.

COPY tablename (column1 [,column2, ...])

If a column in the target table is omitted from the column list, then COPY loads the target column's DEFAULT expression.

If the target column doesn't have a default, then COPY attempts to load NULL.

If COPY attempts to assign NULL to a column that is defined as NOT NULL, the COPY command fails.

If an IDENTITY column is included in the column list, then EXPLICIT_IDS must also be specified; if an IDENTITY column is omitted, then EXPLICIT_IDS can't be specified. If no column list is specified, the command behaves as if a complete, in-order column list was specified, with IDENTITY columns omitted if EXPLICIT_IDS was also not specified.

If a column is defined with GENERATED BY DEFAULT AS IDENTITY, then it can be copied. Values are generated or updated with values that you supply. The EXPLICIT_IDS option isn't required. COPY doesn't update the identity high watermark. For more information, see GENERATED BY DEFAULT AS IDENTITY.

answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions