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!