基于DataLakeAnalytics 的数据湖实践是怎样的

81次阅读

共计 7526 个字符，预计需要花费 19 分钟才能阅读完成。

基于 DataLakeAnalytics 的数据湖实践是怎样的，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

开通 DLA

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

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

mysql -hservice.cn-shanghai.datalakeanalytics.aliyuncs.com 
 -P10000 
 -u your-user-name  
 -p your-password

在这篇文章里面，我会使用 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 =  your-rds-instance-id ,
 VPC_ID =  your-vpc-id-where-your-rds-lives 
);

这里需要解释一下的是 VPC_ID 和 INSTANCE_ID, 我们知道为了安全的原因在阿里云上购买的 RDS 我们一般都会把它放在一个单独的 VPC 里面，以保证只有我们自己可以访问，这里为了让 DLA 能够访问到我们的 MySQL 数据库以进行数据回流，我们需要告诉 DLA 这个 RDS 的相关信息。

其中 INSTANCE_ID 和 VPC_ID 在 RDS 的详情页面都可以找到, 比如 VPC_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)

看完上述内容，你们掌握基于 DataLakeAnalytics 的数据湖实践是怎样的的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！

正文完