Hive数据定义语言DDL

1 Apache Hive客户端使用

Hive发展至今，总共历经了两代客户端工具。

• 第一代客户端（deprecated不推荐使用）：$HIVE_HOME/bin/hive, 是一个 shellUtil。主要功能：一是可用于以交互或批处理模式运行Hive查询；二是用于Hive相关服务的启动，比如metastore服务。
• 第二代客户端（recommended 推荐使用）：$HIVE_HOME/bin/beeline，是一个JDBC客户端，是官方强烈推荐使用的Hive命令行工具，和第一代客户端相比，性能加强安全性提高。

beeline：

• Beeline在嵌入式模式和远程模式下均可工作。
• 在嵌入式模式下，它运行嵌入式 Hive(类似于Hive Client)；而远程模式下beeline通过 Thrift 连接到单独的 HiveServer2服务上，这也是官方推荐在生产环境中使用的模式。

HiveServer、HiveServer2服务：

• HiveServer、HiveServer2都是Hive自带的两种服务，允许客户端在不启动CLI（命令行）的情况下对Hive中的数据进行操作，且两个都允许远程客户端使用多种编程语言如java，python等向hive提交请求，取回结果。

• 但是，HiveServer不能处理多于一个客户端的并发请求。因此在Hive-0.11.0版本中重写了HiveServer代码得到了HiveServer2，进而解决了该问题。HiveServer已经被废弃。

• HiveServer2支持多客户端的并发和身份认证，旨在为开放API客户端如JDBC、ODBC提供更好的支持。

• HiveServer2通过Metastore服务读写元数据。所以在远程模式下，启动HiveServer2之前必须先首先启动metastore服务。

• 特别注意：远程模式下，Beeline客户端只能通过HiveServer2服务访问Hive。而bin/hive是通过Metastore服务访问的。具体关系如下：

Hive第一代客户端使用：

• 在hive安装包的bin目录下，有hive提供的第一代客户端 bin/hive。该客户端可以访问hive的metastore服务，从而达到操作hive的目的。
• 友情提示：如果您是远程模式部署，请手动启动运行metastore服务。如果是内嵌模式和本地模式，直接运行bin/hive，metastore服务会内嵌一起启动。
• 可以直接在启动Hive metastore服务的机器上使用bin/hive客户端操作，此时不需要进行任何配置。

#远程模式 首先启动metastore服务
/export/server/hive/bin/hive --service metastore
#克隆CRT会话窗口 使用hive client连接
/export/server/hive/bin/hive
1
2
3
4

• 如果需要在其他机器上通过bin/hive访问hive metastore服务，只需要在该机器的hive-site.xml配置中添加metastore服务地址即可。

Beeline第二代客户端使用：

• hive经过发展，推出了第二代客户端beeline，但是beeline客户端不是直接访问metastore服务的，而是需要单独启动hiveserver2服务。
• 在hive安装的服务器上，首先启动metastore服务，然后启动hiveserver2服务。
• 注意：开启服务需要先开启hadoop环境（hdfs、yarn）。

#先启动metastore服务 然后启动hiveserver2服务
nohup /export/servers/hive/bin/hive --service metastore &
nohup /export/servers/hive/bin/hive --service hiveserver2 &
1
2
3

• 在node3上使用beeline客户端进行连接访问。需要注意hiveserver2服务启动之后需要稍等一会才可以对外提供服务。
• Beeline是JDBC的客户端，通过JDBC协议和Hiveserver2服务进行通信，协议的地址是：jdbc:hive2://node1:10000

#进入第二代客户端beeline
/export/server/hive/bin/beeline 
#连接node1的hiveserver2服务
beeline>! connect jdbc:hive2://node1:10000
1
2
3
4

2 Hive编译工具

（1）Hive CLI、Beeline CLI

• Hive自带的命令行客户端
• 优点：不需要额外安装
• 缺点：编写SQL环境恶劣，无有效提示，无语法高亮，误操作几率高

（2）文本编辑器

• Sublime、Emacs 、EditPlus、UltraEdit、Visual Studio Code等
• 有些不支持作为客户端连接Hive服务，但是支持SQL语法环境，那就再编辑器中开发SQL,复制到Hive CLI执行；
• 有些支持安装插件作为客户端直连Hive服务；

（3）可视化工具

• IntelliJ IDEA、DataGrip、Dbeaver、SQuirrel SQL Client等
• 可以在Windows、MAC平台中通过JDBC连接HiveServer2的图形界面工具；
• 这类工具往往专门针对SQL类软件进行开发优化、页面美观大方，操作简洁，更重要的是SQL编辑环境优雅；
• SQL语法智能提示补全、关键字高亮、查询结果智能显示、按钮操作大于命令操作；

3 Hive SQL DDL建表基础语法

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]
  
  --设置分隔符（字段间、集合元素间、集合元素kv之间）
[ROW FORMAT DELIMITED|SERDE serde_name WITH SERDEPROPERTIES (property_name=property_value,...)]
  
  --数据存储格式
[STORED AS file_format]
  
  --将表存在哪
[LOCATION hdfs_path]
  
 
[TBLPROPERTIES (property_name=property_value, ...)];
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

• [ ]中括号的语法表示可选。
• |表示使用的时候，左右语法二选一。
• 建表语句中的语法顺序要和语法树中顺序保持一致。

3.1 Hive数据类型详解

概述：

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

原生数据类型：

复杂数据类型：

注意：

• Hive SQL中，数据类型英文字母大小写不敏感；
• 除SQL数据类型外，还支持Java数据类型，比如字符串string；
• 复杂数据类型的使用通常需要和分隔符指定语法配合使用；
• 如果定义的数据类型和文件不一致，Hive会尝试隐式转换，但是不保证成功。

隐式转换：

• 与标准SQL类似，HQL支持隐式和显式类型转换。
• 原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。
• 下表描述了类型之间允许的隐式转换：

显示转换：

• 显式类型转换使用CAST函数。
• 例如，CAST（'100’as INT）会将100字符串转换为100整数值。
• 如果强制转换失败，例如CAST（‘Allen’ as INT），该函数返回NULL。

3.2 Hive读写文件机制

SerDe：

• SerDe是Serializer、Deserializer的简称，目的是用于序列化和反序列化。

• 序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。

• Hive使用SerDe（包括FileFormat）读取和写入表行对象。需要注意的是，“key”部分在读取时会被忽略，而在写入时key始终是常数。基本上行对象存储在“value”中。

• 可以通过desc formatted tablename查看表的相关SerDe信息。

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之间、换行的分隔符号。
• 在建表的时候可以根据数据的特点灵活搭配使用。

Hive默认分隔符：

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

3.3 Hive数据存储路径

默认存储路径：

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

指定存储路径；

• 在Hive建表的时候，可以通过location语法来更改数据在HDFS上的存储路径，使得建表加载数据更加灵活方便。
• 语法：LOCATION ‘’。
• 对于已经生成好的数据文件，使用location指定路径将会很方便。

3.4 案例–王者荣耀数据Hive建表映射

（1）原生数据建表并插入数据

数据样式：

建表：

--创建数据库并切换使用
create database if not exists itheima;
use itheima;
--ddl create table
create table t_archer(
  id int comment "ID",
  name string comment "英雄名称",
  hp_max int comment "最大生命",
  mp_max int comment "最大法力",
  attack_max int comment "最高物攻",
  defense_max int comment "最大物防",
  attack_range string comment "攻击范围",
  role_main string comment "主要定位",
  role_assist string comment "次要定位"
) comment "王者荣耀射手信息"
row format delimited
fields terminated by "\t";
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

传入数据，将文本文件传入到该表的文件夹下：

hadoop fs -put archer.txt  /user/hive/warehouse/itheima.db/t_archer
1

(2)复杂数据建表并插入数据

数据样式：

建表：

create table t_hot_hero_skin_price(
id int,
name string,
win_rate int,
skin_price map<string,int>
)
row format delimited
fields terminated by ',' --字段之间分隔符
collection items terminated by '-'  --集合元素之间分隔符
map keys terminated by ':' --集合元素kv之间分隔符;
1
2
3
4
5
6
7
8
9
10

传入数据即可

4 Hive SQL DDL建表高阶语法

4.1 Hive 内部表、外部表

内部表：

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

外部表：

• 外部表（External table）中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。
• 要创建一个外部表，需要使用EXTERNAL语法关键字。
• 删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。
• 实际场景中，外部表搭配location语法指定数据的路径，可以让数据更安全。

可以使用DESCRIBE FORMATTED tablename,来获取表的元数据描述信息，从中可以看出表的类型。

内外部表的差异：

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

如何选择内外部表：

• 当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。
• 当数据来之不易，防止误删，请使用外部表，因为即使删除表，文件也会被保留。

Location关键字的作用：

• 在创建外部表的时候，可以使用location指定存储位置路径，如果不指定会如何？

  • 如果不指定location，外部表的默认路径也是位于/user/hive/warehouse，由默认参数控制。

• 创建内部表的时候，是否可以使用location指定？

  • 内部表可以使用location指定位置的。

• 是否意味着Hive表的数据在HDFS上的位置不是一定要在/user/hive/warehouse下？

  • 不一定，Hive中表数据存储位置，不管内部表还是外部表，默认都是在/user/hive/warehouse，当然可以在建表的时候通过location关键字指定存储位置在HDFS的任意路径。

4.2 Hive Partitioned Tables 分区表

概述：

• 当Hive表对应的数据量大、文件个数多时，为了避免查询时全表扫描数据，Hive支持根据指定的字段对表进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。
• 比如把一整年的数据根据月份划分12个月（12个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。

创建语法：

--分区表建表语法
CREATE TABLE table_name (
  column1 data_type, 
  column2 data_type,
  ....) 
  PARTITIONED BY (partition1 data_type, partition2 data_type,…);
1
2
3
4
5
6

本质：

• 外表上看起来分区表好像没多大变化，只不过多了一个分区字段。实际上分区表在底层管理数据的方式发生了改变。这里直接去HDFS查看区别。
• 分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。
• 不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。
• 查询过滤的时候只需要根据分区值找到对应的文件夹，扫描本文件夹下本分区下的文件即可，避免全表数据扫描。
• 这种指定分区查询的方式叫做分区裁剪。

使用：

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

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

重点：

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

4.3.1 数据加载

静态加载：

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

load data [local] inpath 'filepath ' into table tablename partition(分区字段='分区值'...);
1

• 其中filepath为虚拟机linux的本地文件路径

动态加载：

所谓动态分区指的是分区的字段值是基于查询结果（参数位置）自动推断出来的。核心语法就是insert+select。

简单来说，将一个表的查询结果插入到另一个表中

启用hive动态分区，需要在hive会话中设置两个参数：

#是否开启动态分区功能
set hive.exec.dynamic.partition=true;
#指定动态分区模式，分为nonstick非严格模式和strict严格模式。
#strict严格模式要求至少有一个分区为静态分区。
set hive.exec.dynamic.partition.mode=nonstrict;
1
2
3
4
5

示例：

--执行动态分区插入
insert into table t_all_hero_part_dynamic partition(role) select tmp.*,tmp.role_main from t_all_hero tmp;
1
2

4.3.2 多重分区表

概述：

• 通过建表语句中关于分区的相关语法可以发现，Hive支持多个分区字段：
  PARTITIONED BY (partition1 data_type, partition2 data_type,….)。
• 多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。
• 从HDFS的角度来看就是文件夹下继续划分子文件夹。比如：把全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是3分区表。

示例：

--单分区表，按省份分区
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);
1
2
3
4
5
6

4.3 Hive Bucketed Tables 分桶表

概述：

• 分桶表也叫做桶表，叫法源自建表语法中bucket单词，是一种用于优化查询而设计的表类型。
• 分桶表对应的数据文件在底层会被分解为若干个部分，通俗来说就是被拆分成若干个独立的小文件。
• 在分桶时，要指定根据哪个字段将数据分为几桶（几个部分）。

分桶规则：

桶编号相同的数据会被分到同一个桶当中。

Bucket number = hash_function(bucketing_column)  mod   num_buckets
分桶编号        = 哈希方法（分桶字段）               取模    分桶个数
1
2

hash_function取决于分桶字段bucketing_column的类型：

• 如果是int类型，hash_function(int) == int;
• 如果是其他比如bigint,string或者复杂数据类型，hash_function比较棘手，将是从该类型派生的某个数字，比如hashcode值。

语法：

• CLUSTERED BY (col_name)表示根据哪个字段进行分；
• INTO N BUCKETS表示分为几桶（也就是几个部分）。
• 需要注意的是，分桶的字段必须是表中已经存在的字段。

--分桶表建表语句
CREATE [EXTERNAL] TABLE [db_name.]table_name
[(col_name data_type, ...)]
CLUSTERED BY (col_name)
INTO N BUCKETS;
1
2
3
4
5

好处：

• 基于分桶字段查询时，减少全表扫描
• JOIN时可以提高MR程序效率，减少笛卡尔积数量：根据join的字段对表进行分桶操作
• 分桶表数据进行高效抽样：当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

4.4 Hive Transactional Tables事务表

背景：

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

从Hive0.14版本开始，具有ACID语义的事务已添加到Hive中，以解决以下场景下遇到的问题：

• 流式传输数据

使用如Apache Flume、Apache Kafka之类的工具将数据流式传输到Hadoop集群中。虽然这些工具可以每秒数百行或更多行的速度写入数据，但是Hive只能每隔15分钟到一个小时添加一次分区。如果每分甚至每秒频繁添加分区会很快导致表中大量的分区,并将许多小文件留在目录中，这将给NameNode带来压力。

因此通常使用这些工具将数据流式传输到已有分区中，但这有可能会造成脏读（数据传输一半失败，回滚了）。

需要通过事务功能，允许用户获得一致的数据视图并避免过多的小文件产生。

• 尺寸变化缓慢

星型模式数据仓库中，维度表随时间缓慢变化。例如，零售商将开设新商店，需要将其添加到商店表中，或者现有商店可能会更改其平方英尺或某些其他跟踪的特征。这些更改导致需要插入单个记录或更新单条记录（取决于所选策略）。

• 数据重述

有时发现收集的数据不正确，需要更正。

局限性：

虽然Hive支持了具有ACID语义的事务，但是在使用起来，并没有像在MySQL中使用那样方便，有很多局限性。原因很简单，毕竟Hive的设计目标不是为了支持事务操作，而是支持分析操作，且最终基于HDFS的底层存储机制使得文件的增加删除修改操作需要动一些小心思。

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

如果不做任何配置修改，直接针对Hive中已有的表进行Update、Delete、Insert操作，可以发现，只有insert语句可以执行，Update和Delete操作会报错。Insert插入操作能够成功的原因在于，底层是直接把数据写在一个新的文件中的。

使用：

--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事务表
create table trans_student(
  id int,
  name String,
  age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

--3、针对事务表进行insert update delete操作
insert into trans_student values(1,"allen",18);
update trans_student
set age = 20
where id = 1;
delete from trans_student where id =1;
select *
from trans_student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4.5 Hive Views 视图

概述：

• Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据。
• 通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图。
• 创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败。
• 视图是用来简化操作的，不缓冲记录，也没有提高查询性能。

创建视图：

create view 视图名 as select ...from 表名/视图名 ...;
1

显示视图：

show tables;
show views;--hive v2.2.0之后支持
1
2

查看视图定义：

 show create table 视图名;
1

删除视图：

drop view v_usa_covid19_from_view;
1

更改视图属性：

alter view 视图名 set TBLPROPERTIES ();
1

更改视图定义：

alter view 视图名 as select ... from ....;
1

注意：视图无法进行数据插入（insert）

视图的好处：

• 将真实表中特定的列数据提供给用户，保护数据隐式
• 降低查询的复杂度，优化查询语句

4.6 Hive3.0新特性：Materialized Views 物化视图

概述：

• 物化视图（Materialized View）是一个包括查询结果的数据库对象，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果。在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。
• 使用物化视图的目的就是通过预计算，提高查询性能，当然需要占用一定的存储空间。
• Hive3.0开始尝试引入物化视图，并提供对于物化视图的查询自动重写机制（基于Apache Calcite实现）。
• Hive的物化视图还提供了物化视图存储选择机制，可以本地存储在Hive，也可以通过用户自定义storage handlers存储在其他系统（如Druid）。
• Hive引入物化视图的目的就是为了优化数据查询访问的效率,相当于从数据预处理的角度优化数据访问。
• Hive从3.0丢弃了index索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度。

物化视图、视图区别：

• 视图是虚拟的，逻辑存在的，只有定义没有存储数据。
• 物化视图是真实的，物理存在的，里面存储着预计算的数据。
• 物化视图能够缓存数据，在创建物化视图的时候就把数据缓存起来了，Hive把物化视图当成一张 “表”，将数据缓存。而视图只是创建一个虚表，只有表结构，没有数据，实际查询的时候再去改写SQL去访问实际的数据表。
• 视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能。

语法：

（1）物化视图创建后，select查询执行数据自动落地，“自动”也即在query的执行期间，任何用户对该物化视图是不可见的,执行完毕之后物化视图可用；

（2）默认情况下，创建好的物化视图可被用于查询优化器optimizer查询重写，在物化视图创建期间可以通过DISABLE REWRITE参数设置禁止使用。

--物化视图的创建语法
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 ...;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

（3）默认SerDe和storage format为hive.materializedview.serde、 hive.materializedview.fileformat；

（4）物化视图支持将数据存储在外部系统（如druid），如下述语法所示：

CREATE MATERIALIZED VIEW druid_wiki_mv
STORED AS 'org.apache.hadoop.hive.druid.DruidStorageHandler'
AS
SELECT __time, page, user, c_added, c_removed
FROM src;
1
2
3
4
5

（5）目前支持物化视图的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;
1
2
3
4
5
6

（6）当数据源变更（新数据插入inserted、数据修改modified），物化视图也需要更新以保持数据一致性，目前需要用户主动触发rebuild重构。

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;
1

基于物化视图的查询重写：

• 物化视图创建后即可用于相关查询的加速，即：用户提交查询query，若该query经过重写后可以命中已经存在的物化视图，则直接通过物化视图查询数据返回结果，以实现查询加速。
• 是否重写查询使用物化视图可以通过全局参数控制，默认为true： hive.materializedview.rewriting=true;
• 用户可选择性的控制指定的物化视图查询重写机制，语法如下：

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;
1

5 Hive SQL DDL其他语法

5.1 Database|Schema（数据库）

概述：

• 在Hive中，DATABASE的概念和RDBMS中类似，我们称之为数据库，DATABASE和SCHEMA是可互换的，都可以使用。
• 默认的数据库叫做default，存储数据位置位于/user/hive/warehouse下。
• 用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下.

创建数据库：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
1
2
3
4

• COMMENT：数据库的注释说明语句
• LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db
• WITH DBPROPERTIES：用于指定一些数据库的属性配置。
• 注意：如果需要使用location指定路径的时候，最好指向的是一个新创建的空文件夹。

显示数据库信息：

显示Hive中数据库的名称，注释（如果已设置）及其在文件系统上的位置等信息。

DESCRIBE DATABASE/SCHEMA [EXTENDED] db_name;
1

• EXTENDED关键字用于显示更多信息。可以将关键字describe简写成desc使用

切换数据库：

切换当前会话使用哪一个数据库进行操作

use db_name;
1

删除数据库：

默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
1

• 要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE。

更改数据库元数据：

更改与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;
1
2
3
4
5
6

5.2 Table（表）

概述：

• Hive中针对表的DDL操作可以说是DDL中的核心操作，包括建表、修改表、删除表、描述表元数据信息。
• 其中以建表语句为核心中的核心，详见Hive DDL建表语句。
• 可以说表的定义是否成功直接影响着数据能够成功映射，进而影响是否可以顺利的使用Hive开展数据分析。
• 由于Hive建表之后加载映射数据很快，实际中如果建表有问题，可以不用修改，直接删除重建。

显示Hive中表的元数据信息：

describe [EXTENDED][FORMATTED] table_name
1

• 如果指定了EXTENDED关键字，则它将以Thrift序列化形式显示表的所有元数据。
• 如果指定了FORMATTED关键字，则它将以表格格式显示元数据。

删除该表的元数据和数据:

DROP TABLE [IF EXISTS] table_name [PURGE];    -- (Note: PURGE available in Hive 0.14.0 and later)
1

• 如果已配置垃圾桶且未指定PURGE，则该表对应的数据实际上将移动到HDFS垃圾桶，而元数据完全丢失。
• 删除EXTERNAL表时，该表中的数据不会从文件系统中删除，只删除元数据。
• 如果指定了PURGE，则表数据跳过HDFS垃圾桶直接被删除。因此如果DROP错，则无法挽回该表数据。

从表中删除所有行：

TRUNCATE [TABLE] table_name;
1

• 可以简单理解为清空表的所有数据但是保留表的元数据结构。
• 如果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);
-- 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,...);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

5.3 Partition（分区）

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

增加分区：

ADD PARTITION会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询时将不会返回结果。

因此需要保证增加的分区位置路径下，数据已经存在，或者增加完分区之后导入分区数据。

--1、增加分区
ALTER TABLE table_name ADD PARTITION (dt='20170101') location
'/user/hadoop/warehouse/table_name/dt=20170101';
--一次添加一个分区
ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';
--一次添加多个分区
1
2
3
4
5
6
7

重命名分区：

--2、重命名分区
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
ALTER TABLE table_name PARTITION (dt='2008-08-09') RENAME TO PARTITION (dt='20080809');
1
2
3

删除分区：

删除表的分区。这将删除该分区的数据和元数据。

--3、删除分区
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; --直接删除数据 不进垃圾桶
1
2
3

修改分区：

--5、修改分区
--更改分区文件存储格式
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET FILEFORMAT file_format;
--更改分区位置
ALTER TABLE table_name PARTITION (dt='2008-08-09') SET LOCATION "new location";
1
2
3
4
5

MSCK partition

背景：

• Hive将每个表的分区列表信息存储在其metastore中。但是，如果将新分区直接添加到HDFS（例如通过使用hadoop fs -put命令）或从HDFS中直接删除分区文件夹，则除非用户ALTER TABLE table_name ADD/DROP PARTITION在每个新添加的分区上运行命令，否则metastore（也就是Hive）将不会意识到分区信息的这些更改。
• MSCK是metastore check的缩写，表示元数据检查操作，可用于元数据的修复。

概述：

• MSCK默认行为ADD PARTITIONS，使用此选项，它将把HDFS上存在但元存储中不存在的所有分区添加到metastore。
• DROP PARTITIONS选项将从已经从HDFS中删除的metastore中删除分区信息。
• SYNC PARTITIONS选项等效于调用ADD和DROP PARTITIONS。
• 如果存在大量未跟踪的分区，则可以批量运行MSCK REPAIR TABLE，以避免OOME（内存不足错误）。

--4、修复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];
1
2

6 Hive Show语法

概述：

• Show相关的语句提供了一种查询Hive metastore的方法。可以帮助用户查询相关信息。
• 比如我们最常使用的查询当前数据库下有哪些表 show tables.

相关操作：

--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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46