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;
```

create table 创建数据库

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来使用
```
describe database/schema extended db_name;
desc database [extend] db_name;
```
use database;选择当前的数据库
```
use database db_name;
```
alter database 修改数据库的内容

更改与Hive中的数据库相关联的元数据

alter (database|schema) database_name set dbproperties (property_name=property_value,...); -- 更改数据库的属性

alter (database|schema) database_name set OWNER USER user; -- 更改数据库的所有者

alter (database|schema) database_name set location hdfs_path; -- 更改数据库的位置

drop database 删除数据库
默认行为是RESTRICT,意味着仅在数据库为空时才删除它
要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE
```
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
```

Hive表的基本命令

show tables;查看hive表的列表
```
show tables in 数据库名;
```

创建表的语法树

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函数

 CAST('100' as INT) -- 会将字符串100转换为100整数值
-- 如果强制类型转换失败，函数会返回NULL

默认分隔符

Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符，是用来分隔字段的，默认的分割符是‘\001’，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。
在vim编辑器中，连续按下Ctrl+v/Ctrl+a即可输入’\001’ ，显示^A
在一些文本编辑器中将以SOH的形式显示：

Hive数据存储路径

将数据放在表目录下，即可映射数据到Hive表中

hadoop fs -put stuent2.txt /user/hive/warehouse/mydb1.db/student2

在Hive建表的时候，可以通过location语法来更改数据在HDFS上的存储路径，使得建表加载数据更加灵活方便。

查看Hive表的结构

describe 表名
显示Hive中表的元数据信息，如果指定了EXTENDED关键字，则它将以Thrift序列化形式显示表的所有元数据；如果指定了FORMATTED关键字，则它将以表格格式显示元数据。
取表中指定的数据创建一个新表，使用AS指定SQL查询语句，查询语句的结果即为新表的结构和内容
```
create table if not exists student5 as select id,name from student1 where age >= 20;
```

LIKE创建表

使用LIKE创建一张表结构与某个表相同的新表，新表中内容为空。
```
create table if not exists student6 like student1;
```
类似于拷贝一张表的结构

元数据和主数据的区别

元数据：即关于数据的数据，用以描述数据及其环境的结构化信息，便于查找、理解、使用和管理数据。
主数据：主数据则定义企业核心业务对象，如客户、产品、地址等，与交易流水信息不同，主数据一旦被记录到数据库中，需要经常对其进行维护，从而确保其时效性和准确性；主数据还包括关系数据，用以描述主数据之间的关系，如客户与产品的关系、产品与地域的关系、客户与客户的关系、产品与产品的关系等。

Hive的内部表和外部表

内部表也被称为被Hive拥有和管理的托管表
默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。
当删除内部表时，会删除数据以及表的元数据（元数据指的是数据的属性信息，而不是具体的数据）
可以使用DESCRIBE FORMATTED table_name 来获取表的元数据信息，从中可以看出表的类型
```
DESCRIBE FORMATTED table_name;
```
外部表：外部表中的数据不是Hive拥有或管理的，只管理表元数据的生命周期
要创建一个外部表，需要使用EXTERNAL关键字
删除外部表只会删除元数据，而不会删除实际数据，在Hive外部仍然可以访问实际数据

内外部表的相同和不同之处

都会在Hive Metastore中管理表的定义、字段类型等元数据信息
删除内部表时，除了会从Metastore中删除表元数据，还会从HDFS中删除其所有数据文件。
删除外部表时，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。

DDL表的修改和删除

drop语句可以指定垃圾桶

drop table [if exists] table_name [PURGE];

普通表数据加载

加载本地数据

创建一张普通表

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 终止于
-- 行格式分隔符，采用行格式进行分隔

将本地数据加载进这张普通表中

load data local inpath 本地文件及路径 [overwrite] into table 表明;

加载HDFS中的数据

load data inpath '/hivedata/student.txt' overwrite into table t_student;
-- 没有local参数即为从HDFS中取数据并加载

插入数据 insert + select (需要执行MR程序)

insert into table table_name select xxx from othertablename;

分区表

分区表的产生原因

where语句的背后需要进行全表扫描才能过滤出结果，对于hive来说需要扫描每一个文件。如果数据文件个数特别多的话，扫描效率很慢也没必要。
如果只需求需要一个archer.txt文件，只需要扫描archer.txt文件即可，如何优化可以加快查询，减少全表扫描呢？
指定文件扫描和全表扫描，效率还是存在差异的。
分区表的创建目的就是为了减少扫描文件的大小，减少全表扫描的概率，提高效率
当Hive表对应的数据量大、文件个数多时，为了避免查询时全表扫描数据，Hive支持根据指定的字段对表进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。

分区表的创建和数据加载

分区字段不能与表中已经存在的字段同名，分区字段可以以虚拟字段的形式显示在表结构的最后

--分区表建表语法
CREATE TABLE table_name ( 
column1 data_type, 
column2 data_type,
....) 
PARTITIONED BY (partition1 data_type, partition2 data_type,…);

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";

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');

我的理解是：实际字段可以不包含源数据中的所有列，但是实际字段+虚拟字段（partition字段）要能对应源数据中的每一个列

分区表和普通表的区别

外表上看起来分区表好像没多大变化，实际上分区表在底层管理数据的方式发生了改变。
普通表
分区表
分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。
不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下
这种指定分区查询的方式叫做分区裁剪。

分区表的重点在于

建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等

查询的时候尽量先使用where进行分区过滤，查询指定分区的数据，避免全表扫描

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

insert into table table_name PARTITION(分区字段) SELECT *** FROM othertable_name;

启动动态分区需要开启动态分区功能，在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;

-- 创建一张表
create table t_usa_covid19(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
row format delimited
fields terminated by ',';

-- 将所有的数据都加载进去
hadoop fs -put us-covid19-countries.dat /user/hive/warehouse/mydb2.db/t_usa_covid19;

多重分区表

partitioned by (partition1 data_type,partition2,data_type,...)

多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。

--单分区表，按省份分区
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操作

主要包括：增加分区，删除分区，修改分区

增加分区：

alter table 表名 add partition (分区字段)
location '分区字段需要的数据文件所在的HDFS位置'

分桶表

分桶表对应的数据文件在底层会被分解为若干个部分，通俗来说就是被拆分成若干个独立的小文件。
在分桶时，要指定根据哪个字段将数据分为几桶（几个部分）。

create [external] table [db_name.]table_name
[(col_name data_type,...)]
clustered by(col_name) -- clustered by 根据哪个字段进行分
into N buckets; -- 分成几桶