hive数据定义语言DDL
hive数据定义语言DDL
hive数 据定义语言DDL
数据定义语言概述
- 数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、table、view、index等。
- DDL核心语法由CREATE、ALTER与DROP三个所组成。DDL是对表结构进行的操作。
- 在某些上下文中，该术语也称为数据描述语言，因为它描述了数据库表中的字段和记录。
Hive DDL操作
DDL基本概念
- 在Hive中，DATABASE的概念和RDBMS中类似，我们称之为数据库，DATABASE和SCHEMA是可互换的，都可以使用。
- hive的数据库本质上就是一个目录
- 默认的数据库叫做default,存储于/user/hive/warehouse下
- 用户自己创建的数据库存储位置是/user/hive/warehouse/数据库名.db下
数据库的基本命令
show databases 查看系统中所有的数据库
``` show databases;
3. 
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. 
13. 
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. 
22. 
23. drop database 删除数据库
24. 默认行为是RESTRICT,意味着仅在数据库为空时才删除它
25. 要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE
26. ```hive
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
Hive表的基本命令
show tables;查看hive表的列表
```hive show tables in 数据库名;
3. 
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, ...)];
-- 建表语法中的语法顺序需要和语法树中的一致建表语法中的语法顺序需要和语法树中的一致
Hive数据类型
数据类型指的是表中列的字段类型
分为：原生数据类型和复杂数据类型
原生数据类型包括：数值类型、时间日期类型、字符串类型、杂项数据类型；
复杂数据类型包括：array数组、map映射、struct结构、union联合体。
因为底层是用Java写的，所以支持Java的数据类型，比如字符串string
显示类型转换使用CAST函数
```hive CAST(‘100’ as INT) – 会将字符串100转换为100整数值 – 如果强制类型转换失败，函数会返回NULL
12. 
13. 
### 默认分隔符
1. Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符，是用来分隔字段的，默认的分割符是‘\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。
2. 
3. 在vim编辑器中，连续按下Ctrl+v/Ctrl+a即可输入'\001' ，显示^A
4. 
5. 在一些文本编辑器中将以SOH的形式显示：
6. 
## Hive数据存储路径
1. 将数据放在表目录下，即可映射数据到Hive表中
2. ```hive
hadoop fs -put stuent2.txt /user/hive/warehouse/mydb1.db/student2在Hive建表的时候，可以通过location语法来更改数据在HDFS上的存储路径，使得建表加载数据更加灵活方便。
查看Hive表的结构
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拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。
当删除内部表时，会删除数据以及表的元数据（元数据指的是数据的属性信息，而不是具体的数据）
可以使用DESCRIBE FORMATTED table_name 来获取表的元数据信息，从中可以看出表的类型
```hive DESCRIBE FORMATTED table_name;
6. 
7. 外部表：外部表中的数据不是Hive拥有或管理的，只管理表元数据的生命周期
8. 要创建一个外部表，需要使用EXTERNAL关键字
9. 删除外部表只会删除元数据，而不会删除实际数据，在Hive外部仍然可以访问实际数据
### 内外部表的相同和不同之处
1. 都会在Hive **Metastore中管理**表的定义、字段类型等**元数据信息**
2. 删除**内部表**时，除了会从Metastore中删除表元数据，还**会从HDFS中删除其所有数据文件**。
3. 删除**外部表**时，只会从Metastore中删除表的元数据，并**保持HDFS位置中的实际数据不变**。
## DDL表的修改和删除
1. 
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 表明;
加载HDFS中的数据
```hive load data inpath ‘/hivedata/student.txt’ overwrite into table t_student; – 没有local参数即为从HDFS中取数据并加载
2. 
3. 
### 插入数据 insert + select (需要执行MR程序)
1. ```hive
insert into table table_name select xxx from othertablename;
分区表
分区表的产生原因
- where语句的背后需要进行全表扫描才能过滤出结果，对于hive来说需要扫描每一个文件。如果数据文件个数特别多的话，扫描效率很慢也没必要。
- 如果只需求需要一个archer.txt文件，只需要扫描archer.txt文件即可，如何优化可以加快查询，减少全表扫描呢？
- 指定文件扫描和全表扫描，效率还是存在差异的。
- 分区表的创建目的就是为了减少扫描文件的大小，减少全表扫描的概率，提高效率
- 当Hive表对应的数据量大、文件个数多时，为了避免查询时全表扫描数据，Hive支持根据指定的字段对表进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。
分区表的创建和数据加载
分区字段不能与表中已经存在的字段同名，分区字段可以以虚拟字段的形式显示在表结构的最后
```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. 
10. 
11. 
### 分区表和普通表的区别
1. 外表上看起来分区表好像没多大变化，实际上**分区表在底层管理数据的方式发生了改变**。
2. 普通表
3. 
4. 分区表
5. 
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. 
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);对于分区表的DDL操作
主要包括：增加分区，删除分区，修改分区
增加分区：
```hive alter table 表名 add partition (分区字段) location ‘分区字段需要的数据文件所在的HDFS位置’
## 分桶表
1. 分桶表对应的数据文件**在底层会被分解为若干个部分**，通俗来说就是**被拆分成若干个独立的小文件**。
2. 在分桶时，要指定根据哪个字段将数据分为几桶（几个部分）。
3. 
4. ```hive
create [external] table [db_name.]table_name
[(col_name data_type,...)]
clustered by(col_name) -- clustered by 根据哪个字段进行分
into N buckets; -- 分成几桶