1.hive的基本操作
1.1建库语法
create database if not exists myhive;
use myhive;
创建的数据库默认路径是在：
创建数据库并制定hdfs存储位置
create database myhive2 location ‘/myhive2’;
修改数据库信息
可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置
alter database myhive2 set dbproperties(‘createtime’=‘20180611’);
查看数据库基本信息
desc database myhive2;
查看数据库更多详细信息
desc database extended myhive2;
删除数据库
drop database myhive2
（如果数据库下面有数据表，那么就会报错）
drop database myhive cascade;
（强制删除数据库，包含数据库下面的表一起删除）
1.2建表语法
external 定义我们的表为外部表
location 指定我们表里面的数据应该存放在hdfs的哪个位置
partitionedby 创建分区表 按照文件夹，将不同的数据，划分到不同的文件夹下面去
clustered by 分桶表 将我们的数据按照一定的规则，划分到多个文件里面去
store as 指定数据的存储格式 text sequenceFile parquet orc
row format 指定我们hive建表的分隔符，与我们hdfs的数据的分隔符保持一致，才能够映射hdfs上面的数据到对应的hive的表的字段里面来
LIKE 允许用户复制现有的表结构，但是不复制数据。
hive当中的四种常见的表模型：
第一种表模型：管理表，又叫做内部表
特性：删除表的时候，hdfs上面对应的数据，也会同步删除
hive建表初体验
use myhive;
create table stu(id int,name string);
insert into stu values (1,“zhangsan”);
select * from stu;
hive当中的默认分隔符： \001 键盘打不出来 asc码值 非打印字符 避免分隔符的冲突
创建表指定字段之间的分隔符
create table if not exists stu2(id int ,name string) row format delimited fields terminated by ‘\t’ stored as textfile location ‘/user/stu2’;
根据查询结果创建表，复制表结构，并且复制表数据到我们创建的表里面去
create table stu3 as select * from stu2;
仅仅复制表结构，不复制表数据
create table stu4 like stu2
查看表的类型
desc formatted tableName;
第二种外部表
外部表：删表的时候不会删除hdfs上面的数据
原因;指定hdfs其他位置的路径的数据，加载到hive的表
hive认为数据是从其他的方移动过来的，hive表没有完全地独占这份数据，删除表的时候不能够删除数据
分别创建老师与学生表外部表，并向表中加载数据
创建老师表：
createexternal table techer (t_id string,t_name string) row format delimited fields terminated by’\t’;
创建学生表：
create external table student(s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by ‘\t’;
从本地文件系统向表中加载数据
load data local inpath ‘/export/servers/hivedatas/student.csv’ into table student;
加载数据并覆盖已有数据
load data local inpath ‘/export/servers/hivedatas/student.csv’ overwrite into table student;
从hdfs文件系统向表中加载数据（需要提前将数据上传到hdfs文件系统，其实就是一个移动文件的操作）
cd /export/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put techer.csv /hivedatas/
load data inpath ‘/hivedatas/techer.csv’ into table techer;
如果删掉student表，hdfs的数据仍然存在，并且重新创建表之后，表中就直接存在数据了,因为我们的student表使用的是外部表，drop table之后，表当中的数据依然保留在hdfs上面了
内部表与外部表的创建：
external关键字决定了我们是内部表还是外部表
内部表：删表的时候，同时删除hdfs的数据
外部表：删表的时候，不会删除hdfs上面的数据
外部表：
本地磁盘：local
hdfs文件系统：不加local
load data [local] inpath ‘/export/servers/hivedatas/techer.csv’ into [overwrite] into table techer;
从本地加载数据的时候：本地数据没有动
从hdfs上面加载数据的时候：把数据移动到了我们hive表的location位置
如果没有指定location位置，那么默认表的位置在/usr/hive/warehouse/dbname/tablename
第三种分区表
分区表：一般没有一种独立的表模型，只有内部分区表，或者外部分区表
核心的思想，也是分治：数据量越少，跑起来就会越快
可以按照一定的规则，创建一些文件夹，可以根据指定的文件夹，找到我们指定的数据
创建分区表语法
create table score(s_id string,c_id string, s_score int) partitioned by (month string）row format delimited fields terminated by ‘\t’;
创建一个表带多个分区
create table score2 (s_id string,c_id string, s_score int) partitioned by (year
string,month string,day string) row format delimited fields terminated by ‘\t’;
加载数据到分区表中
load data local inpath ‘/export/servers/hivedatas/score.csv’ into table score partition (month=‘201806’);
加载数据到一个多分区的表中去
load data local inpath ‘/export/servers/hivedatas/score.csv’ into table score2 partition(year=‘2018’,month=‘06’,day=‘01’);
多分区联合查询使用union all来实现
select * from score where month = ‘201806’ union all select * from score where month =‘201806’;
查看分区
show partitions score;
添加一个分区
alter table score add partition(month=‘201805’);
同时添加多个分区
alter table score add partition(month=‘201804’) partition(month = ‘201803’);
注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹
删除分区 alter table score drop partition(month = ‘201806’);
数据准备：
hdfs dfs -mkdir -p /scoredatas/month=201806
hdfs dfs -put score.csv /scoredatas/month=201806/
创建外部分区表，并指定文件数据存放目录
create external table score4(s_id string, c_id string,s_score int) partitioned by
(month string) row format delimited fields terminated by ‘\t’ location
‘/scoredatas’;
进行表的修复,说白了就是建立我们表与我们数据文件之间的一个关系映射
msck repair table score4;
修复成功之后即可看到数据已经全部加载到表当中去了
第四种表模型 桶表
一般也是与内部表或者外部表搭配使用
可以将我们的数据按照一定的规则，划分成多个文件里面去
分桶表我们指定某一个字段进行分桶，其实就是将这个字段运行了一把mr的程序，以这个字段作为key2,
应用mr的分区规则（HashParttiioner） 通过多个reducer输出多个文件
开启hive的桶表功能
set hive.enforce.bucketing=true;
设置reduce的个数
set mapreduce.job.reduces=3;
创建通表
create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by ‘\t’;
桶表的数据加载，由于通标的数据加载通过hdfs dfs -put文件或者通过load data均不好使，只能通过insert overwrite
创建普通表，并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去
创建普通表：
create table course_common (c_id string,c_name string,t_id string) row format
delimited fields terminated by ‘\t’;
普通表中加载数据
load data local inpath ‘/export/servers/hivedatas/course.csv’ into table
course_common;
通过insert overwrite给桶表中加载数据
insert overwrite table course select * from course_common cluster by(c_id);
分桶表我们指定某一个字段进行分桶，其实就是将这个字段运行了一把mr的程序，以这个字段作为key2,应用mr的分区规则（HashParttiioner） 通过多个reducer输出多个文件
修改表
1.修改表名
基本语法：
alter table old_table_name rename to new_table_name;
增加/修改列信息
（1）查找表结构：desc table_name;
（2）添加列：alter table score5 add columns(mysol string,mysco string);
（3）更新列：alter table score5 change column mysco mysconew int；
（4）删除表：drop table score5;
hive当中的数据加载(掌握)
1.直接向分区表中插入数据（不推荐使用）
create table score3 like score;
insert into table score3 partition(mouth=‘201807’) values(‘001’,‘002’,‘100’);
2.通过查询插入数据
2.1 load data local inpath ‘/export/servers/hivedatas/score.csv’ overwrite into table score partition(month=‘201806’);
2.2通过查询方式加载数据
create table score4 like score;
insert overwrite[a1] table score4 partition(month = ‘201806’) select s_id,c_id,s_score from score;
注意：overwrite必须有
3.多插入模式
常用于实际生产环境当中，将一张表拆开成两部分或者多部分
给score表加载数据
load data local inpath ‘/export/servers/hivedatas/score.csv’ overwrite into table score partition(month=‘201806’);
创建第一部分表：
create table score_first( s_id string,c_id string) partitioned by (month string) row format delimited fields terminated by ‘\t’ ;
创建第二部分表：
create table score_second(c_id string,s_score int) partitioned by (month string) row
format delimited fields terminated by ‘\t’;
分别给第一部分与第二部分表加载数据
from score insert overwrite table score_first partition(month=‘201806’) select s_id,c_id insert overwrite table score_second partition(month =‘201806’) select c_id,s_score;
查询语句中创建表并加载数据（as select）
将查询的结果保存到一张表当中去
create table score5 as select * from score
创建表时通过location指定加载数据路径
1） 创建表，并指定在hdfs上的位置
create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ‘\t’ location ‘/myscore6’;
2）上传数据到hdfs上
hdfs dfs -mkdir -p /myscore6
hdfs dfs -put score.csv /myscore6;
3）查询数据
select * from score6;
export导出与import 导入
hive表数据（内部表操作）
create table techer2 like techer;
export table techer to ‘/export/techer’;
import table techer2 from ‘/export/techer’;
hive表中的数据导出（了解）
1.insert导出
2.Hadoop命令导出到本地
3.hive shell 命令导出
4.export导出到HDFS上
5.sqoop导出
清空表数据
只能清空管理表，也就是内部表
truncate table score6;
清空这个表会报错
hive的查询语法
1.SELECT
语法结构
注：1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2、**sort by不是全局排序，其在数据进入reducer前完成排序。**因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。
全表查询
select * from score;
选择特定列查询
select s_id ,c_id from score;
列别名
select s_id as myid ,c_id from score;
常用函数
1）求总行数（count）
select count(1) from score;
2）求分数的最大值（max）
select max(s_score) from score;
3）求分数的最小值（min）
select min(s_score) from score;
4）求分数的总和（sum）
select sum(s_score) from score;
5）求分数的平均值（avg）
select avg(s_score) from score;
3.2.3、LIMIT语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
select * from score limit 3;
3.2.4、WHERE语句
1）使用WHERE 子句，将不满足条件的行过滤掉。
2）WHERE 子句紧随 FROM 子句。
3）案例实操
查询出分数大于60的数据
select * from score where s_score > 60;
比较运算符（BETWEEN/IN/ IS NULL）
下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。
（1）查询分数等于80的所有的数据
select * from score where s_score = 80;
（2）查询分数在80到100的所有数据
select * from score where s_score between 80
and 100;
（3）查询成绩为空的所有数据
select * from score where s_score is null;
（4）查询成绩是80和90的数据
select * from score where s_score in(80,90);
LIKE和RLIKE
分组
GROUP BY语句
注意：select的字段 只能少于等于group by的字段
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作
（1）计算每个学生的平均分数
select s_id ,avg(s_score) from score groupby s_id;
（2）计算每个学生最高成绩
select s_id ,max(s_score) from score group by s_id;
HAVING语句
having与where不同点
（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。
2）案例实操：
求每个学生的平均分数
select s_id ,avg(s_score) from score group by s_id;
求每个学生平均分数大于85的人
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
等值JOIN
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
表的别名
select * from techer t join course c on t.t_id = c.t_id;
内连接（INNER JOIN）：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select * from techer t inner join course c on t.t_id =c.t_id;
左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
查询老师对应的课程
select * from techer t left join course c on t.t_id = c.t_id;
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select * from techer t right join course c on t.t_id =c.t_id;
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
SELECT * FROM techer
t FULL JOIN course c ON t.t_id = c.t_id ;
多表连接
注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生
select * from techer t
left join course c
on t.t_id = c.t_id
left join score s
on s.c_id = c.c_id
left join student stu
on s.s_id = stu.s_id;
大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表techer和表course进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表score;进行连接操作。
数据的差值查询（不要使用NOT IN ，因为NOT IN效率非常低下）
排序
1.全局排序（Order By）
Order By：全局排序，一个reduce
1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2）ORDER BY 子句在SELECT语句的结尾。
3）案例实操
（1）查询学生的成绩，并按照分数降序排列
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC
（2）查询学生的成绩，并按照分数升序排列
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score asc;
2.按照别名排序
按照学生分数的平均值排序
select s_id ,avg(s_score) avg from score group by s_id order by avg;
3.多个列排序
按照学生id和平均成绩进行排序（二次排序）
select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;
4.每个MapReduce内部排序（Sort By）局部排序
Sort By：每个MapReduce内部进行排序，对全局结果集来说不是排序。
1）设置reduce个数
set mapreduce.job.reduces=3;
2）查看设置reduce个数
set mapreduce.job.reduces;
3）查询成绩按照成绩降序排列
select * from score sort by s_score;
将查询结果导入到文件中（按照成绩降序排列）
insert overwrite local directory ‘/export/servers/hivedatas/sort’ select * from score
sort by s_score;
5.分区排序（DISTRIBUTE BY）
Distribute By：类似MR中partition，进行分区，结合sort by使用。
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
（1） 先按照学生id进行分区，再按照学生成绩进行排序。
设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去
set mapreduce.job.reduces=7;
通过distribute by 进行数据的分区
insert overwrite local directory ‘/export/servers/hivedatas/sort’ select * from score distribute by s_id sort by s_score;
6.CLUSTER BY
当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。
1） 以下两种写法等价
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;
Hive Shell参数
1.Hive命令行
2.Hive参数配置方式
Hive参数大全：
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
对于一般参数，有以下三种设定方式：
1.配置文件 hive-site.xml（用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml）
2.命令行参数 启动hive客户端的时候可以设置参数（如：bin/hive -hiveconf
hive.root.logger=INFO,console）
3.参数声明 进入客户单以后设置的一些参数 set （如：set mapred.reduce.tasks=100;）
上述三种设定方式的优先级：参数声明 > 命令行参数 > 配置文件参数（hive）
Hive函数
1.内置函数
《Hive官方文档》https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
1）查看系统自带的函数
hive> show functions;
2）显示自带的函数的用法
hive> desc function upper;
3）详细显示自带的函数的用法
hive> desc function extended upper;
hive的自定义函数：
第一种：UDF 一行数据进去，一行数据出来
第二种：UDAF 多行进，一行出
第三种：UDTF 一行进 多行出
UDF开发实例
第一步：常见工程，导入jar包
第二步：写一个java类，集成 UDF
第三步：定义一个方法 方法名 evaluate 必须有返回值，而且还有一个参数，表示接收我们输入的数据
第四步：定义我们udf的逻辑
第五步：打成jar包放到hive的lib目录下
第六步：hive的客户端 add jar 添加我们的jar包
第七步：设置临时函数与我们自定义的udf进行关联
第八步：使用udf
hive的数据压缩
开启Map输出阶段压缩
开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下：
案例实操：
1）开启hive中间传输数据压缩功能
hive (default)>set hive.exec.compress.intermediate=true;
2）开启mapreduce中map输出压缩功能
hive (default)>set mapreduce.map.output.compress=true;
3）设置mapreduce中map输出数据的压缩方式
hive (default)>set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
4）执行查询语句
select count(1) from score;
开启Reduce输出阶段压缩
当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。
案例实操：
1）开启hive最终输出数据压缩功能
hive (default)>set hive.exec.compress.output=true;
2）开启mapreduce最终输出数据压缩
hive (default)>set mapreduce.output.fileoutputformat.compress=true;
3）设置mapreduce最终数据输出压缩方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
4）设置mapreduce最终数据输出压缩为块压缩
hive (default)>set mapreduce.output.fileoutputformat.compress.type=BLOCK;
5）测试一下输出结果是否是压缩文件
insert overwrite local directory ‘/export/servers/snappy’ select * from score distribute by s_id sort by s_id desc;
hive的数据存储格式
研究的是使用哪种数据的存储格式，让我们的数据更加紧凑，取数据的时候会更加快速，让数据占用磁盘空间更少，压缩和解压缩的时候更加的快速
存储格式主要有四种：text sequenceFile parquet orc
分为两大类：
行式存储：text sequenceFile
列式存储：parquet orc
orc文件存储格式 列式存储
一个orc文件由多个stripe组成，一个stripe由三块构成，分别是Index Data,Row
Data,Stripe Footer；
index data：数据的索引数据
row data 存放我们的数据
stripe footer:stripe的元数据信息
parquet：存储格式 列式存储