Data Lake Analytics - Deprecated:Automatically create tables based on OSS files

Syntax

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

To create an OSS table, you only need to provide the table name and target file path.

Prerequisites

To facilitate the demonstration, complete the following steps to prepare the test data in OSS:

1. Sign up for OSS.

2. Create a bucket.

3. Upload data files.

   Upload the json1.txt, json2.txt, and json3.txt files to the my_new_table directory in OSS. The json1.txt and json2.txt files have the same structure. The data stored in json1.txt, json2.txt, and json3.txt is as follows.

   {"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. Create an OSS schema.

   All tables in the example use the following OSS schema:

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

Example 1: Create a table for all files in the folder

The json1.txt and json2.txt files have the same structure, so you can run CREATE TABLE LIKE MAPPING to create a table for reading data from the two files.

Create a table:

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

View table creation details:

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/'
)

Query table data:

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  |

Example 2: Create a table for a specified file

The json3.txt file has a different structure from json1.txt and json2.txt, so you can create a table for json3.txt only.

Create a table:

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

View table creation details:

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'
)

Query table data:

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 |