Hive，

Hive

1.HIVE是什么？

HIVE是一个可以将sql翻译为MR程序的工具
HIVE支持用户将HDFS上的文件映射为表结构，然后用户就可以输入SQL对这些表（HDFS上的文件）进行查询分析
HIVE将用户定义的库、表结构等信息存储hive的元数据库（可以是本地derby，也可以是远程mysql）中

2.HIVE的用途？

解放大数据分析程序员，不用自己写大量的mr程序来分析数据，只需要写sql脚本即可
HIVE可用于构建大数据体系下的数据仓库

3.HIVE的使用方式？

方式1：可以交互式查询： 
  **  bin/hive   ----->    hive>select * from t_test;
  ** 将hive启动为一个服务：  bin/hiveserver2(后台启动：nohup hiveserver2 1>/dev/null 2>&1 &)   ，然后可以在任意一台机器上使用beeline客户端连接hive服务，进行交互式查询。具体配置如下。

hive使用beeline配置远程连接（来源于：https://www.cnblogs.com/zuizui1204/p/9999652.html）

0，安装hive,并将mysql驱动jar导入到lib目录中。

1，在hive服务的安装节点的hive-site.xml配置文件中添加以下配置，配置beeline的远程访问用户名和密码

<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>123456</value>
<description>password to use against metastore database</description>
</property>
</configuration>

2，保证hadoop集群环境正常运行，另外重要的一点是要在hive的服务安装节点开启hiveserver2

　　直接运行hiveserver2会一直停留在打印hive的操作日志的状态，可以改为后台运行

　　后台运行命令为：nohup hiveserver2 > /dev/null 2>&1 &

　　nohup后台运行的命令怎么关掉呢？一个方法为：jobs -l 可以看到运行的进程号，kill -9 进程号

3，把hadoop的安装文件夹和hive的安装文件夹copy到要配置hive客户端的这个linux上面，并配置环境变量/etc/profile

　　注意：不配置HADOOP_HOME，运行beeline会提示缺少HADOOP_HOME参数，你可以理解为需要连接集群的地址，如master:9000的相关配置是在hdfs-site.xml中，source /etc/profile重新加载配置文件。

　　hive是需要这些信息连接集群环境的，毕竟hive是在操作hdfs上的文件

4，配置命令；经过上面那三个步骤，其实你已经可以使用beeline -u jdbc:hive2://192.168.1.89:10000 -n root --color=true --silent=false 来远程登录hive了

　　但是看着这一长串的命令，懒癌犯了。。。配置一个简单点的命令吧。想到的一个办法就是alias自定义命令，并把它配置到环境变量中，在~/.bashrc中添加

　　alias beeline="/opt/softWare/hive/apache-hive-1.2.2-bin/bin/beeline -u jdbc:hive2://ip:10000  -n root --color=true --silent=false"

　　source ~/.bashrc即可，需要注意的是beeline使用了绝对路径，因为我新生成的命令就叫beeline，为避免冲突，写了绝对路径，当然你可以改成其他命令

5，最后测试一下，敲出beeline命令即可进入到远程hive的命令行

  
方式2：可以将hive作为命令一次性运行：
  **  bin/hive -e "sql1;sql2;sql3;sql4"
  **  事先将sql语句写入一个文件比如 q.hql ，然后用hive命令执行：　　bin/hive -f q.hql

方式3：可以将方式2写入一个xxx.sh脚本中  

4.HIVE的DDL语法

建库：    create database db1;   ---> hive就会在/user/hive/warehouse/下建一个文件夹： db1.db
建内部表：    use db1;
create table t_test1(id int,name string,age int,create_time bigint)
row format delimited
fields terminated by '\001';

建表后，hive会在仓库目录中建一个表目录：  /user/hive/warehouse/db1.db/t_test1

建外部表：    
create external table t_test1(id int,name string,age int,create_time bigint)
row format delimited
fields terminated by '\001'
location '/external/t_test';

删除表

drop table 表名;如果要永久性删除，不准备再恢复：

导入数据：
本质上就是把数据文件放入表目录；
可以用hive命令来做：
hive>  load data [local] inpath '/data/path' [overwrite] into table t_test;

建分区表：
分区的意义在于可以将数据分子目录存储，以便于查询时让数据读取范围更精准；
create table t_test1(id int,name string,age int,create_time bigint)
partitioned by (day string,country string)
row format delimited
fields terminated by '\001';

插入数据到指定分区：
hive> load data [local] inpath '/data/path1' [overwrite] into table t_test partition(day='2017-06-04',country='China');
hive> load data [local] inpath '/data/path2' [overwrite] into table t_test partition(day='2017-06-05',country='China');
hive> load data [local] inpath '/data/path3' [overwrite] into table t_test partition(day='2017-06-04',country='England');

导入完成后，形成的目录结构如下：
/user/hive/warehouse/db1.db/t_test1/day=2017-06-04/country=China/...
/user/hive/warehouse/db1.db/t_test1/day=2017-06-04/country=England/...
/user/hive/warehouse/db1.db/t_test1/day=2017-06-05/country=China/...

5.HIVE的DML

基本查询语法跟标准sql基本一致
SELECT FIELDS,FUNCTION(FIELDS) 
FROM T1 
JOIN T2
WHERE CONDITION
GROUP BY FILEDS
HAVING CONDTION
ORDER BY FIELDS DESC|ASC

6.HIVE的内置函数

时间处理函数：
from_unixtime(21938792183,'yyyy-MM-dd HH:mm:ss')  -->   '2017-06-03 17:50:30'

类型转换函数：
from_unixtime(cast('21938792183' as bigint),'yyyy-MM-dd HH:mm:ss')

字符串截取和拼接
substr("abcd",1,3)  -->   'abc'
concat('abc','def')  -->  'abcdef'

Json数据解析函数
get_json_object('{\"key1\":3333，\"key2\":4444}' , '$.key1')  -->  3333

json_tuple('{\"key1\":3333，\"key2\":4444}','key1','key2') as(key1,key2)  --> 3333, 4444

url解析函数
parse_url_tuple('http://www.edu360.cn/bigdata/baoming?userid=8888','HOST','PATH','QUERY','QUERY:userid')
--->     www.edu360.cn      /bigdata/baoming     userid=8888   8888

函数：explode  和 lateral view
可以将一个数组变成列

加入有一个表，其中的字段为array类型表数据：
1,zhangsan,数学:语文:英语:生物
2,lisi,数学:语文
3,wangwu,化学:计算机:java编程

建表：
create table t_xuanxiu(uid string,name string,kc array<string>)
row format delimited
fields terminated by ','
collection items terminated by ':';

explode效果示例：
select explode(kc) from t_xuanxiu where uid=1;
数学
语文
英语
生物

lateral view 表生成函数
hive> select uid,name,tmp.* from t_xuanxiu 
    > lateral view explode(kc) tmp as course;
OK
1       zhangsan        数学
1       zhangsan        语文
1       zhangsan        英语
1       zhangsan        生物
2       lisi    数学
2       lisi    语文
3       wangwu  化学
3       wangwu  计算机
3       wangwu  java编程

 

利用explode和lateral view 实现hive版的wordcount,有以下数据：
a b c d e f g
a b c
e f g a
b c d b

对数据建表：
create table t_juzi(line string) row format delimited;

导入数据：
load data local inpath '/root/words.txt' into table t_juzi;

** ***** ******** ***** ******** ***** ******** wordcount查询语句：***** ******** ***** ******** ***** ******** 
select a.word,count(1) cnt
from 
(select tmp.* from t_juzi lateral view explode(split(line,' ')) tmp as word) a
group by a.word
order by cnt desc;

 

常用于求分组TOPN

有如下数据：
zhangsan,kc1,90
zhangsan,kc2,95
zhangsan,kc3,68 
lisi,kc1,88
lisi,kc2,95
lisi,kc3,98

建表：
create table t_rowtest(name string,kcId string,score int)
row format delimited
fields terminated by ',';

导入数据：

利用row_number() over() 函数看下效果：
select *,row_number() over(partition by name order by score desc) as rank from t_rowtest;
+-----------------+-----------------+------------------+----------------------+--+
| t_rowtest.name  | t_rowtest.kcid  | t_rowtest.score  | rank                 |
+-----------------+-----------------+------------------+----------------------+--+
| lisi            | kc3               | 98               | 1                    |
| lisi            | kc2               | 95               | 2                    |
| lisi            | kc1               | 88               | 3                    |
| zhangsan        | kc2               | 95               | 1                    |
| zhangsan        | kc1               | 90               | 2                    |
| zhangsan        | kc3               | 68               | 3                    |
+-----------------+-----------------+------------------+----------------------+--+

从而，求分组topn就变得很简单了：

select name,kcid,score
from
(select *,row_number() over(partition by name order by score desc) as rank from t_rowtest) tmp
where rank<3;
+-----------+-------+--------+--+
|   name    | kcid  | score  |
+-----------+-------+--------+--+
| lisi      | kc3     | 98     |
| lisi      | kc2     | 95     |
| zhangsan  | kc2     | 95     |
| zhangsan  | kc1     | 90     |
+-----------+-------+--------+--+

 

hive自定义parse_user_info() 函数
实现步骤：
1、写一个java类实现函数所需要的功能
public class UserInfoParser extends UDF{    
    // 1,zhangsan:18-1999063117:30:00-beijing
    public String evaluate(String line,int index) {
        String newLine = line.replaceAll(",", "\001").replaceAll(":", "\001").replaceAll("-", "\001");
        StringBuilder sb = new StringBuilder();
        String[] split = newLine.split("\001");
        StringBuilder append = sb.append(split[0])
        .append("\t")
        .append(split[1])
        .append("\t")
        .append(split[2])
        .append("\t")
        .append(split[3].substring(0, 8))
        .append("\t")
        .append(split[3].substring(8, 10)).append(split[4]).append(split[5])
        .append("\t")
        .append(split[6]);
        
        String res = append.toString();

        return res.split("\t")[index];
    }
}

2、将java类打成jar包: d:/up.jar
3、上传jar包到hive所在的机器上  /root/up.jar
4、在hive的提示符中添加jar包
hive>  add jar /root/up.jar;
5、创建一个hive的自定义函数名 跟  写好的jar包中的java类对应
hive>  create temporary function parse_user_info as 'com.doit.hive.udf.UserInfoParser';