[大数据]学Hive数据仓库，看这篇文章就够了（上）

一、数据仓库基础

1、概念

数据库已经在信息技术领域有了广泛的应用，社会生活的各个领域几乎都有各种各样的数据库保存着各种数据。数据仓库作为数据库的一个分支，其概念的提出相对于数据库从时间上就晚得多。

2、特点

• 效率足够高

  数据仓库的分析数据一般按照时间周期分为日、周、月、季、年等类型数据。以日为周期的数据要求的效率最高，要求24小时内用户能看到数据分析结果。

• 数据质量

  数据仓库所提供的各种数据，要求是准确的数据，但由于数据仓库工作流程通常分为多个步骤，包括数据清洗、装载、查询、展现等，如果数据源有脏数据或者代码不严谨，可能导致数据失真，用户看到错误的信息就可能导致错误的决策，从而造成损失。

• 扩展性

  扩展性主要体现在数据建模的合理性上，数据仓库中扩展出一些中间层，使海量数据流有足够的缓冲，不至于因为数据量增大很多就运行不起来。

3、数据模型

数据仓库系统是一个信息提供平台，主要以星状模型和雪花模型进行数据组织，并为用户提供各种手段从数据中获取信息。

星状模型是搭建数据仓库的基本数据模型。在星状模型基础上发展起来的一种新型模型称为雪花模型。雪花模型应用在一些更复杂的场景中。

星状模型是一种由一个中心点向外辐射的模型。例如，对于商品推荐系统，面向的主题就是商品信息。与商品信息相关联的首先是用户信息，用户会购买商品，商品会形成订单，所以与订单信息是相关的。商品进行物流，所以肯定也会有物流信息，另外也会有促销信息。商品是由厂家生产的，所以肯定有厂家信息。这样就以商品信息为核心建立起来一个星状模型，这是一个面向商品信息主题的模型。

雪花模型是基于星状模型发展起来的。在商品信息这个主题的基础上进行扩展，例如以用户信息为核心，用户信息又是一个主题，与用户信息相关的会有用户的家庭信息、用户的地址信息、用户的教育背景信息，另外还有用户的银行信息等。当然，还会有其他信息与用户相关。这是以用户信息为主题来看的，也可以以其他主题来看，比如厂家信息，与厂家信息相关的信息很明显就有厂家的地址信息、员工信息等。所以这个模型就会越扩展越大，从而形成雪花模型。

星状模型和雪花模型是大型数据仓库中最基本的两个模型，用户可以根据自己的实际情况选择一种合适的模型来搭建数据仓库。

以商品推荐系统为例建立星状模型

雪花模型

二、Hive数据仓库

1、概念

Hive是用来开发SQL类型脚本，用于开发MapReduce操作的平台。Hive最初由Facebook开源，用于解决海量结构化日志的数据统计分析。

Hive是建立在Hadoop集群的HDFS上的数据仓库基础框架，其本质是将类SQL语句转换为MapReduce任务运行。可以通过类SQL语句快速实现简单的MapReduce统计计算，十分适合数据仓库的统计分析。

所有Hive处理的数据都存储在HDFS中，Hive在加载数据过程中不会对数据进行任何修改，只是将数据移动或复制到HDFS中Hive设定的目录下。所以Hive不支持对数据的改写和添加，所有数据都是在加载时确定的。

Hive定义了类SQL查询语言，称为HQL（Hive Query Language，Hive查询语言）。Hive是SQL语句解析引擎，Hive将用户的SQL语句通过解释器等工具转换为MapReduce任务，并提交到Hadoop集群上，Hadoop监控任务执行过程，然后返回任务执行结果给用户。Hive不是为联机事务处理（OLTP）而设计的，Hive并不提供实时的查询和基于行级的数据更新操作。

2、优点

• 简单、容易上手，简化了MapReduce应用程序的编写。
• 操作接口采用HQL语法，提供快速开发的能力。
• Hive常用于数据分析，对实时性要求不高。
• Hive常用于处理大数据，对于处理小数据没有优势。
• 用户可以根据需求来实现用户自定义函数。

3、缺点

• Hive的HQL语句表达能力有限

  • 无法表达迭代算法。
  • 不擅长数据挖掘，Hive常用于数据分析。

• Hive的效率比较低

  • Hive自动生成MapReduce任务，不够智能化。
  • Hive调优比较困难。

4、Hive体系结构及执行流程

Hive是为了简化用户编写MapReduce应用程序而生成的一种框架。

4.1、Hive体系结构

在Hive体系结构中，主要包括Hive用户接口、元数据存储（MetaStore）、驱动器（Driver）等。在Hive体系结构中，底层是操作系统，一般会使用Linux操作系统，如CentOS、Ubuntu。

4.2、Hadoop集群

在操作系统之上，就是Hadoop集群。在Hadoop集群中，有名称节点，用来管理整个集群的工作，也有若干个数据节点，用来存储数据。

在Hadoop集群中还有一个JobTracker，负责整个任务的调度。在Hive中执行一条HQL语句，这条语句实际上会被解析成MapReduce任务，并提交到集群上，得到的最终结果会被反馈给客户端，这个工作就是由JobTracker完成的。有了Hadoop集群之后，就可以在其上构建Hive数据仓库。

4.3、驱动器

由于在Hive中需要操作Hadoop集群，所以在Hive体系结构中，会有Hive驱动器。Hive驱动器包括解析器、编译器、优化器和执行器，负责HQL语句的执行过程。Hive执行的HQL语句首先提交给驱动器，然后调用编译器解释驱动，最终解释成MapReduce任务执行，最后将结果返回给客户端。

• 解析器（HQL Parser）：将HQL语句转换成抽象语法树（AST）；对AST进行语法分析，比如表是否存在、字段是否存在、HQL语义是否正确。

• 编译器（Compiler）：将AST编译生成逻辑执行计划。

• 优化器（Query Optimizer）：对逻辑执行计划进行优化。

• 执行器（Execution）：把逻辑执行计划转换成可以运行的MapReduce任务。

4.4、用户接口

用户接口主要有三个：CLI（Command Line Interface）、Thrift Server和HWI（Hive Web Interface），其中最常用的是CLI。

Client是Hive的客户端，Hive启动后，用户连接至HiveServer。客户端可以直接在命令行模式下进行操作。通过命令行，用户可以创建表、执行查询等操作。

Hive提供了Thrift Server，用来提供访问服务。通过这个服务器，可以使用不同的程序语言，如Java、Python，它们都可以连接到Thrift Server上，通过解析器、编译器、优化器和执行器，执行Hadoop集群的操作。

Hive提供了更直观的Web操作控制台，可以执行查询语句和其他命令，这样就可以不用登录到集群中的某台计算机上使用CLI来进行查询工作。

4.5、元数据存储

Hive中的表对应于Hadoop集群中的目录，所以表的存储位置等信息会存储在元数据中。这种信息与表中数据没有任何关系，它反应的是表本身的信息，这种信息称为元数据。Hive中的元数据包括表名、表所属的数据仓库、表的所有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。

由于Hive的元数据需要不断地更新、修改，而HDFS中的文件是读多改少的，显然不能将Hive的元数据存储在HDFS中。所以Hive将元数据存储在数据库中，这个数据库又称为MetaStore，也可以是MySQL或Oracle或者是Derby数据库。Hive默认采用Derby数据库

4.6、Hive表与元数据的映射关系

在Hive数据仓库中有两个表：一个student表和一个score表，student表对应于HDFS中的/hive/student目录，score表对应于HDFS中的/hive/score目录，student表有sid、sname、dname字段，score表有cname、sid、score字段。元数据被默认保存在Derby数据库中，有一个表用于保存表的信息，如表ID、表的名称、存储位置。student表ID为1，名称为student，存储位置为/hive/student；score表ID为2，名称为score，存储位置为/hive/score，这样保存了表的元数据。同样，把列的信息保存在Derby列信息表中，sid为1，对应表的ID也为1。

4.7、Hive执行流程

Hive通过给用户提供的一系列交互接口，接收用户的指令HQL，然后使用自己的驱动器，结合元数据存储，将这些指令翻译成MapReduce任务，提交到Hadoop集群中执行，最后将执行的结果返回并输出到用户接口。Hive执行流程的大致步骤如下。

• 用户提交查询等任务给驱动器。

• 编译器获得该用户的任务计划Plan。

• 编译器根据用户任务计划去MetaStore中获取需要的Hive元数据信息。

• 编译器得到元数据信息，对任务计划进行编译。先将HQL语句转换为抽象语法树，然后将抽象语法树转换成查询块，将查询块转化为逻辑查询计划，重写逻辑查询计划，将逻辑计划转化为物理计划（MapReduce），最后选择最佳的策略。

• 将最终的计划提交给驱动器。

• 驱动器将计划转交给执行器去执行，获取元数据信息，并提交给JobTracker或者SourceManager执行该任务，任务会直接读取HDFS中的文件进行相应的操作。

• 取得并返回执行结果。

5、Hive数据仓库和数据库比较

Hive数据仓库与传统意义上的数据库是有区别的。一般来说，基于传统方式，可以用Oracle数据库或MySQL数据库来搭建数据仓库，数据仓库中的数据保存在Oracle或MySQL数据库中。

Hive数据仓库和它们是不一样的，Hive数据仓库建立在Hadoop集群的HDFS之上。也就是说，Hive数据仓库中的数据是保存在HDFS上的。Hive数据仓库可以通过ETL的形式来抽取、转换和加载数据。Hive提供了类似SQL的查询语句HQL，可以用select * from表名;来查询到Hive数据仓库中的数据，这与关系型数据库的操作是一样的。

其实从结构上来看，Hive数据仓库和数据库除了拥有类似的查询语言，再无类似之处。下面将从多个方面来阐述Hive数据仓库和数据库的差异。

• 查询语言

  针对Hive特性设计了类SQL的查询语言HQL，因此，熟悉数据库SQL的开发者可以很方便地使用Hive进行开发统计分析。

• 数据存储系统

  Hive数据仓库使用Hadoop集群的HDFS来存储数据，而数据库则将数据保存在本地文件系统中。

• 数据更新

  数据库中的数据通常是需要经常进行修改更新的，因此可以使用INSERT添加数据、使用UPDATE修改更新数据。由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多写少的，因此，Hive数据仓库中不建议对数据进行改写，所有的数据都是在加载时确定好的。

• 数据规模

  由于Hive数据仓库建立在Hadoop集群上并可以利用MapReduce进行并行计算，因此支持很大规模的数据；相对而言，数据库支持的数据规模较小。

• 执行延迟

  Hive数据仓库中大多数查询的执行是通过Hadoop集群提供的MapReduce来实现的，而数据库通常有自己的执行引擎。

  Hive数据仓库在查询数据时，由于没有对数据中的某些key建立索引，需要扫描整个表中的所有数据，因此访问延迟较高。由于MapReduce本身具有较高的延迟，因此在利用MapReduce执行Hive查询时，也会有较高的延迟。当数据规模大到超过数据库的处理能力时，Hive数据仓库的并行计算显然能体现出优势。

• 可扩展性

  Hive数据仓库是建立在Hadoop集群之上的，所以Hive数据仓库的可扩展性和Hadoop集群的可扩展性是一致的。而数据库的扩展性非常有限。

• 应用场景

  Hive数据仓库是为海量数据做数据分析设计的，而数据库是为实时查询业务设计的。Hive数据仓库的实时性很差，实时性的差别导致Hive数据仓库的应用场景和数据库有很大不同。

  Hive数据仓库构建在基于静态批处理的Hadoop集群之上，由于Hadoop集群通常都有较高的延迟并且在作业提交和调度时需要大量的开销，因此，Hive数据仓库并不适合那些需要低延迟的应用。它最适合应用在基于大量不可变数据的批处理作业中，例如网络日志分析。

三、Hive环境搭建

1、MySQL安装及配置

Hive元数据默认存储在自带的Derby数据库中。Derby数据库只支持单用户模式，不能并发调用Hive。而MySQL数据库存储元数据支持多用户模式，可以并发调用Hive，因此还需要安装MySQL。

centos7在线安装MySQL

下载并安装MySQL官方的 Yum Repository

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm 
# 安装
yum -y install mysql57-community-release-el7-10.noarch.rpm

安装MySQL服务器

yum -y install mysql-community-server

安装完成后就会覆盖掉之前的mariadb。

MySQL数据库设置

# 启动mysql
systemctl start  mysqld.service
# 查看mysql运行状态
systemctl status mysqld.service

此时MySQL已经开始正常运行，不过要想进入MySQL还得先找出此时root用户的密码，通过如下命令可以在日志文件中找出密码

# 进入数据库
mysql -uroot -p
# 输入上面的初始密码

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
# 其中‘new password’替换成你要设置的密码，注意:密码设置必须要大小写字母数字和特殊符号,不然不能配置成功

# 也可以用下面方法修改密码策略和密码长度
# 修改密码策略
set global validate_password_policy=0;
# 修改密码长度
set global validate_password_length=6;

开启mysql的远程访问

grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;

flush privileges; 

2、Hive安装及配置

2.1、安装

# 解压
tar -zxvf apache-hive-2.1.0-bin.tar.gz -C /opt/module
# 修改名称
mv apache-hive-2.1.0-bin/ hive

2.2、配置

配置环境变量

sudo vi /etc/profile
export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin
source /etc/profile

修改配置文件hive-env.sh

cd conf/
mv hive-env.sh.template hive-env.sh
# 配置HADOOP_HOME路径
export HADOOP_HOME=/opt/module/hadoop
# 配置HIVE_CONF_DIR路径：
export HIVE_CONF_DIR=/opt/module/hive/conf

创建hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <!--连接地址-->
<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=utf-8&amp;useSSL=false</value>
  </property>
    <!--连接驱动-->
    <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
  </property>
    <!--mysql用户名-->
    <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
  </property>
    <!--mysql密码-->
    <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>123456</value>
  </property>

<!--设置显示表头字段名-->
 <property>
   <name>hive.cli.print.header</name>
   <value>true</value>
 </property> 
 <property>
   <name>hive.cli.print.current.db</name>
   <value>true</value>
 </property>
</configuration>

将mysql连接驱动上传至hive安装目录下lib目录下

启动hive

# 先启动mysql
systemctl start  mysqld.service
# 启动hdfs
start-hdfs.sh
# 启动yarn
start-yarn.sh
# 启动hive
hive

2.3、log日志配置

Hive的运行日志log默认存放在当前用户名下，可以更改它的存放位置。

• 将Hive的运行日志log存放到/opt/module/hive/logs目录下。

• 修改conf下hive-log4j.properties.template文件名为hive-log4j.properties：

  mv hivelog4j.properties.template hive-log4j.properties

• 在hive-log4j.properties文件中修改log存放位置：

  hive.log.dir=/opt/module/hive/logs

四、Hive基础

1、Hive数据类型

在创建Hive表时，须指定表字段的数据类型。

Hive中的数据类型分为基本数据类型和复杂数据类型。基本数据类型包括数值类型、布尔类型、字符串类型、时间戳类型等。复杂数据类型包括数组（Array）类型、映射（Map）类型和结构体（Struct）类型等。

基本数据类型

Hive基本数据类型中的String类型相当于数据库中的Varchar类型，该数据类型是一个可变长的字符串，理论上可以存储2GB的字符。

Hive中涉及的日期时间有两种类型。第一种是Date类型，Date类型的数据是通常所说的日期，通常用“年、月、日”来表示一个具体的日期。Date类型的格式为YYYY-MM-DD，YYYY表示年，MM表示月，DD表示日。Hive中的Date类型只包括年、月、日，不包括时、分、秒。

第二种是TimeStamp类型，它是与时区无关的类型，也就是说，各个时区、各个地方所表示的值是相等的，是一个从UNIX时代开始的时间偏移量。当前使用的时间戳偏移量都是10位整数，如果遇到13位的时间戳，则表示毫秒数。

如果TimeStamp为浮点数，则表示精确到纳秒，小数点后保留9位。在Hive中提供的TimeStamp可转换为日期，其格式为YYYY-MM-DD HH:MM:SS。

Hive有3种复杂数据类型，包括数组、映射和结构体。Array和Map与Java语言中的Array和Map类似，Struct与C语言中的Struct类似。

Array类型声明格式为Array<data_type>，表示相同数据类型的数据所构成的集合。Array元素的访问通过从0开始的下标实现，例如Array[1]访问的是第2个数组元素。

Map类型通过Map<key, value>来声明，key只能是基本数据类型，value可以是任意数据类型。Map元素的访问使用[]，例如Map[‘key1’]。

Struct类型封装一组有名字的字段，可以包含不同数据类型的元素，其类型可以是任意的基本数据类型。Struct类型更灵活，可以存储多种数据类型的数据。Struct元素的访问使用点运算符。

复杂数据类型

2、Hive数据存储

Hive建表后，表的元数据存储在关系型数据库（如MySQL）中，表的数据内容以文件的形式存储在Hadoop集群的HDFS中。

Hive数据是基于HDFS的，在Hive数据仓库中，用户可以非常自由地组织Hive中的表数据，可以用一个文本文件来存储表中的数据。创建一个表时，可以指明列与列的分隔符。

Hive数据存储结构包括表（Table）、外部表（External）、分区表（Partition）、桶表（Bucket）、视图（View）等。

Hive中的表和数据库中的Table在概念上是类似的，每个表在Hive中都有一个相应的目录存储数据。例如，一个表test在HDFS中的路径为/user/hive/warehouse/test，该路径是在配置文件hive-site.xml中由${hive.metastore.warehouse.dir}指定的数据仓库的目录，所有的表数据（不包括外部表）都保存在这个目录中。

分区表对应于数据库中的Partition列的密集索引，在Hive中，表中的一个分区表对应于表下的一个目录，所有的分区表的数据都存储在对应的目录中。

桶表对指定列计算Hash（哈希）值，根据Hash值切分数据，目的是为了并行计算，每个桶表对应一个切分数据后的文件。

外部表指向已经在HDFS中存在的数据，可以创建分区表。它和表在元数据的组织上是相同的，而实际数据的存储则有较大的差异。外部表创建表和加载数据同时完成，加载的数据并不会移动到数据仓库的目录中，只是与加载数据建立一个链接。当删除外部表时，仅删除该链接。

视图建立在已有表的基础上，是一种虚表，是一个逻辑概念。视图可以简化复杂的查询。

3、Hive存储格式

Hive在创建表时需要指明该表的存储格式，Hive支持的表存储格式主要有TextFile、SequenceFile、ORC、Parquet，其中TextFile为默认格式。

3.1、行式存储和列式存储

行式存储的特点

查询满足条件的一整行数据时，行式存储只需要找到其中一个值，其余的值都在相邻地方。列式存储则需要去每个聚集的字段找到对应的每个字段的值，所以行式存储查询的速度快。

列式存储的特点

列式存储中，每个字段的数据聚集存储，在查询只需要少数几个字段时，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以有针对性地设计更好的压缩算法。

TextFile和SequenceFile的存储格式是基于行式存储的；ORC和Parquet的存储格式是基于列式存储的。

3.2、TextFile格式

TextFile格式是Hive在创建表时默认的存储格式，不压缩数据，磁盘开销大，数据解析开销也较大。可结合Gzip、Bzip2压缩使用，但使用Gzip压缩方式，Hive不会对数据进行分割，所以无法对数据进行并行计算。

3.3、SequenceFile格式

SequenceFile格式是Hadoop用来存储二进制数形式的<key,value>键值对而设计的一种文件格式。在存储结构上，SequenceFile主要由一个头部（Header）后面跟多条记录（Record）组成。

Header主要包含存储压缩算法、用户自定义元数据等信息。此外，还包含一些同步标识，用于快速定位到记录的边界。每条记录以键值对的方式进行存储，用来表示它的字符数组可以一次解析为记录的长度、key的长度、key值和value值，并且value值的结构取决于该记录是否被压缩。SequenceFile格式支持数据压缩，有以下3种类型的压缩。

无压缩类型(None)

如果没有启用压缩（默认设置），那么每个记录就由它的记录长度（字节数）、key的长度、key值和value值组成。

记录压缩类型(Record)

记录压缩类型与无压缩类型基本相同，不同的是，值字节是用定义在头部的编码器来压缩的。

块压缩类型(Block)

块压缩类型一次压缩多个记录，因此它比记录压缩类型更紧凑，所以一般优先选择该类型。

3.4、ORC格式

ORC（Optimized Row Columnar）格式是Hive 0.11版本引入的表存储格式。

每个ORC文件由一个或多个Stripe组成，每个Stripe大小为250MB。每个Stripe由3部分组成，分别是Index Data、Row Data和Stripe Footer。

Index Data

默认为每隔1万行做一个索引。该索引只记录某行的各字段在Row Data中的偏移量（Offset）。

Row Data

其存储的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行编码，分成多个Stream来存储。

Stripe Footer

其存储的是各个Stream的类型、长度等信息。

每个ORC文件还有一个File Footer，这里面存储的是每个Stripe的行数、每列的数据类型信息等。每个ORC文件的尾部是一个PostScript，这里记录了整个文件的压缩类型及File Footer的长度信息等。在读取文件时，会Seek（寻找）到文件尾部读取PostScript，从里面解析得到File Footer长度，再读File Footer，从里面解析得到各个Stripe信息，再读各个Stripe，即从后往前读。

3.5、Parquet格式

Parquet格式文件是以二进制数形式存储的，所以是不可以直接读取的，文件中包含该文件的数据和元数据，因此Parquet格式文件是自解析的。

通常情况下，在存储Parquet数据时会按照Block（块）的大小设置行组的大小。一般情况下，由于每个Map任务处理数据的最小单位是一个Block，这样可以把每个行组由一个Map任务处理，增加了任务执行并行度。

一个Parquet格式文件可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验是否是一个Parquet文件，Footer Length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每个行组的元数据，每页的开始都会存储该页的元数据。

五、Hive数据定义

1、数据仓库

创建数据仓库

# 创建仓库语法格式
CREATE DATABASE [IF EXISTS] <database name> LOCALTION <dir>;
# 创建名为hive的数据库
create database hive;
# 创建一个数据仓库，并存放在HDFS的根目录下：
create database if not exists hive2 location '/hive2.db';

查询数据仓库

# 显示数据仓库
show databases;
# 过滤查询数据仓库
show databases like 'hive*';
# 查看数据仓库详细信息
desc database extended hive;

修改数据仓库

用户可以使用Alter命令修改数据仓库的Dbproperties键值对的属性值，来描述这个数据仓库的属性信息。数据仓库的其他元数据信息都是不可更改的，包括数据仓库名和数据仓库所在存储位置。

alter database hive set dbproperties('createtime'='20210921');

在Hive中查看修改结果：

desc database extended hive;

删除数据仓库

DROP DATABASE [IF EXISTS] database_name [CASCADE];

如果不知道删除的数据仓库是否存在，则使用IF EXISTS判断数据仓库是否存在。如果数据仓库不为空，其中已经有表存在，可以采用CASCADE关键字强制删除。

drop database hive;

2、表

在Hive中，表都在HDFS的相应目录中存储数据。目录的名称是在创建表时自动创建并以表名来命名的，表中的数据都保存在该目录中。而且，数据以文件的形式存储在HDFS中。

表的元数据会存储在数据库中，如Derby数据库或MySQL数据库。

# 创建表语法格式
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 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, ...) INTO num_buckets BUCKETS]
[SORTED BY (col_name [ASC|DESC], ...)]
[ROW FORMAT DELIMITED row_format]
[STORED AS file_format]
[LOCATION hdfs_path];

① CREATE TABLE，创建一个名字为table_name的表。如果该表已经存在，则抛出异常；可以用IF NOT EXISTS关键字选项来忽略异常。
② 使用EXTERNAL关键字可以创建一个外部表，在建表的同时指定实际表数据的存储路径（LOCATION）。创建Hive内部表时，会将数据移动到数据仓库指定的路径；若创建Hive外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除内部表时，内部表的元数据和数据会被一起删除；在删除外部表时，只删除外部表的元数据，但不删除数据。
③ (col_name data_type, ...)，创建表时要确定字段名及其数据类型，数据类型可以是基本数据类型，也可以是复杂数据类型。COMMENT为表和字段添加注释描述信息。
④ PARTITIONED BY，创建分区表｡
⑤ CLUSTERED BY，创建桶表｡
⑥ SORTED BY，排序｡
⑦ ROW FORMAT DELIMITED，用于指定表中数据行和列的分隔符及复杂数据类型数据的分隔符。这些分隔符必须与表数据中的分隔符完全一致。
	[Fields Terminated By Char]，用于指定字段分隔符。
	[Collection Items Terminated By Char]，用于指定复杂数据类型Map、Struct和Array的数据分隔符。
	[Map Keys Terminated By Char]，用于指定Map中的key与value的分隔符。
	[Lines Terminated By Char]，用于指定行分隔符。
⑧ STORED AS，指定表文件的存储格式，如TextFile格式、SequenceFile格式、ORC格式和Parquet格式等。如果文件数据是纯文本的，可以使用TextFile格式，这种格式是默认的表文件存储格式。如果数据需要压缩，可以使用SequenceFile格式等。
⑨ LOCATION，用于指定所创建表的数据在HDFS中的存储位置。

2.1、内部表

创建

# 创建表
create table test(id int,name string)
row format delimited fields terminated by '\t';
# 导入数据
load data local inpath '/home/test.txt' into table test;

创建复杂数据类型内部表

# 数据
{
    "name": "Steve",
    "friends": ["Bill" , "Linus"],  //Array
    "children": {                      //Map
        "Paul": 18,
        "Dennis": 21
    }
    "address": {                       //Struct
        "street": "xidan",
        "city": "beijing" 
    }
}
# 注意：Array、Map和Struct类型中的元素间关系都用同一个字符表示，这里用“_”。

create table complex(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':';

# 导入文本数据到表complex
load data local inpath '/home/complex.txt' into table complex;

2.2、外部表

带EXTERNAL关键字创建的表是外部表。外部表和内部表在元数据的组织上是相同的。

外部表加载的数据文件存储在LOCATION指定的目录下，该表会读取到该目录下的全部文件。创建外部表时，如果没有使用LOCATION指定数据文件存储位置，Hive将在HDFS中的/user/hive/warehouse数据仓库所在目录以外部表的表名创建一个目录，并将属于这个表的数据存放在这里。

删除外部表并不会删除LOCATION指定目录下的表数据文件，只是删除了外部表的元数据信息。

内部表和外部表的使用场景不同。例如，对每天收集到的网站数据，需要做大量的统计数据分析，所以在数据源上可以使用外部表进行存储，以方便数据的共享。在做统计分析时用到的中间表、结果表可以使用内部表，因为这些数据不需要共享，所以使用内部表更为合适。

# 创建外部表
create external table if not exists dept(
deptno int,
dname string,
buildingsno int)
row format delimited fields terminated by '\t';
# 查看表格式化数据
desc formatted dept;

2.3、内部表和外部表的转换

内部表和外部表之间可以互相转换，由关键字ALTER…SET设置。

# 语法格式
ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL'='TRUE|FALSE');
# 外部表转内部表
alter table dept set tblproperties('EXTERNAL'='FALSE');

2.4、分区表

Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。分区表实际上就是对应一个HDFS中的目录，该目录下是该分区所有的数据文件。

在Hive中，一个分区对应于表下的一个子目录，而所有的分区数据都存储在对应的子目录中。

Hive中分区字段不是表中的一个实际的字段，而是一个或者多个伪字段。也就是说，在分区表的数据文件中实际并不保存分区字段的信息与数据。

创建分区表的操作在实际的生产环境中是非常有用的，可以提高查询速度，当数据量非常大时，建立分区表是非常有必要的。

# 创建分区表
create table dept_p(
deptno int, dname string, buildingsno int)
partitioned by (month string)
row format delimited fields terminated by '\t';
# 加载数据到分区
load data local inpath '/home/dept.txt' into table dept_p partition(month='202109');

load data local inpath '/home/dept.txt' into table dept_p partition(month='202108');

load data local inpath '/home/dept.txt' into table dept_p partition(month='202107');

load data local inpath '/home/dept.txt' into table dept_p partition(month='202106');
# 查询分区表
select deptno,dname,buildingsno from dept_p where month='202109';

# 创建二级分区
create table dept_p2(
deptno int, dname string, buildingsno int) 
partitioned by (month string, day string) 
row format delimited fields terminated by '\t';
# 加载数据
load data local inpath '/home/dept.txt' into table dept_p2 partition(month='202109', day='18');
# 查询
select * from dept_p2 where month='202109' and day='18';

把数据直接上传到分区目录中，让分区表和数据产生关联的3种方式

• 上传数据后修复

# 上传数据
dfs -mkdir –p /user/hive/warehouse/hive.db/dept_p2/month=202109/day=12; 

dfs -put /opt/datas/dept.txt /user/hive/warehouse/hive.db/dept_p2/month=202109/day=12;

# 查询数据（查询不到刚上传的数据）：

select * from dept_p2 where month='202109' and day='12';

# 执行修复命令：

msck repair table dept_p2;

# 再次查询数据：

select * from dept_p2 where month='202109' and day='12';

• 上传数据后添加分区

# 上传数据：
dfs -mkdir –p /user/hive/warehouse/hive.db/dept_p2/month=202109/day=11;
dfs -put /opt/datas/dept.txt /user/hive/warehouse/hive.db/dept_p2/month=202109/day=11;
# 执行添加分区字段：
alter table dept_p2 add partition(month='202109',day='11');
# 查询数据：
select * from dept_p2 where month='202109' and day='11';

• 创建目录后加载数据到分区表

# 创建目录：
dfs -mkdir –p /user/hive/warehouse/hive.db/dept_p2/month=202109/day=10;
# 上传数据：
load data local inpath '/home/dept.txt' into table dept_p2 partition(month='202109',day='10');
# 查询数据：
select * from dept_p2 where month='202109' and day='10';

2.5、桶表

分区提供了一种隔离数据和优化查询的便利方式。不过，并非所有的数据都可形成合理的分区。

桶表是将数据分解成更容易管理的若干部分。桶表是通过对指定字段进行Hash计算来实现的，通过Hash值将一个字段名下的数据切分为若干部分，并使每个部分对应于该字段名下的一个存储文件。

桶表针对的是数据文件，分区表针对的是数据的存储路径。

分桶字段是表中的字段。Hive将表组织成桶表有以下目的：

• 抽样更高效。当处理大规模的数据集时，在开发、测试阶段将所有的数据全部处理一遍可能不太现实，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

• 更好的查询效率。桶表为表提供了额外的结构，Hive在处理某些查询时利用这个结构，能够有效地提高查询效率。

在建立桶表之前，需要设置hive.enforce.bucketing属性为True，使得Hive能识别到桶表。

# 桶表属性设置
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
# 创建桶表
create table test_b(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

2.6、修改表

# 重命名
ALTER TABLE table_name RENAME TO new_table_name;

alter table dept_p2 rename to dept_p3;

# 增加单个分区
alter table dept_p add partition(month='202105');
# 同时增加多个分区，多个分区之间使用空格
alter table dept_p add partition(month='202104') partition(month='202103');
# 删除单个分区
alter table dept_p drop partition(month='202103');
# 同时删除多个分区，多个分区之间使用逗号
alter table dept_p drop partition(month='202104'), partition(month='202105');

# 修改列的语法格式：
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
# 增加和替换列的语法格式：
ALTER TABLE table_name ADD|REPLACE COLUMNS(col_name data_type [COMMENT col_comment], …) 
关键字ADD表示新增一字段，字段位置在所有列后面（Partition列前）；REPLACE则表示替换表中所有字段。

2.7、删除表

表的删除使用关键字Drop。

当删除一个内部表时，Hive也会删除这个表中的数据，所以内部表不适合和其他工具共享数据。删除外部表并不会删除表数据，只是描述表的元数据信息会被删除。

drop table dept_p2;

删除表中数据Truncate

Truncate仅删除表中数据，保留表的元数据信息。Truncate只能删除内部表中的数据，不能删除外部表中的数据。外部表在删除后，HDFS中的数据还存在，不会被删除。因此要删除外部表数据，可以把外部表转成内部表或者删除HDFS文件。

truncate table test;

2.8、视图

视图在Hive中的用法和在SQL中的用法相同。视图是一种虚表，是一个逻辑概念，可以跨越多个表，操作视图和操作表是完全一样的。视图建立在已有表的基础上，视图并不存储数据。从视图中查询出来的数据，都来自视图所依赖的表，视图赖以建立的表称为基表。可以根据用户的需求创建视图，也可以将任何结果集数据保存为一个视图。可以在视图上执行所有DML操作。视图可以简化复杂的查询。

# 创建视图语法格式
CREATE VIEW[IF NOT EXISTS]view_name[(column_name[COMMENT column_comment],...)] [COMMENT table_comment];
# 创建视图
create view test_view as select * from test;
# 查询
select * from test_view; 
# 删除视图
drop view test_view;

六、HQL查询

大多数语法跟mysql一致，就不细说了

说一说排序

1、排序

1.1、Order By全局排序

Order By按照一个或多个字段排序。

Hive中的Order By和传统SQL中的Order By一样，对查询结果做全局排序，会新启动一个任务进行排序，把所有数据放到同一个Reduce中进行处理。不管数据有多少，不管文件有多少，都启用一个Reduce进行处理。

数据量大的情况下将会消耗很长时间去执行排序，而且可能不会出结果，因此必须使用关键字Limit指定输出条数。

ASC（Ascend）表示升序（默认），DESC（Descend）表示降序。Order By语句在Select语句的结尾。

select ename,gender,bday,area,score,deptno from emp order by deptno;

1.2、Sort By内部排序

Sort By是内部排序，会在每个Reduce中进行排序，单个Reduce出来的数据是有序的，但不保证全局有序。假设设置了3个Reduce，那么这3个Reduce就会生成3个文件，每个文件都会按Sort By设置的条件排序，但是当这3个文件数据合在一起，就不一定有序了。一般情况下，可以先进行Sort By内部排序，再进行全局排序，这样会提高排序效率。

使用Sort By可以先指定执行的Reduce个数（set mapreduce.job.reduces=<number>），对输出的数据再执行排序，即可以得到全部排序结果。

# 设置Reduce个数为3
set mapreduce.job.reduces=3;

select empno,ename,bday,area,score,deptno from emp sort by deptno desc;

1.3、Distribute By分区排序

Hive中的Distribute By是控制在Map端如何拆分数据给Reduce端的。按照指定的字段把数据划分到不同的Reduce输出文件中，默认采用Hash算法。对于Distribute By分区排序，一定要多分配Reduce进行处理，否则无法看到Distribute By分区排序的效果。Hive要求Distribute By语句写在Sort By语句之前。

Distribute By和Sort By的使用场景主要包括：

• Map输出的文件大小不均；

• Reduce输出的文件大小不均；

• 小文件过多；

• 文件超大。

set mapreduce.job.reduces=3;

select empno,ename,bday,area,score,deptno from emp distribute by deptno sort by empno desc;

1.4、Cluster By排序

Cluster By除具有Distribute By的功能外，还兼具Sort By的功能。当Distribute By和Sort By字段相同时，可以使用Cluster By方式排序。但是只能是升序排序，不能是降序排序。下面两种写法完全等价：

select empno,ename,bday,area,score,deptno from emp cluster by deptno;

# 或

select empno,ename,bday,area,score,deptno from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定的数值，可以是200号和300号部门分到一个分区里面。

2、抽样查询

当数据量特别大，对全部数据进行处理存在困难时，抽样查询就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性。Hive支持桶表抽样查询、数据块抽样查询和随机抽样查询。

2.1、桶表抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果，Hive可以通过对桶表进行抽样查询来满足这个需求。

桶表抽样语法格式：

TABLESAMPLE(BUCKET x OUT OF y ON col_name | RAND())

Tablesample语句允许用户编写用于数据抽样而不是整个表的查询，该语句出现在From语句中，可用于桶表中。

桶表编号从1开始，col_name表明抽取样本的字段，可以是非分区字段中的任意一字段，或者使用随机函数Rand()表明在整个行中抽取样本而不是单个字段。在col_name上桶表的行随机进入1到y个桶中，返回属于桶x的行。

y必须是桶表总桶数的倍数或者因子。Hive根据y的大小，决定抽样的比例。x表示从哪个桶开始抽取，如果需要抽取多个桶，以后的桶号为当前桶号加上y。需要注意的是，x的值必须小于或等于y的值，否则出现异常。

# 查询桶表test_b中的数据，抽取桶1数据
select id,name from test_b tablesample(bucket 1 out of 4 on id);
# 查询桶表test_b中的数据，抽取桶1和桶3数据
select id,name from test_b tablesample(bucket 1 out of 2 on id);
# 查询桶表test_b中的数据，随机抽取4个桶中的数据
select id,name from test_b tablesample(bucket 1 out of 4 on rand());

2.2、数据块抽样查询

Hive提供了一种按照抽样百分比进行抽样查询的方式，这是基于行数的、按照输入路径下的数据块百分比进行抽样的。该方式还允许Hive随机抽取数据总量的百分比或n字节的数据及n行数据。

• 语法格式

SELECT * FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS);

其中，ByteLengthLiteral的取值为 (Digit)+ (b | B | k | K| m | M| g | G)，表明数据的单位。

• 按数据块百分比抽样

按数据块百分比抽样允许抽取数据行数大小的至少n%作为输入，支持CombineHiveInput Format，而一些特殊的压缩格式是不能够被处理的，如果抽样失败，MapReduce作业的输入将是整个表。由于在HDFS块层级进行抽样，所以抽样粒度为块的大小，例如，如果块大小为256MB，即使输入的n%仅为100MB，也会得到256MB的数据。例如：

select ename, bday, score from emp tablesample(10 percent);

• 按数据大小抽样

按数据大小抽样方式的最小抽样单元是一个HDFS数据块。如果数据大小小于普通的块大小128MB，那么会返回所有的行。例如：

select ename, bday, score from emp tablesample(1b);

• 按数据行数抽样

这种方式可以根据行数来抽样，但要特别注意的是这里指定的行数，是在每个InputSplit中抽样的行数，也就是说，每个Map中都抽样n行。例如：

select ename, bday, score from emp tablesample(8 rows);

2.3、随机抽样查询

使用Rand()函数可以进行随机抽样查询，Limit关键字限制抽样返回的数据条数，其中Rand()函数前的Distribute By和Sort By关键字可以保证数据在Map和Reduce阶段是随机分布的。

例如，随机抽取emp表中的10条数据：

select empno,ename,bday from emp distribute by rand() sort by rand() limit 10;