hive
概述
简介
- Facebook开源的用于解决海量结构化数据的数据分析框架
- 基于Hadoop的数据仓库,可以将结构化的数据文件映射成一张表,并提供类SQL查询功能
- 使用HQL作为查询接口
- 使用HDFS存储
- 用MapReduce计算(现可支持多种计算框架)
- 本质:将HQL转化成MapReduce程序
优点
- 使用类SQL语法,使用门槛降低
- 统一的元数据管理,可与impala/spark等共享元数据
- 灵活、易扩展:支持自定义函数(UDF)、自定义存储格式等
使用场景
离线数据处理,比如:日志分析
架构
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HFJdYbW0-1588692880434)(asset/hive架构.png)]
-
用户接口:Client
CLI(hive shell)、JDBC/ODBC(java或其他语言访问hive)、WEBUI(浏览器访问hive)
-
驱动器:Driver(解析器、编译器、优化器、执行器)
- 解析器:将SQL转化成抽象语法树AST,这一步一般都用第三方工具库完成,比如antlr;对AST进行语法分析,比如表是否存在、字段是否存在、SQL语义是否有误
- 编译器(分析器):将AST编译生成逻辑执行计划
- 优化器:对逻辑执行计划进行优化
- 执行器:把逻辑执行计划转化成可以运行的物理执行计划(MR/Tez/Spark)
-
元数据:Metastore
- 元数据包括:表名、表所属数据库(默认是default)、表的拥有者、列/分区字段、表的类型、表的数据所在目录等
- 默认存储在自带的derby数据库中,推荐使用MySQL存储
-
Hadoop
使用HDFS存储,使用MapReduce进行计算
环境搭建
-
下载:https://mirror.bit.edu.cn/apache/hive/
-
解压:tar zxf apache-hive-2.3.7-bin.tar.gz -C ~
-
配置conf/hive-env.sh
HADOOP_HOME=/home/test/hadoop-2.9.2
derby默认数据库
derby数据库不支持一个机器多用户,可配置MySQL存储metastore元数据支持单机器多用户。
-
初始化metastore
bin/schematool -initSchema -dbType derby
-
运行Hadoop
sbin/start-all.sh
-
运行Hive
bin/hive
mysql数据库
-
配置hive-site.xml
<property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>123456</value> </property>
-
拷贝connector.jar
-
初始化metastore
bin//schematool -initSchema -dbType mysql
-
mysql中查看表信息
mysql -uroot -p123456 use metastore show tables;
-
启动hive
bin/hive show databases;
-
删除hive
#删hive rm -rf hive-2.3.7 #mysql中hive database删除 drop database hive;
基本操作
基本命令
#查看有哪些数据库
show databases;
#使用默认数据库
use default;
#查看数据库下有哪些表
show tables;
#创建用户表,id字段和name字段值用制表符分隔
#\n 每行一条记录
#^A(Ctrl+A) 分隔列(八进制 \001)
#^B(Ctrl+B) 分隔ARRAY或者STRUCT中的元素,或者MAP中多个键值对之间分隔(八进制 \002)
#^C(Ctrl+C) 分隔MAP中键值对的“键”和“值”(八进制 \003)
#默认分隔符输入:在vi/vim中通过a或i或O进入编辑模式,执行ctrl+v,然后ctrl+a
create table users(id int, name string) row format delimited fields terminated by '\t';
#加载本地文件到user表中,数据文件存到了hdfs的/user/hive/warehouse/user目录下
load data local inpath '/home/test/hive-2.3.7/data/users.txt' into table users;
#查询users表
select * from users;
#查看列字段
describe users;
desc users;
desc extended users;
desc formatted users;
#查看内置函数
show functions;
#查看具体某个函数
desc function upper;
desc function extended upper;
shell命令
-help:查看帮助
-e:直接执行sql语句,返回结果
-f:直接执行sql文件
-d/--define/--hiveconf/--hivevar:定义属性 k=v
-h:连接到hive server的主机名
-p:连接到hive server的端口号
-i:执行初始化sql文件
-S/-v:静默/冗余模式(是否在命令行显示执行的sql语句)
常用属性配置
-
在cli命令行上显示当前的数据库名以及查询的行头信息
<property> <name>hive.cli.print.current.db</name> <value>true</value> </property> <property> <name>hive.cli.print.header</name> <value>true</value> </property>
-
日志文件配置
默认log日志文件在/tmp/”用户名”/hive.log
- 将hive目录下conf中的hive-log4j.properties.template重命名为hive-log4j.properties
- 在hive-log4j.properties文件中设置以下几项:
- hive.log.dir为日志文件的存放目录
- hive.log.file为日志的文件名
-
属性信息配置
- hive启动时配置属性信息:bin/hive --hiveconf <property=value>
- 在hive启动后配置属性信息:set property=value;
- 查看所有配置信息(set命令)
-
数据仓库位置配置
-
默认位置为:/user/hive/warehouse
-
在仓库目录下,没有对默认的数据库default创建文件夹
- 如果某张表属于default数据库,直接在数据仓库目录下创建一个文件夹
- 如果新建一个数据库,则会在仓库目录下新建一个以该数据库为名字的文件夹
-
修改仓库位置
<property> <name>hive.metastore.warehouse.dir</name> <value>/user/hive/warehouse</value> </property>
-
Hive与文件系统交互
-
操作hdfs文件系统
hive (default)> dfs -ls /;
-
操作本地文件系统
hive (default)> !ls /home/test;
数据类型
基础数据类型
-
数值类型
TINYINT、SMALLINT、 INT/INTEGER、 BIGINT、 FLOAT、DOUBLE、DECIMAL
-
字符串类型
STRING、VARCHAR、CHAR
-
日期/时间类型
TIMESTAMP、DATE、INTERVAL
-
Misc类型
BOOLEAN、BINARY
复杂数据类型
ARRAY、MAP、STRUCT、UNIONTYPE
参考:https://blog.csdn.net/u010670689/article/details/72885944
数据存储格式
-
TextFile
- 行存储
- 每一行都是一条记录,每行都以换行符(\ n)结尾。数据不做压缩,磁盘开销大,数据解析开销大。可结合Gzip、Bzip2使用(系统自动检查,执行查询时自动解压),但使用这种方式,hive不会对数据进行切分,从而无法对数据进行并行操作。
-
SequenceFile
- 行存储
- Hadoop API提供的一种二进制文件支持,其具有使用方便、可分割、可压缩的特点。支持三种压缩选择:NONE, RECORD, BLOCK。 Record压缩率低,一般建议使用BLOCK压缩。
-
RCFile
- 行列存储相结合的存储方式
- 按行分块,每块按照列存储。首先,其将数据按行分块,保证同一个record在一个块上,避免读一个记录需要读取多个block。其次,块数据列式存储,有利于数据压缩和快速的列存取。
-
ORC
- 行列存储相结合的存储方式
- 提供了一种将数据存储在Hive表中的高效方法。这个文件系统实际上是为了克服其他Hive文件格式的限制而设计的。Hive从大型表读取,写入和处理数据时,使用ORC文件可以提高性能。压缩快,快速列存取 ,效率比rcfile高,是rcfile的改良版本。
-
Parquet
- 列存储
- 面向列的二进制文件格式。Parquet对于大型查询的类型是高效的。对于扫描特定表格中的特定列的查询,Parquet特别有用。Parquet可以使用snappy(默认)/gzip等压缩格式。相对于ORC,Parquet压缩比较低,查询效率较低,不支持update、insert和ACID.但是Parquet支持Impala查询引擎
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5zR6whGa-1588692880437)(asset/数据存储格式.png)]
总结:如果仅仅是在Hive中存储和查询,建议使用ORC格式;如果在Hive中存储,而使用Impala查询,建议使用Parquet。
DDL操作
数据库(database)
- 创建:create database hive_test;
- 查看有哪些数据库:show databases;
- 使用:use hive_test;
- 修改属性:alter hive_test set DBPROPERTIES (property_name=property_value, …);
- 删除:drop database hive_test;
表类型
-
管理表(内部表)
普通表、分区表、桶表
-
外部表
普通表、分区表、桶表
管理表
- 所有数据都保存在内部表指定的目录下(默认是/user/hive/warehouse/)
- 分为创建过程和数据加载过程。在加载数据时,将数据移动到内部表指定的目录。
- 删除表时,元数据和数据都会被删除。
外部表
- 所有数据都保存在外部表指定的目录下
- 只有一个过程。创建表和加载数据同时完成。在加载数据时,将数据不会移动,只是建立一个链接。
- 删除表时,只是删除元数据和链接,数据不会被删除。
分区表
- 分区可以理解为分类,通过分类把不同类型的数据放到不同的目录下。
- 分类的标准就是分区字段,可以一个,也可以多个。
- 分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段,就会全部扫描。
桶表
- 每个桶对应一个分区下的文件。
- 数据进行哈希取值,然后将不同类型的数据放到不同的文件中。
- 桶表专门用于抽样查询,不是日常用来存储数据的表,需要抽样查询时,才使用。
创建表
三种方式
普通方式,CTAS(CREATE TABLE AS SELECT),LIKE
普通方式
内部表
create table page_view(viewTime string, userId bigint, page_url string, referrer_url string, ip string)
row format delimited fields terminated by ' '
stored as textfile;
默认字段分隔符是ascii码的控制符\001(在vi中使用ctrl+v和ctrl+a),默认存储格式是TextFile。SequenceFile 、 RCFile、ORC、Parquet存储格式都以类似方式创建。
#加载本地数据
load data local inpath '/home/test/hive-2.3.7/data/page_view.txt' into table page_view;
#插入数据 (textfile/parequetfile/parquet/rcfile/orc/...)
insert into page_view_xxx select * from page_view;
#插入数据到分区表中
insert into users2_partition partition(dt='2020-04-30') select * from users2;
外部表
create external table page_view_external(viewTime string, userId bigint, page_url string, referrer_url string, ip string)
row format delimited fields terminated by ' '
stored as textfile;
分区表
create table page_view_partition(viewTime string, userId bigint, page_url string, referrer_url string, ip string)
partitioned by (dt string, country string)
row format delimited fields terminated by ' '
stored as sequencefile;
#插入数据
insert into page_view_partition partition (dt='2020-04',country='china') select * from page_view;
#如果文件格式位textfile,可直接
load data local inpath '/home/test/hive-2.3.7/data/page_view.txt' into table page_view_partition partition(dt='2020-04',country='china');
#查看分区
show partitions page_view_partition;
参考:https://blog.csdn.net/XDSXHDYY/article/details/94503158
桶表
create table page_view_bucket(viewTime string, userId bigint, page_url string, referrer_url string, ip string)
partitioned by (dt string, country string)
clustered by (userId) sorted by (viewTime desc) into 4 buckets
row format delimited fields terminated by ' '
stored as textfile;
# partitioned by可选
#设置变量,设置分桶为true, 设置reduce数量是分桶的数量个数
set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;
#插入数据
insert into table page_view_bucket partition(dt='2020-04',country='china')
select * from page_view distribute by(userId) sort by(viewTime asc);
CTAS
#创建的表不能是外部表、分区表、桶表;SQL会执行MR作业
CREATE TABLE page_view_ctas STORED AS RCFile AS SELECT * FROM page_view SORT BY viewTime, userid;
LIKE
CREATE TABLE page_view_like LIKE page_view;
LIKE与CTAS的区别:LIKE只是拷贝表结构;CTAS拷贝结构和数据,执行MR
修改表
- 修改表名:ALTER TABLE page_view RENAME TO page_view1;
- 修改表属性:ALTER TABLE page_view SET TBLPROPERTIES (‘comment’ = ‘new_comment’);
- 添加SerDe属性:ALTER TABLE page_view SET SERDEPROPERTIES(‘field.delim’=’,’);
修改列
-
创建表: CREATE TABLE test_change (a int, b int, c int);
-
改变列名/类型/位置/注释
ALTER TABLE test_change CHANGE a a1 int AFTER b; ALTER TABLE test_change CHANGE c c1 INT FIRST;
-
添加/替换列
#添加d字段 ALTER TABLE test_change ADD COLUMNS (d int); #删除c字段 ALTER TABLE test_change REPLACE COLUMNS (a int, b int);
修改分区
-
添加分区
ALTER TABLE page_view_partition ADD PARTITION (dt='2020-05', country='us') location ' /user/hive/warehouse/page_view_partition/xxx';
-
修改分区
ALTER TABLE page_view_partition PARTITION (dt=xxx,country=xxx) SET LOCATION '<path>'
-
重命名分区
ALTER TABLE page_view_partition PARTITION (dt=xxx,country=xxx) RENAME TO PARTITION (dt=xxx,country=xxx);
-
修复分区
msck repair table page_view_partition;
清空/删除表或分区
-
清空表/分区
#TRUNCATE TABLE table_name [PARTITION partition_spec]; #truncate不能清空外部表 truncate table page_view_partition partition(dt='2020-04',country='china');
-
删除表/分区
#删除表 #purge:强制删除,不进hdfs回收站 #hdfs回收站:https://www.cnblogs.com/Richardzhu/p/3652228.html #DROP TABLE [IF EXISTS] table_name [PURGE]; drop table page_view_partition; #删除分区 #ALTER TABLE table_name DROP PARTITION partition_spec alter table page_view_partition drop partition(dt='2020-04',country='china');
查看库/表/字段/分区
#DESCRIBE DATABASE [EXTENDED] db_name;
desc database extended hive_test;
#DESCRIBE [EXTENDED|FORMATTED] table_name;
desc formatted page_view_partition;
#DESCRIBE FORMATTED [db_name.]table_name column_name;
desc formatted page_view_partition referrer_url;
#DESCRIBE [EXTENDED|FORMATTED] table_name PARTITION partition_spec;
desc formatted page_view_partition partition(dt='2020-04',country='china');
DML操作
加载数据
-
加载本地数据
load data local inpath '/home/test/hive-2.3.7/data/pageview.txt' into table page_view;
-
加载hdfs数据
load data inpath '/home/test/hive-2.3.7/data/pageview.txt' into table page_view;
-
覆盖表中的数据
load data [local] inpath '/home/test/hive-2.3.7/data/pageview.txt' overwrite into table page_view;
插入数据
-
查询方式
#单表插入 #INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] #select_statement1 FROM from_statement; insert into page_view_partition partition (dt='2020-04',country='china') select * from page_view; #多表插入 #FROM from_statement INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, #partcol2=val2 ...) [IF NOT EXISTS]] select_statement1[INSERT OVERWRITE TABLE #tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]; from page_view insert into page_view_partition partition (dt='2020-05',country='china') select * where userId=10001 insert into page_view_external select * where userId=10001;
-
SQL方式
#INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES #values_row [, values_row ...] insert into page_view_partition partition(dt='2020-05',country='china') values ('2020-05-05',1002,'www.pinduoduo.com','www.wx.com','192.168.0.103');
写出数据
#单输出(目录为空)
#INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS #file_format] SELECT ... FROM ...
insert overwrite local directory '/home/test/hive-2.3.7/output' select * from page_view;
#多输出
#FROM from_statement INSERT OVERWRITE [LOCAL] DIRECTORY directory1 #select_statement1[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
from page_view
insert overwrite local directory '/home/test/hive-2.3.7/output' select *;
导入导出数据
#导出:导出的目录必须是空的 hdfs目录
export table page_view to '/test'
#导入
import from '/test'
查询
group by
create table student(id int, name string, sex string, age int)
row format delimited fields terminated by ','
stored as textfile;
load data local inpath '/home/test/hive-2.3.7/data/student.txt' into table student;
select sex, count(0) cnt from student group by sex having cnt>2;
- 查询的字段:group by字段和聚合函数生成的字段
- having:分组过滤
- 执行顺序: FROM–>GROUP BY–>SELECT—>HAVING–>SELECT
distinct
去重
select distinct id,name from student;
select id,name from student group by id,name;
join
-
Join三种类型:ReduceJoin、MapJoin、SMB(Sort-Merge-Bucket) Join
-
ReduceJoin不仅有内关联、左关联、右关联、全关联,还支持LEFT SEMI JOIN和CROSS JOIN,但这两种JOIN类型也可以用前面的代替。
-
Hive中Join的关联键必须在ON 中指定,不能在Where中,否则就会先做笛卡尔积,再过滤。
create table customer(first string, last string, id int) row format delimited fields terminated by ',' stored as textfile; load data local inpath '/home/test/hive-2.3.7/data/customer.txt' into table customer; create table orders(cid int, price double, quantity int) row format delimited fields terminated by ',' stored as textfile; load data local inpath '/home/test/hive-2.3.7/data/order.txt' into table orders;
ReduceJoin
Reduce Join在Hive中也叫Common Join或Shuffle Join。如果两边数据量都很大,会把相同key的value合在一起。
select * from customer join orders on customer.id=orders.cid;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cYHKg4h9-1588692880440)(asset/ReduceJoin.png)]
MapJoin
-
大小表连接:如果一张表的数据很大,另外一张表很少(<1000行),那么我们可以将数据量少的那张表放到内存里面,在map端做join。
select /*+ mapjoin(o)*/ c.*, o.* from customer c join orders o on c.id=o.cid;
-
需要做不等值join操作(a.x < b.y 或者 a.x like b.y等)。如果直接写在where中会造成笛卡尔积,数据量大,速度慢。
select /*+ MAPJOIN(o) */ c.*, o.* from customer c join orders o where c.id>=o.cid;
-
可通过 hive.auto.convert.join设置为true,直接将reduceJoin转为mapJoin
SMB(Sort-Merge-Buket) Join
将两个表中的数据按指定字段排序后,放到多个桶中。每一对对应的桶数据进行关联。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yT4WVQKE-1588692880443)(asset/SMB(Sort-Merge-Buket)] Join.png)
子查询
-
FROM子查询
SELECT col FROM (SELECT a+b AS col FROM t1) t2;
-
WHERE子查询
#IN:外表大,内表小;EXISTS:外表小,内表大 SELECT * FROM A WHERE A.a IN (SELECT foo FROM B); SELECT * FROM A WHERE A.a IN (SELECT foo FROM B);
Lateral View
lateral view和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF,UTDF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。
#创建表
create table pageAds (pageid string,adid_list array<int>) row format delimited fields terminated by "\t" COLLECTION ITEMS TERMINATED BY ',';
load data local inpath '/home/test/hive-2.3.7/data/ads.txt' into table pageAds;
#查询
SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
抽样查询(TABLESAMPLE)
select * from bucket_table tablesample(bucket 1 out of 4);
语法解析:TABLESAMPLE(BUCKET x OUT OF y)
-
y:table总bucket数的倍数或因子,hive根据y的大小,决定抽样的比例。
- 例如:table总共64份,当y=32时,抽取(64/32=)2个bucket的数据;
- 当y=128时,抽取 (64/128=)1/2个bucket的数据。
-
x:从哪个bucket开始抽取。
- 例如:table总bucket数为32,tablesample(bucket 3 out of 16)表示总共抽取(32/16=)2个 bucket的数据,分别为第3个bucket和第(3+16=)19个bucket的数据。
DISTRIBUTE BY/SORT BY/CLUSTERED BY/ORDER BY
- DISTRIBUTE BY:控制map的输出在reducer是如何划分的(分区)
- SORT BY:在每个reducer端都会做排序(局部排序)
- CLUSTERED By:distribute by和sort by是同一字段
- ORDER BY:全局排序(只有一个reduce)
内置函数
参考:https://blog.csdn.net/liu123641191/article/details/80845411
UDF
参考:https://www.cnblogs.com/mzzcy/p/7119423.html
UDF(User-Defined-Function)类型
- UDF(User-Defined-Function):一进一出
- UDAF(User-Defined Aggregation Function):聚合函数,多进一出
- UDTF(User-Defined Table-Generating Function):一进多出
UDF
-
继承org.apache.hadoop.hive.ql.UDF
-
重写evaluate函数,实现一进一出的逻辑操作
-
添加jar包:add jar /opt/jars/hive_UDF.jar或直接将jar拷贝到lib目录下(重启hive)
-
生成临时函数:create temporary function xxx as ‘’<class全路径>’
-
删除临时函数:drop temporary function xxx;
-
永久注册函数:create function xxx as ‘<class全路径>’ using jar ‘hdfs:/jars/hive_UDF.jar’;
注:永久注册需要将jar包上传到hdfs,否则在集群中运行的时候,会出现找不到jar包的情况!
-
删除函数:drop function xxx;
UDAF
- 编写resolver类,继承org.apache.hadoop.hive.ql.udf.GenericUDAFResolver2或AbstractGenericUDAFResolver
- 编写evaluator类,继承org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator
- init方法:UDAF初始化
- iterate方法:接收传入的参数,并进行迭代计算
- terminatePartial方法:iterate函数迭代结束后,生成局部聚合数据
- merge方法:接收terminatePartial的返回结果,进行数据merge操作
- terminate方法:返回最终的聚合结果
UDTF
- 继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF
- 编写evaluator类,继承org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator
- initialize方法,返回UDTF的返回行的信息(返回个数,类型)。
- process方法:在process中,每一次forward()调用产生一行。如果产生多列可以将多个列的值放在一个数组中,然后将该数组传入到forward()函数。
- close方法:对需要清理的方法进行清理。
查询时间间隔
设置笛卡尔积:
set hive.strict.checks.cartesian.product=false;
按周查询时间间隔1:
select distinct deviceid from ext_startup_logs
where appid='sdk34734' and concat(ym,day) >= formattime(getweekbegin(-1),'yyyyMMdd')
and concat(ym,day) < formattime(getweekbegin(),'yyyyMMdd')
and deviceid not in
(
select distinct deviceid from ext_startup_logs
where appid='sdk34734' and concat(ym,day) >= formattime(getweekbegin(-2),'yyyyMMdd')
and concat(ym,day) < formattime(getweekbegin(-1),'yyyyMMdd')
);
按周查询时间间隔2:
select distinct s.deviceid from ext_startup_logs s
where appid = 'sdk34734' and concat(ym,day) >= formattime(getweekbegin(-1),'yyyyMMdd')
and deviceid not in
(
select
distinct t.deviceid
from ext_startup_logs t
where t.appid = 'sdk34734' and concat(t.ym,t.day) >= formattime(getweekbegin(-2),'yyyyMMdd') and concat(t.ym,t.day) < formattime(getweekbegin(),'yyyyMMdd')
);
案例
单词统计
参考:https://www.cnblogs.com/walker-/p/11279810.html
电商分析
参考:https://blog.csdn.net/wqy992/article/details/96487520
影评分析
参考:https://blog.csdn.net/jin6872115/article/details/79871104