Apache Hive_GZhY_MBY

大数据课程Hive编程

---

1. 引言

1. 什么是Hive

   1.  Hive是apache组织提供的一个基于Hadoop的数据仓库产品
       数据库   DataBase   OLTP  
       数据量级小  
       数据价值高
       数据仓库  DataWarehouse OLAP 
       数据量级大
       数据价值低
   2.  Hive基于Hadoop 
       底层数据存储 HDFS
       运算方式    MR
   

2. 原有MapReduce处理相关操作时，存在的问题|

   mapreduce处理相关操作时，繁琐
   mapReduce的作用：没有统计，没有分组合并，只做数据清洗，是可以没有reduce
   

3. Hive核心设计思想

   1. metastore （rdb） 文件 — 数据库 相关概念的对应关系 （映射 Mapping）

      Sql中的列和文件中的列对应，Sql中的表和文件对应。

   2. SQL —> 复杂的过程( 语法检查 语义检查 生成语法树 优化sql的执行顺序 )----> MapReduce

   3. 类SQL ( HQL Hive Query Language
      HQL Hibernate Query Language )

   4. 数据放在hdfs上，就只能用MapReduce进行操作。hive可以写SQL，但是底层也要翻译成MapReduce.这个过程是黑盒的，是透明的。

当写完Sql之后，Hive做两件事，第一：查对应关系。第二：找到这个对应关系后，翻译成MapReduce运行。最终才能使得程序员写Sql让Hive转化成MapReduce去运行。

2. Hive的环境搭建和基本使用

1. hive安装

   1. hadoop安装完成
   2. 解压缩hive.tar 到 /opt/install
   3. 修改hive_home/conf/hive-env.sh
      # Set HADOOP_HOME to point to a specific hadoop install directory
      HADOOP_HOME=/usr/hadoop-2.6.0
      # Hive Configuration Directory can be controlled by:
      export HIVE_CONF_DIR=/opt/install/apache-hive-0.13.1-bin/conf
   4. 在hdfs 创建  /tmp  (MapReduce会把它的临时结果放在这个目录)
                  /user/hive/warehouse  （Hive本质上是操作HDFS文件的，一定会有一个目录）
   5. bin/hive
      jps --- runjar进程
   

2. hive基本使用

   1. 查看hive中的所有数据库
      show databases
   2. 创建用户自己的库
      create database baizhi_158;
      create database if not exists baizhi_158;
   3. 使用用户自定义的数据库
      use db_name
      use baizhi_158;
   #  所谓hive中的数据库，本质就是hdfs上的一级目录 默认： /user/hive/warehouse/${db_name}.db
   4. 建表语句
      create table if not exists t_user(
       id int,
       name string
      )row format delimited fields terminated by '\t';
   5. 查看当前数据库中所有的表 
      show tables
   # 所谓hive中的表，本质就是hdfs上的一级目录 默认： /user/hive/warehouse/${db_name}.db/${table_name}
                                               /user/hive/warehouse/baizhi_158.db/t_user
   6. hive中导入数据命令
      load data local inpath '' into table table_name;
      load data local inpath '/root/hive/data/data1' into table t_user;
   
   # 所谓hive导入数据，本质就是hdfs中目录的数据文件
   # 细节：
     1. hive中的数据导入，本质上就是hdfs文件上传，删除表中的数据，本质上就是hdfs中文件的删除
        hive  load data local inpath '/root/hive/data/data1' into table t_user;
        hdfs  bin/hdfs dfs -put /root/hive/data/data1 /user/hive/warehouse/baizhi_158.db/t_user
     2. hive命令的数据导入，如果出现文件名相同的情况下，自动改名
     3. hive中表的数据，只的是这个目录下，所有文件数据之和
     4. hive 执行sql 
        select * 的操作 不启动mr
        select column  启动mr
   
   

3. 切换MetaStore从derby到mysql

hive的metastore默认使用的derby数据库，进行映射存储
问题：derby作为metastore使用，只能让客户开启一个client进行访问

1. 安装MySQL

   1. wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
   2. yum -y install mysql57-community-release-el7-10.noarch.rpm
   3. yum -y install mysql-community-server
   4. systemctl start  mysqld.service
   
   5. mysql管理员密码
      5.1 grep "password" /var/log/mysqld.log 查看临时密码
          A temporary password is generated for root@localhost: ium+hhgLH7Xv
      5.2 mysql -uroot -pium+hhgLH7Xv
      5.3 修改密码
          set global validate_password_policy=0;
          set global validate_password_length=1;
          ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
      5.4 systemctl restart mysqld.service
      5.5 mysql -uroot -p123456
   
   6. 打开mysql远端访问权限
      6.1  set global validate_password_policy=0;
      6.2  set global validate_password_length=1;
      6.3  GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";
      6.4  flush privileges;
      6.5  systemctl stop firewalld;
   

2. hive相关的设置

   hive_home/conf/hive-site.xml
    <property>
           <name>javax.jdo.option.ConnectionURL</name>
           <value>jdbc:mysql://localhost:3306/hive_mysql?createDatabaseIfNotExist=true&amp;useSSL=false</value>
           <description>JDBC connect string fora JDBC metastore</description>
       </property>
       <property>
           <name>javax.jdo.option.ConnectionDriverName</name>
           <value>com.mysql.jdbc.Driver</value>
           <description>Driver class name for aJDBC metastore</description>
       </property>
       <property>
           <name>javax.jdo.option.ConnectionUserName</name>
           <value>yourname</value>
           <description>username to use againstmetastore database</description>
       </property>
       <property>
           <name>javax.jdo.option.ConnectionPassword</name>
           <value>yourpass</value>
           <description>password to use againstmetastore database</description>
      </property>
   

3. mysql驱动jar 上传 hive/lib

4. yum加速

1. mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
2. 下载 centos7文件 并且上传 /etc/yum.repos.d/
3. yum clean all
4. yum makecache

5. Hive相关的配置参数

优先级顺序1-4。

6. Hive的命令行启动参数

1. hive终端基本的形式
   bin/hive
2. 启动hive的同时，设置hive的相关参数
   bin/hive --hiveconf hive.cli.print.current.db true
3. 启动hive的同时，指定数据库
   bin/hive --database db_name
4. 启动hive的同时，运行sql语句
   bin/hive --database baizhi_158 -e 'select * from t_user'
   bin/hive --database baizhi_158 -e 'select * from t_user' > /root/hive_file 
   bin/hive --database baizhi_158 -e 'select * from t_user' >> /root/hive_file 
5. 启动hive的同时，运行sql文件中的sql语句
   bin/hive --database baizhi_158 -f /root/hive_sql

7. Hive相关SQL命令的详解

1. 数据库相关

   1. 创建数据库
      create database if not exists db_name;  // /user/hive/warehouse/db_name.db
      create database if not exists db_name location '/suns';  //
   2. 显示所有数据库
      show databases;
   3. 使用数据库
      use db_name
   4. 删除数据库
      drop database baizhi_suns; //删除空数据库
      drop database baizhi_suns cascade //删除非空库
      
      
   

2. 表的相关操作

   1. 管理表建表

      0. 查看表的结构
         1. 查看基本结构
            desc table_name;
            desc t_user;
            describe t_user;
         2. 查看表的扩展信息
            desc extended table_name;
            desc extended t_user;
         3. 格式化查看表的扩展信息
            desc formatted table_name;
            desc formatted t_user;
            
      1. 基本建表语句
         create table if not exists table_name(
         id int,
         name string
         )row format delimited fields terminated by '\t';
       2. 指定表的存储位置
         create table if not exists table_name(
         id int,
         name string
         )row format delimited fields terminated by '\t' locaiton 'hdfs_path';
       3. as 关键字建表 （洗数据）
         create table if not exists table_name as select id,name from t_user;
         （前面表的表结构和后面的查询一致，还会把查询出的数据插入到其中）
       4. like 关键字建表 
         create table if not exists table_name like t_user;
      

   2. 外部表

      1. 语法

      1. 基本建表语句
         create external table if not exists table_name(
         id int,
         name string
         )row format delimited fields terminated by '\t';
       2. 指定表的存储位置
         create external table if not exists table_name(
         id int,
         name string
         )row format delimited fields terminated by '\t' locaiton 'hdfs_path';
       3. as 关键字建表 （洗数据）
         create external table if not exists table_name as select id,name from t_user;
       4. like 关键字建表 
         create external table if not exists table_name like t_user;
      

      2. 管理表与外部表的区别

         1. 管理表删除后，在hive中表将被删除，同时hdfs上目录也会被删除
         2. 外部表删除后，在hive中表将被删除，同时hdfs上目录不会被删除 （只删除metastore中的信息）
         两者如何使用，不同应用场景
         1. 管理表给管理员用，其它用户让他用外部表，尽量保证数据的安全；如下图。
         

3. 分区表（锦上添花，对于海量数据来说尤为重要）

   分区表一般是用于对hive查询进行优化使用，为了提高查询效率，像传统的Oracle和Mysql也都有分区，而且是第一步优化的手段。做索引也能优化但是mysql一个表的数据量级500以上，即使做了索引优化对整个系统性能的提高基本忽略不计。例如：不做优化5秒，做优化3秒，但是对于用户来说只希望1秒。而分区表可以实际提高我们的查询效率。

   1.分区必须有一个统一的父级目录，日后便于做全表的统一处理。
   2.分区的子目录和数据本身之间没有必然联系，只是区分数据。（没有必然联系的原因是因为现在是静态分区）
   

   建表语句：（指定分区依据）

   create table if not exists t_partition(
   id int,
   name string
   )partitioned by (data string) row format delimited fields terminated by ‘\t’;

   插入数据：（才会真正的划分子目录）

   load data local inpath ‘/root/hive/data/data1’ into table t_partition partition(data=‘aaa’);
   load data local inpath ‘/root/hive/data/data1’ into table t_partition partition(data=‘bbb’);

​ 统计全表：select *from t_partition;

​ 统计不同的分区：select *from t_partition where data =‘aaa’;

4.桶表（了解）

5.临时表（了解）

8. 数据的导入

1. 本地数据的导入（linux) 【重点】
   load data local inpath '' into table table_name
2. hdfs中导入数据
   load data inpath '' into table table_name
   本质：目录中的数据移动
3. hdfs相关shell完成文件导入
   bin/hdfs dfs -put 'local_path' '/user/hive/warehouse/db_name.db/table_name'  
4. as关键字 【重点】
   创建表的同时，通过查询导入数据
   create table t_xxx as select id,name from t_yyy;
5. insert关键字 【重点】
   表已经存在，通过查询导入数据
   insert into table xxxx select id,name from t_user;
   

9.数据的导出

1. insert导出 【重点】
   导出的数据 mr 自动生成
   insert overwrite local directory '/root/xiaohei' select name from t_user; 
2. hdfs的shell 【了解】
   bin/hdfs dfs -get 'user/hive/warehouse/db_name.db/table' '/root/xxx'
3. hive启动的命令行参数 【了解】
   bin/hive --database 'baizhi129' -f /root/hive.sql > /root/result
4. sqoop方式 【重点】
5. Hive导入 导出命令【了解】
   1. export 导出
	  export table tb_name to 'hdfs_path'
   2. import 导入
	  import table tb_name from 'hdfs_path'

10. Hive中HQL (类SQL)

1. 基本查询
   select * from t_user; 不启动mr
   select id from t_user; 启动mr
2. where 
   select * from t_user where id = xxx;
3. 常见谓词 in  between and  
4. 逻辑运算  and or not
5. 分组函数  count()  avg() sum() max() min()
6. 分组 group by 
7. 排序 order by 
8. 分页 limit
   不支持 limit 2,3
9. 多表联合查询
   select e.id,e.name,d.dname
   from t_emp e
   inner join t_dept d
   on e.dept_id = d.id;
10. 内置函数（窗口函数）
    select length(name) from t_user_like;
    select length('suns')
    to_date
    year
    select substring('suns',1,2)
 11. hive不支持子查询    

11. Hive中与MapReduce相关的参数设置

1. mapreduce中map个数？
   textInputFormat --- > block ----> split ----> map
   dbInputFormat   ----> 1 row ----> 1 split ----> 1 map
2. mapreduce中reduce个数？
   默认 1 个
   mapred-default.xml
   <property>
      <name>mapreduce.job.reduces</name>
      <value>1</value>
   </property>
   job中应用过程中reduce 0---n
   job.setReduceNumTasks()
 3.Hive中MapReduce相关参数的设置 
   hive-site.xml
    <property>
          <name>hive.exec.reducers.bytes.per.reducer</name>
          <value>1000000000</value>
    </property>
    <property>
         <name>hive.exec.reducers.max</name>
         <value>999</value>
    </property>

12. Hive中特殊参数设置

hive-site.xml
<property>
  <name>hive.fetch.task.conversion</name>
  <value>minimal</value>
  <description>
    1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only  不启动MR,非上述3种情况，会启动MR
    2. more    : SELECT, FILTER, LIMIT only (TABLESAMPLE, virtual columns) 所有select,where,limit 都不起MR
  </description>
</property>

13.Hive没有集群

使用shell脚本文件监控hive的进程号。

14.实战中如何使用Hive?

Hbase当成nosql数据库就行，找到业务场景设计好RowKey就行。Hive可以做数据库，但是实际不能用来做数据库，因为要起MapReduce,查询太慢。
总结：hive是一种离线处理的大数据技术，不需要实时产生处理结果，所以在hive中所做的相关操作，最终通过Sqoop导入RDB,通过Java程序直接访问RDB获得相应的结果。

15.Sqoop （CDH）

Hadoop 各种发行版本  
1.   apache开源版 ：      免费   难用 
2.   Cloudera （CDH）    Doug Cutting首席架构师  $4000   1个
3.   Hortonworks                               $12000  10个

1. 搭建CDH版Hadoop及其Hive

   1. 解压缩hadoop
   2. 创建临时目录的位置
      hadoop_home/data/tmp
   3. 修改配置文件 hadoop_home/etc/hadoop
      hadoop-env.sh
      yarn-env.sh
      mapred-env.sh
      
      core-site.xml
      <property>
           <name>fs.defaultFS</name>
           <value>hdfs://hive1:8020</value>
       </property>
   
       <property>
           <name>hadoop.tmp.dir</name>
           <value>/opt/install/hadoop-2.5.0-cdh5.3.6/data/tmp</value>
       </property>
      hdfs-site.xml
      <property>
     	 <name>dfs.replication</name>
     	 <value>1</value>
      </property>
      <property>
      	  <name>dfs.permissions.enabled</name>
     	  <value>false</value>
      </property>
      mapred-site.xml
      <property>
         <name>mapreduce.framework.name</name>
         <value>yarn</value>
      </property>
      yarn-site.xml
      <property>
           <name>yarn.nodemanager.aux-services</name>
           <value>mapreduce_shuffle</value>
      </property>
      slaves 
        hive1
   4. 替换cdh lib/native本地库
   5. 格式化namenode
   6. 启动服务进程
   7. 安装hive并解压缩
   8. 修改配置文件
      hive-env.sh
      hive-site.xml
   9. 上传mysql驱动 hive_home/lib
   10. hdfs /tmp
            /user/hive/warehouse
   

2. 安装Sqoop

   1. 解压缩Sqoop
   2. 修改配置 sqoop_home/conf
      修改conf/sqoop-env.sh
      export HADOOP_COMMON_HOME=/opt/install/hadoop-2.5.0-cdh5.3.6
      export HADOOP_MAPRED_HOME=/opt/install/hadoop-2.5.0-cdh5.3.6
      export HIVE_HOME=/opt/install/hive-0.13.1-cdh5.3.6
   3. mysql-connect.jar copy sqoop_home/lib
   4. 测试sqoop是否正常使用
      bin/sqoop list-databases -connect jdbc:mysql://hive1:3306 -username root -password 123456
   

   1. sqoop命令的书写规范

      1. 一行写 
         bin/sqoop list-databases -connect jdbc:mysql://hive1:3306 -username root -password 123456   
      2. 每一个关键字一行
         bin/sqoop list-databases \
         --connect \
         jdbc:mysql://hadoop5:3306 \
         --username root \
         --password 123456
      

   2. mysql的准备工作

      #mysql 创建数据库 创建表
      create database sqoop;
      
      use sqoop;
      
      create table mysql_user(
      id int primary key,
      name varchar(12)
      );
      insert into mysql_user values (1,'suns1');
      insert into mysql_user values (2,'suns2');
      insert into mysql_user values (3,'suns3');
      insert into mysql_user values (4,'suns4');
      insert into mysql_user values (5,'suns5');
      
      insert into mysql_user values (6,'suns6');
      insert into mysql_user values (7,'suns7');
      insert into mysql_user values (8,'suns8');
      
      
      
      

   3. Sqoop的Import（导入）

      bin/sqoop import \
      --connect \
      jdbc:mysql://hadoop5:3306/sqoop \
      --username root \
      --password 123456 \
      --table mysql_user
      
      # Sqoop 进行的数据导入和导出，本质上也是运行的MapReduce 
      # 如果不指定hdfs的导入路径，那么sqoop生成默认值 /user/root/${table_name}
      # mapReduce的目标是数据库，所以Map的数量，由数据库的行数决定
      # 导出文件的列分割符，是，
      

      # 指定sqoop导入位置
      bin/sqoop import \
      --connect \
      jdbc:mysql://hadoop5:3306/sqoop \
      --username root \
      --password 123456 \
      --table mysql_user \
      --target-dir /sqoop
      

      # 删除已存在的目标目录
      bin/sqoop import \
      --connect \
      jdbc:mysql://hadoop5:3306/sqoop \
      --username root \
      --password 123456 \
      --table mysql_user \
      --target-dir /sqoop \
      --delete-target-dir
      

      # 设置map的个数
      bin/sqoop import \
      --connect \
      jdbc:mysql://hive1:3306/sqoop \
      --username root \
      --password 123456 \
      --table mysql_user \
      --target-dir /sqoop \
      --delete-target-dir \
      --num-mappers 1
      

      # 设置列分隔符
      bin/sqoop import \
      --connect \
      jdbc:mysql://hive1:3306/sqoop \
      --username root \
      --password 123456 \
      --table mysql_user \
      --target-dir /sqoop \
      --delete-target-dir \
      --num-mappers 1 \
      --fields-terminated-by '\t'
      
      

      # 快速导入
        使用mysql 导出工具  dump 
      bin/sqoop import \
      --connect \
      jdbc:mysql://hadoop5:3306/sqoop \
      --username root \
      --password 123456 \
      --table mysql_user \
      --target-dir /sqoop \
      --delete-target-dir \
      --num-mappers 1 \
      --fields-terminated-by '\t' \
      --direct
      
      #细节： sqoop 需要和 mysql 安装在同一个节点
             如果没有安装在同一个节点，需要把mysql/bin下的内容 复制到sqoop机器
      

      # 增量导入
      --check-column <column>        Source column to check for incremental    id
      --last-value <value>           Last imported value in the incremental    5
      --incremental <import-type>    Define an incremental import of type     append
                                     'append' or 'lastmodified'
                                     
      --delete-target-dir \ 不需要加
      
      bin/sqoop import \
      --connect \
      jdbc:mysql://hadoop5:3306/sqoop \
      --username root \
      --password 123456 \
      --table mysql_user \
      --target-dir /sqoop \
      --num-mappers 1 \
      --fields-terminated-by '\t' \
      --direct \
      --check-column id \
      --last-value 5 \
      --incremental append
        
      

      # hive表的导入
      #--hive-import \
      #--hive-database baizhi125 \
      #--hive-table t_user \
      
      bin/sqoop import \
      --connect \
      jdbc:mysql://hive1:3306/sqoop \
      --username root \
      --password 123456 \
      --table mysql_user \
      --delete-target-dir \
      --hive-import \
      --hive-database baizhi129 \
      --hive-table t_user \
      --num-mappers 1 \
      --fields-terminated-by '\t'
      

      4.Sqoop的Export(HDFS/Hive----->MySQL)

      bin/sqoop export \
      --connect \
      jdbc:mysql://hadoop5:3306/sqoop \
      --username root \
      --password 123456 \
      --table to_mysql \
      --export-dir /sqoop \
      --num-mappers 1 \
      --fields-terminated-by '\t'
      

      15.1内存溢出和内存泄漏

jdbc:mysql://hive1:3306/sqoop
–username root
–password 123456
–table mysql_user
–delete-target-dir
–hive-import
–hive-database baizhi129
–hive-table t_user
–num-mappers 1
–fields-terminated-by ‘\t’
```

  4.Sqoop的Export(HDFS/Hive----->MySQL)

  ```
  bin/sqoop export \
  --connect \
  jdbc:mysql://hadoop5:3306/sqoop \
  --username root \
  --password 123456 \
  --table to_mysql \
  --export-dir /sqoop \
  --num-mappers 1 \
  --fields-terminated-by '\t'
  ```

  ##### 15.1内存溢出和内存泄漏

  [外链图片转存中...(img-1fTeS8BH-1578316523066)]

在分内存的时候会有间隙，间隙比较小，在生成新的内存分配的时候，这个间隙用不上,就成为了内存碎片。尤其是redis,redis是纯用内存的，会摒弃它自带的内存管理器。