DocsICP FilingConsole
Documentation
Home Page DataWorks User Guide Data Integration Data source list SelectDB data source

SelectDB data source

Updated at:
Copy as MD
Product
My Favorites

DataWorks Data Integration allows you to write data to SelectDB by using single-table or full-database batch synchronization. This topic describes the data synchronization capabilities supported for SelectDB data sources in DataWorks.

Supported SelectDB versions

SelectDB Writer uses MySQL Driver 5.1.47. For more information about the driver, see MySQL Connectors. The driver supports the following SelectDB versions:

  • Standard SelectDB: 2.2.31 and 2.2.22.

  • ApsaraDB for SelectDB: 2.4, 3.0, and 4.0.

Limits

Writing data to columns of the BITMAP, HLL (HyperLogLog), or QUANTILE_STATE type is not supported.

Supported data types

For a complete list of data types supported by each SelectDB version, see the official SelectDB documentation. The following table uses SelectDB 2.2.22 as an example to describe the supported data types.

SelectDB data type

Batch write (SelectDB Writer)

INT

Supported

BIGINT

Supported

LARGEINT

Supported

SMALLINT

Supported

TINYINT

Supported

BOOLEAN

Supported

DECIMAL

Supported

DOUBLE

Supported

FLOAT

Supported

CHAR

Supported

VARCHAR

Supported

STRING

Supported

DATE

Supported

DATEV2

Supported

DATETIME

Supported

DATETIMEV2

Supported

ARRAY

Supported

JSONB

Supported

BITMAP

Not supported

HLL (HyperLogLog)

Not supported

QUANTILE_STATE

Not supported

Prepare for data synchronization

Before you synchronize data in DataWorks, prepare the SelectDB environment as described in this section. This ensures that you can configure and run SelectDB synchronization tasks in DataWorks.

Step 1: Check the SelectDB version

Data Integration supports specific SelectDB versions. For more information, see Supported SelectDB versions. In the SelectDB console, check whether the SelectDB instance that you want to synchronize meets the version requirements.

Step 2: Create an account and configure permissions

We recommend that you plan and create a dedicated SelectDB account for DataWorks to access the data source. If you use the default admin account of the SelectDB data warehouse to log on, you must set a password for the account.

Step 3: Configure network connectivity to SelectDB

SelectDB supports private and public network connections. Data Integration allows you to connect to SelectDB over either network.

  1. Private network connection: We recommend a private network connection for its higher bandwidth and greater stability. Before you use a private network connection, create an endpoint as described in Network configuration. After the endpoint is created, its status changes from Creating to Available, and its connection status changes from Connecting... to Connected. You must then associate the VPC that contains the endpoint with the VPC that contains the resource group. For more information, see Network connectivity solutions.

  2. Public network connection: Add the EIP of the resource group to the SelectDB whitelist. For more information, see Configure a whitelist.

Add a data source

Before you develop a synchronization task in DataWorks, you must add the required data source to DataWorks by following the instructions in Data source configuration. You can view parameter descriptions in the DataWorks console to understand the meanings of the parameters when you add a data source.

The following list describes several configuration parameters for a SelectDB data source:

  • MySQL connection address: Enter the JDBC connection string. In the SelectDB console, go to Connection mode > Other methods and copy the JDBC connection string. Both public and private IP addresses are supported.

  • HTTP connection address: Enter the HTTP access address. In the SelectDB console, go to Connection mode > Other methods and copy the HTTP access address. Both public and private IP addresses are supported.

  • Username: Enter the username of the SelectDB data warehouse account.

  • Password: Enter the password of the SelectDB data warehouse account.

Develop data synchronization tasks

For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.

Configure a single-table batch synchronization task

Configure a full-database batch synchronization task

Appendix: SelectDB sample scripts and parameters

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 Script mode configuration. 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.

Sample Writer script

{
  "stepType": "selectdb",//Plug-in name.
  "parameter":
  {
    "postSql"://SQL statements to execute after the synchronization task runs.
    [],
    "preSql":
    [],//SQL statements to execute before the synchronization task runs.
    "datasource":"selectdb_datasource",//Data source name.
    "table": "selectdb_table_name",//Table name.
    "column":
    [
      "id",
      "table_id",
      "table_no",
      "table_name",
      "table_status"
    ],
    "loadProps":{
      "format":"csv",//Use CSV format.
      "column_separator": "\\x01",//Column delimiter.
      "line_delimiter": "\\x02"//Row delimiter.
    }
  },
  "name": "Writer",
  "category": "writer"
}

Writer parameters

Parameter

Description

Required

Default value

datasource

The name of the data source. You can add a data source when you configure a task in the code editor. The value must match the name of the data source that you add.

Yes

None

table

The name of the table to which you want to synchronize data.

Yes

None

column

The columns in the destination table to which you want to write data, separated by commas (,). Example: "column":["id","name","age"]. To write data to all columns in order, use (*). Example: "column":["*"].

Yes

None

preSql

The SQL statements to execute before the synchronization task runs. The codeless UI supports only one SQL statement, whereas the code editor supports multiple SQL statements. For example, you can clear existing data from the table before the task runs.

No

None

postSql

The SQL statements to execute after the synchronization task runs. The codeless UI supports only one SQL statement, whereas the code editor supports multiple SQL statements. For example, you can add a timestamp after the task runs.

No

None

maxBatchRows

The maximum number of rows to import in each batch. This parameter and batchSize jointly control the amount of data imported in each batch. A batch is imported as soon as either threshold is reached.

No

500000

batchSize

The maximum amount of data to import in each batch. This parameter and maxBatchRows jointly control the amount of data imported in each batch. A batch is imported as soon as either threshold is reached.

No

94371840

maxRetries

The number of retries if a batch import fails.

No

3

labelPrefix

The label prefix for files uploaded in each batch. The final label is generated as labelPrefix + UUID to form a globally unique label and prevent duplicate data imports.

No

datax_selectdb_writer_

loadProps

The request parameters for COPY INTO, primarily used to specify the format of the data to import. JSON is the default format. If loadProps is not configured or is set to "loadProps":{}, the default JSON format is used, as shown in the following example. (SelectDB currently supports only strip_outer_array=true.)

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

To import data in CSV format, specify the format and the row and column delimiters as shown in the following example. If you do not specify the delimiters, the input data is converted to strings and combined into a CSV file for import into SelectDB, with \t as the column delimiter and \n as the row delimiter.

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

No

None

clusterName

The name of the SelectDB Cloud cluster. You can view it in the SelectDB console.

No

None

flushInterval

The interval between write batches, in milliseconds. If maxBatchRows and batchSize are set to large values, the system may import a batch when this interval elapses, before either data volume threshold is reached.

No

30000
Previous：Salesforce data sourceNext：Snowflake