MaxCompute JSON类型使用指南

MaxCompute当前支持JSON数据类型,提高了表中带有JSON类型数据的计算和分析的性能,本文为您介绍JSON类型的使用方法。

JSON类型简介

背景信息

半结构化数据介于结构和非结构化数据之间,数据中有一定的Schema,但是Schema灵活,没有强约束,通常数据的Schema是自描述的。典型的例子就是JSON数据。MaxCompute中已经支持Schema Evolution、JSON STRING或复杂类型内置函数、Lambda等工作来增强SQL对半结构化数据的支持,在这种模型下,系统仍然要求用户将半结构化数据通过规范化的处理后,导入到有Schema的结构化表中。当业务数据变化时,需要用户显式执行Schema Evolution DDL语句对表结构进行修改。image.png

上述模式存在强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 BYGROUP 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

  • member accessor:$.phone,特殊字符可以使用如 $."sf*"

  • wildcard member accessor:$.*

  • element accessor:$[1, 2, 4 to 7]

  • wildcard element accessor:$[*]

mode

可选值为:laxstrict,默认使用lax模式。

  • lax:lax模式包含了wrapper和unwrapper过程。例如'lax $.phones.phonetype'

    以上述JSON数据为例,为您展示以下表达式的结果:

    • $[0] : wrap object [{....}] ,表示访问第0号数据,返回{....}

    • $[1] : wrap object [{....}] ,表示访问第1号数据,返回NULL

    • $.name.* : "name" 下是"Molly" ,期望是object,返回NULL

    • $.name[*] : "name" 下是"Molly" ,期望是array,此时会进行wrap成["Molly"],返回["Molly"]

    • $.phones.phonetype:phones的value是array,会对array进行unwrap成2个object,继续获取object中的phonetype,最终返回["work","cell"]

    • $.phones[*].phonetype:准确获取phonetype的value,返回["work","cell"]

  • strict:strict模式严格要求JSON Path路径与实际的类型一致,否则会返回NULL。例如'strict $.phones.phonetype'

    以上述JSON数据为例,为您展示以下表达式的结果:

    • strict $.phones.phonetype:由于phones的子节点是array,期望是object,返回NULL

    • strict $.address:由于没有address变量,也会返回NULL

重要

目前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   |
+-----+