Hive的核心入门到上手

hive
------------------
   在hadoop处理结构化数据的数据仓库。
   不是:    关系数据库
         不是OLTP
         实时查询和行级更新。

hive特点
-----------------
   hive存储数据结构(schema)在数据库中,处理的数据进入hdfs.
   OLAP
   HQL / HiveQL


hive安装
-----------------
   1.下载hive2.1-tar.gz
   2.tar开
      $>tar -xzvf hive-2.1.0.tar.gz -C /soft //tar开
      $>cd /soft/hive-2.1.0              //
      $>ln -s hive-2.1.0 hive                //符号连接

   3.配置环境变量
      [/etc/profile]
      HIVE_HOME=/soft/hive
      PATH=...:$HIVE_HOME/bin

   4.验证hive安装成功
      $>hive --v

   5.配置hive,使用win7的mysql存放hive的元数据.
      a)复制mysql驱动程序到hive的lib目录下。
         ...
      b)配置hive-site.xml
         复制hive-default.xml.template为hive-site.xml
         修改连接信息为mysql链接地址，将${system:...字样替换成具体路径。
         [hive/conf/hive-site.xml]
         <property>
            <name>javax.jdo.option.ConnectionPassword</name>
            <value>root</value>
            <description>password to use against metastore database</description>
         </property>
         <property>
            <name>javax.jdo.option.ConnectionUserName</name>
            <value>root</value>
            <description>Username to use against metastore database</description>
         </property>
         <property>
            <name>javax.jdo.option.ConnectionURL</name>
            <value>jdbc:mysql://192.168.231.1:3306/hive2</value>
         </property>
         <property>
            <name>javax.jdo.option.ConnectionDriverName</name>
            <value>com.mysql.jdbc.Driver</value>
            <description>Driver class name for a JDBC metastore</description>
         </property>

      6)在msyql中创建存放hive信息的数据库
         mysql>create database hive2 ;

      6)初始化hive的元数据(表结构)到mysql中。
         $>cd /soft/hive/bin
         $>schematool -dbType mysql -initSchema



hive命令行操作
------------------------
   1.创建hive的数据库

      $hive>hive --version            //
      $hive>hive --help                //

      $hive>create database mydb2 ;             //
      $hive>show databases ;
      $hive>use mydb2 ;
      $hive>create table mydb2.t(id int,name string,age int);
      $hive>drop table t ;
      $hive>drop table mydb2.t ;
      $hive>select * from mydb2.t ;     //查看指定库的表
      $hive>exit ;                  //退出

      $>hive                      //hive --service cli
      $>hive                      //hive --service cli


通过远程jdbc方式连接到hive数据仓库
--------------------------------
   1.启动hiveserver2服务器，监听端口10000
      $>hive --service hiveserver2 &

   2.通过beeline命令行连接到hiveserver2
      $>beeline                                //进入beeline命令行(于hive --service beeline)
      $beeline>!help                            //查看帮助
      $beeline>!quit                            //退出
      $beeline>!connect jdbc:hive2://localhost:10000/mydb2//连接到hibve数据
      $beeline>show databases ;
      $beeline>use mydb2 ;
      $beeline>show tables;                       //显式表


使用Hive-jdbc驱动程序采用jdbc方式访问远程数据仓库
----------------------------------------------------
   1.创建java模块
   2.引入maven
   3.添加hive-jdbc依赖
      <?xml version="1.0" encoding="UTF-8"?>
      <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
         <modelVersion>4.0.0</modelVersion>

         <groupId>com.it18zhang</groupId>
         <artifactId>HiveDemo</artifactId>
         <version>1.0-SNAPSHOT</version>

         <dependencies>
            <dependency>
               <groupId>org.apache.hive</groupId>
               <artifactId>hive-jdbc</artifactId>
               <version>2.1.0</version>
            </dependency>
         </dependencies>
      </project>

   4.App
      package com.it18zhang.hivedemo;

      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.ResultSet;
      import java.sql.Statement;

      /**
       * 使用jdbc方式连接到hive数据仓库，数据仓库需要开启hiveserver2服务。
       */
      public class App {
         public static void main(String[] args) throws  Exception {
            Class.forName("org.apache.hive.jdbc.HiveDriver");
            Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.231.201:10000/mydb2");
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select id , name ,age from t");
            while(rs.next()){
               System.out.println(rs.getInt(1) + "," + rs.getString(2)) ;
            }
            rs.close();
            st.close();
            conn.close();
         }
      }

hive中表
-------------------
   1.managed table
      托管表。
      删除表时，数据也删除了。

   2.external table
      外部表。
      删除表时，数据不删。

hive命令
----------------
   //创建表,external 外部表
   $hive>CREATE external TABLE IF NOT EXISTS t2(id int,name string,age int)
   COMMENT 'xx' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE ;

   //查看表数据
   $hive>desc t2 ;
   $hive>desc formatted t2 ;

   //加载数据到hive表
   $hive>load data local inpath '/home/centos/customers.txt' into table t2 ;  //local上传文件
   $hive>load data inpath '/user/centos/customers.txt' [overwrite] into table t2 ;    //移动文件

   //复制表
   mysql>create table tt as select * from users ;    //携带数据和表结构
   mysql>create table tt like users ;       //不带数据，只有表结构

   hive>create table tt as select * from users ;
   hive>create table tt like users ;


   //count()查询要转成mr
   $hive>select count(*) from t2 ;
   $hive>select id,name from t2 ;


   //
   $hive>select * from t2 order by id desc ;           //MR

   //启用/禁用表
   $hive>ALTER TABLE t2 ENABLE NO_DROP;   //不允许删除
   $hive>ALTER TABLE t2 DISABLE NO_DROP;  //允许删除


   //分区表,优化手段之一，从目录的层面控制搜索数据的范围。
   //创建分区表.
   $hive>CREATE TABLE t3(id int,name string,age int) PARTITIONED BY (Year INT, Month INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

   //显式表的分区信息
   $hive>SHOW PARTITIONS t3;

   //添加分区,创建目录
   $hive>alter table t3 add partition (year=2014, month=12);

   //删除分区
   hive>ALTER TABLE employee_partitioned DROP IF EXISTS PARTITION (year=2014, month=11);

   //分区结构
   hive>/user/hive/warehouse/mydb2.db/t3/year=2014/month=11
   hive>/user/hive/warehouse/mydb2.db/t3/year=2014/month=12


   //加载数据到分区表
   hive>load data local inpath '/home/centos/customers.txt' into table t3 partition(year=2014,month=11);

   //创建桶表
   $hive>CREATE TABLE t4(id int,name string,age int) CLUSTERED BY (id) INTO 3 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

   //加载数据不会进行分桶操作
   $hive>load data local inpath '/home/centos/customers.txt' into table t4 ;

   //查询t3表数据插入到t4中。
   $hive>insert into t4 select id,name,age from t3 ;

   //桶表的数量如何设置?
   //评估数据量，保证每个桶的数据量block的2倍大小。


   //连接查询
   $hive>CREATE TABLE customers(id int,name string,age int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
   $hive>CREATE TABLE orders(id int,orderno string,price float,cid int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

   //加载数据到表
   //内连接查询
   hive>select a.*,b.* from customers a , orders b where a.id = b.cid ;
   //左外
   hive>select a.*,b.* from customers a left outer join orders b on a.id = b.cid ;
   hive>select a.*,b.* from customers a right outer join orders b on a.id = b.cid ;
   hive>select a.*,b.* from customers a full outer join orders b on a.id = b.cid ;

   //explode,炸裂,表生成函数。
   //使用hive实现单词统计
   //1.建表
   $hive>CREATE TABLE doc(line string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
   ------------------------------------------------------------------------------------------------------------------
   hive
    ------------
       $hive>CREATE TABLE t3(id int,name string,age int) PARTITIONED BY (Year INT, Month INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

    export
    ---------
       $hive>EXPORT TABLE customers TO '/user/centos/tmp.txt';       //导出表结构+数据。

       //order全排序
       $hive>select * from orders order by id asc ;

       //sort,map端排序,本地有序。
       $hive>select * from orders sort by id asc ;

       //DISTRIBUTE BY类似于mysql的group by,进行分区操作。
       //select cid , ... from orders distribute by cid sort by name ;          //注意顺序.
       $hive>select id,orderno,cid from orders distribute by cid sort by cid desc ;

       //cluster by ===>  distribute by cid sort by cid

    函数
    ----------------
       mysql>select concat('tom',1000) ;
       $hive>select current_database(),current_user() ;
       $hive>tab                       //查看帮助



    设置作业参数
    ---------------
        $hive>set hive.exec.reducers.bytes.per.reducer=xxx          //设置reducetask的字节数。
        $hive>set hive.exec.reducers.max=0                      //设置reduce task的最大任务数
        $hive>set mapreduce.job.reduces=0                   //设置reducetask个数。


    动态分区
    ---------------
       动态分区模式:strict-严格模式，插入时至少指定一个静态分区，nonstrict-非严格模式-可以不指定静态分区。
       set hive.exec.dynamic.partition.mode=nonstrict       //设置非严格模式
       $hive>INSERT OVERWRITE TABLE employees PARTITION (country, state) SELECT ..., se.cnty, se.st FROM staged_employees se WHERE se.cnty = 'US';

    159

    hive事务处理在>0.13.0之后支持行级事务。
    ---------------------------------------
       1.所有事务自动提交。
       2.只支持orc格式。
       3.使用bucket表。
       4.配置hive参数，使其支持事务。
    $hive>SET hive.support.concurrency = true;
    $hive>SET hive.enforce.bucketing = true;
    $hive>SET hive.exec.dynamic.partition.mode = nonstrict;
    $hive>SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
    $hive>SET hive.compactor.initiator.on = true;
    $hive>SET hive.compactor.worker.threads = 1;

       5.使用事务性操作
          $>CREATE TABLE tx(id int,name string,age int) CLUSTERED BY (id) INTO 3 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as orc TBLPROPERTIES ('transactional'='true');


    聚合处理
    ---------------
       $hive>select cid,count(*) c ,max(price) from orders group by cid having c > 1 ;

    wordcount
    ----------------
       $hive>select t.word,count(*) c from ((select explode(split(line, ' ')) as word from doc) as t) group by t.word order by c desc limit 2 ;

    创建新表:stats(word string,c int) ;
       将查询结果插入到指定表中。


    view:视图,虚表
    -----------
       //创建视图
       $hive>create view v1 as select a.id aid,a.name ,b.id bid , b.order from customers a left outer join default.tt b on a.id = b.cid ;

       //查看视图
       $hive>show tables ;
       $hive>select * from v1 ;

    Map端连接
    -------------------
       $hive>set hive.auto.convert.join=true        //设置自动转换连接,默认开启了。
       //使用mapjoin连接暗示实现mapjoin
       $hive>select /*+ mapjoin(customers) */ a.*,b.* from customers a left outer join orders b on a.id = b.cid ;

    调优
    --------------------
       1.explain
          使用explain查看查询计划
          hive>explain [extended] select count(*) from customers ;
          hive>explain select t.name , count(*) from (select a.name ,b.id,b.orderno from customers a ,orders b where a.id = b.cid) t group by t.name ;

          //设置limit优化测，避免全部查询.
          hive>set hive.limit.optimize.enable=true

          //本地模式
          $hive>set mapred.job.tracker=local;          //
          $hive>set hive.exec.mode.local.auto=true   //自动本地模式,测试


          //并行执行,同时执行不存在依赖关系的阶段。??
          $hive>set hive.exec.parallel=true        //

          //严格模式,
          $hive>set hive.mapred.mode=strict        //1.分区表必须指定分区进行查询
                                           //2.order by时必须使用limit子句。
                                           //3.不允许笛卡尔积.


          //设置MR的数量
          hive> set hive.exec.reducers.bytes.per.reducer=750000000;  //设置reduce处理的字节数。

          //JVM重用
          $hive>set mapreduce.job.jvm.numtasks=1    //-1没有限制，使用大量小文件。


          //UDF
          //User define function,用户自定义函数
          //current_database(),current_user();

          //显式所有函数
          $hive>show functions;
          $hive>select array(1,2,3) ;

          //显式指定函数帮助
          $hive>desc function current_database();

          //表生成函数,多行函数。
          $hive>explode(str,exp);          //按照exp切割str.
          -----------------------
   前置条件：
   hive启动
       root用户登录 密码123456
       启动mysql：service mysqld restart
       使用hadoop用户登录，启动metastore：hive --service metastore &
   数据文件准备
       将文档文件夹中的classes.txt和students.txt移动到linux机器和hdfs文件系统上。
       命令：hdfs dfs -put ./13 /beifeng/
   创建hive相关表准备：
   create database beifeng13;
   create table students(studentId int comment 'this is student id, is not null', classId int comment 'this is class id, can set to null', studentName string comment 'this is student name') row format delimited fields terminated by ',';
   create table classes(classId int comment 'this is class id, is not null', className string comment 'this is class name') row format delimited fields terminated by ',';

   一、导入数据
       1. 分别导入local和hdfs的数据
          a. 分别从linux机器上导入数据
             load data local inpath '/home/hadoop/datas/13/classes.txt' into table classes;
             load data local inpath '/home/hadoop/datas/13/students.txt' into table students;
             load data local inpath '/home/hadoop/datas/13/classes.txt' overwrite into table classes;
          b. 从hdfs上导入数据
             load data inpath '/beifeng/13/students.txt' into table students;
             dfs -put /home/hadoop/datas/13/students.txt /beifeng/13/
             load data inpath '/beifeng/13/students.txt' overwrite into table students;
       2. 导入其他表的数据(多表插入)
          将学生表的学生id和classid分别导出到不同表中，
          create table test1(id int);
          create table test2(id int);
          from students insert into table test1 select studentid  insert overwrite table test2 select distinct classid where classid is not null;
   二、select语法介绍
       from语法
          1. 正常from:
             select * from students;
          2. from语句提前:
              from students select *;
       cte语法：
          1. 获取班级号为1的学生信息:
             with tmp as (select studentid as sid,classid as cid,studentname as name from students where classid=1) from tmp select *;
          2. 获取总学生数、已经分配班级的学生数、未分配班级的学生数(作业1)。
             分析；
                总学生数：studentid的总数
                分配班级的学生数：classid不为空的学生总数
                未分配的学生数: classid为空的学生数
             结果: 12 7 5
       where & group by语法实例：
          group语句只能返回对于的group列&进行聚合的value。
          1. 获取学生数大于3的班级id
             from students select classid where classid is not null group by classid having count(studentid) > 3;
       排序语法：
          1. 使用order by根据学生id倒序。
             select * from students order by studentid desc;
          2. 设置hive.mapred.mode为strict，然后在进行order by操作。
             set hive.mapred.mode=strict;
             select * from students order by studentid desc; 会出现异常
             select * from students order by studentid desc limit 5;
          3. 使用sort by根据学生id排序。
             select * from students sort by studentid desc;
          4. 设置mapreduce.job.reduces个数为两个，然后再使用sort by进行排序。
             set mapreduce.job.reduces=2;
             select * from students sort by studentid desc;
   三、join语法
       内连接语法
          1. 获取学生和班级之间完全匹配的数据。
             select students.*,classes.* from classes join students on classes.classid=students.classid;
             select students.*,classes.* from classes cross join students on classes.classid=students.classid;
       外链接语法：
          1. 获取全部学生的班级信息，如果该学生没有分配班级，那么班级信息显示为null。
             select students.*, classes.* from students left join classes on students.classid = classes.classid;
          2. 获取全部班级的学生信息，如果某个班级没有学生，那么学生信息显示为null。(作业2)
          3. 获取全部信息，如果没有匹配数据的显示null。(作业3)
       半连接：
          1. 获取学生表中班级id在班级表中的所有学生信息。
             sql: select students.* from students where classid in (select distinct classid from classes);
             原hql: select students.* from students  join classes on students.classid = classes.classid;
             新hql: select students.* from students left semi join classes on students.classid=classes.classid;
       mapjoin:
          select /*+ mapjoin(classes) */ * from students join classes on students.classid=classes.classid;
   四、子查询
       1. 获取学生数最多的班级，学生的个数。
          第一步：获取每个班级的学生总数
          第二步：选择学生数最多的班级学生数
          from (select count(studentid) as sc from students where classid is not null group by classid) as tmp select max(sc);
       2. 获取学生数最多的班级信息。(作业4)
          第一步：获取每个班级的学生总数
          第二步：选择学生数最多的班级学生数
          第三步：根据最多的学生数和第一步获取的表数据进行比较，获取班级信息。

   五、导出数据
       1. 导出表关联后的班级名称和学生名称(loca&hdfs)。（导出全部不为空的信息）
          班级1，学生1
          from (select classes.classname as col1, students.studentname as col2 from classes join students on classes.classid = students.classid) as tmp insert overwrite local directory  '/home/hadoop/result/13/01' select col1,col2 insert overwrite directory '/beifeng/result/13/01/' select col1,col2 ;

          格式化：
          from (select classes.classname as col1, students.studentname as col2 from classes join students on classes.classid = students.classid) as tmp insert overwrite local directory  '/home/hadoop/result/13/01' row format delimited fields terminated by ',' select col1,col2  ;
       2. 同时分别将已经分配班级的学生和未分配班级的学生导出到不同的文件夹中。(作业5)
   六、其他命令
       1. 在students和classes表上创建一个视图，视图包含两列分别是：班级名称，学生名称
          create view viewname as select classes.classname as cname, students.studentname as sname from classes join students on classes.classid = students.classid
       2. 在linux系统中通过命令hive -f/-e将所有学生信息保存到一个文件中。
          新建一个文件，文件内容为:select * from students
          执行：hive --database beifeng13 -f test.sql >> result.txt
   ----------------------------------------
   高级操作   自定义函数
   hive自定义udf依赖的包：hadoop基础包&hive-exec
   <dependency>
     <groupId>org.apache.hive</groupId>
     <artifactId>hive-exec</artifactId>
     <version>0.13.1</version>
   </dependency>

   创建Function命令: CREATE FUNCTION [db_name.]function_name AS class_name [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ]; file_uri可以为hdfs上文件路径
   删除: drop function function_name;
   显示: show functions ['regex']

   使用database-beifeng13

   一、自定义UDF(一个输入，一个输出)
       1. 实现一个大小写转换的自定义函数。
          实现完成后
          1. 添加jar：add jar /home/hadoop/jobs/beifeng14-0.0.1.jar;
          2. 创建function:
             临时function： create temporary function temp_f as 'com.beifeng.hive.ql.UDFLowerOrUpperCase';
             永久function: create function lower_upper as 'com.beifeng.hive.ql.UDFLowerOrUpperCase';
          3. 使用
             select [dbname.]lower_upper(studentname) from beifeng13.students;
   二、自定义UDAF(多个输入，一个输出) 和group by一起操作
       1. UDAF介绍
          PARTIAL1：iterate&terminatePartial  map的输入阶段
          PARTIAL2: merge&terminatePartial map输出阶段
          FINAL: merge&terminate reducer输入&reducer输出阶段
          COMPLETE: iterate&terminate 没有reducer的map输出阶段
       2. UDAF实例：实现自定义sum函数
          create function self_sum as 'com.beifeng.hive.ql.UDAFSumCase';
   三、自定义UDTF(一个输入，多个输出)
       1. UDTF实例：解析爬虫数据，从数据中读取产品id、产品名称、价格。
          1. 实现udtf,并打包jar，copy到linux机器上。
          2. 创建hbase关联表
             create external table hive_data(rowkey string,content string) row format serde 'org.apache.hadoop.hive.hbase.HBaseSerDe' stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with serdeproperties('hbase.columns.mapping'=':key,f:content') tblproperties ('hbase.table.name'='data');
          3. 创建函数
             add jar /home/hadoop/jobs/beifeng14-0.0.1.jar;
             create function f1 as 'com.beifeng.hive.ql.UDTFCase';
   四、hive集成自定义函数的各种不同的方式
       1. 修改hive-site.xml
          <property>
           <name>hive.aux.jars.path</name>
           <value>file:///home/hadoop/jobs/beifeng14-0.0.1.jar</value>
     </property>
       最常用的：使用将jar上传到hdfs中，然后再进行操作的方式。
          1. 上传文件到hdfs: dfs -put /home/hadoop/jobs/beifeng14-0.0.1.jar /beifeng/beifeng14-0.0.1.jar
          2. 创建函数:create function f2 as 'com.beifeng.hive.ql.UDTFCase' using jar 'hdfs://hh:8020/beifeng/beifeng14-0.0.1.jar';