DataWorks:TiDB data source

Preparation 1: Check the version of your TiDB database

Data Integration requires that the version of the TiDB database be of 7.x or 8.x. You must check whether the version of your TiDB database from which you want to synchronize data meets the requirement. You can execute the following statement to check the version of your TiDB database:

SELECT TIDB_VERSION()\G

*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v8.1.1
Edition: Community
Git Commit Hash: 821e491a20fbab36604b36b647b5bae26a2c1418
Git Branch: HEAD
UTC Build Time: 2024-08-27 19:16:25
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

Preparation 2: Prepare an account that has the required permissions

We recommend that you plan and create an account for DataWorks to access your TiDB database. To prepare such an account, perform the following steps:

1. Optional. Create an account. For more information, see TiDB User Account Management.

2. Grant the required permissions to the account.

   Batch data read: The account must have the SELECT permission.

   You can execute the following statement to grant permissions to the account. Alternatively, you can grant the SUPER permission to the account. Replace Account for data synchronization in the statement with the created account.

   -- CREATE USER 'Account for data synchronization'@'%' IDENTIFIED BY 'Password'; //Create an account that can be used for data synchronization and specify a password. This way, you can use the account and password to access the database from any host. % indicates a host. 
   GRANT SELECT ON *.* TO 'Account for data synchronization'@'%'; // Grant the SELECT permission to the account.

   *.* indicates that the account is granted the preceding permissions on all tables in all databases. You can also grant the preceding permissions on specific tables in a database to the account. For example, to grant the account the preceding permissions on the user table in the test database, execute the following statement: GRANT SELECT ON test.user TO 'Account for data synchronization'@'%';.

   Note

   The REPLICATION SLAVE permission is a global permission. You cannot grant this permission on specific tables in a database to the account.

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 batch synchronization task by using 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.

Code for TiDB Reader

The following code provides an example on how to configure a synchronization task to read data from a TiDB data source.

{
  "type": "job",
  "version": "2.0",
  "steps":
  [
    {
      "stepType": "tidb",
      "parameter":
      {
        "column":
        [
          "id",
          "name"
        ],
        "where": "",
        "splitPk": "id",
        "connection":
        [
          {
            "selectedDatabase": "test_database",
            "datasource": "test_datasource",
            "table":
            [
              "test_table"
            ]
          }
        ]
      },
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "odps",
      "parameter":
      {
      },
      "name": "Writer",
      "category": "writer"
    }
  ],
  "setting":
  {
    "errorLimit":
    {
      "record": "0"
    },
    "speed":
    {
      "throttle": false,
      "concurrent": 3
    }
  },
  "order":
  {
    "hops":
    [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  }
}

Parameters in code for TiDB Reader