如何使用DataWorks实时同步公共数据至Hologres并进行实时数据分析_大数据开发治理平台 DataWorks-阿里云帮助中心

本文以使用DataWorks实时同步公共数据至Hologres，并通过Hologres进行实时数据分析为例，为您示例DataWorks的数据同步能力与Hologres的实时分析能力。本教程以申请免费资源为例为您示例详细操作步骤，您也可以使用付费资源，操作类似。

教程简介

本教程基于GitHub Archive公开数据集，通过DataWorks将GitHub中的项目、行为等20多种事件类型数据实时采集至Hologres进行分析，同时使用DataV内置模板，快速搭建实时可视化数据大屏，从开发者、项目、编程语言等多个维度了解GitHub实时数据变化情况。

我能学到什么

学会通过DataWorks实时同步数据。
熟悉使用DataV大屏进行可视化操作。

操作难度	易
所需时间	55分钟
使用的阿里云产品	实时数仓Hologres 云原生大数据计算服务 MaxCompute 专有网络VPC 大数据开发治理平台 DataWorks DataV数据可视化
所需费用	0元阿里云提供一定额度的资源包供您免费体验，开通后会使用计算、存储的免费资源包分别进行抵扣，各自的超出部分均会按量计费，免费额度详情请参见新用户免费试用。请注意不要直接用于生产，注意控制成本，超出部分的计费详情请参见按量付费。

准备环境和资源

准备新阿里云账号

进入阿里云官网，单击免费注册，创建阿里云账号。
对阿里云账号进行实名认证。
创建访问密钥AccessKey。
成功创建AccessKey后，返回AccessKey页面。您可以查看当前账号的AccessKey状态。详情请参见准备阿里云账号。

创建专有网络VPC和交换机

登录专有网络管理控制台。
在顶部菜单栏，选择华东2（上海）地域。
在左侧导航栏，单击专有网络。
在专有网络页面，单击创建专有网络。
在创建专有网络页面，配置1个专有网络（VPC）和1台交换机，交换机的可用区选择上海可用区E，然后单击确定。详情请参见创建专有网络和交换机。
单击已创建的VPC实例ID，在资源管理页签添加安全组。详情请参见创建安全组。

申请Hologres免费试用

登录Hologres免费试用，在弹出的试用实时数仓Hologres产品的面板上完成参数信息配置。本试用教程以表格中的参数信息为例，未提及参数保持默认值。

参数	示例值
地域	华东2（上海）
实例类型	通用型
计算资源	8核32GB（计算节点数量：1）
专有网络	选择已创建的VPC。
专有网络交换机	选择已创建的交换机。
实例名称	hologres_test
资源组	默认资源组

勾选服务协议后，单击立即试用，并根据页面提示完成试用申请。
单击前往控制台，开启试用体验。

申请MaxCompute免费试用

登录MaxCompute免费试用。
在云原生大数据计算服务 MaxCompute卡片上，单击立即试用。
在弹出的试用云原生大数据计算服务 MaxCompute产品的面板中配置开通地域为华东2（上海），其他参数保持默认。
勾选服务协议，单击立即试用，并根据页面提示完成试用申请。

申请DataWorks独享数据集成资源组免费试用

进入DataWorks按量付费页面，开通DataWorks按量付费，地域和可用区选择华东2（上海）。
进入DataWorks免费试用页面，申请DataWorks独享数据集成资源组免费试用。

申请DataV免费试用

登录DataV免费试用。
在数据可视化 DataV卡片上，单击立即试用。
在弹出的试用数据可视化 DataV产品的面板上勾选服务协议，单击立即试用，并根据页面提示完成试用申请。

导入实时数

初始化Hologres环境。

进入Hologres控制台-实例列表页面，单击目标实例名称，进入实例详情页面。
在实例详情页面单击登录实例，进入HoloWeb。
在元数据管理页面中单击数据库。

在新建数据库对话框中配置如下参数，并单击确认。

参数	说明
实例名	选择在哪个Hologres实例上创建数据库。默认展示当前已登录实例的名称，您也可以在下拉框中选择其他Hologres实例。
数据库名称	本示例数据库名称设置为`holo_tutorial`。
简单权限策略	选择默认的SPM。更多关于权限策略的说明，请参见： SPM：简单权限模型，该权限模型授权是以DB为粒度，划分admin（管理员）、developer（开发者）、writer（读写者）以及viewer（分析师）四种角色，您可以通过少量的权限管理函数，即可对DB中的对象进行方便且安全的权限管理。 SLPM：基于Schema级别的简单权限模型，该权限模型以Schema为粒度，划分 <db>.admin（DB管理员）、<db>.<schema>.developer（开发者）、<db>.<schema>.writer（读写者）以及 <db>.<schema>.viewer（分析师），相比于简单权限模型更为细粒度。专家：Hologres兼容PostgreSQL，使用与Postgres完全一致的权限系统。
立即登录	选择是。

进入顶部菜单栏的SQL编辑器页面，单击左上角的图标，打开临时Query查询页面。

新建Hologres内部表。

在临时Query查询页面执行如下示例命令，创建Hologres内部表hologres_dataset_github_event.hologres_github_event，后续会将数据实时写入至该表中。

-- 新建schema用于创建内表并导入数据
CREATE SCHEMA IF NOT EXISTS hologres_dataset_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 PRIMARY KEY,
  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,    
  commit_id text,
  member_id bigint,
  language 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.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.language IS '编程语言';

COMMIT;

实时同步数据至Hologres

网络打通。

需要将DataWorks独享数据集成资源组与创建的VPC进行绑定，保障网络连通性，才可进行数据同步。

前往授权页面，授予DataWorks进行云资源访问权限。
进入DataWorks资源组列表页面，单击目标独享数据集成资源组操作列的网络设置。

在专有网络绑定页签下，单击新增绑定，在弹出的新增专有网络绑定面板中，填写专有网络信息。并单击确定。

参数	说明
资源组名称	默认为目标独享数据集成资源组的名称。
专有网络	选择上述已创建的VPC专有网络。
可用区	选择VPC专有网络所在的可用区。
交换机	选择上述已创建的交换机。
安全组	选择上述已创建的安全组。

在DataWorks资源组列表页面，单击目标独享数据集成资源组操作列的修改归属工作空间，将资源组绑定至目标工作空间。

创建实时同步任务。

进入DataWorks数据集成首页，单击创建我的数据同步。
在新建同步任务页面，根据下表配置任务名称和同步类型，未提及的参数保持默认。
参数
说明
新任务名称
自定义。本文以data_test为例。
选择数据源
来源端：选择MySQL。
目的端：选择Hologres。

在网络与资源配置区域配置数据来源。

单击数据来源右侧的新建数据源。

在新增MySQL数据源对话框中配置如下参数，未提及的参数保持默认。并单击完成。

参数	说明
数据源类型	选择连接串模式。
数据源名称	自定义。本文以mysqlData为例。
JDBC URL	`jdbc:mysql://rm-bp1z69dodhh85z9qa.mysql.rds.aliyuncs.com:3306/github_events_share`
用户名	workshop
密码	workshop#2017 此密码仅为本教程示例，请勿在实际业务中使用。
认证选项	无认证。

在网络与资源配置区域配置同步资源组。
选择DataWorks免费独享数据集成资源组。
注意：为保持公共数据源连接稳定，独享数据集成资源组与公共MySQL数据源创建连接后7天将进行释放，不影响独享数据集成资源组与您自己的MySQL创建的连接。

在网络与资源配置区域配置数据去向。

单击数据去向右侧的新建数据源。

在新增Hologres数据源对话框中配置如下参数，未提及的参数保持默认。并单击完成。

参数	说明
数据源类型	阿里云实例模式。
数据源名称	自定义。本文以hologresData为例。
实例ID	进入实例列表页面单击目标实例，在实例详情页面，获取实例ID。
数据库名	填写上述已创建的Hologres数据库名称。
AccessKey ID	进入AccessKey管理页面获取AccessKey ID。
AccessKey Secret	进入AccessKey管理页面获取AccessKey Secret。
认证选项	无认证。

单击网络与资源配置区域的测试所有连通性，保障网络连通。
实时同步任务设置。
1. 测试连通性成功后，选择对应MySQL中的github表，勾选表并批量刷新映射。
2. 基于上述已创建的Hologres内部表，将目标Schema名改为hologres_dataset_github_event，目标表名改为hologres_github_event，单击完成配置。
3. 在任务列表页面启动任务，查看执行详情。
  公共数据源MySQL中保留近7天数据，离线数据将会通过全量进行同步，实时数据将在全量初始化完成后，实时写入Hologres。
4. 待数据同步成功后，前往Hologres进行实时数据分析。

实时数据分析与可视化

实时数据分析。
1. 进入HoloWeb SQL编辑器，查询实时更新的过去24小时GitHub最活跃项目。
```
SELECT
    repo_name,
    COUNT(*) AS events
FROM
    hologres_dataset_github_event.hologres_github_event
WHERE
    created_at >= now() - interval '1 day'
GROUP BY
    repo_name
ORDER BY
    events DESC
LIMIT 5;
```
2. 可以在元数据管理中自定义实时查询与分析Hologres近7天的数据。后续将其他维度的实时查询分析结果对接到DataV大屏，SQL样例可以查看附录。

实时数据可视化。

基于DataV自带模板，快速完成数据可视化大屏搭建。

前往DataV控制台，在左侧导航栏中选择数据准备 > 数据源。
在数据源页面，单击新建数据源。

在弹出的添加数据源面板中，根据下表参数信息新增Hologres数据源，并单击确定。

参数	说明
类型	实时数仓 Hologres
网络	内网华东2
VPC ID	在Hologres管控台-实例列表中单击目标实例名称，在实例详情页面的网络信息区域获取VPC ID。
VPC 实例 ID	在Hologres管控台-实例列表中单击目标实例名称，在实例详情页面的网络信息区域获取VPC实例 ID。
名称	自定义命名。
域名	在Hologres管控台-实例列表中单击目标实例名称，在实例详情页面的网络信息区域获取域名。
用户名	进入AccessKey管理页面获取AccessKey ID。
密码	进入AccessKey管理页面获取AccessKey Secret。
端口	在Hologres管控台-实例列表中单击目标实例名称，在实例详情页面的网络信息区域，获取指定VPC的域名列对应的端口。例如：80。
数据库	选择上述已创建的Hologres数据库名称。

前往DataV控制台，单击未分组页面的创建 PC 端看板，选择使用Hologres实时分析GitHub事件数据模板创建看板。
在创建看板对话框输入看板名称后，单击创建看板。
在已创建的看板页面中，单击数字，并在右侧数据源页签中，选择已添加的Hologres数据源。共计需要选择15处。
所有数字涉及的数据源都选择完成后，单击看板右上角的预览，即可获取实时更新的数据大屏预览链接（正式发布需要升级DataV高级版本）。最终效果如下：

（可选）历史离线数据分析

实时数仓Hologres与大数据计算服务MaxCompute深度融合，可以组成一体化的大数据查询与分析架构。在MaxCompute公共数据集中，存储了历史GitHub全量数据。如果想要做更长时间的数据分析，有两种方式：

使用外部表查询，在不导入数据的情况下，使用Hologres直接查询MaxCompute数据。
使用内部表查询，将历史数据通过0 ETL的形式快速导入Hologres，获得更快的查询速度。

重要

Github每日数据量约为300MB，Hologres免费试用存储额度为20GB，如果导入过多存量历史数据，将会收产生额外费用，外部表查询不受影响。

外部表查询MaxCompute数据。

创建MaxCompute外部表。

DROP FOREIGN TABLE IF EXISTS dwd_github_events_odps;

IMPORT FOREIGN SCHEMA "bigdata_public_dataset#github_events" LIMIT to
(
    dwd_github_events_odps
) 
FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'error',if_unsupported_type 'error');

通过外部表直接查询MaxCompute数据。例如查询昨日起最活跃项目：

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

0 ETL导入MaxCompute数据。

创建Hologres内部表。

DROP TABLE IF EXISTS gh_event_data;

BEGIN;
CREATE TABLE gh_event_data (
    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('public.gh_event_data', 'distribution_key', 'id');
CALL set_table_property('public.gh_event_data', 'event_time_column', 'created_at');
CALL set_table_property('public.gh_event_data', 'clustering_key', 'created_at');
COMMIT;

通过外部表导入数据至内部表。

INSERT INTO gh_event_data
SELECT
    *
FROM
    dwd_github_events_odps
WHERE
    ds >= (CURRENT_DATE - interval '1 day')::text;

-- 更新表的统计信息
ANALYZE gh_event_data;

通过内部表查询昨日起最活跃项目。

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

附录

实验中包含数据大屏涉及到的所有SQL如下，需要将下面的<table_name>分别换成实际的表名。

今日开发者和项目总数。

SELECT
    uniq (actor_id) actor_num,
    uniq (repo_id) repo_num
FROM
    <table_name>
WHERE
    created_at > date_trunc('day', now());

过去24小时最活跃项目。

SELECT
    repo_name,
    COUNT(*) AS events
FROM
    <table_name>
WHERE
    created_at >= now() - interval '1 day'
GROUP BY
    repo_name
ORDER BY
    events DESC
LIMIT 5;

过去24小时最活跃开发者。

SELECT
    actor_login,
    COUNT(*) AS events
FROM
    <table_name>
WHERE
    created_at >= now() - interval '1 day'
    and actor_login not like '%[bot]'
GROUP BY
    actor_login
ORDER BY
    events DESC
LIMIT 5;

今日公开事件总数。

select count(*) from <table_name> where created_at >= date_trunc('day', now());

过去24小时星标项目排行。

SELECT
    repo_id,
    repo_name,
    COUNT(actor_login) total
FROM
    <table_name>
WHERE
    type = 'WatchEvent'
    AND created_at > now() - interval '1 day'
GROUP BY
    repo_id,
    repo_name
ORDER BY
    total DESC
LIMIT 10;

WITH agg_language AS (
    SELECT
        CASE
        LANGUAGE
        WHEN 'TypeScript' THEN
            'TypeScript'
        WHEN 'JavaScript' THEN
            'JavaScript'
        WHEN 'Python' THEN
            'Python'
        WHEN 'Go' THEN
            'Go'
        WHEN 'Java' THEN
            'Java'
        WHEN 'HTML' THEN
            'HTML'
        WHEN 'C++' THEN
            'C++'
        WHEN 'C#' THEN
            'C#'
        WHEN 'PHP' THEN
            'PHP'
        WHEN 'Ruby' THEN
            'Ruby'
        ELSE
            'Others'
        END AS
        LANGUAGE,
        count(*) total
    FROM
        <table_name>
    WHERE
        created_at > now() - interval '1 hour'
        AND
        LANGUAGE IS
        NOT NULL
    GROUP BY
        LANGUAGE
    ORDER BY
        total DESC
)
SELECT
    LANGUAGE,
    sum(total) AS sum
FROM
    agg_language
GROUP BY
    LANGUAGE
ORDER BY
    sum DESC;

实时事件展示。

SELECT
    cast(created_at as text),
    actor_login,
    type,
    repo_name
FROM
    <table_name>
ORDER BY
    created_at DESC
LIMIT 5;

完成

完成以上操作后，您已经成功完成了Hologres数据查询操作。查询命令执行成功后，在临时Query查询页面下弹出结果页签，显示如下查询数据结果。

查询实时更新的过去24小时GitHub最活跃项目结果示例：
通过外部表直接查询MaxCompute数据（查询昨日起最活跃项目）结果示例：
通过内部表查询昨日起最活跃项目。

清理及后续

在体验完成本教程后，如果后续您不再使用的话，请及时释放相关资源。

欢迎加入实时数仓Hologres交流群（钉钉群号：32314975）交流。

释放Hologres实例
登录Hologres管理控制台，在实例列表页面释放目标Hologres实例，详情请参见删除实例。
释放交换机
登录专有网络管理控制台，在交换机页面，找到目标交换机，然后在操作列单击删除，按照界面提示释放实例。
释放专有网络VPC
登录专有网络管理控制台，在专有网络页面，找到目标VPC，然后在操作列单击删除，按照界面提示释放实例。
删除DataWorks中删除同步任务和数据源
1. 登录DataWorks管理控制台-同步任务页面，找到目标任务，在操作列单击更多 > 删除，按照界面提示删除同步任务。
2. 登录DataWorks管理控制台-数据源页面，找到目标数据源，在操作列单击图标，按照界面提示删除数据源。
删除DataV数据源
登录DataV管理控制台，在数据源页面，找到目标数据源并删除。

参数	说明
新任务名称	自定义。本文以data_test为例。
选择数据源	来源端：选择MySQL。目的端：选择Hologres。