Hadoop_14_hive基本操作

hive数据库与数据库表

创建数据库

创建一个数据库，就会再hdfs中创建一个文件夹。
hive的表存放位置模式是由hive-site.xml当中的一个属性指定的
hive.metastore.warehouse.dir
/user/hive/warehouse

创建数据库并指定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;

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], …)]
[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]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

external 定义我们的表为外部表
location 指定我们表里面的数据应该存放在hdfs的哪个位置
partitioned by 创建分区表 按照文件夹，将不同的数据，划分到不同的文件夹下面去
clustered by 分桶表 将我们的数据按照一定的规则，划分到多个文件里面去
store as 指定数据的存储格式 text sequenceFile parquet orc
row format 指定我们hive建表的分隔符，与我们hdfs的数据的分隔符保持一致，才能够映射hdfs上面的数据到对应的hive的表的字段里面来
like 允许用户复制现有的表结构，但是不复制数据。

四种常见的表模型

管理表

又叫做内部表 删除表的时候，hdfs上面对应的数据，同步删除。

建表
use myhive;
create table stu(id int,name string);
insert into stu values (1,“zhangsan”);
select * from stu;

创建表并指定字段之间的分隔符，存储格式，路径
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 stu2;

外部表

删表的时候不会删除hdfs上面的数据。
指定hdfs其他位置的路径的数据，加载到hive的表当中来，hive认为数据是从其他地方移动过来的，hive表没有完全的独占这份数据，删除表的时候不能够删除数据。

管理表和外部表的使用场景
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

分别创建老师与学生表外部表，并向表中加载数据
创建老师表：
create external 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’;

1. 从本地文件系统向表中加载数据
   load data local inpath ‘/export/servers/hivedatas/student.csv’ into table student;

2. 加载数据并覆盖已有数据
   load data local inpath ‘/export/servers/hivedatas/student.csv’ overwrite into table student;

3. 从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表使用的是外部表，drop table之后，表当中的数据依然保留在hdfs上面了

分区表

一般没有一种独立的表模型，只有内部分区表，或者外部分区表。

核心的思想：分治：数据量越少，跑起来就会越快

可以按照一定的规则，创建一些文件夹，可以根据指定的文件夹，找到我们指定的数据。比如每天创建一个。

创建分区表语法
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’);

外部分区表综合练习

现在有一个文件score.csv文件，存放在集群的这个目录下/scoredatas/month=201806，这个文件每天都会生成，存放到对应的日期文件夹下面去，文件别人也需要公用，不能移动。需求，创建hive对应的表，并将数据加载到表中，进行数据统计分析，且删除表之后，数据不能删除。

数据准备：
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’;

桶表的数据加载只能通过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);

修改与删除

表重命名
alter table old_table_name rename to new_table_name;
把表score4修改成score5
alter table score4 rename to score5;

增加/修改列信息
（1）查询表结构
desc score5;
（2）添加列
alter table score5 add columns (mycol string, mysco string);
（3）查询表结构
desc score5;
（4）更新列
alter table score5 change column mysco mysconew int;
（5）查询表结构
desc score5;

删除表
drop table score5;

hive数据加载

通过load方式加载数据
load data local inpath ‘/export/servers/hivedatas/score.csv’ overwrite into table score partition(month=‘201806’);

通过查询方式加载数据
create table score4 like score;
insert overwrite table score4 partition(month = ‘201806’) select s_id,c_id,s_score from 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查询语法

语法结构
SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

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的功能外，还会对该字段进行排序。
5. group by : select的字段 只能少于等于group by的字段

LIKE和RLIKE
1）查找以8开头的所有成绩
select * from score where s_score like ‘8%’;
2）查找第二个数值为9的所有成绩数据
select * from score where s_score like ‘_9%’;
3）查找成绩中含9的所有成绩数据
select * from score where s_score rlike ‘[9]’;

逻辑运算符（AND/OR/NOT）
1）查询成绩大于80，并且s_id是01的数据
select * from score where s_score >80 and s_id = ‘01’;
2）查询成绩大于80，或者s_id 是01的数
select * from score where s_score > 80 or s_id = ‘01’;
3）查询s_id 不是 01和02的学生
select * from score where s_id not in (‘01’,‘02’);

GROUP BY语句
1）计算每个学生的平均分数
select s_id ,avg(s_score) from score group by s_id;
2）计算每个学生最高成绩
select s_id ,max(s_score) from score group by s_id;

HAVING语句
1）求每个学生的平均分数
select s_id ,avg(s_score) from score group by s_id;
2）求每个学生平均分数大于85的人
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

JOIN语句
1）查询分数对应的姓名
SELECT s.s_id,s.s_score,stu.s_name,stu.s_birth FROM score s LEFT JOIN student stu ON s.s_id = stu.s_id

hive排序

全局排序（Order By）
查询学生的成绩，并按照分数降序排列
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;

多个列排序
按照学生id和平均成绩进行排序
select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;

每个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;

4）将查询结果导入到文件中（按照成绩降序排列）
insert overwrite local directory ‘/export/servers/hivedatas/sort’ select * from score sort by s_score;

分区排序（DISTRIBUTE BY）
Distribute By：类似MR中partition，进行分区，结合sort by使用。
Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

需求：先按照学生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;

CLUSTER BY
当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。

以下两种写法等价
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

hive Shell参数

hive当中的三种参数设置
第一种 : hive-site.xml 对所有的hive的客户端都有效

第二种：命令行的参数 bin/hive -hiveconf 参数名=参数值 对我们进入的这次会话有效
bin/hive -hiveconf hive.root.logger=INFO,console
这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。

第三种 ：参数声明 对当前的sql语句生效 set 参数名=参数值
set mapred.reduce.tasks=100;
这一设定的作用域也是session级的。

参数声明 > 命令行参数 > 配置文件参数（hive）

hive函数

查看系统自带的函数
hive> show functions;
显示自带的函数的用法
hive> desc function upper;
详细显示自带的函数的用法
hive> desc function extended upper;

用户自定义函数
UDF（User-Defined-Function）
一进一出
UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
UDTF（User-Defined Table-Generating Functions）
一进多出
如lateral view explore()

官方文档地址

编程步骤

1. 继承org.apache.hadoop.hive.ql.UDF
2. 需要实现evaluate函数；evaluate函数支持重载；

注意事项

1. UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
2. UDF中常用Text/LongWritable等类型，不推荐使用java类型；

自定义函数示例

1. 开发java类继承UDF，并重载evaluate 方法

package udf;

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

public class CustomUDF extends UDF {
    //定义方法evaluate
    //实现：将小写字母转大写字母

    public Text evaluate(Text inputData) {
        if (null != inputData && !inputData.toString().equals("")) {
            String s = inputData.toString().toUpperCase();
            return new Text(s);
        }else {
            return new Text("");
        }
    }
}

2. 将我们的项目打包，并上传到hive的lib目录下

3. 添加jar包并重命名，添加到hive客户端
   cd /export/servers/hive-1.1.0-cdh5.14.0/lib
   mv original-day_06_hive_udf-1.0-SNAPSHOT.jar udf.jar
   add jar /export/servers/hive-1.1.0-cdh5.14.0/lib/udf.jar;

4. 设置函数与我们的自定义函数关联
   reate temporary function touppercase as ‘udf.CustomUDF’;

5. 使用自定义函数
   select touppercase(‘abc’);

hive自定义函数步骤
第一步：常见工程，导入jar包
第二步：写一个java类，集成 UDF
第三步：定义一个方法 方法名 evaluate，必须有返回值，而且还有一个参数，表示接收我们输入的数据
第四步：定义我们udf的逻辑
第五步：打成jar包放到hive的lib目录下
第六步：hive的客户端 add jar 添加我们的jar包
第七步：设置临时函数与我们自定义的udf进行关联
第八步：使用udf

hive的数据压缩

开启Map输出阶段压缩

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输出阶段压缩

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的数据存储格式

Hive支持的存储数的格式主要有：TEXTFILE（行式存储） 、SEQUENCEFILE(行式存储)、ORC（列式存储）、PARQUET（列式存储）。

行存储的特点： 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

TEXTFILE格式
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

ORC格式
列式存储
一个orc文件由多个stripe组成，一个stripe由三块构成 Index Data,Row Data,Stripe Footer。
index data：数据的索引数据
row data 存放我们的数据
stripe footer:stripe的元数据信息

PARQUET格式
Parquet是面向分析型业务的列式存储格式。是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。