在Hologres中基于DLF读取OSS数据 - 实时数仓Hologres

基于阿里云数据湖构建（Data Lake Formation，DLF）和对象存储（Object Storage Service，OSS）推出的Hologres数据湖加速服务，提供了灵活的数据访问和分析能力以及高效的数据处理能力，显著加快了对OSS数据湖的查询和分析过程。本文将为您介绍在Hologres中如何通过DLF读写OSS数据。

前提条件

您已开通DLF数据湖构建，详情请参见快速入门。支持开通DLF的地域请参见已开通的地域和访问域名。
您已开通OSS并准备好数据，详情请参见开通OSS服务。
您已进行OSS授权操作，通过外部表方式访问OSS数据，需要访问的账号有OSS的相关访问权限，否则即使创建外表成功了，也无法查询数据，OSS授权请参见Bucket Policy。
（可选）如果您需要使用OSS-HDFS功能，请开通OSS-HDFS服务，详情请参见开通OSS-HDFS服务。

注意事项

Hologres数据导出至OSS时仅支持执行INSERT INTO命令，不支持执行INSERT ON CONFLICT、UPDATE和DELETE命令。
仅Hologres V1.3及以上版本支持回写数据至OSS，且仅支持ORC、Parquet、CSV、SequenceFile格式文件。
Hologres暂不支持从引擎侧自动创建OSS外部表，如需使用INSERT INTO命令回写数据至OSS，请在DLF中提前创建好对应的外部表，如需导出数据至分区表，请提前创建好对应的分区。
只读从实例暂不支持开启数据湖加速能力。
IMPORT FOREIGN SCHEMA语句支持导入存储于OSS上的分区表，当前Hologres最多支持查询512个分区，请添加分区过滤条件，使得一次查询不超过512个分区。
湖数据查询的原理是在运行时将外表数据的特定查询分区加载到Hologres的内存和缓存中完成计算，为不影响查询体验，Hologres一次查询支持的数据量不超过200GB（经分区过滤后命中的数据量）。
不支持对外表执行UPDATE、DELETE及TRUNCATE等命令。

使用说明

环境配置

在Hologres实例中开启DLF_FDW后台配置。
前往Hologres管控台实例列表或实例详情页，单击目标实例操作列的数据湖加速并确认，后台将自动配置DLF_FDW并重启实例，重启完成即可使用该服务。
说明
Hologres控制台自助开启DLF_FDW后台配置功能陆续开放中，如果您暂时看不到数据湖加速按钮，请您使用自助升级或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。
开启DLF_FDW后默认使用当前系统资源（目前规格是1Core 4GB），无需额外购买资源。
创建Extension。
在Hologres中由Superuser在DB中执行以下语句创建Extension，用于开启通过DLF读取OSS数据的功能。该操作针对整个DB生效，一个DB只需执行一次。
```
CREATE EXTENSION IF NOT EXISTS dlf_fdw;
```

创建外部服务器。

Hologres支持DLF数据目录（Multi-Catalog）功能，如果您只有一个EMR集群，则使用DLF默认数据目录（DLF Default Catalog）即可，如果您有多个EMR集群，可以使用自定义数据目录来控制Hologres实例链接到不同的EMR集群。同时，您也可以选择原生OSS或者OSS-HDFS作为数据来源，具体配置如下。

使用DLF默认数据目录和原生OSS存储创建Server，示例语法如下。

--查看现有server（其中meta_warehouse_server，odps_server是系统内置server，不可以修改和删除）
SELECT * FROM pg_foreign_server;

--删除现有server
DROP SERVER <server_name> CASCADE;

--创建server
CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    dlf_region '<region id>',
    dlf_endpoint 'dlf-share.<region id>.aliyuncs.com',
    oss_endpoint 'oss-<region id>-internal.aliyuncs.com'
);

使用OSS-HDFS作为数据湖存储。

确定OSS-HDFS Endpoint（地域节点）
通过DLF_FDW访问存储在OSS-HDFS上数据，需要配置OSS-HDFS的服务域名；域名地址可以在OSS控制台中，已开通OSS-HDFS服务的Bucket概览页获取。

创建外部Server并配置Endpoint信息

确认Bucket域名后即可在Hologres中配置DLF_FDW OSS_Endpoint选项，示例语法如下。

CREATE EXTENSION IF NOT EXISTS dlf_fdw;

CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    dlf_region '<region id>',
    dlf_endpoint 'dlf-share.<region id>.aliyuncs.com',
    oss_endpoint '<bucket_name>.<region id>.oss-dls.aliyuncs.com' -- OSS-HDFS Bucket Endpoint 域名节点
);

参数说明。

参数	说明	示例
server_name	自定义的server名称。	dlf_server
dlf_region	连接DLF所在的地域，请您根据地域进行选择。华北2（北京）：`cn-beijing`。华东1（杭州）：`cn-hangzhou`。华东2（上海）：`cn-shangha`i。华南1（深圳）：`cn-shenzhen`。华北3（张家口）：`cn-zhangjiakou`。新加坡：`ap-southeast-1`。德国（法兰克福）：`eu-central-1`。美国（弗吉尼亚）：`us-east-1`。印度尼西亚（雅加达）：`ap-southeast-5`。	cn-hangzhou
dlf_endpoint	推荐使用DLF的对内服务Endpoint，可以获得更好的访问性能。华北2（北京）：`dlf-share.cn-beijing.aliyuncs.com`。华东1（杭州）：`dlf-share.cn-hangzhou.aliyuncs.com` 华东2（上海）：`dlf-share.cn-shanghai.aliyuncs.com`。华南1（深圳）：`dlf-share.cn-shenzhen.aliyuncs.com`。华北3（张家口）：`dlf-share.cn-zhangjiakou.aliyuncs.com`。新加坡：`dlf-share.ap-southeast-1.aliyuncs.com`。德国（法兰克福）：`dlf-share.eu-central-1.aliyuncs.com`。美国（弗吉尼亚）：`dlf-share.us-east-1.aliyuncs.com`。印度尼西亚（雅加达）：`dlf-share.ap-southeast-5.aliyuncs.com`。	`dlf-share.cn-shanghai.aliyuncs.com`
oss_endpoint	原生OSS存储推荐使用OSS的内网Endpoint,以获得更好的访问性能。 OSS-HDFS目前仅支持内网访问。	OSS `oss-cn-shanghai-internal.aliyuncs.com` OSS-HDFS `cn-hangzhou.oss-dls.aliyuncs.com`

（可选）创建用户映射。

Hologres支持通过CREATE USER MAPPING命令指定其他用户身份来访问DLF和OSS，如：foreign server的Owner可以通过CREATE USER MAPPING指定RAM用户123xxx来访问OSS外部数据。

查询时请确保该账号有对应外部数据的查询权限。详细原理请参见postgres create user mapping。

CREATE USER MAPPING FOR <账号uid> SERVER <server_name> OPTIONS 
(
    dlf_access_id 'accessid', 
    dlf_access_key 'accesskey',
    oss_access_id 'accessid', 
    oss_access_key 'accesskey'
);

使用示例：

--为当前用户创建用户映射
CREATE USER MAPPING FOR current_user SERVER <server_name> OPTIONS
(
    dlf_access_id 'LTAI5txxx', 
    dlf_access_key 'y8LUUyyy',
    oss_access_id 'LTAI5txxx', 
    oss_access_key 'y8LUUyyy'
);

-- 为RAM用户123xxx创建用户映射
CREATE USER MAPPING FOR "p4_123xxx" SERVER <server_name> OPTIONS
(
    dlf_access_id 'LIlY5txxx', 
    dlf_access_key 'KsjkXKyyy',
    oss_access_id 'LIlY5txxx', 
    oss_access_key 'KsjkXKyyy'
);

--删除用户映射
DROP USER MAPPING FOR current_user SERVER <server_name>;
DROP USER MAPPING FOR "p4_123xxx" SERVER <server_name>;

读取OSS湖数据

以DLF数据源为例，您需要在DLF中准备元数据表，并保证该表中已抽取数据。在Hologres中以外部表方式通过DLF访问OSS的数据操作步骤如下：

在Hologres实例中创建外部表。

Server创建完成后，您可以在Hologres中使用CREATE FOREIGN TABLE创建外部表或IMPORT FOREIGN SCHEMA语法单独或者批量创建外部表，用于读取DLF抽取的OSS数据。

说明

如果OSS外部表存在和Holo内表同名的表，IMPORT FOREIGN SCHEMA会跳过该外部表的创建，请使用CREATE FOREIGN TABLE来定义一个非重复表名来创建。

Hologres支持读取OSS中的分区表，支持作为分区键的数据类型为TEXT、VARCHAR和INT。CREATE FOREIGN TABLE方式因只做字段映射，不实际存储数据，将分区字段作为普通字段来创建即可；IMPORT FOREIGN SCHEMA方式无需关心表字段，会自动处理表字段映射。

语法示例

-- 方式一
CREATE FOREIGN TABLE [ IF NOT EXISTS ] oss_table_name ( [
{ column_name data_type }
  [, ... ]
  ] )
SERVER <server_name>
OPTIONS
(
  schema_name '<dlf_database_name>',
  table_name '<dlf_table_name>'
);

-- 方式二
IMPORT FOREIGN SCHEMA schema_name
    [ { limit to | except } ( table_name [, ...] ) ]
    from server <server_name>
    into local_schema
    [ options ( option 'value' [, ... ] ) ]

参数说明

参数	说明
schema_name	DLF中创建的元数据库名。
table_name	DLF中创建的元数据表名。
server_name	Hologres中创建的Server名。
local_schema	Hologres中的Schema名。
options	IMPORT FOREIGN SCHEMA中的option参数取值，详情请参见IMPORT FOREIGN SCHEMA。

使用示例。

单独创建。

创建一张外部表映射DLF元数据库dlfpro中元数据表dlf_oss_test的数据，该表位于Hologres中的public schema，并且检验是否存在该外部表，若存在，则对已有表更新。

-- 方式一
CREATE FOREIGN TABLE dlf_oss_test_ext
(
  id text,
  pt text
)
SERVER <server_name>
OPTIONS
(
  schema_name 'dlfpro',
  table_name 'dlf_oss_test'
);

-- 方式二
IMPORT FOREIGN SCHEMA dlfpro LIMIT TO
(
  dlf_oss_test
)
FROM SERVER <server_name> INTO public options (if_table_exist 'update');

批量创建。

将DLF元数据库dlfpro中所有的表都映射至Hologres的public Schema，将会在Hologres中批量创建同名外部表。

整库导入。

IMPORT FOREIGN SCHEMA dlfpro
FROM SERVER <server_name> INTO public options (if_table_exist 'update');

多表导入。

IMPORT FOREIGN SCHEMA dlfpro
(
  table1,
  table2,
  tablen
)
FROM SERVER <server_name> INTO public options (if_table_exist 'update');

数据查询。
创建外部表成功后，可以直接查询外部表读取OSS中的数据。
- 非分区表
```
SELECT * FROM dlf_oss_test;
```
- 分区表
```
SELECT * FROM partition_table where dt = '2013';
```

下一步

若您需要将OSS数据导入Hologres内部表直接查询，以获得更好的性能体验，请参见使用SQL从数据湖导入。
若您要将Hologres内部表数据回写至OSS数据湖，并使用外部引擎查询，请参见导出至数据湖。

常见问题

创建DLF外部表时，提示报错ERROR: babysitter not ready,req:name:"HiveAccess"。

问题原因。
未添加后台配置。
解决方法。
请您在管理控制台的实例列表页单击数据湖加速以开启后台配置。

示例教程

数据湖加速查询