关于hive:五Hive常用函数示例

5次阅读

共计 5714 个字符，预计需要花费 15 分钟才能阅读完成。

示例咱们有这样一组数据

data1.txt

id    name    dept    project
1001    zhangsan    sale    A
1002    lisi    sale    A
1003    wangwu    IT    B
1004    zhaoliu    IT    A
1004    zhaosi    sale    B

求出每个部门加入 a、b 我的项目各有多少人？

创立表

create table emp_pro(
id int,
name string,
dept string,
project string)
row format delimited fields terminated by "\t";

导入数据

load data local inpath into '/home/v2admin/demo/demo1.txt' into table emp_pro;

查看下咱们的表的内容

+-------------+---------------+---------------+------------------+
| emp_pro.id  | emp_pro.name  | emp_pro.dept  | emp_pro.project  |
+-------------+---------------+---------------+------------------+
| 1001        | zhangsan      | sale          | A                |
| 1002        | lisi          | sale          | A                |
| 1003        | wangwu        | IT            | B                |
| 1004        | zhaoliu       | IT            | A                |
| 1004        | zhaosi        | sale          | B                |
+-------------+---------------+---------------+------------------+

创立表和导入数据没有问题，接下来开始实现咱们的需要

select 
dept,
sum(case project when "A" then 1 else 0 end)  A_Count,
sum(case project when "B"  then 1 else 0 end)  B_Count
from emp_pro
group by dept;

后果

+-------+----------+----------+
| dept  | a_count  | b_count  |
+-------+----------+----------+
| IT    | 1        | 1        |
| sale  | 2        | 1        |
+-------+----------+----------+

函数	阐明
CONCAT(string A/col, string B/col…)	返回输出字符串连贯后的后果，反对任意个输出字符串;
CONCAT_WS(separator, str1, str2,…)	它是一个非凡模式的 CONCAT()。第一个参数残余参数间的分隔符。分隔符能够是与残余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连贯的字符串之间;
COLLECT_SET(col)	函数只承受根本数据类型，它的次要作用是将某字段的值进行去重汇总，产生 array 类型字段。

示例如下
还是这个数据

data1.txt

id    name    dept    project
1001    zhangsan    sale    A
1002    lisi    sale    A
1003    wangwu    IT    B
1004    zhaoliu    IT    A
1004    zhaosi    sale    B

咱们要失去如下后果

sale,A    zhangsan,lisi
sale,B    zhaosi
IT,A    zhaoliu
IT,B    wangwu

查问语句

select 
t1.bs,
concat_ws("|",collect_set(t1.name)) name
from(select name,concat(dept,",",project) bs from emp_pro)  t1
group by t1.bs;

后果

+---------+----------------+
|  t1.bs  |      name      |
+---------+----------------+
| IT,A    | zhaoliu        |
| IT,B    | wangwu         |
| sale,A  | zhangsan|lisi  |
| sale,B  | zhaosi         |
+---------+----------------+

函数	阐明
EXPLODE(col)	将 hive 一列中简单的 array 或者 map 构造拆分成多行。
LATERAL VIEW	用于和 split, explode 等 UDTF 一起应用，它可能将一列数据拆成多行数据，在此基础上能够对拆分后的数据进行聚合，例如 LATERAL VIEW udtf(expression) tableAlias AS columnAlias

示例
有这样一组数据

书名            标签
Python 外围编程    开发、后盾、编程、python
Java 编程与进阶    开发、编程、Java
MYSQL 实战        数据库、SQL

咱们想得到开展的数据

Python 外围编程    开发
Python 外围编程    后盾
Python 外围编程    编程
Python 外围编程    python
Java 编程与进阶    开发
Java 编程与进阶    编程
Java 编程与进阶    Java
MYSQL 实战    数据库
MYSQL 实战    SQL

首先筹备数据 data2.txt

Python 外围编程    开发、后盾、编程、python
Java 编程与进阶    开发、编程、Java
MYSQL 实战    数据库、SQL

创立表并导入数据

-- 创立表
create table book_info(
book_name string,
category string)
row format delimited fields terminated by "\t";

-- 导入数据
load data local inpath "/home/v2admin/demo/data2.txt" into table book_info;

查看下表的数据

+----------------------+---------------------+
| book_info.book_name  | book_info.category  |
+----------------------+---------------------+
| Python 外围编程           | 开发、后盾、编程、python     |
| Java 编程与进阶            | 开发、编程、Java          |
| MYSQL 实战              | 数据库、SQL             |
+----------------------+---------------------+

Ok，接下来实现咱们的需要

select
book_info.book_name,
tb.cate
from book_info
lateral view
explode(split(category, "、")) tb as cate;

后果

+----------------------+-----------+
| book_info.book_name  | tbl.cate  |
+----------------------+-----------+
| Python 外围编程           | 开发        |
| Python 外围编程           | 后盾        |
| Python 外围编程           | 编程        |
| Python 外围编程           | python    |
| Java 编程与进阶            | 开发        |
| Java 编程与进阶            | 编程        |
| Java 编程与进阶            | Java      |
| MYSQL 实战              | 数据库       |
| MYSQL 实战              | SQL       |
+----------------------+-----------+

函数	阐明
OVER()	指定剖析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变动。
CURRENT ROW	以后行
n PRECEDING	前 n 行数据
n FOLLOWING	后 n 行数据
UNBOUNDED	终点，UNBOUNDED PRECEDING 示意从后面的终点，UNBOUNDED FOLLOWING 示意到前面的起点
LAG(col,n,default_val)	往前第 n 行数据
LEAD(col,n, default_val)	往后第 n 行数据
NTILE(n)	把有序窗口的行散发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。留神：n 必须为 int 类型。

过一下即可，等看示例的时候，再对照着看

1) 老套路，先上数据
姓名日期生产

# 姓名    日期    生产
zhangsan    2017-01-01    10
zhangsan    2017-01-02    15
wangwu    2017-02-03    23
wangwu    2017-01-04    29
zhaoliu    2017-01-05    46
zhaoliu    2017-04-06    42
wangwu    2017-01-07    50
zhangsan    2017-01-08    55
zhangsan    2017-04-08    62
wangwu    2017-04-09    68
zaholiu    2017-05-10    12
zhouzhou    2017-04-11    75
zhouzhou    2017-04-13    94

保留文件 data3.txt

2)创立表并导入数据

-- 创立表
create table log_info(
name string,
log_date string,
cost int)
row format delimited fields terminated by "\t";

-- 导入数据
load data local inpath "/home/v2admin/demo/data3.txt" into table log_info;

3)需要一: 查问 2017 年 4 月份生产过的用户和用户总数

-- over() 不加参数，示意整张表开个窗口
select
name,
count(*) over() as count
from log_info
where substring(log_date,1,7) = '2017-04'
group by name;

后果

+-----------+--------+
|   name    | count  |
+-----------+--------+
| wangwu    | 4      |
| zhangsan  | 4      |
| zhaoliu   | 4      |
| zhouzhou  | 4      |
+-----------+--------+

4）需要 2 查问用户的生产明细和每月生产总额

select
name,
log_date,
cost,
sum(cost) over(partition by month(log_date))
from log_info;

后果

+-----------+-------------+-------+---------------+
|   name    |  log_date   | cost  | sum_window_0  |
+-----------+-------------+-------+---------------+
| zhangsan  | 2017-01-01  | 10    | 205           |
| zhangsan  | 2017-01-02  | 15    | 205           |
| wangwu    | 2017-01-04  | 29    | 205           |
| zhaoliu   | 2017-01-05  | 46    | 205           |
| wangwu    | 2017-01-07  | 50    | 205           |
| zhangsan  | 2017-01-08  | 55    | 205           |
| wangwu    | 2017-02-03  | 23    | 23            |
| zhouzhou  | 2017-04-11  | 75    | 341           |
| zhouzhou  | 2017-04-13  | 94    | 341           |
| zhangsan  | 2017-04-08  | 62    | 341           |
| wangwu    | 2017-04-09  | 68    | 341           |
| zhaoliu   | 2017-04-06  | 42    | 341           |
| zaholiu   | 2017-05-10  | 12    | 12            |
+-----------+-------------+-------+---------------+

5）需要 3 查问用户上次的生产日期

select
name,
log_date,
cost,
lag(log_date,2) over (partition by name order by log_date) as last_time
from log_info;

后果

+-----------+-------------+-------+-------------+
|   name    |  log_date   | cost  |  last_time  |
+-----------+-------------+-------+-------------+
| wangwu    | 2017-01-04  | 29    | NULL        |
| wangwu    | 2017-01-07  | 50    | NULL        |
| wangwu    | 2017-02-03  | 23    | 2017-01-04  |
| wangwu    | 2017-04-09  | 68    | 2017-01-07  |
| zaholiu   | 2017-05-10  | 12    | NULL        |
| zhangsan  | 2017-01-01  | 10    | NULL        |
| zhangsan  | 2017-01-02  | 15    | NULL        |
| zhangsan  | 2017-01-08  | 55    | 2017-01-01  |
| zhangsan  | 2017-04-08  | 62    | 2017-01-02  |
| zhaoliu   | 2017-01-05  | 46    | NULL        |
| zhaoliu   | 2017-04-06  | 42    | NULL        |
| zhouzhou  | 2017-04-11  | 75    | NULL        |
| zhouzhou  | 2017-04-13  | 94    | NULL        |
+-----------+-------------+-------+-------------+

1)current_date 返回以后日期

0: jdbc:hive2://hadoop10:10000> select current_date();
+-------------+
|     _c0     |
+-------------+
| 2020-01-15  |
+-------------+

2）date_add, date_sub,datediff()日期的加减

-- 从明天开始，100 天当前的日期
select date_add(current_date(), 100);

-- 从明天开始，100 天以前的日期；select date_sub(current_date(), 100);

-- 两个日期之间的差
select datediff(current_date(), "2019-01-03");

正文完