Supported versions and data types for Amazon Redshift - DataWorks

Supported Amazon Redshift versions

Amazon Redshift uses redshift-jdbc4.2 Driver 2.1.0.1. For more information about driver capabilities, see Configure a JDBC driver version for Amazon Redshift.

Data type mappings

The following table describes the mappings between Amazon Redshift data types and SQL/Java data types. For more information, see the official Amazon Redshift documentation.

Amazon Redshift data type	SQL data type	Java data type
BIGINT	SQL_BIGINT	LONG
BOOLEAN	SQL_BIT	Boolean
CHAR	SQL_CHAR	STRING
DATE	SQL_TYPE_DATE	java.sql.Date
DECIMAL	SQL_NUMERIC	BigDecimal
DOUBLE PRECISION	SQL_DOUBLE	Double
GEOMETRY	SQL_ LONGVARBINARY	byte[]
INTEGER	SQL_INTEGER	INTEGER
OID	SQL_BIGINT	LONG
SUPER	SQL_LONGVARCHAR	STRING
REAL	SQL_REAL	Float
SMALLINT	SQL_SMALLINT	SHORT
TEXT	SQL_VARCHAR	STRING
TIME	SQL_TYPE_TIME	java.sql.Time
TIMETZ	SQL_TYPE_TIME	java.sql.Time
TIMESTAMP	SQL_TYPE_ TIMESTAMP	java.sql.Timestamp
TIMESTAMPTZ	SQL_TYPE_ TIMESTAMP	java.sql.Timestamp
VARCHAR	SQL_VARCHAR	STRING

Preparations for data synchronization

Before you synchronize data in DataWorks, you must establish network connections between your data sources and a serverless resource group or an exclusive resource group for Data Integration to enable the resource group to access the data sources over an internal network. We recommend that you use a serverless resource group for data synchronization. For information about how to establish network connections, see Network connectivity solutions.

Create a data source

Before you develop a synchronization task in DataWorks, you must add the required data source to DataWorks by following the instructions in Data source management. You can view parameter descriptions in the DataWorks console to understand the meanings of the parameters when you add a data source.

The following are key parameters for an Amazon Redshift data source.

JDBC URL: The JDBC connection string, including the IP address, port number, database, and connection parameters. Both public and private IP addresses are supported. If you use a public IP address, ensure that the data integration resource group can access the host where your Amazon Redshift instance is located.
Username: The username for your Amazon Redshift database.
Password: The password for the specified username.

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.

Configure a batch synchronization task to synchronize data of a single table

For more information about the configuration procedure, see Configure a batch synchronization task by using the codeless UI and Configure a batch synchronization task by using the code editor.
For a complete list of parameters and a script example for the code editor, see Appendix: Script examples and parameter descriptions.

Appendix: Script examples and parameter descriptions

Configure a batch synchronization task by using the code editor

If you want to configure a batch synchronization task by using the code editor, you must configure the related parameters in the script based on the unified script format requirements. For more information, see Script mode configuration. The following information describes the parameters that you must configure for data sources when you configure a batch synchronization task by using the code editor.

Reader script example

{
  "stepType": "redshift"
  "parameter":
  {
    "datasource":"redshift_datasource",
    "table": "redshift_table_name",
    "where": "xxx=3",
    "splitPk": "id",
    "column":
    [
      "id",
      "table_id",
      "table_no",
      "table_name",
      "table_status"
    ]
  },
  "name": "Reader",
  "category": "reader"
}

Reader parameters

Parameter	Description	Required	Default value
datasource	The name of the data source. This must match the name of the data source you added in DataWorks.	Yes	No default value
table	The name of the source table to read data from.	Yes	No default value
column	The columns to synchronize, separated by commas. For example,`"column":["id","name","age"]`. To synchronize all columns, use an asterisk (). For example,`"column":[""]`.	Yes	No default value
where	The WHERE clause. The task concatenates the column, table, and where parameters into an SQL statement to read data from the source table. For testing, you can set this to limit 10. To read the data that is generated on the current day, you can set the where parameter to gmt_create > $bizdate. You can use the WHERE clause to read incremental data. If the where parameter is not provided or is left empty, all data is read from the source table.	No	No default value
splitPk	The column used for data sharding. When specified, the system uses parallel threads to synchronize data, which improves efficiency.	No	No default value

Writer script example

{
  "stepType": "redshift",// The plugin name.
  "parameter":
  {
    "postSql":["delete from XXX;"],
    "preSql":["delete from XXX;"],
    "datasource":"redshift_datasource",// The data source name.
    "table": "redshift_table_name",// The table name.
    "writeMode": "insert",
    "batchSize": 2048,
    "column":
    [
      "id",
      "table_id",
      "table_no",
      "table_name",
      "table_status"
    ]
  },
  "name": "Writer",
  "category": "writer"
}

Writer parameters

Parameter	Description	Required	Default
datasource	The name of the data source, which must match the name configured in DataWorks.	Yes	N/A
table	The name of the destination table.	Yes	N/A
column	The destination columns to write data to, separated by commas. For example,`"column":["id","name","age"]`. To write to all columns in order, use an asterisk (). For example,`"column":[""]`.	Yes	N/A
preSql	An SQL statement to execute before the synchronization task starts. For example, you can clear old data from the target table. You can specify one statement in the codeless UI or multiple statements in the code editor.	No	N/A
postSql	An SQL statement to execute after the synchronization task finishes. For example, you can add a timestamp. You can specify one statement in the codeless UI or multiple statements in the code editor.	No	N/A
batchSize	The number of records to write per batch.	No	2048
writeMode	The write mode. Only `insert` is supported.	No	insert