JSON外部表

语法结构

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>',...)];

公共参数

详情请参见基础语法参数说明。

独有参数

注意事项

在创建JSON外部表时，如果某个字段的内容本身是一个嵌套的JSON对象（即该字段对应的值为一个JSON结构体），不能直接将该字段的数据类型定义为STRING或JSON，否则系统无法自动解析其中的子字段。 推荐的两种做法是：

• 将该字段定义为STRING，在查询时结合get_json_object等函数按需提取内部的子字段内容。

• 使用STRUCT类型对该字段进行结构化定义，将JSON对象的各个子字段映射为表中的独立子列。这样可以直接通过字段名.子字段名的方式访问内部数据。

具体操作方法，请参考下文示例二。

示例一：创建、写入及查询JSON外部表

通过内置开源数据解析器创建JSON格式外部表并将数据写入OSS进行查询。

1. 准备数据。

   登录OSS控制台，上传测试数据json2025.txt至OSS Bucket指定目录external-table-test/json/dt=20250521/。具体操作请参见OSS文件上传。

2. 创建JSON格式外部表。

   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. 引入分区数据。当创建的OSS外部表为分区表时，需要额外执行引入分区数据的操作，详情请参见补全OSS外部表分区数据语法。

   -- 引入分区
   MSCK REPAIR TABLE mc_oss_extable_name_json ADD PARTITIONS;

4. 读取JSON外部表。

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   返回结果如下：

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

5. 写入JSON外部表。

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

6. 查看写入数据。

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   返回结果如下：

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

示例二：读取JSON格式的字段值

准备数据

创建JSON数据events.json：

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

登录OSS控制台，上传测试数据至OSS Bucket指定目录external-table-test/json-sturct/。具体操作请参见OSS文件上传。

方式一：创建TEXTFILE外部表，并用get_json_object函数读取字段值

1. 创建TEXTFILE外部表，仅包含一个string类型的列：

   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;      

   返回结果如下：

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

2. 通过get_json_object函数读取a和id字段：

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

   返回结果如下：

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

3. 进一步读取嵌套字段x、y和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;          

   返回结果如下：

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

方式二：创建JSON外部表，用STRUCT类型接收

1. 创建JSON格式外部表，用STRUCT类型接收嵌套字段：

   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. 直接查询表内容：

   SELECT * FROM extable_json_test02;

   返回结果如下：

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

3. 也可以通过get_json_object和TO_JSON函数读取x、y字段：

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

   返回结果如下：

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