13個實驗帶你玩轉MaxCompute SQL之JSON操作

你好呀，我是Bella醬～

一個主要寫Java和SQL的妹子，這周工作中呢，我想要做一個功能（嗯，做什麼功能自己定），這個功能呢，主要是對某些資料名額稽核，以監測這些名額的值是否正确，即監控資料品質。

背景

這些名額是我在MaxCompute中寫SQL生成的（嗯，我監控我自己），而且這些名額是以JSON的形式全部存放在了一個字段中。為什麼不是一個名額一列來存放資料，而是所有名額全部放在一個字段中呢？一是因為名額種類太多了，幾十種；二是因為名額的種類不是固定的，是可以根據配置動态生成的，即有哪些名額，是不固定的，如果以列的形式存放的話，要能夠動态生成列才行。接觸過MongoDB的同學都知道，它是以document的結構來存放資料的，用document來存放資料的一個很重要的優勢就是列不固定，你往document中扔什麼，它就存放什麼，相比固定列來說，更靈活。是以考慮到我的資料名額的特性，我當初就把我動态生成的所有名額全部都扔在了一個字段中，考慮到Java讀取名額的便捷性，我又把所有名額以及它的值序列化成了JSON的格式。

所有名額以JSON的形式放在一個字段中（假設這個字段叫index_value），爽！确實很爽！

但是對于今日的我來說，不太爽，而且是太不爽了，為什麼呢？因為我要把這些名額反序列，然後再對它們進行各種數學運算。

方案确定

好了，了解完背景，我們回到資料名額稽核這個功能上，考慮這個功能實作細節時的我，第一反應是寫一個UDF，讓MaxCompute SQL調用UDF。UDF的入參設定為index_value（即存放所有名額的那個字段），出參設定為反序列化後，經過各種數學運算的結果值。嗯，想到這個方案時，我的嘴角不自覺上揚，這個實作嘛，很簡單，就是Java的反序列化和普通的邏輯計算，這個誰還不會嘛。但是，很快，腦海中出現了一個小人，她告訴我，“為什麼不看看SQL能不能解決這個問題呢？為什麼又要SQL中調用UDF呢，為什麼不搞點新東西呢？跳出舒适區吧。”

聽從了小人兒的想法，我開始了SQL反序列化Json字元串的探索之旅。果然，功夫不負有心人，I get it！現在呢，我的功能已經實作并且釋出好啦，我們來一起看看MaxCompute SQL如何操作JSON字元串吧！

生成JSON資料

我們先來看下如何生成JSON資料，包含利用MaxCompute官方提供的函數和UDF 2種方式。

MaxCompute官方提供了 TO_JSON 函數來生成JSON格式的字元串，但是這個函數可以支援的場景非常有限。我們先來看下它的指令格式：

to_json(expr)

其中expr為必填項，且僅支援3種格式：MAP、ARRAY、STRUCT類型。

我們來看幾個例子。

map類型

1.要求key-value對必須同時存在，否則運作時将報錯

2.生成的JSON資料中的key和map中的key完全一樣，不會自動轉換大小寫

3.value為null值的key-value對，仍然會正常輸出

SELECT to_json(map('Bella醬_map',100,'MySQL',100,'Java',99,'Redis',98,'geography',60, 'Flink', CAST(NULL AS STRING )));

STRUCT類型

2.生成的JSON資料中的key全部為小寫

3.value為null值的key-value對，不會輸出，自動過濾掉了

SELECT to_json(NAMED_STRUCT('Bella醬_named_struct', 100, 'ES', 99, 'HBase', 98, 'Java', CAST(null AS STRING)));

array類型

1.生成JSON Array格式的資料

SELECT to_json(ARRAY(map('Bella醬_array_map_1', 100, 'ES', 90, 'Java', 60), map('Bella醬_array_map_2', 90, 'C', 80)));

UDF

除了上述3種方式，MaxCompute也提供了UDF的方式來生成JSON，我就是采用這種方式生成的，因為我要多行轉一列，然後這一列的資料格式為JSON。

-- 1.建表
DROP TABLE IF EXISTS student_score ;

CREATE TABLE IF NOT EXISTS student_score 
(
    id BIGINT COMMENT 'id，邏輯主鍵'
    ,student_no BIGINT COMMENT '學号'
    ,student_name STRING COMMENT '姓名'
    ,suject STRING COMMENT '科目'
    ,score BIGINT COMMENT '成績'
)
;

-- 2.插入資料
INSERT OVERWRITE TABLE student_score VALUES 
    (1, 2021073101, 'Bella醬', 'MySQL', 100), 
    (2, 2021073101, 'Bella醬', 'Java', 99), 
    (3, 2021073101, 'Bella醬', 'Redis', 98), 
    (4, 2021073101, 'Bella醬', 'HBase', 97), 
    (5, 2021073101, 'Bella醬', 'geography', 60), 
    (6, 2021073102, '特拉法爾加·羅', 'MySQL', 100), 
    (7, 2021073102, '特拉法爾加·羅', 'Java', 100), 
    (8, 2021073102, '特拉法爾加·羅', 'Redis', 100), 
    (9, 2021073102, '特拉法爾加·羅', 'HBase', 100), 
    (10, 2021073102, '特拉法爾加·羅', 'geography', 100), 
    (11, 2021073103, '索隆', 'MySQL', 95), 
    (12, 2021073103, '索隆', 'Java', 94), 
    (13, 2021073103, '索隆', 'Redis', 93), 
    (14, 2021073103, '索隆', 'HBase', 98), 
    (15, 2021073103, '索隆', 'geography', 20) ;

-- 3.按學生次元來存放資料，所有科目的成績以json的形式存放在一個字段中
DROP TABLE IF EXISTS student_score_json;

CREATE TABLE IF NOT EXISTS student_score_json AS 
SELECT  MAX(id) AS id
        ,student_no
        ,MAX(student_name) AS student_name
        ,GENERATEJSONSTRING(
            WM_CONCAT(',',suject_score)
            ,','
            ,'='
        ) AS suject_score
FROM    (
            SELECT  id
                    ,student_no
                    ,student_name
                    ,suject
                    ,score
                    ,CONCAT_WS('=', suject, score) AS suject_score
            FROM    student_score
        ) a
GROUP BY student_no
;

其中UDF代碼如下：

import com.aliyun.odps.udf.UDF;
import com.google.gson.Gson;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

/**
 * 生成JSON格式字元串
 *
 * @author Bella醬
 * @date 2021/08/01
 */
public class GenerateJsonString extends UDF {

    public String evaluate(String source, String delimiter, String joiner) {
        Map<String, String> map = transferStr2Map(source, delimiter, joiner);
        return new Gson().toJson(map);
    }

    /**
     * @param source    資料源
     * @param delimiter 分隔符
     * @param joiner    連接配接符
     * @return
     */
    private static Map<String, String> transferStr2Map(String source, String delimiter, String joiner) {
        Map<String, String> map = new HashMap<>(128);
        if (StringUtils.isBlank(source)) {
            return map;
        }

        String[] sourceArray = source.split(delimiter);
        for (String item : sourceArray) {
            String[] itemArray = item.split(joiner);
            if (Objects.isNull(itemArray) || itemArray.length == 0) {
                break;
            }

            map.put(itemArray[0], itemArray[1]);
        }

        return map;
    }
}

最終生成的 student_score_json 表中資料如下：

可以看到subject_score字段即JSON格式資料。

好了，了解完MaxCompute SQL生成JSON格式資料的4種方式後，接下來呢，我們将以 student_score_json 表為例，來講解MaxCompute SQL如何解析JSON格式資料。

解析JSON資料生成多列

MaxCompute SQL提供了2種解析JSON字元串的函數，分别是GET_JSON_OBJECT和JSON_TUPLE。

GET_JSON_OBJECT

我們先來看下GET_JSON_OBJECT，指令格式如下：

string get_json_object(string json, string path)

從指令格式可以看出，我們每一次調用get_json_object函數隻能從JSON字元串中提取出一個字段，若JSON串中有N個字段，那我們則要調用N次get_json_object，同樣，讀取了JSON字元串N次。在海量資料的情況下（MaxCompute又偏偏是處理海量資料的），這種行為是非常糟糕的，會将整個資料處理放大N倍，是可能會影響到性能的。

這裡需要注意一點，path要以

開頭，表示根節點，

表示子節點，讀取suject_core中的Java字段的值則應該寫為

$.Java

。

代碼和執行效果如下，當某個path對應的值不存在時，get_json_object函數傳回值為null。

SELECT  id
        ,student_no
        ,student_name
        ,GET_JSON_OBJECT(s.suject_core, "$.Java") AS Java
        ,GET_JSON_OBJECT(s.suject_core, "$.geography") AS geography
        ,GET_JSON_OBJECT(s.suject_core, "$.MySQL") AS MySQL
        ,GET_JSON_OBJECT(s.suject_core, "$.Redis") AS Redis
        ,GET_JSON_OBJECT(s.suject_core, "$.HBase") AS HBase
FROM    student_score_json s
;

如果要讀取JSON Array數組呢？當然也是可以的啦。

資料key[*]

即可讀取數組中所有資料

數組key[數組下标]

即可讀取數組相應下标中存放的JSON字元串，若要進一步讀取JSON字元串中的值，

數組key[數組下标].字段key

即可。

我們一起來看個栗子吧。首先是資料準備。

DROP TABLE IF EXISTS tmp_score_array_demo ;

CREATE TABLE IF NOT EXISTS tmp_score_array_demo 
(
    score_array STRING COMMENT '成績json數組'
)
;

INSERT OVERWRITE TABLE tmp_score_array_demo 
VALUES (to_json(MAP('scores', ARRAY(MAP('array_map_1', 80, 'Flink', 70, 'Redis', 60), 
                                    MAP('array_map_2', 90, 'ES', 70, 'Redis', 60))))) ;

上述腳本準備好的資料是這個樣子的。

1）讀取scores數組的值。

SELECT  GET_JSON_OBJECT(tmp_score_array_demo.score_array, '$.scores[*]')
FROM    tmp_score_array_demo
;

2) 讀取scores數組中第一個元素的值。

SELECT  GET_JSON_OBJECT(tmp_score_array_demo.score_array, '$.scores[0]')
FROM    tmp_score_array_demo
;

3) 讀取scores數組中第一個元素中key為Flink的值

SELECT  GET_JSON_OBJECT(tmp_score_array_demo.score_array, '$.scores[0].Flink')
FROM    tmp_score_array_demo
;

JSON_TUPLE

我們再來看下JSON_TUPLE，指令格式如下：

string json_tuple(string json, string key1, string key2, string key3...)

從指令格式可以看出，即使我們要讀取JSON中多個key的值，也隻需要讀取一次JSON資料就好了。這，不就是我一直在尋找的嗎，激動的我趕快試了下。

SELECT  JSON_TUPLE(
            student_score_json.suject_score
            ,"Java"
            ,"geography"
            ,"MySQL"
            ,"Redis"
            ,"HBase"
        )
FROM    student_score_json
;

好用，确實是非常好用，但是有2個問題。

1.隻有成績，沒有學生資訊，我無法知道2者的對應關系。

2.列名都丢失了，無法知道每一列是哪個科目的成績。

莫慌～MaxCompute還提供了LATERAL VIEW的功能，我們可以配合LATERAL VIEW一起食用，完美解決了上述2個問題。

SELECT  s.id AS id
        ,s.student_no AS student_no
        ,s.student_name AS student_name
        ,a.Java AS Java
        ,a.geography AS geography
        ,a.MySQL AS MySQL
        ,a.Redis AS Redis
        ,a.HBase AS HBase
FROM    student_score_json s
LATERAL VIEW JSON_TUPLE(s.suject_score, "Java","geography","MySQL","Redis","HBase") a AS Java, geography, MySQL, Redis, HBase
;

至此，我想要的效果出來啦！

再配合nvl函數，将上述的

a.Java

之類的都改為

nvl(a.Java, 0)

（如果json中不存在key對應的值，則取預設值0），後面就可以随心所欲的對這些列進行資料運算了。

如果要讀取JSON Array數組呢？當然也是可以的啦。我們還以上文中的tmp_score_array_demo表為例。表中資料如下：

1）讀取scores數組的值

SELECT json_tuple(tmp_score_array_demo.score_array, "scores[*]") FROM tmp_score_array_demo;

2) 讀取scores數組中第一個元素的值

SELECT json_tuple(tmp_score_array_demo.score_array, "scores[0]") FROM tmp_score_array_demo;

SELECT  json_tuple(tmp_score_array_demo.score_array, "scores[0].Flink")
FROM    tmp_score_array_demo
;

好啦，通過上述13個實驗，我們已經把如何生成JSON資料，如何解析JSON資料、JSON Array資料等講解完了。我們今天的文章就到這裡啦，下期見～

13個實驗帶你玩轉MaxCompute SQL之JSON操作

背景

方案确定

生成JSON資料

map類型

STRUCT類型

array類型

UDF

解析JSON資料生成多列

GET_JSON_OBJECT

JSON_TUPLE

繼續閱讀

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

【python】【資料處理】畫多元資料分布圖

mybatis_入門程式Mybatis入門

vue-cli簡介（中文翻譯）

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

sqlServer根據經緯查距離

Ajax發送和擷取json資料到Spring mvc 1.spring mvc後端2.web前段

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method

JSONObject包導入異常 java.lang.NoClassDefFoundErrorweb項目的導入包的問題