AnalyticDB：使用OSS Foreign Table進行資料湖分析

使用限制

AnalyticDB for PostgreSQL執行個體和OSS儲存空間Bucket必須位於同一地區。

功能介紹

OSS FDW基於PG FDW（PostgreSQL Foreign Data Wrapper）架構進行開發，通過OSS FDW，您可以進行如下操作。

• 將OSS資料匯入本地表（行存表或列存表）進行分析加速。

• 直接查詢分析OSS的海量資料。

• OSS外表與本地表關聯分析。

OSS FDW支援多種格式的資料檔案，適用不同的業務情境，具體檔案格式如下。

• 支援訪問CSV、TEXT、JSON、JSONLINE格式的非壓縮文字檔。

• 支援訪問CSV、TEXT格式的GZIP壓縮、標準SNAPPY壓縮文字檔。

• 支援訪問JSON、JSONLINE格式的GZIP壓縮文字檔。

• 支援訪問ORC格式的二進位檔案。ORC資料類型與AnalyticDB PostgreSQL版資料類型的映射關係，請參見ORC檔案資料類型對照表。

• 支援訪問PARQUET格式的二進位檔案。PARQUET資料類型與AnalyticDB PostgreSQL版資料類型的映射關係，請參見Parquet檔案資料類型對照表。

• 支援訪問AVRO格式的二進位檔案。AVRO資料類型與AnalyticDB PostgreSQL版資料類型的映射關係，請參見Avro檔案資料類型對照表。

準備工作

準備OSS資料

準備樣本檔案example.csv。

擷取OSS Bucket資訊

以下內容將指導您擷取Bucket 名稱、檔案的路徑、Endpoint（地域節點）和Bucket 域名。

1. 登入OSS管理主控台。

2. 在左側導覽列中，單擊目標Bucket 列表。

3. 在Bucket 列表，單擊目標Bucket。

   在Bucket 列表頁面，您可以擷取Bucket 名稱。

4. 在文件管理頁面，您可以擷取的Bucket中檔案的路徑。

5. 單擊左側概覽。

6. 在概覽頁面的訪問連接埠地區，您可以擷取Endpoint（地域節點）和Bucket 域名。

   建議使用ECS 的 VPC 網絡訪問（內網）的訪問網域名稱進行訪問。

擷取AccessKey ID和AccessKey Secret

擷取AccessKey ID和AccessKey Secret的具體操作，請參見建立AccessKey。

建立OSS Server

使用CREATE SERVER語句建立OSS Server，指定需要訪問的OSS服務端。更多關於CREATE SERVER的介紹，請參見CREATE SERVER。

文法

CREATE SERVER server_name
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

參數選項

OPTIONS參數選項請參見下表。

樣本

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-********.aliyuncs.com',
        bucket 'adb-pg'
  );

您也可以通過ALTER SERVER語句修改OSS Server的配置，使用方法，請參見ALTER SERVER。

修改OSS Server配置樣本如下。

• 修改OSS Server中的某個參數。

  ALTER SERVER oss_serv OPTIONS(SET endpoint 'oss-cn-********.aliyuncs.com');

• 添加OSS Server中的某個參數。

  ALTER SERVER oss_serv OPTIONS(ADD connect_timeout '20');

• 刪除OSS Server中的某個參數。

  ALTER SERVER oss_serv OPTIONS(DROP connect_timeout);

您也可以通過DROP SERVER語句刪除該OSS Server，使用方法，請參見DROP SERVER。

建立OSS User Mapping

建立OSS Server後，您還需要建立一個訪問OSS Server的使用者。您可以使用CREATE USER MAPPING語句建立OSS User Mapping，用於定義AnalyticDB PostgreSQL版資料庫使用者與訪問OSS Server使用者的映射關係。更多介紹，請參見CREATE USER MAPPING。

文法

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
    SERVER <server_name>
    [ OPTIONS ( option 'value' [, ... ] ) ]

參數選項

OPTIONS參數選項請參見下表。

樣本

CREATE USER MAPPING FOR PUBLIC
    SERVER oss_serv
    OPTIONS (
        id 'LTAI****************',
        key 'yourAccessKeySecret'
    );

您也可以通過DROP USER MAPPING語句刪除該使用者，使用方法，請參見DROP USER MAPPING。

建立OSS FDW

擁有OSS Server和訪問OSS Server的使用者後，您可以開始建立OSS FDW。您可以使用CREATE FOREIGN TABLE語句建立OSS FDW，更多資訊，請參見CREATE FOREIGN TABLE。

文法

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
      [, ... ]
] )
    SERVER server_name
  [ OPTIONS ( option 'value' [, ... ] ) ]

參數選項

OPTIONS參數選項請參見下表。

樣本

CREATE FOREIGN TABLE ossexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');

您也可以通過DROP FOREIGN TABLE語句刪除該OSS FDW，使用方法，請參見DROP FOREIGN TABLE。

查詢分析OSS資料

查詢OSS FDW外表的資料與查詢本地表資料方法一樣，常見查詢如下。

• 索引值過濾查詢，樣本如下。

  SELECT * FROM ossexample WHERE volume = 5;

• 彙總查詢，樣本如下。

  SELECT count(*) FROM ossexample WHERE volume = 5;

• 過濾、分組和LIMIT查詢，樣本如下。

  SELECT low, sum(volume)
    FROM ossexample
   GROUP BY low
   ORDER BY low
   limit 5;

OSS FDW外表與本地表關聯分析

1. 建立用於關聯分析的本地表example，並插入測試資料，樣本如下。

   CREATE TABLE example (id int, volume int);
   INSERT INTO example VALUES(1,1), (2,3), (4,5);

2. 本地表example和OSS FDW外表ossexample執行關聯查詢，樣本如下。

   SELECT example.volume, min(high), max(low)
   FROM
   ossexample,
   example
   WHERE ossexample.volume = example.volume
   GROUP BY(example.volume)
   ORDER BY example.volume;

容錯機制

OSS FDW通過log_errors和segment_reject_limit參數提供容錯功能，未經處理資料中的錯誤資料不會導致OSS外表掃描停止。

關於log_errors和segment_reject_limit參數的資訊，請參見建立OSS FDW。

• 建立支援容錯的OSS FDW外表，樣本如下。

  CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text)
      SERVER oss_serv
      OPTIONS (log_errors 'true',         -- 記錄錯誤行資訊
               segment_reject_limit '10', -- 錯誤行數不得超過10行，否則會停止掃描。
               dir 'error_sales/',        -- 指定外表匹配的OSS檔案目錄
               format 'csv',              -- 指定按csv格式解析檔案
               encoding 'utf8');          -- 指定檔案編碼 

• 查看錯誤行的日誌，查看方式如下。

  SELECT * FROM gp_read_error_log('oss_error_sales');

• 刪除錯誤行的日誌，刪除方式如下。

  SELECT gp_truncate_error_log('oss_error_sales');

常見問題

Q：刪除OSS FDW上的資料時，能否同時刪除OSS上的資料。

A：刪除OSS FDW上的資料時，無法刪除OSS上的資料。

相關文檔

• 使用OSS Foreign Table分區表

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

• OSS Foreign Table最佳實務

• OSS Foreign Table資料類型對照表