hive 入门

hive怎么出现：
facebook最初研发来处理海量社交数据和机器学习。
简化分析：使用sql

hive是什么：
hive是一个大数据仓库。
hive是一个基于hadoop的大数据仓库。
hive是一个基于hadoop的数据仓库，可以通过类sql语句来对数据进行读、写、管理(元数据管理)。

hive的架构图：
用户连接客户端：cli 、 jdbc/odbc 、web GUI
thriftServer：第三方服务
metastore：hive的元数据(库名、表名、字段名、字段类型、分区、分桶、创建时间、创建人等等)。

解释器：将hql语句生成抽象表达式树。
编译器：对hql语句进行词法、语法、语言的编译(此时需要联系元数据)，编译完成之后会生成一个有向无环的执行计划。
优化器：将执行计划进行优化，减少不必要的列、使用分区等。
执行器：将优化后的执行计划交给hadoop的mapreduce框架去执行。

hive和hadoop的关系：
hive基于hadoop：
hive本身没有存储和分析功能。它就相当于套在hadoop只上的一个壳子，它的存储依靠于hdfs，他的分析依靠于mapreduce。

hive的特点：
Built on top of Apache Hadoop?, Hive provides the following features:
1.Tools to enable easy access to data via SQL, thus enabling data warehousing tasks such as extract/transform/load (ETL), reporting, and data analysis.
2.A mechanism to impose structure on a variety of data formats
3.Access to files stored either directly in Apache HDFS? or in other data storage systems such as Apache HBase?
4.Query execution via Apache Tez?, Apache Spark?, or MapReduce
5.Procedural language with HPL-SQL
6.Sub-second query retrieval via Hive LLAP, Apache YARN and Apache Slider.

hive安装：
hive常有三种安装：(hive会自动监测hadoop的环境变量，如有就必须启动hadoop)
1、本地模式(多用户)：
使用hive自带默认元数据库derby来进行存储，通常用于测试
优点：使用简单，不用进行配置
缺点：只支持单session。
步骤：

2、远程模式(元数据在该台服务器上)(多用户)：
通常使用关系型数据库来进行元数据存储。(mysql、oracle等带jdbc驱动的数据库)
优点：支持多session
缺点：需要配置、还需要安装mysql、oracle等关系型数据库。
步骤：
1、解压配置环境变量
2、配置配置文件
mv ./conf/hive-env.sh.template ./conf/hive-env.sh
vi ./conf/hive-env.sh (配置jdk环境变量或者不配都可以)
mv ./conf/hive-default.xml.template ./conf/hive-site.xml
vi ./conf/hive-site.xml (配置hive的属性)
3、将mysql的驱动包cp到hive安装目录的./lib下。
4、在指定数据库中创建元数据库，数据库的编码设置为latin1。
5、启动测试

3、远程模式(元数据在远程服务器上)(多用户模式)：
和2差不多，只不过可以将元数据放到别的服务器上。可以有一个hive的server 和多个hive的client。

hive的数据模型：

hive的基本命令练习：

hive有一个默认的数据库，叫default，操作hql不指定则默认使用该库。

注释：
//
/**
*/

–

hive数据库名、表名不区分大小写
1、名字不能使用数字开头
2、不能使用关键字 (user关键字)
3、尽量不要使用特殊符号

create database if not exists qf1701 comment ‘this is database of 1701’;
创建数据库的本质：在hive的数据仓库下创建一个目录(库名.db的目录)

切换库：
use qf1701;

创建表：
create table if not exists qf1701.u1(
uid int,
uname string,
age int
)
;

create [external] table [if not exists] t2(
uid int [comment ‘this is id’],
uname string [comment ‘this is uname’],
age int
)
[comment ‘this is table of t1’]
[row format delimited fields terminated by ’ ']
[lines terminated by ‘\n’]
[stored as textfile]
;

hive分为内部表和外部表：
内部表和外部表的区别：
创建时：
默认创建内部表；加上关键字：external创建的是外部表
删除时：
删除内部表，将会删除元数据和hdfs中的表对应的数据内容。
删除外部表，将只删除元数据，不删除hdsf中的数据内容。

使用场景：
使用后数据不删除使用外部表(推荐使用这个)。

create external table if not exists t3(
uid int comment ‘this is id’,
uname string comment ‘this is uname’,
age int
)
comment ‘this is table of t3’
row format delimited fields terminated by ’ ’
lines terminated by ‘\n’
stored as textfile
;

删除表：
删除表的本质：删除表所对应的目录
drop table [if exists] t2;

hive 使用的是严格读时模式:加载数据不检测完整性，读时发现不对则使用NULL来代替。
而mysql使用的是写时模式：

创建表的本质：在库下面创建目录：
加载数据方式：
load data [local] inpath ‘’ [overwrite] into table u1;
加载数据本质：将数据文件copy（不完全是copy）到对应表目录下面。
load data local inpath ‘/home/hivedata/u2’ into table u2;
create table if not exists qf1701.u2(
uid int,
uname string,
age int
)
;
从hdfs中加载数据到表中(移动)：
load data inpath ‘/u2’ into table u2;

create table if not exists qf1701.u3(
uid int,
uname string,
age int
)
;

insert into 加载数据：
insert into table u3
select uid,uname,age from u2 where uid < 3;

create table if not exists qf1701.u4(
uid int,
uname string,
age int
)
;

克隆表,不带数据：
create table if not exists u5 like u4;

克隆带数据：
create table if not exists u7 like u4 location ‘/user/hive/warehouse/qf1701.db/u4’;

create table if not exists qf1701.u6(
uid int,
uname string,
age int
)
location ‘/user/hive/warehouse/qf1701.db/u4’ #后面接的一定是hdfs中的目录，不是文件
;

查看库描述：
describe database [ extended] qf1701;
desc database [ extended] qf1701;

查看表：
desc/ [ extended] describe u7;
show [ extended] create table u7; 较全

CREATE TABLE log1(
id string COMMENT ‘this is id column’,
phonenumber bigint,
mac string,
ip string,
url string,
stats1 string,
stats2 string,
upflow int,
downflow int,
statscode String,
dt String
)
COMMENT ‘this is log table’
ROW FORMAT DELIMITED FIELDS TERMINATED BY ’ ’
LINES TERMINATED BY ‘\n’
stored as textfile
;

load data local inpath ‘/home/hivedata/log’ overwrite into table log1;

第一个需求，求每个号码的总流量：
select
l.phonenumber phonenum,
round(sum(l.upflow+l.downflow )/1024.0,2) total
from log1 l
group by l.phonenumber
;

第二个需求，求访问排名前3的website：
select
l.url website,
count(l.url) websitecount
from log1 l
group by l.url
order by websitecount desc
limit 3
;

第三个需求，模拟收费：

表属性的修改：
1、修改表名：rename to ???是否comment
alter table u7 rename to u8;
2、修改列名：change column
alter table u8 change column uname name1 string;
3、修改列的位置
alter table u8 change column name1 uname string after age;
alter table u8 change column uname uname1 string first;
4、修改字段类型：
alter table u8 change column uname1 uname1 int;

5、增加字段：
alter table u8 add columns(
sex int,
add string
)
;

6、删除字段：
alter table u8 replace columns(
uname1 int,
uid int,
age int
)
;

7、内部表和外部表的转换：
alter table u8 set tblproperties(‘EXTERNAL’=‘TRUE’); ##true一定大写
alter table u8 set tblproperties(‘EXTERNAL’=‘false’); ##false大小写都没有关系

显示当前库：
set hive.cli.print.current.db=true;

删除库：
drop database if exists qf1702; ##删除空库
drop database if exists qf1702 cascade; ##cascade强制删除

18+1个sql的语句(需要收)：
mr的java代码：(抽查)
今天的知识点：(敲一遍)
预习hive的分区、分桶、高级查询：

-------------------------分区-----------------
为什么要分区？？
随着时间，单表的数据量越来越大，而hive查询通常使用全表扫描，这样将会导致大量不必要的数据扫描，从而大大减小查询效率。
从而引进分区技术，使用分区技术，避免hive全表扫描，提升查询效率。
将越来越多的数据按照某标识将其分开。

怎么分区？？根据业务，通常年 、 月 、日 、地区 等。

hive分区和mysql分区的区别？？
hive分区使用的是表外字段；而mysql使用的是表内字段。

分区技术？？
1、hive的分区名区分大小写
2、hive的分区本质是在表下面创建目录，但是该分区字段是一个伪列，不真实存在数据中。
3、一张表可以有一个或者多个分区，分区下面也可以有一个或者多个分区。

创建一级分区表：

create table if not exists part1(
uid int,
uname string,
login string
)
partitioned by(dt string)
row format delimited fields terminated by ’ ’
;

load data local inpath ‘/home/hivedata/l1’ into table part1 partition(dt=“2017-05-31”);

创建二级分区：
create table if not exists part2(
uid int,
uname string,
login string
)
partitioned by(year string,month string)
row format delimited fields terminated by ’ ’
;

load data local inpath ‘/home/hivedata/l1’ into table part2 partition(year=‘2017’,month=05);

创建三级分区：
create table if not exists part3(
uid int,
uname string,
login string
)
partitioned by(year string,month string,day string)
row format delimited fields terminated by ’ ’
;

load data local inpath ‘/home/hivedata/l1’ into table part3 partition(year=‘2017’,month=05,day=31);

显示分区：
show partitions part1;

修改分区：
分区的名字不能修改？？(没有命令修改，手动可以)??找一下命令修改
增加分区：
alter table part1 add partition(dt=“2017-05-30”);
alter table part1 add partition(dt=“2017-05-28”) partition(dt=“2017-05-29”);
增加分区并设置数据：
alter table part1 add partition(dt=“2017-05-27”) location ‘/user/hive/warehouse/qf1701.db/part1/dt=2017-06-01’;
alter table part1 add partition(dt=“2017-05-25”) location ‘/user/hive/warehouse/qf1701.db/part1/dt=2017-06-01’ partition(dt=“2017-05-26”) location ‘/user/hive/warehouse/qf1701.db/part1/dt=2017-05-31’;
修改分区的hdfs存储路径：
alter table part1 partition(dt=“2017-05-25”) set location ‘hdfs://qianfeng/user/hive/warehouse/qf1701.db/part1/dt=2017-05-31’;

删除分区：
alter table part1 drop partition(dt=“2017-05-31”);
alter table part1 drop partition(dt=“2017-05-25”),partition(dt=“2017-05-26”);

分区类型：
静态分区：加载数据指定分区的值。
动态分区：数据未知，根据分区的值确定创建分区。
混合分区：静态和动态的有。

动态分区的属性：
hive.exec.dynamic.partition=true
hive.exec.dynamic.partition.mode=strict/nonstrict
hive.exec.max.dynamic.partitions=1000
hive.exec.max.dynamic.partitions.pernode=100

create table if not exists part_tmp(
uid int,
uname string,
login string,
dt string
)
row format delimited fields terminated by ’ ’
;
load data local inpath ‘/home/hivedata/part_tmp’ into table part_tmp;

create table if not exists dy_part(
uid int,
uname string,
login string
)
partitioned by(dt string)
row format delimited fields terminated by ’ ’
;
###动态分区不能使用load方式加载：
load data local inpath ‘/home/hivedata/part_tmp’ into table dy_part partition(dt);
insert into table dy_part partition(dt)
select uid,uname,login,dt from part_tmp
;

混合：(注意列的个数匹配)
create table if not exists dy_part1(
uid int,
uname string,
login string
)
partitioned by(year string,month string,day string)
row format delimited fields terminated by ’ ’
;

insert into table dy_part1 partition(year=“2017”,month,day)
select uid,uname,login,month,day from part3
;

设置hive为严格模式执行：
set hive.mapred.mode=nonstrict/strict;
The mode in which the Hive operations are being performed.
In strict mode, some risky queries are not allowed to run. They include:
Cartesian Product.
No partition being picked up for a query.
Orderby without limit.
Comparing bigints and strings.
Comparing bigints and doubles.

注意事项：
1、hive的分区使用的表外字段，分区字段是一个伪列但可以做查询过滤。
2、分区字段不建议使用中文
3、不太建议使用动态分区，因为动态分区将会使用mapreduce来进行查询数据，
如果分区数量过多将会导致namenode和yarn的性能瓶颈。所以建议动态分区前也尽可能的预知分区数量。
4、分区属性修改均可以使用手动元数据和hdfs数据内容。

------------------------分桶---------------
为什么要分桶？？
单个分区或者表中的数据量越来越大，当分区不能细粒度划分数据时，所以会采用分桶技术将数据更加细粒度的划分和管理。

分桶关键字：bucket

分桶技术：
clustered by(uid) into n buckets
默认采用对分桶字段进行hash值%总桶数的余数就是分桶的桶数。

分桶意义？？
1、减少hive扫描量，提高查询速度。
2、分桶还可以高效采样。sample

##创建表分桶：
create table if not exists buc1(
uid int,
uname string,
login string
)
clustered by(uid) into 4 buckets
row format delimited fields terminated by ’ ’
;
##分桶使用load方式加载数据不能体现分桶。
load data local inpath ‘/home/hivedata/part_tmp’ into table buc1;

设置强制分桶属性：
set hive.enforce.bucketing=false/true
如果reducer个数和分桶个数不一致的时候，请手动设置：
mapreduce.job.reduces=4

create table if not exists buc2(
uid int,
uname string,
login string
)
clustered by(uid) into 4 buckets
row format delimited fields terminated by ’ ’
;

##分桶数据需要使用insert into方式来加载
insert into table buc2
select uid,uname,login from part_tmp
;

对分桶表的查询：
查询全部：
select * from buc2;
select * from buc2 tablesample(bucket 1 out of 1);

查询第几桶：
select * from buc2 tablesample(bucket 1 out of 4 on uid);
select * from buc2 tablesample(bucket 2 out of 4 on uid);

select * from buc2 tablesample(bucket 1 out of 2 on uid);
select * from buc2 tablesample(bucket 2 out of 2 on uid);

tablesample(bucket x out of y on uid)
x:代表从第几桶开始查询
y：查询总的桶数，y可以是总的桶数的倍数或者是因子；x不能大于y

不压缩不拉伸：
1 1+4
压缩：
1 1+4/2 + 1+4/2+4/2
2 2+4/2 + 2+4/2+4/2

select * from buc2 tablesample(bucket 1 out of 8 on uid);
select * from buc2 tablesample(bucket 5 out of 8 on uid);

select * from buc2 tablesample(bucket 1 out of 5 on uid);
select * from buc2 tablesample(bucket 2 out of 8 on uid);

查询id为基数：
select
*
from buc2 tablesample(bucket 2 out of 2 on uid)
where uname = “aa1”
;

select
*
from buc2
where uname = “aa1”
;

查询：
select * from part_tmp limit 3;
select * from part_tmp tablesample(3 rows);
select * from part_tmp tablesample(66 percent);
select * from part_tmp tablesample(24B); ###k B M G
select * from part_tmp order by rand() limit 3;

分区下面分桶：按照性别分区(1男2女) 在分区中按照stuid的基偶分桶 ：
stuid
1 cc1 1
2 cc2 1
3 cc3 2
4 cc4 1
5 cc5 2
6 cc6 1
7 cc7 2
8 cc8 1
9 cc9 2

clustered by(uid) into 4 buckets
create table if not exists stu_tmp(
uid int,
uname string,
sex int
)
row format delimited fields terminated by ’ ’
;
load data local inpath ‘/home/hivedata/stu_tmp’ into table stu_tmp;

create table if not exists stu(
uid int,
uname string
)
partitioned by(sex int)
clustered by(uid) into 2 buckets
row format delimited fields terminated by ’ ’
;

insert into table stu partition(sex)
select uid,uname,sex from stu_tmp;

查询性别为女性的、并且学号为基数的学生：
select
*
from stu tablesample(bucket 2 out of 2 on uid)
where sex = 2;

注意：
分区使用的表外字段，分桶使用的表内字段
分桶更加细粒度的管理数据，更多使用来做数据抽样
-----------------分区分桶 end------------------------

------------------------hive的高级查询---------------
select
from
join/left join /inner join
on
where
group by
having
distribute by
cluster by
sort by/order by
limit
union/union all

尽量不要子查询 、 尽量不要带in not in 等。
查询尽量避免join连接查询，但是永远不可能避免。
查询永远是小表驱动大表(永远是小结果集驱动大结果集)。

##left join 、left outer join 、 left semi join （常用）
(以左表为准，和右表匹配，如果匹配不上将使用NULL来代替)
select
e2.ename
from emp e1
left join emp e2
on e1.mgr = e2.empno
where e1.ename = “JONES”
;

select
e2.ename
from emp e1
left outer join emp e2
on e1.empno = e2.mgr
where e1.ename = “JONES”
;

select
d.*
from dept d
left semi join emp e
on d.deptno <> e.deptno
;

###exits：
select
d.*
from dept d
left semi join emp e
on d.deptno = e.deptno
;

##查询部门编号没有在员工表中存在过的部门：
select
d.*
from dept d
left join emp e
on d.deptno = e.deptno
where e.ename is null
;

left join 从hive0.8版本开始有
left join 和 left outer join 几乎差不多
left semi join 叫做半开连接，通常是left join的一种优化，只能查询左表的信息，然后主要解决hive中存在不不存在的问题。

##right join 、 right outer join
以右表为准，来匹配左表信息，如果匹配不上，使用null来代替。
hive不支持right semi join：
select
d.dname,
e.ename
from dept d
left join emp e
on d.deptno = e.deptno
;

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

select
d.dname,
e.ename
from dept d
right outer join emp e
on d.deptno = e.deptno
;

##join 、 inner join 、 多表用逗号分开 ：内连接，三者差不太多，相互连接上才能出来结果。
join : 不加任何的on 或者是where过滤条件时，称之为笛卡尔积。
select
d.dname,
e.ename
from dept d
join emp e
on d.deptno = e.deptno
;

select
d.dname,
e.ename
from dept d
inner join emp e
on d.deptno = e.deptno
;

select
d.dname,
e.ename
from dept d,emp e
where d.deptno = e.deptno
;

full outer join (相互进行连接，如果有一张表的数据连接不上来使用null来代替)

select
d.dname,
e.ename
from dept d
full outer join emp e
on d.deptno = e.deptno
;

hive提供小表标识：使用的是STREAMTABLE(小表别名)
select
/+STREAMTABLE(d)/
d.dname,
e.ename
from emp e
join dept d
on d.deptno = e.deptno
;

子查询：(hive对子查询支持不是很友好) 特别是 "="问题较多
select
e.*
from emp e
where e.deptno = (
select
d.deptno
from dept d
limit 1
)
;

select
e.*
from emp e
where e.deptno in (
select
d.deptno
from dept d
)
;

select
e.*
from dept d
join emp e
on d.deptno = e.deptno
;

inner join 和outer join的区别：
分区字段对outer join 中的on条件是无效，对inner join 中的on条件有效

有inner join 但是没有full inner join
有full outer join但是没有outer join
所有join连接，只支持等值连接(= 和 and )。不支持 != 、 < 、> 、 <> 、>=、 <= 、or

map-side join：
如果所有的表中有小表，将会把小表缓存内存中，然后再map端进行连接关系查找。hive在map端
查找时将减小查询量，从内存中读取缓存小表数据，效率较快，还省去大量数据传输和shuffle耗时。

注意看该属性：
set hive.auto.convert.join=true
select
e.*
from dept d
join emp e
on d.deptno = e.deptno
;

以前的老版本，需要添加(/+MAPJOIN(小表名)/)来标识该join为map端的join。hive 0.7以后hive已经废弃，但是任然管用：
???需要再测试看看是否有效???
select
/+MAPJOIN(dept)/
e.*
from dept d
join emp e
on d.deptno = e.deptno
;

到底小表多大才会被转换为map-side join：
set hive.mapjoin.smalltable.filesize=25000000 约23.8MB

on ： 所有on只支持等值连接。

where ： where后面通常是表达式 、还可以是非聚合函数表达式(但是不能是聚合函数表达式)

select
d.*
from dept d
where length(d.dname) > 5
;

####？？？？表达式别名？？？
select
d.dname,
length(d.dname) as ds
from dept d
;
####where后不能跟聚合函数
select
e.deptno,
count(e.deptno) ct
from emp e
where count(e.deptno) > 3
group by e.deptno
;

group by ： 分组，通常和聚合函数搭配使用
查询的字段要么出现在group by 后面，要么出现在聚合函数里面
select
e.deptno,
count(e.ename) ct
from emp e
group by e.deptno
;

having : 对分组以后的结果集进行过滤。
select
e.deptno,
count(e.deptno) ct
from emp e
group by e.deptno
having ct > 3
;

sort by:局部排序，只保证单个reducer有顺序。
order by：全局排序，保证所有reducer中的数据都是有顺序。
如果reduser个数只有一个，两者都差不多。
两者都通常和 desc 、 asc 搭配。默认使用升序asc。

手动设置reducer个数：
set mapreduce.job.reduces=3;
select
e.empno
from emp e
order by e.empno desc
;

只要使用order by ，reducer的个数将是1个。

distribute by ： 根据by后的字段和reducer个数，决定map的输出去往那个reducer。
默认使用查询的第一列的hash值来决定map的输出去往那个reducer。如果reducer的个数为1时没有任何体现。

如果sort by 和 distribute by 同时出现：那个在前面？？

如果sort by 和 distribute by 同时出现，并且后面的字段一样、sort by使用升序时 <==> cluster by 字段

cluster by ：兼有distribute by以及sort by的升序功能。

select
e.*
from emp e
distribute by e.deptno
sort by e.empno asc
;

select
e.*
from emp e
distribute by e.deptno
sort by e.empno asc
;

------------如下两个hql等价------
select
e.*
from emp e
distribute by e.deptno
sort by e.deptno asc
;

select
e.*
from emp e
cluster by e.deptno
;

limit ： 从结果集中取数据的条数

union ：将多个结果集合并，去重，不排序
union all ：将多个结果集合并，不去重，排序。

select
d.deptno as deptno,
d.dname as dname
from dept d
union
select
e.deptno as deptno,
e.ename as dname
from emp e
;

select
d.deptno as deptno,
d.dname as dname
from dept d
union all
select
d.dname as dname,
d.deptno as deptno
from dept d
;

(
select
deptno,
dname
from dept
limit 1
)
union all
(
select
deptno,
dname
from dept
limit 1
)
;

select
e.empno,
e.comm
from emp e
where e.comm is NULL
union all
select
e.empno,
e.comm
from emp e
where e.comm is not NULL
;

单个union 语句不支持：orderBy、clusterBy、distributeBy、sortBy、limit
单个union语句字段的个数要求相同，字段的顺序要求相同。

distinct : 去重

-----------------------查询 end ------------
hive的数据类型分为基础数据类型和复杂数据类型：
基础数据类型：
tinyint 1 -128~127
smallint 2 -2的15 ~ 2的15-1
int 4
bigint 8
float 4
double 8
boolean 1
string
binary 字节
timestamp 2017-06-02 11:36:22

java中有的而hive中没有的：
long
char
short
byte

create table if not exists bs1(
id1 tinyint,
id2 smallint,
id3 int,
id4 bigint,
sla float,
sla1 double,
isok boolean,
content binary,
dt timestamp
)
row format delimited fields terminated by ‘\t’
;

233 12 342523 455345345 30000 60000 nihao helloworld 2017-06-02
126 13 342526 455345346 80000 100000 true helloworld1 2017-06-02 11:41:30

load data local inpath ‘/home/hivedata/bs’ into table bs1;

复杂数据类型:
array：col array<基本类型>, 下标从0开始，越界不报错
map：col map<String,String>,
struct：

create table if not exists arr1(
province string,
city array
)
row format delimited fields terminated by ‘\t’
;

河北 石家庄,邯郸,保定,张家口
湖北 武汉,荆州,孝感,宜昌,襄阳
load data local inpath ‘/home/hivedata/arr1’ into table arr1;

##注意顺序？？
create table if not exists arr2(
province string,
city array
)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’
;

查询：
select province,city[1] from arr2 where size(city) > 4;

create table if not exists map1(
province string,
score map<String,double>
)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’
map keys terminated by ‘:’
;

湖北 chinese:90,math:99,english:68
河北 chinese:88,math:90,english:98
load data local inpath ‘/home/hivedata/map1’ into table map1;

select
m.province,
map_keys(m.score),
map_values(m.score)
from map1 m
where m.score[“math”] > 95
and size(m.score) > 2
;

###struct：
create table if not exists str1(
province string,
score structchinese:double,math:double,english:double
)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’
;

湖北 90,99,68
河北 88,90,98
load data local inpath ‘/home/hivedata/str1’ into table str1;

select
s.province,
s.score.chinese,
s.score.math
from str1 s
where s.score.math > 95
;

uid uname belong tax add
1 lx ll,lw,lg,lm wuxian:300,gongjijin:1200,shebao:300 陕西,西安,五路
1 ll lw,gf,lg,mm wuxian:200,gongjijin:2400,shebao:200 北京,海淀,天丰利

create table if not exists ss(
uid int,
uname string,
belong array,
tax map<String,double>,
add structprovice:string,city:string,county:string
)
row format delimited fields terminated by ‘\t’
collection items terminated by ‘,’
map keys terminated by ‘:’
;
load data local inpath ‘/home/hivedata/ss’ into table ss;
查询：下属数量为四个的、公积金大于2000、省份是北京的数据
(uid、uname 、下属取前两个、公积金和社保、省市县)

select
s.uid,
s.uname,
s.belong[0],
s.belong[1],
s.tax[“gongjijin”],
s.tax[“shebao”],
s.add.provice,
s.add.city,
s.add.county
from ss s
where size(s.belong) = 4
and s.tax[“gongjijin”] > 2000
and s.add.provice = “北京”
;

???嵌套型？？？ 所有元素分割符自己调

create table if not exists ss1(
uname string,
col1 map<String,Array>
)
…
;

zs chinese:90,80 math:99,88 english:78,90
ls chinese:93,82 math:76,86 english:98,88

--------------------------------内部函数--------------
常用内部函数：
1、0-1的随机数rand()
select rand();
select rand(10);
2、split(str,spliter)
select split(rand()*100,"\.")[0];
3、substring(str,start,length) substr(str,start,length)
select if(rand()*100 > 10,substring(rand()*100,0,2),substring(rand()*100,0,1));
select substr(rand()*100,0,2);
4、if(expr,true,false)
select if(1=1,“男”,“女”);
select if(3=1,“男”,if(3=2,“女”,“妖”));
5、替换regexp_replace(str,要替换的内容,替换成的内容)
select regexp_replace(“a.html”,".html",".jsp");
6、字符串连接：concat(str1,str2,str3…) concat_ws(spliter,str1,str2,str3…)
select concat(“1”,“2”,“3”);
select concat_ws("_",“1”,“2”,“3”);
7、case 值 when then end; case when expr then … end;
select
case 1
when 1 then “男”
when 2 then “女”
else “妖”
end;

select
case
when 3=1 then “男”
when 3=2 then “女”
else “妖”
end;

8、类型转换：cast(str as 类型)
select cast(“2” as int) + cast(“1” as int);
select cast(2.0 as int);
select “1”+“2”;

9、排名函数：
row_number() : 没有并列，相同名次顺序排
rank() ： 有并列，相同名次空位
dense_rank() ：并列，相同名次不空位

10、聚合函数：
count(col) :统计列条数
count(1) :不管行有没有值，只要有行统计
count(*) ：正行不全为null的时候就统计
count(distinct col) :去重列的统计
sum()
avg()
max()
min()

create table if not exists rstu(
uid int,
classid int,
score int
)
row format delimited fields terminated by ‘\t’
;
uid classid score
1 1701 95
2 1701 95
3 1701 78
4 1701 67
5 1701 88
6 1702 65
7 1702 89
8 1702 96
9 1702 87

load data local inpath ‘/home/hivedata/rstu’ into table rstu;

select
tmp.*
from
(
select
s.*,
row_number() over(distribute by s.classid sort by s.score desc) rm
from rstu s
) tmp
where tmp.rm < 4
;

select
tmp.*
from
(
select
s.*,
row_number() over(distribute by s.classid sort by s.score desc) rm,
rank() over(distribute by s.classid sort by s.score desc) rk,
dense_rank() over(distribute by s.classid sort by s.score desc) drk
from rstu s
) tmp
where tmp.drk < 4
;

select
tmp.*
from
(
select
s.*,
row_number() over(partition by s.classid order by s.score desc) rm,
rank() over(partition by s.classid order by s.score desc) rk,
dense_rank() over(partition by s.classid order by s.score desc) drk
from rstu s
) tmp
where tmp.drk < 4
;

其它内部函数：
几乎任何数和NULL进行操作，都返回NULL。
is null
is not null
rlike

& : 同真为真其余全假
4&4
0100
0100
0100=4
4&6
0100
0110
0100=4
4&8
0100
1000
0000=0

| : 同假为假其余全真
4&8
0100
1000
1100=12

and 、or 均可以
&& 、 || 有异常：
round(str,2)
size(arr/map)
trim()
length()
array_contains(array(1,2,3,4),3)
map_values(map)
map_keys(map)
sort_array(arr)
from_unixtime(1496392248,“yyyy-MM-dd HH:mm:ss”);
unix_timestamp();
2017-05-31 得到指定时间的时间戳：格式严格要求
unix_timestamp(“2015-05-31 00:00:00”);
to_date(“2015-05-31 00:00:00”);
year(“2015-05-31 00:00:00”);
month(“2015-05-31 00:00:00”);
day(“2015-05-31 00:00:00”);
hour(“2015-05-31 18:00:00”);
minute(“2015-05-31 18:00:00”);
seconds(“2015-05-31 18:00:00”);
week(“2015-05-31 18:00:00”);

####：：：

为什么需要自定义函数：
hive的内部函数没法满足所有的业务需求。
hive提供很多模块可以自定义功能。比如：serder 、自定义函数、输入输出格式等。

常见的自定义函数有哪些：
udf：用户自定义函数，user defined function。一对一的输入输出。(经常常用)
udaf:用户自定义聚合函数，user defind agregation function。多对一的输入输出。
udtf:用户自定义的表生成函数，user defined table-generate function。一对多的输入输出。

编写udf的方式：
1、继承UDF，重写它evaluate()。允许重载。
2、继承genricUDF,重写initlizer()、getdisplay()、evaluate()

使用方法:
第一种： (当前session有效)
1、将编写好的udf的jar包上传到服务器，并添加到hive中
hive>add jar /home/1701Demo-0.0.1.jar

hive.aux.jars.path $HIVE_HOME/auxlib

2、创建一个自定的临时函数名
hive>create temporary function myconcat as ‘edu.qianfeng.udf.FirstUDF’;
3、测试是否创建临时函数成功：
show functions;
desc function myconcat;
使用：
4、确定没有调用该临时函数可以摧毁(小心)
drop function if exists myconcat;

select
l.id,
l.phonenumber,
myconcat(l.id,l.phonenumber)
from log1 l
;

第二种：(也相当于临时函数)
1、将编写好的udf的jar包上传到服务器
vi ./hive-init
add jar /home/1701Demo-0.0.1.jar
create temporary function bta as ‘edu.qianfeng.udf.BirthdayToAge’;

2、启动的时候带上初始化文件：
hive -i ./hive-init

第三种：
在hive的安装目录的bin目录下面创建一个文件，文件名.hiverc：
vi ./bin/.hiverc
add jar /home/1701Demo-0.0.1.jar;
create temporary function bta as ‘edu.qianfeng.udf.BirthdayToAge’;

编译源码：(费劲)
1)将写好的Jave文件拷贝到~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/udf/
cd ~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/udf/
ls -lhgt |head
2)修改~/install/hive-0.8.1/src/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java，增加import和RegisterUDF

import com.meilishuo.hive.udf.UDFIp2Long; //添加import

registerUDF(“ip2long”, UDFIp2Long.class, false); //添加register
3)在~/install/hive-0.8.1/src下运行ant -Dhadoop.version=1.0.1 package
cd ~/install/hive-0.8.1/src
ant -Dhadoop.version=1.0.1 package
4)替换exec的jar包，新生成的包在/hive-0.8.1/src/build/ql目录下,替换链接
cp hive-exec-0.8.1.jar /hadoop/hive/lib/hive-exec-0.8.1.jar.0628
rm hive-exec-0.8.1.jar
ln -s hive-exec-0.8.1.jar.0628 hive-exec-0.8.1.jar
5)重启进行测试

案例1：

案例2：

案例3：

5、根据key找到value。
如：sex=1&hight=180&weight=130&sal=28000
select fun1(“sex=1&hight=180&weight=130&sal=28000”,“weight”) 130

10、正则表达式解析日志：
解析前：
220.181.108.151 - - [31/Jan/2012:00:02:32 +0800] “GET /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP/1.1” 200 8784 “-” “Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)”
解析后：
220.181.108.151 20120131 120232 GET /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP 200 Mozilla

-----------------------serder
serder：
serialize:序列化(写数据)
deserialize:反序列化(读数据)

常用serder：csv 、tsv 、json serder 、 regexp serder 等。
csv ： 逗号分隔值
tsv ： tab 分隔值
json ： json格式的数据
regexp ： 数据需要复合正则表达式

创建表：
create table if not exists csv1(
uid int,
uname string,
age int
)
row format serde ‘org.apache.hadoop.hive.serde2.OpenCSVSerde’
stored as textfile
;

load data local inpath ‘/home/hivedata/csv1’ into table csv1;

create table if not exists csv3(
uid int,
uname string,
age int
)
row format serde ‘org.apache.hadoop.hive.serde2.OpenCSVSerde’
with serdeproperties(
“separatorChar”=",",
“qutoeChar”="’",
“escapeChar”="\"
)
stored as textfile
;

json serde：
如果是第三方jar包或者是自己写的，就必须要先加载jar包：
hive>add jar /home/json-serde-1.3-jar-with-dependencies.jar;

create table if not exists js1(
pid int,
content string
)
row format serde “org.openx.data.jsonserde.JsonSerDe”
;

{“pid”:1,“content”:“this is pid of 1 content”}
{“pid”:2,“content”:“this is pid of 2 content”}
load data local inpath ‘/home/hivedata/js1’ into table js1;

create table if not exists complex(
uid int,
uname string,
belong array,
tax map<String,array>
)
row format serde “org.openx.data.jsonserde.JsonSerDe”
;

{“uid”:1,“uname”:“zs”,“belong”:[“zs1”,“zs2”,“zs3”],“tax”:{“shebao”:[220,280,300],“gongjijin”:[600,1200,2400]}}
{“uid”:2,“uname”:“ls”,“belong”:[“ls1”,“ls2”,“ls3”],“tax”:{“shebao”:[260,300,360],“gongjijin”:[800,1600,2600]}}

load data local inpath ‘/home/hivedata/complex’ into table complex;

select
c.*
from complex c
where size(c.belong) = 3
and c.tax[“gongjijin”][1] > 1800
;

###???regex serde:???

---------------------#######hive的数据存储格式：
hive默认的列与列之间的分隔符是：\001,注意不是tab
通常分隔符：
tab
,
" "
|
\n
\001 ^A (\u0001、,注意不是\0001也不是\01)
\002 ^B
\003 ^C

数据文件存储格式：
hive默认的数据文件存储格式为：textfile
textfile：普通的文本文件存储，不压缩。
优缺点：
sequencefile: hive为用户提供的二进制存储，本身就压缩。不能用load方式加载数据
rcfile:hive提供行列混合存储，hive在该格式下，将会尽量把附近的行和列的块尽量存储到一起。任然压缩，查询效率较高。
orc:

hive.default.fileformat
TextFile

Expects one of [textfile, sequencefile, rcfile, orc].
Default file format for CREATE TABLE statement. Users can explicitly override it by CREATE TABLE … STORED AS [FORMAT]

create table if not exists text1(
uid int,
uname string
)
row format delimited fields terminated by ’ ’
;

load data local inpath ‘/home/hivedata/seq1’ into table seq1;

创建sequencefile：
create table if not exists seq1(
uid int,
uname string
)
row format delimited fields terminated by ’ ’
stored as sequencefile
;
##该方式不行：
load data local inpath ‘/home/hivedata/seq1’ into table seq1;
insert into table seq1
select uid,uname from text1;

创建rcfile：
create table if not exists rc1(
uid int,
uname string
)
row format delimited fields terminated by ’ ’
stored as rcfile
;

##该方式不行：
load data local inpath ‘/home/hivedata/seq1’ into table rc1;
insert into table rc1
select uid,uname from text1;

综合效率：是defaultCodec+rcfile较好

自定义存储格式：
数据：
seq_yd元数据文件：
aGkseWFuZ2xpdXFpbmc=
aGVsbG8sbHVvamlheGlhbmcgc2xlZXA=
seq_yd文件为base64编码后的内容，decode后数据为：

hi,yangliuqing
hello,luojiaxiang sleep

create table cus(str STRING)
stored as
inputformat ‘org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextInputFormat’
outputformat ‘org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextOutputFormat’;

LOAD DATA LOCAL INPATH ‘/home/hivedata/cus’ INTO TABLE cus;

--------------------hive 索引
索引是数据库的一种标准技术，hive在0.7以后支持索引，只不过该索引和传统数据库较差。

优点：提高查询效率；避免全表扫描
缺点：冗余存储；加载数据较慢

索引文件特点：索引数据有序，量较小。

索引的参数：index

创建表：
create table if not exists tab1(
uid int,
uname string
)
row format delimited fields terminated by ’ ’
;
LOAD DATA LOCAL INPATH ‘/home/hivedata/tab1’ INTO TABLE tab1;

查询比较：

索引前：select count(uid) from tab1;Time taken: 22.401 seconds
索引后：select count(uid) from tab1;Time taken: 14.672 seconds

创建索引：
create index idx_tab1_uid
on table tab1(uid)
as ‘compact’
with deferred rebuild
;

修改索引(重建索引)：
alter index idx_tab1_uid
on tab1 rebuild
;

查看索引：
show index on tab1;

创建联合索引：
create index idx_tab1_uid_uname
on table tab1(uid,uname)
as ‘bitmap’
with deferred rebuild
;

删除索引：
drop index idx_tab1_uid_uname on tab1;

##########hive的视图：
hive的视图简单理解为逻辑上的表
hive现目前只支持逻辑视图，不支持物化视图。

hive的视图意义：
1、对数据进行局部暴露(涉及隐私数据不暴露)。
2、简化复杂查询。

创建视图：
create view if not exists tab_v1
as
select uid from tab1 where uid < 10;

查看视图：
show tables;
show create table tab_v1;
desc tab_v1;

视图是否可以克隆：(hive-1.2.1暂时不支持)
create view tab_v2 like tab_v1;

#####？？view 暂没有结构的修改？？？？？(直接修改元数据可行)
ALTER VIEW view_name SET TBLPROPERTIES table_properties
table_properties:
: (“TBL_NAME” = “tbv1”)
;

create view if not exists v1 as select * from text1;

ALTER VIEW v1 SET TBLPROPERTIES(“TBL_NAME” = “v11”); ???任然有问题
create view if not exists v1 as select * from text1;

删除视图：
drop view if exists tab_v2;

注意：
1、切忌先删除视图对应的表后再查询视图。
2、视图是不能用insert into 或者load 方式来加载数据。
3、视图是只读，不能修改其结构、表相关属性。

-------------hive的日志：
hive的系统日志：
默认目录：/tmp/$user.namehive.log.dir=${java.io.tmpdir}/$user.namehive.log.file=hive.loghive的查询日志：<property><name>hive.querylog.location</name><value>${system:java.io.tmpdir}/${system:user.name}
Location of Hive run time structured log file

-----------------------hive的导入导出：
hive表的数据导入：
1、从本地文件系统中导入hive表
2、从hdfs文件系统中导入hive表
3、从hive的一个表中导入到另一个表
4、直接将数据copy到hive表目录
5、location
6、克隆带数据
7、多表导入
8、CTAS

###hive不允许局部数据操作(增、删、改)。

CREATE TABLE text1(
uid int,
uname string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ’ ’
;

create table text2 (
uid int,
uname string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ’ ’
;

create table text3 (
uname string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ’ ’
;

7、多表导入:一次扫描源表，多次导入
from text1
insert into text2
select uid,uname
where uid < 2
insert into text3
select uname
;

8、CTAS：

create table if not exists text5
as
select uid from text1 where uid > 3
;

hive数据导出：
1、从hive表中导出本地文件系统中(目录、文件)
2、从hive表中导出hdfs文件系统中
3、hive表中导出到其它hive表中

关键字：insert overwrite directory

1、
insert overwrite local directory ‘/home/hivedata/out/00’
select * from text1;
###直接导入到本地文件系统的文件中：
hive -help
hive -e ‘select * from text1’ >> /home/hivedata/out/02;

2、
insert overwrite directory ‘/home/hivedata/out/00’
select * from text1;

修改导出后的列与列之间的格式：
insert overwrite local directory ‘/home/hivedata/out/01’
row format delimited fields terminated by ‘\t’
select * from text1;

--------------hive 的压缩：
hive的map阶段压缩：
set hive.exec.compress.output=false;
set hive.exec.compress.intermediate=false;
hive.intermediate.compression.codec
hive.intermediate.compression.type

hive的reduce阶段压缩：
set hive.exec.compress.output=false;
set hive.exec.compress.intermediate=false;
hive.intermediate.compression.codec
hive.intermediate.compression.type

##############hive的运行方式：
hive的属性设置：
1、hive-site.xml
2、hive通过命令行参数设置
3、hive通过cli端set设置

区别：
1、属性优先级别从上往下一次升高。
2、hive-site.xml是全局和永久的，其它两是临时和局部。
3、hive-site.xml适合所有属性配置，而后两个对于系统级别的属性不能配置。
比如启动所需的元数据库url、log配置等。

hive有四类属性：
hiveconf：可读可写
hivevar：自定义临时变量，可读可写
system：可读可写
env：可读不可写

–hiveconf <property=value> Use value for given property
–hivevar <key=value> Variable subsitution to apply to hive
commands. e.g. --hivevar A=B
hive -e
hive -f
hive -S 静音模式
hive -i
hive --database

hive>set -v; ##查看hive相关的环境变量
hive -e “set” | grep current ##单个查找

hive三种运行方式：
在hive的cli端运行：(开发测试，以及临时跑作业)
通过命令行 hive -e ‘sql query’;
通过命令行 hive -f /hql文件 (生产线)

hive --database qf1701 -e ‘select * from text1’;
hive --database qf1701 --hivevar ls=2 --hiveconf tn=text1 -e ‘select * from ${hiveconf:tn} limit ${hivevar:ls}’;

hive -S --hivevar mapoutputdir=/home/hivedata/out/05 --hivevar textoutdir=/home/hivedata/out/06 --hivevar limit=1 -f ./hql

注意：
1、一个–hivevar 或者 --hiveconf 只能带一个参数
2、–hiveconf 或者 --hivevar 可以混合使用
3、–hiveconf 或 --hivevar 定义参数不能取消

---------hive 的远程模式安装-------------

----------hive的优化----------------
？？？
1、环境方面：服务器的配置、容器的配置、环境搭建
2、具体软件配置参数：
3、代码级别的优化：

1、explain 和 explain extended ：

explain select * from text1;
explain extended select * from text1;
explain extended
select
d.deptno as deptno,
d.dname as dname
from dept d
union all
select
d.dname as dname,
d.deptno as deptno
from dept d
;
explain ： 只有对hql语句的解释。
explain extended：对hql语句的解释，以及抽象表达式树的生成。

stage 相当于一个job，一个stage可以是limit、也可以是一个子查询、也可以是group by等。
hive默认一次只执行一个stage，但是如果stage之间没有相互依赖，将可以并行执行。
任务越复杂，hql代码越复杂，stage越多，运行的时间一般越长。

2、join
hive的查询永远是小表(结果集)驱动大表(结果集)
hive中的on的条件只能是等值连接
注意hive是否配置普通join转换成map端join、以及mapjoin小表文件大小的阀值

3、limit的优化：
hive.limit.row.max.size=100000
hive.limit.optimize.limit.file=10
hive.limit.optimize.enable=false (如果limit较多时建议开启)
hive.limit.optimize.fetch.max=50000

4、本地模式：
hive.exec.mode.local.auto=false (建议打开)
hive.exec.mode.local.auto.inputbytes.max=134217728
hive.exec.mode.local.auto.input.files.max=4

5、并行执行：
hive.exec.parallel=false (建议开启)
hive.exec.parallel.thread.number=8

6、严格模式：
hive.mapred.mode=nonstrict

Cartesian Product.
No partition being picked up for a query.
Orderby without limit.
Comparing bigints and strings.
Comparing bigints and doubles.

select
e.*
from dept d
join emp e
;

select
e.*
from emp e
order by e.empno desc
;

create table text9 (
uid bigint,
uid1 double
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ’ ’
;

LOAD DATA LOCAL INPATH ‘/home/hivedata/text8’ INTO TABLE text9;

select
*
from text9 t
where t.uid = t.uid1
;

7、mapper和reducer的个数：
不是mapper和redcuer个数越多越好，也不是越少越好。

将小文件合并处理(将输入类设置为：CombineTextInputFormat)
通过配置将小文件合并：
mapred.max.split.size=256000000
mapred.min.split.size.per.node=1
mapred.min.split.size.per.rack=1
hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat

手动设置：
set mapred.map.tasks=2;

reducer的个数(自动决定和手动设置)：
mapred.reduce.tasks=-1
hive.exec.reducers.max=1009

8、配置jvm重用：
mapreduce.job.jvm.numtasks=1 ###

mapred.job.reuse.jvm.num.tasks=1

9、数据倾斜：
数据倾斜：由于key分布不均匀造成的数据向一个方向偏离的现象。
本身数据就倾斜
join语句容易造成
count(distinct col) 很容易造成倾斜
group by 也可能会造成

找到造成数据倾斜的key，然后再通过hql语句避免。
hive.map.aggr=true
hive.groupby.skewindata=false (建议开启)
hive.optimize.skewjoin=false

Whether to enable skew join optimization.
The algorithm is as follows: At runtime, detect the keys with a large skew. Instead of
processing those keys, store them temporarily in an HDFS directory. In a follow-up map-reduce
job, process those skewed keys. The same key need not be skewed for all the tables, and so,
the follow-up map-reduce job (for the skewed keys) would be much faster, since it would be a
map-join.

10、索引是一种hive的优化：

11、分区本身就是hive的一种优化：

12、job的数量：
一般是一个查询产生一个job，然后通常情况一个job、可以是一个子查询、一个join、一个group by 、一个limit等一些操作。

mysql和hive的区别：
mysql用自己的存储存储引擎，hive使用的hdfs来存储。
mysql使用自己的执行引擎，而hive使用的是mapreduce来执行。
mysql使用环境环境几乎没有限制，hive是基于hadoop的。
mysql的低延迟，hive是高延迟。
mysql的handle的数据量较小，而hive的能handle数据量较大。
mysql的可扩展性较低，而hive的扩展性较高。
mysql的数据存储格式要求严格，而hive对数据格式不做严格要求。
mysql可以允许局部数据插入、更新、删除等，而hive不支持局部数据的操作。

nohup hive -e " select word ,count(*) as count from (select explode(split(ipcst,’ ')) as word from zgg_patent_clean.cpatent_20190122 ) w group by word order by word " >> ipcDistinct.txt 2>&1 &