借助clickhouse-jdbc实现oracle数据迁移到mysql（脚本版）

鱼鱼不愚与

已于 2025-01-03 09:55:23 修改

阅读量828

点赞数 5

文章标签： clickhouse oracle mysql 数据库

于 2024-12-18 16:07:12 首次发布

本文链接：https://blog.youkuaiyun.com/2301_79096508/article/details/144563063

版权

一、脚本逻辑

1.1 数据流图

Clickhouse数据库所在主机安装clickhouse-jdbc&相应数据库驱动之后，可以在Clickhouse读取Oracle数据库的数据；同时Clickhouse 自带mysql函数，可以直接读取，插入数据到mysql数据库中（注意⚠️：在clickhouse数据库上修改不了mysql数据库数据）

这个过程借助第三方数据库实现数据从oracle数据库到mysql数据的转变，比起传统的ETL工具，既不需要空间去转换数据，也不需要下载原始数据和上传转换好的数据。

1.2 方便使用的一些额外配置

1.2.1 named collection

命名集合(named collection)提供了一种存储键值对集合的方法，用于配置与外部源的集成。您可以将命名集合与字典、表、表函数和对象存储一起使用。

命名集合可以使用DDL或在配置文件中配置，并在ClickHouse启动时应用。它们简化了对象的创建和对没有管理权限的用户隐藏凭据。命名集合中的键必须与相应函数、表引擎、数据库等的参数名称匹配。

在SQL中可以重写命名集合中设置的参数，如下例所示。使用[NOT]OVERRIDABLE(译为：可覆盖的）关键字和XML属性和/或配置选项allow_named_collection_override_by-default可以限制此功能。

在users.xml文件中，我们可以找到default用户相关权限设置named_collection_control和access_management：

<users>
        <!-- If user name was not specified, 'default' user is used. -->
        <default>
          <profile>default</profile>
            <networks>
                <ip>10.10.208.160</ip>
                <ip>127.0.0.1</ip>
            </networks>
                <password_sha256_hex>bcb95c15840d47e269341b5d73ec9f42bc4f43</password_sha256_hex>
          <quota>default</quota>
          <access_management>1</access_management>
          <named_collection_control>1</named_collection_control>
        </default>
</users>

本文中我们主要用named collection 来

Named collections for accessing MySQL database

CREATE NAMED COLLECTION aicc AS
user = 'aicc',
password = 'aiccpass',
host = '10.80.161.210',
port = 3188,
database = 'aicc',
connection_pool_size = 24,
replace_query = 1,
read_write_timeout=1800;

然后，我们就能在clickhouse数据库中查或者插入mysql表数据，如下例子是查的mysql 数据库中aicc.aicc_test

的表数据。

SELECT count(*) FROM mysql(aicc, table = 'aicc_test');

下面展示named-collection具体用法：

ALTER NAMED COLLECTION collection2 SET key1=4, key3='value3'
DROP NAMED COLLECTION [IF EXISTS] name [on CLUSTER cluster]

创建好之后我们可以在/clickhouse/data/named_collections目录下面查到具体语句

1.2.2 mysql命令参数

使用 -NB 参数获取干净的输出
MySQL 命令行工具支持 -NB 参数：
-N: 不输出列名。
-B: 使用批量输出模式，避免格式化干扰。

column=$(mysql -uroot -p'mysqlpass' -N -S /mysql/data3337/socket/mysqld.sock -e "SELECT GROUP_CONCAT(COLUMN_NAME)  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'cic_0001' AND TABLE_NAME = '$table';")

二、具体实现

#!/bin/bash

# 定义表名列表
tables=(
"cic_config"
"cic_content_attachment"
"cic_cust_interact"
"cic_dim"
"cic_dim_service"
"cic_dynamic_menu"
)

# 定义Mysql服务器、数据库及凭据
mysql_host="10.10.204.160:3337"
mysql_database='cic00'
mysql_user="cic"
mysql_password="cic@123"

# 定义Oracle连接
oracle_connection="jdbc('oracle-cic','SELECT "

# 遍历表名
for table in "${tables[@]}"; do
    # 构建 INSERT 语句
    column=$(mysql -uroot -p'mysqlpass' -N -S /mysql/data3337/socket/mysqld.sock -e "SELECT GROUP_CONCAT(COLUMN_NAME)  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'cic_0001' AND TABLE_NAME = '$table';")
    query="INSERT INTO function mysql(cic, table='$table')($column) SELECT * FROM $oracle_connection $column from $table');"
    
    clickhouse-client -udefault --password=password --query="$query"
    # 检查是否有错误
    if [[ $? -eq 0 ]]; then
        # 如果没有错误，输出表名和 success
        echo "$table success"
    else
        # 如果有错误，输出表名和错误信息
        echo "!!!!!!$table error!!!!!!!!!!!please check"
    fi
done

#!/bin/bash

# 定义表名列表
tables=(
"aicc_action"
    表
)

# 定义Mysql服务器、数据库及凭据
mysql_host="10.10.168.218"
mysql_port="3306"
mysql_database='aicc'
mysql_user="aicc"
mysql_password="aiccpass"

# 定义Oracle连接
oracle_connection="jdbc('oracle-aicc','SELECT "

# 遍历表名
for table in "${tables[@]}"; do
    # 构建 INSERT 语句
    column=$(mysql -uaicc -p'password' -h$mysql_host -P$mysql_port -N  -e "SELECT GROUP_CONCAT(COLUMN_NAME)  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'aicc' AND TABLE_NAME = '$table';")
    query="INSERT INTO function mysql(aicc, table='$table')($column) SELECT $column  FROM $oracle_connection $column from $table');"
    clickhouse-client -udefault --password=password --query="$query"
    # 检查是否有错误
    if [[ $? -eq 0 ]]; then
        # 如果没有错误，输出表名和 success
        echo "$table success"
    else
        # 如果有错误，输出表名和错误信息
        echo "!!!!!!$table error!!!!!!!!!!!please check"
    fi
done

导入中有表失败会有许多报错（数据类型啥的，数据都会打印出来，所以最好将错误输出到空）

[root@localhost soft]# ./insert_oracle_to_mysql.sh 2>/dev/null

三、结果输出

[root@localhost soft]# ./insert_oracle_to_mysql.sh 2>/dev/null
cic_config success
cic_content_attachment success
cic_cust_interact success
cic_dim success
cic_dim_service success
cic_dynamic_menu success
cic_enrich_service success
!!!!!!cic_event error!!!!!!!!!!!please check
cic_event_class success
cic_event_flow success
cic_event_flow_sp_auth success

四、结果检查

主要用来比较oracle和mysql相同表的数据量，来佐证导入数据的成功性。

[root@localhost soft]# cat check_insert.sh 
#!/bin/bash

# 定义表名列表
tables=(
"aicc_action_catg"
"aicc_action_detail"
"aicc_action_type"
"aicc_chat_external_entry"
)

# 定义Oracle连接和MySQL连接
oracle_connection="jdbc('oracle-aicc'"
mysql_database="aicc"

# 遍历表名
for table in "${tables[@]}"; do
    # 从 Oracle 数据源获取行数
    oracle_count=$(clickhouse-client -udefault --password=password  -q "SELECT count(*) FROM jdbc('oracle-aicc', 'SELECT * FROM $table');" 2>/dev/null)

    # 检查 Oracle 查询是否出错
    if [[ $? -ne 0 ]]; then
        echo "!!!!!!Error querying Oracle for table $table!!!!!!!!!!!"
        continue
    fi

    # 从 MySQL 数据源获取行数
    mysql_count=$(clickhouse-client -udefault --password=password -q "SELECT count(*) FROM mysql(aicc, table='$table');" 2>/dev/null)

    # 检查 MySQL 查询是否出错
    if [[ $? -ne 0 ]]; then
        echo "!!!!!!Error querying MySQL for table $table!!!!!!!!!!!"
        continue
    fi

    # 比较两个数据源的行数
    if [[ "$oracle_count" -ne "$mysql_count" ]]; then
        echo "Mismatch in table $table: Oracle count = $oracle_count, MySQL count = $mysql_count"
    else
       # echo "Table $table: Row count matches ($oracle_count)" #匹配的可以忽略
    fi
done

[root@localhost soft]# ./check_insert.sh 
Table aicc_action_catg: Row count matches (21)
Mismatch in table aicc_action_detail: Oracle count = 209, MySQL count = 211
Mismatch in table aicc_action_type: Oracle count = 7, MySQL count = 14
Table aicc_chat_external_entry: Row count matches (37)
Table aicc_chat_intent: Row count matches (7)
Table aicc_chat_intent_catg: Row count matches (2)
Table aicc_chat_intent_sentence: Row count matches (75)
Mismatch in table aicc_chat_msg: Oracle count = 65271, MySQL count = 0
Table aicc_chat_msg_score: Row count matches (773)
.......

五、额外建议

mysql端在导入过程中不要开外键检查：SET global FOREIGN_KEY_CHECKS=0;

mysql端表结构要建立好，数据最好清空；

mysql表结构不要有逻辑错误，默认列值是NULL，但是NULL列又是NO；

mysql> desc aicc.aicc_test;
+-------------------+---------------+------+-----+-------------------+-------------------+
| Field             | Type          | Null | Key | Default           | Extra             |
+-------------------+---------------+------+-----+-------------------+-------------------+
| SESSION_ID        | decimal(16,0) | NO   | PRI | NULL              |                   |
| SESSION_NAME      | varchar(255)  | NO   |     | NULL              |                   |
| USER_ID           | decimal(16,0) | YES  | MUL | NULL              |                   |
| CREATED_DATE      | datetime      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| TENANT_ID         | decimal(16,0) | YES  |     | NULL              |                   |
| TEMPORARY_FLAG    | char(1)       | NO   | MUL | N                 |                   |
| TOPIC_ID          | decimal(16,0) | YES  |     | NULL              |                   |
| PARENT_SESSION_ID | decimal(16,0) | YES  | MUL | NULL              |                   |
| ENTRY_ID          | decimal(16,0) | YES  |     | NULL              |                   |
| CHANNEL_ID        | decimal(16,0) | NO   |     | NULL              |                   |
+-------------------+---------------+------+-----+-------------------+-------------------+
10 rows in set (0.00 sec)

mysql> insert into aicc.test VALUES ('10928','db9f261a-4f41-4d93-85dc-f20fd17b9267','1','2024-07-01 18:20:26','1','Y',NULL,NULL,'1121','');
ERROR 1366 (HY000): Incorrect decimal value: '' for column 'CHANNEL_ID' at row 1
mysql> insert into aicc.test VALUES ('10928','db9f261a-4f41-4d93-85dc-f20fd17b9267','1','2024-07-01 18:20:26','1','Y',NULL,NULL,'1121');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into aicc.aicc_chat_session VALUES ('10928','db9f261a-4f41-4d93-85dc-f20fd17b9267','1','2024-07-01 18:20:26','1','Y',NULL,NULL,'1121');

^C
mysql> insert INTO `aicc`.`aicc_test` (`SESSION_ID`, `SESSION_NAME`, `USER_ID`, `CREATED_DATE`, `TENANT_ID`, `TEMPORARY_FLAG`, `TOPIC_ID`, `PARENT_SESSION_ID`, `ENTRY_ID`) VALUES ('10928','db9f261a-4f41-4d93-85dc-f20fd17b9267','1','2024-07-01 18:20:26','1','Y',NULL,NULL,'1121');
ERROR 1364 (HY000): Field 'CHANNEL_ID' doesn't have a default value
mysql> insert INTO `aicc`.`cicc_test` (`SESSION_ID`, `SESSION_NAME`, `USER_ID`, `CREATED_DATE`, `TENANT_ID`, `TEMPORARY_FLAG`, `TOPIC_ID`, `PARENT_SESSION_ID`, `ENTRY_ID`,`CHANNEL_ID`) VALUES ('10928','db9f261a-4f41-4d93-85dc-f20fd17b9267','1','2024-07-01 18:20:26','1','Y',NULL,NULL,'1121',NULL);
ERROR 1048 (23000): Column 'CHANNEL_ID' cannot be null
mysql>