Create Table Like Mapping: 基于类型推断的建表方法

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数据

假设我们的 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!