Hive学习笔记-HiveSQL(1)-数据定义语言DDL

数据定义语言概述

数据定义语言（Data Definition Language， DDL），是SQL中对数据库内部的对象结构进行创建，删除，修改等操作的语言，核心语法由create、alter和drop三个部分组成，并不涉及表内部数据的操作

Hive SQL与标准SQL的语法大体相同，在细节处存在少量差异。

推荐使用IDEA中的DataBase来连接Hive，其中需要下载指定对应版本的驱动。

Hive SQL DDL 建表语法

完整语法树

Hive建表语法的完整语法树如下：

• 蓝色字体是建表语法的关键字，用于指定某些特定的功能
• 【】中括号的语法表示可选
• 表示使用的时候，左右语法需要二选一
• 建表语法中，语法顺序需要和语法树中的顺序保持一致

数据类型

Hive中的数据类型整体上分为原生数据类型和复杂数据类型

• 原生数据类型包括：数值类型、时间日期类型、字符串类型、杂项数据类型
• 复杂数据类型包括：Array数组、Map映射、Struct结构、Union联合体

注意事项：

1. HQL中，数据类型大小写不敏感
2. 除了SQL数据类型之外，也支持Java数据类型
3. 复杂数据类型的使用通常需要和分隔符指定语法来配合使用
4. 如果定义的数据类型和文件中的内容不一致，Hive会尝试隐式转换，但是不保证成功
5. 也提供显示类型转换的函数CAST

读写文件机制

首先介绍SerDE。SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。Hive中使用SerDE读取和写入表中的行对象。

具体来说，在读取HDFS文件的时候，首先通过InputFileFormat以键值对的形式读取一行的数据，然后通过SerDE进行反序列化得到行对象。在写HDFS文件的时候，先通过SerDe进行行对象的序列化，得到键值对的形式，然后通过OutputFileFormat写入HDFS文件中。（需要注意的是键值对中的Key表示行偏移量，在读取的时候会被忽略，在写入的时候key始终是常数，行对象基本存储在value中）

我们可以通过desc formatted table_name来查看表的相关SerDe的信息。默认情况下：

• SerDe使用的是：org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
• InputFormat使用的是：org.apache.hadoop.mapred.TextInputFormat
• OutputFormat使用的是：org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

在语法树中，相关语法是row format，用于指定使用哪个SerDe类进行序列化，以及如何指定分隔符

row format是语法关键字，后面需要跟上delimited和serde二选一

• delimited表示使用默认的LazySimpleSerDe类来处理数据，也是默认使用的关键字
• serde表示指定其他的SerDe类，或者自己自定义的SerDE类来处理数据

--默认使用delimited
row format delimited
	[fields terminated by 'xxx'] --指定字段之间的分隔符
	[collection items terminated by 'xxx'] --指定集合元素之间的分隔符
	[map keys terminated by 'xxx'] --指定Map里面KV之间的分隔符
	[lines terminated by 'xxx'] --指定行数据之间的分隔符

如果在建表的时候如果没有使用row format指定分隔符，则采用默认分隔符，默认分隔符为\001，是一种特殊字符，在Vim编辑器中表现为^A，在一些文本编辑器中，可以显示的表示法显示为SOH

• 这里的分隔符都是单字节，类型为char的分隔符

数据存储路径

一般情况下，我们使用建表语句创建表之后，会在HDFS中对应目录下创建一个文件夹，代表该表，之后我们向其中上传HDFS文件，如果文件中的内容和表模式匹配上的话，Hive就会帮我们完成文件到表的映射。

Hive表默认存储路径是由${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定，默认值为：/user/hive/warehouse。在该路径下，文件将根据所属的库、表，有规律地存储在对应的文件夹下。

当然我们也可以不使用默认的存储路径，而在创建表的时候指定该表读取哪个目录下的文件，通过location语法来指定数据在HDFS上的存储路径，使得建表加载数据更加灵活方便。

如果指定了数据的路径，那么对应的默认路径下将没有该表的文件夹

Hive 内部表和外部表

在Hive中存在内部表和外部表的概念。

内部表：

• 内部表（Internal Table），也称为托管表（Managed Table）
• Hive拥有内部表的结构和文件，完全管理表（元数据+真实数据）的声明周期
• 当我们删除表的时候，会删除真实数据以及表的元数据
• 默认情况下创建的表属于内部表

外部表：

• 外部表（External Table），其中的数据不是Hive拥有或者管理的，Hive只管理外部表元数据的生命周期
• 创建外部表，只需要在创建表的时候增加external关键字
• 删除外部表只会删除元数据，不会删除实际数据。在Hive外部，HDFS上仍然可以访问到真实数据

通常我们会使用外部表搭配location语法来指定数据的路径，让数据更加安全。但是需要注意的是，location的使用与否与是否是外部表没有绝对的联系。

Hive Partitioned 分区表

分区表是Hive中的一种优化手段。当Hive表中的数据量变大，文件个数变多的时候，为了避免查询的时候进行全表扫描，Hive支持根据指定的字段对表进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。

表现在HDFS文件的存储上，表文件夹下会有多个分区文件夹，命名形式为分区字段=分区值，在不同的分区文件夹下，存放了分区对应的数据文件

在建表语法中，分区功能通过partitioned by关键字来完成

partitioned by {col_name data_type [comment col_comment],...}

• 注意：分区字段不能是表中已经存在的字段，因为分区字段最终会以虚拟字段的形式显示在表结构上

我们既然不能指定分区字段是表中已经存在的字段，但是总得有一种方式告诉Hive我们的数据是处在哪个分区中。Hive中提供两种分区表数据加载的模式，分别是静态分区和动态分区加载。

首先明确一个前提，我们已经执行了建表语句，其中指定了分区字段假设为col_name，注意分区字段的名称不和实际字段重复。此时在HDFS上已经存在了对应的表文件夹

静态分区加载：

静态分区指的是我们人为指定某个文件属于哪一个分区，这样我们应该保证在实际场景下，文件中的所有行都是对应分区下的数据。

使用如下语法：

load data [local] inpath <file_path> into table <table_name> partition(分区字段='分区值')

• local参数用于指定待加载的数据是位于本地文件系统还是HDFS文件系统
• 需要指定文件路径、表名、该文件下数据对应的分区值

总结来说，如果使用静态分区加载，我们需要事先准备不同的文件，每个文件下的数据，分区值都是相同的，然后按照分区值手动进行文件加载。这样Hive就会将文件组织成分区文件夹的格式，并且在底层按照分区表来管理数据。

动态分区加载：

动态分区指的是分区的字段值是基于查询结果（参数位置自动推断出来的），核心语法为insert+select。具体来说，我们插入表中的结果是从其他的表查询得到的，在查询的过程中根据分区字段的值来划分到不同的分区中，因此，我们需要事先有一张数据表。

启用Hive动态分区，需要在Hive会话中设置下面两个参数

--是否开启动态分区
set hive.exec.dynamic.partition=ture;

-- 指定动态分区模式，分为nonstrict非严格模式和strict严格模式
-- strict严格模式下，要求至少有一个分区为静态分区
set hive.exec.dynamic.partition.mode=nonstrict

严格模式经常和多分区匹配使用，其中至少要有一个分区为jing'tai

之后结合insert和select进行动态分区加载：

insert into table 动态分区表 partition(role) 
select tmp.*,tmp.role_main from 查询表 as tmp;

• role为我们指定的分区字段
• role_main为查询表中的具体字段，我们将这个字段的值作为分区的依据
• 根据位置进行分区字段的对应，即tmp.role_main对应到分区字段

总结来说，如果使用动态分区加载，完成的逻辑就是从已有的表中进行查询，然后将需要作为分区依据的字段查询到最后，然后插入分区表中。这样Hive会在查询的过程中判断分区字段，然后存放到不同的分区文件夹中。

上面说的是单分区，实际上观察建表语句中分区的相关语法，我们可以发现Hive也支持多重分区，只需要在partitioned by后面指定多个分区字段即可。但是分区字段有先后之分，是一种递进的关系。具体来说会在前一个分区文件夹下继续划分子文件夹。

分区表的本质在于提供了一种将Hive表数据分离成多个目录的方法。不同分区对应不同文件夹，同一分区的数据存储在同一个文件夹下。在查询过滤的时候只需要根据分区值找到对应的文件夹，然后扫描文件夹下本分区的文件即可，这样就避免了全表扫描。这种指定分区查询的方式就叫做分区裁剪。

分区表的使用重点在于，建表的时候要根据业务场景设置合适的分区字段，例如日期、地域、类别等，然后在查询的时候尽量先使用where进行分区过滤，查询指定分区的数据来避免全表扫描。

简单总结：

1. 分区表不是建表的必要语法规则，是一种优化手段，可选
2. 分区字段不能是表中已有的字段，不能重复
3. 分区字段是虚拟字段，其数据并不存储在底层的文件中
4. 分区字段值的确定来自于用户手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）
5. Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度

Hive Bucketed 分桶表

分桶表也是Hive中的一种优化方式。分桶表对应的数据文件在HDFS底层会被分解为若干个部分，即被拆分成多个独立的小文件。拆分的依据在建表的时候指定要根据哪个字段将数据分为几个部分。

具体语法如下：

clustered by (col_name) 
[sorted by (xxx desc|asc)]
into n buckets;

• 分别指定分桶的字段和分成几个桶
• 需要注意的是分桶的字段必须是表中已经存在的字段
• 在创建分桶表的时候，还可以指定分桶内的数据排序规则
• 分桶规则如下：桶编号相同的数据会被分到一个桶当中

Bucket number = hash_function(bucketing_column) mod num_buckets

• 哈希方法取决于分桶字段的类型，如果是ini则直接是原值，如果是其他复杂数据类型，哈希方法是从该类型派生出来的某个数字，例如hashcode值

分桶表的数据加载需要经过如下的流程，核心语法也是insert+select：

1. 开启分桶的功能（从Hive2.0开始不需要设置，默认开启）

   set hive.enforce.bucketing=ture

2. 将源数据加载到普通的hive表中，包括hive表创建以及文件上传到HDFS对应路径两个步骤

   -- 普通hive表这里命名为tmp

3. 使用分桶语法创建分桶表

   -- 分桶表这里命名为bucket_table

4. 使用insert+select语法将数据加载到分桶表中（类似于分区表的动态架加载）

   insert into bucket_table select * from tmp;

经过以上的流程之后，就可以将数据加载到分桶表中。并且在HDFS上查看对应目录可以发现数据被分成了几个部分，分桶字段一样的数据就一定被分到了同一个桶中。

分桶表的核心在于原始的数据被拆分成了不同的文件，分桶字段对应值相同的数据进入同一个桶（同一个桶中，数据的对应字段可能不同）

使用分桶表可以带来下面的好处：

1. 基于分桶字段查询的时候，减少全表扫描
2. 在Join的时候可以提高效率，减少笛卡尔积的数量（基于分桶字段join）
3. 分桶表数据进行高效抽样

Hive Transactional 事务表

Hive本身从设计之初时，就是不支持事务的，因为Hive的核心目标是将已经存在的结构化数据文件映射成为表，然后提供基于表的SQL分析处理，是一款面向分析的工具。且映射的数据通常存储于HDFS上，而HDFS是不支持随机修改文件数据的。这个定位就意味着在早期的Hive的SQL语法中是没有update，delete操作的，也就没有所谓的事务支持了，因为都是select查询分析操作。

从Hive0.14版本开始，事务开始添加到Hive中，解决一些可能需要事务的场景，例如流式传输数据、数据修正、插入或更新单条记录等场景。

但是Hive毕竟不是专门用于这类场景的工具，虽然支持了具有ACID性质的事务，在使用的时候还是有很多局限性：

• 不支持事务的begin、commit和rollback，操作都是自动提交
• 仅支持orc文件格式
• 默认情况下事务配置关闭，需要手动配置参数开启
• 表参数transactional必须为true
• 外部表不能成为ACID表，不允许从非ACID会话读取或写入ACID表

对于一个普通表，我们执行update、delete和insert操作，只有insert语句可以执行，update和delete操作会报错，这是因为insert操作底层是直接把数据写在了一个新的文件当中

而开启了事务之后，可以执行update和delete操作，但是底层并不是随机修改的（由于HDFS不支持随机修改），而是通过标记机制来达到对应的效果

--Hive中事务表的创建使用
--1、开启事务配置（可以使用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; --动态分区模式  非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。

--2、创建Hive事务表
drop table if exists trans_student;
create table trans_student(
   id int,
   name String,
   age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
--注意 事务表创建几个要素：开启参数、分桶表、存储格式orc、表属性

关于事务表更加详细的说明可以查看后续的笔记。

Hive Views 视图

Hive中的视图View是一张虚拟的表，只保存定义，而不实际存储数据。我们可以将视图看作是一张普通的Hive表

• 通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图
• 创建视图的时候，会冻结视图的架构。如果删除或更改基础表，则视图失效
• 视图是用来简化操作的，不会缓冲记录，也不会提高查询的性能

视图的语法如下：

-- 创建视图
create view <视图名称> as select * from xxx;

-- 删除视图
drop view <视图名称>

-- 更改视图属性
alter view <视图名称> set tblproperties('comment'='new_comment')

-- 更改视图定义
alter view <视图名称> as [新的selet语句]

使用视图可以将真实表中的特定列数据提供给用户，保护数据的隐私；也可以降低查询的复杂度，优化查询语句。

Hive Materialized Views 物化视图

物化视图是Hive3.0中的新特性，指的是一个包括查询结果的数据库对象，可以用于预先计算并保存操作的结果。相当于一个预处理和缓存的操作，在执行查询的时候，可以避免进行这些耗时的操作，从而快速地得到结果。

使用物化视图的目的就是通过预先计算来提高查询性能，当然相应的需要占用一定的存储空间。

• Hive3.0开始尝试引入物化视图，并提供对于物化视图的查询自动重写机制（基于Apache Calcite实现）
• Hive的物化视图还提供了物化视图存储选择机制，可以本地存储在Hive，也可以通过用户自定义storage handlers存储在其他系统（如Druid）
• Hive引入物化视图的目的就是为了优化数据查询访问的效率,相当于从数据预处理的角度优化数据访问
• Hive从3.0丢弃了index索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度

视图 VS 物化视图：

1. 视图是虚拟的，只有定义没有存储数据
2. 物化视图是真实的，里面存储了预计算的数据
3. 使用物化视图的时候，直接将物化视图当作一张表，将数据缓存
4. 使用视图的时候，对于用户来说这是可以当作一张表，但是对于Hive来说实际查询的时候还是再去执行SQL去访问实际的数据表
5. 视图的目的是简化降低查询的复杂度
6. 物化视图的目的是提高查询性能

物化视图的语法如下：

create materialized view <物化视图名称>
as select语句

• 物化视图创建之后，会自动执行select来查询。直到查询完成之后物化视图才变成可用状态

• 当数据源发生变更，物化视图也应该需要更新来保持数据的一致性，但是目前需要用户主动触发rebuild进行重构

  alter materialized view <物化视图名称> rebuild;

基于物化视图的查询重写：这是一种查询的优化手段，物化视图创建之后可以用于相关查询的加速。如果用户提交了一个查询，而这个查询正好和某个物化视图的定义相同，则可以直接通过物化视图返回结果，以实现查询的加速

是否使用基于物化视图的查询重写可以通过全局参数控制，默认为true

hive.materializedview.rewriting=true;

用户也可以选择性的控制，指定特定的物化视图用于重写机制

alter materialized view <物化视图名称> enable|disable rewrite;

Hive SQL DDL 其他语法

DataBase 数据库操作

--创建数据库
create database if not exists test_database
comment "数据库注释说明" --指定数据库的注释说明
location hdfs_path --指定数据库在hdfs上的存储位置
with dbproperties ('createdBy'='xxx'); -- 指定数据库的属性配置

--描述数据库信息
describe database test_database;
describe database extended test_database; --显示更多信息
desc database extended test_database; --简写

--切换数据库
use default;
use test_database;

--删除数据库
--注意 CASCADE关键字慎重使用
--默认情况下只有在数据库为空的时候才可以删除，使用的是restrict，但是我们可以使用CASCADE来强制删除
drop (database|schema) [if exists] database_name [restrict|cascade];
drop database test_database cascade ;

--更改数据库属性
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;

Table 表操作

Hive中针对表的操作是DDL中的核心操作，包括建表、修改表、删除表、描述表元数据信息，其中的建表语句又是一个核心。

由于Hive建表之后加载映射数据很快，实际中如果建表有问题，更多可以直接删除重建

--查询指定表的元数据信息
describe formatted test_database.test_table;

--删除表
--删除表的元数据和数据
drop table [if exists] table_name [purge]
--清空表(从表中删除所有行，但是保留表的元数据结构)
truncate table table_name

• 如果配置了垃圾桶但是没有指定purge，则该表对应的数据实际上移动到HDFS垃圾桶，但是元数据完全丢失；如果指定了purge，则表数据跳过HDFS垃圾桶，直接删除实际数据
• 删除外部表，只删除元数据

--修改操作
--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);
alter table test_change change a a1 int;
alter table test_change change a1 a2 string after b;
alter table test_change change c c1 int first;
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,...);

Partition 分区操作

Hive中针对分区Partition的操作主要包括增加分区、删除分区、重命名分区、修复分区和修改分区。

增加分区：增加分区会更改表的元数据，但是不会加载数据。如果分区位置中不存在数据，则查询的时候不会返回结果。（需要自己将数据加载到增加的分区当中）

--添加分区
alter table table_name add partition (分区字段='分区值') 
location hdfs_path;

--一次性添加多个分区
alter table table_name add 
partition (分区字段='分区值') location hdfs_path
partition (分区字段='分区值') location hdfs_path;

删除分区：删除分区会删除该分区的数据和元数据

alter table table_name drop [if exists] partition (dt='2008-08-08', country='us');
alter table table_name drop [if exists] partition (dt='2008-08-08', country='us') purge; --直接删除数据 不进垃圾桶

重命名分区：

alter table table_name partition (分区字段 ="旧值") rename to partition (分期字段 ="新值");

修改分区：

--更改分区文件存储格式
alter table table_name partition (分区字段='分区值') set fileformat file_format;
--更改分区位置
alter table table_name partition (分区字段='分区值') set location "new location";

修复分区：

msck [repair] table table_name [add/drop/sync partitions];

在前面我们都是使用Hive来进行分区数据加载，之后Hive会维护元数据，以及在HDFS上有规律地组织文件夹结构。具体表现为在表文件夹下存在对应的分区文件夹。

那么如果我们直接手动创建或者删除HDFS上的分区文件夹，Metastore将不会意识到分区信息的更改，我们也无法查询到对应的信息。而msck就是Metastore Check的缩写，表示元数据检查操作，可以用于元数据的修复。

• msck默认行为为add partitions：将hdfs上存在但是元数据中不存在的分区添加到metastore（也可以看作是一种数据导入的方法）
• drop partitions：将hdfs上不存在但是元数据中存在的分区信息删除
• sync partitions：同步分区信息，等效于上面两个的综合
• 如果存在大量未跟踪的分区，则可以批量运行msck repair table，以避免内存不足的错误

Hive Show 语法

show语法主要功能是展示数据库、数据表的一些相关信息

--显示所有数据库 schemas和databases的用法 功能一样
show databases;
show schemas;

--显示当前数据库所有表/视图/物化视图/分区/索引
show tables;
show tables [in database_name]; --指定某个数据库

--显示当前数据库下所有视图
show views;
show views 'test_*'; --正则匹配，展示所有以test_开头的view
show views [in/from database_name]; -- 展示数据库test的所有视图

--显示当前数据库下所有物化视图
show materialized views [in/from database_name];

--显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错
show partitions table_name;
show partitions test_database.table_name;

--显示表/分区的扩展信息
show table extended [in|from database_name] like table_name;
show table extended like student;
describe formatted test_database.table_name;

--显示表的属性信息
show tblproperties table_name;

--显示表、视图的创建语句
show create table ([db_name.]table_name|view_name);

--显示表中的所有列，包括分区列。
show columns (from|in) table_name [(from|in) db_name];

--显示当前支持的所有自定义和内置的函数
show functions;

--查看表信息
desc extended table_name;
--查看表信息（格式化美观-以表格形式展示）
desc formatted table_name;
--查看数据库相关信息
describe database database_name;