文章目录
Hive
1.基本概念
1.什么是Hive
Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供类
SQL查询功能。
其本质是将SQL转换为MapReduce的任务进行运算,底层由HDFS来提供数据的存储,说白了hive可以
理解为一个将SQL转换为MapReduce的任务的工具,甚至更进一步可以说hive就是一个MapReduce的
客户端
2.Hive的特点
- 可扩展:Hive可以自由的扩展集群的规模,一般情况下不需要重启服务。
- 延展性:Hive支持用户自定义函数,用户可以根据自己的需求来实现自己的函数
- 容错:良好的容错性,节点出现问题SQL仍可完成执行
3.Hive的架构
Hive基础架构由用户接口、元数据存储和解析器三部分组成;
Hive包含一个名为HiveServer2的服务器,通过它可以使用JDBC、开放式数据库连接及Thrift的客户端连接。HiveServer2支持多种会话模式,由驱动器、编译器和执行器构成,共同完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成,生成的查询计划存储在HDFS中,并随后由MapReduce调用执行。并于元数据服务通信,并使用关系型数据库存储元数据(Hive Metastore),存储内容包括表的名字,表的列和分区及属性,以及表的数据所在的位置。
2.Hive的交互方式
- bin/hive
cd /{Hive的目录}/apache-hive-3.1.0-bin/
bin/hive //启动命令行
- HiveServer2
官方推荐HiveServer2方式,需要先启动hiveserver2服务端,然后通过客户端连接:
cd /{Hive目录}/apache-hive-3.1.0-bin/
bin/hive --service hiveserver2 //启动服务端
cd /{hive目录}/apache-hive-3.1.0-bin
bin/beeline
!connect jdbc:hive2://node01:10000
注意:需要在hadoop的配置文件core-site.xml中添加以下两行配置:
<property>
<name>hadoop.proxyuser.hadoop.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.hadoop.groups</name>
<value>root</value>
</property>
- 直接执行sql语句或者脚本
-e直接执行sql;-f执行脚本文件
//执行sql
bin/hive -e "create database if not exists mytest;"
//执行脚本
bin/hive -f /hive.sql
3.Hive基础语句
3.1 创建数据库
create database if not exists myhive;
注意:hive的表存放位置模式是由hive-site.xml当中的一个属性指定的
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
如指定存放位置:
create database myhive2 location '/myhive2';
3.2 修改/查看/删除数据库
(1)alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的,包括
数据库的名称以及数据库所在的位
alter database myhive2 set dbproperties('createtime'='20180611');
(2)查看数据库基本信息:
desc指令
desc database myhive2;
//查看更详细信息
desc database extended myhive2;
(3)删除数据库:删除一个空数据库,如果数据库下面有数据表,那么就会报错
drop database myhive2;
强制删除数据库,包含数据库下面的表一起删除
drop database myhive cascade
3.3 创建数据库表
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]
1.CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用
IF NOT EXISTS 选项来忽略这个异常。
2… EXTERNAL关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径
(LOCATION),Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅
记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被
一起删除,而外部表只删除元数据,不删除数据
3.. LIKE 允许用户复制现有的表结构,但是不复制数据。
4.. ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS
TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] |SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, …)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者
ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,
用户在指定表的列的同时也会指定自定义的 SerDe,Hive通过 SerDe 确定表的具体的列的数据。
如创建指定字段之间的分隔符:
create table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t'
5… STORED AS
SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED
AS SEQUENCEFILE
6.CLUSTERED BY :对于每一个表(table)或者分区, Hive可以进一步组织成桶,也就是说桶是更为细粒度的数据范 围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
把表(或者分区)组织成桶(Bucket)有两个理由:
- 获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接(Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量
- 使取样(sampling)更高效。在处理大规模数据集时,在开发和修改查询的阶段,如果能在数据集的一小部分数据上试运行查询,会带来很多方便。
3.4 Hive支持数据类型
除了原始数据类型以外,Hive还支持以下四种复杂数据类型:
ARRAY | 有序的同类型集合:array(1,2) |
---|---|
MAP | key-value,key必须为原始类型,value可以任意类型 |
STRUCT | 字段集合,类型可以不同 |
UNION | 有限取值范围内的一个值 |
详细可查看官网:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
3.5 内/外部表
定义:未被external修饰的是内部表(managed table),被external修饰的为外部表(external table)
区别:
- 内部表数据由Hive自身管理,外部表数据由HDFS管理;
- 内部表数据存储的位置是hive.metastore.warehouse.dir(默认:/user/hive/warehouse),外部表数据的存储位置由自己制定(如果没有LOCATION,Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹,并将属于这个表的数据存放在这里);
- 删除内部表会直接删除元数据(metadata)及存储数据;删除外部表仅仅会删除元数据,HDFS上的文件并不会被删除;
- 对内部表的修改会将修改直接同步给元数据,而对外部表的表结构和分区进行修改,则需要修复(MSCK REPAIR TABLE table_name;)
3.6分区表
在大数据中,最常用的一种思想就是分治,我们可以把大的文件切割划分成一个个的小的文件,这样每次操作一个小的文件就会很容易了,同样的道理,在hive当中也是支持这种思想的,就是我们可以把大的数据,按照每天,或者每小时进行切分成一个个的小的文件,这样去操作小的文件就会容易得多了。
创建分区表语法:
注意的是,分区字段和表字段没有关联
create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t'
创建一个表带多个分区 /year/month/day:
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='201801')
多分区表联合查询——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 drop partition(month = '201806');
直接向分区表中插入数据:
create table score3 like score;
insert into table score3 partition(month ='201807') values ('001','002','100');
3.7 分桶表
将数据按照指定的字段进行分成多个桶中去,说白了就是将数据按照字段进行划分,可以将数据按照字段划分到多个文件当中去
开启Hive分桶功能:
set hive.enforce.bucketing=true;
设置Reduce个数:
set mapreduce.job.reduces=3;
创建桶表:
clustered by(字段) into (int)buckets
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'
桶表的数据加载:
注意:桶表的数据加载通过hdfs dfs -put或者load data均不好使用,只能通过创建普通表,注入数据,然后通过insert overwrite方式将普通表的数据通过查询的方式加载到桶表中;
- 创建普通表,并load data注入数据
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);
3.8 局部/分区/分桶排序
-
局部排序——sort by
Sort By:每个MapReduce内部进行排序,对全局结果集来说不是排序 -
分区排序——distribute by
Distribute By:类似MR中partition,进行分区,结合sort by使用 ;Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。 -
分桶排序——cluster by
当distribute by和sort by字段相同时,可以使用cluster by方式
当distribute by和sort by字段相同时,可以使用cluster by方式
以下两种写法等价:
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;
3.9 其他常用操作
常用函数:count、max、min、sum、avg
Like和RLike:
- 使用LIKE运算选择类似的值 ,选择条件可包含字符或者数字;
- RLike子句可以通过正则表达式这个更强大的语言来匹配条件;
例:
1.查找以8开头的成绩
select * from score where s_score like '8%';
2.查找成绩中含有9的所有成绩数据
select * from score where s_score rlike '[9]'; # like '%9%'
Having语句:
与where的区别:where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据 ;having一般接在分组Group by后面;
常用内置函数:
字符串连接函数: concat
select concat('abc','def’,'gh');
# 带分隔符字符串连接函数: concat_ws
select concat_ws(',','abc','def','gh');
# cast类型转换
select cast(1.5 as int);
#get_json_object(json 解析函数,用来处理json,必须是json格式)
select get_json_object('{"name":"jack","age":"20"}','$.name');
# URL解析函数
select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');