【Hive】SQL语句大全

库操作

创建数据库

-- 创建一个数据库，在HDFS上的默认路径为/user/hive/warehouse/*.db
create database mydatabase;
-- 可以使用if exists判断数据库是否已存在(存在则不创建)
create database if not exists mydatabase;
-- 创建一个数据库，并指定其存放路径
create database mydatabase location '/mydatabase.db'; 
-- 创建一个数据库，指定一个已存在的文件夹(my)作为数据库内容的存放位置
create database mydatabase location '/databases/my/';

查询数据库

-- 显示所有数据库
show databases;
-- 模糊搜索
show databases like 'my*';
-- 查看信息
desc database mydatabase;
-- 查看详细信息
desc database extended mydatabase;
-- 切换当前数据库
use mydatabase;

修改数据库

可以修改一些其他的附加信息，不能修改元数据信息

-- 给数据库添加信息
alter database mydatabase set dbproperties('createtime'='202003');
-- 查看上述添加的信息
desc database extended mydatabase;

删除数据库

-- 删除一个空的数据库
drop database mydatabase;
-- 最好使用if exists判断数据库是否存在
drop database if exists mydatabase;
-- 如果数据库不为空,可以采用cascade命令强制删除
drop database mydatabase cascade;

表操作

创建表

-- 创建一张表
create table student(id int, name string);
-- 创建一张外部表
create external table student(id int, name string);
-- 创建表并设置表中数据的分隔符(以制表符为例)
create table student(id int, name string) 
row format delimited fields terminated by '\t';
-- 创建表并设置表中数组数据的分隔符(以制表符为例)
create table student(id int, name string) 
collection items terminated by "\t" ;

查看表

-- 查看当前数据库中的所有表
show tables;

修改表

-- 重命名表
alter table student rename to new_student;
-- 添加列(添加的列在分区字段之前,括号中使用逗号分隔添加多列)
alter table student add columns(gender string);
-- 更新列信息(旧字段名,新字段名,新字段类型都要写)
alter table student change column name name2 string;
-- 替换表中所有字段(将所有字段合并替换为一个字段)
alter table student replace columns(replace string);

删除表

drop table student;

内部表(管理表)和外部表

两者的区别

删除时，内部表把元数据和具体数据都删除，而外部表只删除元数据。

互相转换

注意：这里区分大小写，括号中的内容要大写！

如果不是大写，该属性会变成普通的附加属性。

-- 转为外部表
alter table student set tblproperties('EXTERNAL'='TRUE');
-- 转为内部表
alter table student set tblproperties('EXTERNAL'='FALSE');

分区表

分区在HDFS上对应一个独立的文件夹，属于元数据，但用法相当于一个字段，可以用来过滤

创建分区表

-- 创建一个表,并设置以"month"字段分区
create table student(id int, name string) 
partitioned by(month string);
-- 创建二级分区表
create table student(id int, name string)
partitioned by(month string, day string)

添加分区

-- 往分区表里添加一个分区
alter table student add partition(month='202003');
-- 往分区表里添加多个分区(以空格隔开)
alter table student add partition(month='202003') partition(month='202003');

往分区表中添加数据

-- 加上关键字partition(...)指定分区即可;如果没有该分区,则自动新建
load data local inpath'/opt/file.txt' into student partition(month='202003');
insert into student partition(month='202003') values(1,'abc');

查询分区表数据

-- 通过分区查找数据
select * from student where month='202003';
select * from student where month='202003' and day='01';

删除分区

-- 删除一个分区表里的分区
alter table student drop partition(month='202003');
-- 删除多个分区表里的分区(以逗号隔开)
alter table student drop partition(month='202003'),partition(month='202003');

查看分区

-- 显示所有分区
show partitions student;

修复分区

如果数据是通过HDFS直接上传到分区目录，如果分区没定义，则会查询不到刚上传的数据

-- 修复命令
msck repair table student;
-- 也可以直接让此目录成为分区目录(这里以month='20200316'为例)
alter table student add partition(month='20200316');

数据操作

数据导入

Load导入

-- 本地文件导入Hive表
load data local inputpath '/opt/student.txt' into table student;
-- HDFS文件导入Hive表(相当于移动文件到数据库所在的文件夹)
load data inputpath '/student.txt' into table student;
-- 也可以直接移动文件至HDFS中的Hive表目录下
hadoop fs -put student.txt /user/hive/warehouse/student
-- 导入,并覆盖所有表中数据
load data local inputpath '/opt/student.txt' overwrite into table student;
-- 建表时通过Location指定加载数据路径(文件夹)
create table student(id int, name string)
row format delimited fields terminated by '\t'
location '/dir';

Insert插入

-- 直接添加一条记录
insert into table student values(1,'abc');
-- 添加,并覆盖所有表中数据
insert overwrite table student values(1,'abc');

Import导入

只能导入被export导出的文件

-- 通过import导入数据
import table student2 from '/export/student';

数据查询

基本查询

-- 查询表中所有数据
select * from student;
-- 查询表中指定列数据
select id, name from student;
-- 将查询到的结果插入到其他表
insert into student2 select * from student;
-- 以查询到的结果创建新表
create table student2 as select id, name from student;
-- 以列别名显示(as可不写),使用别名还可以提升性能
select id as sid, name as sname from student;
-- 将查询到的id值加100后显示
select id+100 from student;
-- 常用函数(计数:count, 最大值:max, 最小值:min, 求和:sum, 平均数:avg)
select count(*) from student;
-- Limit语句用于限制返回的行数
select * from student limit 3;
-- Where语句用于过滤
select * from student where id = 1;

Floor 取整

-- 对123.123取整,结果为123
select floor(123.123)

Like 和 Rlike

like: 选择类似的值

% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。

rlike: Java的正则匹配

-- 查询姓“小”开头的学生
select * from student where name like '小%';
-- 查询姓名以“小”开头的学生，并且名字只有两个字
select * from student where name like '小_';
-- 查询age字段中只包含数字的那些记录
select * from student where age rlike '\\d+';

Distinct 去重

会将数据放入同一个Reducer，可能会报内存溢出，数据量大时慎用

-- 无论这个年龄段人数有多少,去重后只显示1个
select age,count(distinct age) from mydatabase.student group by age;

Group By 分组查询

-- 以字段age分组,配合count使用显示每组的个数
select age,count(*) from student group by age;
-- 以字段grade分组,配合avg使用显示每组age的平均数
select grade,avg(age) from student group by grade;
-- 先以age分组,再以gender分组,统计每个年龄段男女人数
select count(gender) from student group by age,gender;

Having 语句

where：对表中的列发挥作用，不可跟聚合函数

having：对查询结果中的列发挥作用，相当于二次筛选，可跟聚合函数，只能用于group byf分组统计语句

-- 以字段grade分组,显示age平均值大于18的grade
select grade from student group by grade having avg(age)>18;

Join 语句

只支持等值连接，不支持非等值连接

-- 假设有两张表:dept部门表和employee员工表
-- 内连接(只有都存在的数据才会显示)
-- 查询员工表和部门表编号相同的数据,并显示员工名字和部门名称
select employee.name,dept.name from employee join dept on dept.d_id=employee.d_id;
-- 左外连接(显示所有左表中有的数据)
select employee.name,dept.name from employee left join dept on dept.d_id=employee.d_id;
-- 右外连接(显示所有右表中有的数据)
select employee.name,dept.name from employee right join dept on dept.d_id=employee.d_id;
-- 满外连接(显示所有数据,不匹配的值使用NULL值代替)
select employee.name,dept.name from employee full join dept on dept.d_id=employee.d_id;

常用查询函数

NVL 空字段赋值

NVL(string1, replace_with)

如果string1为NULL，该函数返回replace_with的值，否则返回string1的值

-- 如果age为null,用18代替
select nvl(age,18) from student;
-- 替换的参数可以是字段,如果age为null,用id值代替
select nvl(age,id) from student;

时间类

Date_format

格式化时间 ，注意:只能匹配横杆 "-"

select date_format('2020-03-19','yyyy-MM-dd HH:mm:ss');
-- 结果: 2020-03-19 00:00:00

Date_add

时间跟天数相加，天数可以为负

select date_add('2020-03-19', 10); 
-- 结果: 2020-03-29

Date_sub

时间跟天数相减，天数可以为负

select date_sub('2020-03-19', 10);
-- 结果: 2020-03-09

Datediff

两个时间相减，结果为天数，注意：是参数1 - 参数2

时分秒不影响最后的结果

select datediff('2020-03-19', '2020-03-29');
-- 结果: -10
select datediff('2020-03-29', '2020-03-19');
-- 结果: 10
select datediff('2020-03-29 13:13:13','2020-03-19 12:12:12');
-- 结果: 10

CASE WHEN 语句

-- 判断，如果gender为'男'或'女'，分别设置1，最后统计每个年龄段男女人数
select
age,
sum(case gender when '男' then 1 else 0 end) male_count,
sum(case gender when '女' then 1 else 0 end) female_count
from student group by age;

IF 语句

-- 以下代码等价于上面的case when
select
age,
sum(if(gender='男',1,0)) male_count,
sum(if(gender='女',1,0)) female_count
from student group by age;

行转列

Concat

concat(string1/col, string2/col, …)

输入任意个字符串(或字段,可以为int类型等),返回拼接后的结果

select concat(id,'-',name,'-',age) from student;

Concat_ws

concat_ws(separator, str1, str2, …)

特殊形式的concat()，参数只能为字符串，第一个参数为后面参数的分隔符

select concat_ws('-', name, gender) from student;

Collect_set

collect_set(col)

将某字段进行去重处理，返回array类型；该函数只接受基本数据类型

select collect_set(age) from student;

列转行

Explode

explode(col)

将一列中复杂的array或map结构拆分成多行

-- 将上面collect_set后的结果使用explode拆分
select explode(ages)
from (select collect_set(age) as ages from student ) as n1;

Lateral View

LATERAL VIEW udtf(expression) tableAlias AS columnAlias

配合split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，并且对拆分后的结果进行聚合

-- 假设有如下movies表,字段名分别为movie(string)和category(array<string>)
-- movie	category
--《疑犯追踪》	悬疑,动作,科幻,剧情
--《海豹突击队》	动作,剧情,罪案
--《战狼2》	战争,动作,灾难
select movie, cate
from movies
lateral view explode(category) tmp_table as cate;
-- 结果:
--《疑犯追踪》	悬疑
--《疑犯追踪》	动作
--《疑犯追踪》	科幻
--《疑犯追踪》	剧情
--《海豹突击队》	动作
-- ...

窗口函数

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

注意：该函数会对结果数据产生影响(比如在over(order by id)中排序后,结果也会被排序)

CURRENT ROW：当前行；
n PRECEDING：往前 n 行数据；
n FOLLOWING：往后 n 行数据；
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING 表示到后面的终点；
LAG(col,n)：往前第 n 行数据；
LEAD(col,n)：往后第 n 行数据；
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，
对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

-- 几个参数的固定格式写法
-- 计算从当前行开始计算[2,4]行的gender数量
select *,count(gender) over(rows between 2 following and 4 following) from student;

假设有如下business表

name orderdate cost
------------------
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
------------------
需求 
（1）查询在 2017 年 4 月份购买过的顾客及总人数 
（2）查询顾客的购买明细及月购买总额 
（3）上述的场景,要将 cost 按照日期进行累加 
（4）上述的场景，分别累加每个用户每个月的开销
（5）查询顾客上次的购买时间 
（6）查询前20%时间的订单信息

1. 查询在 2017 年 4 月份购买过的顾客及总人数

   select 
   name,
   count(*) over() as all_person
   from business
   where date_format(orderdate,'yyyy-MM')='2017-04'
   group by name;
   

2. 查询顾客的购买明细及该用户月购买总额

   select 
   name,
   orderdate,
   date_format(orderdate,'yyyy-MM') this_month,
   cost,
   sum(cost) over(distribute by name, date_format(orderdate,'yyyy-MM')) as this_user_this_month_sum
   from business;
   

3. 上述的场景，要将 cost 按照日期进行累加

   select 
   name,
   orderdate,
   cost,
   sum(cost) over(distribute by name sort by orderdate)
   from business;
   

4. 上述的场景，分别累加每个用户每个月的开销

   select 
   name,
   orderdate,
   cost,
   sum(cost) over(distribute by name,month(orderdate) sort by day(orderdate))
   from business;
   

5. 查询顾客上次的购买时间

   -- lag的第三个参数:如果没有找到数据,用该参数代替,否则为NULL
   select
   name,
   orderdate,
   cost,
   lag(orderdate,1,'0000-00-00') over(distribute by name sort by orderdate)
   from business;
   

6. 查询前20%时间的订单信息

   -- 使用ntile函数分组实现该操作
   select * from
   (
       select
       name,
       orderdate,
       cost,
       ntile(5) over(order by orderdate) as sorted
       from business
   ) as tmp_table
   where sorted = 1;
   

Rank 排序

该函数配合OVER()使用

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

假设有如下score表

name  subject  score 
--------------------
小明	语文	87
小明	数学	95
小明	英语	68
小绿	语文	94
小绿	数学	56
小绿	英语	84
小红	语文	64
小红	数学	86
小红	英语	84
小蓝	语文	65
小蓝	数学	85
小蓝	英语	78
---------------------

-- 需求：计算每门学科成绩排名。 
select
*,
rank() over(distribute by subject sort by score desc),
dense_rank() over(distribute by subject sort by score desc),
row_number() over(distribute by subject sort by score desc)
from score;

Regexp_replace 正则替换

regexp_replace(string A, string B, replace)

将字符串A中的符合JAVA正则表达式B的部分替换为replace。

注意，在有些情况下要使用转义字符

-- 将字符串中的“/”替换为“-”
select regexp_replace('2020/03/21','/','-');
-- 结果:2020-03-21

数据排序

Order By 全局排序

整张表的排序，只有一个Reducer

-- 将数据按id值升序排序(默认升序,可以不写asc)
select * from student order by id asc;
-- 将数据按id值降序排序
select * from student order by id desc;

Sort By 内部排序

对每个Reducer进行排序，不影响全局结果集

直接使用会将结果平均分配给每个文件(避免数据倾斜)

一般配合Distribute By使用

-- 先设置reduce个数
set mapreduce.job.reduces=3;
-- 直接查看结果,看不出变化
select * from student sort by id;
-- 将排序结果导出到文件
insert overwrite local directory '/opt/datas/sort-out'
select * from student sort by id;

Distribute By 分区排序

类似MapReduce中的Partition分区，一般配合Sort By排序使用

需要分配多个reduce才能看到效果

注意：该语句需要写在 Sort By 语句之前！

-- 先设置reduce的个数
set mapreduce.job.reduces=3;
-- 先按照id值分区,再按照age值升序排序
insert overwrite local directory '/opt/datas/dis-out'
select * from student distribute by id sort by age;

Cluster By 排序

当 Distribute By 和 Sort By 字段相同时，可以使用 Cluster By 方式

该排序只能是升序排序

-- 以下两种写法等价
select * from student cluster by grade;
select * from student distribute by grade sort by grade;

分桶和抽样查询

分区针对的是数据的存储路径，分桶针对的是数据文件

创建分桶表

-- 创建分桶表
create table studentbucket (id int, name string, age int)
clustered by (id) into 4 buckets
row format delimited fields terminated by '\t';
-- 可以查看表结构获取bucket数量
desc formatted studentbucket;

在导入数据之前，要先设置一些属性

-- 开启分桶功能
set hive.enforce.bucketing=true;
-- 设置reduce个数为-1,会自动根据桶个数决定reduce数
set mapreduce.job.reduces=-1;

插入数据

-- 因为需要分区,所以要走mr任务的形式插入数据
-- 注意:load方法不走mr任务
-- 所以这里使用select其他表的数据进行插入
insert into table studentbucket select * from student;

分桶表抽样查询

抽样语法：TABLESAMPLE(BUCKET x OUT OF y)

注意：x的值必须小于等于y的值！

含义：x表示从哪个bucket开始抽取，

​ y表示总共抽取 (bucket数量 / y) 个bucket的数据，每隔一个y取下一个bucket

-- 抽样查询
-- 这里是从bucket1开始抽取一个bucket数量(4/4=1)的数据
select * from studentbucket tablesample(bucket 1 out of 4 on id);
-- 这里是从bucket1开始抽取2个bucket(第x=1和第x+y=3个bucket)的数据
select * from studentbucket tablesample(bucket 1 out of 2 on id);

数据导出

Insert 导出

-- 将查询的结果导出到本地
insert overwrite local directory '/opt/datas' select * from student;
-- 将查询的结果导出到本地,并按'\t'分割 
insert overwrite local directory '/opt/datas'
row format delimited fields terminated by '\t'
select * from student;
-- 将查询的结果导出到HDFS
insert overwrite directory '/opt/datas' select * from student;

Hadoop 命令导出

# 直接将HDFS的文件直接发送到本地
hadoop fs -get /user/hive/warehouse/student /opt/datas

Hive Shell 命令导出

# 通过linux中的重定向符将查询结果导出到文件
bin/hive -e "select * from student" > /opt/datas/student.txt;

Export 导出

-- 通过export导出至HDFS,并且保存了元数据
export table student to '/export/student';

数据删除

Truncate 删除

清空表中数据，只能删除内部表，不能删除外部表中的数据

-- 使用truncate清空表中数据
truncate table student;

函数

系统内置函数

-- 查看系统内置函数
show functions;
-- 查看系统内置函数的用法(split为例)
desc function split;
-- 查看系统内置函数的详细信息(split为例)
desc function extended split;

自定义函数

UDF

User-Defined-Function

一进一出

如：split，datediff

继承 org.apache.hadoop.ql.exec.UDF

实现 evaluate 方法

UDAF

User-Defined Aggregation Function

聚集函数，多进一出

类似：count/max/min

UDTF

User-Defined Table-Generating Functions

一进多出

如：lateral view explore()

继承 org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

实现三个方法 initialize，process，close

编程步骤

1. 继承org.apache.hadoop.ql.exec.UDF

2. 需要实现 evaluate 函数；evaluate 函数支持重载；

3. 在 hive 的命令行窗口创建函数

   • 添加 jar包

     add jar linux_jar_path
     

   • 创建 function

     create [temporary] function [dbname.]function_name AS class_name;
     

4. 在 hive 的命令行窗口删除函数

   Drop [temporary] function [if exists] [dbname.]function_name; 
   

5. 注意事项

   UDF 必须要有返回类型，可以返回 null，但是返回类型不能为 void；

Maven依赖

<dependencies> 
    <!--https://mvnrepository.com/artifact/org.apache.hive/hive-exec --> 
    <dependency> 
      <groupId>org.apache.hive</groupId> 
      <artifactId>hive-exec</artifactId> 
      <version>1.2.1</version> 
    </dependency> 
</dependencies>