DataWorks:MaxCompute data source

Offline read

• MaxCompute Reader supports reading from partitioned and standard tables. It does not support reading from virtual views or synchronizing foreign tables.

• When you perform an offline read from a MaxCompute partitioned table, you cannot directly map partition fields. To synchronize partition field values, you must add a custom field, manually enter the partition name, and then perform field mapping.

• You can specify partitions using scheduling parameters for automatic replacement. This is useful for scenarios where you need to synchronize partitions based on the scheduling time.

  For example, consider a partitioned table named t0 with the fields id and name. The hash partition is pt, and the subpartition is ds. To read data from the partition where pt=<data timestamp> and ds=hangzhou, you must specify the partition values as pt=${scheduling parameter} and ds=hangzhou in the data source configuration. Then, you can map the id and name fields during the field mapping configuration.

• You can write partition fields to a target table by adding them as custom fields.

• MaxCompute Reader supports data filtering using the WHERE clause.

Offline write

• MaxCompute Writer does not support the VARCHAR type if the data contains null values.

• If you write data to a DeltaTable, set Visible After Sync to Yes. Otherwise, the task fails when the concurrency is greater than 1.

  

Real-time write

• Real-time data synchronization tasks support serverless resource groups (recommended) and exclusive resource groups for Data Integration.

• Real-time data synchronization tasks do not support tables without a primary key.

• When you perform real-time synchronization to the default MaxCompute data source (usually odps_first), a temporary AccessKey (AK) is used by default. The temporary AK expires after seven days, which causes the task to fail. The platform automatically restarts the task when it detects a failure caused by an expired temporary AK. If you have configured monitoring alerts for this type of failure, you will receive an alert.

• For a one-click real-time synchronization task to MaxCompute, you can query the full historical data only on the day of configuration. Incremental data can be queried in MaxCompute only after the merge is completed the next day.

• A one-click real-time synchronization task to MaxCompute generates a full partition every day. To prevent excessive data from consuming storage resources, the MaxCompute table automatically created by this solution has a default lifecycle of 30 days. If this duration does not meet your business requirements, you can click the MaxCompute table name during task configuration to modify its lifecycle.

• Data Integration uses the MaxCompute engine's data channel for data uploads and downloads. For details about the data channel's Service-Level Agreement (SLA), see Data Transmission Service (Upload) Scenarios and Tools. You should evaluate your data synchronization solution based on the SLA of the MaxCompute engine's data channel.

• For one-click real-time synchronization to MaxCompute in instance mode, the exclusive resource group for Data Integration must have at least 8 vCPUs and 16 GB of memory.

• Only user-created MaxCompute data sources in the same region as the current workspace are supported. You can successfully connect to cross-region MaxCompute projects when you test the data source connectivity. However, when a synchronization task runs, an "engine not found" error occurs during the table creation phase in MaxCompute.

• When MaxCompute is the destination for full database synchronization, the supported synchronization methods depend on the table type. For standard tables, only full and incremental (near real-time) synchronization of an entire database is supported. For Delta Tables, both real-time synchronization and full and incremental (near real-time) synchronization of an entire database are supported.

  Note

  When you use a user-created MaxCompute data source, the DataWorks project must still be attached to a MaxCompute engine. Otherwise, you cannot create MaxCompute SQL nodes, which causes the creation of the 'done' node for full synchronization to fail.

Supported fields for data type 1.0

Supported fields for data type 2.0 and Hive-compatible data types

Data type mapping

The following table lists the data type mappings for MaxCompute Reader.

Connect to MaxCompute and enable project-level configurations

• Log on to the MaxCompute client. For more information, see Connect using the local client (odpscmd).

• Enable project-level configurations for MaxCompute. Confirm that you have the required permissions. You can use a Project Owner account to perform these operations. For more information about MaxCompute permissions, see Role planning.

Enable ACID attributes

You can use a Project Owner account to run the following command on the client to enable ACID attributes. For more information about ACID semantics in MaxCompute, see ACID semantics.

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

(Optional) Enable data type 2.0

To use the timestamp type from MaxCompute data type 2.0, you can use a Project Owner account to run the following command on the client to enable data type 2.0.

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

(Optional) Grant permissions

When you attach a MaxCompute compute resource to a workspace, a MaxCompute data source is created in DataWorks by default. You can use this data source to synchronize data within the current workspace. To synchronize data from this MaxCompute data source in another workspace, you must ensure that the access account specified for the data source in the other workspace has access permissions to this MaxCompute project. For more information about cross-account authorization, see Cross-account authorization (MaxCompute and Hologres).

Configure an offline synchronization task for a single table

• For the procedure, see Configure a task in the codeless UI and Configure a task in the code editor.

• For all parameters and a script demo for configuration in the code editor, see Appendix: Script demo and parameter description.

Configure a real-time synchronization task for a single table

For the procedure, see Configure a real-time synchronization task in Data Integration and Configure a real-time synchronization task in DataStudio.

Configure a full database synchronization task

For the procedure, see Offline synchronization of an entire database, Real-time synchronization of an entire database, and Full and incremental (near real-time) synchronization of an entire database.

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 Configure a task in the code editor. 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 demo

{
    "type":"job",
    "version":"2.0",
    "steps":[
        {
            "stepType":"odps",// The plug-in name.
            "parameter":{
                "partition":[],// The partition from which to read data.
                "isCompress":false,// Specifies whether to compress data.
                "datasource":"",// The data source.
                "column":[// The columns of the source table.
                    "id"
                ],
                "where": "",// If you use a WHERE clause for data filtering, enter the specific clause.
                "enableWhere":false,// Specifies whether to use a WHERE clause for data filtering.
                "table":""// The table name.
            },
            "name":"Reader",
            "category":"reader"
        },
        { 
            "stepType":"stream",
            "parameter":{
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The number of error records.
        },
        "speed":{
            "throttle":true,// If throttle is set to false, the mbps parameter does not take effect, and the data rate is not limited. If throttle is set to true, the data rate is limited.
            "concurrent":1, // The number of concurrent jobs.
            "mbps":"12"// The data rate limit. 1 mbps = 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

To specify the Tunnel Endpoint for MaxCompute, you can manually configure the data source in the code editor. Replace "datasource":"", in the preceding example with the specific data source parameters. The following code provides an example.

"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":"*****", 

Reader script parameters

Writer script demo

The following code provides a sample script configuration.

{
    "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 partition information.
                "truncate":true,// The cleanup rule.
                "compress":false,// Specifies whether to compress data.
                "datasource":"odps_first",// The data source name.
            "column": [// The source column names.
                "id",
                "name",
                "age",
                "sex",
                "salary",
                "interest"
                ],
                "table":""// The table name.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The number of error records. This is the maximum number of dirty data records allowed.
        },
        "speed":{
            "throttle":true,// If throttle is set to false, the mbps parameter does not take effect, and the data rate is not limited. If throttle is set to true, the data rate is limited.
            "concurrent":1, // The number of concurrent jobs.
            "mbps":"12"// The data rate limit. 1 mbps = 1 MB/s.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

To specify the Tunnel Endpoint for MaxCompute, you can manually configure the data source in the code editor. Replace "datasource":"", in the preceding example with the specific data source parameters. The following code provides an example.

"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":"**********", 

Writer script parameters