数据定义语言 (Data Definition Language, DDL)
是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、table、view、index等。核心语法由CREATE、ALTER与DROp三个所组成。DDL并不涉及表内部数据的操作。
完整建表语法树
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_ s_path] [TBLPROPERTIES (property_name=property_value, ...)];2.Hive数据类型详解
整体概述
Hive中的数据类型指的是Hive表中的列字段类型。Hive数据类型整体分为两个类别：原生数据类型（primitive data type）和复杂数据类型（complex data type）。
原生数据类型包括：数值类型、时间类型、字符串类型、杂项数据类型；
复杂数据类型包括：array数组、map映射、struct结构、union联合体。
原生数据类型：
复杂数据类型：
关于Hive数据类型，需要注意：
英文字母大小写不敏感除SQL数据类型外，还支持Java数据类型，比如：stringint和string是使用最多的，大多数函数都支持复杂数据类型的使用通常需要和分隔符指定语法配合使用如果定义的数据类型和文件不一样，Hive会尝试隐式转换，但是不保证成功
数据类型隐式、显示转换：
与SQL类似，HQL支持隐式和显式类型转换。 原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许下表描述了类型之间允许的隐式转换：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-En9JCSLN-1648214096440)(spark_stu.assets/1648205113962.png)] 3.Hive读写机制
What is SerDe？
SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。
Hive使用SerDe（和FileFormat）读取和写入行对象。
需要注意的是，“key”部分在读取会忽略，而在写入时key始终是常数。基本上行对象存储在“value”中
Hive读写文件流程
Hive读取文件机制：首先调用InputFormat（默认TextInputFormat），返回一条一条kv键值对记录（默认是一行对应一条记录）。然后调用SerDe（默认LazySimpleSerDe）的Deserializer，将一条记录中的value根据分隔符切分为各个字段Hive写文件机制：将Row写入文件时，首先调用SerDe（默认LazySimpleSerDe）的Serializer将文件转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中
Serde相关语法
ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一
如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据。如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类
LazySimpleSerDe分隔符指定
LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。
默认分隔符：’ 01’，是一种特殊的字符，使用的是ascii编码的值，键盘是打不出来的
4.Hive的内、外部表概述
什么是内部表？
**内部表（Internal table）**也称为被Hive拥有和管理的托管表（Managed table）。默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。当您删除内部表时，它会删除数据以及表的元数据。可以使用DESCRIBE FORMATTED 表名 ;来获取表的描述信息
什么是外部表？
**外部表（External table）**中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字。删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。而且外部表更为方便的是可以搭配location语法指定数据的路径。
内部表和外部表有什么区别？
无论内部表还是外部表，Hive都在Hive metastore中管理表定义及其分区信息
删除内部表会从metastore中删除表元数据，还会从HDFS中删除其所有数据/文件
删除外部表，只会从metastore中删除表的元数据，并保持HDFS位置中的实际数据不变
如何选择内部表和外部表？
当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。当文件已经存在或位于远程位置时，请使用外部表，因为即使删除表，文件也会被保留 5.分区表概述
什么是分区表？
分区表指的是：根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。只需要根据分区值找到对应的文件夹，扫描本分区下的文件即可，避免全表数据扫描。
分区表建表语法：
CREATE TABLE table_name (column1 data_type, column2 data_type) PARTITIonED BY (partition1 data_type, partition2 data_type,….);
注意：分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上。可以构建多重分区表：多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。
分区表数据加载——静态分区
所谓静态分区指的是分区的字段值是由用户在加载数据的时候手动指定的
语法：
load data [local] inpath ' ' into table tablename partition (分区字段='分区值'...);
分区表数据加载——动态分区
所谓动态分区指的是分区的字段值是基于查询结果自动推断出来的。核心语法就是insert+select
启动Hive动态分区，需要在Hive回话中设置两个参数
set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict;
第一个参数表示开启动态分区功能第二个参数指定动态分区的模式。分为nonstick非严格模式和strict严格模式。strict严格模式要求至少有一个分区为静态分区。
语法：
insert into tablename partition(role) select role from tablename;
分区表的使用
建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等查询的时候尽量先使用where进行分区过滤，查询指定分区的数据，避免全表扫描
分区表的注意事项
分区表不是建表的必要语法规则，是一种优化手段表分区字段不能是自己表中已有的字段，不能重复分区字段是虚拟字段，其数据并不储存在底层的文件中分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度
add partition
分区值仅在为字符串时才应加引号。位置必须是数据文件所在的目录
add partition 会更改元数据，但不会加载数据。如果分区位置中不存在数据，查询将不会返回任何结果。
-- 增加分区
ALTER TABLE table_name ADD PARTITION (分区字段='分区值'...) [local] inpath ' ';
-- 一次添加一个分区
ALTER TABLE table_name ADD PARTITION (分区字段='分区值'...) [local] inpath ' '
PARTITION (分区字段='分区值'...) [local] inpath ' ';
-- 一次添加多个分区
rename partition
-- 重命名分区 ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
delete partition
可以使用ALTER TABLE DROP PARTITION删除表的分区。这将删除该分区的数据和元数据
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (分区字段='分区值'...); ALTER TABLE table_name DROP [IF EXISTS] PARTITION (分区字段='分区值'...) PURGE; --直接删除数据 不进垃圾桶
msck partition
Hive将每个表的分区列表信息存储在其metastore中。但是，如果将新分区直接添加到HDFS（例如通过使用hadoop fs -put命令）或从HDFS中直接删除分区文件夹，则除非用户ALTER TABLE table_name ADD/DROP PARTITION在每个新添加的分区上运行命令，否则metastore（也就是Hive）将不会意识到分区信息的这些更改
MSC命令的默认选项是“添加分区”。使用此选项，它将把HDFS上存在但元存储中不存在的所有分区添加到元存储中。DROP PARTITIONS选项将从已经从HDFS中删除的metastore中删除分区信息。SYNC PARTITIONS选项等效于调用ADD和DROP PARTITIONS
如果存在大量未跟踪的分区，则可以批量运行MSCK REPAIR TABLE，以避免OOME（内存不足错误）
-- 修复分区 MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];
alter partition
-- 修改分区 -- 更改分区文件存储格式 ALTER TABLE table_name PARTITION (分区字段='分区值'...) SET FILEFORMAT file_format; -- 更改分区位置 ALTER TABLE table_name PARTITION (分区字段='分区值'...) SET LOCATION "new location";6.分桶表概述
什么是分桶表？
分桶表指的是根据字段名的hashcode值和分桶数进行求模取余，根据余数进行划分，余数相同的会被划分到同一个文件中
公式：
Bucket number = hash_function(bucketing_column) mod num_buckets。
语法：
--分桶表建表语句 CREATE [EXTERNAL] TABLE [db_name.]table_name [(col_name data_type, ...)] CLUSTERED BY (col_name) INTO N BUCKETS;
其中clustered by (col_name) 表示根据那个字段进行分桶into n buckets 表示分为几桶需要注意的是：分桶的字段必须是表中已经存在的字段
分桶表的使用好处
基于分桶字段查询时，减少全表扫描join时可以提高MR程序效率，减少笛卡尔积数量分桶表数据进行抽取 7. Hive Transactional Table 事务表
Hive事务背景知识
Hive的核心目标是将已经存在的结构化数据文件映射成为表，然后提供基于表的SQL分析处理，是一款面向分析的工具。所以在设计的时候，就是不支持事务的但后期为了解决以下场景出现的问题，加入了事务
流式传输数据。使用如Apache Flume或Apache Kafka之类的工具将数据流式传输到Hadoop集群中。虽然这些工具可以每秒数百行或更多行的速度写入数据，但是Hive只能每隔15分钟到一个小时添加一次分区。频繁添加分区会很快导致表中大量的分区。因此通常使用这些工具将数据流式传输到现有分区中，但是这会使读者感到脏读（也就是说，他们将在开始查询后看到写入的数据），并将许多小文件留在目录中，这将给NameNode带来压力。通过事务功能，同时允许读者获得一致的数据视图并避免过多的文件。尺寸变化缓慢。在典型的星型模式数据仓库中，维度表随时间缓慢变化。例如，零售商将开设新商店，需要将其添加到商店表中，或者现有商店可能会更改其平方英尺或某些其他跟踪的特征。这些更改导致插入单个记录或更新
记录（取决于所选策略）。数据重述。有时发现收集的数据不正确，需要更正。从Hive 0.14开始，可以通过INSERT，UPDATE和DELETE支持这些用例
Hive事务表的局限性
尚不支持BEGIN，COMMIT和ROLLBACK。所有语言操作都是自动提交的
仅支持ORC文件格式（STORED AS ORC）
默认情况下事务配置为关闭。需要配置参数开启使用
表必须是**分桶表（Bucketed）**才可以使用事务功能
表参数transactional必须为true
外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表
语法：
-- Hive中事务表的创建使用
-- 开启事务配置（可以使用set设置当前session生效 也可以配置在hive-site.xml中）
set hive.support.concurrency = true; --Hive是否支持并发
set hive.enforce.bucketing = true; --从Hive2.0开始不再需要 是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式，非严格
create table tablename(column_name)clustered by (column_name) into n buckets stored as orc TBLPROPERTIES('transactional'='true');
8.Hive View 视图
View的概念
Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据。通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图。创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败，并且视图不能存储数据，操作数据，只能查询概况起来就是：视图是用来简化操作的，它其实是一张虚表，在视图中不缓冲记录，也没有提高查询性能
View相关语法
--hive中有一张真实的基础表t_usa_covid19
select *
from itcast.t_usa_covid19;
--1、创建视图
create view v_usa_covid19 as select count_date, county,state,deaths from t_usa_covid19 limit 5;
--能否从已有的视图中创建视图呢 可以的
create view v_usa_covid19_from_view as select * from v_usa_covid19 limit 2;
--2、显示当前已有的视图
show tables;
show views;--hive v2.2.0之后支持
--3、视图的查询使用
select *
from v_usa_covid19;
--能否插入数据到视图中呢？
--不行 报错 SemanticException:A view cannot be used as target table for LOAD or INSERT
insert into v_usa_covid19 select count_date,county,state,deaths from t_usa_covid19;
--4、查看视图定义
show create table v_usa_covid19;
--5、删除视图
drop view v_usa_covid19_from_view;
--6、更改视图属性
alter view v_usa_covid19 set TBLPROPERTIES ('comment' = 'This is a view');
--7、更改视图定义
alter view v_usa_covid19 as select county,deaths from t_usa_covid19 limit 2;
View的好处
将真实表中特定的列数据提供给用户，保护数据隐私降低查询的复杂度，优化查询语句 9. Hive3.0新特性：物化视图 materialized views
What is materialized views ？
物化视图（Materialized View）是一个包括查询结果的数据库对像，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。使用物化视图的目的就是通过预计算，提高查询性能，需要占用一定的存储空间。值得注意的是，3.0中提供了物化视图存储选择机制，可以本地存储在hive，同时可以通过用户自定义storage handlers存储在其他系统（如Druid）。Hive引入物化视图的目的就是为了优化数据查询访问的效率,相当于从数据预处理的角度优化数据访问。Hive从3.0丢弃了index索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度
物化视图和视图的区别
视图是虚拟的，逻辑存在的，只有定义没有存储数据物化视图是真实的，物理存在的，里面存储着预计算的数据
不同于视图，物化视图能够缓存数据，在创建物化视图的时候就把数据缓存起来了，hive把物化视图当成一张“表”，将数据缓存。而视图只是创建一个虚表，只有表结构，没有数据，实际查询的时候再去改写SQL去访问实际的数据表 视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能。
语法：
--物化视图的创建语法
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
[DISABLE REWRITE]
[COMMENT materialized_view_comment]
[PARTITIonED ON (col_name, ...)]
[CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
AS SELECt ...;
物化视图创建后，select查询执行数据自动落地，"自动"也即在query的执行期间，任何用户对该物化视图是不可见的
默认该物化视图可被用于查询优化器optimizer查询重写（在物化视图创建期间可以通过DISABLE REWRITE参数设置禁止使用）
SerDe和storage format非强制参数，可以用户配置，默认可用hive.materializedview.serde、
hive.materializedview.fileformat
物化视图可以使用custom storage handlers存储在外部系统
目前支持物化视图的drop和show操作。
-- Drops a materialized view DROP MATERIALIZED VIEW [db_name.]materialized_view_name; -- Shows materialized views (with optional filters) SHOW MATERIALIZED VIEWS [IN database_name]; -- Shows information about a specific materialized view DESCRIBE [EXTENDED | FORMATTED] [db_name.]materialized_view_name;
当数据源变更（新数据插入inserted、数据修改modified），物化视图也需要更新以保持数据一致性，目前需要用户主动触发rebuild
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;
基于物化视图的查询重写
物化视图创建后即可用于相关查询的加速，用户提交查询query，若该query经过重写后可命中已建视图，则被重写命中相关已建视图实现查询加速。
是否重写查询使用物化视图可以通过全局参数控制，默认为true：
SET hive.materializedview.rewriting=true;
用户可选择性的失能物化视图的重写：
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;10 Hive DDL 其他语法
Database|schema（数据库） DDL操作
create database/schema
CREATE (DATAbase|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)];
COMMENT：数据库的注释说明语句LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouseWITH DBPROPERTIES：用于指定一些数据库的属性配置。注意：使用location指定路径的时候，最好是一个新创建的空文件夹
Describe database
DESCRIBE DATAbase/SCHEMA [EXTENDED] db_name;
Hive中的DESCRIBE DATAbase语句用于显示Hive中数据库的名称，其注释（如果已设置）及其在文件系统上的位置等信息。
use database
USE database_name;
Hive中的USE DATAbase语句用于选择特定的数据库,切换当前会话使用哪一个数据库进行操作。
drop database
DROP (DATAbase|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。要删除带有表的数据库，我们可以使用CASCADE
alter database
-- 更改数据库属性 ALTER (DATAbase|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- 更改数据库所有者 ALTER (DATAbase|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; -- 更改数据库位置 ALTER (DATAbase|SCHEMA) database_name SET LOCATION hdfs_path;
Hive中的ALTER DATAbase语句用于更改与Hive中的数据库关联的元数据
Table（表）DDL操作
describe table
describe formatted [db_name.]table_name; describe extended [db_name.]table_name;
Hive中的DESCRIBE table语句用于显示Hive中表的元数据信息。如果指定了EXTENDED关键字，则它将以Thrift序列化形式显示表的所有元数据如果指定了FORMATTED关键字，则它将以表格格式显示元数据。
drop table
DROP TABLE [IF EXISTS] table_name [PURGE];
DROP TABLE删除该表的元数据和数据。如果已配置垃圾桶（且未指定PURGE），则该表对应的数据实际上将移动到.Trash/Current目录，而元数据完全丢失。删除EXTERNAL表时，该表中的数据不会从文件系统中删除，只删除元数据如果指定了PURGE，则表数据不会进入.Trash/Current目录，跳过垃圾桶直接被删除。因此如果DROP失败，则无法挽回该表数据。
truncate table
TRUNCATE [TABLE] table_name;
从表中删除所有行。可以简单理解为清空表的所有数据但是保留表的元数据结构。如果HDFS启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除
alter table
--1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;
--2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
--更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
--3、更改SerDe属性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');
--移除SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );
--4、更改表的文件存储格式 该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进行。
ALTER TABLE table_name SET FILEFORMAT file_format;
--5、更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";
--6、更改列名称/类型/位置/注释
CREATE TABLE test_change (a int, b int, c int);
// First change column a's name to a1.
ALTER TABLE test_change CHANGE a a1 INT;
// Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table's structure is: b int, a2 string, c int.
// Then change column c's name to c1, and put it as the first column.
ALTER TABLE test_change CHANGE c c1 INT FIRST;
// The new table's structure is: c1 int, b int, a2 string.
// Add a comment to column a1
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';
--7、添加/替换列
--使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。
--REPLACE COLUMNS 将删除所有现有列，并添加新的列集。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);
11 Hive Show 显示语法
--1、显示所有数据库 SCHEMAS和DATAbaseS的用法 功能一样 show databases; show schemas; --2、显示当前数据库所有表/视图/物化视图/分区/索引 show tables; SHOW TABLES [IN database_name]; --指定某个数据库 --3、显示当前数据库下所有视图 Show Views; SHOW VIEWS 'test_*'; -- show all views that start with "test_" SHOW VIEWS FROM test1; -- show views from database test1 SHOW VIEWS [IN/FROM database_name]; --4、显示当前数据库下所有物化视图 SHOW MATERIALIZED VIEWS [IN/FROM database_name]; --5、显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错 show partitions table_name; --6、显示表/分区的扩展信息 SHOW TABLE EXTENDED [IN|FROM database_name] LIKE table_name; show table extended like student; --7、显示表的属性信息 SHOW TBLPROPERTIES table_name; show tblproperties student; --8、显示表、视图的创建语句 SHOW CREATE TABLE ([db_name.]table_name|view_name); show create table student; --9、显示表中的所有列，包括分区列。 SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]; show columns in student; --10、显示当前支持的所有自定义和内置的函数 show functions; --11、Describe desc --查看表信息 desc extended table_name; --查看表信息（格式化美观） desc formatted table_name; --查看数据库相关信息 describe database database_name;
