ApsaraDB for SelectDB:Use DataWorks to import data

Code example

The following script uses MySQL Reader and SelectDB Writer to synchronize data from a MySQL table to ApsaraDB for SelectDB.

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

Replace the placeholders with your actual values:

SelectDB Writer parameters

datasource

The name of the data source configured in DataWorks. Must match the name used when adding the data source.

• Required: Yes

table

The name of the destination table to write data into.

• Required: Yes

column

The column names to write to in the destination table. Separate multiple column names with commas. To write to all columns, use ["*"].

• Required: Yes

• Example: ["id", "name", "age"]

loadUrl

The HTTP endpoint of the ApsaraDB for SelectDB instance. Use the VPC endpoint and HTTP port.

• Required: Yes

• Format: ip:port

• Example: selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:8080

username

The username for accessing the ApsaraDB for SelectDB database.

• Required: Yes

password

The password for accessing the ApsaraDB for SelectDB database.

• Required: Yes

preSql

SQL statements to run before the synchronization task starts — for example, to delete stale data. The codeless UI supports one statement; the code editor supports multiple.

• Required: No

postSql

SQL statements to run after the synchronization task completes — for example, to add a timestamp. The codeless UI supports one statement; the code editor supports multiple.

• Required: No

maxBatchRows

The maximum number of rows written per batch. maxBatchRows controls how frequently data is flushed to SelectDB — a higher value means fewer, larger batches.

• Required: No

• Default: 500000

loadProps

Parameters for the COPY INTO request that control the import format. If left blank or set to {}, JSON format is used by default.

• Required: No

• Default: json

JSON format (default): The strip_outer_array parameter is automatically set to true.

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

CSV format: Specify delimiters explicitly. If you do not specify the row and column delimiters, the imported data is converted into strings by default — \t is used as the column delimiter and \n as the row delimiter.

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