如何使用AnalyticDBMySQL版匯入工具匯入本機資料 - AnalyticDB

本文介紹如何使用AnalyticDB for MySQL匯入工具將本機資料匯入至AnalyticDB for MySQL數倉版叢集。

功能介紹

工作原理
AnalyticDB for MySQL匯入工具通過JDBC協議接入負載平衡器（Load Balancer），負載平衡器下可串連多個前端節點（FrontNode），前端節點主要負責MySQL協議解析、SQL解析、資料寫入、查詢調度等，資料經由前端節點轉寄給儲存節點進行匯入。
功能特點
相較於MySQL Load Data工具，AnalyticDB for MySQL匯入工具有如下特點：
- 支援通過配置batchSize、並發數等來控制匯入速度，實現以最大化的輸送量進行資料匯入。參數配置詳情，請參見步驟三：指令碼準備。
- 支援單個檔案、多個檔案或檔案夾的匯入，無需啟動多個MySQL Load Data進程並行匯入。
- 能夠利用並行、Batch、池化、流水線執行（讀寫非串列）、GC-less programming、大塊檔案順序IO讀等技術實現更佳的匯入效能，匯入工具如果配置合理，可以最大化AnalyticDB for MySQL叢集的寫入吞吐（Throughput）。

匯入流程介紹

步驟	說明
步驟一：下載並解壓匯入工具	下載AnalyticDB for MySQL匯入工具並完成解壓和安裝。
步驟二：匯入資料準備	準備需要匯入的來源資料。
步驟三：指令碼準備	修改匯入指令碼模板中的參數，準備資料匯入指令碼。
步驟四：執行匯入	執行匯入指令碼將本機資料匯入至AnalyticDB for MySQL叢集。

步驟一：下載並解壓匯入工具

執行以下命令，建立一個目錄（本文樣本中為/u01/loadata）。
```
mkdir -p /u01/loadata
```
執行以下命令，進入目錄。
```
cd /u01/loadata
```

執行以下命令，下載匯入工具。

wget https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20220811/gpvn/adb-import-tool.tar.gz

執行以下命令，解壓匯入工具。
```
tar zxvf adb-import-tool.tar.gz
```
解壓後將會產生如下檔案：
```
adb-import.sh.template
adb-import.sh.template.md5
adb-import-tool.jar
adb-import-tool.jar.md5
```
說明
您可以通過執行java -version命令來確認是否已安裝Java，以及Java版本是否為1.8或以上。

步驟二：匯入資料準備

說明

您還可以使用Linux的split命令對大檔案進行切分（建議切分後的檔案大小為1 GB~2 GB），檔案切分後會形成更多的分區，更利於AnalyticDB for MySQL匯入工具執行並行匯入，從而提升匯入速度，縮短匯入時間。

例如，您可以將一個128 GB的檔案filename.txt均勻地切分為64份，每個檔案2 GB，那麼AnalyticDB for MySQL匯入工具將會以64的並行度來讀取檔案。切分命令如下：

split -l$((`wc -l < filename.txt`/64 + 1)) filename.txt filename.txt.split -da 2;

確認需要匯入的檔案或檔案夾的絕對路徑。
確認匯入檔案的行分隔字元和資料行分隔符號。更多關於分隔字元的說明，請參見步驟三：指令碼準備。
確認匯入檔案的列順序必須與建表DDL定義順序一致，可以在資料庫中執行SHOW CREATE TABLE確保列順序。列數必須大於1。
例如，可以使用如下命令定義表結構：
```
CREATE TABLE `product_info` (
`id` bigint NOT NULL,
`name` varchar,
`price` decimal(15, 2) NOT NULL
)
DISTRIBUTED BY HASH(`id`)
INDEX_ALL='Y';
```
合法檔案內容如下：
```
1|tv|1000.0
2|computer|2000.0
3|cup|15.8
```
匯入工具相容最後一列包含多餘分隔字元的情況。
例如1|abc|3.0和1|abc|3.0|都是合法的。
說明
- 對於Null 字元串，匯入工具預設按照null處理，例如匯入的檔案為4||5.0，則name列會插入null值，而不是字串''。
- 如果列中存在自增列，檔案中無需特殊體現，匯入工具可以相容。

步驟三：指令碼準備

adb-import.sh.template是一個模板指令碼，支援自訂指令碼名稱，例如要匯入的表名為product_info，您可以將指令碼命名為adb-import-product_info.sh。您可以複製一份模板指令碼，並根據資料匯入情境編輯匯入指令碼參數。

# 指令碼中的參數說明 #
#-------------------------------- #
#          下面是必填參數
# -------------------------------- #

####################################
# java命令路徑
# 注:如果在控制台下可直接執行java命令，則無需設定。
####################################
java_cmd=java

####################################
# 匯入程式jar包地址
# 如果在指令碼所在目錄執行，則無需設定，否則需要設定絕對路徑。
####################################
jar_path=adb-import-tool.jar

####################################
# 設定資料庫串連參數
# 注:確保database已經在ADB中建立
#    如果encryptPassword=true需要填寫base64加密後的密碼。
####################################
host=host
port=3306
user=adbuser
password=pwd
database=dbname
encryptPassword=false

####################################
# 匯入表名
####################################
tableName=please_set_table_name

####################################
# 匯入的檔案或檔案夾的絕對路徑，支援：
# 1）單個檔案或單個檔案夾
# 或者
# 2）同時匯入多個檔案，多個檔案的路徑間用英文逗號（,）分隔。
####################################
dataPath=please_set_data_file_path_or_dir_path

####################################
# 匯入並行度
# 注:越大的並行度越有利於發揮ADB的效能
#    建議值>=16，<=96。
####################################
concurrency=64

####################################
# 匯入寫入VALUES的數量
# 注:越大的批次越有利於發揮ADB的效能
#    但也要結合單行的長度，不宜過大
#    建議值>=1024，<=4096。
####################################
batchSize=4096

####################################
# 匯入檔案編碼，UTF-8或者GBK。
####################################
encoding=UTF-8

####################################
# 行分隔字元
# 支援使用可見符（例如"\\n"）和不可見符作為分隔字元。
# 如需使用不可見符作為分隔字元，需使用16進位來表示。
* 例如\x0d\x06\x08\x0a需使用十六進位表示為"hex0d06080a"。
####################################
lineSeparator="\\n"

####################################
# 資料行分隔符號
# 支援使用可見符（例如"\\|"）或不可見符作為分隔字元。
# 如需使用不可見符作為分隔字元，需使用16進位來表示。
# 例如\x07\x07需使用十六進位表示為"hex0707"。
####################################
delimiter="\\|"

# -------------------------------- #
#           下面是選填參數
# -------------------------------- #

####################################
# jvm參數
####################################
jvmopts="-Xmx12G -Xms12G"

####################################
# 當dataFile是一個檔案夾時，
# 並行讀取檔案的數量。
####################################
maxConcurrentNumOfFilesToImport=64

####################################
# 選填，預設值：false，Null 字元串會變成null；
# 若設定為true,則Null 字元串會變成''。
# 建議設定為預設值false。
####################################
nullAsQuotes=false

####################################
# 每個檔案匯入完畢後是否列印目標表實際行數。
# 選填，預設值：false。
####################################
printRowCount=false

####################################
# SQL執行失敗時候會列印SQL，
# 設定錯誤SQL的列印截斷長度。
# 選填，預設值：1000。
####################################
failureSqlPrintLengthLimit=1000

####################################
# 匯入資料時是否不執行INSERT，僅列印INSERT SQL命令。
# 選填，預設false。
####################################
disableInsertOnlyPrintSql=false

####################################
# 跳過表頭。選填，預設false。
####################################
skipHeader=false

####################################
# INSERT SQL的緩衝數量。
# 便於發送給ADB的時候做到
# IO和計算分離，提高用戶端效能。
####################################
windowSize=128

####################################
# 是否轉義列中的\以及'符號。選填，預設true，表示需要轉義。
# 轉義對於用戶端有一定字串解析造成的效能損失，
# 特殊情況下保證沒有逸出字元的情況下，可以置false。
####################################
escapeSlashAndSingleQuote=true

####################################
# 匯入資料遇到錯誤，是否忽略失敗的批次。
####################################
ignoreErrors=false

####################################
# 匯入資料遇到錯誤，是否列印出錯的SQL。
####################################
printErrorSql=true

####################################
# 當匯入資料遇到錯誤，且printErrorSql=true時，
# 是否列印出錯的棧資訊。
####################################
printErrorStackTrace=true

步驟四：執行匯入

執行如下命令匯入指令碼：
```
sh adb-import-product_info.sh;
```
若列印出如下日誌表示執行正常：
```
[2021-03-13 17:50:24.730] add consumer consumer-01
```
匯入期間，匯入工具不會進行過多的日誌滾動，您可以查詢資料庫擷取匯入進度，例如查詢目標表的總行數，命令如下：
```
mysql > select count(*) from dbname.product_info;
```
說明
資料匯入期間若執行SQL出錯，匯入工具會立即停止匯入，並列印出錯誤SQL的詳細資料。此時匯入的資料是不完整的，可通過執行TRUNCATE TABLE table_name清空表後重新匯入；或者執行DROP TABLE table_name刪除表後再建立表來重新匯入。
匯入結束後會列印每個檔案的讀取行數、耗時，以及總體的耗時，最後會提示是否全部執行成功。如果全部執行成功，則列印all import finished successfully，否則列印all import finished with ERROR!。詳細的匯入行數，請查詢資料庫進行校正。

常見問題

Q：如何查驗用戶端或其所在伺服器負載是否存在瓶頸？

A：若用戶端存在瓶頸，將無法最大化壓測資料庫，此時您可以通過查看以下常用命令來查驗用戶端自身以及所在伺服器負載是否存在瓶頸。

命令	說明
top	查看CPU使用率。
free	查看記憶體佔用。
vmstat 1 1000	查看綜合負載。
dstat -all --disk-util或iostat 1 1000	查看磁碟的讀頻寬和使用率。
jstat -gc <pid> 1000	查看匯入工具Java進程的記憶體回收（Garbage Collection，簡稱GC）詳情，如果GC頻繁，可以嘗試適當擴大JVM參數`jvmopts`中的堆記憶體大小，例如將其擴大到`-Xmx16G -Xms16G`。

Q：如何將匯入指令碼參數化？
A：如果確保匯入檔案的行資料行分隔符號一致，可修改匯入指令碼中的tableName和dataPath參數，通過傳入不同的表名和檔案路徑參數，實現一個指令碼匯入多個表的需求。
樣本如下：
```
tableName=$1
dataPath=$2
```
使用參數化的方式執行匯入。
```
# sh adb-import.sh table_name001 /path/table_001
# sh adb-import.sh table_name002 /path/table_002
# sh adb-import.sh table_name003 /path/table_003
```
Q：如何將匯入程式放在後台運行？
A：您可以執行如下命令在後台運行匯入程式：
```
# nohup sh adb-import.sh &
```
匯入程式在後台開始運行後，您可以執行以下命令查看檢查日誌，如果列印異常資訊棧則說明匯入存在錯誤，需要根據異常資訊進行問題排查。命令如下：
```
# tail -f nohup.out
```
您還可以使用如下命令查看匯入進程是否仍正常執行：
```
# ps -ef|grep import
```
Q：如何忽略匯入程式中的錯誤行？
A：匯入程式中的錯誤行可以分為如下兩類：
- 執行SQL出錯。
  針對此類錯誤，您可以通過設定參數ignoreErrors=true來忽略錯誤行。此時會在執行結果中列印詳細的出錯檔案、起始行號（因設定了batchSize，錯誤行會在起始行號後的batchSize行內）以及執行出錯的SQL。
- 檔案列數不符合預期。
  當檔案列數不符合預期時，系統會立即停止匯入該檔案並列印出錯誤資訊，但由於該錯誤是由於非法檔案導致的，因此並不會被忽略，您需要手動排查檔案的正確性。此類錯誤會列印如下錯誤資訊：
```
[ERROR] 2021-03-22 00:46:40,444 [producer- /test2/data/lineitem.csv.split00.100-41] analyticdb.tool.ImportTool
(ImportTool.java:591) -bad line found and stop import! 16, file = /test2/data/tpch100g/lineitem.csv.split00.100, rowCount = 7, current row = 3|123|179698|145|73200.15|0.06|0.00|R|F|1994-02-02|1994-01-04|1994-02-
23|NONE|AIR|ongside of the furiously brave acco|
```
Q：如何縮小匯入失敗原因的排查範圍？
A：為協助更快的定位匯入失敗原因，您可以從如下幾個方面來縮小失敗原因的排查範圍：
- 當匯入失敗時，AnalyticDB for MySQL匯入工具會列印錯誤記錄檔以及詳細的錯誤原因，預設會截斷SQL語句（最長支援1000個字元），若需要列印更全的SQL資訊，您可以使用如下命令將failureSqlPrintLengthLimit參數擴大至一個合理值（例如1500）：
```
printErrorSql=true
failureSqlPrintLengthLimit=1500;
```
- 由於SQL設定了batchSize，通常是上千行批量執行的SQL，不利於分辨錯誤行，您可以縮小batchSize參數（例如設定為10）以便於定位錯誤的行。參數修改命令如下：
```
batchSize=10;
```
- 如果檔案已進行了切分且已知錯誤的行所在的檔案分區，為了複現問題，可通過修改dataPath參數來匯入存在錯誤行的單個檔案，查看錯誤資訊。語句如下：
```
dataPath=/u01/this/is/the/directory/where/product_info/stores/file007;
```

Q：如何在Windows環境下運行匯入程式？

A：Windows環境暫未提供bat批處理指令碼，您可以直接使用如下方法調用JAR檔案來執行：

usage: java -jar adb-import-tool.jar [-a <arg>] [-b <arg>] [-B <arg>] [-c <arg>] 
      [-D <arg>] [-d <arg>] [-E <arg>] [-f <arg>] [-h <arg>] [-I <arg>] 
      [-k <arg>] [-l <arg>] [-m <arg>] [-n <arg>] [-N <arg>] [-O <arg>]
      [-o <arg>] [-p <arg>] [-P <arg>] [-Q <arg>] [-s <arg>]  [-S <arg>]
      [-t <arg>] [-T <arg>] [-u <arg>] [-w <arg>][-x <arg>] [-y <arg>] [-z <arg>]

參數	是否必填	說明
`-h,--ip <arg>`	必填	AnalyticDB for MySQL叢集的串連地址。
`-u,--username <arg>`		AnalyticDB for MySQL叢集的資料庫帳號。
`-p,--password <arg>`		AnalyticDB for MySQL叢集的資料庫帳號對應的密碼。
`-P,--port <arg>`		AnalyticDB for MySQL叢集使用的連接埠號碼。
`-D,--databaseName <arg>`		AnalyticDB for MySQL叢集的資料庫名稱。
`-f,--dataFile <arg>`		需要匯入的檔案或檔案夾的絕對路徑，支援如下幾種匯入情境：僅匯入單個檔案或單個檔案夾。同時匯入多個檔案，多個檔案的路徑間用英文逗號（,）分隔。
`-t,--tableName <arg>`		需要匯入的表名。
`-a,--createEmptyFinishFilePath <arg>`	選填	匯入完畢後是否產生一個標誌檔案。預設為空白字串，表示不產生。若需要產生標誌檔案，直接輸入檔案名稱即可。例如您可以設定`-a file_a`，即可產生一個名為`file_a`的標誌檔案。
`-b,--batchSize <arg>`		設定`INSERT INTO tablename VALUES (..),(..)`中批量寫入VALUES的數量。預設值：1。說明為更好地實現資料批量寫入效果，建議將該值設定在1024~4096之間。
`-B,--encryptPassword <arg>`		資料庫密碼是否使用密碼編譯演算法加密。預設值：false，表示不使用密碼編譯演算法加密資料庫密碼。
`-c,--printRowCount <arg>`		每個檔案匯入完畢後是否列印目標表實際行數。預設值：false，表示不列印。
`-d,--skipHeader <arg>`		是否跳過表頭。預設值：false，表示不跳過表頭。
`-E,--escapeSlashAndSingleQuote <arg>`		是否轉義列中的`\`以及`'`符號。預設值：true，表示需要轉義。說明轉義對於用戶端字串解析的效能有一定損失，若確保需要匯入的檔案中沒有逸出字元，可以設定該參數為false。
`-I,--ignoreErrors <arg>`		匯入資料遇到錯誤，是否忽略失敗批次。預設值：false，表示不忽略。
`-k,--skipLineNum <arg>`		跳過的行數，類似`IGNORE number {LINES \| ROWS}`參數。預設值：0，表示不跳過。
`-l,--delimiter <arg>`		資料行分隔符號。AnalyticDB for MySQL預設使用可見符`\\\|`作為資料行分隔符號。同時也支援使用不可見符作為分隔字元，如需使用不可見符，需要使用十六進位來表示。例如，`\x07\x07`需使用十六進位表示為`hex0707`。
`-m,--maxConcurrentNumOfFilesToImport <arg>`		當dataFile是一個檔案夾時，並行讀取檔案的數量。預設值：`Integer.MAX_VALUE`，表示讀所有檔案。
`-n,--nullAsQuotes <arg>`		當需要匯入的檔案中存在`\|\|`時，是否需要將其設定為`''`。預設值：false，表示不將`\|\|`設定為`''`，而是設定為null。
`-N,--printErrorSql <arg>`		匯入資料遇到錯誤，是否列印出錯的SQL。預設值：true，表示列印出錯誤的SQL。
`-O,--connectionPoolSize <arg>`		AnalyticDB for MySQL資料庫連接池大小。預設值：2。
`-o,--encoding <arg>`		檔案編碼方式。取值範圍：GBK或UTF-8（預設值）。
`-Q,--disableInsertOnlyPrintSql <arg>`		匯入資料庫時是否不執行INSERT，僅列印INSERT的SQL命令。選填，預設值：false，表示執行INSERT。
`-s,--lineSeparator <arg>`		行分隔字元。AnalyticDB for MySQL預設使用可見符`\\n`作為行分隔字元。同時也支援使用不可見符作為分隔字元，如需使用不可見符，需要使用十六進位來表示。例如，`\x0d\x06\x08\x0a`需使用十六進位表示為`hex0d06080a`。
`-S,--printErrorStackTrace <arg>`		當匯入資料遇到錯誤，且`printErrorSql=true`時，是否列印出錯的棧資訊。預設值：false，表示不列印。
`-w,--windowSize <arg>`		INSERT SQL的緩衝數量。便於將INSERT SQL命令發送至AnalyticDB for MySQL時，實現流水線加速以及IO和計算分離，從而提高用戶端效能。預設值：128。
`-x,--insertWithColumnNames <arg>`		執行`INSERT INTO`命令時是否帶上列名，即是否執行`INSERT INTO tb(column1, column2)`命令進行資料匯入。預設值：true，表示匯入時需要帶上列名。
`-y,--failureSqlPrintLengthLimit <arg>`		當執行INSERT 命令失敗時需要列印錯誤SQL，使用該參數設定錯誤SQL的列印截斷長度。預設值：1000。
`-z,--connectionUrlParam <arg>`		資料庫連接參數。預設值：`?characterEncoding=utf-8`。樣本：`?characterEncoding=utf-8&autoReconnect=true`。

案例1：使用預設參數配置匯入單個檔案，命令如下：

java -Xmx8G -Xms8G -jar adb-import-tool.jar -hyourhost.ads.aliyuncs.com -uadbuser -ppassword -P3306 -Dtest --dataFile /data/lineitem.sample --tableName LINEITEM

案例2：修改相關參數實現最大化吞吐匯入檔案夾下所有檔案，命令如下：

java -Xmx16G -Xms16G -jar adb-import-tool.jar -hyourhost.ads.aliyuncs.com -uadbuser -ppassword -P3306 -Dtest --dataFile /data/tpch100g --tableName LINEITEM --concurrency 64 --batchSize 2048