2. Hive常见操作命令整理

该笔记主要整理了《Hive编程指南》中一些常见的操作命令，大致如下（持续补充中）：

1. 查看/设置/修改变量
2. 执行命令
3. 搜索相关内容
4. 查看库表信息
5. 创建表
6. 分区
7. 修改表（重命名、修改列、删除列、增加列）
8. 找到表位置并导出至本地
9. 去空格
10. case...when...then句式
11. 操作符
12. group by...having句式
13. JOIN
14. order by和sort by
15. 抽样查询
16. 视图
17. 分桶表
18. 函数
19. with...as
20. rank() over (partition by ... order by ... asc/desc)
21. 时间相关的函数（from_unixtime，unix_timestamp，date_add，months_between）
22.substr()
23.regexp_replace()
24.动态分区

1. 查看/设置/修改变量

$ hive

############
# 查询环境变量
############
hive>set env:HOME;
# 打印命名空间hivevar, hiveconf, system和env所有变量
hive>set;
# 还打印Hadoop所定义的所有属性
hive>set -v;

############
# 设置hive变量
############
hive>set hivevar:foo=bar
# 查看刚设置的变量
hive>set foo;
hive>set hivevar:foo;

############
# 修改属性变量
############
# hiveconf: Hive相关的配置属性
# 不进入Hive进行配置属性修改
hive --hiveocnf hive.cli.print.current.db=true
# 进入hive进行配置修改
hive>set hiveconf:hive.cli.print.current_db=true;

2. 执行命令

# 执行命令方式1：使用下面的 “一次使用”命令(-e是指一次执行，-S是指静默模式，在输出结果中不显示Ok和Time taken字段)
hive -e -S "select * from mytable limit 3";

# 执行命令方式2：调用Hive执行hql文件
hive -f /path/query.hql

# 执行命令方式3：在Hive shell内执行hql文件
$ hive
hive>source /path/query.hql

3. 搜索相关内容

# 模糊搜索set命令的输出结果中某个于warehouse相关的属性
$ hive -S -e "set" | grep warehouse
hive.metastore.warehouse.dir=/user/hive/warehouse
hive.warehouse.subdir.inherit.perms=false

4. 查看库表信息

# 查看数据库，使用like是以ahf开头，以其他字符结尾(即.*)的数据库名
show databases;
show databases like 'ahf.*';

# 查看表的详细表结构信息(formatted比extended输出内容更详细且可读性更高)
describe mydb.table;
describe formatted mydb.table;
describe extended mydb.table;

5. 创建表

# 拷贝表结构，而不拷贝数据（用like）
create table if not exists mydb.mytable like mydb.mytable2;

# 拷贝表结构，且拷贝数据（用as）
create table if not exists mydb.mytable as
select * from mydb.mytable2;

# 直接创建表结构
create table if not exists mydb.mytable
(
    id         string comment 'id',
    name    string comment '姓名'
)
partitioned by (class string)
stored as orc;

6. 分区

# 建立分区
create table
(字段1 字段类型, 字段2 字段类型)
paritioned by (字段名3 字段类型, 字段名4 字段类型);

# 查看分区
show partitions table_name；

# 查看某个特定分区键的分区
show partitions table_name partition(一个分区字段='该分区字段下的某个值');

# 增加分区
alter table log_message add partition(year=2012, month=1, day=2);

# 删除分区
alter table log_messages drop if exists partition(year=2012, month=1, day=2);

7. 修改表（重命名、修改列、删除列、增加列）

# 表重命名
alter table log_messages rename to logmsgs;

# 修改列信息, 在下面的例子中，
# 我们将字段hms重命名为hour_minutes_seconds，修改其类型和注释，再转移到severity字段之后
# 如果用户想将这个字段移动到第一个为位置，只需要使用first关键字替换after severity
alter table log_messages change column hms hour_minutes_seconds int
comment 'the hours, minutes, and seconds parts of the timestamp'
after severity;

# 删除或替换列, 下面的例子移除了之前所有字段并重新指定了新的字段
alter table log_messages replace columns
(
   新字段1    int        comment '...',
   新字段2    string    comment '...'
);

# 增加列
alter table log_messages add columns(
app_name string comment 'application name',
session_id long comment 'the current session id'
);

8. 找到表位置并导出至本地

# 找到表位置
describe formatted mydb.mytable;
# 例如：Location:
hdfs://alg-hdfs/warehouse/user/alvinai.mydb/mytable

# 导出文件
hadoop fs -cp [表来源] [目标导出路径]

9. 去空格

# 去空格 ltrim去左空格，rtrim去右空格
ltrim(string s)

10. case...when...then句式

select name,
         salary,
         case when salary < 5000.0 then 'low'
              when salary >= 5000.0 and salary < 70000.0 then 'middle'
              else 'high'
              end as bracket
from employees;

11. 操作符

# 常见的谓语操作符
A<>B跟A!=B是一样的
A<==>B是指任一为NULL，则结果为NULL
A is null还有A is not null
a not between b and c, between是闭区间

# LiKE和RELIKE谓语操作符
A like B, A relike B前者是SQL，后者是JAVA的正则表达式
A like B, ‘x%’表示A必须以字母x开头，‘%x’表示A必须以字母x结尾，‘%x%’表示A包含字母x，可以位于开头结尾或者字符串中间
A relike B， .号表示任意字符，*表示重复左边的字符串零次到无数次，表达式（x|y）表示x或者y匹配
例子:查找住址以Ave结尾的人
select name, address from where employees where address like 'Ave.';
例子:查找地址以0开头的人
select name, address from where employees where address like 'O%';

# split操作符
select split(row_key, '_')[0] as account_id

12. group by...having句式

# 如果想要对group by语句产生的分组进行条件过滤，可以用having例如：
select year(ymd),
       avg(price_close) from stocks
where exchange = 'NASDAQ' and symbol = 'AAPL'
group by year(ymd)
having avg(price_close) > 0.0;

13. JOIN

Hive连接有：

• inner join: 交集，就是join。扩展：MySQL交集用inersect，Hive用inner join。
• left outer join: 就是left join。
• right outer join: 就是right join。
• full outer join: 并集，就是全外连接。
• left semi join：左半开连接，Hive没有右半开连接，它与left join的差异就是对待右表中重复key的处理方式差异：因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 left join on 则会一直遍历，这就是left outer join和left semi join的区别。
• join：笛卡尔积连接。
• union all：将2个或多个表进行合并，每一个union子查询都必须具有相同列，而且对应的每个字段的字段类型必须是一致的。
• 其它连接：map-side JOIN（Hive在map端执行连接过程，将小表完全放到内存中），sort-merge JOIN（分类-合并连接）。

union all的例子：
select log.ymd, log.level, log.message
from(
    select l1.ymd, l1.level, l1.message, 'Log1' as source from log1 l1
    union all
    select l2.ymd, l2.level, l2.message, 'Log2' as source from log2 l2
) log
sort by log.ymd asc

使用JOIN时要注意：

• 由于表之间关联关系是一对多时，出现的数据重复问题。例如如果右表有重复数据（即多条数据对应左边表一条数据），那么重复数据会全部保留。例如使用left join是连接两表(左表A，右表B)， 它会保留左表A有数据但是右表B为空的数据也就是说只要右表B重复数据（多条数据对应左边表一条数据），那么会全部保留。
• inner join也可能导致数据重复，如果是一对以关系，使用后也请注意查重确保正常连接，在使用过程中建议在select后加distinct或者group by去重下。

14. order by和sort by

• order by对查询结果进行全局排序。
• sort by对查询结果进行局部排序，因为它只会在每个reducer中对数据进行排序，但并非全局有序。

如果reducer数量只有一个，sort by跟order by得到的结果一样，而且速度更快。
distribute by规定map端如何分发数据到同一个reducer中进行处理，然后使用sort by对数据进行排序。Hive要求distribute by语句要写在sort by前面。简单来说sort by得搭配distribute by一起使用。例子如下：

select s.ymd, s.symbol, s.price_close
from stocks s
distributed by s.symbol
sort by s.symbol asc, s.ymd asc;

另外，cluster by等价于distribute by和sort by的搭配，但只允许降序排序。使用distribute by... sort by语句或者简化版的cluster by语句会剥夺sort by的并行性，但可以实现输出文件的数据是全局排序。

15. 抽样查询

抽样有两种：随机抽样，分桶抽样和数据块抽样。

随机抽样：使用order by或cluster都行。

# 使用order by，即全局随机抽样
hive>create table mytable as
select *
from mytable2
order by rand()
limit 100;

# 使用cluster by，即局部随机抽样，这比sort by快，因为不需要distribute by rand() 和sort by rand() 进行了两次随机，cluster by rand() 仅一次随机
hive>create table mytable as
select *
from mytable2
cluster by rand()
limit 10000;

分桶抽样：举个例子，假设numbers表只有number字段，其值是1-10。我们可以用下面的分桶语句来抽样，分桶语句中的分母表示的是数据将会被散列的桶的个数，而分子表示将会选择的桶编号。简单来说它把10个数分到2个桶里，1个桶就是5个数据。

hive>select * from numbers tablesample(bucket 1 out of 2 on number) s;
2
4
6
8
10
hive>select * from db.table tablesample(bucket 1 out of 10 on rand());

数据块抽样：按照抽样百分比（至数据大小占比，而不是行数） tablesample(n percent) /大小 tablesample(n M) /行数 tablesample(n rows)进行抽样。这种抽样的最小抽样单元是一个HDFS数据块，如果表的数据大小小于普通块大小128MB的话，那么将会返回所有行。

hive>select * from numbersflat tablesample(0.1 percent) s;

tablesample(n rows) 实际返回的行数可能会有很大差异，具体原因可参考，TABLESAMPLE returns wrong number of rows?，原因在上面也解释了，涉及数据块大小的因素。为了解决这个可以考虑使用下面的代码：

select * from (select * from table tablesample(1000 rows)) a limit 1000

*注意： select TOP 10000 * from tabData TABLESAMPLE(10000 ROWS) REPEATABLE(100); 不能用，因为Hive无法识别TOP和REPEATABLE操作词，它们是SQL Server的语法。另外，Hive在使用嵌套子查询是要记得加别名，如上面的a。

16. 视图

创建视图来降低查询复杂度，避免用户通过重用重复的部分来构建复杂的查询，能帮助减少多重嵌套子查询。

# 创建视图
create view shorter join as
select * from people join cart
on cart,people_id=people_id
where firsetname='john';

# 删除视图
drop view if exists view_name;

17. 分桶表

# 建立分桶表
# 例如使用user_id作为分桶字段，则字段值会根据用户指定的值进行哈希分发到同种。同一个user_id下的记录通常会存储到同一个桶内。假设用户数要比桶数多，那么每个桶就会包含多个用户的记录
create table weblog (user_id int, url string, source_ip string)
partitioned by (dt string)
clustered by (user_id) into 96 buckets;

# 给分桶表插入分区数据 - 为了强制Hive为目标表的分桶初始化设置一个正确的reducer个数
hive> set hive.enfore.bucketing = true;
hive> from raw_logs
insert overwrite table weblog
partition (dt='2009-02-25')
select user_id, url, source_ip where dt='2009-02-25';

给分桶表插入数据 - 如果没有使用hive.enfore.bucketing，我们就需要自己设置和分桶个数相匹配的reducer个数，例如
 set mapred.reduce.tasks=96; 。然后再insert语句中，需要在select语句后增加cluster by语句。

之所以要给分桶表做以上的操作，是因为对于所有表的元数据，指定分桶并不能保证表可以正确地填充，用户可以根据前面的示例来确保是否正确地填充了表。分桶地好处在于：有利于执行高效地map-side join。

18. 函数

UDF是指用户自定义函数，显示当前Hive会话所加载的所有函数名称。

show functions；

# 展示concat函数的介绍
describe function concat;
describe function extended concat;

19. with...as

with t1 as (...),
t2 as (...)
select ...
from ...
where

20. rank() over (partition by ... order by ... asc/desc)

OVER()窗口函数常搭配聚合函数（例：sum(), rank()等）。OVER()：指定分析函数工作的数据窗口大小，决定了聚合函数的范围。举个简单的例子：我们想知道各班级下，各小组里，同学的成绩排名。

rank() over(parition by class_no, group_no order by student_grade desc) as student_group_rank

21. 时间相关的函数（from_unixtime，unix_timestamp，date_add）

如果我想将时间‘20200320‘加一天并转为’20200321‘该怎么办？转换前先了解下面三个函数：

UNIX_TIMESTAMP(date)  ：一个DATE转为Unix timestamp时间格式。

DATE_ADD()  ：函数向日期添加指定的时间间隔，只支持’yyyy-mm-dd‘格式的操作。

FROM_UNIXTIME(unix_timestamp,format) ：返回表示Unix时间标记的一个字符串，根据format字符串格式化。

完整转换代码（先将20200320转为unix timestamp->转为2020-03-20格式->加一天为2020-03-21->转为20200321）：

from_unixtime(unix_timestamp(date_add(from_unixtime(unix_timestamp(’20200320‘,'yyyymmdd'),'yyyy-mm-dd'),1),'yyyy-mm-dd'),'yyyymmdd')

那如果想找到两个时间之间的月份间隔长度，可以使用 MONTHS_BETWEEN 函数：

floor(months_between(from_unixtime(unix_timestamp(start_dayno,'yyyymmdd'),'yyyy-mm-dd'),from_unixtime(unix_timestamp(end_dayno,'yyyymmdd'),'yyyy-mm-dd'))) as time_period

22. substr()

substr(待截取字符串，截取起始点，截取长度) ，注意Hive的字符串起点是1，而不是0。另外， substr() 结果与另外一个数做比较时，会忽略掉首位空格。例如：  substr(' 2020/7/29 9:13', 1, 10) ='2020/7/29 '与'2020/7/29'相等。

23. regexp_replace()

regexp_replace(rtrim(substr(log_map['StartTime'],1,10)),'/','') as dayno

select处将结尾空格去掉，替换/分隔符为空。即2020/07/29 22:56:44 -> 20200729。

24. 动态分区

如果你需要将从一个分区大表里抽取数据放置另外一张新表且新表要为分区表(源表和新表)，那怎么办？

• 首先，建新表表结构（若源表和新表表结构没区别，可以用 create table mydb.mytable like source_db.table ）;
• 然后插入数据有两种方式，第一种是构建调度，一天天写入分区。第二种是使用动态分区，设置动态分区字段，让Hive自动根据分区字段插入对应分区的数据进去。第二种方法比较省事，因为不需要建调度。

set hive.exec.dynamic.partition=true;
set hive.exec.max.dynamic.partitions=10000;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table mydb.mytable (partition_col)
select * from source_db.table

需要注意的一件事：当你使用静态分区时，partition_col不需要写在字段构建区内，而使用动态分区时，建表要将partition_colx写在字段构建区最后的位置上，不然动态分区差数会报错说建表字段数和插入字段数不匹配。

上述代码有几个设置需要注意：

set hive.exec.dynamic.partition=true; ：默认为false即静态分区，若要使用动态分区，请设置true。

set hive.exec.max.dynamic.partitions=10000; ：默认1000。表示一个动态分区语句可以创建的最大动态分区个数，超出报错。

set hive.exec.dynamic.partition.mode=nonstrict; ：这个属性默认值是strict, 就是要求分区字段必须有一个是静态的分区值。

补充：实际使用中，上述方法不是最优法，因为一段源表抽取数据太大，会报错OOM。所以为了优化，改为静态方法，用while去依次抽取小部分数据，然后插入一个分区，然后再抽再写入下个分区。实现伪代码如下：

# 用shell脚本跑
v_start_dayno='20201010'
v_end_dayno='20201110'

while [ ${v_start_dayno} -le ${v_end_dayno} ]
do

hql="insert overwrite table mydb.mytable (dayno=${v_start_dayno})
select * from source_db.table
where dayno = '${v_start_dayno}';"
执行hql函数

v_start_dayno=`date -d "${v_start_dayno} +1 day" +%Y%m%d`
done