331. 提取JSON中的value值
- get_json_object
語(yǔ)法：get_json_object(json_string, ‘$.key’),注意事項(xiàng)：這個(gè)函數(shù)每次只能返回一個(gè)數(shù)據(jù)項(xiàng)
select
get_json_object('{"company":"阿里","age":18}','$.ompany'),
get_json_object('{"company":"拼多多","age":18}','$.age');
- json_tuple
語(yǔ)法：json_tuple(json_string, k1, k2 …)，解析多個(gè)字段不加$hive：函數(shù)：json_tuple處理json數(shù)據(jù)
如下：
hive> select * from json_test;
select * from json_test
OK
1 {"name":"孫先生","carrer":"大數(shù)據(jù)開(kāi)發(fā)工程師","dream":["開(kāi)個(gè)便利店","去外面逛一逛","看本好書(shū)"],"friend":{"friend_1":"MM","friend_2":"NN","friend_3":"BB","friend_4":"VV"}}
2 {"name":"唐女士","carrer":"退休農(nóng)民","dream":["兒子聽(tīng)話","帶孫子"],"friend":{"friend_1":"CC"}}
-- 提取二級(jí)格式下的數(shù)據(jù)(如好友1)
select good_friend_1
from temp_db.json_test
lateral view json_tuple(str,'friend') dd as good_friend
lateral view json_tuple(good_friend,'friend_1') tb as good_friend_1;
查詢結(jié)果：
MM
CC
-- 提取標(biāo)簽中所有的內(nèi)容(沒(méi)有的標(biāo)簽，返回null)
select good_friend_1,good_friend_2,good_friend_3
from temp_db.json_test
lateral view json_tuple(str,'friend') dd as good_friend
lateral view json_tuple(good_friend,'friend_1','friend_2','friend_3') tb as good_friend_1,good_friend_2,good_friend_3;
查詢結(jié)果：
MM NN BB
CC NULL NULL
--dreaming string格式轉(zhuǎn)數(shù)組（string不可以直接轉(zhuǎn)數(shù)組的定血，即使string的數(shù)據(jù)符合數(shù)組的格式）
select regexp_replace(regexp_replace(regexp_replace(dreaming, '\\[', ''),'\\]',''),'\\"','')
from temp_db.json_test
lateral view json_tuple(str,'dream') dd as dreaming;
查詢結(jié)果：
開(kāi)個(gè)便利店,去外面逛一逛,看本好書(shū)
兒子聽(tīng)話,帶孫子
- 嵌套子查詢解析json數(shù)組
[{“website”:“baidu.com”,“name”:“百度”},{“website”:“google.com”,“name”:“谷歌”}]
解析為
website name
baidu.com 百度
google.com 谷歌
SELECT explode(split(
regexp_replace(
regexp_replace(
'[
{"website":"baidu.com","name":"百度"},
{"website":"google.com","name":"谷歌"}
]',
'\\[|\\]' , ''), 將json數(shù)組兩邊的中括號(hào)去掉
'\\}\\,\\{' , '\\}\\;\\{'), 將json數(shù)組元素之間的逗號(hào)換成分號(hào)
'\\;') 以分號(hào)作為分隔符(split函數(shù)以分號(hào)作為分隔)
);
嵌套一層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;
- 使用 lateral view 解析json數(shù)組
lateral view通常和UDTF一起出現(xiàn)岩饼，為了解決UDTF不允許在select存在多個(gè)字段的問(wèn)題
goods_id json_str
1,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”}]
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;
332. 提取JSON中 key值
[{“website”:“baidu.com”,“name”:“百度”},{“website”:“google.com”,“name”:“谷歌”}]
select rn
,val as key
from(
select pos+1 as rn
,val
from(
select regexp_replace(translate('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]'
,'[]{}""','') ,'\,','\:') as str
) t1 lateral view posexplode(split(str,':')) t2 as pos,val
) m
where rn%2=1
提供了一種方法粟关，主要的思路還是正則和炸裂的使用
website:baidu.com:name:百度:website:google.com:name:谷歌
這里有個(gè)難點(diǎn)是怎么獲取奇數(shù)值岩臣，作者使用了posexplode函數(shù)烈评，第一個(gè)值是索引伐蒋，第二是值是value值工三，我在想其實(shí)不用吧 ,--> :
333. 遍歷字符串
- 'a,b,c,d,e,f'
select posexplode(split('a,b,c,d,e,f',','))
- "abcdef"
先根據(jù)字符串長(zhǎng)度生成對(duì)應(yīng)的索引
select posexplode(split(space(length("abcdef")-1),' '))
針對(duì)生成的索引值按行進(jìn)行展開(kāi)
select 'abcdef' as str
,pos --索引
,substr("abcdef",pos+1,1)
,substr("abcdef",0,pos+1)
from(
select posexplode(split(space(length("abcdef")-1),' '))
) t
（1）利用posexplode()函數(shù)生成索引
（2）已知某個(gè)長(zhǎng)度值，根據(jù)長(zhǎng)度值生成索引的方法先鱼。space()函數(shù)及posexplode()函數(shù)
（3）通過(guò)索引值及substr()函數(shù)獲取當(dāng)前索引處的字符串值俭正。
334. 刪除字符串中多余的字符 translate(input, from, to)
input：輸入字符串【集是要被替換的字符串】
from：需要匹配的字符【即需要被替換的字符】，這里一定要注意是字符不是字符串
to ：用哪些字符來(lái)替換被匹配到的字符
- 如果 from 字符串長(zhǎng)度=to的字符串長(zhǎng)度焙畔，如translate(“abcdef-abcdef”,“abcdef”,“123456”);替換不是說(shuō)把"abcdef"替換成"123456"掸读，而是把a(bǔ)替換成1，把b替換成2宏多，把c替換成3儿惫，把d替換成4，e替換成5伸但，f替換成6姥闪。
- 如果 from 字符串長(zhǎng)度>to的字符串長(zhǎng)度 ，例如TRANSLATE(‘a(chǎn)bcdef-abcdef’,‘a(chǎn)dbc’,‘123’) 意思是把 a替換為1砌烁，b替換為2筐喳，c替換為3催式，d替換為空，即刪除掉
- 如果 from里有重復(fù)字符 比如abca避归，1231荣月，重復(fù)的字符a對(duì)應(yīng)to的替換不會(huì)起作用
- from長(zhǎng)度<to的長(zhǎng)度，不報(bào)錯(cuò)但是to里面長(zhǎng)的字符沒(méi)有意義梳毙。
select
TRANSLATE('abcdef-abcdef','abcd','1234'),--1234ef-1234ef
TRANSLATE('abcdef-abcdef','abcd','123'), --123ef-123ef
TRANSLATE ('abcdaabbaaabbb','aa','12'),--1bcd11bb111bbb
TRANSLATE ('abcdaabbaaabbb','a','123')--1bcd11bb111bbb
335. 分離字符串中的字符和數(shù)字 repeat()
- 轉(zhuǎn)換為指定特征字符或特征數(shù)字
select data
,translate(lower(data),'abcdefghijklmnopqrstuvwxyz',repeat('*',26))
,translate(lower(data),'0123456789',repeat('$',10))
from books
- 刪除指定的特征字符
select data
,cast(regexp_replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',repeat('*',26)),'\\*','') as bigint) as book_money
,regexp_replace(translate(lower(data),'0123456789',repeat('$',10)),'\\$','') as book_name
from books
336. 某個(gè)字符是否在字符串中
- 用like正則匹配
- 用locate()函數(shù)判斷
- 用regexp正則匹配哺窄。注意此時(shí)regxp()里面的正則為regexp(’.* 1.*’)
337. HIVE日期函數(shù)大全
【hive 日期函數(shù) 大全】Hive常用日期函數(shù)整理
注意：1) hive 沒(méi)有 to_char函數(shù) 2) HIVE 日期函數(shù)只識(shí)別 年-月-日 不能識(shí)別 年-月 ，所以處理月份的時(shí)候需要特殊處理
1)hive 字符創(chuàng)拼接:
CONCAT(string A, string B…)
SELECT CONCAT('2019','05','11');
2) 字符截取
select substr(add_months(from_unixtime((unix_timestamp('2015-09','yyyy-MM')),'yyyy-MM-dd'),-1),0,7);
SELECT SUBSTR(from_unixtime((unix_timestamp('201509','yyyyMM')),'yyyyMMdd'),0,6);
SELECT SUBSTR(from_unixtime((unix_timestamp('201509','yyyyMM')),'yyyy-MM-dd'),0,7);
hive (felix)> SELECT from_unixtime(unix_timestamp(cast('201509' as string),'yyyyMM'),'yyyy-MM-dd') ;
2015-09-01
Time taken: 0.06 seconds, Fetched: 1 row(s)
hive (felix)> SELECT from_unixtime(unix_timestamp(cast('201509' as string),'yyyyMM'),'yyyy-MM') ;
2015-09
select from_unixtime(unix_timestamp(add_months(from_unixtime((unix_timestamp('201509','yyyyMM')),'yyyy-MM-dd'),-1),'yyyy-MM-dd'),'YYYYMM') time_list
3) 時(shí)間區(qū)間獲取
4) 中英文日期轉(zhuǎn)換
hive (default)> select from_unixtime((unix_timestamp('201509','yyyyMM')),'MMM-YY');
OK
Sep-15
Time taken: 0.06 seconds, Fetched: 1 row(s)
hive (default)> SELECT from_unixtime(unix_timestamp('Sep-19','MMM-YY'),'yyyyMM');
OK
201812
---英文日期大寫(xiě)
hive (default)> select upper(from_unixtime((unix_timestamp('201509','yyyyMM')),'MMM-YY'));
OK
SEP-15
Time taken: 0.062 seconds, Fetched: 1 row(s)
1.unix_timestamp()
返回當(dāng)前時(shí)區(qū)的unix時(shí)間戳
返回類(lèi)型：bigint
hive (default)> SELECT UNIX_TIMESTAMP();
2.from_unixtime(bigint unixtime[,string format])
時(shí)間戳轉(zhuǎn)日期函數(shù)
返回類(lèi)型：string
hive (default)> select from_unixtime(unix_timestamp(),'yyyyMMdd') ;
20160614
3.unix_timestamp(string date)
返回指定日期格式的的時(shí)間戳
返回類(lèi)型：bigint
注意：如果后面只有date參數(shù)账锹，date的形式必須為'yyyy-MM-dd HH:mm:ss'的形式萌业。
hive (default)> select unix_timestamp('2020-05-01');
NULL
hive (default)> select unix_timestamp('2020-05-01 00:00:00');
1464710400
4.unix_timestamp(string date,string pattern)
返回指定日期格式的時(shí)間戳
返回類(lèi)型:bigint
hive (default)> select unix_timestamp('2020-05-01','yyyy-MM-dd');
1449331200
5.to_date(string date)
返回時(shí)間字段中的日期部分 必須是yyyy-MM-dd格式
返回類(lèi)型:string
說(shuō)明: 返回日期時(shí)間字段中的日期部分半沽。只能識(shí)別到 “年-月-日” 級(jí)別的時(shí)間若治，無(wú)法識(shí)別 “年-月” 級(jí)別的時(shí)間续搀。
--1) 注意 使用年月志鞍，to_date 日期是識(shí)別不了的
hive (default)> select to_date('2016-09'); --有問(wèn)題，結(jié)果為null
OK
NULL
Time taken: 0.067 seconds, Fetched: 1 row(s)
hive (default)> select add_months('2020-05-01',-1);
OK
2020-04-01
hive (default)> select to_date('2020-05-01 00:00:00') ;
2020-05-01
hive (default)> select to_date('2020-05-01');
2020-05-01
6.year(string date)
返回時(shí)間字段中的年
返回類(lèi)型:int
hive (default)> select year('2020-05-01 00:00:00') ;
2016
hive (default)> select year('2020-05-01') ;
2016
7.month(string date)
返回時(shí)間字段中的月
返回類(lèi)型:int
hive (default)> select month('2020-05-01') ;
6
hive (default)> select month('2020-05-01');
6
8.day(string date)
返回時(shí)間字段中的天
返回類(lèi)型:int
hive (default)> select day('2020-05-01') ;
1
9搀擂、day：返回日期中的天
select day('2015-04-13 11:32:12');
輸出：13
10寻拂、hour：返回日期中的小時(shí)
select hour('2015-04-13 11:32:12');
輸出：11
11瓶佳、minute：返回日期中的分鐘
select minute('2015-04-13 11:32:12');
輸出：32
12桌粉、second：返回日期中的秒
select second('2015-04-13 11:32:56');
輸出：56
13.weekofyear(string date)
返回時(shí)間字段是本年的第多少周
返回類(lèi)型:int
hive (default)> select weekofyear('2020-05-01') ;
22
14.datediff(string enddate,string begindate)
返回enddate與begindate之間的時(shí)間差的天數(shù)
返回類(lèi)型:int
hive (default)> select datediff('2020-05-01','2016-05-01') ;
31
--DEMO 2
hive (default)> SELECT datediff('2020-05-01 00:00:00','2016-05-01 23:00:00');
OK
31
15.date_add(string date,int days)
返回date增加days天后的日期
返回類(lèi)型:string
hive (default)> select date_add('2020-05-01',15) ;
2020-05-16
16.date_sub(string date,int days)
返回date減少days天后的日期
返回類(lèi)型:string
hive (default)> select date_sub('2020-05-01',15) ;
2016-05-17
17：Hive中取最近30天數(shù)據(jù)
datediff(CURRENT_TIMESTAMP ,gmt_create)<=30
18蒸绩、Hive中 兩個(gè)日期相差多少小時(shí)
select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/3600
輸出：1
19、Hive中 兩個(gè)日期相差多少分鐘
select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/60
輸出：60
20铃肯、hive 計(jì)算某一個(gè)日期屬于星期幾患亿，如2018-05-20 是星期日
SELECT IF(pmod(datediff('2018-05-20', '1920-01-01') - 3, 7)='0', 7, pmod(datediff('2018-05-20', '1920-01-01') - 3, 7))
輸出：7
21、hive返回上個(gè)月第一天和最后一天
--上個(gè)月第一天
select trunc(add_months('2020-05-08',-1),'MM'); --只能識(shí)別 年-月-日
select trunc(add_months(CURRENT_TIMESTAMP,-1),'MM')
hive (default)> select trunc(add_months('2020-05',-1),'MM');
OK
NULL
Time taken: 0.067 seconds, Fetched: 1 row(s)
hive (default)> select trunc(add_months('2020-05-08',-1),'MM');
OK
2020-04-01
Time taken: 0.079 seconds, Fetched: 1 row(s)
hive (default)>
select concat(substr(add_months(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1),1,7),'-01');
338. 時(shí)間序列--補(bǔ)全數(shù)據(jù)
date_id a b c
2014 AB 12 bc
2015 23
2016 d
2017 BC
如何使用最新數(shù)據(jù)補(bǔ)全表格
date_id a b c
2014 AB 12 bc
2015 AB 23 bc
2016 AB 23 d
2017 BC 23 d
select
date_id,
first_value(a) over(partition by aa order by date_id) as a,
first_value(b) over(partition by bb order by date_id) as b,
first_value(c) over(partition by cc order by date_id) as c
from
(
select
date_id,
a,
b,
c,
count(a) over(order by date_id) as aa,
count(b) over(order by date_id) as bb,
count(c) over(order by date_id) as cc
from t20
)tmp1;
339. like押逼，not like步藕，rlike， regexp的區(qū)別和使用詳解
- Rlike功能和like功能大致一樣宴胧，like是后面只支持簡(jiǎn)單表達(dá)式匹配（_%）,而rlike則支持標(biāo)準(zhǔn)正則表達(dá)式語(yǔ)法。所以如果正則表達(dá)式使用熟練的話表锻，建議使用rlike恕齐，功能更加強(qiáng)大。所有的like匹配都可以被替換成rlike瞬逊。反之显歧，則不行。但是注意：like是從頭逐一字符匹配的确镊，是全部匹配士骤，但是rlike則不是,可以從任意部位匹配，而且不是全部匹配蕾域。
- NOT A LIKE B是LIKE的結(jié)果否定拷肌，如果like匹配結(jié)果時(shí)true到旦，則not…like的匹配結(jié)果時(shí)false，反之也是結(jié)果也是相對(duì)巨缘。實(shí)際中也可以使用 A NOT LIKE B添忘，也是LIKE的否定，與 NOT A LIKE B一樣若锁。當(dāng)然前提要排除出現(xiàn)null問(wèn)題搁骑，null值這個(gè)奇葩除外，null的結(jié)果都是null值又固。
- 同理NOT RLIKE 的使用仲器，也是NOT A RLIKE B是對(duì)RLIKE的否定。當(dāng)然前提要排除出現(xiàn)null問(wèn)題仰冠，null值這個(gè)奇葩除外乏冀，null的結(jié)果都是null值。
340. 統(tǒng)計(jì)字符串中字符的個(gè)數(shù)
- 用regexp_replace()函數(shù)將要計(jì)算的字符替換為’'
- 用字符串的總長(zhǎng)度減去替換字符后的串長(zhǎng)度沪停，得到要計(jì)算的字符總長(zhǎng)度
select (length("HELLO HELLO")) - (length(regexp_replace("HELLO HELLO","LL",'')))
