Hive简介及常用操作
一、Hive简介
1.1 什么是hive
- facebook开源用于解决海量结构化日志的数据统计工具
- 基于hadoop的一个数据仓库工具,可以将结构化数据文件映射成为一张表,并提供类SQL查询功能。
- hive的本质是把HQL(hive sql)解析为MapReduce作业。还支持以Tez和Spark作为执行引擎。
1.2 为什么要使用hive
- hive的出现的主要原因是因为hadoop的MapReduce代码通常都很复杂,而sql语法相对于MapReduce代码语言要更加简洁,学习成本更低。
1.3 hive的优缺点
优点:
- 采用sql作为开发语言,学习成本低,开发简洁。
- 支持大量分析函数,如开窗函数等,所以适合作为olap工具。
- 基于hdfs分布式存储和yarn分布式计算,有很好的横向扩展性。
- 支持海量数据的分析计算,在小数据量的情况下并无优势。
- 支持自定义函数,在系统默认函数无法解决时,可以采用自定义函数处理。
缺点
- hive基于MapReduce作为默认执行引擎,执行效率低。
- sql表达能力有限,某些复杂功能,需要使用代码实现。
二、hive和greenplum
1.1 区别
hive | greenplum |
---|---|
查询速度 | 慢 |
存储 | hdfs |
计算 | 默认MR |
事务 | 不支持 |
数据量 | 超大规模数据量 |
1.2 常用数据类型对比
greenplum | hive |
---|---|
int2 | SMALLINT |
int4 | INT |
int | BIGINT |
float4 | FLOAT |
float8 | DOUBLE |
numeric | DECIMAL |
varchar | STRING |
bpchar | STRING |
text | STRING |
boolean | BOOLEAN |
date | date |
timestamp | timestamp |
1.3 hive复杂数据类型介绍
数据类型 | 描述 | 示例 |
---|---|---|
STRUCT | 一种嵌套结构,内部数据可以通过.来获取。 | structstreet:string,city:string |
MAP | 是一个key-value类型的。通过[‘key’]获取元素 | map<string,int> |
ARRAY | 数组类型。通过[index]获取元素 | array |
三、常见用法
3.1 DDL
3.1.1 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
3.1.2 创建一个数据库
# 默认存储路径是hdfs上的/user/hive/warehouse/*.db
create database db;
# 指定hdfs存储路径/data/warehouse/db/
create database db location '/data/warehouse/db/';
3.1.3 查看数据库
hive (liker)> desc database db;
OK
db_name comment location owner_name owner_type parameters
db hdfs://hadoop001:9820/data/warehouse/db liker USER
3.1.4 显示数据库详细信息
hive (liker)> desc database extended db;
OK
db_name comment location owner_name owner_type parameters
db hdfs://hadoop001:9820/data/warehouse/db liker USER {crtdate=2021-11-11}
3.1.5 切换数据库
# 切换到db数据库
hive (liker)> use db;
OK
Time taken: 0.013 seconds
hive (db)>
3.1.6 常用建表语句
CREATE EXTERNAL TABLE dms_table (
`id` STRING COMMENT 'ID',
`wnumber` STRING COMMENT 'SN',
`itemcode` STRING COMMENT '整车物料编码',
`itemname` STRING COMMENT '整车物料描述',
`series_code` STRING COMMENT '产品系列编码'
) COMMENT '电摩主部件'
PARTITIONED BY (`dt` STRING COMMENT '时间分区',`rgn` STRING COMMENT '大区')
ROW FORMAT DELIMITED FIELDS TERMINATED by '\001'
STORED AS parquet
LOCATION '/data/hive/warehouse/db/dws/dms_table' TBLPROPERTIES ('parquet.compression' = 'SNAPPY');
3.2 常用关键字/语法
关键字/语法 | 描述 |
---|---|
EXTERNAL | 指定表为外部表,默认为内部表,通常指定为外部表 |
PARTITIONED BY | 指定分区字段 |
ROW FORMAT DELIMITED FIELDS TERMINATED by | 指定字段分隔符 |
STORED AS | 指定存储格式,默认为text文本。通常使用orc/parquet列式存储 |
LOCATION | 指定表的数据在HDFS上的存储路径 |
TBLPROPERTIES | 设置表的配置,例如配置压缩格式、内部表/外部表。 |
3.3 内部表/外部表
hive的数据是存储在hdfs上的,内部表和外部表的差别就是,当删除表的时候,hdfs的文件会不会被删除。
由于删除内部表的时候,数据也会被删除。为了数据安全通常正式建表,都是创建外部表,并且指定外部表存储路径。
内部表通常用于测试使用,用完删了即可,数据也会被删除,不会占用集群存储空间。例如,在数据校验时,作为对比的表,就可以创建内部表,当校验结束后,把表删除即可。
# 创建内部表
create table table_name ...
# 创建外部表
create external table table_name ...
# 内部表和外部表转换
alter table table_name set tblproperties('EXTERNAL'='TRUE');
# 通过desc查看
desc formatted table_name;
......
EXTERNAL TRUE
......
3.4 分区
分区的作用是把数据分开存储,在hdfs上将一个表的不同分区的数据分开存储。优点就是在查询的时候,可以通过指定分区来减少查询时底层需要加载的数据量。
例如增量存储的数据中,可以把数据按照天来存储。
0B dt=20211101
0B dt=20211102
0B dt=20211103
3.4.1 创建分区表
# 创建按天分区
create external table partition_table(
...)
partitioned by (day string,rgn string)
3.4.2 分区表使用
① 添加测试数据
create table test_partition (id int, name string)
partitioned by(dt string);
insert overwrite table test_partition partition(dt='20211101') values(3,'hehe');
insert overwrite table test_partition partition(dt='20211102') values(3,'hehe');
② 查看分区
show partitions test_partition;
dt=20211101
dt=20211102
③ 手动添加分区
# 添加分区
alter table table_name if not exists add partition (dt='20211103')
# 添加后,查看分区
show partitions test_partition;
dt=20211101
dt=20211102
dt=20211103
④ 手动删除分区
# 删除分区
alter table table_name drop partition (dt='20211101')
# 删除后查看分区
show partitions test_partition;
dt=20211102
dt=20211103
# 查询分区的数据
# 当只需要某一天或某几天的数据时,可以在where后添加分区的过滤条件
select * from test_partition where day='20210101'
# or
select * from test_partition where day='20210101' or day='20210102'
3.4.3 insert into & insert overwrite
- insert into:向指定表增量添加数据。通常不会使用
- insert overwrite:向指定表添加数据,覆盖之前的数据,通常结合分区使用。
# insert into 示例
create table test_tab (id int, name string);
insert into test_tab values(1,'kwang');
insert into test_tab values(2,'fwang');
select * from test_tab;
1 kwang
2 fwang
# insert overwrite 示例
insert overwrite table test_tab values(4,'zz');
select * from test_tab;
4 hehe
- 向分区表添加数据,向分区表添加数据,必须指定分区,否则报错
create table test_partition (id int, name string)
partitioned by(dt string);
insert into test_partition partition(dt='20211101') values(1,'kwang');
insert into test_partition partition(dt='20211101') values(2,'fwang');
select * from test_partition ;
1 kwang
2 fwang
# insert overwrite
insert overwrite table test_partition partition(dt='20211101') values(3,'hehe');
select * from test_partition ;
3 hehe
3.4.4 动态分区
以上介绍的分区操作都是在添加的时候指定分区,如果在向某个表添加数据时,需要根据表中的具体数据来添加到指定分区。
- 添加测试数据
create table test_data (id int, name string,day_date string);
insert into table test_data values(1,'mapreduce','20211101'),(2,'hadoop','20211102'),(4,'hive','20211102'),(3,'spark','20211103');
select * from test_data;
1 mapreduce 20211101
2 hadoop 20211102
4 hive 20211102
3 spark 20211103
- 添加数据到分区表
# 创建分区表
create table test_partition (id int, name string)
partitioned by(dt string);
# 在hive中动态分区默认是关闭的,使用前需要手动打开,并且关闭严格模式。需要设置如下两个参数
SET hive.exec.dynamici.partition = TRUE;
SET hive.exec.dynamic.partition.mode = nonstrict;
insert overwrite table test_partition partition(dt)
select
id,
name,
day_date as dt
from test_data;
# 验证分区的数据
select * from test_data where dt='20211101';
1 mapreduce 20211101
select * from test_data where dt='20211102';
2 hadoop 20211102
4 hive 20211102
select * from test_data where dt='20211103';
3 spark 20211103
3.4.5 去重操作,避免使用distinct,使用group by
# 避免使用distinct的方式
select distinct name,age,tel from user
# 使用group by
select name,age,tel from user group by name,age,tel
# 同样的count(distinct) 改为group by
select distinct name,age,tel from user
select name,age,tel,count(1) cnt from user group by name,age,tel
3.4.6 常用函数
- nvl:接受两个参数,如果第一个为null,取第二个值
select nvl(null,1)
1
select nvl(1,2,)
1
- coalesce: 返回第一个不为null的值
select coalesce(null,'1',3)
1
3.4.7 数组操作
# 添加测试数据
create table test_array_data(id int,name string,hoby string);
insert into test_array_data values(1,'hive','hiveserver2,beenline,hadoop'),(2,'hdfs','namenode,hadoop,datanode'),(3,'spark','hadoop,rdd,sql,dataframe,dataset')
drop table test_arr;
select id,name,split(hoby,',') hoby from test_array_data;
create table test_arr(id int,name string,hoby array<string>);
insert into table test_arr select id,name,split(hoby,',') hoby from test_array_data;
select * from test_arr;
1 hive ["hiveserver2","beenline","hadoop"]
2 hdfs ["namenode","hadoop","datanode"]
3 spark ["hadoop","rdd","sql","dataframe","dataset"]
select id,name,split(hoby,',') hoby from test_array_data;
# explode
select id,name,h from test_arr lateral view explode(hoby) test_arr as h
1 hive hiveserver2
1 hive beenline
1 hive hadoop
2 hdfs namenode
2 hdfs hadoop
2 hdfs datanode
3 spark hadoop
3 spark rdd
3 spark sql
3 spark dataframe
3 spark dataset
# size
select id,name,size(hoby) len from test_arr;
id name len
1 hive 3
2 hdfs 3
3 spark 5
# array_contains
select id,name,array_contains(hoby,'beenline') from test_arr;1 hive true
2 hdfs false
3 spark false
# 根据下标获取
select hoby[1] from test_arr;
beenline
hadoop
rdd
# concat_ws
select id,name,concat_ws('-',hoby) from test_arr;
1 hive hiveserver2-beenline-hadoop-hadoop
2 hdfs namenode-hadoop-datanode
3 spark hadoop-rdd-sql-dataframe-dataset
3.4.8 字符串转数组
-- 测试数据
insert overwrite table test_array_data values(1,'hive','hiveserver2,beenline,hadoop,hadoop'),(2,'hdfs','namenode,hadoop,datanode'),(3,'spark','hadoop,rdd,sql,dataframe,dataset')
insert overwrite table test_arr select id,name,split(hoby,',') hoby from test_array_data;
# 查询数据
with temp as (
select id,name,h from test_arr lateral view explode(hoby) test_arr as h
)select id,name,collect_list(h) h_list,collect_set(h) h_set from temp group by id,name
# collect_list & ,collect_set
id name h_list h_set
1 hive ["hiveserver2","beenline","hadoop","hadoop"] ["hiveserver2","beenline","hadoop"]
2 hdfs ["namenode","hadoop","datanode"] ["namenode","hadoop","datanode"]
3 spark ["hadoop","rdd","sql","dataframe","dataset"] ["hadoop","rdd","sql","dataframe","dataset"]
# split
select id,name,split(hoby,',') hoby from test_array_data;
1 hive ["hiveserver2","beenline","hadoop","hadoop"]
2 hdfs ["namenode","hadoop","datanode"]
3 spark ["hadoop","rdd","sql","dataframe","dataset"]
3.4.9 常用日期函数
- date_add: 用来为给定的日期增加指定的天数
# 格式 string date_add(string startdate, int days)
# 增加2天
select date_add('2021-10-10',2) # 2021-10-12
# 减去1天
select date_add('2021-10-10',-1) # 2021-10-09
- datediff:计算两个日期相差的天数 计算时会忽略时分秒
# 格式 int datediff(string enddate, string startdate)
# 计算2021-10-10 和2021-10-14相差几天
select datediff('2021-10-10','2021-10-14') # -4
select datediff('2021-10-14 00:00:00','2021-10-10 23:59:59') # 4
- date_format:指定格式转换格式
# 将yyyy-MM-dd HH:mm:ss 转换为yyyy/MM/dd HH-mm-dd
select date_format('2021-10-10 11:12:11','yyyy/MM/dd HH-mm-ss')
# 2021/10/10 11-12-11
- current_date:获取当前日期,年月日
# 获取当前日期
select current_date(); #2021-11-17
- last_day:获取指定时间所在月的最后一天
# 获取当前时间所在月的最后一天
select last_day(current_date()) # 2021-11-30
- from_unixstamp:根据时间戳获取指定格式的时间
# 从时间戳
select from_unixtime(unix_timestamp('2021-11-17 10:11:12'),'yyyy/MM/dd HH-mm-ss') # 2021/11/17 10-11-12
- unix_timestamp
# 获取指定时间的时间戳
select unix_timestamp('2021-11-17 10:10-10','yyyy-MM-dd HH:mm-ss') ; # 1637143810
- weekofyear
select weekofyear('2021-10-10') # 40