MySQL Shell import_table數(shù)據(jù)導(dǎo)入的實(shí)現(xiàn)
更新時(shí)間：2021年08月05日 15:54:09 作者：ZhenXing_Yu
這篇文章主要介紹了MySQL Shell import_table數(shù)據(jù)導(dǎo)入的實(shí)現(xiàn)，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
1. import_table介紹
上期技術(shù)分享我們介紹了MySQL Load Data的4種常用的方法將文本數(shù)據(jù)導(dǎo)入到MySQL,這一期我們繼續(xù)介紹另一款更加高效的數(shù)據(jù)導(dǎo)入工具,MySQL Shell 工具集中的import_table,該工具的全稱是Parallel Table Import Utility,顧名思義,支持并發(fā)數(shù)據(jù)導(dǎo)入,該工具在MySQL Shell 8.0.23版本后,功能更加完善, 以下列舉該工具的核心功能
- 基本覆蓋了MySQL Data Load的所有功能,可以作為替代品使用
- 默認(rèn)支持并發(fā)導(dǎo)入(支持自定義chunk大小)
- 支持通配符匹配多個(gè)文件同時(shí)導(dǎo)入到一張表(非常適用于相同結(jié)構(gòu)數(shù)據(jù)匯總到一張表)
- 支持限速(對帶寬使用有要求的場景,非常合適)
- 支持對壓縮文件處理
- 支持導(dǎo)入到5.7及以上MySQL
2. Load Data 與 import table功能示例
該部分針對import table和Load Data相同的功能做命令示例演示,我們依舊以導(dǎo)入employees表的示例數(shù)據(jù)為例,演示MySQL Load Data的綜合場景
- 數(shù)據(jù)自定義順序?qū)?/li>
- 數(shù)據(jù)函數(shù)處理
- 自定義數(shù)據(jù)取值
## 示例數(shù)據(jù)如下
[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"
## 示例表結(jié)構(gòu)
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 | | -- 表新增字段,導(dǎo)出數(shù)據(jù)文件中不存在
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
| modify_date | datetime | YES | | NULL | | -- 表新增字段,導(dǎo)出數(shù)據(jù)文件中不存在
| delete_flag | varchar(1) | YES | | NULL | | -- 表新增字段,導(dǎo)出數(shù)據(jù)文件中不存在
+-------------+---------------+------+-----+---------+-------+
2.1 用Load Data方式導(dǎo)入數(shù)據(jù)
具體參數(shù)含義不做說明,需要了解語法規(guī)則及含義可查看系列上一篇文章<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方式導(dǎo)入數(shù)據(jù)
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], ## 文件中多少個(gè)列就用多少個(gè)序號標(biāo)識就行
"decodeColumns": {
"emp_no": "@1", ## 對應(yīng)文件中的第1列
"birth_date": "@2", ## 對應(yīng)文件中的第2個(gè)列
"first_name": "upper(@3)", ## 對應(yīng)文件中的第3個(gè)列,并做轉(zhuǎn)為大寫的處理
"last_name": "lower(@4)", ## 對應(yīng)文件中的第4個(gè)列,并做轉(zhuǎn)為大寫的處理
"full_name": "concat(@3,' ',@4)", ## 將文件中的第3,4列合并成一列生成表中字段值
"gender": "@5", ## 對應(yīng)文件中的第5個(gè)列
"hire_date": "@6", ## 對應(yīng)文件中的第6個(gè)列
"modify_date": "now()", ## 用函數(shù)生成表中字段值
"delete_flag": "if(@6<'1988-01-01','Y','N')" ## 基于文件中第6列做邏輯判斷,生成表中對應(yīng)字段值
}
})
3. import_table特定功能
3.1 多文件導(dǎo)入(模糊匹配)
## 在導(dǎo)入前我生成好了3分單獨(dú)的employees文件,導(dǎo)出的結(jié)構(gòu)一致
[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
## 導(dǎo)入命令,其中對對文件用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], ## 文件中多少個(gè)列就用多少個(gè)序號標(biāo)識就行
"decodeColumns": {
"emp_no": "@1", ## 對應(yīng)文件中的第1列
"birth_date": "@2", ## 對應(yīng)文件中的第2個(gè)列
"first_name": "upper(@3)", ## 對應(yīng)文件中的第3個(gè)列,并做轉(zhuǎn)為大寫的處理
"last_name": "lower(@4)", ## 對應(yīng)文件中的第4個(gè)列,并做轉(zhuǎn)為大寫的處理
"full_name": "concat(@3,' ',@4)", ## 將文件中的第3,4列合并成一列生成表中字段值
"gender": "@5", ## 對應(yīng)文件中的第5個(gè)列
"hire_date": "@6", ## 對應(yīng)文件中的第6個(gè)列
"modify_date": "now()", ## 用函數(shù)生成表中字段值
"delete_flag": "if(@6<'1988-01-01','Y','N')" ## 基于文件中第6列做邏輯判斷,生成表中對應(yīng)字段值
}
})
## 導(dǎo)入命令,其中對要導(dǎo)入的文件均明確指定其路徑
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], ## 文件中多少個(gè)列就用多少個(gè)序號標(biāo)識就行
"decodeColumns": {
"emp_no": "@1", ## 對應(yīng)文件中的第1列
"birth_date": "@2", ## 對應(yīng)文件中的第2個(gè)列
"first_name": "upper(@3)", ## 對應(yīng)文件中的第3個(gè)列,并做轉(zhuǎn)為大寫的處理
"last_name": "lower(@4)", ## 對應(yīng)文件中的第4個(gè)列,并做轉(zhuǎn)為大寫的處理
"full_name": "concat(@3,' ',@4)", ## 將文件中的第3,4列合并成一列生成表中字段值
"gender": "@5", ## 對應(yīng)文件中的第5個(gè)列
"hire_date": "@6", ## 對應(yīng)文件中的第6個(gè)列
"modify_date": "now()", ## 用函數(shù)生成表中字段值
"delete_flag": "if(@6<'1988-01-01','Y','N')" ## 基于文件中第6列做邏輯判斷,生成表中對應(yīng)字段值
}
})
3.2 并發(fā)導(dǎo)入
在實(shí)驗(yàn)并發(fā)導(dǎo)入前我們創(chuàng)建一張1000W的sbtest1表(大約2G數(shù)據(jù)),做并發(fā)模擬,import_table用threads參數(shù)作為并發(fā)配置, 默認(rèn)為8個(gè)并發(fā).
## 導(dǎo)出測試需要的sbtest1數(shù)據(jù)
[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個(gè)并發(fā)
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 導(dǎo)入速率控制
可以通過maxRate和threads來控制每個(gè)并發(fā)線程的導(dǎo)入數(shù)據(jù),如,當(dāng)前配置線程為4個(gè),每個(gè)線程的速率為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大小
默認(rèn)的chunk大小為50M,我們可以調(diào)整chunk的大小,減少事務(wù)大小,如我們將chunk大小調(diào)整為1M,則每個(gè)線程每次導(dǎo)入的數(shù)據(jù)量也相應(yīng)減少
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性能對比
- 使用相同庫表
- 不對數(shù)據(jù)做特殊處理,原樣導(dǎo)入
- 不修改參數(shù)默認(rèn)值,只指定必備參數(shù)
-- 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耗時(shí)約5分鐘,而import_table則只要不到一半的時(shí)間即可完成數(shù)據(jù)導(dǎo)入,效率高一倍以上(虛擬機(jī)環(huán)境磁盤IO能力有限情況下)
5. 技術(shù)總結(jié)
- import_table包含了Load Data幾乎所有的功能
- import_table導(dǎo)入的效率比Load Data更高
- import_table支持對導(dǎo)入速度,并發(fā)以及每次導(dǎo)入的數(shù)據(jù)大小做精細(xì)控制
- import_table的導(dǎo)入進(jìn)度報(bào)告更加詳細(xì),便于排錯(cuò)及時(shí)間評估,包括
- 導(dǎo)入速度
- 導(dǎo)入總耗時(shí)
- 每批次導(dǎo)入的數(shù)據(jù)量,是否存在Warning等等
- 導(dǎo)入最終的匯總報(bào)告
到此這篇關(guān)于MySQL import_table數(shù)據(jù)導(dǎo)入的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL import_table數(shù)據(jù)導(dǎo)入內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- Mysql命令行導(dǎo)入sql數(shù)據(jù)
- MYSQL 數(shù)據(jù)庫導(dǎo)入導(dǎo)出命令
- mysql導(dǎo)入導(dǎo)出數(shù)據(jù)中文亂碼解決方法小結(jié)
- MySQL 文本文件的導(dǎo)入導(dǎo)出數(shù)據(jù)的方法
- MySQL如何導(dǎo)入csv格式數(shù)據(jù)文件解決方案
- 在linux中導(dǎo)入sql文件的方法分享(使用命令行轉(zhuǎn)移mysql數(shù)據(jù)庫)
- mysql 導(dǎo)入導(dǎo)出數(shù)據(jù)庫、數(shù)據(jù)表的方法
- mysql 導(dǎo)入導(dǎo)出數(shù)據(jù)庫以及函數(shù)、存儲過程的介紹
- MYSQL大數(shù)據(jù)導(dǎo)入
相關(guān)文章
SQL去重是數(shù)據(jù)分析工作中比較常見的一個(gè)場景,下面這篇文章主要給大家介紹了關(guān)于SQL去重的3種實(shí)用方法的相關(guān)資料,文中通過圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-12-12
文章主要描述了在MySQL中沒有root用戶的情況如何解決,首先,作者提到由于沒有root用戶,無法進(jìn)行后續(xù)的權(quán)限設(shè)置和操作,然后,作者詳細(xì)介紹了創(chuàng)建root用戶并賦予其所有權(quán)限的步驟,包括解決可能遇到的錯(cuò)誤,最后,作者總結(jié)了整個(gè)過程,并希望大家能夠從中受益2025-02-02
利用MySQL函數(shù)實(shí)現(xiàn)判斷視頻擴(kuò)展名的代碼
MySQL擁有強(qiáng)大的自定義函數(shù)功能，如下，我寫了一個(gè)用MySQL函數(shù) 判斷視頻地址是否可以手機(jī)端播放2012-02-02
mysql5.6及以下版本如何查詢數(shù)據(jù)庫里的json
MySQL里面保存數(shù)據(jù)有時(shí)候會把一些雜亂且不常用的時(shí)候丟進(jìn)一個(gè)json字段里面,那么如何查詢數(shù)據(jù)庫里的json呢以及mysql存儲json注意那些格式呢？接下來通過本文給大家詳細(xì)介紹，需要的朋友參考下2017-03-03![MySQL 錯(cuò)誤處理例子[譯]](http://img.jbzj.com/images/xgimg/bcimg4.png)
MySQL 錯(cuò)誤處理例子，國外人寫的，大家可以參考下。2009-10-10
MySQL中的保存點(diǎn)Savepoint是一個(gè)用于控制事務(wù)的重要工具,本文主要介紹了MySQL的savepoint簡介及實(shí)例,具有一定的參考價(jià)值,感興趣的可以了解一下2023-08-08
本文主要介紹了MySQL查看使用的內(nèi)存情況的幾種方法,主要介紹了5種查看方法,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-05-05
MySQL利用procedure analyse()函數(shù)優(yōu)化表結(jié)構(gòu)
不知道是否遇到過為MySQL表結(jié)構(gòu)該選擇什么類型字段而郁悶？或者為MySQL字段該選擇多少長度而糾結(jié)？下面這篇文章就給大家介紹一個(gè)武林秘籍吧~也是我最近才學(xué)來的，感興趣的朋友們下面來一起看看吧。2016-12-12
淺談mysql中多表不關(guān)聯(lián)查詢的實(shí)現(xiàn)方法
下面小編就為大家?guī)硪黄獪\談mysql中多表不關(guān)聯(lián)查詢的實(shí)現(xiàn)方法。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2016-10-10
MySql 快速插入千萬級大數(shù)據(jù)的方法示例
這篇文章主要介紹了MySql 快速插入千萬級大數(shù)據(jù)的方法示例，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-08-08