基于OSS文件自动推断建表

语法

CREATE EXTERNAL TABLE table_name LIKE MAPPING ('oss://path/to/target/dir')

创建OSS表时，只需要提供表名和目标文件的路径即可完成建表。

前提条件

为了更加方便地演示操作，您需要先通过以下步骤在OSS中准备好测试数据。

1. 开通OSS服务

2. 创建存储空间

3. 上传数据文件

   OSS的my_new_table目录下上传json1.txt、json2.txt、json3.txt三个文件，json1.txt、json2.txt两个文件的结构相同。json1.txt、json2.txt、json3.txt存储的数据分别如下所示。

    {"id": 123, "name": "jack", "c3": "2001-02-03 12:34:56"}
    {"id": 456, "name": "rose", "c3": "1906-04-18 05:12:00"}

    {"id": 789, "name": "tom", "c3": "2001-02-03 12:34:56"}
    {"id": 234, "name": "alice", "c3": "1906-04-18 05:12:00"}

    {"id": 0122, "name": "bond", "address": "hangzhou", "c3":"2012-02-03 12:34:56"}
    {"id": 0133, "name": "saly", "address": "beijing", "c3":"2012-04-18 05:12:00"}

4. 创建OSS Schema

   本文示例中所有文件数据表均使用以下OSS Schema。

    CREATE SCHEMA my_new_table with DBPROPERTIES(
     catalog='oss',
     location= 'oss://bucker-name/my_new_table/'
     );

示例一：针对文件夹中的所有文件创建表

由于json1.txt、json2.txt结构相同，可以通过CREATE TABLE LIKE MAPPING创建一个表，即可读出两个文件中的数据。

创建表：

CREATE EXTERNAL TABLE my_new_table LIKE
MAPPING ('oss://bucker-name/my_new_table/');

查询建表详情：

SHOW CREATE TABLE my_new_table
+------+-------------------+---+------+
CREATE EXTERNAL TABLE `my_new_table`.`my_new_table` (
    `c3` varchar(1024) COMMENT 'from deserializer',
    `id` int COMMENT 'from deserializer',
    `name` varchar(1024) COMMENT 'from deserializer'
)
STORED AS `JSON`
LOCATION 'oss://bucker-name/my_new_table'
TBLPROPERTIES (
    'create.table.like.file' = 'oss://bucket_name/my_new_table/'
)

查询表数据：

SELECT * FROM my_new_table.my_new_table
|----|------|---------------------|
| id | name |         c3          |
| 123| jack | 2001-02-03 12:34:560|
| 456| rose | 1906-04-18 05:12:00 |
| 789| tome | 2001-02-03 12:34:56 |
| 234| alice|1906-04-18 05:12:00  |

示例二：针对指定文件创建表

由于json3.txt的结构和json1.txt、json2.txt结构不同，以下示例针对json3.txt创建表。

创建表：

CREATE EXTERNAL TABLE my_new_table LIKE
MAPPING ('oss://bucker-name/my_new_table/json3.txt');

查询建表详情：

SHOW CREATE TABLE my_new_table
+------+-------------------+---+------+
CREATE EXTERNAL TABLE `my_new_table`.`my_new_table` (
    `address` varchar(1024) COMMENT 'from deserializer',
    `c3` varchar(1024) COMMENT 'from deserializer',
    `id` int COMMENT 'from deserializer',
    `name` varchar(1024) COMMENT 'from deserializer'
)
STORED AS `JSON`
LOCATION 'oss://bucker-name/my_new_table/json3.txt'
TBLPROPERTIES (
    'create.table.like.file' = 'oss://bucker-name/my_new_table/json3.txt'
)

查询表数据：

SELECT * FROM my_new_table.my_new_table
|-----|------|--------|--------------------|
| id  | name | address|      c3            |
| 0122| bond |hangzhou|2012-02-03 12:34:560|
| 0133| saly |beijing |2012-04-18 05:12:00 |