Data synchronization capabilities of the DataWorks AnalyticDB for PostgreSQL connector - DataWorks

Limitations

Batch synchronization supports reading data from a view.

Supported versions

The connector supports versions up to and including 7.0.

Supported data types

Batch read

AnalyticDB for PostgreSQL Reader supports most AnalyticDB for PostgreSQL data types. Verify that your data types are supported before use.

The following table lists the supported data types for batch reads.

Category	AnalyticDB for PostgreSQL type
Integer	BIGINT, BIGSERIAL, INTEGER, SMALLINT, SERIAL, and GEOMETRY
Floating point	DOUBLE, PRECISION, MONEY, NUMERIC, and REAL
String	VARCHAR, CHAR, TEXT, BIT, and INET
Date and time	DATE, TIME, and TIMESTAMP
Boolean	BOOL
Binary	BYTEA

Batch write

AnalyticDB for PostgreSQL Writer supports most AnalyticDB for PostgreSQL data types. Verify that your data types are supported before use.

The following table maps DataWorks data types to AnalyticDB for PostgreSQL data types for batch writes.

DataWorks data type	AnalyticDB for PostgreSQL type
LONG	BIGINT, BIGSERIAL, INTEGER, SMALLINT, and SERIAL
DOUBLE	DOUBLE, PRECISION, MONEY, NUMERIC, and REAL
STRING	VARCHAR, CHAR, TEXT, BIT, INET, and GEOMETRY
DATE	DATE, TIME, and TIMESTAMP
BOOLEAN	BOOL
BYTES	BYTEA

Note

You must use a type cast, such asa_inet::varchar, for the MONEY, INET, and BIT data types.

Add 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.

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.

Single-table batch synchronization

For the procedure, see Configure a sync task using the Codeless UI and Configure a sync task using the Code Editor.
For a complete list of parameters and script samples for the Code Editor, see Appendix: Script samples and parameters.

Full database batch synchronization

For the procedure, see Configure a batch synchronization task for an entire database.

Appendix: Script samples and parameters

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 sample

{
    "type": "job",
    "steps": [
        {
            "parameter": {
                "datasource": "test_004",// The name of the data source.
                "column": [// The columns in the source table.
                    "id",
                    "name",
                    "sex",
                    "salary",
                    "age"
                ],
                "where": "id=1001",// The WHERE clause.
                "splitPk": "id",// The shard key.
                "table": "public.person"// The name of the source table.
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "parameter": {},
            "name": "Writer",
            "category": "writer"
        }
    ],
    "version": "2.0",// The version number.
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    },
    "setting": {
        "errorLimit": {// The error record count.
            "record": ""
        },
        "speed": {
            "concurrent": 6,// The number of concurrent threads.
            "throttle": true,// Specifies whether to enable throttling. If you set this parameter to false, throttling is disabled and the mbps parameter does not take effect. If you set this parameter to true, throttling is enabled.
           "mbps":"12"// The throttling rate in MB/s.
        }
    }
}

Reader parameters

Parameter	Description	Required	Default
datasource	The name of the data source as registered in DataWorks.	Yes	None
table	The name of the source table.	Yes	None
column	The columns to synchronize, specified as a JSON array. By default, all columns are synchronized, as indicated by [*]. You can select a subset of columns to export. You can change the order of columns. You can configure constants. You must follow the SQL syntax. Example: `["id", "table","1","'mingya.wmy'","'null'", "to_char(a+1)","2.3","true"]`. id is a regular column name. table is a column name that is also a reserved keyword. 1 is an integer constant. 'mingya.wmy' is a string constant. The value must be enclosed in single quotation marks ('). 'null' is a string constant. to_char(a+1) is a function expression. 2.3 is a floating-point number. true is a boolean value. You must explicitly specify the columns to synchronize for the column parameter. The value cannot be empty.	Yes	None
splitPk	The shard key. Set splitPk to a column to enable parallel data extraction through sharding. We recommend using the table's primary key, which is typically distributed evenly and helps prevent data hotspots. The splitPk parameter supports data sharding for integer columns only. If you specify a column of a different data type, such as string, float, or date, Data Integration ignores the splitPk configuration and synchronizes data in a single thread. If you do not configure the splitPk parameter or leave it empty, the task synchronizes data using a single thread.	No	None
where	The WHERE clause used to filter the extracted data. The reader constructs an SQL query from the column, table, and where parameters. For example: `id>2 and sex=1`. You can use the WHERE clause to perform incremental synchronization. If you do not specify a WHERE clause, the connector synchronizes the entire table.	No	None
querySql (advanced; not available in Codeless UI)	A custom SQL query for data filtering. When the `where` parameter cannot express your filter conditions, use this parameter instead. Data Integration ignores the column, table, and where parameters when this is configured. Example: `select a,b from table_a join table_b on table_a.id = table_b.id`. When you configure the querySql parameter, AnalyticDB for PostgreSQL Reader ignores the column, table, and where parameters.	No	None
fetchSize	The number of records to fetch per batch from the database. A larger value reduces network round trips and can improve extraction performance. Note A fetchSize value greater than 2048 may cause an out-of-memory (OOM) error in the data synchronization process.	No	512

Writer script sample

{
    "type": "job",
    "steps": [
        {
            "parameter": {},
            "name": "Reader",
            "category": "reader"
        },
        {
            "parameter": {
                "postSql": [],// The post-execution SQL statement.
                "datasource": "test_004",// The name of the data source.
                "column": [// The columns in the destination table.
                    "id",
                    "name",
                    "sex",
                    "salary",
                    "age"
                ],
                "table": "public.person",// The name of the destination table.
                "preSql": []// The pre-execution SQL statement.
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "version": "2.0",// The version number.
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    },
    "setting": {
        "errorLimit": {// The error record count.
            "record": ""
        },
        "speed": {
            "throttle":true,// Specifies whether to enable throttling. If you set this parameter to false, throttling is disabled and the mbps parameter does not take effect. If you set this parameter to true, throttling is enabled.
            "concurrent":6, // The number of concurrent threads.
            "mbps":"12"// The throttling rate.
        }
    }
}

Writer parameters

Parameter	Description	Required	Default
datasource	The name of the data source as registered in DataWorks.	Yes	None
table	The name of the destination table.	Yes	None
writeMode	The write mode. Valid values: insert, copy, and upsert. insert: Executes the PostgreSQL `INSERT INTO ... VALUES ...` statement to write data to PostgreSQL. This mode is recommended. copy: PostgreSQL provides the COPY command to copy data between a table and a file (standard input or standard output). Data Integration supports using `COPY FROM` to load data into a table. Use this mode to improve performance. upsert: If a data conflict occurs during the write operation, the system handles the new and existing data based on the conflictMode parameter.	No	insert
conflictMode	If writeMode is set to upsert and a primary key or unique index conflict occurs when writing data to PostgreSQL, you can select one of the following conflict handling policies: replace: If a conflict occurs, the incoming new data overwrites the existing data. ignore: If a conflict occurs, the connector ignores the new data and retains the existing data. Note You can configure the conflict handling policy only in the Code Editor.	No	replace
column	The destination columns to write data to, separated by commas (,). Example: `"column":["id","name","age"]`. To write to all columns in order, use an asterisk (). Example: "column":[""].	Yes	None
preSql	An SQL statement that runs before the synchronization task starts. The Codeless UI supports one statement; the Code Editor supports multiple statements, for example, to clear old data.	No	None
postSql	An SQL statement that runs after the synchronization task completes. The Codeless UI supports one statement; the Code Editor supports multiple statements, for example, to add a timestamp.	No	None
batchSize	The number of records to write per batch. A larger value reduces network round trips and improves throughput, but an excessively large value may cause an OOM error.	No	1024