Destination - Amazon Redshift

Setting items

STEP1 Basic settings

item name	indispensable	default value	Contents
Amazon Redshift connection information	Yes	-	Select from the pre-registered Amazon Redshift connection information that has the necessary permissions for this transfer setup.
database	Yes	-	Select the destination database name.
schema	Yes	-	Select the destination schema name.
table	Yes	-	Enter the name of the destination table. If the target table does not exist in the destination database schema, the table will be created automatically.
Amazon S3 bucket	Yes	-	Specify the name of the S3 bucket for temporary data storage during data transfer. You can also load the list by pressing Load Bucket List.
Amazon S3 key prefix	Yes	-	Specify the S3 path name for temporary data storage during data transfer.
transfer mode	Yes	Append `(INSERT` )	Select the transfer mode. For details about each mode, see About Transfer Modes below.

item name

indispensable

default value

Contents

Amazon Redshift connection information

Yes

Select from the pre-registered Amazon Redshift connection information that has the necessary permissions for this transfer setup.

database

Yes

Select the destination database name.

schema

Yes

Select the destination schema name.

table

Yes

Enter the name of the destination table.
If the target table does not exist in the destination database schema, the table will be created automatically.

Amazon S3 bucket

Yes

Specify the name of the S3 bucket for temporary data storage during data transfer.
You can also load the list by pressing Load Bucket List.

Amazon S3 key prefix

Yes

Specify the S3 path name for temporary data storage during data transfer.

transfer mode

Yes

Append (INSERT )

Select the transfer mode.
For details about each mode, see About Transfer Modes below.

STEP1 Detailed settings

Click on Advanced Settings to display the configuration items.

item name	indispensable	default value	Contents
Batch size (KB)	Yes	16,384	Enter the batch size. If an `OutOfMemoryError` occurs during the transfer, reduce the batch size accordingly.

item name

indispensable

default value

Contents

Batch size (KB)

Yes

16,384

Enter the batch size.
If an OutOfMemoryError occurs during the transfer, reduce the batch size accordingly.

STEP2 Output Options

item name	default value	Details
table constraint	-	Table constraints can be added to the CREATE TABLE statement. `CREATE TABLE \<table_name\> (\<column1\> <type1\>, \<column2\> \<type2\>, ... \<table_constraints\>) \<table_options\>`
Table Options	-	Table options can be added to the CREATE TABLE statement. `CREATE TABLE \<table_name\> (\<column1\> \<type1\>, \<column2\> \<type2\>, ... \<table_constraints\>) \<table_options\>`
Delete files temporarily stored in Amazon S3	be just about to	-
IAM role name	-	IAM roles can be used in place of AWS access keys and AWS secret access keys.
Maximum number of retries	12	-
Retry wait time (ms)	1,000	-
Maximum retry wait time (ms)	1,800,000	-
Merge Key Settings	-	This can be set when `UPSERT (MERGE)` is selected in the transfer mode. Enter the name of the column to be treated as a merge key (primary key) that has no duplicate values and no null values.
Default time zone	UTC	If there is a column in timestamp format, the time zone can be specified.
Column Setting	-	-
SQL to execute before loading	-	-
SQL to execute after loading	-	-

item name

default value

Details

table constraint

Table constraints can be added to the CREATE TABLE statement.
CREATE TABLE \<table_name\> (\<column1\> <type1\>, \<column2\> \<type2\>, ... \<table_constraints\>) \<table_options\>

Table Options

Table options can be added to the CREATE TABLE statement.
CREATE TABLE \<table_name\> (\<column1\> \<type1\>, \<column2\> \<type2\>, ... \<table_constraints\>) \<table_options\>

Delete files temporarily stored in Amazon S3

be just about to

IAM role name

IAM roles can be used in place of AWS access keys and AWS secret access keys.

Maximum number of retries

Retry wait time (ms)

1,000

Maximum retry wait time (ms)

1,800,000

Merge Key Settings

This can be set when UPSERT (MERGE) is selected in the transfer mode.
Enter the name of the column to be treated as a merge key (primary key) that has no duplicate values and no null values.

Default time zone

UTC

If there is a column in timestamp format, the time zone can be specified.

Column Setting

SQL to execute before loading

SQL to execute after loading

Conditions under which schema-related settings apply

STEP2出力オプションのカラム設定の内容は、テーブルを新規作成する場合にのみ、適用されます。
具体的には、以下の状態でジョブ実行した際に適用されます。

If the target table does not exist at the destination
When REPLACE is selected in the transfer mode
- In this case, the schema of the destination table is updated for each transfer, so the column settings are applied each time.

supplementary information

About transfer mode

All of the following explanations are based on the case where the target table already exists at the destination.
If the target table does not exist at the destination, a new table is created in either mode.

transfer mode	Schema Change	transaction	behavior	supplement
Append `(INSERT` )	No	Yes	The following additions are made to the table. Create a temporary table and populate it with data `INSERT` data from a temporary table `into` a target table
Postscript `(INSERT DIRECT)`	No	No	The table is appended as follows `INSERT` data directly `into` the target table	Compared to the append `(INSERT` ) mode, processing time is shorter, but transactions are not secured. Therefore, if a transfer fails midway through, rows added along the way may remain in the target table.
`TRUNCATE INSERT`	No	Yes	The table will be rewashed as follows Create a temporary table and populate it with data `DELETE the` actual data in the target table and `INSERT the` data in the temporary table `into` the target table
All cases washed `(REPLACE` )	Yes	Yes	The table will be rewashed as follows Create a temporary table and populate it with data `DROP` the target table itself and change the temporary table name to the target table name.	The schema of the existing table is deleted and updated to that of the new table.
`UPSERT (MERGE)`	No	Yes	UPSERT to the table as follows Create a temporary table and populate it with data `MERGE` data in temporary table to target table	For each row of transferred data, if the value of the merge key exists in the corresponding column of the target table, the row is updated. If it does not exist, the line is appended. If the merge key has duplicate values or contains null values, it will not `UPSERT` correctly.

transfer mode

Schema Change

transaction

behavior

supplement

Append (INSERT )

Yes

The following additions are made to the table.

Create a temporary table and populate it with data
INSERT data from a temporary table into a target table

Postscript (INSERT DIRECT)

The table is appended as follows

INSERT data directly into the target table

Compared to the append (INSERT ) mode, processing time is shorter, but transactions are not secured.

Therefore, if a transfer fails midway through, rows added along the way may remain in the target table.

TRUNCATE INSERT

Yes

The table will be rewashed as follows

Create a temporary table and populate it with data
DELETE the actual data in the target table and INSERT the data in the temporary table into the target table

All cases washed (REPLACE )

Yes

Yes

The table will be rewashed as follows

Create a temporary table and populate it with data
DROP the target table itself and change the temporary table name to the target table name.

The schema of the existing table is deleted and updated to that of the new table.

UPSERT (MERGE)

Yes

UPSERT to the table as follows

Create a temporary table and populate it with data
MERGE data in temporary table to target table

For each row of transferred data, if the value of the merge key exists in the corresponding column of the target table, the row is updated. If it does not exist, the line is appended.

If the merge key has duplicate values or contains null values, it will not UPSERT correctly.

Destination - Amazon Redshift

summary

Setting items

STEP1 Basic settings

STEP1 Detailed settings

STEP2 Output Options

supplementary information

About transfer mode

What's Next