Read and synchronize data in OceanBase Database by using SeaTunnel|OceanBase Database|docs|OceanBase Documentations

SeaTunnel is a distributed, highly scalable data integration platform for transferring data between various data storage systems. It provides easy-to-configure data flow task plugins, allowing you to define processes for data processing and transformation. For more information, visit the GitHub page about SeaTunnel.

Prerequisites

Before data synchronization, make sure that the following conditions are met:

You have downloaded SeaTunnel.
You have installed Java 1.8 or later.
You have downloaded the MySQL driver to the lib directory.
You have installed oblogproxy.

Read data by using SeaTunnel

Synchronize data by using SeaTunnel

This section describes how to execute SQL queries in a MySQL tenant of OceanBase Database by using MySQL Change Data Capture (CDC) Source Connector for SeaTunnel. connector-cdc-mysql is a plug-in of SeaTunnel for real-time capture and streaming of change data in MySQL databases.

Procedure

Follow the steps below to synchronize data between MySQL tenants of OceanBase Database:

Create a SeaTunnel user.

 CREATE USER 'Username'@'%' IDENTIFIED BY 'Password';
 GRANT SELECT ON *.* TO 'Username';

Configure the config/plugin_config file.

Specify connector-cdc-mysql and execute the sh bin/install-plugin.sh 2.3.3 command.

Configure SeaTunnel.

Modify the configuration file config/v2.streaming.conf.template as follows:

source {
# This is a example source plugin **only for test and demonstrate the feature source plugin**
MySQL-CDC {
result_table_name = "fake"
parallelism = 1
server-id = 5656
username = "Username"
password = "Password"
table-names = ["seatunnel.full_types"]
base-url = "jdbc:mysql://xx.xxx.x.xx:2881/seatunnel"
}

# If you would like to get more information about how to configure SeaTunnel and see full list of source plugins,
# please go to https://seatunnel.apache.org/docs/category/source-v2
}

Start SeaTunnel.

Run the following command to start SeaTunnel:

./bin/seatunnel.sh --config ./config/v2.streaming.conf.template -e local

This section describes how to synchronize data by using JDBC OceanBase Source Connector and JDBC OceanBase Sink Connector for SeaTunnel. JDBC OceanBase Source Connector supports full read and write on data from a single table of OceanBase Database. For more information, see JDBC OceanBase Source Connector. JDBC OceanBase Sink Connector supports full read and write on data from multiple tables of OceanBase Database. For more information, see JDBC OceanBase Sink Connector.

Procedure

Follow the steps below to synchronize data between MySQL tenants of OceanBase Database:

Create a SeaTunnel user.

 CREATE USER 'Username'@'%' IDENTIFIED BY 'Password';
 GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'Username';

Configure the config/plugin_config file.
Download the OceanBase Connector/J driver to the lib directory. For more information, see OceanBase Client.

Configure SeaTunnel.

Modify the configuration file config/ob_to_ob as follows:

env {
parallelism = 1
job.mode = "BATCH"
}

source {
# This is a example source plugin **only for test and demonstrate the feature source plugin**
Jdbc {
    driver = com.oceanbase.jdbc.Driver
    url = "jdbc:oceanbase://e2e_oceanbase_mysql:2881/seatunnel?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&serverTimezone=UTC"
    user = "Username"
    password = "Password"
    query = "SELECT c_bit_1, c_bit_8 FROM source"
    compatible_mode = "mysql"
}

# If you would like to get more information about how to configure seatunnel and see full list of source plugins,
# please go to https://seatunnel.apache.org/docs/connector-v2/source/FakeSource
}

sink {
Jdbc {
    driver = com.oceanbase.jdbc.Driver
    url = "jdbc:oceanbase://e2e_oceanbase_mysql:2881/seatunnel?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&serverTimezone=UTC"
    user = "Username"
    password = "Password"
    query = "insert into sink(c_bit_1, c_bit_8) values (?, ?);"
    compatible_mode = "mysql"
}
# If you would like to get more information about how to configure seatunnel and see full list of sink plugins,
# please go to https://seatunnel.apache.org/docs/connector-v2/sink
}

Start SeaTunnel.

Run the following command to start SeaTunnel:
```
./bin/seatunnel.sh --config ./config/ob_to_ob -e local
```

Notice

OceanBase Database does not support the SET datatype. If you use the SET datatype, the following error message may be returned:
Caused by: org.apache.seatunnel.api.table.catalog.DataTypeConvertException: ErrorCode:[COMMON-07], ErrorDescription:[Unsupported data type] - Convert type: SET to SeaTunnel data type error.

For more information, see SeaTunnel MySQL CDC documentation.