基于 DataLakeAnalytics 的数据湖实践

一时失言乱红尘 2022-10-02 00:41 113阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

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

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

## 开通DLA ##

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

![v2-57e1f63f954a429c35959fc43968893e\_hd.jpg][v2-57e1f63f954a429c35959fc43968893e_hd.jpg]

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

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

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

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

![v2-37274bf5332af6d0235c2c0e44ab3ade\_hd.jpg][v2-37274bf5332af6d0235c2c0e44ab3ade_hd.jpg]

## 用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 个文件(这个例子里面只有一个文件，实际使用中，这里可以有多个文件):

![v2-0d31e1af03dc5c4deb6ffae8479f46e1\_hd.jpg][v2-0d31e1af03dc5c4deb6ffae8479f46e1_hd.jpg]

我们把这个 `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` :

![v2-27b3f46e33846f4e6091b27a7afda4a2\_hd.jpg][v2-27b3f46e33846f4e6091b27a7afda4a2_hd.jpg]

`INSTANCE_ID` :

![v2-96915da5d87b6a72ea55cb194111ccb0\_hd.jpg][v2-96915da5d87b6a72ea55cb194111ccb0_hd.jpg]

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

![v2-09e55ce3b950359bc7c0973941a5f479\_hd.jpg][v2-09e55ce3b950359bc7c0973941a5f479_hd.jpg]

到这里为止，准备工作就完成了，我们的 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里面去。

一站式开发者服务，海量学习资源0元起！

阿里热门开源项目、机器学习干货、开发者课程/工具、小微项目、移动研发等海量资源；更有开发者福利Kindle、技术图书幸运抽奖，100%中--》[【阿里云】开年Hi购季，开发者会场][Hi]

[原文链接][Link 1]

本文为云栖社区原创内容，未经允许不得转载。

转载于:https://my.oschina.net/u/3889140/blog/3024574

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/6012bec3eb034b7794af7fa4f68cb423.png
[OSS]: http://link.zhihu.com/?target=https%3A//www.aliyun.com/product/oss%3Fspm%3D5176.8142029.selected.3.e9396d3eHz02fQ
[DataLake Analytics]: http://link.zhihu.com/?target=https%3A//www.aliyun.com/product/datalakeanalytics
[v2-57e1f63f954a429c35959fc43968893e_hd.jpg]: /images/20220113/701c3f25f92e4b2282aa34307ac81d44.png
[v2-37274bf5332af6d0235c2c0e44ab3ade_hd.jpg]: /images/20220113/0c82835bb1a244d7834badeff4357565.png
[v2-0d31e1af03dc5c4deb6ffae8479f46e1_hd.jpg]: /images/20220113/211f6a39fe034af48c1c4c5f2a59639a.png
[v2-27b3f46e33846f4e6091b27a7afda4a2_hd.jpg]: /images/20220113/cee43da368f549a5a8a76b73845cf9e7.png
[v2-96915da5d87b6a72ea55cb194111ccb0_hd.jpg]: /images/20220113/c1a5d2286b514ea6b7afcd4927da3a5f.png
[v2-09e55ce3b950359bc7c0973941a5f479_hd.jpg]: /images/20220113/9051127ff48c4a87b363a9344d3a1382.png
[Hi]: http://link.zhihu.com/?target=https%3A//www.aliyun.com/acts/product-section-2019/developer%3Futm_content%3Dg_1000047140
[Link 1]: http://link.zhihu.com/?target=https%3A//yq.aliyun.com/articles/633513%3Futm_content%3Dg_1000047402