基于 DataLakeAnalytics 的数据湖实践

本篇文章给大家分享《基于 DataLakeAnalytics 的数据湖实践》，覆盖了数据库的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

随着软硬件各方面条件的成熟，数据湖(Data Lake)已经越来越受到各大企业的青睐, 与传统的数仓实践不一样的是，数据湖不需要专门的“入仓”的过程，数据在哪里，我们就从哪里读取数据进行分析。这样的好处在于：一来数据可以保存在很便宜的存储上面(比如阿里云的OSS 上面), 给企业节省预算，而需要分析的时候又可以分析；另一方面，因为省去了入仓的流程，对于中小型企业来说人员投入更少，更容易上手。

今天我们就给大家介绍一下，如何基于阿里云的数据湖分析引擎: DataLake Analytics(后面简称DLA) 对用户保存在 OSS 里面的数据建立数据湖，对数据进行各个维度的分析，分析完成得到业务洞见之后再把这些产生的结果再回流到的 RDS 里面供前台业务决策使用。

开通DLA
在开始之前我们要有一个 DLA 的账号，目前 DLA 正在公测，直接申请试用就好了。试用审批成功之后，你会获得一个用户名和密码, 然后在控制台登录就可以使用:

或者如果你是极客，更偏爱命令行，你也可以使用普通的 MySQL 客户端就可以连接 DLA 了:

mysql -hservice.cn-shanghai.datalakeanalytics.aliyuncs.com

  -P10000 
  -u<your-user-name> 
  -p<your-password></your-password></your-user-name>

在这篇文章里面，我会使用 MySQL 命令行给大家演示 DLA 的功能。

另外你还需要在您的OSS上准备一些测试数据, 我这里准备的是著名的 TPCH 测试数据集:

用DLA分析OSS上的数据
DLA 是一个以 SQL 作为查询语言的数据湖引擎，为了能够让 DLA 能够对 OSS 上的数据进行查询，我们需要以某种方式告诉 DLA 我们 OSS 数据的结构。为了让用户使用更方便，DLA 使用了传统的 数据库, 表 的概念来维护这些数据的元信息，也就说，OSS的文件结构的数据映射到 DLA 变成了一个数据库和一堆表。

以 TPCH 数据集来举个例子，我们知道 TPCH 数据集里面包含了如下几块信息: 用户(customer), 订单(orders), 订单的详情(lineitem) 等等，这些数据整体属于一块业务，我们建立一个数据库来对应:

CREATE SCHEMA oss_tpch with DBPROPERTIES(
CATALOG = 'oss',
LOCATION = 'oss://public-datasets-cn-hangzhou/tpch/1x/'
);
这每块数据对应到OSS上一个目录的多个文件，拿 订单 来说，它对应的是 orders_text 目录下面的 1 个文件(这个例子里面只有一个文件，实际使用中，这里可以有多个文件):

我们把这个 orders_text 目录映射到我们的数据库 oss_tpch 下面的一张表:

use oss_tpch;

CREATE EXTERNAL TABLE IF NOT EXISTS orders (

O_ORDERKEY INT, 
O_CUSTKEY INT, 
O_ORDERSTATUS STRING, 
O_TOTALPRICE DOUBLE, 
O_ORDERDATE DATE, 
O_ORDERPRIORITY STRING, 
O_CLERK STRING, 
O_SHIPPRIORITY INT, 
O_COMMENT STRING

)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://public-datasets-cn-hangzhou/tpch/1x/orders_text/';
这样我们就可以通过 DLA 对OSS上的进行数据分析了, 比如我们先来查个前十条看看:

mysql> select * from orders limit 10;
o_orderkey	o_custkey	o_orderstatus	o_totalprice	o_orderdate	o_orderpriority	o_clerk	o_shippriority	o_comment
1	3689999	O	224560.83	1996-01-02	5-LOW	Clerk#000095055	0	nstructions sleep furiously among
2	7800163	O	75388.65	1996-12-01	1-URGENT	Clerk#000087916	0	foxes. pending accounts at the pending, silent asymptot
3	12331391	F	255287.36	1993-10-14	5-LOW	Clerk#000095426	0	sly final accounts boost. carefully regular ideas cajole carefully. depos
4	13677602	O	43119.84	1995-10-11	5-LOW	Clerk#000012340	0	sits. slyly regular warthogs cajole. regular, regular theodolites acro
5	4448479	F	125809.76	1994-07-30	5-LOW	Clerk#000092480	0	quickly. bold deposits sleep slyly. packages use slyly
6	5562202	F	56408.2	1992-02-21	4-NOT SPECIFIED	Clerk#000005798	0	ggle. special, final requests are against the furiously specia
7	3913430	O	240358.24	1996-01-10	2-HIGH	Clerk#000046961	0	ly special requests
32	13005694	O	136666.23	1995-07-16	2-HIGH	Clerk#000061561	0	ise blithely bold, regular requests. quickly unusual dep
33	6695788	F	183460.23	1993-10-27	3-MEDIUM	Clerk#000040860	0	uriously. furiously final request
34	6100004	O	52842.63	1998-07-21	3-MEDIUM	Clerk#000022278	0	ly final packages. fluffily final deposits wake blithely ideas. spe

10 rows in set (0.21 sec)
我们再来看看用户 36901 的前十条订单:

mysql> select * from orders where o_custkey= '36901' limit 10;
o_orderkey	o_custkey	o_orderstatus	o_totalprice	o_orderdate	o_orderpriority	o_clerk	o_shippriority	o_comment
1243264	36901	F	103833.45	1992-03-23	2-HIGH	Clerk#000000922	0	nts haggle. even, even theodolites are. blithely
1274530	36901	O	181977.58	1997-04-29	2-HIGH	Clerk#000000232	0	bold foxes along the carefully expres
1599527	36901	F	322352.11	1993-10-16	2-HIGH	Clerk#000000674	0	the slyly even dependencies.
1837477	36901	F	101653.62	1993-05-27	5-LOW	Clerk#000000891	0	lyly special requests. express foxes sleep fu
1994082	36901	O	77952.78	1995-07-05	3-MEDIUM	Clerk#000000525	0	luffily ironic courts. bold, e
2224802	36901	F	243852.76	1993-01-14	1-URGENT	Clerk#000000827	0	sly final requests. pending, regular ideas among the furiously u
4957636	36901	F	5741.32	1992-05-20	5-LOW	Clerk#000000230	0	ackages. fluffily even packages solve carefully dolphins. unusua
5078467	36901	F	119823.03	1994-04-29	4-NOT SPECIFIED	Clerk#000000402	0	regular asymptotes cajo
5173859	36901	F	103624.02	1994-05-28	3-MEDIUM	Clerk#000000335	0	regular dependencies poach quickly. unusu
5525574	36901	O	136098.0	1998-02-16	4-NOT SPECIFIED	Clerk#000000425	0	cial pinto beans wake. slyly even warthogs use. bo

10 rows in set (1.07 sec)
再来查一查订单量最多的前是个人:

mysql> select o_custkey, count(*) as cnt from orders group by o_custkey order by cnt desc limit 10;
o_custkey	cnt
3451	41
102022	41
102004	41
79300	40
117082	40
122623	40
69682	39
143500	39
142450	38
53302	38

10 rows in set (2.69 sec)
恩，这些人就是我们要重点服务好的客户啊，我们要把这些用户的ID回写到前台的 RDS 数据库里面让我们的营销同学做一些针对性的营销活动，没问题，DLA支持把分析好的数据回流到RDS

数据回流 RDS
映射 MySQL 数据库信息进 DLA
要把分析好的数据回流到RDS我们首先一种机制来告诉 DLA 数据回流的目的地，得益于DLA统一的设计，我们就像映射 OSS 的数据一样，我们映射一个 MySQL 数据库进来就好了，比如我们要把数据写到如下的数据库里面:

mysql -habcde.mysql.rds.aliyuncs.com -P3306 -uhello -pworld -Dmarketing
那么我们在 DLA 里面建一个映射的库:

CREATE SCHEMA

mysql_marketing

WITH DBPROPERTIES
(
CATALOG = 'mysql',
LOCATION = 'jdbc:mysql://abcde.mysql.rds.aliyuncs.com:3306/marketing',
USER='hello',
PASSWORD='world',
INSTANCE_ID = '',
VPC_ID = ''
);
这里需要解释一下的是 VPC_ID 和 INSTANCE_ID, 我们知道为了安全的原因在阿里云上购买的 RDS 我们一般都会把它放在一个单独的VPC里面，以保证只有我们自己可以访问，这里为了让 DLA 能够访问到我们的 MySQL 数据库以进行数据回流，我们需要告诉 DLA 这个 RDS的相关信息。

其中 INSTANCE_ID 和 VPC_ID 在 RDS的详情页面都可以找到, 比如 VPC_ID :

INSTANCE_ID :

由于 RDS 的安全组会对访问的来源IP进行控制，我们需要把DLA相关的地址段 100.104.0.0/16 IP地址段加入到你的RDS的白名单列表，如下图:

到这里为止，准备工作就完成了，我们的 mysql 数据库建好了。

映射 MySQL 结果表进 DLA
我们要保存的结果很简单，就是下单量前 10 的用户, 这个表在 MySQL 数据库里面的建表语句如下:

create table top10_user (

custkey int,
order_cnt bigint

);
而为了把这个表映射进 DLA 我们建一个对应的表，建表语句几乎一样:

use mysql_marketing;
create external table top10_user (

custkey int,
order_cnt bigint

);
ETL
下面我们就可以把查出来的数据进行回流了:

mysql> insert into mysql_marketing.top10_user

-> select o_custkey, count(*) as cnt from oss_tpch.orders
-> group by o_custkey order by cnt desc limit 10;

rows
10

1 row in set (4.71 sec)

mysql> select * from mysql_marketing.top10_user;
custkey	order_cnt
143500	39
102004	41
53302	38
3451	41
122623	40
129637	38
102022	41
117082	40
69682	39
79300	40

10 rows in set (0.14 sec)
总结
在这篇文章里面，我带大家一起体验了一下如何用 DLA 建立基于 OSS 的数据湖，对数据库里面的数据进行各个维度的分析，分析完成之后把分析得到的关键数据再回写到我们的RDS里面去。例子里面很多地方写的比较简单，如果想进一步了解更多相关详细信息可以参考以下资料:

Data Lake Analytics + OSS数据文件格式处理大全: https://yq.aliyun.com/article...
Data Lake Analytics中OSS LOCATION的使用说明: https://yq.aliyun.com/article...
如何使用Data Lake Analytics创建分区表: https://yq.aliyun.com/article...
基于Data Lake Analytics来分析OTS上的数据: https://yq.aliyun.com/article...
使用Data Lake Analytics从OSS清洗数据到AnalyticDB: https://yq.aliyun.com/article...
使用Data Lake Analytics读/写RDS数据: https://yq.aliyun.com/article...

理论要掌握，实操不能落！以上关于《基于 DataLakeAnalytics 的数据湖实践》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！