MaxCompute当前支持JSON数据类型,提高了表中带有JSON类型数据的计算和分析的性能,本文为您介绍JSON类型的使用方法。
JSON类型简介
背景信息
半结构化数据介于结构和非结构化数据之间,数据中有一定的Schema,但是Schema灵活,没有强约束,通常数据的Schema是自描述的。典型的例子就是JSON数据。MaxCompute中已经支持Schema Evolution、JSON STRING或复杂类型内置函数、Lambda等工作来增强SQL对半结构化数据的支持,在这种模型下,系统仍然要求用户将半结构化数据通过规范化的处理后,导入到有Schema的结构化表中。当业务数据变化时,需要用户显式执行Schema Evolution DDL语句对表结构进行修改。
上述模式存在强Schema约束,无法将半结构化数据快速导入到系统中,数据导入时不符合Schema规范的数据只能丢弃,无法全量保存。针对以上问题,我们设计了一种新的数据类型JSON,既可以支持无强Schema约束的半结构化数据,又能够充分利用列存储的优化,同时满足高灵活性和高性能的要求。
基本原理
JSON数据类型作为一种新的数据类型,使用方法和其他类型相似。我们无需管理Schema信息,插入JSON数据后,由MaxCompute自动进行公共Schema提取并进行优化,尽可能列存以提高性能。以下面测试数据为例:
CREATE TABLE json_table
(
json_val json
);
CREATE TABLE string_table
(
string_val STRING
);
INSERT INTO string_table VALUES
('{"a":1, "b":2}')
,('{"a":"key", "b":2}')
,('{"c":3}');
INSERT INTO json_table
SELECT json_parse(string_val)
FROM string_table;
在写入数据时MaxCompute会自动提取出公共Schema<"a":binary, "b":bigint, "c":bigint>
,当读取数据时可以根据Schema进行列裁剪,减少读的数据,提高效率。例如:
SELECT json_val["b"]
,json_val["c"]
FROM json_table
;
-- 在读表时进行列裁剪只保留b, c变量
+-----+-----+
| _c0 | _c1 |
+-----+-----+
| 2 | NULL |
| 2 | NULL |
| NULL | 3 |
+-----+-----+
对于非公共Schema部分,MaxCompute采用BINARY进行存储,相较于原始STRING可以减少存储空间。同时相比于用户自定义UDF,新的JSON数据类型对于STRING和JSON相互转换效率也有较大提高。
JSON类型使用
当前在新版的MaxCompute项目中,odps.sql.type.json.enable
参数默认为true,而在存量MaxCompute项目中,odps.sql.type.json.enable
参数默认为false。因此,若您是存量MaxCompute项目,在使用JSON数据类型时,需执行set odps.sql.type.json.enable=true;
,开启对JSON类型特性的支持。您可以执行setproject;
代码,确认odps.sql.type.json.enable
的参数值。
使用限制
目前支持的开发工具包括odpscmd客户端,Studio和DataWorks,暂不支持Dataphin等外围生态。如果需要跟外部系统做组合使用时,请先确认后再使用。使用odpscmd客户端和Studio时需要关注以下内容。
使用odpscmd客户端
使用Studio
需要将客户端升级到V0.46.5及以上版本,否则无法使用
desc json_table
命令且无法通过Tunnel下载JSON类型数据。需要将客户端安装路径下的conf\odps_config.ini文件中的参数
use_instance_tunnel
设置为false,否则查询会报错。
Studio只支持查询JSON类型的操作,不支持上传、下载JSON类型数据。
如果表存在其他引擎读取情况,比如Hologres等,目前不支持读取JSON数据类型。
暂不支持对一张表新增JSON列。
暂不支持对JSON类型的比较操作,也不支持对JSON类型进行
ORDER BY
、GROUP BY
或作为JOIN
的key等。目前JSON NUMBER的整数和小数分别使用BIGINT和DOUBLE类型进行存储。当整数部分超出BIGINT范围时会溢出,小数转为DOUBLE时会损失精度。
生成JSON类型数据所用的字符串里不支持UNICODE
\u0000
。Java UDF和Python UDF暂不支持JSON类型。
目前JSON类型不支持Cluster表。
Java SDK V0.44.0以下版本和PyODPS V0.11.4.1以下版本均不支持JSON数据类型。
Delta Table类型的表暂不支持JSON类型。
LITERAL常量
JSON类型完全按照JSON标准定义,支持BOOLEAN、NUMBER、STRING、NULL、ARRAY、OBJECT。其中NUMBER采用BIGINT和DOUBLE存储,超过限制会有精度损失,同时注意json 'null'
和sql null
是不同的。
JSON 'null'
JSON '123'
JSON '123.34'
JSON 'true'
JSON '{"id":123,"name":"MaxCompute"}'
JSON '[12, 34]'
常量必须符合JSON标准定义,比如JSON '{id:123,"name":"MaxCompute"}'
为非法JSON STRING,id
必须包含在""
中。
JSON类型定义
无需指定Schema,像创建基本数据类型一样创建JSON即可。
CREATE TABLE mf_json_table (json_val JSON);
JSON类型数据生成
有多种方式可以生成JSON类型数据:
JSON Literal
insert into mf_json_table values (json '123');
JSON函数
--json_object和json_array是MaxCompute的内置函数 insert into mf_json_table select json_object("key",123, "value", "abc"); select * from mf_json_table; --返回结果 +----------+ | json_val | +----------+ | 123 | | {"key":123,"value":"abc"} | +----------+ insert into mf_json_table select json_array("key",234, "value", "abc"); select * from mf_json_table; --返回结果 +----------+ | json_val | +----------+ | 123 | | ["key",234,"value","abc"] | | {"key":123,"value":"abc"} | +----------+
类型转换
cast转换需要留意与json_parse的区别,具体参见复杂类型函数说明:
insert into mf_json_table select cast("abc" as json); select * from mf_json_table; --返回 +----------+ | json_val | +----------+ | 123 | | "abc" | | ["key",234,"value","abc"] | | {"key":123,"value":"abc"} | +----------+
JSON访问
JSON类型数据可以通过索引方式、json_extract、get_json_object函数访问,返回JSON类型。
索引方式访问
索引访问方式为strict模式,包括下标index访问和fieldName访问。如果JSON Path和实际结构不一致,则返回NULL。
json_val['a'] [0][1]
相当于json_extract(json_val, 'strict $.a[0][1]')
。
--返回123
SELECT v['id']
FROM VALUES (JSON '{"id":123}') as t(v);
--返回12
SELECT v[0]
FROM VALUES (JSON '[12, 34]') as t(v);
--返回1
select v['x']['a'] from values (json '{"x": {"a": 1, "b": 2}}') as t(v);
--返回NULL
SELECT v[0]
FROM VALUES (JSON '{"id":123}') as t(v);
--返回NULL
SELECT v['not_exists']
FROM VALUES (JSON '{"id":123}') as t(v);
JSON函数访问
例如通过json_extract/get_json_object函数访问。
--通过get_json_object函数访问,返回'MaxCompute'
SELECT GET_JSON_OBJECT(v, '$.x.name')
FROM VALUES (JSON '{"x": {"id": 1001, "name": "MaxCompute"}}') as t(v);
--返回结果
+-----+
| _c0 |
+-----+
| MaxCompute |
+-----+
--通过json_extract函数访问,返回JSON 'MaxCompute'
SELECT JSON_EXTRACT(v, '$.x.name')
FROM VALUES (JSON '{"x": {"id": 1001, "name": "MaxCompute"}}') as t(v);
--返回结果
+-----+
| _c0 |
+-----+
| "MaxCompute" |
+-----+
新的JSON类型采用了更为规范的JSON Path解析,与MaxCompute旧函数get_json_object的JSON Path不同,可能存在兼容性问题,因此新的SQL推荐使用json_extract函数,更多JSON内置函数请参见复杂类型函数。
JSON Path规范
JSON Path用于指定JSON中某一节点的位置,方便查找节点、获取想要的数据,常作为JSON函数的参数,新的JSON类型中采用的JSON Path解析器与PostgreSQL一致,属于PostgreSQL的子集。示例如下:
JSON数据:
{ "name": "Molly", "phones": [ { "phonetype": "work", "phone#": "650-506-7000" }, { "phonetype": "cell", "phone#": "650-555-5555" } ] }
JSON Path示例:
$.phones[1]."phone#'
的结果为:"650-555-5555"。
下表中以上述JSON数据为例为您介绍JSON Path的相关规范:
变量 | 访问运算符 |
accessor |
|
mode | 可选值为:lax、strict,默认使用lax模式。
重要 目前lax模式不支持列裁剪优化,strict模式支持。 |
JSON类型实践示例
--若您的项目odps.sql.type.json.enable参数值为false,需执行以下命令
set odps.sql.type.json.enable=true;
create table json_table(json_val json);
create table mf_string_table(string_val string);
insert into mf_string_table values('{"a":1, "b":2}');
insert into json_table select json_parse(string_val)
from mf_string_table
where json_valid(string_val);
select * from json_table where json_val is not null;
--返回结果
+----------+
| json_val |
+----------+
| {"a":1,"b":2} |
+----------+
select json_val['b'] from json_table where json_val is not null;
--返回结果
+-----+
| _c0 |
+-----+
| 2 |
+-----+