数据仓库 Hive 从入门到小牛(一)

逃离我推掉我的手 2022-10-20 01:17 216阅读 0赞

### 目录 ###

*  一、数据仓库的介绍
 *   *  1.1 数据仓库的基本概念
     *  1.2 数据仓库的主要特征
     *  1.3 数据仓库与数据库区别
     *  1.4 数据仓库分层架构
     *  1.5 数据仓库之 ETL
 *  二、Hive 简介
 *   *  2.1 什么是 Hive?
     *  2.2 为什么使用 Hive?
     *  2.3 Hive 的体系结构
     *  2.4 Hive 与关系型数据库区别
 *  三、Hive 的安装
 *  四、Hive 的交互方式
 *  五、Hive 数据库和表操作
 *   *  5.1 数据库的操作
     *  5.2 数据表的操作
     *   *  5.2.1 创建数据库表语法
         *  5.2.2 Hive 建表时的数据类型
         *  5.2.3 存储格式
         *  5.2.4 表相关命令练习
         *  5.2.5 复杂数据类型操作
         *   *  5.2.5.1 Array 类型
             *  5.2.5.2 Map 类型
             *  5.2.5.3 Struct 类型
     *  5.3 Hive 中的表类型
     *  5.4 Hive 中的数据操作
     *   *  5.4.1 装载数据
         *  5.4.2 插入数据
         *  5.4.3 导出数据

# 一、数据仓库的介绍 #

## 1.1 数据仓库的基本概念 ##

数据仓库，英文名称为 Data Warehouse，可简写为 DW 或 DWH。数据仓库顾名思义，是一个很大的数据存储集合，出于企业的分析性报告和决策支持目的而创建，对多样的业务数据进行筛选与整合。它为企业提供一定的 BI (商业智能)能力，指导业务流程改进、监视时间、成本、质量以及控制。数据仓库的输入方是各种各样的数据源，最终的输出用于企业的数据分析、数据挖掘、数据报表等方向。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70]

## 1.2 数据仓库的主要特征 ##

数据仓库是 **面向主题的(Subject-Oriented)、集成的(Integrated)、稳定的(Non-Volatile)和时变的(Time-Variant)数据集合，用以支持管理决策。**

1.  主题性。不同于传统数据库对应于某一个或多个项目，数据仓库根据使用者实际需求，将不同数据源的数据在一个较高的抽象层次上做整合，所有数据都围绕某一主题来组织。这里的主题怎么来理解呢？比如对于城市，“天气湿度分析” 就是一个主题，对于淘宝，“用户点击行为分析” 就是一个主题。
2.  集成性。数据仓库中存储的数据是来源于多个数据源的集成，原始数据来自不同的数据源，存储方式各不相同。要整合成为最终的数据集合，需要从数据源经过一系列抽取、清洗、转换的过程。
3.  稳定性。数据仓库中保存的数据是一系列历史快照，不允许被修改。用户只能通过分析工具进行查询和分析。这里说明一点，数据仓库基本上是不许允许用户进行修改，删除操作的。大多数的场景是用来查询分析数据。
4.  时变性。数据仓库会定期接收新的集成数据，反应出最新的数据变化。这和稳定特点并不矛盾。

## 1.3 数据仓库与数据库区别 ##

**1.3.1 数据库。** 数据库是面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理，也被称为 **联机事务处理 OLTP(On-Line Transaction Processing)。**

**1.3.2 数据仓库。** 数据仓库一般针对某些主题的历史数据进行分析，支持管理决策，又被称为联机分析处理 OLAP((On-Line Analytical Processing)。首先要明白，数据仓库的出现，并不是要取代数据库。

**1.3.3 两者区别。**

1.  数据库是面向事务的设计，数据仓库是面向主题设计的。
2.  数据库一般存储业务数据，数据仓库存储的一般是历史数据。
3.  数据库设计是尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的 User 表，记录用户名、密码等简单数据即可，符合业务应用，但是不符合分析。数据仓库在设计是有意引入冗余，依照分析需求，分析维度、分析指标进行设计。
4.  数据库是为捕获数据而设计，数据仓库是为分析数据而设计。以银行业务为例。数据库是事务系统的数据平台，客户在银行做的每笔交易都会写入数据库，被记录下来，这里，可以简单地理解为用数据库记账。  
    数据仓库是分析系统的数据平台，它从事务系统获取数据，并做汇总、加工，为决策者提供决策的依据。比如，某银行某分行一个月发生多少交易，该分行当前存款余额是多少。如果存款又多，消费交易又多，那么该地区就有必要设立 ATM 了。 显然，银行的交易量是巨大的，通常以百万甚至千万次来计算。事务系统是实时的，这就要求时效性，客户存一笔钱需要几十秒是无法忍受的，这就要求数据库只能存储很短一段时间的数据。而分析系统是事后的，它要提供关注时间段内所有的有效数据。这些数据是海量的，汇总计算起来也要慢一些，但是，只要能够提供有效的分析数据就达到目的了。 数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它决不是所谓的“大型数据库”。

## 1.4 数据仓库分层架构 ##

按照数据流入流出的过程，数据仓库架构可分为三层——源数据、数据仓库、数据应用。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 1]  
数据仓库的数据来源于不同的源数据，并提供多样的数据应用，数据自下而上流入数据仓库后向上层开放应用，而数据仓库只是中间集成化数据管理的一个平台。

1.  源数据层(ODS)：操作性数据(Operational Data Store)，是作为数据库到数据仓库的一种过渡，ODS 的数据结构一般与数据来源保持一致，而且 ODS 的数据周期一般比较短。ODS 的数据为后一步的数据处理做准备。
2.  数据仓库层(DW)：数据仓库(Data Warehouse)，是数据的归宿，这里保持这所有的从 ODS 到来的数据，并长期报错，而且这些数据不会被修改，DW层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗(去除了杂质)后的数据。
3.  数据应用层(DA)：数据应用(Data Application)，为了特定的应用目的或应用范围，而从数据仓库中独立出来的一部分数据，也可称为部门数据或主题数据，该数据面向应用。如根据报表、专题分析需求而计算生成的数据。

## 1.5 数据仓库之 ETL ##

ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过 **抽取(extract)、转换(transform)、加载(load)** 至目的端的过程。ETL 是将业务系统的数据经过抽取、清洗、转换之后加载到数据仓库的过程，目的是将企业中分散、零乱、标准不统一的数据整合到一起。ETL 是数据仓库的流水线，也可以认为是数据仓库的血液，它维系着数据仓库中数据的新陈代谢，而数据仓库日常的管理和维护工作的大部分精力就是保持 ETL 的正常和稳定。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 2]

# 二、Hive 简介 #

## 2.1 什么是 Hive? ##

Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的 SQL 查询功能，并可以将 SQL 语句转换为 MapReduce 任务进行运行。Hive 定义了简单的类 SQL 查询语言，称为 HiveQL，简写为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 Mapper 和 Reducer 来处理内建的 Mapper 和 Reducer 无法完成的复杂的分析工作。在实际开发中， 80% 的操作都不会由 MapReduce 程序直接完成，而是由 Hive 来完成，所以 Hive 的使用频率非常高，而且也易于用户使用。只要用户对 SQL 熟练，即可掌握 Hive。Hive 提交任务的过程严格遵守 MapReduce 的执行流程：将用户提交的 SQL 语句通过 Hive 的解释器转换成 MapReduce 作业提交到 Hadoop 集群中，Hadoop 监控作业执行的详细过程，最后将执行结果返回给提交者。

## 2.2 为什么使用 Hive? ##

通过上面的介绍了解到，MapReduce 的实现逻辑太难不易理解，所以出现了 Hive。Hive 具有以下两个优点：

1.  开发周期短。Hive 可以缩短开发周期，但并不是说 Hive 可以提供更快地开发 Map 和 Reduce 任务的方法，而是定义了一种类似 SQL 的查询语言。Hive 使用 HQL 语句表述查询操作，并将其自动转换成一个或多个 MapReduce 作业，然后执行这些 MapReduce 的程序并将结果反馈给用户，省去了编译 ⇒ 提交这一过程，缩短了开发周期。
2.  减轻开发人员的学习成本与负担。之前，用户要想使用 Hadoop，首先必须要掌握软件开发的编程语言和 MapReduce 的编程思想。而现在，只要熟悉 SQL 的用户都可以通过 Hive 来操作 Hadoop。

## 2.3 Hive 的体系结构 ##

与 Hadoop 的 HDFS 和 MapReduce 计算框架不同，Hive 并不是分布式的，它独立于集群之外，可以看作是一个 Hadoop 的客户端，Hadoop 的体系结构如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 3]  
Hive 体系结构是由多个组件组成的，其中包含元数据(MetaStore)、驱动(包含编译器、优化器、执行器)、用户接口(包含客户端、UI、ThriftServer)，下面分别进行介绍：

1.  元数据(MetaStore)。元数据通常存储在关系型数据库(RDBMS)中，如 MySQL、Derby。元数据中包含了表名、列名、字段名、分区、表的类型(是否属于外部表)和数据存储的路径等信息。Hive 将 HDFS 上的结构化的数据通过元数据映射为一张张表，这样用户可以通过 HQL 对数据进行查询。Derby 数据库的缺点：在同一个目录下一次只能打开一个会话，使用 Derby存储方式时，Hive 会在当前目录生成一个 derby.log 文件和一个 metastore\_db 目录，metastore\_db 里面会存储具体的元数据信息，如果下次切换到一个另一个新目录访问 Hive，则会重新生成 derby.log 文件和 metastore\_db 目录，这样就没有办法使用之前的元数据信息了。推荐使用 MySQL 作为外置存储引擎，可以支持多用户同时访问以及元数据共享。
2.  驱动(Driver)。Hive 的驱动在接收到 HQL 语句后，通过创建会话来启动语句的执行，并监控执行的生命周期和进度。同时，它会将 HQL 在执行过程中产生的元数据信息进行存储。编译器、优化器、执行器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行。
3.  用户接口。客户端在日常开发中较为频繁，启动 Hive 终端会同时启动一个 Hive 副本，用户可以使用JDBC(或ODBC)客户端连接到 Hive Server。Hive 的数据存储在 HDFS 中，大部分的查询、计算会启动 MapReduce 任务，但查询不会生成 MapReduce 任务，如 `SELECT * FROM tb;` 如果在 SQL 语句后面再增加 where 过滤条件就会生成 MapReduce 任务了。

拓展：从 Hive2 开始，其实官方就不建议默认使用 MapReduce 引擎了，而是建议使用 Tez 引擎或者是 Spark 引擎，不过目前一直到最新的 3.x 版本中 MapReduce 还是默认的执行引擎。其实大数据计算引擎是有几个发展阶段的，首先是第一代大数据计算引擎：MapReduce，接着是第二代大数据计算引擎：Tez，Tez 的存在感比较低，它是源于 MapReduce，主要和 Hive 结合在一起使用，它的核心思想是将 Map 和 Reduce 两个操作进一步拆分，这些分解后的元操作可以灵活组合，产生新的操作，这些操作经过一些控制程序组装后，可以形成一个大的作业，这样可以提高计算效率，在实际工作中 Hive 使用的就是 Tez 引擎，替换 Hive 的执行引擎也很简单，只需要把 Tez 安装好(Tez 也是支持在 YARN 上执行的)，然后到 Hive 中配置一下就可以了，不管使用什么引擎，不会对我们使用 Hive 造成什么影响，也就说对上层的使用没有影响。接着是第三代大数据计算引擎: Spark，Spark 在当时属于一个划时代的产品，改变了之前基于磁盘的计算思路，而是采用内存计算，就是说 Spark 把数据读取过来以后，中间的计算结果是不会进磁盘的，一直到出来最终结果，才会写磁盘，这样就大大提高了计算效率，而 MapReduce 的中间结果是会写磁盘的，所以效率没有 Spark 高。Spark 的执行效率号称比 MapReduce 快100倍，当然这需要在一定数据规模下才会差这么多，如果我们就计算几十兆或者几百兆的文件，你去对比发现其实也不会差多少，注意：Spark 也是支持在 YARN 上执行的。其实目前还有第四代大数据计算引擎：Flink，Flink 是一个可以支持纯实时数据计算的计算引擎，在实时计算领域要优于 Saprk，Flink 和 Spark 其实是有很多相似之处，在某些方面他们两个属于互相参考，互相借鉴，互相成长。注意：Flink 也是支持在 YARN 上执行的。MapReduce、Tez、Spark、Flink 这些计算引擎都是支持在 YARN 上执行的，所以说 Hadoop2 中对架构的拆分是非常明智的。

## 2.4 Hive 与关系型数据库区别 ##

Hive 的查询语言 HQL 支持 SQL-92 标准，这与 SQL 非常类似，因此 Hive 会被误解当成关系型数据库。但是，Hive 和关系型数据库除了查询语句类似外，再无相似之处，两者的区别如下表所示：

<table> 
 <thead> 
  <tr> 
   <th align="left">对比项</th> 
   <th align="left">Hive</th> 
   <th align="left">RMDBS</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">查询语句</td> 
   <td align="left">HQL</td> 
   <td align="left">SQL</td> 
  </tr> 
  <tr> 
   <td align="left">数据存储位置</td> 
   <td align="left">Hadoop分布式文件系统（HDFS）</td> 
   <td align="left">本地文件系统</td> 
  </tr> 
  <tr> 
   <td align="left">数据格式</td> 
   <td align="left">用户定义</td> 
   <td align="left">系统决定</td> 
  </tr> 
  <tr> 
   <td align="left">数据更新</td> 
   <td align="left">不支持</td> 
   <td align="left">支持</td> 
  </tr> 
  <tr> 
   <td align="left">索引</td> 
   <td align="left">支持，但与RMDBS的索引并不相同</td> 
   <td align="left">支持</td> 
  </tr> 
  <tr> 
   <td align="left">执行引擎</td> 
   <td align="left">MapReduce</td> 
   <td align="left">Executor</td> 
  </tr> 
  <tr> 
   <td align="left">执行延迟</td> 
   <td align="left">高</td> 
   <td align="left">低</td> 
  </tr> 
  <tr> 
   <td align="left">可扩展性</td> 
   <td align="left">高</td> 
   <td align="left">低</td> 
  </tr> 
  <tr> 
   <td align="left">数据规模</td> 
   <td align="left">海量</td> 
   <td align="left">小</td> 
  </tr> 
 </tbody> 
</table>

# 三、Hive 的安装 #

由于截图太多，后续整理后，再补上！

# 四、Hive 的交互方式 #

**1、第一种交互方式：bin/hive。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 4]  
创建一个数据库，并查看：

hive (default)> create database mytest;
    OK
    Time taken: 0.461 seconds
    hive (default)> show databases;
    OK
    database_name
    default
    mytest
    Time taken: 0.081 seconds, Fetched: 2 row(s)
    hive (default)>

可以输入 `quit;` 退出 hive 的命令行，或者直接按 ctrl+c 也可以退出：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 5]  
**2、第二种交互方式：使用 sql 语句或者 sql 脚本进行交互，即不进入 hive 的客户端直接执行 hive 的 hql 语句。**

[root@bigdata04 apache-hive-3.1.2-bin]# bin/hive -e "create database mytest2;"

或者我们可以将我们的 hql 语句写成一个 sql 脚本然后执行，vi hive\_test.sql，脚本内容如下：

create database mytest3;
    use mytest3;
    create table stu(id int,name string);

通过 hive -f 来执行 sql 脚本：

[root@bigdata04 ~]# cd /data/soft/apache-hive-3.1.2-bin
    [root@bigdata04 apache-hive-3.1.2-bin]# bin/hive -f ~/hive_test.sql

**3、第三种交互方式：Beeline Client。**

这种方式需要先启动一个 hiveserver2 服务 ，因为 beeline 客户端需要通过这个服务连接 hive，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 6]  
启动 hiveserver2 服务之后，最下面会输出几行 Hive Session ID 的信息，一定要等到输出 4 行以后再使用 beeline 去连接，否则会提示连接拒绝。hiveserver2 默认会监听本机的 10000 端口，命令如下：

[root@bigdata04 apache-hive-3.1.2-bin]# bin/beeline -u jdbc:hive2://localhost:10000

效果如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 7]  
这样进入后，发现在添加数据的时候会报错，提示匿名户对 /tmp/hadoop-yarn 没有写权限，解决方法如下：

1.  给 hdfs 中的 /tmp/hadoop-yarn 设置 777 权限，让匿名用户具备权限  
    可以直接给 tmp 及下面的所有目录设置 777 权限，命令：hdfs dfs -chmod -R 777 /tmp
2.  在启动 beeline 的时候指定一个对这个目录有操作权限的用户，命令：  
    bin/beeline -u jdbc:hive2://localhost:10000 -n root。

**官方目前是推荐使用 beeline 命令的，可以把 hive 的 bin 目录配置到 path 环境变量中，后续直接使用 hive 或者 beeline 就可以了，如下：**

export JAVA_HOME=/data/soft/jdk1.8
    export HADOOP_HOME=/data/soft/hadoop-3.2.0
    export HIVE_HOME=/data/soft/apache-hive-3.1.2-bin
    export PATH=.:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HIVE_HOME/bin:$PATH

# 五、Hive 数据库和表操作 #

## 5.1 数据库的操作 ##

Hive 中的数据库本质上就是表的一个目录或命名空间，如果用户没有指定数据库，那么将会使用 Hive 默认的数据库 default。

**1、创建数据库。** CREATE DATABASE test; 或者 CREATE DATABASE IF NOT EXISTS test; 前者数据库存在会报错。

```text
    hive (default)> CREATE DATABASE test;
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database test already exists
    hive (default)> CREATE DATABASE IF NOT EXISTS test;
    OK
    Time taken: 0.013 seconds
    hive (default)> 
    ```

**2、查看数据库。** SHOW DATABASES;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 8]  
Hive 会为每个创建的数据库在 HDFS 上创建一个目录，该数据库中的表会以子目录的形式存储。表中的数据会以表目录下的文件的形式存储。如果用户使用 default 数据库，该数据库本身没有自己的目录。 在 hive-site.xml 中有一个参数 hive.metastore.warehouse.dir，对应的值为 /user/hive/warehouse，表示数据库所在的默认目录，如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 9]  
在浏览器中查看：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 10]  
从上图中可以看到，/user/hive/warehouse 下面只有一个目录，test.db 是 test 数据库的数据库目录，而该数据库中的表将以子目录的形式存放在 test.db 目录下。**说明：以 .db 结尾的为数据库目录。**

数据库的信息在 Metastore 中也有记录，在 hive 数据库中的 dbs 表里，如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 11]  
如果你不希望创建的数据库在这个目录下面，想要手工指定，那也是可以的，在创建数据库的时候通过 location 来指定 hdfs 目录的位置，命令如下：

hive (default)> create database mydb location '/user/hive/mydb2';
    OK
    Time taken: 0.412 seconds

到浏览器中确认一下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 12]  
**3、选择数据库。** USE mytest;  
![在这里插入图片描述][20210709014210463.png]  
**4、查看某个已存在的数据库的存储信息。** DESCRIBE/DESC DATABASE test;  
![在这里插入图片描述][20210709021601544.png]  
**5、删除数据库。** DROP DATABASE IF EXISTS hive2; 或者是 DROP DATABASE hive2; 加上 IF EXISTS，可以避免要删除的数据库不存在而引起的警告。

hive (hive2)> DROP DATABASE IF EXISTS hive2;
    默认情况下，Hive 不允许删除非空数据库，如果强行删除，会出现下面这样的错误信息。
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. 
    InvalidOperationException(message:Database hive2 is not empty. One or more tables exist.)
    
    使用CASCADE语句，则表示删除数据库时，会将其中的表一起删除，
    hive (hive2)> DROP DATABASE IF EXISTS hive2 CASCADE;
    OK
    Time taken: 0.288 seconds
    hive (hive2)> 
    当某个数据库被删除后，其对应的HDFS目录也将被一起删除。

## 5.2 数据表的操作 ##

### 5.2.1 创建数据库表语法 ###

CREATE [external] TABLE [IF NOT EXISTS] table_name (
    col_name data_type [COMMENT '字段描述信息']
    col_name data_type [COMMENT '字段描述信息'])
    [COMMENT '表的描述信息']
    [LOCATION '指定表的路径']
    [PARTITIONED BY (col_name data_type,...)]
    [CLUSTERED BY (col_name,col_name,...)]
    [SORTED BY (col_name [asc|desc],...) INTO num_buckets buckets]
    [ROW FORMAT row_format]
    [LOCATION location_path]

参数说明如下：

1.  CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。**\[\] 语法表示是可选项。**
2.  EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径(LOCATION)，Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3.  LIKE 允许用户复制现有的表结构，但是不复制数据。
4.  ROW FORMAT DELIMITED 可用来指定行分隔符。
5.  STORED AS SEQUENCEFILE|TEXTFILE|RCFILE 来指定该表数据的存储格式，hive 中，表的默认存储格式为 TextFile。
6.  CLUSTERED BY 对于每一个表(table)进行分桶(MapReuce 中的分区)，桶是更为细粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
7.  LOCATION: 指定表在 HDFS 上的存储位置。

### 5.2.2 Hive 建表时的数据类型 ###

Hive 支持关系型数据库中的大多数据类型，也支持一些独有的数据类型，并且 Hive 对于数据在文件中的编码方式也有很大的灵活性，下面分别进行介绍

**1、基本数据格式。** Hive 的基本数据类型和关系型数据库中的基本数据类型类似，分为整型、浮点型、布尔型等，如下表所示：

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">描述</th> 
   <th align="left">示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">tinyint</td> 
   <td align="left">1字节的有符号整数，-128~127之间的整数</td> 
   <td align="left">1Y</td> 
  </tr> 
  <tr> 
   <td align="left">smallint</td> 
   <td align="left">2字节的有符号整数，-32768~32767之间的整数</td> 
   <td align="left">1S</td> 
  </tr> 
  <tr> 
   <td align="left">int</td> 
   <td align="left">4个字节带符号整数，-231~231-1之间的整数</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">bigint</td> 
   <td align="left">8字节带符号整数，-263~263-1之间的整数</td> 
   <td align="left">1L</td> 
  </tr> 
  <tr> 
   <td align="left">float</td> 
   <td align="left">4字节单精度浮点数，有效位数6~7位</td> 
   <td align="left">1.5</td> 
  </tr> 
  <tr> 
   <td align="left">double</td> 
   <td align="left">8字节双精度浮点数，有效位数15~16位</td> 
   <td align="left">1.5</td> 
  </tr> 
  <tr> 
   <td align="left">deicimal</td> 
   <td align="left">任意精度的带符号小数</td> 
   <td align="left">1.5</td> 
  </tr> 
  <tr> 
   <td align="left">boolean</td> 
   <td align="left">true/false</td> 
   <td align="left">true</td> 
  </tr> 
  <tr> 
   <td align="left">string</td> 
   <td align="left">变长字符串</td> 
   <td align="left">‘hadoop’</td> 
  </tr> 
  <tr> 
   <td align="left">varchar</td> 
   <td align="left">变长字符串</td> 
   <td align="left">‘hadoop’</td> 
  </tr> 
  <tr> 
   <td align="left">char</td> 
   <td align="left">固定长度字符串</td> 
   <td align="left">‘hadoop’</td> 
  </tr> 
  <tr> 
   <td align="left">timestamp</td> 
   <td align="left">时间戳，定义1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数</td> 
   <td align="left">123345635765</td> 
  </tr> 
  <tr> 
   <td align="left">date</td> 
   <td align="left">日期 年月日</td> 
   <td align="left">‘2021-07-09’</td> 
  </tr> 
  <tr> 
   <td align="left">Time</td> 
   <td align="left">时分秒</td> 
   <td align="left">‘15:05:30’</td> 
  </tr> 
  <tr> 
   <td align="left">DateTime</td> 
   <td align="left">年月日 时分秒</td> 
   <td align="left"></td> 
  </tr> 
 </tbody> 
</table>

由于 Hive 是由 Java 编写，所以 Hive 的基本数据类型都是对 Java 中接口的实现，这些基本的数据类型与 Java 的基本数据类型是一一对应的。如 smallint 类型对应 Java 中的 short 类型，float 对应 Java 中的 float 类型。

**2、复杂数据格式。** Hive 除了以上支持的基本数据类型，还支持以下三种集合数据类型，分别为 array、map 和 struct，如下表所示：

<table> 
 <thead> 
  <tr> 
   <th align="left"><strong>类型</strong></th> 
   <th align="left"><strong>描述</strong></th> 
   <th align="left"><strong>示例</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">array</td> 
   <td align="left">数组，有序的同类型集合</td> 
   <td align="left">array(1,2)</td> 
  </tr> 
  <tr> 
   <td align="left">map</td> 
   <td align="left">一组键值对元组的集合，形式为key-value，即键—值，可以通过键访问值。key必须为原始类型，value可以为任意类型</td> 
   <td align="left">map(‘a’,1,‘b’,2)</td> 
  </tr> 
  <tr> 
   <td align="left">struct</td> 
   <td align="left">结构体，类型可以不同</td> 
   <td align="left">struct(‘hadoop’,1,1.0)</td> 
  </tr> 
 </tbody> 
</table>

### 5.2.3 存储格式 ###

Hive 会为每个创建的数据库在 HDFS 上创建一个目录，该数据库的表会以子目录形式存储，表中的数据会以表目录下的文件形式存储。对于 default 数据库，默认的缺省数据库没有自己的目录，default 数据库下的表默认存放在 **/user/hive/warehouse** 目录下。

1.  textfile。textfile 为默认格式，存储方式为行存储。数据不做压缩，磁盘开销大，数据解析开销大。
2.  SequenceFile。SequenceFile 是 Hadoop API 提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。SequenceFile 支持三种压缩选择：NONE，RECORD，BLOCK。Record 压缩率低，一般建议使用 BLOCK 压缩。
3.  RCFile。一种行列存储相结合的存储方式。首先，将其数据按行分块，保证同一条记录在一个块上，避免读一条记录需要读取多个块。其次，块上的数据按照列式存储，有利于数据压缩和快速地进行列存取，即先按水平划分再按垂直划分，如下图所示：  
    ![在这里插入图片描述][20210709151553994.png]
4.  ORCFile。数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。hive 给出的新格式，属于 RCFILE 的升级版，性能有大幅度提升，而且数据可以压缩存储。
5.  Parquet。Parquet 也是一种行式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。

**这里做一下简单了解，后续在进行深入讲解。接下来看一下数据的格式：**

当数据存储在文本文件中，必须按照一定格式区别行和列，并且在 Hive 中指明这些区分符。Hive 默认使用了几个平时很少出现的字符，这些字符一般不会作为内容出现在记录中。Hive 默认的行和列分隔符如下表所示：

<table> 
 <thead> 
  <tr> 
   <th align="left">分隔符</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">\n</td> 
   <td align="left">对于文本文件来说，每行是一条记录，所以 \n 用来分割记录</td> 
  </tr> 
  <tr> 
   <td align="left">^A(Ctrl+A)</td> 
   <td align="left">分割字段，也可以用 \001 来表示</td> 
  </tr> 
  <tr> 
   <td align="left">^B(Ctrl+B)</td> 
   <td align="left">用于分割 Arrary 或者 Struct 中的元素，或者用于 map 中键值之间的分割，也可以用 \002 分割</td> 
  </tr> 
  <tr> 
   <td align="left">^C</td> 
   <td align="left">用于 map 中键和值自己分割，也可以用 \003 表示</td> 
  </tr> 
 </tbody> 
</table>

### 5.2.4 表相关命令练习 ###

【示例1】创建一个名为student的数据表，包含的字段有id学号，name学生姓名，age年龄。示例代码如下：

CREATE TABLE IF NOT EXISTS mytest.student (
    id string COMMENT 'student id',
    name string COMMENT 'student name',
    age int COMMENT 'student age')
    COMMENT 'Student Information'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
    STORED AS TEXTFILE
    LOCATION '/user/hive/warehouse/mytest.db/student';

下面详细说明建表命令。

1.  当在建表语句中加上 IF NOT EXISTS 选项时，如果该表存在时，Hive 会忽略掉后面的命令，不会创建此表。如果要创建的数据表所属的数据库不是当前数据库的话，必须在表名前加上所属数据库的名字，如 `mytest.student`。或者在建表命令之前，使用 USE 命令切换该表所属的数据库，如执行命令 `USE mytest` 后，可直接创建表 student，不用加 `mytest.`。
2.  COMMENT 命令为表或表的字段的注释，在需要添加注释的字段后可以使用 COMMENT 命令；在定义好全部字段后使用 COMMENT 命令即为表的注释。合理的添加注释，在实际开发中十分必要。
3.  使用 ROW FORMAT DELIMITED 子句来指定行列的数据格式和文件的存储格式，如果省略此子句则表示使用 Hive 提供的默认值。如本例创建 student 表时，`ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '` 表示从文件中将数据导入数据表的时候，文件中的数据每行各个字段之间的分隔符为空格。
4.  LOCATION 子句可以指定该表在 HDFS 文件系统中的存储位置，如果省略此子句，则会存储在 Hive 默认的数据仓库目录。
5.  如果注释是中文的话，会乱码，需要执行下列这些命令。(在创建表前执行)。
    
        --登录到mysql数据库中
        use hive;
        alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
        alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
        -- 如果你的表创建了分区的话就要再执行两条命令:
        alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
        alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

【示例2】AS查询建表法。建表的时候使用 'AS SELECT 另外一张表’语句，即可获得该表的表结构和表数据，如创建一张表student1，此表的表结构和表数据与student表一致，那么在创建student1时，可以使用如下语句：

--为了方便，我这里就全部使用小写了
    create table if not exists mytest.student1 as select * from mytest.student;

【示例3】student2表只复制student表的部分字段，如id列与name列，并且要求只导入id>100的数据，创建语句如下：

create table if not exists mytest.student2 as select id,name from mytest.student where id>100;

【示例4】LIKE建表法。如果想要创建一张与原有表相同表结构的数据表的话，可以通过复制的语句，如要创建数据表student3，表结构与表student相同，命令如下：

create table if not exists mytest.student3 like mytest.student;

总结 Hive 中的三种建表方式：

1.  创建一张新表时，直接创建；
2.  创建一张表时，复制其他表的表结构和数据，借助 AS 命令；
3.  创建一张表时，复制其他表的表结构，借助 LIKE 命令来获取表结构而不是数据。
4.  **特别注意：“AS SELECT 另外一张表” 语句复制表结构和表中数据，LIKE 语句只是复制表结构，而不复制表中数据。**

【示例5】查看当前数据库中有哪些数据表。show tables;  
【示例6】查看指定数据库中有哪些数据表。show tables in mytest; mytest 数据库名。  
【示例7】查看表结构。desc student; student 表名。  
【示例8】查看表注释。desc formatted student;desc extended student;student 为表名。  
【示例9】查看表的创建信息。show create table student;

**注意：表中的数据是存储在 hdfs 中的，但是表的名称、字段信息是存储在 Metastore 中的。**

到 Metastore 中看一下，先看 tbls 表，这个里面中存储的都是在 hive 中创建的表，其中有一个 DB\_ID 为1，可以到 dbs 表中看到默认 default 数据库的 id 就是 1。TBL\_NAME 是这个表的名称。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 13]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 14]  
在表 COLUMNS\_V2 中存储的是 Hive 表的字段信息(包含字段注释、字段名称、字段类型、字段顺序)其中的 CD\_ID 和 tbls 中的 TBL\_ID 相等。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 15]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 16]  
【示例10】对表名进行重命名。ALTER TABLE 旧的表名 RENAME TO 新的表名;将student改名为student\_info，命令如下：

alter table student rename to student_info;

hdfs 中对应的目录名称也会同步发生变化。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 17]  
【示例11】修改列信息。通过ALTER TABLE … CHANGE COLUMN语句可以对表中的某个字段进行重命名，也可修改其数据类型、注释、在表中的位置，如下：

alter table student1 change column name sname string comment 'stu name' after id;

【示例12】增加列。通过ALTER TABLE … ADD COLUMNS命令为表增加列，如下：

alter table student1 add columns(address string);

【示例13】删除或者替换列。通过ALTER TABLE … REPLACE COLUMNS命令为表删除或者替换列，如下：

# 将表中原来的列替换为 address和address2
    alter table student1 replace columns(address string,address2 string);

【示例14】删除表。DROP TABLE命令可以删除数据表，如：

drop table student1;
    drop table if exists student1;
    --只能清空管理表，也就是内部表
    truncate table score4;

### 5.2.5 复杂数据类型操作 ###

#### 5.2.5.1 Array 类型 ####

Array 是数组类型，Array 中存放相同类型的数据，**源数据：**  
说明：id 与 name，name 与 hobby 之间空格分隔，hobby 中元素之间逗号分隔：

id name hobby
    1 jerry sing,dance,drink
    2 amo sing,read,playing
    3 paul coding,eat

**建表语句：**

create table stu2(
    id int,
    name string,
    hobby array<string>) 
    row format delimited fields terminated by ' '
    collection items terminated by ',';

**导入数据：**

load data local inpath '/data/hiveData/stu2.txt' into table stu2;

**常用查询：**

-- 查询所有数据
    select * from stu2;
    -- 查询hobby数组中第一个元素
    select id,name,hobby[0] hobby from stu2;
    -- 查询hobby数组中元素的个数
    select id,name,size(hobby) hobby from stu2;
    -- 查询hobby数组中包含sing的信息
    select * from stu2 where array_contains(hobby,'sing');

#### 5.2.5.2 Map 类型 ####

Map 存储的是键值对，每一个键值对属于 Map 集合的一个 item，**源数据：**  
说明：id 与 name，name 与 hobby 之间制表符分隔，成绩中元素之间逗号分隔，键值对使用冒号分隔key与value

1	paul	chinese:80,math:90,english:100
    2	lisa	chinese:89,math:88,english:99
    3	jerry	chinese:60,math:80,english:77
    3	jason	chinese:70,math:66,english:88

**建表语句：**

create table stu3(
    id int,
    name string,
    grade map<string,int>)
    row format delimited fields terminated by '\t'
    collection items terminated by ',' --指定了map中元素之间的分隔符 一个键值对就是一个元素
    --指定了key和value之间的分隔符
    map keys terminated by ':';

**导入数据：**

load data local inpath '/data/hiveData/stuGrade.txt' into table stu3;

**常用查询：**

-- 查询所有数据
    select * from stu3;
    -- 查询所有学生的语文和数学成绩
    select id,name,grade["chinese"] c_grade,grade["math"] m_grade from stu3;

#### 5.2.5.3 Struct 类型 ####

结构体，类型可以不同，**源数据：**

1	zhangsan	bj,sh
    2	lisi	gz,sz
    3	wangwu	xm,cq
    4	zhaoliu	cd,lz

**建表语句：**

create table stu4(
    id int,
    name string,
    address struct<home_addr:string,work_addr:string>)
    row format delimited fields terminated by '\t'
    collection items terminated by ',';

**导入数据：**

load data local inpath '/data/hiveData/stu3.txt' into table stu4;

**常用查询：**

-- 查询所有数据
    select * from stu4;
    --查询所有学生的家庭住址
    select id,name,address.home_addr from stu4;

总结：

1.  map 中可以随意增加 k-v 对的个数，struct 中的 k-v 个数是固定的。简而言之，就是说装载源数据的时候，struct 定义的时候写了几个键值对，你源数据中的数据要和其一一对应。
2.  map 在建表语句中需要指定 k-v 的类型，struct 在建表语句中需要指定好所有的属性名称和类型。
3.  map 中通过 \[\] 取值，struct 中通过 `.` 取值，类似 java 中的对象属性引用
4.  map 的源数据中需要带有 k-v，struct 的源数据中只需要有 v 即可。

总体而言还是 map 比较灵活，但是会额外占用磁盘空间，因为它比 struct 多存储了数据的 key，struct 只需要存储 value，比较节省空间，但是灵活性有限，后期无法动态增加 k-v。

## 5.3 Hive 中的表类型 ##

Hive 中有如下几种数据表的分类：  
(1) 管理表。在 Hive 中，建表时，如果没有特别指明的话，创建的都是 Hive 中的管理表(MANAGED TABLE)，也叫内部表。当创建表时，Hive 会默认将数据存储在 /user/hive/warehouse 下，也可以通过 LOCATION 指定；当删除表时，会删除表数据以及元数据。我们前面创建的表都属于管理表。  
(2) 外部表。如果当一份数据需要被多种工具(如Pig、Hive)分析时，可以创建一个外部表(EXTERNAL TABLE)指向这份数据。创建语句需要加上 EXTERNAL 关键字，如 `CREATE EXTERNAL TABLE 表名`。当删除外部表时，只会删除元数据而不会删除表数据。**源数据：**

student.txt 文件内容如下：
    01 赵雷 1990-01-01 男 
    02 钱电 1990-12-21 男 
    03 孙风 1990-05-20 男 
    04 李云 1990-08-06 男 
    05 周梅 1991-12-01 女 
    06 吴兰 1992-03-01 女 
    07 郑竹 1989-07-01 女 
    08 王菊 1990-01-20 女 
    
    teacher.txt 文件内容如下：
    01 张三
    02 李四
    03 王五

**建表语句：**

--创建学生外部表
    create external table student(
    sid string,
    sname string,
    sbirth date,
    ssex char(2))
    row format delimited fields terminated by ' ';
    
    --创建老师外部表
    create external table teacher(
    tid string,
    tname string)
    row format delimited fields terminated by ' ';
    
    --加载数据
    load data local inpath '/data/hiveData/student.txt' into table student;
    --覆盖
    load data local inpath '/data/hiveData/student.txt' overwrite into table student;
    --加载数据
    load data local inpath '/data/hiveData/teacher.txt' into table teacher;

来看一下 metastore 中的 tbls 表，这里看到 external\_table 的类型是外部表。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 18]  
注意：实际上内外部表是可以互相转化的，需要我们做一下简单的设置即可。

alter table student2 set TBLPROPERTIES ('EXTERNAL'='FALSE'); --外部表转内部表
    alter table student2 set TBLPROPERTIES ('EXTERNAL'='TRUE'); -- 内部表转外部表

在实际工作中，我们在 hive 中创建的表 95% 以上的都是外部表，先通过 flume 采集数据，把数据上传到 hdfs 中，然后在 hive 中创建外部表和 hdfs 上的数据绑定关系，就可以使用 sql 查询数据了，所以连 load 数据那一步都可以省略了，因为是先有数据，才创建的表。

(3) 分区表。分区表实际就是对应 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。分区可以理解为分类，通过分类把不同类型的数据放到不同的目录下。分类的标准就是分区字段，可以一个，也可以多个。分区表的意义在于优化查询，查询时尽量利用分区字段，如果不使用分区字段，就会全部扫描。**源数据：**

score.txt 数据如下：
    01	01	80
    01	02	90
    01	03	99
    02	01	70
    02	02	60
    02	03	80
    03	01	80
    03	02	80
    03	03	80
    04	01	50
    04	02	30
    04	03	20
    05	01	76
    05	02	87
    06	01	31
    06	03	34
    07	02	89
    07	03	98

**创建分区表语法：**

create table score(
    sid string,
    cid string,
    sscore int)
    --在定义分区的字段的时候不能和定义表的字段重合，否则会发生错误。
    partitioned by(month string) --指定按照月份进行分区
    row format delimited fields terminated by '\t';
    
    --创建一个表带多个分区
    create table score2(
    sid string,
    cid string,
    sscore int)
    partitioned by(year string,month string)
    row format delimited fields terminated by '\t';

**加载数据：**

load data local inpath '/data/hiveData/score.txt' into table score partition(month='202107');
    --
    load data local inpath '/data/hiveData/score.txt' into table score2 partition(year='2021',month='07');
    load data local inpath '/data/hiveData/score.txt' into table score2 partition(year='2021',month='06');
    load data local inpath '/data/hiveData/score.txt' into table score2 partition(year='2020',month='07');
    load data local inpath '/data/hiveData/score.txt' into table score2 partition(year='2020',month='06');
    --手动添加分区
    alter table score add partition(month='202106');
    --同时添加多个分区
    alter table score add partition(month='202104') partition(month='202105');
    --向手动添加的分区中装载数据
    load data local inpath '/data/hiveData/score.txt' into table score partition(month='202106');
    --查看score表中有哪些分区
    show partitions score;
    --删除分区
    alter table score drop partition(month='202106');
    --查询
    select * from score2; --全表扫描，没有用到分区的特性
    select * from score2 where year='2021'; --用到了一个分区字段进行过滤
    select * from score2 where year='2021' and month='06'; --用到了两个分区字段进行过滤
    -- 以上操作的都是内部分区表，外部分区表是工作中最常用的表，其它的操作和前面操作内部分区表是—样的。

如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 19]  
(4) 桶表。分桶就是将数据划分到不同的文件，其实就是 MapReduce 的分区。将数据按照指定的字段分到多个桶中去，说白了就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去。

**创建桶表语法：**

--创建桶表
    create table course(
    cid string,
    cname string,
    tid string)
    clustered by(cid) into 3 buckets
    row format delimited fields terminated by '\t';

这个时候往桶中加载数据的时候，就不能使用 load data 的方式了，而是需要使用其它表中的数据，那么给桶表加载数据的写法就有新的变化了。类似这样的写法：

insert into table ... select ... from ...;

**源数据：**

01	语文	02
    02	数学	01
    03	英语	03

**向桶表中装载数据：**

--开启hive的桶表功能
    set hive.enforce.bucketing=true;
    --设置reduce的个数
    set mapreduce.job.reduces=3;  
    
    --创建普通表
    create table course_common(
    cid string,
    c_name string,
    tid string)
    row format delimited fields terminated by '\t';
    --向普通表中装载数据
    load data local inpath '/data/hiveData/course.txt' into table course_common;
    --通过insert给桶表中加载数据
    insert into table course select * from course_common cluster by(cid);

桶表的主要作用：

1.  数据抽样。假如我们使用的是一个大规模的数据集，我们只想去抽取部分数据进行查看，使用 bucket 表可以变得更加的高效
2.  提高某些查询效率。

## 5.4 Hive 中的数据操作 ##

在创建了数据表之后，接下来就需要向表中装载数据、插入数据或是导出数据等。

### 5.4.1 装载数据 ###

需要注意的是，在 Hive 中，没有行级别的数据插入、数据更新和删除操作。向 Hive 表一次性地装载大量数据的命令为 LOAD DATA，语法如下：

LOAD DATA [local] INPATH 'filepath' [overwrite] INTO TABLE table_name [partition(part1=val1,part2= val2)]

下面对语法中出现的关键字和参数进行说明：

1.  **LOAD。** 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
2.  **filepath。** 路径包括以下三种形式：相对路径，例如：project/data1，绝对路径，例如：/user/hive/project/data1，包含模式的完整 URI，如：hdfs://namenode:9000/user/hive/project/data1。
3.  **local。** 如果指定了 local，load 命令会去查找本地文件系统中的 filepath。如果没有指定 local 关键字，则根据 INPATH 中的 URI 查找文件。
4.  **overwrite。** 如果使用了 overwrite 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

【示例15】装载本地数据。将本地的 /data/hiveData/stu.txt 文件数据加载到表 student 中。/data/hiveData/stu.txt文件的内容如下所示：

[root@bigdata04 hiveData]# more stu.txt 
    1 paul 18
    2 amo 19
    3 jason 20

装载本地数据的命令如下：

load data local inpath '/data/hiveData/stu.txt' into table student;

查看表中的内容：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 20]  
到 hdfs 上去看一下这个表，发现刚才的文件其实就是上传到了 student 目录中：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 21]  
直接手工通过 put 命令把数据上传到该目录也是可以的。命令如下：

hdfs dfs -put /data/hiveData/stu.txt /user/hive/warehouse/mytest.db/student/stu2.txt

如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 22]  
查看表中的内容：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 23]  
**注意：hive 在创建表的时候，一定要指定相应的分隔符，否则会出现下面这种情况：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 24]  
解决方案1：在源数据中加入 hive 默认的分隔符：  
![在这里插入图片描述][20210710124921551.png]  
然后再重新建表、装载数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 25]  
解决方案2：在创建表的时候指定一下分隔符就可以了。行分隔符可以忽略不写，但是如果要写的话，只能写到最后面。

【示例16】装载HDFS数据。例如，将HDFS的/teacher.txt文件数据加载到表teacher中。首先在终端查看/teacher.txt的文件内容，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 26]  
下面将此文件中的数据加载到 teacher 表中，如果需要覆盖 teacher 表已有的记录，就需要加上 OVERWRITE 关键字，装载数据命令如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 27]  
**注意：1.在装载数据之前，必须存在 /teacher.txt 文件，否在会执行出错。2.加载数据时既可以装载本地数据，如 /data/hiveData/stu.txt；也可装载 HDFS 数据，如 /teacher.txt。区别在于，装载本地数据到表时，需要加上 LOCAL 关键字，而装载 HDFS 数据时则不需要。加上 LOCAL 关键字，Hive 会将本地文件复制一份再上传至指定目录；如果不加 LOCAL 关键字，Hive 只是将 HDFS 上的数据文件移动到指定目录，注意，原本目录下的数据文件将被删除，所以在装载 HDFS 数据时应做好数据文件的备份。**

【示例17】装载数据到分区表。

--创建分区表
    create table people(id int,name string)
    partitioned by(sex string)
    row format delimited fields terminated by ',';
    --源数据
    --female.txt
    load data local inpath '/data/hiveData/load_female.txt' into table people partition(sex='female');
    --male.txt
    load data local inpath '/data/hiveData/load_male.txt' into table people partition(sex='male');

### 5.4.2 插入数据 ###

Hive 中插入数据到数据表有三种方法，一为上面介绍的装载数据的方式，二是通过查询语句向表中插入数据，三是利用动态分区向表中插入数据(后面再说)，下面详细介绍第二种方法。Hive 支持通过查询语句向表中插入数据，语法如下：

INSERT OVERWRITE/INTO TABLE 表名
    [PARTITION(part=val1,part2=val2,...)]
    SELECT ... FROM 另一张表名

【示例18】创建一张名为result的数据表，包含学号和学生姓名字段，并将student表中的学号和学生姓名数据插入到result表中。

create table result(id int,name string);
    insert overwrite table result select id,name from student;
    select * from result;
    --使用INSERT INTO语句向表中插入一条数据，例如向result表中插入student表的第一条数据
    insert into table result select id,name from student limit 1;

【示例19】利用动态分区向表中插入数据。

CREATE TABLE tb_au_part(id int,name string) PARTITIONED BY(sex string);
    CREATE TABLE person(id int,name string,sex string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
    LOAD DATA LOCAL INPATH '/data/hiveData/load_female.txt' OVERWRITE INTO TABLE person;
    LOAD DATA LOCAL INPATH '/data/hiveData/load_male.txt' INTO TABLE person;
    --设置使用动态分区
    SET hive.exec.dynamic.partition=true;
    --设置动态分区模式为非严格模式，表示允许所有分区都是动态的
    SET hive.exec.dynamic.partition.mode=nonstrict;
    INSERT OVERWRITE TABLE tb_au_part PARTITION(sex) SELECT id,name,sex FROM person;
    show partitions tb_au_part;
    select * from tb_au_part;
    
    -- 直接向分区表中插入数据
    insert into table score3 partition(month ='202007') values ('001','002',100);
    insert overwrite table score4 partition(month = '202006') select sid,cid,sscore from score;
    --多插入模式
    create table score(sid string,cid string,sscore int)
    --在定义分区的字段的时候不能和定义表的字段重合，否则会发生错误。
    partitioned by(month string) --指定按照月份进行分区
    row format delimited fields terminated by '\t';
    load data local inpath '/data/hiveData/score.txt' into table score partition(month='202107');
    --创建第一部分表：
    create table score_first( sid string,cid  string) partitioned by (month string) row format delimited fields terminated by '\t' ;
    --创建第二部分表：
    create table score_second(cid string,sscore int) partitioned by (month string) row format delimited fields terminated by '\t';
    --分别给第一部分与第二部分表加载数据
    from score insert overwrite table score_first partition(month='202107') 
    select sid,cid insert overwrite table score_second partition(month = '202107') select cid,sscore;

### 5.4.3 导出数据 ###

【示例20】导出数据到本地。

INSERT OVERWRITE LOCAL DIRECTORY '/data/hiveData/tb_au_part' SELECT * FROM tb_au_part;

【示例21】导出数据到本地。

--collection items terminated by '#'
    insert overwrite local directory '/data/hiveData/tb_au_part1' row format delimited fields terminated by '\t' select * from tb_au_part;

【示例22】导出数据到HDFS。

INSERT OVERWRITE DIRECTORY '/test/person' SELECT * FROM person;

【示例22】hive shell命令导出。

bin/hive -e "select * from mytest.person;" > /data/hiveData/person.txt

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70]: /images/20221004/e357f989e5c74a8284cab7ee556e3feb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 1]: /images/20221004/fb6de9223811460cbb80c0938c326733.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 2]: /images/20221004/d662b57275ea4460868fb16f3e08d4a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 3]: /images/20221004/d1f066a43d4d4e298e0d9e9d4c8c507e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 4]: /images/20221004/2a0ab7d360684865ab67c1a4f4095a40.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 5]: /images/20221004/ff99c4941e2b4f798d63f101e2ceb0a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 6]: /images/20221004/d92e3200c2994aa0a279b5960a66e767.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 7]: /images/20221004/65a9b19b80a64fe589bbf881d5671c74.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 8]: /images/20221004/f8f78aa2ec4c41e1969fc273c20263bc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 9]: /images/20221004/e5fcb49096404e89a8b7ffa5948ae84d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 10]: /images/20221004/a806008b3e434d3c9bdfd975abf1405d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 11]: /images/20221004/afad99fec8bf4bb69b5ac1b19125029a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 12]: /images/20221004/99c28130843b4bf282e52e9577ff7ffc.png
[20210709014210463.png]: /images/20221004/21f7b40ca8f54c2b95e2714ff3ffc1b7.png
[20210709021601544.png]: /images/20221004/0a4c66fae4f945e5b1d1616617e1c7d1.png
[20210709151553994.png]: /images/20221004/7b370c2043ae4ea3a1710456b3cd05a8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 13]: /images/20221004/171c41a12cda4f218ede4018ff6345da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 14]: /images/20221004/75c32490a2d54539a020c947a6d966ae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 15]: /images/20221004/c80cffd69cb442cba80d540d0be41843.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 16]: /images/20221004/f732d22afabf4ac6a45a258041e15e98.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 17]: /images/20221004/b0f3e55f3689489ea219b18b8453cfca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 18]: /images/20221004/e387129e874a4ffbbae2497d663893ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 19]: /images/20221004/9b4381da764748efb94e735673fbc553.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 20]: /images/20221004/eb24be35f4db419784957d215a4e06f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 21]: /images/20221004/e766f4e935cf466bb6cf545be393fa04.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 22]: /images/20221004/aaf8004909c24468934ea4c4dcceba16.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 23]: /images/20221004/387f7534a95947e7a06b32cf9e6d4499.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 24]: /images/20221004/761c1e26294d4922973cea1fb2f962c9.png
[20210710124921551.png]: /images/20221004/1741e7568b2f467fbb1f9841bada5b3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 25]: /images/20221004/c3afe5cd7385425981762e526212833c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 26]: /images/20221004/5d73e214a7544527893d3df0bdf423e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h3MTY4MA_size_16_color_FFFFFF_t_70 27]: /images/20221004/857e51da2429487fb0ebeec42ed8ca1a.png