【大数据】一篇认识Hive

一、数据仓库

1.1、数据仓库概念

• 数据仓库，中文简称数仓。英文叫做Data WareHouse,简称DW。
• 数据仓库是面向分析的集成化数据平台，分析的结果给企业提供决策支持；
• 数据仓库本身不生产数据；

其分析的数据来自于企业各种数据源。
企业中常见的数据源：
	RDBMS关系型数据库--->业务数据
	log file----->日志文件数据
	爬虫数据
	其他数据

• 数据仓库本身也不消费数据；

其分析的结果给外部各种数据应用（Data application）来使用。

Data visualization（DV）数据可视化
Data Report 数据报表
Data Mining(DM) 数据挖掘
Ad-Hoc 即席查询

	即席查询（Ad Hoc）是用户根据自己的需求，灵活的选择查询条件，系统能够根据用户的选择生成相应的统计报表。即席查询与普通应用查询最大的不同是普通的应用查询是定制开发的，而即席查询是由用户自定义查询条件的。

• 企业中一般先有数据库，然后有数据仓库，可以没有数据仓库，但是不能没有数据库。
  • 企业刚刚起步时,必须有业务数据库,生成业务数据
  • 当业务数据达到一定体量才需要数据仓库进行统一数据分析
• 数据仓库不是大型的数据库，只是一个数据分析的平台。

1.2、数据仓库核心特征

• 面向主题性（Subject-Oriented）

  主题（subject）是一个抽象的概念 数据综合体。一个分析的主题可以对应多个数据源。
  在数仓中开展分析，首先确定分析的主题，然后基于主题寻找、采集跟主题相关的数据。

• 集成性（Integrated）

  数仓不是生成数据的平台 其数据来自于各个不同的数据源
  当我们确定主题之后 就需要把和主题相关的数据从各个数据源集成过来。
  因为同一个主题的数据可能来自不同的数据源 它们之间会存在着差异（异构数据）：字段同名不统一、单位不统一、编码不统一；
  因此在集成的过程中需要进行ETL(Extract抽取 Transform转换 load加载)

• 不可更新性（Non-Volatile）

  数仓上面的数据几乎没有修改操作，都是分析的操作。
  数仓是分析数据规律的平台 不是创造数据规律的平台。
  注意：数据之间的规律不能修改。

• 时变性（Time-Variant）

  数仓是一个持续维护建设的东西。
  站在时间的角度，数仓的数据成批次变化更新。一天一分析（T+1） 一周一分析（T+7）
  上述所讲的更多偏向于离线数仓，当下还有比较火的实时数仓。

1.3、数据库和数据仓库的区别

• 本质的区别就是OLTP 和OLAP系统的区别
  • 数据库更偏重于事务处理,要求其支持事务,增删改查效率较高
    • 事务: 最小业务单元, 在执行过程中,要么全部成功,要么全部失败
      • 举例:小椭圆给小绿转账1000元,银行系统需要给小椭圆减少1000元, 给小绿增加一千元，要么同时成功,要么同时失败
  • 数据仓库偏重于数据吞吐量和稳定,不要求支持事务,不要求较高的响应效率,但必须可以处理海量数据或文件
• OLTP（On-Line Transaction Processing）

Transaction 事务 面向事务支持事务 
OLTP系统注重的是数据安全、完整、响应效率。通常指的就是RDBMS关系型数据库。
  #面向事务 支持事务
  #RDBMS: MySQL ORACLE
  #注意 不是NoSQL数据库 没有事务支持： Redis HBase

• OLAP（On-Line Analytical Processing） 中文发音：欧莱普

  Analytical 分析 面向分析支持分析
  OLAP更注重的是数据吞吐量
  主要指的是数据仓库、数据集市（小型数据仓库）：Apache Hive、Apache Impala

• 注意：在某些场合下，说ORACLE也是OLAP系统，如何理解？

  如果把RDBMS只用作数据分析工作，就是OLAP系统。
  
  关系型数据库只要从事的是数据分析的工作,就可以看做是OLAP系统
  但是Hive不可以作为OLTP使用因为其不具备事务功能.

• 数据仓库不是大型的数据库，也没有要取代数据库的目标，只是一个数据分析的平台。

1.4、数据仓库分层架构[重要]

• 数仓本身不生产数据也不消费数据，按照数据流入流出的特点，对平台进行分层
• 最基础最核心的3层架构，企业实际应用中，可以结合需要添加不同分层。
• 核心3层架构
  • ODS 操作型数据层、源数据层、临时存储层
    • 直接抽取源数据,数据的异常值,缺失值,数据的单位等都有问题,无法用于数据分析

其数据来自于各个不同的数据源 临时存储 和数据源解耦合之间有差异一般不直接用于分析

• DW 数据仓库(大数据开发工程师一般都是在该层进行各种操作)

其数据来自于ODS经过层层的ETL变成各种模型的数据.数据干净.规则统一
基于各种模型开展各种分析
企业中根据业务复杂度 继续在DW中继续划分子层。 存储大量的中间结果。其数据来自于ODS经过层层ETL得出 企业中可以根据需求在DW中继续分层。

• DA 数据应用层

最终消费DW数据的各种应用。

• 分层好处
  • 解耦合(相互依赖),方便协同开发
  • 可以追根溯源,更快对于问题进行解决和修复
  • 过程中创建的中间表可以重复利用
  • 空间换时间 提高最终应用层使用数据的效率

1.5、ETL和ELT

E: 数据抽取 将多个数据源中的数据抽取到临时存储空间(内存, 磁盘)
T: 数据转换 将数据进行清洗或转换,使其干净统一,满足数据分析的使用要求
L: 数据加载 将数据加载到数据仓库中,待其他业务部门使用.
ETL: 先将数据抽取到临时位置或者内存中,进行数据转换处理,最终结果加载到数仓

• 在传统数仓中,ETL用的多,因为业务相对简单,变化小,或不变化,或者数据体量较小
  ELT: 先将数据抽取出来,加载到数仓中,在数仓中进行进一步的转换处理
• 在现阶段绝大多数公司都是ELT,便于数仓分层,更适合处理灵活多变的数据需求

二、Apache Hive

2.1、Hive的概念

• Hive是Facebook开源出来，后来贡献给力Apache .宗旨是：提高分析数据的能力降低分析数据的开发成本。
• Hive是基于 Hadoop 的一个**数据仓库**工具，用于分析数据的。
  • hadoop具备数据存储和计算的能力,理论上可以作为一个数据仓库使用,但是不合适
  • hadoop中的mr任务处理结构化数据过于复杂,代码量巨大,且开发效率不高,容易出错
  • Hive解决了mr任务的这种缺陷,让我们使用类似于sql语句的方式进行结构化数据的增删改查操作

  为什么说Hive是基于Hadoop的呢？
  
  #作为一款数据仓库软件，应该要具备哪些能力？
  具备存储数据的能力
  具备分析数据的能力
  
  Hive作为数仓软件，当然具备上述两种能力？
  #Hive使用Hadoop HDFS作为数据存储系统
  #Hive使用Hadoop MapReduce来分析数据
  
  基于此说Hive是基于Hadoop的数仓软件。

总结:

hive是一个数仓管理工具,是基于hadoop的数仓软件
hive既不负责数据的存储,也不负责数据的计算.
hive的作用:

• hive可以将结构化数据映射为表
• hive提供了一种类似于sql的查询语句
• hive可以将sql语句转换为mr任务并执行
  hive极大程度上提高了数据仓库的开发效率,降低了数仓的使用门槛

什么是结构化数据?

可以通过行索引和列索引定位一个数据的数据结构,类似于excel表数据或则sql数据库数据
结构化数据:

1, 小明, 12
2, 小芳, 15
3, 小绿, 22

表:

学号	姓名	年龄
1	小明	12
2	小芳	15
3	小绿	22

结构化数据映射表时需要提取hive中的元数据.

2.2、Hive的架构组件(非常重要)

• 客户端用户接口

  所谓的客户端指的是给用户一种方式编写Hive SQL
  目前常见的客户端：CLI（命令行接口 shell）、Web UI、JDBC|ODBC

• Hive Driver驱动程序

  hive的核心
  完成从接受HQL到编译成为MR程序的过程。
  sql解释 编译 校验 优化 制定计划

• metadata

  元数据存储。 描述性数据。
  对于hive来说，元数据指的是表和文件之间的映射关系。

• Hadoop

  HDFS  存储文件
  MapReduce 计算数据
  YARN  程序运行的资源分配

• Q:Hive是分布式的软件吗？

  Hive不是分布式软件。只需要在一台机器上部署Hive服务即可；
  Hive的分布式处理能力是借于Hadoop完成的。HDFS分布式存储  MapReduce分布式计算。
  hive不是分布式软件,但是可以借助hadoop完成分布式的存储和计算任务

总结: 其实hive的架构一共就三大部分: 客户端 驱动程序 元数据服务

2.3、Hive和Mysql的区别

• 从外表、形式模型、语法各层面上看 ，hive和数据库（Mysql）很类似。
• 底层应用场景是完全不一样的。
• hive属于olap系统是面向分析的侧重于数据分析（select）
• 数据库属于oltp系统是面向事务的侧重于数据时间交互（CRUD）
• Hive绝不是大型数据库也不是为了要取代MySQL这样的数据库。

三、Hive安装

3.1、Metadata、metastore

回顾: hdfs的元数据

元数据: 描述数据的数据
hdfs中存储一个文件,文件中的数据内容就是我们想要获取的内容.
如果想要获取这个内容,我们就需要知道这个文件的相关描述信息
例如: 路径,大小,创建修改时间,名称,block块的数量, 位置 …

# Metadata 元数据
对于hive来说，元数据主要指的是表和文件之间的映射关系。
元数据也是数据，存储在哪里呢？Hive当下支持两种地方存储元数据。
	1、存储在Hive内置的RDBSM中，Apache Derby(内存级别轻量级关系型数据库)  
		Derby数据库主要是为了让程序员测试hive的执行使用,正常开发中不会使用该数据库
	2、存储在外界第三方的RDBMS中，比如：MySQL。  企业中常用的方式。

# metastore 元数据访问服务
专门用于操作访问metadata的一种服务，对外暴露服务地址给各个不同的客户端使用访问Hive的元数据。
并且某种程度上保证了metadata的安全。

思考:hive中的元数据都包含什么?

hive中我们使用元数据构建表,用来描述结构化数据,就是结构化数据的映射.
库名,表名, 字段名, 字段类型, 数据存储位置…
hive映射的过程,就是使用元数据建表,通过元数据存储的数据位置,从hdfs中获取数据,依次填写到表中,形成表数据,再使用sql语句进行查询.

思考:hive为什么不将元数据存储在hdfs中呢?

hive进行元数据管理时,我们需要快速创建表结构,并且频繁进行元数据的增删改查, hdfs中的数据不支持随机修改.且增删改的效率较低,不适合作为hive的元数据存储服务出现

3.2、Hive的安装部署模式

• 如何区别，关键在于两个问题？

  • metadata元数据是存储在哪里的？ 内置derby还是外置的Mysql。
  • metastore服务是否需要单独配置，单独手动启动？

• 内嵌模式(体验)

  1、元数据存储在内置的derby
  2、不需要单独配置metastore 也不需要单独启动metastore服务
  
  安装包解压即可使用。
  
  适合测试体验。实际生产中没人用。适合单机单人使用。
  

• 本地模式(测试)

  1、元数据使用外置的RDBMS，常见使用最多的是MySQL。
  2、不需要单独配置metastore 也不需要单独启动metastore服务
  缺点:
  如果使用多个客户端进行访问,就需要有多个Hiveserver服务,此时会启动多个Metastore
  有可能出现资源竞争现象
  

• 远程模式(开发) 知道就可以了

    1、元数据使用外置的RDBMS，常见使用最多的是MySQL。
    2、metastore服务单独配置  单独手动启动  全局唯一。
    这样的话各个客户端只能通过这一个metastore服务访问Hive.
    企业生产环境中使用的模式，支持多客户端远程并发操作访问Hive.

• 对比

  	metadata存储在哪	metastore服务如何
  内嵌模式	Derby	不需要配置启动
  本地模式	MySQL	不需要配置启动
  远程模式	MySQL	单独配置、单独启动

3.3、Hive的远程模式部署安装

3.3.1、安装Hadoop

启动hive之前，需要保证Hadoop启动且服务正常可用。

1、等待安全模式结束启动Hive

2、在Hadoop中设置用户代理，注意3台机器都需要修改，重启生效
 
vim etc/hadoop/core-site.xml
 
<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>

3.3.2、安装MySQL

主要考虑的就是MySQL Hive版本的兼容性 和jdbc驱动版本
在Centos7环境下如何安装MySQL
仅在node1中安装了mysql所以我们选择node1进行hive的安装

3.3.3、安装Hive (选择node1安装)

#apache-hive-3.1.2-bin.tar.gz

上传到/export/software 解压到  /export/server
cd /export/software
tar -zxvf apache-hive-3.1.2-bin.tar.gz -C ../server

• 0、解决Hive与Hadoop之间guava版本差异

  cd /export/server/apache-hive-3.1.2-bin/
  rm -rf lib/guava-19.0.jar
  cp /export/server/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar ./lib/

• 1、hive-env.sh

  cd /export/server/apache-hive-3.1.2-bin/conf
  mv hive-env.sh.template hive-env.sh
  
  vim hive-env.sh
  
  
  export HADOOP_HOME=/export/server/hadoop-3.3.0
  export HIVE_CONF_DIR=/export/server/apache-hive-3.1.2-bin/conf
  export HIVE_AUX_JARS_PATH=/export/server/apache-hive-3.1.2-bin/lib

• 2、hive-site.xml
  vim hive-site.xml

  <configuration>
  <!-- 存储元数据mysql相关配置 -->
  <property>
  	<name>javax.jdo.option.ConnectionURL</name>
  	<value>jdbc:mysql://node1:3306/hive3?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
  </property>
  
  <property>
  	<name>javax.jdo.option.ConnectionDriverName</name>
  	<value>com.mysql.jdbc.Driver</value>
  </property>
  
  <property>
  	<name>javax.jdo.option.ConnectionUserName</name>
  	<value>root</value>
  </property>
  
  <property>
  	<name>javax.jdo.option.ConnectionPassword</name>
  	<value>123456</value>
  </property>
  
  <!-- H2S运行绑定host -->
  <property>
      <name>hive.server2.thrift.bind.host</name>
      <value>node1</value>
  </property>
  
  <!-- 远程模式部署metastore metastore地址 -->
  <property>
    <name>hive.metastore.uris</name>
      <value>thrift://node1:9083</value>
  </property>
  
  <!-- 关闭元数据存储授权  --> 
  <property>
    <name>hive.metastore.event.db.notification.api.auth</name>
      <value>false</value>
  </property>
  </configuration>
  

• 3、上传Mysql jdbc驱动到Hive安装包的Lib目录下

  mysql-connector-java-5.1.32.jar

• 4、手动执行命令初始化Hive的元数据

  cd /export/server/apache-hive-3.1.2-bin/
  
  bin/schematool -initSchema -dbType mysql -verbos
  # 初始化成功会在mysql中创建74张表

• 5、在hdfs创建hive存储目录
  • 默认情况下,会将hive中的数据存储在该位置,修改权限是因为我们希望可以任意在该文件中读写信息
    

  hadoop fs -mkdir /tmp
  hadoop fs -mkdir -p /user/hive/warehouse
  hadoop fs -chmod g+w /tmp
  hadoop fs -chmod g+w /user/hive/warehouse

四、Hive 服务的启动[重要]

4.1、metastore服务

• 前台启动

  #前台启动
  /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore
  
  #前台启动开启debug日志
  /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore --hiveconf hive.root.logger=DEBUG,console  
  
  #前台启动关闭方式  ctrl+c结束进程

• 后台挂起启动
  

nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore &

#后台挂起启动 结束进程
使用jps查看进程 使用kill -9 杀死进程

#nohup 命令，在默认情况下（非重定向时），会输出一个名叫 nohup.out 的文件到当前目录下

注意: 日志文件存储在启动该服务时使用的工作目录中的 nohup.out文件下的.

• 添加hive的环境变量

  vim /etc/profile
  
  # 在文件末尾添加如下内容
  #HIVE_HOME
  export HIVE_HOME=/export/server/hive
  export PATH=$PATH:$HIVE_HOME/bin  

• 加载环境变量

  source /etc/profile

• 启动hive服务

  # 前台启动
  hive --service metastore
  
  # 后台启动
  nohup hive --service metastore &

4.2、Hive的客户端

• Hive的第一代客户端

  • bin/hive

  • 直接访问metastore服务

  • 如果需要使用hive服务访问元数据服务,只需要在配置文件中书写元数据服务访问路径即可

    • hive元数据服务的端口号默认是9083

  • 配置

    <configuration>
    <property>
            <name>hive.metastore.uris</name>
            <value>thrift://node1:9083</value>
    </property>
    </configuration>
    

  • 弊端：
    Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.

    

    第一代客户端属于shell脚本客户端 性能友好安全方面存在不足 Hive已经不推荐使用
    官方建议使用第二代客户端beeline
    

  
• Hive的第二代客户端
  
• bin/beeline
• 无法访问metastore服务，只能够访问Hiveserver2服务。
  
• beeline客户端,需要先连接服务,再使用服务
  
• 使用

  # 拷贝node1上 hive安装包到beeline客户端机器上（node3）
  scp -r /export/server/apache-hive-3.1.2-bin/ node3:/export/server/
  
  #1、在安装hive的服务器上 首先启动metastore服务 再启动hiveserver2服务
  nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore &
  nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2 &
  
  #2、在任意机器(如node3)上使用beeline客户端访问
  [root@node3 ~]# /export/server/apache-hive-3.1.2-bin/bin/beeline                   
  beeline> ! connect jdbc:hive2://node1:10000    #jdbc访问HS2服务
  Connecting to jdbc:hive2://node1:10000
  Enter username for jdbc:hive2://node1:10000: root  #用户名 要求具备HDFS读写权限
  Enter password for jdbc:hive2://node1:10000:       #密码可以没有
  

  • 注意: hiveserver2服务启动后无法立即使用类似于hdfs的安全模式,大概要半分钟到一分钟才能开始使用.
  • 注意: 所有通过jdbc连接hive的第三方服务,连接的端口号都是10000,也就是只有第二代客户端才可以使用第三方服务连接
    查询10000端口是否被绑定的方法

lsof -i:10000

五、Hive初体验

• 体验目标:在hive中如何才能够将一份结构化的数据文件 映射成为一张表 然后使用SQL分析？

  --结构化文件
  1,allen,18
  2,james,24
  3,anna,45
  
  --在hive中建表
  create table t_1(id int,name string,age int);
  

  • 猜想1：只要在hive中创建表，那么在HDFS就会有一个文件夹与之对应。

  • 难道把结构化文件放置在这个目录下，才能映射成功吗？ 不一定

    /user/hive/warehouse/test_db.db/t_1
    hadoop fs -put 1.txt /user/hive/warehouse/test_db.db/t_1
    

  • 猜想2：难道要指定分隔符？ 不一定

    create table t_1(id int,name string,age int);
    create table t_2(id int,name string,age int) row format delimited fields terminated by ','; --指定分隔符为逗号
    

  • 猜想3：建表的时候字段类型要不要和文件中数据保持一致？ 一定要保持一致

  • 如果不一致，hive会尝试进行转换，但是不保证成功，如果不成功显示null。

    create table t_3(id int,name int,age string) row format delimited fields terminated by ',';
    +---------+-----------+----------+--+
    | t_3.id  | t_3.name  | t_3.age  |
    +---------+-----------+----------+--+
    | 1       | NULL      | 18       |
    | 2       | NULL      | 24       |
    | 3       | NULL      | 45       |
    +---------+-----------+----------+--+
    

• 当我们映射成功之后，会如何？

  就可以基于表写Hive SQL 开展数据分析，不用写MapReduce。
  
  0: jdbc:hive2://node1:10000> select * from t_2 where age >18;
  +---------+-----------+----------+--+
  | t_2.id  | t_2.name  | t_2.age  |
  +---------+-----------+----------+--+
  | 2       | james     | 24       |
  | 3       | anna      | 45       |
  +---------+-----------+----------+--+
  2 rows selected (0.722 seconds)
  0: jdbc:hive2://node1:10000> 
  
  0: jdbc:hive2://node1:10000> select count(*) from t_2 where age >18;
  WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
  +-----+--+
  | c0  |
  +-----+--+
  | 2   |
  +-----+--+
  1 row selected (67.76 seconds)
  

总结:

1. hive在数据的查询过程中稍慢, 但是写入数据极其缓慢.
2. hive数据在创建表时会创建一个目录将数据上传至该目录时可以快速映射为一个表.
3. hive中数据如果想要映射成功,需要保证每列的数据类型一致,且分隔符指定正确.
4. 在hive终端中或之后书写的hive脚本中不要出现中文(所有的库名,表名,字段名都不要包含中文.)

六、Apache Hive SQL-DDL、DML

DDL : 数据定义语言 (操作表, 操作库, 操作字段) 给数据搭建一个框架,等待数据填入
DML : 数据操作语言 (数据记录的增删改操作) 数据库数据记录的写入操作
DCL : 数据控制语言 (用户权限,表权限,库权限, 事务级别, 数据库相关配置等.) 所有控制,设置数据库的操作方式
DQL : 数据查询语言 (数据的查询操作) 数据库数据记录的读取操作

6.1、 DataGrip连接hive数据仓库

1. 点击左上角的加号,选择datasource, 在下拉菜单中,选择Apache Hive
   
2. 填写配置参数并点击下方download下载hive驱动
   
3. 添加驱动完成后点击测试连接,成功后点击ok即可
   注意: 如果无法下载驱动可以使用本地驱动

1.点击Apache driver,跳转到驱动设置页面

2.点击加号,进入自定义jar包位置的选择

4. 选择驱动所在位置,点击ok 即可配置本地驱动

5.回到datasource,选择刚才操作的连接,点击测试

测试成功后即可连接完成.

检测连接是否成功的方法: 查看数据连接中是否可以正确显示数据仓库的名称.

6.2、Hive SQL

6.2.1、Hive数据类型

• Hive除了支持SQL类型之外，还支持java数据类型；(string)
• Hive除了支持基础数据类型之外，还支持复合类型（array数组 map映射）；
  • array就是一个数据容器,内部存储了多个相同数据类型的数据值 [‘坤坤’, ‘程程’,‘史史’]
  • map也是一个数据容器,内部存储了多个数据的键值对信息 {‘name’:‘宝强’, ‘age’: 18}
  • 针对复合类型的数据要想直接从文件中解析成功 还必须配合分隔符指定的语法。
• Hive中大小写不敏感；
• 在建表的时候，最好表的字段类型要和文件中的类型保持一致，
  • 文件中的数据类型是什么? 文件中都是字符串类型数据,其实说数据类型一致是和源数据的数据类型一致
    • 举例:上述文件是从mysql中提取出来的,则保证mysql中的数据类型和hive中一致
    • 在开发中如果数据类型不确定或者数据类型复杂,在hive建表阶段就把数据类型写成string类型,在使用时再进行数据类型转换
  • 如果不一致，Hive会尝试进行类型隐式转换，不保证转换成功，如果不成功，显示null;
• 常用的数据类型

整数类型: int
浮点型: double
字符串类型: string
布尔型: boolean
复合数据类型:
map: 类似于字典,但是字典中键值对的数据类型要保持一致 
array: 类似于列表,但是只能存储相同数据类型的内容
struct: 结构体(偶尔会用到)
在hive中数据都是写在文件中的,所占用的空间大小已经固定,使用哪些数据类型就不那么重要了

6.2.2、建库/表

• 建库

-- 1.创建数据库
-- create database 数据库名称;
create database db_test;

-- 再次创建同名数据库会报错, 所以我们需要增加安全性判断条件 if not exists
create database if not exists db_test;

-- 在hive中 database 和 schema 作用完全一样,记住一个就可以了,但是别人写的时候我们要认识
create schema db_test1;

-- 2. 查看所有的数据库
show databases;

-- 3. 使用数据库
use db_test;

-- 4. 查看当前正在使用的数据库
select current_database();

-- 5. 查看数据库的相关元数据信息
desc database db_test;

-- 6.创建一个数据库指定存储位置
create database db_test2 location '/tmp/test';
desc database db_test2;

-- 7. 删除数据库
drop database db_test;
-- bigdata_db is not empty. One or more tables exist. 非空数据库无法被删除
-- 如果想要删除非空数据库,要使用cascade (慎用)
drop database bigdata_db cascade;

• 建表

-- 1. 创建数据表
-- 格式: create table 表名 (字段信息);
create table test_db.students
(
    id   int,
    name string,
    age  int
);

-- 2. 如果该数据库中已经有该名称的表,将会报错,所以我们要添加安全性判断 if not exists
create table if not exists test_db.students(
    id int,
    name string
);

-- 3. 查看数据表相关的元数据信息
desc test_db.students;

-- 4. 查看数据库中全部数据表的名称
show tables in test_db;

-- 5. 清空数据表
--  does not support these operations.  在hive中不支持随机删除
-- delete from test_db.students;
-- 可以使用, 注意 table不能省略
truncate table test_db.students;

-- 6. 删除数据表, 有没有数据都可以删除该表
drop table test_db.students;
drop table test_db.person2;

6.2.3、Hive读写HDFS上文件[重点]

create external(外部表)  table if not exists 表名 (
    id int,
    name string
)
partitioned by name (分区表)
cluster by name sorted by id into num(分桶表)
row format delimited  (序列化类型)
fields terminated by ',' (字段分隔符)
collection items terminated by '-' (元素分隔符)
map keys terminated by ':' (键值分隔符)
tblproperties '' 表的属性信息
;

• 机制：SerDe（Serializer and Deserializer）序列化机制(文件读写的规则)

Deserializer: 可以理解为将数据从磁盘中读取到内存中的过程
Serializer :可以理解为将数据从内存中写入到磁盘中的操作

• 读文件
  • HDFS files --> InputFileFormat --> <key, value> --> Deserializer --> Row object
  • hdfs中的文件 >>> 使用输入流对象 >>> 转换为kv结构 >>> 进行反序列化 >>> 转换成数据行对象

    #1、使用InputFileFormat（默认实现TextInputFormat）读取hdfs上文件
        一行一行读取数据，返回<k，v>键值对类型
    #2、返回<key, value>，其中数据存储在value中
    
    #3、使用Deserializer反序列化动作读取value  解析成为对象（Row object）
        默认的序列化类LazysimpleSerDe
    

• 写文件
  • Row object --> Serializer --> <key, value> --> OutputFileFormat --> HDFS files
  • 数据行对象>>> 序列化操作 >>> 转化kv结构的二进制数据 >>> 使用输出流对象 >>> 写入hdfs文件中
• hive中默认的序列化机制叫做LazySimpleSerDe
  • 在默认序列化机制中可以指定各种分隔符,以便于对于数据进行映射
  • 在开发中绝大多数情况下使用的都是默认的序列化机制
    
• 分隔符指定语法
  • 语法格式

    ROW FORMAT DELIMITED | SERDE 
    
    ROW FORMAT DELIMITED  表示使用LazySimpleSerDe类进行序列化解析数据
     
    ROW FORMAT SERDE      表示使用其他SerDe类进行序列化解析数据
    

  • ROW FORMAT DELIMITED具体的子语法

    row format delimited
    [fields terminated by char]  #指定字段之间的分隔符
    [collection items terminated by char]  #指定集合元素之间的分隔符
    [map keys terminated by char]         #指定map类型kv之间的分隔符
    [lines terminated by char]            #指定换行符
    

• 默认分隔符
  • Hive在建表的时候，如果没有row format语法，则该表使用==\001默认分隔符==进行字段分割；
  • 如果此时文件中的数据字段之间的分隔符也是\001 ，那么就可以直接映射成功。
  • 针对默认分隔符，其是一个不可见分隔符，在代码层面是\001表示
  • 在实际工作中，Hive最喜欢的就是\001分隔符，在清洗数据的时候，有意识的把数据之间的分隔符指定为\001;
  • \001的好处.这个字符ascii码中的第一个字符,占用空间小, \001在生产生活中的数据记录中绝不可能出现
• 例子

    -- 创建一个test_db数据库
    create database test_db;
    
    -- 创建一个数据表
    create table test_db.t_archer(
    	id int comment "ID",
    	name string comment "英雄名称",
    	hp_max int comment "最大生命",
    	mp_max int comment "最大法力",
    	attack_max int comment "最高物攻",
    	defense_max int comment "最大物防",
    	attack_range string comment "攻击范围",
    	role_main string comment "主要定位",
    	role_assist string comment "次要定位"
    ) comment "王者荣耀射手信息";
    
    -- 将数据文件全部上传至node1的/root/hive_data目录中
    -- 将数据上传到hdfs中
    -- hadoop fs -put archer.txt /user/hive/warehouse/test_db.db/t_archer
    
    -- 查看表中数据是否插入成功, 数据插入成功,但是映射出正确数据
    select * from test_db.t_archer;
    
    -- 数据没有映射成功,数据内容全部为null,此处就要使用serde机制进行分隔符指定
    create table test_db.t_archer1(
    	id int comment "ID",
    	name string comment "英雄名称",
    	hp_max int comment "最大生命",
    	mp_max int comment "最大法力",
    	attack_max int comment "最高物攻",
    	defense_max int comment "最大物防",
    	attack_range string comment "攻击范围",
    	role_main string comment "主要定位",
    	role_assist string comment "次要定位"
    ) comment "王者荣耀射手信息"
    row format delimited fields terminated by '\t';
    
    -- 将数据文件全部上传至node1的/root/hive_data目录中
    -- 将数据上传到hdfs中
    -- hadoop fs -put archer.txt /user/hive/warehouse/test_db.db/t_archer1
    
    -- 查询数据表中的数据内容
    select * from test_db.t_archer1;
    
    -- 查看表的元数据信息
    desc formatted test_db.t_archer1;
    desc formatted test_db.t_archer;
    
    
    -- 使用delimited 格式化模式加载map类型数据
    -- map类型数据中,需要使用分隔符指定其键值对的规则
    create table test_db.t_hot_hero_skin_price(
    	id int,
    	name string,
    	win_rate int,
    	skin_price map<string,int>  -- <键数据类型,值数据类型> 这种写法叫做泛型
    )
    row format delimited  -- 默认序列化机制
    fields terminated by ',' -- 字段间的分隔符
    collection items terminated by '-' -- map类型多个键值对之间的分隔符
    map keys terminated by ':'; -- 字典的键和值之间的分隔符
    
    -- 查看表的元数据信息
    desc formatted test_db.t_hot_hero_skin_price;
    
    -- 将数据文件全部上传至node1的/root/hive_data目录中
    -- 将数据上传到hdfs中
    -- hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/test_db.db/t_hot_hero_skin_price
    
    -- 查看表中的数据内容
    select * from test_db.t_hot_hero_skin_price;
    
    --有点想法： 就把map数据类型当成字符串映射如何？
    create table test_db.t_hot_hero_skin_price_str(
    	id int,
    	name string,
    	win_rate int,
    	skin_price string
    )
    row format delimited
    fields terminated by ',';
    
    -- 查看以字符串形式存储的皮肤价格数据映射出来的表
    select * from test_db.t_hot_hero_skin_price_str;
  
    -- map类型的使用
    -- 需求: 获取大圣娶亲皮肤的价格
    select skin_price["大圣娶亲"] from test_db.t_hot_hero_skin_price;
    -- 使用str数据类型存储时,需要进行大量的字符串操作,才可以获取皮肤价格,所以map类型使用更加方便
    -- select split(skin_price, '-')[0] from test_db.t_hot_hero_skin_price_str;
  

6.2.4、内部表、外部表[重点]

内部表: hive完全拥有该表中数据记录和元数据的操作权限,删除该表时,数据记录和元数据全部销毁
外部表: hive只拥有该表元数据的操作权限,不具备数据记录的操作权限,删除该表时,数据记录文件依然存在,但是元数据被全部销毁了.

• 内部表

  --创建内部表
  create table student_inner(
  Sno int
  ,Sname string
  ,Sex string
  ,Sage int
  ,Sdept string)
  row format delimited fields terminated by ','
  ;
  
  --上传文件到内部表中
  hadoop fs -put students.txt /user/hive/warehouse/test.db/student_inner
  
  --针对内部表进行drop删除操作
  drop table student_inner;  --内部表在删除的时候 元数据和数据都会被删除
  

• 外部表

  --创建外部表 关键字external
  create external table student_external(
  Sno int
  ,Sname string
  ,Sex string
  ,Sage int
  ,Sdept string)
  row format delimited fields terminated by ','
  ;
  
  --上传文件到外部表中
  hadoop fs -put students.txt /user/hive/warehouse/test.db/student_external
  -- 针对外部表 进行drop删除操作
  drop table student_external; --外部表在删除的时候 只删除元数据  而HDFS上的数据文件不会动
  

6.3、Hive的分区表[重中之重]

• 分区表的创建

  -- 创建数据表
  create table test.t_all_hero
  (
  	id           int,
  	name         string,
  	hp_max       int,
  	mp_max       int,
  	attack_max   int,
  	defense_max  int,
  	attack_range string,
  	role_main    string,
  	role_assist  string
  )
  	row format delimited
  		fields terminated by "\t";
  
  -- 向数据表中上传数据文件
  -- hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/test.db/t_all_hero
  
  -- 查询数据是上传成功
  -- 在同一个表目录中上传多个文件,查询表数据时,会依次获取每一个文件中的数据内容,此时我们将所有文件中的数据都提取出来了
  select *
  from test.t_all_hero;
  
  -- 查询role_main主要定位是射手并且hp_max最大生命大于6000的有几个
  select count(1) from test.t_all_hero where role_main = 'archer' and hp_max > 6000;
  select * from test.t_all_hero where role_main = 'archer';
  
  -- 思考: 此时我们查询role_main = 'archer' and hp_max > 6000时,是检索了所有的文件,还是只查询了archer.txt文件呢??
  -- 此时我们查询了所有文件,但是我们要的数据其实仅仅在archer.txt文件中
  -- 如果我们可以缩小检索范围,那么查询效率就会有大幅度的提升,此时我们就要用到分区表.
  
  -- 分区表的作用: 减小数据查询时检索文件的范围,从而提高查询效率
  
  
  -- 经分析,我们认为根据角色的主要定位进行分区,可以最大限度的减小检索范围
  -- create table test.t_all_hero_part
  -- (
  --     id           int,
  --     name         string,
  --     hp_max       int,
  --     mp_max       int,
  --     attack_max   int,
  --     defense_max  int,
  --     attack_range string,
  --     role_main    string,
  --     role_assist  string
  -- )
  --     partitioned by (role_main string)
  --     row format delimited
  --         fields terminated by "\t";
  -- 此处报错: Column repeated in partitioning columns
  -- 这里告诉我们有重复的列, 此处我们的分区字段是不能和表中其他字段名重复的.
  create table test.t_all_hero_part
  (
  	id           int,
  	name         string,
  	hp_max       int,
  	mp_max       int,
  	attack_max   int,
  	defense_max  int,
  	attack_range string,
  	role_main    string,
  	role_assist  string
  )
  	partitioned by (role string)
  	row format delimited
  		fields terminated by "\t";
  
  -- 查询表的元数据信息
  desc formatted test.t_all_hero_part;
  
  -- 向分区表中加载数据
  -- hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/test.db/t_all_hero_part
  
  -- 查询该表中的数据,数据是否成功映射
  select * from test.t_all_hero_part;
  
  -- 此时数据没有映射成功,该如何向数据表中加载数据呢????
  

• 分区表的数据加载

  • 静态分区加载

    -- 查询一下分区表是否存在,且是否进行了分区
    desc test.t_all_hero_part;
    
    -- 向分区表中插入数据,在插入数据时指定分区名称,这种加载数据的方式叫做静态加载
    -- 格式:  load data inpath '加载路径' into table 表名  partition(分区字段 = 值)
    load data local inpath '/root/hive_data/archer.txt' into table test.t_all_hero_part partition (role = 'archer');
    load data local inpath '/root/hive_data/tank.txt' into table test.t_all_hero_part partition (role = 'tank');
    load data local inpath '/root/hive_data/mage.txt' into table test.t_all_hero_part partition (role = 'mage');
    load data local inpath '/root/hive_data/mage.txt' into table test.t_all_hero_part partition (role = '123');
    
    -- 查看数据是否加载成功
    select * from test.t_all_hero_part;
    
    -- 通过查看hdfs中的文件路径可知,我们将数据加载到了表的分区目录中
    -- 我们推测.每增加一个分区,就会增加一个分区目录,所有数据都存放在分区目录中.
    
    -- 查看分区有哪些
    show partitions test.t_all_hero_part;
    -- partition是元数据中的数据内容,与hdfs无关,我们只是指定了每一个分区数据存放的位置
    
    -- 思考: 我们查询的时候,有没有role字段???  有,  但是这个字段不在数据文件中,是一个元数据构建出来的虚拟字段
    

  • 动态分区加载

    insert+select
    插入的数据来自于后面的查询语句返回的结果。
    查询返回的内容，其字段类型、顺序、个数要和待插入的表保持一致。

    -- 准备一个分区表
    create table test.t_all_hero_part2
    (
    	id           int,
    	name         string,
    	hp_max       int,
    	mp_max       int,
    	attack_max   int,
    	defense_max  int,
    	attack_range string,
    	role_main    string,
    	role_assist  string
    )
    partitioned by (role string)
    row format delimited
    fields terminated by "\t";
    
    -- 准备一个非分区表,内部加载了全部的数据
    select *
    from test.t_all_hero;
    
    -- 动态加载的本质,就是将数据读取到内存中,然后通过分区字段的值,进行判断将每一个数据记录存储在哪一个分区目录中
    -- 动态加载格式: insert into 表名 partition (分区字段名) select 字段列表 from 表;
    -- To turn this off set hive.exec.dynamic.partition.mode=nonstrict
    -- 使用动态加载,需要开启动态加载的非严格模式,才可以.
    set hive.exec.dynamic.partition.mode=nonstrict;
    
    -- Table insclause-0 has 10 columns, but query has 9 columns.
    -- 被插入的表有10列数据, 而插入的数据仅有9列,少了一列分区字段
    insert into test.t_all_hero_part2 partition (role)
    select *, role_main -- 在所有字段的末尾增加了一列role_main作为分区字段的值
    from test.t_all_hero;
    
    -- 查看表中的数据内容
    select * from test.t_all_hero_part2;
    
    -- 查看表中有哪些分区
    show partitions test.t_all_hero_part2;
    
    -- 为什么默认配置下,阻止我们使用动态加载呢?
    -- 因为该操作如果进行了误操作,很容易造成服务器崩溃.
    -- 例如: 我们要根据日期进行分区,一个月的数据分30个目录可以正常使用,但是我们误把创建时间(年月日十分秒)数据作为动态分区字段,
    -- 此时我们会拆分为 30 * 24 * 60 *60 个分区目录,系统直接崩溃.
    

• 分区表注意事项

  • 分区表的字段不能是表中已有的字段；分区的字段也会显示在查询结果上；

  • 分区的字段是虚拟的字段，出现在表所有字段的后面，其值来自于元数据。

  • 分区在底层的形式就是以文件夹管理不同的文件；不同文件夹就是表不同分区；文件夹的名字就是分区字段=值：

      /user/hive/warehouse/数据库.db/表
      					/分区字段=分区值1
      					    xxxx.txt
      					/分区字段=分区值2
      						zzzz.txt
    

  • 分区表是一种优化表，建表的时候可以不使用，但是，当创建分区表之后，使用分区字段查询可以减少全表扫描，提高查询的效率。

  注意: 分区表只有根据分区字段进行筛选时,才能提高查询效率,否则和普通表没有区别

  • 企业中常用的分区字段
    • 地域维度：省，市
    • 时间维度：day,month,year

• 多重分区表

  -- 多重分区表: 就是分区表使用了多个分区字段.
  -- 格式: partitioned by (多个字段) 即可
  
  -- 创建多重分区表
  create table db_1.student_duo_part
  (
  	id   int,
  	name string,
  	age  int
  )
  partitioned by (year int, month int, day int)
  row format delimited
  fields terminated by ',';
  
  -- 查看多重分区表元数据
  desc formatted db_1.student_duo_part;
  
  -- 给多重分区表静态加载数据
  load data local inpath '/root/hive_data/students.txt' into table db_1.student_duo_part partition (year=2020, month=12, day=12);
  load data local inpath '/root/hive_data/students.txt' into table db_1.student_duo_part partition (year=2021, month=11, day=6);
  load data local inpath '/root/hive_data/students.txt' into table db_1.student_duo_part partition (year=2020, month=9, day=7);
  
  -- 动态加载数据
  -- insert into db_1.student_duo_part partition (year, month, day)
  -- select *,year,month,day from db_1.student ;
  
  -- 如果是开发中,我们进行多重分区,最多分三层,如果分区层数过多,就会有大量的小文件出现, 在动态分区时内存消耗也非常大.
  -- 多重分区的每一层之间要有逻辑包含关系,否则后期维护十分困难.  省市区,  年月日
  

6.4、Hive分桶表（分簇表）[重中之重]

CLUSTERED BY xxx INTO N BUCKETS
--根据xxx字段把数据分成N桶
--根据表中的字段把数据文件成为N个部分

t_user(id int,name string);
--1、根据谁分？
 CLUSTERED BY xxx ；  xxx必须是表中的字段
--2、分成几桶？
 N BUCKETS   ；N的值就是分桶的个数
--3、分桶的规则？
clustered by id into 3 bucket

hashfunc(分桶字段)  %  N bucket  余数相同的来到同一个桶中
1、如果分桶的字段是数字类型的字段，hashfunc(分桶字段)=分桶字段本身
2、如果分桶的字段是字符串或者其他字段，hashfunc(分桶字段) = 分桶字段.hashcode

6.4.2、分桶的创建及加载

-- 创建一个分桶表,使用的分桶字段是表中已有的字段,必须用括号括起来,不需要书写数据类型.
CREATE TABLE test.t_usa_covid19_bucket
(
    count_date string,
    county     string,
    state      string,
    fips       int,
    cases      int,
    deaths     int
)
clustered by (state) into 5 buckets
row format delimited fields terminated by ',';

-- 查看表中的元数据
describe formatted test.t_usa_covid19_bucket;

-- 使用已有的数据表,将数据读取出来,然后把数据从内存中写入到分桶表中.
-- 1. 先创建一个不分桶的表
CREATE TABLE itheima.t_usa_covid19
(
    count_date string,
    county     string,
    state      string,
    fips       int,
    cases      int,
    deaths     int
)
row format delimited fields terminated by ',';

-- 2. 向未分桶表中添加数据  直接将数据上传到未分桶表中即可
-- 此处直接使用web页面上传,开发中不要这样操作

-- 3. 读取未分桶表中的全部数据,写入到分桶表中(这个指令会走MR任务,所以速度比较慢)
-- 格式: insert into 目标表名  查询集(完整的select语句)
-- 注意: 被插入数据的表中的字段数据量,和字段类型,以及字段顺序,要与读取出来额查询集中完全相同.
insert into test.t_usa_covid19_bucket
select *
from test.t_usa_covid19;

-- 4. 查询分桶表中的数据是否加载成功
select *
from test.t_usa_covid19_bucket;

-- 走mr任务时,由于要生成5个数据文件,会执行5个reduce任务

-- 分桶表的分桶规则: 哈希取模法/哈希取余法   取模就是取余数,  例如 7 / 2  商 3 余  1  此时取模结果就是1
-- 规则: 将分桶字段值取哈希值,然后按照分桶规则模以桶数. 根据余数不同分到不同的桶内
-- 哈希算法: 这个是一个散列函数,不需要了解其底层怎么计算的,我们只要知道这个算法可以将所有的数据计算为一个唯一的数值即可.
-- 一个值无论进行多少次哈希计算,最终结果都是相同的.
select hash('测试'); -- 汉字的哈希值多为负数
select hash('abc'); -- 字母符号的哈希值多为正数
select hash(1); -- 整数的哈希值就是数字本身
select hash(-5.1); -- 浮点型的哈希值是一个计算后的整数

-- 由于New York 和 Florida 的哈希值与5进行取模后值相等,所以在同一个分桶中.
select hash('New York'); -- 1382994575
select hash('Florida'); -- 898707645

6.4.5、分桶的总结

• 分桶表也是一种优化表，可以**减少join查询时笛卡尔积的数量**、提高抽样查询的效率(从全表随机,数量较大,分桶表可以在分桶文件内分别抽样,效率更高)。
  • 普通表join连接时,比对次数为左表条目数*右表条目数
  • 如果按照分桶字段进行连接,则比对次数会明显减少
• 分桶表的字段必须是表中已有的字段；
• 在join的时候，针对join的字段进行分桶，可以提高join的效率 减少笛卡尔积数量 (必须使用分桶字段进行链接,否则和普通表没有任何区别)。
• 分桶数量不宜过多,会造成很多的分桶文件没有数据,或者数据量过少
  • 举例: 按照班级学员的性别进行分桶, 分为5桶, 此时必有空桶.因为仅有两种性别.
• 分桶数量不宜过少,会造成数据倾斜(将一个任务分成多个map或reduce任务,某一个map或reduce任务中数据量巨大,其他任务中数据量较少.)
• 分桶的目的一般不是为了提高查询效率,而是为了提高表的连接效率.
  分桶和分区的区别:
• 分桶的目的是减少笛卡尔积的数量,提升连接性能,对于查询效率的提升是次要提升
• 分区对于查询效率的提升是主要提升
• 分桶字段真实存在,而分区字段是虚拟字段,是使用元数据映射出来的
• 分桶表是使用一定规则进行文件的拆分,而分区表是拆分为多个文件目录
  分桶和分区的共同点:
• 都可以提高查询效率
• 都是表的优化方式,并不是必须存在的.
• 都是只有按照分区或者分桶字段连接或查询才能提高效率,否则和普通表无异

6.5、Hive SQL–DDL其他操作

• 数据库操作

  -- 1. comment  给表或者库进行注释,在查询表的元数据信息时,可以显示在控制台中
  create table db_1.person1
  (
  	id   int comment 'primary key',
  	name string comment 'your name'
  ) comment 'describe person';
  
  -- 查看表的元数据信息
  describe formatted db_1.person1;
  
  -- 目前我们还不能使用中文注释,后续在新零售项目中会解决这个问题.
  
  -- 2. dbproperties 数据库属性
  -- 可以给数据库增加注释
  create database db_2 comment 'Second DB' with dbproperties ('chuanzhi' = 'good');
  
  
  -- 修改数据库属性值, 当添加属性的键存在时,则为修改, 如果该键不存在则为新增.
  alter database db_2 set dbproperties ('chuanzhi' = 'youxiu');
  alter database db_2 set dbproperties ('teacher' = 'zeiyouxiu');
  
  describe database extended db_2;
  
  -- 3. 修改数据库属性
  alter database db_2 set owner user hive;
  

• 数据表操作

  -- 4. 更改表名  rename to
  -- 修改表名时一定要指定数据库名称,否则会移动到其他数据库
  alter table db_1.student2
  	rename to db_1.student_2;
  show tables in db_1;
  
  -- 5. 更改表的属性
  alter table person_1
  	set tblproperties ('chuanzhi' = 'youxiu');
  -- 修改表的类型为外部表
  alter table person_1
  	set tblproperties ('EXTERNAL' = 'TRUE');
  -- 修改表的注释信息
  alter table person_1
  	set tblproperties ('comment' = 'test_tb');
  -- 修改表的分隔符
  alter table person_1
  	set serdeproperties ('field.delim' = ',');
  
  desc formatted person_1;
  
  -- 6. 字段的增加
  alter table person_1
  	add columns (gender string);
  
  -- 7. 修改字段
  alter table person_1
  	replace columns (gender int);
  

• 数据表分区操作

  -- 创建一个分区表,如果存在则先删除
  drop table if exists test.t_user_province;
  create table test.t_user_province
  (
  	num  int,
  	name string,
  	sex  string,
  	age  int,
  	dept string
  ) partitioned by (province string);
  
  -- 向分区表中加载数据
  load data local inpath '/root/hive_data/students.txt' into table test.t_user_province partition (province = 'SZ');
  load data local inpath '/root/hive_data/students.txt' into table test.t_user_province partition (province = 'BJ');
  
  -- 1. 分区的重命名
  -- 只要是使用alter table就不会修改数据记录文件中的内容.
  alter table test.t_user_province
  	partition (province = 'SZ') rename to partition (province = 'shenzhen');
  
  -- 2. 添加空分区
  alter table test.t_user_province
  	add partition (province = 'GZ');
  alter table test.t_user_province
  	add partition (province = 'CS') partition (province = 'HZ');
  --一次性增加多个分区
  
  -- 3. 删除分区
  alter table test.t_user_province
  	drop partition (province = 'shenzhen');
  alter table test.t_user_province
  	drop partition (province = 'HZ');
  -- 当我们删除分区时,且hdfs开启垃圾桶机制,会将数据存入垃圾桶中.
  -- 如果我们使用 purge 进行标记,则不会放入垃圾桶,直接删除.
  alter table test.t_user_province
  	drop partition (province = 'shenzhen') purge;
  -- 慎用
  
  -- 4. 修复分区
  -- MSCK REPAIR TABLE table_name [ADD/DROP/SYNC PARTITIONS];
  -- 表目录 中存在的分区目录,在元数据中不存在则添加
  msck repair table test.t_user_province add partitions;
  -- 表目录 中不存在的分区目录,在元数据中存在则删除
  msck repair table test.t_user_province drop partitions;
  -- 既使用add 又使用drop 则我们可以使用sync (使表目录中的分区与元数据中统一)
  msck repair table test.t_user_province sync partitions;
  
  -- 查看当前表中的所有分区
  show partitions test.t_user_province;
  

• Hive SQL中常见的show语法

  show databases
  show tables
  show partitions
  desc formatted table_name; 查看表的元数据信息
  show create table table_name; 获取表的DDL建表语句
  show functions;

  --1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样
  show databases;
  
  --2、显示当前数据库所有表/视图/物化视图/分区/索引
  show tables;
  SHOW TABLES [IN database_name]; --指定某个数据库
  
  
  --3、显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错
  show partitions table_name;
  show partitions itheima.student_partition;
  
  --4、显示表、视图的创建语句
  SHOW CREATE TABLE ([db_name.]table_name|view_name);
  show create table student;
  
  --5、显示当前支持的所有自定义和内置的函数
  show functions;
  
  --6、Describe desc
  --查看表信息（格式化美观）
  desc formatted table_name;
  
  
  -- 视图的展示
  create view student_view as select * from itheima.student_plus;
  
  select * from student_view;
  
  desc formatted student_view;
  

6.6、Hive DML 数据操纵语言

• load加载数据

--1. 建表student_local 用于演示从本地加载数据
create table student_local
(
    num  int,
    name string,
    sex  string,
    age  int,
    dept string
) row format delimited fields terminated by ',';
-- 本地其实说的就是node1 也就是hive服务部署到哪一个服务器,这个服务器的Linux系统就是本地文件系统
-- 从本地加载到hive表中的数据,会复制一份加载到hdfs上,本地数据不会清空
load data local inpath '/root/hive_data/students.txt' into table student_local;


-- 2. 建表student_HDFS  用于演示从HDFS加载数据到非分区表
create table student_HDFS
(
    num  int,
    name string,
    sex  string,
    age  int,
    dept string
) row format delimited fields terminated by ',';

-- 在hdfs中创建一个hive_data目录
-- hadoop fs -mkdir /test/hive_data
-- 将linux中的students.txt文件上传到hdfs中
-- hadoop fs -put hive_data/students.txt /test/hive_data

-- 将hdfs上的数据内容加载到student_HDFS表中
load data inpath 'hdfs://node1:8020/test/hive_data/students.txt' into table student_HDFS;
-- 再次加载数据时students.txt文件消失,因为使用loaddata 从hdfs向表中加载数据时为移动该数据.
load data inpath '/test/hive_data/students.txt' into table student_HDFS;


-- 3. 建表student_HDFS_b  用于演示从HDFS加载数据到分桶表
create table student_HDFS_b
(
    num  int,
    name string,
    sex  string,
    age  int,
    dept string
)
    clustered by (num) into 3 buckets
    row format delimited fields terminated by ',';

-- 从hdfs中向分桶表中添加数据
load data inpath '/test/hive_data/students.txt' into table student_HDFS_b;

-- 4. 建表student_HDFS_p 用于演示从HDFS加载数据到分区表
create table student_HDFS_p
(
    num  int,
    name string,
    sex  string,
    age  int,
    dept string
) partitioned by (country string) row format delimited fields terminated by ',';

-- hdfs中向分区表中添加数据.必须指定分区值否则无法添加
load data inpath '/test/hive_data/students.txt' into table student_HDFS_p partition (country = 'BJ');

-- 5. 向表中覆盖数据
-- 默认使用into table 是追加数据
load data local inpath '/root/hive_data/students.txt' into table student_local;

select *
from student_local;
-- 加载三次一共有66条数据

-- 如果需要覆盖数据可以使用 overwrite 关键字
load data local inpath '/root/hive_data/students.txt' overwrite into table student_local;

select *
from student_local; -- 覆盖成功后,一共有22条数据

• Hive中insert用法

-- 创建一张源表student
drop table if exists student;
create table student
(
    num  int,
    name string,
    sex  string,
    age  int,
    dept string
)
    row format delimited
        fields terminated by ',';

-- 将 student_HDFS表中的数据插入到student表中, 并且将每一个人的年龄增加一岁
insert into student
select num, name, sex, age + 1 as age, dept
from student_hdfs;

select *
from student;

-- 将查询到的数据插入到指定的表中还可以使用 create table as
create table student_c as
select num, name, sex, age + 1 as age, dept
from student_hdfs;

select *
from student_c;

-- 注意:
-- 1. create table 创建的表不能存在, insert into 可以给表追加数据
-- 2. create table as 的方法执行效率比insert into select 块很多, 因为create table 是DDL语句, 而insert into select 是DML语句
-- 底层实现时, create table as 是将数据一次写入, insert into select 是一行一行写入.


-- 多重插入
-- 读取一次数据,插入多次,此时插入数据的效率会有所提升
create table source_table
(
    id   int,
    name string
) row format delimited fields terminated by ',';

create table test_insert1
(
    id int
) row format delimited fields terminated by ',';

create table test_insert2
(
    name string
) row format delimited fields terminated by ',';

-- 向 source_table里导入数据
load data local inpath '/root/hive_data/students.txt' into table source_table;

-- 查询数据内容
select * from source_table;

-- 将 id 插入到 test_insert1表中
insert into test_insert1 select id from source_table;

-- 将 name 插入到 test_insert2表中
insert into test_insert2 select name from source_table;

-- 上述插入方式,读取了两次source_table表 也插入了两次, 但是两次读取的是同一张表
-- 使用多重插入方法就可以实现一次读取,多次插入
from source_table
insert overwrite table test_insert1 select id
insert overwrite table test_insert2 select name;

• Hive中的数据导出[导出是覆盖操作，不要轻易使用]

-- 1. 从hdfs中导出数据到hdfs中的指定目录下
insert overwrite directory '/test/output_dir' select * from student_local;

-- 查询数据源数据是否存在, 导出数据时,不会移动,修改,删除原数据.
select * from student_local;

-- 2. 导出数据时指定分隔符
insert overwrite directory '/test/output_dir' row format delimited fields terminated by '❤' select * from student_local;

-- 3. 导出数据时,会将目录中的全部数据清空
insert overwrite directory '/test' row format delimited fields terminated by ',' select * from student_local;

-- 4. 将数据导出到hive服务所在的linux系统中
insert overwrite local directory '/root/hive_data' select * from student_local;

-- 注意: 没有insert into directory这种写法
-- 在开发中我们更多的会使用ETL工具或者数据采集工具进行数据导出. 例如 kettle, sqoop

七、Apache Hive SQL-DQL、参数配置与函数

7.1、Hive SQL–DQL-Select

7.1.1、select语法树

-- 1. 基础查询
select *
from test.student_local;
select num, name
from test.student_local;
select name, age + 10
from test.student_local;
select name, 1, 2, 3, 4
from test.student_local;

-- 2. 条件查询
select *
from test.student_local
where age > 20;
select *
from test.student_local
where sex = '女';
select *
from test.student_local
where age > 20
  and sex = '女';
select *
from test.student_local
where age between 18 and 20; -- 这个范围中包含18和20
select *
from test.student_local
where age in (18, 20);
select *
from test.student_local
where name like '李%';
select *
from test.student_local
where name is null;

-- 3.聚合查询
select count(*)
from test.student_local;
select max(age)
from test.student_local;

-- 4.分组查询
select sex, max(age)
from test.student_local
group by sex;
select dept, count(1)
from test.student_local
group by dept;

-- 5. 分组后筛选
select sex, max(age)
from test.student_local
group by sex
having max(age) > 20;

select dept, count(1) as total
from test.student_local
group by dept
having total > 3;

-- 6. 排序函数
select *
from test.student_local
order by age; -- 升序
select *
from test.student_local
order by age desc; -- 降序

-- 7. 分页查询
select * from test.student_local limit 2;
select * from test.student_local limit 3, 3;

7.2.2、CLUSTER BY分桶查询

-- 1. 对查询到的数据进行分桶排序
-- 普通查询
select * from student;
-- 分桶查询
select * from student cluster by num;

-- 问题: 是否分桶 结果完全相同,使用了cluster by 也没有明显的效果
-- 原因: 分桶的数量与reducetask的数量有关,如果没有设置reducetask 则默认为1个
-- 解决办法: 手动设置reducetask数量 , 如果该值为-1,则根据查询语句规则自动定义reduce数量.
set mapreduce.job.reduces;
set mapreduce.job.reduces = 3; -- 设置reducetask数量为3

-- 再次分桶查询
-- 思考:分桶查询可以查询非分桶表么? 分桶查询和原表是否分桶没有任何关系,是将数据查询后才进行分桶规则判断的,所以原数据是否分桶都没关系.
select * from student cluster by num;
-- 观察发现,将num哈希取余后,根据余数不同,将数据记录存放在的不同的桶中, 有3个reduce任务,就产生了三个分桶
-- 在每个桶的内部对于数据进行了升序排列

-- 使用order by 不分桶直接排序
select * from student order by num;
-- order by 又叫做全局排序,无论reducetask数量是几,最终我们只会使用一个reducetask
-- 在数据量较大时,该reduce任务的任务量巨大,所以开发时我们一般不建议使用order by , 如果非要使用, 我们会配合 where, 或者limit 进行使用.

-- 思考: cluster by可以指定升序降序么?  不可以,只能使用默认的升序排列方式.

7.3.3、DISTRIBUTE BY+SORT BY(记住)

-- 普通查询
select *
from student;
-- 分桶查询 cluster by
select *
from student cluster by num;

set mapreduce.job.reduces =2;
-- 将使用distribute by + sort by 将指定不同的字段为分桶字段和排序字段.
select *
from student distribute by dept;

select *
from student distribute by dept sort by num;

select *
from student distribute by num sort by age desc;

-- 结论:
-- 1. distribute by 只负责分桶工作
-- 2. sort by 负责将分桶的结果进行桶内排序
-- 3. 当分桶字段和排序字段为同一个字段,且排序规则为升序时, cluster by >>> distribute by num sort by

7.4.4、union联合查询 [重要且简单]

-- union 联合查询,联合查询和join相似,只不过join时左右连接,union是上下连接
-- union可以将两个查询集上下连接到一个查询集(虚拟表结构)中
-- 1. 查询年龄大于20岁的女生
select *
from student
where age > 20
  and sex = '女';

-- 2. 查询年龄小于20岁的男生
select *
from student
where age < 20
  and sex = '男';

-- 3. 使用union联合查询将上述两个查询集链接到一起
-- union 联合查询,无论如何都会走mr程序
-- 无论联合查询前是什么顺序,联合后都会重新排序,且顺序无规律.
-- 如果需要排序,可以联合查询之后,对其结果统一排序
select *
from student
where age > 20
  and sex = '女'
union
select *
from student
where age < 20
  and sex = '男'
order by age;

-- 4. 使用联合查询将第二步的数据连接两次,最终有多少条记录???? 原始数据有6条
-- union 联合查询后一共有6条数据
-- 因为union 联合查询 如果不加任何修饰符 默认等价于 union distinct  默认去重(所有字段值均相同)
select *
from student
where age < 20
  and sex = '男'
union
select *
from student
where age < 20
  and sex = '男';

-- 如果我们不希望union自动去重 可以使用union all
select *
from student
where age < 20
  and sex = '男'
union all
select *
from student
where age < 20
  and sex = '男';

-- 5. 思考: union联合查询时,上下两个表中的字段数量和类型是否必须完全相同???  数据类型必须相同, 数据数量也必须相同
-- select num, name from student
-- union
-- select name, num from student;

-- select num, name
-- from student
-- union
-- select num, name, age
-- from student;

-- 总结:
-- 1. union联合查询就是将上下两张表连接在一起
-- 2. union联合查询会打乱联合之前的排序.所以一般联合之后要重新排序
-- 3. union联合查询后会自动去重.如果不需要去重,则使用union all
-- 4. union联合查询时上下两张表数据类型必须相同, 数据数量也必须相同

7.5.5、CTE表达式(记住最简单的调用形式即可)

-- 使用select 语句查询一个结果集,使用with as 语法将其临时保存并命名,在当前sql语句中可以查询使用,sql语句结束,结果集释放.
-- 格式: with 临时表名 as (select结果集)  select 查询语句
with t1 as (select * from student)
select *
from t1;

-- 需求: 获取与年龄最大的两个女生在相同部门的男生信息

-- 子查询:
select *
from student
where sex = '男'
  and dept in (select dept from student where sex = '女' order by age desc limit 2);

-- CTE表达式
with top_2nv_dept as (select dept from student where sex = '女' order by age desc limit 2)
select s.*
from student s
         join top_2nv_dept t on s.dept = t.dept
where sex = '男';


-- CTE表达式链式调用
-- 我们在CTE表达式中可以一次创建多个临时表,且后创建的临时表,可以调用先次创建的临时表
with q1 as (select * from student where num = 95002),
     q2 as (select num, name, age from q1)
select *
from (select num from q2) a;

-- 在同一个select 语句中,可以使用所有的临时表
with q1 as (select num, name from student where sex = '男' and age > 20),
     q2 as (select num, name from student where sex = '女' and age < 20)
select *
from q1
union
select *
from q2;

-- 思考: CTE表达式在sql语句结束后还能否调用到之前的临时表呢? 不行
-- select * from q1;

set mapreduce.job.reduces;

7.2、Hive SQL join查询(左连接,内连接)

7.2.1、Hive支持join语法

--Join语法练习 建表
drop table if exists test.employee_address;
drop table if exists test.employee_connection;
drop table if exists test.employee;

--table1: 员工表
CREATE TABLE test.employee
(
    id     int,
    name   string,
    deg    string,
    salary int,
    dept   string
) row format delimited
    fields terminated by ',';

--table2:员工家庭住址信息表
CREATE TABLE test.employee_address
(
    id     int,
    hno    string,
    street string,
    city   string
) row format delimited
    fields terminated by ',';

--table3:员工联系方式信息表
CREATE TABLE test.employee_connection
(
    id    int,
    phno  string,
    email string
) row format delimited
    fields terminated by ',';

-- 给表添加数据
-- hadoop fs -put hive_data/employee.txt /user/hive/warehouse/test.db/employee
-- hadoop fs -put hive_data/employee_address.txt /user/hive/warehouse/test.db/employee_address
-- hadoop fs -put hive_data/employee_connection.txt /user/hive/warehouse/test.db/employee_connection

-- 查询表中的数据是否添加成功
select *
from test.employee;
select *
from test.employee_address;
select *
from test.employee_connection;

-- 1. 内连接  inner join
-- 内连接将左表和右表中的指定字段内进行匹配,保留匹配成功的数据记录,忽略没有匹配成功的数据.
select *
from test.employee e
         inner join test.employee_address ea
                    on e.id = ea.id;
-- 内连接中 inner可以省略
select *
from test.employee e
         join test.employee_address ea
              on e.id = ea.id;

-- 2. 左外连接  left outer join
-- 左连接将左表和右表中的指定字段进行匹配,保留左表中全部的数据,右表中的数据匹配成功则保留,匹配不成功则填充null
select *
from test.employee e
         left outer join test.employee_address ea
                         on e.id = ea.id;

-- 外连接中可以省略outer
select *
from test.employee e
         left join test.employee_address ea
                   on e.id = ea.id;

-- 3. 右外连接  right outer join
-- 右连接将左表和右表中的指定字段进行匹配,保留右表中全部的数据,左表中的数据匹配成功则保留,匹配不成功则填充null
select *
from test.employee e
         right outer join test.employee_address ea
                          on e.id = ea.id;

-- 外连接中可以省略outer
select *
from test.employee e
         right join test.employee_address ea
                    on e.id = ea.id;

-- 4. 全连接  full join
-- 全连接将左表和右表中的指定字段进行匹配,保留左表和右表中的全部数据,如果匹配成功则保留数据,不成功则填充null
select *
from test.employee e
         full join test.employee_address ea on e.id = ea.id;

-- 5. 左半连接 left semi join
-- 其实就是左表和右表进行内连接, 但是仅可以获取到左表中匹配成功的数据,右表全部记录将被删除
select *
from test.employee e
         left semi
         join test.employee_address ea on e.id = ea.id;

-- 左半连接,在开发中使用的场景与内连接比较相似,但是左半连接指向效率更高,因为只获取左表中的部分数据.

-- 6. 交叉连接
-- 交叉连接其实就是进行笛卡尔积计算, 左表中的每一条记录,依次和右表中的每一条进行匹配,物品匹配是否成功都保留.
select *
from test.employee e
         cross join test.employee_address ea;

7.2.2、join查询优化及注意事项

• 允许使用复杂的联接表达式；(可以使用大于小于号进行连接条件判断)

• 同一查询中可以连接2个以上的表；

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

  • a表中的id 值连接b表中的id b表中的id值又连接了c表中的id ,此时b表中的id就是一个中间列,此时转换为单个mr任务

    

  • a 表中的id 值 连接了b表中的id 值, b表中的age 字段,连接了c表中的age字段,此时还是多个mr任务
    

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

  • 多张表连接,先计算前两张表的连接结果,再将连接结果发送给第三张表所在的服务,连接结果再发送给第四张表
  • 如果大表在最后一个位置,大表流转次数就会减少,此时内存占用就会降低

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

7.3、Hive Shell命令行[用的不多但十分重要]

批处理：一次连接，一次交互， 执行结束断开连接
交互式处理：保持持续连接， 一直交互
注意：如果说hive的shell客户端 指的是第一代客户端bin/hive
hive客户端其实是shell命令行控制hive的一种方式,是原生shell脚本与服务端的交互
而第二代客户端bin/beeline属于JDBC客户端 不是shell。
beeline是将数据发送给jdbc驱动,由驱动指挥hive完成指令

• bin/hive

  • 功能1：作为第一代客户端 连接访问metastore服务，使用Hive。交互式方式

  • 功能2：启动hive服务

    /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore 
    /export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2 
    

  • 功能3：批处理执行Hive SQL

    #-e 执行后面的sql语句
    /export/server/hive/bin/hive  -e 'select * from itheima.students;'
    
    #-f 执行后面的sql文件(重要)
    vim hive.sql
    select * from itheima.student limit 2
    
    /export/server/hive/bin/hive  -f hive.sql
    
    #sql文件不一定是.sql 要保证文件中是正确的HQL语法。
    
    #-f调用sql文件执行的方式 是企业中hive生产环境主流的调用方式。
    

• hive参数配置方式

  • 有哪些参数可以配置？

     https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
    

  • 配置方式有哪些？ 注意配置方式影响范围影响时间是怎样？

    • 方式1：配置文件 conf/hive-site.xml(影响整个由此安装包启动的服务)

    • 无论服务重启多少次,只要是配置文件没有修改,影响一直存在

      影响的是基于这个安装包的任何使用方式。
      

    • 方式2：配置参数 –hiveconf(影响由被启动的服务创建的所有会话)

    • 如果服务重启,没有设置该配置,则配置失效,按照配置文件进行启动

      /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore  
      
      /export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2  --hiveconf 		hive.root.logger=DEBUG,console
      
      #影响的是session会话级别的
      

    • 方式3：set命令 (影响当前会话)

      • 服务启动后,谁连接此服务,就对当前连接服务的会话进行修改,其他会话不影响

        session会话级别的 设置完之后将会对后面的sql执行生效。
        session结束 set设置的参数将失效。
        
        也是推荐搭建使用的设置参数方式。  谁需要 谁设置 谁生效
        

• 总结(重要)
  • 从方式1到方式3 影响的范围是越来越小的。
  • 从方式1到方式3 优先级越来越高。set命令设置的会覆盖其他的。
  • Hive作为的基于Hadoop的数仓，也会把Hadoop 的相关配置 解析加载进来。(hive也可以一定程度上使用配置信息修改hadoop的参数)

7.4、Hive内置运算符[理解]

-- 1. 查看所有的内置运算符
show functions;

-- 2. 查看运算符的含义
desc function +;
desc function `current_timestamp`;

-- 3. 查看函数或运算符的使用方法
desc function extended `current_timestamp`;

desc function extended sum;

desc function extended avg;

desc function extended concat;

-- hive中的关系运算符
-- 1. 比较运算符: = > < >= <= != <>
select 1 > 2;

-- 2. 空值判断 :  is null | is not null
-- select * from student where num is null;
select null is not null; -- flase
select 'null' is not null;
-- null
-- null 可以使用=判断么?  不可以
-- select null = null;  -- null 和任何数据比较,判断都为空

-- 3. 模糊查询
select '江湖再见' like '%湖%';
-- 模糊查询在hive中很少使用,因为效率较低

-- 4. 正则匹配 -- 就是一个字符串的匹配规则
select 'testdemo' rlike 'testdemo';
select 'testdemo' rlike '.*de.*';
select '13932627450' rlike '1[3-9]\\d{9}';
-- 在正则表达式中使用\要使用两个,才能输出\

-- 5. regexp 也是正则匹配和rlike完全相同
select 'testdemo' regexp '.*de.*';

-- hive中的算数运算符
-- 1. + - * / % div
-- 11 / 2 = 商 5 余 1
select 11 % 2; -- 1  取余
select 11 div 2;

-- 2. 按位与操作 -- 同一个数位两个数据都为1则结果为1, 否则为0
-- 4 >>> 0100
-- 8 >>> 1000
-- 结果   0000
select 4 & 8;
select 7 & 9;
-- 7 >>> 0111   9 >>> 1001

-- 3. 按位或操作 -- 同一个数位两个数据有任意一个为1则结果为1, 否则为0
select 4 | 8; -- >>> 1100
select 7 | 9;
-- >>> 1111

-- 4. 按位异或  -- 同一个数位两个数据若不同则结果为1, 否则结果为0
select 4 ^ 8; -- >>> 1100
select 7 ^ 9;
-- >>> 1110

-- 异或的其他用法
-- 同一个数字连续异或两次相同的数字还是原数字
select 4 ^ 8 ^ 8; -- 4
select 8 ^ 4 ^ 4; -- 8

-- hive中的逻辑运算
-- 1. and 逻辑与 -- 同真即真
select true and false;
select true and true;

-- 2. or 逻辑或  -- 同假即假
select true or false;
select true or true;
select false or false;

-- 3. not 逻辑非 -- 真假互换
select not true;
select not false;

八、Hive函数

8.1、函数的分类

-- UDF : 一进一出
select round(12.444, 2);
select unix_timestamp('2020-11-12 12:00:00');

-- UDAF : 多进一出  聚合函数
select avg(age) from student;

-- UDTF : 一进多出  高阶函数
select explode(`array`(1,2,3,4));

8.1.1、内置的函数（build in func）

所谓的内置指的是hive开发好，可以直接上手使用的；

• 日期函数、数字函数、字符串函数、集合函数、条件函数…

• 常用内置函数
  • 字符串函数

    -- 1. 字符串连接
    -- concat(字符串1, 字符串2 ....)
    select concat('it', '行业', '真好', '工作', '岗位', '很多');
    
    -- concat_ws(间隔符, 字符串1, 字符串2,...)
    -- 根据指定的间隔符,将字符串拼接到一起
    select concat_ws('❤', '小明', '小芳');
    
    -- 2. 字符串拆分
    -- split(字符串, 间隔符) --将字符串按照指定间隔符拆分为array类型数据
    select split('hello python', ' ');
    -- 间隔符也可以使用正则表达式.
    
    -- 3. 字符串截取
    -- substr >>> substring (字符串, 截取位置, 截取长度) 截取位置从1开始
    select substr('飒爽英姿五尺枪,曙光初照演兵场!', 5, 3); -- 五尺枪
    select substr('飒爽英姿五尺枪,曙光初照演兵场!', 13, 3); -- 演兵场
    select substr('飒爽英姿五尺枪,曙光初照演兵场!', -4, 3); -- 演兵场
    
    -- 4. json数据解析 json数据是一种数据传输流 使用的是 array类型数据和map类型数据相互嵌套的数据形式
    select get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"},' ||
                   ' {"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '$[0].website');
    

  • 日期函数

    -- 1.获取当前日期
    select `current_timestamp`();
    select `current_date`();
    
    -- 2. 获取时间戳
    -- 时间戳就是从1970年1月1日 0时0分0秒到现在的秒数或毫秒数
    select unix_timestamp(); -- 如果()内什么也不写,就是获取当前时间的时间戳
    -- 获取指定时间的时间戳
    select unix_timestamp('2020-11-12 12:22:33'); -- 1605183753
    -- 获取时间戳,时间字符串必须按照 yyyy-MM-dd HH:mm:ss的格式进行书写
    select unix_timestamp('2020-11-12');
    
    -- 3. 通过时间戳获取 日期数据
    select from_unixtime(1605183753); -- 2020-11-12 12:22:33
    -- 获取日期时可以指定日期数据的格式
    select from_unixtime(0, 'yyyy年MM月dd日 HH点mm分ss秒');
    
    -- 4. 日期数据格式化 -- 事件类型以及标准时间格式的字符串都可以进行转换
    -- date_format
    select date_format(`current_date`(),'yyyy年MM月dd日 HH点mm分ss秒');
    select date_format('2022-3-12 12:33:44','yyyy年MM月dd日 HH点mm分ss秒');
    
    -- 5. 日期差值计算
    -- datediff 计算两个日期之间的差值, 代为为天, 前边的时间减去后边的时间
    select datediff('2022-3-12 12:33:44', `current_date`());
    
    -- 6. 日期偏移
    select date_add('2022-3-12 12:33:44', 5);
    select date_sub('2022-3-12 12:33:44', 5);
    
    -- 日期偏移量可以为负数
    select date_add('2022-3-12 12:33:44', -8);
    
    desc function extended date_add;
    

  • 数学函数

    --取整函数: round  返回double类型的整数值部分 （遵循四舍五入）
    select round(3.1415926);
    --指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
    select round(3.1415926,4);
    --向下取整函数: floor
    select floor(3.1415926); -- 3
    select floor(-3.1415926); -- -4
    --向上取整函数: ceil
    select ceil(3.1415926); -- 4
    select ceil(-3.1415926); -- -3
    --取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
    select rand();
    --指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
    select rand(0);
    
    -- 计算: 使用round 进行向上取整
    select round(9.65 + 0.5);
    -- 计算: 使用round 进行向下取整
    select round(-7.685 - 0.5);
    

  • 条件函数

    -- 1. if函数
    -- if (条件, 成立返回第一个值, 不成立返回第二个值)
    select *,
    	`if`(sex = '男', '男生', '女生')
    from student;
    
    -- 2. case when 同一个字段使用casewhen只能返回一个数据,也就是只有一个then会被执行.
    /*
    格式1:
    case
    	when 条件1
    		then 条件1成立时返回的数据
    	when 条件2
    		then 条件2成立时返回的数据
    	.....
    	else 所有条件均不成立时返回的数据
    	end
    */
    select num,
    	name,
    	sex,
    	age,
    	case
    		when dept = 'CS'
    			then '计算机科学'
    		when dept = 'IS'
    			then '信息技术'
    		when dept = 'MA'
    			then '数学'
    		else '未知学科'
    		end
    		as dept
    from student;
    
    /*
    格式2:
    case 判断字段
    	when 字段值1
    		then 字段等于值1时返回的数据
    	when 字段值2
    		then 段等于值2时返回的数据
    	.....
    	else 所有条件均不成立时返回的数据
    	end
    */
    select num,
    	name,
    	sex,
    	age,
    	case dept
    		when 'CS'
    			then '计算机科学'
    		when 'IS'
    			then '信息技术'
    		when 'MA'
    			then '数学'
    		else '未知学科'
    		end
    		as dept
    from student;
    
    -- 在开发中第一种格式使用场景更多,因为第二种只限制了字段等于某值的情况,而第一种格式可以进行各种复杂判断例如大于小于,包含不包含等,还可以在判断时进行计算.
    
    -- 3. nvl 判断数据是否为空,如果不为空则返回该值,如果为空则返回默认值
    select nvl('itcast','default');
    select nvl(null,'default');
    
    -- 4. coalesce 获取所有数据中第一个不为空的数据
    -- 从左至右获取第一个不为空的数据值,如果所有数据都为null则返回null
    select coalesce(null, 1, null, 2);
    select coalesce(null, null, null, 2);
    select coalesce(null, null, null, null);
    

  • 类型转换函数

    -- 类型转换函数cast
    -- 格式 cast(数据 as 类型)
    
    select cast('2022-11-12' as date);
    select '12';
    select cast('12' as double);
    select cast('12' as int);
    
    -- 在hive中数据类型可以自动转换,并不是那么敏感,但是后续使用presto 或这 impala的时候数据类型要求极其严格
    -- 举例:
    select date_format('2022-11-12', 'yyyy年-MM月');
    -- 在hive中上述内容可以执行,但是在presto中date_format中的第一个参数必须是时间类型参数.否则无法使用.
    
    select '12' + '13';  -- 15
    select concat(12, 13);  -- 1213
    

  • 脱敏函数

    --mask
    --将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
    select mask("abc123DEF");
    select mask("abc123DEF中国",'-','.','^'); --自定义替换的字母
    
    --mask_first_n(string str[, int n]
    --对前n个进行脱敏替换
    select mask_first_n("abc123DEF",4);
    
    --mask_last_n(string str[, int n])
    select mask_last_n("abc123DEF",4);
    
    --mask_show_first_n(string str[, int n])
    --除了前n个字符，其余进行掩码处理
    select mask_show_first_n("abc123DEF",4);
    
    --mask_show_last_n(string str[, int n])
    select mask_show_last_n("abc123DEF",4);
    
    --mask_hash(string|char|varchar str)
    --返回字符串的hash编码。
    select mask_hash("abc123DEF");
    

8.1.2、用户定义函数（user-defined function）

用户编程实现函数的逻辑在hive中使用。

• UDF根据函数输入行数和输出行数进行分类
• UDF 、UDAF、UDTF

#1、UDF（User-Defined-Function）普通函数 一进一出  输入一行数据输出一行数据

0: jdbc:hive2://node1:10000> select split("allen woon hadoop"," ");
+----------------------------+--+
|            _c0             |
+----------------------------+--+
| ["allen","woon","hadoop"]  |
+----------------------------+--+

#2、UDAF（User-Defined Aggregation Function）聚合函数，多进一出 输入多行输出一行

count sum max  min  avg

#3、UDTF（User-Defined Table-Generating Functions）表生成函数 一进多出 输入一行输出多行

explode 、parse_url_tuple

• UDF分类标准的扩大化

  • 本来，udf/udtf/udaf3个标准是针对用户自定义函数分类的；
  • 但是，现在可以将这个分类标准扩大到hive中所有的函数，包括内置函数和自定义函数；
  • 不要被UD这两个字母所影响。 Built-in Aggregate Functions (UDAF)。

九、Hive的函数高阶应用

9.1、explode函数[重要] & lateral view 侧视图[重要]

explode属于UDTF函数，表生成函数，输入一行数据输出多行数据。
侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表

-- 如果想学习explode函数怎么使用,需要使用desc进行查询
desc function extended explode;

-- 使用explode将array数据爆炸为单列多行
select explode(`array`(1, 2, 3, 4)) as num;

-- 使用explode将map数据爆炸为多列多行
select explode(`map`('name', '小明', 'age', 18)) as (c1, c2);


-- 夺冠球队案例
-- 1. 建表
create table the_nba_championship
(
	team_name     string,
	champion_year array<string>
) row format delimited
	fields terminated by ','
	collection items terminated by '|';

-- 2.上传数据
load data local inpath '/root/hive_data/The_NBA_Championship.txt' into table the_nba_championship;

-- 3.查询数据是否上传成功
select *
from the_nba_championship;

-- 4. 将夺冠的年份爆炸为多行
-- 爆炸后,多个年份对应一个队名,不符合结构化数据规则.不能这样书写
-- select team_name, explode(champion_year) from the_nba_championship;

-- 5. 我们可以使用表连接的方式进行合并
select name_list.team_name,
	year_list.year
from the_nba_championship name_list -- 获取所有的球队名称列表
		join
		(select explode(champion_year) as year from the_nba_championship) year_list --获取所有的球队夺冠年份列表
			on array_contains(name_list.champion_year, year_list.year);

-- 6. 上边的方法有点复杂,我们可以使用侧视图,非常简单的完成爆炸函数和原表数据的合并
select *
from the_nba_championship lateral view explode(champion_year) t as year;

-- 侧视图的意义: 就是将UDTF函数在拆分为多行后,还可以与原数据表进行连接,效率更高,写法更简单.

9.2、多行转单列

• 数据收集函数

  collect_set --把多行数据收集为一行  返回set集合  去重无序
  collect_list --把多行数据收集为一行  返回list集合  不去重有序
  

--建表
create table row2col2
(
    col1 string,
    col2 string,
    col3 int
) row format delimited fields terminated by '\t';

-- 查看数据是否上传成功
select *
from row2col2;


-- 1. 将col1和col2内的相同数据进行合并,此时需要使用group by
select col1,
       col2
from row2col2
group by col1, col2;

-- 2. 获取col3中所有的数据,添加到一个字段值中
select col1,
       col2,
       collect_list(col3) as col3_list -- collect_list()作用就是将多行数据转换为一个array类型数据
from row2col2
group by col1, col2;

-- 3. 使用字符串拼接函数 concat_ws 将数组类型数据拼接为 1-2-3这种格式的数据
-- :Argument 2 of function CONCAT_WS must be "string or array<string>", but "array<int>" was found.
-- select col1,
--        col2,
--        concat_ws('-',collect_list(col3)) as col3_list -- collect_list()作用就是将多行数据转换为一个array类型数据
-- from row2col2
-- group by col1, col2;

desc function extended concat_ws;
desc function extended concat;

-- 4. concat_ws只能使用内部是string类型数据的数组,不能使用内部是int类型数据的数组.
select col1,
       col2,
       concat_ws('-', collect_list(cast(col3 as string))) as col3 -- collect_list()作用就是将多行数据转换为一个array类型数据
from row2col2
group by col1, col2;

9.3、单列转多行

• 技术原理： explode+lateral view

--创建表
create table col2row2
(
    col1 string,
    col2 string,
    col3 string
) row format delimited fields terminated by '\t';

-- 检测数据是否上传成功
select *
from col2row2
limit 1000;

-- 1. 将 col3 中的数据内容,从字符串类型转换为数组类型数据.
select col1,
       col2,
       split(col3, ',') as col3
from col2row2;

-- 2. 将col3中的数组信息炸开,炸开称为多行数据
select explode(split(col3, ','))
from col2row2;

-- 3. 使用侧视图,将UDTF函数的结果和原数据表进行连接
select col1, col2, col_3
from col2row2 lateral view explode(split(col3, ',')) t1 as col_3;

9.4、json格式数据处理(要记住json_tuple)

• 在hive中，没有json类的存在，一般使用string类型来修饰，叫做json字符串，简称json串。
• 在hive中，处理json数据的两种方式
  • hive内置了两个用于解析json的函数

    json_tuple
    --是UDTF 表生成函数  输入一行，输出多行  一次提取多个值  可以单独使用 也可以配合lateral view侧视图使用
    
    get_json_object
    --是UDF普通函数，输入一行 输出一行 一次只能提取一个值 多次提取多次使用
    

  • 使用==JsonSerDe 类解析==，在加载json数据到表中的时候完成解析动作

--创建表
create table tb_json_test1
(
    json string
);

select *
from tb_json_test1;

-- 1. 使用get_json_object 解析json
select get_json_object(json, '$.device')     as device,
       get_json_object(json, '$.deviceType') as deviceType,
       get_json_object(json, '$.signal')     as signal,
       get_json_object(json, '$.time')       as `time`
from tb_json_test1;

-- 2. 使用json_tuple 解析json
select json_tuple(json, 'device', 'deviceType', 'signal', 'time') as (device, deviceType, signal, `time`)
from tb_json_test1;

desc function extended json_tuple;

-- 使用json_tuple 配合侧视图进行使用
select json,device, deviceType, signal, `time`
from tb_json_test1 lateral view
    json_tuple(json, 'device', 'deviceType', 'signal', 'time') t_json
    as device, deviceType, signal, `time`;

-- 3.使用jsonserde进行映射
create table tb_json_test2
(
    device string,
    deviceType string,
    signal string,
    `time` string
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe';

select * from tb_json_test2;

十、Hive 窗口函数[重中之重]

• window function 窗口函数、开窗函数、olap分析函数。
• 窗口：可以理解为操作数据的范围，窗口有大有小，本窗口中操作的数据有多有少。
• 可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行；而窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。
• 如果使用聚合函数,需要先进行分组,分组后再聚合,数据条目数就减少了, 窗口函数进行聚合计算后,数据条目数不变

• 窗口函数-聚合函数

  -- 普通聚合函数
  select *
  from test.employee;
  -- 需求: 求出每个部门薪资等平均值
  select dept, avg(salary)
  from test.employee
  group by dept;
  
  -- 经观察发现,聚合之后,数据行数减少.仅为分组数.如果我想求每个部门的薪资,但是不想减少数据行数
  -- 在大数据开发中有一个原则叫做宁滥勿缺, 所以我们聚合后数据有缺失,我们更希望使用窗口函数解决聚合计算的问题.
  
  -- 窗口函数聚合计算
  -- 1. 我们获取的是所有人的平均薪资,
  -- avg计算范围是全部数据, 此时我们就说开窗范围是全部数据记录
  select *, avg(salary) over () as avg_salary
  from test.employee;
  
  -- 2. 获取每个部门的平均薪资
  -- avg计算的范围指定分组内部, 此时我们就说开窗范围是组内聚合
  select *, avg(salary) over (partition by dept) as avg_salary
  from test.employee;
  
  -- 3. 获取每个部门员工薪资由上到下的累加和
  -- sum的计算范围是每个组的开始到当前行,开窗范围是从组的开始位置到当前行为止
  select *, sum(salary) over (partition by dept order by id) as avg_salary
  from test.employee;
  
  -- 总结:
  -- 1. 开窗函数不会减少数据条目数,不会使数据缺失
  -- 2. 窗口函数聚合计算时,over内部什么也不写,开窗范围就是全部数据记录
  -- 3. 窗口函数聚合计算时,over内部书写partition by 就是根据指定的字段进行分组,开窗范围就是分组内部全部数据
  -- 4. 窗口函数聚合计算时,over内部书写partition by 和 order by 就是根据指定字段进行分组,再根据指定字段进行排序,排序完成后,开窗范围就是分组顶部开始到当前行为止
  

• 窗口函数-开窗范围控制

  rows between

  • preceding：往前
  • following：往后
  • current row：当前行
  • unbounded：起点
  • unbounded preceding 表示从前面的起点 第一行
  • unbounded following：表示到后面的终点 最后一行

  -- 建表并且加载数据
  create table website_pv_info
  (
  	cookieid   string,
  	createtime string, --day
  	pv         int
  ) row format delimited
  	fields terminated by ',';
  
  create table website_url_info
  (
  	cookieid   string,
  	createtime string, --访问时间
  	url        string  --访问页面
  ) row format delimited
  	fields terminated by ',';
  
  -- 检查数据是否加载完成
  select *
  from website_pv_info;
  select *
  from website_url_info;
  
  -- 需求1: 查询每个用户最近三天的pv数之和 (前天 昨天 今天  >>> 前2行 前1行 和 当前行)
  -- 按照用户进行分组,按照时间进行排序,此时就知道每个用户登录的时间和次数了
  select *,
  	sum(pv) over (partition by cookieid order by createtime
  		rows between 2 preceding and current row
  		)
  from website_pv_info;
  
  
  -- 计算前三行到后三行的pv数之和  注意此时一共涉及了7行数据  前三行 + 后三行 + 当前行
  select *, sum(pv) over (partition by cookieid order by createtime
  	rows between 3 preceding and 3 following)
  from website_pv_info;
  
  -- 计算当前行到分组末尾的pv数
  select *, sum(pv) over(partition by cookieid order by createtime
  	rows between current row and unbounded following) from website_pv_info;
  
  -- 计算分组开始 到当前行的pv数  -- 这个就是默认开创范围
  select *, sum(pv) over(partition by cookieid order by createtime
  	rows between unbounded preceding and current row ) from website_pv_info;
  

• 窗口函数-排序函数

  -- 需求1 : 给每日访问量进行排序
  select *,
  	row_number() over (partition by cookieid order by pv) as r1,
  	dense_rank() over (partition by cookieid order by pv) as r2,
  	rank() over (partition by cookieid order by pv)       as r3
  from website_pv_info;
  
  
  -- row_number就是从上到下依次编号,是否排序与其无关
  -- dense_rank 和 rank 都是按照order by指定的字段大小进行编号,如果没有使用order by进行排序,则输出均为1
  -- rank 在编号时,如果排序字段值相同,则赋予相同的编号,跳过未使用的编号 例如  113446
  -- dense_rank在编号时,如果排序字段值相同,则赋予相同的编号,不跳过未使用的编号 例如  112334
  select *,
  	row_number() over (partition by cookieid ) as r1,
  	dense_rank() over (partition by cookieid ) as r2,
  	rank() over (partition by cookieid )       as r3
  from website_pv_info;
  
  -- 如果字段按照降序排列,则最大值编号最小.最小值编号最大.
  select *,
  	row_number() over (partition by cookieid order by pv desc ) as r1,
  	dense_rank() over (partition by cookieid order by pv desc ) as r2,
  	rank() over (partition by cookieid order by pv desc )       as r3
  from website_pv_info;
  
  
  -- NTILE(n) 将数据分为n块 , 每块的数据量相同,如果不能均分,则优先给编号较小的分块
  SELECT
  	cookieid,
  	createtime,
  	pv,
  	NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
  FROM website_pv_info
  ORDER BY cookieid,createtime;
  

• 窗口函数-其他窗口函数

  -- LAG 获取当前行之前第n行数据
  select *,
  	lag(createtime, 2) over (partition by cookieid order by createtime),
  	lag(createtime, 2, '9999-99-99 00:00:00') over (partition by cookieid order by createtime)
  from website_url_info;
  
  -- LEAD 获取当前行之后第n行数据
  select *,
  	lead(createtime, 2) over (partition by cookieid order by createtime),
  	lead(createtime, 1, '9999-99-99 00:00:00') over (partition by cookieid order by createtime)
  from website_url_info;
  
  -- first_value 获取开窗范围内的第一个数据值
  select *,
  	first_value(createtime) over (partition by cookieid order by createtime)
  from website_url_info;
  
  -- last_value 获取开窗范围内的最后一个数据值
  select *,
  	last_value(createtime) over (partition by cookieid order by createtime)
  from website_url_info;
  

十一、Hive的数据压缩(节省磁盘空间,提升io效率)

• Hive的默认执行引擎是MapReduce，因此通常所说的Hive压缩指的是MapReduce的压缩。

• 压缩是指通过算法对数据进行重新编排，降低存储空间。无损压缩。

• MapReduce可以在两个阶段进行数据压缩

  • map的输出
    • 减少shuffle的数据量 提高shuffle时网络IO的效率
  • reduce的输出
    • 减少输出文件的大小 降低磁盘的存储空间

• 压缩的弊端

  • 浪费时间
  • 消耗CPU、内存
  • 某些优秀的压缩算法需要钱(我们的缺点)

• 压缩的算法（推荐使用snappy）

  Snappy
  org.apache.hadoop.io.compress.SnappyCodec
  snappy的压缩比例不是很高,但是解压和压缩速度非常快,而且免费
  
  apache版本的hadoop不支持snappy, 我们给大家对的安装包已经进行支持,并重新编译
  

• (了解)Hive中压缩的设置：注意 本质还是指的是MapReduce的压缩

  --设置Hive的中间压缩 也就是map的输出压缩
  1）开启 hive 中间传输数据压缩功能
  set hive.exec.compress.intermediate=true;
  2）开启 mapreduce 中 map 输出压缩功能
  set mapreduce.map.output.compress=true;
  3）设置 mapreduce 中 map 输出数据的压缩方式
  set mapreduce.map.output.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
  
  --设置Hive的最终输出压缩，也就是Reduce输出压缩
  1）开启 hive 最终输出数据压缩功能
  set hive.exec.compress.output=true;
  2）开启 mapreduce 最终输出数据压缩
  set mapreduce.output.fileoutputformat.compress=true;
  3）设置 mapreduce 最终数据输出压缩方式
  set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;
  4）设置 mapreduce 最终数据输出压缩为块压缩  还可以指定RECORD
  set mapreduce.output.fileoutputformat.compress.type=BLOCK;  
  

  --设置完毕之后  只有当HiveSQL底层通过MapReduce程序执行 才会涉及压缩。
  --已有普通格式的表
  select * from student;
  
  --ctas语句  这种压缩方式,很少用  因为我们不会将所有的表都是用压缩模式,而且不会将所有的表读使用同一种压缩模式
  create table student_snappy as select * from student;
  
  一般情况下我们再ods层 会使用压缩比例较高的压缩类型
  一般在dw层或者da层都会使用压缩和解压效率较高的压缩类型
  

  注意: 常用的压缩格式就两种 : zlib(压缩比例最高) snappy(压缩个解压速度最快)

十二、Hive的数据存储格式

• 列式存储、行式存储
  • 数据最终在文件中底层以什么样的形成保存。
  • 行存储:写入效率高,读取效率低
    • 写入数据时按行写入
  • 列存储: 写入效率低,读取效率高(HIVE建议使用列式存储) 且压缩比例高.因为相同数据类型的数据存储在一起方便压缩
    • 读取数据时按列读取

• Hive中表的数据存储格式，不是只支持text文本格式，还支持其他很多格式。
• hive表的文件格式是建表的时候通过STORED AS 语法指定。如果没有指定默认都是textfile。
• Hive中主流的几种文件格式。
  • textfile 文件格式
  • ORC、Parquet 列式存储格式。

    都是列式存储格式，底层是以二进制形式存储。数据存储效率极高，对于查询贼方便。
    二进制意味着肉眼无法直接解析，hive可以自解析。
    

--1、创建表，存储数据格式为TEXTFILE
create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;  --如果不写stored as textfile 默认就是textfile

--加载数据
load data local inpath '/root/hivedata/log.data' into table log_text;

-- 检测数据是否执行成功
select * from log_parquet;

--2、创建表，存储数据格式为ORC
create table log_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc ;

--向表中插入数据 思考为什么不能使用load命令加载？ 因为load是纯复制移动操作 不会调整文件格式。
insert into table log_orc select * from log_text;

--3、创建表，存储数据格式为parquet
create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS PARQUET ;

--向表中插入数据
insert into table log_parquet select * from log_text ;

十三、Hive通用调优

13.1、Fetch抓取机制

原则上,hive操作元数据时不走MR任务, 操作数据时需要走MR任务

• 功能：在执行sql的时候，能不走MapReduce程序处理就尽量不走MapReduce程序处理。
• 尽量直接去操作数据文件。
• 设置： hive.fetch.task.conversion= more。

-- Fatch抓取
select * from student;
-- 没有涉及计算,不需要走mr任务
select * from student where age > 20;

13.2、mapreduce本地模式

• 功能：如果非要执行MapReduce程序，能够本地执行的，尽量不提交yarn上执行。
• 默认是关闭的。意味着只要走MapReduce就提交yarn执行。

  mapreduce.framework.name = local 本地模式
  mapreduce.framework.name = yarn 集群模式 
  

• Hive提供了一个参数，自动切换MapReduce程序为本地模式，如果不满足条件，就执行yarn模式。

  set hive.exec.mode.local.auto = true;
   
  --3个条件必须都满足 自动切换本地模式
  The total input size of the job is lower than: hive.exec.mode.local.auto.inputbytes.max (128MB by default)  --数据量小于128M
  
  The total number of map-tasks is less than: hive.exec.mode.local.auto.tasks.max (4 by default)  --maptask个数少于4个
  
  The total number of reduce tasks required is 1 or 0.  --reducetask个数是0 或者 1
  

• 切换Hive的执行引擎

  WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
  
  如果针对Hive的调优依然无法满足你的需求 还是效率低， 尝试使用spark计算引擎 或者Tez.
  

13.3、join优化

• 1. 开启map端jion 不需要执行shuffle
• 2. 先对于连接字段进行null值过滤
• 3. 对于连接字段分桶,并且两个表的分桶数量最好是相同或者是倍数关系

• 底层还是MapReduce的join优化。

• MapReduce中有两种join方式。指的是join的行为发生什么阶段。

  • map端join
  • reduce端join

• 优化1：Hive自动尝试选择map端join提高join的效率 省去shuffle的过程。

  开启 mapjoin 参数设置：
  （1）设置自动选择 mapjoin
  set hive.auto.convert.join = true;  --默认为 true
  （2）大表小表的阈值设置：
  set hive.mapjoin.smalltable.filesize= 25000000; -- 此处为小于25M就是一个小表可以走map端join,否则不行
  

  不使用map端join
  

  使用map端join
  

• 优化2：大表join大表

  • 可以对于连接字段进行分桶
  • join 数据的连接如果连接字段为null 连接一定是无意义的, 但是会占用内存和资源
  • 在join之前把空值替换或删除,则会减少匹配次数,提高效率
  • 可以先对于被连接表进行过滤,再连接,不要先连接再过滤

  --背景：
  大表join大表本身数据就十分具体，如果join字段存在null空值 如何处理它？
  
  --方式1：空key的过滤  此行数据不重要
  参与join之前 先把空key的数据过滤掉
  SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id =b.id;
  
  --方式2：空Key转换
  CASE WHEN a.id IS NULL THEN 'xxx任意字符串' ELSE a.id END
  CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id  --避免转换之后数据倾斜 随机分布打散
  

• 优化3：桶表join提高优化效率。bucket mapjoin

  1.1 条件
  	1） set hive.optimize.bucketmapjoin = true;
  	2） 一个表的bucket数是另一个表bucket数的整数倍(计算余数的时候更方便)
  	3） bucket列 == join列
  	4） 必须是应用在map join的场景中
  
  1.2 注意
  	1）如果表不是bucket的，只是做普通join。
  

• SMB map join (sort merge bucket map join)
  可以大幅度提高合并效率,但是规则更为苛刻

条件
1） set hive.optimize.bucketmapjoin = true;
​ 2） 两个表的分桶数量必须相同
​ 2） 两个表的分桶字段必须排序
​ 4） bucket列 == join列 == sort列
​ 5） 必须是应用在map join的场景中

13.4、group by 数据倾斜优化

数据倾斜是不可避免的。因为数据已经存在,倾斜是既定事实,我们只能优化计算方案,不能修改数据

（1）是否在 Map 端进行聚合，默认为 True
set hive.map.aggr = true;
（2）在 Map 端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000;
（3）有数据倾斜的时候进行负载均衡（默认是 false）
set hive.groupby.skewindata = true;

--Q:在hive中数据倾斜开启负载均衡之后 底层执行机制是什么样？

--step1:启动一个MapReduce程序 将倾斜的数据随机发送到各个reduce中 进行打散 
        每个reduce进行聚合都是局部聚合
        
--step2:再启动第二个MapReduce程序 将上一步局部聚合的结果汇总起来进行最终的聚合       

13.5、hive中如何调整底层MapReduce中task的个数（并行度）

• maptask个数

  • 如果是在MapReduce中 maptask是通过逻辑切片机制决定的。
  • 但是在hive中，影响的因素很多。比如逻辑切片机制，文件是否压缩、压缩之后是否支持切割。
  • 因此在Hive中，调整MapTask的个数，直接去HDFS调整文件的大小和个数，效率较高。(调整block size大小, block块数量)

    如果是小文件,默认每一个文件会使用一个map任务
    如果是大文件,会根据split size 参数的大小进行分割,将每一个部分放入不同的map任务中.
    但是,split size 默认值为 128M  与 block size 相同, 所以此时我们可以将一个块放入一个map 任务重,有多少个块,就有多少个map任务
    

• reducetask个数

  • 如果在MapReduce中，通过代码可以直接指定 job.setNumReduceTasks(N)

  • 在Hive中，reducetask个数受以下几个条件控制的

    （1）每个 Reduce 处理的数据量默认是 256MB
    hive.exec.reducers.bytes.per.reducer=256000000
    （2）每个任务最大的 reduce 数，默认为 1009
    hive.exec.reducsers.max=1009
    （3）mapreduce.job.reduces
    该值默认为-1，由 hive 自己根据任务情况进行判断。
    
    
    --如果用户不设置 hive将会根据数据量或者sql需求自己评估reducetask个数。
    --用户可以自己通过参数设置reducetask的个数
      set mapreduce.job.reduces = N
    --用户设置的不一定生效，如果用户设置的和sql执行逻辑有冲突，比如order by，在sql编译期间，hive又会将reducetask设置为合理的个数。  
    
    Number of reduce tasks determined at compile time: 1
    

13.6、其他几个通用调优

• 执行计划explain
  • 通过执行计划可以看出hive接下来是如何打算执行这条sql的。
  • 语法格式：explain + sql语句

    -- 执行计划
    -- 执行计划,不走mr任务,这个就是将sql语句输入到driver程序中,给我们的一个执行规划,该规划,在实际执行中还会发生改变.
    -- 执行计划只是代码执行的参照
    explain select * from student;
    
    explain select sex,avg(age) from student group by sex;
    

• 并行执行机制
  • 如果hivesql的底层某些stage阶段可以并行执行，就可以提高执行效率。
  • 前提是stage之间没有依赖 并行的弊端是瞬时服务器压力变大。
  • 参数

    set hive.exec.parallel=true; --是否并行执行作业。适用于可以并行运行的 MapReduce 作业，例如在多次插入期间移动文件以插入目标
    set hive.exec.parallel.thread.number=16; --最多可以并行执行多少个作业。默认为8。
    

• Hive的严格模式
  • 注意！不要和动态分区的严格模式搞混淆。
  • 这里的严格模式指的是开启之后 hive会禁止一些用户都影响不到的错误包括效率低下的操作，不允许运行一些有风险的查询。
  • 设置

    set hive.mapred.mode = strict --默认是严格模式  nonstrict
    
    -- 必须按照分区字段进行筛选才可以执行,否则不行
    -- select * from itheima.t_all_hero_part where attack_max > 500;
    select * from itheima.t_all_hero_part where role='archer';
    

  • 解释

    1、如果是分区表，没有where进行分区裁剪 禁止执行
    2、order by语句必须+limit限制