挑战SQL Bond,寻找数据分析最强007

本文涉及的产品
DataWorks独享数据集成资源组,8核16GB 1个月
大数据开发治理平台 DataWorks,不限时长
简介: 免费领取5000元大数据产品,实时分析近7年Github公开数据集,Show Your SQL!
+关注继续查看

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

一、准备环境与资源

准备新阿里云账号

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

创建专有网络VPC和交换机

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

申请Hologres免费试用

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

参数

示例值

地域

华东2(上海)

实例类型

通用型

计算资源

8核32GB(计算节点数量:1)

专有网络

选择已创建的VPC。

专有网络交换机

选择已创建的交换机。

实例名称

hologres_test

资源组

默认资源组

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

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

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

申请MaxCompute免费试用

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

离线数据分析

实时数仓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编辑器页面,单击左上角的image图标,打开临时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语句信息,基于表结构进行自定义分析。

image.png


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

数据库创建成功后,您需在数据库中创建对应的表。

  1. 网络打通。需要将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表,勾选表并批量刷新映射。
image

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

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

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

实时数据分析

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


相关实践学习
简单用户画像分析
本场景主要介绍基于海量日志数据进行简单用户画像分析为背景,如何通过使用DataWorks完成数据采集 、加工数据、配置数据质量监控和数据可视化展现等任务。
一站式大数据开发治理平台DataWorks初级课程
DataWorks 从 2009 年开始,十ー年里一直支持阿里巴巴集团内部数据中台的建设,2019 年双 11 稳定支撑每日千万级的任务调度。每天阿里巴巴内部有数万名数据和算法工程师正在使用DataWorks,承了阿里巴巴 99%的据业务构建。本课程主要介绍了阿里巴巴大数据技术发展历程与 DataWorks 几大模块的基本能力。 课程目标 &nbsp;通过讲师的详细讲解与实际演示,学员可以一边学习一边进行实际操作,可以深入了解DataWorks各大模块的使用方式和具体功能,让学员对DataWorks数据集成、开发、分析、运维、安全、治理等方面有深刻的了解,加深对阿里云大数据产品体系的理解与认识。 适合人群 &nbsp;企业数据仓库开发人员 &nbsp;大数据平台开发人员 &nbsp;数据分析师 &nbsp;大数据运维人员 &nbsp;对于大数据平台、数据中台产品感兴趣的开发者
相关文章
|
3天前
|
SQL 分布式计算 数据可视化
Spark SQL案例【电商购买数据分析】
Spark SQL案例【电商购买数据分析】
|
3天前
|
SQL 分布式计算 数据可视化
Spark SQL【基于泰坦尼克号生还数据的 Spark 数据分析处理】
Spark SQL【基于泰坦尼克号生还数据的 Spark 数据分析处理】
|
25天前
|
SQL 数据挖掘 关系型数据库
数据分析法宝,一个SQL语句查询多个异构数据源
NineData DSQL 是针对多个同异构数据库系统进行跨库查询的功能,当前支持对表和视图进行 SELECT 操作。您可以在一个查询中访问多个数据库,获取分散在各个数据库中的有用信息,并且将这些信息聚合为一份查询结果返回,轻松实现跨多个库、多个数据源,乃至跨多个异构数据源的数据查询。
236 0
数据分析法宝,一个SQL语句查询多个异构数据源
|
4月前
|
SQL 数据库
SQL Bond使用指南
SQL bond使用只能总结
198 0
|
4月前
|
SQL 数据挖掘 网络安全
【获奖名单公示】【保姆级教程】挑战SQL Bond,寻找数据分析最强007
各位开发者大家好,感谢大家参加挑战“SQL Bond,寻找数据分析最强007”挑战赛,以下为活动的参与指南,请您仔细阅读便于更顺利地完成比赛。
1383 0
【获奖名单公示】【保姆级教程】挑战SQL Bond,寻找数据分析最强007
|
5月前
|
SQL 运维 Prometheus
32-一条SQL引发的系统卡死(上)-数据分析
今天给大家分享一个真实的案例,这是我之前一个朋友公司的项目在开发完毕后上线发现整个项目在线上的体验非常不好,有明显的频繁卡顿,后来经过一连串的排查、定位、分析和优化才发现原来是一条SQL引发的问题,而且该SQL语句是一位刚入职不久的工程师所写。 通过这次事故的教训也让这个团队在后续的开发中更加注重了JVM参数的设置以及GC的监控。
83 0
|
5月前
|
SQL 数据采集 数据可视化
数据开发模块中的SQL和数据分析模块中的SQL查询
数据开发模块中的SQL和数据分析模块中的SQL查询
130 1
|
7月前
|
SQL 分布式计算 数据挖掘
Spark SQL的数据分析应用
Spark SQL的数据分析应用
|
7月前
|
SQL 存储 Oracle
数据分析基础--SQL
我们在做数据分析的工作时,会用到大量的数据,而这些数据都来自于数据库,SQL可以让我们很方便的去访问和查询数据库。 作为一个数据分析师,我们需要掌握一些SQL的核心技能
|
7月前
|
SQL 机器学习/深度学习 数据可视化
《PolarDB for PostgreSQL源码与应用实战》——PolarDB for PostgreSQL用SQL做数据分析(1)
《PolarDB for PostgreSQL源码与应用实战》——PolarDB for PostgreSQL用SQL做数据分析(1)
994 0
相关产品
大数据开发治理平台 DataWorks
推荐文章
更多