Hologres：基於Hive Metastore訪問OSS資料湖資料

登入Hologres執行個體，執行以下SQL建立External Database。

建立External Database需要Superuser許可權，文法詳情請參見CREATE EXTERNAL DATABASE。

CREATE EXTERNAL DATABASE <EXTERNAL_DATABASE_NAME>
  owner '<ACCOUNT_NAME>'
  metastore_type 'hms'
  catalog_type 'hive'
  hive_metastore_uris 'thrift://<HIVE_METASTORE_IP>:<PORT>'
  metadata_cache_ttl_sec '600'
  metadata_cache_update_interval_sec '60'
  metadata_refresh_interval_sec '7200'
  oss_endpoint 'oss-<REGION_ID>-internal.aliyuncs.com'
  table_count_limitation_per_schema '2000'
  comment '<EXTERNAL_DATABASE_COMMENT>';

參數說明如下。

（可選）建立使用者映射。

Hologres支援通過CREATE USER MAPPING為指定帳號配置訪問OSS所需的身份憑證，詳情請參見CREATE USER MAPPING。

CREATE USER MAPPING FOR <ACCOUNT_NAME>
EXTERNAL DATABASE <EXTERNAL_DATABASE_NAME>
OPTIONS (
  oss_access_id '<ACCESS_KEY_ID>',
  oss_access_key '<ACCESS_KEY_SECRET>'
);

查詢外部表格。

External Database建立完成後，可使用由External Database、Hive資料庫和Hive表組成的三層名稱查詢外部表格。

• 非分區表

  SELECT *
  FROM <EXTERNAL_DATABASE_NAME>.<HIVE_DATABASE_NAME>.<HIVE_TABLE_NAME>;

• 分區表：在WHERE條件中指定分區鍵。

  SELECT *
  FROM <EXTERNAL_DATABASE_NAME>.<HIVE_DATABASE_NAME>.<HIVE_PARTITION_TABLE_NAME>
  WHERE <PARTITION_KEY> = '<PARTITION_VALUE>';

執行SQL命令，建立EXTENSION。

建立EXTENSION需要Superuser許可權，該操作針對整個DB生效，一個DB只需執行一次。

CREATE EXTENSION IF NOT EXISTS hive_fdw;

基於hive_fdw建立Foreign Server（外部伺服器）並配置Endpoint資訊。

CREATE SERVER IF NOT EXISTS <SERVER_NAME> FOREIGN DATA WRAPPER hive_fdw 
OPTIONS (
   hive_metastore_uris 'thrift://<HIVE_METASTORE_IP>:<PORT>',
   oss_endpoint 'oss-<REGION_ID>-internal.aliyuncs.com | <BUCKET_NAME>.<REGION_ID>.oss-dls.aliyuncs.com' 
);

（可選）建立使用者映射。

Hologres支援通過CREATE USER MAPPING來指定其他使用者身份訪問特定的Foreign Server。例如：Foreign Server的Owner可以通過CREATE USER MAPPING指定RAM使用者（123xxx）來訪問OSS外部資料。CREATE USER MAPPING詳情，請參見postgres create user mapping。

CREATE USER mapping FOR <ACCOUNT_NAME> server <SERVER_NAME> options
(
    dlf_access_id '<ACCESS_KEY_ID>', 
    dlf_access_key '<ACCESS_KEY_SECRET>',
    oss_access_id '<ACCESS_KEY_ID>', 
    oss_access_key '<ACCESS_KEY_SECRET>'
);

樣本如下。

--為目前使用者建立使用者映射
CREATE USER mapping FOR current_user server <SERVER_NAME> options
(
    dlf_access_id '<ACCESS_KEY_ID>', 
    dlf_access_key '<ACCESS_KEY_SECRET>',
    oss_access_id '<ACCESS_KEY_ID>', 
    oss_access_key '<ACCESS_KEY_SECRET>'
);

--為RAM使用者123xxx建立使用者映射
CREATE USER mapping FOR "p4_123xxx" server <SERVER_NAME> options
(
    dlf_access_id '<ACCESS_KEY_ID>', 
    dlf_access_key '<ACCESS_KEY_SECRET>',
    oss_access_id '<ACCESS_KEY_ID>', 
    oss_access_key '<ACCESS_KEY_SECRET>'
);

--刪除使用者映射
Drop USER MAPPING FOR CURRENT_USER server <SERVER_NAME>;
Drop USER MAPPING FOR "p4_123xxx" server <SERVER_NAME>;

建立外部表格。

Hologres支援以下命令建立外部表格：

• CREATE FOREIGN TABLE：一次僅建立一張外部表格，但支援通過指定部分列來自訂建立外部表格，適用於需要建立的外部表格較少且無需映射所有外部表格欄位的情況。

• IMPORT FOREIGN SCHEMA：大量建立外部表格，適用於需要建立多張外部表格或者外部資料源批量映射的情境。

-- CREATE FOREIGN TABLE方式
CREATE FOREIGN TABLE <HOLO_SCHEMA_NAME>.<TABLE_NAME>
(
  { column_name data_type }
  [, ... ]
  ] )
)
SERVER <SERVER_NAME>
OPTIONS
(
  schema_name '<HIVE_DATABASE_NAME>',
  table_name '<HIVE_TABLE_NAME>'
);


-- IMPORT FOREIGN SCHEMA方式
IMPORT FOREIGN SCHEMA <HIVE_DATABASE_NAME> 
[
  { limit TO | EXCEPT } 
  ( table_name [, ...] ) 
]
FROM server <SERVER_NAME>
INTO <HOLO_SCHEMA_NAME> 
options(
  if_table_exist 'update',
  if_unsupported_type 'error'
        );

查詢外部表格。

建立外部表格成功後，可以直接查詢外部表格讀取OSS中的資料。

• 非分區表

  SELECT * FROM <HOLO_SCHEMA_NAME>.<TABLE_NAME>;

• 分區表

  SELECT * FROM <HOLO_SCHEMA_NAME>.<PARTITION_TABLE_NAME> WHERE <PARTITION_KEY> = '<PARTITION_VALUE>';