This is an automated email from the ASF dual-hosted git repository.
jiafengzheng 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 54e6f12110 [improvement](mysql-to-doris)Fully resolved Mysql external
table issues (#13229)
54e6f12110 is described below
commit 54e6f12110e0aae1caf45b777f05d4912962b764
Author: toms <94617906+toms1...@users.noreply.github.com>
AuthorDate: Mon Oct 10 16:48:52 2022 +0800
[improvement](mysql-to-doris)Fully resolved Mysql external table issues
(#13229)
Fully resolved Mysql external table issues
---
extension/mysql_to_doris/README.md | 12 +-
extension/mysql_to_doris/all_tables.sh | 154 ---------------------
extension/mysql_to_doris/bin/e_auto.sh | 52 +++++++
extension/mysql_to_doris/bin/e_mysql_to_doris.sh | 97 +++++++++++++
.../mysql_to_doris/conf/{tables => doris_tables} | 28 +---
.../mysql_to_doris/conf/{tables => mysql_tables} | 25 +---
extension/mysql_to_doris/lib/mysql_to_doris.sh | 61 ++++++++
extension/mysql_to_doris/user_define_tables.sh | 154 ---------------------
8 files changed, 225 insertions(+), 358 deletions(-)
diff --git a/extension/mysql_to_doris/README.md
b/extension/mysql_to_doris/README.md
index 049dafcdf3..351f2b439f 100644
--- a/extension/mysql_to_doris/README.md
+++ b/extension/mysql_to_doris/README.md
@@ -22,11 +22,13 @@ How to do?
1、To configure mysql.conf and doris.conf in the conf directory,the conf
including
host、port and password
-2、sh all_tables.sh and give it two args,it is mysql database and doris database
+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
-In addition
+3、To execute e_mysql_to_doris.sh by sh e_mysql_to_doris.sh
-user can add mysql tables to the conf directory tables by sh
user_define_tables.sh
-can also be export mysql tables to doris
+4、To execute e_auto.sh by nohup sh e_auto.sh &
+
+What do you get?
+
+A simple configuration synchronizes all configured tables and Monitor Mysql
metadata changes in real time
-just do it
diff --git a/extension/mysql_to_doris/all_tables.sh
b/extension/mysql_to_doris/all_tables.sh
deleted file mode 100644
index 634ece0f71..0000000000
--- a/extension/mysql_to_doris/all_tables.sh
+++ /dev/null
@@ -1,154 +0,0 @@
-#!/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.
-
-
-####################################################################
-# This script is used to will mysql databases import doris by external
-####################################################################
-
-#reference configuration file
-source ./conf/mysql.conf
-source ./conf/doris.conf
-
-#define mysql database and doris database
-d_mysql=$1
-d_doris=$2
-
-#check args
-if [ ! -n "$1" ];then
- echo "please check source database"
- exit
-fi
-if [ ! -n "$2" ];then
- echo "please check sink database"
- exit
-fi
-
-#mkdir files to store tables and tables.sql
-mkdir -p files
-rm -rf ./files/tables
-rm -rf ./files/tables.sql
-#get tables from mysql databases
-echo "use $d_mysql; show tables;" |mysql -h$mysql_host -uroot
-p$mysql_password 2>/dev/null >> ./files/tables
-
-#delete tables first line
-sed -i '1d' ./files/tables
-
-#reference tables to create tables.sql
-for table in $(awk -F '\n' '{print $1}' ./files/tables)
- do
- sed -i "/${table}view/d" ./files/tables
- echo "use $d_mysql; show create table ${table};" |mysql -h$mysql_host
-uroot -p$mysql_password 2>/dev/null >> ./files/tables.sql
- echo "print ${table} sql to tables.sql in the file dir"
-
-done
-
-echo '==============================start to transform mysql table for doris
extral table======================'
-#adjust sql
-awk -F '\t' '{print $2}' ./files/tables.sql |awk '!(NR%2)' |awk '{print $0
";"}' > ./files/tables1.sql
-sed -i 's/\\n/\n/g' ./files/tables1.sql
-sed -n '/CREATE TABLE/,/ENGINE\=/p' ./files/tables1.sql > ./files/tables2.sql
-#delete tables special struct
-sed -i '/^ CON/d' ./files/tables2.sql
-sed -i '/^ KEY/d' ./files/tables2.sql
-rm -rf ./files/tables.sql
-rm -rf ./files/tables1.sql
-mv ./files/tables2.sql ./files/tables.sql
-
-#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");'
./files/tables.sql
-sed -i "s/\"driver\" = \"MySQL\"/\"driver\" = \"$doris_odbc_name\"/g"
./files/tables.sql
-
-#delete match line
-sed -i '/ENGINT=/d' ./files/tables.sql
-sed -i '/PRIMARY KEY/d' ./files/tables.sql
-sed -i '/UNIQUE KEY/d' ./files/tables.sql
-#delete , at the beginning (
-sed -i '/,\s*$/{:loop; N; /,\(\s*\|\n\))/! bloop; s/,\s*[\n]\?\s*)/\n)/}'
./files/tables.sql
-
-#delete a line on keyword
-sed -i -e '$!N;/\n.*ENGINE=ODBC/!P;D' ./files/tables.sql
-#replace mysql password、database、table、host
-for t_name in $(awk -F '\n' '{print $1}' ./files/tables)
- do
- sed -i "0,/ApacheDorisHostIp/s/ApacheDorisHostIp/${mysql_host}/"
./files/tables.sql
- sed -i
"0,/ApacheDorisHostPassword/s/ApacheDorisHostPassword/${mysql_password}/"
./files/tables.sql
- sed -i "0,/ApacheDorisDataBases/s/ApacheDorisDataBases/${d_mysql}/"
./files/tables.sql
- sed -i "0,/ApacheDorisTables/s/ApacheDorisTables/${t_name}/"
./files/tables.sql
-
-done
-######################################################################################################################################################################
-#replace mysql type with doris
-sed -i 's/AUTO_INCREMENT//g' ./files/tables.sql
-sed -i 's/CHARACTER SET utf8 COLLATE utf8_bin//g' ./files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci//g'
./files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin//g' ./files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci//g'
./files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8_general_ci//g' ./files/tables.sql
-sed -i 's/CHARACTER SET utf8 COLLATE utf8_general_ci//g' ./files/tables.sql
-sed -i 's/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP//g'
./files/tables.sql
-sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP//g' ./files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin//g' ./files/tables.sql
-sed -i 's/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP//g'
./files/tables.sql
-sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP//g' ./files/tables.sql
-sed -i 's/DEFAULT CURRENT_TIMESTAMP//g' ./files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4//g' ./files/tables.sql
-sed -i 's/CHARACTER SET utf8//g' ./files/tables.sql
-sed -i 's/COLLATE utf8mb4_general_ci//g' ./files/tables.sql
-sed -i 's/COLLATE utf8_general_ci//g' ./files/tables.sql
-sed -i 's/COLLATE utf8_bin//g' ./files/tables.sql
-sed -i 's/\<tinytext\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<text\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<mediumtext\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<longtext\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<tinyblob\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<blob\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<mediumblob\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<longblob\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<tinystring\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<mediumstring\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<longstring\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<timestamp\>/datetime/g' ./files/tables.sql
-sed -i 's/\<unsigned\>//g' ./files/tables.sql
-sed -i 's/\<zerofill\>//g' ./files/tables.sql
-sed -i 's/\<json\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/enum([^)]*)/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<set\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<bit\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/datetime([0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/string([0-9][0-9][0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/string([0-9][0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/string([0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<string\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/binary([0-9][0-9][0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/binary([0-9][0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/binary([0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<binary\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/varbinary([0-9][0-9][0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/varbinary([0-9][0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/varbinary([0-9])/varchar(65533)/g' ./files/tables.sql
-sed -i 's/\<varbinary\>/varchar(65533)/g' ./files/tables.sql
-sed -i 's/decimal([^)]*)/double/g' ./files/tables.sql
-
-
-#######################################
-#import doris
-echo '==========================================start to write
database========================================'
-echo " create database if not exists $d_doris ;use $d_doris ; source
./files/tables.sql;" |mysql -h$master_host -P$master_port -uroot
-p$doris_password
-echo '==========================================write database
success========================================='
-
diff --git a/extension/mysql_to_doris/bin/e_auto.sh
b/extension/mysql_to_doris/bin/e_auto.sh
new file mode 100644
index 0000000000..0951d9111b
--- /dev/null
+++ b/extension/mysql_to_doris/bin/e_auto.sh
@@ -0,0 +1,52 @@
+# 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
new file mode 100644
index 0000000000..222205af46
--- /dev/null
+++ b/extension/mysql_to_doris/bin/e_mysql_to_doris.sh
@@ -0,0 +1,97 @@
+# 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
+ echo "show create table ${table};" |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/conf/tables
b/extension/mysql_to_doris/conf/doris_tables
similarity index 52%
copy from extension/mysql_to_doris/conf/tables
copy to extension/mysql_to_doris/conf/doris_tables
index 5c52bf87e7..5bbc2710e4 100644
--- a/extension/mysql_to_doris/conf/tables
+++ b/extension/mysql_to_doris/conf/doris_tables
@@ -14,27 +14,7 @@
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
-####################################################################
-# The tables is used to define tables
-####################################################################
-inspection_agency
-inspection_equipment
-inspection_equipment_in_out
-inspection_file_info
-inspection_login_role
-inspection_login_user
-inspection_personnel
-inspection_report
-inspection_result_insulation_resistance_detail
-inspection_result_looks_detail
-inspection_result_pay_error_detail
-inspection_result_record
-inspection_result_value_errors_detail
-inspection_result_work_error_detail
-inspection_task
-inspection_task_approve
-inspection_task_related_deprecated
-inspection_task_urge
-inspection_token
-inspection_urge_agency
-inspection_user_role
+demo.demo1
+demo.demo2
+demo.demo3
+demo2.demo
diff --git a/extension/mysql_to_doris/conf/tables
b/extension/mysql_to_doris/conf/mysql_tables
similarity index 64%
rename from extension/mysql_to_doris/conf/tables
rename to extension/mysql_to_doris/conf/mysql_tables
index 5c52bf87e7..ec4409dcfe 100644
--- a/extension/mysql_to_doris/conf/tables
+++ b/extension/mysql_to_doris/conf/mysql_tables
@@ -17,24 +17,7 @@
####################################################################
# The tables is used to define tables
####################################################################
-inspection_agency
-inspection_equipment
-inspection_equipment_in_out
-inspection_file_info
-inspection_login_role
-inspection_login_user
-inspection_personnel
-inspection_report
-inspection_result_insulation_resistance_detail
-inspection_result_looks_detail
-inspection_result_pay_error_detail
-inspection_result_record
-inspection_result_value_errors_detail
-inspection_result_work_error_detail
-inspection_task
-inspection_task_approve
-inspection_task_related_deprecated
-inspection_task_urge
-inspection_token
-inspection_urge_agency
-inspection_user_role
+t_demo.t_cickp_charge_connector
+t_demo.t_cickp_charge_equipment
+p_demo.p_inspection_task
+p_demo.p_inspection_result_record
diff --git a/extension/mysql_to_doris/lib/mysql_to_doris.sh
b/extension/mysql_to_doris/lib/mysql_to_doris.sh
new file mode 100644
index 0000000000..3fb3605df2
--- /dev/null
+++ b/extension/mysql_to_doris/lib/mysql_to_doris.sh
@@ -0,0 +1,61 @@
+# 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
+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
diff --git a/extension/mysql_to_doris/user_define_tables.sh
b/extension/mysql_to_doris/user_define_tables.sh
deleted file mode 100644
index a75a847497..0000000000
--- a/extension/mysql_to_doris/user_define_tables.sh
+++ /dev/null
@@ -1,154 +0,0 @@
-#!/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.
-
-
-####################################################################
-# This script is used to will mysql tables import doris by external and by
user to define tables
-####################################################################
-
-
-#reference configuration file
-source ./conf/mysql.conf
-source ./conf/doris.conf
-
-#define mysql database and doris database
-d_mysql=$1
-d_doris=$2
-
-#check args
-if [ ! -n "$1" ];then
- echo "please check source database"
- exit
-fi
-if [ ! -n "$2" ];then
- echo "please check sink database"
- exit
-fi
-
-#mkdir files to store tables and tables.sql
-mkdir -p user_files
-rm -rf ./user_files/tables
-rm -rf ./user_files/tables.sql
-
-#reference tables to create tables.sql
-for table in $(cat ./conf/tables |grep -v '#' | awk -F '\n' '{print $1}')
- do
- sed -i "/${table}view/d" ./conf/tables
- echo "use $d_mysql; show create table ${table};" |mysql -h$mysql_host
-uroot -p$mysql_password 2>/dev/null >> ./user_files/tables.sql
- echo "print ${table} sql to tables.sql in the user_file dir"
-done
-
-echo '==============================start to transform mysql table for doris
extral table======================'
-
-#adjust sql
-awk -F '\t' '{print $2}' ./user_files/tables.sql |awk '!(NR%2)' |awk '{print
$0 ";"}' > ./user_files/tables1.sql
-sed -i 's/\\n/\n/g' ./user_files/tables1.sql
-sed -n '/CREATE TABLE/,/ENGINE\=/p' ./user_files/tables1.sql >
./user_files/tables2.sql
-
-#delete tables special struct
-sed -i '/^ CON/d' ./user_files/tables2.sql
-sed -i '/^ KEY/d' ./user_files/tables2.sql
-rm -rf ./user_files/tables.sql
-rm -rf ./user_files/tables1.sql
-mv ./user_files/tables2.sql ./user_files/tables.sql
-#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");'
./user_files/tables.sql
-sed -i "s/\"driver\" = \"MySQL\"/\"driver\" = \"$doris_odbc_name\"/g"
./user_files/tables.sql
-#delete match line
-sed -i '/ENGINT=/d' ./user_files/tables.sql
-sed -i '/PRIMARY KEY/d' ./user_files/tables.sql
-sed -i '/UNIQUE KEY/d' ./user_files/tables.sql
-#delete , at the beginning (
-sed -i '/,\s*$/{:loop; N; /,\(\s*\|\n\))/! bloop; s/,\s*[\n]\?\s*)/\n)/}'
./user_files/tables.sql
-
-#delete a line on keyword
-sed -i -e '$!N;/\n.*ENGINE=ODBC/!P;D' ./user_files/tables.sql
-
-#replace mysql password、database、table、host
-for t_name in $(cat ./conf/tables |grep -v '#' | awk -F '\n' '{print $1}')
- do
- sed -i "0,/ApacheDorisHostIp/s/ApacheDorisHostIp/${mysql_host}/"
./user_files/tables.sql
- sed -i
"0,/ApacheDorisHostPassword/s/ApacheDorisHostPassword/${mysql_password}/"
./user_files/tables.sql
- sed -i "0,/ApacheDorisDataBases/s/ApacheDorisDataBases/${d_mysql}/"
./user_files/tables.sql
- sed -i "0,/ApacheDorisTables/s/ApacheDorisTables/${t_name}/"
./user_files/tables.sql
-
-done
-#replace mysql type with doris
-sed -i 's/AUTO_INCREMENT//g' ./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8 COLLATE utf8_bin//g' ./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci//g'
./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin//g' ./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci//g'
./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8_general_ci//g'
./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8 COLLATE utf8_general_ci//g'
./user_files/tables.sql
-sed -i 's/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP//g'
./user_files/tables.sql
-sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP//g' ./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin//g' ./user_files/tables.sql
-sed -i 's/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP//g'
./user_files/tables.sql
-sed -i 's/DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP//g' ./user_files/tables.sql
-sed -i 's/DEFAULT CURRENT_TIMESTAMP//g' ./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8mb4//g' ./user_files/tables.sql
-sed -i 's/CHARACTER SET utf8//g' ./user_files/tables.sql
-sed -i 's/COLLATE utf8mb4_general_ci//g' ./user_files/tables.sql
-sed -i 's/COLLATE utf8_general_ci//g' ./user_files/tables.sql
-sed -i 's/COLLATE utf8_bin//g' ./user_files/tables.sql
-sed -i 's/\<tinytext\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<text\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<mediumtext\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<longtext\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<tinyblob\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<blob\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<mediumblob\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<longblob\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<tinystring\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<mediumstring\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<longstring\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<timestamp\>/datetime/g' ./user_files/tables.sql
-sed -i 's/\<unsigned\>//g' ./user_files/tables.sql
-sed -i 's/\<zerofill\>//g' ./user_files/tables.sql
-sed -i 's/\<json\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/enum([^)]*)/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<set\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<bit\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/datetime([0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/string([0-9][0-9][0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/string([0-9][0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/string([0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<string\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/binary([0-9][0-9][0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/binary([0-9][0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/binary([0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<binary\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/varbinary([0-9][0-9][0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/varbinary([0-9][0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/varbinary([0-9])/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/\<varbinary\>/varchar(65533)/g' ./user_files/tables.sql
-sed -i 's/decimal([^)]*)/double/g' ./user_files/tables.sql
-
-
-
-#######################################
-#import doris
-for table in $(cat ./conf/tables |grep -v '#' | awk -F '\n' '{print $1}')
- do
- echo "use $d_doris; drop table if exists ${table};" |mysql
-h$master_host -P$master_port -uroot -p$doris_password 2>/dev/null
-done
-
-echo '==========================================start to write
database========================================'
-echo "create database if not exists $d_doris; use $d_doris; source
./user_files/tables.sql;" |mysql -h$master_host -P$master_port -uroot
-p$doris_password
-echo '==========================================write database
success========================================='
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
