在Hologres中基于DLF读取OSS数据_实时数仓 Hologres(Hologres)-阿里云帮助中心

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

前提条件

您已开通DLF数据湖构建，详情请参见快速入门。支持开通DLF的地域请参见已开通的地域和访问域名。
（可选）如果您需要使用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格式文件。
只读从实例暂不支持开启数据湖加速能力。
不支持读写使用了OSS加速器的数据湖数据，OSS加速器详情请参见使用OSS加速器。
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 'cn-<region>',
    dlf_endpoint 'dlf-share.cn-<region>.aliyuncs.com',
    oss_endpoint 'oss-cn-<region>-internal.aliyuncs.com'
);

使用DLF自定义数据目录。
创建Server默认连接到DLF的默认数据目录，如您需要访问自定义数据目录，需要在创建Server时指定DLF数据目录参数，示例语法如下。
```
--删除现有 server
drop server <server_name> cascade;

--创建 server
CREATE SERVER IF NOT EXISTS <server_name> FOREIGN data wrapper dlf_fdw options (
    dlf_region 'cn-<region>',
    dlf_endpoint 'dlf-share.cn-<region>.aliyuncs.com',
    oss_endpoint 'oss-cn-<region>-internal.aliyuncs.com',
    [dlf_catalog 'dlf_catalog_name']                     -- 添加自定义Catalog参数
);
```
重要
如果您使用DLF默认数据目录，请勿定义dlf_catalog参数，仅自定义数据目录支持使用dlf_catalog参数进行声明。使用DLF默认数据目录请参见使用DLF默认数据目录和原生OSS存储创建Server。

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

确定OSS-HDFS Bucket域名
通过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 'cn-<region>',
    dlf_endpoint 'dlf-share.cn-<region>.aliyuncs.com',
    oss_endpoint 'oss-cn-<region>.oss-dls.aliyuncs.com' -- OSS-HDFS Bucket 域名
);

参数说明。

参数	说明	示例
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-HDFS服务域名。	OSS `oss-cn-shanghai-internal.aliyuncs.com` OSS-HDFS `cn-hangzhou.oss-dls.aliyuncs.com`
dlf_catalog	在DLF中新建的数据目录，详情请参见新建数据目录。	dlf_catalog

（可选）创建用户映射。

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

参数说明

参数	说明
remote_schema	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';
```

导入数据至Hologres内部表

您可以将外部表数据导入Hologres内部表直接查询，以获得更好的性能体验。

创建Hologres内部表。
在Hologres中创建对应的内表，保持相同的表结构，示例如下：
```
CREATE TABLE IF NOT EXISTS holo_dlf_oss_test(
 uuid int,
 name string,
 price double);
```
同步外部表数据至内部表。
使用Insert into ... select ...命令语句同步外部表数据到内部表中，示例如下：
```
INSERT INTO holo_dlf_oss_test SELECT uuid,name,price FROM dlf_oss_test;
```
查询内部表数据。
使用如下命令查看内部表数据。
```
select * from holo_dlf_oss_test;
```

导出数据至OSS

在数据更新的场景下，需要将数据回写至OSS，之后再由EMR等外部引擎进行处理，此时，您可以直接通过SQL向外部表插入数据。

回写数据至OSS。
使用如下SQL，将Hologres内部表的数据回写入OSS。
```
insert into <foreign_table_name>(<col_name>,......) select <col_name>,...... from <holo_table_name>;
```
参数说明如下：
参数
说明
foreign_table_name
创建的外部表名称。
holo_table_name
导出数据的Hologres内部表名称。
col_name
表中的数据列名称。
查询回写数据。
写入完成后，您可以在EMR（Hive或Spark）中运行如下SQL查询到写入的数据。
```
SELECT * FROM <foreign_table_name> WHERE <col_name> = value;
```
返回成功则表示：Hologres已经成功将数据回写到OSS，并且EMR也可以顺利读取。

常见问题

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

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

OSS数据湖加速