大数据笔记28—Hadoop基础篇11 (Hive的基本语法与使用)

Hive的基本语法与使用

苟有恒，何必三更眠五更起；
最无益，莫过一日曝十日寒。

知识点01：yarn回顾

1. TextInputFormat中分片的规则是什么？

   • 规则：判断文件大小是否满足128M的1.1

   • 满足：128M一个分片

   • 不满足：整体成为一个分片

   • 公式

     max(minSplit,min(MaxSplit,BlockSize))
     

2. 什么是Reduce Join，特点和应用场景是什么？

   • 思想：将两个文件的关联字段作为K2，利用Shuffle过程中的分组实现两个文件的关联

   • 特点：必须经过Shuffle过程，Reduce端实现join

   • 场景：大文件 join 大文件

3. 什么是Map Join，特点和应用场景是什么？

   • 思想：将小文件的整个内容放入分布式缓存中，在每台节点上让大文件的每个部分都与完整的小文件进行关联

   • 特点：不需要经过shuffle，Map端实现Join

   • 场景：小 join 大，小 join 小

4. YARN的架构是什么？

   • 分布式主从架构

   • ResourceManager

     • 管理从节点：心跳机制
     • 资源管理和任务调度
     • 接客

   • NodeManager

     • 负责管理自己所在节点的资源，执行主节点分配的任务

5. 常见的调度机制有哪些，各自的特点和区别是什么？

   • FIFO：单队列，队列内部是FIFO，不能并行和并发

   • Capacity：多队列，队列内部是FIFO，能并行

   • Fair：多列列，队列内部是公平共享资源，能并行和并发

6. 什么是Hive，有什么功能，应用场景是什么？

   • Hive本质：一种支持SQL的Hadoop客户端
   • 功能
     • 一：将HDFS文件映射成表的数据
       • 构建数据仓库
     • 二：将SQL转换为MR，提交给YARN
       • 实现分布式计算：替代品：Presto、Impala、SparkSQL……
   • 应用
     • 用于构建数据仓库

知识点02：今日目标

1. Hive的功能是怎么实现的？【重点】
   • 如何能将HDFS的文件映射成表的数据？
   • 如何能将SQL转换为MapReduce的程序？
2. Hive元数据的管理==【掌握】==
   • 元数据存储
   • 元数据使用
   • 元数据服务
3. Hive基本使用
   • 客户端
   • SQL脚本
   • 命令、日志配置
4. HiveSQL语法：HQL**【重点】**
   • DDL
   • DML
   • DQL
5. Hive中表的分类
   • 管理表
   • 临时表
   • 外部表

知识点03：Hive与HDFS的数据映射

• 引入：SQL对表进行操作，Hive是如何知道表对应的HDFS数据是哪个的？

• 目标：掌握Hive与HDFS数据映射关系

• 路径

  • step1：Hive对象与HDFS的关系
  • step2：元数据映射

• 实施

  • Hive对象与HDFS的关系

    • 数据库：每个数据库会在HDFS中对应一个目录

      • 目录的名字：数据库名.db

- 表：每张表会在数据库对应的目录下建立一个与表同名的目录

- 表的数据：映射的是HDFS上的文件

• 元数据映射

  • 当执行SQL语句，Hive如何知道这个表对应的HDFS的数据是什么？

    select * from wordcount;
    

  • 所有Hive中数据库、表与HDFS的映射关系都存储在元数据中，Hive服务端会读取元数据找到这张表对应的HDFS数据

  • 过程

    • 先检索数据库的信息

  - 再检索表的信息

  - 通过表的SD_ID来获取这张表映射的HDFS的地址

  - 将整个表的目录中所有数据进行读取并返回

• 小结

  • Hive与HDFS的映射关系怎么实现的？
  • 将Hive中对象与HDFS映射记录在元数据中
    • 数据库：HDFS目录
    • 表：HDFS目录
    • 表中的数据：HDFS文件

知识点04：Hive转换MapReduce过程

• 引入：SQL对表进行操作，Hive是如何转换为MapReduce程序的？

• 目标：了解Hive转换MapReduce的过程

• 路径

  • step1：功能映射
  • step2：执行解析

• 实施

  • 功能映射

    • SQL语法

      select 1 from 2 where 3 group by 4 having 5 order by 6 limit 7
      

      • 1-决定了结果中的列的内容

    • MapReduce阶段

      • Input、Map、Shuffle、Reduce、Output

    • 映射

      MapReduce	SQL
      Input	2
      Map	1,2
      Shuffle	4，6
      Reduce	5,7
      Output	将SQL结果保存

  • 执行解析

    select region,count(*) as numb from tb_house where region != '浦东' group by region order by numb;
    

    • 查看执行计划：explain

      explain select region,count(*) as numb from tb_house where region != '浦东' group by region order by numb;
      

• 小结

  • 了解SQL语法与MapReduce的基本映射关系即可

知识点05：元数据：metastore功能与存储

• 引入：Hive的元数据的功能是什么？元数据都存储在什么位置？

• 目标：了解metastore的功能与存储方式

• 路径

  • step1：metastore的功能
  • step2：metastore的存储方式

• 实施

  • metastore的功能

    • Hive中的元数据记录了Hive中所有对象信息
      • 数据库信息
      • 表的信息
      • 字段的信息
    • 重点记录了Hive表与HDFS文件的映射关系
      • 每次创建表关联文件：Hive都会自动创建表的元数据
      • 每次查询表的数据：Hive都会从元数据中获取表对应的HDFS信息

  • metastore的存储三种方式

    • 方式

      • 嵌入式数据库：Local/Embedded Metastore Database (Derby)
        • 存储在derby中
      • 本地数据库
        • 存储在MySQL中，直接访问
      • 远程Metastore服务
        • 存储在MySQL中，但是是通过一个进程来进行访问

    • 位置

      • 默认位置：Hive自带的Derby数据库

        • 缺点：不能共享，不能启动多个实例，一般不用

      • 自定义位置：自定义将元数据存储在其他数据库中

        • 类型：MySQL、Oracle、PostGrepSQL，工作中一般存储在MySQL中

• 小结

  • metastore的功能是什么？
    • 存储Hive中所有对象的信息：数据库、表、列
    • 存储Hive中表与HDFS的映射关系
  • metastore存储在什么位置？
    • 默认位置：derby数据库
    • 自定义位置：MySQL中

知识点06：元数据：metastore共享问题

• 引入：实际工作中如果不使用Hive本身做计算处理，外部的计算系统如何获取Hive中的表的信息？

• 目标：了解Hive中的元数据共享问题

• 路径

  • step1：工作中的应用场景
  • step2：面临的问题

• 实施

  • 工作中的应用场景
    • 不使用Hive来实现数据仓库中的分布式计算
    • 使用替代品：SparkSQL、Impala、Presto
    • 更快，性能更好，语法都兼容Hive的语法
  • 面临的问题
    • 问题1：如果用SparkSQL来处理Hive数据仓库中的表，SparkSQL怎么知道Hive中有哪些表？
      • 解决：让SparkSQL读取Hive元数据就可以了
    • 问题2：如何SparkSQL获取了Hive的元数据，SparkSQL怎么知道这个元数据的含义是什么？
      • 解决：解析元数据的含义
    • 问题3：如果多个框架都需要访问Hive的元数据，每个框架都封装解析代码，就非常冗余

• 小结

  • metastore共享的问题是什么？
  • 大家都需要访问元数据，但是只有Hive知道元数据是什么含义，怎么解决

知识点07：元数据：metastore服务

• 引入：如何解决元数据共享的问题？

• 目标：掌握metastore服务的功能与使用

• 路径

  • step1：metastore的功能
  • step2：metastore的配置
  • step3：metastore的启动

• 实施

  • metastore的功能

    • 元数据共享服务

  • step1：专门负责管理Hive的元数据

    • step2：接受所有需要访问元数据的请求

- **注意：一旦配置了Metastore，必须先启动metastore，再启动Hive的服务端**

• metastore的配置

  • 修改hive-site.xml

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

  • metastore的启动

    • 先启动Metastore服务

      hive --service metastore
      

- 再启动Hive的服务端和客户端

  ```
  hive
  ```

• 小结

  • metastore服务的功能是什么？

    • 实现元数据共享

  • metastore服务如何启动？

    hive --service metastore
    

知识点08：Hive客户端与服务端

• 目标：掌握Hive 客户端的功能及应用场景

• 路径

  • step1：Hive shell
  • step2：Beeline与hiveserver2
  • step3：JDBC

• 实施

  • Hive shell

    • 功能：Hive的特殊客户端，启动时会自动包含启动服务端
    • 命令
      • hive
    • 特点
      • 服务端和客户端一体
      • 交互性不太友好
    • 应用
      • 用于封装SQL脚本

  • Beeline与hiveserver2

    • 功能

      • beeline：纯客户端

    • hiveserver2：Hive中独立的服务端进程

    • 命令

      • beeline：启动Hive纯客户端

        beeline -u jdbc地址 -n  用户名  -p 密码
        beeline
        	!connect jdbc地址
        	用户名
        	密码
        

      • hiveserver2：启动Hive服务端

        hive --service hiveserver2
        或者
        hiveserver2
        

      • 启动测试

        • 先启动metastore

          hive --service metastore
          

          • 端口：9083

        • 再启动Hive服务端

          hiveserver2
          

          • 端口:10000

    • 配置

      • 修改Hadoop用户授权

        • 关闭HDFS和YARN

          stop-dfs.sh
          stop-yarn.sh
          

        • 修改core-site.xml

          cd /export/server/hadoop-2.7.5/etc/hadoop/
          vim core-site.xml 
          

          <property> 
              <name>hadoop.proxyuser.root.hosts</name>
              <value>*</value> 
          </property> 
          <property> 
              <name>hadoop.proxyuser.root.groups</name> 
              <value>*</value> 
          </property>
          

        • 分发

          scp core-site.xml node2:$PWD
          scp core-site.xml node3:$PWD
          

        • 启动HDFS和YARN

          start-dfs.sh
          start-yarn.sh
          

      • 复制standalone包

        cd /export/server/hive-2.1.0-bin
        cp jdbc/hive-jdbc-2.1.0-standalone.jar lib/
        

    • 启动

      • 方式一：直接进入

        beeline
        !connect jdbc:hive2://node3:10000
        root
        123456
        

  - 方式二：直接连接

    ```
    beeline -u jdbc:hive2://node3:10000 -n root -p 123456
    ```

  - 退出：!quit

- 特点

  - 交互性比较友好

- 应用

  - 一般用于交互式的查询

• JDBC

  • 语法

    • 基本与MySQL的JDBC是一致的

      //step1：指定驱动类
      //step2：构建连接对象
      //step3：构建SQL对象
      //step4：执行SQL获取结果
      

  • 测试

    package bigdata.itcast.cn.hive.client.jdbc;
    
    import java.sql.SQLException;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.sql.DriverManager;
    
    public class HiveJdbcClient {
        private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    
        /**
         * @param args
         * @throws SQLException
         */
        public static void main(String[] args) throws SQLException {
            try {
                Class.forName(driverName);
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                System.exit(1);
            }
            //replace "hive" here with the name of the user the queries should run as
            Connection con = DriverManager.getConnection("jdbc:hive2://node3:10000/default", "root", "123456");
          Statement stmt = con.createStatement();
            String tableName = "tb_house";
          String sql = "select region,t_price,s_price from " + tableName +" limit 100";
            System.out.println("Running: " + sql);
            ResultSet res = stmt.executeQuery(sql);
            while (res.next()) {
                System.out.println(res.getString(1) + "\t" + res.getInt(2)+ "\t" + res.getInt(3));
            }
        }
    }
    

  • 应用

    • 一般用于封装交互式的程序：Navicat、DataGrip

• 小结

  • Hive的常见客户端以及各自的应用场景是什么？

    • 客户端

      • Hive Shell：客户端与服务端是一体的，用于封装SQL脚本
      • Beeline：交互性比较友好，单纯的客户端，必须搭配服务端来实现，一般用于交互式开发测试
      • JDBC：原生的JDBC的接口代码，一般用于封装交互式的客户端，我们一般不用

    • 服务端

      • hiveserver2:10000

        hive --service hiveserver2
        hiveserver2
        

      • metastore：9083

知识点09：启动脚本与SQL脚本

• 引入：工作中如何封装SQL脚本实现SQL脚本的自动化运行？

• 目标：掌握HIve的SQL脚本的封装

• 路径

  • step1：封装Hive服务端的启动脚本
  • step2：HiveSQL脚本的封装
  • step3：SQL脚本中传递变量

• 实施

  • 封装Hive服务端的启动脚本

    • 创建日志目录

      mkdir /export/server/hive-2.1.0-bin/logs
      

      • DEBUG：详细的日志级别
      • INFO：显示的信息会包含主要的日志信息
      • WARN：只记录警告级别的日志
      • ERROR：只记录错误级别的日志

    • Metastore启动脚本

      vim /export/server/hive-2.1.0-bin/bin/start-metastore.sh
      

      #!/bin/bash
      #HIVE_HOME
      HIVE_HOME=/export/server/hive-2.1.0-bin
      #run metastore
      $HIVE_HOME/bin/hive --service metastore >> $HIVE_HOME/logs/metastore.log 2>&1 &
      

    • HiveServer2启动脚本

      vim /export/server/hive-2.1.0-bin/bin/start-hiveserver2.sh
      

      #!/bin/bash
      #HIVE_HOME
      HIVE_HOME=/export/server/hive-2.1.0-bin
      #run hiveserver2
      $HIVE_HOME/bin/hiveserver2  >> $HIVE_HOME/logs/hiveserver2.log 2>&1 &
      

    • Beeline启动脚本

      vim /export/server/hive-2.1.0-bin/bin/start-beeline.sh
      

      #!/bin/bash
      #HIVE_HOME
      HIVE_HOME=/export/server/hive-2.1.0-bin
      #run beeline
      $HIVE_HOME/bin/beeline -u jdbc:hive2://node3:10000 -n root -p 123456
      

    • 修改权限

      chmod u+x /export/server/hive-2.1.0-bin/bin/start-*
      

• HiveSQL脚本的封装

  • 需求：每天00:01分自动对昨天的数据做分析

    select  count(*) from table where daystr = '2021-05-01';
    

  • 问题1：每天的0点01分自动执行，怎么实现？

    • Linux Crontab：定时任务

      *	*	*	*	*		Linux command
      

    • 实现

      01	00		*		*		*	hive -e 'select  count(*) from table where daystr = '2021-05-01';'
      

  • 问题2：怎么让Hive的SQL语句在Linux的命令行执行？

    • 解决：利用Hive Shell的客户端来实现，查看客户端的用法

      [root@node3 ~]# hive -H
      usage: hive
       -d,--define <key=value>          Variable subsitution to apply to hive
                                        commands. e.g. -d A=B or --define A=B
          --database <databasename>     Specify the database to use
       -e <quoted-query-string>         SQL from command line
       -f <filename>                    SQL from files
       -H,--help                        Print help information
          --hiveconf <property=value>   Use value for given property
          --hivevar <key=value>         Variable subsitution to apply to hive
                                        commands. e.g. --hivevar A=B
       -i <filename>                    Initialization SQL file
       -S,--silent                      Silent mode in interactive shell
       -v,--verbose                     Verbose mode (echo executed SQL to the
                                        console)
      

      • -e：执行命令行中的SQL语句

        hive -e 'show databases;'
        

        • 直接执行命令行中提供的SQL语句
        • 应用：要执行比较少的单条SQL语句

      • -f：执行一个SQL文件

        • 创建一个SQL文件：vim /export/data/hive.sql

          show databases;
          use default;
          select region,s_price,area from tb_house limit 10;
          

        • 执行SQL文件

          hive -f /export/data/hive.sql
          

    • 工作中的使用

      • 定时任务

        01	00	*	*	*	bash  /export/data/exec.sh
        

      • exec.sh

        #!/bin/bash
        
        
        #定义变量
        HIVE_HOME=/export/server/hive-2.1.0-bin
        #运行SQL语句
        #$HIVE_HOME/bin/hive -e 'show databases;'
        $HIVE_HOME/bin/hive -f /export/data/hive.sql
        

• SQL脚本中传递变量

  • 问题：如果运行的SQL文件，SQL文件中的SQL语句中的参数是动态变化的，如何解决？

  • 解决：通过–hiveconf，将Shell脚本中变量转换为一个Hive中的变量

    • –hiveconf：用于定义Hive中属性的值或者定义Hive中的变量

    • shell脚本

      #!/bin/bash
      
      #获取昨天的日期
      yesterday=`date -d '-1 day' +%Y%m%d`
      
      #定义变量
      HIVE_HOME=/export/server/hive-2.1.0-bin
      #运行SQL语句
      #$HIVE_HOME/bin/hive -e 'select  count(*) from table where daystr = '${yesterday}';'
      
      $HIVE_HOME/bin/hive --hiveconf yester=${yesterday} -f /export/data/hive.sql
      

    • SQL文件

      select  count(*) from table where daystr = '${hiveconf:yester}';
      

• 小结

  • 如何封装Hive的SQL脚本？
    • 在Linux中执行SQL语句
      • -e：执行命令行中的单条SQL语句
      • -f：执行多条SQL语句封装的SQL文件
    • 变量传递
      • –hiveconf

知识点10：常用命令与日志配置

• 目标：掌握基本常用命令及实现日志存储配置

• 路径

  • step1：常用命令
  • step2：日志存储配置

• 实施

  • 常用命令

    • dfs：用于直接在Hive执行HDFS的操作

- set：查看或者临时修改【只在当前的会话窗口有效】Hive中属性的值

  - hive.exec.mode.local.auto：Hive中的本地模式

    - 默认关闭，测试时可以开启，对于小数据量的MR程序运行的比较快，不提交给YARN
    - 限制
      - MapTask的个数不允许超过4个
      - ReduceTask个数不允许超过1个
      - 输入数据量不允许超过128M

- add：添加jar包或者文件到Hive的环境变量中

  ```
  add jar  xxx.jar;
  add file xxx
  ```

- list：列举添加的文件或者jar包

  ```
  list files
  list jars
  ```

- delete：删除添加的文件或者jar包

• 日志存储配置

  • 重命名日志配置文件

    cd /export/server/hive-2.1.0-bin/conf/
    mv hive-log4j2.properties.template hive-log4j2.properties
    

  • 修改配置

    vim hive-log4j2.properties
    

    #修改第24行
    property.hive.log.dir = /export/server/hive-2.1.0-bin/logs
    

  • 重启Hive的服务端

• 小结

  • 了解常用命令并实现日志配置即可

知识点11：HQL语法：DDL

• 目标：掌握HQL语法中的DDL命令

• 路径

  • step1：数据库管理
  • step2：数据表管理

• 实施

  • Hive官网语法文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual

  • 数据库管理

    • 列举

      show databases;
      

- 创建

  ```sql
  create database [if not exists ] dbname [comment] [location]
  ```

  - comment：数据库的注释

  - location：用于指定这个数据库对应的HDFS目录是谁

    - 目录默认的位置：/user/hive/warehouse/dbname.db
    - 如果不指定，数据库目录就在默认的目录下

  - 举个栗子

    ```sql
    create database db_emp1;
    dfs -mkdir /testdb;
    create database db_emp2 location '/testdb';
    ```

- 切换

  ```sql
  use dbname;
  ```

- 删除

  ```sql
  drop database [if exists] dbname [cascade];
  ```

  - cascade：用于删除非空的数据库

• 数据表管理

  • 列举

    show tables;
    show tables in dbname;
    

- 创建

  - 语法

    ```sql
    CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
    (
    col1Name col1Type [COMMENT col_comment],
        co21Name col2Type [COMMENT col_comment],
        co31Name col3Type [COMMENT col_comment],
        co41Name col4Type [COMMENT col_comment],
        co51Name col5Type [COMMENT col_comment],
        ……
        coN1Name colNType [COMMENT col_comment]
    
    )
    [PARTITIONED BY (col_name data_type ...)]		--分区表结构
    [CLUSTERED BY (col_name...) [SORTED BY (col_name ...)] INTO N BUCKETS] --分桶表结构
    [ROW FORMAT row_format] -- 指定数据文件的分隔符
    	row format delimited fields terminated by '列的分隔符' -- 列的分隔符，默认为\001
    	lines terminated by '行的分隔符' --行的分隔符，默认\n
    [STORED AS file_format] -- 指定文件的存储格式
    [LOCATION hdfs_path] -- 用于指定表的目录所在位置，默认表的目录在数据库的目录下面
    ```

    

  - 流程

    - step1：先确认数据的格式

  - 数据的分隔符是什么

    - 有几列

  - 每一列的类型是什么

    - step2：再按照创建表的语法去建表
    - 注意：数据的格式决定了表的结构

  - **==方式一：普通方式==**

    - 功能：一般用于创建一张表加载数据文件，将文件构建表结构

    - 先查看员工数据文件格式

      - 列

        ```
        7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
        员工id
        员工姓名
        职位
        领导id
        入职日期
        薪水
        奖金
        部门编号
        ```

      - 分隔符

        - 制表符

    - 创建员工表

      ```sql
      create database db_emp;
      use db_emp;
      create table tb_emp(
      empno string,
      ename string,
      job string,
      managerid string,
      hiredate string,
      salary double,
      jiangjin double,
      deptno string
      ) row format delimited fields terminated by '\t';
      --加载数据
      load data local inpath '/export/data/emp.txt' into table tb_emp;
      ```

    - 查看部门表的数据内容

      - 数据

        ```
        10,ACCOUNTING,NEW YORK
        部门编号
        部门名称
        部门位置
        ```

      ```
        
      
      - 分隔符
      
        - 逗号
      ```

    - 创建部门表

      ```sql
      create table tb_dept(
      deptno string,
      dname string,
      loc string
      )
      row format delimited fields terminated by ',';
      --加载数据
      load data local inpath '/export/data/dept.txt' into table tb_dept;
      ```

      

  - **方式二：将Select语句的结果保存到一张新表中**

    - 关键字：as

    - 语法

      ```sql
      create table newTablename as  select ……
      ```

      ```sql
      create table tb_emp_as as select empno,ename,salary,deptno from tb_emp;
      ```

    

  - **方式三：复制表的结构到一张新表中**

    - 关键字：like

    - 语法 

      ```sql
      create table newTablename like oldTableName;
      ```

      ```sql
      create table tb_emp_like like tb_emp;
      ```

    - 注意：只复制表结构，不复制数据内容

  - 删除表

    ```sql
    drop table [if exists] tbname;
    ```

  - 查看表

    ```sql
    #查看表的结构
    desc tbname;
    #查看表的元数据
    desc formatted tbname;
    ```

  - 清空表

    ```sql
    truncate tbname;
    ```

• 小结

  • 基本与MySQL数据库的语法一致

知识点12：HQL语法：DML

• 目标：掌握HQL语法中的DML命令

• 路径

  • step1：删除与修改
  • step2：加载文件load
  • step3：插入数据insert

• 实施

  • 删除与修改

    • MySQL：增删改【行级】

      • insert
      • delete
      • update

    • Hive是否支持对数据的行级事务操作？

      • 支持

      • insert

        INSERT INTO TABLE tablename  VALUES values_row [, values_row ...]
        insert into table values(1,zhangsan)
        

        • Hive数据仓库中的数据来源
          • 已经存在的大量数据
          • 分析的结果：Select语句的结果

      • delete

        DELETE FROM tablename [WHERE expression]
        

      • update

        UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
        

    • Hive中对于行级的事务支持有严格的要求：表的结构必须为桶表，数据文件类型必须为orc类型

    • 工作中是否有对应的使用的场景？

      • 不用这样的操作，没有对应的场景
      • 如果有这样场景
        • 用户5月1号下了一个订单，订单状态为支付成功
        • 用户5月2号提交退款申请，订单状态为退款成功

  • 加载文件load

    • 应用：用于将数据文件关联到Hive的表中

    • 本质：将文件直接放入表对应的HDFS目录下

  - 可以的

- 语法

  ```
  load data [local] inpath 'filePath' [overwrite] into tbname;
  ```

  - local：表示加载本地文件，如果不加local，加载的是HDFS文件
    - hdfs文件：移动了这个文件到表的目录下
    - 本地文件：上传了本地文件到HDFS对应的表的目录下
  - overwrite：覆盖表中所有的内容

• 插入数据insert

  • 功能：将SQL语句的结果保存到一张已存在的表中或者目录中

  • 语法

    • 保存到已存在的表中：经常使用用于保存分析的结果

      INSERT OVERWRITE|INTO TABLE tablename1  
      select_statement1 FROM from_statement;
      
      |
      
      FROM from_statement
      INSERT OVERWRITE|INTO TABLE tablename1  select_statement1 ;
      

      • OVERWRITE：覆盖

      • INTO：追加

      • 实现

        insert into table tb_emp_like select * from tb_emp limit 3;
        

    • 保存到目录中：一般用于测试或者导出SQL语句的结果到文件中

      INSERT OVERWRITE [LOCAL] DIRECTORY 目录 【指定分隔符】
      select……
      

      • local：不加表示保存到HDFS的目录中，加了就是本地目录

      insert overwrite local directory '/export/data/insert' 
      row format delimited fields terminated by ','
      select * from tb_emp limit 5;
      

• 小结

  • Hive中DML的命令有哪些？
    • 支持行级的事务命令：insert、update、delete，但是不用
    • 加载数据命令
      • load：将一个已存在的文件关联到表中
      • insert：保存SQL的结果

知识点13：HQL语法：DQL

• 目标：掌握HQL语法中的DQL命令

• 实施

  • 基本查询

    • 查询每个员工的编号、姓名、薪水及部门编号

      select empno,ename,salary,deptno from tb_emp;
      

• 过滤查询

  • 查询薪资大于2000的所有员工的姓名及薪水和部门编号

    select ename,salary,deptno from tb_emp where salary > 2000;
    

• 分组查询

  • 查询每个部门的人数

    select deptno,count(*) as numb from tb_emp group by deptno;
    

• 排序查询

  • 查询所有部门人数超过3人的部门编号并按照人数降序排序

    select deptno,count(*) as numb from tb_emp group by deptno having numb > 3 order by numb desc;
    

• 关联查询

  • 查询所有员工的姓名、部门编号和部门名称

    select 
      a.ename,
      a.deptno,
      b.dname
    from tb_emp a join tb_dept b on a.deptno = b.deptno;
    

• 子查询

  • 查询除SALES部门以外的所有部门的员工信息

    select * from tb_emp where deptno not in (select deptno from tb_dept where dname = 'SALES');
    

- 子查询的另外一种写法

  ```
  with t1 as (
    select deptno from tb_dept where dname = 'SALES'
  )
  select * from t1 ;
  ```

• 小结

  • 基本与MySQL中的语法一致

知识点14：表的分类：管理表

• 目标：掌握管理表的特点及应用场景

• 实施

  • 语法

    create table ……
    

• 特点

  • 不手动删除，管理表一直存在
  • 删除表时，元数据与HDFS映射的表的目录一起被删除

• 应用

  • Hive中默认的表类型

• 小结

  • 管理表的特点是什么？
    • 不会自动删除
    • 删除时候，既删除元数据也删除数据

知识点15：表的分类：临时表

• 目标：了解临时表的特点及应用场景

• 实施

  • 语法

    create temporary table ……
    

  • 特点

    • 表是临时存在，如果客户端一旦断开，表会自动被删除，类似于ZK中的临时节点
    • 是一种特殊的管理表，这种管理表的生命周期伴随客户端的

  • 应用

    • 用于存放临时数据

  • 测试

    create temporary table tb_emp_tmp(
    empno string,
    ename string,
    job string,
    managerid string,
    hiredate string,
    salary double,
    jiangjin double,
    deptno string
    ) row format delimited fields terminated by '\t';
    
    load data local inpath '/export/data/emp.txt' into table tb_emp_tmp;
    

• 小结

  • 临时表的特点是什么？
    • 如果客户端断开，客户端创建的临时表将自动删除

知识点16：表的分类：外部表

• 目标：掌握外部表的特点及应用场景

• 实施

  • 语法

    create external table ……
    

  • 特点

    • 在删除表时，只删除元数据，数据是不会被删除

  • 应用

    • 工作中大部分的表都是外部表类型

  • 测试

    • 举例：现在有一份数据文件，要基于这个文件做两件事情

    • 数据分析师：构建一张表，做数据分析

      create external table tb_emp_ext1(
      empno string,
      ename string,
      job string,
      managerid string,
      hiredate string,
      salary double,
      jiangjin double,
      deptno string
      ) row format delimited fields terminated by '\t'
      location '/user/hive/warehouse/db_emp.db/tb_emp';
      

    • 算法工程师：构建一张表，做用户画像

      create external table tb_emp_ext2(
      empno string,
      ename string,
      job string,
      managerid string,
      hiredate string,
      salary double,
      jiangjin double,
      deptno string
      ) row format delimited fields terminated by '\t'
      location '/user/hive/warehouse/db_emp.db/tb_emp';
      

    • 算法工程师，用完了，将表删除

      drop table tb_emp_ext2;
      

• 小结

  • 外部表的特点是什么？
    • 应用：用于保证数据安全，或者用于多张表共用一份数据，互不影响
    • 特点：只删除元数据，不删除数据

附录一：Hive Maven依赖

<properties>
        <hadoop.version>2.7.5</hadoop.version>
        <mysql.version>5.1.38</mysql.version>
        <hive.version>2.1.0</hive.version>
    </properties>

    <dependencies>
        <!--引入单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <!-- Hadoop Client 依赖 -->
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>${hadoop.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>${hadoop.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-hdfs</artifactId>
            <version>${hadoop.version}</version>
        </dependency>
        <!-- MySQL Client 依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!-- Hive依赖 -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>${hive.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-common</artifactId>
            <version>${hive.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-cli</artifactId>
            <version>${hive.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>${hive.version}</version>
        </dependency>
    </dependencies>