MaxCompute:JSON external tables

Syntax

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS textfile
LOCATION '<oss_location>';

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
STORED AS textfile
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

Common parameters

For more information, see Basic syntax parameters.

Unique parameters

Usage notes

When you create a JSON external table, if a field contains a nested JSON object (the value of the field is a JSON struct), do not define the data type of this field as STRING or JSON. Otherwise, the system cannot automatically parse the subfields. You can use one of the following methods:

• Define the field as STRING. When you query data, you can use functions such as get_json_object to extract the content of the internal subfields as needed.

• You can use the STRUCT type to define the structure of the field. This maps the subfields of the JSON object to separate subcolumns in the table. You can then directly access the internal data using the field_name.subfield_name format.

For more information about these operations, see Example 2.

Example 1: Create, write to, and query a JSON external table

This example shows how to create a JSON-formatted external table using a built-in open-source data parser, write data to OSS, and query the data.

1. Prepare the data.

   Log on to the OSS console and upload the test data file json2025.txt to the external-table-test/json/dt=20250521/ folder in an OSS bucket. For more information, see Upload files.

2. Create a JSON-formatted external table.

   CREATE EXTERNAL TABLE mc_oss_extable_name_json
   (
     action STRING,
     time STRING
   )
   PARTITIONED BY (dt STRING)
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json/';

3. Discover and register partitions. If the OSS external table that you created is a partitioned table, you must run an additional command to register the partitions from your OSS directory. For more information, see Syntax for adding partition data to an OSS external table.

   -- Discover and register partitions.
   MSCK REPAIR TABLE mc_oss_extable_name_json ADD PARTITIONS;

4. Read data from the JSON external table.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   The following result is returned:

   +------------+------------+------------+
   | action     | time       | dt         |
   +------------+------------+------------+
   | Close      | 1469679568 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   +------------+------------+------------+

5. Write data to the JSON external table.

   INSERT INTO mc_oss_extable_name_json PARTITION (dt='20250526') VALUES ('test','1627273823');

6. View the written data.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   The following result is returned:

   +------------+------------+------------+
   | action     | time       | dt         |
   +------------+------------+------------+
   | test       | 1627273823 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   +------------+------------+------------+

Example 2: Read values from JSON-formatted fields

Prepare the data

Create the JSON data file events.json:

{"a":{"x":1, "y":2}, "id":"123"}
{"a":{"x":3, "y":4}, "id":"345"}

Log on to the OSS console and upload the test data to the external-table-test/json-struct/ folder in an OSS bucket. For more information, see Upload files.

Method 1: Create a TEXTFILE external table and use the get_json_object function to read field values

1. Create a TEXTFILE external table that contains only one column of the string type:

   CREATE EXTERNAL TABLE extable_json_test01 (
     col STRING
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n'
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json_struct/';  
   
   SELECT * FROM extable_json_test01;      

   The following result is returned:

   +------------------------------------+
   |               col                  |
   +------------------------------------+
   | {"a": {"x": 1, "y": 2},"id":"123"} |
   | {"a": {"x": 3, "y": 4},"id":"345"} |
   +------------------------------------+

2. You can use the get_json_object function to read the a and id fields:

   SELECT 
       get_json_object(col, '$.a') AS a,
       get_json_object(col, '$.id') AS id
   FROM extable_json_test01;         

   The following result is returned:

   +-------------------+-----+
   |        a          | id  |
   +-------------------+-----+
   | {"x":1,"y":2}     | 123 |
   | {"x":3,"y":4}     | 345 |
   +-------------------+-----+

3. You can read the nested fields x, y, and id:

   SELECT 
       get_json_object(get_json_object(col,'$.a'),'$.x') AS x,
       get_json_object(get_json_object(col,'$.a'),'$.y') AS y,
       get_json_object(col,'$.id') AS id
   FROM extable_json_test01;          

   The following result is returned:

   +---+---+-----+
   | x | y | id  |
   +---+---+-----+
   | 1 | 2 |123  |
   | 3 | 4 |345  |
   +---+---+-----+       

Method 2: Create a JSON external table and use the STRUCT type to define the data structure

1. Create a JSON-formatted external table and use the STRUCT type to define nested fields:

   CREATE EXTERNAL TABLE extable_json_test02
   (
     a STRUCT<x: BIGINT, y: BIGINT>,
     id STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json_struct/';          

2. You can directly query the table content:

   SELECT * FROM extable_json_test02;

   The following result is returned:

   +----------+-----+
   |    a     | id  |
   +----------+-----+
   | {x:1, y:2}|123 |
   | {x:3, y:4}|345 |
   +----------+-----+

3. You can also use the get_json_object and TO_JSON functions to read the x and y fields:

   SELECT 
       get_json_object(TO_JSON(a), '$.x') AS x,
       get_json_object(TO_JSON(a), '$.y') AS y,
       id
   FROM extable_json_test02;         

   The following result is returned:

   +---+---+-----+
   | x | y | id  |
   +---+---+-----+
   | 1 | 2 |123  |
   | 3 | 4 |345  |
   +---+---+-----+       

Example 3: Set prefixes, suffixes, and extensions for output OSS files

1. Set the custom prefix of the output OSS file to test06_. The DDL statement is as follows:

   CREATE EXTERNAL TABLE  <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Set a custom prefix.
       'odps.external.data.output.prefix'='test06_') 
   ;
   
   -- Write data to the external table.
   INSERT INTO  <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   The following figure shows the generated file.

   

2. Set the custom suffix of the output OSS file to _beijing. The DDL statement is as follows:

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Set a custom suffix.
       'odps.external.data.output.suffix'='_beijing') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   The following figure shows the generated file.

   

3. An extension is automatically generated for the output OSS file. The DDL statement is as follows:

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Automatically generate an extension.
       'odps.external.data.enable.extension'='true') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   The figure below shows the generated file.

4. Set the custom extension of the output OSS file to jsonl. The DDL statement is as follows:

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- Set a custom extension.
      'odps.external.data.output.explicit.extension'='jsonl') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   The following figure shows the generated file.

   

5. Set the prefix of the output OSS file to mc_, the suffix to _beijing, and the extension to jsonl. The DDL statement is as follows:

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
       -- Set a custom prefix.
       'odps.external.data.output.prefix'='mc_', 
       -- Set a custom suffix.
       'odps.external.data.output.suffix'='_beijing', 
       -- Set a custom extension.
       'odps.external.data.output.explicit.extension'='jsonl') 
   ;  
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   The following figure shows the generated file.