5 分钟使用 Hologres 实时湖仓加速分析挑战赛来袭

云布道师

活动简介：5 分钟快速使用 Hologres 实时湖仓能力，加速分析数据湖 OSS 上 Hudi、Delta、Paimon、ORC 等格式数据，赢取精美礼品

活动入口：
https://developer.aliyun.com/topic/hologres/dlf
或点击文末【阅读全文】参与挑战
活动时间
2024 年 1 月 4 日-2024 年 1 月 30
活动奖品
●挑战奖：小米充电宝 15 个，非教程代码执行 SQL，完成运行速度对比
●一等奖：LAMY 钢笔 1 个，作品点赞数位列第 1 名，且点赞数≥20 个
●二等奖：小米背包 20 个，作品点赞数位列第 2-21 名，且点赞数≥10 个
●参与奖：社区积分，每位参赛者可获得社区 100 积分

准备工作

本文以上海地域为例开通 OSS、DLF 和 Hologres 服务。

1. 开通 OSS 服务并准备测试数据。
   a. 打开 OSS 开通页面，按照界面指引完成开通操作。说明
   ■若您是新用户，可以免费试用 OSS。
   b. 登录 OSS 管理控制台，创建存储空间（Bucket）。具体操作，请参见控制台快速入门。
   
   a.上传 tpch_10g_orc_3.zip 测试数据至 Bucket 目录。
   ●测试数据文件上传后，若存在.DS_Store等文件需手动删除。
   ●考虑到下载速度，这里仅包含本文需要的nation_orc、supplier_orc、partsupp_orc 数据表。
   
2. 通 DLF 服务并导入 OSS 测试数据。
   a. 访问开通 DLF 页面，您也可以单击免费开通，免费试用 DLF 产品。
   b. 登录数据湖管理控制台，在元数据管理页面，单击新建数据库。具体操作，请参见创建元数据库。本文以创建 mydatabase 数据库为例。
   c. 在元数据抽取页面，创建元数据抽取任务，将 OSS 测试数据导入。具体操作，请参见元数据抽取。抽取完成后，您可以在元数据管理页面的数据表页签查看。
   
3. 开通 Hologres 服务并购买 Hologres 实例。
   具体操作，请参见购买 Hologres。若您是新用户可以申请免费试用 Hologres，为保证使用体验，免费试用请选择 32C配置，若您没有免费试用规格，可以购买 Hologres 按量付费
   

步骤一：配置环境

1. 在 Hologres 实例中开启数据湖加速功能。
   访问 Hologres 实例列表，单击目标实例操作列中的数据湖加速并确认，开启数据湖加速功能后，Hologres 实例将重启。
   
2. 登录 Hologres 实例，创建数据库。具体操作，请参见连接 HoloWeb。
   
   
   3（可选）创建 Extension。本文以 dlf_fdw 为例。
   说明
   Hologres V2.1 版本已默认创建，您无需进行此操作。您可以访问 Hologres 实例列表，在实例详情页面确认您的实例版本。
   CREATE EXTENSION IF NOT EXISTS dlf_fdw;
   说明
   使用 Superuser 在 SQL 编辑器-HoloWeb 中执行上述语句创建 Extension，该操作针对整个 DB 生效，一个 DB 只需执行一次。关于 Hologres 账号授权详情，请参见授权服务账号。
3. 在 SQL 编辑器-HoloWeb，执行以下语句，创建 dlf_server 外部服务器并配置Endpoint 信息，确保 Hologres、DLF 和 OSS 之间的正常访问。关于更多的创建方式和相关参数介绍详情，请参见创建外部服务器。

--创建外部服务器，以上海reigon为例
CREATE SERVER IF NOT EXISTS dlf_server FOREIGN data wrapper dlf_fdw options (
    dlf_region 'cn-shanghai',
    dlf_endpoint 'dlf-share.cn-shanghai.aliyuncs.com',
    oss_endpoint 'oss-cn-shanghai-internal.aliyuncs.com');
1
2
3
4
5

步骤二：通过 Hologres 外部表查询 OSS 数据湖

Hologres 外部表保存与 OSS 数据湖数据的映射关系，数据在 OSS 数据湖中存储，不占用 Hologres 存储空间，查询性能一般在秒级至分钟级。

1. 创建 Hologres 外部表，并将 OSS 数据湖数据映射至 Hologres 外部表。

IMPORT FOREIGN SCHEMA mydatabase LIMIT TO ----本文以mydatabase为例，创建时需替换为您在DLF元数据管理中的自定义的数据库名称
(
  nation_orc,
  supplier_orc,
  partsupp_orc
)
FROM SERVER dlf_server INTO public options (if_table_exist 'update');
1
2
3
4
5
6
7

1. 数据查询。创建外部表成功后，可以直接查询外部表读取 OSS 中的数据。示例语句如下。

--TPCH Q11查询语句
select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
from
        partsupp_orc,
        supplier_orc,
        nation_orc
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and RTRIM(n_name) = 'EGYPT'
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * 0.000001
                        from
                                partsupp_orc,
                                supplier_orc,
                                nation_orc
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and RTRIM(n_name) = 'EGYPT'
                )
order by
        value desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

步骤三：通过 Hologres 内部表查询 OSS 数据湖

Hologres 内部表查询是将 OSS 数据湖数据导入至 Hologres 中，数据将在Hologres 中存储，可获得更好的查询性能和更高的数据处理能力。关于存储费用详情介绍，请参见计费概述。

1. 在 Hologres 中创建与外部表相同表结构的内部表，示例如下。

-- 创建nation表
DROP TABLE IF EXISTS NATION;
BEGIN;
CREATE TABLE NATION (
    N_NATIONKEY int NOT NULL PRIMARY KEY,
    N_NAME text NOT NULL,
    N_REGIONKEY int NOT NULL,
    N_COMMENT text NOT NULL
);
CALL set_table_property ('NATION', 'distribution_key', 'N_NATIONKEY');
CALL set_table_property ('NATION', 'bitmap_columns', '');
CALL set_table_property ('NATION', 'dictionary_encoding_columns', '');
COMMIT;
-- 创建supplier表
DROP TABLE IF EXISTS SUPPLIER;
BEGIN;
CREATE TABLE SUPPLIER (
    S_SUPPKEY int NOT NULL PRIMARY KEY,
    S_NAME text NOT NULL,
    S_ADDRESS text NOT NULL,
    S_NATIONKEY int NOT NULL,
    S_PHONE text NOT NULL,
    S_ACCTBAL DECIMAL(15, 2) NOT NULL,
    S_COMMENT text NOT NULL
);
CALL set_table_property ('SUPPLIER', 'distribution_key', 'S_SUPPKEY');
CALL set_table_property ('SUPPLIER', 'bitmap_columns', 'S_NATIONKEY');
CALL set_table_property ('SUPPLIER', 'dictionary_encoding_columns', '');
COMMIT;
-- 创建partsupp表
DROP TABLE IF EXISTS PARTSUPP;
BEGIN;
CREATE TABLE PARTSUPP (
    PS_PARTKEY int NOT NULL,
    PS_SUPPKEY int NOT NULL,
    PS_AVAILQTY int NOT NULL,
    PS_SUPPLYCOST DECIMAL(15, 2) NOT NULL,
    PS_COMMENT text NOT NULL,
    PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY)
);
CALL set_table_property ('PARTSUPP', 'distribution_key', 'PS_PARTKEY');
CALL set_table_property ('PARTSUPP', 'bitmap_columns', 'ps_availqty');
CALL set_table_property ('PARTSUPP', 'dictionary_encoding_columns', '');
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

1. 同步 Hologres 外部表数据至 Hologres 内部表。

---将Hologres外表数据导入内表
INSERT INTO nation SELECT * FROM nation_orc;
INSERT INTO supplier SELECT * FROM supplier_orc;
INSERT INTO partsupp SELECT * FROM partsupp_orc;
1
2
3
4

1. 查询 Hologres 内部表数据。

--TPCH Q11查询语句
select
        ps_partkey,
        sum(ps_supplycost * ps_availqty) as value
from
        partsupp,
        supplier,
        nation
where
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and RTRIM(n_name) = 'EGYPT'
group by
        ps_partkey having
                sum(ps_supplycost * ps_availqty) > (
                        select
                                sum(ps_supplycost * ps_availqty) * 0.000001
                        from
                                partsupp,
                                supplier,
                                nation
                        where
                                ps_suppkey = s_suppkey
                                and s_nationkey = n_nationkey
                                and RTRIM(n_name) = 'EGYPT'
                )
order by
        value desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

晒出结果

一等奖、二等奖、参与奖：
将内表查询或外表查询的运行日志截图晒出。
外表查询速度：

内表查询速度：

分享作品参与点赞排行
●请使用提交作品账号开通产品并参与挑战，后台会校验产品开通及SQL运行情况。
●请上传原创数据表及截图，若为抄袭则取消资格；若发现有刷赞行为，立即撤销作品，取消参赛资格。
挑战奖：
基于已有的外表和内表，分别运行2条同样的自定义SQL，附上外表SQL语句、运行日志、运行结果、内表SQL语句、运行日志、运行结果，6个部分合并1张截图上传。
●SQL需要涉及多表查询，单表SQL不支持参与挑战
●获奖名单按照作品提交时间排序
●为保证SQL原创性，同样SQL只取第一位
●挑战奖优先，不与一等奖、二等奖重复获取
挑战奖作品示例：