挑战SQL Bond，寻找数据分析最强007-阿里云开发者社区

通过DataWorks数据集成，将最新Github数据集实时同步至Hologres。同时MaxCompute存储近7年公开数据，在数据不导出的情况下，利用Hologres离线实时一体化分析能力，快速查询分析结果。

一、准备环境与资源

准备新阿里云账号

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

创建专有网络VPC和交换机

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

申请Hologres免费试用

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

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

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

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

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

申请MaxCompute免费试用

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

离线数据分析

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

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

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

在Hologres中创建内部表用于后续写入数据。

初始化Hologres环境

进入Hologres控制台-实例列表页面，单击目标实例名称，进入实例详情页面。

在实例详情页面单击登录实例，进入HoloWeb。

a.在元数据管理页面中单击数据库。

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

参数	说明
实例名	选择在哪个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查询页面。

外部表查询MaxCompute数据

创建MaxCompute外部表

DROP FOREIGN TABLE IF EXISTS dwd_github_events_odps;
IMPORT FOREIGN SCHEMA "maxcompute_public_data#github_events" LIMIT to
(
    dwd_github_events_odps
) 
FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'error',if_unsupported_type 'error');

1. 通过外部表直接查询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;

查询DDL信息

在元数据管理-已登录实例-外表中，可以查看DDL语句信息，基于表结构进行自定义分析。

可选-内部表查询，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;

实时数据分析

导入实时数据

新建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。
认证选项	无认证。

单击网络与资源配置区域的测试所有连通性，保障网络连通。

实时同步任务设置。

测试连通性成功后，选择对应MySQL中的github表，勾选表并批量刷新映射。

基于上述已创建的Hologres内部表，将目标Schema名改为hologres_dataset_github_event，目标表名改为hologres_github_event，单击完成配置。

在任务列表页面启动任务，查看执行详情。
公共数据源MySQL中保留近7天数据，离线数据将会通过全量进行同步，实时数据将在全量初始化完成后，实时写入Hologres。

待数据同步成功后，前往Hologres进行实时数据分析。

实时数据分析

进入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;