一、背景
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.sql
duckdb> 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.148988
duckdb> CREATE PERSISTENT SECRET tidb_secret_1 (
TYPE MYSQL,
HOST '172.16.201.122',
PORT 4500,
DATABASE bakdb,
USER 'bakuser',
PASSWORD 'bak12345'
);
Success = true
Run Time (s): real 0.003 user 0.003724 sys 0.000006
duckdb> ATTACH '' AS tidb_bakdb (TYPE MYSQL, SECRET tidb_secret_1);
Run Time (s): real 0.012 user 0.012089 sys 0.000302
duckdb> .mode duckbox
duckdb> show databases;
┌───────────────┐
│ database_name │
│ varchar │
├───────────────┤
│ bak │
│ tidb_bakdb │
└───────────────┘
Run Time (s): real 0.002 user 0.001863 sys 0.000000
duckdb> use tidb_bakdb;
Run Time (s): real 0.010 user 0.010106 sys 0.000000
duckdb> 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.017694
duckdb> 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 line
duckdb> select * from '/root/sso.json' limit 1 ;
_id = {'$oid': 677b2d71e4b020d9e1188c46}
eventType = GET
eventTypeName =
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_ERRORLOG
auditTypeName =
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.026003
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.005043 sys 0.000011
duckdb> .mode line
duckdb> select * from t_sso limit 1;
_id = {'$oid': 677b2d71e4b020d9e1188c46}
eventType = GET
eventTypeName =
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_ERRORLOG
auditTypeName =
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,endTime
Run Time (s): real 0.001 user 0.000974 sys 0.000000
duckdb> .mode duckbox
duckdb> 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.000000
duckdb> 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.014937
duckdb> select count(1) from tidb_bakdb.t_sso;
┌──────────┐
│ count(1) │
│ int64 │
├──────────┤
│ 2620 │
└──────────┘
Run Time (s): real 0.012 user 0.007022 sys 0.002068
duckdb> select count(1) from t_sso;
┌──────────┐
│ count(1) │
│ int64 │
├──────────┤
│ 2620 │
└──────────┘
Run Time (s): real 0.001 user 0.001053 sys 0.000004
duckdb> 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.001027
duckdb> 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 line
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'))
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 line
duckdb> 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.000043
duckdb> .mode table
duckdb> 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.001032
duckdb> 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.001826
duckdb> .mode table
duckdb> 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.000924
duckdb> CALL mysql_clear_cache();
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ 0 rows │
└─────────┘
Run Time (s): real 0.000 user 0.000730 sys 0.000000
duckdb> 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_ERRORLOG
auditTypeName:
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: GET
eventTypeName:
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: 0
1 row in set (0.00 sec)
四、总结
在 MySQL 生态的用户中，同时使用 MongoDB 数据库和 TiDB 数据库的用户较多，借助 DuckDB 的插件能力将 MongoDB 的数据迁移到 TiDB，实现轻量数据迁移的便捷化。在 DuckDB 插件生态中，有多种其他数据库的连接器，其他的数据库也可以参考。