全部產品
Search
文件中心

:使用OSS Foreign Table匯入和匯出資料

更新時間:Aug 16, 2024

AnalyticDB PostgreSQL版支援通過OSS Foreign Table(簡稱OSS FDW)匯入OSS資料到本地表和匯出本機資料到OSS。

支援的檔案格式

OSS FDW目前僅支援匯入和匯出如下格式的資料檔案:

  • 支援匯入和匯出CSV、TEXT格式的非壓縮文字檔。

  • 支援匯入和匯出CSV、TEXT格式的GZIP壓縮檔。

  • 支援匯入和匯出ORC格式的二進位檔案。

    關於ORC與AnalyticDB PostgreSQL版的資料類型的區別,請參見ORC檔案資料類型對照表

建立OSS Server、OSS User Mapping和OSS FDW

使用OSS FDW前您需要提前建立OSS Server、OSS User Mapping和OSS FDW。

匯入OSS資料到本地表

  1. 上傳準備工作中的樣本檔案example.csv到OSS,具體操作,請參見控制台上傳檔案

    說明
    • 建議資料檔案的編碼和資料庫編碼保持一致,減少編碼轉換,提高效率。資料庫編碼預設UTF-8。

    • AnalyticDB PostgreSQL版的每個資料分區(Segment)將按輪詢方式並行對OSS上的資料檔案進行讀取。

    • 對於CSV和TEXT檔案,AnalyticDB PostgreSQL版支援多檔案並行讀取,預設並行數為4。檔案數量建議為資料節點數(Segment節點數量 X 單個Segment核心數)的整倍數,從而提升讀取效率。如何拆分源檔案,請參見切分大檔案

  2. 串連AnalyticDB PostgreSQL版資料庫,串連方式,請參見用戶端串連

  3. 建立OSS Server,樣本如下:

    CREATE SERVER oss_serv
        FOREIGN DATA WRAPPER oss_fdw
        OPTIONS (
            endpoint 'oss-cn-********.aliyuncs.com',
            bucket 'adb-pg'
      );
  4. 建立訪問OSS Server的使用者,樣本如下:

    CREATE USER MAPPING FOR PUBLIC
        SERVER oss_serv
        OPTIONS (
            id 'LTAI5t7Ge***************',
            key 'FikziJd2La*******************'
        );
  5. 建立OSS FDW,樣本如下:

    CREATE FOREIGN TABLE ossexample (
        date text,
        time text,
        open float,
        high float,
        low float,
        volume int
    ) SERVER oss_serv OPTIONS (dir 'oss_adb/', format 'csv');
  6. 匯入OSS資料,您可以通過INSERT語句或CREATE TABLE AS語句匯入資料,具體方法如下:

    • INSERT

      1. 使用INSERT語句匯入資料需要在本地建立一張結構一致的表,建表示例如下:

        CREATE TABLE adbexample (
            date text,
            time text,
            open float,
            high float,
            low float,
            volume int
        ) WITH (APPENDONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5);
      2. 執行INSERT語句將外表ossexample的資料匯入到本地表adbexample,樣本如下:

        INSERT INTO adbexample SELECT * FROM ossexample;
    • CREATE TABLE AS

      執行CREATE TABLE AS語句建立一張本地表adbexample並匯入外表ossexample的資料,樣本如下:

      CREATE TABLE adbexample AS SELECT * FROM ossexample DISTRIBUTED BY (volume);

匯出本機資料到OSS

  1. 建立一個OSS FDW,檔案格式為CSV,匯出目錄為tt_csv

    CREATE FOREIGN TABLE foreign_x (i int, j int)
    SERVER oss_serv
    OPTIONS (format 'csv', dir 'tt_csv/');
  2. 使用INSERT INTO語句將本機資料匯出到OSS。

    INSERT INTO foreign_x SELECT * FROM local_x;

匯出檔案命名規則

匯出時,多個計算節點會並行將資料寫出到相同的目錄下,OSS外表匯出檔案名稱格式如下:

{tablename | prefix } _{timestamp}_{random_key}_{seg}{segment_id}_{fileno}.{ext}[.gz]

參數說明如下:

參數

說明

{tablename | prefix }

匯出首碼。指定路徑的方式不同,首碼資訊不同:

  • prefix方式:以prefix指定的資訊為首碼。

  • dir方式:以OSS外表名稱為預設首碼。

{timestamp}

匯出時的時間戳記,格式如YYYYMMDDHH24MISS

{random_key}

隨機索引值。

{seg}{segment_id}

由“seg”和“Segment節點編號”組成。例如“seg1”表明該檔案由Segment 1匯出。

{fileno}

檔案段序號,從0開始。

{ext}

匯出的檔案格式。

  • CSV檔案格式:顯示為.csv

  • TEXT檔案格式:顯示為.txt

  • ORC檔案格式:顯示為.orc

[.gz]

表示匯出檔案為GZIP壓縮檔。

樣本如下:

  • 匯出檔案格式為CSV,並進行GZIP壓縮,使用dir方式指定路徑:

    CREATE FOREIGN TABLE fdw_t_out_1(a int)
    SERVER oss_serv
    OPTIONS (format 'csv', filetype 'gzip', dir 'test/');

    檔案名稱如下:

    fdw_t_out_1_20200805110207_1718599661_seg-1_0.csv.gz
  • 匯出檔案格式為ORC,使用prefix方式指定首碼路徑:

    CREATE FOREIGN TABLE fdw_t_out_2(a int)
    SERVER oss_serv
    OPTIONS (format 'orc', prefix 'test/my_orc_test');

    檔案名稱如下:

    my_orc_test_20200924153043_1737154096_seg0_0.orc

相關文檔