ApsaraDB for SelectDB:Import data by using DataWorks

Usage notes

• If you use the Data Integration feature of DataWorks to import data to an ApsaraDB for SelectDB instance, the data must be written offline.

• You cannot write fields of the BITMAP, HyperLogLog (HLL), or QUANTILE_STATE data type.

Develop a data synchronization task

Add data sources

Before you configure a data synchronization task, you must add a MySQL data source and an ApsaraDB for SelectDB data source to DataWorks.

1. For more information about how to add a MySQL data source, see MySQL data source.

2. For more information about how to add an ApsaraDB for SelectDB data source, see Add and manage data sources. The following table describes the parameters that are used to add an ApsaraDB for SelectDB data source.

   Parameter	Description
   Data Source Name	The name of the data source.
   JDBC URL	The Java Database Connectivity (JDBC) URL in the jdbc:mysql://<ip>:<port>/<dbname> format. To obtain the virtual private cloud (VPC) endpoint or public endpoint and MySQL port of an ApsaraDB for SelectDB instance, perform the following operations: Log on to the ApsaraDB for SelectDB console and go to the Instance Details page of the instance whose information you want to view. In the Network Information section of the Basic Information tab, view the values of the VPC Endpoint or Public Endpoint parameter and the MySQL Port parameter. Example: jdbc:mysql://selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:9030/test_db Note If the MySQL instance and the ApsaraDB for SelectDB instance reside in the same VPC, use the VPC endpoint for this parameter. If the MySQL instance and the ApsaraDB for SelectDB instance reside in different VPCs, use the public endpoint for this parameter.
   HTTP address	The IP address and port number that are used to access the ApsaraDB for SelectDB instance over HTTP. The value is in the <ip>:<port> format. To obtain the VPC endpoint or public endpoint and HTTP port of an ApsaraDB for SelectDB instance, perform the following operations: Log on to the ApsaraDB for SelectDB console and go to the Instance Details page of the instance whose information you want to view. In the Network Information section of the Basic Information tab, view the values of the VPC Endpoint or Public Endpoint parameter and the HTTP Port parameter. Example: selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:8080 Note If the MySQL instance and the ApsaraDB for SelectDB instance reside in the same VPC, use the VPC endpoint for this parameter. If the MySQL instance and the ApsaraDB for SelectDB instance reside in different VPCs, use the public endpoint for this parameter.
   Username	The username of the owner account of the ApsaraDB for SelectDB instance.
   Password	The password of the owner account of the ApsaraDB for SelectDB instance.

   Important

   To ensure that the data source can be added, you must add the IP address of the resource group in DataWorks to the IP address whitelist of the data source such as ApsaraDB for SelectDB. For more information, see Configure an IP address whitelist. When you configure IP address whitelists, you must add the IP addresses of different types of resource groups to different IP address whitelists. For example, the IP addresses of an exclusive resource group for Data Integration and a shared resource group for Data Integration must be added to different IP address whitelists.

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

You can configure a batch synchronization task by using the codeless UI or the code editor. For more information, see the following topics:

• Configure a batch synchronization task by using the codeless UI

• Configure a batch synchronization task by using the code editor

  For more information about the example on how to configure a batch synchronization task by using the code editor and the related parameters, see the Sample code and parameters section of this topic.

Sample code and parameters

Configure a batch synchronization task by using the code editor

If you configure a batch synchronization task by using the code editor, you must write the script based on the format requirements in the code editor. For more information, see Configure a batch synchronization task by using the code editor.

Code for MySQL Reader and SelectDB Writer

{
	"type":"job",
	"version":"2.0",
	"steps":[
		{
			"stepType":"mysql",
			"parameter":{
				"column":[
          "<id>",
          "<table_id>",
          "<table_no>",
          "<table_name>",
          "<table_status>"
				],
				"connection":[
					{
						"datasource":"<mysql_datasource>",
						"table":[
							"<mysql_table_name>"
						]
					}
				],
				"where":"",
				"splitPk":"",
				"encoding":"UTF-8"
			},
			"name":"Reader",
			"category":"reader"
		},
		{
			"stepType":"selectdb",
			"parameter":{
        "postSql":[
        ],
        "preSql":[
        ],
        "username": "<selectdb_username>",
        "password": "<selectdb_password>",
        "loadUrl":[
          "<ip:port>"
        ],
        "column":[
          "<id>",
          "<table_id>",
          "<table_no>",
          "<table_name>",
          "<table_status>"
				],
        "connection":[
					{
						"datasource":"<selectdb_datasource>",
						"table":[
							"<selectdb_table_name>"
						]
					}
				],
        "maxBatchRows":1000000,
        "loadProps":{
          "format":"csv",
          "column_separator": "\\x01",
          "line_delimiter": "\\x02"
        }
      },
			"name":"Writer",
			"category":"writer"
		}
	],
	"setting":{
		"errorLimit":{
			"record":"0"
		},
		"speed":{
			"throttle":false,
			"concurrent":1
		}
	},
	"order":{
		"hops":[
			{
				"from":"Reader",
				"to":"Writer"
			}
		]
	}
}

Parameters

"loadProps": {
   "format": "json",
   "strip_outer_array":true
}

"loadProps": {
   "format":"csv",
   "column_separator": "\\x01",
   "line_delimiter": "\\x02"
}

Supported data types

The following table describes the data types that are supported by ApsaraDB for SelectDB.

References

What is DataWorks?