本文将详细介绍JSON格式的OSS外部表的创建、读取及写入方法。
前提条件
已具备访问OSS的权限。阿里云账号(主账号)、RAM用户或RAMRole身份可以访问OSS外部表,授权信息请参见OSS的STS模式授权。
(可选)已准备好OSS存储空间(Bucket)、OSS目录及OSS数据文件。具体操作请参见创建存储空间、管理目录和简单上传。
MaxCompute已支持在OSS侧自动创建目录,对于携带外部表及UDF的SQL语句,您可以通过一条SQL语句执行读写外部表及UDF的操作。原手动创建目录方式仍然支持。
已创建MaxCompute项目。具体操作请参见创建MaxCompute项目。
由于MaxCompute只在部分地域部署,跨地域的数据连通性可能存在问题,因此建议Bucket与MaxCompute项目所在地域保持一致。
已具备在MaxCompute项目中创建表(CreateTable)的权限。表操作的权限信息请参见MaxCompute权限。
使用限制
OSS外部表不支持cluster属性。
单个文件大小不能超过3 GB,如果文件过大,建议拆分。
注意事项
读取JSON数据时,如果JSON文件中的列数小于外表DDL中的列数,系统会将缺少的列值补充为NULL。反之(大于时),会丢弃超出的列数据。
数据类型支持
MaxCompute数据类型详情请参见1.0数据类型版本、2.0数据类型版本。
表示支持,
表示不支持。
数据类型 | 是否支持 | 数据类型 | 是否支持 |
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 |
创建外部表
语法结构
精简语法结构
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,否则不压缩。 |
| False |
mcfed.mapreduce.output.fileoutputformat.compress.codec | 当需要将TEXTFILE数据文件以压缩方式写入OSS时,请添加该属性。 | TEXTFILE压缩属性。设置TEXTFILE数据文件的压缩方式。默认输出 说明:只支持 |
| org.apache.hadoop.io.compress.DeflateCodec |
注意事项
在创建JSON外部表时,如果某个字段的内容本身是一个嵌套的JSON对象(即该字段对应的值为一个JSON结构体),不能直接将该字段的数据类型定义为STRING或JSON,否则系统无法自动解析其中的子字段。 推荐的两种做法是:
将该字段定义为STRING,在查询时结合
get_json_object
等函数按需提取内部的子字段内容。使用STRUCT类型对该字段进行结构化定义,将JSON对象的各个子字段映射为表中的独立子列。这样可以直接通过
字段名.子字段名
的方式访问内部数据。
具体操作方法,请参考下文示例二。
写入数据
MaxCompute写入数据至OSS的语法,详情请参见将数据写入OSS。
查询分析
使用示例
示例一:创建、写入及查询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 | +---+---+-----+