目录

一、Hive的简介

1.1 出现的原因

1.2 hive是什么

1.3 Hive的优缺点

二、hive的框架原理(重点)

二、hive的框架原理(重点)

2.1 hive的框架结构

2.2 hive的运行原理

2.3 hive和hadoop的关系

2.4 Hive与关系型数据库的比较

三、hive的安装模式

3.3 远程模式

3.3.1 概念

3.3.2 hive的服务端的配置

3.3.3 服务项的启动操作

3.3.4 客户端连接hiveserver2

3.3.5 客户端连接metastore

四、HIVE的库和表操作

4.1 库操作

4.2 表操作

4.3 数据的导入（重点）

4.4 案例演示

4.5 数据的导出

五、hive的shell技巧(熟悉)

六、表类型的详解（重点）

6.1 表的分类

6.2 表类型的转换

6.3 两种表的区别

七、Hive的基本查询语句

7.1 查询原则：

7.2 在hive(2.1.1版本)中的注意事项：

7.3 exists的用法

7.4 left semi join

7.5 hive的日志

7.6 hql的运行方式

八、HIVE的数据类型

8.1 hive的基本数据类型

8.2 复杂类型之Array类型

8.3 展开函数explode的应用

8.4 搜集函数的应用

8.5 复杂类型之Map类型

8.6 复杂类型之struct类型

8.7 综合练习

九、HIVE的函数（重点）

9.1 常用的内置函数

9.2 高级函数之窗口函数over

9.3 over()函数的其他用法

9.4 window子句的作用

9.5 排名函数

9.6 其他序列函数

9.7 自定义函数

一、Hive的简介

1.1 出现的原因

 facebook团队要管理和分析大量的结构化的日志文件，因为写mapreduce非常麻烦,所以该团队就开发出来一个框架，叫hive.

1.2 hive是什么

 hive是一款运行在hadoop上的数据仓库管理工具，可以将具有结构化的数据文件映射成一张数据表，使用类似sql的方式来对这种文件进行读、写、管理(包括元数据)等。这种类sql的语言，称之为Hive SQL,简称HQL。 实际上hive底层是mapreduce或者是spark或者是tez.
 ​
 如果是mapreduce，则是将hql翻译成对应的mapreduce程序。

1.3 Hive的优缺点

 一、优点
    1. 学习成本低
    2. 扩展性好
    3. 适合做离线分析(OLAP)
    4. 容错性好(某一个节点宕机，hive依然可以完成)
    5. 延展性好(用户可以自定义hive函数)
    6. hive的元数据统一管理。
 二、缺点：
     1. hive的表达能力有限(特别复杂的算法难以实现)
     3. hive的效率低(调优不方便，速度慢)

二、hive的框架原理(重点) 二、hive的框架原理(重点)

2.1 hive的框架结构

 1. 用户接口层:  就是供用户链接hive的接口，有三类。
             - shell命令行接口
             - jdbc/odbc接口
             - webui接口
 2. Thrift server：hive的可选组件(第三方组件)，供编程语言链接hive时使用。
 3. metaStore：元数据库
     用来存储hive的元数据的，元数据包括库名，表名，表字段，字段类型，表数据所在的目录等。
     注意：hive在工作时，会访问元数据库，可能是读取元数据，也可能是更改元数据。
 4. Driver: hive的驱动
      - 解析器： 解析hql语法
      - 编译器： 将没有问题的hql翻译成mr程序
      - 优化器： 对翻译好的mr程序进行部分优化(比如去掉不必要的列等)
      - 执行器： 提交mr程序job。

2.2 hive的运行原理

 1. 用户提交hql语句给Driver.
 2. Driver的解析器校验hql,然后传给编译器，由编译器翻译成mr执行计划。
 3. 编译器读取元数据库里的元数据
 4. 编译器获取元数据后，再次完善mr执行计划，比如设置输入路径等。
 5. 将完善后的mr执行计划交由driver。此时，整个解析和翻译过程结束。
 6. driver将执行计划交给执行引擎
 7. 执行引擎提交作业到yarn上，运行mr程序
 8. 运算结果交由执行引擎。
 9.执行引擎获取数据后，交给driver
 10.driver将结果传递给用户。

2.3 hive和hadoop的关系

 hive就像是在hadoop上的一个壳子。 hive要处理的数据存储在hdfs/hbase上。hive的计算基于mapreduce/spark/tez

2.4 Hive与关系型数据库的比较

 - mysql用自己的存储存储引擎，hive使用的hdfs来存储。
 - mysql使用自己的执行引擎，而hive使用的是mapreduce来执行。
 - mysql使用环境几乎没有限制，hive是基于hadoop的。
 - mysql的低延迟，hive是高延迟。
 - mysql的handle的数据量较小，而hive的能handle数据量较大。
 - mysql的可扩展性较低，而hive的扩展性较高。
 - mysql的数据存储格式要求严格，而hive对数据格式不做严格要求。
 - mysql可以允许局部数据插入、更新、删除等，而hive不支持局部数据的操作。

三、hive的安装模式

3.2.1

元数据库是关系型数据库mysql,hive的相关进程都在同一台机器上，与mysql在哪一台机器上无关。

注意：hive在启动客户端时，会内置的自动启动一个元数据服务项。

3.2.2安装步骤

步骤1：上传，解压，更名，配置环境变量

步骤2： 修改hive-env.sh

如果不存在,就用hive.env.sh.template复制一个

export HIVE_CONF_DIR=/usr/local/hive/conf  
export JAVA_HOME=/usr/local/jdk  export HADOOP_HOME=/usr/local/hadoop  
export HIVE_AUX_JARS_PATH=/usr/local/hive/lib

步骤3： 修改hive-site.xml

hive2.1.1中默认是没有hive-site.xml,可以把conf/hive-default.xml.template拷贝过来使用

    javax.jdo.option.ConnectionURL
    jdbc:mysql://qianfeng03:3306/hive?createDatabaseIfNotExist=true



    javax.jdo.option.ConnectionDriverName
    com.mysql.jdbc.Driver



    javax.jdo.option.ConnectionUserName
    root



    javax.jdo.option.ConnectionPassword
    @Mmforu45

    hive.metastore.warehouse.dir
    /user/hive/warehouse

    hive.exec.scratchdir
    /tmp/hive

    hive.exec.local.scratchdir
    /usr/local/hive/iotmp


    hive.downloaded.resources.dir
    /usr/local/hive/iotmp/${hive.session.id}_resources


    hive.querylog.location
    /usr/local/hive/iotmp/root


    hive.server2.logging.operation.log.location
    /usr/local/hive/iotmp/root/operation_logs



    hive.cli.print.current.db
    true



    hive.exec.mode.local.auto
    true

 

步骤4： 导入mysql的驱动包到hive的lib目录下

mysql-connector-XXX.jar

步骤5：初始化元数据库

schematool -initSchema -dbType mysql

 hive

 

3.3 远程模式

3.3.1 概念

 hive的服务项进程必须单独启动，服务项有两个，一个是hiveserver2,一个是metastore。 启动哪一个都可以，注意启动hiveserver2时，其本质还是在内部启动了metastore

3.3.2 hive的服务端的配置



    javax.jdo.option.ConnectionURL
    jdbc:mysql://qianfeng03:3306/hive?createDatabaseIfNotExist=true



    javax.jdo.option.ConnectionDriverName
    com.mysql.jdbc.Driver



    javax.jdo.option.ConnectionUserName
    root



    javax.jdo.option.ConnectionPassword
    @Mmforu45

    hive.metastore.warehouse.dir
    /user/hive/warehouse

    hive.exec.scratchdir
    /tmp/hive

    hive.exec.local.scratchdir
    /usr/local/hive/iotmp


    hive.downloaded.resources.dir
    /usr/local/hive/iotmp/${hive.session.id}_resources


    hive.querylog.location
    /usr/local/hive/iotmp/root


    hive.server2.logging.operation.log.location
    /usr/local/hive/iotmp/root/operation_logs



    hive.cli.print.current.db
    true



    hive.exec.mode.local.auto
    true

注意：如果使用远程模式连接服务项。那么必须修改hadoop的core-site.xml文件，追加以下两个属性

    hadoop.proxyuser.root.hosts
    *


    hadoop.proxyuser.root.groups
    *
 

要scp到其他节点上，还要重启HDFS，使配置生效。

3.3.3 服务项的启动操作

启动hiveserver2:
        方法1：
            直接调用hiveserver2。会进入监听状态不退出。
        方法2：
            hive --service hiveserver2 & 进入后台启动
        方法3：
               hive --service hiveserver2 2>&1 >/dev/null &; #信息送入黑洞。

启动metastore：
        方法1：
            - hive --service metastore &
        方法2：
            - hive --service metastore 2>&1 >/dev/null &; #信息送入黑洞。

3.3.4 客户端连接hiveserver2

如果启动的是hiveserver2服务项，必须使用beeline工具连接。



方式1：
       step1. beeline 回车
       step2. !connect jdbc:hive2://ip:10000 回车
       step3. 输入用户名 回车
       step4. 输入密码 回车
方法2(直连)：
    1. beeline -u jdbc:hive2://qianfeng01:10000 -n root     #我一直使用！

解析:
    hive2，是hive的协议名称
    ip:  hiveserver2服务所在的主机IP。
    10000，是hiveserver2的端口号

 

3.3.5 客户端连接metastore

想要连接metastore服务的客户端必须配置如下属性和属性值

 
     hive.metastore.uris 
     thrift://ip:9083
 
 ​
 解析：thrift:是协议名称
      ip为metastore服务所在的主机ip地址
      9083是默认端口号

配置完，直接使用hive指令即可

四、HIVE的库和表操作

命名规范

 1. 名字可以由字母，数字等构成，数字不能开头
 2. 不区分大小写
 3. 不能使用关键字，不要使用特殊符号

4.1 库操作

1）库的创建语法

create database dbName; 
create database if not exists dbName;  
create database [if not exists] dbName comment '备注信息';

注意： hive的库的本质，是在hdfs上的一个目录，目录名为dbName.db

2) 库的查看语法

查看所有数据库：
show databases;
查看某一个数据库的信息
desc database dbName
desc database extended dbName;
显示创建语法
show create database dbName;

 

注意： hive的库的本质，是在hdfs上的一个目录，目录名为dbName.db

 

3) 库的切换语法

 use dbName

4）库的删除语法

 drop if database dbName;   只能删除没有表的空库
 drop  database dbName cascade;  可以强制删除有表的库

4.2 表操作

1）常用的字段类型

 int
 float
 double
 string
 varchar
 char
 date
 timestamp

2）表的创建语法（重点）

create table tableName(
fieldName1 Type,
fieldName1 Type,
....
);

create table dbName.tableName(
fieldName1 Type,
fieldName1 Type,
    .....
);

create table [if not exists] tableName(
fieldName1 Type,
fiedlName2 Type comment '备注',
.......
)
comment 'this is my table'       --表备注
row format delimited             --规定输入规则,以及切分规则
fields terminated by 't'        --列分隔符，默认值是^A
lines terminated by 'n'         --行分隔符，也是默认值
stored as textfile;              --指定表对应的文件是什么存储格式，textfile，也是默认值

注意：hive的表的本质，是在库目录下的子目录，目录名为表名。

3）表的查看语法

查看库里的所有的表  show tables;  ​
查看某一个表的结构  desc tableName;  
                   desc extended tableName;  ​  
查看建表语句  show create table tableName

4）表的结构修改语法

 

- 修改表名
    alter table oldTableName rename to newTableName;
- 修改列名、修改列类型是同一个语法
    alter table tableName change column oldColumnName newColumnName [Type|newType]
 
  注意：在修改类型时，不能将大类型转成小类型，可以使用小类型转向大类型。
- 修改列的位置:  注意，2.x版本后，必须是相同类型进行移动位置。
    alter table tableName change column colName colName colType after colName1;   
    alter table tableName change column colName colName colType first;

- 增加字段：add columns
    alter table tableName add columns (sex int,...);

- 删除字段：replace columns    #注意，2.x版本后，注意类型的问题，替换操作，其实涉及到位置的移动问题。
    alter table tableName replace columns(
    id int,
    name int,
    size int,
    pic string
    );
    注意：实际上是保留小括号内的字段。   

5）表的删除语法

 drop table  [if exists] tableName;

4.3 数据的导入（重点）

方式1）使用hdfs上的上传指令

 在知道表的位置的基础上，使用hdfs dfs -put 本地文件  表路径

方式2）使用hive命令上的load指令

 load data [local] inpath '具体路径' [overwrite] into table tableName;

注意： 实际上hive的load指令底层使用的就是hdfs的上传或移动。

方式3）基于别的表进行查询插入

 insert into tablename select ....  from tableName1 [where conditions]
 ​
 扩展内容：向多张表中插入数据的语法
     from tableName1
     insert into tableName2 select * where 条件
     insert into tableName3 select * where 条件
     .....

方式4）表的克隆

 -- 克隆表极其表数据
 create table [if not exists] tablename as select .... from ....;
 create table [if not exists] tablename like tablename1 location 'tablename1的路径'
 ​
 -- 只克隆表结构
 create table [if not exists] tablename like tablename1;

4.4 案例演示

 create table flow(
id             string COMMENT 'this is id column',
phonenumber     string,
mac             string,
ip               string,
url              string,
urltype          string,
uppacket         int,
downpacket       int,
upflow            int,
downflow         int,
issuccess    int
)
row format delimited
fields terminated by "t"
lines terminated by "n"
stored as textfile;

 需求：统计每个手机的总上行，总下行，总流量

select phonenumber,sum(upflow),sum(downflow),sum(upflow+downflow)
from flow
group by phonenumber;

 

4.5 数据的导出

4.5.1 将hive的表的数据导出到目录下

 -- 可以导出到本地文件系统的目录下
    insert overwrite local directory '本地目录' select .... from tablename;
 -- 可以导出到hdfs系统的目录下
    insert overwrite directory 'hdfs目录' select .... from tablename;

注意：导出的数据文件里字段没有分开，使用的是默认分隔符"^A";

可以自定义分隔符，语法如下：

  insert overwrite [local] directory '目录' row format delimited fields terminated by '分隔符'
  select .... from tablename;

4.5.2 将hive的表的数据导入到指定文件中

[root@qianfeng01 ~]# hive -e e 'DQL' >> '文件路径'

注意：默认分隔符为制表符‘t’

五、hive的shell技巧(熟悉)

在配置文件中，配置显示当前数据库和启用mr的本地运行模式

 
    hive.cli.print.current.db
    true


    hive.exec.mode.local.auto
    true

六、表类型的详解（重点）

6.1 表的分类

表根据用途来划分为两种类型，一种是管理表(也叫内部表)，一种是外部表。

管理表

-- 也叫内部表
-- 表的数据存在属性${hive.metastore.warehouse.dir}指定的目录下的相应的库下的与表名相同的子目录下
-- 普通的创建语法创建出来的就是管理表

外部表

 -- 建表时必须使用关键字"external"
  create external table  tablename (.....) ....  [location .....]
-- 可以使用location关键字来指定外部表的数据的具体位置，如果不使用该关键字，和管理表的位置相似。

6.2 表类型的转换

内部表转成外部表

 alter table tablename set tblproperties('EXTERNAL'='TRUE');

外部表转成内部表

 alter table tablename set tblproperties('EXTERNAL'='FALSE');

注意事项：

属性external必须大写。true必须大写。false大小写都行

6.3 两种表的区别

 1. 建表期间
     -外部表必须使用关键字external，内部表正常创建，没有该关键字
     -外部表可以使用location关键字指定数据的具体位置，内部表只能在库目录下。
 2. 用途
    - 外部表通常用于存储永久性数据文件。换句话说，就是不能随便删除的表要使用外部表。数据仓库底层一般都是外部表
    - 内部表一般用于测试数据
 3. 删除表时
     - 删除内部表时，mysql中的元数据以及hdfs上的原始数据都会被删除。
     - 删除外部表时，mysql中的元数据会被删除，但是hdfs上的原始数据不会被删除。

注意：建表时会在hdfs上产生目录以及在mysql中存储元数据。

七、Hive的基本查询语句

7.1 查询原则：

 1. 尽量不使用子查询、尽量不使用in 或者not in (可以使用 [not] exists替代)
 2. 尽量避免join连接查询，但是通常避免不了   
 3. 查询永远是小表驱动大表（小表作为驱动表）
   --注意：内连接时，默认是左表是驱动表，因此左表一定要是小表。
   --     外连接看需求而定。  

7.2 在hive(2.1.1版本)中的注意事项：

 1. 列别名可以提前应用到having子句中
 2. 子查询如果在where子句中作为条件，那么不能使用等号，必须使用in，not in 换句话说，即使子查询返回的是唯一的一个值，也是集合形式。
 3. 表名问题：
     如果涉及到嵌套或者是子查询，表名相同，那么必须起表别名。
 4. 分组查询时，select子句中除了分组字段和聚合函数之外，不能有普通的字段存在。   
      使用count()函数时的执行效率问题：
      当表中有主键字段时，count(主键字段) 效率最高
      没有主键字段时，count(1)效率最高
 5. limit后支持两个参数
          语法格式： limit [index,] lines;
          index:表示行的下标，从0开始
          lines:表示要查询的行数。

7.3 exists的用法

 exists:   作用： 用于判断主查询中是否有满足条件的记录，如果有就返回。如果没有，就过滤掉。子查询的特点时：    如果有满足条件，就会selec后指定的数据，至少一条。相当于true.即存在
           select * from emp A where exists(select 1 from emp t1 where A.mgr=t1.empno)
           
 not exists:
         select * from emp A where not exists(select 1 from emp t1 where A.mgr=t1.empno)
         
         注意：子查询中的条件只能使用等号。

7.4 left semi join

 和exists的原理是一样的
 ​
 用于判断左表中是否有满足关联条件的数据，如果有就显示，没有就过滤掉。不显示右表的数据
 ​
 select * from emp A left semi join emp M on A.mgr = M.empno;
 ​
 ​
 ​
 hive --database sz2103 --hivevar tname=emp -e 'select deptno,count(1) from ${hivevar:tname} group by deptno';

7.5 hive的日志

 位置：/tmp/root/下
 特点：每天一个日志。
 日志名： hive.log
 ​
 当hql语句执行异常时，可以查看该日志。

7.6 hql的运行方式

方式1：在hive的命令行上运行

 本地模式下连接hive
 远程模式下使用beeline连接hiveserver2服务
 源码模式下使用hive(9083)连接metastore服务

方式2：运行hql脚本文件

 步骤1： 将hql语句封装到文件中
 步骤2： 在linux命令行上使用hive指令和-f参数来运行该hql脚本文件

 [root@qianfeng01 ~]# mkdir hql
 [root@qianfeng01 ~]# vim hql/query1.hql
 use mydb2;
 select * from u1 left semi join u2 on u1.id = u2.id;
 [root@qianfeng01 ~]# hive -f hql/query1.hql

方式3：直接在linux命令行上运行hql

第一种：
[root@qianfeng01 ~]#  hive  -e 'select * from mydb2.u1';
第二种：
[root@qianfeng01 ~]#  hive --database mydb2  -e 'select * from u1';    
第三种：声明变量法
[root@qianfeng01 ~]#  hive --database mydb2 --hivevar tablename=emp --hiveconf deptno=10 -e 'select * from ${hivevar:tablename} where deptno=${hiveconf:deptno}';

 

八、HIVE的数据类型

hive的数据类型分两种，一种是基本数据类型，一种是复杂数据类型

8.1 hive的基本数据类型

1. 整型有： int  bigint smallint tinyint
2. 浮点型： double,float, 精确类型(decimal)
3. 布尔型： boolean
4. 字符串类型：string varchar(length),char(length),
5. 日期类型：date,timestamp

案例演示：

-- 数据如下：
233    12    342523    455345345    30000    60000    nihao    helloworld    2017-06-02
126    13    342526    455345346    80000    100000    true    helloworld1    2017-06-02 11:41:30

--建表语句：
create table if not exists datatype1(
id1 tinyint,
id2 smallint,
id3 int,
id4 bigint,
slary float,
comm double,
isok boolean,
content binary,
dt timestamp
)
row format delimited
fields terminated by 't'
;

--导入数据
load data local inpath './data/datatype.txt' into table datatype1;

特点总结：
1. 文件里的数据映射成表的字段值时，如果超出范围，或者类型不匹配，则映射成null。 timestamp类型必须是yyyy-MM-dd HH:mm:ss的格式才能映射成功。
2. 隐式转换
    - 在做运算时，小范围类型都可以自动转为大范围类型做运算

8.2 复杂类型之Array类型

8.2.1 建表语法

create table [if not exists] tablename(
.......
fieldName array<元素类型>
.......
)

 

8.2.2 案例演示

1）数据准备

zhangsan    78,89,92,96
lisi    67,75,83,94
王五    23,12

2）建表语句

-- 错误示范，不指定元素分隔符。
create table scores(
name string,
scores array
)
row format delimited fields terminated by 't' lines terminated by 'n';

-- 结论：array字段映射失败，没有数据，只有一个null元素

-- 正确写法，应该使用collection items terminated by ''指定数组的元素分隔符
drop table if exists scores;
create table scores(
name string,
scores array
)
row format delimited fields terminated by 't'
collection items terminated by ','
lines terminated by 'n';

 

3）导入数据

 load data local inpath 'data/scores.txt' into table scores;

4）array类型的查询

 下标从0开始，如果下标书写时越界，不会报错，只会返回null。

练习1：统计每个人的每科的成绩

 select name,scores[0] math,scores[1] chinese,scores[2] english,scores[3] physic  from scores;
 ​
 结论：下标越界，返回null.

练习2：统计每个人的总成绩 （注意null值的处理）

 select name,scores[0]+scores[1]+nvl(scores[2],0)+nvl(scores[3],0) totalScore from scores;

练习3：统计每个人考试的科目数。(可以借助size函数，来统计array的元素个数）

 select name,size(scores) from scores;

练习4：统计考了3科以上的学生信息

 select * from scores where size(scores)>=3;

练习5：统计第二科成绩小于第一科成绩的学生信息

 select * from scores where scores[1] 
8.3 展开函数explode的应用 
通过练习2，可以发现，如果有多列，那么在计算和的时候，可能会很繁琐。如果能将array的元素变成多行显示。然后使用分组查询，来统计和，就简单多了。因此来研究一下array元素的展开，需要使用展开函数explode
 
案例需求：
 
 zhangsan    78,89,92,96
 lisi    67,75,83,94
 王五  23,12
 ​
 将上述效果转成下面的效果，更方便统计每个人的总成绩。
 ​
 zhangsan        78
 zhangsan        89
 zhangsan        92
 zhangsan        96
 lisi    67
 lisi    75
 lisi    83
 lisi    94
 王五  23
 王五  12
 
8.3.1 简介
 
 - explode：
     展开函数(UDTF函数中的一种),作用是：接受一个数据行，然后返回产生多个数据行  
 - lateral view:虚拟表。
     会将UDTF函数生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到数据聚合的目的
 
8.3.2 案例演示
 
 1. 使用展开函数，只查询array字段，看效果
     hive> select explode(scores) from scores;
     OK
     78
     89
     92
     96
     67
     75
     83
     94
     23
     12
 2. 利用虚拟表与其他字段进行关联。看效果
     语法格式：  
     ....from  原表名  lateral view explode(展开字段) 虚拟表别名 as 展开字段的列别名.
 ​
     hive> select name,score from scores lateral view explode(scores) t1 as score;
     OK
     zhangsan        78
     zhangsan        89
     zhangsan        92
     zhangsan        96
     lisi    67
     lisi    75
     lisi    83
     lisi    94
     王五    23
     王五    12
 3. 利用展开效果，统计每个人的总成绩    
 ​
 select name,sum(score) from scores lateral view explode(scores) t1 as score group by name;
 ​
 注意：from中的虚拟表就已经是关联结果了，所有没有必须在嵌套一层，直接分组查询即可。
 
8.4 搜集函数的应用 
搜集函数有两种，一种是collect_list，一种是collect_set。
 
 collect_list的特点：是有序，可重复
 collect_set的特点：是无序，不可重复
 
8.4.1 案例演示：
 
1）案例需求：
 
 向array字段中动态加载数据，不能直接load加载，而是insert。
 
2）数据准备，也就是准备一张表
 
 hive> create table score_1 as select name,score from scores lateral view explode(scores) t1 as score;
 ​
 hive> select * from score_1;
 OK
 zhangsan        78
 zhangsan        89
 zhangsan        92
 zhangsan        96
 lisi    67
 lisi    75
 lisi    83
 lisi    94
 王五    23
 王五    12
 
3）借助函数收集数据，将每个人的成绩搜集起来
 
 
 
select name,collect_list(score) score_arr from score_1 group by name;
 结果如下：
 lisi    [67,75,83,94]
 zhangsan        [78,89,92,96]
 王五    [23,12]
 
 
注意：搜集函数的返回值就是一个数组。
 
 
 4）动态插入到一个有数组字段的新表中
 
 
 
-- 创建新表
 create table scores_new(
 name string,
 scores array
 )
 row format delimited fields terminated by 't'
 collection items terminated by ','
 lines terminated by 'n';
 
 
-- 动态导入数据
 insert into scores_new select name,collect_list(score) score_arr from score_1 group by name;
 
 
8.5 复杂类型之Map类型 
8.5.1 建表语法
 
 create table tableName(
 .......
 colName map
 ......
 )
 
8.5.2 案例演示1
 
1）数据准备
 
 zhangsan    chinese:90,math:87,english:63,nature:76
 lisi    chinese:60,math:30,english:78,nature:0
 wangwu  chinese:89,math:25
 
2）建表语句
 
 create table scores_map(
 name string,
 scores map
 )
 row format delimited
 fields terminated by 't'
 collection items terminated by ','
 map keys terminated by ':'
 lines terminated by 'n';
 
3）导入数据
 
 load data local inpath './data/map1.txt' into table scores_map;
 
4）map类型的查询
 
 访问map的元素的写法：
 字段名["key"]
 
练习1）查询每个人的数学成绩
 
 select name,scores["math"] from scores_map;
 
练习2）查询语文成绩大于70的学生信息
 
 select * from scores_map where scores["chinese"]>70;
 
练习3）查询每个人的总成绩
 
 select name,scores["chinese"]+scores["math"]+scores["english"]+scores["nature"] from scores_map;
 上述结果，不正确，因为没有处理null值。
 ​
 select name,scores["chinese"]+scores["math"]+nvl(scores["english"],0)+nvl(scores["nature"],0) from scores_map;
 
练习4）使用展开函数来实现练习3的需求
 
 
 
-- 展开效果如下：
 hive> select explode(scores) from scores_map;
 OK
 chinese 90
 math    87
 english 63
 nature  76
 chinese 60
 math    30
 english 78
 nature  0
 chinese 89
 math    25
 
 
--借助虚拟表进行展开
 hive>  select name,subject,score  from scores_map lateral view explode(scores) t1 as subject,score;
 
 
zhangsan        chinese 90
 zhangsan        math    87
 zhangsan        english 63
 zhangsan        nature  76
 lisi    chinese 60
 lisi    math    30
 lisi    english 78
 lisi    nature  0
 wangwu  chinese 89
 wangwu  math    25
 
 
-- 直接查询每个人的总成绩
 select name,sum(score) from scores_map lateral view explode(scores) t1 as subject,score group by name;
 
 
 
 
8.5.2 案例演示2
 
1）案例需求：
 
 将数据动态写入含有map字段的表中
 
2）数据准备
 
 hive> create table map_1 as select name,subject,score  from scores_map lateral view explode(scores) t1 as subject,score;
 ​
 hive> select * from map_1;
 OK
 zhangsan        chinese 90
 zhangsan        math    87
 zhangsan        english 63
 zhangsan        nature  76
 lisi    chinese 60
 lisi    math    30
 lisi    english 78
 lisi    nature  0
 wangwu  chinese 89
 wangwu  math    25
 
3）步骤如下
 
 第一步：将字段subject和score拼接到一起
     select name,concat(subject,":",score) from map_1;
 第二步：将所有属于同一个人的数据组合在一起
     select name,collect_set(concat(subject,":",score)) from map_1 group by name;
 第三步: 将数组的元素用逗号拼接成字符串，借助concat_ws函数
      concat_ws(sep,array): 
      第一个参数是拼接符号，第二个参数是数组。作用就是用拼接符号将数组元素拼接成字符串
     
     select name,concat_ws(",",collect_set(concat(subject,":",score))) from map_1 group by name;
 ​
 第四步：将拼接后的字符串转成map类型
       str_to_map(string,delimiter1,delimiter2):
       参数string: 表示要转换的字符串
       参数delimiter1：指定map的元素分隔符
       参数delimiter2：指定map的key和value的分隔符
 select name,str_to_map(concat_ws(",",collect_set(concat(subject,":",score))),",",":")from map_1 group by name;    
 ​
 第五步：直接使用克隆表的方式，将数据存储到新表中
 create table map_new as select name,str_to_map(concat_ws(",",collect_set(concat(subject,":",score))),",",":") scores from map_1 group by name; 
 ​
 
提前建表，使用insert into动态插入，注意类型转换的问题
 
 drop table scores_map2;
 create table scores_map2(
 name string,
 scores map
 )
 row format delimited
 fields terminated by 't'
 collection items terminated by ','
 map keys terminated by ':'
 lines terminated by 'n';
 ​
 ​
 hive (sz2103)> insert into scores_map2 select name, str_to_map(concat_ws(",",collect_list(concat(subject,":",score))),",",":") from map_10 group by name;
 ​
 
 
8.6 复杂类型之struct类型 
8.6.1 建表语法
 
 create table tablename(
 ......
 fieldName struct
 ......
 )
 
8.6.2 案例演示
 
1）数据准备
 
 zhangsan    90,87,63,76
 lisi    60,30,78,0
 wangwu  89,25,81,9
 
2）建表语句
 
 create table struct_1(
 name string,
 scores struct
 )
 row format delimited
 fields terminated by 't'
 collection items terminated by ',';
 
 
导入数据
 
 load data local inpath './data/struct1.txt' into table struct_1;
 
4）struct类型的查询
 
练习1：查询每个人的数学和英语成绩
 
 select name,scores.math,scores.english from struct_1;
 
练习2：查询英语成绩大于70分的学生信息
 
 select * from struct_1 where scores.english>70;
 
8.7 综合练习 
数据如下：
 
 -- 主管信息表如下：
 manager(uid uname belong tax addr)
 ​
 -- 数据如下：
 1   xdd ll,lw,lg,lc,lz  wx:600,gongjijin:1200,shebao:450    北京,西城,中南海
 2   lkq lw,lg,lc,lz,lq  wx:1000,gongjijin:600,shebao:320    河北,石家庄,中山路
 3   zs  lw,lg,lc        wx:2000,gongjijin:300,shebao:10     江西,南昌,八一大道
 
1）建表语句
 
 create table manager(
 uid int,
 uname string,
 belong array,
 tax map,
 addr struct
 )
 row format delimited
 fields terminated by 't'
 collection items terminated by ','
 map keys terminated by ':';
 
2）导入数据
 
 load data local inpath './data/manager.txt' into table manager;
 
3）练习：查询下属个数大于4个，公积金小于1200，省份在河北的数据
 
 select *
 from manager where size(belong)>4 and tax["gongjijin"]<1200 and addr.province="河北"; 
 
扩展：默认分隔符的输入规则
 
 默认分隔符：^A    对应输入方式:ctrl+V ctrl+A    
 
九、HIVE的函数（重点） 
hive的函数分为两种类型，一种是内置函数，一种是用户自定义函数。
 
如何查看函数：
 
 show functions：查看hive中所有的函数
 desc function funcName: 查看指定函数的用法
 
9.1 常用的内置函数 
常用的内置函数，可以分为时间日期函数、数学函数，字符串函数、高级函数
 
9.1.1 常用的日期时间函数
 
 当前系统时间函数：
     current_date()、
     current_timestamp()、
     unix_timestamp()
     练习：
     hive> select current_date(),current_timestamp(),unix_timestamp();
 日期转时间戳函数：
     unix_timestamp()
     练习：
     select unix_timestamp("1970-1-1 00:00:00","yyyy-MM-dd HH:mm:ss");
     -28800
     注意：当我们查询东八区的凌晨时，返回的是本初子午线的时间点。那个时候正好是69年12月31号下午4点整。
      
     东八区为8点时，本初子午线才是凌晨。 
 ​
 时间戳转日期函数：
     from_unixtime
     练习：
     select from_unixtime(28800,"yyyy-MM-dd HH:mm:ss");
     注意：第一个参数是本初子午线距离1970年1月1日的时间秒数。
 计算时间差函数：
     datediff()
     months_between()
     
     练习
      select datediff(current_date(),'2020-9-6');   :返回的是第一个参数减去第二个参数的天数
      select months_bettween(current_date(),'2020-9-6');
                          :返回的是相差的月数，会有小数。
 日期时间分量函数：
     year()、
     month()、
     day()、
     hour()、
     minute()、
     second()
 日期定位函数：
     last_day()、next_day()
     练习：
     select last_day(current_date())  :返回指定日期所在月的最后一天的日期
     select next_day(current_date(),'mon'):返回指定日期后的第一个周几的具体日期
 日期加减函数：
     date_add()、
     date_sub()、
     add_months()
     
     练习：
      select date_add(current_date(),-1);
      select date_sub(current_date(),1);
      select add_months(current_date(),1);
 字符串转日期：
     to_date(express)
     练习：
     select to_date("2008-8-8")  : 注意，只能使用日期的默认格式yyyy-MM-dd的字符串。
 日期转字符串(格式化)函数：
     date_format
     
     练习
      select date_format(current_date(),'yyyy/MM/dd');
 日期截断函数：
 select trunc(current_date(),"YYYY");  截取到年份，表示后面的日期分量都回到最小值。
 
9.1.2 常用的字符串函数
 
 lower/lcase--（转小写）
 select lower('ABC');
 ​
 upper/ucase--（转大写）
 select upper('abc');
 ​
 length--（字符串长度，字符数）
 select length('abc');
 ​
 concat--（字符串拼接）
 select concat("A", 'B');
 ​
 concat_ws --（指定分隔符）
 select concat_ws('-','a' ,'b','c');
 ​
 substr--（求子串）
 select substr('abcde',3);
 ​
 split(str,regex)--切分字符串，返回数组。
 select split("a-b-c-d-e-f","-");
 ​
 ​
 大小写的函数练习
 hive> select lower("AbcDef"),upper("AbcDef"),lcase("A"),ucase("a");
 hive> select length("no zuo no die"),concat("my teacher"," is canglaoshi"),concat_ws("-","the teacher of all over the world","is","boduoyelaoshi");
 hive> select substr("helloworld",4);    返回的结果为：loworld
       注意：下标从1开始，表示从第4个开始截取
       select substr("helloworld",4,2);   第三个参数表示截取的长度。
 
9.1.3 常用的数学函数
 
 常用的数学统计函数，必须配合分组查询。
 max()
 min()
 avg()
 sum()
 count()
 ​
 四舍五入函数：round(): 
 向上取整函数：ceil()   返回的是大于参数的最小整数
 向下取整函数：floor()， 返回的是小于参数的最大整数
 随机函数：rand() ,  返回的是[0,1)之间的任意一个小数。
 ​
 select round(0.618,2),ceil(0.618),floor(-0.618),rand();
 
9.1.4 其他函数
 
 nvl(value,default value):如果value为null,则使用default value，否则使用本身value.
 if(p1,p2,p3)
 case when  then ....when ...then.. else... end
 ​
 ​
 isnull()
 isnotnull()
 ​
 coalesce(col1,col2,col3...)返回第一个不为空的
 
9.2 高级函数之窗口函数over 
先来看一下这个需求：求每个部门的员工信息以及部门的平均工资。在mysql中如何实现呢
 
 需要两个sql语句来完成
 第一个：查询员工信息
     select * from emp;
 第二个：查询部门的平均工资
     select deptno,avg(ifnull(sal,0)) from emp group by deptno;
 合并一下：
    select e.*,t.avg_sal
    from (select deptno,avg(ifnull(sal,0)) avg_sal from emp group by deptno) t join emp e
    on t.deptno = e.deptno;
    
 另外一种写法，但是效率低下。   
 select e.*,(select avg(ifnull(sal,0)) from emp where deptno = a.deptno) avg_sal from emp a;
 
我们也可以使用窗口函数，更方便的做到如上查询，那么什么是窗口函数呢
 
9.2.1 窗口函数的介绍
 
 -1) 窗口函数over()又名开窗函数，属于分析函数的一种。
 -2) 是一种用于解决复杂报表统计需求的函数。
 -3) 窗口函数常用于计算基于组的某种值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
     简单的说窗口函数对每条详细记录开一个窗口,进行聚合统计的查询
     
     换句话说：窗口中有多少条，就返回多少条
 -4) 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
 -5) 窗口函数一般不单独使用，要配合其他函数一起使用。
 -6) 窗口函数内也可以分组和排序
 
9.2.2 案例演示
 
数据准备
 
 zsan,2019-10-11,10
 zsan,2019-10-12,20
 lisi,2019-10-12,30
 zsan,2019-10-13,30
 lisi,2019-10-13,20
 wangwu,2019-10-13,10
 wangwu,2019-10-14,20
 saml,2018-01-01,10
 saml,2018-01-08,55
 tony,2018-01-07,50
 saml,2018-01-05,46
 tony,2018-01-04,29
 tony,2018-01-02,15
 saml,2018-02-03,23
 mart,2018-04-13,94
 saml,2018-04-06,42
 mart,2018-04-11,75
 mart,2018-04-09,68
 mart,2018-04-08,62
 neil,2018-05-10,12
 neil,2018-06-12,80
 
创建order表:
 
 drop table t_order;
 create table if not exists t_order
 (
     name      string,
     orderdate string,
     cost      int
 )  row format delimited fields terminated by ',';
 
加载数据:
 
 load data local inpath "./data/order1.txt" into table t_order;
 
练习1：查询每个人的信息以及总记录数。
 
不使用开窗函数的写法如下：
 
 select a.*,b.num  from t_order a join (select count(*) num from t_order) b;
 
使用开窗函数的写法如下：
 
 select *,count(1) over() from t_order;
 
注意：over()，如果括号里没有内容，则窗口的数据量是整张表。
 
9.3 over()函数的其他用法 
9.3.1 distribute by + sort by 组合
 
 位置：在over函数的小阔号
 写法：可以单独使用，也可以一起组合使用
     如：
     over(distribute by colName)
     over(sort by colName)
     over(distribute by colName sort by colName [asc|desc])
 作用：
    distribute by colName：用于指定分组字段，表示按照指定字段分组，那么每一组对应一个窗口,
                           如果没有，则表示整张表为一组
    sort by colName: 用于排序，如果没有distribute by组合，表示整张表为一
                    组，进行排序，如果有则组内进行排序
 
练习：使用窗口函数，统计每个人的总花销
 
 select *,sum(cost) over(distribute by name) from t_order;
 
练习：查询每个人的详情以及总花销，按照name降序
 
 select *,sum(cost) over(sort by name desc) from t_order;
 
练习：查询每个人的总花销，并且按照花销降序
 
 select *,sum(cost) over(distribute by name sort by cost desc) from t_order;
 
9.3.2 partition by +order by组合
 
 位置：还是在over小括号里
 写法：可以单独使用，也可以一起组合使用
     如
     over(partition by colName)
     over(order by colName)
     over(partition by colName order by colName [asc|desc])
 作用：与 distribute by + sort by 组合效果一模一样。  
 ​
 ​
 练习：查询每个人的总花销，最大的花销数，最小的花销，平均花销，以及花销次数以及详情
 select *,
 sum(cost) over(partition by name),
 max(cost) over(partition by name),
 min(cost) over(partition by name),
 avg(nvl(cost,0)) over(partition by name),
 count(1) over(partition by name),
 sum(cost) over() `商场营业额`
 from t_order;
 
9.4 window子句的作用 
window子句用来更细粒度的管理窗口大小的
 
 current row： 当前行
 preceding:  向前
 following:  向后
 unbounded preceding: 从起点
 unbounded following: 到终点
 
 select name,orderdate,cost,
        sum(cost) over() as sample1,--所有行相加
        sum(cost) over(partition by name) as sample2,-- 按name分组，组内数据相加
 ​
        sum(cost) over(partition by name order by orderdate) as sample3,-- 按name分组，组内数据累加
        
        sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,-- 与sample3一样，由起点到当前行的聚合
        
        sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, -- 当前行和前面一行做聚合
        
        sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,-- 当前行和前边一行及后面一行
        
        sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 -- 当前行及后面所有行
        
 from t_order;
 ​
 ​
 练习： 统计每个人的详细信息，以及最近三天的花销总额
 select *,sum(cost) over(partition by name order by orderdate rows between 2 preceding and current row) from t_order;
 
 
9.5 排名函数 
有三个排名函数，分别是row_number()、dense_rank()、rank()这三个，他们的排名效果不同。情况如下：
 
 效果一：   分数有相同，名次是连续不重复的，    对应的函数是row_number() 
 分数   名次
 100     1
 99      2
 99      3
 98      4
 ​
 效果二：   分数相同，名次有重复，是间断的     对应的函数是rank() 
 分数   名次
 100     1
 99      2
 99      2
 98      4
 ​
 效果三：   分数有相同，名次重复不间断        应的函数是dense_rank() 
 分数   名次
 100     1
 99      2
 99      2
 98      3
 
这三个排名函数，不能单独使用，也必须配合over函数一起使用。
 
练习1：显示全校排名名次
 
 select *, row_number() over(sort by score desc) rk1,rank() over(sort by score desc) rk2,dense_rank() over(sort by score desc) rk3
 from stu_score;
 
练习2：查询每个班级的排名情况，效果：分数相同的话，名次一样，不间断
 
 select * ,dense_rank() over(distribute by classno sort by score desc) from stu_score;
 
练习3：取每个班级的前三名，dense_rank()。
 
 select *
 from 
 (select * ,dense_rank() over(distribute by classno sort by score desc) rk from stu_score) t
 where rk<=3;
 
9.6 其他序列函数 
1）ntile函数
 
 也是一个强大的分析函数，可以看成是：它把有序的数据集合 "平均分配" 到 "指定数量"（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
 
练习：
 
 select *,ntile(3) over(sort by score desc) from stu_score; 
 select *,ntile(3) over(distribute by classno sort by score desc) from stu_score; 
 
2）lag()和lead()函数
 
 lag(colName,precedingNum[,defaultValue])
     :向前取第precedingNum行的该colName的值，如果没有，使用defaultValue
 lead(colName,followingNum[,defaultValue])
     :向后取第followingNum行的该colName的值，如果没有，使用defaultValue
 
练习1：列出每个顾客的当次消费以及上一次的消费额度
 
 select *,lag(cost,1) over(partition by name order by orderdate) from t_order;
 
练习2：列出每个顾客的当次消费以及下2次的消费额度，如果下2次没有消费，设置为0
 
 select *,lead(cost,2,0) over(partition by name order by orderdate) from t_order;
 
练习3： 求5分钟连续点击100次的用户
 
 dt                  id  url
 ........
 2019-08-22 19:00:01,1,www.baidu.com
 2019-08-22 19:01:01,1,www.baidu.com
 2019-08-22 19:02:01,1,www.baidu.com
 2019-08-22 19:03:01,1,www.baidu.com
 .......
 ​
 思路：  每个用户分组并按照时间升序，并显示详情，然后向前取第100条，与当前条的时间做差值。如果差值<300秒，则为5分钟之后连续点击超过100次。
 
3）first_value和last_value函数
 
 
first_value(colName) 取分组内排序后，截止到当前行，第一个值
  
last_value(colName) 分组内排序后，截止到当前行，最后一个值
 
练习
 
 select *,first_value(cost) over(partition by name order by orderdate) from t_order;
 select *,last_value(cost) over(partition by name order by orderdate) from t_order;
 
练习2：取每一组中的第一个值和最后一个值
 
 select *,
 first_value(cost) over(partition by name order by orderdate) firstV,
 first_value(cost) over(partition by name order by orderdate desc) lastV from t_order;
 
9.7 自定义函数 
如果hive的内置函数不能满足我们的查询需求，那么就可以使用hive的api来自定义一些hive的函数。自定义函数分三类：
 
 一、UDF(用户自定义函数)
     特点：一进一出，一对一的形式
 二、UDAF(用户自定义聚合函数)
     特点：多进一出，多对一的形式，类似max,sum等函数
 三、UDTF(用户自定义表生成函数)
     特点：一进多出，一对多的形式，类似explode函数
 
9.7.1 UDF的应用
 
1）准备工作和注意事项
 
在pom.xml,加入以下maven的依赖包
 
 
     org.apache.hive
     hive-exec
     2.1.1
 
 
定义UDF函数要注意下面几点:
 
 1. 继承org.apache.hadoop.hive.ql.exec.UDF
 2. 重写evaluate()，这个方法不是由接口定义的,因为它可接受的参数的个数,数据类型都是不确定的。
 当在hive的hql里应用自定义函数时，Hive会根据向函数中传入的参数个数以及类型,查看对应的自定义类中能否找到和函数调用相匹配的evaluate()方法
 
案例演示1：
 
 写一个UDF, 将小写字母转成大写字母。
 
代码如下：
 
 
 
package com.qf.hive.udf;
 ​
 import org.apache.hadoop.hive.ql.exec.UDF;
 ​
 
 public class MyUpperCase extends UDF {
     public String evaluate(String value){
         if(value==null||value.length()==0){
             return null;
         }
         return value.toUpperCase();
     }
 }
 
 ​
 
使用第一种方式临时加载（只针对于当前session有效）
 
 步骤1：将写好的程序打包并上传到linux上，然后使用hive的add jar指定加载到classpath里
     hive> add jar /root/hive-1.0-SNAPSHOT.jar;
 步骤2：创建临时函数
     hive> create temporary function myUpper as 'com.qf.hive.udf.FirstUDF'; 
 步骤3. 查看我们创建的自定义函数，
     hive> show functions;
 步骤4.在hive中使用函数进行功能测试 
     hive> select myupper('a'); 
 步骤5. 如何删除自定义函数？在删除一个自定义函数的时候一定要确定该函数没有调用
     hive> drop temporary function if exists myupper;
 
案例演示2
 
 把生日转换成年纪
 
代码如下：
 
 package com.qf.hive.udf;
 ​
 import org.apache.commons.lang.StringUtils;
 import org.apache.hadoop.hive.ql.exec.UDF;
 ​
 import java.util.Calendar;
 ​
 public class Birthday2Age extends UDF {
     public int evaluate(String birth) {
         // 1、判断参数
         if (StringUtils.isEmpty(birth)) {
             return -1;
         }
         // 拆分生日，获取年月日
         String[] birthdays = birth.split("-");
         // 得到生日年月日
         int birthYear = Integer.parseInt(birthdays[0]);
         int birthMonth = Integer.parseInt(birthdays[1]);
         int birthDay = Integer.parseInt(birthdays[2]);
         // 获取当前时间
         Calendar calendar = Calendar.getInstance();
         int nowYear = calendar.get(Calendar.YEAR);
         int nowMonth = calendar.get(Calendar.MONTH) + 1;
         int nowDay = calendar.get(Calendar.DAY_OF_MONTH);
         // 计算年龄
         int age = nowYear - birthYear;
         // 判断月份和日期
         if (nowMonth < birthMonth) {
             age -= 1;
         } else if (nowMonth == birthMonth && nowDay < birthDay) {
             age -= 1;
         }
         return age;
     }
     public static void main(String[] args) {
 ​
         System.out.println(new Birthday2Age().evaluate("1980-09-8"));
     }
 }
 
使用第二种方式加载自定义函数并测试
 
 第二种方式：使用hive指令，在启动时加载配置文件形式(只针对当前会话生效)
 ​
 步骤1：上传打包好的程序到linux
 步骤2：创建一个配置文件/root/hive-init.conf,书写以下内容
     add jar /root/hive-1.0-SNAPSHOT.jar;
     create temporary function birthdayToAge as 'com.qf.hive.udf.Birthday2Age';
 步骤3：退出hive客户端，重新进入hive
     hive - i  /root/hive-init.conf
 步骤4：检查是否存在
     show functions
 步骤5：测试应用
     select birthdaytoage("1986-11-08");
 
案例演示3
 
 根据key获取value
 
 
数据准备：
 
 sex=1&hight=180&weight=100&sal=2000000
 ​
 输入:sal
 输出:2000000
 
 
实现思路：
 
 1. 将这种输入字符串转换成json格式的字符串{sex:1,hight:180,weight:100,sal:2000000}
 2. 构建一个json的解析器
 3. 根据key获取value
 
3）代码如下
 
 
 
package com.qf.hive.udf;
 ​
 import org.apache.commons.lang.StringUtils;
 import org.apache.hadoop.hive.ql.exec.UDF;
 import org.json.JSONException;
 import org.json.JSONObject;
 ​
 public class Key2Value extends UDF {
     public String evaluate(String str, String key) throws JSonException {
         // 1、判断参数
         if (StringUtils.isEmpty(str) || StringUtils.isEmpty(key)) {
             return null;
         }
         // 将str转换成json格式
         // sex=1&hight=180&weight=130&sal=28000
         // {'sex':1,'hight':180,'weight':130,'sal':28000}
         String s1 = str.replace("&", ",");
         String s2 = s1.replace("=", ":");
         String s3 = "{" + s2 + "}";
         // 使用尽送对象解析json串
         JSonObject jo = new JSonObject(s3);
         return jo.get(key).toString();
     }
     public static void main(String[] args) throws JSonException {
         System.out.println(new Key2Value().evaluate("sex=1&hight=180&weight=130&sal=28000&faceId=189", "faceId")
         );
     }
 }
 
4）使用第三种方式来加载自定义函数
 
 1、将编写好的自定义函数程序上传到linux
 2、在hive的安装目录下的bin目录中创建一个名为.hiverc的文件并输入以下内容
     add jar /root/hive-1.0-SNAPSHOT.jar;
     create temporary function getvaluebykey as 'com.qf.hive.udf.Key2Value'; 
 3、重新启动hive客户端
 4、查看是否存在
 5、测试运行
     select getvaluebykey("uid=1001&name=zangs&age=23&girlfriend=canglaoshi","girlfriend");
 
案例演示4：
 
 正则表达式解析日志
 
代码如下：
 
 package com.qf.hive.udf;
 ​
 import org.apache.commons.lang.StringUtils;
 import org.apache.hadoop.hive.ql.exec.UDF;
 ​
 import java.text.ParseException;
 import java.text.SimpleDateFormat;
 import java.util.Date;
 import java.util.Locale;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 ​
 public class LogParser extends UDF {
     public String evaluate(String log) throws ParseException, Exception {
         // 1
         if (StringUtils.isEmpty(log)) {
             return null;
         }
         // 220.181.108.151 - - [31/Jan/2012:00:02:32 +0800] "GET
         // /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP/1.1" 200 8784
         // "-" "Mozilla/5.0 (compatible; Baiduspider/2.0;
         // +http://www.baidu.com/search/spider.html)"
         // 220.181.108.151 20120131 120232 GET
         // /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP 200 Mozilla
         // 定义一个正则表达式
         String reg = "^([0-9.]+\d+) - - \[(.* \+\d+)\] .+(GET|POST) (.+) (HTTP)\S+ (\d+) .+\"(\w+).+$";
         // 获取一个模式匹配器
         Pattern pattern = Pattern.compile(reg);
         // 匹配结果
         Matcher matcher = pattern.matcher(log);
         //
         StringBuffer sb = new StringBuffer();
         // 判断数据是否匹配上
         if (matcher.find()) {
             // 先获取匹配的段数
             int count = matcher.groupCount();
             // 循环获取每段的内容，并且将内容拼接起来
             for (int i = 1; i <= count; i++) {
                 // 判断字段是否是时间字段，如果是，则做时间格式转换
                 if (i == 2) {
                     // 定义一个时间格式来解析当前时间
                     Date d = new SimpleDateFormat("dd/MMM/yyyy:HH:mm:ss Z", Locale.ENGLISH).parse(matcher.group(i));
                     // 定义输出的时间格式
                     SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd hhmmss");
                     // 将时间格式转换成输出格式，并输出
                     sb.append(sdf.format(d) + "t");
                 } else {
                     sb.append(matcher.group(i) + "t");
                 }
             }
         }
         return sb.toString();
     }
 ​
     public static void main(String[] args) throws Exception {
         System.out.println(new LogParser().evaluate(
                 "220.181.108.151 - - [31/Jan/2012:00:02:32 +0800] \"GET /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP/1.1\" 200 8784 \"-\" \"Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)\""));
     }
 }
 
准备数据：
 
 数据文件名access.log
 ​
 create table log100(
 log string
 );
 ​
 load data local inpath './data/access.log' into table log100;
 
使用第一种临时加载的方式测试自定义函数
 
 ...
 create temporary function logparser as 'com.qf.hive.udf.LogParser'
 ..
 select logparser(log) from log100;
 
案例演示5：
 
 Json数据解析UDF开发
 
1） 数据准备：
 
 有原始json数据如下：
 {"movie":"1193","rate":"5","datetime":"978300760","uid":"1"}
 {"movie":"661","rate":"3","datetime":"978302109","uid":"1"}
 {"movie":"914","rate":"3","datetime":"978301968","uid":"1"}
 {"movie":"3408","rate":"4","datetime":"978300275","uid":"1"}
 {"movie":"2355","rate":"5","datetime":"978824291","uid":"1"}
 {"movie":"1197","rate":"3","datetime":"978302268","uid":"1"}
 {"movie":"1287","rate":"5","datetime":"978302039","uid":"1"}
 ​
 参考数据文件：rating.json
 ​
 最终形成hive表中这样的数据
 movie   rate    datetime   uid
 1197    3       978302268    1
 
2） 将原始数据导入到hive库，先创建一个单字段的表
 
 create table if not exists movie_json
 (json string); 
 ​
 load data local inpath './data/rating.json' into table movie_json;
 
代码如下：
 
RateBean
 
 package com.qf.hive.udf;
 ​
 import org.apache.hadoop.io.WritableComparable;
 ​
 import java.io.DataInput;
 import java.io.DataOutput;
 import java.io.IOException;
 ​
 public class RateBean implements WritableComparable {
     private int uid;
     private int movie;
     private int rate;
     private String datetime;
 ​
     public RateBean(){}
 ​
     public RateBean(int uid, int movie, int rate, String datetime) {
         this.uid = uid;
         this.movie = movie;
         this.rate = rate;
         this.datetime = datetime;
     }
 ​
     public int getUid() {
         return uid;
     }
 ​
     public void setUid(int uid) {
         this.uid = uid;
     }
 ​
     public int getMovie() {
         return movie;
     }
 ​
     public void setMovie(int movie) {
         this.movie = movie;
     }
 ​
     public int getRate() {
         return rate;
     }
 ​
     public void setRate(int rate) {
         this.rate = rate;
     }
 ​
     public String getDatetime() {
         return datetime;
     }
 ​
     public void setDatetime(String datetime) {
         this.datetime = datetime;
     }
 ​
     
     @Override
     public int compareTo(RateBean o) {
         return o.getRate()-this.rate;
     }
 ​
     @Override
     public void write(DataOutput out) throws IOException {
         out.writeInt(uid);
         out.writeInt(movie);
         out.writeInt(rate);
         out.writeUTF(datetime);
     }
 ​
     @Override
     public void readFields(DataInput in) throws IOException {
         uid = in.readInt();
         movie = in.readInt();
         rate = in.readInt();
         datetime = in.readUTF();
     }
     public String toString(){
         return movie+"t"+rate+"t"+datetime+"t"+uid;
     }
 }
 ​
 
 package com.qf.hive.udf;
 ​
 import com.google.common.base.Strings;
 import org.apache.hadoop.hive.ql.exec.UDF;
 import org.apache.log4j.Logger;
 import org.codehaus.jackson.map.ObjectMapper;
 ​
 import java.io.IOException;
 ​
 public class JsonParser extends UDF {
     private Logger logger = Logger.getLogger(JsonParser.class);
     public String evaluate(String json) {
         //判断传入参数
         if(Strings.isNullOrEmpty(json)){
             return null;
         }
         ObjectMapper objectMapper = new ObjectMapper();
         try {
             RateBean bean = objectMapper.readValue(json, RateBean.class);
             return bean.toString();
         } catch (IOException e) {
             logger.error("解析json串失败！！",e);
         }
         return null;
     }
     public static void main(String[] args) {
         System.out.println(new JsonParser().evaluate("{"movie":"1193","rate":"5","datetime":"978300760","uid":"1"}"));
     }
 }
 
使用临时加载的方式加载自定义函数，并进行测试
 
 
 
 ....  create temporary function jsonParser as 'com.qf.hive.udf.JsonParser'  ....  select jsonparser(json) from movie_json limit 100;
 
 
将解析的数据存入到一张新表中（直接克隆一张新表进行存储数据）
 
 
 
 drop table movie_rate;  create table if not exists movie_rate  as  select  split(jsonParser(json),'t')[0] as movie,  split(jsonParser(json),'t')[1] as rate,  split(jsonParser(json),'t')[2] as times,  split(jsonParser(json),'t')[3] as uid  from movie_json;
 ​
 ​
 drop table movie_rate;
 create table if not exists movie_rate
 as
 select 
 split(getjson(json),'t')[0] as movie,
 split(getjson(json),'t')[1] as rate,
 split(getjson(json),'t')[2] as times,
 split(getjson(json),'t')[3] as uid
 from movie_json;
 
 
案例演示6
 
内置的json解析函数：
 
 
 
 select get_json_object(json,"$.movie") movie,  get_json_object(json,"$.rate") rate,  get_json_object(json,"$.datetime") datetime,  get_json_object(json,"$.uid") uid  from movie_json;