关键字尽量大写 但是本人小写容易记忆 所以本篇为小写
--------------------------------------------------------------------------
数据库
下方数据库名统一为: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还可以进一步组织成桶,也就是更为细粒度的数据范围划分。
小结一波:
- 分桶对数据的处理比分区更加细粒度化;
- 分桶和分区两者不干扰,可以把分区表进一步分桶;
- 分区针对的是数据的存储路径;分桶针对的是数据文件。
创建分桶表
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 预做判断。在使用视图时候需要注意以下事项:
- 视图是
只读
的,不能用作 LOAD / INSERT / ALTER 的目标;
- 在创建视图的时候,视图就已经固定,对基表的后续更改(如添加列)将不会反映在视图;
- 删除基表并不会删除视图,需要手动删除视图;
- 视图可能包含 ORDER BY 和 LIMIT 子句。如果引用视图的查询语句也包含这类子句,其执行优先级低于视图对应字句。例如,视图
custom_view 指定 LIMIT 5
,查询语句为 select * from custom_view LIMIT 10
,此时结果最多返回 5 行。
- 创建视图时,如果未提供列名,则将从 SELECT 语句中自动派生列名;
- 创建视图时,如果 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 索引名;