常用的hive sql - tw511教學網

細節：sql 中有涉及到正則匹配函數的，要注意跳脫符號

因為在不同語言下正則匹配規則是否需要加跳脫符號是不同的，舉例，regexp_replace 函數，在hive sql的正則匹配規則的 \d+ 需要前面給它加上跳脫符號\，而在java中可能不用，在Presto sql 就是不用加跳脫符號\的。

☺ 思路：不用特意去記哪一種語言需要加跳脫符號，哪種語言不需要，只需要記住正則匹配規則，報錯的一個原因，可能是跳脫符號的問題即可。
 regexp_replace(`date`, '\\d+ 小時前', '${DateUtil.addDays(dt, 1)}')

1、拆解 json 欄位/json 解析函數 get_json_object

(1) 語法：get_json_object(string json_string, string path)

json_string：必填。STRING型別。標準的JSON格式物件，格式為{Key:Value, Key:Value,...}。如果遇到英文雙引號（"），需要用兩個反斜槓（\）進行跳脫。如果遇到英文單引號（'），需要用一個反斜槓（\）進行跳脫。
path：必填。STRING型別。以$開頭。

$：表示根節點。

.或['']：表示子節點。MaxCompute支援用這兩種字元解析JSON物件，當JSON的Key本身包含.時，可以用['']來替代。

[]：表示陣列下標，從0開始。

*：返回整個陣列。*不支援跳脫。

(2) 例子：

-- json字串資料如下：
json_string:
{
	"store": {
		"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
         "bicycle":{"price":19.95,"color":"red"} }, 
     "email":"amy@only_for_json_udf_test.net",
     "owner":"amy" 
} 

-- 獲取owner欄位資訊，返回amy。
  select get_json_object(json_string, '$.owner') from json_string;

-- 提取store.fruit欄位第一個陣列資訊，返回{"weight":8,"type":"apple"}。
  select get_json_object(json_string, '$.store.fruit[0]') from json_string;

2、擷取字串

(1) 符合正規表示式方式，擷取字串 regexp_extract

語法：regexp_extract(string subject, string pattern, int index)
抽取字串subject 中符合正規表示式pattern 的第index 個部分的子字串

index是返回結果取表示式的哪一部分

0 表示把整個正規表示式對應的結果全部返回

1 表示返回正規表示式中第一個()對應的結果，以此類推。

select regexp_extract('histry','(i)(.*?)(e)',0);

(2) 按字元位置擷取字串 substr

語法：substr(string|binary A, int start) substr(string|binary A, int start, int len)

substr(title,1,10)

3、替換字串中的字元 regexp_replace

(1) 語法：

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

(2) 作用：

按照正規表示式pattern 將字串INTIAL_STRING 中符合條件的部分替換成REPLACEMENT 指定的字串

(3) 例子：

-- 舉例：替換字元
regexp_replace(get_json_object(map_col,'$.title'), '\n|\t|\r', '') title,-- 去掉空格等特殊符號，以防儲存是出現資料錯行
regexp_replace(get_json_object(map_col,'$.date'),'\/ ', '') `date`, -- 去掉時間前面的/

4、拼接字串 concat/concat_ws

(1) concat 拼接字串

語法：concat_ws(字串1,字串2)

-- 舉例：拼接欄位
concat('https://developer.unity.cn/projects/',get_json_object(map_col,'$.id')) url

(2) concat_ws 帶分割符的拼接字串

語法：concat_ws('分隔符',字串1,字串2)

-- 舉例：帶分割符的拼接欄位
concat_ws('/','https://t.bilibili.com',dynamic_id) note_url

5、時間格式相關

(1) 規定時間輸出格式 date_format

-- 舉例：規定時間輸出格式，預設格式 yyyy-MM-dd HH:mm:ss
date_format(get_json_object(map_col,'$.create_time'),'yyyy-MM-dd HH:00:00')

細節：date_format 無法識別/的時間格式，例如：select date_format('2023/01/17', 'y'); 結果是null

解決：先通過字串替換函數，將/ 替換成 -，然後再使用date_format 函數，獲取指定格式的時間
select date_format(regexp_replace('2023/01/17', '/', '-'), 'yyyy-MM-dd');

(2) 和時間戳相關

unix_timestamp(string date) 獲取當前時間戳

unix_timestamp(string timestame) 輸入的時間戳格式必須為'yyyy-MM-dd HH:mm:ss',如不符合則返回null

unix_timestamp(string date, string pattern) 指定格式將時間字串轉化成時間戳

select unix timestamp('2023-1-6''yyyy-MM-dd');

from_unixtime(bigint unixtime[, string format]) 將時間戳轉成國際協調時間

6、排序/排名/視窗函數 ROW_NUMBER

語法：ROW_NUMBER() OVER(PARTITION BY 分割區的欄位 ORDER BY 升序/降序欄位 [DESC])

-- 舉例：根據標題分割區後根據建立時間降序展示自然數排名
ROW_NUMBER() OVER(PARTITION BY get_json_object(map_col,'$.title') ORDER BY get_json_object(map_col,'$.create_time') DESC)AS rn

7、炸裂函數explode + 側檢視函數LATERAL VIEW

(1) 作用：

炸裂函數，實現將一行轉換成多列，然後側檢視函數進行聚合

(2) 例子：

原資料結果：

試試炸裂函數explode 效果：

SELECT
    explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ","))  genre 
FROM
	ods_crawler_table                         
WHERE
	dt = '2023-02-26'       
AND get_json_object(map_col,'$.code') = 'xxx'

▷ explode(genre) 和其他欄位一起查詢

實際業務，欄位game_name，genre 都要查詢

SELECT
    get_json_object(map_col,'$.game_name') game_name,
    explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ","))  genre 
FROM
	ods_crawler_table                           
WHERE
	dt = '2023-02-26'       
AND get_json_object(map_col,'$.code') = 'xxx'

報錯：UDTF's are not supported outside the SELECT clause, nor nested in expressions

分析：原因是因為這個欄位genre，炸裂之後，轉換成多列(3列)，而game_name 欄位依然是1列，列數不匹配

解決：側檢視(表)的聚合

ods_crawler_table -- 原先的表
LATERAL VIEW -- 聚合（本質上就是笛卡爾乘積）
explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ",")) v -- 炸裂後作為一個表，兩個表聚合之後成v表
as genre -- 是炸裂函數explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ","))的別名


------------------------------------------------------------------------------------------------------------------------
SELECT
    get_json_object(map_col,'$.game_name') game_name,
    genre 
FROM
	ods_crawler_table      
LATERAL VIEW explode(split(regexp_replace(get_json_object(map_col,'$.genre'), '\\[|\\]', ''), ",")) v as genre		
WHERE
	dt = '2023-02-26'       
    AND get_json_object(map_col,'$.code') = 'xxx'

聚合效果：

8、去掉json多餘的欄位 json2map + map_remove + map_values

先將json轉換成map，然後使用方法map_remove刪除，最後使用map_values取出來

-- 舉例：
map_values(map_remove(json2map(map_col),'code','create_time')) AS datas

9、條件判斷，判斷是否為空 nvl、IF

nvl(valueExp1, valueExp2)：根據第一個表示式的值是否為空，不為空則返回第一個表示式的值，若為空則返回第二個表示式的值

nvl(IF(gap>120, null, gap), 0) gap

10、提高查詢效能，相當於臨時表、檢視 with...as

(1) 作用：

with as短語，也叫做子查詢部分，是用來定義一個SQL片斷，該SQL片斷會被整個SQL語句所用到。其中，SQL片段產生的結果集儲存在記憶體中,
後續的sql均可以存取這個結果集，作用與檢視或臨時表類似。

(2) 語法：

with temp as (
    select xx欄位 from xx表
)
select xx欄位 from temp;

(3) 本質:

with...as 子查詢部分，和直接用子查詢效率上沒有什麼區別，只是這種寫法增加了sql可讀性。

(4) 小細節：

with...as 特點：是一次性的，例如下面的例子，定義的"臨時表"temp1，在第一次查詢name 之後，就不可以再檢視id了。

11、型別強轉 cast

(1) 語法：

cast(expr as <type>) 將表示式 expr 的結果轉換為 <type>

Cast(欄位名 as 轉換的型別)

(2) 例子：

舉例1：cast('1' as BIGINT) 將字串 '1' 轉換為其整數表示形式
舉例2：表tableA 有一個時間欄位 release_time：2018-11-03 15:31:26

select cast(release_time as date) as release_time from tableA;

結果：release_time：2018-11-03

如果本文對你有幫助的話記得給一樂點個贊哦，感謝！