作者：王華峰阿裡雲智能計算平台團隊

半結構化資料得益于其本身的易用性以及強大的表達能力，使得半結構化資料的使用場景非常廣泛。本文将為大家介紹Hologres JSON半結構化資料的極緻分析性能實作原理。

近年來，随着移動端應用的普及，應用埋點、使用者标簽計算等場景開始誕生，為了更好的支撐這類場景，越來越多的大資料系統開始使用半結構化JSON格式來存儲此類資料，以獲得更加靈活的開發和處理。

Hologres是阿裡雲自研的雲原生一站式實時數倉，支援PB級資料多元分析（OLAP）以及高并發低延遲的線上資料服務（Serving），在對半結構化資料分析場景，Hologres持續優化技術能力，從最開始支援JSONB類型，到支援JSONB GIN索引，再到1.3版本支援JSONB列存，在不犧牲使用靈活性的前提下，提升JSONB資料的查詢性能，同時也降低存儲成本。JSONB列存也在阿裡集團内部多個核心業務使用，其中穩定支撐搜尋事業部2022年雙11大促，曆經生産考驗，查詢性能提升400%，存儲下降50%。

通過本文，我們将會揭秘Hologres JSONB半結構化資料的技術原理，實作JSON半結構資料的極緻分析性能。

一、什麼是半結構化資料

介紹什麼是半結構資料之前，我們首先明确下什麼是結構化資料。結構化資料可以了解成在關系型資料庫（RDBMS）中的一張表，每張表都有明确嚴格的結構定義，比如包含哪些列，每列的資料類型是怎樣的，存儲的資料必須嚴格遵循表結構的定義。

相對應的，半結構化資料就是非固定結構的、經常變化的，且一般是自描述的，資料的結構和内容混雜在一起，最典型的例子就是JSON格式資料。JSON有标準的格式定義，其主要由對象（Object）和數組構成（Array），對象中存儲的是鍵值對，其中鍵隻能是字元串，值可以是字元串、數組、布爾值、Null值、對象或者數組，數組中可以存放任意多個值。

以下就是一個簡單的JSON執行個體，相信大家都很熟悉：

{"user_name": "Adam", "age": 18, "phone_number": [123456, 567890]}

Hologres目前正是通過支援JSON資料類型來提供半結構化資料的能力，為了相容Postgres生态，我們支援Postgres的JSON/JSONB這兩種原生類型，其中JSON類型實際以TEXT格式進行存儲，而JSONB類型存儲的是解析過後的二進制，因為查詢時不需要再解析，是以JSONB在處理時會快很多，下文提到的Hologres半結構化資料方案的很多内部優化都是依托JSONB類型完成的。

二、我們為什麼需要半結構化資料？

半結構化資料得益于其本身的易用性以及強大的表達能力，使得半結構化資料的使用場景非常廣泛。

對于數倉來說，每當上遊的資料格式有變更時，比如變更資料類型、增删字段，數倉中的強Schema格式的表，必須進行相應的表結構演進（Schema Evoluation）來适配上遊的資料，比如需要執行DDL進行加列或者删列，甚至中間的實時資料ETL作業也需要進行适配改動并重新上線。

在有頻繁Schema Evoluation的場景的時候，如何保證資料的品質是個很大的挑戰，同時維護和管理表結構，對于資料開發人員來說也是一項瑣碎且麻煩的工作。

而半結構化資料則天然支援Schema Evoluation，上遊業務的變更，隻需要在JSON列資料中進行增删相應的字段，無需對數倉中的表做任何DDL就能完成，也能對中間的ETL作業做到透明，這樣就能大大降低維護和管理表結構的成本。

三、傳統數倉的半結構化資料解決方案

數倉在處理半結構化資料的時候，衡量一個解決方案好壞的核心考量主要有兩點：

能否保持半結構化資料的易用性和靈活性
能否實作高效的查詢性能

而傳統的解決方案常常是顧此失彼，沒法做到“熊掌”與“魚”的兼得。常見的JSON資料處理方式有2種，以下方案都以JSON資料為例，假設我們有如下JSON資料：

方案1: 數倉直接存儲原始JSON資料

一種最直覺的方案就是将原始JSON資料存成單獨的一列，以Hive為例：

在存儲層，這張Hive表的資料也是以一個完整的JSON值作為最小的存儲粒度在磁盤上連續存儲：

之後使用相關的JSON函數進行查詢，比如查詢所有年齡大于20的使用者數：

SELECT COUNT(1) FROM tbl WHERE cast(get_json_object(json_data, '$.age') as int) > 20;

抽象成下面的流程：

上遊直接寫入JSON類型到Hologres，中間不經過處理，應用層查詢時，再去解析需要的資料。

這種處理方式：

優點是：JSON則天然支援Schema Evoluation，上遊業務的變更，隻需要在JSON列資料中進行增删相應的字段，無需對數倉中的表做任何DDL就能完成，也能對中間的ETL作業做到透明，最大程度地保留了半結構化資料的易用性和靈活性，能大大降低維護和管理表結構的成本。

缺點是：應用端查詢時需要選擇合适的處理函數和方法，才能解析到需要的資料，開發較為複雜，如果JSON較複雜，同時查詢性能會有退化，因為每次JSON列的資料參與計算的時候，都需要對JSON資料完整的解析一遍，比如需要抽取出整個JSON中某個字段，那麼查詢引擎執行的時候就要讀出每一行JSON，解析一遍，取出需要的字段再傳回。這中間會涉及大量的IO和計算，而需要的可能隻是JSON資料成百上千字段當中的一個字段，這中間的大量IO和計算都是浪費的。

方案2: 加工成寬表

既然JSON查詢時的解析開銷很大，那就把解析前置在資料加工鍊路中，于是另外一種做法就是把JSON拍平成了一張寬表：

相應的抽象出來的流程如下：

上遊是JSON格式，在導入時，将JSON進行解析，比如常見的通過Flink的JSON_VALUE函數解析，然後打寬成一張大寬表，再寫入至Hologres，對于上層應用，直接查詢Hologres中已經解析好的列。

對于這種處理方法：

優點是：寫入Hologres時，因為是普通列寫入，是以寫入性能會更好，同時在查詢側，不需要對JSON資料進行解析，查詢性能也會更好。

缺點是：每當上遊的資料格式有變更時，比如變更資料類型、增删字段、執行DDL進行加列或者删列，中間的實時資料ETL作業也需要進行适配改動并重新上線，使用非常不靈活，也會額外增加運維和開發負擔。

基于此背景，業界也迫切需要一個既能保持高效的查詢性能，又不犧牲使用靈活性的方案，來應對海量半結構化資料的極緻分析場景。

四、Hologres列式JSON實作方案

為了更好的支援JSON分析場景，Hologres不斷疊代技術能力，在早期版本支援了JSON資料格式和相關解析函數，使用者可以直接寫入JSON類型以及相關的查詢解析。同時1.1版本在查詢層做了JSON相關的優化，有效的提升JSON資料查詢性能，比如支援GIN反向索引，加速JSON資料的過濾，支援表達式下推等，但整體加速場景有限且使用難度較高，于是1.3版本我們做了大量的存儲層優化，通過JSONB列存的方式來實作更好的查詢性能。

4.1 總體方案介紹

經我們觀察，實際使用者的非結構化資料，在一段時間周期内，整體資料的結構都是比較穩定的，通常隻會有有限個數的确定的字段，差別隻是每個字段出現的頻率會有所不同，且每個字段的資料類型也是整體穩定的。

基于以上經驗，Hologres提供的實作方案的核心要點就是，在導入JSON類型資料至Hologres的時候，引擎自動去抽取JSON資料的結構（字段個數，字段類型等），然後在存儲層，将JSON資料轉化成強Schema格式的列式存儲格式的檔案，以此來達到加速查詢的效果，同時對外接口上，依舊保持JSON的語義，真正做到了保持JSON易用性的同時，兼顧了OLAP查詢性能。

以下圖為例，Hologres每張表在同一個Shard上的資料，也是會分檔案存儲的，而且同一個檔案中的資料，通常也是在鄰近的時間點寫入的，是以在JSON場景下，檔案與檔案之間可能會有結構的差異，但單個檔案内的資料能有比較穩定的結構，進而整體上做到JSON資料結構的穩定演進。

4.2 JSON與JSONB

在詳細介紹Hologres JSON列存化實作之前，我們先簡單介紹下Postgres中的JSON和JSONB兩種資料類型的差別。

JSON和JSONB這兩種資料類型在使用者接口上沒有很大的差異，大部分操作符都是相同，主要差別在于存儲格式上的差别：

JSON類型隻會校驗寫入的資料是否符合JSON規範，存儲上直接将JSON原文按照TEXT存儲，無任何優化

JSONB在JSON的基礎上，會對資料進行格式優化，存儲的是對原始JSON資料優化過後的二進制格式，其優化包含但不限于:

去除資料中的備援空格
對相同路徑下的同名字段去重
對JSON資料中的字段進行排序，重新排列組織，加速查詢能力

在函數覆寫上，JSON和JSONB這兩個類型也有些許差别，比如JSON類型無法直接Cast成INT/Float/Numeric等類型，而JSONB則可以，是以整體文法層面JSONB更完整易用。

Hologres的JSON列存化方案，目前的實作主要還是基于JSONB這個資料類型，具體原因下文會講到。

4.3 JSON結構抽取

JSON資料的結構抽取，主要做的是确定JSON資料的格式，包括JSON具體有哪些字段，每個字段對應的資料類型，以此作為底層列存檔案的實際存儲結構。

Hologres資料寫入流程整體是個LSM (The Log-Structured Merge-Tree)架構，當資料寫入到Hologres的一張表的時候，資料首先會寫到記憶體表（MemTable) 中，當一個MemTable滿了以後，将其以異步的方式Flush到檔案系統中（下圖第4步），并初始化一個新的MemTable，同時背景會有任務，不停将Flush到檔案系統的檔案做進一步的合并（Compaction，下圖第5步）。

而JSON資料的結構抽取，也主要發生在Flush和Compaction兩個階段。

4.4 Flush階段

當MemTable Flush時，我們會周遊一次在MemTable中所有JSON資料，記錄下每個JSON中出現過的字段，以及每個字段的資料類型，周遊完成後，就能知道這列JSON資料列存化之後，具體會有哪些列以及每一列的對應類型。

還是以下面的資料為例:

我們就能夠抽取出以下JSON格式：

另外，在周遊JSON的過程中，我們也會進行類型泛化。比如user_id字段某一行資料出現了超過INT類型門檻值的值，我們就會把user_id列的類型泛化成BigINT類型來相容所有資料。

抽取完JSON結構之後，我們就能把MemTable中的資料寫到檔案系統了，JSON列資料會被拆分寫到對應的4列中去。

4.5 Compaction階段

Compaction做的事情就是把多個檔案合并成一個更大的檔案，這裡也涉及到JSON結構的抽取。

與Flush不同的是，由于Compaction的輸入檔案已經對JSON列進行了列存化處理，是以我們在大部分情況下并不需要再完整周遊所有檔案中的JSON資料去抽取結構，而是可以直接通過檔案的Meta資訊就能推導出輸出檔案的JSON格式，隻需要對所有檔案的輸入列取一個并集，并對沖突列的類型進行泛化即可。

通過上述Flush和Compaction階段的JSON資料處理，我們就能将資料在存儲層列式化，便于後續的查詢加速。

4.6 查詢自适應改寫

上文提到，Hologres雖然底層存儲将JSONB資料轉成了列式存儲，但使用者接口還是沿用了原生JSONB的查詢接口，而由于底層JSONB資料格式的改變，如果查詢引擎還是将列式化後的資料當成JSONB類型，查詢勢必會失敗（資料的實際輸入類型和執行計劃的預期輸入類型不一緻），是以這就要求我們的查詢引擎有查詢自适應改寫的能力。

接下來我們以一個簡單的SQL為例子講解查詢過程中涉及到的查詢自适應改寫：

CREATE TABLE TBL(json_data jsonb); --建表DDL
SELECT SUM((json_data->'quantity')::BIGINT) FROM TBL;

在Hologres中，對JSONB類型最常用的兩個操作符就是->和->>

-> 操作符的含義是，根據操作符後面的路徑參數，取出對應的JSONB資料，該操作符的傳回資料類型是JSONB。
->> 操作符的含義是，根據操作符後面的路徑參數，取出對應的JSONB資料，該操作符的傳回資料類型是TEXT。

是以，上面例子的含義就是，讀取json_data這一JSONB列中的quantity字段，并轉成BIGINT類型後，進行SUM聚合運算。

是以在實體執行計劃中，Scan節點就會有上圖中最左邊的表達式樹，根節點代表将JSONB轉換成BIGINT的函數，它的孩子節點表是取出json_data列中的quantity字段。

但實際上底層檔案存儲的是列存化後的資料，已經沒有了json_data這一實體JSON列，是以我們在Scan節點就需要進行自适應的實體執行計劃改寫：

第一步就是進行列裁剪，如果我們發現底層檔案的Meta資訊中含有quantity這一列，我們就可以直接消除->這一表達式計算，得到了上圖中間所示的表達式樹。當然如果我們發現Meta資訊中沒有quantity這一列，那我們就可以直接跳過掃描這個檔案，傳回執行結果，大大提升執行效率。

第二步就是根據檔案Meta資訊判斷quantity這一列的實體存儲類型，當我們發現實際存儲類型和要求Cast的類型目标一緻時，我們就能進一步改寫優化執行計劃，省去了Cast的操作，得到了上圖中最右所示的表達式樹，也就是直接傳回實體存儲的列資料。另外如果實際存儲類型是INT，那麼我們就需要将原始的Cast節點替換成INT到BIGINT的Cast操作，來保證結果的正确性。

那為什麼不直接讓SQL Optimizer把執行計劃一開始就改寫好呢？

原因在于優化器并不知道JSONB列在存儲引擎的真正格式，比如同一列quantity，在檔案A中的類型是INT，在檔案B中的類型是TEXT，是以對于不同檔案的執行計劃可能是不同的，SQL Optimizer無法用一個實體執行計劃表達所有可能的情況，這就要求執行引擎能夠進行自适應的執行計劃改寫。

4.6 髒資料、稀疏資料處理

由于JSON類型的易用性，理論上使用者可以寫入任意符合JSON格式的資料，這也導緻相較于強Schema類型，JSON類型更容易産生髒資料，這就要求Hologres的JSON列式方案要有比較強的魯棒性，能夠容忍髒資料，這裡我們主要讨論兩類問題:：資料類型不一緻的問題以及字段名錯誤導緻的資料稀疏問題。

髒資料

首先如何處理不一緻的資料類型，假設我們現在有以下JSON資料需要列式存儲：

可以看到age列的前兩行資料都是INT類型的，但是到第三行的時候，age列的值就是一個TEXT類型的資料了，這時候我們就會對類型泛化，泛化成我們在上文提到JSONB類型：

我們可以把JSON看做是個遞歸定義的格式，像16、41、"21"這些age字段的值，本身也是一個JSON值（Object類型），是以我們可以進行這樣的類型泛化。這樣泛化之後，之後對于age列的查詢性能會稍弱于沒有髒資料的情況，因為在執行引擎層，無法像上一節提到的，直接略去JSONB的Cast操作，但整體性能還是遠好于沒有JSON列存化的方案的，因為我們還是隻需要讀取age這一列資料，可以省去大量的IO和計算操作。

稀疏資料

我們再來看下如何處理稀疏資料，通常稀疏資料産生的原因是上遊資料生成的邏輯有問題，生成了大量不重複的字段名，比如以下資料：

可以看到每一行都有一個不一樣的字段，且不重複，如果我們選擇抽取key_1，key_2，key_3這三列，那這三列的資料就會非常稀疏，也會導緻整體檔案的列數膨脹的很厲害。

我們選擇将這些稀疏資料單獨抽取到特殊的一列（holo.remaining），該列的類型也是JSONB，我們會把出現頻度低于某個門檻值（可配置）的資料，都存放到這個字段中：

可以認為在remaining列中存儲的就是整個JSON資料的一個子集，這一列并上其他列式化的資料，就能構造成原來完整的一個JSON值。

查詢remaining列時的性能也會稍弱于查詢已經列式化的列，因為存儲的是JSONB，會包含所有稀疏字段，是以查詢時需要在JSONB資料中搜尋指定的字段，這裡有額外的開銷。但因為這一列中存儲的都是稀疏的資料，通常查詢命中remaining列的機率也不會很高，是以可以容忍。

4.7 嵌套與複雜結構處理

上文中給出的JSON執行個體都是比較簡單的扁平化的資料，但實際上含有嵌套結構的JSON資料也是比較常見的，接下來簡單介紹下Hologres是如何處理複雜JSON結構的。

嵌套結構

對于嵌套結構，我們可以把JSON資料看成是一顆樹，資料都存在葉子節點中（沒有複雜嵌套結構的情況下），比如下面這個JSON資料，就會抽取出右圖所示的樹形結構：

因為非葉子節點本身并不存儲資料，是以實際上存儲的時候就可以把上面的樹狀結構拍平得到以下表結構，另外我們的中繼資料會記錄節點的深度資訊，以此來保證拍平的時候不會出現列名歧義或者沖突的情況。

複雜嵌套結構

首先我們需要先明确下目前Hologres抽取JSON結構時，隻會抽取出以下基本類型：

INT
BIGINT
TEXT
INT[]
BIGINT[]
TEXT[]
JSONB

這裡面JSONB類型就是我們嘗試類型泛化後仍舊無法抽取成前面6種基本類型時，作為兜底的類型實作，這當中也包括的複雜嵌套結構，比如下面這行JSON資料就會抽取出右圖所示的結構，可以看到對于descs這個字段，因為是數組裡面嵌套了非基本類型資料，是以這裡類型退化成了JSONB類型。

是以這裡要注意的點就是，對于這類退化成JSONB類型的資料，針對這一列的操作的性能會不如那些抽成基本類型資料的列，但整體性能還是會比非列式JSON方案會好很多，因為JSONB列隻存儲了完整JSON資料的一個子集，查詢這一列涉及到的IO和計算都會小很多。

4.8 列式JSON不适用場景

查詢帶出完整JSON資料

Hologres的列式JSON方案對于大部分使用場景都有比較好的優化效果，主要需要注意的點是，對于查詢結果需要帶出完整JSON列的場景，性能相較于直接存儲原始格式的JSON會有退化，比如以下SQL：

CREATE TABLE TBL(pk int primary key, json_data jsonb); --建表DDLSELECT json_data FROM TBL WHERE pk = 123;SELECT * FROM TBL limit 10;

原因在于底層已經将JSON資料轉成了列式存儲，是以當需要查詢出完整JSON資料的時候，就需要将那些已經列式存儲的資料重新拼裝成原來的JSON格式：

這個步驟就會産生大量的IO以及轉換開銷，如果涉及到的資料量很大，列數又很多，甚至可能成為性能瓶頸，是以這種場景下建議不要開啟列式優化。

極稀疏的JSON資料

上文已經提到，當我們列式化JSON資料遇到稀疏的字段時，我們會将這部分字段合并至一個叫做holo.remaining的特殊列中，以此來避免列數膨脹的問題。

是以如果使用者的JSON資料，包含的都是稀疏字段，比如極端情況下每個字段都隻會出現一次，那麼我們的列式化将不會起效，因為所有字段都是稀疏的，那麼所有字段都會合并至holo.remaining字段，等于沒有進行列式化，這種情況下就不會有查詢性能的提升。

五、Hologres列式JSON方案收益：降本增效

收益1：存儲降本

我們使用了TPCH的資料集來測試Hologres JSON列式方案對于存儲空間的優化效果，具體測試對比方案是将TPCH的表都建成一列JSONB的格式，然後對比開啟列式方案的效果（幾張資料量較小的表略去了）：

--存儲原始Jsonb資料的表
CREATE TABLE CUSTOMER(data jsonb);
CREATE TABLE LINEITEM(data jsonb);
CREATE TABLE ORDERS(data jsonb);
CREATE TABLE PART(data jsonb);
CREATE TABLE PARTSUPP(data jsonb);

--開啟列式Json優化的表
CREATE TABLE CUSTOMER_COLUMNAR(data jsonb);
ALTER TABLE CUSTOMER_COLUMNAR ALTER COLUMN data SET (enable_columnar_type = on);

CREATE TABLE LINEITEM_COLUMNAR(data jsonb);
ALTER TABLE LINEITEM_COLUMNAR ALTER COLUMN data SET (enable_columnar_type = on);

CREATE TABLE ORDERS_COLUMNAR(data jsonb);
ALTER TABLE ORDERS_COLUMNAR ALTER COLUMN data SET (enable_columnar_type = on);

CREATE TABLE PART_COLUMNAR(data jsonb);
ALTER TABLE PART_COLUMNAR ALTER COLUMN data SET (enable_columnar_type = on);

CREATE TABLE PARTSUPP_COLUMNAR(data jsonb);
ALTER TABLE PARTSUPP_COLUMNAR ALTER COLUMN data SET (enable_columnar_type = on);

使用了TPCH 100GB的測試集進行驗證，結果如下：

可以看到，開啟列式JSONB優化後，每張表的存儲空間都有比較顯著的下降，原因在于列式化之後:

1）原來JSON資料中的字段名都不會再存儲了，而隻需要存儲每個字段對應的具體值，比如下面是轉成JSON後CUSTOMER表的一行資料，資料中的c_name、c_phone、c_acctbal等字元串，列式化後都不需存儲。

{"c_name": "Customer#002662050", "c_phone": "23-793-162-6786", "c_acctbal": 4075.57, "c_address": "paJBRFkD N368pMSvGsYivWyRAs", "c_comment": "ly. fluffily even packages along the blithely even deposits should sleep slyly above the", "c_custkey": 2662050, "c_nationkey": 13, "c_mktsegment": "BUILDING"}

2）列式化後每列的資料類型都是一樣的，列式存儲能有比較好的資料壓縮率

這裡要多說一點的是，在某些資料集上我們也觀察到過開啟列式優化後實際存儲空間沒有下降的情況，這種情況通常是由于JSON資料中的字段比較稀疏，列數膨脹比較厲害，且列式化後每一列的類型都是TEXT類型，導緻壓縮效果不好導緻的。是以上述測試隻是一個理論值，實際使用者的資料各種各樣，實際壓縮後的存儲效果還是要以實際情況為準。

收益2：查詢性能提升

得益于底層列式化的存儲格式，對于那些能夠利用到JSON列裁剪的查詢，經我們測試觀察，通常性能都會有數倍的提升，甚至在特定場景下能有十倍以上的性能提升。

這裡我們使用Github的資料集（見文末SQL和DDL附錄）來驗證Hologres JSON列式化方案的查詢提升，該資料集記錄了Github上的各種使用者行為日志，包括發起代碼評審、評論等等，該資料集是一份JSON格式的資料集。我們選用了2015年的總計172309645行的資料，導入到同一個Hologres執行個體後，對比了使用原生JSON類型、原生JSONB類型存儲和開啟列式JSONB優化後的查詢性能：

可以看到，開啟列式JSONB優化後的查詢性能，相較于原始JSONB格式，有了質的提升。但要注意的是，由于資料集的不同，以及查詢模式的不同，性能收益可能會有較大的差異，具體效果還是要以實際情況為準。後續我們也将陸續推出Hologres JSON列式方案在不同場景下的實作案例，以及對應的性能收益。

六、淘寶搜尋推薦A/B實驗場景成功案例

阿裡巴巴搜尋推薦事業部通過Hologres承載了阿裡巴巴集團淘寶、淘寶特價版、餓了麼等多個電商業務的實時數倉場景，包括即席多元分析，A/B Test等。

在搜尋推薦這類業務場景中，會有很多的使用者标簽、商品标簽、賣家标簽和算法桶号等多值屬性，以使用者标簽為例，業務上對使用者的畫像屬性不是一成不變的，業務可能随時需要新增一類屬性進行觀測，如果每次都需要用一個新的字段來存儲新的使用者屬性，那在整個實時鍊路上都會十分低效，在使用列式JSONB之前，使用的是Text數組類型作為多值字段的存儲格式。

該方案已經穩定使用了幾年，但該方案并不是最高效的，無論是存儲成本還是計算性能都有進一步提升的空間，且我們認為，從整個資料模型來說，使用JSONB來存儲各種屬性，才是最直覺自然的方式。

切換成JSONB後，無論是資料還是查詢，都更加直覺且自然了，2022年雙11完成了Hologres列式JSONB方案的遷移，并且在遷移後，無論是存儲成本還是查詢性能，都獲得了非常不錯的收益：

七、總結

Hologres的列式JSON方案，真正做到了在保持JSON易用性和靈活性的同時，兼顧了極緻的OLAP查詢性能，讓使用者能夠在Hologres上充分挖掘半結構化資料，甚至讓Hologres這個一站式實時數倉承擔部分資料湖的能力。我們後續也會繼續不斷優化列式JSON實作，為大家帶來更為極緻的性能，敬請期待。

八、附錄

1）Github資料集: https://www.gharchive.org/

2）查詢性能測試DDL

CREATE TABLE gh_2015(gh_jsonb jsonb);
--開啟列式優化
ALTER TABLE gh_2015 ALTER COLUMN gh_jsonb SET (enable_columnar_type = on);

3）查詢性能測試Query

--Query 1
SELECT COUNT(1) FROM gh_2015 WHERE gh_jsonb->>'type' = 'WatchEvent';

--Query 2
SELECT gh_jsonb->'repo'->>'name', count(1) AS stars FROM gh_2015 WHERE gh_jsonb->>'type' = 'WatchEvent' GROUP BY gh_jsonb->'repo'->>'name' ORDER BY stars DESC LIMIT 50

--Query 3
SELECT to_date((substring((gh_jsonb ->> 'created_at')FROM 1 FOR 8) || '01'), 'YYYY-MM-DD') AS event_month,
       sum(coalesce((gh_jsonb -> 'payload' -> 'issue' ->> 'number'), (gh_jsonb -> 'payload' -> 'pull_request' ->> 'number'),
             (gh_jsonb -> 'payload' ->> 'number'))::int) AS closed
FROM gh_2015
WHERE (gh_jsonb ->> 'type') = 'IssuesEvent'
  AND (gh_jsonb -> 'payload' ->> 'action') = 'closed'
  AND (gh_jsonb -> 'repo' ->> 'id')::bigint = 41986369
GROUP BY 1;

--Query 4
SELECT event_month,
       all_size
FROM
  (SELECT event_month,
          COUNT(*) OVER (PARTITION BY event_month) AS all_size,
                        ROW_NUMBER() OVER (PARTITION BY event_month) AS row_num
   FROM
     (SELECT (gh_jsonb ->> 'type') AS TYPE,
             (gh_jsonb -> 'repo' ->> 'id')::bigint AS repo_id,
             (gh_jsonb -> 'payload' ->> 'action') AS action,
             to_date((substring((gh_jsonb ->> 'created_at')
                                FROM 1
                                FOR 8) || '01'), 'YYYY-MM-DD') AS event_month
      FROM gh_2015) t
   WHERE TYPE = 'PullRequestEvent'
     AND repo_id = 41986369
     AND action = 'opened') sub
WHERE row_num = 1
ORDER BY event_month;

Hologres技術揭秘：JSON半結構化資料的極緻分析性能