【Hive】SQL语句大全

大家好，又见面了，我是你们的朋友全栈君。

文章目录

• 库操作
  • 创建数据库
  • 查询数据库
  • 修改数据库
  • 删除数据库
• 表操作
  • 创建表
  • 查看表
  • 修改表
  • 删除表
  • 内部表(管理表)和外部表
    • 两者的区别
    • 互相转换
  • 分区表
    • 创建分区表
    • 添加分区
    • 往分区表中添加数据
    • 查询分区表数据
    • 删除分区
    • 查看分区
    • 修复分区
• 数据操作
  • 数据导入
    • Load导入
    • Insert插入
    • Import导入
  • 数据查询
    • 基本查询
      • Floor 取整
      • Like 和 Rlike
      • Distinct 去重
      • Group By 分组查询
      • Having 语句
      • Join 语句
    • 常用查询函数
      • NVL 空字段赋值
      • 时间类
      • Date_format
      • Date_add
      • Date_sub
      • Datediff
      • CASE WHEN 语句
      • IF 语句
      • 行转列
      • Concat
      • Concat_ws
      • Collect_set
      • 列转行
      • Explode
      • Lateral View
      • 窗口函数
      • Rank 排序
      • Regexp_replace 正则替换
  • 数据排序
    • Order By 全局排序
    • Sort By 内部排序
    • Distribute By 分区排序
    • Cluster By 排序
    • 分桶和抽样查询
      • 创建分桶表
      • 分桶表抽样查询
  • 数据导出
    • Insert 导出
    • Hadoop 命令导出
    • Hive Shell 命令导出
    • Export 导出
  • 数据删除
    • Truncate 删除
• 函数
  • 系统内置函数
  • 自定义函数
    • UDF
    • UDAF
    • UDTF
    • 编程步骤
    • Maven依赖

库操作

创建数据库

代码语言：javascript复制

-- 创建一个数据库，在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/';

查询数据库

代码语言：javascript复制

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

修改数据库

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

代码语言：javascript复制

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

删除数据库

代码语言：javascript复制

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

表操作

创建表

代码语言：javascript复制

-- 创建一张表
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" ;

查看表

代码语言：javascript复制

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

修改表

代码语言：javascript复制

-- 重命名表
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);

删除表

代码语言：javascript复制

drop table student;

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

两者的区别

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

互相转换

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

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

代码语言：javascript复制

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

分区表

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

创建分区表

代码语言：javascript复制

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

添加分区

代码语言：javascript复制

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

往分区表中添加数据

代码语言：javascript复制

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

查询分区表数据

代码语言：javascript复制

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

删除分区

代码语言：javascript复制

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

查看分区

代码语言：javascript复制

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

修复分区

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

代码语言：javascript复制

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

数据操作

数据导入

Load导入

代码语言：javascript复制

-- 本地文件导入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插入

代码语言：javascript复制

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

Import导入

只能导入被export导出的文件

代码语言：javascript复制

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

数据查询

基本查询

代码语言：javascript复制

-- 查询表中所有数据
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 取整

代码语言：javascript复制

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

Like 和 Rlike

like: 选择类似的值 % 代表零个或多个字符(任意个字符)。 _ 代表一个字符。 rlike: Java的正则匹配

代码语言：javascript复制

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

Distinct 去重

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

代码语言：javascript复制

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

Group By 分组查询

代码语言：javascript复制

-- 以字段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分组统计语句

代码语言：javascript复制

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

Join 语句

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

代码语言：javascript复制

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

代码语言：javascript复制

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

时间类

Date_format

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

代码语言：javascript复制

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

Date_add

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

代码语言：javascript复制

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

Date_sub

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

代码语言：javascript复制

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

Datediff

两个时间相减，结果为天数，注意：是参数1 – 参数2 时分秒不影响最后的结果

代码语言：javascript复制

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

代码语言：javascript复制

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

代码语言：javascript复制

-- 以下代码等价于上面的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类型等),返回拼接后的结果

代码语言：javascript复制

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

Concat_ws

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

代码语言：javascript复制

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

Collect_set

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

代码语言：javascript复制

select collect_set(age) from student;

列转行

Explode

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

代码语言：javascript复制

-- 将上面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一起使用，它能够将一列数据拆成多行数据，并且对拆分后的结果进行聚合

代码语言：javascript复制

-- 假设有如下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 类型。

代码语言：javascript复制

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

假设有如下business表

代码语言：javascript复制

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

Rank 排序

该函数配合OVER()使用 RANK() 排序相同时会重复，总数不会变 DENSE_RANK() 排序相同时会重复，总数会减少 ROW_NUMBER() 会根据顺序计算

假设有如下score表

代码语言：javascript复制

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

代码语言：javascript复制

-- 需求：计算每门学科成绩排名。 
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。 注意，在有些情况下要使用转义字符

代码语言：javascript复制

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

数据排序

Order By 全局排序

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

代码语言：javascript复制

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

Sort By 内部排序

对每个Reducer进行排序，不影响全局结果集 直接使用会将结果平均分配给每个文件(避免数据倾斜) 一般配合Distribute By使用

代码语言：javascript复制

-- 先设置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 语句之前！

代码语言：javascript复制

-- 先设置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 方式 该排序只能是升序排序

代码语言：javascript复制

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

分桶和抽样查询

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

创建分桶表

代码语言：javascript复制

-- 创建分桶表
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;

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

代码语言：javascript复制

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

插入数据

代码语言：javascript复制

-- 因为需要分区,所以要走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

代码语言：javascript复制

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

代码语言：javascript复制

-- 将查询的结果导出到本地
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 命令导出

代码语言：javascript复制

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

Hive Shell 命令导出

代码语言：javascript复制

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

Export 导出

代码语言：javascript复制

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

数据删除

Truncate 删除

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

代码语言：javascript复制

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

函数

系统内置函数

代码语言：javascript复制

-- 查看系统内置函数
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

编程步骤

Maven依赖

代码语言：javascript复制

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

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/144614.html原文链接：https://javaforall.cn

数据库 sql mapreduce

0 人点赞