全部產品
Search
文件中心

AnalyticDB:使用OSS外表高速匯入OSS資料

更新時間:Jun 19, 2024

雲原生資料倉儲AnalyticDB PostgreSQL版支援通過OSS外部表格(gpossext功能),將資料並行從阿里雲Object Storage Service匯入到AnalyticDB PostgreSQL

功能介紹

目前gpossext支援讀寫TEXT、CSV格式的檔案以及GZIP壓縮格式的TEXT、CSV檔案。

gpossext架構圖如下。

OSS

TEXT和CSV格式說明

下列幾個參數可以在外表DDL參數中指定,用於規定讀寫OSS的檔案格式:

  • TEXT和CSV行分割符號是\n,也就是分行符號。

  • DELIMITER用於定義列的分割符:

    • 當使用者資料中包括DELIMITER時,則需要和QUOTE參數一同使用。

    • 推薦的列分割符有,\t|或一些不常見的字元。

  • QUOTE用於包裹有特殊字元的使用者資料(以列為單位):

    • 包含有特殊字元的字串會被QUOTE包裹,用於區分使用者資料和控制字元。

    • 如果不必要,例如整數,基於最佳化效率的考慮,不必使用QUOTE包裹資料。

    • QUOTE不能和DELIMITER相同,預設QUOTE是雙引號。

    • 當使用者資料中包含了QUOTE字元,則需要使用逸出字元ESCAPE加以區分。

  • ESCAPE用於特殊字元轉義:

    • 逸出字元出現在需要轉義的特殊字元前,表示它不是一個特殊字元。

    • ESCAPE預設和QUOTE相同,為雙引號""

    • 也支援設定成\(MySQL預設的逸出字元)或別的字元。

表 1. 典型的TEXT和CSV預設控制字元

控制字元和格式

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在同一地區下。

操作步驟

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

    使用OSS外部表格時,需要在AnalyticDB PostgreSQL中先建立OSS外部表格外掛程式(每個庫中均需要單獨建立)。建立命令如下:

    CREATE EXTENSION IF NOT EXISTS oss_ext;
  2. 將待匯入AnalyticDB PostgreSQL的資料均勻分散儲存在多個OSS檔案中。操作方式,請參見大檔案切分

    說明

    AnalyticDB PostgreSQL的每個資料分區(Segment節點)將按輪詢方式並行對OSS上的資料檔案進行讀取,檔案的數目建議為資料節點數(Segment個數)的整數倍,從而提升讀取效率。

  3. 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,僅是匹配首碼。

    說明

    dirfilepath互斥,三者只能設定其中一個。

    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中的虛擬資料夾路徑。

    說明

    prefixfilepath互斥,三者只能設定其中一個。

    • 檔案夾路徑需要以/結尾,如test/mydir/

    • 在匯入資料時,使用此參數建立外部表格,會匯入指定虛擬目錄下的所有檔案,但不包括它子目錄和子目錄下的檔案。與filepath不同,dir下的檔案沒有命名要求。

    filepath

    OSS中包含路徑的檔案名稱。

    說明
    • prefixdir互斥,三者只能設定其中一個。

    • 這個參數只能在建立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 = falseasync = f

    • 非同步模式和普通模式比,會消耗更多的硬體資源。

  4. 並行匯入資料。

    AnalyticDB PostgreSQL資料庫中執行如下命令,將OSS上的資料並行匯入到AnalyticDB PostgreSQL資料庫。

    INSERT INTO <目標表> SELECT * FROM <外部表格>

操作樣本

本文以目標表example為例,介紹將OSS的資料通過外部表格匯入目標表example。

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

    建立命令如下:

    CREATE EXTENSION IF NOT EXISTS oss_ext;
  2. 建立目標表,用於裝載資料。

    CREATE TABLE example
            (date text, time text, open float,
             high float, low float, volume int)
             DISTRIBUTED BY (date);
  3. 建立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;
  4. 將資料並行從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相關參數處理。

參考文檔