This is an automated email from the ASF dual-hosted git repository.
diwu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new e3454494b4 [improvement](extension) add new features to Mysql to doris
tools (#14243)
e3454494b4 is described below
commit e3454494b452e7054bcd540e36a8b2d229c2c99f
Author: gnehil <[email protected]>
AuthorDate: Mon Jun 19 11:08:48 2023 +0800
[improvement](extension) add new features to Mysql to doris tools (#14243)
---
docs/en/docs/ecosystem/mysql-to-doris.md | 240 ++++++++++++++++-----
docs/zh-CN/docs/ecosystem/mysql-to-doris.md | 196 ++++++++++++-----
extension/mysql_to_doris/README.md | 173 ++++++++++++++-
extension/mysql_to_doris/bin/e_auto.sh | 52 -----
extension/mysql_to_doris/bin/e_mysql_to_doris.sh | 99 ---------
extension/mysql_to_doris/bin/run.sh | 199 +++++++++++++++++
.../conf/{doris.conf => doris_external_tables} | 13 +-
extension/mysql_to_doris/conf/doris_tables | 11 +-
.../mysql_to_doris/conf/{mysql.conf => env.conf} | 15 +-
extension/mysql_to_doris/lib/e_auto.sh | 58 +++++
extension/mysql_to_doris/lib/e_mysql_to_doris.sh | 92 ++++++++
extension/mysql_to_doris/lib/get_tables.sh | 35 +++
.../doris.conf => lib/jdbc/create_jdbc_catalog.sh} | 25 ++-
extension/mysql_to_doris/lib/jdbc/sync_to_doris.sh | 42 ++++
extension/mysql_to_doris/lib/mysql_to_doris.sh | 115 ++++++----
.../{mysql_to_doris.sh => mysql_type_convert.sh} | 35 ++-
extension/mysql_to_doris/lib/sync_check.sh | 53 +++++
extension/mysql_to_doris/lib/sync_to_doris.sh | 43 ++++
18 files changed, 1166 insertions(+), 330 deletions(-)
diff --git a/docs/en/docs/ecosystem/mysql-to-doris.md
b/docs/en/docs/ecosystem/mysql-to-doris.md
index 67afcd1f9d..9a4e74f086 100644
--- a/docs/en/docs/ecosystem/mysql-to-doris.md
+++ b/docs/en/docs/ecosystem/mysql-to-doris.md
@@ -28,78 +28,214 @@ under the License.
# Mysql to Doris
-mysql to doris is mainly suitable for automating the creation of doris odbc
tables, mainly implemented with shell scripts
-
-## manual
+This is an easy-to-use tool to help mysql users on using doris with odbc
external table creation, olap table creation and data sync.
mysql to doris code
[here](https://github.com/apache/doris/tree/master/extension/mysql_to_doris)
-### Directory Structure
+## Directory Structure
```text
-├── mysql_to_doris
-│ ├── conf
-│ │ ├── doris.conf
-│ │ ├── mysql.conf
-│ │ └── tables
-│ ├── all_tables.sh
-│ │
-└── └── user_define_tables.sh
+├── bin
+│ └── run.sh
+├── conf
+│ ├── doris_external_tables
+│ ├── doris_tables
+│ ├── env.conf
+│ └── mysql_tables
+└── lib
+ ├── jdbc
+ │ ├── create_jdbc_catalog.sh
+ │ └── sync_to_doris.sh
+ ├── e_auto.sh
+ ├── e_mysql_to_doris.sh
+ ├── get_tables.sh
+ ├── mysql_to_doris.sh
+ ├── mysql_type_convert.sh
+ ├── sync_check.sh
+ └── sync_to_doris.sh
```
-1. all_tables.sh
+## configurations
- This script mainly reads all the tables under the mysql specified library
and automatically creates the Doris odbc external table
+All configuration files are in the conf directory.
-2. user_define_tables.sh
+### env.conf
- This script is mainly used for users to customize certain tables under the
specified mysql library to automatically create Doris odbc external tables
+Fill in your doris and mysql information here.
-3. conf
+```text
+# doris env
+fe_master_host=<your_fe_master_host>
+fe_master_port=<your_fe_master_query_port>
+doris_username=<your_doris_username>
+doris_password=<your_doris_password>
+doris_odbc_name=<your_doris_odbc_driver_name>
+doris_jdbc_catalog=<jdbc_catalog_name>
+doris_jdbc_default_db=<jdbc_default_database>
+doris_jdcb_driver_url=<jdcb_driver_url>
+doris_jdbc_driver_class=<jdbc_driver_class>
+
+# mysql env
+mysql_host=<your_mysql_host>
+mysql_port=<your_mysql_port>
+mysql_username=<your_mysql_username>
+mysql_password=<your_mysql_password>
+```
- Configuration file, `doris.conf` is mainly used to configure doris related,
`mysql.conf` is mainly used to configure mysql related, `tables` is mainly used
to configure user-defined mysql library tables
+### mysql_tables
-### full
+Fill in the mysql table here, in the form of `database.table`.
-1. Download using mysql to doris
[here](https://github.com/apache/doris/tree/master/extension/mysql_to_doris)
-2. Configuration related files
-
- ```shell
- #doris.conf
- master_host=
- master_port=
- doris_password=
- doris_odbc_name=''
-
- #mysql.conf
- mysql_host=
- mysql_password=
- ```
-
- | Configuration item | illustrate |
- | -------------- | ----------------------- |
- | master_host | Doris FE master node IP |
- | master_port | Doris FE query_port port |
- | doris_password | Doris Password (default root user) |
- | doris_odbc_name | The name of mysql odbc in the odbcinst.ini
configuration file under be/conf |
- | mysql_host | Mysql IP |
- | mysql_password | Mysql Password (default root user) |
-
-3. Execute the `all_tables.sh` script
+```text
+db1.table1
+db1.table2
+db2.table3
+```
+### doris_tables
+
+Fill in the doris olap table here, in the form of `database.table`.
+
+```text
+doris_db.table1
+doris_db.table2
+doris_db.table3
```
-sh all_tables.sh mysql_db_name doris_db_name
+
+### doris_external_tables
+
+Fill in the doris external table here, in the form of `database.table`.
+
+```text
+doris_db.e_table1
+doris_db.e_table2
+doris_db.e_table3
+```
+
+## How to use
+
+bin/run.sh is the startup shell script and this is options for the script:
+
+```shell
+Usage: run.sh [option]
+ -e, --create-external-table: create doris external table
+ -o, --create-olap-table: create doris olap table
+ -i, --insert-data: insert data into doris olap table from doris external
table
+ -d, --drop-external-table: drop doris external table
+ -a, --auto-external-table: create doris external table and auto check
mysql schema change
+ --database: specify the database name to process all tables under the
entire database, and separate multiple databases with ","
+ -t, --type: specify external table type, valid options: ODBC(default), JDBC
+ -h, --help: show usage
```
-After successful execution, the files directory will be generated, and the
directory will contain `tables` (table name) and `tables.sql` (doris odbc table
creation statement)
-### custom
+### 1. Create doris external table
-1. Modify the `conf/tables` file to add the name of the odbc table that needs
to be created
-2. To configure mysql and doris related information, refer to step 2 of full
creation
-3. Execute the `user_define_tables.sh` script
+use like this:
+```shell
+sh bin/run.sh --create-external-table
```
-sh user_define_tables.sh mysql_db_name doris_db_name
+
+or
+
+```shell
+sh bin/run.sh -e
+```
+
+then doris odbc external table has been created, and the table creation
statement will be generated in `result/mysql/e_mysql_to_doris.sql` file.
+
+If `--type` option is specified as `JDBC`, create jdbc catalog statement will
be generated in `result/mysql/jdbc_catalog.sql` file.
+
+### 2. Create doris olap table
+
+use like this:
+
+```shell
+sh bin/run.sh --create-olap-table
+```
+
+or
+
+```shell
+sh bin/run.sh -o
+```
+
+then doris odbc olap table has been created, and the table creation statement
will be generated in `result/mysql/mysql_to_doris.sql` file.
+
+### 3. Create doris olap table and sync data from odbc external table
+
+The premise is that you have created the external table, if not, please create
it first.
+
+use like this:
+
+```shell
+sh bin/run.sh --create-olap-table --insert-data
```
-After successful execution, the user_files directory will be generated, and
the directory will contain `tables.sql` (doris odbc table creation statement)
+or
+
+```shell
+sh bin/run.sh -o -i
+```
+
+then doris odbc olap table has been created, and the table creation statement
will be generated in `result/mysql/mysql_to_doris.sql` file, and the insert
statement is generated in `result/mysql/sync_to_doris.sql` file.
+
+#### Sync result check
+
+After the data is synchronized, the task of checking the synchronization
results will be executed to compare the data volume of the olap table and mysql
table, and the checking results will be saved in the `result/mysql/sync_check`
file.
+
+#### Drop external table
+
+If you want to drop odbc external table after data sync finished, add
`--drop-external-table` or `-d` option.
+
+use like this:
+
+```shell
+sh bin/run.sh --drop-external-table
+```
+
+or
+
+```shell
+sh bin/run.sh -d
+```
+
+this option is valid for `ODBC` type.
+
+### 4. Create doris external table and sync schema change automatically
+
+use like this:
+
+```shell
+sh bin/run.sh --auto-external-table
+```
+
+or
+
+```shell
+sh bin/run.sh -a
+```
+
+the program will run in the background, process id is saved in `e_auto.pid`,
and this option is valid for `ODBC` type.
+
+### Process by specifying the database
+
+If you have a large number of tables and do not need to customize the doris
table name, you can specify the database name to be processed through the
`--databases` option without manual configuration.
+
+use like this:
+
+```shell
+# single database
+sh bin/run.sh --database db1
+```
+
+or
+
+```shell
+# multiple databases
+sh bin/run.sh --database db1,db2,db3
+```
+
+With this option, the program will automatically obtain all the tables under
the specified mysql database, and generate the configuration of `mysql_tables`,
`doris_tables` and `doris_external_tables`.
+
+**Please note that this option needs to be used in conjunction with other
options.**
diff --git a/docs/zh-CN/docs/ecosystem/mysql-to-doris.md
b/docs/zh-CN/docs/ecosystem/mysql-to-doris.md
index f08b192c9e..300f6ce02d 100644
--- a/docs/zh-CN/docs/ecosystem/mysql-to-doris.md
+++ b/docs/zh-CN/docs/ecosystem/mysql-to-doris.md
@@ -28,81 +28,179 @@ under the License.
# Mysql to Doris
-mysql to doris 主要适用于自动化创建doris odbc 表，主要用shell脚本实现
-
-## 使用手册
+这是一个通过集合了 odbc 外部表创建、内部表创建以及数据同步等功能来帮助 MySQL 用户使用 Doris 的易用工具。
mysql to doris
代码[这里](https://github.com/apache/doris/tree/master/extension/mysql_to_doris)
-### 目录结构
+## 目录结构
```text
-├── mysql_to_doris
-│ ├── conf
-│ │ ├── doris.conf
-│ │ ├── mysql.conf
-│ │ └── tables
-│ ├── all_tables.sh
-│ │
-└── └── user_define_tables.sh
+├── bin
+│ └── run.sh
+├── conf
+│ ├── doris_external_tables
+│ ├── doris_tables
+│ ├── env.conf
+│ └── mysql_tables
+└── lib
+ ├── jdbc
+ │ ├── create_jdbc_catalog.sh
+ │ └── sync_to_doris.sh
+ ├── e_auto.sh
+ ├── e_mysql_to_doris.sh
+ ├── get_tables.sh
+ ├── mysql_to_doris.sh
+ ├── mysql_type_convert.sh
+ ├── sync_check.sh
+ └── sync_to_doris.sh
```
-1. all_tables.sh
+## 配置信息
- 这个脚本主要是读取mysql指定库下的所有表，自动创建Doris odbc外表
+所有配置文件都在`conf`目录下。
-2. user_define_tables.sh
+### env.conf
+在这里配置 MySQL 和 Doris 的相关配置信息。
+```text
+# doris env
+# doris env
+fe_master_host=<your_fe_master_host>
+fe_master_port=<your_fe_master_query_port>
+doris_username=<your_doris_username>
+doris_password=<your_doris_password>
+doris_odbc_name=<your_doris_odbc_driver_name>
+doris_jdbc_catalog=<jdbc_catalog_name>
+doris_jdbc_default_db=<jdbc_default_database>
+doris_jdbc_driver_url=<jdbc_driver_url>
+doris_jdbc_driver_class=<jdbc_driver_class>
+
+# mysql env
+mysql_host=<your_mysql_host>
+mysql_port=<your_mysql_port>
+mysql_username=<your_mysql_username>
+mysql_password=<your_mysql_password>
+```
- 这个脚本主要用于用户自定义指定mysql库下某几张表，自动创建Doris odbc外表
+### mysql_tables
+在这里配置 MySQL 表信息，以`database.table`的形式。
+```text
+db1.table1
+db1.table2
+db2.table3
+```
-3. conf
+### doris_tables
+在这里配置 Doris Olap 表信息，以`database.table`的形式。
+```text
+doris_db.table1
+doris_db.table2
+doris_db.table3
+```
-
配置文件，`doris.conf`主要是配置doris相关的，`mysql.conf`主要配置mysql相关的，`tables`主要用于配置用户自定义mysql库的表
+### doris_external_tables
+在这里配置 Doris ODBC 外部表信息，以`database.table`的形式。
+```text
+doris_db.e_table1
+doris_db.e_table2
+doris_db.e_table3
+```
-### 全量
+## 如何使用
+bin/run.sh 是启动的 shell 脚本，下面是脚本的参数选项：
+```shell
+Usage: run.sh [option]
+ -e, --create-external-table: create doris external table
+ -o, --create-olap-table: create doris olap table
+ -i, --insert-data: insert data into doris olap table from doris external
table
+ -d, --drop-external-table: drop doris external table
+ -a, --auto-external-table: create doris external table and auto check
mysql schema change
+ --database: specify the database name to process all tables under the
entire database, and separate multiple databases with ","
+ -t, --type: specify external table type, valid options: ODBC(default), JDBC
+ -h, --help: show usage
+```
-1. 下载使用mysql to
doris[这里](https://github.com/apache/doris/tree/master/extension/mysql_to_doris)
+### 创建 Doris ODBC 外部表
+使用方法如下：
+```shell
+sh bin/run.sh --create-external-table
+```
+或者
+```shell
+sh bin/run.sh -e
+```
+执行完成后 ODBC 外部表就创建完成，同时建表语句会被生成到`result/mysql/e_mysql_to_doris.sql`文件中。
-2. 配置相关文件
+### 创建 Doris OLAP 表
+使用方法如下：
+```shell
+sh bin/run.sh --create-olap-table
+```
+或者
+```shell
+sh bin/run.sh -o
+```
+执行完成后 ODBC OLAP 表就创建完成，同时建表语句会被生成到`result/mysql/mysql_to_doris.sql`文件中。
+
+如果设置 `--type` 选项为 `JDBC`，则会创建 JDBC Catalog，同时创建语句语句会被生成到
`result/mysql/jdbc_catalog.sql` 文件中。
- ```shell
- #doris.conf
- master_host=
- master_port=
- doris_password=
- doris_odbc_name=''
-
- #mysql.conf
- mysql_host=
- mysql_password=
- ```
+### 创建 Doris OLAP 表同时从外部同步数据
- | 配置项 | 说明 |
- |------------------| ----------------------- |
- | master_host | Doris FE master节点IP |
- | master_port | Doris FE query_port端口 |
- | doris_password | Doris 密码(默认root用户) |
- | doris_odbc_name | be/conf 下odbcinst.ini配置文件中mysql odbc的名称 |
- | mysql_host | Mysql IP |
- | mysql_password | Mysql 密码(默认root用户) |
+前提是你已经创建外部表（JDBC 方式则为 JDBC Catalog），如果没有，请先创建。
+
+使用方法如下：
+```shell
+sh bin/run.sh --create-olap-table --insert-data
+```
+或者
+```shell
+sh bin/run.sh -o -i
+```
+执行完成后 ODBC OLAP
表就创建完成，同时建表语句会被生成到`result/mysql/mysql_to_doris.sql`文件中，并且同步语句会被生成到`result/mysql/sync_to_doris.sql`文件中。
+#### 同步结果检查
+同步数据之后会执行同步结果检查任务，对olap表和mysql表的数据量进行对比，检查结果保存在 `result/mysql/sync_check` 文件中。
-3. 执行`all_tables.sh`脚本
+#### 删除 ODBC 外部表
+如果在数据同步执行完成后想要删除 ODBC 外部表，添加`--drop-external-table`或`-d`选项。
+使用方式如下：
+```shell
+sh bin/run.sh --create-olap-table --insert-data --drop-external-table
```
-sh all_tables.sh mysql_db_name doris_db_name
+或者
+```shell
+sh bin/run.sh -o -i -d
```
-执行成功后会生成 files目录，该目录包含`tables`（表名称） 和 `tables.sql` （doris odbc建表语句）
+此选项只当 `--type` 为 `ODBC` 时有效。
-### 自定义
+### 创建 Doris OLAP 表并且自动同步表结构变化
+使用方式如下：
+```shell
+sh bin/run.sh --auto-external-table
+```
+或者
+```shell
+sh bin/run.sh -a
+```
+
+程序会在后台执行，进程 ID 被保存到`e_auto.pid`文件。
-1. 修改`conf/tables`文件，添加需要创建doris odbc的表
-2. 配置mysql和doris相关信息，参考全量创建第2步
-3. 执行`user_define_tables.sh`脚本
+### 通过指定数据库来处理
+如果你的表比较多，并且不需要自定义doris表名，可以通过`--databases`选项指定要处理的数据库名，无需手动配置。
+
+使用方式如下：
+```shell
+# 单个数据库
+sh bin/run.sh --databases db1
```
-sh user_define_tables.sh mysql_db_name doris_db_name
+或者
+```shell
+# 多个数据库
+sh bin/run.sh --databases db1,db2,db3
```
-执行成功后会生成 user_files目录，该目录包含 `tables.sql` （doris odbc建表语句）
+通过这个选项，程序会自动获取mysql指定数据库下的全部表，并生成mysql_tables,
doris_tables和doris_external_tables的配置。
+
+**请注意，该选项需要配合其他选项一起使用。**
diff --git a/extension/mysql_to_doris/README.md
b/extension/mysql_to_doris/README.md
index 351f2b439f..185b068bea 100644
--- a/extension/mysql_to_doris/README.md
+++ b/extension/mysql_to_doris/README.md
@@ -17,18 +17,175 @@ specific language governing permissions and limitations
under the License.
-->
-How to do?
+# Mysql to doris
-1、To configure mysql.conf and doris.conf in the conf directory,the conf
including
-host、port and password
+This is an easy-to-use tool to help mysql users on using doris.
-2、To configure mysql_tables and doris_tables in the conf directory,the conf is
user need to synchronization tables and want to get table name
+## Directory Structure
+```text
+├── bin
+│ └── run.sh
+├── conf
+│ ├── doris_external_tables
+│ ├── doris_tables
+│ ├── env.conf
+│ └── mysql_tables
+└── lib
+ ├── jdbc
+ │ ├── create_jdbc_catalog.sh
+ │ └── sync_to_doris.sh
+ ├── e_auto.sh
+ ├── e_mysql_to_doris.sh
+ ├── get_tables.sh
+ ├── mysql_to_doris.sh
+ ├── mysql_type_convert.sh
+ ├── sync_check.sh
+ └── sync_to_doris.sh
+```
-3、To execute e_mysql_to_doris.sh by sh e_mysql_to_doris.sh
+## configurations
-4、To execute e_auto.sh by nohup sh e_auto.sh &
+All configuration files are in the conf directory.
-What do you get?
+### env.conf
+Fill in your doris and mysql information here.
+```text
+# doris env
+fe_master_host=<your_fe_master_host>
+fe_master_port=<your_fe_master_query_port>
+doris_username=<your_doris_username>
+doris_password=<your_doris_password>
+doris_odbc_name=<your_doris_odbc_driver_name>
+doris_jdbc_catalog=<jdbc_catalog_name>
+doris_jdbc_default_db=<jdbc_default_database>
+doris_jdcb_driver_url=<jdcb_driver_url>
+doris_jdbc_driver_class=<jdbc_driver_class>
-A simple configuration synchronizes all configured tables and Monitor Mysql
metadata changes in real time
+# mysql env
+mysql_host=<your_mysql_host>
+mysql_port=<your_mysql_port>
+mysql_username=<your_mysql_username>
+mysql_password=<your_mysql_password>
+```
+### mysql_tables
+Fill in the mysql table here, in the form of `database.table`.
+```text
+db1.table1
+db1.table2
+db2.table3
+```
+
+### doris_tables
+Fill in the doris olap table here, in the form of `database.table`.
+```text
+doris_db.table1
+doris_db.table2
+doris_db.table3
+```
+
+### doris_external_tables
+Fill in the doris external table here, in the form of `database.table`.
+```text
+doris_db.e_table1
+doris_db.e_table2
+doris_db.e_table3
+```
+
+## How to use
+bin/run.sh is the startup shell script and this is options for the script:
+```shell
+Usage: run.sh [option]
+ -e, --create-external-table: create doris external table
+ -o, --create-olap-table: create doris olap table
+ -i, --insert-data: insert data into doris olap table from doris external
table
+ -d, --drop-external-table: drop doris external table
+ -a, --auto-external-table: create doris external table and auto check
mysql schema change
+ --database: specify the database name to process all tables under the
entire database, and separate multiple databases with ","
+ -t, --type: specify external table type, valid options: ODBC(default), JDBC
+ -h, --help: show usage
+```
+
+### 1. Create doris external table
+use like this:
+```shell
+sh bin/run.sh --create-external-table
+```
+or
+```shell
+sh bin/run.sh -e
+```
+then doris odbc external table has been created, and the table creation
statement will be generated in `result/mysql/e_mysql_to_doris.sql` file.
+
+If `--type` option is specified as `JDBC`, create jdbc catalog statement will
be generated in `result/mysql/jdbc_catalog.sql` file.
+
+### 2. Create doris olap table
+use like this:
+```shell
+sh bin/run.sh --create-olap-table
+```
+or
+```shell
+sh bin/run.sh -o
+```
+then doris odbc olap table has been created, and the table creation statement
will be generated in `result/mysql/mysql_to_doris.sql` file.
+
+### 3. Create doris olap table and sync data from odbc external table
+The premise is that you have created the external table, if not, please create
it first.
+
+use like this:
+```shell
+sh bin/run.sh --create-olap-table --insert-data
+```
+or
+```shell
+sh bin/run.sh -o -i
+```
+then doris odbc olap table has been created, and the table creation statement
will be generated in `result/mysql/mysql_to_doris.sql` file, and the insert
statement is generated in `result/mysql/sync_to_doris.sql` file.
+
+#### Sync result check
+After the data is synchronized, the task of checking the synchronization
results will be executed to compare the data volume of the olap table and mysql
table, and the checking results will be saved in the `result/mysql/sync_check`
file.
+
+#### Drop external table
+If you want to drop odbc external table after data sync finished, add
`--drop-external-table` or `-d` option.
+
+use like this:
+```shell
+sh bin/run.sh --drop-external-table
+```
+or
+```shell
+sh bin/run.sh -d
+```
+
+this option is valid for `ODBC` type.
+
+### 4. Create doris external table and sync schema change automatically
+use like this:
+```shell
+sh bin/run.sh --auto-external-table
+```
+or
+```shell
+sh bin/run.sh -a
+```
+
+the program will run in the background, process id is saved in `e_auto.pid`,
and this option is valid for `ODBC` type.
+
+### Process by specifying the database
+If you have a large number of tables and do not need to customize the doris
table name, you can specify the database name to be processed through the
`--databases` option without manual configuration.
+
+use like this:
+```shell
+# single database
+sh bin/run.sh --database db1
+```
+or
+```shell
+# multiple databases
+sh bin/run.sh --database db1,db2,db3
+```
+
+With this option, the program will automatically obtain all the tables under
the specified mysql database, and generate the configuration of `mysql_tables`,
`doris_tables` and `doris_external_tables`.
+
+**Please note that this option needs to be used in conjunction with other
options.**
diff --git a/extension/mysql_to_doris/bin/e_auto.sh
b/extension/mysql_to_doris/bin/e_auto.sh
deleted file mode 100644
index 0951d9111b..0000000000
--- a/extension/mysql_to_doris/bin/e_auto.sh
+++ /dev/null
@@ -1,52 +0,0 @@
-# Licensed to the Apache Software Foundation (ASF) under one
-# or more contributor license agreements. See the NOTICE file
-# distributed with this work for additional information
-# regarding copyright ownership. The ASF licenses this file
-# to you under the Apache License, Version 2.0 (the
-# "License"); you may not use this file except in compliance
-# with the License. You may obtain a copy of the License at
-#
-# http://www.apache.org/licenses/LICENSE-2.0
-#
-# Unless required by applicable law or agreed to in writing,
-# software distributed under the License is distributed on an
-# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-# KIND, either express or implied. See the License for the
-# specific language governing permissions and limitations
-# under the License.
-#!/bin/bash
-source ../conf/doris.conf
-#Execute the create external table sql
-echo "source ../result/e_mysql_to_doris.sql;" |mysql -h$fe_host
-P$fe_master_port -uroot -p$fe_password 2>error.log
-
-#Circulation monitoring mysql or conf
-while (( 1 == 1 ))
-do
-
-#Monitor interval
-sleep 30
-
-#get new create table sql
-sh ./e_mysql_to_doris.sh ../result/new_e_mysql_to_doris.sql 2>error.log
-
-#get a md5 from old create table sql
-old=`md5sum ../result/e_mysql_to_doris.sql |awk -F ' ' '{print $1}'`
-
-#get a md5 from new create table sql
-new=`md5sum ../result/new_e_mysql_to_doris.sql |awk -F ' ' '{print $1}'`
-
- if [[ $old != $new ]];then
-#table charges to drop old table and create new table
- for table in $(cat ../conf/doris_tables |grep -v '#' | awk -F
'\n' '{print $1}')
- do
- echo "drop table if exists ${table};" |mysql -h$fe_host
-P$fe_master_port -uroot -p$fe_password
- done
- echo "source ../result/new_e_mysql_to_doris.sql;" |mysql
-h$fe_host -P$fe_master_port -uroot -p$fe_password 2>error.log
-#delete old create table
- rm -rf ../result/e_mysql_to_doris.sql
-#alter new table sql name
- mv ../result/new_e_mysql_to_doris.sql
../result/e_mysql_to_doris.sql
- fi
-#if table no charge delete new create table
- rm -f ../result/new_e_mysql_to_doris.sql
-done
diff --git a/extension/mysql_to_doris/bin/e_mysql_to_doris.sh
b/extension/mysql_to_doris/bin/e_mysql_to_doris.sh
deleted file mode 100644
index 823a32ea41..0000000000
--- a/extension/mysql_to_doris/bin/e_mysql_to_doris.sh
+++ /dev/null
@@ -1,99 +0,0 @@
-# Licensed to the Apache Software Foundation (ASF) under one
-# or more contributor license agreements. See the NOTICE file
-# distributed with this work for additional information
-# regarding copyright ownership. The ASF licenses this file
-# to you under the Apache License, Version 2.0 (the
-# "License"); you may not use this file except in compliance
-# with the License. You may obtain a copy of the License at
-#
-# http://www.apache.org/licenses/LICENSE-2.0
-#
-# Unless required by applicable law or agreed to in writing,
-# software distributed under the License is distributed on an
-# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-# KIND, either express or implied. See the License for the
-# specific language governing permissions and limitations
-# under the License.
-#!/bin/bash
-source ../conf/mysql.conf
-source ../conf/doris.conf
-
-#mkdir files to store tables and tables.sql
-mkdir -p ../result
-
-#The default path is ../result/mysql_to_doris.sql for create table sql
-path=${1:-../result/mysql_to_doris.sql}
-
-#delete sql file if it is exists
-rm -f $path
-
-
-#get create table sql for mysql
-for table in $(cat ../conf/mysql_tables |grep -v '#' | awk -F '\n' '{print
$1}')
- do
- d_d=$(echo $table |awk -F '.' '{print $1}')
- d_t=$(echo $table |awk -F '.' '{print $2}')
- echo "show create table \`$d_d\`.\`$d_t\`;" |mysql -h$mysql_host
-uroot -p$mysql_password >> $path
-done
-
-#adjust sql
-awk -F '\t' '{print $2}' $path |awk '!(NR%2)' |awk '{print $0 ";"}' >
../result/tmp111.sql
-sed -i 's/\\n/\n/g' ../result/tmp111.sql
-sed -n '/CREATE TABLE/,/ENGINE\=/p' ../result/tmp111.sql > ../result/tmp222.sql
-
-
-
-#delete tables special struct
-sed -i '/^ CON/d' ../result/tmp222.sql
-sed -i '/^ KEY/d' ../result/tmp222.sql
-rm -rf $path
-rm -rf ../result/tmp111.sql
-mv ../result/tmp222.sql $path
-
-#start transform tables struct
-sed -i '/ENGINE=/a) ENGINE=ODBC\n COMMENT "ODBC"\nPROPERTIES (\n"host" =
"ApacheDorisHostIp",\n"port" = "3306",\n"user" = "root",\n"password" =
"ApacheDorisHostPassword",\n"database" = "ApacheDorisDataBases",\n"table" =
"ApacheDorisTables",\n"driver" = "MySQL",\n"odbc_type" = "mysql");' $path
-sed -i "s/\"driver\" = \"MySQL\"/\"driver\" = \"$doris_odbc_name\"/g" $path
-
-
-#delete match line
-sed -i '/PRIMARY KEY/d' $path
-sed -i '/UNIQUE KEY/d' $path
-#delete , at the beginning (
-sed -i '/,\s*$/{:loop; N; /,\(\s*\|\n\))/! bloop; s/,\s*[\n]\?\s*)/\n)/}' $path
-
-#delete a line on keyword
-sed -i -e '$!N;/\n.*ENGINE=ODBC/!P;D' $path
-#replace mysql password、database、table、host
-
-
-
-
-for t_name in $(cat ../conf/mysql_tables |grep -v '#' | awk -F '\n' '{print
$1}')
- do
- d=`echo $t_name | awk -F '.' '{print $1}'`
- t=`echo $t_name | awk -F '.' '{print $2}'`
- sed -i "0,/ApacheDorisHostIp/s/ApacheDorisHostIp/${mysql_host}/" $path
- sed -i
"0,/ApacheDorisHostPassword/s/ApacheDorisHostPassword/${mysql_password}/" $path
- sed -i "0,/ApacheDorisDataBases/s/ApacheDorisDataBases/$d/" $path
- sed -i "0,/ApacheDorisTables/s/ApacheDorisTables/$t/" $path
-done
-
-
-
-#do transfrom from mysql to doris external
-sh ../lib/mysql_to_doris.sh $path
-
-#get an orderly table name and add if not exists statement
-x=0
-for table in $(cat ../conf/doris_tables |grep -v '#' | awk -F '\n' '{print
$1}')
- do
- let x++
- d_t=`cat ../conf/mysql_tables |grep -v '#' | awk "NR==$x{print}" |awk
-F '.' '{print $2}'`
- sed -i "s/TABLE \`$d_t\`/TABLE if not exists $table/g" $path
-done
-
-#create database
-for d_doris in $(cat ../conf/doris_tables |grep -v '#' | awk -F '\n' '{print
$1}' |awk -F '.' '{print $1}' |sort -u)
-do
- sed -i "1icreate database if not exists $d_doris;" $path
-done
diff --git a/extension/mysql_to_doris/bin/run.sh
b/extension/mysql_to_doris/bin/run.sh
new file mode 100644
index 0000000000..6eb472b183
--- /dev/null
+++ b/extension/mysql_to_doris/bin/run.sh
@@ -0,0 +1,199 @@
+#!/bin/bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+function usage() {
+ echo "Usage: run.sh [option]"
+ echo " -e, --create-external-table: create doris external table"
+ echo " -o, --create-olap-table: create doris olap table"
+ echo " -i, --insert-data: insert data into doris olap table from doris
external table"
+ echo " -d, --drop-external-table: drop doris external table"
+ echo " -a, --auto-external-table: create doris external table and auto
check mysql schema change"
+ echo " --database: specify the database name to process all tables under
the entire database, and separate multiple databases with \",\""
+ echo " -t, --type: specify external table type, valid options:
ODBC(default), JDBC"
+ echo " -h, --help: show usage"
+ exit 1
+}
+
+cur_dir=$(cd "$(dirname "${BASH_SOURCE[0]}")" &>/dev/null && pwd)
+
+if [[ $# -eq 0 ]]; then
+ usage
+fi
+
+opts=$(getopt -o eaoidht: \
+ -l create-external-table \
+ -l create-olap-table \
+ -l insert-datadrop-external-table \
+ -l auto-external-table \
+ -l database: \
+ -l type: \
+ -l help \
+ -n "$0" \
+ -- "$@")
+
+eval set -- "${opts}"
+
+CREATE_EXTERNAL_TABLE=0
+CREATE_OLAP_TABLE=0
+INSERT_DATA=0
+DROP_EXTERNAL_TABLE=0
+AUTO_EXTERNAL_TABLE=0
+DATABASE=''
+TYPE='ODBC'
+
+while true; do
+ case "$1" in
+ -e | --create-external-table)
+ CREATE_EXTERNAL_TABLE=1
+ shift
+ ;;
+ -o | --create-olap-table)
+ CREATE_OLAP_TABLE=1
+ shift
+ ;;
+ -i | --insert-data)
+ INSERT_DATA=1
+ shift
+ ;;
+ -d | --drop-external-table)
+ DROP_EXTERNAL_TABLE=1
+ shift
+ ;;
+ -a | --auto-external-table)
+ AUTO_EXTERNAL_TABLE=1
+ shift
+ ;;
+ --database)
+ DATABASE="$2"
+ shift 2
+ ;;
+ -t | --type)
+ TYPE="$2"
+ shift 2
+ ;;
+ -h | --help)
+ usage
+ shift
+ ;;
+ --)
+ shift
+ break
+ ;;
+ *)
+ echo "Internal error"
+ exit 1
+ ;;
+ esac
+done
+
+home_dir=$(cd "${cur_dir}"/.. && pwd)
+
+source "${home_dir}"/conf/env.conf
+
+# when fe_password is not set or is empty, do not put -p option
+use_passwd=$([ -z "${doris_password}" ] && echo "" || echo
"-p${doris_password}")
+
+if [ -n "${DATABASE}" ]; then
+ sh "${home_dir}"/lib/get_tables.sh "${DATABASE}"
+fi
+
+# create doris jdbc catalog
+if [[ "JDBC" == "${TYPE}" && "${CREATE_EXTERNAL_TABLE}" -eq 1 ]]; then
+ echo "====================== start create doris jdbc catalog
======================"
+ sh "${home_dir}"/lib/jdbc/create_jdbc_catalog.sh
"${home_dir}"/result/mysql/jdbc_catalog.sql 2>>error.log
+ echo "source ${home_dir}/result/mysql/jdbc_catalog.sql;" | mysql
-h"${fe_master_host}" -P"${fe_master_port}" -u"${doris_username}"
"${use_passwd}" 2>>error.log
+ res=$?
+ if [ "${res}" != 0 ]; then
+ echo "====================== create doris jdbc catalog failed
======================"
+ exit "${res}"
+ fi
+ echo "====================== create doris jdbc catalog finished
======================"
+fi
+
+# create doris external table
+if [[ "ODBC" == "${TYPE}" && "${CREATE_EXTERNAL_TABLE}" -eq 1 ]]; then
+ echo "====================== start create doris external table
======================"
+ sh "${home_dir}"/lib/e_mysql_to_doris.sh
"${home_dir}"/result/mysql/e_mysql_to_doris.sql 2>error.log
+ echo "source ${home_dir}/result/mysql/e_mysql_to_doris.sql;" | mysql
-h"${fe_master_host}" -P"${fe_master_port}" -u"${doris_username}"
"${use_passwd}" 2>>error.log
+ res=$?
+ if [ "${res}" != 0 ]; then
+ echo "====================== create doris external table failed
======================"
+ exit "${res}"
+ fi
+ echo "====================== create doris external table finished
======================"
+fi
+
+# create doris olap table
+if [[ "${CREATE_OLAP_TABLE}" -eq 1 ]]; then
+ echo "====================== start create doris olap table
======================"
+ sh "${home_dir}"/lib/mysql_to_doris.sh
"${home_dir}"/result/mysql/mysql_to_doris.sql 2>>error.log
+ echo "source ${home_dir}/result/mysql/mysql_to_doris.sql;" | mysql
-h"${fe_master_host}" -P"${fe_master_port}" -u"${doris_username}"
"${use_passwd}" 2>>error.log
+ res=$?
+ if [ "${res}" != 0 ]; then
+ echo "====================== create doris olap table failed
======================"
+ exit "${res}"
+ fi
+ echo "====================== create doris olap table finished
======================"
+fi
+
+# insert data into doris olap table
+if [[ "${INSERT_DATA}" -eq 1 ]]; then
+ echo "====================== start insert data ======================"
+ if [[ "JDBC" == "${TYPE}" ]]; then
+ sh "${home_dir}"/lib/jdbc/sync_to_doris.sh
"${home_dir}"/result/mysql/sync_to_doris.sql 2>>error.log
+ else
+ sh "${home_dir}"/lib/sync_to_doris.sh
"${home_dir}"/result/mysql/sync_to_doris.sql 2>>error.log
+ fi
+ echo "source ${home_dir}/result/mysql/sync_to_doris.sql;" | mysql
-h"${fe_master_host}" -P"${fe_master_port}" -u"${doris_username}"
"${use_passwd}" 2>>error.log
+ res=$?
+ if [ "${res}" != 0 ]; then
+ echo "====================== insert data failed ======================"
+ exit "${res}"
+ fi
+ echo "====================== insert data finished ======================"
+ echo "====================== start sync check ======================"
+ sh "${home_dir}"/lib/sync_check.sh "${home_dir}"/result/mysql/sync_check
2>>error.log
+ res=$?
+ if [ "${res}" != 0 ]; then
+ echo "====================== sync check failed ======================"
+ exit "${res}"
+ fi
+ echo "====================== sync check finished ======================"
+fi
+
+# drop doris external table
+if [[ "ODBC" == "${TYPE}" && "${DROP_EXTERNAL_TABLE}" -eq 1 ]]; then
+ echo "====================== start drop doris external table
=========================="
+ for table in $(cat ${home_dir}/conf/doris_external_tables | grep -v '#' |
awk -F '\n' '{print $1}' | sed 's/\./`.`/g'); do
+ echo "DROP TABLE IF EXISTS \`${table}\`;" | mysql -h"${fe_master_host}"
-P"${fe_master_port}" -u"${doris_username}" "${use_passwd}" 2>>error.log
+ res=$?
+ if [ "${res}" != 0 ]; then
+ echo "====================== drop doris external table failed
======================"
+ exit "${res}"
+ fi
+ done
+ echo "====================== create drop external table finished
======================"
+fi
+
+# create doris external table and auto check mysql schema change
+if [[ "ODBC" == "${TYPE}" && "${AUTO_EXTERNAL_TABLE}" -eq 1 ]]; then
+ echo "====================== start auto doris external table
======================"
+ nohup sh ${home_dir}/lib/e_auto.sh &
+ echo $! >e_auto.pid
+ echo "====================== create doris external table started
======================"
+fi
diff --git a/extension/mysql_to_doris/conf/doris.conf
b/extension/mysql_to_doris/conf/doris_external_tables
similarity index 70%
copy from extension/mysql_to_doris/conf/doris.conf
copy to extension/mysql_to_doris/conf/doris_external_tables
index a870906795..e32e54bb8d 100644
--- a/extension/mysql_to_doris/conf/doris.conf
+++ b/extension/mysql_to_doris/conf/doris_external_tables
@@ -14,9 +14,10 @@
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
-
-
-master_host=192.168.0.151
-master_port=9030
-doris_password=123456
-doris_odbc_name='MySQL ODBC'
+####################################################################
+# The tables is used to define doris external tables
+####################################################################
+t_demo.e_t_cickp_charge_connector
+t_demo.e_t_cickp_charge_equipment
+p_demo.e_p_inspection_task
+p_demo.e_p_inspection_result_record
diff --git a/extension/mysql_to_doris/conf/doris_tables
b/extension/mysql_to_doris/conf/doris_tables
index 5bbc2710e4..8095c1bbaa 100644
--- a/extension/mysql_to_doris/conf/doris_tables
+++ b/extension/mysql_to_doris/conf/doris_tables
@@ -14,7 +14,10 @@
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
-demo.demo1
-demo.demo2
-demo.demo3
-demo2.demo
+####################################################################
+# The tables is used to define doris olap tables
+####################################################################
+doris.t_cickp_charge_connector
+doris.t_cickp_charge_equipment
+doris.p_inspection_task
+doris.p_inspection_result_record
diff --git a/extension/mysql_to_doris/conf/mysql.conf
b/extension/mysql_to_doris/conf/env.conf
similarity index 63%
rename from extension/mysql_to_doris/conf/mysql.conf
rename to extension/mysql_to_doris/conf/env.conf
index e017aedeef..f27c22ba54 100644
--- a/extension/mysql_to_doris/conf/mysql.conf
+++ b/extension/mysql_to_doris/conf/env.conf
@@ -15,6 +15,19 @@
# specific language governing permissions and limitations
# under the License.
+# doris env
+fe_master_host=127.0.0.1
+fe_master_port=9030
+doris_username=root
+doris_password=
+doris_odbc_name='MySQL ODBC 5.3 Unicode Driver'
+doris_jdbc_catalog='jdbc_catalog'
+doris_jdbc_default_db='information_schema'
+doris_jdbc_driver_url='https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar'
+doris_jdbc_driver_class='com.mysql.jdbc.Driver'
-mysql_host=192.168.0.171
+# mysql env
+mysql_host=127.0.0.1
+mysql_port=3306
+mysql_username=root
mysql_password=123456
diff --git a/extension/mysql_to_doris/lib/e_auto.sh
b/extension/mysql_to_doris/lib/e_auto.sh
new file mode 100644
index 0000000000..7e4e85c47c
--- /dev/null
+++ b/extension/mysql_to_doris/lib/e_auto.sh
@@ -0,0 +1,58 @@
+#!/bin/bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+cur_dir=$(cd "$(dirname "${BASH_SOURCE[0]}")" &>/dev/null && pwd)
+home_dir=$(cd "${cur_dir}"/.. && pwd)
+
+source ${home_dir}/conf/env.conf
+
+# when fe_password is not set or is empty, do not put -p option
+use_passwd=$([ -z "${doris_password}" ] && echo "" || echo
"-p${doris_password}")
+
+#Execute the create external table sql
+echo "source ${home_dir}/result/mysql/e_mysql_to_doris.sql;" | mysql
-h$fe_master_host -P$fe_master_port -u$doris_username "${use_passwd}"
2>>e_auto_error.log
+
+#Circulation monitoring mysql or conf
+while ((1 == 1)); do
+
+ #Monitor interval
+ sleep 30
+
+ #get new create table sql
+ sh ${home_dir}/lib/e_mysql_to_doris.sh
${home_dir}/result/mysql/new_e_mysql_to_doris.sql 2>>e_auto_error.log
+
+ #get a md5 from old create table sql
+ old=$(md5sum ${home_dir}/result/mysql/e_mysql_to_doris.sql | awk -F ' '
'{print $1}')
+
+ #get a md5 from new create table sql
+ new=$(md5sum ${home_dir}/result/mysql/new_e_mysql_to_doris.sql | awk -F '
' '{print $1}')
+
+ if [[ $old != $new ]]; then
+ #table charges to drop old table and create new table
+ for table in $(cat ${home_dir}/conf/doris_external_tables | grep -v
'#' | awk -F '\n' '{print $1}'); do
+ echo "DROP TABLE IF EXISTS ${table};" | mysql -h$fe_master_host
-P$fe_master_port -u$doris_username "${use_passwd}" 2>>e_auto_error.log
+ done
+ echo "source ${home_dir}/result/mysql/new_e_mysql_to_doris.sql;" |
mysql -h$fe_master_host -P$fe_master_port -u$doris_username "${use_passwd}"
2>>e_auto_error.log
+ #delete old create table
+ rm -rf ${home_dir}/result/mysql/e_mysql_to_doris.sql
+ #alter new table sql name
+ mv ${home_dir}/result/mysql/new_e_mysql_to_doris.sql
${home_dir}/result/mysql/e_mysql_to_doris.sql
+ fi
+ #if table no charge delete new create table
+ rm -f ${home_dir}/result/mysql/new_e_mysql_to_doris.sql
+done
diff --git a/extension/mysql_to_doris/lib/e_mysql_to_doris.sh
b/extension/mysql_to_doris/lib/e_mysql_to_doris.sh
new file mode 100644
index 0000000000..73b17a536c
--- /dev/null
+++ b/extension/mysql_to_doris/lib/e_mysql_to_doris.sh
@@ -0,0 +1,92 @@
+#!/bin/bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+cur_dir=$(cd "$(dirname "${BASH_SOURCE[0]}")" &>/dev/null && pwd)
+home_dir=$(cd "${cur_dir}"/.. && pwd)
+
+source ${home_dir}/conf/env.conf
+
+#mkdir files to store tables and tables.sql
+mkdir -p ${home_dir}/result/mysql
+
+#The default path is ../result/mysql_to_doris.sql for create table sql
+path=${1:-${home_dir}/result/mysql/e_mysql_to_doris.sql}
+
+#delete sql file if it is exists
+rm -f $path
+
+#get create table sql for mysql
+for table in $(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk -F '\n'
'{print $1}'); do
+ d_d=$(echo $table | awk -F '.' '{print $1}')
+ d_t=$(echo $table | awk -F '.' '{print $2}')
+ echo "show create table \`$d_d\`.\`$d_t\`;" | mysql -h$mysql_host
-P$mysql_port -u$mysql_username -p$mysql_password >>$path
+done
+
+#adjust sql
+awk -F '\t' '{print $2}' $path | awk '!(NR%2)' | awk '{print $0 ";"}'
>${home_dir}/result/mysql/tmp1.sql
+sed -i 's/\\n/\n/g' ${home_dir}/result/mysql/tmp1.sql
+sed -n '/CREATE TABLE/,/ENGINE\=/p' ${home_dir}/result/mysql/tmp1.sql
>${home_dir}/result/mysql/tmp2.sql
+
+#delete tables special struct
+sed -i '/^ CON/d' ${home_dir}/result/mysql/tmp2.sql
+sed -i '/^ KEY/d' ${home_dir}/result/mysql/tmp2.sql
+rm -rf $path
+rm -rf ${home_dir}/result/mysql/tmp1.sql
+mv ${home_dir}/result/mysql/tmp2.sql $path
+
+#start transform tables struct
+sed -i '/ENGINE=/a) ENGINE=ODBC\n COMMENT "ODBC"\nPROPERTIES (\n"host" =
"ApacheDorisHostIp",\n"port" = "MysqlPort",\n"user" =
"MysqlUsername",\n"password" = "ApacheDorisHostPassword",\n"database" =
"ApacheDorisDataBases",\n"table" = "ApacheDorisTables",\n"driver" =
"MySQL",\n"odbc_type" = "mysql");' $path
+sed -i "s/\"driver\" = \"MySQL\"/\"driver\" = \"$doris_odbc_name\"/g" $path
+sed -i "s/MysqlUsername/${mysql_username}/g" $path
+sed -i "s/MysqlPort/${mysql_port}/g" $path
+
+#delete match line
+sed -i '/PRIMARY KEY/d' $path
+sed -i '/UNIQUE KEY/d' $path
+#delete , at the beginning (
+sed -i '/,\s*$/{:loop; N; /,\(\s*\|\n\))/! bloop; s/,\s*[\n]\?\s*)/\n)/}' $path
+
+#delete a line on keyword
+sed -i -e '$!N;/\n.*ENGINE=ODBC/!P;D' $path
+#replace mysql password、database、table、host
+
+for t_name in $(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk -F '\n'
'{print $1}'); do
+ d=$(echo $t_name | awk -F '.' '{print $1}')
+ t=$(echo $t_name | awk -F '.' '{print $2}')
+ sed -i "0,/ApacheDorisHostIp/s/ApacheDorisHostIp/${mysql_host}/" $path
+ sed -i
"0,/ApacheDorisHostPassword/s/ApacheDorisHostPassword/${mysql_password}/" $path
+ sed -i "0,/ApacheDorisDataBases/s/ApacheDorisDataBases/$d/" $path
+ sed -i "0,/ApacheDorisTables/s/ApacheDorisTables/$t/" $path
+done
+
+#do transfrom from mysql to doris external
+sh ${home_dir}/lib/mysql_type_convert.sh $path
+
+#get an orderly table name and add if not exists statement
+x=0
+for table in $(cat ${home_dir}/conf/doris_external_tables | grep -v '#' | awk
-F '\n' '{print $1}'); do
+ let x++
+ d_t=$(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk
"NR==$x{print}" | awk -F '.' '{print $2}')
+ table=$(echo ${table} | sed 's/\./`.`/g')
+ sed -i "s/TABLE \`$d_t\`/TABLE IF NOT EXISTS \`$table\`/g" $path
+done
+
+#create database
+for d_doris in $(cat ${home_dir}/conf/doris_external_tables | grep -v '#' |
awk -F '\n' '{print $1}' | awk -F '.' '{print $1}' | sort -u); do
+ sed -i "1i CREATE DATABASE IF NOT EXISTS $d_doris;" $path
+done
diff --git a/extension/mysql_to_doris/lib/get_tables.sh
b/extension/mysql_to_doris/lib/get_tables.sh
new file mode 100644
index 0000000000..b1beae36bb
--- /dev/null
+++ b/extension/mysql_to_doris/lib/get_tables.sh
@@ -0,0 +1,35 @@
+#!/bin/bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+cur_dir="$(cd "$(dirname "$0")" && pwd)"
+home_dir=$(cd "${cur_dir}"/.. && pwd)
+
+source ${home_dir}/conf/env.conf
+
+dbs=$1
+if [ -z ${dbs} ]; then
+ echo "database option is empty"
+ exit 1
+fi
+
+# get mysql tables and write to ${home_dir}/conf/mysql_tables
+echo "select concat(TABLE_SCHEMA, '.', TABLE_NAME) from
information_schema.TABLES where TABLE_SCHEMA in ('$(echo ${dbs} | sed
"s/,/','/g")') and TABLE_TYPE = 'BASE TABLE' order by TABLE_SCHEMA,
TABLE_NAME;" | mysql -N -h"${mysql_host}" -P"${mysql_port}"
-u"${mysql_username}" -p"${mysql_password}" > "${home_dir}"/conf/mysql_tables
+# get mysql tables and generate external tables and write to
${home_dir}/conf/doris_external_tables
+echo "select concat(TABLE_SCHEMA , '.e_', TABLE_NAME) from
information_schema.TABLES where TABLE_SCHEMA in ('$(echo ${dbs} | sed
"s/,/','/g")') and TABLE_TYPE = 'BASE TABLE' order by TABLE_SCHEMA,
TABLE_NAME;" | mysql -N -h"${mysql_host}" -P"${mysql_port}"
-u"${mysql_username}" -p"${mysql_password}" >
"${home_dir}"/conf/doris_external_tables
+# get mysql tables and generate olap tables and write to
${home_dir}/conf/doris_tables
+echo "select concat(TABLE_SCHEMA , '.', TABLE_NAME) from
information_schema.TABLES where TABLE_SCHEMA in ('$(echo ${dbs} | sed
"s/,/','/g")') and TABLE_TYPE = 'BASE TABLE' order by TABLE_SCHEMA,
TABLE_NAME;" | mysql -N -h"${mysql_host}" -P"${mysql_port}"
-u"${mysql_username}" -p"${mysql_password}" > "${home_dir}"/conf/doris_tables
\ No newline at end of file
diff --git a/extension/mysql_to_doris/conf/doris.conf
b/extension/mysql_to_doris/lib/jdbc/create_jdbc_catalog.sh
similarity index 54%
rename from extension/mysql_to_doris/conf/doris.conf
rename to extension/mysql_to_doris/lib/jdbc/create_jdbc_catalog.sh
index a870906795..5633d910d1 100644
--- a/extension/mysql_to_doris/conf/doris.conf
+++ b/extension/mysql_to_doris/lib/jdbc/create_jdbc_catalog.sh
@@ -1,3 +1,4 @@
+#!/bin/bash
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements. See the NOTICE file
# distributed with this work for additional information
@@ -15,8 +16,24 @@
# specific language governing permissions and limitations
# under the License.
+cur_dir=$(cd "$(dirname "${BASH_SOURCE[0]}")" &>/dev/null && pwd)
+home_dir=$(cd "${cur_dir}"/../.. && pwd)
-master_host=192.168.0.151
-master_port=9030
-doris_password=123456
-doris_odbc_name='MySQL ODBC'
+source ${home_dir}/conf/env.conf
+
+# mkdir files to store tables and tables.sql
+mkdir -p ${home_dir}/result/mysql
+
+path=${1:-${home_dir}/result/mysql/jdbc_catalog.sql}
+
+rm -f $path
+
+echo 'CREATE CATALOG IF NOT EXISTS '${doris_jdbc_catalog}'
+PROPERTIES (
+ "type"="jdbc",
+ "user"="'${mysql_username}'",
+ "password"="'${mysql_password}'",
+
"jdbc_url"="jdbc:mysql://'${mysql_host}:${mysql_port}/${doris_jdbc_default_db}'?useSSL=false",
+ "driver_url"="'${doris_jdcb_driver_url}'",
+ "driver_class"="'${doris_jdbc_driver_class}'"
+); ' >> $path
diff --git a/extension/mysql_to_doris/lib/jdbc/sync_to_doris.sh
b/extension/mysql_to_doris/lib/jdbc/sync_to_doris.sh
new file mode 100644
index 0000000000..641925d5ab
--- /dev/null
+++ b/extension/mysql_to_doris/lib/jdbc/sync_to_doris.sh
@@ -0,0 +1,42 @@
+#!/bin/bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+cur_dir="$(cd "$(dirname "$0")" && pwd)"
+home_dir=$(cd "${cur_dir}"/../.. && pwd)
+
+source ${home_dir}/conf/env.conf
+
+#mkdir files to store tables and tables.sql
+mkdir -p ${home_dir}/result/mysql
+
+#The default path is ../result/mysql_to_doris.sql for create table sql
+path=${1:-${home_dir}/result/mysql/sync_to_doris.sql}
+
+rm -f $path
+
+# generate insert into select statement
+idx=0
+for table in $(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk -F '\n'
'{print $1}' | sed 's/ //g' | sed '/^$/d'); do
+ m_d=$(echo $table | awk -F '.' '{print $1}')
+ m_t=$(echo $table | awk -F '.' '{print $2}')
+ # get mysql table columns
+ columns=$(echo "SELECT group_concat(COLUMN_NAME) FROM
information_schema.COLUMNS WHERE TABLE_SCHEMA = '$m_d' AND TABLE_NAME =
'$m_t';" | mysql -N -h"${mysql_host}" -P"${mysql_port}" -u"${mysql_username}"
-p"${mysql_password}")
+ let idx++
+ i_t=$(cat ${home_dir}/conf/doris_tables | grep -v '#' | awk
"NR==$idx{print}" | sed 's/ //g' | sed 's/\./`.`/g')
+ echo "INSERT INTO \`${i_t}\` (${columns}) SELECT ${columns} FROM
\`${doris_jdbc_catalog}\`.\`${m_d}\`.\`${m_t}\`;" >> $path
+done
\ No newline at end of file
diff --git a/extension/mysql_to_doris/lib/mysql_to_doris.sh
b/extension/mysql_to_doris/lib/mysql_to_doris.sh
index 3fb3605df2..00098103a6 100644
--- a/extension/mysql_to_doris/lib/mysql_to_doris.sh
+++ b/extension/mysql_to_doris/lib/mysql_to_doris.sh
@@ -1,3 +1,4 @@
+#!/bin/bash
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements. See the NOTICE file
# distributed with this work for additional information
@@ -14,48 +15,72 @@
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
-#!/bin/bash
-path=$1
-sed -i 's/AUTO_INCREMENT//g' $path
-sed -i 's/CHARACTER SET utf8 COLLATE utf8_bin//g' $path
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci//g' $path
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin//g' $path
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci//g' $path
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8_general_ci//g' $path
-sed -i 's/CHARACTER SET utf8 COLLATE utf8_general_ci//g' $path
-sed -i 's/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP//g' $path
-sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP//g' $path
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin//g' $path
-sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP//g' $path
-sed -i 's/DEFAULT CURRENT_TIMESTAMP//g' $path
-sed -i 's/CHARACTER SET utf8mb4//g' $path
-sed -i 's/CHARACTER SET utf8//g' $path
-sed -i 's/COLLATE utf8mb4_general_ci//g' $path
-sed -i 's/COLLATE utf8_general_ci//g' $path
-sed -i 's/COLLATE utf8_bin//g' $path
-sed -i 's/\<tinytext\>/varchar(65533)/g' $path
-sed -i 's/\<text\>/varchar(65533)/g' $path
-sed -i 's/\<mediumtext\>/varchar(65533)/g' $path
-sed -i 's/\<longtext\>/varchar(65533)/g' $path
-sed -i 's/\<tinyblob\>/varchar(65533)/g' $path
-sed -i 's/\<blob\>/varchar(65533)/g' $path
-sed -i 's/\<mediumblob\>/varchar(65533)/g' $path
-sed -i 's/\<longblob\>/varchar(65533)/g' $path
-sed -i 's/\<tinystring\>/varchar(65533)/g' $path
-sed -i 's/\<mediumstring\>/varchar(65533)/g' $path
-sed -i 's/\<longstring\>/varchar(65533)/g' $path
-sed -i 's/\<timestamp\>/datetime/g' $path
-sed -i 's/\<unsigned\>//g' $path
-sed -i 's/\<zerofill\>//g' $path
-sed -i 's/\<json\>/varchar(65533)/g' $path
-sed -i 's/enum([^)]*)/varchar(65533)/g' $path
-sed -i 's/\<set\>/varchar(65533)/g' $path
-sed -i 's/\<bit\>/varchar(65533)/g' $path
-sed -i 's/\<string\>/varchar(65533)/g' $path
-sed -i 's/\<binary\>/varchar(65533)/g' $path
-sed -i 's/\<varbinary\>/varchar(65533)/g' $path
-sed -i 's/decimal([^)]*)/double/g' $path
-sed -i 's/varbinary([^)]*)/varchar(65533)/g' $path
-sed -i 's/binary([^)]*)/varchar(65533)/g' $path
-sed -i 's/string([^)]*)/varchar(65533)/g' $path
-sed -i 's/datetime([^)]*)/varchar(65533)/g' $path
+
+cur_dir="$(cd "$(dirname "$0")" && pwd)"
+home_dir=$(cd "${cur_dir}"/.. && pwd)
+
+source ${home_dir}/conf/env.conf
+
+#mkdir files to store tables and tables.sql
+mkdir -p ${home_dir}/result/mysql
+
+#The default path is ${home_dir}/result/mysql_to_doris.sql for create table sql
+path=${1:-${home_dir}/result/mysql/mysql_to_doris.sql}
+
+#delete sql file if it is exists
+rm -f $path
+
+#get create table sql for mysql
+for table in $(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk -F '\n'
'{print $1}' | sed 's/ //g' | sed '/^$/d'); do
+ m_d=$(echo $table | awk -F '.' '{print $1}')
+ m_t=$(echo $table | awk -F '.' '{print $2}')
+ echo "show create table \`$m_d\`.\`$m_t\`;" | mysql -h$mysql_host
-P$mysql_port -u$mysql_username -p$mysql_password >>$path
+done
+
+#adjust sql
+awk -F '\t' '{print $2}' $path | awk '!(NR%2)' | awk '{print $0 ";"}'
>${home_dir}/result/mysql/tmp1.sql
+sed -i 's/\\n/\n/g' ${home_dir}/result/mysql/tmp1.sql
+sed -n '/CREATE TABLE/,/ENGINE\=/p' ${home_dir}/result/mysql/tmp1.sql
>${home_dir}/result/mysql/tmp2.sql
+
+#delete tables special struct
+sed -i '/^ CON/d' ${home_dir}/result/mysql/tmp2.sql
+sed -i '/^ KEY/d' ${home_dir}/result/mysql/tmp2.sql
+rm -rf $path
+rm -rf ${home_dir}/result/mysql/tmp1.sql
+mv ${home_dir}/result/mysql/tmp2.sql $path
+
+#start transform tables struct
+sed -i '/ENGINE=/a) ENGINE=OLAP\nDUPLICATE KEY(APACHEDORISID1)\n COMMENT
"OLAP"\nDISTRIBUTED BY HASH(APACHEDORISID2) BUCKETS 10\nPROPERTIES
(\n"replication_allocation" = "tag.location.default: 3"\n);' $path
+
+#delete match line
+sed -i '/PRIMARY KEY/d' $path
+sed -i '/UNIQUE KEY/d' $path
+#delete , at the beginning (
+sed -i '/,\s*$/{:loop; N; /,\(\s*\|\n\))/! bloop; s/,\s*[\n]\?\s*)/\n)/}' $path
+
+#delete a line on keyword
+sed -i -e '$!N;/\n.*ENGINE=OLAP/!P;D' $path
+#replace mysql password、database、table、host
+
+for t_name in $(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk -F '\n'
'{print $1}' | sed 's/ //g' | awk -F '.' '{print $2}' | sed '/^$/d' | sed
's/^/`/g' | sed 's/$/`/g'); do
+ id=$(cat $path | grep -A 1 "$t_name" | grep -v "$t_name" | awk -F ' '
'{print $1}')
+ sed -i "0,/APACHEDORISID1/s/APACHEDORISID1/$id/" $path
+ sed -i "0,/APACHEDORISID2/s/APACHEDORISID2/$id/" $path
+done
+
+#do transfrom from mysql to doris external
+sh ${home_dir}/lib/mysql_type_convert.sh $path
+
+#get an orderly table name and add if not exists statement
+x=0
+for table in $(cat ${home_dir}/conf/doris_tables | grep -v '#' | sed 's/ //g'
| sed '/^$/d'); do
+ let x++
+ d_t=$(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk "NR==$x{print}"
| awk -F '.' '{print $2}' | sed 's/ //g')
+ table=$(echo ${table} | sed 's/\./`.`/g')
+ sed -i "s/TABLE \`$d_t\`/TABLE IF NOT EXISTS \`$table\`/g" $path
+done
+
+#create database
+for d_doris in $(cat ${home_dir}/conf/doris_tables | grep -v '#' | awk -F '\n'
'{print $1}' | awk -F '.' '{print $1}' | sed 's/ //g' | sed '/^$/d' | sort -u);
do
+ sed -i "1i CREATE DATABASE IF NOT EXISTS $d_doris;" $path
+done
diff --git a/extension/mysql_to_doris/lib/mysql_to_doris.sh
b/extension/mysql_to_doris/lib/mysql_type_convert.sh
similarity index 70%
copy from extension/mysql_to_doris/lib/mysql_to_doris.sh
copy to extension/mysql_to_doris/lib/mysql_type_convert.sh
index 3fb3605df2..cf52aa6f4b 100644
--- a/extension/mysql_to_doris/lib/mysql_to_doris.sh
+++ b/extension/mysql_to_doris/lib/mysql_type_convert.sh
@@ -1,3 +1,4 @@
+#!/bin/bash
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements. See the NOTICE file
# distributed with this work for additional information
@@ -14,30 +15,36 @@
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
-#!/bin/bash
path=$1
sed -i 's/AUTO_INCREMENT//g' $path
sed -i 's/CHARACTER SET utf8 COLLATE utf8_bin//g' $path
+sed -i 's/CHARACTER SET utf8mb3 COLLATE utf8mb3_bin//g' $path
sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci//g' $path
sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin//g' $path
sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci//g' $path
sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8_general_ci//g' $path
sed -i 's/CHARACTER SET utf8 COLLATE utf8_general_ci//g' $path
-sed -i 's/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP//g' $path
-sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP//g' $path
+sed -i 's/DEFAULT CURRENT_TIMESTAMP\(()\)\? ON UPDATE
CURRENT_TIMESTAMP\(()\)\?//ig' $path
+sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP\(()\)\?//ig' $path
sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin//g' $path
-sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP//g' $path
-sed -i 's/DEFAULT CURRENT_TIMESTAMP//g' $path
+sed -i "s/DEFAULT '0000-00-00 00:00:00'/DEFAULT '2000-01-01 00:00:00'/g" $path
+sed -i 's/DEFAULT CURRENT_TIMESTAMP\(()\)\?//ig' $path
+sed -i 's/DEFAULT b/DEFAULT/g' $path
+sed -i "s/DEFAULT \(\(\-\)\?[0-9]\+\(\.[0-9]\+\)\?\)/DEFAULT '\1'/g" $path
sed -i 's/CHARACTER SET utf8mb4//g' $path
sed -i 's/CHARACTER SET utf8//g' $path
sed -i 's/COLLATE utf8mb4_general_ci//g' $path
sed -i 's/COLLATE utf8_general_ci//g' $path
+sed -i 's/COLLATE utf8mb4_unicode_ci//g' $path
+sed -i 's/COLLATE utf8_unicode_ci//g' $path
sed -i 's/COLLATE utf8_bin//g' $path
sed -i 's/\<tinytext\>/varchar(65533)/g' $path
+sed -i 's/text([^)]*)/varchar(65533)/g' $path
sed -i 's/\<text\>/varchar(65533)/g' $path
sed -i 's/\<mediumtext\>/varchar(65533)/g' $path
sed -i 's/\<longtext\>/varchar(65533)/g' $path
sed -i 's/\<tinyblob\>/varchar(65533)/g' $path
+sed -i 's/blob([^)]*)/varchar(65533)/g' $path
sed -i 's/\<blob\>/varchar(65533)/g' $path
sed -i 's/\<mediumblob\>/varchar(65533)/g' $path
sed -i 's/\<longblob\>/varchar(65533)/g' $path
@@ -49,13 +56,21 @@ sed -i 's/\<unsigned\>//g' $path
sed -i 's/\<zerofill\>//g' $path
sed -i 's/\<json\>/varchar(65533)/g' $path
sed -i 's/enum([^)]*)/varchar(65533)/g' $path
+sed -i 's/set([^)]*)/varchar(65533)/g' $path
sed -i 's/\<set\>/varchar(65533)/g' $path
+sed -i 's/bit([^)]*)/varchar(65533)/g' $path
+sed -i 's/bit([^)]*)/varchar(65533)/g' $path
sed -i 's/\<bit\>/varchar(65533)/g' $path
-sed -i 's/\<string\>/varchar(65533)/g' $path
-sed -i 's/\<binary\>/varchar(65533)/g' $path
-sed -i 's/\<varbinary\>/varchar(65533)/g' $path
-sed -i 's/decimal([^)]*)/double/g' $path
sed -i 's/varbinary([^)]*)/varchar(65533)/g' $path
sed -i 's/binary([^)]*)/varchar(65533)/g' $path
sed -i 's/string([^)]*)/varchar(65533)/g' $path
-sed -i 's/datetime([^)]*)/varchar(65533)/g' $path
+sed -i 's/\<string\>/varchar(65533)/g' $path
+sed -i 's/\<binary\>/varchar(65533)/g' $path
+sed -i 's/\<varbinary\>/varchar(65533)/g' $path
+sed -i 's/\<mediumint/int/g' $path
+sed -i 's/float([^)]*)/float/g' $path
+sed -i 's/double([^)]*)/double/g' $path
+sed -i 's/\<time([^)]*)/varchar(64)/g' $path
+sed -i 's/\<time\>/varchar(64)/g' $path
+sed -i 's/year([^)]*)/varchar(64)/g' $path
+sed -i 's/\<year\>/varchar(64)/g' $path
\ No newline at end of file
diff --git a/extension/mysql_to_doris/lib/sync_check.sh
b/extension/mysql_to_doris/lib/sync_check.sh
new file mode 100644
index 0000000000..2a2be1056b
--- /dev/null
+++ b/extension/mysql_to_doris/lib/sync_check.sh
@@ -0,0 +1,53 @@
+#!/bin/bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+cur_dir="$(cd "$(dirname "$0")" && pwd)"
+home_dir=$(cd "${cur_dir}"/.. && pwd)
+
+source ${home_dir}/conf/env.conf
+
+#mkdir files to store tables and tables.sql
+mkdir -p ${home_dir}/result/mysql
+
+#The default path is ../result/mysql_to_doris.sql for create table sql
+path=${1:-${home_dir}/result/mysql/sync_check}
+
+rm -f $path
+
+# when fe_password is not set or is empty, do not put -p option
+use_passwd=$([ -z "${fe_password}" ] && echo "" || echo "-p${fe_password}")
+
+# generate insert into select statement
+idx=0
+for table in $(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk -F '\n'
'{print $1}' | sed 's/ //g' | sed '/^$/d'); do
+ m_d=$(echo $table | awk -F '.' '{print $1}')
+ m_t=$(echo $table | awk -F '.' '{print $2}')
+ let idx++
+ i_t=$(cat ${home_dir}/conf/doris_tables | grep -v '#' | awk
"NR==$idx{print}" | sed 's/ //g' | sed 's/\./`.`/g')
+ # get mysql table count
+ m_count=$(echo "SELECT count(*) FROM \`${m_d}\`.\`${m_t}\`;" | mysql
-N -h"${mysql_host}" -P"${mysql_port}" -u"$mysql_username" -p"$mysql_password")
+ d_count=$(echo "SELECT count(*) FROM \`${i_t}\`;" | mysql -N
-h"${fe_master_host}" -P"${fe_master_port}" -u"${doris_username}"
"${use_passwd}")
+ check=''
+ if [ $m_count -eq $d_count ]; then
+ check="${m_d}.${m_t}: ${m_count} ==> ${d_count}, ok"
+ else
+ check="${m_d}.${m_t}: ${m_count} ==> ${d_count}, check failed"
+ fi
+ echo "${check}" >> $path
+done
+exit $(grep "check failed" "${path}" | wc -l)
\ No newline at end of file
diff --git a/extension/mysql_to_doris/lib/sync_to_doris.sh
b/extension/mysql_to_doris/lib/sync_to_doris.sh
new file mode 100644
index 0000000000..0a20e27e22
--- /dev/null
+++ b/extension/mysql_to_doris/lib/sync_to_doris.sh
@@ -0,0 +1,43 @@
+#!/bin/bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+cur_dir="$(cd "$(dirname "$0")" && pwd)"
+home_dir=$(cd "${cur_dir}"/.. && pwd)
+
+source ${home_dir}/conf/env.conf
+
+#mkdir files to store tables and tables.sql
+mkdir -p ${home_dir}/result/mysql
+
+#The default path is ../result/mysql_to_doris.sql for create table sql
+path=${1:-${home_dir}/result/mysql/sync_to_doris.sql}
+
+rm -f $path
+
+# generate insert into select statement
+idx=0
+for table in $(cat ${home_dir}/conf/mysql_tables | grep -v '#' | awk -F '\n'
'{print $1}' | sed 's/ //g' | sed '/^$/d'); do
+ m_d=$(echo $table | awk -F '.' '{print $1}')
+ m_t=$(echo $table | awk -F '.' '{print $2}')
+ # get mysql table columns
+ columns=$(echo "SELECT group_concat(COLUMN_NAME) FROM
information_schema.COLUMNS WHERE TABLE_SCHEMA = '$m_d' AND TABLE_NAME =
'$m_t';" | mysql -N -h"${mysql_host}" -P"${mysql_port}" -u"${mysql_username}"
-p"${mysql_password}")
+ let idx++
+ i_t=$(cat ${home_dir}/conf/doris_tables | grep -v '#' | awk
"NR==$idx{print}" | sed 's/ //g' | sed 's/\./`.`/g')
+ e_t=$(cat ${home_dir}/conf/doris_external_tables | grep -v '#' | awk
"NR==$idx{print}" | sed 's/ //g' | sed 's/\./`.`/g')
+ echo "INSERT INTO \`${i_t}\` (${columns}) SELECT ${columns} FROM
\`${e_t}\`;" >> $path
+done
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
