DuckDB x TiDB -- 将 MongoDB 数据导入 TiDB
作者：TiDB 社区干货传送门
- 2025-02-14 北京
本文字数：25099 字
阅读完需：约 82 分钟
作者： pepezzzz 原文来源：https://tidb.net/blog/e1808110
一、背景
MongoDB 数据库是非常流行的 NOSQL 数据库，具有灵活的数据模型和易扩展性等特性，项目组基于 MongoDB 能实现业务的快速开发上线和迭代。但是在业务的某一阶段，要将 MongoDB 数据导入关系型数据库，如 TiDB 时，往往需要业务开发团队提供关系范式关系和迁移手段。
DuckDB 是轻量的嵌入式 AP 场景数据库，能支持读取和解析 JSON 文件，并支持 struct 字段类型，能远程访问 MySQL。
本次尝试通过 DuckDB 和 JSON 插件、MySQL 插件的结合，能够将 MongoDB 的备份 SSO 文件写入给 TiDB，实现 MongoDB 数据的轻量级场景实现数据迁移的目的。
二、环境准备
MongoDB 按 collection (表) 进行导出，以一个 sso 系统的日志表为例。
# mongoexport -h -uxx -pxxx -d xx -c xxx -o sso.json--host or -h: Specifies the MongoDB host and port (e.g., localhost:27017).--username or -u: Specifies the username for authentication.--password or -p: Specifies the password for authentication.--db or -d: Specifies the database name. 库名--collection or -c: Specifies the collection name. 表名--out or -o: Specifies the output file (e.g., sso.json). 备份文件名
复制代码
三、数据导入
配置目标端 TiDB
Duckdb 环境
https://github.com/duckdb/duckdb/releases/download/v1.1.3/duckdb_cli-linux-amd64.zip
# unzip duckdb_cli-linux-amd64.zip Archive: duckdb_cli-linux-amd64.zip inflating: duckdb # vi init.sql SET extension_directory = '/data/duckdb_ext';.prompt 'duckdb> ' .timer on # ./duckdb bak.duckdb --init init.sqlduckdb> SELECT current_database();┌────────────────────┐│ current_database() ││ varchar │├────────────────────┤│ bak │└────────────────────┘Run Time (s): real 0.001 user 0.000429 sys 0.000501
复制代码
安装插件
duckdb> INSTALL mysql;Run Time (s): real 24.360 user 24.535172 sys 0.148988duckdb> CREATE PERSISTENT SECRET tidb_secret_1 ( TYPE MYSQL, HOST '172.16.201.122', PORT 4500, DATABASE bakdb, USER 'bakuser', PASSWORD 'bak12345' );Success = trueRun Time (s): real 0.003 user 0.003724 sys 0.000006duckdb> ATTACH '' AS tidb_bakdb (TYPE MYSQL, SECRET tidb_secret_1);
Run Time (s): real 0.012 user 0.012089 sys 0.000302duckdb> .mode duckboxduckdb> show databases;┌───────────────┐│ database_name ││ varchar │├───────────────┤│ bak ││ tidb_bakdb │└───────────────┘Run Time (s): real 0.002 user 0.001863 sys 0.000000duckdb> use tidb_bakdb;Run Time (s): real 0.010 user 0.010106 sys 0.000000duckdb> show tables;┌──────────┐│ name ││ varchar │├──────────┤│ t1 ││ users ││ users10m │└──────────┘Run Time (s): real 0.072 user 0.030356 sys 0.001032
复制代码
如果 duckdb 重启，需要重新装载目标端。
duckdb> load mysql;Run Time (s): real 0.040 user 0.193298 sys 0.017694duckdb> ATTACH '' AS tidb_bakdb (TYPE MYSQL, SECRET tidb_secret_1);Run Time (s): real 0.003 user 0.002285 sys 0.001021
复制代码
导入目标 json 文件
duckdb> .mode lineduckdb> select * from '/root/sso.json' limit 1 ; _id = {'$oid': 677b2d71e4b020d9e1188c46} eventType = GETeventTypeName = eventResult = FAILURE eventTime = {'$date': 2025-01-29T01:10:09.531Z} userId = FC8364C29422E70A2799054281289C39 userName = YWZY userType = 0 userTitle = 联调_运维 auditContent = org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.IndexOutOfBoundsException: Index: 0, Size: 0... 错误堆栈略 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) ... 70 more
auditCatalog = Request processing failed; nested exception is java.lang.IndexOutOfBoundsException: Index: 0, Size: 0 auditType = AUDIT_ERRORLOGauditTypeName = compName = /aa_aaa_amanage methodName = /aa_aaa_amanage/storage/initArcMatain clientIp = 10.0.0.41 clientAddr = 10.0.0.245 clienMac = serviceIp = 10.0.0.38 serviceAddr = app2 serviciePort = 7099 requestMs = 0 rsRowcount = 0 auditId = 20250129091009004005 beginTime = {'$date': 2025-01-29T01:10:09.531Z} endTime = {'$date': 2025-01-29T01:10:09.532Z}Run Time (s): real 0.086 user 0.083863 sys 0.016180
duckdb> create table t_sso as select * from '/root/sso.json' ;Run Time (s): real 0.133 user 0.116647 sys 0.026003duckdb> select * from t_sso limit 1;┌──────────────────────┬───────────┬───────────────┬─────────────┬──────────────────────┬──────────────────────┬──────────┬───┬─────────────┬──────────────┬───────────┬────────────┬──────────────────────┬──────────────────────┬──────────────────────┐│ _id │ eventType │ eventTypeName │ eventResult │ eventTime │ userId │ userName │ … │ serviceAddr │ serviciePort │ requestMs │ rsRowcount │ auditId │ beginTime │ endTime ││ struct("$oid" varc… │ varchar │ varchar │ varchar │ struct("$date" var… │ varchar │ varchar │ │ varchar │ varchar │ int64 │ int64 │ varchar │ struct("$date" var… │ struct("$date" var… │├──────────────────────┼───────────┼───────────────┼─────────────┼──────────────────────┼──────────────────────┼──────────┼───┼─────────────┼──────────────┼───────────┼────────────┼──────────────────────┼──────────────────────┼──────────────────────┤│ {'$oid': 677b2d71e… │ GET │ │ FAILURE │ {'$date': 2025-01-… │ FC8364C29422E70A27… │ YWZY │ … │ app2 │ 7099 │ 0 │ 0 │ 20250129091009004005 │ {'$date': 2025-01-… │ {'$date': 2025-01-… │├──────────────────────┴───────────┴───────────────┴─────────────┴──────────────────────┴──────────────────────┴──────────┴───┴─────────────┴──────────────┴───────────┴────────────┴──────────────────────┴──────────────────────┴──────────────────────┤│ 1 rows 26 columns (14 shown) │└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Run Time (s): real 0.003 user 0.005043 sys 0.000011duckdb> .mode lineduckdb> select * from t_sso limit 1; _id = {'$oid': 677b2d71e4b020d9e1188c46} eventType = GETeventTypeName = eventResult = FAILURE eventTime = {'$date': 2025-01-29T01:10:09.531Z} userId = FC8364C29422E70A27E1054281289C39 userName = YWZY userType = 0 userTitle = 联调_运维 auditContent = org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.IndexOutOfBoundsException: Index: 0, Size: 0... 字段内错误堆栈文本略at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) ... 70 more
auditCatalog = Request processing failed; nested exception is java.lang.IndexOutOfBoundsException: Index: 0, Size: 0 auditType = AUDIT_ERRORLOGauditTypeName = compName = /aa_aaa_amanage methodName = /aa_aaa_amanage/storage/initArcMatain clientIp = 10.0.0.48 clientAddr = 10.0.0.245 clienMac = serviceIp = 10.0.0.38 serviceAddr = app2 serviciePort = 7099 requestMs = 0 rsRowcount = 0 auditId = 20250129091009004005 beginTime = {'$date': 2025-01-29T01:10:09.531Z} endTime = {'$date': 2025-01-29T01:10:09.532Z}Run Time (s): real 0.003 user 0.002731 sys 0.002099
复制代码
由于 json 文件解析后存在 struct 类型，不能直接导入 TiDB。
duckdb> create table tidb_bakdb.t_sso as select * from bak.t_sso;Not implemented Error: MySQL does not support composite types - unsupported type "STRUCT("$oid" VARCHAR)"
复制代码
对 struct 通过 json 函数进行有效列的提取。
_id: MongoDB 的自增主键，可以进行忽略，也可以选择导入。
eventTime 等：通过 json_extract_string(eventTime, ‘.date’) 进行提取并格式化后，再进行 UTC 时区的转换（时间后缀 Z 表示 UTC 时间）。时间格式化链接：https://duckdb.org/docs/sql/functions/dateformat.html#format-specifiers
处理步骤举例如下：
duckdb> select json_extract_string(endTime, '$.$date') as endTime from t_sso limit 1;┌──────────────────────────┐│ endTime ││ varchar │├──────────────────────────┤│ 2025-01-29T01:10:09.532Z │└──────────────────────────┘duckdb> select strptime(json_extract_string(endTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ') as endTime from t_sso limit 1;┌─────────────────────────┐│ endTime ││ timestamp │├─────────────────────────┤│ 2025-01-29 01:10:09.532 │└─────────────────────────┘duckdb> select date_add(strptime(json_extract_string(endTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as endTime from t_sso limit 1;┌─────────────────────────┐│ endTime ││ timestamp │├─────────────────────────┤│ 2025-01-29 09:10:09.532 │└─────────────────────────┘
复制代码
按列处理后数据导入 TiDB
duckdb> select group_concat(name) FROM pragma_table_info('t_sso');group_concat("name") = _id,eventType,eventTypeName,eventResult,eventTime,userId,userName,userType,userTitle,auditContent,auditCatalog,auditType,auditTypeName,compName,methodName,clientIp,clientAddr,clienMac,serviceIp,serviceAddr,serviciePort,requestMs,rsRowcount,auditId,beginTime,endTimeRun Time (s): real 0.001 user 0.000974 sys 0.000000duckdb> .mode duckboxduckdb> select json_extract_string(_id, '$.$oid') as oid,eventType,eventTypeName,eventResult,date_add(strptime(json_extract_string(endTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as endTime,userId,userName,userType,userTitle,auditContent,auditCatalog,auditType,auditTypeName,compName,methodName,clientIp,clientAddr,clienMac,serviceIp,serviceAddr,serviciePort,requestMs,rsRowcount,auditId,date_add(strptime(json_extract_string(beginTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as beginTime, date_add(strptime(json_extract_string(endTime, '$.$date'),'%Y-%m-%dT%I:%M:%S.%gZ'),INTERVAL 8 HOUR) as endTime, from t_sso limit 1;┌──────────────────────┬───────────┬───────────────┬─────────────┬──────────────────────┬──────────────────────┬──────────┬───┬─────────────┬──────────────┬───────────┬────────────┬──────────────────────┬──────────────────────┬──────────────────────┐│ oid │ eventType │ eventTypeName │ eventResult │ endTime │ userId │ userName │ … │ serviceAddr │ serviciePort │ requestMs │ rsRowcount │ auditId │ beginTime │ endTime ││ varchar │ varchar │ varchar │ varchar │ timestamp │ varchar │ varchar │ │ varchar │ varchar │ int64 │ int64 │ varchar │ timestamp │ timestamp │├──────────────────────┼───────────┼───────────────┼─────────────┼──────────────────────┼──────────────────────┼──────────┼───┼─────────────┼──────────────┼───────────┼────────────┼──────────────────────┼──────────────────────┼──────────────────────┤│ 677b2d71e4b020d9e1… │ GET │ │ FAILURE │ 2025-01-29 09:10:0… │ FC8364C29422E70A27… │ YWZY │ … │ app2 │ 7099 │ 0 │ 0 │ 20250129091009004005 │ 2025-01-29 09:10:0… │ 2025-01-29 09:10:0… │├──────────────────────┴───────────┴───────────────┴─────────────┴──────────────────────┴──────────────────────┴──────────┴───┴─────────────┴──────────────┴───────────┴────────────┴──────────────────────┴──────────────────────┴──────────────────────┤│ 1 rows 26 columns (14 shown) │└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘duckdb> CALL mysql_clear_cache();┌─────────┐│ Success ││ boolean │├─────────┤│ 0 rows │└─────────┘Run Time (s): real 0.000 user 0.000901 sys 0.000000duckdb> create table tidb_bakdb.t_sso as select json_extract_string(_id, '$.$oid') as oid,eventType,eventTypeName,eventResult,date_add(strptime(json_extract_string(eventTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as eventTime,userId,userName,userType,userTitle,auditContent,auditCatalog,auditType,auditTypeName,compName,methodName,clientIp,clientAddr,clienMac,serviceIp,serviceAddr,serviciePort,requestMs,rsRowcount,auditId,date_add(strptime(json_extract_string(beginTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as beginTime, date_add(strptime(json_extract_string(endTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as endTime from t_sso;100% ▕████████████████████████████████████████████████████████████▏ Run Time (s): real 2.520 user 0.131091 sys 0.014937duckdb> select count(1) from tidb_bakdb.t_sso;┌──────────┐│ count(1) ││ int64 │├──────────┤│ 2620 │└──────────┘Run Time (s): real 0.012 user 0.007022 sys 0.002068duckdb> select count(1) from t_sso;┌──────────┐│ count(1) ││ int64 │├──────────┤│ 2620 │└──────────┘Run Time (s): real 0.001 user 0.001053 sys 0.000004duckdb> select * from tidb_bakdb.t_sso limit 1;┌──────────────────────┬───────────┬───────────────┬─────────────┬─────────────────────┬──────────────────────┬──────────┬───┬─────────────┬──────────────┬───────────┬────────────┬──────────────────────┬─────────────────────┬─────────────────────┐│ oid │ eventType │ eventTypeName │ eventResult │ eventTime │ userId │ userName │ … │ serviceAddr │ serviciePort │ requestMs │ rsRowcount │ auditId │ beginTime │ endTime ││ varchar │ varchar │ varchar │ varchar │ timestamp │ varchar │ varchar │ │ varchar │ varchar │ int64 │ int64 │ varchar │ timestamp │ timestamp │├──────────────────────┼───────────┼───────────────┼─────────────┼─────────────────────┼──────────────────────┼──────────┼───┼─────────────┼──────────────┼───────────┼────────────┼──────────────────────┼─────────────────────┼─────────────────────┤│ 677b2d71e4b020d9e1… │ GET │ │ FAILURE │ 2025-01-29 09:10:10 │ FC8364C29422E70A27… │ YWZY │ … │ app2 │ 7099 │ 0 │ 0 │ 20250129091009004005 │ 2025-01-29 09:10:10 │ 2025-01-29 09:10:10 │├──────────────────────┴───────────┴───────────────┴─────────────┴─────────────────────┴──────────────────────┴──────────┴───┴─────────────┴──────────────┴───────────┴────────────┴──────────────────────┴─────────────────────┴─────────────────────┤│ 1 rows 26 columns (14 shown) │└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Run Time (s): real 0.010 user 0.007042 sys 0.001070 duckdb> select * from t_sso limit 1;┌──────────────────────┬───────────┬───────────────┬─────────────┬──────────────────────┬──────────────────────┬──────────┬───┬─────────────┬──────────────┬───────────┬────────────┬──────────────────────┬──────────────────────┬──────────────────────┐│ _id │ eventType │ eventTypeName │ eventResult │ eventTime │ userId │ userName │ … │ serviceAddr │ serviciePort │ requestMs │ rsRowcount │ auditId │ beginTime │ endTime ││ struct("$oid" varc… │ varchar │ varchar │ varchar │ struct("$date" var… │ varchar │ varchar │ │ varchar │ varchar │ int64 │ int64 │ varchar │ struct("$date" var… │ struct("$date" var… │├──────────────────────┼───────────┼───────────────┼─────────────┼──────────────────────┼──────────────────────┼──────────┼───┼─────────────┼──────────────┼───────────┼────────────┼──────────────────────┼──────────────────────┼──────────────────────┤│ {'$oid': 677b2d71e… │ GET │ │ FAILURE │ {'$date': 2025-01-… │ FC8364C29422E70A27… │ YWZY │ … │ app2 │ 7099 │ 0 │ 0 │ 20250129091009004005 │ {'$date': 2025-01-… │ {'$date': 2025-01-… │├──────────────────────┴───────────┴───────────────┴─────────────┴──────────────────────┴──────────────────────┴──────────┴───┴─────────────┴──────────────┴───────────┴────────────┴──────────────────────┴──────────────────────┴──────────────────────┤│ 1 rows 26 columns (14 shown) │└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘Run Time (s): real 0.003 user 0.003964 sys 0.000065
复制代码
DDL 对比
duckdb> desc tidb_bakdb.t_sso;┌───────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐│ column_name │ column_type │ null │ key │ default │ extra ││ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │├───────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤│ oid │ VARCHAR │ YES │ │ │ ││ eventType │ VARCHAR │ YES │ │ │ ││ eventTypeName │ VARCHAR │ YES │ │ │ ││ eventResult │ VARCHAR │ YES │ │ │ ││ eventTime │ TIMESTAMP │ YES │ │ │ ││ userId │ VARCHAR │ YES │ │ │ ││ userName │ VARCHAR │ YES │ │ │ ││ userType │ BIGINT │ YES │ │ │ ││ userTitle │ VARCHAR │ YES │ │ │ ││ auditContent │ VARCHAR │ YES │ │ │ ││ auditCatalog │ VARCHAR │ YES │ │ │ ││ auditType │ VARCHAR │ YES │ │ │ ││ auditTypeName │ VARCHAR │ YES │ │ │ ││ compName │ VARCHAR │ YES │ │ │ ││ methodName │ VARCHAR │ YES │ │ │ ││ clientIp │ VARCHAR │ YES │ │ │ ││ clientAddr │ VARCHAR │ YES │ │ │ ││ clienMac │ VARCHAR │ YES │ │ │ ││ serviceIp │ VARCHAR │ YES │ │ │ ││ serviceAddr │ VARCHAR │ YES │ │ │ ││ serviciePort │ VARCHAR │ YES │ │ │ ││ requestMs │ BIGINT │ YES │ │ │ ││ rsRowcount │ BIGINT │ YES │ │ │ ││ auditId │ VARCHAR │ YES │ │ │ ││ beginTime │ TIMESTAMP │ YES │ │ │ ││ endTime │ TIMESTAMP │ YES │ │ │ │├───────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤│ 26 rows 6 columns │└─────────────────────────────────────────────────────────────────────┘Run Time (s): real 0.002 user 0.000406 sys 0.001027duckdb> SELECT * FROM mysql_query('tidb_bakdb', 'desc t_sso');┌───────────────┬──────────┬─────────┬─────────┬─────────┬─────────┐│ Field │ Type │ Null │ Key │ Default │ Extra ││ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │├───────────────┼──────────┼─────────┼─────────┼─────────┼─────────┤│ oid │ text │ YES │ │ │ ││ eventType │ text │ YES │ │ │ ││ eventTypeName │ text │ YES │ │ │ ││ eventResult │ text │ YES │ │ │ ││ eventTime │ datetime │ YES │ │ │ ││ userId │ text │ YES │ │ │ ││ userName │ text │ YES │ │ │ ││ userType │ bigint │ YES │ │ │ ││ userTitle │ text │ YES │ │ │ ││ auditContent │ text │ YES │ │ │ ││ auditCatalog │ text │ YES │ │ │ ││ auditType │ text │ YES │ │ │ ││ auditTypeName │ text │ YES │ │ │ ││ compName │ text │ YES │ │ │ ││ methodName │ text │ YES │ │ │ ││ clientIp │ text │ YES │ │ │ ││ clientAddr │ text │ YES │ │ │ ││ clienMac │ text │ YES │ │ │ ││ serviceIp │ text │ YES │ │ │ ││ serviceAddr │ text │ YES │ │ │ ││ serviciePort │ text │ YES │ │ │ ││ requestMs │ bigint │ YES │ │ │ ││ rsRowcount │ bigint │ YES │ │ │ ││ auditId │ text │ YES │ │ │ ││ beginTime │ datetime │ YES │ │ │ ││ endTime │ datetime │ YES │ │ │ │├───────────────┴──────────┴─────────┴─────────┴─────────┴─────────┤│ 26 rows 6 columns │└──────────────────────────────────────────────────────────────────┘Run Time (s): real 0.005 user 0.001166 sys 0.000209
复制代码
从 desc 的命令输出可以看出，使用 CTAS 命令实现数据的写入时，varchar 类型被转换成为 text，datetime 类型丢失毫秒精度。
使用自定义表结构和 Insert … Select … 导入数据
得到基础表结构
duckdb> .mode lineduckdb> WITH table_columns AS ( SELECT name, type, CASE WHEN "notnull" = 1 THEN 'NOT NULL' ELSE '' END AS not_null FROM pragma_table_info('t_sso')) SELECT 'CREATE TABLE t_sso (' || string_agg(name || ' ' || type || ' ' || not_null, ', ') || ');' AS create_table_sql FROM table_columns;create_table_sql = CREATE TABLE t_sso (_id STRUCT("$oid" VARCHAR) , eventType VARCHAR , eventTypeName VARCHAR , eventResult VARCHAR , eventTime STRUCT("$date" VARCHAR) , userId VARCHAR , userName VARCHAR , userType BIGINT , userTitle VARCHAR , auditContent VARCHAR , auditCatalog VARCHAR , auditType VARCHAR , auditTypeName VARCHAR , compName VARCHAR , methodName VARCHAR , clientIp VARCHAR , clientAddr VARCHAR , clienMac VARCHAR , serviceIp VARCHAR , serviceAddr VARCHAR , serviciePort VARCHAR , requestMs BIGINT , rsRowcount BIGINT , auditId VARCHAR , beginTime STRUCT("$date" VARCHAR) , endTime STRUCT("$date" VARCHAR) );Run Time (s): real 0.001 user 0.000538 sys 0.001036
复制代码
得到文本精度
duckdb> .mode lineduckdb> WITH table_columns AS ( SELECT name, type FROM pragma_table_info('t_sso')) select concat('select ',group_concat(concat('max(length(',name,')) as ',name)), ' from t_sso;') as col_len_sql from table_columns where type='VARCHAR'; col_len_sql = select max(length(eventType)) as eventType,max(length(eventTypeName)) as eventTypeName,max(length(eventResult)) as eventResult,max(length(userId)) as userId,max(length(userName)) as userName,max(length(userTitle)) as userTitle,max(length(auditContent)) as auditContent,max(length(auditCatalog)) as auditCatalog,max(length(auditType)) as auditType,max(length(auditTypeName)) as auditTypeName,max(length(compName)) as compName,max(length(methodName)) as methodName,max(length(clientIp)) as clientIp,max(length(clientAddr)) as clientAddr,max(length(clienMac)) as clienMac,max(length(serviceIp)) as serviceIp,max(length(serviceAddr)) as serviceAddr,max(length(serviciePort)) as serviciePort,max(length(auditId)) as auditId from t_sso;Run Time (s): real 0.001 user 0.001623 sys 0.000043duckdb> .mode tableduckdb> select max(length(eventType)) as eventType,max(length(eventTypeName)) as eventTypeName,max(length(eventResult)) as eventResult,max(length(userId)) as userId,max(length(userName)) as userName,max(length(userTitle)) as userTitle,max(length(auditContent)) as auditContent,max(length(auditCatalog)) as auditCatalog,max(length(auditType)) as auditType,max(length(auditTypeName)) as auditTypeName,max(length(compName)) as compName,max(length(methodName)) as methodName,max(length(clientIp)) as clientIp,max(length(clientAddr)) as clientAddr,max(length(clienMac)) as clienMac,max(length(serviceIp)) as serviceIp,max(length(serviceAddr)) as serviceAddr,max(length(serviciePort)) as serviciePort,max(length(auditId)) as auditId from t_sso;+-----------+---------------+-------------+--------+----------+-----------+--------------+--------------+-----------+---------------+----------+------------+----------+------------+----------+-----------+-------------+--------------+---------+| eventType | eventTypeName | eventResult | userId | userName | userTitle | auditContent | auditCatalog | auditType | auditTypeName | compName | methodName | clientIp | clientAddr | clienMac | serviceIp | serviceAddr | serviciePort | auditId |+-----------+---------------+-------------+--------+----------+-----------+--------------+--------------+-----------+---------------+----------+------------+----------+------------+----------+-----------+-------------+--------------+---------+| 4 | 0 | 7 | 32 | 15 | 5 | 22691 | 535 | 14 | 0 | 21 | 55 | 14 | 14 | 0 | 11 | 8 | 4 | 89 |+-----------+---------------+-------------+--------+----------+-----------+--------------+--------------+-----------+---------------+----------+------------+----------+------------+----------+-----------+-------------+--------------+---------+Run Time (s): real 0.006 user 0.008515 sys 0.001032duckdb> UNPIVOT (select max(length(eventType)) as eventType,max(length(eventTypeName)) as eventTypeName,max(length(eventResult)) as eventResult,max(length(userId)) as userId,max(length(userName)) as userName,max(length(userTitle)) as userTitle,max(length(auditContent)) as auditContent,max(length(auditCatalog)) as auditCatalog,max(length(auditType)) as auditType,max(length(auditTypeName)) as auditTypeName,max(length(compName)) as compName,max(length(methodName)) as methodName,max(length(clientIp)) as clientIp,max(length(clientAddr)) as clientAddr,max(length(clienMac)) as clienMac,max(length(serviceIp)) as serviceIp,max(length(serviceAddr)) as serviceAddr,max(length(serviciePort)) as serviciePort,max(length(auditId)) as auditId from t_sso ) ON COLUMNS(*) INTO NAME colname VALUE collen ;+---------------+--------+| colname | collen |+---------------+--------+| eventType | 4 || eventTypeName | 0 || eventResult | 7 || userId | 32 || userName | 15 || userTitle | 5 || auditContent | 22691 || auditCatalog | 535 || auditType | 14 || auditTypeName | 0 || compName | 21 || methodName | 55 || clientIp | 14 || clientAddr | 14 || clienMac | 0 || serviceIp | 11 || serviceAddr | 8 || serviciePort | 4 || auditId | 89 |+---------------+--------+Run Time (s): real 0.009 user 0.011116 sys 0.001008
复制代码
通过关联两张表，将得到的最长文本长度传入 DDL。
duckdb> WITH table_columns AS ( SELECT name, type, CASE WHEN "notnull" = 1 THEN 'NOT NULL' ELSE '' END AS not_null FROM pragma_table_info('t_sso')), column_length AS ( UNPIVOT (select max(length(eventType)) as eventType,max(length(eventTypeName)) as eventTypeName,max(length(eventResult)) as eventResult,max(length(userId)) as userId,max(length(userName)) as userName,max(length(userTitle)) as userTitle,max(length(auditContent)) as auditContent,max(length(auditCatalog)) as auditCatalog,max(length(auditType)) as auditType,max(length(auditTypeName)) as auditTypeName,max(length(compName)) as compName,max(length(methodName)) as methodName,max(length(clientIp)) as clientIp,max(length(clientAddr)) as clientAddr,max(length(clienMac)) as clienMac,max(length(serviceIp)) as serviceIp,max(length(serviceAddr)) as serviceAddr,max(length(serviciePort)) as serviciePort,max(length(auditId)) as auditId from t_sso ) ON COLUMNS(*) INTO NAME name VALUE charlen ) SELECT 'CREATE TABLE tidb_bakdb.t_sso2 (' || string_agg(name || ' ' || type || CASE WHEN "charlen" >0 THEN concat ('(',charlen,')') ELSE '' END ||' ' || not_null , ', ') || ');' AS create_table_sql from (select table_columns.name , table_columns.type ,column_length.charlen,table_columns.not_null FROM table_columns left join column_length using ('name') order by name);create_table_sql = CREATE TABLE tidb_bakdb.t_sso2 (_id STRUCT("$oid" VARCHAR) , auditCatalog VARCHAR(535) , auditContent VARCHAR(22691) , auditId VARCHAR(89) , auditType VARCHAR(14) , auditTypeName VARCHAR , beginTime STRUCT("$date" VARCHAR) , clienMac VARCHAR , clientAddr VARCHAR(14) , clientIp VARCHAR(14) , compName VARCHAR(21) , endTime STRUCT("$date" VARCHAR) , eventResult VARCHAR(7) , eventTime STRUCT("$date" VARCHAR) , eventType VARCHAR(4) , eventTypeName VARCHAR , methodName VARCHAR(55) , requestMs BIGINT , rsRowcount BIGINT , serviceAddr VARCHAR(8) , serviceIp VARCHAR(11) , serviciePort VARCHAR(4) , userId VARCHAR(32) , userName VARCHAR(15) , userTitle VARCHAR(5) , userType BIGINT );Run Time (s): real 0.008 user 0.010580 sys 0.000970
复制代码
将 DDL 中的 STRUCT 等少量字段手工调整后，得到创建 TiDB 表的 DDL。
CREATE TABLE bakdb.t_sso2 (oid VARCHAR(32) primary key, auditCatalog VARCHAR(535) , auditContent TEXT , auditId VARCHAR(89) , auditType VARCHAR(14) , auditTypeName VARCHAR(20) , beginTime datetime(3) , clienMac VARCHAR(20) , clientAddr VARCHAR(14) , clientIp VARCHAR(14) , compName VARCHAR(21) , endTime datetime(3) , eventResult VARCHAR(7) , eventTime datetime(3) , eventType VARCHAR(4) , eventTypeName VARCHAR(20) , methodName VARCHAR(55) , requestMs BIGINT , rsRowcount BIGINT , serviceAddr VARCHAR(8) , serviceIp VARCHAR(11) , serviciePort VARCHAR(4) , userId VARCHAR(32) , userName VARCHAR(15) , userTitle VARCHAR(5) , userType BIGINT );
复制代码
通过远程执行的方式创建表。
duckdb> CALL mysql_execute('tidb_bakdb', 'CREATE TABLE bakdb.t_sso2 (oid VARCHAR(32) primary key, auditCatalog VARCHAR(535) , auditContent TEXT , auditId VARCHAR(89) , auditType VARCHAR(14) , auditTypeName VARCHAR(20) , beginTime datetime(3) , clienMac VARCHAR(20) , clientAddr VARCHAR(14) , clientIp VARCHAR(14) , compName VARCHAR(21) , endTime datetime(3) , eventResult VARCHAR(7) , eventTime datetime(3) , eventType VARCHAR(4) , eventTypeName VARCHAR(20) , methodName VARCHAR(55) , requestMs BIGINT , rsRowcount BIGINT , serviceAddr VARCHAR(8) , serviceIp VARCHAR(11) , serviciePort VARCHAR(4) , userId VARCHAR(32) , userName VARCHAR(15) , userTitle VARCHAR(5) , userType BIGINT )');Run Time (s): real 0.664 user 0.658869 sys 0.001826duckdb> .mode tableduckdb> SELECT * FROM mysql_query('tidb_bakdb', 'desc t_sso2');+---------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------------+--------------+------+-----+---------+-------+| oid | varchar(32) | NO | PRI | | || auditCatalog | varchar(535) | YES | | | || auditContent | text | YES | | | || auditId | varchar(89) | YES | | | || auditType | varchar(14) | YES | | | || auditTypeName | varchar(20) | YES | | | || beginTime | datetime(3) | YES | | | || clienMac | varchar(20) | YES | | | || clientAddr | varchar(14) | YES | | | || clientIp | varchar(14) | YES | | | || compName | varchar(21) | YES | | | || endTime | datetime(3) | YES | | | || eventResult | varchar(7) | YES | | | || eventTime | datetime(3) | YES | | | || eventType | varchar(4) | YES | | | || eventTypeName | varchar(20) | YES | | | || methodName | varchar(55) | YES | | | || requestMs | bigint | YES | | | || rsRowcount | bigint | YES | | | || serviceAddr | varchar(8) | YES | | | || serviceIp | varchar(11) | YES | | | || serviciePort | varchar(4) | YES | | | || userId | varchar(32) | YES | | | || userName | varchar(15) | YES | | | || userTitle | varchar(5) | YES | | | || userType | bigint | YES | | | |+---------------+--------------+------+-----+---------+-------+Run Time (s): real 0.004 user 0.000490 sys 0.001035
复制代码
导入数据
duckdb> select group_concat(name) FROM (select name from pragma_table_info('bak.t_sso') order by name);+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| group_concat("name") |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| _id,auditCatalog,auditContent,auditId,auditType,auditTypeName,beginTime,clienMac,clientAddr,clientIp,compName,endTime,eventResult,eventTime,eventType,eventTypeName,methodName,requestMs,rsRowcount,serviceAddr,serviceIp,serviciePort,userId,userName,userTitle,userType |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Run Time (s): real 0.003 user 0.000910 sys 0.000924duckdb> CALL mysql_clear_cache(); ┌─────────┐│ Success ││ boolean │├─────────┤│ 0 rows │└─────────┘Run Time (s): real 0.000 user 0.000730 sys 0.000000duckdb> insert into tidb_bakdb.t_sso2 select json_extract_string(_id, '$.$oid') as oid,auditCatalog,auditContent,auditId,auditType,auditTypeName,date_add(strptime(json_extract_string(beginTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as beginTime,clienMac,clientAddr,clientIp,compName,date_add(strptime(json_extract_string(endTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as endTime,eventResult,date_add(strptime(json_extract_string(eventTime, '$.$date'),'%Y-%m-%dT%H:%M:%S.%gZ'),INTERVAL 8 HOUR) as eventTime,eventType,eventTypeName,methodName,requestMs,rsRowcount,serviceAddr,serviceIp,serviciePort,userId,userName,userTitle,userType from bak.t_sso;Run Time (s): real 1.211 user 0.138558 sys 0.052049
复制代码
在 TiDB 查看
mysql> show create table t_sso2;+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t_sso2 | CREATE TABLE `t_sso2` ( `oid` varchar(32) NOT NULL, `auditCatalog` varchar(535) DEFAULT NULL, `auditContent` text DEFAULT NULL, `auditId` varchar(89) DEFAULT NULL, `auditType` varchar(14) DEFAULT NULL, `auditTypeName` varchar(20) DEFAULT NULL, `beginTime` datetime(3) DEFAULT NULL, `clienMac` varchar(20) DEFAULT NULL, `clientAddr` varchar(14) DEFAULT NULL, `clientIp` varchar(14) DEFAULT NULL, `compName` varchar(21) DEFAULT NULL, `endTime` datetime(3) DEFAULT NULL, `eventResult` varchar(7) DEFAULT NULL, `eventTime` datetime(3) DEFAULT NULL, `eventType` varchar(4) DEFAULT NULL, `eventTypeName` varchar(20) DEFAULT NULL, `methodName` varchar(55) DEFAULT NULL, `requestMs` bigint DEFAULT NULL, `rsRowcount` bigint DEFAULT NULL, `serviceAddr` varchar(8) DEFAULT NULL, `serviceIp` varchar(11) DEFAULT NULL, `serviciePort` varchar(4) DEFAULT NULL, `userId` varchar(32) DEFAULT NULL, `userName` varchar(15) DEFAULT NULL, `userTitle` varchar(5) DEFAULT NULL, `userType` bigint DEFAULT NULL, PRIMARY KEY (`oid`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
mysql> select * from bakdb.t_sso2 limit 1 \G*************************** 1. row *************************** oid: 677b2d71e4b020d9e1188c46 auditCatalog: Request processing failed; nested exception is java.lang.IndexOutOfBoundsException: Index: 0, Size: 0 auditContent: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is java.lang.IndexOutOfBoundsException: Index: 0, Size: 0... 字段内错误堆栈文本略 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) ... 70 more
auditId: 20250129091009004005 auditType: AUDIT_ERRORLOGauditTypeName: beginTime: 2025-01-29 09:10:09.531 clienMac: clientAddr: 10.0.0.245 clientIp: 10.0.0.48 compName: /aa_aaa_amanage endTime: 2025-01-29 09:10:09.532 eventResult: FAILURE eventTime: 2025-01-29 09:10:09.531 eventType: GETeventTypeName: methodName: /aa_aaa_amanage/storage/initArcMatain requestMs: 0 rsRowcount: 0 serviceAddr: app2 serviceIp: 10.0.0.38 serviciePort: 7099 userId: FC8364C29422E70A27E1054281289C39 userName: YWZY userTitle: 联调_运维 userType: 01 row in set (0.00 sec)
复制代码
四、总结
在 MySQL 生态的用户中，同时使用 MongoDB 数据库和 TiDB 数据库的用户较多，借助 DuckDB 的插件能力将 MongoDB 的数据迁移到 TiDB，实现轻量数据迁移的便捷化。在 DuckDB 插件生态中，有多种其他数据库的连接器，其他的数据库也可以参考。
划线
评论
复制
发布于: 刚刚阅读数: 4
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/712383c6b58d1693af604629f】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论