hive> select to_date('2011-12-08 10:03:01') from tableName;
2011-12-08
6、日期轉年函數(shù): year
語法: year(string date) 返回值: int 說明: 返回日期中的年。
hive> select year('2011-12-08 10:03:01') from tableName;
2011
hive> select year('2012-12-08') from tableName;
2012
7、日期轉月函數(shù): month
語法: month (string date) 返回值: int 說明: 返回日期中的月份。
hive> select month('2011-12-08 10:03:01') from tableName;
12
hive> select month('2011-08-08') from tableName;
8
8、日期轉天函數(shù): day
語法: day (string date) 返回值: int 說明: 返回日期中的天。
hive> select day('2011-12-08 10:03:01') from tableName;
8
hive> select day('2011-12-24') from tableName;
24
9、日期轉小時函數(shù): hour
語法: hour (string date) 返回值: int 說明: 返回日期中的小時。
hive> select hour('2011-12-08 10:03:01') from tableName;
10
10、日期轉分鐘函數(shù): minute
語法: minute (string date) 返回值: int 說明: 返回日期中的分鐘。
hive> select minute('2011-12-08 10:03:01') from tableName;
3
hive> select second('2011-12-08 10:03:01') from tableName;
1
12、日期轉周函數(shù): weekofyear
語法: weekofyear (string date) 返回值: int 說明: 返回日期在當前的周數(shù)。
hive> select weekofyear('2011-12-08 10:03:01') from tableName;
49
13、日期比較函數(shù): datediff
語法: datediff(string enddate, string startdate) 返回值: int 說明: 返回結束日期減去開始日期的天數(shù)。
hive> select datediff('2012-12-08','2012-05-09') from tableName;
213
14、日期增加函數(shù): date_add
語法: date_add(string startdate, int days) 返回值: string 說明: 返回開始日期startdate增加days天后的日期。
hive> select date_add('2012-12-08',10) from tableName;
2012-12-18
15、日期減少函數(shù): date_sub
語法: date_sub (string startdate, int days) 返回值: string 說明: 返回開始日期startdate減少days天后的日期。
hive> select date_sub('2012-12-08',10) from tableName;
2012-11-28
1.3、條件函數(shù)
1、If函數(shù): if
語法: if(boolean testCondition, T valueTrue, T valueFalseOrNull) 返回值: T 說明: 當條件testCondition為TRUE時，返回valueTrue；否則返回valueFalseOrNull
hive> select if(1=2,100,200) from tableName;
200
hive> select if(1=1,100,200) from tableName;
100
2、非空查找函數(shù): COALESCE
語法: COALESCE(T v1, T v2, …) 返回值: T 說明: 返回參數(shù)中的第一個非空值；如果所有值都為NULL，那么返回NULL
hive> select COALESCE(null,'100','50') from tableName;
100
3、條件判斷函數(shù)：CASE
語法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END 返回值: T 說明：如果a等于b，那么返回c；如果a等于d，那么返回e；否則返回f
hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
mary
hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
tim
4、條件判斷函數(shù)：CASE
語法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END 返回值: T 說明：如果a為TRUE,則返回b；如果c為TRUE，則返回d；否則返回e
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
mary
hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
tom
1.4、字符串函數(shù)
1、字符串長度函數(shù)：length
語法: length(string A) 返回值: int 說明：返回字符串A的長度
hive> select length('abcedfg') from tableName;
2、字符串反轉函數(shù)：reverse
語法: reverse(string A) 返回值: string 說明：返回字符串A的反轉結果
hive> select reverse('abcedfg') from tableName;
gfdecba
3、字符串連接函數(shù)：concat
語法: concat(string A, string B…) 返回值: string 說明：返回輸入字符串連接后的結果，支持任意個輸入字符串
hive> select concat('abc','def','gh') from tableName;
abcdefgh
4、字符串連接并指定字符串分隔符：concat_ws
語法: concat_ws(string SEP, string A, string B…) 返回值: string 說明：返回輸入字符串連接后的結果，SEP表示各個字符串間的分隔符
語法: substr(string A, int start),substring(string A, int start) 返回值: string 說明：返回字符串A從start位置到結尾的字符串
hive> select substr('abcde',3) from tableName;
cde
hive> select substring('abcde',3) from tableName;
cde
hive> select substr('abcde',-1) from tableName; （和ORACLE相同）
e
6、字符串截取函數(shù)：substr,substring
語法: substr(string A, int start, int len),substring(string A, int start, int len) 返回值: string 說明：返回字符串A從start位置開始，長度為len的字符串
hive> select substr('abcde',3,2) from tableName;
cd
hive> select substring('abcde',3,2) from tableName;
cd
hive>select substring('abcde',-2,2) from tableName;
de
7、字符串轉大寫函數(shù)：upper,ucase
語法: upper(string A) ucase(string A) 返回值: string 說明：返回字符串A的大寫格式
hive> select upper('abSEd') from tableName;
ABSED
hive> select ucase('abSEd') from tableName;
ABSED
8、字符串轉小寫函數(shù)：lower,lcase
語法: lower(string A) lcase(string A) 返回值: string 說明：返回字符串A的小寫格式
hive> select lower('abSEd') from tableName;
absed
hive> select lcase('abSEd') from tableName;
absed
create table score_map(name string, score map)
row format delimited fields terminated by '\t'
collection items terminated by ',' map keys terminated by ':';
創(chuàng)建數(shù)據(jù)內容如下并加載數(shù)據(jù)
cd /kkb/install/hivedatas/
vim score_map.txt
zhangsan 數(shù)學:80,語文:89,英語:95
lisi 語文:60,數(shù)學:80,英語:99
加載數(shù)據(jù)到hive表當中去
load data local inpath '/kkb/install/hivedatas/score_map.txt' overwrite into table score_map;
map結構數(shù)據(jù)訪問：
獲取所有的value：
select name,map_values(score) from score_map;
獲取所有的key：
select name,map_keys(score) from score_map;
按照key來進行獲取value值
select name,score["數(shù)學"] from score_map;
查看map元素個數(shù)
select name,size(score) from score_map;
創(chuàng)建struct表
hive> create table movie_score( name string, info struct )row format delimited fields terminated by "\t" collection items terminated by ":";
加載數(shù)據(jù)
cd /kkb/install/hivedatas/
vim struct.txt
ABC 1254:7.4
DEF 256:4.9
XYZ 456:5.4
加載數(shù)據(jù)
load data local inpath '/kkb/install/hivedatas/struct.txt' overwrite into table movie_score;
hive當中查詢數(shù)據(jù)
hive> select * from movie_score;
hive> select info.number,info.score from movie_score;
OK
1254 7.4
256 4.9
456 5.4
hive (default)> create database hive_explode;
hive (default)> use hive_explode;
第二步：創(chuàng)建hive表，然后使用explode拆分map和array
create table hive_explode.t3(name string,
children array,
address Map)
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
stored as textFile;
第三步：加載數(shù)據(jù)
node03執(zhí)行以下命令創(chuàng)建表數(shù)據(jù)文件
cd /kkb/install/hivedatas/
vim maparray
數(shù)據(jù)內容格式如下
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
hive表當中加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/maparray' into table hive_explode.t3;
第四步：使用explode將hive當中數(shù)據(jù)拆開
將array當中的數(shù)據(jù)拆分開
hive (hive_explode)> SELECT explode(children) AS myChild FROM hive_explode.t3;
將map當中的數(shù)據(jù)拆分開
hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM hive_explode.t3;
hive (hive_explode)>
create table hive_explode.explode_lateral_view (
area string,
goods_id string,
sale_info string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS textfile;
第二步：準備數(shù)據(jù)并加載數(shù)據(jù)
準備數(shù)據(jù)如下
cd /kkb/install/hivedatas
vim explode_json
a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"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"}]
加載數(shù)據(jù)到hive表當中去
hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/explode_json' overwrite into table hive_explode.explode_lateral_view;
第三步：使用explode拆分Array
hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from hive_explode.explode_lateral_view;
第四步：使用explode拆解Map
hive (hive_explode)> select explode(split(area,',')) as area from hive_explode.explode_lateral_view;
第五步：拆解json字段
hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as sale_info from hive_explode.explode_lateral_view;
hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as sale_info from hive_explode.explode_lateral_view;
然后出現(xiàn)異常FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF explode不能寫在別的函數(shù)內
如果你這么寫，想查兩個字段，select explode(split(area,',')) as area,good_id from explode_lateral_view;
會報錯FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'
使用UDTF的時候，只支持一個字段，這時候就需要LATERAL VIEW出場了
3、配合LATERAL VIEW使用
配合lateral view查詢多個字段
hive (hive_explode)> select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;
hive (hive_explode)> select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source, get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales, get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales, get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales from explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;
hive (hive_explode)> create table person_info( name string, constellation string, blood_type string) row format delimited fields terminated by "\t";
加載數(shù)據(jù)
hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/constellation.txt' into table person_info;
6．按需求查詢數(shù)據(jù)
hive (hive_explode)> select t1.base, concat_ws('|', collect_set(t1.name)) name from (select name, concat(constellation, "," , blood_type) base from person_info) t1 group by t1.base;
1.9.2、行轉列
1．函數(shù)說明
EXPLODE(col)：將hive一列中復雜的array或者map結構拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
用于將分組數(shù)據(jù)按照順序切分成n片，返回當前切片值，如果切片不均勻，默認增加第一個切片的分布。NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。
cd /kkb/install/hivedatas
vim cookiepv.txt
cookie1,2015-04-10,1
cookie1,2015-04-11,5
cookie1,2015-04-12,7
cookie1,2015-04-13,3
cookie1,2015-04-14,2
cookie1,2015-04-15,4
cookie1,2015-04-16,4
cookie2,2015-04-10,2
cookie2,2015-04-11,3
cookie2,2015-04-12,5
cookie2,2015-04-13,6
cookie2,2015-04-14,3
cookie2,2015-04-15,9
cookie2,2015-04-16,7
加載數(shù)據(jù)到hive表當中去
load data local inpath '/kkb/install/hivedatas/cookiepv.txt' overwrite into table cookie_pv
第三步：使用分析函數(shù)來求取每個cookie訪問PV的前三條記錄
SELECT
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM cookie_pv
WHERE rn1 <= 3 ;
public class MyUDF extends UDF {
public Text evaluate(final Text s) {
if (null == s) {
return null;
}
//**返回大寫字母
return new Text(s.toString().toUpperCase());
}
}
重慶分公司
重慶分公司
