一文學會Hive解析Json數組(好文收藏)

2022-01-14 五分鐘學大數據

在Hive中會有很多數據是用Json格式來存儲的，如開發人員對APP上的頁面進行埋點時，會將多個欄位存放在一個json數組中，因此數據平臺調用數據時，要對埋點數據進行解析。接下來就聊聊Hive中是如何解析json數據的。

Hive自帶的json解析函數1. get_json_object

語法：get_json_object(json_string, '$.key')

說明：解析json的字符串json_string,返回path指定的內容。如果輸入的json字符串無效，那麼返回NULL。這個函數每次只能返回一個數據項。

select 
get_json_object('{"name":"zhangsan","age":18}','$.name'); 
如果既要解析name欄位，也解析age欄位，則可以這樣寫：
select 
get_json_object('{"name":"zhangsan","age":18}','$.name'),
get_json_object('{"name":"zhangsan","age":18}','$.age');
但是如果要解析的欄位有很多，再這樣寫就太麻煩了，所以就有了 json_tuple 這個函數。
2. json_tuple
語法：json_tuple(json_string, k1, k2 ...)
說明：解析json的字符串json_string,可指定多個json數據中的key，返回對應的value。如果輸入的json字符串無效，那麼返回NULL。
select 
b.name
,b.age
from tableName a lateral view
json_tuple('{"name":"zhangsan","age":18}','name','age') b as name,age;
注意：上面的json_tuple函數中沒有$.
如果在使用json_tuple函數時加上$.就會解析失敗：
select 
b.name
,b.age
from tableName a lateral view
json_tuple('{"name":"zhangsan","age":18}','$.name','$.age') b as name,age;
結果:
欄位全是NULL，所以json_tuple函數不需要加$.了，否則會解析不到。
總結：json_tuple相當於get_json_object的優勢就是一次可以解析多個json欄位。但是如果我們有個json數組，這兩個函數都無法處理。
Hive解析json數組一、嵌套子查詢解析json數組
如果有一個hive表，表中 json_str 欄位的內容如下：
json_str[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]
我們想把這個欄位解析出來，形成如下的結構：
websitenamebaidu.com百度google.com谷歌
要解析這個json數組，僅用上面介紹的兩個函數就解析不出來了，還需用到如下介紹的幾個函數：
explode函數
說明：explode()函數接收一個array或者map類型的數據作為輸入，然後將array或map裡面的元素按照每行的形式輸出，即將hive一列中複雜的array或者map結構拆分成多行顯示，也被稱為列轉行函數。
-- 解析array
hive> select explode(array('A','B','C'));
OK
A
B
C
-- 解析map
hive> select explode(map('A',10,'B',20,'C',30));
OK
A       10
B       20
C       30
regexp_replace函數
語法: regexp_replace(string A, string B, string C)
說明：將字符串A中的符合java正則表達式B的部分替換為C。注意，在有些情況下要使用轉義字符，類似oracle中的regexp_replace函數。
hive> select regexp_replace('foobar', 'oo|ar', ''); 
OK
fb
上述示例將字符串中的 oo 或 ar 替換為''。
有了上述幾個函數，接下來我們來解析json_str欄位的內容：
先將json數組中的元素解析出來，轉化為每行顯示：
hive> SELECT explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'));
OK
{"website":"baidu.com","name":"百度"}
{"website":"google.com","name":"谷歌"}
對上述sql進行簡要說明：
SELECT explode(split(
    regexp_replace(
        regexp_replace(
            '[
                {"website":"baidu.com","name":"百度"},
                {"website":"google.com","name":"谷歌"}
            ]', 
            '\\[|\\]' , ''), 將json數組兩邊的中括號去掉
            
              '\\}\\,\\{' , '\\}\\;\\{'), 將json數組元素之間的逗號換成分號
                
                 '\\;') 以分號作為分隔符(split函數以分號作為分隔)
          );  
為什麼要將json數組元素之間的逗號換成分號？
因為元素內的分隔也是逗號，如果不將元素之間的逗號換掉的話，後面用split函數分隔時也會把元素內的數據給分隔，這不是我們想要的結果。
上步已經把一個json數組轉化為多個json字符串了，接下來結合son_tuple函數來解析json裡面的欄位：
select 
json_tuple(explode(split(
regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) 
, 'website', 'name') ;
執行上述語句，結果報錯了：
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
意思是UDTF函數不能寫在別的函數內，也就是這裡的explode函數不能寫在json_tuple裡面。
既然explode函數不能寫在別的json_tuple裡面，那我們可以用子查詢方式，如下所示：
select json_tuple(json, 'website', 'name') 
from (
select explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) 
as json) t;
執行上述語句，沒有報錯，執行結果如下：
www.baidu.com   百度
google.com      谷歌
二 使用 lateral view 解析json數組
hive表中 goods_id 和 json_str 欄位的內容如下：
goods_idjson_str1,2,3[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
目的：把 goods_id 欄位和 json_str 欄位中的monthSales解析出來。
下面我們就開始解析：
拆分goods_id欄位及將json數組轉化成多個json字符串：
select 
explode(split(goods_id,',')) as good_id,
explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) 
as sale_info 
from tableName;
執行上述語句，結果報錯：
FAILED: SemanticException 3:0 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'sale_info'
意思是用UDTF的時候，SELECT 只支持一個欄位。而上述語句select中有兩個欄位，所以報錯了。
那怎麼辦呢，要解決這個問題，還得再介紹一個hive語法：
lateral view
lateral view用於和split、explode等UDTF一起使用的，能將一行數據拆分成多行數據，在此基礎上可以對拆分的數據進行聚合，lateral view首先為原始表的每行調用UDTF，UDTF會把一行拆分成一行或者多行，lateral view在把結果組合，產生一個支持別名表的虛擬表。
假設我們有一張用戶興趣愛好表 hobbies_table，它有兩列數據，第一列是name，第二列是用戶興趣愛好的id_list，是一個數組，存儲興趣愛好的id值：
nameid_listzhangsan[1,2,3]lisi[3,4,5]
我們要統計所有興趣id在所有用戶中出現的次數：
SELECT name, hobby_id 
FROM hobbies_table 
LATERAL VIEW explode(id_list) tmp_table AS hobby_id;
上述sql執行結果：
namehobby_idzhangsan1zhangsan2zhangsan3lisi3lisi4lisi5
2. 按照hobby_id進行分組聚合即可：
SELECT hobby_id ,count(name) client_num
FROM hobbies_table 
LATERAL VIEW explode(id_list) tmp_table AS hobby_id
GROUP BY hobby_id;
結果：
hobby_idclient_num1121324151
介紹完 lateral view 之後，我們再來解決上面遇到的用UDTF的時候，SELECT 只支持一個欄位的問題：
select good_id,get_json_object(sale_json,'$.monthSales') as monthSales
from tableName 
LATERAL VIEW explode(split(goods_id,','))goods as good_id 
LATERAL VIEW explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) sales as sale_json;
注意：上述語句是三個表笛卡爾積的結果，所以此方式適用於數據量不是很大的情況。
上述語句執行結果如下：
goods_idmonthSales149001209016987249002209026987349003209036987
如果表中還有其他欄位，我們可以根據其他欄位篩選出符合結果的數據。
總結：lateral view通常和UDTF一起出現，為了解決UDTF不允許在select存在多個欄位的問題。
--END--

相關焦點

  • 如何再Apache Hive中解析 Json數組
    如何在 Apache Hive 中解析 Json 數組Hive 內部提供了大量的內置函數用於處理各種類型的需求。從內置的 UDF 可以看到兩個用於解析 Json的函數：get_json_object 和 json_tuple。
  • 全網最全的SQL解析Json總結
    一、HiveSQL中的Json解析對於從事數倉開發的同學Json解析是進行數倉開發中很很常會遇到的問題
  • 常用Hive函數的學習和總結
    本文代碼較多，需要各位看官耐心學習，可以收藏備查，歡迎補充和討論。由於公眾號對代碼的支持不太友好，您可以在後臺回復「hive函數」獲取本文的PDF版本，方便閱讀。說明：解析json的字符串json_string，返回path指定的內容。
  • 使用JSONObject生成和解析json
    如果想要直觀點看其內容，可以用一些在線的json解析器看，例如：http://www.jsoneditoronline.org/2.2 使用HashMap構建使用HashMap構建json，實際上即先創建好一個HashMap對象並且將數據打包進去，而後在創建JSONObject時將其作為一個參數傳進去。
  • Json解析神器
    更花式高效解析Json有如下json字符串:{    "data":[        {            "name":"張三",            "age":18        },        {
  • Android:用GSON 五招之內搞定任何JSON數組
    因為前幾晚跟好友 xiasuhuei321 探討了一下GSON解析複雜的JSON的時候，能不能只解析源數據中的數組，甚至只解析數組的某一部分。探討了二十分鐘，得出結論：沒用過，不知道。所以今天特地研究了一下，發現真的So Easy！之前想複雜了，學習的過程中，發現有五種方式分別搞定不同情況的JSON數組，也就是今天說的五大招！
  • 細說Golang的JSON解析
    之前一直寫一些動態語言，覺得解析JOSN還是很簡單的，往往只需要幾行代碼就能拿到解析好的JSON對象。Go語言自帶的json包可以讓你在程序中方便的讀取和寫入 JSON 數據。生成JSON場景相對簡單一些，json.Marshal()會根據傳入的結構體生成JSON數據。
  • php解析json格式的文本
    昨天，在我校的高級培訓群裡，有同學諮詢了如何用php解析json格式的文本問題，老師發現這個問題比較典型，在此將相關的知識點總結分享給同學們，希望對其他同學掌握該知識點也能起到一些幫助。同學jsoly提問說，有這樣的一個數組$arr（如下圖所示）想轉換為php的數組，該如何處理。一眼望去就是個json格式的字符串，顯然php裡面不能這樣定義，這樣的定義會報語法錯誤，就像這樣：該同學認為用json格式存儲數據比較簡潔，問直接用json格式定義一個數組該怎麼做。
  • python解析json方法
    東西很簡單，大家一起來學一學。一、Json簡介 JSON(JavaScript Object Notation) 是一種輕量級的數據交換格式。例如下面這樣：{"b": "Hello", "c": null, "a": true}它的值可以是雙引號括起來的字符串（string）、數值(number)、true、false、 null、對象（object）或者數組（array）。這些結構可以嵌套。
  • 最強最全面的Hive SQL開發指南,超四萬字全面解析!
    : regexp_extract(string subject, string pattern, int index)URL解析函數：parse_url(string urlString, string partToExtract [, string keyToExtract])返回值: stringjson解析函數：get_json_object(string json_string
  • Java解析json的一些操作
    對象的使用方法在引入了fastjson的包之後，能夠得到一種類「 JSONObject」，實例化之後稱為json對象JSONObject object = JSONObject.parseObject({\"boolean\":true,\"string\":\"string\",\"list\":[1,2,3],\"int\":2}"); //string
  • 【踩坑】數組轉json後的結果是數組還是對象?
    ，因為最近接口數據寫的比較多，經常遇到這種問題，如果僅是 PHP 裡面看，則可以都是數組...問題：數組轉json，有時是對象，有時是數組？情況一：$arr = [1,2,3];echo json_encode($arr);     $arr = [1=>1,2=>2,3=>3];echo json_encode($arr);此時如果需要返回的是數組，可以這麼處理
  • jq:命令行下解析JSON的神器
    jq '.' example.jsonjson文件中的commit信息都是一個數組，其中一個commit可以使用.[x]操作,這和各個語言的數組操作也一樣。比如:第一個commit為.[0],以零開頭。
  • 數據提取之JSON與JsonPATH
    ,因為大多數數據是不需要的,所以我們需要進行數據解析,常用的數據解析方式有正則表達式,xpath,bs4,這次我們來介紹一下另一個數據解析庫--jsonpath,在此之前我們需要先了解一下什麼是json。
  • (實用篇)PHP JSON數組與對象的理解
    因此，前後端相關人員先對PHP的json_encode函數原理有必要的了解是最重要的一個環節。PHP中的array是個萬能的數據結構，並不像其它語言根據需要的場景會定義很多約束性的數據類型來描述結構，所以PHP程式設計師是很難給客戶端人員講清楚返回的是什麼結構的數據的。也就變成明明是通過PHP的數組數據編碼成json的，但輸出值有時是數組、有時是對象的情況。
  • 【乾貨】Hive常用函數大全
    舉例：hive> select regexp_replace('foobar', 'oo|ar', '') from lxw_dual;fb13、正則表達式解析函數：regexp_extract語法: regexp_extract(string subject, string pattern
  • JavaScript-算法和json方法及解析有哪些?
    本節主要內容：數組去重數組排序json概述json方法json解析學習目標：一、數組去重數組去重是指：從數組中找到相同的元素，並將相同的元素刪除。保證數組中的每個元素都是獨一無二的操作。JSON是一門獨立的語言，用於存儲和傳輸數據的格式，通常用於服務端向網頁傳遞數據 。JSON語言採用了JavaScript的語法，這意味著在 JavaScript 中處理 JSON數據不須要任何特殊的 API 或工具包，但是還是需要說明的一點就是：JSON和Javascript是兩門不同的語言。
  • HiveSql基礎函數使用(一)
    一、hive函數1、關係函數2、日期函數3、條件函數4、字符串函數5、統計函數二、hiveQLhive函數1、關係函數等值比較: =語法：A=B。k1=v1&k2=v2#Ref1', 'HOST') from dual; ##返回值為facebook.comjson解析函數：get_json_object語法: get_json_object
  • 程式設計師:JSON、JSONObject 與 JSONArray 簡單講解
    fastjson簡介fastjson 是阿里巴巴的開源JSON解析庫，它可以解析 JSON 格式的字符串，支持將 Java Bean 序列化為 JSON 字符串，也可以從 JSON 字符串反序列化到 JavaBean。
  • hive sql 優化心得
    如何寫好一個hql作為一個數據開發工程師，hive sql是我們必備的技能，可能大家都知道一些基本的優化方法（例如：使用分區、小表join大表、不使用distinct、where條件儘量寫到子查詢裡面減少數據量等等），但是你有沒有想過為什麼？是不是真的對執行效率有提升。