实时数仓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>';