DataWorks:MaxCompute data source

Limits

Batch data read

• MaxCompute Reader can read data from only partitioned and non-partitioned tables. It cannot read virtual views or read data from external tables.

• When you configure a batch synchronization task used to synchronize data from a MaxCompute partitioned table, you cannot directly configure mappings between the partition fields in the partitioned table and fields in a destination table. You must specify information about the partition from which you want to synchronize data when you configure the source.

  For example, a partitioned table named t0 contains the id and name fields. The level-1 partition field is pt and the level-2 partition field is ds. If you want to read data from the partition pt=1,ds=hangzhou in the t0 table, you must specify pt=1,ds=hangzhou when you configure the source. This way, you can configure mappings for the id and name fields later.

• MaxCompute Reader allows you to use a WHERE clause to filter data.

Batch data write

• If the data in the source contains a NULL value, MaxCompute Writer cannot convert the data type of the data into VARCHAR.

• If you want to run a batch synchronization task to write data to a MaxCompute Delta table, you must set the Visible After Synchronization parameter to Yes when you configure the task by using the codeless user interface (UI). Otherwise, an error is reported if more than one parallel thread is configured for the batch synchronization task.

  

Real-time data write

• You can use only exclusive resource groups for Data Integration to run real-time synchronization tasks.

• You can run a real-time synchronization task to synchronize data only from a PolarDB, Oracle, or MySQL data source to MaxCompute.

• A real-time synchronization task cannot be used to synchronize data from a table that has no primary key.

• If you use the default MaxCompute data source odps_first as the destination of the real-time synchronization task, the temporary AccessKey pair is used for data synchronization by default. The temporary AccessKey pair is valid for only seven days. After seven days, the temporary AccessKey pair automatically expires, and the real-time synchronization task fails. If the system detects that the real-time synchronization task fails due to the expiration of the temporary AccessKey pair, the system restarts the real-time synchronization task. If a related alert rule is configured for the real-time synchronization task, the system reports an alert.

• On the day on which you configure a one-click real-time synchronization task used to synchronize data to MaxCompute, you can query only the historical full data. You can query the incremental data only after full data and incremental data are merged on the next day.

• A one-click real-time synchronization task used to synchronize data to MaxCompute generates a partition for storing full data in a MaxCompute table every day. To prevent data from occupying excessive storage resources, the default lifecycle of a MaxCompute table that is automatically created is 30 days. If the lifecycle does not meet your business requirements, you can click the name of a MaxCompute table to modify the lifecycle of the table when you configure the related synchronization task.

• Data Integration uses the channels that are provided by MaxCompute to upload and download data. You can select a channel based on your business requirements. For more information about the types of channels that are provided by MaxCompute, see Data upload scenarios and tools.

• If you want to run a real-time synchronization task to synchronize data to MaxCompute in whole-instance mode, the specifications of the exclusive resource group for Data Integration that you use to run the synchronization task must be at least 8 vCPUs and 16 GiB of memory.

• You can use only a self-managed MaxCompute data source that resides in the same region as your workspace. If you use a self-managed MaxCompute data source that resides in a different region from your workspace, the data source can be connected to the resource group that you use. However, an error indicating that the compute engine does not exist is reported when the system creates a MaxCompute table during the running of the synchronization task.

  Note

  If you use a self-managed MaxCompute data source, you must associate a MaxCompute compute engine with your DataWorks workspace. Otherwise, an ODPS SQL node cannot be created. As a result, a node that is used to mark the end of full synchronization cannot be created.

Precautions

If a column in a destination table has no mapped source column, the values in the column in the destination table are NULL after the synchronization task finishes running. In addition, even if default values are specified for the column when the system creates the destination table, the values in the column are still NULL after the synchronization task finishes running.

Supported data types

The following data type editions are supported: MaxCompute V1.0 data type edition, MaxCompute V2.0 data type edition, and Hive-compatible data type edition. This section provides the support status of data types of each edition.

MaxCompute V1.0 data type edition

MaxCompute V2.0 data type edition and Hive-compatible data type edition

Data type mappings

Prepare a MaxCompute environment before data synchronization

Before you read data from or write data to a MaxCompute table, you can determine whether to enable related properties based on your business requirements.

Connect to MaxCompute and obtain the permissions to perform project-level configurations

• Start the MaxCompute client. For more information, see MaxCompute client (odpscmd).

• Obtain the permissions to perform project-level configurations. Make sure that your account has the required permissions. You can use the account of a project owner to perform related operations. For more information, see Role planning.

Enable the ACID semantics

setproject odps.sql.acid.table.enable=true;

(Optional) Enable the MaxCompute V2.0 data type edition

setproject odps.sql.type.system.odps2=true;

(Optional) Create an account

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a MaxCompute data source

Before you develop a synchronization task for a MaxCompute data source, you must add a MaxCompute project to the desired workspace as a data source. For more information about how to add a MaxCompute data source, see Add a MaxCompute data source.

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 information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Configure a real-time synchronization task to synchronize data of a single table

For more information about the configuration procedure, see Create a real-time synchronization node to synchronize incremental data from a single table and Configure a real-time synchronization task in DataStudio.

Configure synchronization settings to implement batch synchronization of all data in a database, real-time synchronization of full data or incremental data in a database, and real-time synchronization of data from tables in sharded databases

For more information about the configuration procedure, see Configure a synchronization task in Data Integration.

FAQ

• What are the items that I must take note of when I add a source field in a batch synchronization task that synchronizes data from a MaxCompute table?

• How do I read data from partition key columns in a MaxCompute table?

• How do I synchronize data from multiple partitions of a MaxCompute table?

• How do I perform operations such as filtering fields, reordering fields, and setting empty fields to null when I run a batch synchronization task to synchronize data to MaxCompute?

• What do I do if the number of fields that I want to write to a destination MaxCompute table is greater than the number of fields in the table?

• What are the precautions that I must practice when I configure partition information for a destination MaxCompute table?

• How do I ensure the idempotence of data that is written to MaxCompute in task rerun and failover scenarios?

For information about other common issues in Data Integration, see FAQ about Data Integration.

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the reader and writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the reader and writer in the code editor.

Code for MaxCompute Reader

{
    "type":"job",
    "version":"2.0",
    "steps":[
        {
            "stepType":"odps",// The plug-in name. 
            "parameter":{
                "partition":[],// The names of the partitions from which you want to read data. 
                "isCompress":false,// Specifies whether to enable compression. 
                "datasource":"",// The name of the data source. 
                "column":[// The names of the columns. 
                    "id"
                ],
                "where": "",// The WHERE clause that you want to use to filter data. 
                "enableWhere":false,// Specifies whether to use a WHERE clause to filter data. 
                "table":""// The name of the table from which you want to read data. 
            },
            "name":"Reader",
            "category":"reader"
        },
        { 
            "stepType":"stream",
            "parameter":{
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed. 
        },
        "speed":{
            "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

"accessId":"*******************",
"accessKey":"*******************",
"endpoint":"http://service.eu-central-1.maxcompute.aliyun-inc.com/api",
"odpsServer":"http://service.eu-central-1.maxcompute.aliyun-inc.com/api", 
"tunnelServer":"http://dt.eu-central-1.maxcompute.aliyun.com", 
"project":"*****", 

Parameters in code for MaxCompute Reader

Code for MaxCompute Writer

{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"odps",// The plug-in name. 
            "parameter":{
                "partition":"",// The names of the partitions to which you want to write data. 
                "truncate":true,// The write rule. 
                "compress":false,// Specifies whether to enable compression. 
                "datasource":"odps_first",// The name of the data source. 
            "column": [// The names of the columns to which you want to write data. 
                "id",
                "name",
                "age",
                "sex",
                "salary",
                "interest"
                ],
                "table":""// The name of the table to which you want to write data. 
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed. 
        },
        "speed":{
            "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12"// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

"accessId":"<yourAccessKeyId>",
 "accessKey":"<yourAccessKeySecret>",
 "endpoint":"http://service.eu-central-1.maxcompute.aliyun-inc.com/api",
 "odpsServer":"http://service.eu-central-1.maxcompute.aliyun-inc.com/api", 
"tunnelServer":"http://dt.eu-central-1.maxcompute.aliyun.com", 
"project":"**********", 

Parameters in code for MaxCompute Writer