MPP 二、Greenplum數據載入

Loading external data into greenplum database table using different ways...

Greenplum 有常規的COPY載入方法，有基於分布式的並行的gpfdist載入方法；COPY方式適合用於載入小數據；gpfdist適合大數據量載入；下文中將討論這兩種數據載入方式。

gp_sydb=# select current_database,current_user,current_schema,session_user,current_timestamp,version;

current_database | gp_sydb
current_user | gpadmin
current_schema | faa
session_user | gpadmin
now | 2017-06-04 15:33:01.000678+08
version | PostgreSQL 8.3.23 (Greenplum Database 5.0.0-alpha.5 build commit:2e87c5aa435c779b2f3837fa8c7273876497f6ba) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0 compiled on May 19 2017 18:14:12


1 COPY方式載入數據

使用COPY方式載入外部文件，可以指定文件類型、文件格式、日誌信息，greenplum便會自動解析，將數據載入到目標表，這種方式比單純的insert語句效率高，但它不是並行的，適合於載入少量數據。比如有如下包含兩列的csv數據(csv數據和表結構可以在gpdb-sandbox-tutorials上獲取到)；

[gpadmin@gp-master faa]$ more L_AIRLINE_ID.csv
Code,Description
"19031","Mackey International Inc.: MAC"
"19032","Munz Northern Airlines Inc.: XY"
"19033","Cochise Airlines Inc.: COC"
"19034","Golden Gate Airlines Inc.: GSA"
"19035","Aeromech Inc.: RZZ"
"19036","Golden West Airlines Co.: GLW"
"19037","Puerto Rico Intl Airlines: PRN"
"19038","Air America Inc.: STZ"
"19039","Swift Aire Lines Inc.: SWT"
"19040","American Central Airlines: TSF"
"19041","Valdez Airlines: VEZ"
"19042","Southeast Alaska Airlines: WEB"
"19043","Altair Airlines Inc.: AAR"
"19044","Chitina Air Service: CHI"
"19045","Marco Island Airways Inc.: MRC"
"19046","Caribbean Air Services Inc.: OHZ"
"19047","Sundance Airlines: PRO"
"19048","Seair Alaska Airlines Inc.: SAI"


在資料庫中創建相同結構的分布表；

gp_sydb=# d faa.d_airlines
Table "faa.d_airlines"
Column | Type | Modifiers
--------------+---------+-----------
airlineid | integer |
airline_desc | text |
Distributed by: (airlineid)


將外部文件的數據載入到分布表中；

COPY faa.d_airlines FROM "L_AIRLINE_ID.csv" CSV HEADER
LOG ERRORS
SEGMENT REJECT LIMIT 500 ROWS;


LOG ERRORS 表示將有問題無法正常導出的數據記錄到系統表中，數據載入完成後可以通過如下的語句查詢到有問題無法正常導入的數據。

gp_sydb=# SELECT gp_read_error_log("D_AIRLINES");
gp_read_error_log
------------------------------------------------------------------------------------------------------------
("2017-06-04 12:07:13.151372+08",d_airlines,,1517,,"unterminated CSV quoted field","""21395,""Virgin
Blue International Airlines t/a V Australia: VA""

這種方式導入數據和在oracle中通過sqlload，在mysql中通過load導入數據很相似，對於錯誤的監控greenplum也做得比較完善。對於csv文件，greenplum要求首行指定數據列名；其它的格式的文件，我們可能要指定分割符，結束符，這些都是統一的，比如|分隔換行符結束的text文件的導入；

COPY country FROM "/data/gpdb/data01.txt"
WITH DELIMITER "|" LOG ERRORS
SEGMENT REJECT LIMIT 10 ROWS;


2 GPFDIST方式載入數據

gpfdist程序運行在數據存放的節點上，它將數據均勻分布到每個節點上，它是並行工作的，文件可以是按照特定格式存儲後壓縮的gzip文件，也可以是未壓縮的原文件；這種方式適用於大數據載入。假設要載入如下的已經壓縮的csv數據；

[gpadmin@gp-master faa]$ ls -ltr --block-size m otp*.gz
-rwxrwxrwx 1 root root 31M Aug 6 2012 otp200912.gz
-rwxrwxrwx 1 root root 30M Aug 6 2012 otp201001.gz


要將這些數據載入到faa.faa_otp_load表，我們需要先創建gpfdist進程；

[gpadmin@gp-master faa]$ gpfdist -d /mnt/vbox/greenplum-master/test_data/faa -p 8081 > /tmp/gpfdist.log 2>&1 &
[1] 19533


gpfdist類似文件伺服器，需要指定埠，文件目錄信息；gpfdist創建以後需要創建一張external table；

CREATE EXTERNAL TABLE faa.ext_load_otp
(LIKE faa.faa_otp_load)
LOCATION ("gpfdist://192.168.56.10:8081/otp*.gz")
FORMAT "csv" (header)
LOG ERRORS SEGMENT REJECT LIMIT 50000 rows;


因為gpfdist要將數據均勻的分布到每個節點上，所以創建EXTERNAL TABLE時LOCATION中指定的地址要是集群內的節點能夠訪問的地址，如果指定為127.0.0.1僅僅是當前伺服器可以訪問，其它節點訪問不了，系統會報錯拒絕連接。

gp_sydb=# INSERT INTO faa.faa_otp_load SELECT * FROM faa.ext_load_otp;
ERROR: connection with gpfdist failed for gpfdist://localhost:8081/otp*.gz. effective url: http://127.0.0.1:8081/otp*.gz. error code = 111 (Connection refused) (seg2 slice1 192.168.56.12:40002 pid=3546)


從external table載入數據到表中；

gp_sydb=# INSERT INTO faa.faa_otp_load SELECT * FROM faa.ext_load_otp;
NOTICE: Found 26526 data formatting errors (26526 or more input rows). Rejected related input data.
INSERT 0 1024552
gp_sydb=# select count(*) from faa.faa_otp_load;
count
---------
1024552
(1 row)


Greenplum也支持通過external table對外部文件的訪問，但數據不能存放到內存中，每次操作成本很高。

gp_sydb=# select count(*) from faa.ext_load_otp;
NOTICE: Found 26526 data formatting errors (26526 or more input rows). Rejected related input data.
count
---------
1024552
(1 row)


查看載入錯誤的數據(在external table表上查看);

SELECT gp_read_error_log("faa.ext_load_otp");


最後停止gpfdist進程；

[gpadmin@gp-master faa]$ killall gpfdist


3 GPLOAD

gpfdist的操作需要我們一步步的配置和執行，Greenplum提供了一個封裝好的依賴配置的工具GPLOAD；首先我們創建所需的配置文件gpload01.yaml和操作日誌表faa.load_audit；

create table faa.load_audit(tname varchar(100),tnode varchar(300),tdate timestamp);

---
VERSION: 1.0.0.1
DATABASE: gp_sydb # 資料庫名稱
USER: gpadmin # 用戶名
HOST: 192.168.56.10
PORT: 5432
GPLOAD:
INPUT:
- SOURCE:
LOCAL_HOSTNAME:
- 192.168.56.10
PORT: 8081
FILE: # 文件位置
- /mnt/vbox/greenplum-master/test_data/faa/otp*.gz
- FORMAT: csv
- QUOTE: """
- ERROR_LIMIT: 50000
- LOG_ERRORS: true
OUTPUT:
- TABLE: faa.faa_otp_load
- MODE: INSERT
PRELOAD:
- REUSE_TABLES: true
SQL:
- BEFORE: "INSERT INTO faa.load_audit VALUES("faa.faa_otp_load","start", current_timestamp)"
- AFTER: "INSERT INTO faa.load_audit VALUES("faa.faa_otp_load","end", current_timestamp)"


注意檢查埠是否被佔用，文件路徑是否正確；最後執行載入；

[gpadmin@gp-master faa]$ gpload -f gpload01.yaml -l gpload01.log
2017-06-04 14:05:58|INFO|gpload session started 2017-06-04 14:05:58
2017-06-04 14:05:58|INFO|started gpfdist -p 8081 -P 8082 -f "/mnt/vbox/greenplum-master/test_data/faa/otp*.gz" -t 30
2017-06-04 14:05:58|INFO|did not find an external table to reuse. creating ext_gpload_reusable_e0c13d44_48eb_11e7_868b_0800279a5c02
2017-06-04 14:06:32|WARN|2084 bad rows
2017-06-04 14:06:32|WARN|Please use following query to access the detailed error
2017-06-04 14:06:32|WARN|select * from gp_read_error_log("ext_gpload_reusable_e0c13d44_48eb_11e7_868b_0800279a5c02") where cmdtime = "2017-06-04 14:05:58.88747+08"
2017-06-04 14:06:32|INFO|running time: 34.07 seconds
2017-06-04 14:06:32|INFO|rows Inserted = 1024552
2017-06-04 14:06:32|INFO|rows Updated = 0
2017-06-04 14:06:32|INFO|data formatting errors = 2084
2017-06-04 14:06:32|INFO|gpload succeeded with warnings


日誌提示臨時創建的external table，數據插入、更新、錯誤信息，還提示怎麼查看錯誤數據。

gp_sydb=# select count(*) from faa.faa_otp_load;
count
---------
1024552
(1 row)


GPLOAD提供更多的支持和自動化操作，也更方便定製某些特殊的操作，比如監控和統計。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！