【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');

-- 创建一个表,并设置以"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;

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

-- 本地文件导入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 into table student values(1,'abc');
-- 添加,并覆盖所有表中数据
insert overwrite table student values(1,'abc');

-- 通过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;

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

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

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

-- 以字段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;

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

-- 假设有两张表: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;

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

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

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

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

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

-- 判断，如果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;

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

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

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

select collect_set(age) from student;

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

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

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

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%时间的订单信息

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;

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

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

-- 先设置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;

-- 先设置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;

-- 以下两种写法等价
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;

-- 抽样查询
-- 这里是从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 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;

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

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

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

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

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

<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>