Hive笔记

 

1.  什么是Hive？

1.1 Hive基本思想

　　Hive是基于Hadoop的一个数据仓库工具(离线)，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

 

1.2 为什么使用Hive

• 直接使用hadoop所面临的问题

　　　　人员学习成本太高

　　　　项目周期要求太短

　　　　MapReduce实现复杂查询逻辑开发难度太大 

• 为什么要使用Hive

　　　　操作接口采用类SQL语法，提供快速开发的能力。

　　　　避免了去写MapReduce，减少开发人员的学习成本。

　　　　功能扩展很方便。

1.3 Hive的特点

• 可扩展

　　　　Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。

• 延展性

　　　　Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

• 容错

　　　　良好的容错性，节点出现问题SQL仍可完成执行。

 

2. Hive安装

2.1 最简安装：用内嵌derby作为元数据库

　　准备工作：安装hive的机器上应该有HADOOP环境（安装目录，HADOOP_HOME环境变量）

　　安装：直接解压一个hive安装包即可。此时，安装的这个hive实例使用其内嵌的derby数据库作为记录元数据的数据库。此模式不便于让团队成员之间共享协作

2.2 标准安装：将mysql作为元数据库

2.2.1 mysql安装

　　具体安装步骤以及报错解决方案见博客：

　　安装：https://www.cnblogs.com/julyme/p/5969626.html

　　报错解决：https://blog.csdn.net/a774630093/article/details/79270080

2.2.2 Hive的元数据库配置

　　1. 在 $HIVE_HOME/conf 目录下添加（修改）文件：

vi conf/hive-site.xml

<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</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.ConnectionPassword</name>
<value>root</value>
<description>password to use against metastore database</description>
</property>
</configuration>

　　2. 上传一个mysql的驱动jar包到Hive的安装目录的lib中

　　3.配置HADOOP_HOME 和HIVE_HOME到系统环境变量中：/etc/profile

　　4. source /etc/profile

　　5.hive启动测试

　　　　然后用命令启动hive交互界面：   

　　　　[root@hdp20-04 ~]# hive

3. Hive的使用方式

3.1 最基本使用方式

　　启动一个hive交互shell

　　bin/hive

　　hive>

　　设置一些基本参数，让hive使用起来更便捷，比如：

　　1、让提示符显示当前库：

　　　　hive>set hive.cli.print.current.db=true;

　　2、显示查询结果时显示字段名称：

　　　　hive>set hive.cli.print.header=true; 

　　但是这样设置只对当前会话有效，重启hive会话后就失效，解决办法：在linux的当前用户目录中，编辑一个.hiverc文件，将参数写入其中：

vi .hiverc

set hive.cli.print.header=true;
set hive.cli.print.current.db=true;

 

3.2 启动Hive服务使用

　　启动hive的服务：

[root@hdp2-slaver2 hive-1.2.2]# bin/hiveserver2 -hiveconf hive.root.logger=DEBUG,console

　　上述启动，会将这个服务启动在前台，如果要启动在后台，则命令如下：

nohup bin/hiveserver2 1>/dev/null 2>&1 &

　　启动成功后，可以在别的节点上用beeline去连接。

　　方式（1）

　　　　[root@hdp20-04 hive-1.2.1]# bin/beeline  回车，进入beeline的命令界面

　　输入命令连接hiveserver2

　　　　beeline> !connect jdbc:hive2//mini1:10000

　　　　（hadoop01是hiveserver2所启动的那台主机名，端口默认是10000）

　　方式（2）

　　启动时直接连接：

bin/beeline -u jdbc:hive2://mini1:10000 -n root

　　接下来就可以做正常sql查询了

3.3. 脚本化运行

　　大量的hive查询任务，如果用交互式shell来进行输入的话，显然效率及其低下，因此，生产中更多的是使用脚本化运行机制：

　　该机制的核心点是：hive可以用一次性命令的方式来执行给定的hql语句

[root@hdp20-04 ~]#  hive -e "insert into table t_dest select * from t_src;"

　　然后，进一步，可以将上述命令写入shell脚本中，以便于脚本化运行hive任务，并控制、调度众多hive任务，示例如下：

vi t_order_etl.sh

#!/bin/bash
hive -e "select * from db_order.t_order"
hive -e "select * from default.t_user"
hql="create table  default.t_bash as select * from db_order.t_order"
hive -e "$hql"

　　如果要执行的hql语句特别复杂，那么，可以把hql语句写入一个文件：

vi x.hql

select * from db_order.t_order;
select count(1) from db_order.t_user;

　　然后，用hive -f /root/x.hql 来执行

 

4. Hive建库建表与数据导入

4.1. 建库

　　hive中有一个默认的库：

　　库名： default

　　库目录：hdfs://hdp20-01:9000/user/hive/warehouse

　　新建库：

create database db_order;

　　库建好后，在hdfs中会生成一个库目录：hdfs://hdp20-01:9000/user/hive/warehouse/db_order.db

4.2 建表

4.2.1 基本建表语句

use db_order;

create table t_order(id string,create_time string,amount float,uid string);

　　表建好后，会在所属的库目录中生成一个表目录 /user/hive/warehouse/db_order.db/t_order

　　只是，这样建表的话，hive会认为表数据文件中的字段分隔符为 ^A

　　正确的建表语句为：

create table t_order(id string,create_time string,amount float,uid string)
row format delimited
fields terminated by ',';

　　这样就指定了，我们的表数据文件中的字段分隔符为 ","

4.2.2 删除表

drop table t_order;

　　删除表的效果是：

　　　　hive会从元数据库中清除关于这个表的信息；

　　　　hive还会从hdfs中删除这个表的表目录；

4.2.3 内部表与外部表

　　内部表(MANAGED_TABLE)：表目录按照hive的规范来部署，位于hive的仓库目录/user/hive/warehouse中

　　外部表(EXTERNAL_TABLE)：表目录由建表用户自己指定

create external table t_access(ip string,url string,access_time string)
row format delimited
fields terminated by ','
location '/access/log';

　　外部表和内部表的特性差别：

　　　　1、内部表的目录在hive的仓库目录中 VS 外部表的目录由用户指定

　　　　2、drop一个内部表时：hive会清除相关元数据，并删除表数据目录

　　　　3、drop一个外部表时：hive只会清除相关元数据；

　　一个hive的数据仓库，最底层的表，一定是来自于外部系统，为了不影响外部系统的工作逻辑，在hive中可建external表来映射这些外部系统产生的数据目录；然后，后续的etl操作，产生的各种表建议用managed_table

4.2.4. 分区表

　　分区表的实质是：在表目录中为数据文件创建分区子目录，以便于在查询时，MR程序可以针对分区子目录中的数据进行处理，缩减读取数据的范围。比如，网站每天产生的浏览记录，浏览记录应该建一个表来存放，但是，有时候，我们可能只需要对某一天的浏览记录进行分析。这时，就可以将这个表建为分区表，每天的数据导入其中的一个分区；当然，每日的分区目录，应该有一个目录名（分区字段）。

4.2.4.1. 一个分区字段的实例：

　　示例如下：

　　1、创建带分区的表

create table t_access(ip string,url string,access_time string)
partitioned by(dt string)
row format delimited
fields terminated by ',';

　　注意：分区字段不能是表定义中的已存在字段

　　2、向分区中导入数据

load data local inpath '/root/access.log.2017-08-04.log' into table t_access partition(dt='20170804');
load data local inpath '/root/access.log.2017-08-05.log' into table t_access partition(dt='20170805');

　　3、针对分区数据进行查询

　　a、统计8月4号的总PV：

select count(*) from t_access where dt='20170804';

　　实质：就是将分区字段当成表字段来用，就可以使用where子句指定分区了

　　b、统计表中所有数据总的PV：

select count(*) from t_access;

　　实质：不指定分区条件即可

4.2.4.2. 多个分区字段示例

　　建表：

create table t_partition(id int,name string,age int)
partitioned by(department string,sex string,howold int)
row format delimited fields terminated by ',';

　　导数据：

load data local inpath '/root/p1.dat' into table t_partition partition(department='xiangsheng',sex='male',howold=20);

4.2.5. CTAS建表语法

　　1、可以通过已存在表来建表：

create table t_user_2 like t_user;

　　新建的t_user_2表结构定义与源表t_user一致，但是没有数据

　　2、在建表的同时插入数据

create table t_access_user
as
select ip,url from t_access;

　　t_access_user会根据select查询的字段来建表，同时将查询的结果插入新表中。

 

4.3 数据导入导出

4.3.1 将数据文件导入hive的表

　　方式1：导入数据的一种方式：

　　　　手动用hdfs命令，将文件放入表目录；

　　方式2：在hive的交互式shell中用hive命令来导入本地数据到表目录

　　　　hive>load data local inpath '/root/order.data.2' into table t_order;

　　方式3：用hive命令导入hdfs中的数据文件到表目录

　　　　hive>load data inpath '/access.log.2017-08-06.log' into table t_access partition(dt='20170806');

　　注意：导本地文件和导HDFS文件的区别：

　　　　本地文件导入表：复制

　　　　hdfs文件导入表：移动

4.3.2 将hive表中的数据导出到指定路径的文件

　　1、将hive表中的数据导入HDFS的文件

insert overwrite directory '/root/access-data'
row format delimited fields terminated by ','
select * from t_access;

　　2、将hive表中的数据导入本地磁盘文件

insert overwrite local directory '/root/access-data'
row format delimited fields terminated by ','
select * from t_access limit 100000;

4.3.3 hive文件格式

　　HIVE支持很多种文件格式： SEQUENCE FILE | TEXT FILE | PARQUET FILE | RC FILE

create table t_pq(movie string,rate int)  stored as textfile;
create table t_pq(movie string,rate int)  stored as sequencefile;
create table t_pq(movie string,rate int)  stored as parquetfile;

　　演示：

　　1、先建一个存储文本文件的表

create table t_access_text(ip string,url string,access_time string)
row format delimited fields terminated by ','
stored as textfile;

　　导入文本数据到表中：

load data local inpath '/root/access-data/000000_0' into table t_access_text;

　　2、建一个存储sequence file文件的表：

create table t_access_seq(ip string,url string,access_time string)
stored as sequencefile;

　　从文本表中查询数据插入sequencefile表中，生成数据文件就是sequencefile格式的了：

insert into t_access_seq
select * from t_access_text;

　　3、建一个存储parquet file文件的表：

create table t_access_parq(ip string,url string,access_time string)
stored as parquetfile;

4.4 数据类型

4.4.1 数字类型

　　TINYINT (1-byte signed integer, from -128 to 127)
　　SMALLINT (2-byte signed integer, from -32,768 to 32,767)
　　INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)
　　BIGINT (8-byte signed integer, from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
　　FLOAT (4-byte single precision floating point number)
　　DOUBLE (8-byte double precision floating point number)

　　示例：

create table t_test(a string ,b int,c bigint,d float,e double,f tinyint,g smallint)

4.4.2 日期时间类型

　　TIMESTAMP (Note: Only available starting with Hive 0.8.0)
　　DATE (Note: Only available starting with Hive 0.12.0)

　　示例，假如有以下数据文件：

1,zhangsan,1985-06-30
2,lisi,1986-07-10
3,wangwu,1985-08-09

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

　　那么，就可以建一个表来对数据进行映射。然后导入数据

load data local inpath '/root/customer.dat' into table t_customer;

　　然后，就可以正确查询

4.4.3 字符串类型

　　STRING
　　VARCHAR (Note: Only available starting with Hive 0.12.0)
　　CHAR (Note: Only available starting with Hive 0.13.0)

4.4.4 混杂类型

　　BOOLEAN
　　BINARY (Note: Only available starting with Hive 0.8.0)

4.4.5 复合类型

4.4.5.1 array数组类型

　　arrays: ARRAY<data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)

　　示例：array类型的应用。假如有如下数据需要用hive的表去映射：

战狼2,吴京:吴刚:龙母,2017-08-16
三生三世十里桃花,刘亦菲:痒痒,2017-08-20

 　　设想：如果主演信息用一个数组来映射比较方便

　　建表：

create table t_movie(moive_name string,actors array<string>,first_show date)
row format delimited fields terminated by ','
collection items terminated by ':';

　　导入数据：

load data local inpath '/root/movie.dat' into table t_movie;

　　查询：

select * from t_movie;
select moive_name,actors[0] from t_movie;
select moive_name,actors from t_movie where array_contains(actors,'吴刚');
select moive_name,size(actors) from t_movie;

4.4.5.2 map类型

　　maps: MAP<primitive_type, data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)

　　1) 假如有以下数据：

1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

　　可以用一个map类型来对上述数据中的家庭成员进行描述 

　　2) 建表语句：

create table t_person(id int,name string,family_members map<string,string>,age int)
row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';

　　3) 查询

select * from t_person; 

　　## 取map字段的指定key的值

select id,name,family_members['father'] as father from t_person;

　　## 取map字段的所有key

select id,name,map_keys(family_members) as relation from t_person;

　　## 取map字段的所有value

select id,name,map_values(family_members) from t_person;
select id,name,map_values(family_members)[0] from t_person;

　　## 综合：查询有brother的用户信息

select id,name,father
from
(select id,name,family_members['brother'] as father from t_person) tmp
where father is not null;

4.4.5.3 struct类型

structs: STRUCT<col_name : data_type, ...>

　　1) 假如有如下数据：

1,zhangsan,18:male:beijing
2,lisi,28:female:shanghai

　　其中的用户信息包含：年龄：整数，性别：字符串，地址：字符串 。设想用一个字段来描述整个用户信息，可以采用struct

　　2) 建表：

create table t_person_struct(id int,name string,info struct<age:int,sex:string,addr:string>)
row format delimited fields terminated by ','
collection items terminated by ':';

　　3) 查询

select * from t_person_struct;
select id,name,info.age from t_person_struct;

4.5 修改表定义

　　仅修改Hive元数据，不会触动表中的数据，用户需要确定实际的数据布局符合元数据的定义。

　　修改表名：

ALTER TABLE table_name RENAME TO new_table_name

　　示例：alter table t_1 rename to t_x;

 　　修改分区名：

alter table t_partition partition(department='xiangsheng',sex='male',howold=20) rename to partition(department='1',sex='1',howold=20);

　　添加分区：

alter table t_partition add partition (department='2',sex='0',howold=40);

　　删除分区：

alter table t_partition drop partition (department='2',sex='2',howold=24);

　　修改表的文件格式定义：

ALTER TABLE table_name [PARTITION partitionSpec] SET FILEFORMAT file_format

alter table t_partition partition(department='2',sex='0',howold=40 ) set fileformat sequencefile;

　　修改列名定义：

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENTcol_comment] [FIRST|(AFTER column_name)]  

alter table t_user change price jiage float first;

　　增加/替换列：

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type[COMMENT col_comment], ...)  

alter table t_user add columns (sex string,addr string);
alter table t_user replace columns (id string,age int,price float);

 

5 Hive查询语法

　　提示：在做小数据量查询测试时，可以让hive将mrjob提交给本地运行器运行，可以在hive会话中设置如下参数：

hive> set hive.exec.mode.local.auto=true;

5.1 基本查询示例

select * from t_access;
select count(*) from t_access;
select max(ip) from t_access;

5.2 条件查询

select * from t_access where access_time<'2017-08-06 15:30:20'
select * from t_access where access_time<'2017-08-06 16:30:20' and ip>'192.168.33.3';

5.3 join关联查询示例

　　假设有a.txt文件

a,1
b,2
c,3
d,4

　　假设有b.txt文件

a,xx
b,yy
d,zz
e,pp

　　进行各种join查询：

　　1、inner join（join）

select a.*,b.*
from t_a a inner join t_b b;

　　结果：

 

　　2、left outer join（left join）

select 
a.name as aname,
a.numb as anumb,
b.name as bname,
b.nick as bnick
from t_a a
left outer join t_b b
on a.name=b.name

　　结果：

　　3、right outer join（right join）

select 
a.name as aname,
a.numb as anumb,
b.name as bname,
b.nick as bnick
from t_a a
right outer join t_b b
on a.name=b.name

　　结果：

　　4、full outer join（full join）

select 
a.name as aname,
a.numb as anumb,
b.name as bname,
b.nick as bnick
from t_a a
full join t_b b
on a.name=b.name;

　　结果：

5.4 left semi join

　　hive中不支持exist/IN子查询，可以用left semi join来实现同样的效果：

select 
a.name as aname,
a.numb as anumb
from t_a a
left semi join t_b b
on a.name=b.name;

　　结果：

 

　　注意： left semi join的 select子句中，不能有右表的字段

5.5 group by分组聚合

select dt,count(*),max(ip) as cnt from t_access group by dt;
select dt,count(*),max(ip) as cnt from t_access group by dt having dt>'20170804';

select 
dt,count(*),max(ip) as cnt 
from t_access 
where url='http://www.edu360.cn/job'
group by dt having dt>'20170804';

　　注意： 一旦有group by子句，那么，在select子句中就不能有 （分组字段，聚合函数） 以外的字段 

　　## 为什么where必须写在group by的前面，为什么group by后面的条件只能用having

　　　　因为，where是用于在真正执行查询逻辑之前过滤数据用的 

　　　　having是对group by聚合之后的结果进行再过滤；

　　上述语句的执行逻辑： 

　　　　1、where过滤不满足条件的数据

　　　　2、用聚合函数和group by进行数据运算聚合，得到聚合结果 

　　　　3、用having条件过滤掉聚合结果中不满足条件的数据

5.6 子查询

select id,name,father
from
(select id,name,family_members['brother'] as father from t_person) tmp
where father is not null;

6 hive函数使用

　　小技巧：测试函数的用法，可以专门准备一个专门的dual表

　　其实：直接用常量来测试函数即可　　select substr("abcdefg",1,3);

　　hive的所有函数手册

6.1 常用内置函数

6.1.1 类型转换函数

select cast("5" as int) from dual;
select cast("2017-08-03" as date) ;
select cast(current_timestamp as date);

　　示例：

1	1995-05-05 13:30:59	1200.3
2	1994-04-05 13:30:59	2200
3	1996-06-01 12:20:30	80000.5

create table t_fun(id string,birthday string,salary string)
row format delimited fields terminated by ',';

select id,cast(birthday as date) as bir,cast(salary as float) from t_fun;

6.1.2 数学运算函数

select round(5.4) from dual;   ## 5
select round(5.1345,3) from dual;  ##5.135
select ceil(5.4) from dual; // select ceiling(5.4) from dual;   ## 6
select floor(5.4) from dual;  ## 5
select abs(-5.4) from dual;  ## 5.4
select greatest(3,5,6) from dual;  ## 6
select least(3,5,6) from dual;

 

　　示例：（有表如下）

 

select greatest(cast(s1 as double),cast(s2 as double),cast(s3 as double)) from t_fun2;

　　结果：

+---------+--+
|   _c0   |
+---------+--+
| 2000.0  |
| 9800.0  |
+---------+--+

select max(age) from t_person;    聚合函数
select min(age) from t_person;    聚合函数

6.1.3 字符串函数

substr(string, int start)   ## 截取子串
substring(string, int start)
示例：select substr("abcdefg",2) from dual;
substr(string, int start, int len)
substring(string, int start, int len)
示例：select substr("abcdefg",2,3) from dual;
concat(string A, string B...)  ## 拼接字符串
concat_ws(string SEP, string A, string B...)
示例：select concat("ab","xy") from dual;
select concat_ws(".","192","168","33","44") from dual;
length(string A)
示例：select length("192.168.33.44") from dual;
split(string str, string pat)
示例：select split("192.168.33.44",".") from dual; 错误的，因为.号是正则语法中的特定字符
select split("192.168.33.44","\\.") from dual;
upper(string str) ##转大写

6.1.4 时间函数

select current_timestamp;
select current_date;
## 取当前时间的毫秒数时间戳
select unix_timestamp();
## unix时间戳转字符串
from_unixtime(bigint unixtime[, string format])
示例：select from_unixtime(unix_timestamp());
select from_unixtime(unix_timestamp(),"yyyy/MM/dd HH:mm:ss");
## 字符串转unix时间戳
unix_timestamp(string date, string pattern)
示例： select unix_timestamp("2017-08-10 17:50:30");
select unix_timestamp("2017/08/10 17:50:30","yyyy/MM/dd HH:mm:ss");
## 将字符串转成日期date
select to_date("2017-09-17 16:58:32");

6.1.5 表生成函数

6.1.5.1 行转列函数：explode()

　　假如有以下数据：

1,zhangsan,化学:物理:数学:语文

2,lisi,化学:数学:生物:生理:卫生

3,wangwu,化学:语文:英语:体育:生物

　　映射成一张表：

create table t_stu_subject(id int,name string,subjects array<string>)
row format delimited fields terminated by ','
collection items terminated by ':';

　　使用explode()对数组字段“炸裂”。然后，我们利用这个explode的结果，来求去重的课程：

select distinct tmp.sub
from
(select explode(subjects) as sub from t_stu_subject) tmp;

 

6.1.5.2 表生成函数lateral view

select id,name,tmp.sub
from t_stu_subject lateral view explode(subjects) tmp as sub;

　　理解： lateral view 相当于两个表在join

　　左表：是原表

　　右表：是explode(某个集合字段)之后产生的表

　　而且：这个join只在同一行的数据间进行

　　那样，可以方便做更多的查询：

　　比如，查询选修了生物课的同学

select a.id,a.name,a.sub from
(select id,name,tmp.sub as sub from t_stu_subject lateral view explode(subjects) tmp as sub) a
where sub='生物';

6.1.6 集合函数

array_contains(Array<T>, value)  返回boolean值

示例：
select moive_name,array_contains(actors,'吴刚') from t_movie;
select array_contains(array('a','b','c'),'c') from dual; 
sort_array(Array<T>) 返回排序后的数组
 
示例：
select sort_array(array('c','b','a')) from dual;
select 'haha',sort_array(array('c','b','a')) as xx from (select 0) tmp; 
size(Array<T>)  返回一个int值
 
示例：
select moive_name,size(actors) as actor_number from t_movie; 
size(Map<K.V>)  返回一个int值
map_keys(Map<K.V>)  返回一个数组
map_values(Map<K.V>) 返回一个数组 

6.1.7 条件控制函数

6.1.7.1 case when

语法：
CASE   [ expression ]
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ...
       WHEN conditionn THEN resultn
       ELSE result
END
 
示例：
select id,name,
case
when age<28 then 'youngth'
when age>27 and age<40 then 'zhongnian'
else 'old'
end
from t_user;

6.1.7.2 IF

select id,if(age>25,'working','worked') from t_user;
select moive_name,if(array_contains(actors,'吴刚'),'好电影','rom t_movie;

6.1.8 json解析函数：表生成函数

json_tuple函数

示例：
select json_tuple(json,'movie','rate','timeStamp','uid') as(movie,rate,ts,uid) from t_rating_json;

　　产生结果：利用json_tuple从原始json数据表中，etl出一个详细信息表：

create table t_rate
as
select
uid,
movie,
rate,
year(from_unixtime(cast(ts as bigint))) as year,
month(from_unixtime(cast(ts as bigint))) as month,
day(from_unixtime(cast(ts as bigint))) as day,
hour(from_unixtime(cast(ts as bigint))) as hour,
minute(from_unixtime(cast(ts as bigint))) as minute,
from_unixtime(cast(ts as bigint)) as ts
from
(select
json_tuple(rateinfo,'movie','rate','timeStamp','uid') as(movie,rate,ts,uid)
from t_json) tmp;

6.1.9 分析函数：row_number() over()——分组TOPN

6.1.9.1 需求： 需要查询出每种性别中年龄最大的2条数据

　　有如下数据：

1,18,a,male

2,19,b,male

3,22,c,female

4,16,d,female

5,30,e,male

6,26,f,female

 

6.1.9.2 实现：使用row_number函数，对表中的数据按照性别分组，按照年龄倒序排序并进行标记

 　　hql代码：

select id,age,name,sex,
row_number() over(partition by sex order by age desc) as rank
from t_rownumber

　　产生结果：

　　然后，利用上面的结果，查询出rank<=2的即为最终需求

select id,age,name,sex
from
(select id,age,name,sex,
row_number() over(partition by sex order by age desc) as rank
from t_rownumber) tmp
where rank<=2;

6.2 自定义函数

6.2.1 需求：

　　需要对json数据表中的json数据写一个自定义函数，用于传入一个json，返回一个数据值的数组 。json原始数据表：

　　需要做ETL操作，将json数据变成普通表数据，插入另一个表中：

6.2.2 实现步骤：

　　1、开发JAVA的UDF类

public class ParseJson extends UDF{
    // 重载 ：返回值类型 和参数类型及个数，完全由用户自己决定
    // 本处需求是：给一个字符串，返回一个数组
    public String[] evaluate(String json) { 
    String[] split = json.split("\"");
    String[] res = new String[]{split[3],split[7],split[11],split[15]};
    return res;
    }
}

2、打jar包　　在eclipse中使用export即可

3、上传jar包到运行hive所在的linux机器

4、在hive中创建临时函数：

　　在hive的提示符中：

　　hive> add jar /root/jsonparse.jar;

　　然后，在hive的提示符中，创建一个临时函数：

　　hive>CREATE  TEMPORARY  FUNCTION  jsonp  AS  'cn.edu360.hdp.hive.ParseJson';

5、开发hql语句，利用自定义函数，从原始表中抽取数据插入新表

insert into table t_rate
select
split(jsonp(json),',')[0],
cast(split(jsonp(json),',')[1] as int),
cast(split(jsonp(json),',')[2] as bigint),
cast(split(jsonp(json),',')[3] as int)
from
t_rating_json;

　　注：临时函数只在一次hive会话中有效，重启会话后就无效 

　　如果需要经常使用该自定义函数，可以考虑创建永久函数：

　　拷贝jar包到hive的类路径中：

　　　　cp wc.jar apps/hive-1.2.1/lib/

　　创建了：

　　　　create function pfuncx as 'com.doit.hive.udf.UserInfoParser';

 

　　删除函数：

DROP  TEMPORARY  FUNCTION  [IF  EXISTS] function_name  
DROP FUNCTION[IF EXISTS] function_name