0. Data Lake Analytics（简称DLA）介绍

数据湖（Data Lake）是时下大数据行业热门的概念：https://en.wikipedia.org/wiki/Data_lake。基于数据湖做分析，可以不用做任何ETL、数据搬迁等前置过程，实现跨各种异构数据源进行大数据关联分析，从而极大的节省成本和提升用户体验。关于Data Lake的概念。

终于，阿里云现在也有了自己的数据湖分析产品：https://www.aliyun.com/product/datalakeanalytics
可以点击申请使用（目前公测阶段还属于邀测模式），体验本教程分析OTS数据之旅。
产品文档：https://help.aliyun.com/product/70174.html

1. ETL介绍

ETL（https://en.wikipedia.org/wiki/Extract,_transform,_load）就是Extract、Transfrom、Load即抽取、转换、加载，是传统数仓和大数据的重要工具。

抽取：就是从源系统抽取需要的数据，这些源系统是同构或异构的：比如Excel表格、XML文件、关系型数据库。
转换：源系统的数据按照分析目的，转换成目标系统要求的格式，或者做数据清洗和数据加工。
加载：把转换后的数据装载到目标数据库，作为联机分析、数据挖掘、数据展示的基础。

整个ETL过程就像是在源系统和目标系统之间构建一个管道，数据在这个管道里源源不断的流动。

2. DLA与ETL

Data Placement Optimization（数据摆放优化）是目前云平台上的业务系统的主流架构方向和思路。架构师们会从读写性能、稳定性、强一致性、成本、易用性、开发效率等方面来考量不同存储引擎给业务上带来的好处，从而实现整个业务系统的完美的平衡状态。

而这种跨异构数据源之间的数据搬迁，却不是一件容易的事情。很多ELT工具基本上属于框架级别，需要自己开发不少的辅助工具；同时表达能力也较弱，无法满足很多场景；另外对异构数据源的抽象和兼容性也不是那么完美。

反观DLA，无论从哪方面来看，DLA都完美的契合ETL的需求场景。下图是DLA的简易架构图，DLA一开始就是基于“MPP计算引擎+存储计算分离+弹性高可用+异构数据集源”等架构原则来设计的，支持各种异构数据源读写是DLA的核心目标！

image.png | left | 706x724

通过连接异构数据源来执行select + join + subQuery等逻辑实现Extract，通过Filter+ Project + Aggregation + Sort + Functions等实现数据流转换和映射Transform，而通过insert实现Load，下面是一个例子：

--基本格式
insert into target_table (col1, col2, col3, ....)  --需要导入的列以及列的顺序
select c1, c2, c3, ....                            --需要与导入列的类型兼容，顺序要确认清楚
from ...                         --可以是任何你想要查询的数据目标
where ...


--下面是一个例子
insert into target_table (id, name, age)  
select s1.pk1, s2.name, s1.age            
from source_table1 s1
join source_table2 s2
on s1.sid = s2.sid
where s1.xxx = 'yyy'

下面我们就尝试往不同的数据源导入数据吧。

3. 实际测试（以TableStore:为例）

准备DLA账号（已有测试账号）
- 测试集群：上海region；
- 账号账号：DLA测试账号；
准备两个来源表（两个TPC-H的OSS表，customer和nation），用来做join和数据查询；
准备一个TableStore(https://help.aliyun.com/document_detail/27280.html)的目标表；
执行导入SQL，写入数据后校验结果；

a）两个来源表定义：

mysql> show create database tpch_50x_text;
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Database      | Create Database                                                                                                                                                        |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tpch_50x_text | CREATE DATABASE `tpch_50x_text`
WITH DBPROPERTIES (
    catalog = 'hive',
    location = 'oss://${您的bucket}/datasets/tpch/50x/text_date/'
)
COMMENT '' |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)


mysql> show tables;
+------------+
| Table_Name |
+------------+
| customer   |
| nation     |
+------------+
2 rows in set (0.03 sec)

mysql> show create table customer;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                           |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer | CREATE EXTERNAL TABLE `tpch_50x_text`.`customer` (
    `c_custkey` int,
    `c_name` string,
    `c_address` string,
    `c_nationkey` int,
    `c_phone` string,
    `c_acctbal` double,
    `c_mktsegment` string,
    `c_comment` string
)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '|'
STORED AS `TEXTFILE`
LOCATION 'oss://${您的bucket}/datasets/tpch/50x/text_date/customer_text' |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.90 sec)


mysql> show create table nation;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                    |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| nation     | CREATE EXTERNAL TABLE `tpch_50x_text`.`nation` (
    `n_nationkey` int,
    `n_name` string,
    `n_regionkey` int,
    `n_comment` string
)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '|'
STORED AS `TEXTFILE`
LOCATION 'oss://${您的bucket}/datasets/tpch/50x/text_date/nation_text' |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.73 sec)

b）准备TableStore的库和表

## 建库
mysql> show create database etl_ots_test;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                                                                                                                |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| etl_ots_test | CREATE DATABASE `etl_ots_test`
WITH DBPROPERTIES (
    catalog = 'ots',
    location = 'https://${您的instance}.cn-shanghai.ots-internal.aliyuncs.com',
    instance = '${您的instance}'
)
COMMENT '' |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

## 使用库
mysql> use etl_ots_test;
Database changed

## 建表
mysql> show create table test_insert;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                          |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_insert | CREATE EXTERNAL TABLE `test_insert` (
    `id1_int` int NOT NULL COMMENT '客户id主键',
    `c_address` varchar(20) NULL COMMENT '客户的地址',
    `c_acctbal` double NULL COMMENT '客户的account balance',
    PRIMARY KEY (`id1_int`)
)
COMMENT ''             |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

以下是实际数据的截图：

image.png | left | 747x416

image.png | left | 747x601

c）开始导入数据，确保导入字段顺序和类型兼容性：

## 检查数据，都是空的
mysql> select * from etl_ots_test.test_insert;
Empty set (0.31 sec)

mysql> use tpch_50x_text;
Database changed

## 查询下nation数据，其中CANADA的nationkey是3，后续要找这个数据
mysql> select n_nationkey, n_name from nation;
+-------------+----------------+
| n_nationkey | n_name         |
+-------------+----------------+
|           0 | ALGERIA        |
|           1 | ARGENTINA      |
|           2 | BRAZIL         |
|           3 | CANADA         |
|           4 | EGYPT          |
|           5 | ETHIOPIA       |
|           6 | FRANCE         |
|           7 | GERMANY        |
|           8 | INDIA          |
|           9 | INDONESIA      |
|          10 | IRAN           |
|          11 | IRAQ           |
|          12 | JAPAN          |
|          13 | JORDAN         |
|          14 | KENYA          |
|          15 | MOROCCO        |
|          16 | MOZAMBIQUE     |
|          17 | PERU           |
|          18 | CHINA          |
|          19 | ROMANIA        |
|          20 | SAUDI ARABIA   |
|          21 | VIETNAM        |
|          22 | RUSSIA         |
|          23 | UNITED KINGDOM |
|          24 | UNITED STATES  |
+-------------+----------------+
25 rows in set (0.37 sec)

## 查询下customer数据，我们只关注nationkey=3以及c_mktsegment='BUILDING'的数据
mysql> select count(*) from customer where c_nationkey = 3 and c_mktsegment = 'BUILDING';
+----------+
| count(*) |
+----------+
|    60350 |
+----------+
1 row in set (0.66 sec)

## 查询下customer数据，我们只关注nationkey=3以及c_mktsegment='BUILDING'的数据
mysql> select * from customer where c_nationkey = 3 and c_mktsegment = 'BUILDING' order by c_custkey limit 3;
+-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
| c_custkey | c_name             | c_address               | c_nationkey | c_phone         | c_acctbal | c_mktsegment | c_comment                                                                                          |
+-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
|        13 | Customer#000000013 | nsXQu0oVjD7PM659uC3SRSp |           3 | 13-761-547-5974 |   3857.34 | BUILDING     | ounts sleep carefully after the close frays. carefully bold notornis use ironic requests. blithely |
|        27 | Customer#000000027 | IS8GIyxpBrLpMT0u7       |           3 | 13-137-193-2709 |   5679.84 | BUILDING     |  about the carefully ironic pinto beans. accoun                                                    |
|        40 | Customer#000000040 | gOnGWAyhSV1ofv          |           3 | 13-652-915-8939 |    1335.3 | BUILDING     | rges impress after the slyly ironic courts. foxes are. blithely                                    |
+-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
3 rows in set (0.78 sec)

导入之前我们想清楚需求：把国家是'CANADA'的，客户的market segmentation为'BUILDING'的客户找到，然后对c_custkey排序，选择前10条数据，然后选择他们的c_custkey、c_address、c_acctbal三列，清晰到OTS的test_insert表中，以备后续使用。

##先查询下数据，看看有几条数据
mysql> select c.c_custkey, c.c_address, c.c_acctbal 
    -> from tpch_50x_text.customer c
    -> join tpch_50x_text.nation n 
    -> on c.c_nationkey = n.n_nationkey
    -> where n.n_name = 'CANADA' 
    -> and c.c_mktsegment = 'BUILDING' 
    -> order by c.c_custkey
    -> limit 10;
+-----------+--------------------------------+-----------+
| c_custkey | c_address                      | c_acctbal |
+-----------+--------------------------------+-----------+
|        13 | nsXQu0oVjD7PM659uC3SRSp        |   3857.34 |
|        27 | IS8GIyxpBrLpMT0u7              |   5679.84 |
|        40 | gOnGWAyhSV1ofv                 |    1335.3 |
|        64 | MbCeGY20kaKK3oalJD,OT          |   -646.64 |
|       255 | I8Wz9sJBZTnEFG08lhcbfTZq3S     |   3196.07 |
|       430 | s2yfPEGGOqHfgkVSs5Rs6 qh,SuVmR |   7905.17 |
|       726 | 4w7DOLtN9Hy,xzZMR              |   6253.81 |
|       905 | f iyVEgCU2lZZPCebx5bGp5        |   -600.73 |
|      1312 | f5zgMB4MHLMSHaX0tDduHAmVd4     |    9459.5 |
|      1358 | t23gsl4TdVXqTZha DioEHIq5w7y   |   5149.23 |
+-----------+--------------------------------+-----------+
10 rows in set (1.09 sec)


##开始导入
mysql> insert into etl_ots_test.test_insert (id1_int ,c_address, c_acctbal)
    -> select c.c_custkey, c.c_address, c.c_acctbal 
    -> from tpch_50x_text.customer c
    -> join tpch_50x_text.nation n 
    -> on c.c_nationkey = n.n_nationkey
    -> where n.n_name = 'CANADA' 
    -> and c.c_mktsegment = 'BUILDING' 
    -> order by c.c_custkey
    -> limit 10;
+------+
| rows |
+------+
|   10 |
+------+
1 row in set (2.14 sec)

## 验证结果，没有问题：
mysql> select * from etl_ots_test.test_insert;
+---------+--------------------------------+-----------+
| id1_int | c_address                      | c_acctbal |
+---------+--------------------------------+-----------+
|      13 | nsXQu0oVjD7PM659uC3SRSp        |   3857.34 |
|      27 | IS8GIyxpBrLpMT0u7              |   5679.84 |
|      40 | gOnGWAyhSV1ofv                 |    1335.3 |
|      64 | MbCeGY20kaKK3oalJD,OT          |   -646.64 |
|     255 | I8Wz9sJBZTnEFG08lhcbfTZq3S     |   3196.07 |
|     430 | s2yfPEGGOqHfgkVSs5Rs6 qh,SuVmR |   7905.17 |
|     726 | 4w7DOLtN9Hy,xzZMR              |   6253.81 |
|     905 | f iyVEgCU2lZZPCebx5bGp5        |   -600.73 |
|    1312 | f5zgMB4MHLMSHaX0tDduHAmVd4     |    9459.5 |
|    1358 | t23gsl4TdVXqTZha DioEHIq5w7y   |   5149.23 |
+---------+--------------------------------+-----------+
10 rows in set (0.27 sec)

d）注意点：

虽然有ETL工具快速导入导出，但也有些问题需要注意的，比如：

如果导入任务时间太长，请走异步模式，否则连接断开可能会影响任务正常运行；
TableStore目前的insert是根据主键覆盖，主键不会去重判断的，请务必不能对你正常的数据表做插入；
目前DLA和TableStore的事务能力还不够，可能会出现中断，已导入的数据不会清楚，需要自行清理；
列的个数和列的类型，需要自己对齐保障，否则会报错；

4. 其他数据源导入

整个过程是不是很简单？是不是想要导入其他场景的数据源？对DLA而言，底层任何数据源都以相同方式处理，只要确保其他数据源的库、表在DLA中正常创建，就可以正常的读写，实现ETL啦！赶紧试试吧！

其他相关的文档：

使用Data Lake Analytics从OSS清洗数据到AnalyticDB：https://yq.aliyun.com/articles/623401
DLA相关技术文档：https://yq.aliyun.com/search?q=DLA&type=ARTICLE
Data Lake Analytics使用场景：https://help.aliyun.com/document_detail/70380.html
OLAP on TableStore——基于Data Lake Analytics的Serverless SQL大数据分析https://yq.aliyun.com/articles/618501
使用Data Lake Analytics 分析OSS数据：https://help.aliyun.com/document_detail/70387.html
Data Lake Analytics数据库的连接方式：https://help.aliyun.com/document_detail/71074.htm
Data Lake Analytics分析RDS数据：https://yq.aliyun.com/articles/629046

Data Lake Analytics，大数据的ETL神器！

0. Data Lake Analytics（简称DLA）介绍

1. ETL介绍

2. DLA与ETL

3. 实际测试（以TableStore:为例）

a）两个来源表定义：

b）准备TableStore的库和表

c）开始导入数据，确保导入字段顺序和类型兼容性：

d）注意点：

4. 其他数据源导入

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Data Lake Analytics，大数据的ETL神器！

0. Data Lake Analytics（简称DLA）介绍

1. ETL介绍

2. DLA与ETL

3. 实际测试（以TableStore:为例）

a）两个来源表定义：

b）准备TableStore的库和表

c）开始导入数据，确保导入字段顺序和类型兼容性：

d）注意点：

4. 其他数据源导入

热门文章

最新文章

相关课程

相关电子书

相关实验场景