HiveSql数据定义语言(DDL)

一、数据定义语言(DDL)概述及建表基础

数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、table、view、index等。核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。

1.1 Hive数据类型详解

Hive中的数据类型指的是Hive表中的列字段类型。Hive数据类型整体分为两个类别：原生数据类型（primitive data type）和复杂数据类型（complex data type）。
原生数据类型包括：数值类型、时间类型、字符串类型、杂项数据类型；
复杂数据类型包括：array数组、map映射、struct结构、union联合体。
关于Hive的数据类型，需要注意：

• 英文字母大小写不敏感；
• 除SQL数据类型外，还支持Java数据类型，比如：string；
• int和string是使用最多的，大多数函数都支持；
• 复杂数据类型的使用通常需要和分隔符指定语法配合使用;
• 如果定义的数据类型和文件不一致，hive会尝试隐式转换，但是不保证成功。显式类型转换可以使用CAST函数，如果强制转换失败就会返回为空NULL。

1.2 Hive读写文件机制

SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。Hive使用SerDe（和FileFormat）读取和写入行对象。

1.3 Hive读写文件流程

Hive读取文件机制：首先调用InputFormat（默认TextInputFormat），返回一条一条kv键值对记录（默认是一行对应一条记录）。然后调用SerDe（默认LazySimpleSerDe）的Deserializer，将一条记录中的value根据分隔符切分为各个字段。
Hive写文件机制：将Row写入文件时，首先调用SerDe（默认LazySimpleSerDe）的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。

1.4 SerDe的语法

row format delimited | serde
ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据。如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据,也支持用户自定义SerDe类。

1.5 LazySimpleSerDe分隔符指定

LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。如复杂类型map。需要指定字段之间分隔符、集合元素之间分隔符、map kv之间分隔符。

1.6 默认分隔符

hive建表时如果没有row format语法。此时字段之间默认的分割符是'\001'，是一种特殊的字符，使用的是ascii编码的值，键盘是打不出来的。

1.7 hive数据存储路径

Hive表默认存储路径是由${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定。默认值是：/user/hive/warehouse。在Hive建表的时候，可以通过location语法来更改数据在HDFS上的存储路径，使得建表加载数据更加灵活方便。

二、Hive DDL建表高阶

2.1 Hive内、外部表

2.1.1 内部表

内部表（Internal table）也称为被Hive拥有和管理的托管表（Managed table）。默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。

2.1.2 外部表

外部表（External table）中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字。删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。

2.1.3 内部表、外部表的差异

无论内部表还是外部表，Hive都在Hive Metastore中管理表定义及其分区信息。删除内部表会从Metastore中删除表元数据，还会从HDFS中删除其所有数据/文件。删除外部表，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。

选择方法：当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。当文件已经存在或位于远程位置时，请使用外部表，因为即使删除表，文件也会被保留。

2.2 Hive分区表

当Hive表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive支持根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。需要注意：分区字段不能是表中已经存在的字段。

2.2.1 分区表数据加载--静态分区

所谓静态分区指的是分区的字段值是由用户在加载数据的时候手动指定的。使用load加载数据。

2.2.2 分区表数据加载--动态分区

所谓动态分区指的是分区的字段值是基于查询结果自动推断出来的。核心语法就是insert+select。
启用hive动态分区，需要在hive会话中设置两个参数：

# 第一个参数表示开启动态分区功能
set hive.exec.dynamic.partition=true;
# 第二个参数指定动态分区的模式。分为nonstick非严格模式和strict严格模式。
# strict严格模式要求至少有一个分区为静态分区。
set hive.exec.dynamic.partition.mode=nonstrict;

hive对动态分区的默认配置

-- 开启或关闭动态分区
hive.exec.dynamic.partition=false;
-- 设置为nonstrict模式，让所有分区都动态配置，否则至少需要指定一个分区值
hive.exec.dynamic.partition.mode=strict;
-- 能被mapper或reducer创建的最大动态分区数，超出而报错
hive.exec.max.dynamic.partitions.pernode=100;
-- 一条带有动态分区SQL语句所能创建的最大动态分区总数，超过则报错
hive.exec.max.dynamic.partitions=1000;
-- 全局能被创建文件数目的最大值，通过Hadoop计数器跟踪，若超过则报错
hive.exec.max.created.files=100000;

2.2.3 多重分区表

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

2.2.4 分区表的本质

分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。只需要根据分区值找到对应的文件夹，扫描本分区下的文件即可，避免全表数据扫描,这种指定分区查询的方式叫做分区裁剪。
注意：

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

2.3 Hive分桶表

2.3.1 分桶表概述

分桶表也叫做桶表，是一种用于优化查询而设计的表类型。该功能可以让数据分解为若干个部分易于管理。在分桶时，我们要指定根据哪个字段将数据分为几桶(几个部分),可以发现桶编号相同的数据会被分到同一个桶当中,hash_function取决于分桶字段bucketing_column的类型。而且分桶的字段必须是表中已经存在的字段，其中CLUSTERED BY (col_name)表示根据哪个字段进行分；INTO N BUCKETS表示分为几桶（也就是几个部分）。

2.3.2 分桶表的好处

1、基于分桶字段查询时，减少全表扫描；
2、JOIN时可以提高MR程序效率，减少笛卡尔积数量；
3、分桶表数据进行抽样；

2.4 Hive Transactional Tables事务表

我理解的呢就是很片面的更改删除操作，在执行这些操作前，需要开启事务配置：

# 可以使用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实例上运行多少个压缩程序工作线程。

Hive事务表局限性

• 尚不支持BEGIN，COMMIT和ROLLBACK。所有语言操作都是自动提交的。
• 仅支持ORC文件格式（STORED AS ORC）。
• 默认情况下事务配置为关闭。需要配置参数开启使用。
• 表必须是分桶表（Bucketed）才可以使用事务功能。
• 表参数transactional必须为true；
• 外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表。

2.5 Hive View视图

Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据。通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图，但是视图不能存储数据，操作数据，只能查询，视图是用来简化操作的虚表。

2.6 物化视图materialized views

2.6.1 概念

物化视图（Materialized View）是一个包括查询结果的数据库对像，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果。这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。使用物化视图的目的就是通过预计算，提高查询性能，当然需要占用一定的存储空间。

2.6.2 物化视图和视图的区别

• 视图是虚拟的，逻辑存在的，只有定义没有存储数据。物化视图是真实的，物理存在的，里面存储着预计算的数据，物化视图能够缓存数据，在创建物化视图的时候就把数据缓存起来了。
• 视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能。

2.6.3 物化视图的查询重写

物化视图创建后即可用于相关查询的加速，用户提交查询query，若该query经过重写后可命中已建视图，则被重写命中相关已建视图实现查询加速。是否重写查询使用物化视图可以通过全局参数控制，默认为true： SET hive.materializedview.rewriting=true;
用户可选择性的失能物化视图的重写：

# 禁用物化视图自动重写
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

三、Hive DDL其他语法

3.1 Database|schema(数据库)DDL操作

在Hive中， DATABASE和SCHEMA是可互换的，使用DATABASE或SCHEMA都可以。
1. Create database:创建数据库

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/warehouse；
# WITH DBPROPERTIES：用于指定一些数据库的属性配置。

2. Describe database:描述数据库

DESCRIBE/DESC DATABASE/SCHEMA [EXTENDED] db_name;
# EXTENDED：用于显示更多信息。

3. Use database:切换数据库

USE database_name;

4. Drop database:删除数据库

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

5. Alter database:更改与Hive中的数据库关联的元数据

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

3.2 Table（表）DDL操作

1. Describe table:显示表的元数据信息

# 指定了FORMATTED关键字，则它将以表格格式显示元数据
describe formatted [db_name.]table_name;
# 指定了EXTENDED关键字，则它将以Thrift序列化形式显示表的所有元数据
describe extended [db_name.]table_name;

2. Drop table:删除表

# 如果配置垃圾桶(未指定PURGE)，则该表对应的数据实际上将移到.Trash/Current目录# 如果指定了PURGE，则表数据不会进入.Trash/Current目录，跳过垃圾桶直接被删除。
DROP TABLE [IF EXISTS] table_name [PURGE];

3. Truncate table:清空表

# 从表中删除所有行。清空表的所有数据但是保留表的元数据结构。
# 如果HDFS启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除。
TRUNCATE [TABLE] table_name;

4. Alter table:修改表

# 更改表名
ALTER TABLE table_name RENAME TO new_table_name;
# 也可以修改属性，位置，注释，添加/替换列等

3.3 Partition（分区）DDL操作

1. Add partition

# ADD PARTITION会更改表元数据，但不会加载数据。
# 如果分区位置中不存在数据，查询将不会返回任何结果。
ALTER TABLE table_name ADD PARTITION (dt='2') location
'/user/hadoop/warehouse/table_name/dt=2'; 

2. rename partition

# 重命名分区
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

3. delete partition

# 直接删除数据 不进垃圾桶
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us') PURGE;

4. msck partition
MSCK是metastore check的缩写，表示元数据检查操作，可用于元数据的修复。MSC命令的默认选项是“添加分区”。使用此选项，它将把HDFS上存在但元存储中不存在的所有分区添加到元存储中。

# 修复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

5. alter partition

# 修改分区的存储格式或位置
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET FILEFORMAT file_format/LOCATION "new location";

四、Hive Show显示语法

Show相关的语句提供了一种查询Hive metastore的方法。可以帮助用户查询相关信息。

show databases;
show schemas;
show tables;
Show Views;
SHOW MATERIALIZED VIEWS;
show partitions table_name;
show table extended table_name;
SHOW TBLPROPERTIES table_name;
show create table table_name;
show columns in table_name;
show functions;
desc extended table_name;
desc formatted table_name;
describe database database_name;