Hive介绍

概念：基于hadoop HDFS之上的数据仓库。

         数据仓库是一个面向主题，集成的，不可更新的的，随时间不变化的数据集合，它用于支持企业或者组织的决策分析处理。  定义了类似的SQL查询语言，成为HQL。

           Hive允许熟悉MapReduce开发者的开发自定义的mapper和reducer来处理内键的mappper和reducer无法完成的复杂工作。

          Hive是SQL解析引擎，它将SQL语句转移成M/RJob然后在Hadoop执行

          Hive的表其实就是HDFS的目录/文件

结构和建立过程：

      

OLTP应用与OLAP应用：

     OLTP（On-Line Transaction Processing）应用：转账

     OLAP（Online Analytical Processing）应用：商品推荐系统

数据仓库中数据模型：

   星型模型：

   雪花模型

Hive元数据：

    Hive将元数据存储在数据库中（metastore），支持mysql，derby等数据库，默认为derby

    Hive中的元数据包括表的名字·，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录

HQL的执行过程：

     解释器，编译器，优化器完成HQL查询语法从词法分析，语法分析，编译，优化以及查询计划的生成。生成计划存储在HDFS中，并在随后有MapReduce调用执行。

     HQL（select）--》解释器（词法分析）--》编译器（生成HQL执行计划）--》优化器（生成最佳的执行计划）-》执行

Hive体系结构：

Hive的安装模式：

     嵌入模式：

          元数据被存储在Hive自带的Derby数据库

          只允许创建一个连接

          多用于Demo

     本地模式：

          元数据被存储在Mysql数据库

          mysql和HIve运行在同一台物理机器上

          开发和测试

          允许多个连接

      远程模式：

          元数据被存储在Mysql数据库

          mysql和HIve运行不在同一台物理机器上

          开发和测试

           允许多个连接

HIve安装：

       嵌入模式：直接解压，运行hive命令，在那个目录运行hive，就会在那个目录下生成元数据目录

       远程模式和本地模式：1.导入mysql.jar到lib目录下

                                       2.hive-site.xml:

<configuration>

   <property>
    <name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://10.203.87.35:3306/hive?createDatabaseIfNotExist=true</value>
   </property>
   <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
   </property>
   <property>
    <name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
   </property>
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
   </property>
</configuration>

 Hive命令：

         启动：/bin/hive/

                  hive  --service cli

       清屏：Ctrl +L或者！clean

       查看数据仓库中的表：show tables

       查看数据仓库中的函数：show functions；

       查看表结构：desc 表名

       查看HDFS上的文件：dfs -ls 目录

      执行操作系统的命令：！命令

      执行HQL语句：select ** from ***

      执行SQL的脚本：source sql文件

      hive -S

      hive -e

Web界面访问：

    jar cvfM0 hive -hwi-1.1.0.0.war -C web/

    hive --service hwi

    访问：ip：9999/hwi/

远程服务：

       端口号：10000

       启动方式：hive --service  hiveserver

       以JDBC或者ODBC的程序操作HIve中的表，必须选用远程服务启动

Hive的数据类型：

    基本数据类型：

        tinyint/smallint/int/bigint:整数类型

        float/double:浮点型类型

         boolean：布尔类型

         string：字符串类型

    复杂数据类型： 

         Array：数组类型

             create table student(
                 sid int,
                 sname string,
                 grade array<float>);

         Map集合类型：包含key--》value键值对，可以通过key来访问元素，key值相同，value会被覆盖。

              create table student1
                    (sid int,
                     sname string,
                     grade map<string,float>);

         Struct：结构类型。可以包含不同类型的元素，这些元素可以通过“点语法”的方式来得到所需的元素

               create table student2(
               sid int,
               info struct<name:string,age:int,sex:string>);

    时间类型：

         Date：从Hive0.12.0开始

         Timestamp：从Hive0.8.0开始支持

Hive的数据存储：

         基于HDFS

          没有专门的数据存储格式（txt，csv）默认情况下，采用制表符为列和列的分隔符。

         存储结构主要包括：数据库，文件，表，视图

         可以直接加载文本文件（.txt文件等，.csv文件）

         创建表时，指定Hive数据的列分隔符与行分隔符

        表：

                Table 内部表 

                          与数据库中的Table在概念上是相似的

                          每一个Table在Hive中都有一个相应的目录存储数据

                           所有的Table数据（不包括 External table）都保存在这个目录中

                           删除表时，元数据和数据都会被删除

                           create table t1(
                                      id int,
                                      name string);

                                      location '路径';

                            alter table t1 add columns (english int);

                             create table t3(
                                           id int,
                                           name string)
                                           row format delimited fields terminated by ',';

                Partition 分区表

                            Partition对应于数据库的Partition列的密集索引

                            在Hive中，表中的一个Partition对应于表下的一个目录，所有的Partition的数据都存储在对应的目录下

                                   create table partition_table(
                                    sid int,
                                    sname string)
                                    partitioned by (gender string)
                                    row format delimited fields terminated by ',';  

                External Table  外部表

                           指向已经在HDFS中存在的数据，可以创建Partition

                           它和内部表在元数据的组织上是相同的,而实际数据的存储则有较大差异

                           外部表只有一个过程，加载数据和创建表同时完成，并不会移动到数据仓库目录中，只是与外部数据建立一个链接。当删除一个外部表时，仅删除该链接。

                              create external table external_student(
                                       sid int,sname string,age int)
                                       row format delimited fields terminated by ","
                                       location "/input";

                Bucket  Table 桶表

                        是对数据进行哈希取值，然后放到不同文件中存储。提高查询速度

                        create table bucket_table(

                                             sid int,

                                             sanme  string,

                                             age int)

                                   clustered by(sanme) into 5 buckets;

       视图：

                 是一种虚表，是一个逻辑概念，可以跨越多张表

                 建立在已有表的基础上，视图赖以建立的这些表称为基表

                 视图可以简化复杂查询

                        create view empinfo

                        as

                        select e.empno,e.ename,e.sal,e.sal*12 annlsal,d.dname

                        from emp e,dept d

                        where e.deptno=d.deptno;

                oracle和mysql是支持物化视图，hive不支持

使用load语句进行数据导入：

      语法：

                load data 【local】 inpath 'filepath' [overwrite]

                into table tablename [partition (partcoll=val1,partcoll=val2....) ]

      解释：local：是否从本地导入，否则从HDFS系统上导入。

                inpath：导入路径

                overwrite：是否覆盖

                partition：导入那个分区

使用Sqoop进行数据导入：

        导出oracle数据：

        上传oracle驱动到sqoop目录的lib下

         使用Sqoop导入Oracle数据到HDFS
sqoop import --connect jdbc:oracle:thin:@10.204.98.240:1521:orcl --username scott --password tiger --table emp --columns 'empno,ename,job,sal,deptno' -m 1 --target-dir '/test'
         使用Sqoop导入Oracle数据到Hive中
sqoop import --hive-import --connect jdbc:oracle:thin:@10.204.98.240:1521:orcl --username scott --password tiger --table EMP1 -m 1 --columns 'EMPNO,ENAME'
          使用Sqoop导入Oracle数据到Hive中，并且指定表名
sqoop import --hive-import --connect jdbc:oracle:thin:@10.204.98.240:1521:orcl --username scott --password tiger --table EMP1 -m 1 --columns 'EMPNO,ENAME' --hive-table emp --hive-database default
           使用Sqoop导入Oracle数据到Hive中，并且使用where条件
sqoop import --hive-import --connect jdbc:oracle:thin:@10.204.98.240:1521:orcl --username scott --password tiger --table EMP --columns 'EMPNO,ENAME' --hive-table emp --where 'DEPTNO=10'
           使用Sqoop导入Oracle数据到Hive中，并且使用查询语句 
sqoop import --hive-import --connect jdbc:oracle:thin:@10.204.98.240:1521:orcl --username scott -password tiger -m 1 --query 'select * from EMP WHERE SAL<2000 AND $CONDITIONS' --target-dir '/user/huawei/' --hive-table emp
           使用Sqoop将HIve的数据到出到Oracle中（首先在oracle中创建表，类型和数量对应）
sqoop export --connect jdbc:oracle:thin:@10.204.98.240:1521:orcl --username scott --password tiger -m 1 --table STUDENT --input-fields-terminated-by '\001' --export-dir '/user/hive/warehouse/student'
           使用Sqoop导入mysql数据到HDFS
sqoop import --connect jdbc:mysql://10.204.98.240:3306/shopping --table items --username root -P --hive-import  -- --default-character-set=utf-8

Hive分析流程图：

Hive数据查询：

    select * from emp  where comm is null

    select distinct id,name from student;(distinct 作用于id和name，综合起来没有重复的行）

    select * from emp where ename="KING";（在hive中严格区分字符串大小写）

    SELECT * FROM EMP WHERE ENAME LIKE "%\\_%";(模糊查询）

    select empno,ename,sal,sal*12 from emp order by sal*12;（order by 后面可以接：列名，表达式，别名，序号）

    select empno,ename,sal,sal*12 annsal from emp order by annsal;（别名排序）

    通过序号排序需要输入命令：set hive.groupby.orderby.position.alias=true;    （序号排序）     

    然后select empno,ename,sal,sal*12 annsal from emp order by 4

    升序时：null比0靠前；降序时：null比0靠后

简单查询Fetch Task查询

    简单查询：没有函数，没有排序

    hive0.10.0版本开始支持

    从HDFS文件查询数据

    配置方式：

          

Hive的函数：

    内置函数：

          数学函数：round（x，y）四舍五入    解释：x：四舍五入的对象   y：保留几位小数，

                          select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2);

                          ceil（x）：向上取整

                          floor：向下取整

          字符函数：lower

                          upper

                          length：字符串

                          concat：连接字符串

                          substr（a,b）:从a中，第b位开始取，取右边所有的字符

                          substr（a,b,c）:从a中，第b位开始取，取C个字符

                          trim：去掉前后字符串

                          lpad（a,b,c）：左填充  解释：a：要填充的字符串  b:填充多少位 c:用什么填充

                          rpad（a,b,c）：右填充

            收集函数：size（map(key,value),<key,value>)）

                             select  size(map(1,"tom",2,"wxh"));

            转换函数：cast

                           select cast(1 as float);

            日期函数：to_date:  取出字符串中日期的部分 

                                       select to_date("2015-04-23 11:23:11");

                            year：取出日期中的年

                                       select year("2015-04-23 11:23:11");

                            month：取出日期中的月

                                        select month("2015-04-23 11:23:11");

                            day：取出日期中的日 

                                        select day("2015-04-23 11:23:11");

                            weeekofyear：返回一个日期在一年中是第几个星期

                                        select weekofyear("2018-04-9 11:23:11");

                            datediff：两个日期相减返回相差天数

                                        select datediff("2017-05-24","2016-05-24")

                            date_add：在一个日期上加上多少天

                                        select date_add("2015-2-02",2);

                            date_sub：在一个日期中减去多少天

                                         select date_sub("2015-2-02",2);

              条件函数：

                             coalesce：从左到右返回第一个不为null的值

                                  select comm,sal,empno,coalesce(comm,sal,empno) from emp;

                             case....when...:条件表达式

                                   CASE a  WHEN b THEN c 【WHEN d THEN e】* 【ELSE f】 END

                                 select empno,ename,job,sal case job when "CLERK" then sal+100 when "SALESMAN" then sal+200 else sal +300 End from emp;

    聚合函数： 

               count 

               sum

               min

               max

               avg

               select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;

    表生成函数：

            explode：把map集合或者数据中每一个元素单独生成一个行

     

Hive表连接：

          等值连接：

                 select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;

         不等值连接：

                select e.empno,e.ename,e.sal,s.grade
                          from emp e,salgrade sal

                where e.sal between s.losal and s.hisal

         外连接：

                select d.deptno,d.dname,count(e.empno)

                from emp e,dept d

                where e.deptno=d.deptno

                group by d.deptno,d.dname

                (在select语句中所有没有包含在聚合函数的字段，都要用到groupby）

                通过外连接可以将对于连接条件不成立的记录仍然包含在最后结果中。

                      左外连接

                      右外连接

                          select d.deptno,d.dname,count(e.empno)
                                from emp e right outer join dept d
                                on (e.deptno=d.deptno)

                                group by d.deptno,d.dname;

        自连接：通过表的别名将同一张表视为多张表

               select e.name,b.ename
                  from emp e,emp b

                  where e.mgr=b.empno;

Hive子查询：

     注意问题：

            语法中的括号

            合理的书写风格

            Hive中只支持Where和From子句中的子查询

            主查询和子查询可以不是一张表

            子查询中的空值问题

                 如果子查询中含有空值，不能使用not in 可以使用 in（在hive-1.1.0中可以使用）

                 select e.name from emp e where e.empno not in (select mgr from emp);

Hive自定义函数：

    可以直接应用于select语句，对查询结果做格式化处理后，在输出内容。

    实现方法：自定义UDF需要继承org.apache.hadoop.hive.ql.UDF

                    需要实现evalute函数，evalute函数支持重载

                    把程序打包打包到目标机器上去

                   进入hive客户端，添加jar包：

                   hive>add jar /root/trainig/udfjar/udf_test.jar

                   创建临时函数：

                  create temporary function 《函数名》

                  AS “Java类全名”

                 select  《函数名》 from table；

                 销毁临时函数：drop temporyary function <函数名>