Creating an Amazon Redshift target node

Permissions needed

AWS Glue Studio jobs using Amazon Redshift data target require additional permissions. For more information on how to add permissions to ETL jobs, see Review IAM permissions needed for ETL jobs.

The following permissions are needed in order to use an Amazon Redshift connection.

• redshift-data:ListSchemas

• redshift-data:ListTables

Adding an Amazon Redshift target node

To create a a Amazon Redshift target node:

1. Choose an existing Amazon Redshift table as the target, or enter a new table name.

2. When you use the Data target - Redshift target node, you can choose from the following options:

   • APPEND – If a table already exists, dump all the new data into the table as an insert. If the table doesn't exist, create it and then insert all new data.

     Additionally, check the box if you want to update (UPSERT) existing records in the target table. The table must exist first, otherwise the operation will fail.

   • MERGE – AWS Glue will update or append data to your target table based on the conditions you specify.

     Note

     To use the merge action in AWS Glue, you must enable Amazon Redshift merge functionality. For instructions on how to enable merge for your Amazon Redshift instance, see MERGE (preview) .

     Choose options:

     • Choose keys and simple actions – choose the columns to be used as matching keys between the source data and your target data set.

       Specify the following options when matched:

       • Update record in your target data set with data from source.

       • Delete record in your target data set.

       Specify the following options when not matched:

       • Insert source data as a new row into your target data set.

       • Do nothing.

     • Enter custom MERGE statement – You can then choose Validate Merge statement to verify that the statement is valid or invalid.

   • TRUNCATE – If a table already exists, truncate the table data by first clearing the contents of the target table. If truncate is successful, then insert all data. If the table doesn't exist, create the table and insert all data. If truncate is not successful, the operation will fail.

   • DROP – If a table already exists, delete the table metadata and data. If deletion is successful, then insert all data. If the table doesn't exist, create the table and insert all data. If drop is not successful, the operation will fail.

   • CREATE – Create a new table with the default name. If table name already exist, create a new table with a name postfix of job_datetime to the name for uniqueness. This will insert all the data into the new table. If the table exists, the final table name will have the postfix appended. If the table doesn’t exist, a table will be created. In either case, a new table will be created.