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;