雲原生資料倉儲AnalyticDB PostgreSQL版支援通過OSS外部表格(gpossext功能),將資料並行從阿里雲Object Storage Service匯入到AnalyticDB PostgreSQL。
功能介紹
目前gpossext支援讀寫TEXT、CSV格式的檔案以及GZIP壓縮格式的TEXT、CSV檔案。
gpossext架構圖如下。
TEXT和CSV格式說明
下列幾個參數可以在外表DDL參數中指定,用於規定讀寫OSS的檔案格式:
TEXT和CSV行分割符號是
\n
,也就是分行符號。DELIMITER用於定義列的分割符:
當使用者資料中包括DELIMITER時,則需要和QUOTE參數一同使用。
推薦的列分割符有
,
、\t
、|
或一些不常見的字元。
QUOTE用於包裹有特殊字元的使用者資料(以列為單位):
包含有特殊字元的字串會被QUOTE包裹,用於區分使用者資料和控制字元。
如果不必要,例如整數,基於最佳化效率的考慮,不必使用QUOTE包裹資料。
QUOTE不能和DELIMITER相同,預設QUOTE是雙引號。
當使用者資料中包含了QUOTE字元,則需要使用逸出字元ESCAPE加以區分。
ESCAPE用於特殊字元轉義:
逸出字元出現在需要轉義的特殊字元前,表示它不是一個特殊字元。
ESCAPE預設和QUOTE相同,為雙引號
""
。也支援設定成
\
(MySQL預設的逸出字元)或別的字元。
控制字元和格式 | TEXT | CSV |
DELIMITER(列分割符) | \t(Tab) | , (Comma) |
QUOTE(摘引) | " (Double-Quote) | "(Double-Quote) |
ESCAPE(轉義) | (不適用) | 與QUOTE相同 |
NULL(空值) | \N(Backslash-N) | (無引號的Null 字元串) |
所有的控制字元都必須是單位元組字元。
注意事項
建立和使用外部表格的文法,除了location相關參數,其餘參數和Greenplum的使用方式相同。
資料匯入匯出的效能和AnalyticDB PostgreSQL的資源(CPU、I/O、記憶體、網路等)有關,也和OSS相關。為了擷取最優的匯入匯出效能,建議在建立表時,使用列式儲存加壓縮功能。例如,指定子句
"WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)"
,詳細資料,請參見 Greenplum Database建立表官方文檔。為了保證資料匯入匯出的效能,請保證OSS與AnalyticDB PostgreSQL在同一地區下。
操作步驟
建立OSS外部表格外掛程式。
使用OSS外部表格時,需要在AnalyticDB PostgreSQL中先建立OSS外部表格外掛程式(每個庫中均需要單獨建立)。建立命令如下:
CREATE EXTENSION IF NOT EXISTS oss_ext;
將待匯入AnalyticDB PostgreSQL的資料均勻分散儲存在多個OSS檔案中。操作方式,請參見大檔案切分。
說明AnalyticDB PostgreSQL的每個資料分區(Segment節點)將按輪詢方式並行對OSS上的資料檔案進行讀取,檔案的數目建議為資料節點數(Segment個數)的整數倍,從而提升讀取效率。
在AnalyticDB PostgreSQL中,建立READABLE外部表格。
建立OSS外部表格文法如下。
CREATE [READABLE] EXTERNAL TABLE tablename ( columnname datatype [, ...] | LIKE othertable ) LOCATION ('ossprotocol') FORMAT 'TEXT' [( [HEADER] [DELIMITER [AS] 'delimiter' | 'OFF'] [NULL [AS] 'null string'] [ESCAPE [AS] 'escape' | 'OFF'] [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'] [FILL MISSING FIELDS] )] | 'CSV' [( [HEADER] [QUOTE [AS] 'quote'] [DELIMITER [AS] 'delimiter'] [NULL [AS] 'null string'] [FORCE NOT NULL column [, ...]] [ESCAPE [AS] 'escape'] [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF'] [FILL MISSING FIELDS] )] [ ENCODING 'encoding' ] [ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count [ROWS | PERCENT] ] ossprotocol: oss://oss_endpoint [prefix=prefix_name|dir=[folder/[folder/]...]/file_name|filepath=[folder/[folder/]...]/file_name] id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
參數說明如下。
參數
說明
FORMAT
支援檔案格式,例如TEXT、CSV。
ENCODING
檔案中資料的編碼格式,例如UTF8。
LOG ERRORS
指定該子句可以忽略掉匯入中出錯的資料,將這些資料寫入error_table,並可以使用count參數指定報錯的閾值。
說明通過
LOG ERRORS
將錯誤行資訊記錄到內部關聯檔案。LOG ERRORS SEGMENT REJECT LIMIT 5;
通過函數
gp_read_error_log('external_table_name')
可以讀取錯誤行資訊。SELECT * FROM gp_read_error_log('external_table_name');
內部檔案隨外表刪除而刪除,也可以通過函數
gp_truncate_error_log('external_table_name')
刪除。SELECT gp_truncate_error_log('external_table_name');
oss://oss_endpoint
協議和Endpoint,格式為
協議名://oss_endpoint
,其中協議名為oss,oss_endpoint為OSS對應地區的網域名稱。樣本如下:oss://oss-cn-hangzhou.aliyuncs.com
重要如果是從阿里雲的主機訪問資料庫,應該使用內網網域名稱(即帶有
internal
的網域名稱),避免產生公網流量。id
阿里雲帳號的AccessKey ID。擷取AccessKey操作,請參見建立AccessKey。
key
阿里雲帳號的AccessKey Secret。擷取AccessKey操作,請參見建立AccessKey。
bucket
指定資料檔案所在的Bucket,需要在OSS上預先建立。
prefix
指定資料檔案對應路徑名的首碼,不支援Regex,僅是匹配首碼。
說明與dir、filepath互斥,三者只能設定其中一個。
READABLE外部表格會在匯入資料時將含有這一首碼的所有OSS檔案都會被匯入。
如果指定prefix=test/filename,以下檔案都會被匯入:
test/filename
test/filenamexxx
test/filename/aa
test/filenameyyy/aa
test/filenameyyy/bb/aa
如果指定prefix=test/filename/,只有以下檔案會被匯入(上面列的其他檔案不會被匯入):
test/filename/aa
dir
OSS中的虛擬資料夾路徑。
說明與prefix、filepath互斥,三者只能設定其中一個。
檔案夾路徑需要以
/
結尾,如test/mydir/
。在匯入資料時,使用此參數建立外部表格,會匯入指定虛擬目錄下的所有檔案,但不包括它子目錄和子目錄下的檔案。與filepath不同,dir下的檔案沒有命名要求。
filepath
OSS中包含路徑的檔案名稱。
說明與prefix、dir互斥,三者只能設定其中一個。
這個參數只能在建立READABLE外部表格時指定,即僅支援在匯入資料時使用。
compressiontype
匯入檔案的壓縮格式。
none(預設值):匯入的檔案未壓縮。
gzip:匯入的檔案壓縮格式為GZIP。
說明目前僅支援GZIP壓縮格式。
compressionlevel
設定寫入OSS的檔案的壓縮等級,取值範圍為1~9,預設值為6。樣本如下:
compressionlevel=6
oss_connect_timeout
設定連線逾時。單位為秒,預設為10秒。
oss_dns_cache_timeout
設定DNS逾時。單位為秒,預設為60秒。
oss_speed_limit
設定觸發逾時的最小速率。預設為1024位元組,即1 KB。
需要與oss_speed_time參數配合使用。
說明如果使用預設值且連續15秒的傳輸速率小於1 KB,會觸發逾時。具體資訊,請參見OSS SDK 錯誤處理。
oss_speed_time
設定觸發逾時的最長時間。預設為15秒。
需要與oss_speed_limit參數配合使用。
說明如果使用預設值且連續15秒的傳輸速率小於1 KB,會觸發逾時。具體資訊,請參見OSS SDK 錯誤處理。
async
是否啟用非同步模式匯入資料。
開啟輔助線程從OSS匯入資料,加速匯入效能。
預設情況下非同步模式為開啟狀態,如果需要關掉,可以使用參數
async = false
或async = f
。非同步模式和普通模式比,會消耗更多的硬體資源。
並行匯入資料。
在AnalyticDB PostgreSQL資料庫中執行如下命令,將OSS上的資料並行匯入到AnalyticDB PostgreSQL資料庫。
INSERT INTO <目標表> SELECT * FROM <外部表格>
操作樣本
本文以目標表example為例,介紹將OSS的資料通過外部表格匯入目標表example。
建立OSS外部表格外掛程式。
建立命令如下:
CREATE EXTENSION IF NOT EXISTS oss_ext;
建立目標表,用於裝載資料。
CREATE TABLE example (date text, time text, open float, high float, low float, volume int) DISTRIBUTED BY (date);
建立OSS匯入外部表格。
建立外部表格時,使用prefix參數指定待匯入資料所在的路徑。樣本如下:
CREATE READABLE EXTERNAL TABLE ossexample (date text, time text, open float, high float, low float, volume int) location('oss://oss-cn-hangzhou.aliyuncs.com prefix=osstest/example id=XXX key=XXX bucket=testbucket compressiontype=gzip') FORMAT 'csv' (QUOTE '''' DELIMITER E'\t') ENCODING 'utf8' LOG ERRORS SEGMENT REJECT LIMIT 5;
建立外部表格時,使用dir參數指定待匯入資料所在的路徑。樣本如下:
CREATE READABLE EXTERNAL TABLE ossexample (date text, time text, open float, high float, low float, volume int) location('oss://oss-cn-hangzhou.aliyuncs.com dir=osstest/ id=XXX key=XXX bucket=testbucket') FORMAT 'csv' LOG ERRORS SEGMENT REJECT LIMIT 5;
建立外部表格時,使用filepath參數指定待匯入資料所在的路徑。樣本如下:
CREATE READABLE EXTERNAL TABLE ossexample (date text, time text, open float, high float, low float, volume int) location('oss://oss-cn-hangzhou.aliyuncs.com filepath=osstest/example.csv id=XXX key=XXX bucket=testbucket') FORMAT 'csv' LOG ERRORS SEGMENT REJECT LIMIT 5;
將資料並行從ossexample外部表格匯入example表中。
INSERT INTO example SELECT * FROM ossexample;
執行如下查詢計劃,可以看到每個Segment節點都會從OSS並行拉取資料,然後通過執行節點Redistribution Motion將資料HASH計算後分發給對應的Segment節點,接收資料的Segment節點通過INSERT執行節點進行入庫。
EXPLAIN INSERT INTO example SELECT * FROM ossexample;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Insert (slice0; segments: 4) (rows=250000 width=92)
-> Redistribute Motion 4:4 (slice1; segments: 4) (cost=0.00..11000.00 rows=250000 width=92)
Hash Key: ossexample.date
-> External Scan on ossexample (cost=0.00..11000.00 rows=250000 width=92)
(4 rows)
SDK錯誤處理
當匯入或匯出操作出錯時,錯誤記錄檔可能會出現如下資訊:
code:出錯請求的HTTP狀態代碼。
error_code:OSS的錯誤碼。
error_msg:OSS的錯誤資訊。
req_id:標識該次請求的UUID。當您無法解決問題時,可以憑req_id來請求OSS開發工程師的協助。
具體資訊,請參見OSS API 錯誤響應,逾時相關的錯誤可以使用oss_ext相關參數處理。