MySQL Shell import_table資料匯入

ZhenXing_Yu發表於2021-08-05

1. import_table介紹

上期技術分享我們介紹了MySQL Load Data的4種常用的方法將文字資料匯入到MySQL,這一期我們繼續介紹另一款更加高效的資料匯入工具,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方式匯入資料

具體引數含義不做說明,需要了解語法規則及含義可檢視系列上一篇文章<MySQL 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能力有限情況下)

5. 技術總結

import_table包含了Load Data幾乎所有的功能
import_table匯入的效率比Load Data更高
import_table支援對匯入速度,併發以及每次匯入的資料大小做精細控制
import_table的匯入進度報告更加詳細,便於排錯及時間評估,包括
- 匯入速度
- 匯入總耗時
- 每批次匯入的資料量,是否存在Warning等等
- 匯入最終的彙總報告

mysql資料匯入匯出
2014-11-10
MySql
mysql資料匯出匯入
2013-11-09
MySql
資料庫 MySQL 資料匯入匯出
2021-08-10
資料庫MySql
資料匯入shell指令碼(下）
2014-07-12
指令碼
mysql 資料庫匯入匯出
2010-07-02
MySql資料庫
MySQL資料庫匯入匯出
2007-11-06
MySql資料庫
MySQL入門--匯出和匯入資料
2019-06-04
MySql
【mysql】資料庫匯出和匯入
2017-09-19
MySql資料庫
mysql匯入匯出.csv格式資料
2013-12-19
MySql
mysqldump匯入匯出mysql資料庫
2013-08-20
MySql資料庫
MySQL表資料匯入與匯出
2016-02-28
MySql
MYSQL資料檔案匯入
2016-01-14
MySql
ORACLE MYSQL互相匯入資料
2015-11-07
OracleMySql
MySQL資料匯入匯出牛刀小試
2015-04-11
MySql
Windows 下 MySQL 資料匯入 Redis
2019-07-03
WindowsMySqlRedis
MySQL匯入百萬資料實踐
2018-11-03
MySql
MySQL 批量匯入資料優化
2016-07-12
MySql優化
Mysql匯入資料相當慢
2015-12-08
MySql
MySQL資料匯入匯出亂碼問題
2017-12-10
MySql
【MySQL】白話說MySQL(五)，資料的匯出與匯入
2020-12-19
MySql
MySQL資料匯入匯出之Load data file
2016-09-01
MySql
MySQL資料匯入匯出方法與工具介紹
2007-11-06
MySql
用Navicat把SQLServer資料匯入MySQL
2021-04-01
ServerMySql
Solr-5.3.1 dataimport 匯入mysql資料
2017-06-05
SolrAIImportMySql
把Excel表資料匯入到mysql
2017-12-28
ExcelMySql
MySQL資料匯入到infobright中
2016-01-26
MySql
從Excel到匯入MYSQL資料庫
2007-04-09
ExcelMySql資料庫
mysql資料庫備份匯入命令
2009-12-30
MySql資料庫
MySQL資料庫結構和資料的匯出和匯入 (轉)
2007-12-04
MySql資料庫
shell，ant指令碼實現自動資料泵(exp.expdp)匯出匯入資料
2016-04-14
指令碼
SQLServer匯出匯入資料到MySQL
2023-11-13
ServerMySql
Navicat、into outfile、mysql命令、mysqldump、mysqlpump、mydumper匯出匯入資料
2020-05-29
MySql
sqoop用法之mysql與hive資料匯入匯出
2020-12-22
OOPMySqlHive
通過cmd視窗匯入匯出mysql資料庫
2017-09-22
MySql資料庫
MYSQL資料匯出備份、匯入的幾種方式
2018-05-06
MySql
定時將資料匯入到hive的shell指令碼
2015-05-12
Hive指令碼
在SQL Server資料庫中匯入MySQL資料庫
2009-02-01
Server資料庫MySql
spark sql與mysql 資料載入與匯出資料到mysql
2018-11-08
SparkMySql

MySQL Shell import_table資料匯入

1. import_table介紹

2. Load Data 與 import table功能示例

2.1 用Load Data方式匯入資料

2.2 用import_table方式匯入資料

3. import_table特定功能

3.1 多檔案匯入(模糊匹配)

3.2 併發匯入

3.3 匯入速率控制

3.4 自定義chunk大小

4. Load Data vs import_table效能對比

5. 技術總結

相關文章