全网最细之HiveQL语句操作

关键字尽量大写 但是本人小写容易记忆 所以本篇为小写

--------------------------------------------------------------------------

数据库

下方数据库名统一为：db （我难得打中文）

创建一个数据库

#创建一个数据库，在HDFS上的默认路径为user/hive/warehouse/*.db
create database db;
#可以用 if  exists 判断是否存在（存在则不创建）
create database if not exists db;
#创建一个数据库 指定存放的路径  这里我使用的是自己的存放路径
create database db location "/user/hive/warehouse";
# 

查看数据库

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

修改数据库信息

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

--给数据库添加信息
alter database db set dbproperties("createtime"="2022-04-20");

删除数据库

--删除一个 空的!!! 数据库
drop database db;
--还是最好用if exists 判断是否存在
drop database if exists db;
--如果数据库不为空 可以选择使用 cascade 强制删除
drop database db cascade;

--------------------------------------------------------------------------

普通表

老规矩 表名为: tb

创建表

--创建一张表
create table tb(id int,name string);
--创建一张外部表
create external tb(id int,name string);
--创建表并设置表中数据的分隔符(以制表符为例)
--复制代码时候 记得把注释删了
create table tb(
	name string,
	friends array<string>,
	children map<string, int>,
	address struct<street:string, city:string>
)
row format delimited 
fields terminated by ','   /* 列分隔符 */
collection items terminated by '_'         /*  MAP STRUCT 和 ARRAY 的分隔符(数据分割
符号)  */
NUll defined as '' 数据中有空值的时候需要转换为hdfs识别的到的 null 
map keys terminated by ':'    /* MAP 中的 key 与 value 的分隔符    */
lines terminated by '\n'       /* 行分隔符  */
stored as textfile;      /*存储格式*/

查看表

--查看表
>1：desc tb
>--查看详细信息
>2：desc formatted tb

修改表名

>alter table 旧表名 rename to  新表名

添加n列

>1、一次增加一个列(默认添加为最后一列，分区字段之前，用逗号风格多列)
alter table table_name add columns (new_col INT); 
2、可以一次增加多个列
alter table table_name add columns (c1 INT,c2 STRING); 
3、添加一列并增加列字段注释
alter table table_name add columns (new_col INT  comment 'a comment');

修改表

修改位置

>将列a1的名称更改为a2，将其数据类型更改为字符串，并将其放在列b之后:
alter table testa change a1 a2 string   after b;
>放在第一排
>alter table testa change a2 a3 string first;

>注意:
>列位置更换后数据位置还是不动的,`若想让数据跟着字段一起移动,需更新表数据,`
使用`insert overwrite table` 从表中将移动之前对应的数据插入到移动之后对应的字段中

单独修改列名 数据类型

>alter table 表名 change column   原列名  新列名  新数据类型

替换列

会删除原来的所有列,也就是把所有的列替换为一个字段

>alter table 表名 replace columns (列名 数据类型)

删除n个字段

alter table tb drop column id,drop column name;

修改表的注释

>alter table testa set tblproperties( "comment" = "new comment"  )

删除表

>1：drop table 表名
>--purge 强制删除 就是放在最后面
>2:drop table 表名 purge  

清空表数据

truncate table tb;	

内部表（管理表）和 外部表

两者的区别

删除时，内部表会把元数据和具体数据都删除，但是外部表只会删除元数据

相互转换

注意：这里你第一眼会很眼熟，切记！！要大写大写大写！不然就是加附加信息了！
其实就是 external 等于 true或者false

--转换为外部表
alter table tb   set tblproperties('EXTERNAL'='TRUE');
--转换为内部表
alter table tb set tblproperties('EXTERNAL'='FAlSE');

--------------------------------------------------------------------------

分区表

分区是HDFS上对应一个独立的文件夹，属于元数据，但用法相当于一个字段，可以用来过滤
数据库分区的主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间，主要包括两种分区形式：水平分区与垂直分区。水平分区是对表进行行分区。而垂直分区是对列进行分区，一般是通过对表的垂直划分来减少目标表的宽度，常用的是水平分区.

创建分区表

1：分区字段不能和表字段重名
2:：一但创建了分区字段，就无法进行修改和添加

--创建一个表，并设置以moth字段分区
create table student(id int,name string)
partitioned by(month string);
--二级分区也即使多级文件夹
create table student(id int,name string)
pertitioned by(month string,day string);

显示所有分区

show partitions tb;

添加分区

--往分区字段表里添加一个分区
alter table student add partition(month='202003')
--往分区里面添加多个分区（用空格分隔）
alter table student add partition(month='2022') 	partition(month='2023');

往分区里面添加数据

--加上 partition（） 指定分区
load data local inpath'/opt/file.txt' into table tb partition(month='202003');
insert into table tb partition(month='202003') values('4','20');
****************************************************************************
load data [local] inpath '数据的 path' [overwrite] 

into table   tablename  [partition (partcol1=val1,…)];

查询分区

select * from tb where month='202003';
select * from tb where month='202003' and day='20';

删除分区

--删除一个分区的表里面的分区
alter table tb drop partition(month='202003');
--删除分区表里面的多个分区（以逗号分隔）
alter table tb drop partition(month='202003'),partition(month='202004');

修复分区

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

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

移动分区

移动分区可以将分区表A中的表移动到具有相同结构的分区表B中，值得注意的是表B中不能存在表A中要移动的分区

-- 移动分区
--语法
alter table 表名>被插入的表 exchange partition(分区名) with table 表名>提供分区的表
--例子
--将表a的二级分区province=HuBei/city=WuHan移动分区表b中：
alter table b exchange partition (province=HuBei,city=WuHan) with table a

--------------------------------------------------------------------------

分桶表

什么是分桶表？

分桶是将数据集分解成更容易管理的若干部分的一个技术，是比表或分区更为细粒度的数据范围划分。针对某一列进行桶的组织，对列值哈希，然后除以桶的个数求余，决定将该条记录存放到哪个桶中。

常用于：

• 获得更高的查询处理效率
• 抽样调查

分桶表和分区表的区别

分区表提供了一个隔离数据和优化查询的便利方式。但是在实际场景下，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive还可以进一步组织成桶，也就是更为细粒度的数据范围划分。

小结一波：

1. 分桶对数据的处理比分区更加细粒度化；
2. 分桶和分区两者不干扰，可以把分区表进一步分桶；
3. 分区针对的是数据的存储路径；分桶针对的是数据文件。

创建分桶表

create table test_bucket (
id int comment 'ID', 
name string comment '名字'
)
clustered by(id) into 4 buckets

查看分桶表

desc formatted test_bucket;

--------------------------------------------------------------------------

临时表

作为临时表创建的表将只对当前会话可见。数据将存储在用户的scratch目录（临时目录）中，并在会话结束时删除。

如果用数据库中已经存在的永久表的数据库/表名创建了一个临时表，那么在该会话中，对该表的任何引用都将解析为临时表，而不是永久表。如果不删除临时表或将其重命名为不冲突的名称，用户将无法在该会话中访问原始表。

临时表存在如下限制:

• 不支持分区列
• 不支持创建索引

Hive的临时表在数据加载的过程中会频繁使用到，对于复杂的业务逻辑，可以将数据先存储在临时表，然后再从临时表取值进行进一步计算。

创建临时表

create table temporary temporary1(id int,name string);

--------------------------------------------------------------------------

视图

在 Hive 中可以使用 CREATE VIEW 创建视图，如果已存在具有相同名称的表或视图，则会抛出异常，建议使用 IF NOT EXISTS 预做判断。在使用视图时候需要注意以下事项：

1. 视图是只读的，不能用作 LOAD / INSERT / ALTER 的目标；
2. 在创建视图的时候，视图就已经固定，对基表的后续更改（如添加列）将不会反映在视图；
3. 删除基表并不会删除视图，需要手动删除视图；
4. 视图可能包含 ORDER BY 和 LIMIT 子句。如果引用视图的查询语句也包含这类子句，其执行优先级低于视图对应字句。例如，视图 custom_view 指定 LIMIT 5，查询语句为 select * from custom_view LIMIT 10，此时结果最多返回 5 行。
5. 创建视图时，如果未提供列名，则将从 SELECT 语句中自动派生列名；
6. 创建视图时，如果 SELECT 语句中包含其他表达式，例如 x + y，则列名称将以_C0，_C1 等形式生成；

视图的创建

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name   -- 视图名称
  [(column_name [COMMENT column_comment], ...) ]    --列名
  [COMMENT view_comment]  --视图注释
  [TBLPROPERTIES (property_name = property_value, ...)]  --额外信息
  AS SELECT ...;

create view if not exists 数据库名.视图名
字段名  字段注释   --可选
comment 视图注释
tblproperties (注释名=注释值)
as select 用于指定查询的语句

查看视图

--查看视图
desc 视图名;
--查看详细信息
desc  formatted 视图名;
--查看数据库中的视图
show views;

修改视图属性

alter view viewname set tblproperties(property1=propervalue1，property2=propervalue2，。。。。);

修改视图

alter view viewname as 新的查询;

删除视图

drop view if not exists 视图名;

--------------------------------------------------------------------------

索引

创建索引

CREATE INDEX index_name     --索引名称
  ON TABLE base_table_name (col_name, ...)  --建立索引的列
  AS index_type    --索引类型
  [WITH DEFERRED REBUILD]    --重建索引
  [IDXPROPERTIES (property_name=property_value, ...)]  --索引额外属性
  [IN TABLE index_table_name]    --索引表的名字
  [
     [ ROW FORMAT ...] STORED AS ...  
     | STORED BY ...
  ]   --索引表行分隔符 、 存储格式
  [LOCATION hdfs_path]  --索引表存储位置
  [TBLPROPERTIES (...)]   --索引表表属性
  [COMMENT "index comment"];  --索引注释
  
  --简写↓
create index index_name on table 建立索引的表（表的字段）
as 索引的类型       
[with deferred rebuild]    --可选
[indexproperties(property_name1=property_value,....)]   --索引额外属性 可选 
[in table 自定义我们索引表的名字]     --可选

[
	row format ... stored as ... stored by          --行分隔符、存储格式  可选
]
[location hdfs上的路径]   --可选
[tblproperties (...)]    --可选
[comment 'this is indextable']  -- 索引注释 可选

查看索引

show [formatted]  表名

重建索引

在这里插入代码片

删除索引

drop index 索引名;