创建外部表读写OSS JSON数据及处理嵌套结构-云原生大数据计算服务 MaxCompute-阿里云

本文介绍JSON格式的OSS外部表的创建、读取及写入方法。

适用范围

OSS外部表不支持cluster属性。
单个文件大小不能超过2GB，如果文件过大，建议拆分。
MaxCompute需要与OSS部署在同一地域。

支持数据类型

数据类型	是否支持	数据类型	是否支持
TINYINT		STRING
SMALLINT		DATE
INT		DATETIME
BIGINT		TIMESTAMP
BINARY		TIMESTAMP_NTZ
FLOAT		BOOLEAN
DOUBLE		ARRAY
DECIMAL(precision,scale)		MAP
VARCHAR(n)		STRUCT
CHAR(n)		JSON

创建外部表

语法结构

读取JSON数据时，如果JSON文件中的列数小于外表DDL中的列数，系统会将缺少的列值补充为NULL。反之（大于时），会丢弃超出的列数据。

精简语法结构

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

公共参数

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

独有参数

tblproperties属性参数

property_name	使用场景	说明	property_value	默认值
mcfed.mapreduce.output.fileoutputformat.compress	当需要将TEXTFILE数据文件以压缩方式写入OSS时，请添加该属性。	TEXTFILE压缩属性。配置该参数值为True时，MaxCompute才可以将TEXTFILE数据文件以压缩方式写入OSS，否则不压缩。	True False	False
mcfed.mapreduce.output.fileoutputformat.compress.codec	当需要将TEXTFILE数据文件以压缩方式写入OSS时，请添加该属性。	TEXTFILE压缩属性。设置TEXTFILE数据文件的压缩方式。默认输出`.deflate`压缩的文件说明：只支持`property_value`中的压缩方式。	com.hadoop.compression.lzo.LzoCodec com.hadoop.compression.lzo.LzopCodec org.apache.hadoop.io.compress.SnappyCodec com.aliyun.odps.io.compress.SnappyRawCodec org.apache.hadoop.io.compress.BZip2Codec org.apache.hadoop.io.compress.Lz4Codec org.apache.hadoop.io.compress.DeflateCodec org.apache.hadoop.io.compress.GzipCodec	org.apache.hadoop.io.compress.DeflateCodec
odps.external.data.output.prefix （兼容odps.external.data.prefix）	当需要添加输出文件的自定义前缀名时，请添加该属性。	仅包含数字、字母、下划线（a-z、A-Z、0-9、_）。长度在1-10之间。	符合条件的字符组合，例如`mc_`。	无
odps.external.data.enable.extension	当需要显示输出文件的扩展名时，请添加该属性。	True表示显示输出文件的扩展名，反之不显示扩展名。	True False	False
odps.external.data.output.suffix	当需要添加输出文件的自定义后缀名时，请添加该属性。	仅包含数字、字母、下划线（a-z、A-Z、0-9、_）。	符合条件的字符组合，例如'_hangzhou'。	无
odps.external.data.output.explicit.extension	当需要添加输出文件的自定义扩展名时，请添加该属性。	仅包含数字、字母、下划线（a-z、A-Z、0-9、_）。长度在1-10之间。优先级高于参数`odps.external.data.enable.extension`。	符合条件的字符组合，例如`jsonl`。	无
odps.text.option.bad.row.skipping	当需要跳过OSS数据文件中的脏数据时，请添加该属性。	MaxCompute读取OSS数据文件时，可以选择跳过或者不跳过脏数据。	rigid：一定会执行跳过逻辑，不能被session/project层面的配置覆盖。 flexible：数据层面开启弹性跳过，但也可以被session/project层面的配置覆盖。

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

推荐的两种做法如下，具体操作步骤，请参考下文示例：

将该字段定义为STRING，在查询时结合get_json_object等函数按需提取内部的子字段内容。
使用STRUCT类型对该字段进行结构化定义，将JSON对象的各个子字段映射为表中的独立子列。这样可以直接通过字段名.子字段名的方式访问内部数据。

写入数据

MaxCompute写入数据至OSS的语法，详情请参见将数据写入OSS。

查询分析

SELECT语法，详情请参见读取OSS数据。
优化查询计划，详情请参见查询优化。
BadRowSkipping详情，请参见BadRowSkipping。

BadRowSkipping

当JSON数据中出现脏数据时，可以通过BadRowSkipping功能，设置参数选择跳过或者不跳过报错数据。参数开启与否，不影响理解底层数据格式。

参数配置

表级别参数odps.text.option.bad.row.skipping
- rigid：一定要执行跳过逻辑，不能被session/project层面的配置覆盖。
- flexible：数据层面开启弹性跳过，但也可以被session/project层面的配置覆盖。
session/project级别参数
- odps.sql.unstructured.text.bad.row.skipping，可以覆盖flexible表参数，但不能覆盖rigid表参数。
  - on：主动打开，如果表上未配置，自动打开。
  - off：主动关闭，如果表上配置成flexible，会执行主动关闭，其他情况以表参数为准。
  - <null>/输入不合法时：只看表级别配置行为。
- odps.sql.unstructured.text.bad.row.skipping.debug.num，表示允许打印出错结果到Logview的Stdout中的条数。
  - 最大值只能为1000
  - 值<=0时，表示关闭
  - 输入不合法时，表示关闭

Session级别参数和表层面参数交互关系

tbl property	session flag	result
rigid	on	开，强制开启
	off
	<null>，或者不合法的值，或者不配置该参数值
flexible	on	开
	off	关，被session关闭
	<null>，或者不合法的值，或者不配置该参数值	开
没有配置	on	开，session开启
	off	关
	<null>，或者不合法的值，或者不配置该参数值	关

使用示例

数据准备
将存在部分脏数据的测试数据json_bad_row_skipping.json上传至OSSoss-mc-test/badrow/。

创建JSON外部表

表级别参数和session级别flag不一样，三种情况如下所示。

表参数： odps.text.option.bad.row.skipping = flexible/rigid/<不填>
session flag： odps.sql.unstructured.text.bad.row.skipping = on/off/<不填>

表级别没有配置参数

-- 表级别没有配置参数，该报错就报错，靠session级别flag来控制
CREATE EXTERNAL TABLE test_json_bad_data_skipping_flag
(
  a INT,
  b INT
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored AS textfile
location '<oss://databucketpath>';

表级别参数跳过错误行，Session级别可以关闭

-- 表级别参数跳过错误行，不过session级别的flag可以主动关闭
CREATE EXTERNAL TABLE test_json_bad_data_skipping_flexible
(
  a INT,
  b INT
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored AS textfile
location '<oss://databucketpath>'
tblproperties (
  'odps.text.option.bad.row.skipping' = 'flexible'   -- 弹性开启，session级别可关闭
);

表级别参数跳过错误行，Session级别无法关闭

-- 表级别参数强制开启跳过错误，session级别无法关闭
CREATE EXTERNAL TABLE test_json_bad_data_skipping_rigid
(
  a INT,
  b INT
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored AS textfile
location '<oss://databucketpath>'
tblproperties (
  'odps.text.option.bad.row.skipping' = 'rigid'  -- 强制开启
);

验证查询结果

表级别没有配置参数

-- session级别开启
SET odps.sql.unstructured.text.bad.row.skipping=on;

-- session级别关闭，如果表级别配置为'flexible'，则会被关闭，如果表级别配置'rigid'则不受影响；
SET odps.sql.unstructured.text.bad.row.skipping=off;

-- session级别打印有问题的行数据，最大1000条，小于等于0时，就关闭了错误数据打印
SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;

SELECT * FROM test_json_bad_data_skipping_flag;

返回报错：FAILED: ODPS-0123131:User defined function exception

表级别参数跳过错误行，Session级别可以关闭

-- session级别开启
SET odps.sql.unstructured.text.bad.row.skipping=on;

-- session级别关闭，如果表级别配置为'flexible'，则会被关闭，如果表级别配置'rigid'则不受影响；
SET odps.sql.unstructured.text.bad.row.skipping=off;

-- session级别打印有问题的行数据，最大1000条，小于等于0时，就关闭了错误数据打印
SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;

SELECT * FROM test_json_bad_data_skipping_flexible;

返回报错：FAILED: ODPS-0123131:User defined function exception

表级别参数跳过错误行，Session级别无法关闭

-- session级别开启
SET odps.sql.unstructured.text.bad.row.skipping=on;

-- session级别关闭，如果表级别配置为'flexible'，则会被关闭，如果表级别配置'rigid'则不受影响；
SET odps.sql.unstructured.text.bad.row.skipping=off;

-- session级别打印有问题的行数据，最大1000条，小于等于0时，就关闭了错误数据打印
SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;

SELECT * FROM test_json_bad_data_skipping_rigid;

返回结果如下：

+------------+------------+
| a          | b          | 
+------------+------------+
| 1          | 2          | 
| 15         | 16         | 
+------------+------------+

使用示例

前置准备

已创建MaxCompute项目。
已准备好OSS存储空间（Bucket）、OSS目录。具体操作请参见创建存储空间、管理目录。
由于MaxCompute只在部分地域部署，跨地域的数据连通性可能存在问题，因此建议Bucket与MaxCompute项目所在地域保持一致。
授权
1. 具备访问OSS的权限。阿里云账号（主账号）、RAM用户或RAMRole身份可以访问OSS外部表，授权信息请参见OSS的STS模式授权。
2. 已具备在MaxCompute项目中创建表（CreateTable）的权限。表操作的权限信息请参见MaxCompute权限。

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

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

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

创建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/';

引入分区数据。当创建的OSS外部表为分区表时，需要额外执行引入分区数据的操作，详情请参见补全OSS外部表分区数据语法。
```
-- 引入分区
MSCK REPAIR TABLE mc_oss_extable_name_json ADD PARTITIONS;
```

读取JSON外部表。

SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

返回结果如下：

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

写入JSON外部表。

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

查看写入数据。

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函数读取字段值

创建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"} |
+------------------------------------+

通过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 |
+-------------------+-----+

进一步读取嵌套字段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类型接收

创建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/';

直接查询表内容：

SELECT * FROM extable_json_test02;

返回结果如下：

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

也可以通过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  |
+---+---+-----+

示例三：写出OSS文件设置前后缀和扩展名

为写入OSS的文件自定义前缀为test06_，DDL如下所示：

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 (
-- 设置自定义前缀。
    'odps.external.data.output.prefix'='test06_') 
;

-- 向外表写入数据。
INSERT INTO  <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

写入后生成的文件如下图所示：

为写入OSS的文件自定义后缀为_beijing，DDL如下所示：

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 (
-- 设置自定义后缀。
    'odps.external.data.output.suffix'='_beijing') 
;

-- 向外表写入数据。
INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

写入后生成的文件如下图所示：

为写入OSS的文件自动生成扩展名，DDL如下所示：

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 (
-- 自动生成扩展名。
    'odps.external.data.enable.extension'='true') 
;

-- 向外表写入数据。
INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

写入后生成的文件如下图所示:

为写入OSS的文件自定义扩展名jsonl，DDL如下所示:

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 (
-- 自定义扩展名。
   'odps.external.data.output.explicit.extension'='jsonl') 
;

-- 向外表写入数据。
INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

写入后生成的文件如下图所示：

为写入OSS的文件设置前缀为mc_，后缀为_beijing，扩展名为jsonl，DDL如下所示：

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 (
    -- 自定义前缀。
    'odps.external.data.output.prefix'='mc_', 
    -- 自定义后缀。
    'odps.external.data.output.suffix'='_beijing', 
    -- 自定义扩展名。
    'odps.external.data.output.explicit.extension'='jsonl') 
;  

-- 向外表写入数据。
INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

写入后生成的文件如下图所示：