全部产品
云市场

基于OSS文件自动推断建表

更新时间:2019-05-06 16:30:50

语法

  1. 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存储的数据分别如下所示。

    1. {"id": 123, "name": "jack", "c3": "2001-02-03 12:34:56"}
    2. {"id": 456, "name": "rose", "c3": "1906-04-18 05:12:00"}
    1. {"id": 789, "name": "tom", "c3": "2001-02-03 12:34:56"}
    2. {"id": 234, "name": "alice", "c3": "1906-04-18 05:12:00"}
    1. {"id": 0122, "name": "bond", "address": "hangzhou", "c3":"2012-02-03 12:34:56"}
    2. {"id": 0133, "name": "saly", "address": "beijing", "c3":"2012-04-18 05:12:00"}
  4. 创建OSS Schema

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

    1. CREATE SCHEMA my_new_table with DBPROPERTIES(
    2. catalog='oss',
    3. location= 'oss://bucker-name/my_new_table/'
    4. );

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

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

创建表:

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

查询建表详情:

  1. SHOW CREATE TABLE my_new_table
  2. +------+-------------------+---+------+
  3. CREATE EXTERNAL TABLE `my_new_table`.`my_new_table` (
  4. `c3` varchar(1024) COMMENT 'from deserializer',
  5. `id` int COMMENT 'from deserializer',
  6. `name` varchar(1024) COMMENT 'from deserializer'
  7. )
  8. STORED AS `JSON`
  9. LOCATION 'oss://bucker-name/my_new_table'
  10. TBLPROPERTIES (
  11. 'create.table.like.file' = 'oss://bucket_name/my_new_table/'
  12. )

查询表数据:

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

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

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

创建表:

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

查询建表详情:

  1. SHOW CREATE TABLE my_new_table
  2. +------+-------------------+---+------+
  3. CREATE EXTERNAL TABLE `my_new_table`.`my_new_table` (
  4. `address` varchar(1024) COMMENT 'from deserializer',
  5. `c3` varchar(1024) COMMENT 'from deserializer',
  6. `id` int COMMENT 'from deserializer',
  7. `name` varchar(1024) COMMENT 'from deserializer'
  8. )
  9. STORED AS `JSON`
  10. LOCATION 'oss://bucker-name/my_new_table/json3.txt'
  11. TBLPROPERTIES (
  12. 'create.table.like.file' = 'oss://bucker-name/my_new_table/json3.txt'
  13. )

查询表数据:

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