Data Lake Analytics 作为云上数据处理的枢纽, 最近加入了一种创新型的, 基于类型推断的建表语法, 能够帮助用户大大减少建表的工作量, 今天带你来体验一下.
Create Table Like Mapping 语法简介
我们通常的建表语句是要求你填写好所有的字段信息的, 比如在 DLA 里面建一个底层映射到 MongoDB 的表的语法是这样的:
- create external table person (
- id int,
- title varchar(127),
- age int,
- create_time timestamp
- );
这里字段比较少可能还不觉得什么, 实际业务里面表的字段往往都是几十上百个字段, 让用户手动敲这么多字段定义是非常耗时的. 而有了 Create Table Like Mapping 的语法, 这个工作量可以大大减少, 它的语法如下:
create external table dla_table like mapping('underlying_data_source_table');
注意这里的最后的 mapping('underlying_data_source_table') , 这里表示的是我们要推断的表是来源于底层 MongoDB/MySQL 等等的, 而不是一个 DLA 的表. 下面我们以 MongoDB 为例来实际使用一下这个功能.
Create Table Like Mapping + MongoDB
首先我们在 DLA 里面创建一个映射到 MongoDB 的库:
- CREATE DATABASE `mongo_test`
- WITH DBPROPERTIES (
- catalog = 'mongodb',
- location = 'mongodb://<your-user-name>:<your-password>@dds-bp1694axxxxxxxx.mongodb.rds.aliyuncs.com:3717,dds-bp1694ayyyyyyyy.mongodb.rds.aliyuncs.com:3717/admin?replicaSet=zzzzz',
- database = 'mongo_test',
- vpc_id = 'vpc-aaaaaaa'
- );
关于如何在 DLA 里面使用 MongoDB 的详情可以参见: 使用 Data Lake Analytics 读 / 写 MongoDB 数据 https://zhuanlan.zhihu.com/p/53138099
假设我们的 MongoDB 数据库里面有一个名字叫 person 的 collection, 它里面的数据长这样:
- {
- "_id": "ObjectId("5c134c3f36d9cf6ad7077043")"
- "id": 1
- "name": "james"
- "age": 10
- "create_time": "ISODate("2018-12-14T06:22:54.369Z")"
- }
那么我们现在要建一个 DLA 映射表用下面的语句:
create external table person like mapping('person');
在这条语句的背后, 我们 DLA 的引擎会自动去 MongoDB 里面捞一条样例数据, 然后对样例数据里面的字段, 字段的类型进行分析, 然后自动产生对应的建表语句, 创建相应的表:
- MySQL> create external table person like mapping('person');
- Query OK, 0 rows affected (1.01 sec)
- MySQL> desc person;
- +-------------+-----------+-----------------+
- | Field | Type | Collation |
- +-------------+-----------+-----------------+
- | age | double | utf8_general_ci |
- | create_time | timestamp | utf8_general_ci |
- | id | double | utf8_general_ci |
- | name | varchar | utf8_general_ci |
- +-------------+-----------+-----------------+
- 4 rows in set (0.02 sec)
这里为了简洁美观, 省略了部分字段.
然后我们就可以通过 DLA 的 person 表对底层 MongoDB 里面的数据进行查询了:
- MySQL> select * from person limit 4;
- +------+-------------------------+------+-------+
- | age | create_time | id | name |
- +------+-------------------------+------+-------+
- | 10.0 | 2018-12-14 14:22:54.369 | 1.0 | james |
- | 20.0 | 2018-12-14 14:23:48.527 | 2.0 | bond |
- | 30.0 | 2018-12-14 14:23:48.962 | 3.0 | lily |
- | 20.0 | 2018-12-14 14:23:49.396 | 4.0 | lucy |
- +------+-------------------------+------+-------+
- 15 rows in set (2.17 sec)
这里我们展示了使用 create table like mapping 进行 MongoDB 的表的创建, DLA 对于其它的存储比如 MySQL, SQLServer, Postgres 也都支持这个功能.
Create Table Like DLA table
上面我们介绍的都是创建跟底层存储结构一样的表结构, 传统数据库其实还支持把一个表的结构复制一份, 然后起一个新名字的 create table like 语法, 我们其实也是支持的, 它的语法如下:
create external dla_table_2 like dla_table_1;
还是以前面我们刚刚建好的 DLA 的表 person 为例:
- MySQL> create external table person_2 like person;
- Query OK, 0 rows affected (0.20 sec)
- MySQL> select * from person_2 limit 1;
- +------+-------------------------+------+-------+
- | age | create_time | id | name |
- +------+-------------------------+------+-------+
- | 10.0 | 2018-12-14 14:22:54.369 | 1.0 | james |
- +------+-------------------------+------+-------+
- 1 row in set (0.72 sec)
总结
基于类型推断的建表 Create Table Like Mapping 语法可以帮助用户大大简化表创建的工作量, 目前支持的数据源有: MySQL, SQLServer, Postgres, MongoDB, PolarDB 等等, 后面我们会拓展到所有的数据源.
Happy DLAing!
来源: https://yq.aliyun.com/articles/699641