AnalyticDB PostgreSQL版支援COPY和UNLOAD命令,COPY表示從外表匯入資料到本地表,UNLOAD表示從本地表匯出資料到外表。
COPY和UNLOAD都是基於OSS Foreign Table來完成資料匯入匯出的,OSS Foreign Table的詳細內容請參見使用 OSS Foreign Table 訪問 OSS 資料。
COPY
文法
COPY <table_name>
[ <column_list> ]
FROM <data_source>
ACCESS_KEY_ID '<access_key_id>'
SECRET_ACCESS_KEY '<secret_access_key>'
[ [ FORMAT ] [ AS ] <data_format> ]
[ MANIFEST ]
[ option '<value>' [ ... ] ]
參數
參數 | 是否必填 | 說明 |
table_name | 是 | 需要匯入資料的本地表名,必須是已經存在的本地表。 |
column_list | 否 | 需要寫入的目標列列表。若不使用,則預設寫入所有列。 |
data_source | 是 | OSS路徑,如oss://<bucket_name>/path_prefix。 |
access_key_id | 是 | 阿里雲帳號或者具備OSS存取權限的RAM使用者的AccessKey ID。 如何擷取AccessKey ID,請參見建立AccessKey。 |
secret_access_key | 是 | 阿里雲帳號或者具備OSS存取權限的RAM使用者的AccessKey Secret。 如何擷取AccessKey Secret,請參見建立AccessKey。 |
[ FORMAT ] [ AS ] <data_format> | 否 | 若不使用,則預設FORMAT AS CSV。
|
MANIFEST | 否 | 表示data_source是Manifest資訊清單檔。Manifest資訊清單檔必須是JSON格式,由以下元素構成:
|
[ option '<value>' [ ... ] ] | 否 | 選項列表,以 |
選項列表。
選項 | 類型 | 是否必選 | 備忘 |
ENDPOINT | 字串 | 是 | 指定OSS的Endpoint。如何擷取OSS的Endpoint,請參見訪問網域名稱和資料中心。 |
FDW | 字串 | 是 | 指定OSS FDW外掛程式名字。COPY命令在建立臨時Server時需要用到。 |
其他所有建立外表時用到的option,如FORMAT,FILETYPE,DELIMITER,ESCAPE等。 | 不涉及 | 不涉及 | 建立臨時外表時用到的option,詳細內容請參見使用 OSS Foreign Table 訪問 OSS 資料。 |
樣本
樣本1
建立本地表。
CREATE TABLE local_t2 (a int, b float8, c text);
使用COPY命令匯入資料,唯寫入a和c兩列,b列全部寫入NULL。
COPY local_t2 (a, c) FROM 'oss://adbpg-regress/local_t/' ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****' SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****' FORMAT AS CSV ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com' FDW 'oss_fdw';
查詢表中的資料。
SELECT * FROM local_t2 LIMIT 10;
返回資訊如下。
a | b | c ----+---+---------------------------------- 12 | | a24cba6ebdc5e0c485cd88ef60b72fea 15 | | c4d3028f5205fab98e5f43c7945db4ba 20 | | 769884311db01f400e21a903a3f1cb50 26 | | 7d12c981d262e0067ea1a04368f32f2a 30 | | 4e64bda52d54d263d16f42771b1d0225 35 | | b70c976d4c04568bd497b42a7d2e451d 40 | | d07ce2948b8618b47c351b6e222182f6 46 | | c2234393f878f5557776b7e778299564 47 | | cde904b2331fa274cd8d9266aa858342 50 | | 1235b900fb644bb36440a274314e4b6b (10 rows)
可以看到從外表匯入的a和c列,與來源資料表local_t的a和c列資料相同。
SELECT sum(hashtext(t.a::text)) AS col_a_hash, sum(hashtext(t.c::text)) AS col_c_hash FROM local_t2 t;
返回資訊如下。
col_a_hash | col_c_hash -------------+------------- 23725368368 | 13447976580 (1 row)
SELECT sum(hashtext(t.a::text)) AS col_a_hash, sum(hashtext(t.c::text)) AS col_c_hash FROM local_t t;
返回資訊如下。
col_a_hash | col_c_hash -------------+------------- 23725368368 | 13447976580 (1 row)
匯入其他格式樣本。
匯入ORC格式資料:
COPY tt FROM 'oss://adbpg-regress/q_oss_orc_list/' ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****' SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****' FORMAT AS ORC ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com' FDW 'oss_fdw';
匯入PARQUET格式資料:
COPY tp FROM 'oss://adbpg-regress/test_parquet/' ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****' SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****' FORMAT AS PARQUET ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com' FDW 'oss_fdw';
樣本2
建立本地表。
CREATE TABLE local_manifest (a int, c text);
建立Manifest檔案,其中OSS檔案清單可位於不同的Bucket。
{ "entries": [ {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1450404435_seg2_0.csv", "mandatory":true}, {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1856683967_seg1_0.csv", "mandatory":true}, {"url": "oss://adbpg-regress/local_t/_20210114103840_83f407434beccbd4eb2a0ce45ef39568_1880804901_seg0_0.csv", "mandatory":true}, {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1008521914_seg1_0.csv", "mandatory":true}, {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1234881553_seg2_0.csv", "mandatory":true}, {"url": "oss://adbpg-regress-2/local_t/_20210114103849_67100080728ef95228e662bc02cb99d1_1711667760_seg0_0.csv", "mandatory":true} ] }
使用COPY命令從Manifest清單中匯入本地表。
COPY local_manifest FROM 'oss://adbpg-regress-2/unload_manifest/t_manifest' ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****' SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****' FORMAT AS CSV MANIFEST -- 表示從Manifest檔案中匯入。 ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com' FDW 'oss_fdw';
樣本3
COPY匯入OSS資料時,可能會存在異常的資料行(無法正常COPY匯入)。當遇到這種情況時,可以通過額外的option選項設定實現容錯。
log_errors:表示是否記錄錯誤行資訊。
segment_reject_limit:
segment_reject_limit '10'
表示最多容忍10行,大於等於10行時報錯退出;segment_reject_limit '10%'
表示當前的錯誤總行數/當前總共已處理的行 >= 10% 時,報錯退出。
建立本地表。
CREATE TABLE sales(id integer, value float8, x text) DISTRIBUTED BY (id);
使用COPY匯入OSS資料檔案,檔案中有3行資料存在編碼問題。
COPY sales FROM 'oss://adbpg-const/error_sales/' ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****' SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****' FORMAT AS csv log_errors 'true' segment_reject_limit '10' endpoint 'oss-cn-hangzhou-internal.aliyuncs.com' FDW 'oss_fdw'; NOTICE: found 3 data formatting errors (3 or more input rows), rejected related input data COPY FOREIGN TABLE
查看具體的錯誤行資訊。
SELECT * FROM gp_read_error_log('<COPY的目標表名>');
例如,查看sales表的錯誤行資訊。
SELECT * FROM gp_read_error_log('sales');
返回資訊如下。
cmdtime | relname | filename | linenum | bytenum | errmsg | rawdata | rawbytes -------------------------------+------------------------------------------------+-------------------------+---------+---------+-----------------------------------------------------------+---------+---------- 2021-02-08 14:24:04.225238+08 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.2.csv | 2 | | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad | | \x 2021-02-08 14:24:04.225238+08 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.2.csv | 3 | | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad | | \x 2021-02-08 14:24:04.225269+08 | adbpgforeigntabletmp_20210208142403_1936866966 | error_sales/sales.3.csv | 2 | | invalid byte sequence for encoding "UTF8": 0xed 0xab 0xad | | \x (3 rows)
說明追加儲存的錯誤行日誌,需要佔用一定的儲存空間。刪除錯誤行日誌文法為:
SELECT gp_truncate_error_log('<table_name>')
。
UNLOAD
注意事項
當您匯出CSV檔案時,可能出現option因關鍵字衝突而發生語法錯誤,此時需要將option選項用雙引號引用,並寫成小寫字母形式。需要進行特殊處理的options如下:delimiter
、quote
、null
、header
、escape
、encoding
。樣本如下:
UNLOAD ('SELECT * FROM test')
TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT csv
"delimiter" '|'
"quote" '"'
"null" ''
"header" 'true'
"escape" 'E'
"encoding" 'utf-8'
FDW 'oss_fdw'
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com';
文法
UNLOAD ('<select_statement>')
TO <destination_url>
ACCESS_KEY_ID '<access_key_id>'
SECRET_ACCESS_KEY '<secret_access_key>'
[ [ FORMAT ] [ AS ] <data_format> ]
[ MANIFEST [ '<manifest_url>' ] ]
[ PARALLEL [ { ON | TRUE } | { OFF | FALSE } ] ]
[ option '<value>' [ ... ] ]
參數
參數 | 是否必填 | 說明 |
select_statement | 是 | 一個SELECT查詢語句,查詢的結果資料會被寫到OSS。 |
destination_url | 是 | OSS路徑,如oss://<bucket_name>/path_prefix。 |
access_key_id | 是 | 阿里雲帳號或者具備OSS存取權限的RAM使用者的AccessKey ID。 如何擷取AccessKey ID,請參見建立AccessKey。 |
secret_access_key | 是 | 阿里雲帳號或者具備OSS存取權限的RAM使用者的AccessKey Secret。 如何擷取AccessKey ID,請參見建立AccessKey。 |
[ FORMAT ] [ AS ] <data_format> | 否 | 若不使用,則預設FORMAT AS CSV。
|
MANIFEST | 否 | 表示匯出時需要產生匯出資訊清單檔。 說明 如果 |
PARALLEL | 否 | 是否多Segment並行匯出。預設多節點並行匯出,每個節點產生獨立的匯出檔案。值設定為OFF或FALSE時,表示關閉並行,匯出資料大小不超過8 GB時,僅匯出一個檔案。 |
[ option '<value>' [ ... ] ] | 否 | 選項列表,以 |
選項列表。
選項 | 類型 | 是否必選 | 備忘 |
ENDPOINT | 字串 | 是 | 指定OSS的Endpoint。如何擷取OSS的Endpoint,請參見訪問網域名稱和資料中心。 |
FDW | 字串 | 是 | 指定OSS FDW外掛程式名字。COPY命令在建立臨時Server時需要用到。 |
其他所有建立外表時用到的option,如FORMAT,FILETYPE,DELIMITER,ESCAPE等。 | 不涉及 | 不涉及 | 建立臨時外表時用到的option,詳細內容請參見使用 OSS Foreign Table 訪問 OSS 資料。 |
樣本
樣本1
建立本地表並寫入測試資料。
查詢本地表。
SELECT * FROM local_t LIMIT 5;
返回資訊如下。
使用UNLOAD,匯出指定列資料到OSS。
查看OSS對應路徑下已經寫入的CSV檔案。
$ ossutil --config hangzhou-zmf.config ls oss://adbpg-regress/local_t/
返回資訊如下。
查看檔案資料,唯寫出了a和c兩列的資料。
$ head -n 10 adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv
返回資訊如下。
CREATE TABLE local_t (a int, b float8, c text);
INSERT INTO local_t SELECT r, random() * 1000, md5(random()::text) FROM generate_series(1,1000)r;
a | b | c
----+------------------+----------------------------------
5 | 550.81393988803 | 8009fa725372e996786849213a695ce0
6 | 95.8335199393332 | ce7952c6728cdffdee06cc5b502d6457
9 | 421.379795763642 | d3260ccbf6b9c03f3658d96bb7678b4d
10 | 362.347379792482 | 2bbbf89d23a2f83b089b589f55b5c4fc
11 | 800.203878898174 | a52994c5573e6b36d8a1c357bf800ce5
(5 rows)
UNLOAD ('select a, c from local_t') TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS CSV
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE: OSS output prefix: "local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618".
UNLOAD
LastModifiedTime Size(B) StorageClass ETAG ObjectName
2020-09-07 16:48:01 +0800 CST 12023 Standard 9F38B5407142C044C1F3555F00000000 oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg0_0.csv
2020-09-07 16:48:01 +0800 CST 12469 Standard 807BA680A0DED49BC1F3555F00000000 oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg1_0.csv
2020-09-07 16:48:01 +0800 CST 12401 Standard 3524F68F628CEB64C1F3555F00000000 oss://adbpg-regress/local_t/adbpgforeigntabletmp_20200907164801_1354519958_20200907164801_652261618_seg2_0.csv
Object Number is: 3
0.153414(s) elapsed
7,1225341d0d367a69b1b345536b21ef73
19,424a7a5c36066842f4de8c8a8341fc89
27,c214432e9928e4a6f7bef7bd815424c0
29,ade5d636e2b5d2a606a02e79255da4bd
37,85660e60ede47b68493f6295620db568
77,e1be448ba2b08f0a2ca05b7ed812abfd
80,5e85d597a3b0f2f9736a728724a0f9e0
92,dc23f76f0b1446504b8f1c2274521d2f
94,50304822488d55a500e3a71bcf40890f
97,e970fde8cd0df9c6b610925a488f6042
樣本2
使用UNLOAD匯出時自動產生Manifest檔案(Manifest檔案與資料檔案有相同的路徑首碼)。
查看匯出的檔案清單。
ossutil ls -s oss://adbpg-regress/local_t/
除了資料檔案外,還有一個資訊清單檔,返回資訊如下。
查看資訊清單檔內容。
ossutil cat oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_manifest
返回資訊如下。
使用UNLOAD匯出時,產生指定Manifest檔案(Manifest路徑可與資料檔案路徑不同)。
查看匯出的檔案清單。
ossutil ls -s oss://adbpg-regress/local_t/
匯出路徑下只有資料檔案,返回資訊如下。
查看資訊清單檔內容,資訊清單檔位於另一個Bucket下。
ossutil cat oss://adbpg-regress-2/unload_manifest/t_manifest
返回資訊如下。
UNLOAD ('select * from local_t') TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS CSV
MANIFEST -- 表示UNLOAD匯出時產生匯出清單,資訊清單檔與資料檔案有相同的路徑首碼。
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE: OSS output prefix: "local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c".
UNLOAD
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_162488956_seg1_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_163756258_seg0_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_1741120517_seg2_0.csv
oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_manifest
Object Number is: 4
0.136180(s) elapsed
{
"entries": [
{"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_162488956_seg1_0.csv"},
{"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_163756258_seg0_0.csv"},
{"url": "oss://adbpg-regress/local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c_1741120517_seg2_0.csv"}
]
}
ALLOWOVERWRITE為TRUE時,會覆寫已存在的Manifest檔案,但不會覆寫資料檔案,資料檔案由客戶按需自行刪除。
UNLOAD ('select * from local_t') TO 'oss://adbpg-regress/local_t/'
ACCESS_KEY_ID 'LTAI5tDfyHVmxCf66Un****'
SECRET_ACCESS_KEY 'TNPPxOFY4xx7CZGjVgSsVBtIs****'
FORMAT AS CSV
MANIFEST 'oss://adbpg-regress-2/unload_manifest/t_manifest' -- 表示UNLOAD匯出時產生指定路徑的匯出清單。
ALLOWOVERWRITE 'true' -- 覆寫已存在的Manifest檔案。
ENDPOINT 'oss-cn-hangzhou-internal.aliyuncs.com'
FDW 'oss_fdw';
NOTICE: OSS output prefix: "local_t/_20210114100329_3e9b07726306d88b3193dc95c10a5c5c".
UNLOAD
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1736161168_seg0_0.csv
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1925769064_seg2_0.csv
oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_644328153_seg1_0.csv
Object Number is: 3
0.118540(s) elapsed
{
"entries": [
{"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1736161168_seg0_0.csv"},
{"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_1925769064_seg2_0.csv"},
{"url": "oss://adbpg-regress/local_t/_20210114100956_4d3395a9501f6e22da724a2b6df1b6d3_644328153_seg1_0.csv"}
]
}
常見問題
Q:匯出CSV檔案時,為什麼產生了多個CSV檔案?
A:AnalyticDB PostgreSQL版執行個體使用UNLOAD命令匯出資料到OSS時,每個計算節點都會產生一份CSV檔案,檔案數量與計算節點數量有關。例如您的執行個體有4個計算節點,那麼匯出CSV檔案時,也會產生4個CSV檔案。