全部產品
Search
文件中心

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

更新時間:Aug 28, 2024

本文介紹如何使用OSS Foreign Table(OSS外表,簡稱OSS FDW)匯入OSS中的資料並進行資料分析。

功能介紹

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' [, ... ] ) ]

參數選項

參數

類型

是否必填

說明

server_name

字串

OSS Server的名稱。

fdw_name

字串

管理伺服器的外部資料容器的名稱,固定為oss_fdw。

OPTIONS參數選項請參見下表:

參數

類型

是否必填

說明

endpoint

字串

Bucket網域名稱,擷取方法請參見準備工作

bucket

字串

資料檔案所屬的Bucket的名稱,擷取方法請參見準備工作

說明
  • OSS Server和OSS FDW中必須有一個設定了Bucket。關於OSS FDW的Bucket相關資訊,請參見建立OSS FDW

  • 如果OSS Server和OSS FDW都設定了Bucket,則OSS FDW中的Bucket生效。

speed_limit

數值

觸發逾時的資料量。單位為位元組,預設值為1024位元組。

需要與speed_time參數配合使用。

說明

預設情況下,如果連續90秒傳輸的資料量少於1024位元組,則會觸發逾時。具體資訊,請參見OSS SDK 錯誤處理

speed_time

數值

觸發逾時的時間。單位為秒,預設值為90秒。

需要與speed_limit參數配合使用。

說明

預設情況下,如果連續90秒傳輸的資料量少於1024位元組,則會觸發逾時。具體資訊,請參見OSS SDK 錯誤處理

connect_timeout

數值

連線逾時時間。單位為秒,預設值為10秒。

dns_cache_timeout

數值

DNS逾時時間。單位為秒,預設值為60秒。

樣本

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 servername
    [ OPTIONS ( option 'value' [, ... ] ) ]

參數選項

參數

類型

是否必填

說明

username

字串

是,四選一

指定映射的AnalyticDB PostgreSQL版執行個體的使用者名稱。

USER

字串

映射當前的AnalyticDB PostgreSQL版執行個體的使用者名稱。

CURRENT_USER

字串

PUBLIC

字串

匹配所有AnalyticDB PostgreSQL版執行個體的使用者名稱,包括以後建立的使用者。

servername

字串

OSS Server的名稱。

OPTIONS參數選項請參見下表:

參數

類型

是否必填

說明

id

字串

AccessKey ID,擷取方法,請參見建立AccessKey

key

字串

AccessKey Secret,擷取方法,請參見建立AccessKey

樣本

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

您也可以通過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' [, ... ] ) ]

參數選項

參數

類型

是否必填

說明

table_name

字串

OSS FDW名稱。

column_name

字串

列名。

data_type

字串

該列的資料類型。

OPTIONS參數選項請參見下表:

參數

類型

是否必填

說明

filepath

字串

是,三選一

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

如果使用filepath參數,則僅選擇指定檔案。

prefix

字串

指定資料檔案對應路徑名的首碼,不支援Regex,僅是匹配首碼。

如果使用prefix參數,則會選擇含有這一首碼的所有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中的目錄路徑。目錄路徑需要以/結尾,例如test/mydir/

如果使用dir參數,會選擇虛擬目錄下的所有檔案,但不包括它的子目錄和子目錄下的檔案。

bucket

字串

資料檔案所屬的Bucket的名稱,擷取方法請參見準備工作

說明
  • OSS Server和OSS FDW中必須有一個設定了Bucket。

  • 如果OSS Server和OSS FDW都設定了Bucket,則OSS FDW中的Bucket生效。

format

字串

檔案格式,取值範圍如下:

  • csv

  • text

  • orc

  • avro

  • parquet

  • json

    關於JSON的使用規範,請參見JSON規範

  • jsonline

    以分行符號分隔的JSON,所有能被JSONLINE讀取的資料一定可以用JSON讀取,反之則不一定。在可行的情況下,推薦使用JSONLINE。更多JSONLINE規範,請參見JSONLINE規範

filetype

字串

檔案類型,取值範圍如下:

  • plain(預設):按位元組二進位讀取,不做額外處理。

  • gzip:讀取原始位元據並使用GZIP解壓縮。

  • snappy:讀取原始位元據並使用SNAPPY解壓縮。

    僅支援標準格式的SNAPPY壓縮,暫不支援HADOOP-SNAPPY壓縮檔。

說明
  • filetype參數僅支援CSV、TEXT、JSON和JSONLINE格式的檔案。

  • 其中snappy選項不支援JSON和JSONLINE格式的檔案。

log_errors

布爾型

是否將錯誤記錄到記錄檔。預設值為false。更多資訊,請參見容錯機制

說明

該參數僅適用於CSV和TEXT格式的檔案。

segment_reject_limit

數值

異常中止任務(error abort)的數量。

包含%時表示錯誤行百分比,不包含%時表示錯誤行數。例如:

  • segment_reject_limit = '10':表示錯誤的行數超過10行時,任務停止並報錯退出。

  • segment_reject_limit = '10%':表示錯誤的行數超過已處理行數的10%時,任務停止並報錯退出。

說明

該參數僅適用於CSV和TEXT格式的檔案。

header

布爾型

源檔案中欄位名是否包含header行,取值如下:

  • true:包含header行。

  • false(預設):不包含header行。

說明

該參數僅適用於CSV格式的檔案。

delimiter

字串

欄位分隔符號,僅允許設定為單位元組字元。

  • CSV檔案:預設為逗號(,)。

  • TEXT檔案:預設為tab鍵。

說明

該參數僅適用於CSV和TEXT格式的檔案。

quote

字串

欄位引號,僅允許設定為單位元組字元。預設為雙引號(")。

說明

該參數僅適用於CSV格式的檔案。

escape

字串

聲明匹配quote參數的字串,只允許為單位元組的字元。預設為雙引號(")。

說明

該參數僅適用於CSV格式的檔案。

null

字串

指定檔案中的NULL字串。

  • CSV格式:預設為\N(backslash)。

  • TEXT格式:預設為未被引號引用的空白字元。

說明

該參數僅適用於CSV和TEXT格式的檔案。

encoding

字串

指定資料檔案編碼。預設情況下為用戶端編碼。

說明

該參數僅適用於CSV和TEXT格式的檔案。

force_not_null

布爾型

是否聲明欄位的值不匹配Null 字元串,取值如下:

  • true:表示欄位的值不匹配Null 字元串。

  • false(預設):表示欄位的值匹配Null 字元串。

說明

該參數僅適用於CSV和TEXT格式的檔案。

force_null

布爾型

Null 字元串處理方法,取值如下:

  • true:Null 字元串的欄位的值無論是否添加了引號均作為NULL返回。

  • false(預設):Null 字元串的欄位的值只有未加引號的情況下作為NULL返回。

說明

該參數僅適用於CSV和TEXT格式的檔案。

樣本

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');
說明

建立OSS FDW完成後,您可以通過如下方式查看OSS FDW匹配的OSS檔案清單是否符合預期:

  • 方法一:

    EXPLAIN VERBOSE SELECT * FROM <OSS FDW表名>;
  • 方法二:

    SELECT * FROM get_oss_table_meta('<OSS FDW表名>');

您也可以通過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_errorssegment_reject_limit參數提供容錯功能,未經處理資料中的錯誤資料不會導致OSS外表掃描停止。

關於log_errorssegment_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上的資料。

相關文檔