show databases 查看系统中所有的数据库
``` show databases;
3. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114193204.png)
4. create table 创建数据库
5. ```hive
create (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment] -- COMMENT用来添加数据库的注释说明语句
[LOCATION hdfs_path] -- LOCATION用来指定数据库在HDFS的存储位置,默认/user/hive/warehouse/dbname.db
[WITH DBPROPERTIES (proterty_name = property_value,...)];
如果需要使用location指定路径的时候,最好指向的是一个新创建的空文件夹
describe database 查看数据库结构
显示hive中数据库的名称,注释(如果已经设置),及其在文件系统中的位置等信息
extended关键字用于显示更多的信息,可以将describe简写成desc来使用
```hive describe database/schema extended db_name; desc database [extend] db_name;
12. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114200306.png)
13. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114200312.png)
14. use database;选择当前的数据库
15. ```hive
use database db_name;
alter database 修改数据库的内容
更改与Hive中的数据库相关联的元数据
```hive alter (database|schema) database_name set dbproperties (property_name=property_value,…); – 更改数据库的属性
19. ```hive
alter (database|schema) database_name set OWNER USER user; -- 更改数据库的所有者
```hive alter (database|schema) database_name set location hdfs_path; – 更改数据库的位置
21. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114201434.png)
22. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114201439.png)
23. drop database 删除数据库
24. 默认行为是RESTRICT,意味着仅在数据库为空时才删除它
25. 要删除带有表的数据库(不为空的数据库),我们可以使用CASCADE
26. ```hive
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
show tables;查看hive表的列表
```hive show tables in 数据库名;
3. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114205024.png)
4. 创建表的语法树
5. ```hive
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [COMMENT col_comment], ... ]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED|SERDE serde_name WITH SERDEPROPERTIES (property_name=property_value,...)]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)];
-- 建表语法中的语法顺序需要和语法树中的一致
建表语法中的语法顺序需要和语法树中的一致
数据类型指的是表中列的字段类型
分为:原生数据类型和复杂数据类型
原生数据类型包括:数值类型、时间日期类型、字符串类型、杂项数据类型;
复杂数据类型包括:array数组、map映射、struct结构、union联合体。
因为底层是用Java写的,所以支持Java的数据类型,比如字符串string
显示类型转换使用CAST函数
```hive CAST(‘100’ as INT) – 会将字符串100转换为100整数值 – 如果强制类型转换失败,函数会返回NULL
12. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114211004.png)
13. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114211011.png)
### 默认分隔符
1. Hive建表时如果没有row format语法指定分隔符,则采用默认分隔符,是用来分隔字段的,默认的分割符是‘\001',是一种特殊的字符,使用的是ASCII编码的值,键盘是打不出来的。
2. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114211310.png)
3. 在vim编辑器中,连续按下Ctrl+v/Ctrl+a即可输入'\001' ,显示^A
4. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114211526.png)
5. 在一些文本编辑器中将以SOH的形式显示:
6. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114211637.png)
## Hive数据存储路径
1. 将数据放在表目录下,即可映射数据到Hive表中
2. ```hive
hadoop fs -put stuent2.txt /user/hive/warehouse/mydb1.db/student2
在Hive建表的时候,可以通过location语法来更改数据在HDFS上的存储路径,使得建表加载数据更加灵活方便。
describe 表名
显示Hive中表的元数据信息,如果指定了EXTENDED关键字,则它将以Thrift序列化形式显示表的所有元数据;如果指定了FORMATTED关键字,则它将以表格格式显示元数据。
取表中指定的数据创建一个新表,使用AS指定SQL查询语句,查询语句的结果即为新表的结构和内容
```hive create table if not exists student5 as select id,name from student1 where age >= 20;
## LIKE创建表
1. 使用LIKE创建一张表结构与某个表相同的新表,新表中内容为空。
2. ```hive
create table if not exists student6 like student1;
类似于拷贝一张表的结构
内部表也被称为被Hive拥有和管理的托管表
默认情况下创建的表就是内部表,Hive拥有该表的结构和文件。换句话说,Hive完全管理表(元数据和数据)的生命周期,类似于RDBMS中的表。
当删除内部表时,会删除数据以及表的元数据(元数据指的是数据的属性信息,而不是具体的数据)
可以使用DESCRIBE FORMATTED table_name 来获取表的元数据信息,从中可以看出表的类型
```hive DESCRIBE FORMATTED table_name;
6. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114220742.png)
7. 外部表:外部表中的数据不是Hive拥有或管理的,只管理表元数据的生命周期
8. 要创建一个外部表,需要使用EXTERNAL关键字
9. 删除外部表只会删除元数据,而不会删除实际数据,在Hive外部仍然可以访问实际数据
### 内外部表的相同和不同之处
1. 都会在Hive **Metastore中管理**表的定义、字段类型等**元数据信息**
2. 删除**内部表**时,除了会从Metastore中删除表元数据,还**会从HDFS中删除其所有数据文件**。
3. 删除**外部表**时,只会从Metastore中删除表的元数据,并**保持HDFS位置中的实际数据不变**。
## DDL表的修改和删除
1. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114222014.png)
2. drop语句可以指定垃圾桶
3. ```hive
drop table [if exists] table_name [PURGE];
创建一张普通表
```hive create table t_student( num int, name string, sex string, age int, dept string) row format delimited fields terminated by ‘,’; – delimited fields的意思是分隔字段,delimited指分隔的 – fields 字段 – terminated by 终止于 – 行格式分隔符,采用行格式进行分隔
3. 将本地数据加载进这张普通表中
4. ```hive
load data local inpath 本地文件及路径 [overwrite] into table 表明;
```hive load data inpath ‘/hivedata/student.txt’ overwrite into table t_student; – 没有local参数即为从HDFS中取数据并加载
2. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114191829.png)
3. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221114191833.png)
### 插入数据 insert + select (需要执行MR程序)
1. ```hive
insert into table table_name select xxx from othertablename;
分区字段不能与表中已经存在的字段同名,分区字段可以以虚拟字段的形式显示在表结构的最后
```hive –分区表建表语法 CREATE TABLE table_name ( column1 data_type, column2 data_type, ….) PARTITIONED BY (partition1 data_type, partition2 data_type,…);
3. ```hive
create table t_all_hero_part(
id int,
name string,
hp_max int,
mp_max int,
attack_max int,
defense_max int,
attack_range string,
role_assist string
) partitioned by (role_main string) -- 这里是分区字段
row format delimited
fields terminated by "\t";
```hive load data local inpath ‘/root/hivedata/archer.txt’ into table t_all_hero_part partition(role_main=’sheshou’); load data local inpath ‘/root/hivedata/assassin.txt’ into table t_all_hero_part partition(role _main =’cike’);load data local inpath ‘/root/hivedata/mage.txt’ into table t_all_hero_part partition(role _main =’fashi’);
8. 我的理解是:实际字段可以不包含**源数据**中的所有列,但是**实际字段+虚拟字段(partition字段)要能对应源数据中的每一个列**
9. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221115173138.png)
10. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221115173304.png)
11. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221115173248.png)
### 分区表和普通表的区别
1. 外表上看起来分区表好像没多大变化,实际上**分区表在底层管理数据的方式发生了改变**。
2. 普通表
3. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221115190253.png)
4. 分区表
5. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221115190301.png)
6. 分区的概念提供了一种**将Hive表数据分离为多个文件/目录的方法**。
7. **不同分区对应着不同的文件夹**,同一分区的数据存储在同一个文件夹下
8. 这种指定分区查询的方式叫做**分区裁剪**。
### 分区表的重点在于
1. 建表时根据业务场景**设置合适的分区字段**。比如日期、地域、类别等
2. 查询的时候尽量**先使用where进行分区过滤**,查询**指定分区的数据**,**避免全表扫描**
3. ```hive
select count(*) from t_all_hero where role_main='archer' and hp_max > 5800;
select count(*) from t_all_hero where role_main='sheshou' and hp_max > 5800;
分区字段不能和表中已有的字段同名
分区字段是虚拟字段,其数据并不存储在底层的文件中
分区字段值的确定来自于用户价值数据手动指定(静态分区)或者根据查询结果位置自动推断(动态分区)
Hive支持多重分区,也就是说在分区的基础上继续分区,划分更加细粒度
所谓动态分区指的是分区的字段值是基于查询结果(参数位置)自动推断出来的。
核心语法就是insert+select
```hive insert into table table_name PARTITION(分区字段) SELECT *** FROM othertable_name;
4. 启动动态分区需要开启动态分区功能,在hive会话中设置几个参数
5. ```hive
set hive.exec.dynamic.partition=true; -- 是否开启动态分区功能
set hive.exec.dynamic.partition.mode=nonstrict; -- 指定动态分区模式,分为nonstrick非严格模式和strict严格模式 , strict严格模式要求至少有一个分区为静态分区
set hive.exec.max.dynamic.partitions=50000;
set hive.exec.max.dynamic.partitions.pernode=10000;
```hive – 创建一张表 create table t_usa_covid19( count_date string, county string, state string, fips int, cases int, deaths int) row format delimited fields terminated by ‘,’;
7. ```bash
-- 将所有的数据都加载进去
hadoop fs -put us-covid19-countries.dat /user/hive/warehouse/mydb2.db/t_usa_covid19;
多重分区表
```hive partitioned by (partition1 data_type,partition2,data_type,…)
12. 多重分区下,分区之间是一种递进关系,可以理解为**在前一个分区的基础上继续分区**。从HDFS的角度来看就是**文件夹下继续划分子文件夹**。
13. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221115194448.png)
14. ```hive
--单分区表,按省份分区
create table t_user_province (id int, name string,age int) partitioned by (province string);
--双分区表,按省份和市分区
create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string);
--三分区表,按省份、市、县分区
create table t_user_province_city_county (id int, name string,age int) partitioned by (province string, city string,county string);
主要包括:增加分区,删除分区,修改分区
增加分区:
```hive alter table 表名 add partition (分区字段) location ‘分区字段需要的数据文件所在的HDFS位置’
## 分桶表
1. 分桶表对应的数据文件**在底层会被分解为若干个部分**,通俗来说就是**被拆分成若干个独立的小文件**。
2. 在分桶时,要指定根据哪个字段将数据分为几桶(几个部分)。
3. ![](https://strongwillpro.oss-cn-beijing.aliyuncs.com/img/20221115203849.png)
4. ```hive
create [external] table [db_name.]table_name
[(col_name data_type,...)]
clustered by(col_name) -- clustered by 根据哪个字段进行分
into N buckets; -- 分成几桶