Hive中的HQL语句

一、 DML语句

1、 hive数据表中数据增加方式

• 通过hdfs上传命令将一个符合表字段分割要求的文件上传到数据库所在路径，数据也可以通过数据库命令查询到，但该方式没有经过hive的元数据库，会导致一些计算查询不到结果
• load装载数据

load data [local] inpath "路径" [overwrite] into table table_name [partition(分区信息)]

• insert增加数据

insert into table(字段列表) values(字段值)

• 创建表的时候增加数据

create table table_name as select 查询语句

• Sqoop软件导入数据
• import命令增加数据

import table table_name [partition(分区信息)] from "hdfs路径信息"

2、 hive数据表数据导出方式

• 通过insert命令导出

insert overwrite[local] directory 'path' select语句

• 通过hdfs的get命令导出
• 通过export命令导出

export table  table_name to "路径"

• sqoop软件导出数据

注意：hive不支持update 更新表数据，也不支持使用delete删除表数据，支持清空表数据truncate

二、 DQL语法

1、 单表查询

select [distinct] 查询列表
from table_name
where 筛选条件
group by 分组字段 having 分组后的筛选
order by 字段 asc/desc
cluster by 列字段 | distribute by 字段 | sort by 字段
limit num

2、 多表查询

• union 将两个查询结果连接起来 （ 联合查询）
• left join | right join | inner join | full join （连接查询）

3、 排序查询

• order by
  代表对最终的查询结果数据进行排序
  适用于只有一个输出文件结果的情况下，类似于MR程序中的全排序，只有一个输出结果文件，一个结果文件内部有序

• sort by
  可以让HQL转成的MR程序的reduce任务数有多个
  sort by保证多个reduce任务输出有序

• distribute by
  用来指定分区字段，代表怎么对数据进行分区，分区规则默认使用hashPartitioner，必须与sort by 结合使用

• cluster by
  当distribute by 和sort by 使用的同一个字段时，可以用cluster by来代替，但是不能指定排序规则，默认使用升序排序

注意：当我们想要查询整体有序，使用order by；当我们数据量大，需要多个reduce任务运行，并且保证每个reduce分区有序，则使用distribute by 分区字段 sort by 排序字段 asc|desc；当分区字段和排序字段一致，而我们只关注分区数据有序，但什么顺序不关注时可以使用cluster by

三、 hive中的函数

1、 函数的分类

• UDF：一进一出
• UDTF：一进多出
• UDAF：多进一出

hive中一些函数的相关命令

• show functions：查看hive中的自带函数
• desc function：函数名
• desc function extended 函数名

2、 使用方式

• 字符串相关的函数

1. UDF函数

concat/concat_ws
instr
length
lower/upper
lpad/rpad
ltrim/rtrim/trim
replace
reverse
substr/substring
split
parse_url(url,part)
part常用取值：HOST,  PATH,   QUERY,  PROTOCOL

2. UDTF函数

parse_url_tuple(url, part1,part2)
和parse_url函数的不同点在于这个函数是一个一对多函数
partname：HOST,  PATH,  QUERY,  PROTOCOL,   QUERY:<KEY_NAME>

• 数值有关的

1. UDF函数

abs
ceil
floor
mod
pow
round

2. UDAF函数

sum/avg/count/max/min

• 特殊函数

1. explode

传递一个数组，把数组转换成一行多列的数据集
传递一个map集合，将集合转换成两列多行的数据集
一旦使用explode函数，此时就不能查询表中的其他字段了

2. lateral view

语法：
select xxx from a lateral view UDTF tableb as columnname
作用：
将UDTF函数得到的多行数据组成一个虚拟表格，然后与原表进行join操作，将虚拟数据加入到原表查询结果中
使用场景：
表中有一个字段含有多个值，对每个字段中的每个值需要进行处理

3. 开窗函数

一般要与聚合函数或者排名函数结合使用：
select sum/count  over() from table_name
over开窗函数，决定将表数据分成几个窗口
over(partition by 分区字段 order by 排序字段)

和排名函数结合使用
row_number()  值相同时也会自增填充   12345
rank()   值相同时，排名相同，下一条数据跳过一个值再填充  11345
dense_rank()   值相同时，排名相同，不会跳过一个值再填充   11234

• 自定义函数