hive基础

• hive其实是一个数据仓库工具，用来读取hdfs上的数据，来作为离线查询使用，然后用的是mapreduce，那么速度必然是比较慢的，在实际工作中一个任务的时间会根据数据量的大小和sql语句的性能而波动。
• 在日常开发中 Hive 用的还是挺广泛的，常做一些统计工作，就我自己工作来看其实 70% 的工作由 Hive 的基础部分就能完成了，只有很少的情况需要用到一些复杂查询或者调优工作。
  本文主要内容如下：

1. hive查询原理
2. 内部表&外部表
3. 分区表
4. 分桶表
5. 数据导入
6. 复合类型建表
7. 4个by
8. 工作中实际需求举例

1.hive查询原理

大家都知道hive的底层其实是讲hql转化成一个个MapReduce来完成任务的，这里我们先来简单看一下hive的查询过程
1 根据HDFS上要存放的数据格式，创建hive表

2 通过映射关系将HDFS数据导入到表中

3 此时hive表对应的元数据信息记录到 mysql 中，元数据可不是指的HDFS上的数据，它是指 hive 表的一些参数。

4 写 select 语句时，根据表与数据的映射关系去写对应的查询语句

5 在执行查询操作时 ,先从元数据库中找到 对应表对应的文件位置，

再通过 hive 的 解析器、编译器、优化器 执行器 将 sql 语句 转换成 MR 程序，运行在 Yarn 上，最终得到结果。

PS：Hive 里有三种查询方式，分别是bin/hive (客户端)、beeline、webUI(zeppelin)。

2.内部表外部表区别

Hive 表与常规的数据库表不同，它分为内部表和外部表，它们的区别删除表时有所不同。
未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；
区别：
内部表数据由Hive自身管理，外部表数据由HDFS管理；

删除表时：

内部表删除，数据一起删除

外部表不会删除数据

所以区别就很明显了，一般工作中使用外部表做为数据映射，即使drop表也不会删除hdfs上的数据、保证了数据的安全性，而统计出的结果一般多使用内部表，因为内部表仅仅用于储存结果或者关联，与 HDFS上的外部表数据无关。

那么怎么区分表是外部表或者是内部表呢？
对于已经创建的表可以使用：
desc formatted 表名即可查看。

对于新建表：
使用建表语句时即可区分，其中带 EXTERNAL 的是外部表，不带的则是内部表。

建表语句如下：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] dbName.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] row format delimited fields terminated by ‘分隔符’

[STORED AS file_format] – 以什么形式存储

[LOCATION hdfs_path] – 对应HDFS文件路径

3.hive 分区表

分区表在工作中用的是蛮多的，一般根据实际场景中的最细粒度去分区，一般以自然年月作为分区，这样数据也比较好管理。在数据查询的时候，指定分区查询也会更节省服务器资源。

不加分区的情况 ：
select col1,col2 … from table1 where xxx

这样会扫描全表数据，假如数据量比较大，那执行时间大家可想而知。

添加分区情况 ：
select col1,col2 … from table1 where (year = ‘2021’ and month=‘09’);

这样的话就只会查询2021年09月的数据了，善用分区会大大提升查询效率。

那分区怎么创建呢？

举个例子，一个分区字段的分区表就不说了，多个分区分区表 sql 如下：

create table student (id string,name string, age int)
partitioned by (year string,month string,day string)
row format delimited fields terminated by ‘\t’;

分区划分：

分区分为静态分区和动态分区。

静态分区需要人为指定分区，并且需要明确分区字段
举例 sql 如下：

1 创建分区表：

create table mydemo.order_partition(

order_id string,

order_price double,

order_time string

)

partitioned by(year string,month string)

row format delimited fields terminated by ‘\t’;

2 准备数据 在 order.txt 中内容如下：

10001 100 2021-03-02
10002 200 2021-03-02
10003 300 2021-03-02
10004 400 2021-03-03
10005 500 2021-03-03
10006 600 2021-03-03
10007 700 2021-03-04
10008 800 2021-03-04
10009 900 2021-03-04

3 将本地文件加载到 表中

load data local inpath ‘/opt/data/order.txt’ overwrite into table mydemo.order_partition partition(year=‘2021’,month=‘03’)

这里再最后指定分区为 year=‘2021’,month=‘03’，所以以上所有内容都会在这个分区中。分区可以手动添加、删除。

– 查看有几个分区
show partitions order_partition

结果如下：
year=2021/month=03

– 增加分区
alter table order_partition add partition(year=‘2021’,month=‘04’)

– 增加多个分区，不加载数据
alter table order_partition add partition(year=‘2021’ , month=‘05’) partition(year=‘2021’ , month=‘06’)

– 增加多个分区。准备数据
hdfs dfs -cp /hive312/warehouse/mydemo.db/order_partition/year=2021/month=03 /hive312/warehouse/mydemo.db/order_partition/year=2021/month=07
hdfs dfs -cp /hive312/warehouse/mydemo.db/order_partition/year=2021/month=03 /hive312/warehouse/mydemo.db/order_partition/year=2021/month=08

注意 这边只是复制了month=07，08 两个文件夹，这时候使用show partitions order_partition，会发现分区里面是没有07和08的，也就是元数据没发生改变，通俗的来讲就是，数据在了，但是查不到，这里和本来有一张外部表，然后drop外部表的效果是一样的

– 增加多个分区。加载数据
alter table order_partition add partition(year=‘2021’ ,month=‘07’) location ‘/hive312/warehouse/mydemo.db/order_partition/year=2021/month=07’ partition(year=‘2021’ ,month=‘08’) location ‘/hive312/warehouse/mydemo.db/order_partition/year=2021/month=08’

执行完此条语句，才会在元数据表中补上07和08两个分区

– 修改分区的hdfs路径（谨慎使用)：
alter table order_partition partition(year=‘2021’ , month=‘03’) set location ‘/hive312/warehouse/mydemo.db/order_partition/year=2021/month=04’

– 可以删除一个或多个分区，用逗号隔开
alter table order_partition drop partition(year=‘2021’ , month=‘04’), partition(year=‘2021’ , month=‘05’);

– 查看某个分区的数据(直接当成普通列来用)：
select * from order_partition where year=‘2021’ and month=‘04’;

注意：分区的字段，不是表中已经存在的数据，可以将分区字段看成伪列

动态分区则可以将数据自动导入表的不同分区中，与静态分区不同的是 动态分区只需要指定分区字段，不需要明确分区字段的值。
例如：

1 创建分区表：

–创建普通表

create table t_order(

order_id string,

order_price double,

order_time string

)row format delimited fields terminated by ‘\t’

–创建目标分区表

create table order_dynamic_partition(

order_id string,

order_price double

)partitioned BY(order_time string)

row format delimited fields terminated by ‘\t’

2 准备数据 order2.txt内容

10001 100 2021-03-02
10002 200 2021-05-02
10003 300 2021-08-02
10004 400 2021-03-03
10005 500 2021-04-03
10006 600 2021-06-03
10007 700 2021-09-04
10008 800 2021-12-04
10009 900 2021-01-04

3 向普通表t_order加载数据

load data local inpath ‘/opt/data/order2.txt’ overwrite into table t_order

这里没有手动指明分区字段的值，而是根据分区字段内部自己判断数据落在哪个分区中。

4 动态加载数据到分区表中

要想进行动态分区，需要设置参数：

//开启动态分区功能

hive> set hive.exec.dynamic.partition=true;

//设置hive为非严格模式

hive> set hive.exec.dynamic.partition.mode=nonstrict;

//从普通表向动态表加载数据

hive> insert into table order_dynamic_partition partition(order_time) select order_number,order_price,order_time from t_order

//查看分区情况
show partitions order_dynamic_partition

4.hive分桶表

分桶规则：分桶字段.hashCode % 分桶数

分桶表加载数据时，先创建分桶表结构,再使用 insert……select…… 方式进行

网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive 1.x 以前的版本;Hive 2.x 中，不需要设置此参数，默认开启，始终可以分桶;

1 开启分桶

set hive.enforce.bucketing = true

2 设置桶个数

set mapreduce.job.reduces = 4

创建分桶表：
// 1 创建分桶表

create table user_bucket_demo(id int,name string)

clustered by (id)

into 4 buckets

row format delimited fields terminated by ‘\t’

// 2 创建普通表

create table user_demo(id int,name string)

row format delimited fields terminated by ‘\t’

// 3 加载本地数据到普通表

load data local inpath ‘/opt/data/user_bucket.txt’ overwrite into table user_demo

注意：

//使用这个方式 加载数据到分桶表，数据不会分桶(在hive1.x版本中)
在高版本hive，如hive3.1.2中直接load的方法，也会将数据进行正确的分桶，毕竟高版本跟新了更多的功能,但一般不建议这样使用

load data local inpath ‘/opt/data/user_bucket.txt’ into table user_bucket_demo

// 4 正确的分桶表加载数据方式:

insert into user_bucket_demo select * from user_demo

// 5 查看结果(对表进行抽样)

select * from user_bucket_demo tablesample(bucket 1 out of 2)

– bucket x out of y
– 确定3个值 n=总桶数 x=抽桶的开始位置 y 2^x =抽取总桶数的比例 n/y=2/2=1
– 假设 n=10 y=5 抽取比例 抽取比例10/5=2 x=1 第一个桶1 第2个桶 x+y=6
– 假设 n=64 y=32 抽取比例 抽取比例64/32=2 x=1 第一个桶1 第2个桶 x+y=33
– 假设 n=64 y=2 抽取比例 抽取比例64/2=32 x=1 第一个桶1 第2个桶 x+y=3 5 7 9
– 假设 n=2 y=4 x=1 抽取比例 抽取比例1/2 可以写 无法抽取

– 分桶表tablesample(bucket x out of y on id)
– 普通标抽数据
– 随机抽x行 tablesample(x rows)
– 随机抽x比例(x<=100) tablesample(x percent)
– 随机抽x文件尺寸 (K,M,G) tablesample(x [K,M,G])

– select * from user_bucket_demo tablesample(bucket 1 out of 2 on id)
– select * from user_bucket_demo tablesample(10 percent)
– select * from user_bucket_demo tablesample(10 rows)
– select * from user_bucket_demo tablesample(16b)

– 需要的总桶数=4/2=2个

– 先从第1个桶中取出数据

– 再从第1+2=3个桶中取出数据

5 Hive数据导入

数据导入一般是初始化的工作，一般将表与 HDFS 路径映射好之后，后续的分区数据会自动与表做好映射。所以这块一般来说用的不多，在自己测试时使用的居多吧。

数据导入方式如下：

load 方式加载数据

这种方式在之前分区表时已经使用过了。

load data [local] inpath ‘dataPath’ [overwrite ] into table student [partition (partcol1=val1,…)];

注意:写overwrite 是全量表 不写overwrite是 增量表

添加 local 表示从本地加载，不添加表示从 HDFS 上加载

添加 overwrite 表示 覆盖原表数据 ，不添加 overwrite 表示追加

添加 partition 表示向某个分区添加数据

查询方式加载数据

insert overwrite（into） table tableName partition(month = ‘202109’) select column1,column2 from otherTable

查询语句中创建表并加载数据
只能建内部表 适用于mysql oracle hive

create table tableName as select * from otherTable

只复制结构 数据不复制 hive
create table mydemo.usi2 like mydemo.usi

使用location 指定加载数据路径（常用）

1 创建表，并指定HDFS上路径

create external table score (s_id string,c_id string,s_score int)
row format delimited fields terminated by ‘\t’
location ‘/myscore’

2 上传数据到 HDFS 上
小技巧: 可在 Hive客户端通过 dfs 命令操作 HDFS和黑界面效果是一样的

//创建 HDFS 路径

dfs -mkdir -p /myscore

//上传数据到 HDFS 上

dfs -put /opt/data/score.csv /myscore

3.查看结果

select * from score

注意：

如果查询不到数据 可使用 ：

msck repair table score;

进行表的修复,说白了就是建立我们表与我们数据文件之间的一个关系映射

解决通过hdfs dfs -put或者hdfs api写入hive分区表的数据在hive中无法被查询到的问题。

我们知道hive有个服务叫metastore，
这个服务主要是存储一些元数据信息，
比如数据库名，表名或者表的分区等等信息。
如果不是通过hive的insert,load等插入语句，
很多分区信息在metastore中是没有的，
如果插入分区数据量很多的话，
你用 alter table table_name add partition(parName=‘xxx’)一个个分区添加十分麻烦。
这时候msck repair table tableName就派上用场了。
只需要运行msck repair table tableName命令，
hive就会去检测这个表在hdfs上的文件，
把没有写入metastore的分区信息写入metastore。

6 使用复合数据类型建表

Hive 中复合数据类型有 Array、Map、Struct 这三种。

Array 代表数组，类型相同的数据

Map kv键值对

Struct 则存储类型不同的一组数据

创建表时除需要指定每行的分隔符（row format），要是有复合类型的还需要指定复合类型的分隔符。

复合数据建表语句：

create table tablename (id string,name string,…)

row format delimited fields terminated by ’ ’

Collection items terminated by ‘\t’ – array Array、Struct 的统一分隔符

Map keys terminated by ‘:’ --kv之间的 分隔符

查询使用：

array – select colName[0]

map – colName[‘name’]

struct – colName.name colName.age

测试案例：

Array
准备测试数据文档 t_array.txt，多个字段使用“，”拼接

数据：

1 zhangsan beijing,shanghai

2 lisi shanghai,tianjin

建表：

create table t_array(

id string,

name string,

locations array

)

row format delimited fields terminated by ’ ’ collection items terminated by ‘,’;

加载数据到表中

load data local inpath ‘/home/hadoop/data/01/t_array.txt’ into table t_array;

测试查询结果：

1 简单查询：

select id,locations[0],locations[1] from t_array;

2 查询数组中元素个数

select size(locations) from t_array

3 查询locations中包含 beijing 的信息

select * from t_array

where array_contains(address,‘beijing’)

Map

准备测试数据文档t_map.txt

数据：

1 name:zhangsan#age:30

2 name:lisi#age:40

建表：

create table t_map

(id string,info map<string,string>)

row format delimited fields terminated by ’ ’

collection items terminated by ‘#’ — 表示多个 KV 之间拼接的符号

map keys terminated by ‘:’ ----- 表示一个 KV 间的分隔符

加载数据：

load data local inpath ‘/home/hadoop/data/01/t_map.txt’ into table t_map;

查询结果：

1 简单查询：

select id,info[‘name’],info[‘age’] from t_map;

2 查询 map 的所有 key 值：

select map_keys(info) from t_map;

3 查询 map_values 所有 value 值：

select map_values(info) from t_map;

Struct

准备测试数据文档t_struct.txt

数据：

1 zhangsan:30:beijing

2 lisi:40:shanghai

建表：

create table t_struct(id string,info structname:string,age:int,address:string)

row format delimited fields terminated by ’ ’ --字段间分隔符

collection items terminated by ‘:’ – struct间分隔符

加载数据：

load data local inpath ‘’ into table t_struct;

查询结果：

select id,info.name,info.age,info.address from t_struct;

7 Hive 中 4 个 by 的区别

distribute by与group by 的区别

都是按key值划分数据 都使用reduce操作 **唯一不同的是，distribute by只是单纯的分散数据，distribute by col – 按照col列把数据分散到不同的reduce。而group by把相同key的数据聚集到一起，后续必须是聚合操作。

order by与sort by 的区别

order by是全局排序 sort by只是确保每个reduce上面输出的数据有序。如果只有一个reduce时，和order by作用一样。

order by
全局排序，只有一个reduce

sort by
对每一个reducer内部的数据进行排序，全局结果集来说不是排序的，即只能保证每一个reduce输出的文件中的数据是按照规定的字段进行排序的
insert overwrite local directory ‘’ select * from table_name sort by colum_name

distribute by
注意：这边需要设置reduces的数量为分区的数量，否则不会启动相应的reducer去进行任务的执行，这最终会导致不能完全分区
set mapreduce.job.reduces=n;

针对于注意事项，假设我们现在有员工信息表如下：

empno   ename   sal   deptno
1       wjl     100.0   1
2       sry      30.0    1
3       yw     10.0    2
4       yyn     100.0   2
5       scm      15.0    3
6       lyx      30.0    3

现在我们需要按照deptno进行分区，然后按照sal进行排序，假如我们没有设置rdeucer的数量，那么有可能我们的集群只会启动一个reducer处理这批数据，那么最后的结果就是这样的：

3yw10.02
5scm15.03
6lyx30.03
2sry30.01
4yyn100.02
1wjl100.01

因为只有一个reducer，那么在执行mapreduce的分区方法的时候：

public class HashPartitioner<K, V> extends Partitioner<K, V> {

  /** Use {@link Object#hashCode()} to partition. */
  public int getPartition(K key, V value,
                          int numReduceTasks) {
    return (key.hashCode() & Integer.MAX_VALUE) % numReduceTasks;
  }

}

因为numReduceTasks为1，所以所有的数据都跑到一个reduce里面了，然后在这个reducer里面按照sal进行了排序，得到的结果就是上面那个样子了

但是如果我们set mapreduce.job.reduces=3的话，那么相同的deptno的数据就会到同一个reducer中，并且那个reducer刚刚好只会有一个deptno的数据结果如下

000000_0 
5kk15.03
6jj30.03

000001_0 
2lq30.01
1jyw100.01

000002_0 
3xzz10.02
4dzz100.02

cluster by
当distribute by和sort by的字段相同的时候使用cluster by
但是排序只能升序排序，不可指定。

用法举例:
set mapreduce.job.reduces=2
insert overwrite
local directory ‘/opt/data/gender’
select * from dws_sale.dws_customers distribute by gender sort by custid

说明:mapreduce.job.reduces默认值为-1
当reduce个数<分区列不同hash值总数,那么生成reduce个数个文件
当reduce个数>分区列不同hash值总数,那么生成reduce个数个文件(其中有hash值总数个实际文件，另外都是空文件)

8 实际需求-表连接时使用分区查询

Hive表连接与常规数据库的表连接使用方法一样，关键字还是 inner join ,left join 等等，下面看一下实际工作中用到的需求。

需求如下：

Hive 中一张存储文章表，

字段说明：

title --标题

content – 内容

pubtime --发布时间

serviceId --文章类型

表分区字段 --year month

查询文章发布时间 2019年11月份 11-18号，标题与内容相同，并且标题大于 30 的文章 ，文章类型在 1-5

结果使用子查询 + 自连接查相同文章

注意：一定要使用分区，不然程序会卡死。

结果 sql 如下：

select t1.id, t1.title,t1.content, t1.pubtime,t1.serviceId

from (select id, title,content, pubtime,serviceId from article_info where (year = ‘2019’ and month = ‘11’)) t1

inner join (select id, url, content, pubtime,serviceId from article_info where (year = ‘2019’ and month = ‘11’)) t2

on t1.id = t2.id

where t1.pubtime >= ‘2019-11-11 00:00:00’ and t1.pubtime <=‘2019-11-18 23:59:59’

and length(t1.title) > 30 and t1.serviceId in (1,2,3,4,5) and t1.title = t2.content

总结：其实 hive并不难 就是需要多练，祝大家成为一个头发茂密的SQL boy。