Hive是一种用类SQL语句来协助读写、管理那些存储在分布式存储系统上大数据集的数据仓库软件，是由Facebook为了解决海量日志数据的分析而开发了Hive，后来开源给了Apache软件基金会。Hive的数据是存储在HDFS上的。Hive中的库和表可以看作是对HDFS上数据做的一个映射。所以Hive必须是运行在一个Hadoop集群上的。

重要概念

外部表和内部表

内部表（managed table）
  • 默认创建的是内部表（managed table），存储位置在hive.metastore.warehouse.dir设置，默认位置是/user/hive/warehouse。
  • 导入数据的时候是将文件剪切（移动）到指定位置，即原有路径下文件不再存在
  • 删除表的时候，数据和元数据都将被删除
  • 默认创建的就是内部表create table xxx (xx xxx)
外部表（external table）
  • 外部表文件可以在外部系统上，只要有访问权限就可以
  • 外部表导入文件时不移动文件，仅仅是添加一个metadata
  • 删除外部表时原数据不会被删除
  • 分辨外部表内部表可以使用DESCRIBE FORMATTED table_name命令查看
  • 创建外部表命令添加一个external即可，即create external table xxx
  • 外部表指向的数据发生变化的时候会自动更新，不用特殊处理

分区表和桶表

分区（partioned）
  • 有些时候数据是有组织的，比方按日期/类型等分类，而查询数据的时候也经常只关心部分数据，比方说我只想查2017年8月8号，此时可以创建分区，查询具体某一天的数据时，不需要扫描全部目录，所以会明显优化性能
  • 一个Hive表在HDFS上是有一个对应的目录来存储数据，普通表的数据直接存储在这个目录下，而分区表数据存储时，是再划分子目录来存储的
  • 使用partioned by (xxx)来创建表的分区
分桶（clustered）
  • 分桶是相对分区进行更细粒度的划分。分桶将整个数据内容安装某列属性值得hash值进行区分，按照取模结果对数据分桶。如取模结果相同的数据记录存放到一个文件。
  • 桶表也是一种用于优化查询而设计的表类型。创建通表时，指定桶的个数、分桶的依据字段，hive就可以自动将数据分桶存储。查询时只需要遍历一个桶里的数据，或者遍历部分桶，这样就提高了查询效率。

具体说明分桶

  • clustered by (user_id) sorted by(leads_id) into 10 buckets
    • clustered by是指根据user_id的值进行哈希后模除分桶个数，根据得到的结果，确定这行数据分入哪个桶中，这样的分法，可以确保相同user_id的数据放入同一个桶中。
    • sorted by 是指定桶中的数据以哪个字段进行排序，排序的好处是，在join操作时能获得很高的效率。
    • into 10 buckets是指定一共分10个桶。
    • 在HDFS上存储时，一个桶存入一个文件中，这样根据user_id进行查询时，可以快速确定数据存在于哪个桶中，而只遍历一个桶可以提供查询效率。

Hive文件格式

hive文件存储格式包括以下几类：

  1. TEXTFILE
  2. SEQUENCEFILE
  3. RCFILE
  4. ORCFILE(0.11以后出现)

其中TEXTFILE为默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理；SEQUENCEFILE，RCFILE，ORCFILE格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中， 然后再从表中用insert导入SequenceFile,RCFile,ORCFile表中。

TEXTFILE

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。

可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

SEQUENCEFILE

SequenceFile是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。

SequenceFile支持三种压缩选择：NONE，RECORD，BLOCK。Record压缩率低，一般建议使用BLOCK压缩。

RCFILE

RCFILE是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。

  • RCFile保证同一的数据位于同一节点，因此元组重构代价较低(需要将分散的数据重新组织,比如一列数据散落在不同集群，查询的时候，需要将各个节点的数据重新组织；但是如果数据都在一个机器上，那就没有必要重新组织)
  • RCFile通过列进行数据压缩，因为同一列都是相同的数据类型，所以压缩比比较好
  • RCFile可以跳过不必要的列读取

从以上几点也可以看出它是兼顾了行式和列式存储的部分优点。

ORC File

ORCFile存储格式，就是Optimized RC File的缩写。意指优化的RCFile存储格式。

ORC File 和 RC File比较

  • 每一个任务只输出单个文件，这样可以减少NameNode的负载
  • 支持各种复杂的数据类型，比如datetime,decimal,以及复杂的struct,List,map等
  • 在文件中存储了轻量级的索引数据
  • 基于数据类型的块模式压缩：比如Integer类型使用RLE(RunLength Encoding)算法，而字符串使用字典编码(DictionaryEncoding)
  • 使用单独的RecordReader并行读相同的文件
  • 无需扫描标记就能分割文件
  • 绑定读写所需要的内存
  • 元数据存储使用PB,允许添加和删除字段

语句

创建数据库

// 数据库分层创建, 主题

1
2
3
4
5

create database data_agency; 
create database data_course; 
create database data_dimen; 
create database data_platform;
create database data_subject;

创建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE EXTERNAL TABLE
IF NOT EXISTS data_zhaobiao.suzhou
(url String,
agency String, 
area String, 
budget String, 
buy_unit String, 
buy_way String, 
project_name String,
release_time String,
title String,
`type` String,
contract_tab String,
supplement_tab String,
tender_tab String,
single_tab String,
doc_tab String,
winning_tab String) 
PARTITIONED BY (years String,months String,days String)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS ORC
location'/user/hive/warehouse/data_zhaobiao.db/suzhou';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE EXTERNAL TABLE
IF NOT EXISTS data_zhaobiao.suzhou
(url String,
agency String,
area String,
budget String,
buy_unit String,
buy_way String,
project_name String,
release_time String,
title String,
`type` String,
contract_tab String,
supplement_tab String,
tender_tab String,
single_tab String,
doc_tab String,
winning_tab String)
PARTITIONED BY (years String,months String,days String)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
STORED AS TEXTFILE
location ’/user/hive/warehouse/data_zhaobiao.db/suzhou’;

删除表

drop table if exists xxxxxx

写入数据

  1. 从本地文件系统中导入数据到Hive表

load data local inpath ‘xxx.txt’ into table xxx；

  1. 从HDFS上导入数据到Hive表

load data inpath ‘/home/xxx/add.txt’ into table xxx

alter table db.access_log add partition (dt='18-09-18’) location ‘hdfs://ns/hive/warehouse/access_log/dt=18-09-18’;

  1. 从别的表中查询出相应的数据并导入到Hive表中

insert overwrite table db.log_v2 partition(dt='18-09-26’) select uid,model,key,value,time from db.log where dt='18-09-26’;

  1. 在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中

create table test4 as select id, name, tel from xxx;

Hive不建议直接使用Insert into values语法，有性能问题。

查看表信息

查看一些分隔符，元数据路径等信息

desc formatted data_zhaobiao.suzhou_ods

函数

时间函数

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

-- 1、hive取得当前日期时间：

-- 1.1) 取得当前日期：
select current_date();

-- 1.2) 取得当前日期时间：
select current_timestamp();

-- 1.3) hive取得当前时间戳：
select unix_timestamp();

-- 1.4) 时间戳转日期：
select from_unixtime(1517725479,'yyyy-MM-dd HH:dd:ss');

-- 1.5) 日期转unix时间戳：
select to_nuix_timestamp('2017-01-01 12:12:12','yyyy-MM-dd HH:dd:ss');

-- 1.6) hive取得当前时间：
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:dd:ss');

-- 2、hive自动计算其他日期（昨天，今天）：
-- hive中日期加减函数：date_add(start_date,num_days)

-- 2.1) 取得昨天日期：
select date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1);
select date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);
select date_format(date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1);


-- 2.2) 取得明天日期：
select date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);
select date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1);

-- 2.3)hive取得两个日期之间差值（差值为天数）：
-- datediff(date1,date2):date1大于date2，返回值为正，否则，返回值为负。

select datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-10));
select datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),10));

-- 2.4) 字符串转时间（字符串必须为：yyyy-MM-dd格式）

select to_date('2017-01-01 12:12:12');

-- 2.5) 日期、时间戳、字符串类型格式化输出标准时间格式：

select date_format(current_timestamp(),'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(),'yyyyMMdd');
select date_format('2017-01-01','yyyy-MM-dd HH:mm:ss');       --字符串必须满足yyyy-MM-dd格式

-- 2.6) utc时间转换:

select from_utc_timestamp(current_timestamp(),8);
select to_utc_timestamp(current_timestamp(),8);

FAQ

hive创建表中包含关键字

需要使用 ``反引号括起来使用

总结

会随着使用不断的更新内容