hive新手学习随笔

一、回顾
    1、hive基于Hadoop的（存储HDFS，计算MR）
    2、sql on hadoop概念
            -》简化开发的操作
            -》提升业务的效率
    3、描述表的三种方式
            desc tb_name;
            desc extended tb_name;
            desc formatted tb_name;
    4、hive默认情况下创建的表，类似都为： MANAGED_TABLE 管理表
    5、函数方法
         show functions;查看系统中的方法
         desc function upper;
         desc function extended upper;
         注意描述一个方法，需要加上function关键词，与描述表进行区分
        
二、hive自定义日志log文件
    1、对hive下的conf目录下的mv hive-log4j.properties.template hive-log4j.properties重命名
    2、修改hive.log.dir=/opt/moduels/hive-0.13.1-bin/logs，指定自定义的log路径
                -》注意先创建，默认是在/tmp/***用户名下

三、hive指定数据库名和列名
    1、修改hive-site.xml文件
    -》指定显示列名
    <property>
      <name>hive.cli.print.header</name>
      <value>true</value>
    </property>
    -》指定显示数据库名
    <property>
      <name>hive.cli.print.current.db</name>
      <value>true</value>
    </property>

四、hive的常用shell参数
    使用bin/hive -help查看参数
    1、指定默认链接的数据库
    $ bin/hive --database hadoop14
    2、在linuxshell命令行执行HQL语句（）
    $ bin/hive -e 'show databases'
    $ bin/hive -e 'show databases' > hivetest.txt
    将结果重定向到一个指定的文件中，使用追加或者覆盖符号
    3、在linuxshell命令行执行一个写有sql语句的文件
    $ bin/hive -f /opt/datas/hive.sql
    4、只针对于当前shell生效的更改配置的参数
    $ bin/hive --hiveconf hive.cli.print.current.db=false
    5、查看当前参数的设置的值是什么
    set hive.cli.print.current.db;
    set hive.cli.print.current.db=false;
    同样也可以更改当前参数的值，只针对于当前shell生效

五、hive数据库的常用操作
1、LOCATION指定数据库表的位置
建库：
create database if not exists db01_loc LOCATION '/locate';
建表：
create table db01_loc.tb01(
name string
)row format delimited fields terminated by '\t';

2、
建库：
create database if not exists db02;
删除数据库：
drop database db02;
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
drop database db01_loc CASCADE;
删除非空的数据库

六、hive的表的常用操作

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format]
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

表的第一种创建方式：普通创建
create table if not exists stu_info(
num int,
name string
)
row format delimited fields terminated by '\t'
stored as textfile;
load data local inpath '/opt/datas/student.txt' into table stu_info;
清空表的内容,保留了表的结构
truncate table student;

删除表：
drop table if exists student;

create table if not exists student(
num int,
name string
)
row format delimited fields terminated by '\t'
stored as textfile;
从本地加载
load data local inpath '/opt/datas/student.txt' into table student;

从HDFS加载
load data inpath '/student.txt' into table student;

本地加载和HDFS加载的区别，一个本地的复制拷贝，一个是移动数据文件的位置到对应的表目录下


表的第二种创建方式：子查询
create table stu_as as select name from student;
特点：将子查询的结构赋予一张新的表


表的第三种创建方式：like方式
create table stu_like like student;
特点：复制表的结构


建库：
create database if not exists db_emp;

员工表：
create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
row format delimited fields terminated by '\t'
stored as textfile;
load data local inpath '/opt/datas/emp.txt' into table emp;

load data local inpath '/opt/datas/emp.txt' overwrite into table emp;
先删除数据，后加载数据

部门表：
create table dept(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t'
stored as textfile;
load data local inpath '/opt/datas/dept.txt' into table dept;

七、hive的外部表
    1、EXTERNAL-》外部表，另一种表的类型
    2、举例：
    Web服务器-》生成大量日志文件
                -》20170513.log文件
    情况：多个部门要分析多个不同的指标，建不同的表，但分析的数据源文件只有一份


create table emp1(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
row format delimited fields terminated by '\t'
LOCATION '/user/hive/warehouse/db_emp.db/emp';
load data local inpath '/opt/datas/emp.txt' into table emp;


show tables;->mysql找table
如果没有列出表，说明表的元数据被删除了，然后再删除表的文件夹


创建外部表：
create EXTERNAL table dept_ext(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t'
LOCATION '/user/hive/warehouse/db_emp.db/dept';

管理表删除的时候是删除元数据和表的对应文件夹
外部表删除的时候只删除元数据

首先创建管理表，然后可以创建多个外部表

作用：保证数据的安全性

八、hive中的分区表

举例：

    -》先查询再过滤
    WEB服务器，存储日志文件，分析
            -》logs文件夹
                -》20170513.log
                -》20170514.log
                -》20170515.log
                -》20170516.log
    使用hive去分析前一天的数据
    第一种方式：将所有的日志文件放到一个文件夹下，使用一张表进行加载
    在hive输入SQL-》找到元数据-》找到table以及找到HDFS上table对应的文件夹
    -》将文件夹中的数据返回-》封装给MR
    select * from logs where date='20170513';
    
    -》直接加载，hive中的分区概念
    WEB服务器，存储日志文件，分析
            -》logs文件夹
                -》20170513文件夹
                    -》20170513.log
                -》20170514文件夹
                    -》20170514.log
                -》20170515文件夹
                    -》20170515.log
                -》20170516文件夹
                    -》20170516.log
    select * from logs where date='20170513';

创建分区表：分区表的分区是虚拟逻辑的
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 '/opt/datas/emp.txt' into table emp_part partition (date='20170513');
load data local inpath '/opt/datas/emp.txt' into table emp_part partition (date='20170512');


select * from emp_part where date='20170513';


-》logs文件夹
                -》20170513文件夹
                    -》20170513.log
                -》20170514文件夹
                    -》20170514.log
                -》20170515文件夹
                    -》20170515.log
                -》20170516文件夹
                    -》20170516.log

create table emp_part3(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (date string,hour string)
row format delimited fields terminated by '\t';

load data local inpath '/opt/datas/emp.txt' into table emp_part3 partition (date='20170513',hour='10');
load data local inpath '/opt/datas/emp.txt' into table emp_part3 partition (date='20170513',hour='11');

作用：提高查询检索的效率


九、分析函数&窗口函数
    作用：对分组后的数据进行处理
create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
row format delimited fields terminated by '\t';
load data local inpath '/opt/datas/emp.txt' into table emp;


需求：查看部门10的所有员工，按照薪资进行降序排列，默认情况下是升序的
select * from emp where deptno='10' order by sal desc;

emp.empno       emp.ename       emp.job emp.mgr emp.hiredate    emp.sal emp.comm        emp.deptno
7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10
7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10
7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10


需求：按照所有部门进行分组，按照薪资进行降序排列，每个部门薪资最高的那个人显示在最后一列

select empno,ename,deptno,sal,max(sal) over (partition by deptno order by sal desc) as max_as from emp;

(partition by deptno order by sal desc)这部分进行了分组，然后针对每个分组进行排序
如果不使用这种分析函数之类的去分析的话，排序和分组都是全局的

empno   ename   deptno  sal     max_as
7839    KING    10      5000.0  5000.0
7782    CLARK   10      2450.0  5000.0
7934    MILLER  10      1300.0  5000.0

7788    SCOTT   20      3000.0  3000.0
7902    FORD    20      3000.0  3000.0
7566    JONES   20      2975.0  3000.0
7876    ADAMS   20      1100.0  3000.0
7369    SMITH   20      800.0   3000.0

7698    BLAKE   30      2850.0  2850.0
7499    ALLEN   30      1600.0  2850.0
7844    TURNER  30      1500.0  2850.0
7654    MARTIN  30      1250.0  2850.0
7521    WARD    30      1250.0  2850.0
7900    JAMES   30      950.0   2850.0


select empno,ename,deptno,sal,row_number() over (partition by deptno order by sal desc) as rn from emp;

empno   ename   deptno  sal     rn
7839    KING    10      5000.0  1
7782    CLARK   10      2450.0  2
7934    MILLER  10      1300.0  3

7788    SCOTT   20      3000.0  1
7902    FORD    20      3000.0  2
7566    JONES   20      2975.0  3
7876    ADAMS   20      1100.0  4
7369    SMITH   20      800.0   5

7698    BLAKE   30      2850.0  1
7499    ALLEN   30      1600.0  2
7844    TURNER  30      1500.0  3
7654    MARTIN  30      1250.0  4
7521    WARD    30      1250.0  5
7900    JAMES   30      950.0   6

select empno,ename,deptno,sal from (select empno,ename,deptno,sal,row_number() over (partition by deptno order by sal desc) as rn from emp) tmp where rn <3;

empno   ename   deptno  sal
7839    KING    10      5000.0
7782    CLARK   10      2450.0

7788    SCOTT   20      3000.0
7902    FORD    20      3000.0

7698    BLAKE   30      2850.0
7499    ALLEN   30      1600.0

LEAD向后和LAG向前 （列、偏移量、默认值）

id        name        lag（name，1,0）
1        jack        0
2        tom            0

十、hive的数据导入方式
    1、load方式，本地
    load data local inpath 'local_path' into table tb_name;
        从本地复制了文件到表的路径下
        应用场景：大部分的使用，文件几乎都是默认现在本地的
    2、load方式，HDFS
    load data inpath 'hdfs_path' into table tb_name;
    将文件移动到了表的路径下
        应用场景：更适合大数据量的存储
    3、load方式，overwrite
    load data inpath 'hdfs_path' overwrite into table tb_name;
        应用场景：适合一些重复写入的表（临时表），作为一个过渡使用
    4、子查询方式，as
        应用场景：对于数据查询结果的保存
    5、insert方式
        传统关系型数据库中，insert是插入一个值
        在hive中insert into table后面还是跟一个语句（select语句）
    insert into table select sql；
    举例：
create table emp_insert like emp;

insert into table emp_insert select * from emp;
        应用场景：和子查询类似
    
    6、location
        指定一个文件夹，然后将数据导入进去

十一、hive数据的导出
    1、insert方式
    格式：insert overwrite [local] directory 'path' select sql;
    数据导出到本地
    insert overwrite local directory '/opt/datas/emp_in01' select * from emp;
    -》输出的目标可以提前存在，底层实现的时候，先删除再重新创建
    -》指定分隔符
    insert overwrite local directory '/opt/datas/emp_in01' row format delimited fields terminated by '\t' select * from emp;
    -》HDFS
    insert overwrite directory '/emp_insert' select * from emp;
    -》注意：上一级的父目录必须存在
    2、HDFS SHELL命令 -get
    bin/hdfs dfs -get hdfs_path local_path
    3、在Linux的命令行使用hive的-e -f参数，将输出重定向保存到本地文件
    4、sqoop方式
    5、hive支持export和import
    -》export
    export table tb_name to 'hdfs_path'
    -》import
    import table tb_name from 'hdfs_path'

十二、hive的常用HQL语句
    1、过滤条件
        where 、limit、 distinct、 between and 、 null、 is not null
select * from emp where sal > 3000;
select * from emp limit 1;
select distinct deptno from emp;   
select * from emp where sal between 2000 and 3000;
select ename from emp where comm is null;
select ename from emp where comm is not null;

    2、聚合函数
    count、 sum、 avg、 max、 min 、group by、 having

select count(1) from emp;
select count(*) from emp;  -》运行效率较低

select avg(sal) avg_sal from emp;
select deptno,avg(sal) from emp group by deptno;
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

    3、join
        等值join
        左join  left
        右join    right
        全join    full

A表：
    ID        NAME
    1        张三
    2        李四
    3        王五
    5        赵六
B表：
    ID        phone
    1        1111
    2        2222
    3        3333
    4        4444

select e.empno,e.ename,d.deptno,e.sal from emp e join dept d on e.deptno=d.deptno;

select e.empno,e.ename,d.deptno,e.sal from emp e left join dept d on e.deptno=d.deptno;

select e.empno,e.ename,d.deptno,e.sal from emp e right join dept d on e.deptno=d.deptno;

select e.empno,e.ename,d.deptno,e.sal from emp e full join dept d on e.deptno=d.deptno;

十三、hive与MR的常用参数设置

设置每个reduce处理的数据量
  set hive.exec.reducers.bytes.per.reducer=<number>
 
  <property>
  <name>hive.exec.reducers.bytes.per.reducer</name>
  <value>1000000000</value>
  <description>size per reducer.The default is 1G, i.e if the input size is 10G, it will use 10 reducers.</description>
</property>

设置最大能够运行的reduce个数
  set hive.exec.reducers.max=<number>
 
  <property>
  <name>hive.exec.reducers.max</name>
  <value>999</value>
  <description>max number of reducers will be used. If the one
    specified in the configuration parameter mapred.reduce.tasks is
    negative, Hive will use this one as the max number of reducers when
    automatically determine number of reducers.</description>
</property>

实际reduce的个数
  set mapreduce.job.reduces=<number>

<property>
  <name>mapreduce.job.reduces</name>
  <value>1</value>
  <description>The default number of reduce tasks per job. Typically set to 99%
  of the cluster's reduce capacity, so that if a node fails the reduces can
  still be executed in a single wave.
  Ignored when mapreduce.jobtracker.address is "local".
  </description>
</property>


十四、hive中的几种排序方式
    1、order by
    select * from emp order by sal;
    2、sort by

insert overwrite local directory '/opt/datas/emp_sort' row format delimited fields terminated by '\t' select * from emp sort by sal;

    3、distribute by
    
insert overwrite local directory '/opt/datas/emp_dist' row format delimited fields terminated by '\t' select * from emp distribute by deptno sort by sal;

    4、cluster by
        =distribute by+sort by

insert overwrite local directory '/opt/datas/emp_cls' row format delimited fields terminated by '\t' select * from emp cluster by sal;

十五、UDF函数
    1、【需求】实现大小写转换
    2、在pom.xml文件中添加hive的相关依赖，重新update工程即可
       <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>0.13.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>0.13.1</version>
        </dependency>
    3、将自己的hive-site.xml文件放到eclipse工程下，便于读取配置
    4、继承UDF类，import org.apache.hadoop.hive.ql.exec.UDF;
    5、写完程序，打jar包上传到Linux系统中
    6、与jar包进行关联
        add jar /opt/datas/udf.jar;
    7、创建function函数方法
create temporary function my_udf as 'com.bigdata.mapreduce.BigdataUdf';
    8、执行将emp表中的ename全部转换成小写
    select ename,my_udf(ename) low_ename from emp;