文档

数仓加速查询

更新时间:

本文以GitHub公开事件数据为例,为您介绍使用Hologres加速查询MaxCompute,并对接BI分析工具(本文以DataV为例),实现实时分析离线数据的通用架构与核心步骤。

示例架构

搭建离线数仓时,Hologres可直接读取离线数仓MaxCompute中的数据,并对接BI分析工具将数据实时展示在大屏中,示例架构如图所示。

image..png

其中:

  • 待处理数据:

    本实践使用GitHub公开事件作为示例数据,更多关于数据集的介绍请参见业务与数据认知

  • 离线数仓:

    MaxCompute是大数据计算服务,可以对海量离线数据进行处理并归档,构建离线数据仓库。本实践中使用MaxCompute已通过公共数据集存储的GitHub事件数据。

  • 实时数仓:

    Hologres是兼容PostgreSQL协议的交互式分析引擎,与MaxCompute底层无缝打通,可以加速查询MaxCompute数据,并为下游提供数据服务。

  • 实时大屏:

    本实践以DataV为例,为您展示搭建实时大屏后查看并分析数据的效果。

实践步骤

准备工作

本实践使用已存储在MaxCompute的Github公共事件作为示例数据,因此您无需操作离线数仓的数据集成步骤,可使用Hologres直接读取MaxCompute中的示例数据。因此本实践的操作可直接从读取MaxCompute数据开始。您仅需在实践前准备Hologres和大屏搭建产品环境。

  • 准备Hologres环境

    您需开通Hologres,创建并连接Hologres数据库,后续在Hologres数据库中执行命令读取数据。

    您也可以申请Hologres的免费资源包,免费试用体验本教程的核心步骤。Hologres提供的免费资源包介绍及申请引导请参见新用户免费试用

  • (可选)准备大屏搭建产品:本文以DataV为例

    本文以DataV为例,为您示例搭建大屏后的效果。

读取离线数仓数据(外部表

您可以使用外部表直接读取MaxCompute的数据。核心步骤如下。

  1. 创建外部表。

    -- 新建schema用于创建外部表
    CREATE SCHEMA IF NOT EXISTS hologres_foreign_dataset_github_event;
    
    DROP FOREIGN TABLE IF EXISTS hologres_foreign_dataset_github_event.dwd_github_events_odps;
    
    IMPORT FOREIGN SCHEMA "bigdata_public_dataset#github_events" LIMIT TO
    (
        dwd_github_events_odps
    ) 
    FROM SERVER odps_server INTO hologres_foreign_dataset_github_event OPTIONS(if_table_exist 'error',if_unsupported_type 'error');
  2. 通过外部表读取MaxCompute数据。

    以下以查询昨日起最活跃项目数据为例,为您示例查询代码。

    说明

    通过外部表查询MaxCompute分区表数据有最大扫描分区数限制,最多为512个分区,因此您需要在过滤条件中增加分区键限制,减少分区扫描量。

    SELECT
        repo_name,
        COUNT(*) AS events
    FROM
        hologres_foreign_dataset_github_event.dwd_github_events_odps
    WHERE
        ds >= (CURRENT_DATE - interval '1 day')::text
    GROUP BY
        repo_name
    ORDER BY
        events DESC
    LIMIT 5;

查询数据(内部表)

您可以新建一个内部表,将待分析数据导入至Hologres,核心步骤如下。

  1. 创建内部表。

    -- 新建schema用于创建内表并导入数据
    CREATE SCHEMA IF NOT EXISTS hologres_dataset_github_event;
    
    DROP TABLE IF EXISTS hologres_github_event;
    
    DROP TABLE IF EXISTS hologres_dataset_github_event.hologres_github_event;
    BEGIN;
    CREATE TABLE hologres_dataset_github_event.hologres_github_event (
        id bigint,
        actor_id bigint,
        actor_login text,
        repo_id bigint,
        repo_name text,
        org_id bigint,
        org_login text,
        type text,
        created_at timestamp with time zone NOT NULL,
        action text,
        iss_or_pr_id bigint,
        number bigint,
        comment_id bigint,
        commit_id text,
        member_id bigint,
        rev_or_push_or_rel_id bigint,
        ref text,
        ref_type text,
        state text,
        author_association text,
        language text,
        merged boolean,
        merged_at timestamp with time zone,
        additions bigint,
        deletions bigint,
        changed_files bigint,
        push_size bigint,
        push_distinct_size bigint,
        hr text,
        month text,
        year text,
        ds text
    );
    CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'distribution_key', 'id');
    CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'event_time_column', 'created_at');
    CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'clustering_key', 'created_at');
    
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.id IS '事件ID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.actor_id IS '事件发起人ID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.actor_login IS '事件发起人登录名';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.repo_id IS 'repoID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.repo_name IS 'repo名称';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.org_id IS 'repo所属组织ID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.org_login IS 'repo所属组织名称';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.type IS '事件类型';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.created_at IS '事件发生时间';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.action IS '事件行为';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.iss_or_pr_id IS 'issue/pull_request ID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.number IS 'issue/pull_request 序号';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.comment_id IS 'comment(评论)ID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.commit_id IS '提交记录ID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.member_id IS '成员ID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.rev_or_push_or_rel_id IS 'review/push/release ID';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.ref IS '创建/删除的资源名称';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.ref_type IS '创建/删除的资源类型';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.state IS 'issue/pull_request/pull_request_review的状态';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.author_association IS 'actor与repo之间的关系';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.language IS '编程语言';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.merged IS '是否接受合并';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.merged_at IS '代码合并时间';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.additions IS '代码增加行数';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.deletions IS '代码减少行数';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.changed_files IS 'pull request 改变文件数量';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.push_size IS '提交数量';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.push_distinct_size IS '不同的提交数量';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.hr IS '事件发生所在小时,如00点23分,hr=00';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.month IS '事件发生所在月,如2015年10月,month=2015-10';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.year IS '事件发生所在年,如2015年,year=2015';
    COMMENT ON COLUMN hologres_dataset_github_event.hologres_github_event.ds IS '事件发生所在日,ds=yyyy-mm-dd';
    
    COMMIT;
  2. 导入数据至内部表。

    INSERT INTO hologres_dataset_github_event.hologres_github_event
    SELECT
        *
    FROM
        hologres_foreign_dataset_github_event.dwd_github_events_odps
    WHERE
        ds >= (CURRENT_DATE - interval '1 day')::text;
    
    -- 更新表的统计信息
    ANALYZE hologres_dataset_github_event.hologres_github_event;
  3. 查询内部表数据。

    SELECT
        repo_name,
        COUNT(*) AS events
    FROM
        hologres_dataset_github_event.hologres_github_event
    WHERE
        ds >= (CURRENT_DATE - interval '1 day')::text
    GROUP BY
        repo_name
    ORDER BY
        events DESC
    LIMIT 5;

(可选)通过DataV搭建实时大屏

您可以通过DataV的数据大屏模板,基于Hologres数据源来快速搭建GitHub事件数据实时大屏。

  1. 创建Hologres数据源。

    将数据所在的Hologres实例和数据库创建为DataV的数据源,详情请参见DataV

  2. 创建可视化应用。

    1. 登录DataV控制台

    2. 工作台页面,单击创建PC端看板

      选择使用Hologres实时分析GitHub事件数据模板

    3. 修改模板中相关组件的数据源。

      以左上角的今日公开事件总数为例:

      1. 单击今日公开事件总数对应的数字框,点击右侧数据源,选择数据源类型实时数仓Hologresimage.png

      2. 选择已有数据源为您已创建的数据源。

        如果您在Hologres中的表名和Schema与本实践相同,则不需修改SQL。

      3. 修改完成后,数据响应结果刷新,大屏中成功展示实时数据。

      4. 按照示例更新大屏中的数据源和表名,需更新组件及更新后效果如下图所示。

        image.png

    4. 单击右上角发布,完成大屏搭建。

      您也可以单击预览,预览实时更新的数据大屏。

  • 本页导读 (1)
文档反馈