1. 全栈程序员必看首页

【Hive】SQL语句大全

全栈程序员-用户IM • • 未分类

【Hive】SQL语句大全文章目录库操作创建数据库查询数据库修改数据库删除数据库表操作创建表查看表修改表删除表内部表(管理表)和外部表两者的区别互相转换分区表创建分区表添加分区往分区表中添加数据查询分区表数据删除分区查看分区修复分区数据操作数据导入Load导入Insert插入Import导入数据查询基本查询Floor取整Like和RlikeDistinct去重GroupBy分组查询Having语句Join语…

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

文章目录

库操作

创建数据库

-- 创建一个数据库，在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>
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

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

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (1)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

  • PLSQL操作Oracle创建用户和表

    PLSQL操作Oracle创建用户和表

    PLSQL操作Oracle创建用户和表1、打开PLSQL，填写用户名和密码（初始有两个用户sys和system，密码是自己安装oracle数据库时定的），Database选择ORCL（默认数据库，oracle中创建的用户就像是mysql中建的数据库，两者有异曲同工之妙）2、登陆成功后在界面的头部可以看到一下信息，最上面的system@ORCL就表示是用户system在登录状态，其中菜单栏的session可以登录、退出…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年5月19日
  • 整数规划matlab实例,整数规划matlab[通俗易懂]

    整数规划matlab实例,整数规划matlab[通俗易懂]

    整数规划matlab实例,整数规划matlab[通俗易懂]整数规划matlabTag内容描述：1、例已知非线性整数规划为maxz=x12+x22+3×32+4×42+2×52-8×1-2×2-3×3-x4-2x5s.t.0xi99,i=1,2,5×1+x2+x3+x4+x5400x1+2×2+2×3+x4+6x58002x1+x2+6x3200x3+x4+5×5200(1)编写M文件mengte.m,定义目标函数f和约束向量函数g，程序如下：funct…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年7月12日
  • sp_executesql_sp_executesql存储过程简介和示例

    sp_executesql_sp_executesql存储过程简介和示例

    sp_executesql_sp_executesql存储过程简介和示例sp_executesqlThesp_executesqlisabuilt-instoredprocedureinSQLServerthatenablestoexecuteofthedynamicallyconstructedSQLstatementsorbatches.Executingthedynamicallyconstructe…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年5月22日
  • vue动态组件component原理_component注解

    vue动态组件component原理_component注解

    vue动态组件component原理_component注解componentis内置组件切换方法一：component组件（单独拿出一个组件来专门进行切换使用）使用is来绑定你的组件：如下面的reviewedPlanplanDetailsListattachmentList等引入的组件名changeViewFun是用来切换组件的方法通过给is绑定的currentView来实现切换组件pathUrl就是当前的路由…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年9月16日
  • hadoop是什么意思_hadoop三大组件

    hadoop是什么意思_hadoop三大组件

    hadoop是什么意思_hadoop三大组件Hadoop是一个由Apache基金会所开发的分布式系统基础架构。用户可以在不了解分布式底层细节的情况下，开发分布式程序。充分利用集群的威力进行高速运算和存储。[1]Hadoop实现了一个分布式文件系统（HadoopDistributedFileSystem），简称HDFS。HDFS有高容错性的特点，并且设计用来部署在低廉的（low-cost）硬件上；而且它提供高吞吐量（highthro

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年9月29日
  • python缩进错误提示(python缩进讲解)

    python缩进错误提示(python缩进讲解)

    参考链接：Python语句，缩进和注释广告关闭腾讯云11.11云上盛惠，精选热门产品助力上云，云服务器首年88元起，买的越多返的越多，最高返5000元！学习python与其他语言最大的区别就是，python的代码块不使用大括号{}来控制类，函数以及其他逻辑判断。python最具特色的就是用缩进来写模块。缩进…有时候，你觉得两行代码的缩进是一样的，但编译器仍然报错。这可能是因为一个地方使用空格来缩进，而另一个地方使用了tab键来缩进。碰到这种情况要统一…..

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年4月10日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号