【大数据】一篇认识Hive

立聪堂德州十三局店  金牌会员 | 2024-11-16 00:15:39 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 858|帖子 858|积分 2574

一、数据堆栈

1.1、数据堆栈概念



  • 数据堆栈，中文简称数仓。英文叫做Data WareHouse,简称DW。
  • 数据堆栈是面向分析的集成化数据平台，分析的结果给企业提供决策支持；
  • 数据堆栈本身不生产数据；
  1. 其分析的数据来自于企业各种数据源。
  2. 企业中常见的数据源：
  3.         RDBMS关系型数据库--->业务数据
  4.         log file----->日志文件数据
  5.         爬虫数据
  6.         其他数据
复制代码


  • 数据堆栈本身也不消费数据；
  1. 其分析的结果给外部各种数据应用（Data application）来使用。
  3. Data visualization（DV）数据可视化
  4. Data Report 数据报表
  5. Data Mining(DM) 数据挖掘
  6. Ad-Hoc 即席查询
  8.         即席查询（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）
  1. Transaction 事务 面向事务支持事务
  2. OLTP系统注重的是数据安全、完整、响应效率。通常指的就是RDBMS关系型数据库。
  3.   #面向事务 支持事务
  4.   #RDBMS: MySQL ORACLE
  5.   #注意 不是NoSQL数据库 没有事务支持： Redis HBase
复制代码


  • OLAP（On-Line Analytical Processing） 中文发音：欧莱普
  1.   Analytical 分析 面向分析支持分析
  2.   OLAP更注重的是数据吞吐量
  3.   主要指的是数据仓库、数据集市（小型数据仓库）：Apache Hive、Apache Impala
复制代码


  • 注意：在某些场所下，说ORACLE也是OLAP系统，如何明白？
  1.   如果把RDBMS只用作数据分析工作，就是OLAP系统。
  2.   
  3.   关系型数据库只要从事的是数据分析的工作,就可以看做是OLAP系统
  4.   但是Hive不可以作为OLTP使用因为其不具备事务功能.
复制代码


  • 数据堆栈不是大型的数据库，也没有要取代数据库的目的，只是一个数据分析的平台。
1.4、数据堆栈分层架构[紧张]



  • 数仓本身不生产数据也不消费数据，按照数据流入流出的特点，对平台举行分层
  • 最基础最核心的3层架构，企业实际应用中，可以结合须要添加差别分层。
  • 核心3层架构

    • ODS 操作型数据层、源数据层、临时存储层

      • 直接抽取源数据,数据的异常值,缺失值,数据的单位等都有问题,无法用于数据分析


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


  • DW 数据堆栈(大数据开发工程师一样平常都是在该层举行各种操作)
  1. 其数据来自于ODS经过层层的ETL变成各种模型的数据.数据干净.规则统一
  2. 基于各种模型开展各种分析
  3. 企业中根据业务复杂度 继续在DW中继续划分子层。 存储大量的中间结果。其数据来自于ODS经过层层ETL得出 企业中可以根据需求在DW中继续分层。
复制代码


  • DA 数据应用层
  1. 最终消费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语句的方式举行结构化数据的增删改查操作

  1.   为什么说Hive是基于Hadoop的呢？
  2.   
  3.   #作为一款数据仓库软件，应该要具备哪些能力？
  4.   具备存储数据的能力
  5.   具备分析数据的能力
  6.   
  7.   Hive作为数仓软件，当然具备上述两种能力？
  8.   #Hive使用Hadoop HDFS作为数据存储系统
  9.   #Hive使用Hadoop MapReduce来分析数据
  10.   
  11.   基于此说Hive是基于Hadoop的数仓软件。
复制代码
总结:
   hive是一个数仓管理工具,是基于hadoop的数仓软件
hive既不负责数据的存储,也不负责数据的盘算.
hive的作用:
  

  • hive可以将结构化数据映射为表
  • hive提供了一种类似于sql的查询语句
  • hive可以将sql语句转换为mr任务并实行
    hive极大程度上提高了数据堆栈的开发服从,低落了数仓的使用门槛
  什么是结构化数据?
   可以通过行索引和列索引定位一个数据的数据结构,类似于excel表数据或则sql数据库数据
结构化数据:
  1. 1, 小明, 12
  2. 2, 小芳, 15
  3. 3, 小绿, 22
复制代码
表:
  学号姓名年龄1小明122小芳153小绿22  结构化数据映射表时须要提取hive中的元数据.
  2.2、Hive的架构组件(非常紧张)




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


  • Hive Driver驱动步伐
  1.   hive的核心
  2.   完成从接受HQL到编译成为MR程序的过程。
  3.   sql解释 编译 校验 优化 制定计划
复制代码


  • metadata
  1.   元数据存储。 描述性数据。
  2.   对于hive来说，元数据指的是表和文件之间的映射关系。
复制代码


  • Hadoop
  1.   HDFS  存储文件
  2.   MapReduce 计算数据
  3.   YARN  程序运行的资源分配
复制代码


  • Q:Hive是分布式的软件吗？
  1.   Hive不是分布式软件。只需要在一台机器上部署Hive服务即可；
  2.   Hive的分布式处理能力是借于Hadoop完成的。HDFS分布式存储  MapReduce分布式计算。
  3.   hive不是分布式软件,但是可以借助hadoop完成分布式的存储和计算任务
复制代码
总结: 其实hive的架构一共就三大部分: 客户端 驱动步伐 元数据服务
2.3、Hive和Mysql的区别



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

3.1、Metadata、metastore

回顾: hdfs的元数据
   元数据: 形貌数据的数据
hdfs中存储一个文件,文件中的数据内容就是我们想要获取的内容.
如果想要获取这个内容,我们就须要知道这个文件的相关形貌信息
例如: 路径,大小,创建修改时间,名称,block块的数目, 位置 …
  1. # Metadata 元数据
  2. 对于hive来说，元数据主要指的是表和文件之间的映射关系。
  3. 元数据也是数据，存储在哪里呢？Hive当下支持两种地方存储元数据。
  4.         1、存储在Hive内置的RDBSM中，Apache Derby(内存级别轻量级关系型数据库)  
  5.                 Derby数据库主要是为了让程序员测试hive的执行使用,正常开发中不会使用该数据库
  6.         2、存储在外界第三方的RDBMS中，比如：MySQL。  企业中常用的方式。
  8. # metastore 元数据访问服务
  9. 专门用于操作访问metadata的一种服务，对外暴露服务地址给各个不同的客户端使用访问Hive的元数据。
  10. 并且某种程度上保证了metadata的安全。
复制代码
思考:hive中的元数据都包罗什么?
   hive中我们使用元数据构建表,用来形貌结构化数据,就是结构化数据的映射.
库名,表名, 字段名, 字段范例, 数据存储位置…
hive映射的过程,就是使用元数据建表,通过元数据存储的数据位置,从hdfs中获取数据,依次填写到表中,形成表数据,再使用sql语句举行查询.
  思考:hive为什么不将元数据存储在hdfs中呢?
   hive举行元数据管理时,我们须要快速创建表结构,并且频繁举行元数据的增删改查, hdfs中的数据不支持随机修改.且增删改的服从较低,不适互助为hive的元数据存储服务出现
  3.2、Hive的安装摆设模式



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

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

  • 内嵌模式(体验)
    1. 1、元数据存储在内置的derby
    2. 2、不需要单独配置metastore 也不需要单独启动metastore服务
    4. 安装包解压即可使用。
    6. 适合测试体验。实际生产中没人用。适合单机单人使用。
    复制代码



  • 本地模式(测试)
    1. 1、元数据使用外置的RDBMS，常见使用最多的是MySQL。
    2. 2、不需要单独配置metastore 也不需要单独启动metastore服务
    3. 缺点:
    4. 如果使用多个客户端进行访问,就需要有多个Hiveserver服务,此时会启动多个Metastore
    5. 有可能出现资源竞争现象
    复制代码



  • 长途模式(开发) 知道就可以了
  1.     1、元数据使用外置的RDBMS，常见使用最多的是MySQL。
  2.     2、metastore服务单独配置  单独手动启动  全局唯一。
  3.     这样的话各个客户端只能通过这一个metastore服务访问Hive.
  4.     企业生产环境中使用的模式，支持多客户端远程并发操作访问Hive.
复制代码



  • 对比   metadata存储在哪metastore服务如何内嵌模式Derby不须要配置启动本地模式MySQL不须要配置启动长途模式MySQL单独配置、单独启动
3.3、Hive的长途模式摆设安装

3.3.1、安装Hadoop

   启动hive之前，须要保证Hadoop启动且服务正常可用。
  1. 1、等待安全模式结束启动Hive
  3. 2、在Hadoop中设置用户代理，注意3台机器都需要修改，重启生效
  5. vim etc/hadoop/core-site.xml
  7. <property>
  8.     <name>hadoop.proxyuser.root.hosts</name>
  9.     <value>*</value>
  10. </property>
  11. <property>
  12.     <name>hadoop.proxyuser.root.groups</name>
  13.     <value>*</value>
  14. </property>
复制代码
3.3.2、安装MySQL

   主要考虑的就是MySQL Hive版本的兼容性 和jdbc驱动版本
在Centos7情况下如何安装MySQL
仅在node1中安装了mysql所以我们选择node1举行hive的安装
  3.3.3、安装Hive (选择node1安装)

  1. #apache-hive-3.1.2-bin.tar.gz
  3. 上传到/export/software 解压到  /export/server
  4. cd /export/software
  5. tar -zxvf apache-hive-3.1.2-bin.tar.gz -C ../server
复制代码


  • 0、办理Hive与Hadoop之间guava版本差异
  1.   cd /export/server/apache-hive-3.1.2-bin/
  2.   rm -rf lib/guava-19.0.jar
  3.   cp /export/server/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar ./lib/
复制代码


  • 1、hive-env.sh
  1.   cd /export/server/apache-hive-3.1.2-bin/conf
  2.   mv hive-env.sh.template hive-env.sh
  3.   
  4.   vim hive-env.sh
  5.   
  6.   
  7.   export HADOOP_HOME=/export/server/hadoop-3.3.0
  8.   export HIVE_CONF_DIR=/export/server/apache-hive-3.1.2-bin/conf
  9.   export HIVE_AUX_JARS_PATH=/export/server/apache-hive-3.1.2-bin/lib
复制代码


  • 2、hive-site.xml
    vim hive-site.xml
  1.   <configuration>
  2.   <!-- 存储元数据mysql相关配置 -->
  3.   <property>
  4.           <name>javax.jdo.option.ConnectionURL</name>
  5.           <value>jdbc:mysql://node1:3306/hive3?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
  6.   </property>
  7.   
  8.   <property>
  9.           <name>javax.jdo.option.ConnectionDriverName</name>
  10.           <value>com.mysql.jdbc.Driver</value>
  11.   </property>
  12.   
  13.   <property>
  14.           <name>javax.jdo.option.ConnectionUserName</name>
  15.           <value>root</value>
  16.   </property>
  17.   
  18.   <property>
  19.           <name>javax.jdo.option.ConnectionPassword</name>
  20.           <value>123456</value>
  21.   </property>
  22.   
  23.   <!-- H2S运行绑定host -->
  24.   <property>
  25.       <name>hive.server2.thrift.bind.host</name>
  26.       <value>node1</value>
  27.   </property>
  28.   
  29.   <!-- 远程模式部署metastore metastore地址 -->
  30.   <property>
  31.     <name>hive.metastore.uris</name>
  32.       <value>thrift://node1:9083</value>
  33.   </property>
  34.   
  35.   <!-- 关闭元数据存储授权  -->
  36.   <property>
  37.     <name>hive.metastore.event.db.notification.api.auth</name>
  38.       <value>false</value>
  39.   </property>
  40.   </configuration>
  41.   
复制代码


  • 3、上传Mysql jdbc驱动到Hive安装包的Lib目录下
  1.   mysql-connector-java-5.1.32.jar
复制代码


  • 4、手动实行下令初始化Hive的元数据
  1.   cd /export/server/apache-hive-3.1.2-bin/
  2.   
  3.   bin/schematool -initSchema -dbType mysql -verbos
  4.   # 初始化成功会在mysql中创建74张表
复制代码




  • 5、在hdfs创建hive存储目录

    • 默认情况下,会将hive中的数据存储在该位置,修改权限是由于我们希望可以恣意在该文件中读写信息


  1.   hadoop fs -mkdir /tmp
  2.   hadoop fs -mkdir -p /user/hive/warehouse
  3.   hadoop fs -chmod g+w /tmp
  4.   hadoop fs -chmod g+w /user/hive/warehouse
复制代码
四、Hive 服务的启动[紧张]

4.1、metastore服务



  • 前台启动
  1.   #前台启动
  2.   /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore
  3.   
  4.   #前台启动开启debug日志
  5.   /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore --hiveconf hive.root.logger=DEBUG,console  
  6.   
  7.   #前台启动关闭方式  ctrl+c结束进程
复制代码


  • 背景挂起启动

  1. nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore &
  3. #后台挂起启动 结束进程
  4. 使用jps查看进程 使用kill -9 杀死进程
  6. #nohup 命令，在默认情况下（非重定向时），会输出一个名叫 nohup.out 的文件到当前目录下
复制代码
注意: 日志文件存储在启动该服务时使用的工作目录中的 nohup.out文件下的.


  • 添加hive的情况变量
  1.   vim /etc/profile
  2.   
  3.   # 在文件末尾添加如下内容
  4.   #HIVE_HOME
  5.   export HIVE_HOME=/export/server/hive
  6.   export PATH=$PATH:$HIVE_HOME/bin  
复制代码


  • 加载情况变量
  1.   source /etc/profile
复制代码


  • 启动hive服务
  1.   # 前台启动
  2.   hive --service metastore
  3.   
  4.   # 后台启动
  5.   nohup hive --service metastore &
复制代码
4.2、Hive的客户端



  • Hive的第一代客户端

    • bin/hive
    • 直接访问metastore服务
    • 如果须要使用hive服务访问元数据服务,只须要在配置文件中誊写元数据服务访问路径即可

      • hive元数据服务的端标语默认是9083

    • 配置
      1. <configuration>
      2. <property>
      3.         <name>hive.metastore.uris</name>
      4.         <value>thrift://node1:9083</value>
      5. </property>
      6. </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.

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

  • Hive的第二代客户端

  • bin/beeline
  • 无法访问metastore服务，只能够访问Hiveserver2服务。

  • beeline客户端,须要先毗连服务,再使用服务

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

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

   lsof -i:10000
  五、Hive初体验



  • 体验目的:在hive中如何才能够将一份结构化的数据文件 映射成为一张表 然后使用SQL分析？
    1. --结构化文件
    2. 1,allen,18
    3. 2,james,24
    4. 3,anna,45
    6. --在hive中建表
    7. create table t_1(id int,name string,age int);
    复制代码

    • 猜想1：只要在hive中创建表，那么在HDFS就会有一个文件夹与之对应。
    • 岂非把结构化文件放置在这个目录下，才能映射成功吗？ 不肯定
      1. /user/hive/warehouse/test_db.db/t_1
      2. hadoop fs -put 1.txt /user/hive/warehouse/test_db.db/t_1
      复制代码
    • 猜想2：岂非要指定分隔符？ 不肯定
      1. create table t_1(id int,name string,age int);
      2. create table t_2(id int,name string,age int) row format delimited fields terminated by ','; --指定分隔符为逗号
      复制代码
    • 猜想3：建表的时间字段范例要不要和文件中数据保持一致？ 肯定要保持一致
    • 如果不一致，hive会尝试举行转换，但是不保证成功，如果不成功显示null。
      1. create table t_3(id int,name int,age string) row format delimited fields terminated by ',';
      2. +---------+-----------+----------+--+
      3. | t_3.id  | t_3.name  | t_3.age  |
      4. +---------+-----------+----------+--+
      5. | 1       | NULL      | 18       |
      6. | 2       | NULL      | 24       |
      7. | 3       | NULL      | 45       |
      8. +---------+-----------+----------+--+
      复制代码

  • 当我们映射成功之后，会如何？
    1. 就可以基于表写Hive SQL 开展数据分析，不用写MapReduce。
    3. 0: jdbc:hive2://node1:10000> select * from t_2 where age >18;
    4. +---------+-----------+----------+--+
    5. | t_2.id  | t_2.name  | t_2.age  |
    6. +---------+-----------+----------+--+
    7. | 2       | james     | 24       |
    8. | 3       | anna      | 45       |
    9. +---------+-----------+----------+--+
    10. 2 rows selected (0.722 seconds)
    11. 0: jdbc:hive2://node1:10000>
    13. 0: jdbc:hive2://node1:10000> select count(*) from t_2 where age >18;
    14. 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.
    15. +-----+--+
    16. | c0  |
    17. +-----+--+
    18. | 2   |
    19. +-----+--+
    20. 1 row selected (67.76 seconds)
    复制代码
总结:
   

  • hive在数据的查询过程中稍慢, 但是写入数据极其迟钝.
  • hive数据在创建表时会创建一个目录将数据上传至该目录时可以快速映射为一个表.
  • hive中数据如果想要映射成功,须要保证每列的数据范例一致,且分隔符指定精确.
  • 在hive终端中或之后誊写的hive脚本中不要出现中文(所有的库名,表名,字段名都不要包罗中文.)
  六、Apache Hive SQL-DDL、DML

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


  • 点击左上角的加号,选择datasource, 在下拉菜单中,选择Apache Hive

  • 填写配置参数并点击下方download下载hive驱动

  • 添加驱动完成后点击测试毗连,成功后点击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;

  • 常用的数据范例
  1. 整数类型: int
  2. 浮点型: double
  3. 字符串类型: string
  4. 布尔型: boolean
  5. 复合数据类型:
  6. map: 类似于字典,但是字典中键值对的数据类型要保持一致
  7. array: 类似于列表,但是只能存储相同数据类型的内容
  8. struct: 结构体(偶尔会用到)
  9. 在hive中数据都是写在文件中的,所占用的空间大小已经固定,使用哪些数据类型就不那么重要了
复制代码
6.2.2、建库/表



  • 建库
  1. -- 1.创建数据库
  2. -- create database 数据库名称;
  3. create database db_test;
  5. -- 再次创建同名数据库会报错, 所以我们需要增加安全性判断条件 if not exists
  6. create database if not exists db_test;
  8. -- 在hive中 database 和 schema 作用完全一样,记住一个就可以了,但是别人写的时候我们要认识
  9. create schema db_test1;
  11. -- 2. 查看所有的数据库
  12. show databases;
  14. -- 3. 使用数据库
  15. use db_test;
  17. -- 4. 查看当前正在使用的数据库
  18. select current_database();
  20. -- 5. 查看数据库的相关元数据信息
  21. desc database db_test;
  23. -- 6.创建一个数据库指定存储位置
  24. create database db_test2 location '/tmp/test';
  25. desc database db_test2;
  27. -- 7. 删除数据库
  28. drop database db_test;
  29. -- bigdata_db is not empty. One or more tables exist. 非空数据库无法被删除
  30. -- 如果想要删除非空数据库,要使用cascade (慎用)
  31. drop database bigdata_db cascade;
复制代码


  • 建表
  1. -- 1. 创建数据表
  2. -- 格式: create table 表名 (字段信息);
  3. create table test_db.students
  4. (
  5.     id   int,
  6.     name string,
  7.     age  int
  8. );
  10. -- 2. 如果该数据库中已经有该名称的表,将会报错,所以我们要添加安全性判断 if not exists
  11. create table if not exists test_db.students(
  12.     id int,
  13.     name string
  14. );
  16. -- 3. 查看数据表相关的元数据信息
  17. desc test_db.students;
  19. -- 4. 查看数据库中全部数据表的名称
  20. show tables in test_db;
  22. -- 5. 清空数据表
  23. --  does not support these operations.  在hive中不支持随机删除
  24. -- delete from test_db.students;
  25. -- 可以使用, 注意 table不能省略
  26. truncate table test_db.students;
  28. -- 6. 删除数据表, 有没有数据都可以删除该表
  29. drop table test_db.students;
  30. drop table test_db.person2;
复制代码
6.2.3、Hive读写HDFS上文件[重点]

  1. create external(外部表)  table if not exists 表名 (
  2.     id int,
  3.     name string
  4. )
  5. partitioned by name (分区表)
  6. cluster by name sorted by id into num(分桶表)
  7. row format delimited  (序列化类型)
  8. fields terminated by ',' (字段分隔符)
  9. collection items terminated by '-' (元素分隔符)
  10. map keys terminated by ':' (键值分隔符)
  11. tblproperties '' 表的属性信息
  12. ;
复制代码


  • 机制：SerDe（Serializer and Deserializer）序列化机制(文件读写的规则)
   Deserializer: 可以明白为将数据从磁盘中读取到内存中的过程
Serializer :可以明白为将数据从内存中写入到磁盘中的操作
  

  • 读文件

    • HDFS files --> InputFileFormat --> <key, value> --> Deserializer --> Row object
    • hdfs中的文件 >>> 使用输入流对象 >>> 转换为kv结构 >>> 举行反序列化 >>> 转换成数据行对象
      1. #1、使用InputFileFormat（默认实现TextInputFormat）读取hdfs上文件
      2.     一行一行读取数据，返回<k，v>键值对类型
      3. #2、返回<key, value>，其中数据存储在value中
      5. #3、使用Deserializer反序列化动作读取value  解析成为对象（Row object）
      6.     默认的序列化类LazysimpleSerDe
      复制代码

  • 写文件

    • Row object --> Serializer --> <key, value> --> OutputFileFormat --> HDFS files
    • 数据行对象>>> 序列化操作 >>> 转化kv结构的二进制数据 >>> 使用输出流对象 >>> 写入hdfs文件中

  • hive中默认的序列化机制叫做LazySimpleSerDe

    • 在默认序列化机制中可以指定各种分隔符,以便于对于数据举行映射
    • 在开发中绝大多数情况下使用的都是默认的序列化机制


  • 分隔符指定语法

    • 语法格式
      1. ROW FORMAT DELIMITED | SERDE
      3. ROW FORMAT DELIMITED  表示使用LazySimpleSerDe类进行序列化解析数据
      5. ROW FORMAT SERDE      表示使用其他SerDe类进行序列化解析数据
      复制代码
    • ROW FORMAT DELIMITED详细的子语法
      1. row format delimited
      2. [fields terminated by char]  #指定字段之间的分隔符
      3. [collection items terminated by char]  #指定集合元素之间的分隔符
      4. [map keys terminated by char]         #指定map类型kv之间的分隔符
      5. [lines terminated by char]            #指定换行符
      复制代码

  • 默认分隔符

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

  • 例子
    1.   -- 创建一个test_db数据库
    2.   create database test_db;
    3.   
    4.   -- 创建一个数据表
    5.   create table test_db.t_archer(
    6.           id int comment "ID",
    7.           name string comment "英雄名称",
    8.           hp_max int comment "最大生命",
    9.           mp_max int comment "最大法力",
    10.           attack_max int comment "最高物攻",
    11.           defense_max int comment "最大物防",
    12.           attack_range string comment "攻击范围",
    13.           role_main string comment "主要定位",
    14.           role_assist string comment "次要定位"
    15.   ) comment "王者荣耀射手信息";
    16.   
    17.   -- 将数据文件全部上传至node1的/root/hive_data目录中
    18.   -- 将数据上传到hdfs中
    19.   -- hadoop fs -put archer.txt /user/hive/warehouse/test_db.db/t_archer
    20.   
    21.   -- 查看表中数据是否插入成功, 数据插入成功,但是映射出正确数据
    22.   select * from test_db.t_archer;
    23.   
    24.   -- 数据没有映射成功,数据内容全部为null,此处就要使用serde机制进行分隔符指定
    25.   create table test_db.t_archer1(
    26.           id int comment "ID",
    27.           name string comment "英雄名称",
    28.           hp_max int comment "最大生命",
    29.           mp_max int comment "最大法力",
    30.           attack_max int comment "最高物攻",
    31.           defense_max int comment "最大物防",
    32.           attack_range string comment "攻击范围",
    33.           role_main string comment "主要定位",
    34.           role_assist string comment "次要定位"
    35.   ) comment "王者荣耀射手信息"
    36.   row format delimited fields terminated by '\t';
    37.   
    38.   -- 将数据文件全部上传至node1的/root/hive_data目录中
    39.   -- 将数据上传到hdfs中
    40.   -- hadoop fs -put archer.txt /user/hive/warehouse/test_db.db/t_archer1
    41.   
    42.   -- 查询数据表中的数据内容
    43.   select * from test_db.t_archer1;
    44.   
    45.   -- 查看表的元数据信息
    46.   desc formatted test_db.t_archer1;
    47.   desc formatted test_db.t_archer;
    48.   
    49.   
    50.   -- 使用delimited 格式化模式加载map类型数据
    51.   -- map类型数据中,需要使用分隔符指定其键值对的规则
    52.   create table test_db.t_hot_hero_skin_price(
    53.           id int,
    54.           name string,
    55.           win_rate int,
    56.           skin_price map<string,int>  -- <键数据类型,值数据类型> 这种写法叫做泛型
    57.   )
    58.   row format delimited  -- 默认序列化机制
    59.   fields terminated by ',' -- 字段间的分隔符
    60.   collection items terminated by '-' -- map类型多个键值对之间的分隔符
    61.   map keys terminated by ':'; -- 字典的键和值之间的分隔符
    62.   
    63.   -- 查看表的元数据信息
    64.   desc formatted test_db.t_hot_hero_skin_price;
    65.   
    66.   -- 将数据文件全部上传至node1的/root/hive_data目录中
    67.   -- 将数据上传到hdfs中
    68.   -- hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/test_db.db/t_hot_hero_skin_price
    69.   
    70.   -- 查看表中的数据内容
    71.   select * from test_db.t_hot_hero_skin_price;
    72.   
    73.   --有点想法： 就把map数据类型当成字符串映射如何？
    74.   create table test_db.t_hot_hero_skin_price_str(
    75.           id int,
    76.           name string,
    77.           win_rate int,
    78.           skin_price string
    79.   )
    80.   row format delimited
    81.   fields terminated by ',';
    82.   
    83.   -- 查看以字符串形式存储的皮肤价格数据映射出来的表
    84.   select * from test_db.t_hot_hero_skin_price_str;
    86.   -- map类型的使用
    87.   -- 需求: 获取大圣娶亲皮肤的价格
    88.   select skin_price["大圣娶亲"] from test_db.t_hot_hero_skin_price;
    89.   -- 使用str数据类型存储时,需要进行大量的字符串操作,才可以获取皮肤价格,所以map类型使用更加方便
    90.   -- select split(skin_price, '-')[0] from test_db.t_hot_hero_skin_price_str;
    复制代码
6.2.4、内部表、外部表[重点]

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

  • 内部表
    1. --创建内部表
    2. create table student_inner(
    3. Sno int
    4. ,Sname string
    5. ,Sex string
    6. ,Sage int
    7. ,Sdept string)
    8. row format delimited fields terminated by ','
    9. ;
    11. --上传文件到内部表中
    12. hadoop fs -put students.txt /user/hive/warehouse/test.db/student_inner
    14. --针对内部表进行drop删除操作
    15. drop table student_inner;  --内部表在删除的时候 元数据和数据都会被删除
    复制代码



  • 外部表
    1. --创建外部表 关键字external
    2. create external table student_external(
    3. Sno int
    4. ,Sname string
    5. ,Sex string
    6. ,Sage int
    7. ,Sdept string)
    8. row format delimited fields terminated by ','
    9. ;
    11. --上传文件到外部表中
    12. hadoop fs -put students.txt /user/hive/warehouse/test.db/student_external
    13. -- 针对外部表 进行drop删除操作
    14. drop table student_external; --外部表在删除的时候 只删除元数据  而HDFS上的数据文件不会动
    复制代码

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



  • 分区表的创建
    1. -- 创建数据表
    2. create table test.t_all_hero
    3. (
    4.         id           int,
    5.         name         string,
    6.         hp_max       int,
    7.         mp_max       int,
    8.         attack_max   int,
    9.         defense_max  int,
    10.         attack_range string,
    11.         role_main    string,
    12.         role_assist  string
    13. )
    14.         row format delimited
    15.                 fields terminated by "\t";
    17. -- 向数据表中上传数据文件
    18. -- hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/test.db/t_all_hero
    20. -- 查询数据是上传成功
    21. -- 在同一个表目录中上传多个文件,查询表数据时,会依次获取每一个文件中的数据内容,此时我们将所有文件中的数据都提取出来了
    22. select *
    23. from test.t_all_hero;
    25. -- 查询role_main主要定位是射手并且hp_max最大生命大于6000的有几个
    26. select count(1) from test.t_all_hero where role_main = 'archer' and hp_max > 6000;
    27. select * from test.t_all_hero where role_main = 'archer';
    29. -- 思考: 此时我们查询role_main = 'archer' and hp_max > 6000时,是检索了所有的文件,还是只查询了archer.txt文件呢??
    30. -- 此时我们查询了所有文件,但是我们要的数据其实仅仅在archer.txt文件中
    31. -- 如果我们可以缩小检索范围,那么查询效率就会有大幅度的提升,此时我们就要用到分区表.
    33. -- 分区表的作用: 减小数据查询时检索文件的范围,从而提高查询效率
    36. -- 经分析,我们认为根据角色的主要定位进行分区,可以最大限度的减小检索范围
    37. -- create table test.t_all_hero_part
    38. -- (
    39. --     id           int,
    40. --     name         string,
    41. --     hp_max       int,
    42. --     mp_max       int,
    43. --     attack_max   int,
    44. --     defense_max  int,
    45. --     attack_range string,
    46. --     role_main    string,
    47. --     role_assist  string
    48. -- )
    49. --     partitioned by (role_main string)
    50. --     row format delimited
    51. --         fields terminated by "\t";
    52. -- 此处报错: Column repeated in partitioning columns
    53. -- 这里告诉我们有重复的列, 此处我们的分区字段是不能和表中其他字段名重复的.
    54. create table test.t_all_hero_part
    55. (
    56.         id           int,
    57.         name         string,
    58.         hp_max       int,
    59.         mp_max       int,
    60.         attack_max   int,
    61.         defense_max  int,
    62.         attack_range string,
    63.         role_main    string,
    64.         role_assist  string
    65. )
    66.         partitioned by (role string)
    67.         row format delimited
    68.                 fields terminated by "\t";
    70. -- 查询表的元数据信息
    71. desc formatted test.t_all_hero_part;
    73. -- 向分区表中加载数据
    74. -- hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/test.db/t_all_hero_part
    76. -- 查询该表中的数据,数据是否成功映射
    77. select * from test.t_all_hero_part;
    79. -- 此时数据没有映射成功,该如何向数据表中加载数据呢????
    复制代码
  • 分区表的数据加载

    • 静态分区加载
      1. -- 查询一下分区表是否存在,且是否进行了分区
      2. desc test.t_all_hero_part;
      4. -- 向分区表中插入数据,在插入数据时指定分区名称,这种加载数据的方式叫做静态加载
      5. -- 格式:  load data inpath '加载路径' into table 表名  partition(分区字段 = 值)
      6. load data local inpath '/root/hive_data/archer.txt' into table test.t_all_hero_part partition (role = 'archer');
      7. load data local inpath '/root/hive_data/tank.txt' into table test.t_all_hero_part partition (role = 'tank');
      8. load data local inpath '/root/hive_data/mage.txt' into table test.t_all_hero_part partition (role = 'mage');
      9. load data local inpath '/root/hive_data/mage.txt' into table test.t_all_hero_part partition (role = '123');
      11. -- 查看数据是否加载成功
      12. select * from test.t_all_hero_part;
      14. -- 通过查看hdfs中的文件路径可知,我们将数据加载到了表的分区目录中
      15. -- 我们推测.每增加一个分区,就会增加一个分区目录,所有数据都存放在分区目录中.
      17. -- 查看分区有哪些
      18. show partitions test.t_all_hero_part;
      19. -- partition是元数据中的数据内容,与hdfs无关,我们只是指定了每一个分区数据存放的位置
      21. -- 思考: 我们查询的时候,有没有role字段???  有,  但是这个字段不在数据文件中,是一个元数据构建出来的虚拟字段
      复制代码
    • 动态分区加载           insert+select
      插入的数据来自于背面的查询语句返回的结果。
      查询返回的内容，其字段范例、次序、个数要和待插入的表保持一致。
           
      1. -- 准备一个分区表
      2. create table test.t_all_hero_part2
      3. (
      4.         id           int,
      5.         name         string,
      6.         hp_max       int,
      7.         mp_max       int,
      8.         attack_max   int,
      9.         defense_max  int,
      10.         attack_range string,
      11.         role_main    string,
      12.         role_assist  string
      13. )
      14. partitioned by (role string)
      15. row format delimited
      16. fields terminated by "\t";
      18. -- 准备一个非分区表,内部加载了全部的数据
      19. select *
      20. from test.t_all_hero;
      22. -- 动态加载的本质,就是将数据读取到内存中,然后通过分区字段的值,进行判断将每一个数据记录存储在哪一个分区目录中
      23. -- 动态加载格式: insert into 表名 partition (分区字段名) select 字段列表 from 表;
      24. -- To turn this off set hive.exec.dynamic.partition.mode=nonstrict
      25. -- 使用动态加载,需要开启动态加载的非严格模式,才可以.
      26. set hive.exec.dynamic.partition.mode=nonstrict;
      28. -- Table insclause-0 has 10 columns, but query has 9 columns.
      29. -- 被插入的表有10列数据, 而插入的数据仅有9列,少了一列分区字段
      30. insert into test.t_all_hero_part2 partition (role)
      31. select *, role_main -- 在所有字段的末尾增加了一列role_main作为分区字段的值
      32. from test.t_all_hero;
      34. -- 查看表中的数据内容
      35. select * from test.t_all_hero_part2;
      37. -- 查看表中有哪些分区
      38. show partitions test.t_all_hero_part2;
      40. -- 为什么默认配置下,阻止我们使用动态加载呢?
      41. -- 因为该操作如果进行了误操作,很容易造成服务器崩溃.
      42. -- 例如: 我们要根据日期进行分区,一个月的数据分30个目录可以正常使用,但是我们误把创建时间(年月日十分秒)数据作为动态分区字段,
      43. -- 此时我们会拆分为 30 * 24 * 60 *60 个分区目录,系统直接崩溃.
      复制代码

  • 分区表注意事项

    • 分区表的字段不能是表中已有的字段；分区的字段也会显示在查询结果上；
    • 分区的字段是虚拟的字段，出如今表所有字段的背面，其值来自于元数据。
    • 分区在底层的形式就是以文件夹管理差别的文件；差别文件夹就是表差别分区；文件夹的名字就是分区字段=值：
      1.   /user/hive/warehouse/数据库.db/表
      2.                                           /分区字段=分区值1
      3.                                               xxxx.txt
      4.                                           /分区字段=分区值2
      5.                                                   zzzz.txt
      复制代码
    • 分区表是一种优化表，建表的时间可以不使用，但是，当创建分区表之后，使用分区字段查询可以淘汰全表扫描，提高查询的服从。
           注意: 分区表只有根据分区字段举行筛选时,才能提高查询服从,否则和普通表没有区别
          

    • 企业中常用的分区字段

      • 地域维度：省，市
      • 时间维度：day,month,year


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

  1. CLUSTERED BY xxx INTO N BUCKETS
  2. --根据xxx字段把数据分成N桶
  3. --根据表中的字段把数据文件成为N个部分
  5. t_user(id int,name string);
  6. --1、根据谁分？
  7. CLUSTERED BY xxx ；  xxx必须是表中的字段
  8. --2、分成几桶？
  9. N BUCKETS   ；N的值就是分桶的个数
  10. --3、分桶的规则？
  11. clustered by id into 3 bucket
  13. hashfunc(分桶字段)  %  N bucket  余数相同的来到同一个桶中
  14. 1、如果分桶的字段是数字类型的字段，hashfunc(分桶字段)=分桶字段本身
  15. 2、如果分桶的字段是字符串或者其他字段，hashfunc(分桶字段) = 分桶字段.hashcode
复制代码
6.4.2、分桶的创建及加载

  1. -- 创建一个分桶表,使用的分桶字段是表中已有的字段,必须用括号括起来,不需要书写数据类型.
  2. CREATE TABLE test.t_usa_covid19_bucket
  3. (
  4.     count_date string,
  5.     county     string,
  6.     state      string,
  7.     fips       int,
  8.     cases      int,
  9.     deaths     int
  10. )
  11. clustered by (state) into 5 buckets
  12. row format delimited fields terminated by ',';
  14. -- 查看表中的元数据
  15. describe formatted test.t_usa_covid19_bucket;
  17. -- 使用已有的数据表,将数据读取出来,然后把数据从内存中写入到分桶表中.
  18. -- 1. 先创建一个不分桶的表
  19. CREATE TABLE itheima.t_usa_covid19
  20. (
  21.     count_date string,
  22.     county     string,
  23.     state      string,
  24.     fips       int,
  25.     cases      int,
  26.     deaths     int
  27. )
  28. row format delimited fields terminated by ',';
  30. -- 2. 向未分桶表中添加数据  直接将数据上传到未分桶表中即可
  31. -- 此处直接使用web页面上传,开发中不要这样操作
  33. -- 3. 读取未分桶表中的全部数据,写入到分桶表中(这个指令会走MR任务,所以速度比较慢)
  34. -- 格式: insert into 目标表名  查询集(完整的select语句)
  35. -- 注意: 被插入数据的表中的字段数据量,和字段类型,以及字段顺序,要与读取出来额查询集中完全相同.
  36. insert into test.t_usa_covid19_bucket
  37. select *
  38. from test.t_usa_covid19;
  40. -- 4. 查询分桶表中的数据是否加载成功
  41. select *
  42. from test.t_usa_covid19_bucket;
  44. -- 走mr任务时,由于要生成5个数据文件,会执行5个reduce任务
  46. -- 分桶表的分桶规则: 哈希取模法/哈希取余法   取模就是取余数,  例如 7 / 2  商 3 余  1  此时取模结果就是1
  47. -- 规则: 将分桶字段值取哈希值,然后按照分桶规则模以桶数. 根据余数不同分到不同的桶内
  48. -- 哈希算法: 这个是一个散列函数,不需要了解其底层怎么计算的,我们只要知道这个算法可以将所有的数据计算为一个唯一的数值即可.
  49. -- 一个值无论进行多少次哈希计算,最终结果都是相同的.
  50. select hash('测试'); -- 汉字的哈希值多为负数
  51. select hash('abc'); -- 字母符号的哈希值多为正数
  52. select hash(1); -- 整数的哈希值就是数字本身
  53. select hash(-5.1); -- 浮点型的哈希值是一个计算后的整数
  55. -- 由于New York 和 Florida 的哈希值与5进行取模后值相等,所以在同一个分桶中.
  56. select hash('New York'); -- 1382994575
  57. select hash('Florida'); -- 898707645
复制代码
6.4.5、分桶的总结



  • 分桶表也是一种优化表，可以**淘汰join查询时笛卡尔积的数目**、提高抽样查询的服从(从全表随机,数目较大,分桶表可以在分桶文件内分别抽样,服从更高)。

    • 普通表join毗连时,比对次数为左表条目数*右表条目数
    • 如果按照分桶字段举行毗连,则比对次数会明显淘汰

  • 分桶表的字段必须是表中已有的字段；
  • 在join的时间，针对join的字段举行分桶，可以提高join的服从 淘汰笛卡尔积数目 (必须使用分桶字段举行链接,否则和普通表没有任何区别)。
  • 分桶数目不宜过多,会造成许多的分桶文件没有数据,大概数据量过少

    • 举例: 按照班级学员的性别举行分桶, 分为5桶, 此时必有空桶.由于仅有两种性别.

  • 分桶数目不宜过少,会造成数据倾斜(将一个任务分成多个map或reduce任务,某一个map或reduce任务中数据量巨大,其他任务中数据量较少.)
  • 分桶的目的一样平常不是为了提高查询服从,而是为了提高表的毗连服从.
    分桶和分区的区别:
  • 分桶的目的是淘汰笛卡尔积的数目,提拔毗连性能,对于查询服从的提拔是次要提拔
  • 分区对于查询服从的提拔是主要提拔
  • 分桶字段真实存在,而分区字段是虚拟字段,是使用元数据映射出来的
  • 分桶表是使用肯定规则举行文件的拆分,而分区表是拆分为多个文件目录
    分桶和分区的共同点:
  • 都可以提高查询服从
  • 都是表的优化方式,并不是必须存在的.
  • 都是只有按照分区大概分桶字段毗连或查询才能提高服从,否则和普通表无异
6.5、Hive SQL–DDL其他操作



  • 数据库操作
    1. -- 1. comment  给表或者库进行注释,在查询表的元数据信息时,可以显示在控制台中
    2. create table db_1.person1
    3. (
    4.         id   int comment 'primary key',
    5.         name string comment 'your name'
    6. ) comment 'describe person';
    8. -- 查看表的元数据信息
    9. describe formatted db_1.person1;
    11. -- 目前我们还不能使用中文注释,后续在新零售项目中会解决这个问题.
    13. -- 2. dbproperties 数据库属性
    14. -- 可以给数据库增加注释
    15. create database db_2 comment 'Second DB' with dbproperties ('chuanzhi' = 'good');
    18. -- 修改数据库属性值, 当添加属性的键存在时,则为修改, 如果该键不存在则为新增.
    19. alter database db_2 set dbproperties ('chuanzhi' = 'youxiu');
    20. alter database db_2 set dbproperties ('teacher' = 'zeiyouxiu');
    22. describe database extended db_2;
    24. -- 3. 修改数据库属性
    25. alter database db_2 set owner user hive;
    复制代码
  • 数据表操作
    1. -- 4. 更改表名  rename to
    2. -- 修改表名时一定要指定数据库名称,否则会移动到其他数据库
    3. alter table db_1.student2
    4.         rename to db_1.student_2;
    5. show tables in db_1;
    7. -- 5. 更改表的属性
    8. alter table person_1
    9.         set tblproperties ('chuanzhi' = 'youxiu');
    10. -- 修改表的类型为外部表
    11. alter table person_1
    12.         set tblproperties ('EXTERNAL' = 'TRUE');
    13. -- 修改表的注释信息
    14. alter table person_1
    15.         set tblproperties ('comment' = 'test_tb');
    16. -- 修改表的分隔符
    17. alter table person_1
    18.         set serdeproperties ('field.delim' = ',');
    20. desc formatted person_1;
    22. -- 6. 字段的增加
    23. alter table person_1
    24.         add columns (gender string);
    26. -- 7. 修改字段
    27. alter table person_1
    28.         replace columns (gender int);
    复制代码
  • 数据表分区操作
    1. -- 创建一个分区表,如果存在则先删除
    2. drop table if exists test.t_user_province;
    3. create table test.t_user_province
    4. (
    5.         num  int,
    6.         name string,
    7.         sex  string,
    8.         age  int,
    9.         dept string
    10. ) partitioned by (province string);
    12. -- 向分区表中加载数据
    13. load data local inpath '/root/hive_data/students.txt' into table test.t_user_province partition (province = 'SZ');
    14. load data local inpath '/root/hive_data/students.txt' into table test.t_user_province partition (province = 'BJ');
    16. -- 1. 分区的重命名
    17. -- 只要是使用alter table就不会修改数据记录文件中的内容.
    18. alter table test.t_user_province
    19.         partition (province = 'SZ') rename to partition (province = 'shenzhen');
    21. -- 2. 添加空分区
    22. alter table test.t_user_province
    23.         add partition (province = 'GZ');
    24. alter table test.t_user_province
    25.         add partition (province = 'CS') partition (province = 'HZ');
    26. --一次性增加多个分区
    28. -- 3. 删除分区
    29. alter table test.t_user_province
    30.         drop partition (province = 'shenzhen');
    31. alter table test.t_user_province
    32.         drop partition (province = 'HZ');
    33. -- 当我们删除分区时,且hdfs开启垃圾桶机制,会将数据存入垃圾桶中.
    34. -- 如果我们使用 purge 进行标记,则不会放入垃圾桶,直接删除.
    35. alter table test.t_user_province
    36.         drop partition (province = 'shenzhen') purge;
    37. -- 慎用
    39. -- 4. 修复分区
    40. -- MSCK REPAIR TABLE table_name [ADD/DROP/SYNC PARTITIONS];
    41. -- 表目录 中存在的分区目录,在元数据中不存在则添加
    42. msck repair table test.t_user_province add partitions;
    43. -- 表目录 中不存在的分区目录,在元数据中存在则删除
    44. msck repair table test.t_user_province drop partitions;
    45. -- 既使用add 又使用drop 则我们可以使用sync (使表目录中的分区与元数据中统一)
    46. msck repair table test.t_user_province sync partitions;
    48. -- 查看当前表中的所有分区
    49. 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. --1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样
    2. show databases;
    4. --2、显示当前数据库所有表/视图/物化视图/分区/索引
    5. show tables;
    6. SHOW TABLES [IN database_name]; --指定某个数据库
    9. --3、显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错
    10. show partitions table_name;
    11. show partitions itheima.student_partition;
    13. --4、显示表、视图的创建语句
    14. SHOW CREATE TABLE ([db_name.]table_name|view_name);
    15. show create table student;
    17. --5、显示当前支持的所有自定义和内置的函数
    18. show functions;
    20. --6、Describe desc
    21. --查看表信息（格式化美观）
    22. desc formatted table_name;
    25. -- 视图的展示
    26. create view student_view as select * from itheima.student_plus;
    28. select * from student_view;
    30. desc formatted student_view;
    复制代码
6.6、Hive DML 数据操纵语言



  • load加载数据
  1. --1. 建表student_local 用于演示从本地加载数据
  2. create table student_local
  3. (
  4.     num  int,
  5.     name string,
  6.     sex  string,
  7.     age  int,
  8.     dept string
  9. ) row format delimited fields terminated by ',';
  10. -- 本地其实说的就是node1 也就是hive服务部署到哪一个服务器,这个服务器的Linux系统就是本地文件系统
  11. -- 从本地加载到hive表中的数据,会复制一份加载到hdfs上,本地数据不会清空
  12. load data local inpath '/root/hive_data/students.txt' into table student_local;
  15. -- 2. 建表student_HDFS  用于演示从HDFS加载数据到非分区表
  16. create table student_HDFS
  17. (
  18.     num  int,
  19.     name string,
  20.     sex  string,
  21.     age  int,
  22.     dept string
  23. ) row format delimited fields terminated by ',';
  25. -- 在hdfs中创建一个hive_data目录
  26. -- hadoop fs -mkdir /test/hive_data
  27. -- 将linux中的students.txt文件上传到hdfs中
  28. -- hadoop fs -put hive_data/students.txt /test/hive_data
  30. -- 将hdfs上的数据内容加载到student_HDFS表中
  31. load data inpath 'hdfs://node1:8020/test/hive_data/students.txt' into table student_HDFS;
  32. -- 再次加载数据时students.txt文件消失,因为使用loaddata 从hdfs向表中加载数据时为移动该数据.
  33. load data inpath '/test/hive_data/students.txt' into table student_HDFS;
  36. -- 3. 建表student_HDFS_b  用于演示从HDFS加载数据到分桶表
  37. create table student_HDFS_b
  38. (
  39.     num  int,
  40.     name string,
  41.     sex  string,
  42.     age  int,
  43.     dept string
  44. )
  45.     clustered by (num) into 3 buckets
  46.     row format delimited fields terminated by ',';
  48. -- 从hdfs中向分桶表中添加数据
  49. load data inpath '/test/hive_data/students.txt' into table student_HDFS_b;
  51. -- 4. 建表student_HDFS_p 用于演示从HDFS加载数据到分区表
  52. create table student_HDFS_p
  53. (
  54.     num  int,
  55.     name string,
  56.     sex  string,
  57.     age  int,
  58.     dept string
  59. ) partitioned by (country string) row format delimited fields terminated by ',';
  61. -- hdfs中向分区表中添加数据.必须指定分区值否则无法添加
  62. load data inpath '/test/hive_data/students.txt' into table student_HDFS_p partition (country = 'BJ');
  64. -- 5. 向表中覆盖数据
  65. -- 默认使用into table 是追加数据
  66. load data local inpath '/root/hive_data/students.txt' into table student_local;
  68. select *
  69. from student_local;
  70. -- 加载三次一共有66条数据
  72. -- 如果需要覆盖数据可以使用 overwrite 关键字
  73. load data local inpath '/root/hive_data/students.txt' overwrite into table student_local;
  75. select *
  76. from student_local; -- 覆盖成功后,一共有22条数据
复制代码


  • Hive中insert用法
  1. -- 创建一张源表student
  2. drop table if exists student;
  3. create table student
  4. (
  5.     num  int,
  6.     name string,
  7.     sex  string,
  8.     age  int,
  9.     dept string
  10. )
  11.     row format delimited
  12.         fields terminated by ',';
  14. -- 将 student_HDFS表中的数据插入到student表中, 并且将每一个人的年龄增加一岁
  15. insert into student
  16. select num, name, sex, age + 1 as age, dept
  17. from student_hdfs;
  19. select *
  20. from student;
  22. -- 将查询到的数据插入到指定的表中还可以使用 create table as
  23. create table student_c as
  24. select num, name, sex, age + 1 as age, dept
  25. from student_hdfs;
  27. select *
  28. from student_c;
  30. -- 注意:
  31. -- 1. create table 创建的表不能存在, insert into 可以给表追加数据
  32. -- 2. create table as 的方法执行效率比insert into select 块很多, 因为create table 是DDL语句, 而insert into select 是DML语句
  33. -- 底层实现时, create table as 是将数据一次写入, insert into select 是一行一行写入.
  36. -- 多重插入
  37. -- 读取一次数据,插入多次,此时插入数据的效率会有所提升
  38. create table source_table
  39. (
  40.     id   int,
  41.     name string
  42. ) row format delimited fields terminated by ',';
  44. create table test_insert1
  45. (
  46.     id int
  47. ) row format delimited fields terminated by ',';
  49. create table test_insert2
  50. (
  51.     name string
  52. ) row format delimited fields terminated by ',';
  54. -- 向 source_table里导入数据
  55. load data local inpath '/root/hive_data/students.txt' into table source_table;
  57. -- 查询数据内容
  58. select * from source_table;
  60. -- 将 id 插入到 test_insert1表中
  61. insert into test_insert1 select id from source_table;
  63. -- 将 name 插入到 test_insert2表中
  64. insert into test_insert2 select name from source_table;
  66. -- 上述插入方式,读取了两次source_table表 也插入了两次, 但是两次读取的是同一张表
  67. -- 使用多重插入方法就可以实现一次读取,多次插入
  68. from source_table
  69. insert overwrite table test_insert1 select id
  70. insert overwrite table test_insert2 select name;
复制代码


  • Hive中的数据导出[导出是覆盖操作，不要轻易使用]
  1. -- 1. 从hdfs中导出数据到hdfs中的指定目录下
  2. insert overwrite directory '/test/output_dir' select * from student_local;
  4. -- 查询数据源数据是否存在, 导出数据时,不会移动,修改,删除原数据.
  5. select * from student_local;
  7. -- 2. 导出数据时指定分隔符
  8. insert overwrite directory '/test/output_dir' row format delimited fields terminated by '❤' select * from student_local;
  10. -- 3. 导出数据时,会将目录中的全部数据清空
  11. insert overwrite directory '/test' row format delimited fields terminated by ',' select * from student_local;
  13. -- 4. 将数据导出到hive服务所在的linux系统中
  14. insert overwrite local directory '/root/hive_data' select * from student_local;
  16. -- 注意: 没有insert into directory这种写法
  17. -- 在开发中我们更多的会使用ETL工具或者数据采集工具进行数据导出. 例如 kettle, sqoop
复制代码
七、Apache Hive SQL-DQL、参数配置与函数

7.1、Hive SQL–DQL-Select

7.1.1、select语法树

  1. -- 1. 基础查询
  2. select *
  3. from test.student_local;
  4. select num, name
  5. from test.student_local;
  6. select name, age + 10
  7. from test.student_local;
  8. select name, 1, 2, 3, 4
  9. from test.student_local;
  11. -- 2. 条件查询
  12. select *
  13. from test.student_local
  14. where age > 20;
  15. select *
  16. from test.student_local
  17. where sex = '女';
  18. select *
  19. from test.student_local
  20. where age > 20
  21.   and sex = '女';
  22. select *
  23. from test.student_local
  24. where age between 18 and 20; -- 这个范围中包含18和20
  25. select *
  26. from test.student_local
  27. where age in (18, 20);
  28. select *
  29. from test.student_local
  30. where name like '李%';
  31. select *
  32. from test.student_local
  33. where name is null;
  35. -- 3.聚合查询
  36. select count(*)
  37. from test.student_local;
  38. select max(age)
  39. from test.student_local;
  41. -- 4.分组查询
  42. select sex, max(age)
  43. from test.student_local
  44. group by sex;
  45. select dept, count(1)
  46. from test.student_local
  47. group by dept;
  49. -- 5. 分组后筛选
  50. select sex, max(age)
  51. from test.student_local
  52. group by sex
  53. having max(age) > 20;
  55. select dept, count(1) as total
  56. from test.student_local
  57. group by dept
  58. having total > 3;
  60. -- 6. 排序函数
  61. select *
  62. from test.student_local
  63. order by age; -- 升序
  64. select *
  65. from test.student_local
  66. order by age desc; -- 降序
  68. -- 7. 分页查询
  69. select * from test.student_local limit 2;
  70. select * from test.student_local limit 3, 3;
复制代码
7.2.2、CLUSTER BY分桶查询

  1. -- 1. 对查询到的数据进行分桶排序
  2. -- 普通查询
  3. select * from student;
  4. -- 分桶查询
  5. select * from student cluster by num;
  7. -- 问题: 是否分桶 结果完全相同,使用了cluster by 也没有明显的效果
  8. -- 原因: 分桶的数量与reducetask的数量有关,如果没有设置reducetask 则默认为1个
  9. -- 解决办法: 手动设置reducetask数量 , 如果该值为-1,则根据查询语句规则自动定义reduce数量.
  10. set mapreduce.job.reduces;
  11. set mapreduce.job.reduces = 3; -- 设置reducetask数量为3
  13. -- 再次分桶查询
  14. -- 思考:分桶查询可以查询非分桶表么? 分桶查询和原表是否分桶没有任何关系,是将数据查询后才进行分桶规则判断的,所以原数据是否分桶都没关系.
  15. select * from student cluster by num;
  16. -- 观察发现,将num哈希取余后,根据余数不同,将数据记录存放在的不同的桶中, 有3个reduce任务,就产生了三个分桶
  17. -- 在每个桶的内部对于数据进行了升序排列
  19. -- 使用order by 不分桶直接排序
  20. select * from student order by num;
  21. -- order by 又叫做全局排序,无论reducetask数量是几,最终我们只会使用一个reducetask
  22. -- 在数据量较大时,该reduce任务的任务量巨大,所以开发时我们一般不建议使用order by , 如果非要使用, 我们会配合 where, 或者limit 进行使用.
  24. -- 思考: cluster by可以指定升序降序么?  不可以,只能使用默认的升序排列方式.
复制代码
7.3.3、DISTRIBUTE BY+SORT BY(记住)

  1. -- 普通查询
  2. select *
  3. from student;
  4. -- 分桶查询 cluster by
  5. select *
  6. from student cluster by num;
  8. set mapreduce.job.reduces =2;
  9. -- 将使用distribute by + sort by 将指定不同的字段为分桶字段和排序字段.
  10. select *
  11. from student distribute by dept;
  13. select *
  14. from student distribute by dept sort by num;
  16. select *
  17. from student distribute by num sort by age desc;
  19. -- 结论:
  20. -- 1. distribute by 只负责分桶工作
  21. -- 2. sort by 负责将分桶的结果进行桶内排序
  22. -- 3. 当分桶字段和排序字段为同一个字段,且排序规则为升序时, cluster by >>> distribute by num sort by
复制代码
7.4.4、union联合查询 [紧张且简单]

  1. -- union 联合查询,联合查询和join相似,只不过join时左右连接,union是上下连接
  2. -- union可以将两个查询集上下连接到一个查询集(虚拟表结构)中
  3. -- 1. 查询年龄大于20岁的女生
  4. select *
  5. from student
  6. where age > 20
  7.   and sex = '女';
  9. -- 2. 查询年龄小于20岁的男生
  10. select *
  11. from student
  12. where age < 20
  13.   and sex = '男';
  15. -- 3. 使用union联合查询将上述两个查询集链接到一起
  16. -- union 联合查询,无论如何都会走mr程序
  17. -- 无论联合查询前是什么顺序,联合后都会重新排序,且顺序无规律.
  18. -- 如果需要排序,可以联合查询之后,对其结果统一排序
  19. select *
  20. from student
  21. where age > 20
  22.   and sex = '女'
  23. union
  24. select *
  25. from student
  26. where age < 20
  27.   and sex = '男'
  28. order by age;
  30. -- 4. 使用联合查询将第二步的数据连接两次,最终有多少条记录???? 原始数据有6条
  31. -- union 联合查询后一共有6条数据
  32. -- 因为union 联合查询 如果不加任何修饰符 默认等价于 union distinct  默认去重(所有字段值均相同)
  33. select *
  34. from student
  35. where age < 20
  36.   and sex = '男'
  37. union
  38. select *
  39. from student
  40. where age < 20
  41.   and sex = '男';
  43. -- 如果我们不希望union自动去重 可以使用union all
  44. select *
  45. from student
  46. where age < 20
  47.   and sex = '男'
  48. union all
  49. select *
  50. from student
  51. where age < 20
  52.   and sex = '男';
  54. -- 5. 思考: union联合查询时,上下两个表中的字段数量和类型是否必须完全相同???  数据类型必须相同, 数据数量也必须相同
  55. -- select num, name from student
  56. -- union
  57. -- select name, num from student;
  59. -- select num, name
  60. -- from student
  61. -- union
  62. -- select num, name, age
  63. -- from student;
  65. -- 总结:
  66. -- 1. union联合查询就是将上下两张表连接在一起
  67. -- 2. union联合查询会打乱联合之前的排序.所以一般联合之后要重新排序
  68. -- 3. union联合查询后会自动去重.如果不需要去重,则使用union all
  69. -- 4. union联合查询时上下两张表数据类型必须相同, 数据数量也必须相同
复制代码
7.5.5、CTE表达式(记住最简单的调用形式即可)

  1. -- 使用select 语句查询一个结果集,使用with as 语法将其临时保存并命名,在当前sql语句中可以查询使用,sql语句结束,结果集释放.
  2. -- 格式: with 临时表名 as (select结果集)  select 查询语句
  3. with t1 as (select * from student)
  4. select *
  5. from t1;
  7. -- 需求: 获取与年龄最大的两个女生在相同部门的男生信息
  9. -- 子查询:
  10. select *
  11. from student
  12. where sex = '男'
  13.   and dept in (select dept from student where sex = '女' order by age desc limit 2);
  15. -- CTE表达式
  16. with top_2nv_dept as (select dept from student where sex = '女' order by age desc limit 2)
  17. select s.*
  18. from student s
  19.          join top_2nv_dept t on s.dept = t.dept
  20. where sex = '男';
  23. -- CTE表达式链式调用
  24. -- 我们在CTE表达式中可以一次创建多个临时表,且后创建的临时表,可以调用先次创建的临时表
  25. with q1 as (select * from student where num = 95002),
  26.      q2 as (select num, name, age from q1)
  27. select *
  28. from (select num from q2) a;
  30. -- 在同一个select 语句中,可以使用所有的临时表
  31. with q1 as (select num, name from student where sex = '男' and age > 20),
  32.      q2 as (select num, name from student where sex = '女' and age < 20)
  33. select *
  34. from q1
  35. union
  36. select *
  37. from q2;
  39. -- 思考: CTE表达式在sql语句结束后还能否调用到之前的临时表呢? 不行
  40. -- select * from q1;
  42. set mapreduce.job.reduces;
复制代码
7.2、Hive SQL join查询(左毗连,内毗连)

7.2.1、Hive支持join语法

  1. --Join语法练习 建表
  2. drop table if exists test.employee_address;
  3. drop table if exists test.employee_connection;
  4. drop table if exists test.employee;
  6. --table1: 员工表
  7. CREATE TABLE test.employee
  8. (
  9.     id     int,
  10.     name   string,
  11.     deg    string,
  12.     salary int,
  13.     dept   string
  14. ) row format delimited
  15.     fields terminated by ',';
  17. --table2:员工家庭住址信息表
  18. CREATE TABLE test.employee_address
  19. (
  20.     id     int,
  21.     hno    string,
  22.     street string,
  23.     city   string
  24. ) row format delimited
  25.     fields terminated by ',';
  27. --table3:员工联系方式信息表
  28. CREATE TABLE test.employee_connection
  29. (
  30.     id    int,
  31.     phno  string,
  32.     email string
  33. ) row format delimited
  34.     fields terminated by ',';
  36. -- 给表添加数据
  37. -- hadoop fs -put hive_data/employee.txt /user/hive/warehouse/test.db/employee
  38. -- hadoop fs -put hive_data/employee_address.txt /user/hive/warehouse/test.db/employee_address
  39. -- hadoop fs -put hive_data/employee_connection.txt /user/hive/warehouse/test.db/employee_connection
  41. -- 查询表中的数据是否添加成功
  42. select *
  43. from test.employee;
  44. select *
  45. from test.employee_address;
  46. select *
  47. from test.employee_connection;
  49. -- 1. 内连接  inner join
  50. -- 内连接将左表和右表中的指定字段内进行匹配,保留匹配成功的数据记录,忽略没有匹配成功的数据.
  51. select *
  52. from test.employee e
  53.          inner join test.employee_address ea
  54.                     on e.id = ea.id;
  55. -- 内连接中 inner可以省略
  56. select *
  57. from test.employee e
  58.          join test.employee_address ea
  59.               on e.id = ea.id;
  61. -- 2. 左外连接  left outer join
  62. -- 左连接将左表和右表中的指定字段进行匹配,保留左表中全部的数据,右表中的数据匹配成功则保留,匹配不成功则填充null
  63. select *
  64. from test.employee e
  65.          left outer join test.employee_address ea
  66.                          on e.id = ea.id;
  68. -- 外连接中可以省略outer
  69. select *
  70. from test.employee e
  71.          left join test.employee_address ea
  72.                    on e.id = ea.id;
  74. -- 3. 右外连接  right outer join
  75. -- 右连接将左表和右表中的指定字段进行匹配,保留右表中全部的数据,左表中的数据匹配成功则保留,匹配不成功则填充null
  76. select *
  77. from test.employee e
  78.          right outer join test.employee_address ea
  79.                           on e.id = ea.id;
  81. -- 外连接中可以省略outer
  82. select *
  83. from test.employee e
  84.          right join test.employee_address ea
  85.                     on e.id = ea.id;
  87. -- 4. 全连接  full join
  88. -- 全连接将左表和右表中的指定字段进行匹配,保留左表和右表中的全部数据,如果匹配成功则保留数据,不成功则填充null
  89. select *
  90. from test.employee e
  91.          full join test.employee_address ea on e.id = ea.id;
  93. -- 5. 左半连接 left semi join
  94. -- 其实就是左表和右表进行内连接, 但是仅可以获取到左表中匹配成功的数据,右表全部记录将被删除
  95. select *
  96. from test.employee e
  97.          left semi
  98.          join test.employee_address ea on e.id = ea.id;
  100. -- 左半连接,在开发中使用的场景与内连接比较相似,但是左半连接指向效率更高,因为只获取左表中的部分数据.
  102. -- 6. 交叉连接
  103. -- 交叉连接其实就是进行笛卡尔积计算, 左表中的每一条记录,依次和右表中的每一条进行匹配,物品匹配是否成功都保留.
  104. select *
  105. from test.employee e
  106.          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服务
      1. /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore
      2. /export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2
      复制代码
    • 功能3：批处理实行Hive SQL
      1. #-e 执行后面的sql语句
      2. /export/server/hive/bin/hive  -e 'select * from itheima.students;'
      4. #-f 执行后面的sql文件(重要)
      5. vim hive.sql
      6. select * from itheima.student limit 2
      8. /export/server/hive/bin/hive  -f hive.sql
      10. #sql文件不一定是.sql 要保证文件中是正确的HQL语法。
      12. #-f调用sql文件执行的方式 是企业中hive生产环境主流的调用方式。
      复制代码

  • hive参数配置方式

    • 有哪些参数可以配置？
      1. https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
      复制代码
    • 配置方式有哪些？ 注意配置方式影响范围影响时间是怎样？

      • 方式1：配置文件 conf/hive-site.xml(影响整个由此安装包启动的服务)
      • 无论服务重启多少次,只要是配置文件没有修改,影响不停存在
        1. 影响的是基于这个安装包的任何使用方式。
        复制代码
      • 方式2：配置参数 –hiveconf(影响由被启动的服务创建的所有会话)
      • 如果服务重启,没有设置该配置,则配置失效,按照配置文件举行启动
        1. /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore  
        3. /export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2  --hiveconf                 hive.root.logger=DEBUG,console
        5. #影响的是session会话级别的
        复制代码
      • 方式3：set下令 (影响当前会话)

        • 服务启动后,谁毗连此服务,就对当前毗连服务的会话举行修改,其他会话不影响
          1. session会话级别的 设置完之后将会对后面的sql执行生效。
          2. session结束 set设置的参数将失效。
          4. 也是推荐搭建使用的设置参数方式。  谁需要 谁设置 谁生效
          复制代码



  • 总结(紧张)

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

7.4、Hive内置运算符[明白]

  1. -- 1. 查看所有的内置运算符
  2. show functions;
  4. -- 2. 查看运算符的含义
  5. desc function +;
  6. desc function `current_timestamp`;
  8. -- 3. 查看函数或运算符的使用方法
  9. desc function extended `current_timestamp`;
  11. desc function extended sum;
  13. desc function extended avg;
  15. desc function extended concat;
复制代码
  1. -- hive中的关系运算符
  2. -- 1. 比较运算符: = > < >= <= != <>
  3. select 1 > 2;
  5. -- 2. 空值判断 :  is null | is not null
  6. -- select * from student where num is null;
  7. select null is not null; -- flase
  8. select 'null' is not null;
  9. -- null
  10. -- null 可以使用=判断么?  不可以
  11. -- select null = null;  -- null 和任何数据比较,判断都为空
  13. -- 3. 模糊查询
  14. select '江湖再见' like '%湖%';
  15. -- 模糊查询在hive中很少使用,因为效率较低
  17. -- 4. 正则匹配 -- 就是一个字符串的匹配规则
  18. select 'testdemo' rlike 'testdemo';
  19. select 'testdemo' rlike '.*de.*';
  20. select '13932627450' rlike '1[3-9]\\d{9}';
  21. -- 在正则表达式中使用\要使用两个,才能输出\
  23. -- 5. regexp 也是正则匹配和rlike完全相同
  24. select 'testdemo' regexp '.*de.*';
  26. -- hive中的算数运算符
  27. -- 1. + - * / % div
  28. -- 11 / 2 = 商 5 余 1
  29. select 11 % 2; -- 1  取余
  30. select 11 div 2;
  32. -- 2. 按位与操作 -- 同一个数位两个数据都为1则结果为1, 否则为0
  33. -- 4 >>> 0100
  34. -- 8 >>> 1000
  35. -- 结果   0000
  36. select 4 & 8;
  37. select 7 & 9;
  38. -- 7 >>> 0111   9 >>> 1001
  40. -- 3. 按位或操作 -- 同一个数位两个数据有任意一个为1则结果为1, 否则为0
  41. select 4 | 8; -- >>> 1100
  42. select 7 | 9;
  43. -- >>> 1111
  45. -- 4. 按位异或  -- 同一个数位两个数据若不同则结果为1, 否则结果为0
  46. select 4 ^ 8; -- >>> 1100
  47. select 7 ^ 9;
  48. -- >>> 1110
  50. -- 异或的其他用法
  51. -- 同一个数字连续异或两次相同的数字还是原数字
  52. select 4 ^ 8 ^ 8; -- 4
  53. select 8 ^ 4 ^ 4; -- 8
  55. -- hive中的逻辑运算
  56. -- 1. and 逻辑与 -- 同真即真
  57. select true and false;
  58. select true and true;
  60. -- 2. or 逻辑或  -- 同假即假
  61. select true or false;
  62. select true or true;
  63. select false or false;
  65. -- 3. not 逻辑非 -- 真假互换
  66. select not true;
  67. select not false;
复制代码
八、Hive函数

8.1、函数的分类

  1. -- UDF : 一进一出
  2. select round(12.444, 2);
  3. select unix_timestamp('2020-11-12 12:00:00');
  5. -- UDAF : 多进一出  聚合函数
  6. select avg(age) from student;
  8. -- UDTF : 一进多出  高阶函数
  9. select explode(`array`(1,2,3,4));
复制代码
8.1.1、内置的函数（build in func）

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

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

  • 常用内置函数

    • 字符串函数
      1. -- 1. 字符串连接
      2. -- concat(字符串1, 字符串2 ....)
      3. select concat('it', '行业', '真好', '工作', '岗位', '很多');
      5. -- concat_ws(间隔符, 字符串1, 字符串2,...)
      6. -- 根据指定的间隔符,将字符串拼接到一起
      7. select concat_ws('❤', '小明', '小芳');
      9. -- 2. 字符串拆分
      10. -- split(字符串, 间隔符) --将字符串按照指定间隔符拆分为array类型数据
      11. select split('hello python', ' ');
      12. -- 间隔符也可以使用正则表达式.
      14. -- 3. 字符串截取
      15. -- substr >>> substring (字符串, 截取位置, 截取长度) 截取位置从1开始
      16. select substr('飒爽英姿五尺枪,曙光初照演兵场!', 5, 3); -- 五尺枪
      17. select substr('飒爽英姿五尺枪,曙光初照演兵场!', 13, 3); -- 演兵场
      18. select substr('飒爽英姿五尺枪,曙光初照演兵场!', -4, 3); -- 演兵场
      20. -- 4. json数据解析 json数据是一种数据传输流 使用的是 array类型数据和map类型数据相互嵌套的数据形式
      21. select get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"},' ||
      22.                ' {"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '$[0].website');
      复制代码
    • 日期函数
      1. -- 1.获取当前日期
      2. select `current_timestamp`();
      3. select `current_date`();
      5. -- 2. 获取时间戳
      6. -- 时间戳就是从1970年1月1日 0时0分0秒到现在的秒数或毫秒数
      7. select unix_timestamp(); -- 如果()内什么也不写,就是获取当前时间的时间戳
      8. -- 获取指定时间的时间戳
      9. select unix_timestamp('2020-11-12 12:22:33'); -- 1605183753
      10. -- 获取时间戳,时间字符串必须按照 yyyy-MM-dd HH:mm:ss的格式进行书写
      11. select unix_timestamp('2020-11-12');
      13. -- 3. 通过时间戳获取 日期数据
      14. select from_unixtime(1605183753); -- 2020-11-12 12:22:33
      15. -- 获取日期时可以指定日期数据的格式
      16. select from_unixtime(0, 'yyyy年MM月dd日 HH点mm分ss秒');
      18. -- 4. 日期数据格式化 -- 事件类型以及标准时间格式的字符串都可以进行转换
      19. -- date_format
      20. select date_format(`current_date`(),'yyyy年MM月dd日 HH点mm分ss秒');
      21. select date_format('2022-3-12 12:33:44','yyyy年MM月dd日 HH点mm分ss秒');
      23. -- 5. 日期差值计算
      24. -- datediff 计算两个日期之间的差值, 代为为天, 前边的时间减去后边的时间
      25. select datediff('2022-3-12 12:33:44', `current_date`());
      27. -- 6. 日期偏移
      28. select date_add('2022-3-12 12:33:44', 5);
      29. select date_sub('2022-3-12 12:33:44', 5);
      31. -- 日期偏移量可以为负数
      32. select date_add('2022-3-12 12:33:44', -8);
      34. desc function extended date_add;
      复制代码
    • 数学函数
      1. --取整函数: round  返回double类型的整数值部分 （遵循四舍五入）
      2. select round(3.1415926);
      3. --指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
      4. select round(3.1415926,4);
      5. --向下取整函数: floor
      6. select floor(3.1415926); -- 3
      7. select floor(-3.1415926); -- -4
      8. --向上取整函数: ceil
      9. select ceil(3.1415926); -- 4
      10. select ceil(-3.1415926); -- -3
      11. --取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
      12. select rand();
      13. --指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
      14. select rand(0);
      16. -- 计算: 使用round 进行向上取整
      17. select round(9.65 + 0.5);
      18. -- 计算: 使用round 进行向下取整
      19. select round(-7.685 - 0.5);
      复制代码
    • 条件函数
      1. -- 1. if函数
      2. -- if (条件, 成立返回第一个值, 不成立返回第二个值)
      3. select *,
      4.         `if`(sex = '男', '男生', '女生')
      5. from student;
      7. -- 2. case when 同一个字段使用casewhen只能返回一个数据,也就是只有一个then会被执行.
      8. /*
      9. 格式1:
      10. case
      11.         when 条件1
      12.                 then 条件1成立时返回的数据
      13.         when 条件2
      14.                 then 条件2成立时返回的数据
      15.         .....
      16.         else 所有条件均不成立时返回的数据
      17.         end
      18. */
      19. select num,
      20.         name,
      21.         sex,
      22.         age,
      23.         case
      24.                 when dept = 'CS'
      25.                         then '计算机科学'
      26.                 when dept = 'IS'
      27.                         then '信息技术'
      28.                 when dept = 'MA'
      29.                         then '数学'
      30.                 else '未知学科'
      31.                 end
      32.                 as dept
      33. from student;
      35. /*
      36. 格式2:
      37. case 判断字段
      38.         when 字段值1
      39.                 then 字段等于值1时返回的数据
      40.         when 字段值2
      41.                 then 段等于值2时返回的数据
      42.         .....
      43.         else 所有条件均不成立时返回的数据
      44.         end
      45. */
      46. select num,
      47.         name,
      48.         sex,
      49.         age,
      50.         case dept
      51.                 when 'CS'
      52.                         then '计算机科学'
      53.                 when 'IS'
      54.                         then '信息技术'
      55.                 when 'MA'
      56.                         then '数学'
      57.                 else '未知学科'
      58.                 end
      59.                 as dept
      60. from student;
      62. -- 在开发中第一种格式使用场景更多,因为第二种只限制了字段等于某值的情况,而第一种格式可以进行各种复杂判断例如大于小于,包含不包含等,还可以在判断时进行计算.
      64. -- 3. nvl 判断数据是否为空,如果不为空则返回该值,如果为空则返回默认值
      65. select nvl('itcast','default');
      66. select nvl(null,'default');
      68. -- 4. coalesce 获取所有数据中第一个不为空的数据
      69. -- 从左至右获取第一个不为空的数据值,如果所有数据都为null则返回null
      70. select coalesce(null, 1, null, 2);
      71. select coalesce(null, null, null, 2);
      72. select coalesce(null, null, null, null);
      复制代码
    • 范例转换函数
      1. -- 类型转换函数cast
      2. -- 格式 cast(数据 as 类型)
      4. select cast('2022-11-12' as date);
      5. select '12';
      6. select cast('12' as double);
      7. select cast('12' as int);
      9. -- 在hive中数据类型可以自动转换,并不是那么敏感,但是后续使用presto 或这 impala的时候数据类型要求极其严格
      10. -- 举例:
      11. select date_format('2022-11-12', 'yyyy年-MM月');
      12. -- 在hive中上述内容可以执行,但是在presto中date_format中的第一个参数必须是时间类型参数.否则无法使用.
      14. select '12' + '13';  -- 15
      15. select concat(12, 13);  -- 1213
      复制代码
    • 脱敏函数
      1. --mask
      2. --将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
      3. select mask("abc123DEF");
      4. select mask("abc123DEF中国",'-','.','^'); --自定义替换的字母
      6. --mask_first_n(string str[, int n]
      7. --对前n个进行脱敏替换
      8. select mask_first_n("abc123DEF",4);
      10. --mask_last_n(string str[, int n])
      11. select mask_last_n("abc123DEF",4);
      13. --mask_show_first_n(string str[, int n])
      14. --除了前n个字符，其余进行掩码处理
      15. select mask_show_first_n("abc123DEF",4);
      17. --mask_show_last_n(string str[, int n])
      18. select mask_show_last_n("abc123DEF",4);
      20. --mask_hash(string|char|varchar str)
      21. --返回字符串的hash编码。
      22. select mask_hash("abc123DEF");
      复制代码

8.1.2、用户界说函数（user-defined function）

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

  • UDF根据函数输入行数和输出行数举行分类
  • UDF 、UDAF、UDTF
  1. #1、UDF（User-Defined-Function）普通函数 一进一出  输入一行数据输出一行数据
  3. 0: jdbc:hive2://node1:10000> select split("allen woon hadoop"," ");
  4. +----------------------------+--+
  5. |            _c0             |
  6. +----------------------------+--+
  7. | ["allen","woon","hadoop"]  |
  8. +----------------------------+--+
  10. #2、UDAF（User-Defined Aggregation Function）聚合函数，多进一出 输入多行输出一行
  12. count sum max  min  avg
  14. #3、UDTF（User-Defined Table-Generating Functions）表生成函数 一进多出 输入一行输出多行
  16. 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函数输出的每一行举行毗连，天生一张新的虚拟表
  1. -- 如果想学习explode函数怎么使用,需要使用desc进行查询
  2. desc function extended explode;
  4. -- 使用explode将array数据爆炸为单列多行
  5. select explode(`array`(1, 2, 3, 4)) as num;
  7. -- 使用explode将map数据爆炸为多列多行
  8. select explode(`map`('name', '小明', 'age', 18)) as (c1, c2);
  11. -- 夺冠球队案例
  12. -- 1. 建表
  13. create table the_nba_championship
  14. (
  15.         team_name     string,
  16.         champion_year array<string>
  17. ) row format delimited
  18.         fields terminated by ','
  19.         collection items terminated by '|';
  21. -- 2.上传数据
  22. load data local inpath '/root/hive_data/The_NBA_Championship.txt' into table the_nba_championship;
  24. -- 3.查询数据是否上传成功
  25. select *
  26. from the_nba_championship;
  28. -- 4. 将夺冠的年份爆炸为多行
  29. -- 爆炸后,多个年份对应一个队名,不符合结构化数据规则.不能这样书写
  30. -- select team_name, explode(champion_year) from the_nba_championship;
  32. -- 5. 我们可以使用表连接的方式进行合并
  33. select name_list.team_name,
  34.         year_list.year
  35. from the_nba_championship name_list -- 获取所有的球队名称列表
  36.                 join
  37.                 (select explode(champion_year) as year from the_nba_championship) year_list --获取所有的球队夺冠年份列表
  38.                         on array_contains(name_list.champion_year, year_list.year);
  40. -- 6. 上边的方法有点复杂,我们可以使用侧视图,非常简单的完成爆炸函数和原表数据的合并
  41. select *
  42. from the_nba_championship lateral view explode(champion_year) t as year;
  44. -- 侧视图的意义: 就是将UDTF函数在拆分为多行后,还可以与原数据表进行连接,效率更高,写法更简单.
复制代码
9.2、多行转单列



  • 数据收集函数
    1. collect_set --把多行数据收集为一行  返回set集合  去重无序
    2. collect_list --把多行数据收集为一行  返回list集合  不去重有序
    复制代码
  1. --建表
  2. create table row2col2
  3. (
  4.     col1 string,
  5.     col2 string,
  6.     col3 int
  7. ) row format delimited fields terminated by '\t';
  9. -- 查看数据是否上传成功
  10. select *
  11. from row2col2;
  14. -- 1. 将col1和col2内的相同数据进行合并,此时需要使用group by
  15. select col1,
  16.        col2
  17. from row2col2
  18. group by col1, col2;
  20. -- 2. 获取col3中所有的数据,添加到一个字段值中
  21. select col1,
  22.        col2,
  23.        collect_list(col3) as col3_list -- collect_list()作用就是将多行数据转换为一个array类型数据
  24. from row2col2
  25. group by col1, col2;
  27. -- 3. 使用字符串拼接函数 concat_ws 将数组类型数据拼接为 1-2-3这种格式的数据
  28. -- :Argument 2 of function CONCAT_WS must be "string or array<string>", but "array<int>" was found.
  29. -- select col1,
  30. --        col2,
  31. --        concat_ws('-',collect_list(col3)) as col3_list -- collect_list()作用就是将多行数据转换为一个array类型数据
  32. -- from row2col2
  33. -- group by col1, col2;
  35. desc function extended concat_ws;
  36. desc function extended concat;
  38. -- 4. concat_ws只能使用内部是string类型数据的数组,不能使用内部是int类型数据的数组.
  39. select col1,
  40.        col2,
  41.        concat_ws('-', collect_list(cast(col3 as string))) as col3 -- collect_list()作用就是将多行数据转换为一个array类型数据
  42. from row2col2
  43. group by col1, col2;
复制代码
9.3、单列转多行



  • 技术原理： explode+lateral view
  1. --创建表
  2. create table col2row2
  3. (
  4.     col1 string,
  5.     col2 string,
  6.     col3 string
  7. ) row format delimited fields terminated by '\t';
  9. -- 检测数据是否上传成功
  10. select *
  11. from col2row2
  12. limit 1000;
  14. -- 1. 将 col3 中的数据内容,从字符串类型转换为数组类型数据.
  15. select col1,
  16.        col2,
  17.        split(col3, ',') as col3
  18. from col2row2;
  20. -- 2. 将col3中的数组信息炸开,炸开称为多行数据
  21. select explode(split(col3, ','))
  22. from col2row2;
  24. -- 3. 使用侧视图,将UDTF函数的结果和原数据表进行连接
  25. select col1, col2, col_3
  26. 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的函数
      1. json_tuple
      2. --是UDTF 表生成函数  输入一行，输出多行  一次提取多个值  可以单独使用 也可以配合lateral view侧视图使用
      4. get_json_object
      5. --是UDF普通函数，输入一行 输出一行 一次只能提取一个值 多次提取多次使用
      复制代码
    • 使用==JsonSerDe 类解析==，在加载json数据到表中的时间完成解析动作

  1. --创建表
  2. create table tb_json_test1
  3. (
  4.     json string
  5. );
  7. select *
  8. from tb_json_test1;
  10. -- 1. 使用get_json_object 解析json
  11. select get_json_object(json, '$.device')     as device,
  12.        get_json_object(json, '$.deviceType') as deviceType,
  13.        get_json_object(json, '$.signal')     as signal,
  14.        get_json_object(json, '$.time')       as `time`
  15. from tb_json_test1;
  17. -- 2. 使用json_tuple 解析json
  18. select json_tuple(json, 'device', 'deviceType', 'signal', 'time') as (device, deviceType, signal, `time`)
  19. from tb_json_test1;
  21. desc function extended json_tuple;
  23. -- 使用json_tuple 配合侧视图进行使用
  24. select json,device, deviceType, signal, `time`
  25. from tb_json_test1 lateral view
  26.     json_tuple(json, 'device', 'deviceType', 'signal', 'time') t_json
  27.     as device, deviceType, signal, `time`;
  29. -- 3.使用jsonserde进行映射
  30. create table tb_json_test2
  31. (
  32.     device string,
  33.     deviceType string,
  34.     signal string,
  35.     `time` string
  36. )
  37. row format serde 'org.apache.hive.hcatalog.data.JsonSerDe';
  39. select * from tb_json_test2;
复制代码
十、Hive 窗口函数[重中之重]

   

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

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

    • preceding：往前
    • following：往后
    • current row：当前行
    • unbounded：起点
    • unbounded preceding 表示从前面的起点 第一行
    • unbounded following：表示到背面的终点 末了一行
       
    1. -- 建表并且加载数据
    2. create table website_pv_info
    3. (
    4.         cookieid   string,
    5.         createtime string, --day
    6.         pv         int
    7. ) row format delimited
    8.         fields terminated by ',';
    10. create table website_url_info
    11. (
    12.         cookieid   string,
    13.         createtime string, --访问时间
    14.         url        string  --访问页面
    15. ) row format delimited
    16.         fields terminated by ',';
    18. -- 检查数据是否加载完成
    19. select *
    20. from website_pv_info;
    21. select *
    22. from website_url_info;
    24. -- 需求1: 查询每个用户最近三天的pv数之和 (前天 昨天 今天  >>> 前2行 前1行 和 当前行)
    25. -- 按照用户进行分组,按照时间进行排序,此时就知道每个用户登录的时间和次数了
    26. select *,
    27.         sum(pv) over (partition by cookieid order by createtime
    28.                 rows between 2 preceding and current row
    29.                 )
    30. from website_pv_info;
    33. -- 计算前三行到后三行的pv数之和  注意此时一共涉及了7行数据  前三行 + 后三行 + 当前行
    34. select *, sum(pv) over (partition by cookieid order by createtime
    35.         rows between 3 preceding and 3 following)
    36. from website_pv_info;
    38. -- 计算当前行到分组末尾的pv数
    39. select *, sum(pv) over(partition by cookieid order by createtime
    40.         rows between current row and unbounded following) from website_pv_info;
    42. -- 计算分组开始 到当前行的pv数  -- 这个就是默认开创范围
    43. select *, sum(pv) over(partition by cookieid order by createtime
    44.         rows between unbounded preceding and current row ) from website_pv_info;
    复制代码

  • 窗口函数-排序函数
    1. -- 需求1 : 给每日访问量进行排序
    2. select *,
    3.         row_number() over (partition by cookieid order by pv) as r1,
    4.         dense_rank() over (partition by cookieid order by pv) as r2,
    5.         rank() over (partition by cookieid order by pv)       as r3
    6. from website_pv_info;
    9. -- row_number就是从上到下依次编号,是否排序与其无关
    10. -- dense_rank 和 rank 都是按照order by指定的字段大小进行编号,如果没有使用order by进行排序,则输出均为1
    11. -- rank 在编号时,如果排序字段值相同,则赋予相同的编号,跳过未使用的编号 例如  113446
    12. -- dense_rank在编号时,如果排序字段值相同,则赋予相同的编号,不跳过未使用的编号 例如  112334
    13. select *,
    14.         row_number() over (partition by cookieid ) as r1,
    15.         dense_rank() over (partition by cookieid ) as r2,
    16.         rank() over (partition by cookieid )       as r3
    17. from website_pv_info;
    19. -- 如果字段按照降序排列,则最大值编号最小.最小值编号最大.
    20. select *,
    21.         row_number() over (partition by cookieid order by pv desc ) as r1,
    22.         dense_rank() over (partition by cookieid order by pv desc ) as r2,
    23.         rank() over (partition by cookieid order by pv desc )       as r3
    24. from website_pv_info;
    27. -- NTILE(n) 将数据分为n块 , 每块的数据量相同,如果不能均分,则优先给编号较小的分块
    28. SELECT
    29.         cookieid,
    30.         createtime,
    31.         pv,
    32.         NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
    33. FROM website_pv_info
    34. ORDER BY cookieid,createtime;
    复制代码
  • 窗口函数-其他窗口函数
    1. -- LAG 获取当前行之前第n行数据
    2. select *,
    3.         lag(createtime, 2) over (partition by cookieid order by createtime),
    4.         lag(createtime, 2, '9999-99-99 00:00:00') over (partition by cookieid order by createtime)
    5. from website_url_info;
    7. -- LEAD 获取当前行之后第n行数据
    8. select *,
    9.         lead(createtime, 2) over (partition by cookieid order by createtime),
    10.         lead(createtime, 1, '9999-99-99 00:00:00') over (partition by cookieid order by createtime)
    11. from website_url_info;
    13. -- first_value 获取开窗范围内的第一个数据值
    14. select *,
    15.         first_value(createtime) over (partition by cookieid order by createtime)
    16. from website_url_info;
    18. -- last_value 获取开窗范围内的最后一个数据值
    19. select *,
    20.         last_value(createtime) over (partition by cookieid order by createtime)
    21. from website_url_info;
    复制代码
十一、Hive的数据压缩(节流磁盘空间,提拔io服从)



  • Hive的默认实行引擎是MapReduce，因此通常所说的Hive压缩指的是MapReduce的压缩。
  • 压缩是指通过算法对数据举行重新编排，低落存储空间。无损压缩。
  • MapReduce可以在两个阶段举行数据压缩

    • map的输出

      • 淘汰shuffle的数据量 提高shuffle时网络IO的服从

    • reduce的输出

      • 淘汰输出文件的大小 低落磁盘的存储空间


  • 压缩的弊端

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

  • 压缩的算法（推荐使用snappy）
    1. Snappy
    2. org.apache.hadoop.io.compress.SnappyCodec
    3. snappy的压缩比例不是很高,但是解压和压缩速度非常快,而且免费
    5. apache版本的hadoop不支持snappy, 我们给大家对的安装包已经进行支持,并重新编译
    复制代码
  • (相识)Hive中压缩的设置：注意 本质还是指的是MapReduce的压缩
    1. --设置Hive的中间压缩 也就是map的输出压缩
    2. 1）开启 hive 中间传输数据压缩功能
    3. set hive.exec.compress.intermediate=true;
    4. 2）开启 mapreduce 中 map 输出压缩功能
    5. set mapreduce.map.output.compress=true;
    6. 3）设置 mapreduce 中 map 输出数据的压缩方式
    7. set mapreduce.map.output.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
    9. --设置Hive的最终输出压缩，也就是Reduce输出压缩
    10. 1）开启 hive 最终输出数据压缩功能
    11. set hive.exec.compress.output=true;
    12. 2）开启 mapreduce 最终输出数据压缩
    13. set mapreduce.output.fileoutputformat.compress=true;
    14. 3）设置 mapreduce 最终数据输出压缩方式
    15. set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;
    16. 4）设置 mapreduce 最终数据输出压缩为块压缩  还可以指定RECORD
    17. set mapreduce.output.fileoutputformat.compress.type=BLOCK;  
    复制代码
    1. --设置完毕之后  只有当HiveSQL底层通过MapReduce程序执行 才会涉及压缩。
    2. --已有普通格式的表
    3. select * from student;
    5. --ctas语句  这种压缩方式,很少用  因为我们不会将所有的表都是用压缩模式,而且不会将所有的表读使用同一种压缩模式
    6. create table student_snappy as select * from student;
    8. 一般情况下我们再ods层 会使用压缩比例较高的压缩类型
    9. 一般在dw层或者da层都会使用压缩和解压效率较高的压缩类型
    复制代码
    注意: 常用的压缩格式就两种 : zlib(压缩比例最高) snappy(压缩个解压速率最快)
十二、Hive的数据存储格式



  • 列式存储、行式存储

    • 数据最终在文件中底层以什么样的形成生存。
    • 行存储:写入服从高,读取服从低

      • 写入数据时按行写入

    • 列存储: 写入服从低,读取服从高(HIVE发起使用列式存储) 且压缩比例高.由于雷同数据范例的数据存储在一起方便压缩

      • 读取数据时按列读取





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

    • textfile 文件格式
    • ORC、Parquet 列式存储格式。
      1. 都是列式存储格式，底层是以二进制形式存储。数据存储效率极高，对于查询贼方便。
      2. 二进制意味着肉眼无法直接解析，hive可以自解析。
      复制代码

  1. --1、创建表，存储数据格式为TEXTFILE
  2. create table log_text (
  3. track_time string,
  4. url string,
  5. session_id string,
  6. referer string,
  7. ip string,
  8. end_user_id string,
  9. city_id string
  10. )
  11. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  12. STORED AS TEXTFILE;  --如果不写stored as textfile 默认就是textfile
  14. --加载数据
  15. load data local inpath '/root/hivedata/log.data' into table log_text;
  17. -- 检测数据是否执行成功
  18. select * from log_parquet;
  20. --2、创建表，存储数据格式为ORC
  21. create table log_orc(
  22. track_time string,
  23. url string,
  24. session_id string,
  25. referer string,
  26. ip string,
  27. end_user_id string,
  28. city_id string
  29. )
  30. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  31. STORED AS orc ;
  33. --向表中插入数据 思考为什么不能使用load命令加载？ 因为load是纯复制移动操作 不会调整文件格式。
  34. insert into table log_orc select * from log_text;
  36. --3、创建表，存储数据格式为parquet
  37. create table log_parquet(
  38. track_time string,
  39. url string,
  40. session_id string,
  41. referer string,
  42. ip string,
  43. end_user_id string,
  44. city_id string
  45. )
  46. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  47. STORED AS PARQUET ;
  49. --向表中插入数据
  50. insert into table log_parquet select * from log_text ;
复制代码
十三、Hive通用调优

13.1、Fetch抓取机制

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

  • 功能：在实行sql的时间，能不走MapReduce步伐处理就尽量不走MapReduce步伐处理。
  • 尽量直接去操作数据文件。
  • 设置： hive.fetch.task.conversion= more。
  1. -- Fatch抓取
  2. select * from student;
  3. -- 没有涉及计算,不需要走mr任务
  4. select * from student where age > 20;
复制代码
13.2、mapreduce本地模式



  • 功能：如果非要实行MapReduce步伐，能够本地实行的，尽量不提交yarn上实行。
  • 默认是关闭的。意味着只要走MapReduce就提交yarn实行。
    1. mapreduce.framework.name = local 本地模式
    2. mapreduce.framework.name = yarn 集群模式
    复制代码
  • Hive提供了一个参数，自动切换MapReduce步伐为本地模式，如果不满足条件，就实行yarn模式。
    1. set hive.exec.mode.local.auto = true;
    3. --3个条件必须都满足 自动切换本地模式
    4. The total input size of the job is lower than: hive.exec.mode.local.auto.inputbytes.max (128MB by default)  --数据量小于128M
    6. The total number of map-tasks is less than: hive.exec.mode.local.auto.tasks.max (4 by default)  --maptask个数少于4个
    8. The total number of reduce tasks required is 1 or 0.  --reducetask个数是0 或者 1
    复制代码
  • 切换Hive的实行引擎
    1. 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.
    3. 如果针对Hive的调优依然无法满足你的需求 还是效率低， 尝试使用spark计算引擎 或者Tez.
    复制代码
13.3、join优化




    • 开启map端jion 不须要实行shuffle


    • 先对于毗连字段举行null值过滤


    • 对于毗连字段分桶,并且两个表的分桶数目最好是雷同大概是倍数关系

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

    • map端join
    • reduce端join

  • 优化1：Hive自动尝试选择map端join提高join的服从 省去shuffle的过程。
    1. 开启 mapjoin 参数设置：
    2. （1）设置自动选择 mapjoin
    3. set hive.auto.convert.join = true;  --默认为 true
    4. （2）大表小表的阈值设置：
    5. set hive.mapjoin.smalltable.filesize= 25000000; -- 此处为小于25M就是一个小表可以走map端join,否则不行
    复制代码
    不使用map端join

    使用map端join

  • 优化2：大表join大表

    • 可以对于毗连字段举行分桶
    • join 数据的毗连如果毗连字段为null 毗连肯定是无意义的, 但是会占用内存和资源
    • 在join之前把空值替换或删除,则会淘汰匹配次数,提高服从
    • 可以先对于被毗连表举行过滤,再毗连,不要先毗连再过滤
    1. --背景：
    2. 大表join大表本身数据就十分具体，如果join字段存在null空值 如何处理它？
    4. --方式1：空key的过滤  此行数据不重要
    5. 参与join之前 先把空key的数据过滤掉
    6. SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id =b.id;
    8. --方式2：空Key转换
    9. CASE WHEN a.id IS NULL THEN 'xxx任意字符串' ELSE a.id END
    10. CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id  --避免转换之后数据倾斜 随机分布打散
    复制代码

  • 优化3：桶表join提高优化服从。bucket mapjoin
    1. 1.1 条件
    2.         1） set hive.optimize.bucketmapjoin = true;
    3.         2） 一个表的bucket数是另一个表bucket数的整数倍(计算余数的时候更方便)
    4.         3） bucket列 == join列
    5.         4） 必须是应用在map join的场景中
    7. 1.2 注意
    8.         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. （1）是否在 Map 端进行聚合，默认为 True
  2. set hive.map.aggr = true;
  3. （2）在 Map 端进行聚合操作的条目数目
  4. set hive.groupby.mapaggr.checkinterval = 100000;
  5. （3）有数据倾斜的时候进行负载均衡（默认是 false）
  6. set hive.groupby.skewindata = true;
  8. --Q:在hive中数据倾斜开启负载均衡之后 底层执行机制是什么样？
  10. --step1:启动一个MapReduce程序 将倾斜的数据随机发送到各个reduce中 进行打散
  11.         每个reduce进行聚合都是局部聚合
  12.         
  13. --step2:再启动第二个MapReduce程序 将上一步局部聚合的结果汇总起来进行最终的聚合      
复制代码
13.5、hive中如何调解底层MapReduce中task的个数（并行度）



  • maptask个数

    • 如果是在MapReduce中 maptask是通过逻辑切片机制决定的。
    • 但是在hive中，影响的因素许多。好比逻辑切片机制，文件是否压缩、压缩之后是否支持切割。
    • 因此在Hive中，调解MapTask的个数，直接去HDFS调解文件的大小和个数，服从较高。(调解block size大小, block块数目)
      1. 如果是小文件,默认每一个文件会使用一个map任务
      2. 如果是大文件,会根据split size 参数的大小进行分割,将每一个部分放入不同的map任务中.
      3. 但是,split size 默认值为 128M  与 block size 相同, 所以此时我们可以将一个块放入一个map 任务重,有多少个块,就有多少个map任务
      复制代码

  • reducetask个数

    • 如果在MapReduce中，通过代码可以直接指定 job.setNumReduceTasks(N)
    • 在Hive中，reducetask个数受以下几个条件控制的
      1. （1）每个 Reduce 处理的数据量默认是 256MB
      2. hive.exec.reducers.bytes.per.reducer=256000000
      3. （2）每个任务最大的 reduce 数，默认为 1009
      4. hive.exec.reducsers.max=1009
      5. （3）mapreduce.job.reduces
      6. 该值默认为-1，由 hive 自己根据任务情况进行判断。
      9. --如果用户不设置 hive将会根据数据量或者sql需求自己评估reducetask个数。
      10. --用户可以自己通过参数设置reducetask的个数
      11.   set mapreduce.job.reduces = N
      12. --用户设置的不一定生效，如果用户设置的和sql执行逻辑有冲突，比如order by，在sql编译期间，hive又会将reducetask设置为合理的个数。  
      14. Number of reduce tasks determined at compile time: 1
      复制代码

13.6、其他几个通用调优



  • 实行计划explain

    • 通过实行计划可以看出hive接下来是如何打算实行这条sql的。
    • 语法格式：explain + sql语句
      1. -- 执行计划
      2. -- 执行计划,不走mr任务,这个就是将sql语句输入到driver程序中,给我们的一个执行规划,该规划,在实际执行中还会发生改变.
      3. -- 执行计划只是代码执行的参照
      4. explain select * from student;
      6. explain select sex,avg(age) from student group by sex;
      复制代码

  • 并行实行机制

    • 如果hivesql的底层某些stage阶段可以并行实行，就可以提高实行服从。
    • 条件是stage之间没有依靠 并行的弊端是瞬时服务器压力变大。
    • 参数
      1. set hive.exec.parallel=true; --是否并行执行作业。适用于可以并行运行的 MapReduce 作业，例如在多次插入期间移动文件以插入目标
      2. set hive.exec.parallel.thread.number=16; --最多可以并行执行多少个作业。默认为8。
      复制代码

  • Hive的严格模式

    • 注意！不要和动态分区的严格模式搞肴杂。
    • 这里的严格模式指的是开启之后 hive会禁止一些用户都影响不到的错误包括服从低下的操作，不允许运行一些有风险的查询。
    • 设置
      1. set hive.mapred.mode = strict --默认是严格模式  nonstrict
      3. -- 必须按照分区字段进行筛选才可以执行,否则不行
      4. -- select * from itheima.t_all_hero_part where attack_max > 500;
      5. select * from itheima.t_all_hero_part where role='archer';
      复制代码
    • 解释
      1. 1、如果是分区表，没有where进行分区裁剪 禁止执行
      2. 2、order by语句必须+limit限制
      复制代码


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

立聪堂德州十三局店

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

存储 挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表