分区表 UDF

===分区表=================================================

分区表实例：按照时间来生成和管理日志文件
20160830
    ** 2016083001.log
    ** 2016083002.log
    ** 2016083003.log
    ...
20160831
    ** 2016083101.log
    ** 2016083102.log
    ...
20160901
20160902
...

--创建分区表
create table emp_part(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by(date string)
row format delimited fields terminated by '\t';    

--向分区表加载数据
load data local inpath 'emp.txt' into table emp_part partition(date='20160830');
load data local inpath 'emp.txt' into table emp_part partition(date='20160831');
load data local inpath 'emp.txt' into table emp_part partition(date='20160901');

--分区表查询
hive (mydb)> select * from emp_part;
hive (mydb)> select * from emp_part where date='20160830';

----二级分区表------------------

create table if not exists track_log(
id              string,
url             string,
referer         string,
keyword         string,
type            string,
guid            string,
pageId          string,
moduleId        string,
linkId          string,
attachedInfo    string,
sessionId       string,
trackerU        string,
trackerType     string,
ip              string,
trackerSrc      string,
cookie          string,
orderCode       string,
trackTime       string,
endUserId       string,
firstLink       string,
sessionViewNo   string,
productId       string,
curMerchantId   string,
provinceId      string,
cityId          string,
fee             string,
edmActivity     string,
edmEmail        string,
edmJobId        string,
ieVersion       string,
platform        string,
internalKeyword string,
resultSum       string,
currentPage     string,
linkPosition    string,
buttonPosition  string
)
partitioned by (date string,hour string)
row format delimited fields terminated by '\t';    

--加载数据
load data local inpath '/home/tom/2015082818' into table mydb.track_log partition(date='20150828',hour='18');
load data local inpath '/home/tom/2015082819' into table mydb.track_log partition(date='20150828',hour='19');

--查询
select * from mydb.track_log where date='20150828';
select * from mydb.track_log where date='20150828' and hour='18';
select * from mydb.track_log where date='20150828' and hour='18' limit 10;

-----------

查看一个分区表有几个分区    
hive (mydb)> show partitions track_log;    
删除一个分区表的分区
hive (mydb)> alter table track_log drop partition(date='20150828',hour='19');  --同时删除HDFS上的对应分区目录

----分区表 + 外部表-----------

create external table track_log_ext(
id              string,
url             string,
referer         string,
keyword         string,
type            string,
guid            string,
pageId          string,
moduleId        string,
linkId          string,
attachedInfo    string,
sessionId       string,
trackerU        string,
trackerType     string,
ip              string,
trackerSrc      string,
cookie          string,
orderCode       string,
trackTime       string,
endUserId       string,
firstLink       string,
sessionViewNo   string,
productId       string,
curMerchantId   string,
provinceId      string,
cityId          string,
fee             string,
edmActivity     string,
edmEmail        string,
edmJobId        string,
ieVersion       string,
platform        string,
internalKeyword string,
resultSum       string,
currentPage     string,
linkPosition    string,
buttonPosition  string
)
partitioned by (date string,hour string)
row format delimited fields terminated by '\t';    

alter table track_log_ext add partition(date='20150828', hour='18')
location '/user/hive/warehouse/mydb.db/track_log/date=20150828/hour=18';
alter table track_log_ext add partition(date='20150828', hour='19')
location '/user/hive/warehouse/mydb.db/track_log/date=20150828/hour=19';

select * from track_log_ext where date='20150828' and hour='18' limit 10;
select * from track_log_ext where date='20150828' and hour='19' limit 10;

Hive分区表
    ** 创建表的时候只需要指定分区字段,加载数据时再指定具体分区
    ** 对大表数据进行分目录存储,提高了数据存储的安全性
    ** 最重要的是：使用分区表能大大的提高查询效率

---------------------------

创建表的几种方式：

第一种（create）：
create table mydb.emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)row format delimited fields terminated by '\t';

第二种（复制表结构）：
create table emp1 like emp;

第三种（从基表中抽取出一部分构建一张新表）：
create table emp2 as select * from emp limit 10;

----------------------------

导入数据的几种方式：

1、加载本地文件到Hive表
load data local inpath 'path/file' into table 表名;

2、加载HDFS文件到Hive表
load data inpath 'path/file' into table 表名;

3、覆盖表中已有数据
load data local inpath 'path/file' overwrite into table 表名;

4、通过select创建表
create table emp1 as select * from emp;

5、用insert命令加载
insert into table 表名 select * from emp;       --追加
insert overwrite table 表名 select * from emp;  --覆盖
例：
create table dept_sal(
deptno string,
sal string
)row format delimited fields terminated by '\t';
insert into table dept_sal select deptno,sum(sal) from emp group by deptno;
insert overwrite table dept_sal select deptno,sum(sal) from emp group by deptno;

6、创建表时通过location加载
create table 表名(
    ...
)
partitioned by         --注意顺序
row format delimited
location "..." ;

---------------------------

表数据导出几种方式：

1、insert导出到本地
insert overwrite local directory "/home/tom/emp"
row format delimited fields terminated by '\t' select * from emp;

2、hive -e
$ hive -e "select * from mydb.emp"  > /home/tom/emp_0.log

3、insert导出到HDFS
insert overwrite directory "/path" select * from emp;  --不可以使用row format delimited

4、sqoop工具（后面讲）
Hive表数据 --> mysql表
    
===================================================

Hive基本查询语句
** 查询具体的某个某些字段
    select empno,ename from emp;
** where
    select * from emp where deptno=20;
** limit
    select * from track_log limit 10 ;       
** distinct
   select distinct(deptno) from mydb.emp ;
** < > = >= <=  !=
    select * from emp where sal>5000 ;
** and 、between ... and 、in 、 not in
** like ( %  _ )

Hive常用函数
** 查看有哪些函数
    > show functions;
** 聚合函数：max  min  sum  avg  count    
** 查看具体某个函数的用法    
    > desc function extended 函数名;    
    
** group by 分组字段
a)显示每个部门最高薪资
select deptno,max(sal) from emp group by deptno ;

b)显示部门名称,部门最高薪资
> select dname,max(sal) from emp a join dept b on a.deptno=b.deptno group by dname;
等价于：select dname,max(sal) from emp a,dept b where a.deptno=b.deptno group by dname;

c)显示部门名称,部门最高薪资,部门所在的城市
select dname,loc,maxsal from (select deptno,max(sal) maxsal from emp group by deptno) a
join dept b on a.deptno=b.deptno;
        
** having 条件
显示部门名称,部门最高薪资,且薪资必须大于3000的   --需要部门名唯一，该语句才正确
> select dname,max(sal) from emp a join dept b on a.deptno=b.deptno group by dname having max(sal)>3000;

** 随机函数
select rand();

** 字符串连接
> select concat(empno,ename) from emp;
> select concat(empno,"_",ename) from emp;

** 子字符串
> select substr(hiredate,1,4) from emp;  --起点(字符串第1个字符下标为1),长度

** upper、lower函数

** 求日期
> select day(hiredate) from emp;  
** 求小时
> select hour("2016-12-01 12:01:01");

** 返回自1970-1-1 8:00:00开始到当前系统时间为止的秒数
> select unix_timestamp("2016-12-01 12:01:01");
> select unix_timestamp("1970-01-01 08:00:00");   --返回值为0

** 转换成时间
> select from_unixtime(1480564861);

** 类型转换
> select cast(123456789/1000 as int);

** case when
a)
select ename, case when comm is null then sal else comm+sal end  from emp;

b)
select ename,                          
case when sal<1000 then "lower"   --常量      
when sal>=1000 and sal<=2000 then "pass"
when sal>2000 then "high"               
else "OK" end
from emp;

c)
select ename,                           
case when sal<1000 then deptno    --变量      
when sal>=1000 and sal<=2000 then comm  
else null end
from emp;

-----------------------------
    
Hive中Join    
    ** 内链接  
    ** 显示部门名称,部门最高薪资    --需要部门名唯一
    > select max(b.dname),max(sal) from emp a inner join dept b on a.deptno = b.deptno group by a.deptno;
    
    ** 左链接、右链接、全连接（mysql暂时不支持全连接）
    > select max(b.dname),max(sal) from emp a right join dept b on a.deptno = b.deptno group by a.deptno;

Join优化：
    1、无论有多少张表进行连接，一定按照从小到大的顺序书写，即要将大表放在join的右边，因为hive默认大表在右边，
       它总是按照从左到右的顺序进行连接操作，也就是先将左表缓存起来，然后在扫描右表的时候进行连接
    2、如果有一个表足够小，可以完全载入内存，那么可以将其标注为MAPJOIN，系统会在map执行过程中，进行join操作
       这样可以减少reduce操作，提高效率。MAPJOIN不支持右连接和全连接。
       如：SELECT /*+ MAPJOIN(a) */ a.key, b.value FROM a JOIN b ON a.key = b.key    

====hiveserver2和beeline===============================================

*** hiveserver2是一个提供了远程hive服务的服务端进程
*** beeline是远程命令行客户端
*** hiveserver2可以供多个客户端同时访问
*** 参考文档：wiki--Administrator Documentation和User Documentation

配置：
** 修改hive-site.xml文件
** thrift是一个序列化框架，其优势在于支持多种语言和相对成熟，最初由facebook开发用做系统内各语言间的RPC通信。
<property><!--不变-->
  <name>hive.server2.thrift.port</name>
  <value>10000</value>
</property>
<property>
  <name>hive.server2.thrift.bind.host</name>
  <value>blue01.mydomain</value>
</property>
<property><!--bug，要把5000L改为5000-->
  <name>hive.server2.long.polling.timeout</name>
  <value>5000</value>
</property>

启动服务：
$ bin/hive --service hiveserver2 &  --最后的'&'表示服务在后台运行，可以去掉

查看服务进程pid
$ netstat -antp|grep 10000
-a (all)显示所有套接字的状态
-n 以网络IP地址代替名称，显示出网络连接情形
-t (tcp)仅显示tcp相关选项
-u (udp)仅显示udp相关选项
-p 显示建立相关链接的进程名
-l 仅列出处于Listen(监听)状态的服务

关闭进程：(hiveserver2没有关闭命令，需要用kill)
--kill就是给某个进程发送了一个信号，默认发送的信号是SIGTERM。
--而kill -9发送的信号是SIGKILL，不会被系统阻塞，所以kill -9能顺利杀掉进程。
$ kill -9 9399

通过客户端访问hiveserver2服务：
$ bin/beeline
beeline> help
beeline> !connect jdbc:hive2://192.168.158.100:10000 lxl 123456  --Linux用户名、密码
> show databases;
> use mydb;
> show tables;
> select * from emp;
> !quit

====UDF=================================================

UDF(user defined function 用户自定义函数)

UDF函数开发:
    ** 继承UDF类
    ** 实现一个名为evaluate方法(该方法不属于UDF类，所以不是重写，但必须实现，用于被Hive调用)
    ** 函数类型不能是void（必须要有返回类型,可以返回null,但是返回类型不能为void）
    ** 建议使用Text/LongWritable

-------------------------------
    
** 新建maven工程：com.myblue.myjava
** 修改pom依赖包
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-client</artifactId>
        <version>2.5.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>0.13.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>0.13.1</version>
    </dependency>
    
方法一：
直接上传现成的repository.tar.gz,并解压到/home/tom/.m2/

方法二：（从网上下载）
1、 在百度上搜索“maven 阿里云”，找到可用的镜像地址
    修改maven配置文件settings.xml（/opt/modules/apache-maven-3.0.5/conf）
    <mirror>
      <!--This sends everything else to /public -->
      <id>nexus</id>
      <mirrorOf>*</mirrorOf>
      <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
    </mirror>
    <mirror>
      <!--This is used to direct the public snapshots repo in the
          profile below over to a different nexus group -->
      <id>nexus-public-snapshots</id>
      <mirrorOf>public-snapshots</mirrorOf>
      <url>http://maven.aliyun.com/nexus/content/repositories/snapshots/</url>
    </mirror>
    同时修改.m2目录下的settings.xml，将该文件拷贝到.m2目录：$ cp settings.xml /home/tom/.m2/    
2、 重新更新maven工程（右键--maven--Update Project）
    若是出现'.../XXX/aaa.jar' in project 'myjava' cannot be read or is not a valid ZIP file错误，
    则将XXX目录删除，重新更新maven工程，再次下载即可

-------------------------------------------

package com.myblue.myjava;

import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class Lower extends UDF {

    public Text evaluate(Text str){
        if(null == str){
            return null;
        }
        //若是为空
        if(StringUtils.isBlank(str.toString())){
            return null;
        }
        return new Text(str.toString().toLowerCase());
    }
    
    public static void main(String[] args) {
        System.out.println(new Lower().evaluate(new Text("ABCDEFG")));
    }
}

编写使用UDF
    1、使用eclipse导出jar包，文件名右键--Export--java--jar file，
       只要选择输出目标（/home/tom/myjars/lower.jar）即可
    2、导入jar到Hive上：
    hive> add jar /home/tom/myjars/lower.jar;
    3、创建临时函数：
    hive> CREATE TEMPORARY FUNCTION my_lower AS 'com.myblue.myjava.Lower';
    4、使用指定函数：
    hive> show functions;
    hive> select my_lower(ename) from emp;  --hive自带了一个lower，可以看到我们写的函数和系统函数功能一样
    hive> select my_lower(dname) from dept;
    ** 注意：若是hive重启，临时函数就不能用了。若想要再次使用，需得重新导入jar，再次生成函数才可以

-------------------------------------------

UDF应用：    
    从url里面取出需要的字符串:  
    http://cms.yhd.com/sale/IhSwTYNxnzS?tc=ad.0.0.15116-32638141.1&tp=1.1.708.0.3.LEHaQW1
    取出/sale后面的字符串IhSwTYNxnzS：sale后面的字符串表示销售ID
    
package com.myblue.myjava;

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.hadoop.hive.ql.exec.UDF;

public class GetSaleName extends UDF {
    
    public String evaluate(String url) {
        String str = null;
        Pattern p = Pattern.compile("sale/[a-zA-Z0-9]+");
        Matcher m = p.matcher(url);
        if (m.find()) {
            str = m.group(0).toLowerCase().split("/")[1];
        }
        return str;
    }

    public static void main(String[] args) {
        String url = "http://cms.yhd.com/sale/IhSwTYNxnzS?tc=ad.0.0.15116-32638141.1&tp=1.1.708.0.3.LEHaQW1";
        GetSaleName gs = new GetSaleName();
        System.out.println(gs.evaluate(url));
    }
}
hive> add jar /home/tom/myjars/getsalename.jar;
hive> CREATE TEMPORARY FUNCTION my_getsalename AS 'com.myblue.myjava.GetSaleName';
hive> select my_getsalename(url) from track_log;

---------------------------------------------

UDF函数注意事项：
    ** 临时UDF函数只要退出当前hive就会失效
    ** 一般我们不会把UDF函数设置为永久生效
    ** 日常应用：当我们某个job任务需要用到函数时,我们才会添加，也就是说函数是跟着业务(job)走的
    ** 实际应用中,Hive会有多个Job任务，每个job任务都有自己对应的函数、以及hql语句;
       建议为每个job任务编写脚本文件，如：job01.hql、job02.hql、job03.hql
        bin/hive -f job1.hql
        bin/hive -f job2.hql