Hive基础知识

【数据仓库基础理论】

一、数据仓库概念、起源、构建

1. 概念

• 数据仓库（Data Warehouse，简称数仓、DW），是一个用于存储、分析、报告的数据系统。
• 数据仓库的目的是构建面向分析的集成化数据环境，分析结果为企业提供决策支持
• 数据仓库本身并不生产数据，其数据来源于不同外部系统
• 数据仓库自身也不需要“消费”任何数据，其结果开放给各个外部应用使用

2. 起源

       数据仓库为分析数据而来，分析结果给企业决策提供支持

       企业中，信息总是用作两个目的：操作性记录的保存、分析型决策的制定

（1）操作性记录的保存

       企业向外开展业务，多条业务线的业务正常运营需要记录维护多方面的信息。联机事务处理系统（OLTP）可以满足上述业务需求开展，其主要任务是执行联机事务处理。其基本特征是前台接受的用户数据可以立即传送到后台进行处理，并在短时间内给出处理结果。

       关系型数据库（RDBMS）是OLTP典型应用，例如Oracle、MySQL、SQL Server等。

（2）分析型决策的制定

       随着业务进行，业务数据持续产生，企业开始利用这些数据进行分析，以寻求能够扩大收益的方法。而OLTP不是开展数据分析的最佳选择，原因如下：

• 数据分析对数据开展读写操作，读取压力倍增
• OLTP仅储存数周或数月的数据
• 数据分布在不同系统不同表中，字段类型属性不统一

       当数据分析涉及的数据规模较小时，可以直接在OLTP系统上直接开展分析。但是为了更好的进行各种规模的数据分析，同时也不影响OLTP系统的运行，此时需要构建一个集成统一的数据分析平台。该平台目的很简单：面向分析、支持分析、与OLTP系统解耦。基于这种需求，数据仓库的雏形在企业中出现。

3. 构建

       数仓是一个用于存储、分析、报告的数据系统，目的是构建一个面向分析的集成化数据环境。我们把这种面向分析、支持分析的系统称为OLAP（联机分析处理）系统。数据仓库是OLAP的一种。

二、数据仓库主要特征

        面向主题：主题是一个抽象的概念，是较高层次上数据综合、归类并进行分析利用的抽象。在逻辑上，它是对应企业中某一宏观分析领域所涉及的分析对象（用户、理赔…）。基于主题组织的数据被划分为各自独立的领域，每个领域有各自的逻辑内涵但互不交叉，在抽象层次上对数据进行完整、一致和准确描述。

       集成性：主题相关的数据通常分布在多个操作型系统中，彼此分散、独立、异构，需要集成到数仓主题下，也就是数据在进入数据仓库前，要经过统一与综合，对数据进行抽取、清理、转换和汇总（ETL工作），所要完成的工作有：同一源数据所有矛盾之处（如字段的同名异义、异名同义、单位不同意、字长不一致等），进行数据综合和计算（数据仓库中的数据综合工作可以在从原有数据库抽取数据时生成，但许多是在数据仓库内部生成的，即在进入数据仓库以后进行综合生成的）。

       非易失性：也称非易变形，数据仓库是分析数据的平台，而不是创造数据的平台。数据仓库一般有大量的查询操作，但修改删除操作很少。

       时变性：数据仓库的数据需要随着时间更新，以适应决策需要。数据仓库中的数据按照时间顺序追加，自带时间属性。

注：OLTP、OLAP

       OLTP（联机事务处理）主要目标是做数据处理，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。传统的关系型数据库系统（RDBMS）作为数据管理的主要手段，主要用于操作性处理，主要包括Oracle、MySQL、SQL Server、mongoDB等。

       OLAP（联机分析处理）主要目标是数据分析，数据仓库是OLAP系统的典型示例，主要用于数据分析。

注：数据库、数据仓库

       数据库和数据仓库的区别实际上也就是OLTP和OLAP之间的区别

       OLTP系统的典型应用就是RDBMS，也就是关系型数据库，NoSQL不在讨论范围内

       OLAP系统的典型应用就是DW，也就是数据仓库

• 数据仓库不是大型数据库，虽然数据仓库存储数据规模大
• 数据仓库的出现，并不是要取代数据库
• 数据库是面向事务的设计，数据仓库是面向主题设计的
• 数据库一般存储业务数据，数据仓库一般存储历史数据
• 数据库为捕获数据而设计，数据仓库为分析数据而设计

注：数据仓库、数据集市

       数据仓库是面向整个集团组织的数据，数据集市是面向单个部门使用的

       可以认为数据集市是数据仓库的子集，也有人把数据集市叫做小型数据仓库。数据集市通常只涉及一个主题领域，例如市场营销或销售。因为它们通常更易于管理和维护，并具有灵活的结构

       数据的流程通常是：各种操作型系统数据和包括文件在内的其他数据作为数据源，经过ETL（抽取转换加载）填充到数据仓库中。数据仓库中有不同主题数据，数据集市则根据部门特点面向指定主题，比如采购（Purchasing）、销售（Sales）、库存（Inventory）。各部门用户可以根据主题数据开展各种应用：数据分析、数据报表、数据挖掘。

三、数据仓库分层架构

1. 分层思想和标准

       数据仓库的特点是本身不生产数据，也不最终消费数据。按照数据流入流出数仓的过程进行分层就显得水到渠成。

       最基础的分层思想，理论上分为三层：操作性数据层（ODS）、数据仓库层（DW）和数据应用层（DA）

       通过元数据管理、数据质量监控把控整个数仓中的数据流转过程、血缘依赖关系和生命周期

  （1）ODS层

• 操作型数据层，也成为源数据层、数据引入层、数据暂存层、临时缓存层
• 此层存放未经过处理的原始数据至数据仓库系统，数据结构上与源系统保持一致（进行拷贝），是数据仓库的数据准备区
• 主要完成基础数据引入到数仓的职责，和数据源系统进行解耦，同时记录基础数据的历史变化

  （2）DW层

• 数据仓库层，由ODS层数据加工而成，主要完成数据加工与整合，建立一致性的维度，构建可复用的面向分析和统计的明细事实表，以及汇总公共粒度的指标。内部具体划分如下：
• 公共维度层（DIM）：基于维度建模理念思想，建立整个企业一致性维度
• 公共汇总粒度事实层（DWS、DWB）：以分析的主体对象作为建模驱动，基于上层的应用和产品的指标需求，构建公共粒度的汇总指标事实表，以宽表化手段物理化模型
• 明细粒度事实层（DWD）：将明细事实表的某些重要维度属性字段做适当冗余，即宽表化处理

  （3）DA层（或ADS层）

• 数据应用层，面向最终用户，面向业务定制提供给产品和数据分析使用的数据。
• 包括前端报表、分析图表、KPI、仪表盘、OLAP专题、数据挖掘等分析。

2. 数据仓库分层的必要性

分层的主要原因是在管理数据的时候，能对数据有一个更加清晰的掌控。

（1）清晰数据结构

       每一个数据分层都有其作用域，在使用表的时候能更方便地定位和理解。

（2）数据血缘追踪

       数据分析师最终给业务呈现的是一个能直接使用的业务表，但是它的来源很多，如果一张来源表出现问题，数据分析师可以快速准确定位到问题，并清楚它的危害范围。

（3）减少重复开发

       规范数据分层，开发一些通用的中间层数据，能够极大减少重复计算。

（4）把复杂问题简单化

       将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的的数据，只需要从有问题的步骤开始修复。

（5）屏蔽原始数据的异常

       屏蔽业务的影响，不必改一次就需要重新接入数据。

注：ETL、ELT

       数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL（抽取Extra，转换Transfer、加载Load）的过程。

       但在实际操作中将数据加载到仓库后却产生两种不同做法：ETL和ELT，二者的主要区别是数据转换是否在数据仓库中进行的。

（1）ETL

       首先从数据源池中提取数据，这些数据源通常是事务性数据库。数据保存在临时暂存数据库中（ODS），然后执行转换操作，将数据结构化并转换为适合目标数据仓库系统的形式。然后将结构化数据加载到仓库中，以备分析。

（2）ELT

       使用ELT，数据在从数据源池中提取后立即加载。没有专门的临时数据库（ODS），这意味着数据会立即加载到单一的集中存储库中。数据在数据仓库系统中进行转换，以便与商业智能工具（BI工具）一起使用。大数据时代的数仓这个特点很明显。

3. 案例分析：美团点评酒旅数仓建设实践

      通过一线互联网数仓建设实践案例，从宏观层面感受一些几点：

• 数仓面向主题分析的特点
• 在企业中，数仓是一个不断维护的工程
• 数仓分层不局限于经典三层，可根据自身需求进行调整
• 没有好的架构，只有适合自身业务需求的架构

（1）架构变迁

       在美团点评酒旅事业群内，业务由传统的团购形式转向预定、直连等更加丰富的产品形式，业务系统也在迅速的迭代变化，这些都对数据仓库的扩展性、稳定性、易用性提出更高要求。

       基于此，美团采取分层次、分主题的方式不断优化并调整层次结构，下图展示了技术架构的变迁。

       第一代数仓模型：当时美团整体的业务系统所支持的产品形式比较单一（团购），业务系统中包含了所有业务品类的数据，所以由平台来加工数据仓库基础层，平台统一建设，支持各个业务线来使用。在本阶段中酒旅只是建立了一个相对比较简单的数据集市，所谓了小型数据仓库。

       第二代数仓模型：随着美团酒旅的业务逐渐变得多样化，酒旅相关数据从建设数据集市的形式转变成直接建设酒旅数据仓库，成为酒旅自身业务系统数据的唯一加工者。

        第三代数仓模型：随着美团和点评融合，同时酒旅自身业务系统重构的频率也相对较高，对第二代数仓模型稳定性造成非常大的影响，原本的维度模型难以适配迅速的变化。核心问题是业务系统和业务线关系错综复杂，业务系统之间差异性明显且变更频繁。为解决该问题，在ODS和多维明细层中间加入数据整合层（将不同供应链、不同业务、不同数据进行整合），由业务驱动调整成由技术驱动的方式建设数据仓库基础层。使用本基础层的最根本出发点在于美团的供应链、业务、数据本身的多样性，如果业务、数据相对单一和简单，本层次的架构方案可能不适用。

（2）主题建设

       实际上在一些传统的如银行、制造业、电信、零售等行业里，都有一些比较成熟的模型，如BDWM（银行数据）模型，它们是经过一些具有相类似行业的企业在二三十年数据仓库建设中所积累的行业经验，不断的优化并通用化。

       但美团所处的O2O行业本身就没有可借鉴的成熟的数据仓库主体以及模型，所以在摸索建设的两年时间里，美团总结了比较适合现状的七大主题。

（3）整体架构

       确定好技术和业务主题后，数仓的整体架构变得比较清晰。美团酒旅数仓七个主题基本上都采用6层结构的方式来建设，划分主题更多是从业务的角度出发，而层次划分则是基于技术，实质上就是结合业务和技术完成整体的数据仓库架构

       以订单主题为例。在订单主题的建设过程中，美团是按照由分到总的结构思路来进行建设，首先分供应链建设订单相关实体（数据整合中间层），然后再进行适度抽象把分供应链的相关订单实体进行合并后生成订单实体（数据整合层），后续在数据整合层的订单实体基础上再扩展部分维度信息来完成后续层次的建设。

四、Apache Hive架构、组件、数据模型

1. Apache Hive概述

       Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将储存在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，成为Hive查询语言（HQL），用于访问和分析存储在Hadoop文件中的大型数据集。

       Hive的核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop群集执行

（1）使用Hive的优势

• 使用Hadoop MapReduce的难度较高，使用Hive可以避免直接写MapReduce，减少开发人员的学习成本
• 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）
• 支持自定义函数，方便扩展功能
• 背靠Hadoop，擅长存储分析海量数据集

（2）Hive和Hadoop的关系

• 数据仓库软件至少需要具备两种能力：数据存储能力、数据分析能力，Hive利用HDFS存储数据，利用MapReduce查询分析数据
• 用户编写HQL，Hive帮助用户转换成MapReduce程序完成对数据的分析

2. 案例分析：模拟实现Apache Hive的功能

       如果自行设计Hive软件，要求能够实现用户编写sql语句，Hive自动将sql转换为MapReduce程序，处理位于HDFS上的结构化数据。这该如何实现？

       重点理解以下几点：

• Hive能将数据文件映射成一张表，这个映射指的是什么？（文件和表之间的对应关系）
• Hive本身承担了什么功能职责？将sql语法解析编译成MapReduce

（1）映射信息记录

       映射在数学上是一种对应关系，例如y=x+1，对于每一个x值都有一个y值

       在Hive中能写sql处理的前提是针对表，而不是针对文件，因此需要将文件和表之间的对应关系描述记录清楚。映射信息的专业叫法是元数据信息（元数据是指用来描述数据的数据metadata）

       具体而言，要记录的元数据信息包括：

• 表对应着哪个文件（位置信息）
• 表的列对应着文件哪一个字段（顺序信息）
• 文件字段之间的分隔符是什么

（2）SQL语法解析、编译

       用户写完sql之后，Hive针对sql进行语法校验，并且根据记录的元数据信息解读sql背后的含义，制定执行计划，并且把执行计划转换为MapReduce程序来执行，把执行的结果封装返回给用户。

（3）最终效果

       基于上述分析，最终想要模拟实现Hive的功能，大致需要下图所示组件参与其中。

注：结构化数据、半结构化数据、非结构化数据

       结构化数据是指按照固定模式组织和存储的数据。这类数据通常存储在关系型数据库中，由表格的行和列构成，数据项之间的关系清晰且严格遵循预定义的模式。例如企业的人事信息表，包括员工编号、姓名、职位、部门等。

       半结构化数据是指介于结构化和非结构化之间的一种数据形式。这类数据没有严格的模式约束，但具有一定的组织结构，例如键值对、树形结构或图形结构。常见的存储形式包括JSON、XML和YAML文件。

       非结构化数据是指没有固定组织形式的数据。这类数据通常是大量文本、图像、音频或视频文件，难以用关系型数据库的表格结构直接表示和存储。

3. Apache Hive架构、组件

（1）架构图

（2）组件

       用户接口：即用户访问使用Hive、写sql的方式。包括CLI、JDBC/ODBC、WebGUI。其中，CLI（command line interface）为shell命令行；Hive中的Thrift服务器允许外部客户端通过网络与Hive进行交互，类似于JDBC或ODBC协议。WebGUI是通过浏览器访问Hive。

       元数据存储：通常是存储在关系数据库如mysql/derby中。Hive中的元数据（表和文件的映射关系、对应信息）包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录。

       Driver驱动程序：包括语法解析、计划编译器、优化器、执行器。完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后有执行引擎调用执行。

       执行引擎：Hive本身并不直接处理数据文件，而是通过执行引擎处理。当下Hive支持MapReduce、Tez、Spark这3种执行引擎。

4. Apache Hive数据模型

       数据模型：用来描述数据、组织数据、对数据进行操作，是对现实世界数据特征的描述。

       Hive的数据模型类似于RDBMS库表结构，此外还有自己特有的模型。

       Hive中的数据可以在粒度级别上分为三类：Table表、Partition分区、Bucket分桶

（1）Database数据库

       Hive作为一个数据仓库，在结构上积极向传统数据库看齐，也分数据库（Schema），每个数据库下面由各自的表组成，默认数据库default。

       Hive的数据存储在HDFS上，默认有一个根目录，在hive-site.xml中，由参数hive.metastore.wearhouse.dir指定。默认值为/user/hive/wearhouse。因此Hive中的数据库在HDFS上的存储路径为：${hive.metastore.wearhouse.dir}/databasename.db，比如，名为itcast的数据库存储路径为/user/hive/wearhouse/itcast.db

（2）Table表

       Hive表与关系数据库中的表相同，Hive中的表所对应的数据通常存储在HDFS中，而表相关的元数据存储在RDBMS中。

       Hive中的表的数据在HDFS上的存储路径为：

${hive.metastore.wearhouse.dir}/databasename.db/tablename

       例如itcast数据库中的表t_user的存储路径是：/user/hive/wearhouse/itcast.db/t_user

（3）Partition分区

       分区表是Hive的一种优化手段表，分区是指根据分区列（例如“日期day”）的值将表划分为不同分区。这样可以更快地对指定分区数据进行查询。

       分区在存储层面上的表现是：table表目录下以子文件夹形式存在，一个文件夹表示一个分区，子文件命名标准：分区列=分区值

       Hive还支持分区下继续创建分区，所谓的多重分区。

（4）Buckets分桶

       分桶表是Hive的一种优化手段表，分桶是指根据表中字段（例如“编号ID”）的值，经过hash计算规则将数据文件分成指定的若干小文件。

       分桶规则:hashfunc（字段）%桶个数，余数相同的分到同一个文件

       分桶的好处是可以优化join查询和方便抽样查询。

       分桶表在HDFS中表现为同一个表目录下数据根据hash散列之后变成多个文件。

注：Apache Hive是要取代MySQL吗？

     1. 从数据模型上看Apache Hive和MySQL很相似，库、表、字段之类的，难道Hive也是数据库？

     2. Hive依赖Hadoop，理论上可以无限存储数据，难道它也算是数据库？取代MySQL？

     Hive和MySQL对比：

• Hive虽然具有RDBMS数据库的外表，包括数据模型、SQL语法都非常相似，但应用场景完全不同。
• Hive只适合用来做海量数据的离线分析。Hive的定位是数据仓库，面向分析的OLAP系统。
• 因此Hive不是大型数据库，也不会取代MySQL承担业务数据处理

五、Apache Hive元数据

1. Metadata和Metastore

       元数据（Metadata）：又称中介数据、中继数据，是描述数据的数据，只要是描述数据属性的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。

       Hive Metadata：即Hive的元数据，包含用Hive创建的database、table、表的位置、类型、属性、字段顺序类型等元信息。元数据存储在关系型数据库中，如Hive内置的Derby、第三方如MySQL等。

       Hive Metastore：即元数据服务，Metastore服务的作用是管理Metadata元数据，对外暴露服务地址，让各种客户端通过连接Metastore服务，由Metastore再去连接MySQL数据库来存取元数据。Metastore服务保证多个客户端可以同时连接，且这些客户端不需要知道MySQL数据库的用户名和密码，只需连接Metastore服务即可。某种程度上保证了Hive元数据安全（客户端无法改变元数据）。

2. Metastore配置方式

       Metastore服务配置有3种模式：内嵌模式、本地模式、远程模式。

       区分3种配置方式的关键是弄清楚两个问题：

• Metastore服务是否需要单独配置、单独启动？
• Metastore是存储在内置的derby中，还是第三方RDBMS，比如MySQL

       这里使用企业推荐模式——远程模式部署

（1）内嵌模式

       Metastore默认部署方式，在此模式下，元数据存储在内置的derby数据库中，且derby数据库和Metastore服务都嵌入在主HiveServer进程中，当启动HiveServer进程时，derby和Metastore服务都会启动，不需要额外启动Metastore服务。

       但是该模式一次只能支持一个活动用户，适用于测试体验，不适用于生产环境。

（2）本地模式

       此模式下，Metastore服务与主HiveServer进程在同一进程中运行，但是存储元数据的数据库在单独的进程中运行，并且可以在单独的主机上。Metastore服务将通过JDBC与Metastore数据库进行通信。

       本地模式采用外部数据库来存储元数据，推荐使用MySQL

       Hive根据hive.metastore.uris参数值判断，如果为空，则为本地模式。

       缺点是每启动一次Hive服务，都内置启动了一个metastore。

（3）远程模式

       在此模式下，Metastore服务在单独的JVM上运行，而不再HiveServer的JVM中运行。如果其他进程希望与Metastore服务器通信，可以使用Thrift Network API进行通信。

       远程模式下，需要配置hive.metastore.uris参数来指定Metastore服务运行的机器ip和端口，且需要单独手动启动Metastore服务，元数据也采用外部数据库来存储元数据，推荐使用MySQL。

       在生产环境中，建议用远程模式来配置Hive Metastore.。在这种情况下，其他依赖hive的软件都可以通过Metastorei访问hive。由于还可以完全屏蔽数据库层，因此这也带来了更好的可管理性/安全性。

【Hive SQL数据定义语言（DDL）】

一、数据定义语言(DDL)概述

1. Hive可视化工具IntelliJ IDEA

（1）Hive CLI、Beeline CLI

       Hive自带的命令行客户端

• 优点：不需要额外安装
• 缺点：编写SQL环境恶劣，无有效提示，无语法高亮，误操作几率高

（2）文本编辑器

       Sublime、Emacs、EditPlus、UltraEdit、Visual Studio Codes等

       有些不支持作为客户端连接Hive服务，但是支持SQL语法环境，那就在编辑器中开发SQL,复制到Hive CLI执行；

       有些支持安装插件作为客户端直连Hive服务（Sublime）；

（3）Hive可视化工具

       IntelliJ IDEA、DataGrip、Dbeaver、SQuirrel SQL Client等可以在Windows、MAC平台中通过JDBC连接HiveServer2的图形界面工具。这类工具往往专门针对SQL类软件进行开发优化、页面美观大方，操作简洁，更重要的是SQL编辑环境优雅。

       SQL语法智能提示补全、关键字高亮、查询结果智能显示、按钮操作大于命令操作；

（4）IntelliJ IDEA

       IntelliJ IDEA是JetBrains公司的产品，是java编程语言开发的集成环境。在业界被公认为最好的java开发工具，尤其在智能代码助手、代码自动提示、重构、代码分析、创新的GUI设计等方面的功能可以说是超常的。

       IntelliJ IDEA还有丰富的插件，其中就内置集成了Database插件，支持操作各种主流的数据库、数据仓库。

2. SQL中DDL语法的作用

       数据定义语言(Data Definition Language,DDL),是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database(schema)、table、view、index等。

       DDL核心语法由CREATE（创建）、ALTER（修改）与DROP（删除）三个所组成。DDL并不涉及表内部数据的操作。
在某些上下文中，该术语也称为数据描述语言，因为它描述了数据库表中的字段和记录。

3. Hive中DDL语法的使用

       Hive SQL(HQL)与标准SQL的语法大同小异，基本相通，注意差异即可；

       基于Hive的设计、使用特点，HQL中createi语法(尤其create table)将是学习掌握Hive DDL语法的重中之重。

       建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据。通俗点说，没有表，表没有数据，用Hive分析什么呢？

二、Hive SQL DDL建表基础语法

1. 建表语法树

       蓝色字体是建表语法的关键字，用于指定某些功能。

       [] 中括号的语法表示可选。

       | 表示使用的时候，左右语法二选一。

       建表语句中的语法顺序要和语法树中顺序保持一致。

2. Hive数据类型详解

       Hive数据类型指的是表中列的字段类型，整体分为两类：原生数据类型(primitive data type)和复杂数据类型(complex data type)。

• 原生数据类型包括：数值类型、时间日期类型、字符串类型、杂项数据类型；
• 复杂数据类型包括：array数组、map映射、struct结构、union联合体。

（1）原生数据类型

（2）复杂数据类型

（3）注意事项

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

注：隐式转换、显示转换

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

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

3. Hive读写文件映射成功的机制

（1）SerDe

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

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

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

       可以通过desc formatted tablename查看表的相关SerDe信息。默认如下：

（2）Hive读写文件流程

       Hive读取文件机制：首先调用InputFormat(默认TextInputFormat),返回一条一条kv键值对记录（默认是一行对应一条键值对）。然后调用SerDe(默认LazySimpleSerDe)的Deserializer，将一条记录中的value根据分隔符切分为各个字段。

       Hive写文件机制：将Row写入文件时，首先调用SerDe(默认LazySimpleSerDe)的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。

（3）SerDe相关语法

       ROW FORMAT这一行所代表的是跟读写文件、序列化SerDe相关的语法，功能有二：

• 使用哪个SerDe类进行序列化；
• 如何指定分隔符。

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

       LazySimpleSerDe分隔符指定

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

（4）Hive默认分隔符

       Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符；

       默认的分割符是'\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。

       在vim编辑器中，连续按下Ctr1+v/Ctr1+a即可输入'\001'，显示^A

       在一些文本编辑器中将以SOH的形式显示

4. Hive数据存储路径

（1）默认存储路径

       Hive表默认存储路径是由${HIVE HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指定，默认值是：/user/hive/warehouse。

       在该路径下，文件将根据所属的库、表，有规律的存储在对应的文件夹下。

（2）指定存储路径

       在Hive建表的时候，可以通过locationi语法来更改数据在DFS上的存储路径，使得建表加载数据更加灵活方便。

       语法：LOCATION'<hdfs_location>>'

       对于已经生成好的数据文件，使用location指定路径将会很方便。

5. Hive建表语法练习

练习内容：数据类型、分隔符指定、默认分隔符、指定数据存储路径

（1）原生数据类型使用

       文件archer.txt中记录了手游《王者荣耀》射手的相关信息，包括生命、物防、物攻等属性信息，其中字段之间分隔符为制表符\t,要求在Hive中建表映射成功该文件。

       数据文件内容如图所示，字段含义：id、name（英雄名称）、hp_max（最大生命）、mp_max（最大法力）、attack_max（最高物攻）、defense_max（最大物防）、attack_range（攻击范围）、role_main（主要定位）、role_assist（次要定位）。

       字段都是基本类型，字段的顺序需要注意一下。字段之间的分隔符是制表符，需要使用row format语法进行指定。

       在IntelliJ IDEA输入语法，具体语法为如图所示，其中id int comment "id"的意思是字段名为id，为方面理解，对该字段进行注释，该字段的意思是ID。

       建表成功之后，在Hive的默认存储路径下就生成了表对应的文件夹，把archer.txt文件上传到对应的表文件夹下：将文件拖到Linux服务器中，点击Send Zmodem即可完成上传。

       上传成功后输入红框中显示的命令，将文件传到之前创建表所对应的路径下面，至此完成表与文件之间的映射。

       可对映射结果进行验证，执行图中显示的查询操作，可以看出数据已经映射成功。

（2）复杂数据类型使用

       文件hot_hero_skin_price.txt中记录了手游《王者荣耀》热门英雄的相关皮肤价格信息，要求在Hive中建表映射成功该文件。

        数据文件内容如图所示，字段：id、name（英雄名称）、win rate（胜率）、skin price（皮肤及价格）

       前3个字段原生数据类型、最后一个字段复杂类型map。需要指定字段之间分隔符、集合元素之间（每个kv之间）分隔符、map kv之间分隔符。

       具体语法为如图所示，之后执行和原生数据类型相同的映射和验证操作即可。

（3）默认分隔符使用

       文件team_ace_player.txt中记录了手游《王者荣耀》主要战队内最受欢迎的王牌选手信息，字段之间使用的是\001作为分隔符，要求在Hive中建表映射成功该文件。

       数据文件内容如图所示，字段：id、team_name（战队名称）、ace player name（王牌选手名字）
       数据都是原生数据类型，且字段之间分隔符是\001，因此在建表的时候可以省去row format语句，因为hive默认的分隔符就是\001。

        具体语法为如图所示，之后执行和原生数据类型相同的映射和验证操作即可。

       字段以\001分隔建表时很方便，因此采集、清洗数据时应优先考虑\001作为分隔符。

（4）指定数据存储路径（使用location）

       文件team_ace_player.txt中记录了手游《王者荣耀》主要战队内最受欢迎的王牌选手信息，字段之间使用的是\001作为分隔符。
       要求把文件上传到HDFS任意路径下（非默认路径），不能移动复制，并在Hive中建表映射成功该文件。

       具体语法为如图所示，

三、Hive SQL DDL建表高阶语法

1. Hive内、外部表

（1）内部表

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

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

（2）外部表

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

（3）内、外部表差异

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

（4）内、外部表差异案例演示

       针对创建好的内部表student、外部表student_ext,分别上传结构化数据文件students.txt到对应的表路径下。确定数据被Hive解析加载成功。

       执行drop table tablename命令，分别在Hive中和HDFS中查看效果。

       Hive中的表信息全被删除，不管是内部表还是外部表。而HDFS上，外部表对应的数据文件原封不动，内部表对应的数据文件连同文件夹一起被删除。

（5）如何选择内、外部表

       当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。

       当数据来之不易，防止误删，请使用外部表，因为即使删除表，文件也会被保留。

2. location再探究

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

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

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

  答：内部表可以使用location指定位置

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

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

3. Hive Partitioned Tables分区表构建

（1）案例：分区表产生背景

       现有6份结构化数据文件，分别记录了《王者荣耀》中6种位置的英雄相关信息。现要求通过建立一张表t_a11_hero，把6份文件同时映射加载。

       具体语法如图所示，将6个文件选中并拖到Linux系统中，再输入红框中的命令，即可完成同时映射操作。

       现要求查询role_main主要定位是射手并且hp_max最大生命大于6000的有几个，sql语句如下：

       1. where语句的背后需要进行全表扫描才能过滤出结果，对于hive来说需要扫描每一个文件。如果数据文件个数特别多的话，扫描效率很慢也没必要。

       2. 本需求中，只需要扫描archer.txt文件即可，如何优化可以加快查询，减少全表扫描呢？

       3. 指定文件扫描和全表扫描，效率还是存在差异的。

（2）分区表概念

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

       比如把一整年的数据根据月份划分12个月(12个分区)，后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。

（3）分区表构建

       在建表的完整语法树中，分区表相关语法如图中横线所示

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

       对于之前提及的案例，创建分区表具体语法如下，role是自己随意写的分区字段名。

       至此完成分区表的创建，接下来需要将数据加载到分区表中。

4. 分区表数据加载——静态分区

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

        Local参数用于指定待加载的数据是位于本地文件系统（加）还是HDFS文件系统（不加）。

       对于之前提及的案例，不同的文件对应不同的英雄位置，因此每个文件应该是一个分区，我们需要在加载文件数据时为每个分区定义一个分区值，以便在建立总表时区分英雄位置。因此静态分区语法如图所示，其中分区字段名应与之前建表时定义的分区字段名role保持一致，分区值可任意定义。

       至此，每个英雄都拥有分区值。

       外表上看起来分区表好像没多大变化，只不过多了一个分区字段，实际上分区表在底层管理数据的方式发生了改变。这里直接去HDFS查看区别。

       如果采用分区表的形式，会发现表名下面不再是文件名，而是一个个以分区值命名的文件夹，不同分区的文件在对应的文件夹下面。

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

       数据加载完成后，可以以图中的语法进行查询，用时变得极短

5. 多重分区表构建及静态数据加载

       通过建表语句中关于分区的相关语法可以发现，Hive支持多个分区字段：

       PARTITIONED BY (partition1 data_type,partition2 data_type,....)

       多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。

       从HDFS的角度来看就是文件夹下继续划分子文件夹。比如：把全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是3分区表。

       具体语法如下图所示，多重分区表中分区字段之间存在递进关系，因此要注意字段的顺序。 

6. 分区表数据加载——动态分区

       1. 在向Hive分区表加载数据的时候，我们把使用load命令手动指定分区值的方式叫做静态加载，那么有没有动态加载？

       2. 如果创建的分区很多，是否意味着复制粘贴修改很多load命令去执行，效率低。有没有高效的方法？

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

       针对《3. Hive Partitioned Tables分区表构建》中提及的案例做演示，首先构建分区表t_all_hero_part_dynamic。

       对于动态分区加载数据，需要先将数据存放在一张表中，因此从之前创建的t_all_hero表中将数据以动态分区的方式插入到t_all_hero_part_dynamic表中，具体语法如图所示。

       第一行的分区字段名要和之前创建表时定义的分区字段名保持一致，但是分区值没有像之前一样手动写死指定。

       第二行中t_all_hero tmp表示将t_all_hero表作为临时表，tmp.*的意思是将临时表中所有的字段数据作为表的数据拿出来填充到t_all_hero_part_dynamic表中，再将role_main字段中的数据拿出来作为分区字段role所对应的分区值，

       至此，完成将t_all_hero表中数据以动态分区的方式按照role_main字段中的数据作为分区值插入到t_all_hero_part_dynamic分区表中。

7. 分区表的注意事项

       一、分区表不是建表的必要语法规侧，是一种优化手段表，可选；

       二、分区字段不能是表中已有的字段，不能重复；

       三、分区字段是虚拟字段，其数据并不存储在底层的文件中；

       四、分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）

       五、Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度

8. Hive Bucketed Tables分桶表

（1）概念

       分桶表也叫做桶表，叫法源自建表语法中bucket单词，是一种用于优化查询而设计的表类型。

       分桶表对应的数据文件在底层会被分解为若干个部分，通俗来说就是被拆分成若干个独立的小文件。

       在分桶时，要指定根据哪个字段将数据分为几桶（几个部分）。

（2）分桶规则
       桶编号相同的数据会被分到同一个桶当中。

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

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

       在建表完整语法树中，分桶表相关语法如图中红线所示。

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

（3）分桶表的创建

       现有美国2021-1-28号，各个县county的新冠疫情累计案例信息，包括确诊病例和死亡病例，数据格式如下所示；字段含义：count date（统计日期），county（县），state（州），fips(县编码code)，cases（累计确诊病例），deaths（累计死亡病例）。

       根据state州把数据分为5桶，建表语句如下：

       在创建分桶表时，还可以指定分桶内的数据排序规则：

       接下来将数据加载到分桶表中，与动态分区一致，使用insert+select语法。最后一段代码将普通表t_usa_covid19中所有数据加载到分桶表t_usa_covid19_bucket中，由于分桶表t_usa_covid19_bucket在创建时设定了根据state进行分桶，因此在数据加载过程中会自动根据state进行分桶。

       到HDFS上查看t usa covid19 bucket底层数据结构可以发现，数据被分为了5个部分。并且从结果可以发现，分桶字段一样的数据就一定被分到同一个桶中。

（4）分桶表使用好处

       基于分桶字段查询时，减少全表扫描。

       J0IN（表连接）时可以提高MR程序效率，减少笛卡尔积数量。根据join的字段对表进行分桶操作(比如下图中id是join的字段)

       分桶表数据进行高效抽样。当数据量特别大时，对全体数据进行处理存在困难，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

9. Hive Transactional Tables事务表背景知识

（1）Hive事务背景知识

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

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

  流式传输数据
       使用如Apache Flume、Apache Kafka之类的工具将数据流式传输到Hadoop集群中。虽然这些工具可以每秒数百行或更多行的速度写入数据，但是Hive只能每隔15分钟到一个小时添加一次分区。如果每分甚至每秒频繁添加分区会很快导致表中大量的分区，并将许多小文件留在目录中，这将给NameNode带来压力。因此通常使用这些工具将数据流式传输到已有分区中，但这有可能会造成脏读(数据传输一半失败，回滚了)。基于此，需要通过事务功能，允许用户获得一致的数据视图并避免过多的小文件产生。

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

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

（2） Hive事务表局限性

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

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

10. 案例：创建使用Hive事务表

       在Hive中创建一张具备事务功能的表，并尝试进行增删改操作。体验一下Hive的增删改操作和MySQL比较起来，性能如何？

       事务表的创建包含要素：开启参数、分桶表、存储格式orc、表属性

       首先需要开启事务配置

       接下来需要创建Hive事务表，语法如下图所示：

        语法中第一个红框表示对表格进行分桶，第二个红框表示将表格保存为orc格式，第三个红框表示开启事务功能。

       接下来可对事务表进行insert、update、delete操作，但是用时较长。

四、Hive SQL DDL其他语法

1. Hive Views视图

（1）概念

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

（2）相关语法

（3）使用视图的好处

       将真实表中特定的列数据提供给用户，保护数据隐式。

       降低查询的复杂度，优化查询语句（但是没有优化查询效率）。

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

       在数据分析过程中，有一条SQL执行过程很漫长，但其结果之后会在不同地方使用。此时可以创建一个临时表/中间表，将SQL结果保存起来，之后需要该结果时直接使用，省去重复且漫长的查询过程，这就是预处理思想。

（1）概念

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

       Hive3.0开始尝试引入物化视图，并提供对于物化视图的查询自动重写机制(基于Apache Calcite实现，在查询语句时，如果和物化视图匹配上，它会帮助用户进行重写，效率更加高效)。Hive的物化视图还提供了物化视图存储选择机制，可以本地存储在Hive，也可以通过用户自定义storage handlers存储在其他系统(如Druid)。

       Hive引入物化视图的目的就是为了优化数据查询访问的效率，相当于从数据预处理的角度优化数据访问。

       Hive从3.0丢弃了index索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度。

（2）物化视图、视图区别

       视图是虚拟的，逻辑存在的，只有定义没有存储数据。物化视图是真实的，物理存在的，里面存储着预计算的数据。

       视图只是创建一个虚表，只有表结构，没有数据，实际查询的时候再去改写SQL去访问实际的数据表。物化视图能够缓存数据，在创建物化视图的时候就把数据缓存起来了，Hive把物化视图当成一张“表”，将数据缓存。

       视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能。

（3）语法

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

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

       默认SerDe和storage formati为hive.materializedview.serde、hive.materializedview.fileformat;

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

       目前支持物化视图的drop和show操作，后续会增加其他操作

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

（4）基于物化视图的查询重写

       物化视图创建后即可用于相关查询的加速，即：用户提交查询query，若该query经过重写后可以命中已经存在的物化视图，则直接通过物化视图查询数据返回结果，以实现查询加速。

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

true:hive.materializedview..rewriting=true;

       用户可选择性的控制指定的物化视图查询重写机制，语法如下：

（5）案例：基于物化视图的查询重写

       1.用户提交查询query

       2.若该query经过重写后可以命中已经存在的物化视图

       3.则直接通过物化视图查询数据返回结果，以实现查询加速

       代码如图所示，首先建立事务表。

       接下来建立物化视图，建立该视图的用时和单独执行物化视图后面的查询语句用时一致。

       最后执行物化视图后面的查询语句，发现查询速度极快（因为查询语句和物化视图匹配上了）。

       因此对于在分析过程中需要查询多次的结果，建立物化视图只需要经历一次漫长的等待过程，之后几次的速度会变得极快。

3. Hive Database|Schema（数据库）DDL操作

（1）整体概述

       在Hive中，DATABASE的概念和RDBMS中类似，我们称之为数据库，DATABASE和SCHEMA是可互换的，都可以使用。

       默认的数据库叫做default，存储数据位置位于/user/hive/warehouse下。

       用户自己创建的数据库存储位置是/user/hive/warehouse/database._name.db下。

（2）create database

       create database用于创建新的数据库

       COMMENT:数据库的注释说明语句

       LOCATION:指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db

       WITH DBPROPERTIES:用于指定一些数据库的属性配置。

       例子：创建数据库itcast，注意：如果需要使用location指定路径的时候，最好指向的是一个新创建的空文件夹。

（3）describe database 

       显示Hive中数据库的名称，注释（如果已设置）及其在文件系统上的位置等信息。EXTENDED:关键字用于显示更多信息。可以将关键字describe简写成desc使用。

（4）use database

       想选择特定的数据库时，可以在IDEA中点击下图红框中的图表进行数据库的切换，当前的数据库名称是itheima

（5）drop database

       基于谨慎性原则，删除数据库的默认行为是RESTRICT，仅在数据库为空时才删除它。

       要删除带有表的数据库（不为空的数据库），需要使用CASCADE。

（6）alter database

       更改与Hive中的数据库关联的元数据相关代码如图所示

4. Hive Table（表）DDL操作

（1）整体概述

       Hive中针对表的DDL操作可以说是DDL中的核心操作，包括建表、修改表、删除表、描述表元数据信息。其中以建表语句为核心中的核心，详见Hive DDL建表语句。

       可以说表的定义是否成功直接影响着数据能够成功映射，进而影响是否可以顺利的使用Hive开展数据分析。

       由于Hive建表之后加载映射数据很快，实际中如果建表有问题，可以不用修改，直接删除重建（推荐）。

（2）describe table

       显示Hive中表的元数据信息

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

       如果指定了FORMATTED关键字，则它将以表格格式显示元数据（推荐）。

（3）drop table

       删除该表的元数据和数据，如果已配置垃圾桶且未指定PURGE,则该表对应的数据实际上将移动到DS垃圾桶，而元数据完全丢失。

       删除EXTERNAL表时，该表中的数据不会从文件系统中删除，只删除元数据。

       如果指定了PURGE,则表数据跳过HDFS垃圾桶直接被删除。因此如果DROP失败，则无法挽回该表数据。

（4）truncate table

       从表中删除所有行。可以简单理解为清空表的所有数据但是保留表的元数据结构。

       如果HDFS启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除。

（5）alter table

5. Hive Partition（分区）DDL操作

（1）整体概述

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

（2）add partition

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

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

（3）rename partition

（4）delete partition

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

（5）alter partition

（6）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中删除的metastorer中删除分区信息。

       SYNC PARTITIONS选项等效于调用ADD和DROP PARTITIONS.

       如果存在大量未跟踪的分区，则可以批量运行MSCK REPAIR TABLE,以避免OOME（内存不足错误）。

（7）案例：Hive MSCK修复partition

       ①创建一张分区表，直接使用HDFS命令在表文件夹下创建分区文件夹并上传数据，此时在Hive中查询是无法显示表数据的，因为metastore中没有记录，使用MSCK ADD PARTITIONS进行修复。

       ②针对分区表，直接使用HDFS命令删除分区文件夹，此时在Hive中查询显示分区还在，因为
metastore中还没有被删除，使用MSCK DROP PARTITIONS进行修复。

       首先创建一个分区表

       接下来，使用HDFS命令创建分区文件夹，然后将数据上传至分区文件夹中。

       检查Hive是否知道我们进行的操作，如果不知道则需要使用MSCK进行修复，完成①的相关操作。

       同理，②的相关代码如下图所示

五、Hive Show语法

1. Hive show语法

（1）整体概述

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

       比如最常使用的查询当前数据库下有哪些表show tables。

（2）常用语句

【HiveSQL数据操控、查询语言(DML、DQL)】

一、 Hive SQL-DML-Load加载数据

1. Hive Load功能与语法规则

（1）回顾

       在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名；文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse;

       也可以在建表的时候使用location语句指定任意路径。

       不管路径在哪里，只有把数据文件移动到对应的表文件夹下面，Hive才能映射解析成功；

       最原始暴力的方式就是使用hadoop fs -put | -mv等方式直接将数据移动到表文件夹下；

       但是，Hive官方推荐使用Load命令将数据加载到表中。

（2）功能

       Load英文单词的含义为：加载、装载；

       所谓加载是指：将数据文件移动到与Hive表对应的位置，移动时是纯复制、移动操作。

       纯复制、移动指在数据load加载到表中时，Hive不会对表中的数据内容进行任何转换，任何操作。

（3）语法规则

语法规则之filepath：

       filepath表示待移动数据的路径。可以指向文件(在这种情况下，Hive将文件移动到表中)，也可以指向目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。

       filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：

• 1.相对路径，例如：project/datal
• 2.绝对路径，例如：/user/hive/project/datal
• 3.具有schemal的完整URI,例如：hdfs://namenode:9000/user/hive/project/datal

语法规则之LOCAL

       指定LOCAL,将在本地文件系统中查找文件路径。

       若指定相对路径，将相对于用户的当前工作目录进行解释；

       用户也可以为本地文件指定完整的URI，例如：file:///user/hive/project/datal.

      没有指定LOCAL关键字。

       如果filepath指向的是一个完整的URI,会直接使用这个URI;

       如果没有指定schema,Hive会使用在hadoop配置文件中参数fs.default.name指定的(不出意外，都是HDFS)。

LOCAL本地是哪里？

       如果对HiveServer2服务运行此命令，本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。

语法规则之OVERWRITE

       如果使用了OVERWRITE关键字，则目标表（或者分区）中的已经存在的数据会被删除，然后再将filepath指向的文件/目录中的内容添加到表/分区中。

2. 练习：使用Load加载数据到表中（Load Data from Local FS or HDFS）

• 练习Load Data From Local FS
• 练习Load Data From HDFS
• 理解Local关键字的含义
• 练习Load Dada To Partition Table

（1）建表

（2）加载数据

3. Hive3.0 Load新特性

（1）新特性 

       Hive3.0+，load加载数据时除了移动、复制操作之外，在某些场合下还会将加载重写为INSERT AS SELECT.

       Hive3.0+，还支持使用inputformat、SerDe指定输入格式，例如Text,ORC等。

       比如，如果表具有分区，则load命令没有指定分区，则将load转换为INSERT AS SELECT,并假定最后一组列为分区列，如果文件不符合预期，则报错。

（2）案例

       例子如下：本来加载的时候没有指定分区，语句是报错的，但是文件的格式符合表的结构，前两个是col1,co12,最后一个是分区字段col3,则此时会将load语句转换成为insert as select语句。

二、Hive SQL-DML-Insert插入数据

1. Hive Insert使用方式

（1）背景：RDBMS中如何使用insert

       在MySQL这样的RDBMS中，通常使用insert+values的方式来向表插入数据，并且执行速度很快。这也是RDBMS中表插入数据的核心方式。

       假如把Hive当成RDBMS,用insert+values的方式插入数据，会如何？答案是执行过程非常非常慢，原因在于底层是使用MapReduce把数据写入Hive表中。因此，如果在Hive中使用insert+values,对于大数据环境一条条插入数据，用时难以想象。

       Hive官方推荐加载数据的方式是清洗数据成为结构化文件，再使用Load语法加载数据到表中，这样的效率更高。但是并不意味insert语法在Hive中没有用武之地。

（2）insert+select

       insert+select表示：将后面查询返回的结果作为内容插入到指定表中，注意OVERWRITE将覆盖已有数据。

• 需要保证查询结果列的数目和需要插入数据表格的列数目一致。
• 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

2. Multiple Insert多重插入

       翻译为多次插入，多重插入，其核心功能是：一次扫描，多次插入。

       语法目的就是减少扫描的次数，在一次扫描中。完成多次insert操作。

3. Dynamic Partition Insert动态分区插入（与之前的分区数据加载——动态分区重合）

（1）背景

       对于分区表的数据导入加载，最基础的是通过load命令加载数据。

       在load过程中，分区值是手动指定写死的，叫做静态分区。

       假如说现在有全球224个国家的人员名单（每个国家名单单独一个文件），导入到分区表中，不同国家不同分区，如何高效实现？使用load语法导入224次？

       再假如，现在有一份名单students.txt,内容如下；要求创建一张分区表，根据最后一个字段（选修专业）进行分区，同一个专业的同学分到同一个分区中，如何实现？

（2）动态分区概述

       动态分区插入指的是：分区的值是由后续的select查询语句的结果来动态确定的。

       根据查询结果自动分区。

（3）配置参数

       在执行分区插入操作时，Hive会将最后一个字段作为分区字段，如果顺序错误则会导致分区错误。

4. Insert Directory导出数据

（1）语法格式

       Hive支持将select查询的结果导出成文件存放在文件系统中。语法格式如下；

       注意：导出操作是一个OVERWRITE覆盖操作，慎重。

       目录可以是完整的URI。如果未指定scheme，则Hive将使用hadoop配置变量fs.default.name来决定导出位置；如果使用LOCAL关键字，则Hive会将数据写入本地文件系统上的目录；

       写入文件系统的数据被序列化为文本，列之间用\001隔开，行之间用换行符隔开。如果列都不是原始数据类型，那么这些列将序列化为JSON格式。也可以在导出的时候指定分隔符换行符和文件格式。

三、Hive Transaction事务表

1. Hive事务表实现原理

       Hive的文件是存储在HDFS上的，而HDFS上又不支持对文件的任意修改，只能是采取另外的手段来完成。

• 用HDFS文件作为原始数据（基础数据），用delta保存事务操作的记录增量数据；
• 正在执行中的事务，是以一个staging开头的文件夹维护的，执行结束就是delta文件夹。每次执行一次事务操作都会有这样的一个delta增量文件夹；
• 当访问Hive数据时，根据HDFS原始文件和delta增量文件做合并，查询最新的数据。

       INSERT语句会直接创建delta目录；DELETE目录的前缀是delete_delta；UPDATE语句采用了split-update特性，即先删除、后插入；

（1）实现原理之delta文件夹命名格式

       delta_minWID_maxWID_stmtID，即delta前缀、写事务的ID范围、以及语句ID；删除时前缀是delete_delta，里面包含了要删除的文件；

       Hive会为写事务(INSERT、.DELETE等)创建一个写事务ID(Write ID)，该ID在表范围内唯一；

       语句ID(Statement ID)则是当一个事务中有多条写入语句时使用的，用作唯一标识。

      每个事务的delta文件夹下，都有两个文件：

• orc acid version的内容是2，即当前ACID版本号是2。和版本l的主要区别是UPDATE语句采用了split-update特性,即先删除、后插入。这个文件不是ORC文件，可以下载下来直接查看。
• bucket_00000文件则是写入的数据内容。如果事务表没有分区和分桶，就只有一个这样的文件。文件都以ORC格式存储，底层二级制，需要使用ORC TOOLS查看。

       operation：0表示插入，1表示更新，2表示删除。由于使用了split-update，UPDATE是不会出现的，所以delta文件中的operation是0，delete_delta文件中的operation是2。

       originalTransaction、currentTransaction：该条记录的原始写事务ID，当前的写事务ID。

       rowId：一个自增的唯一ID,在写事务和分桶的组合中唯一。

       row：具体数据。对于DELETE语句，则为null，对于INSERT就是插入的数据，对于UPDATE就是更新后的数据。

（2）合并器(Compactor)

       随着表的修改操作，创建了越来越多的delta增量文件，就需要合并以保持足够的性能。

       合并器Compactor是一套在Hive Metastore内运行，支持ACID系统的后台进程。所有合并都是在后台完成的，不会阻止数据的并发读、写。合并后，系统将等待所有旧文件的读操作完成后，删除旧文件。

       合并操作分为两种，minor compaction（小合并）、major compaction（大合并）：

• 小合并会将一组delta增量文件重写为单个增量文件，默认触发条件为10个delta文件；
• 大合并将一个或多个增量文件和基础文件重写为新的基础文件，默认触发条件为delta文件相应于基础文件占比，10%。

2. Hive事务表使用设置与局限性

（1）局限性

       虽然Hive支持了具有ACID语义的事务，但是在使用起来，并没有像在MySQL中使用那样方便，有很多限制：

• 尚不支持BEGIN,COMMIT和ROLLBACK,所有语言操作都是自动提交的；
• 表文件存储格式仅支持ORC(STORED AS ORC);
• 需要配置参数开启事务使用；
• 外部表无法创建为事务表，因为Hive只能控制元数据，无法管理数据；
• 表属性参数transactional必须设置为true;
• 必须将Hive事务管理器设置为org.apache.hadoop.hive.ql.lockmgr.DbTxnManager才能使用ACID表；
• 事务表不支持LOAD DATA..语句。

（2）设置参数

       Client端：

       服务端：

3. 案例：创建Hive事务表

       创建事务表相关代码如下：

四、Hive SQL-DML-Update、Delete更新、删除数据

1. 概述

       Hive是基于Hadoop的数据仓库，是面向分析支持分析工具。将已有的结构化数据文件映射成为表，然后提供SQL分析数据的能力。因此在Hive中常见的操作就是分析查询select操作。

       Hive早期是不支持update和delete语法的，因为Hive所处理的数据都是已经存在的的数据、历史数据。后续Hive支持了相关的update和delete操作，不过有很多约束。详见Hive事务的支持。

2. update操作

       图中的更新操作是在id=1的那一行数据中，将age列的数据改为1。

五、Hive SQL-DQL-Select查询数据

1. 完整语法树

       从哪里查询取决于FROM关键字后面的table reference。可以是普通物理表、视图、join结果或子查询结果。

       表名和列名不区分大小写。

2. 案例：美国Covid-19新冠数据之select查询

       准备一下select语法测试环境，在附件资料中有一份数据文件《us-covid19-counties.dat》
里面记录了2021-01-28美国各个县累计新冠确诊病例数和累计死亡病例数。

       数据环境准备

 3. Hive SQL select 查询基础语法

（1）select expr
       select expr表示检索查询返回的列，必须至少有一个select_expr。

       其中正则表达式这块的代码第一行需要进行属性设置，第二行的代码要求找到表中所有“c”开头的字段。

（2）ALL、DISTINCT

       用于指定查询返回结果中重复的行如何处理。

• 如果没有给出这些选项，则默认值为ALL（返回所有匹配的行）。
• DISTINCT指定从结果集中删除重复的行。

（3）WHERE

       WHERE后面是一个布尔表达式，用于查询过滤。

       在WHERE表达式中，可以使用Hive支持的任何函数和运算符，但聚合函数除外。因为聚合函数要使用它的前提是结果集已经确定。而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数。

       从Hive0.13开始，WHERE子句支持某些类型的子查询。

（4）分区查询、分区裁剪

       针对Hive分区表，在查询时可以指定分区查询，减少全表扫描，也叫做分区裁剪。

       所谓分区裁剪指：对分区表进行查询时，会检查WHERE子句或J0IN中的ON子句中是否存在对分区字段的过滤，如果存在，则仅访问查询符合条件的分区，即裁剪掉没必要访问的分区。

（5）GROUP BY

       GROUP BY语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

       注意：出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。

       原因：避免出现一个字段多个值的歧义。

• 分组字段出现select expr中，一定没有歧义，因为就是基于该字段分组的，同一组中必相同；
• 被聚合函数应用的字段，也没歧义，因为聚合函数的本质就是多进一出，最终返回一个结果。

       图中基于category进行分组，相同颜色的分在同一组中。在select_expr中，如果出现category字段，则没有问题，因为同一组中category值一样，但是返回day就有问题了，day的结果不一样。

（6）HAVING

       在SQL中增加HAVING子句原因是，WHERE关键字无法与聚合函数一起使用。

       HAVING子句可以让我们筛选分组后的各组数据，并且可以在Having中使用聚合函数，因为此时where,group by已经执行结束，结果集已经确定。

（7）HAVING与WHERE区别

• having是在分组后对数据进行过滤
• where是在分组前对数据进过滤
• having后面可以使用聚合函数
• where后面不可以使用聚合函数

（8）LIMIT

       LIMIT用于限制SELECT语句返回的行数。接受一个或两个数字参数，这两个参数都必须是非负整数常量。

       第一个参数指定要返回的第一行的偏移量(从第n行后开始/从第n+1行开始)，第二个参数指定要返回的最大行数。当给出单个参数时，它代表最大行数，并且偏移量默认为0。

4. 执行顺序

       在查询过程中执行顺序：from>where>group（含聚合）>having>order>select（首先使用where对全表数据进行过滤。然后进行group by分组，分组后每组数据有多少、边界数据集就确定了，此时可以在分组当中进行聚合操作。如果分完组还想按组进行过滤，需要使用having进行分组后过滤，因为聚合操作已完成，因此可以在having中使用聚合结果。最后，想要排序就是用order）。

       聚合语句(sum,min,max,avg,count)要比having子句优先执行

       where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)

       结合下面SQL感受：

5. Hive SQL select 查询高阶语法

（1）ORDER BY

       Hive SQL中的ORDER BY语法类似于标准SQL语言中的ORDER BY语法，会对输出的结果进行全局排序。因此当底层使用MapReduce引擎执行的时候，只会有一个reducetask执行。如果输出的行数太大，会导致需要很长的时间才能完成全局排序。

       默认排序为升序(ASC),也可以指定为DESC降序。

       在Hive2.1.O和更高版本中，支持在ORDER BY子句中为每个列指定null类型结果排序顺序。ASC顺序的默认空排序顺序为NULLS FIRST,而DESC顺序的默认空排序顺序为NULLS LAST。

（2）CLUSTER BY

       根据指定字段将数据分组，每组内再根据该字段正序排序（只能正序）。概况起来就是：根据同一个字段，分组排序。

       分组规则hash散列（分桶表规则一样）：Hash_Func(col name)%reducetask个数

       分为几组取决于reducetask的个数

       执行结果如下：分为两个部分，每个部分内正序排序

       CLUSTER BY局限性

       需求：根据sex性别分为两个部分，每个分组内再根据age年龄的倒序排序。

       CLUSTER BY无法单独完成，因为分和排序的字段只能是同一个；

       ORDER BY更不能在这里使用，因为是全局排序，只有一个输出，无法满足分的需求。

（3）DISTRIBUTE BY +SORT BY

       DISTRIBUTE BY+SORT BY就相当于把CLUSTER BY的功能一分为二：

• DISTRIBUTE BY负责根据指定字段分组；
• SORT BY负责分组内排序规则。

       分组和排序的字段可以不同。

       如果DISTRIBUTE BY+SORT BY的字段一样，则：CLUSTER BY=DISTRIBUTE BY+SORT BY

（4）CLUSTER、DISTRIBUTE、SORT、ORDER BY

• order by全局排序，因此只有一个reducer,结果输出在一个文件中，当输入规模大时，需要较长的计算时间。
• distribute by根据指定字段将数据分组，算法是hash散列。sort by是在分组之后，每个组内局部排序。
• cluster by既有分组，又有排序，但是两个字段只能是同一个字段。
• 如果distribute和sort的字段是同一个时，此时，cluster by=distribute by+sort by

（5）Union联合查询

       UNION用于将来自于多个SELECT语句的结果合并为一个结果集。

• 使用DISTINCT关键字与只使用UNION默认值效果一样，都会删除重复行。1.2.0之前的Hive版本仅支持UNION ALL,在这种情况下不会消除重复的行。
• 使用ALL关键字，不会删除重复行，结果集包括所有SELECT语句的匹配行（包括重复行）。
• 每个select statement返回的列的数量和名称必须相同。

（6）from子句中子查询(Subqueries)

       在Hive0.12版本，仅在FR0M子句中支持子查询。

       必须要给子查询一个名称，因为FROM子句中的每个表都必须有一个名称。子查询返回结果中的列必须具有唯一的名称。子查询返回结果中的列在外部查询中可用，就像真实表的列一样。子查询也可以是带有UNION的查询表达式。

       Hive支持任意级别的子查询，也就是所谓的嵌套子查询。

       Hive0.13.0和更高版本中的子查询名称之前可以包含可选关键字AS。

（7）where子句中子查询(Subqueries)

       从Hive0.13开始，WHERE子句支持下述类型的子查询：

• 1.不相关子查询：该子查询不引用父查询中的列，可以将查询结果视为I和NOTI语句的常量；
• 2.相关子查询：子查询引用父查询中的列；

6. Common Table Expressions CTE)

（1）CTE介绍

       公用表表达式(CTE)是一个临时结果集：该结果集是从WITH子句中指定的简单查询派生而来的，紧接在SELECT或INSERT关键字之前。

• CTE仅在单个语句的执行范围内定义。
• CTE可以在SELECT,INSERT,CREATE TABLE AS SELECTE或CREATE VIEW AS SELECT语句中使用。

六、Hive SQL Join连接操作

1. Hive Join语法规则

（1）join概念回顾

       根据数据库的三范式设计要求和日常工作习惯来说，我们通常不会设计一张大表把所有类型的数据都放在一起，而是不同类型的数据设计不同的表存储。

       比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。

       在这种情况下，有时需要基于多张表查询才能得到最终完整的结果；join语法的出现是用于根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据。

（2）join语法规则

       在Hive中，当下版本3.1.2总共支持6种join语法。分别是：inner join（内连接）、left join（左连接）、right join（右连接）、full outer join（全外连接）、left semi join（左半开连接）、cross join(交叉连接，也叫做笛卡尔乘积)。

       table reference:是join查询中使用的表名，也可以是子查询别名(查询结果当成表参与join).

       table factor:与table reference相同，是联接查询中使用的表名，也可以是子查询别名。

       join condition:join查询关联的条件，如果在两个以上的表上需要连接，则使用AND关键字。

（3）join语法丰富化

       Hive中join语法从面世开始其实并不丰富，不像在RDBMS中那么灵活。

       从Hive0.13.0开始，支持隐式联接表示法(请参阅HIVE-5558)。允许FROM子句连接以逗号分隔的表列表，而省略JOIN关键字。

        从Hive2.2.0开始，支持ON子句中的复杂表达式，支持不相等连接(请参阅HIVE-15211和HIVE-15251)。在此之前，Hive不支持不是相等条件的联接条件。

       图中最后一行括号中是不相等连接

2. Hive 6种Join方式详解

 （1）join查询数据环境准备

       为了更好的练习、学习掌握Hive中的join语法，下面我们去创建3张表并且加载数据到表中。

• 表1:employee 员工表；
• 表2:employee_.address 员工住址信息表；
• 表3:employee_connection 员工联系方式表

（2）inner join内连接

       内连接是最常见的一种连接，它也被称为普通连接，其中inner可以省略：inner join=join;

       只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。

（3）left join左连接

       left join中文叫做是左外连接(Left outer Join)或者左连接，其中outer可以省略，left outer join是早期的写法。

       left join的核心就在于left左。左指的是join关键字左边的表，简称左表。

       通俗解释：join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。

（4）right join右连接

       right join中文叫做是右外连接(Right Outer Jion)或者右连接，其中outer可以省略。

       right join的核心就在于Right右。右指的是join关键字右边的表，简称右表。

       通俗解释：joi时以右表的全部数据为准，左边与之关联右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回。

       很明显，right join和left join之间很相似，重点在于以哪边为准，也就是一个方向的问题。

（5）full outer join全外连接

       full outer join等价full join,中文叫做全外连接或者外连接。

       包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行，没有的填null即可。

       在功能上：等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的操作将上述两个结果集合并为一个结果集。

（6）left semi join左半开连接

       左半开连接(LEFT SEMI JOIN)会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件
       从效果上来看有点像inner join之后只返回左表的结果。

（7）cross join交叉连接

       交叉连接cross join,将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。对于大表来说，cross join慎用。

       在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积，无需指定关联键。

       在HiveSQL语法中，cross join后面可以跟where-子句进行过滤，或者on条件过滤。

3. Hive Join使用注意事项

       a) 允许使用复杂的联接表达式，支持非等值连接

       b) 同一查询中可以连接2个以上的表

       c) 如果每个表在联接子句中使用相同的列，则Hive将多个表上的联接转换为单个MR作业

       d) join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存

       e) 在join的时候，可以通过语法STREAMTABLE提示指定要流式传输的表。如果省略STREAMTABLE提示，则Hive将流式传输最右边的表。

       f) join在WHERE条件之前进行。

       g) 如果除一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行(mapjoin)。

【Hive参数配置与函数、运算符使用】

一、掌握Hive命令行及参数配置方式

1. Hive CLI（命令行接口客户端）——第一代客户端

（1） Hive CLI概述

       $HIVE HOME/bin/hive是一个shell Util,通常称之为hive的第一代客户端或者旧客户端，主要功能有两个：

• 交互式或批处理模式运行Hive查询。注意，此时作为客户端，需要并且能够访问的是Hive metastore服务，而不是hiveserver2服务。
• hive相关服务的启动，比如metastore服务。
• 可以通过运行"hive -H"或者"hive --help"来查看命令行选项。
  标记为红色的为重要的参数。

（2）Hive CLI功能

       功能一：Batch Mode批处理模式。当使用-e或-f选项运行bin/hive时，它将以批处理模式执行SQL命令。所谓的批处理可以理解为一次性执行，执行完毕退出。（客户端和服务端不会保持长久联系，一旦交互完就退出，下次运行再连接、再运行、再退出）

       功能二：Interactive Shell交互式模式。所谓交互式模式可以理解为客户端和hive服务一直保持连接，除非手动退出客户端。

       功能三：启动Hive服务。比如metastore服务和hiveserver2服务的启动。

2. Beeline CLI——第二代客户端

（1）Beeline CLI概述

       $HIVE HOME/bin/beeline被称之为第二代客户端或者新客户端，是一个JDBC客户端，是官方强烈推荐使用的Hive命令行工具。和第一代客户端相比，性能加强安全性提高。Beeline在嵌入式模式和远程模式下均可工作。

• 在嵌入式模式下，它运行嵌入式Hive(类似于Hive CLI);
• 远程模式下beeline通过Thrifti连接到单独的HiveServer2服务上，这也是官方推荐在生产环境中使用的模式。

       Beeline支持的参数非常多，可以通过官方文档进行查询。

（2）使用方式 

       常见的使用方式如下所示：

       在启动hiveserver2服务的前提下使用beeline远程连接HS2服务。

3. Configuration Properties属性配置

（1）概述

       Hive除了默认的属性配置之外，还支持用户使用时修改配置。修改Hive配置之前，作为用户需要掌握两件事：

• 有哪些属性支持用户修改，属性的功能、作用是什么。
• 支持哪种方式进行修改，是临时生效还是永久生效的。

       Hive配置属性是在HiveConf.Java类中管理的，可以参考文件以获取当前使用版中可用的配置属性列表。

       从Hive0.14.0开始，会从HiveConf.java类中直接生成配置模板文件hive-default.xml.template;
详细的配置参数大全可以参考Hive官网配置参数，在页面使用ctrl+f进行搜索。

（2）属性配置方式一：hive-site.xml

       在$HIVE_HOME/conf路径下，可以添加一个hive-site.xml文件，把需要定义修改的配置属性添加进去，这个配置文件会影响到基于这个Hive安装包的任何一种服务启动、客户端使用方式。

       比如使用MySQL作为元数据的存储介质，把连接MySQL的相关属性配置在hive-site.xml文件中，这样不管是本地模式还是远程模式启动，不管客户端本地连接还是远程连接，都将访问同一个元数据存储介质。

（3）属性配置方式二：--hiveconf命令行参数

       hiveconf:是一个命令行的参数，用于在使用Hive CLIE或者Beeline CLI的时候指定配置参数。

       这种方式的配置在整个的会话session中有效，会话结束，失效。

       比如在启动hive服务的时候，为了更好的查看启动详情，可以通过hiveconf参数修改日志级别.

（4）属性配置方式三：set命令（推荐）

       在Hive CLI或Beeline中使用set命令为set命令之后的所有SQL语句设置配置参数，这个也是会话级别的这种方式也是用户日常开发中使用最多的一种配置参数方式。

       因为Hive倡导一种：谁需要、谁配置、谁使用的一种思想，避免你的属性修改影响其他用户的修改。

（5）属性配置方式四：服务特定配置文件

       hivemetastore-site.xml、hiveserver2-site.xml

• Hive Metastore会加载可用的hive-site.xml以及hivemetastore-site.xml配置文件。
• HiveServer2会加载可用的hive-site.xml以及hiveserver2-site.xml.

       如果HiveServer2以嵌入式模式使用元存储，则还将加载hivemetastore-site.xml。

（6）总结——属性配置方式优先级

       set设置>hiveconfa参数>hive-site.xml配置文件

• set参数声明会覆盖命令行参数hiveconf，命令行参数会覆盖配置文件hive-site.xml设定
• 日常开发使用中，如果不是核心的需要全局修改的参数属性，建议使用set命令进行设置
• 另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置

二、理解Hive内置运算符的使用

1. Hive内置运算符

（1）概述

       整体上，Hive支持的运算符可以分为三大类：关系运算、算术运算、逻辑运算。

       官方参考文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

       也可以使用下述方式查看运算符的使用方式。

        还有一种方式是在Hive中创建一个空表，测试各种运算符的功能。

（2）关系运算符

       关系运算符是二元运算符，执行的是两个操作数的比较运算。每个关系运算符都返回boolean类型结果(TRUE或FALSE)。

       其中'itcast'表示字符串，第一行where判断为false，因此前面的表达式不再返回。下面几行同理。

       rlike表示匹配正则表达式，其中第一行'^i.*t$'中，^代表开始匹配正则表达式，$代表正则表达式的结尾，i表示第一个字符是i，.表示任意字符，*表示前面的字符出现多次，t表示结尾是t。完整意思是开始是i，结尾是t，中间有多个字符。

       第二行中\反斜杠转义，\\d表示数字，+表示多个，完整意思是数字有多个

（3）算术运算符

      算术运算符操作数必须是数值类型。分为一元运算符和二元运算符：一元运算符，只有一个操作数；二元运算符有两个操作数，运算符在两个操作数之间。

       其中，位与操作可以理解为且操作，同一位置两个都为1结果才是1，例如第一行返回0000，结果为0，第二行返回0100，结果是4。同理，位或操作可以理解为或操作，同一位置只要有一个是1结果就是1。

（4）逻辑运算符

• 与操作：A  AND B
• 或操作：A OR B
• 非操作：NOT A、!A
• 在：A IN(val1, val2,.)
• 不在：A NOT IN(val1,val2,.)
• 逻辑是否存在：[NOT]EXISTS(subquery)

       [NOT]EXISTS

       语法：SELECT.FROM table WHERE[NOT]EXISTS(subquery).
       功能：将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留。

三、掌握Hive常见的内置函数使用

1. Hive函数概述及分类标准

（1）概述

       Hive内建了不少函数，用于满足用户不同使用需求，提高SQL编写效率：

• 使用show functionsi查看当下可用的所有函数；
• 通过describe function extended funcname来查看函数的使用方式。

（2）分类标准

       Hive的函数分为两大类：内置函数(Built-in Functions)、用户定义函数UDF(User-Defined Functions)

• 内置函数可分为：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；
• 用户定义函数根据输入输出的行数可分为3类：UDF、UDAF、UDTF。

（3）用户定义函数UDF分类标准

       根据函数输入输出的行数：

• UDF(User-Defined-Function)普通函数，一进一出
• UDAF(User-Defined Aggregation Function)聚合函数，多进一出，count、sum
• UDTF(User-Defined Table-Generating Functions)表生成函数，一进多出

（4）UDF分类标准扩大化

       UDF分类标准本来针对的是用户自己编写开发实现的函数。UDF分类标准可以扩大到Hive的所有函数中：包括内置函数和用户自定义函数。因为不管是什么类型的函数，一定满足于输入输出的要求，那么从输入几行和输出几行上来划分没有任何问题。

       干万不要被UD(User-Defined)这两个字母所迷惑，照成视野的狭隘。

       比如Hive官方文档中，针对聚合函数的标准就是内置的UDAF类型。

2. Hive内置函数

（1）概述

       内置函数(build-in)指的是Hive开发实现好，直接可以使用的函数，也叫做内建函数。

       官方文档地址：https:/cwiki.apache.org/confluence,/display/Hive/LanguageManual+UDF

       内置函数根据应用归类整体可以分为8大种类型，接下来主要了解重要的，使用频率高的函数使用。

（2）String Functions字符串函数

       带分隔符字符串连接函数得到的结果是www.itcast.cn。

       正则表达式替换函数中三个参数分别是待替换的字符串、替换的正则表达式（指明需要替换的内容）、替换的内容。因此这行语句的内容是将“100-200”中的所有数字部分替换成“num”，返回num-num。

       正则表达式解析函数中三个参数分别是待提取的字符串、分组的正则表达式（指出对待提取的字符串进行分组的方式，括号括起来的代表一个分组）、提取第几个分组。因此这行语句的内容是将“100-200”分为100和200两组，提取第2个分组，返回200。

       分割字符串函数中'\\s+'的第一个\表示转义、\s+表示任意单个空白字符（空格、字表符、换页符）

（3）Date Functions日期函数

（4）Mathematical Functions数学函数

（5）Collection Functions集合函数

（6）Conditional Functions条件函数

       主要用于条件判断、逻辑判断转换这样的场合

       if条件判断的意思是如果测试条件（第一个参数）为true则返回第二个值（第二个参数），否则返回第三个值（第三个参数）。

       空值转换函数的意思是，如果第一个参数为空值，则将该空值转换为第二个参数，如果第一个参数不为空值，则不进行转换。

（7）Type Conversion Functions类型转换函数

       主要用于显式的数据类型转换：

（8）Data Masking Functions数据脱敏函数

       主要完成对数据脱敏转换功能，屏蔽原始数据，主要如下：

（9）Misc. Functions其他杂项函数

3. Hive用户自定义函数(UDP、UDTF、UDAF)

（1）UDF普通函数

• 特点是一进一出，也就是输入一行输出一行。
• 比如roud这样的取整函数，接收一行数据，输出的还是一行数据。

（2）UDAF聚合函数

• A所代表的单词就是Aggregation聚合的意思。
• 多进一出，也就是输入多行输出一行。
• 比如count、sum这样的函数。

（3）UDTF表生成函数

• T所代表的单词是Table-Generating表生成的意思。
• 特点是一进多出，也就是输入一行输出多行。
• 这类型的函数作用返回的结果类似于表，同时，UDTF函数也是我们接触比较少的函数。
• 比如explode函数。

4. 案例：UDF实现手机号***加密

（1）案例背景

       在企业中处理数据的时候，对于敏感数据往往需要进行脱敏处理。比如手机号。我们常见的处理方式是将手机号中间4位进行****处理。

       Hive中没有这样的函数可以直接实现功能，虽然可以通过各种函数的嵌套调用最终也能实现，但是效率不高，现要求自定义开发实现Hive函数，满足上述需求。

• 能够对输入数据进行非空判断、手机号位数判断
• 能够实现校验手机号格式，把满足规则的进行****处理
• 对于不符合手机号规则的数据直接返回，不处理

（2）UDF实现步骤

• 1. 写一个java类，继承UDF,并重载evaluate方法，方法中实现函数的业务逻辑；
• 2. 重载意味着可以在一个java类中实现多个函数功能；
• 3. 程序打成jar包，上传S2服务器本地或者HDFS;
• 4. 客户端命令行中添加jar包到Hive的classpath:hive>add JAR/xxxx/udf.jar;
• 5. 注册成为临时函数(给UDF命名)：create temporary function函数名 as 'UD类全路径'；
• 6. HQL中使用函数。

（3）开发环境准备

       IDEA中创建Maven工程，添加下述pom依赖，用于开发Hive UDF，完整pom.xml请参考课程附件资料。

（4）步骤1：编写业务代码

（5）步骤2：打jar包上传

       IDEA中使用集成的Maven插件进行打包，这里会把依赖一起打入jar包。

（6）步骤3: jar包上传HS2服务器本地

       把jar包上传到Hiveserver2服务运行所在机器的Linux系统，上传HDFS文件系统也可以，后续路径指定清楚即可。

（7）步骤4：添加jar至Hive Classpath

       在客户端中使用命令把jar包添加至classpath.。

（8）步骤5：注册临时函数

       通俗来说就是给用户编写的函数起个名字

四、理解UDTF函数、Lateral View侧视图

1. Hive UDTF之explode函数

（1）功能介绍

• explode接收map、array类型的数据作为输入，然后把输入数据中的每个元素拆开变成一行数据，一个元素一行。
• explode执行效果正好满足于输入一行输出多行，所有叫做UDTF函数。

• 一般情况下，explode函数可以直接单独使用，也可以根据业务需要结合lateral view侧视图一起使用。
• explode(array)将array里的每个元素生成一行；
• explode (map)将map里的每一对元素作为一行，其中key为一列，valuei为一列；

（2）练习：NBA总冠军球队名单分析

• 1.练习explodel函数的使用
• 2.感悟什么叫做UDTF表生成函数
• 3.发现UDTF函数使用限制

       背景：有一份数据《The NBA Championship.txt》，关于部分年份的NBA总冠军球队名单。第一个字段表示球队名称，第二个字段是获取总冠军的年份。字段之间以，分割，总冠军年份之间 | 以进行分割。

       需求：使用Hive建表映射成功数据，对数据拆分，要求拆分之后数据如下所示：

       fields terminated by '，'  是指字段与字段之间的分隔符为“，”
       collection items terminated by '|'  是指复杂类型（array,struct)字段的各个item之间的分隔符为 “|”

       在select条件中，如果只有explode函数表达式，程序执行是没有任何问题的。但是如果在select条件中，包含explode和其他字段，就会报错。在select的时候，explode的旁边不支持其他字段的同时出现（UDTF's are not supported outside the SELECT clause,nor nested in expressions），理解这个问题需要知道UDTF的语法限制。

（3）UDTF语法限制

       explode函数属于UDTF表生成函数，explode执行返回的结果可以理解为一张虚拟的表，其数据来源于源表。

        在select中只查询源表数据没有问题，只查询explode生成的虚拟表数据也没问题，但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段；通俗点讲，有两张表，不能只查询一张表但是又想返回分别属于两张表的字段；

（4）UDTF语法限制解决

• 1.从SQL层面上来说上述问题的解决方案是：对两张表进行join关联查询；
• 2.Hive专门提供了语法lateral View侧视图，专门用于搭配explodei这样的UDTF函数，以满足上述需要。

2. Hive Lateral View侧视图

（1）概念

       Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。

       一般只要使用UDTF,就会固定搭配lateral view使用。

       官方链接：https://cwiki.apache.org/confluence,/display/Hive/LanguageManual+LateralView

（2）原理

       将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。

       使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by、limits等语句中，不需要再单独嵌套一层子查询。

（3）Lateral View UDTF

       针对explode案例中NBA冠军球队年份排名案例，使用explode函数+lateral view侧视图，可以完美解决：

       UDTF(xxx)表示一个UDTF函数生成的一个虚拟的表，“别名”是指对这个虚拟的表起一个别名。as 后面的 col1,col2,col3…是指对虚拟表中的字段起别名。

五、掌握Hive中基础聚合、理解增强聚合

1. Hive Aggregation基础聚合函数

（1）概述

• 聚合函数的功能是：对一组值执行计算并返回单一的值。
• 聚合函数是典型的输入多行输出一行，使用Hive的分类标准，属于UDAF类型函数。
• 通常搭配Group By语法一起使用，分组后进行聚合操作。

（2）基础聚合

       HQL提供了几种内置的UDAF聚合函数，例如max(…),min(…)和avg(…)。这些可以称为基础的聚合函数。

       通常情况下聚合函数会与GROUP BY子句一起使用。如果未指定GROUP BY子句，默认情况下，它会汇总所有行数据。

        如果val1数据不为空，coalesce会返回原本的值，如果为空值，coalesce会将空值转换为0。

2. Hive Aggregation增强聚合函数

（1）增强聚合

       增强聚合包括grouping_sets、cube、rollup这几个函数；主要适用于OLAP多维数据分析模式中，多维分析中的维指的分析问题时看待问题的维度、角度。

       下面通过案例更好的理解函数的功能含义。数据中字段含义：月份、天、用户标识cookieid。

（2）增强聚合--grouping sets

       grouping sets是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的GROUP BY结果集进行UNION ALL。GROUPING_ID表示结果属于哪一个分组集合。

（3）增强聚合--cube

       cube表示根据GROUP BY的维度的所有组合进行聚合。

       对于cube来说，如果有n个维度，则所有组合的总个数是：2的n次方

       如cube有a,b,c 3个维度，则所有组合情况是：(a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),()

（4）增强聚合--rollup

       cube的语法功能指的是：根据GROUP BY的维度的所有组合进行聚合。

       rollup是cube的子集，以最左侧的维度为主，从该维度进行层级聚合。

       比如ROLLUP有a,b,c 3个维度，则所有组合情况是：(a,b,c),(a,b),(a),（）

六、掌握Hive中的窗口函数使用

1. Hive Windows Functions窗口函数

（1）概述

      窗口函数(Window functions)也叫做开窗函数、OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。如果函数具有OVER子句，则它是窗口函数。

       窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。

（2）直观感受

       通过sum聚合函数进行普通常规聚合和窗口聚合，来直观感受窗口函数的特点。

（3）语法规则

2. 练习：网站用户页面浏览次数分析

       在网站访问中，经常使用cookie来标识不同的用户身份，通过cookie可以追踪不同用户的页面
访问情况。通过用户在网站的访问数据学习Hive中窗口函数的相关语法知识。

（1）原始数据

       有下面两份数据：

       字段含义：cookieid、访问时间、pv数（页面浏览数）

       字段含义：cookieid、访问时间、访问页面url

（2）建表加载数据

（3）窗口聚合函数

       所谓窗口聚合函数指的是sum、max、min、avg这样的聚合函数在窗口中的使用；

       这里以sum函数为例，其他聚合函数使用类似。

3. 窗口表达式

       在sum(..)over(partition by..order by..)语法完整的情况下，进行累积聚合操作，默认累积聚合行为是从第一行聚合到当前行。

       Window expressioni窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行。

        语法如下：

4. 窗口排序函数

（1） row number家族

• row number:在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复；
• rank:在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；
• dense rank:在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；

（2）ntile

       将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号。

       如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

       有时会有这样的需求：如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢？NTILE函数即可以满足。

5. 窗口分析函数

       LAG(col,n,DEFAULT)用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行(可选，默认为1)，第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL);

       LEAD(col,n,DEFAULT)用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行(可选，默认为1)，第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL)。

       FIRST_VALUE取分组内排序后，截止到当前行，第一个值。

       LAST_VALUE取分组内排序后，截止到当前行，最后一个值。

七、了解Hive Sampling抽样函数

1. Hive Sampling抽样函数

• 当数据量过大时，我们可能需要查找数据子集以加快数据处理速度分析。
• 这就是抽样、采样，一种用于识别和分析数据中的子集的技术，以发现整个数据集中的模式和趋势。
• 在HQL中，可以通过三种方式采样数据：随机采样，存储桶表采样和块采样。

2. Random随机抽样

• 随机抽样使用rand（）函数来确保随机获取数据，LIMIT来限制抽取的数据个数。
• 优点是随机，缺点是速度不快，尤其表数据多的时候。
• 推荐DISTRIBUTE+SORT,可以确保数据也随机分布在mapper和reducer之间，使得底层执行有效率。
• ORDER BY语句也可以达到相同的目的，但是表现不好，因为ORDER BY是全局排序，只会启动运行一个reducer。

3. 块抽样

• Block块采样允许随机获取行数据、百分比数据或指定大小的数据。
• 采样粒度是HDFS块大小。
• 优点是速度快，缺点是不随机。

4. Bucket table基于分桶表抽样

       这是一种特殊的采样方法，针对分桶进行了优化。优点是既随机速度也很快。语法如下：

       TABLESAMPLE (BUCKET X OUT OF y [ON colname])

【Hive函数重要应用案例】

一、Hive中多字节分隔符处理

1. Hive中的分隔符

（1）默认规则

       Hive默认序列化类是LazySimpleSerDe,其只支持使用单字节分隔符(char)来加载文本数据，例如逗号、制表符、空格等等，默认的分隔符为”\001”。根据不同文件的不同分隔符，我们可以通过在创建表时使用row format delimited来指定文件中的分割符，确保正确将表中的每一列与文件中的每一列实现一一对应的关系。

（2）案例：单字节分隔符数据加载示例

2. 问题与需求

（1）特殊数据

       情况一：每一行数据的分隔符是多字节分隔符，例如：“||”、“--”等

       情况二：数据的字段中包含了分隔符

       上图中每列的分隔符为空格，但是数据中包含了分割符，时间字段中也有空格

（2）问题：数据加载不匹配

       情况一：每一行数据的分隔符是多字节分隔符，例如“||”、“--”等，尝试直接使用LazysimpleSerDe处理。

       情况二：数据的字段中包含了分隔符

3. 解决方案一：替换分隔符

（1）方案概述

       使用程序提前将数据中的多字节分隔符替换为单字节分隔符

（2）程序开发

       使用MR程序提前将数据中的多字节分隔符替换为单字节分隔符

（3）重新建表加载

       指定分隔符为“|”

4. 解决方案二：RegexSerDe正则加载（推荐）

        Hive内置的SerDe除了使用最多的LazySimpleSerDe，Hive该内置了很多SerDe类；

       官网地址：https://cwiki.apache.org/confluence/display/Hive/SerDe

       多种SerDe用于解析和加载不同类型的数据文件，常用的有ORCSerDe、RegexSerDe、JsonSerDe等。

（1）方法概述

      RegexSerDe用来加载特殊数据的问题，使用正则匹配来加载数据。

       根据正则表达式匹配每一列数据。

https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-ApacheWeblogData

（2）RegexSerDel解决多字节分隔符

       分析数据格式，构建正则表达式

       正则表达式中，([0-9]*)表示多个0到9的数字，\\|\\|表示多字节分隔符“||”，(.*)表示多个任意字符。如果不太会正则表达式的编写操作，可以在网上寻找能够检查正则表达式的网页，如果能找到帮你编写正则表达式的网页就更好了。

（3）重新建表加载

       根据之前的第一份数据，基于正则表达式，使用RegexSerde建表

       查看结果，数据可以正常加载匹配

       针对第二份数据，分析数据格式，构建正则表达式

       ^是指匹配输入字符串的开始位置，除非在方括号表达式中使用，当该符号在方括号表达式中使用时，表示不接受该方括号表达式中的字符集合。要匹配^字符本身，请使用“\^”。

       基于正则表达式，使用RegexSerde建表

5. 解决方案三：自定义InputFormat（不推荐）

（1）方案概述
       Hive中也允许使用自定义InputFormat来解决以上问题，通过在自定义InputFormat,来自定义解析逻辑实现读取每一行的数据。

（2）自定义InputFormat

     与MapReudcet中自定义InputFormat一致，继承TextInputFormat

（3）自定义RecordReader

       与MapReudce中自定义RecordReader一致，实现RecordReader接口，实现next方法

（4）添加自定义InputFormat到Hive中

       打成jar包，添加到Hive的classpath中（将jar包拖到Hive服务器中）

 （5）

       通过指定inutformat包路径，让Hive在解析singer表对应的数据时用之前自定义的inutformat进行解析。

       写数据时，还是使用自带的outformat

6. 总结

       当数据文件中出现多字节分隔符或者数据中包含了分隔符时，会导致数据加载与实际表的字段不匹配的问题，基于这个问题我们提供了三种方案：

• 替换分隔符
• 正则加载RegexSerde
• 自定义InputFormat

       其中替换分隔符无法解决数据字段中依然存在分隔符的问题，自定义InputFormat的开发成本较高，所以整体推荐使用正则加载的方式来实现对于特殊数据的处理。

二、URL解析函数

1. 实际工作需求

       业务需求中，经常需要对用户的访问、用户的来源进行分析，用于支持运营和决策。

       例如对用户访问的页面进行统计分析，分析热门受访页面的Top10，观察大部分用户最喜欢的访问最多的页面等。

       又或者需要分析不同搜索平台的用户来源分析，统计不同搜索平台中进入网站的用户个数，根据数据进行精准的引导和精准的广告投放等

2. URL的基本组成

（1）数据格式

       要想实现上面的受访分析、来源分析等业务，必须在实际处理数据的过程中，对用户访问的URL和用户的来源URL进行解析处理，获取用户的访问域名、访问页面、用户数据参数、来源域名、来源路径等信息。

（2）URL的基本组成

       在对URL进行解析时，我们要先了解URL的基本组成部分，再根据实际的需求从URL中获取对应的部分，例如一条URL由以下几个部分组成。

3. Hive中的URL解析函数

（1）函数

       Hive中为了实现对URL的解析，专门提供了解析URL的函数parse_url和oarse_url_tuple;
在show functions中可以看到对应函数。

（2）案例：从URL中获取每个ID对应HOST、PATH以及QUERY

4. parse url函数

（1）功能

        parse url函数是Hive中提供的最基本的url解析函数，可以根据指定的参数，从URL解析出对应的参数值进行返回，函数为普通的一对一函数类型。

（2）语法

（3）示例

（4）parse url的弊端

       如果想一次解析多个参数，需要使用多次函数

5. parse url tuple函数

（1）功能
       parse_url_tuple函数是Hive中提供的基于parse_url的url解析函数，可以通过一次指定多个参数，从URL解析出多个参数的值进行返回多列，函数为特殊的一对多函数类型，即通常所说的UDTF函数类型。

（2）语法

（3）建表加载数据

（4）parse url tuple的问题

       当执行以下SQL语句，将id与host、path、query等共同查询时报错（因为该函数属于UDTF函数，生成的是虚拟表，不能只引用源表但是查询源表和虚拟表的数据）

       错误

       Hive中的一对多的UDTF函数可以实现高效的数据转换，但是也存在着一些使用中的问题，UDTF函数对于很多场景下有使用限制，例如：select时不能包含其他字段、不能嵌套调用、不能与group bys等放在一起调用等等。

       UDTF函数的调用方式，主要有以下两种方式：

• 方式一：直接在select后单独使用
• 方式二：与Lateral View放在一起使用

（5）和Lateral View侧视图共同使用

       测试1：单个lateral view调用

       测试2：多个lateral view调用

       测试3：UDTF不产生数据的情况

三、行列转换应用与实现

1. 工作应用场景

       统计得到每个小时的UV、PV、IP的个数，构建如下的表结构：

       但是表中的数据存储格式不利于直接查询展示，需要进行调整

2. 行转列：多行转多列

（1）案例：实现多行转多列

（2）case when函数

       功能：用于实现对数据的判断，根据条件，不同的情况返回不同的结果，类似于Java中的switch case功能

       语法：

       语法一

       语法二

（3）实操

       建表并加载数据

       首先基于第1列进行分组，在每组内使用case when函数，第2列的值为c就将第3列的值拿出来并将该列命名为c，第2列的值为d就将第3列的值拿出来并将该列命名为d，第2列的值为e就将第3列的值拿出来并将该列命名为e。

3. 行转列：多行转单列

（1）案例：多行转单列

（2）concat函数

       功能：用于实现字符串拼接，不可指定分隔符

       语法：concat (element1,element2,element3......)

       测试：

       特点：如果任意一个元素为null,结果就为null

（3）concat_ws函数

       功能：用于实现字符串拼接，可以指定分隔符

       语法：concat_ws (SplitChar, element1, element2......）

       测试：

       特点：任意一个元素不为null，结果就不为null

（4）collect_list函数

       功能：用于将一列中的多行合并为一行，不进行去重

       语法：collect_list (colName)

       测试：

（5）collect_set函数

       功能：用于将一列中的多行合并为一行，并进行去重

       语法：collect_set (colName)

       测试：

（6）实操

       因为concat_ws函数接收str类型的数据，因此需要将col3的数据从int类型转换成str类型。

4. 列转行：多列转多行

（1）案例：多列转多行

（2）union关键字

       功能：将多个selecti语句结果合并为一个，且结果去重且排序

       语法：

       测试：

（3）union all关键字

       功能：将多个select语句结果合并为一个，且结果不去重不排序

       语法：

select_statement UNION ALL select_statement UNION ALL select_statement ..

       测试：

（4）实操

       首先建表加载数据

       接下来开始行列转换

5. 列转行：单列转多行

（1）案例：单列转多行

（2）explode函数

       功能：用于将一个集合或者数组中的每个元素展开，将每个元素变成一行

       语法：explode （Map | Array)

       测试：

（3）实操

       建表并加载语句

       使用explode函数进行单列转多行

四、JSON数据处理

1. 应用场景

（1）JSON格式

       JSON数据格式是数据存储及数据处理中最常见的结构化数据格式之一，很多场景下公司
都会将数据以JSON格式存储在HDFS中，当构建数据仓库时，需要对JSON格式的数据进行处理
和分析，那么就需要在Hive中对JSON格式的数据进行解析读取。

（2）案例需求

        对以下JSON数据实现处理，解析每个字段到表中

2. 处理方式

（1）两种处理方式

       Hive中为了实现JSON格式的数据解析，提供了两种解析JSON数据的方式，在实际工作场景下，可以根据不同数据，不同的需求来选择合适的方式对JSON格式数据进行处理。

（2）JSON函数：get_json_object

       功能：用于解析JSON字符串，可以从JSON字符串中返回指定的某个对象列的值

       语法：get_json_object (json_txt,path) - Extract a json object from path

       参数：

• 第一个参数：指定要解析的JSON字符串
• 第二个参数：指定要返回的字段，通过$.columnName的方式来指定path

       特点：每次只能返回JSON对象中一列的值

       在Hive中没有JSON数据格式，通常将JSON保存为字符串格式，叫做JSON串 

       使用get_json_object函数解析JOSN字符串

（3）JSON函数：json_tuple

       功能：用于实现JSON字符串的解析，可以通过指定多个参数来解析JSO返回多列的值

       语法：

json_tuple(jsonStr,pl,p2,...,pn)   like get_json_object, but it takes multiple names and return a tuple

       参数：

• 第一个参数：指定要解析的JS0字符串
• 第二个参数：指定要返回的第1个字段
• ……
• 第N+1个参数：指定要返回的第N个字段

       特点：功能类似于get_json_object，但是可以调用一次返回多列的值，属于UDTF类型函数，一般搭配lateral view使用返回的每一列都是字符串类型

（4）JSONSerde

       功能：上述解析JSON的过程中是将数据作为一个JSON字符串加载到表中，再通过JSON解析函数对JSON字符串进行解析，灵活性比较高，但是对于如果整个文件就是一个JSON文件，在使用起来就相对比较麻烦。

       Hive中为了简化对于JSON文件的处理，内置了一种专门用于解析JSON文件的Serde解析器，在创建表时，只要指定使用JSONSerdel解析表的文件，就会自动将JSON文件中的每一列进行解析。

       建表

3. 总结

       不论是Hive中的JSON函数还是自带的JSONSerde都可以实现对于JSON数据的解析，工作中一般根据数据格式以及对应的需求来实现解析。

       如果数据中每一行只有个别字段是JSON格式字符串，就可以使用JSON函数来实现处理.

       如果数据加载的文件整体就是JSON文件，每一行数据就是一个JSON数据，那么建议直接使用JSONSerde来实现处理最为方便。

五、窗口函数应用实例

1. 案例1：连续登录用户统计

（1）需求：统计连续N次登陆的用户(N>=2)

       当前有一份用户登录数据如下图所示，数据中有两个字段，分别是userId和loginTime。

       userId表示唯一的用户ID，唯一标识一个用户loginTime表示用户的登录日期，例如第一条数据就表示A在2021年3月22日登录了。

（2）实现方案分析

       基于以上的需求根据数据寻找规律，要想得到连续登陆用户，必须找到两个相同用户ID的行之间登陆日期之间的关系。例如：统计连续登陆两天的用户，只要用户ID相等，并且登陆日期之间相差1天即可。基于这个规律，我们有两种方案可以实现该需求。

• 方案一：表中的数据自连接，构建笛卡尔积
• 方案二：使用窗口函数来实现

       数据准备：建表并加载数据

（3）方案一：自连接过滤实现

       a. 构建笛卡尔积

       b. 根据规律过滤数据得到连续两天的登录信息

       c. 查询连续两天登录的用户ID

       如果用这种自连接找规律的方式，如何实现连续三天的登录统计？连续四天？五天？一个月？

（4）方案二：使用窗口函数lead来实现

       功能：用于从当前数据中基于当前行的数据向后偏移取值

       语法：lead(colName,N,defautValue)

• colName:取哪一列的值
• N:向后偏移N行
• defaultValue:如果取不到返回的默认值

       分析：我们可以基于用户的登陆信息，找到如下规律：

• 连续两天登陆：用户下次登陆时间=本次登陆以后的第二天
• 连续三天登陆：用户下下次登陆时间=本次登陆以后的第三天
• 我们可以对用户ID进行分区，按照登陆时间进行排序，通过lead函数计算出用户下次登陆时间
• 通过日期函数计算出登陆以后第二天的日期，如果相等即为连续两天登录。

       a. 连续两天登录的实现

       b. 实现连续两天登录的完整代码

       c. 实现连续三天登录的完整代码

       由此可知，窗口函数可以实现连续登录N天的计算。

2. 案例2：级联累加求和

（1）需求：统计每个用户每个月的消费总金额以及当前累计消费总金额

       当前有一份消费数据如下，记录了每个用户在每个月的所有消费记录，数据表中一共有三列

• userId:用户唯一id,唯一标识一个用户
• mth：用户消费的月份，一个用户可以在一个月多次消费
• money:用户每次消费的金额

（2）实现方案分析

       如果要实现以上需求，首先要统计出每个用户每个月的消费总金额，分组实现聚合，但是需要按照用户ID，将该用户这个月之前的所有月份的消费总金额进行累加实现。

       该需求可以通过两种方案来实现：

• 方案一：分组统计每个用户每个月的消费金额，然后构建自连接，根据条件分组聚合
• 方案二：分组统计每个用户每个月的消费金额，然后使用窗口聚合函数实现

       数据准备：建表并加载数据

（3）方案一：自连接过滤实现

       这种方法不易理解且较为麻烦，不推荐

（4）方案二：窗口函数实现

       功能：用于实现基于窗口的数据求和

       语法：sum(colName) over (partition by col order by col)

                  colName:对某一列的值进行求和

       分析：基于每个用户每个月的消费金额，可以通过窗口函数对用户进行分区，按照月份排序
然后基于聚合窗口，从每个分区的第一行累加到当前行即可得到累计消费金额。

       统计每个用户每个月消费金额及累计总金额：

       想要实现只计算前最近三个月的累计消费金额，可以使用rows between来控制累积的行范围。

3. 案例3：分组TopN

（1）案例需求：统计查询每个部门薪资最高的前两名员工的薪水

       现在有一份数据如下，记录所有员工的信息

（2）实现方案分析

       根据上述需求，这种情况下是无法根据group by分组聚合实现的，因为分组聚合只能实现返回一条聚合的结果，但是需求中需要每个部门返回薪资最高的前两名，有两条结果，这时候就需要用到窗口函数中的分区来实现了。

       数据准备：建表并加载数据

（3）窗口函数实现 

       TopN函数：row number、rank、dense rank

• row_number:对每个分区的数据进行编号，如果值相同，继续编号
• rank：对每个分区的数据进行编号，如果值相同，编号相同，但留下空位
• dense_rank：对每个分区的数据进行编号，如果值相同，编号相同，不留下空位

       基于row number实现，按照部门分区，每个部门内部按照薪水降序排序

六、拉链表的设计与实现

1. 数据同步问题

（1）背景

       Hive在实际工作中主要用于构建离线数据仓库，定期的从各种数据源中同步采集数据到Hive中，经过分层转换提供数据应用。例如每天需要从MySQL中同步最新的订单信息、用户信息、店铺信息等到数据仓库中，进行订单分析、用户分析。

       例如：MySQL中有一张用户表：tb user，每个用户注册完成以后，就会在用户表中新增该用户的信息，记录该用户的id、手机号码、用户名、性别、地址等信息。

• 每天都会有用户注册，产生新的用户信息
• 每天都需要将MySQL中的用户数据同步到Hive数据仓库中
• 需要对用户的信息做统计分析，例如统计新增用户的个数、用户性别分布、地区分布、运营商分布等指标

（2）问题：如果已经同步的数据发生变化怎么办？

       2021-01-01:MySQL中有10条用户信息

       2021-01-02:Hive进行数据分析，将MySQL中的数据同步

       2021-01-02:MySQL中新增2条用户注册数据，并且有1条用户数据发生更新

       新增两条用户数据011和012，008的3ddr发生了更新，从gz更新为sh

       2021-01-03:Hive需要对2号的数据进行同步更新处理

       问题：新增的数据会直接加载到Hive表中，但是更新的数据如何存储在Hive表中？

（3）解决方案

       方案一：在Hive中用新的addr覆盖008的老的addr,直接更新 

       优点：实现最简单，使用起来最方便

       缺点：没有历史状态，008的地址是1月2号在sh，但是1月2号之前是在gz的，如果要查询008的1月2号之前的addr就无法查询，也不能使用sh代替

       方案二：每次数据改变，根据日期构建一份全量的快照表，每天一张表

       优点：记录了所有数据在不同时间的状态

       缺点：冗余存储了很多没有发生变化的数据，导致存储的数据量过大

       方案三：构建拉链表，通过时间标记发生变化的数据的每种状态的时间周期 

2. 拉链表的设计

（1）功能与应用场景

       拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题。

       拉链表的设计是将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同时间上的所有状态，通过时间进行标记每个状态的生命周期，查询时，根据需求可以获取指定时间范围状态的数据，默认用9999-12-31等最大值来表示最新状态。

（2）实现过程

       Step1:增量采集变化数据，放入增量表中

       Step2:将Hive中的拉链表与临时表的数据进行合并，合并结果写入临时表

       Step3:将临时表的数据覆盖写入拉链表中

3. 拉链表的实现

       Step1:创建拉链表并加载数据

       Step2:创建增量表并加载数据

       Step3:创建临时表

       Step4:合并历史拉链表与增量表

       其中left join的过程如下图所示

       b.userid is null表示增量表中没有该用户的更改数据（及该用户没有发生变更）。

       a.endtime<'9999-12-31'表示这行数据已经发生了变更（这条数据并不代表用户这次更改的前一次的状态，至少是上上次的状态，用户这次更改的前一次的状态用endtime='9999-12-31'进行表示）。

       date_sub(b.starttime,1)表示输出增量表中的starttime减去1天的日期，表示用户上一个状态的截止日期

【Hive性能优化及Hive3新特性】

一、Hive表设计优化

1. 分区表结构设计

（1）Hive查询基本原理

       Hive的设计思想是通过元数据解析描述将HDFS上的文件映射成表；

       基本的查询原理是当用户通过HQL语句对Hive中的表进行复杂数据处理和计算时，默认将其转换为分布式计算MapReduce程序对HDFS中的数据进行读取处理的过程。

       在Hive中创建数据库、数据库下再创建一张表tb_login并加载数据到表中。

       HDFS中自动在Hive数据仓库的目录下和对应的数据库目录下，创建表的目录

       加载数据后，数据会自动被关联到表对应的HDFS的目录中

       数据可以正常被查询

       当执行查询计划时，Hive会使用表的最后一级目录作为底层处理数据的输入

       Step1：先根据表名在元数据中进行查询表对应的HDFS目录

       Step2：然后将整个HDFS中表的目录作为底层查询的输入，可以通过explain命令查看执行计划依赖的数据

（2）普通表结构问题

       假设每天有1G的数据增量，一年就是365GB的数据，按照业务需求，每次只需要对其中一天的数据进行处理，也就是处理1GB的数据；

       程序会先加载365GB的数据，然后将364GB的数据过滤掉，只保留一天的数据再进行计算，导致了大量的磁盘和网络的I0的损耗。

（3）分区表结构——分区设计思想

       Hive提供了一种特殊的表结构来解决一一分区表结构。分区表结构的设计思想是：根据查询的需求，将数据按照查询的条件【一般以时间】进行划分分区存储，将不同分区的数据单独使用一个HDFS目录来进行存储，当底层实现计算时，根据查询的条件，只读取对应分区的数据作为输入，减少不必要的数据加载，提高程序的性能。

       上面的案例中，按照登陆日期进行分区存储到Hive表中，每一天一个分区，在HDFS的底层就可以自动实现将每天的数据存储在不同的目录中。

2. 案例：基于分区表的设计实现将所有用户的登录信息进行分区存储

        将所有登陆数据写入分区表，分区存储

       HDFS中会自动在表的目录下，为每个分区创建一个分区目录

       查询2021-03-23或者2021-03-24的数据进行统计

       Hive查询时先检索元数据，元数据中记录该表为分区表并且查询过滤条件为分区字段，所以找到该分区对应的HDFS目录

       查看执行计划

       如果不做分区表结构：读取全表

       如果做了分区表结构：读取分区

2. 分桶表结构设计

（1）Hive中Join的问题

       默认情况下，Hive底层是通过MapReduce来实现的；MapReduce在处理数据之间join的时候有两种方式：MapJoin、ReduceJoin，其中MapJoin效率较高；

       如果有两张非常大的表要进行Join，底层无法使用MapJoin提高Join的性能，只能走默认的ReduceJoin;而ReduceJoin必须经过Shuffle过程，相对性能比较差，而且容易产生数据倾斜。

（2）分桶表设计思想

       分区表是将数据划分不同的目录进行存储，而分桶表是将数据划分不同的文件进行存储。分桶表的设计是按照一定的规则[底层通过MapReduce中的多个Reduce来实现]将数据划分到不同的文件中进行存储，构建分桶表。

       如果有两张表按照相同的划分规则[比如按照Join的关联字段]将各自的数据进行划分；在Join时，就可以实现Bucket与Bucket的Join，避免不必要的比较，减少笛卡尔积数量。

（3）案例：基于Hive实现emp和dept两张大表的分桶Join

       构建普通emp表

       构建分桶emp表

       构建普通dept表

        构建分桶dept表

       对普通表执行Join计划

       对分桶表执行Join计划

3. 索引设计

（1）Hive中的索引

       在传统的关系型数据库例如MySQL、Oracle中，为了提高数据的查询效率，可以为表中的字段单独构建索引，查询时，可以基于字段的索引快速的实现查询、过滤等操作。

       Hive中也同样提供了索引的设计，允许用户为字段构建索引，提高数据的查询效率。但是Hive的索引与关系型数据库中的索引并不相同，比如，Hive不支持主键或者外键索引。Hive索引可以建立在表中的某些列上，以提升一些操作的效率。

       在可以预见到分区数据非常庞大的情况下，分桶和索引常常是优于分区的；而分桶由于SMBJoin对关联键(join字段)要求严格，所以并不是总能生效；

       注意：官方明确表示，索引功能支持是从Hive0.7版本开始，到Hive3.0不再支持

（2）Hive中索引的基本原理

       当为某张表的某个字段创建索引时，Hive中会自动创建一张索引表，该表记录了该字段的每个值与数据实际物理位置之间的关系，例如数据所在的HDFS文件地址，以及所在文件中偏移量offset等信息。

        Hive索引的目的是提高Hive表指定列的查询速度。没有索引时，类似WHERE tabl.col1 = 10的查询，Hive会加载整张表或分区，然后处理所有的行，但是如果在字段col1上面存在索引时，那么只会加载和处理文件的一部分。

（3）索引的使用

       可以使用Hive3.0以下版本测试，创建索引

       构建索引：通过运行一个MapReduce程序来构建索引

alter index idx_user_id_login ON tb_login_part rebuild;

       查看索引结构

desc default_tb_login_part_idx_user_id_login__;

       查看索引内容

select * from default_tb_login_part_idx_user_id_login__;

（4）Hive索引的问题

       Hive构建索引的过程是通过一个MapReduce程序来实现的；

       每次Hive中原始数据表的数据发生更新时，索引表不会自动更新，必须手动执行一个Alter index命令来实现通过MapReduce更新索引表，导致整体性能较差，维护相对繁琐

       表中数据发生新增或者修改

       索引表没有更新

        手动更新索引表：通过MapReduce实现

alter index idx_user_id_login ON tb_login_part rebuild;

（5）小结

       由于Hive的索引设计过于繁琐，所以从Hive3.0版本开始，取消了对Hive Index的支持及使用；如果使用的是Hivel.x或者Hive2.x，在特定的场景下依日可以使用Hive Index来提高性能。

       实际工作场景中，一般不推荐使用Hive Index，推荐使用ORC文件格式中的索引、物化视图来代替Hive Index提高查询性能。

二、Hive表数据优化

1. 文件格式

（1）文件格式——概述

       Hive数据存储的本质还是HDFS，所有的数据读写都基于HDFS的文件来实现；

       为了提高对HDFS文件读写的性能，Hive提供了多种文件存储格式：TextFile、SequenceFile、ORC、Parquet等；

       不同的文件存储格式具有不同的存储特点，有的可以降低存储空间，有的可以提高查询性能。

       Hive的文件格式在建表时指定，默认是TextFile.

（2）文件格式-TextEile

       TextFile是Hive中默认的文件格式，存储形式为按行存储。

       工作中最常见的数据文件格式就是TextFile文件，几乎所有的原始数据生成都是TextFile格式，所以Hive设计时考虑到为了避免各种编码及数据错乱的问题，选用了TextFile作为默认的格式。建表时不指定存储格式即为TextFile，导入数据时把数据文件拷贝至HDFS不进行处理。

       创建原始数据表

       创建TextFile数据表

（3）文件格式——SequenceFile

        SequenceFile是Hadoop里用来存储序列化的键值对即二进制的一种文件格式。SequenceFile文件也可以作为MapReduce作业的输入和输出，hive也支持这种格式。

       SequenceFile的使用

（4）文件格式-Parquet

       Parquet是一种支持嵌套结构的列式存储文件格式，最早是由Twitter和Cloudera合作开发，2015年5月从Apache孵化器里毕业成为Apache顶级项目。

        是一种支持嵌套数据模型对的列式存储系统，作为大数据系统中OLAP查询的优化方案，它已经被多种查询引擎原生支持，并且部分高性能引擎将其作为默认的文件存储格式。

       通过数据编码和压缩，以及映射下推和谓词下推功能，Parquet的性能也较之其它文件格式有所提升。

       Parquet的特点：高效的数据编码和压缩

       Parquet的使用

（5）文件格式-ORC

       ORC(OptimizedRC File)文件格式也是一种Hadoop生态圈中的列式存储格式；

       它的产生早在2013年初，最初产生自Apache Hive，用于降低Hadoop数据存储空间和加速Hive查询速度；

       2015年ORC项目被Apache项目基金会提升为Apache顶级项目。

       ORC不是一个单纯的列式存储格式，仍然是首先根据行组分割整个表，在每一个行组内进行按列存储。

       ORC文件是自描述的，它的元数据使用ProtocolBuffers序列化，并且文件中的数据尽可能的压缩以降低存储空间的消耗，目前也被Hive、Spark SQL、Presto等查询引擎支持。

       ORC的使用

2. 数据压缩

（1）数据压缩概述

       Hive底层运行MapReduce程序时，磁盘I/O操作、网络数据传输、shuffle和merge要花大量的时间，尤其是数据规模很大和工作负载密集的情况下。

       鉴于磁盘I/O和网络带宽是Hadoop的宝贵资源，数据压缩对于节省资源、最小化磁盘I/O和网络传输非常有帮助。

       Hive压缩实际上说的就是MapReduce的压缩。

       压缩的优点：

• 减小文件存储所占空间
• 加快文件传输效率，从而提高系统的处理速度
• 降低I0读写的次数

       压缩的缺点：

       使用数据时需要先对文件解压，加重CPU负荷，压缩算法越复杂，解压时间越长

（2）Hive中的压缩

       Hive中的压缩就是使用了Hadoop中的压缩实现的，所以Hadoop中支持的压缩在Hive中都可以直接使用。

       Hadoop中支持的压缩算法：

       要想在Hive中使用压缩，需要对MapReduce和Hive进行相应的配置

（3）Hive中压缩配置

（4）Hive中压缩测试

       创建表，指定为textfile格式，并使用snappy压缩
create table tb_sogou_snappy
stored as textfile
as select * from tb_sogou_source;

       查看结果数据

       创建表，指定为orc格式，并使用snappy压缩
create table tb_sogou_orc_snappy
stored as orc tblproperties ("orc. compress"="SNAPPY")
as select * from tb_sogou_source;

       查看结果数据

       创建表，指定为textfile格式，并使用snappy压缩
create table tb_sogou_snappy
stored as textfile
as select * from tb_sogou_source;

       查看结果数据

3. 存储优化

（1）避免小文件生成

       Hive的存储本质还是HDFS，HDFS是不利于小文件存储的，因为每个小文件会产生一条元数据信息，并且不利用MapReduce的处理，MapReduce中每个小文件会启动一个MapTask计算处理，导致资源的浪费，所以在使用Hive进行处理分析时，要尽量避免小文件的生成。

       Hive中提供了一个特殊的机制，可以自动的判断是否是小文件，如果是小文件可以自动将小文件进行合并。

（2）合并小文件

        如果遇到数据处理的输入是小文件的情况，怎么解决呢？

       Hive中也提供- -种输入类CombineHiveInputFormat，用于将小文件合并以后，再进行处理

（3）ORC文件索引

       在使用ORC文件时，为了加快读取ORC文件中的数据内容，ORC提供了两种索引机制 : Row Group Index 和 Bloom Filter Index可以帮助提高查询ORC文件的性能当用户写入数据时，可以指定构建索引，当用户查询数据时，可以根据索引提前对数据进行过滤，避免不必要的数据扫描。

       Row Group Index：一个ORC文件包含一个或多个stripes(groups of row data)，每个stripe中包含了每个column的min/max值的索引数据。

       当查询中有大于等于小于的操作时，会根据min/max值，跳过扫描不包含的stripes。而其中为每个stripe建立的包含min/max值的索引，就称为Row Group Index行组索引，也叫min-max
Index大小对比索引，或者Storage Index。

       建立ORC格式表时，指定表参数 orc.create.index' ='true'之后，便会建立Row Group Index。为了使Row Group Index有效利用，向表中加载数据居时，必须对需要使用索引的字段进行排序

       Bloom Filter Index（判断数据在不在）：建表时候通过表参数”orc.bloom.filter.columns”="columnName.."来指定为哪些字段建立BloomFilter索引，在生成数据的时候，会在每个stripe中，为该字段建立BloomFilter的数据结构。
       当查询条件中包含对该字段的等值过滤时候，先从BloomFilter中获取以下是否包含该值，如果不包含，则跳过该stripe。

（4）ORC矢量化查询

       Hive的默认查询执行引擎一次处理一行，而矢量化查询执行是一种Hive针对ORC文件操作的特性，目的是按照每批1024行读取数据，并且一次性对整个记录整合（而不是对单条记录）应用操作，提升了像过滤，联合，聚合等等操作的性能。

       注意：要使用矢量化查询执行，就必须以ORC格式存储数据。

三、Job作业执行优化

1. 计算Job执行优化

（1）Explain查询计划

       HiveQL是一种类SQL的语言，从编程语言规范来说是一种声明式语言，用户会根据查询需求提交声明式的HQL查询，而Hive会根据底层计算引擎将其转化成Mapreduce/Tez/Spark的job ;

       explain命令可以帮助用户了解一条HQL语句在底层的实现过程。通俗来说就是Hive打算如何去做这件事。explain会解析HQL语句，将整个HQL语句的实现步骤、依赖关系、实现过程都会进行解析返回，可以了解一条HQL语句在底层是如何实现数据的查询及处理的过程，辅助用户对Hive进行优化。

       官网 : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain

       常用语法命令如下：

EXPLAIN ［FORMATTED | EXTENDED | DEPENDENCY | AUTHORIZATION|] query（SQL）

• FORMATTED：对执行计划进行格式化，返回JSON格式的执行计划
• EXTENDED：提供一些额外的信息，比如文件的路径信息
• DEPENDENCY：以JSON格式返回查询所依赖的表和分区的列表
• AUTHORIZATION：列出需要被授权的条目，包括输入与输出

（2）查询计划组成

       每个查询计划由以下几个部分组成

       The Abstract Syntax Tree for the query
       抽象语法树(AST)：Hive使用Antlr解析生成器，可以自动地将HQL生成为抽象语法树

       The dependencies between the different stages of the plan
       Stage依赖关系：会列出运行查询划分的stage阶段以及之间的依赖关系

       The description of each of the stages
       Stage内容：包含了每个stage非常重要的信息，比如运行时的operator和sort orders等具体的信息

（3）实操

       explain select count(*)  as cnt from tb_emp where deptno = '10';

2. MapReduce属性优化

（1）本地模式（建议开启）

       使用Hive的过程中，有一些数据量不大的表也会转换为MapReduce处理，提交到集群时，需要申请资源，等待资源分配，启动JVM进程，再运行Task，一系列的过程比较繁琐，本身数据量并不大，提交到YARN运行返回会导致性能较差的问题。

       Hive为了解决这个问题，延用了MapReduce中的设计，提供本地计算模式，允许程序不提交给YARN，直接在本地运行，以便于提高小数据量程序的性能。

       配置：

       开启本地模式

set hive. exec. mode. local. auto = true;

（2）JVM重用（Hive新版本已不再支持）

       Hadoop默认会为每个Task启动一个JVM来运行，而在JVM启动时内存开销太；

       Job数据量大的情况，如果单个Task数据量比较小，也会申请JVM，这就导致了资源紧张及浪费的情况；

       JVM重用可以使得JVM实例在同一个job中重新使用N次，当一个Task运行结束以后，JVM不会进行释放，而是继续供下一个Task运行，直到运行了N个Task以后，就会释放；

       N的值可以在Hadoop的mapred-site.xml文件中进行配置，通常在10-20之间。

（3）并行执行（建议开启）

       Hive在实现HQL计算运行时，会解析为多个Stage，有时候Stage彼此之间有依赖关系，只能挨个执行，但是在一些别的场景下，很多的Stage之间是没有依赖关系的；

       例如Union语句，Join语句等等，这些Stage没有依赖关系，但是Hive依日默认挨个执行每个Stage，这样会导致性能非常差，我们可以通过修改参数，开启并行执行，当多个Stage之间没有依赖关系时，允许多个Stage并行执行，提高性能。

3. Join优化

       Join是数据分析处理过程中必不可少的操作，Hive同样支持Join的语法；Hive Join的底层是通过MapReduce来实现的，Hive实现Join时，为了提高MapReduce的性能，提供了多种Join方案
来实现；
       例如适合小表Join大表的Map Join，大表Join大表的Reduce Join，以及大表Join的优化方案Bucket Join等

（1） Map Join

       应用场景：适合于小表join大表或者小表Join小表

       原理：将小的那份数据给每个MapTask的内存都放一份完整的数据，大的数据每个部分都可以与小数据的完整数据进行join。底层不需要经过shuffle，需要占用内存空间存放小的数据文件。

       使用：尽量使用Map Join来实现Join过程，Hive中默认自动开启了Map Join

hive.auto.convert.join=true

       Hive中小表的大小限制：

• 2.0版本之前的控制属性：hive.mapjoin.smalltable.filesize=25M
• 2.0版本开始由以下参数控制：hive.auto.convert. join.noconditionaltask.size=512000000

（2）Reduce Join

       应用场景：适合于大表Join大表

       原理：将两张表的数据在shuffle阶段利用shuffle的分组来将数据按照关联字段进行合并
必须经过shuffle，利用Shuffle过程中的分组来实现关联

       使用：Hive会自动判断是否满足Map Join，如果不满足Map Join，则自动执行Reduce Join。

（3）Bucket Join

       应用场景：适合于大表Join大表

       原理：将两张表按照相同的规则将数据划分，根据对应的规则的数据进行join，减少了比较次数，提高了性能。

       使用Bucket Join

• 语法：clustered by colName
• 参数： set hive.optimize.bucketmapjoin = true;
• 要求：分桶字段 = Join字段，桶的个数相等或者成倍数

       使用Sort Merge Bucket Join(SMB)

• 基于有序的数据Join
• 语法： clustered by colName sorted by (colName)
• 参数：
• set hive. optimize. bucketmapjoin = true;
• set hive. auto. convert. sortmerge. join=true;
• set hive. optimize. bucketmapjoin. sortedmerge = true;
• set hive. auto. convert. sortmerge. join. noconditionaltask=true;
• 要求：分桶字段 = Join字段 = 排序字段，桶的个数相等或者成倍数

4. 优化器

（1）关联优化

       当一个程序中如果有一些操作彼此之间有关联性，是可以在一个MapReduce中实现的，但是Hive会不智能的选择，Hive会使用两个MapReduce来完成这两个操作。

       例如：当我们执行 select ..from table group by id order by id desc。该SQL语句转换为MapReduce时，我们可以有两种方案来实现：
       方案一：

• 第一个MapReduce做group by，经过shuffle阶段对id做分组
• 第二个MapReduce对第一个MapReduce的结果做order by，经过shuffle阶段对id进行排序

       方案二：
       因为都是对id处理，可以使用一个MapReduce的shuffle既可以做分组也可以排序

       在这种场景下，Hive会默认选择用第一种方案来实现，这样会导致性能相对较差。可以在Hive中开启关联优化，对有关联关系的操作进行解析时，可以尽量放在同一个MapReduce中实现。

       配置：

set hive. optimize.correlation=true;

（2）CBO优化

       Hive默认的优化器（帮助我们选择最佳的方案执行SQL）在解析一些聚合统计类的处理时，底层解析的方案有时候不是最佳的方案。例如当前有一张表[共1000条数据]，id构建了索引，id=100值有900条。需求：查询所有id = 100的数据，SQL语句为：select ＊ from table where id = 100;

• 方案一：由于id这一列构建了索引，索引默认的优化器引擎RBO，会选择先从索引中查询id=100的值所在的位置，再根据索引记录位置去读取对应的数据，但是这并不是最佳的执行方案。
• 方案二：有id=100的值有900条，占了总数据的90%，这时候是没有必要检索索引以后再检索数据的，可以直接检索数据返回，这样的效率会更高，更节省资源，这种方式就是CBO优化器引擎会选择的方案。

       RBO（rule basic optimise）：基于规则的优化器，根据设定好的规则来对程序进行优化

       CBO（cost basic optimise）：基于代价的优化器，根据不同场景所需要付出的代价来合适选择优化的方案。对数据的分布的信息[数值出现的次数，条数，分布]来综合判断用哪种处理的方案是最佳方案

       Hive中支持RBO与CBO这两种引擎，默认使用的是RBO优化器引擎。很明显CBO引擎更加智能，所以在使用Hive时，我们可以配置底层的优化器引擎为CBO引擎。
       set hive. cbo. enable=true;
       set hive. compute. query.using. stats=true;
       set hive. stats.fetch. column. stats=true;

（3）Analyze分析器

       CBO引擎是基于代价的优化引擎，它会通过Analyze分析器获得每种方案的计算代价。

       Analyze分析器功能：用于提前运行一个MapReduce程序将表或者分区的信息构建一些元数据[表的信息、分区信息、列的信息]，搭配CBO引擎一起使用。

       语法：

5. 谓词下推(PPD)

（1）概述

       谓词：用来描述或判定客体性质、特征或者客体之间关系的词项。比如"3大于2"中"大于"是一个谓词。

       谓词下推Predicate Pushdown(PPD)基本思想：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。简单点说就是在不影响最终结果的情况下，尽量将过滤条件提前执行。

       Hive中谓词下推后，过滤条件会下推到map端，提前执行过滤，减少map到reduce的传输数据，提升整体性能。

       开启参数【默认开启】：hive. optimize.ppd=true;

（2）例子

       推荐形式1的方式，先过滤再join。

（3）规则

       1、对于Join(Inner Join)、Full outer Join，条件写在on后面，还是where后面，性能上面没有区别；
       2、对于Left outer Join，右侧的表写在on后面、左侧的表写在where后面，性能上有提高 ；
       3、对于Right outer Join，左侧的表写在on后面、右侧的表写在where后面，性能上有提高；
       4、当条件分散在两个表时，谓词下推可按上述结论2和3自由组合。

6. 数据倾斜

（1）数据倾斜概述

       分布式计算中最常见的，最容易遇到的问题就是数据倾斜。数据倾斜的现象是，当提交运行一个程序时，这个程序的大多数的Task都已经运行结束了，只有某一个Task一直在运行，迟迟不能结束，导致整体的进度卡在99%或者100%，这时候就可以判定程序出现了数据倾斜的问题。

       数据倾斜的原因：数据分配

（2）遇到数据倾斜的场景——Group by、Count(distinct)

       当程序中出现group by或者count(distinct)等分组聚合的场景时，如果数据本身是倾斜的，根据
MapReduce的Hash分区规则，肯定会出现数据倾斜的现象。

       根本原因是因为分区规则导致的，所以可以通过以下几种方案来解决group by导致的数据倾斜的问题。

       方案一：开启Map端聚合

hive. map. aggr=true;

       通过减少shuffle数据量和Reducer阶段的执行时间，避免每个Task数据差过大导致数据倾斜

       方案二：实现随机分区

select * from table distribute by rand() ;

• distribute by用于指定底层按照哪个字段作为Key实现分区、分组等
• 通过rank函数随机值实现随机分区，避免数据倾斜

       方案三：数据倾斜时自动负载均衡（建议开启）

hive. groupby. skewindata=true;

       开启该参数以后，当前程序会自动通过两个MapReduce来运行。第一个MapReduce对数据自动进行随机分布到Reducer中，每个Reducer做部分聚合操作，输出结果。第二个MapReduce将上一步聚合的结果再按照业务(group by key)进行处理，保证相同的分布到一起，最终聚合得到结果。

（3）遇到数据倾斜的场景——Join

       Join操作时，如果两张表比较大，无法实现Map Join，只能走Reduce Join，那么当关联字段中某一种值过多的时候依旧会导致数据倾斜的问题。

       面对Join产生的数据倾斜，核心的思想是尽量避免Reduce Join的产生，优先使用Map Join来实现。但往往很多的Join场景不满足Map Join的需求，那么可以以下几种方案来解决Join产生的数据倾斜问题：

       方案一：提前过滤，将大数据变成小数据，实现MapJoin

       方案二：使用Bucket Join

       如果使用方案一，过滤后的数据依旧是一张大表，那么最后的Join依旧是一个Reduce Join.这种场景下，可以将两张表的数据构建为桶表，实现Bucket Map Join，避免数据倾斜。

       方案三：使用Skew Join
       Skew Join是Hive中一种专门为了避免数据倾斜而设计的特殊的Join过程。这种Join的原理是将Map Join和Reduce Join进行合并，如果某个值出现了数据倾斜，就会将产生数据倾斜的数据单独使用MapJoin来实现，其他没有产生数据倾斜的数据由Reduce Join来实现，这样就避免了Reduce Join中产生数据倾斜的问题，最终将Map Join的结果和Reduce Join的结果进行Union合并。

 四、Hive3新特性

1. Hive执行引擎

       Hive底层的计算由分布式计算框架实现，目前支持三种计算引擎，分别是MapReduce、Tez、Spark。Hive中默认的计算引擎是MapReduce，由hive.execution.engine参数属性控制。

       Hive从2.x版本开始就提示不推荐使用MR，未来的版本可能不能使用了，推荐使用Tez或者Spark引擎来代替MapReduce计算。如果依旧要使用MapReduce，需要使用Hive的1.x版本。

       通过实测发现，当下Hive3.1.2版本默认引擎依然是MapReduce，在实际使用Hive的过程中，建议将Hive的底层计算引擎更改为Tez或者Spark引擎。

（1）Hive on Tez

       Tez是Apache社区中的一种支持DAG作业（有向无环图）的开源计算框架，可以将多个有依赖的作业转换为一个作业从而大幅提升DAG作业的性能，最终Tez也会将程序提交给YARN来实现运行。

       Tez并不直接面向最终用户，事实上它允许开发者为最终用户构建性能更快、扩展性更好的应用程序。

       Tez需要自行下载安装，可以在网上搜索相关下载安装教程。

（2）Tez特点

       灵活的数据流定义；灵活的输入、输出、运行时模型；与数据类型无关；部署方便简洁；高性能执行；最佳资源管理；计划配置动态更新；动态物理数据流决策

       官网：http://tez.apache.org/

（3）Tez整体的执行过程

       1. Hive编译SQL
       2.  Tez执行查询
       3 YARN分配资源，支撑Tez执行
       4. Hive表数据文件默认存储在HDFS
       5．Tez执行完毕返回查询结果给Hive

（4）Tez使用测试

       设置Hive引擎为tez，进行数据插入

       运行速度spark > tez > mr，因此可以考虑使用spark作为执行引擎。

2. LLAP更新

（1）概述

       LLAP是hive 2.0版本就引入的特性，在Hive 3中与Tez集成的应用更加成熟。Hive官方称之为实时长期处理(Live long and process)，实现将数据预取、 缓存到基于yarn运行的守护进程中，降低和减少系统IO和与HDFS DataNode的交互，以提高程序的性能，LLAP目前只支持tez引擎。 

       LLAP提供了一种混合执行模型。它由一个长期存在的守护进程（该守护进程替换了与 HDFS DataNode 的直接交互）以及一个紧密集成的基于 DAG 的框架组成。诸如缓存，预取，某些查询处理和访问控制之类的功能已移至守护程序中。此守护程序直接直接处理小/短查询，而任何繁重的工作都将在标准YARN容器中执行。