AnalyticDB：使用OSS外表高速匯出資料到OSS

功能介紹

目前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預設的逸出字元）或別的字元。

版本限制

AnalyticDB for PostgreSQL6.0版執行個體。

注意事項

• 建立和使用外部表格的文法，除了location相關參數，其餘參數和Greenplum的使用方式相同。

• 資料匯入匯出的效能和AnalyticDB PostgreSQL的資源（CPU、I/O、記憶體、網路等）有關，也和OSS相關。為了擷取最優的匯入匯出效能，建議在建立表時，使用列式儲存加壓縮功能。例如，指定子句"WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)"，詳細資料，請參見 Greenplum Database建立表官方文檔。

• 為了保證資料匯入匯出的效能，請保證OSS與AnalyticDB PostgreSQL在同一地區下。

操作步驟

1. 建立OSS外部表格外掛程式。

   使用OSS外部表格時，需要在AnalyticDB PostgreSQL中先建立OSS外部表格外掛程式（每個庫中均需要單獨建立）。建立命令如下：

   CREATE EXTENSION IF NOT EXISTS oss_ext;

2. 在AnalyticDB PostgreSQL中，建立WRITABLE外部表格。

   建立OSS外部表格文法如下。

   CREATE WRITABLE EXTERNAL TABLE table_name
   ( column_name data_type [, ...] | LIKE other_table )
   LOCATION ('ossprotocol')
   FORMAT 'TEXT'
                  [( [DELIMITER [AS] 'delimiter']
                  [NULL [AS] 'null string']
                  [ESCAPE [AS] 'escape' | 'OFF'] )]
             | 'CSV'
                  [([QUOTE [AS] 'quote']
                  [DELIMITER [AS] 'delimiter']
                  [NULL [AS] 'null string']
                  [FORCE QUOTE column [, ...]] ]
                  [ESCAPE [AS] 'escape'] )]
   [ ENCODING 'encoding' ]
   [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
   ossprotocol:
      oss://oss_endpoint [prefix=prefix_name|dir=[folder/[folder/]...]/file_name]
       id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

   參數說明如下。

   參數	說明
   WRITABLE	WRITABLE是匯出模式外部表格的關鍵字，建立外部表格時需要明確指明。
   FORMAT	支援檔案格式，例如TEXT、CSV。
   ENCODING	檔案中資料的編碼格式，例如UTF-8。
   DISTRIBUTED BY	DISTRIBUTED BY子句可以讓資料節點（Segment）按指定的分布鍵將資料匯出到OSS。
   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互斥，兩者只能設定其中一個。 WRITABLE外部表格在匯出資料時，會根據該首碼自動產生一個唯一的檔案名稱來給匯出檔案命名。 如果指定prefix=osstest/exp/outfromhdb，則表示的匯出路徑為osstest/exp/，匯出檔案的名稱都以outfromhdb開頭。
   dir	OSS中的虛擬資料夾路徑。 說明 與prefix互斥，兩者只能設定其中一個。 檔案夾路徑需要以/結尾，如test/mydir/。 在匯出資料時，使用此參數建立外部表格，所有資料會匯出到此目錄下的多個檔案中，輸出檔案名的形式為filename.x，x為數字，但可能不是連續的。
   compressiontype	匯出檔案的壓縮格式。 none（預設值）：匯入的檔案未壓縮。 gzip：匯入的檔案壓縮格式為GZIP。 說明 目前僅支援GZIP壓縮格式。
   num_parallel_worker	設定匯出到OSS的壓縮資料的並行壓縮線程個數，預設值為3。取值範圍為1~8。樣本如下： num_parallel_worker=3
   oss_flush_block_size	單次匯出資料到OSS的buffer大小，預設為32 MB。取值範圍為1 MB~128 MB。樣本如下： oss_flush_block_size=32
   oss_file_max_size	設定匯出到OSS的最大檔案大小，超出之後會切換到另一個檔案繼續寫。預設為1024 MB。取值範圍為8 MB~4000 MB。樣本如下： oss_file_max_size=1024
   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 錯誤處理。

3. 並行匯出資料。

   在AnalyticDB PostgreSQL資料庫中執行如下命令，並行將資料匯出到OSS。

   INSERT INTO <外部表格> SELECT * FROM <源表>

操作樣本

本文以源表example為例，介紹將源表example的資料匯出到OSS。

1. 建立OSS外部表格外掛程式。

   建立命令如下：

   CREATE EXTENSION IF NOT EXISTS oss_ext;

2. 建立源表，用於裝載待匯出的資料。

   源表example的建表語句如下：

   CREATE TABLE example
           (date text, time text, open float,
            high float, low float, volume int)
            DISTRIBUTED BY (date);

3. 建立OSS匯出外部表格。

   • 建立外部表格時，使用prefix參數指定匯出路徑。樣本如下：

     CREATE WRITABLE EXTERNAL TABLE ossexample_exp
             (date text, time text, open float, high float,
             low float, volume int)
             location('oss://oss-cn-hangzhou.aliyuncs.com
             prefix=osstest/exp/outfromhdb id=XXX
             key=XXX bucket=testbucket') FORMAT 'csv'
             DISTRIBUTED BY (date);

   • 建立外部表格時，使用dir參數指定匯出路徑。樣本如下：

     CREATE WRITABLE EXTERNAL TABLE ossexample_exp
             (date text, time text, open float, high float,
             low float, volume int)
             location('oss://oss-cn-hangzhou.aliyuncs.com
             dir=osstest/exp/ id=XXX
             key=XXX bucket=testbucket') FORMAT 'csv'
             DISTRIBUTED BY (date);

4. 將資料並行地從example表匯出到OSS。

   INSERT INTO ossexample_exp SELECT * FROM example;

執行如下查詢計劃，可以看到Segment節點直接將本機資料匯出到OSS，沒有進行資料重分布。

EXPLAIN INSERT INTO ossexample_exp SELECT * FROM example;

返回資訊如下：

                          QUERY PLAN
---------------------------------------------------------------
 Insert (slice0; segments: 3)  (rows=1 width=92)
   ->  Seq Scan on example  (cost=0.00..0.00 rows=1 width=92)
(2 rows)

SDK錯誤處理

當匯入或匯出操作出錯時，錯誤記錄檔可能會出現如下資訊：

• code：出錯請求的HTTP狀態代碼。

• error_code：OSS的錯誤碼。

• error_msg：OSS的錯誤資訊。

• req_id：標識該次請求的UUID。當您無法解決問題時，可以憑req_id來請求OSS開發工程師的協助。

具體資訊，請參見OSS API 錯誤響應，逾時相關的錯誤可以使用oss_ext相關參數處理。

參考文檔

• OSS Endpoint 資訊

• OSS協助文檔

• OSS SDK錯誤處理

• OSS API錯誤響應

• Greenplum Database建立外部表格官方文檔

• Greenplum Database建立表官方文檔