启动类
| 功能说明 | 命令 |
|---|---|
| 启动hiveserver2服务 | bin/hiveserver2 |
| 启动beeline | bin/beeline |
| 连接hiveserver2 | beeline> !connect jdbc:hive2://hadoop102:10000 |
| metastroe服务 | bin/hive --service metastore |
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
常用交互命令
| 功能说明 | 命令 |
|---|---|
| 不进入hive的交互窗口执行sql | bin/hive -e "sql语句" |
| 执行脚本中sql语句 | bin/hive -f hive.sql |
| 退出hive窗口 | exit 或 quit |
| 命令窗口中查看hdfs文件系统 | dfs -ls / |
| 命令窗口中查看hdfs文件系统 | ! ls /data/h |
SQL类(特殊的)
| 说明 | 语句 |
|---|---|
| 查看hive中的所有数据库 | show databases |
| 用default数据库 | use default |
| 查询表结构 | desc table_name |
| 查看数据库 | show databases |
| 重命名表名 | alter table table1 rename to table2 |
| 修改表中字段 | alter table table_name change name user_name String |
| 修改字段类型 | alter table table_name change salary salary Double |
| 创建外部表 | create external table .... |
| 查询外部表信息 | desc formatted outsidetable |
| 创建视图 | create view view_name as select * from table_name ..... |
| 添加数据 | load data local inpath 'xxx' overwrite into table table_name partition(day='2021-12-01') |
内置函数
（1） NVL
给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL
select nvl(column, 0) from xxx；
（2）行转列
| 函数 | 描述 |
|---|---|
| CONCAT(string A/col, string B/col…) | 返回输入字符串连接后的结果，支持任意个输入字符串 |
| CONCAT_WS(separator, str1, str2,...) | 第一个参数参数间的分隔符，如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间。 |
| COLLECT_SET(col) | 将某字段的值进行去重汇总，产生array类型字段 |
| COLLECT_LIST(col) | 函数只接受基本数据类型，它的主要作用是将某字段的值进行不去重汇总，产生array类型字段。 |
（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再把结果组合，产生一个支持别名表的虚拟表。
准备数据源测试
| movie | category |
|---|---|
| 《将夜》 | 记录,剧情 |
| 《红海行动》 | 战争,动作,灾难 |
SQL
SELECT movie,category_name
FROM movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name ;
测试结果
《将夜》 记录
《将夜》 剧情
《红海行动》 战争
《红海行动》 动作
《红海行动》 灾难
窗口函数
（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)
Impala(时间处理，可类比到hive可用)
1. 获取当前时间
timestamp 样式2021-12-10 04:36:48.147046000
bigint 样式 1639110956
| 函数/方法 | 输出类型 | 说明/样式 |
|---|---|---|
| current_timestamp() | timestamp | 所在时区的当前时间 |
| now() | timestamp | 所在时区的当前时间 |
| unix_timestamp() | bigint | 所在时区的当前时间戳 |
| utc_timestamp | timestamp | UTC时区的当前时间 |
| timeofday() | string | Fri Dec 10 12:39:46 2021 CST |
2. 获取时间指定单位函数
| 序号 | 函数/方法 | 输出类型 | 说明/样式 |
|---|---|---|---|
| 1 | year(timestamp/date) | int | 获取年 yyyy |
| 2 | quarter(timestamp/date) | int | 获取季节（1,2,3,4） |
| 3 | month(timestamp/date) | int | 获取月 |
| 4 | monthname(timestamp/date) | string | 获取月份名称 December |
| 5 | week(timestamp/date) | int | 获取周（1-53） |
| 6 | weekofyear(timestamp/date) | int | 获取周（1-53） |
| 7 | dayofweek(timestamp/date) | int | 获取天（本周第多少天,周日算第一天） |
| 8 | dayname(timestamp/date) | string | 获取天（星期几）Friday |
| 9 | next_day(timestamp/date, 10 | day(string)) | timestamp/date |
| 11 | day(timestamp/date) | int | 获取天（本月第多少天） |
| 12 | dayofmonth(timestamp/date) | int | 获取天（本月第多少天） |
| 13 | last_day(timestamp/date) | timestamp/date | 获取天（本月的最后一天日期） |
| 14 | dayofyear(timestamp/date) | int | 获取天（本年第多少天） |
| 15 | hour(timestamp/date) | int | 获取小时 |
| 16 | minute(timestamp date) | int | 获取分钟 |
| 17 | second(timestamp date) | int | 获取秒 |
| 18 | millisecond(timestamp date) | int | 获取毫秒 |
| 19 | extract (YEAR FROM timestamp) | bigint | 获取参数指定的时间单位 YEAR MONTH DAY HOUR MINUTE SECOND |
| 20 | date_part('year',timestamp) | bigint | 获取参数指定的时间单位 YEAR MONTH DAY HOUR MINUTE SECOND |
| 21 | trunc(timestamp/date,unit) | timestamp/date | 获取截断为指定单位的时间 |
| unit | 截取说明 |
|---|---|
| SYYYY，YYYY，YEAR，SYEAR，YYY，YY，Y | 年 |
| Q | 季节 |
| MONTH，MON，MM，RM | 月 |
| WW | 最近的日期是与一年中的第一天相同的日期 |
| W | 最近的日期是与该月的第一天相同的星期几 |
| DDD，DD，J | 天 |
| DAY，DY，D | 星期几（星期一）的开始 |
| HH，HH12，HH24 | 小时 |
| MI | 分钟 |
3. 时间比较函数
| 序号 | 函数/方法 | 输出类型 | 说明/样式 |
|---|---|---|---|
| 1 | datediff(timestamp enddate,startdate) | int | 返回endDate比startDate多多少天 |
| 2 | int_months_between(timestamp t1,t2) | int | 返回两个日期相差的整数月份个数 |
| 3 | months_between(timestamp t1,t2) | double | 返回浮点数的月数相差的数 |
| 4 | date_cmp(DATE date1, DATE date2) | int | 比较是否相等，返回-1,0,1,null四种数值 |
| 5 | timestamp_cmp(timestamp t1，timestamp t2) | int | 比较是否相等，返回-1,0,1,null四种数值 |
4. 时间格式转换函数
| 序号 | 函数/方法 | 输出类型 | 说明/样式 |
|---|---|---|---|
| 1 | to_date(timestamp date) | string | 返回时间戳对应的date |
| 2 | to_timestamp(bigint unixtime) | timestamp | 返回整数对应的timestamp值 |
| 3 | to_timestamp(string date，string pattern) | timestamp | 返回字符串对应的timestamp值 |
| 4 | to_utc_timestamp(timestamp t，string timezone) | timestamp | 指定时区的时间戳转化为UTC时区的时间戳 |
| 5 | from_timestamp(timestamp t，string pattern) | string | 把timestamp按照pattern进行格式化 |
| 6 | from_timestamp(string date，string pattern) | string | 把date按照pattern进行格式化 |
| 7 | from_unixtime(bigint unixtime) | string | 把时间戳秒数转化为本地地区中的字符串 |
| 8 | from_unixtime(bigint unixtime，string pattern） | string | 时间戳转化为本地时区字符串，pattern格式 |
| 9 | from_utc_timestamp（timestamp t，string timezone） | timestamp | UTC时区指定时间戳转化为指定时区时间戳 |
| 10 | unix_timestamp(string datetime) | bigint | 把string类型的date或日期转化成时间戳Unix |
| 11 | unix_timestamp(timestamp datetime) | bigint | 把string类型的timestamp转化成时间戳Unix |
| 12 | unix_timestamp(string datetime，string pattern) | bigint | 日期按pattern转化成时间戳Unix |
5. 时间计算函数
| 序号 | 函数/方法 | 输出类型 | 说明/样式 |
|---|---|---|---|
| 1 | years_add(timestamp/date date, int/bigint years) | timestamp/date | 增加指定年数 |
| 2 | years_sub(timestamp/date date, int/bigint years) | timestamp/date | 减少指定年数 |
| 3 | months_add(timestamp/date date, int/bigint months) | timestamp/date | 增加指定月数 |
| 4 | months_sub(timestamp/date date, int/bigint months) | timestamp/date | 减少指定月数 |
| 5 | add_months(timestamp/date date, int/bigint months) | timestamp/date | 增加指定月数 |
| 6 | weeks_add(timestamp/date date, int/bigint weeks) | timestamp/date | 增加指定周数 |
| 7 | weeks_sub(timestamp/date date, int/bigint weeks) | timestamp/date | 减少指定周数 |
| 8 | days_add(timestamp/date startdate, int/bigint days) | timestamp/date | 增加指定天数 |
| 9 | days_sub(timestamp/date startdate, int/bigint days) | timestamp/date | 减少指定天数 |
| 10 | date_add(timestamp/date startdate, int/bigint days) | timestamp/date | 增加指定天数 |
| 11 | date_sub(timestamp/date startdate, int/bigint days) | timestamp/date | 减少指定天数 |
| 12 | adddate(timestamp/date startdate, int/int days) | timestamp/date | 增加指定天数 |
| 13 | subdate(timestamp/date startdate，bigint/int days) | timestamp/date | 减少指定天数 |
| 14 | hours_add(timestamp date, int/bigint hours) | timestamp | 增加指定小时 |
| 15 | hours_sub(timestamp date, int/bigint hours) | timestamp | 减少指定小时 |
| 16 | minutes_add(timestamp date, int/bigint minutes) | timestamp | 增加指定分钟 |
| 17 | minutes_sub(timestamp date, int/bigint minutes) | timestamp | 减少指定分钟 |
| 18 | seconds_add(timestamp date, int/bigint seconds) | timestamp | 增加指定秒数 |
| 19 | seconds_sub(timestamp date, int/bigint seconds) | timestamp | 减少指定秒数 |
| 20 | milliseconds_add(timestamp t, int/bigint s） | timestamp | 增加指定毫秒数 |
| 21 | milliseconds_sub(timestamp t, int/bigint s） | timestamp | 减少指定毫秒数 |
| 22 | microseconds_add(timestamp t, int/bigint s) | timestamp | 增加指定微秒数 |
| 23 | microseconds_sub(timestamp t, int/bigint s) | timestamp | 减少指定微秒数 |
| 24 | nanoseconds_add(timestamp t, int/bigint s） | timestamp | 增加指定纳秒数 |
| 25 | nanoseconds_sub(timestamp t, int/bigint s） | timestamp | 减少指定纳秒数 |
| 26 | date_add(timestamp/date startdate, interval_expression) | timestamp/date | 使用参数计算日期增量值（增加） |
| 27 | date_sub(timestamp/date startdate, interval_expression) | timestamp/date | 使用参数计算日期增量值（减少） |
