ApsaraDB for SelectDB:Import data using DataWorks

Add data sources

Before developing a data synchronization task, create a MySQL data source and a SelectDB data source in DataWorks.

1. Create a MySQL data source. For more information, see MySQL Data Source.

2. Create a SelectDB data source. For more information, see Data Source Management. The following table lists the key parameters.

   Parameter	Description
   Data source name	The name of the data source.
   MySQL connection string	The JDBC connection string: jdbc:mysql://<ip>:<port>/<dbname>. In the ApsaraDB for SelectDB console, go to the Instance Details > Network Information page to obtain the VPC Endpoint (or Public Endpoint) and MySQL Port. Example: jdbc:mysql://selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:9030/test_db Note If the MySQL instance and the ApsaraDB for SelectDB instance are in the same VPC, use the VPC Endpoint. Otherwise, use the Public Endpoint.
   HTTP connection address	The HTTP connection address: <ip>:<port>. In the ApsaraDB for SelectDB console, go to the Instance Details > Network Information page to obtain the VPC Endpoint (or Public Endpoint) and HTTP Port. Example: selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:8080 Note If the MySQL instance and the ApsaraDB for SelectDB instance are in the same VPC, use the VPC Endpoint. Otherwise, use the Public Endpoint.
   Username	Enter the username for your ApsaraDB for SelectDB instance.
   Password	Enter the password for the user of your ApsaraDB for SelectDB instance.

   Important

   To add the data source, add the IP addresses of the DataWorks resource group to the whitelist of data sources such as SelectDB. For more information, see Add IP addresses to a whitelist. When you configure the whitelist, distinguish between exclusive and shared Data Integration resource groups.

Configure single-table offline synchronization

You can configure an offline synchronization task in either wizard mode or script mode. For more information, see the following topics:

• Wizard mode configuration

• Script mode configuration

  For a complete list of parameters and a script sample for script mode, see Script mode: samples and parameters.

Offline task script configuration

When configuring an offline task in script mode, use the format detailed in Script mode configuration.

MySQL Reader and SelectDB Writer script sample

{
	"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"
			}
		]
	}
}

Script parameters

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

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