MySQL数据库Shell import_table数据导入
程序员文章站
2022-06-09 21:26:36
目录mysql shell import_table数据导入2. load data 与 import table功能示例2.1 用load data方式导入数据4. load data vs imp...
mysql shell import_table数据导入
1. import_table介绍
这一期我们介绍一款高效的数据导入工具,mysql shell 工具集中的import_table,该工具的全称是parallel table import utility,顾名思义,支持并发数据导入,该工具在mysql shell 8.0.23版本后,功能更加完善, 以下列举该工具的核心功能
- 基本覆盖了mysql data load的所有功能,可以作为替代品使用
- 默认支持并发导入(支持自定义chunk大小)
- 支持通配符匹配多个文件同时导入到一张表(非常适用于相同结构数据汇总到一张表)
- 支持限速(对带宽使用有要求的场景,非常合适)
- 支持对压缩文件处理
- 支持导入到5.7及以上mysql
2. load data 与 import table功能示例
该部分针对import table和load data相同的功能做命令示例演示,我们依旧以导入employees表的示例数据为例,演示mysql load data的综合场景
- 数据自定义顺序导入
- 数据函数处理
- 自定义数据取值
示例数据如下：
[root@10-186-61-162 tmp]# cat employees_01.csv "10001","1953-09-02","georgi","facello","m","1986-06-26" "10003","1959-12-03","parto","bamford","m","1986-08-28" "10002","1964-06-02","bezalel","simmel","f","1985-11-21" "10004","1954-05-01","chirstian","koblick","m","1986-12-01" "10005","1955-01-21","kyoichi","maliniak","m","1989-09-12" "10006","1953-04-20","anneke","preusig","f","1989-06-02" "10007","1957-05-23","tzvetan","zielinski","f","1989-02-10" "10008","1958-02-19","saniya","kalloufi","m","1994-09-15" "10009","1952-04-19","sumant","peac","f","1985-02-18" "10010","1963-06-01","duangkaew","piveteau","f","1989-08-24"
示例表结构：
10.186.61.162:3306 employees sql > desc emp;
+-------------+---------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-------------+---------------+------+-----+---------+-------+
| emp_no | int | no | pri | null | |
| birth_date | date | no | | null | |
| first_name | varchar(14) | no | | null | |
| last_name | varchar(16) | no | | null | |
| full_name | varchar(64) | yes | | null | | -- 表新增字段,导出数据文件中不存在
| gender | enum('m','f') | no | | null | |
| hire_date | date | no | | null | |
| modify_date | datetime | yes | | null | | -- 表新增字段,导出数据文件中不存在
| delete_flag | varchar(1) | yes | | null | | -- 表新增字段,导出数据文件中不存在
+-------------+---------------+------+-----+---------+-------+
2.1 用load data方式导入数据
load data infile '/data/mysql/3306/tmp/employees_01.csv'
into table employees.emp
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@c1,@c2,@c3,@c4,@c5,@c6)
set emp_no=@c1,
birth_date=@c2,
first_name=upper(@c3),
last_name=lower(@c4),
full_name=concat(first_name,' ',last_name),
gender=@c5,
hire_date=@c6 ,
modify_date=now(),
delete_flag=if(hire_date<'1988-01-01','y','n');
2.2 用import_table方式导入数据
util.import_table(
[
"/data/mysql/3306/tmp/employees_01.csv",
],
{
"schema": "employees",
"table": "emp",
"dialect": "csv-unix",
"skiprows": 0,
"showprogress": true,
"characterset": "utf8mb4",
"columns": [1,2,3,4,5,6], ## 文件中多少个列就用多少个序号标识就行
"decodecolumns": {
"emp_no": "@1", ## 对应文件中的第1列
"birth_date": "@2", ## 对应文件中的第2个列
"first_name": "upper(@3)", ## 对应文件中的第3个列,并做转为大写的处理
"last_name": "lower(@4)", ## 对应文件中的第4个列,并做转为大写的处理
"full_name": "concat(@3,' ',@4)", ## 将文件中的第3,4列合并成一列生成表中字段值
"gender": "@5", ## 对应文件中的第5个列
"hire_date": "@6", ## 对应文件中的第6个列
"modify_date": "now()", ## 用函数生成表中字段值
"delete_flag": "if(@6<'1988-01-01','y','n')" ## 基于文件中第6列做逻辑判断,生成表中对应字段值
}
})
3. import_table特定功能
3.1 多文件导入(模糊匹配)
## 在导入前我生成好了3分单独的employees文件,导出的结构一致
[root@10-186-61-162 tmp]# ls -lh
总用量 1.9g
-rw-r----- 1 mysql mysql 579 3月 24 19:07 employees_01.csv
-rw-r----- 1 mysql mysql 584 3月 24 18:48 employees_02.csv
-rw-r----- 1 mysql mysql 576 3月 24 18:48 employees_03.csv
-rw-r----- 1 mysql mysql 1.9g 3月 26 17:15 sbtest1.csv
## 导入命令,其中对对文件用employees_*做模糊匹配
util.import_table(
[
"/data/mysql/3306/tmp/employees_*",
],
{
"schema": "employees",
"table": "emp",
"dialect": "csv-unix",
"skiprows": 0,
"showprogress": true,
"characterset": "utf8mb4",
"columns": [1,2,3,4,5,6], ## 文件中多少个列就用多少个序号标识就行
"decodecolumns": {
"emp_no": "@1", ## 对应文件中的第1列
"birth_date": "@2", ## 对应文件中的第2个列
"first_name": "upper(@3)", ## 对应文件中的第3个列,并做转为大写的处理
"last_name": "lower(@4)", ## 对应文件中的第4个列,并做转为大写的处理
"full_name": "concat(@3,' ',@4)", ## 将文件中的第3,4列合并成一列生成表中字段值
"gender": "@5", ## 对应文件中的第5个列
"hire_date": "@6", ## 对应文件中的第6个列
"modify_date": "now()", ## 用函数生成表中字段值
"delete_flag": "if(@6<'1988-01-01','y','n')" ## 基于文件中第6列做逻辑判断,生成表中对应字段值
}
})
## 导入命令,其中对要导入的文件均明确指定其路径
util.import_table(
[
"/data/mysql/3306/tmp/employees_01.csv",
"/data/mysql/3306/tmp/employees_02.csv",
"/data/mysql/3306/tmp/employees_03.csv"
],
{
"schema": "employees",
"table": "emp",
"dialect": "csv-unix",
"skiprows": 0,
"showprogress": true,
"characterset": "utf8mb4",
"columns": [1,2,3,4,5,6], ## 文件中多少个列就用多少个序号标识就行
"decodecolumns": {
"emp_no": "@1", ## 对应文件中的第1列
"birth_date": "@2", ## 对应文件中的第2个列
"first_name": "upper(@3)", ## 对应文件中的第3个列,并做转为大写的处理
"last_name": "lower(@4)", ## 对应文件中的第4个列,并做转为大写的处理
"full_name": "concat(@3,' ',@4)", ## 将文件中的第3,4列合并成一列生成表中字段值
"gender": "@5", ## 对应文件中的第5个列
"hire_date": "@6", ## 对应文件中的第6个列
"modify_date": "now()", ## 用函数生成表中字段值
"delete_flag": "if(@6<'1988-01-01','y','n')" ## 基于文件中第6列做逻辑判断,生成表中对应字段值
}
})
3.2 并发导入
在实验并发导入前我们创建一张1000w的sbtest1表(大约2g数据),做并发模拟,import_table用threads参数作为并发配置, 默认为8个并发.
## 导出测试需要的sbtest1数据
[root@10-186-61-162 tmp]# ls -lh
总用量 1.9g
-rw-r----- 1 mysql mysql 579 3月 24 19:07 employees_01.csv
-rw-r----- 1 mysql mysql 584 3月 24 18:48 employees_02.csv
-rw-r----- 1 mysql mysql 576 3月 24 18:48 employees_03.csv
-rw-r----- 1 mysql mysql 1.9g 3月 26 17:15 sbtest1.csv
## 开启threads为8个并发
util.import_table(
[
"/data/mysql/3306/tmp/sbtest1.csv",
],
{
"schema": "demo",
"table": "sbtest1",
"dialect": "csv-unix",
"skiprows": 0,
"showprogress": true,
"characterset": "utf8mb4",
"threads": "8"
})
3.3 导入速率控制
可以通过maxrate和threads来控制每个并发线程的导入数据,如,当前配置线程为4个,每个线程的速率为2m/s,则最高不会超过8m/s
util.import_table(
[
"/data/mysql/3306/tmp/sbtest1.csv",
],
{
"schema": "demo",
"table": "sbtest1",
"dialect": "csv-unix",
"skiprows": 0,
"showprogress": true,
"characterset": "utf8mb4",
"threads": "4",
"maxrate": "2m"
})
3.4 自定义chunk大小
默认的chunk大小为50m,我们可以调整chunk的大小,减少事务大小,如我们将chunk大小调整为1m,则每个线程每次导入的数据量也相应减少
util.import_table(
[
"/data/mysql/3306/tmp/sbtest1.csv",
],
{
"schema": "demo",
"table": "sbtest1",
"dialect": "csv-unix",
"skiprows": 0,
"showprogress": true,
"characterset": "utf8mb4",
"threads": "4",
"bytesperchunk": "1m",
"maxrate": "2m"
})
4. load data vs import_table性能对比
- 使用相同库表
- 不对数据做特殊处理,原样导入
- 不修改参数默认值,只指定必备参数
-- load data语句
load data infile '/data/mysql/3306/tmp/sbtest1.csv'
into table demo.sbtest1
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
-- import_table语句
util.import_table(
[
"/data/mysql/3306/tmp/sbtest1.csv",
],
{
"schema": "demo",
"table": "sbtest1",
"dialect": "csv-unix",
"skiprows": 0,
"showprogress": true,
"characterset": "utf8mb4"
})
可以看到,load data耗时约5分钟,而import_table则只要不到一半的时间即可完成数据导入,效率高一倍以上(虚拟机环境磁盘io能力有限情况下)
以上就是mysql shell import_table数据导入详情的详细内容，更多关于import_table数据导入的资料请关注其它相关文章！