隨著軟硬體各方面條件的成熟，資料湖(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>複製程式碼在這篇文章裡面，我會使用 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 :
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資料檔案格式處理大全: yq.aliyun.com/articles/62…
Data Lake Analytics中OSS LOCATION的使用說明: yq.aliyun.com/articles/62…
如何使用Data Lake Analytics建立分割槽表: yq.aliyun.com/articles/62…
基於Data Lake Analytics來分析OTS上的資料: yq.aliyun.com/articles/61…
使用Data Lake Analytics從OSS清洗資料到AnalyticDB: yq.aliyun.com/articles/62…
使用Data Lake Analytics讀/寫RDS資料: yq.aliyun.com/articles/62…
原文連結
一站式開發者服務，海量學習資源0元起！
阿里熱門開源專案、機器學習乾貨、開發者課程/工具、小微專案、移動研發等海量資源；更有開發者福利Kindle、技術圖書幸運抽獎，100%中--》https://www.aliyun.com/acts/product-section-2019/developer?utm_content=g_1000047140