通過LOAD DATA將本機資料匯入數倉 - AnalyticDB

本文介紹如何通過LOAD DATA將本機資料匯入AnalyticDB for MySQL數倉版。

文法

LOAD DATA LOCAL 
    INFILE 'file_name' 
    [REPLACE | IGNORE] 
    INTO TABLE table_name 
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string'] 
        [[OPTIONALLY] ENCLOSED BY 'char'] 
    ]
    [LINES
        [TERMINATED BY 'string'] 
    ]
    [IGNORE number {LINES | ROWS}] 
    [(column_name_or_user_var
    [, column_name_or_user_var] ...)]

參數

參數	說明
`LOAD DATA LOCAL INFILE`	進行本地檔案匯入操作，支援所有文字文件。說明 3.1.10.2和3.2.0及以上核心版本支援CSV格式檔案。該格式預設按照RFC 4180標準進行分割，只支援指定資料行分隔符號，不支援指定行分隔字元和列結尾符。通過關閉配置項`SET adb_config LOAD_CSV_DATA_WITH_OPENCSV_FORMAT=false`，可以不採用RFC 4180的CSV標準。
`file_name`	本地檔案的路徑，包含檔案地址和檔案名稱。說明如果`file_name`使用相對路徑，則是相對於用戶端程式啟動時的路徑。
`table_name`	AnalyticDB for MySQL中目標表名。
`REPLACE`	匯入資料時，遇到主鍵重複則強制用當前資料覆蓋已有資料。
`IGNORE`	匯入資料時，遇到主鍵重複或者資料問題時則自動忽略失敗行，部分行匯入失敗但不影響其他行匯入。
`[FIELDS] TERMINATED BY 'string'`	定義資料行分隔符號，預設為`\t`，與MySQL一致。
`[FIELDS] ENCLOSED BY 'char'`	定義每列資料的enclosed符號。例如，某一列資料為`"a"`，定義`enclosed by '"'`後，匯入資料時先將`"a"`前後的`"`移除，然後匯入資料。
`[LINES] TERMINATED BY 'string'`	定義行分隔字元，預設為`\n`。
`IGNORE number LINES`	設定匯入資料時忽略開始的某幾行。例如`IGNORE 1 LINES`，匯入資料時忽略第一行資料，即第一行資料不會匯入AnalyticDB for MySQL。
`(column_name_or_user_var,...)`	設定匯入的列，如果不設定，預設按照列的順序來匯入資料。如果匯入列數 > 目標表的列數，則系統自動忽略多餘的列。如果匯入列數 < 目標表的列數，則後續缺少的列將自動填滿預設值。

注意事項

用戶端開啟local-infile。
以mysql-client為例，您需要在my.cnf檔案中增加以下配置開啟local-infile功能。
```
cat ~/.my.cnf
[mysqld]
local-infile
[mysql]
local-infile                    
```
更多my.cnf檔案資訊，請參見MySQL官方文檔。
匯入資料時無法保證操作的原子性。
- 在IGNORE模式下，忽略匯入失敗的資料行。
- 在REPLACE模式下，一旦有資料行匯入失敗，系統將中止後續INSERT操作，因此可能存在部分行資料匯入，部分行資料未匯入的情況。
支援通過SHOW WARNINGS命令，查看失敗行的錯誤資訊。

樣本

將本地檔案out.bak中的資料匯入AnalyticDB for MySQL的test表中。out.bak檔案中共有5000行資料，資料行分隔符號為\t，行分隔字元\n，其中第8行資料存在問題，如下所示。

1 bb
2 bb
3 bb
4 bb
5 bb
6 bb
7 bb
bb   8
9 bb
...

串連AnalyticDB for MySQL叢集，通過CREATE DATABASE和CREATE TABLE，在adb_demo資料庫下建立表test表，從本地檔案匯入的資料將儲存在test表中。
```
CREATE TABLE test (
a int NOT NULL DEFAULT '0', 
b varchar NOT NULL, 
PRIMARY KEY (a) 
) DISTRIBUTED by HASH(a);                
```

在MySQL Client中執行LOAD DATA命令將本地檔案out.bak中的資料匯入AnalyticDB for MySQL的test表中。

在IGNORE模式下執行LOAD DATA命令，部分行匯入失敗但不影響其他行匯入。樣本如下：

LOAD DATA LOCAL INFILE '~/out.bak' IGNORE INTO TABLE test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;

查詢匯入結果：

SELECT COUNT(1) FROM test;

+----------+
| count(1) |
+----------+
|     4999 |
+----------+
1 row in set (0.14 sec)

在REPLACE模式下執行LOAD DATA命令，部分行匯入失敗後立即終止後續匯入操作。樣本如下：

LOAD DATA LOCAL INFILE '~/out.bak' REPLACE INTO TABLE test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;

執行結果如下：

ERROR 1064 (42000): [13000, 2019061210070703000511314203303000266] syntax error :syntax error => IDENTIFIER is not value type pos:34 row: 0 and ceil:0

執行LOAD DATA命令，匯入資料時跳過前10條資料。樣本如下：

LOAD DATA LOCAL INFILE '~/out.bak' REPLACE INTO TABLE test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 10 LINES;

返回結果如下：

Query OK, 4990 rows affected (0.37 sec)