相关文章推荐
傻傻的佛珠  ·  sql not like 多条件 - CSDN文库·  2 周前    · 
酒量小的柳树  ·  【Lidar】基于Python的点云数据下采 ...·  2 月前    · 
爱听歌的松树  ·  InvalidAuthenticationT ...·  4 月前    · 
沉着的西红柿  ·  angular8 ...·  8 月前    · 
非常酷的啄木鸟  ·  C#不用config配置文件使用Sqlite ...·  1 年前    · 
Code  ›  Hive(17) Hive的常用函数 - Whatever_It_Takes
hive string 字符串函数 select函数
https://www.cnblogs.com/jimmy888/p/13551591.html
沉稳的汉堡包
9 月前
hive> select round(3.5) from tableName; hive> create table tableName as select round(9542.158) from tableName;
指定精度取整函数: round
  • 语法 : round(double a, int d)
  • 返回值 : DOUBLE
  • 说明 : 返回指定精度 d 的 double 类型 
    • hive> select round(3.1415926, 4) from tableName;
3.1416

    向下取整函数: floor
    

  • 语法: floor(double a)
    • 

  • 返回值: BIGINT
    • 

  • 说明: 返回等于或者小于该double变量的最大的整数
    • 

    hive> select floor(3.1415926) from tableName;
hive> select floor(25) from tableName;

    向上取整函数: ceil
    

  • 语法: ceil(double a)
    • 

  • 返回值: BIGINT
    • 

  • 说明: 返回等于或者大于该double变量的最小的整数
    • 

    hive> select ceil(3.1415926) from tableName;
hive> select ceil(46) from tableName;

    向上取整函数: ceiling
    

  • 语法: ceiling(double a)
    • 

  • 返回值: BIGINT
    • 

  • 说明: 与ceil功能相同
    • 

    hive> select ceiling(3.1415926) from tableName;
hive> select ceiling(46) from tableName;

    取随机数函数: rand
    

  • 语法: rand(), rand(int seed)
    • 

  • 返回值: double
    • 

  • 说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
    • 

    hive> select rand() from tableName;
0.5577432776034763
hive> select rand() from tableName;
0.6638336467363424
hive> select rand(100) from tableName;
0.7220096548596434
hive> select rand(100) from tableName;
0.7220096548596434

    1、UNIX时间戳转日期函数: from_unixtime
    

  • 语法: from_unixtime(bigint unixtime[, string format])
    • 

  • 返回值: string
    • 

  • 说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
    • 

    hive> select from_unixtime(1323308943, 'yyyyMMdd') from tableName;
20111208

    2、获取当前UNIX时间戳函数: unix_timestamp
    

  • 语法: unix_timestamp()
    • 

  • 返回值: bigint
    • 

  • 说明: 获得当前时区的UNIX时间戳
    • 

    hive> select unix_timestamp() from tableName;
1323309615

    3、日期转UNIX时间戳函数: unix_timestamp
    

  • 语法: unix_timestamp(string date)
    • 

  • 返回值: bigint
    • 

  • 说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
    • 

    hive> select unix_timestamp('2011-12-07 13:01:03') from tableName;
1323234063

    4、指定格式日期转UNIX时间戳函数: unix_timestamp
    

  • 语法: unix_timestamp(string date, string pattern)
    • 

  • 返回值: bigint
    • 

  • 说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
    • 

    hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from tableName;
1323234063

    5、日期时间转日期函数: to_date
    

  • 语法: to_date(string datetime)
    • 

  • 返回值: string
    • 

  • 说明: 返回日期时间字段中的日期部分。
    • 

    hive> select to_date('2011-12-08 10:03:01') from tableName;
2011-12-08

    6、日期转年函数: year
    

  • 语法: year(string date)
    • 

  • 返回值: int
    • 

  • 说明: 返回日期中的年。
    • 

    hive> select year('2011-12-08 10:03:01') from tableName;
hive> select year('2012-12-08') from tableName;

    7、日期转月函数: month
    

  • 语法: month (string date)
    • 

  • 返回值: int
    • 

  • 说明: 返回date或datetime中的月份。
    • 

    hive> select month('2011-12-08 10:03:01') from tableName;
hive> select month('2011-08-08') from tableName;

    8、日期转天函数: day
    

  • 语法: day (string date)
    • 

  • 返回值: int
    • 

  • 说明: 返回日期中的天。
    • 

    hive> select day('2011-12-08 10:03:01') from tableName;
hive> select day('2011-12-24') from tableName;

    9、日期转小时函数: hour
    

  • 语法: hour (string date)
    • 

  • 返回值: int
    • 

  • 说明: 返回日期中的小时。
    • 

    hive> select hour('2011-12-08 10:03:01') from tableName;

    10、日期转分钟函数: minute
    

  • 语法: minute (string date)
    • 

  • 返回值: int
    • 

  • 说明: 返回日期中的分钟。
    • 

    hive> select minute('2011-12-08 10:03:01') from tableName;
-- second 返回秒
hive> select second('2011-12-08 10:03:01') from tableName;

    12、日期转周函数: weekofyear
    

  • 语法: weekofyear (string date)
    • 

  • 返回值: int
    • 

  • 说明: 返回日期在当前的周数。
    • 

    hive> select weekofyear('2011-12-08 10:03:01') from tableName;

    13、日期比较函数: datediff
    

  • 语法: datediff(string enddate, string startdate)
    • 

  • 返回值: int
    • 

  • 说明: 返回结束日期减去开始日期的天数。
    • 

    hive> select datediff('2012-12-08','2012-05-09') from tableName;

    14、日期增加函数: 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、日期减少函数: 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、If函数: if
    

  • 语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
    • 

  • 返回值: T
    • 

  • 说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
    • 

    hive> select if(1=2,100,200) from tableName;
hive> select if(1=1,100,200) from tableName;

    2、非空查找函数: COALESCE
    

  • 语法: COALESCE(T v1, T v2, …)
    • 

  • 返回值: T
    • 

  • 说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
    • 

    hive> select COALESCE(null,'100','50') from tableName;

    3、条件判断函数：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;
hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;

    4、条件判断函数：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;
hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;

    字符串函数
    

    1、字符串长度函数：length
    

  • 语法: length(string A)
    • 

  • 返回值: int
    • 

  • 说明：返回字符串A的长度
    • 

    hive> select length('abcedfg') from tableName;

    2、字符串反转函数：reverse
    

  • 语法: reverse(string A)
    • 

  • 返回值: string
    • 

  • 说明：返回字符串A的反转结果
    • 

    hive> select reverse('abcedfg') from tableName;
gfdecba

    3、字符串连接函数：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表示各个字符串间的分隔符
    • 

    hive> select concat_ws(',','abc','def','gh') from tableName;
abc,def,gh

    5、字符串截取函数：substr
    

  • 语法: substr(string A, int start), substring(string A, int start)
    • 

  • 返回值: string
    • 

  • 说明：返回字符串A从start位置到结尾的字符串,位置索引从1开始。
    • 

    hive> select substr('abcde',3) from tableName;
hive> select substring('abcde',3) from tableName;
hive> select substr('abcde',-1) from tableName;  （和ORACLE相同）

    6、字符串截取函数：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;
hive> select substring('abcde',3,2) from tableName;
hive>select substring('abcde',-3,2) from tableName;

    7、字符串转大写函数：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、字符串转小写函数：lower, lcase
    

  • 语法: lower(string A) lcase(string A)
    • 

  • 返回值: string
    • 

  • 说明：返回字符串A的小写格式
    • 

    hive> select lower('abSEd') from tableName;
absed
hive> select lcase('abSEd') from tableName;
absed

    9、去空格函数：trim
    

  • 语法: trim(string A)
    • 

  • 返回值: string
    • 

  • 说明：去除字符串两边的空格
    • 

    hive> select trim(' ab c ') from tableName;

    10、url解析函数 parse_url
    
parse_url(string urlString, string partToExtract [, string keyToExtract])

  • 返回值: string
    • 

  • 说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
    • 

  • HOST:主机

  • PATH:服务器上某资源的位置,通常有目录/子目录/文件名这样结构组成
    • 

  • QUERY:查询，用于给动态网页传递参数，可有多个参数，用"&"符号隔开，每个参数的名和值用"="符号隔开
    • 

  • PROTOCOL:URL 的协议部分,返回https或http
    • 

    select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from t1;   #HOST必须使用大写
www.tableName.com 
select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from t1;
+------+--+
| _c0  |
+------+--+
| v1   |
+------+--+
select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1','REF') from t1;
+-------+--+
|  _c0  |
+-------+--+
| Ref1  |
+-------+--+
select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1','PROTOCOL') from t1;
+--------+--+
|  _c0   |
+--------+--+
| https  |
+--------+--+
select parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1','FILE') from t1;
+---------------------------+--+
|            _c0            |
+---------------------------+--+
| /path1/p.php?k1=v1&k2=v2  |
+---------------------------+--+

    11、json解析  get_json_object
    

  • 语法: get_json_object(string json_string, string path)
    • 

  • 返回值: string
    • 

  • 说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
    • 

    select  get_json_object('{"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') from t1;
+------+--+
| _c0  |
+------+--+
| amy  |
+------+--+

    12、重复字符串函数：repeat
    

  • 语法: repeat(string str, int n)
    • 

  • 返回值: string
    • 

  • 说明：返回重复n次后的str字符串
    • 

    hive> select repeat('abc', 5) from tableName;
abcabcabcabcabc

    13、分割字符串函数: split
    

  • 语法: split(string str, string pat)
    • 

  • 返回值: array
    • 

  • 说明: 按照pat字符串分割str，会返回分割后的字符串数组
    • 

    hive> select split('abtcdtef','t') from tableName;
["ab","cd","ef"]

    集合统计函数
    

    1、个数统计函数: count
    

  • 语法: count(*), count(expr), count(DISTINCT expr[, expr_.])
    • 

  • 返回值：Int
    • 

  • 说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数
    • 

    hive> select count(*) from tableName;
hive> select count(distinct t) from tableName;

    2、总和统计函数: sum
    

  • 语法: sum(col), sum(DISTINCT col)
    • 

  • 返回值: double
    • 

  • 说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果
    • 

    hive> select sum(t) from tableName;
hive> select sum(distinct t) from tableName;

    3、平均值统计函数: avg
    

  • 语法: avg(col), avg(DISTINCT col)
    • 

  • 返回值: double
    • 

  • 说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值
    • 

    hive> select avg(t) from tableName;
hive> select avg (distinct t) from tableName;

    4、最小值统计函数: min
    

  • 语法: min(col)
    • 

  • 返回值: double
    • 

  • 说明: 统计结果集中col字段的最小值
    • 

    hive> select min(t) from tableName;

    5、最大值统计函数: max
    

  • 语法: max(col)
    • 

  • 返回值: double
    • 

  • 说明: 统计结果集中col字段的最大值
    • 

    hive> select max(t) from tableName;

    复合类型构建函数
    

    1、Map类型构建: map
    

  • 语法: map (key1, value1, key2, value2, …)
    • 

  • 说明：根据输入的key和value对构建map类型
    • 

  • collection items terminated by ',' 表示键值对与键值对之间使用','进行分隔。
    • 

  • map keys terminated by ':' 表示key与value间使用':'进行分隔。
    • 

  • 构建map数据类型的语法：字段名 map<string, int>
    • 

    -- 建表
create table score_map(name string, score map<string, int>)
row format delimited fields terminated by '\t' 
collection items terminated by ',' 
map keys terminated by ':';
-- 创建数据内容如下并加载数据
cd /kkb/install/hivedatas/
vim score_map.txt
zhangsan	数学:80,语文:89,英语:95
lisi	语文:60,数学:80,英语:99
-- 加载数据到hive表当中去
load data local inpath '/kkb/install/hivedatas/score_map.txt' overwrite into table score_map;
-- map结构数据访问：
-- 获取所有的value：
select name,map_values(score) from score_map;
+-----------+-------------+--+
|   name    |     _c1     |
+-----------+-------------+--+
| zhangsan  | [80,89,95]  |
| lisi      | [60,80,99]  |
+-----------+-------------+--+
-- 获取所有的key：
select name,map_keys(score) from score_map;
+-----------+-------------------+--+
|   name    |        _c1        |
+-----------+-------------------+--+
| zhangsan  | ["数学","语文","英语"]  |
| lisi      | ["语文","数学","英语"]  |
+-----------+-------------------+--+
-- 按照key来进行获取value值
select name,score["数学"]  from score_map;
+-----------+------+--+
|   name    | _c1  |
+-----------+------+--+
| zhangsan  | 80   |
| lisi      | 80   |
+-----------+------+--+
-- 查看map元素个数（键值对个数）
select name,size(score) from score_map;
+-----------+------+--+
|   name    | _c1  |
+-----------+------+--+
| zhangsan  | 3    |
| lisi      | 3    |
+-----------+------+--+
-- 构建一个map
select map(1, 'zs', 2, 'lisi');
+--------------------+--+
|        _c0         |
+--------------------+--+
| {1:"zs",2:"lisi"}  |
+--------------------+--+

    2、Struct类型构建: struct
    

  • 语法: struct(val1, val2, val3, …)
    • 

  • 说明：根据输入的参数构建结构体struct类型，似于C语言中的结构体，内部数据通过X.X来获取，假设我
    • 

  • 数据格式是这样的，电影ABC，有1254人评价过，打分为7.4分
    • 

    -- 创建struct表
create table movie_score(name string, info struct<number:int,score:float>)
row format delimited fields terminated by "\t"  
collection items terminated by ":"; 
-- 加载数据
cd /kkb/install/hivedatas/
vim struct.txt
-- 电影ABC，有1254人评价过，打分为7.4分
ABC	1254:7.4  
DEF	256:4.9  
XYZ	456:5.4
-- 加载数据
load data local inpath '/kkb/install/hivedatas/struct.txt' overwrite into table movie_score;
-- hive当中查询数据
select * from movie_score;
+-------------------+------------------------------+--+
| movie_score.name  |       movie_score.info       |
+-------------------+------------------------------+--+
| ABC               | {"number":1254,"score":7.4}  |
| DEF               | {"number":256,"score":4.9}   |
| XYZ               | {"number":456,"score":5.4}   |
+-------------------+------------------------------+--+
select info.number,round(info.score,1) from movie_score;
+---------+--------------------+--+
| number  |        _c1         |
+---------+--------------------+--+
| 1254    | 7.400000095367432  |
| 256     | 4.900000095367432  |
| 456     | 5.400000095367432  |
+---------+--------------------+--+
-- 构建一个struct
select struct(1, 'anzhulababy', 'moon', 1.68);
+----------------------------------------------------+--+
|                        _c0                         |
+----------------------------------------------------+--+
| {"col1":1,"col2":"anzhulababy","col3":"moon","col4":1.68} |
+----------------------------------------------------+--+

    3、Array类型构建: array
    

  • 语法: array(val1, val2, …)
    • 

  • 说明：根据输入的参数构建array数组类型
    • 

    hive> create table person(name string, work_locations array<string>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
-- 加载数据到person表当中去
cd /kkb/install/hivedatas/
vim person.txt
-- 数据内容格式如下
biansutao	beijing,shanghai,tianjin,hangzhou
linan	changchun,chengdu,wuhan
-- 加载数据
hive > load  data local inpath '/kkb/install/hivedatas/person.txt' overwrite into table person;
-- 查询所有数据数据
hive > select * from person;
-- 按照下表索引进行查询
hive > select work_locations[0] from person;
-- 查询所有集合数据
hive  > select work_locations from person; 
-- 查询元素个数
hive >  select size(work_locations) from person;   
-- 构建array
select array(1, 2, 1);
select array(1, 'a', 1.0);
select array(1, 2, 1.0);

    复合类型长度统计函数
    

    1、Map类型长度函数: size(Map<k .V>)
    

  • 语法: size(Map<k .V>)
    • 

  • 返回值: int
    • 

  • 说明: 返回map类型的长度
    • 

    hive> select size(map(1, 'zs', 2, 'anzhulababy')) from tableName;

    2、array类型长度函数: size(Array)
    

  • 语法: size(Array)
    • 

  • 返回值: int
    • 

  • 说明: 返回array类型的长度
    • 

    hive> select size(t) from arr_table2;

    3、类型转换函数
    

  • 类型转换函数: cast
    • 

  • 语法: cast(expr as )
    • 

  • 返回值: Expected "=" to follow "type"
    • 

  • 说明: 返回转换后的数据类型
    • 

    hive> select cast('1' as bigint) from tableName;

    行转列案例
    

    行转列：一行的某些字段进行组合等，形成了新的列。




    

    

    1、相关函数说明
    

    CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
    

    CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。
    

  • 第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。
    • 

  • 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
    • 

    COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
    

    2、数据准备
    

  • 创建hive表并加载数据
    • 

    hive (hive_explode)> create table person_info(name string, constellation string,  blood_type string) row format delimited fields terminated by "\t";

    hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/constellation.txt' into table person_info;

    6、按需求查询数据
    

    #查询的需求如下：
射手座,A            老王|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋
select * from person_info;
+-------------------+----------------------------+-------------------------+--+
| person_info.name  | person_info.constellation  | person_info.blood_type  |
+-------------------+----------------------------+-------------------------+--+
| 孙悟空               | 白羊座                        | A                       |
| 老王                | 射手座                        | A                       |
| 宋宋                | 白羊座                        | B                       |
| 猪八戒               | 白羊座                        | A                       |
| 凤姐                | 射手座                        | A                       |
+-------------------+----------------------------+-------------------------+--+
select p.name,concat(p.constellation,',',p.blood_type) c2 from person_info p;
+---------+---------------+--+
| p.name  |      c2       |
+---------+---------------+--+
| 孙悟空     | 白羊座,A         |
| 老王      | 射手座,A         |
| 宋宋      | 白羊座,B         |
| 猪八戒     | 白羊座,A         |
| 凤姐      | 射手座,A         |
+---------+---------------+--+
5 rows selected (0.154 seconds)
# 将上面的查询语句作为子查询的语句
select p.c2,concat_ws('|',collect_set(p.name)) names from (select name,concat(constellation,',',blood_type) c2 from person_info) p 
group by p.c2;
+---------------+----------+--+
|     p.c2      |  names   |
+---------------+----------+--+
| 射手座,A         | 老王|凤姐    |
| 白羊座,A         | 孙悟空|猪八戒  |
| 白羊座,B         | 宋宋       |
+---------------+----------+--+
#一定一定一定不要写成以下这样!!!!!
#select p.c2,concat_ws('|',collect_set(p.name)) names from (select p.name,concat(p.constellation,',',p.blood_type) c2 from person_info) p group by p.c2;
#是错误的语法： select p.name,concat(p.constellation,',',p.blood_type) c2 from person_info
#子查询要用括号()括起来！！！！！

    explode函数
    

    案例1：使用explode拆分Map和Array字段数据
    

    explode可以用于将hive一列中复杂的array或者map结构拆分成多行
    

    # 现在有数据格式如下,字段之间使用\t分割
zhangsan	child1,child2,child3,child4	k1:v1,k2:v2
lisi	child5,child6,child7,child8	 k3:v3,k4:v4

    需求如下：
    

    -- 将所有的child进行拆开成为一列
+----------+--+
| mychild  |
+----------+--+
| child1   |
| child2   |
| child3   |
| child4   |
| child5   |
| child6   |
| child7   |
| child8   |
+----------+--+
-- 将map的key和value也进行拆开，成为如下结果
+-----------+-------------+--+
| mymapkey  | mymapvalue  |
+-----------+-------------+--+
| k1        | v1          |
| k2        | v2          |
| k3        | v3          |
| k4        | v4          |
+-----------+-------------+--+

    第一步：创建hive表
    

    create table explode_t1(name string, children array<string>, address Map<string, string>) row format delimited fields terminated by '\t' collection items terminated by ','  map keys terminated by ':' stored as textFile;

    第二步：加载数据
    

  • node03执行以下命令创建表数据文件
    • 

    cd  /kkb/install/hivedatas/
vim maparray
-- 数据内容格式如下
zhangsan	child1,child2,child3,child4	k1:v1,k2:v2
lisi	child5,child6,child7,child8	k3:v3,k4:v4

  • hive表当中加载数据
    • 

    load data local inpath '/kkb/install/hivedatas/maparray' into table explode_t1;

    第三步：使用explode将hive当中数据拆开
    

  • 将array当中的数据拆分开
    • 

    select explode(children) as myChild from explode_t1;
+----------+--+
| mychild  |
+----------+--+
| child1   |
| child2   |
| child3   |
| child4   |
| child5   |
| child6   |
| child7   |
| child8   |
+----------+--+

  • 将map当中的数据拆分开
    • 

    select explode(address) as (myMapKey, myMapValue) from explode_t1;
+-----------+-------------+--+
| mymapkey  | mymapvalue  |
+-----------+-------------+--+
| k1        | v1          |
| k2        | v2          |
| k3        | v3          |
| k4        | v4          |
+-----------+-------------+--+

    案例2：使用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"}]

    其中字段与字段之间的分隔符是 |
    

    我们要解析得到所有的monthSales对应的值为以下这一列（行转列）
    

    第一步：创建hive表
    

    create table explode2(area string, goods_id string, sale_info string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS textfile;

    第二步：准备数据并加载数据
    

  • 准备数据如下
    • 

    a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|
[{"source":"7fresh","monthSales":4900},
{"source":"jd","monthSales":2090},
{"source":"jdmart","monthSales":6987}]

    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"}]

  • 加载数据到hive表当中去
    • 

    load data local inpath '/kkb/install/hivedatas/explode_json' overwrite into table explode2;

    第三步：使用explode拆分Array
    

    select explode(split(goods_id, ',')) as goods_id from explode2;
+-----------+--+
| goods_id  |
+-----------+--+
| 1         |
| 2         |
| 3         |
| 4         |
| 5         |
| 6         |
| 7         |
| 8         |
| 9         |
+-----------+--+

    第四步：使用explode拆解Map
    

    select explode(split(area, ',')) as area from explode2;
+-------------+--+
|    area     |
+-------------+--+
| a:shandong  |
| b:beijing   |
| c:hebei     |
+-------------+--+

    第五步：拆解json字段
    

    #思路解析：
#第一步，使用正则表达式，去除[{子字符串
select regexp_replace(sale_info,'\\[\\{','') from explode2;
#第二步，嵌套正则表达式，去除}]子字符串
select regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'\\}\\]','') from explode2;
+----------------------------------------------------+----------------+
|                        _c0                         |                |   
+----------------------------------------------------+----------------+
| "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"   |
+----------------------------------------------------+----------------+
#第三步，使用split进行分割，分隔符为 },{
#并使用explode进行拆分成多行
select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as sale_info from explode2;
+----------------------------------------------------+--+
|                     sale_info                      |
+----------------------------------------------------+--+
| "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" |
+----------------------------------------------------+--+
#第四步：使用get_json_object获取特定属性的值
hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as sale_info from explode2;
# 然后出现异常FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
# UDTF explode不能写在别的函数内

    select语句中，如果有explode函数，那么不能够查询两个字段及以上，如果想要实现多个字段，要结合lateral view使用。
    

    select explode(split(area,',')) as area,good_id from explode2;
-- 会报错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出场了

    拓展：UDF
    

    Hive中有三种UDF:
    1、用户定义函数(user-defined function)UDF；
    2、用户定义聚集函数（user-defined aggregate function，UDAF）；
    3、用户定义表生成函数（user-defined table-generating function，UDTF）。
======================================================================================
UDF操作作用于单个数据行，并且产生一个数据行作为输出。大多数函数都属于这一类（比如数学函数和字符串函数）。
UDAF 接受多个输入数据行，并产生一个输出数据行。像COUNT和MAX这样的函数就是聚集函数。
UDTF 操作作用于单个数据行，并且产生多个数据行-------一个表作为输出。lateral view explode()
简单来说：
UDF:返回对应值，一对一
UDAF：返回聚类值，多对一
UDTF：返回拆分值，一对多（explode就是一个udtf函数）

    explode配合LATERAL  VIEW使用
    

    lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合
    

    lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
    

    #第一步，创建数据源
vim vim explode_json2
a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|"jimmy"
#第二步：创建表
create table explode3(area string, goods_id string, sale_info string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS textfile;
#第三步：导入数据
load data local inpath '/kkb/install/hivedatas/explode_json' overwrite into table explode3;
#第四步：查看表
select * from explode3;
+-------------------------------+--------------------+---------------------+--+
|         explode3.area         | explode3.goods_id  | explode3.sale_info  |
+-------------------------------+--------------------+---------------------+--+
| a:shandong,b:beijing,c:hebei  | 1,2,3,4,5,6,7,8,9  | "jimmy"             |
+-------------------------------+--------------------+---------------------+--+
#第五步，结合explode与lateral view来使用,查询两个字段
select goods_id2, sale_info from explode3 LATERAL VIEW explode(split(goods_id, ','))goods as goods_id2;
+------------+------------+--+
| goods_id2  | sale_info  |
+------------+------------+--+
| 1          | "jimmy"    |
| 2          | "jimmy"    |
| 3          | "jimmy"    |
| 4          | "jimmy"    |
| 5          | "jimmy"    |
| 6          | "jimmy"    |
| 7          | "jimmy"    |
| 8          | "jimmy"    |
| 9          | "jimmy"    |
+------------+------------+--+
#goods只是虚拟表的一个别名，改成其它名称也行，不影响结果
#goods_id2是拆分一行产生的列（字段）的名称

    案例2：解决上面拆分json字段的问题
    

  • explode配合lateral view查询多个字段
    • 

    select goods_id2, sale_info from explode2 LATERAL VIEW explode(split(goods_id, ','))goods as goods_id2;
#goods只是虚拟表的一个别名，改成其它名称也行，不影响结果
#goods_id2是拆分一行产生的列（字段）的名称

  • 其中explode2 LATERAL VIEW explode(split(goods_id,','))goods相当于一个虚拟表，该虚拟表与原表explode2的关系是笛卡尔积关联。
    • 

  • 笛卡尔积通俗解释：假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
    • 

  • 也可以多重使用，如下，也是三个表笛卡尔积的结果
    • 

    select goods_id2, sale_info, area2 from explode2
LATERAL VIEW explode(split(goods_id, ','))goods as goods_id2 
LATERAL VIEW explode(split(area,','))area as area2;

  • 最终，我们可以通过下面的句子，把这个json格式的一行数据，完全转换成二维表的方式展现
    • 

    select sale_info_1 from explode2  
LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;
+----------------------------------------------------+--+
|                    sale_info_1                     |
+----------------------------------------------------+--+
| "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 
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 userCount,  get_json_object(concat('{',sale_info_1,'}'),'$.score') as score 
from explode2  
LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;
+---------+-------------+------------+--------+--+
| source  | monthsales  | usercount  | score  |
+---------+-------------+------------+--------+--+
| 7fresh  | 4900        | 1900       | 9.9    |
| jd      | 2090        | 78981      | 9.8    |
| jdmart  | 6987        | 1600       | 9.0    |
+---------+-------------+------------+--------+--+

  • Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select字段的问题。
    • 

  • Multiple Lateral View可以实现类似笛卡尔乘积。
    • 

  • Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。
    • 

    列转行案例
    

    1、函数说明
    

    EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
    

    LATERAL VIEW
    

  • 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
    • 

  • 解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
    • 

    2、数据准备
    

  • 数据内容如下，字段之间都是使用\t进行分割
    • 

    cd /kkb/install/hivedatas
vim movie.txt
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

  • 将电影分类中的数组数据展开。结果如下：
    • 

    《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情
《Lie to me》	悬疑
《Lie to me》	警匪
《Lie to me》	动作
《Lie to me》	心理
《Lie to me》	剧情
《战狼2》	战争
《战狼2》	动作
《战狼2》	灾难

    4、创建hive表并导入数据
    

  • 创建hive表
    • 

    create table movie_info(movie string, category array<string>) 
row format delimited fields terminated by "\t" 
collection items terminated by ",";

    load data local inpath "/kkb/install/hivedatas/movie.txt" into table movie_info;

    5、按需求查询数据
    

    select * from movie_info2;
+--------------------+-----------------------------+--+
| movie_info2.movie  |    movie_info2.category     |
+--------------------+-----------------------------+--+
| 《疑犯追踪》             | ["悬疑","动作","科幻","剧情"]       |
| 《Lie to me》        | ["悬疑","警匪","动作","心理","剧情"]  |
| 《战狼2》              | ["战争","动作","灾难"]            |
+--------------------+-----------------------------+--+
select movie, category_name from movie_info2 
lateral view explode(category) table_tmp as category_name;
+--------------+----------------+--+
|    movie     | category_name  |
+--------------+----------------+--+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+--+
#table_tmp改成其它名称也行，只是一个别名，自定义即可，不影响结果

    reflect函数
    

    reflect函数可以支持在sql中调用java中的自带函数，秒杀一切udf函数。
    

    1、使用java.lang.Math当中的Max求两列中最大值
    

  • 创建hive表
    • 

    create table test_udf(col1 int,col2 int) 
row format delimited fields terminated by ',';

  • 准备数据并加载数据
    • 

    cd /kkb/install/hivedatas
vim test_udf

    load data local inpath '/kkb/install/hivedatas/test_udf' overwrite into table test_udf;

  • 使用java.lang.Math当中的Max求两列当中的最大值
    • 

    select reflect("java.lang.Math","max", col1, col2) from test_udf;

    2、不同记录执行不同的java内置函数
    

  • 创建hive表
    • 

    create table test_udf2(class_name string, method_name string, col1 int, col2 int) row format delimited fields terminated by ',';

    vim test_udf2
java.lang.Math,min,1,2
java.lang.Math,max,2,3

    load data local inpath '/kkb/install/hivedatas/test_udf2' overwrite into table test_udf2;

    select reflect(class_name, method_name, col1, col2) from test_udf2;
+------+--+
| _c0  |
+------+--+
| 1    |
| 3    |
+------+--+

    3、判断是否为数字
    

    使用apache commons中的函数，commons下的jar已经包含在hadoop的classpath中，所以可以直接使用。
    

    使用方式如下：
    

    select reflect("org.apache.commons.lang.math.NumberUtils", "isNumber", "123");
+-------+--+
|  _c0  |
+-------+--+
| true  |
+-------+--+

    分析函数—分组求topN
    

    1、分析函数的作用
    

  • 对于一些比较复杂的数据求取过程，我们可能就要用到分析函数
    • 

  • 分析函数主要用于分组求topN或者求取百分比，或者进行数据的切片等等，我们都可以使用分析函数来解决
    • 

    2、常用的分析函数
    

    1、ROW_NUMBER()：
    

  • 从1开始，按照顺序，给分组内的记录加序列；

  • 比如，按照pv降序排列，生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多
    • 

  • 再比如，获取分组内排序第一的记录;
    • 

  • 获取一个session中的第一条refer等。
    • 

    2、RANK() ：
    

  • 生成数据项在分组中的排名，排名相等会在名次中留下空位
    • 

    3、DENSE_RANK() ：
    

  • 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
    • 

    4、CUME_DIST ：
    

  • 小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例
    • 

    5、PERCENT_RANK ：
    

  • 分组内当前行的RANK值/分组内总行数
    • 

    6、NTILE(n) ：
    

  • 用于将分组数据按照顺序切分成n片，返回当前切片值
    • 

  • 如果切片不均匀，默认增加第一个切片的分布。
    • 

  • NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
    • 

    7、分析函数的常用语法格式
    

  • rank() over(partition by col1 order by col2)
    • 

  • dense_rank() over(partition by col1 order by col2)
    • 

  • row number over(partition by col1 order by col2)
    • 

    3、需求描述
    

  • 现有数据内容格式如下，分别对应三个字段，cookieid，createtime ，pv
    • 

  • 求取每个cookie访问pv前三名的数据记录(根据pv字段的大小进行选取前三名），其实就是分组求topN，求取每组当中的前三个值
    • 

    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

    第一步：创建数据库表
    

  • 在hive当中创建数据库表
    • 

    CREATE EXTERNAL TABLE cookie_pv (
cookieid string,
createtime string, 
pv INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

    第二步：准备数据并加载
    

  • node03执行以下命令，创建数据，并加载到hive表当中去
    • 

    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

  • 加载数据到hive表当中去
    • 

    load data local inpath '/kkb/install/hivedatas/cookiepv.txt' overwrite into table  cookie_pv;

    第三步：使用分析函数来求取每个cookie访问PV的前三条记录
    

    select * from (
SELECT 
cookieid,
createtime,
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 
) temp where temp.rn1 <= 3;
+----------------+------------------+----------+-----------+-----------+-----------+--+
| temp.cookieid  | temp.createtime  | temp.pv  | temp.rn1  | temp.rn2  | temp.rn3  |
+----------------+------------------+----------+-----------+-----------+-----------+--+
| cookie1        | 2015-04-12       | 7        | 1         | 1         | 1         |
| cookie1        | 2015-04-11       | 5        | 2         | 2         | 2         |
| cookie1        | 2015-04-16       | 4        | 3         | 3         | 3         |
| cookie1        | 2015-04-15       | 4        | 3         | 3         | 4         |
| cookie2        | 2015-04-15       | 9        | 1         | 1         | 1         |
| cookie2        | 2015-04-16       | 7        | 2         | 2         | 2         |
| cookie2        | 2015-04-13       | 6        | 3         | 3         | 3         |
+----------------+------------------+----------+-----------+-----------+-----------+--+
     
    推荐文章
    傻傻的佛珠  ·  sql not like 多条件 - CSDN文库
    2 周前
    酒量小的柳树  ·  【Lidar】基于Python的点云数据下采样+体素显示 - RS迷途小书童 - 博客园
    2 月前
    爱听歌的松树  ·  InvalidAuthenticationToken with https://graph.microsoft.com/v1.0/me/calendar/events - Microsoft Q&A
    4 月前
    沉着的西红柿  ·  angular8 实现table表格表头固定_cdk-virtual-scroll-viewport 如何固定表头-CSDN博客
    8 月前
    非常酷的啄木鸟  ·  C#不用config配置文件使用Sqlite数据库_mb6054360584d89的技术博客_51CTO博客
    1 年前
    今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
    删除内容请联系邮箱 2879853325@qq.com
    Code - 代码工具平台
    © 2024 ~ 沪ICP备11025650号