hive操作

Hive

启动类

hive 启动元数据服务（metastore和hiveserver2）和优雅关闭脚本

启动： hive.sh start
关闭： hive.sh stop
重启： hive.sh restart
状态： hive.sh status

脚本如下

#!/bin/bash
HIVE_LOG_DIR=$HIVE_HOME/logs
 
mkdir -p $HIVE_LOG_DIR
 
#检查进程是否运行正常，参数1为进程名，参数2为进程端口
function check_process()
{
    pid=$(ps -ef 2>/dev/null | grep -v grep | grep -i $1 | awk '{print $2}')
    ppid=$(netstat -nltp 2>/dev/null | grep $2 | awk '{print $7}' | cut -d '/' -f 1)
    echo $pid
    [[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1
}
 
function hive_start()
{
    metapid=$(check_process HiveMetastore 9083)
    cmd="nohup hive --service metastore >$HIVE_LOG_DIR/metastore.log 2>&1 &"
    cmd=$cmd" sleep4; hdfs dfsadmin -safemode wait >/dev/null 2>&1"
    [ -z "$metapid" ] && eval $cmd || echo "Metastroe服务已启动"
    server2pid=$(check_process HiveServer2 10000)
    cmd="nohup hive --service hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &"
    [ -z "$server2pid" ] && eval $cmd || echo "HiveServer2服务已启动"
}
 
function hive_stop()
{
    metapid=$(check_process HiveMetastore 9083)
    [ "$metapid" ] && kill $metapid || echo "Metastore服务未启动"
    server2pid=$(check_process HiveServer2 10000)
    [ "$server2pid" ] && kill $server2pid || echo "HiveServer2服务未启动"
}
 
case $1 in
"start")
    hive_start
    ;;
"stop")
    hive_stop
    ;;
"restart")
    hive_stop
    sleep 2
    hive_start
    ;;
"status")
    check_process HiveMetastore 9083 >/dev/null && echo "Metastore服务运行正常" || echo "Metastore服务运行异常"
    check_process HiveServer2 10000 >/dev/null && echo "HiveServer2服务运行正常" || echo "HiveServer2服务运行异常"
    ;;
*)
    echo Invalid Args!
    echo 'Usage: '$(basename $0)' start|stop|restart|status'
    ;;
esac

常用交互命令

SQL类(特殊的)

内置函数

（1） NVL

给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL

select nvl(column, 0) from xxx；

（2）行转列

（3）列转行(一列转多行)

Split(str, separator)： 将字符串按照后面的分隔符切割，转换成字符array。

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

LATERAL VIEW

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

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

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

准备数据源测试

SQL

SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ;
 

测试结果

《功勋》      记录
《功勋》      剧情
《战狼2》     战争
《战狼2》     动作
《战狼2》     灾难
 

窗口函数

（1）OVER()

定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

（2）CURRENT ROW（当前行）

n PRECEDING：往前n行数据
 
n FOLLOWING：往后n行数据

（3）UNBOUNDED（无边界）

UNBOUNDED PRECEDING 前无边界，表示从前面的起点
 
UNBOUNDED FOLLOWING后无边界，表示到后面的终点

SQL案例：由起点到当前行的聚合

select 
    sum(money) over(partition by user_id order by pay_time rows between UNBOUNDED PRECEDING and current row) 
from or_order;

SQL案例：当前行和前面一行做聚合

select 
    sum(money) over(partition by user_id order by pay_time rows between 1 PRECEDING and current row) 
from or_order;

SQL案例：当前行和前面一行和后一行做聚合

select 
    sum(money) over(partition by user_id order by pay_time rows between 1 PRECEDING AND 1 FOLLOWING )
from or_order;

SQL案例：当前行及后面所有行

select 
    sum(money) over(partition by user_id order by pay_time rows between current row and UNBOUNDED FOLLOWING  )
from or_order;

（4）LAG(col,n,default_val)

往前第n行数据，没有的话default_val

（5）LEAD(col,n, default_val)

往后第n行数据，没有的话default_val

SQL案例：查询用户购买明细以及上次的购买时间和下次购买时间

select 
	user_id,,pay_time,money,
	
	lag(pay_time,1,'1970-01-01') over(PARTITION by name order by pay_time) prev_time,
	
	lead(pay_time,1,'1970-01-01') over(PARTITION by name order by pay_time) next_time
from or_order;
 

（6）FIRST_VALUE(col,true/false)

当前窗口下的第一个值，第二个参数为true，跳过空值。

（7）LAST_VALUE (col,true/false)

当前窗口下的最后一个值，第二个参数为true，跳过空值。

SQL案例：查询用户每个月第一次的购买时间 和 每个月的最后一次购买时间

select
	FIRST_VALUE(pay_time) 
	    over(
	        partition by user_id,month(pay_time) order by pay_time 
	        rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
	        ) first_time,
	
	LAST_VALUE(pay_time) 
	    over(partition by user_id,month(pay_time) order by pay_time rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
	    ) last_time
from or_order;
 

（8）NTILE(n)

把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。（用于将分组数据按照顺序切分成n片，返回当前切片值）

SQL案例：查询前25%时间的订单信息

select * from (
    select User_id,pay_time,money,
    
    ntile(4) over(order by pay_time) sorted
    
    from or_order
) t
where sorted = 1;
 

4个By

（1）Order By

全局排序，只有一个Reducer。

（2）Sort By

分区内有序。

（3）Distrbute By

类似MR中Partition，进行分区，结合sort by使用。

（4） Cluster By

当Distribute by和Sorts by字段相同时，可以使用Cluster by方式。Cluster by除了具有Distribute by的功能外还兼具Sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

在生产环境中Order By用的比较少，容易导致OOM。

在生产环境中Sort By+ Distrbute By用的多。

排序函数

（1）RANK()

排序相同时会重复，总数不会变

1
1
3
3
5

（2）DENSE_RANK()

排序相同时会重复，总数会减少

1
1
2
2
3

（3）ROW_NUMBER()

会根据顺序计算

1
2
3
4
5

日期函数

datediff：返回结束日期减去开始日期的天数

datediff(string enddate, string startdate) 
 
select datediff('2021-11-20','2021-11-22') 

date_add：返回开始日期startdate增加days天后的日期

date_add(string startdate, int days) 
 
select date_add('2021-11-20',3) 

date_sub：返回开始日期startdate减少days天后的日期

date_sub (string startdate, int days) 
 
select date_sub('2021-11-22',3)