隨著軟硬件各方面條件的成熟音榜，數(shù)據(jù)湖(Data Lake)已經(jīng)越來越受到各大企業(yè)的青睞, 與傳統(tǒng)的數(shù)倉實(shí)踐不一樣的是芯义，數(shù)據(jù)湖不需要專門的“入倉”的過程，數(shù)據(jù)在哪里，我們就從哪里讀取數(shù)據(jù)進(jìn)行分析坯墨。這樣的好處在于：一來數(shù)據(jù)可以保存在很便宜的存儲(chǔ)上面(比如阿里云的OSS 上面), 給企業(yè)節(jié)省預(yù)算，而需要分析的時(shí)候又可以分析病往；另一方面捣染，因?yàn)槭∪チ巳雮}的流程，對(duì)于中小型企業(yè)來說人員投入更少停巷，更容易上手耍攘。
今天我們就給大家介紹一下，如何基于阿里云的數(shù)據(jù)湖分析引擎: DataLake Analytics(后面簡(jiǎn)稱DLA) 對(duì)用戶保存在 OSS 里面的數(shù)據(jù)建立數(shù)據(jù)湖畔勤，對(duì)數(shù)據(jù)進(jìn)行各個(gè)維度的分析蕾各，分析完成得到業(yè)務(wù)洞見之后再把這些產(chǎn)生的結(jié)果再回流到的 RDS 里面供前臺(tái)業(yè)務(wù)決策使用。
開通DLA
在開始之前我們要有一個(gè) DLA 的賬號(hào)庆揪，目前 DLA 正在公測(cè)式曲，直接申請(qǐng)?jiān)囉镁秃昧恕Ｔ囉脤徟晒χ蟾组唬銜?huì)獲得一個(gè)用戶名和密碼, 然后在控制臺(tái)登錄就可以使用:
或者如果你是極客吝羞，更偏愛命令行，你也可以使用普通的 MySQL 客戶端就可以連接 DLA 了:
mysql -hservice.cn-shanghai.datalakeanalytics.aliyuncs.com
-P10000
-u<your-user-name>
-p<your-password>
在這篇文章里面内颗，我會(huì)使用 MySQL 命令行給大家演示 DLA 的功能钧排。
另外你還需要在您的OSS上準(zhǔn)備一些測(cè)試數(shù)據(jù), 我這里準(zhǔn)備的是著名的 TPCH 測(cè)試數(shù)據(jù)集:
用DLA分析OSS上的數(shù)據(jù)
DLA 是一個(gè)以 SQL 作為查詢語言的數(shù)據(jù)湖引擎，為了能夠讓 DLA 能夠?qū)?OSS 上的數(shù)據(jù)進(jìn)行查詢均澳，我們需要以某種方式告訴 DLA 我們 OSS 數(shù)據(jù)的結(jié)構(gòu)恨溜。為了讓用戶使用更方便，DLA 使用了傳統(tǒng)的 數(shù)據(jù)庫, 表 的概念來維護(hù)這些數(shù)據(jù)的元信息找前，也就說筒捺，OSS的文件結(jié)構(gòu)的數(shù)據(jù)映射到 DLA 變成了一個(gè)數(shù)據(jù)庫和一堆表。
以 TPCH 數(shù)據(jù)集來舉個(gè)例子纸厉，我們知道 TPCH 數(shù)據(jù)集里面包含了如下幾塊信息: 用戶(customer), 訂單(orders), 訂單的詳情(lineitem) 等等系吭，這些數(shù)據(jù)整體屬于一塊業(yè)務(wù)，我們建立一個(gè)數(shù)據(jù)庫來對(duì)應(yīng):
CREATE SCHEMA oss_tpch with DBPROPERTIES(
CATALOG = 'oss',
LOCATION = 'oss://public-datasets-cn-hangzhou/tpch/1x/'
);
這每塊數(shù)據(jù)對(duì)應(yīng)到OSS上一個(gè)目錄的多個(gè)文件颗品，拿 訂單 來說肯尺，它對(duì)應(yīng)的是 orders_text 目錄下面的 1 個(gè)文件(這個(gè)例子里面只有一個(gè)文件沃缘，實(shí)際使用中，這里可以有多個(gè)文件):
我們把這個(gè) orders_text 目錄映射到我們的數(shù)據(jù)庫 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 對(duì)OSS上的進(jìn)行數(shù)據(jù)分析了, 比如我們先來查個(gè)前十條看看:
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)
我們?cè)賮砜纯从脩?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)
再來查一查訂單量最多的前是個(gè)人:
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)
恩则吟，這些人就是我們要重點(diǎn)服務(wù)好的客戶啊槐臀，我們要把這些用戶的ID回寫到前臺(tái)的 RDS 數(shù)據(jù)庫里面讓我們的營銷同學(xué)做一些針對(duì)性的營銷活動(dòng)，沒問題氓仲，DLA支持把分析好的數(shù)據(jù)回流到RDS
數(shù)據(jù)回流 RDS
映射 MySQL 數(shù)據(jù)庫信息進(jìn) DLA
要把分析好的數(shù)據(jù)回流到RDS我們首先一種機(jī)制來告訴 DLA 數(shù)據(jù)回流的目的地水慨，得益于DLA統(tǒng)一的設(shè)計(jì)，我們就像映射 OSS 的數(shù)據(jù)一樣敬扛，我們映射一個(gè) MySQL 數(shù)據(jù)庫進(jìn)來就好了晰洒，比如我們要把數(shù)據(jù)寫到如下的數(shù)據(jù)庫里面:
mysql -habcde.mysql.rds.aliyuncs.com -P3306 -uhello -pworld -Dmarketing
那么我們?cè)?DLA 里面建一個(gè)映射的庫:
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 我們一般都會(huì)把它放在一個(gè)單獨(dú)的VPC里面，以保證只有我們自己可以訪問啥箭，這里為了讓 DLA 能夠訪問到我們的 MySQL 數(shù)據(jù)庫以進(jìn)行數(shù)據(jù)回流谍珊，我們需要告訴 DLA 這個(gè) RDS的相關(guān)信息。
其中 INSTANCE_ID 和 VPC_ID 在 RDS的詳情頁面都可以找到, 比如 VPC_ID :
INSTANCE_ID :
由于 RDS 的安全組會(huì)對(duì)訪問的來源IP進(jìn)行控制急侥，我們需要把DLA相關(guān)的地址段 100.104.0.0/16 IP地址段加入到你的RDS的白名單列表砌滞，如下圖:
到這里為止，準(zhǔn)備工作就完成了坏怪，我們的 mysql 數(shù)據(jù)庫建好了贝润。
映射 MySQL 結(jié)果表進(jìn) DLA
我們要保存的結(jié)果很簡(jiǎn)單，就是下單量前 10 的用戶, 這個(gè)表在 MySQL 數(shù)據(jù)庫里面的建表語句如下:
create table top10_user (
custkey int,
order_cnt bigint
);
而為了把這個(gè)表映射進(jìn) DLA 我們建一個(gè)對(duì)應(yīng)的表铝宵，建表語句幾乎一樣:
use mysql_marketing;
create external table top10_user (
custkey int,
order_cnt bigint
);
ETL
下面我們就可以把查出來的數(shù)據(jù)進(jìn)行回流了:
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)
總結(jié)
在這篇文章里面打掘，我?guī)Т蠹乙黄痼w驗(yàn)了一下如何用 DLA 建立基于 OSS 的數(shù)據(jù)湖，對(duì)數(shù)據(jù)庫里面的數(shù)據(jù)進(jìn)行各個(gè)維度的分析捉超，分析完成之后把分析得到的關(guān)鍵數(shù)據(jù)再回寫到我們的RDS里面去胧卤。例子里面很多地方寫的比較簡(jiǎn)單，如果想進(jìn)一步了解更多相關(guān)詳細(xì)信息可以參考以下資料:
- Data Lake Analytics + OSS數(shù)據(jù)文件格式處理大全: https://yq.aliyun.com/articles/623246
- Data Lake Analytics中OSS LOCATION的使用說明: https://yq.aliyun.com/articles/623247
- 如何使用Data Lake Analytics創(chuàng)建分區(qū)表: https://yq.aliyun.com/articles/624151
- 基于Data Lake Analytics來分析OTS上的數(shù)據(jù): https://yq.aliyun.com/articles/618501
- 使用Data Lake Analytics從OSS清洗數(shù)據(jù)到AnalyticDB: https://yq.aliyun.com/articles/623401
- 使用Data Lake Analytics讀/寫RDS數(shù)據(jù): https://yq.aliyun.com/articles/629046
