hive的概念与基本操作

Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供类SQL查询功能。其本质是将SQL转换为MapReduce的任务进行运算,底层由HDFS来提供数据的存储,说白了hive可以理解为一个将SQL转换为MapReduce的任务的工具,甚至更进一步可以说hive就是一个MapReduce的客户端

为什么使用Hive

  • 操作接口采用类SQL语法,提供快速开发的能力。
  • 避免了去写MapReduce,减少开发人员的学习成本。
  • 功能扩展很方便。

Hive的特点

  • 可扩展 :Hive可以自由的扩展集群的规模,一般情况下不需要重启服务。
  • 延展性: Hive支持用户自定义函数,用户可以根据自己的需求来实现自己的函数。
  • 容错: 良好的容错性,节点出现问题SQL仍可完成执行。

Hive架构

架构图

基本组成

用户接口:包括CLI、JDBC/ODBC、WebGUI。其中,CLI(command line interface)为shell命令行;JDBC/ODBC是Hive的java实现,与传统数据库JDBC类似;WebGUI是通过浏览器访问Hive。

元数据存储:通常是存储在关系数据库如mysql/derby中。Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字,表的列和分区及其属性,表的属性(是否为外部表等),表的数据所在目录等。

解释器、编译器、优化器、执行器:完成HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS 中,并在随后有MapReduce 调用执行。

Hive与Hadoop的关系

Hive利用HDFS存储数据,利用MapReduce查询分析数据。

Hive与传统数据库对比

hive用于海量数据的离线数据分析

总结:hive具有sql数据库的外表,但引用场景完全不同,hive只适合用来做批量数据统计分析。

Hive的数据存储

  1. Hive中所有的数据都存储在 HDFS 中,没有专门的数据存储格式(可支持Text,SequenceFile,ParquetFile,ORC格式RCFILE等)

    SequenceFile是hadoop中的一种文件格式:文件内容是以序列化的kv对象来组织的。

  2. 只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符,Hive 就可以解析数据。

  3. Hive 中包含以下数据模型:DB、Table,External Table,Partition,Bucket。

    • db:在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
    • table:在hdfs中表现所属db目录下一个文件夹
    • external table:与table类似,不过其数据存放位置可以在任意指定路径
    • partition:在hdfs中表现为table目录下的子目录
    • bucket:在hdfs中表现为同一个表目录下根据散列之后的多个文件

Hive基本操作

数据库操作

创建数据库

1
2
create database if not exists myhive;
use myhive;

说明:hive的表存放位置模式是由hive-site.xml当中的一个属性指定的

1
2
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>

创建数据库并指定hdfs存储位置

1
create database myhive2 location '/myhive2';

设置数据库键值对信息

数据库可以有一些描述性的键值对信息,在创建时添加:

1
create database foo with dbproperties('owner'='wjqixige','date'='20190120');

查看数据库键值对信息

1
desc database extended myhive2;

修改数据库键值对信息

1
alter database foo set dbproperties('owner'='wjqixige');

删除数据库

删除一个空数据库,如果数据库下面有数据表,那么就会报错

1
drop database myhive2;

强制删除数据库,包含数据库下面的表一起删除

1
drop database myhive2 cascade;

数据库表操作

创建表

1
2
3
4
5
6
7
8
9
10
create [external] table [if not exists] table_name(
col_name data_type [comment '字段描述信息'],
col_name data_type [comment '字段描述信息'])
[comment '表描述信息']
[partitioned by (col_name data_type [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 '指定表的路径']

说明:

  1. create table :创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
  2. external:关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive 创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
  3. comment:表示注释,默认不能使用中文
  4. partitioned by:表示使用表分区,一个表可以拥有一个或者多个分区,每一个分区单独存在一个目录下
  5. clustered by:对于每一个表分文件,Hive可以进一步组指成桶,也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。
  6. sorted by:指定排序字段和排序规则
  7. row format:指定表文件字段分隔符
  8. *sorted as *:指定表文案及你的存储格式。常用格式:SEQUENCEFILE, TEXTFILE,RCFILE,如果文件数据是纯文本,可以使用SRORED AS TEXTFILE。如果数据需要压缩,使用storted as SEQUENCEFILE。
  9. location:指定表文件的存储路径

内部表

创建表时,如果没有使用external关键字,则该表是内部表(managed table)

Hive建表字段类型

分类 类型 描述 字面量示例
原始类型 BOOLEAN true/false TRUE
TINYINT 1字节的有符号整数 -128~127 1Y
SMALLINT 2字节的有符号整数,112768~12767 1S
INT 4个字节的带符号整数 1
BIGINT 8字节带符号整数 1L
FLOAT 4字节单精度浮点数 1.0
DOUBLE 8字节双精度浮点数 1.0
DEICIMAL 任意精度的带符号小数 1.0
STRING 字符串,变长 “a”,’b’
VARCHAR 变长字符串 “a”,’b’
CHAR 固定长度字符串 “a”,’b’
BINARY 字节数组 无法表示
TIMESTAMP 时间戳,毫秒值精度 122127491795
DATE 日期 ‘2019-10-06’
INTERVAL 时间频率间隔
复杂类型 ARRAY 有序的同类型的集合 array(1,2)
MAP key-value,key必须为原始类型,
value任意类型
map(‘a’,1,’b’,2)
STRUCT 字段集合,类型可以不同 struct(‘1’,1,1.0),
named_stract
(‘col1’,’1’,’col2’,1,’col1’,1.0)
UNION 在有限取值范围内的一个值 create_union(1,’a’,61)

建表入门

1
2
3
4
use myhive;
create table stu(id int,name string);
insert into stu values(1,"zhangsan");
select * fomr stu;

创建表并指定字段之间的分隔符

1
create table if not exists stu2(id int,name string) row format delimited fields terminated by '\t';

创建表并指定表文件的存放路径

1
create table if not exists stu2(id int,name string) row format delimited fields terminated by '\t' location '/user/stu2';

根据查询结果创建表

1
create table stu1 as select * from stu2; ##通过复制表结构和表内容创建新表

根据已经存在的表结构创建表

1
create table stu4 like stu;

查询表的详细信息

1
desc formatted stu2;

删除表

1
drop table stu;

外部表

外部表说明

外部表因为是指定其他的hdfs路径的数据加载到表当中来,所以hive表会认为自己不完全独占这份数据,所以删除hive表的时候,数据仍然存放在hdfs当中,不会删掉。

内部表和外部表的使用场景

每天将收集到的网站日志定期流入HDFS文本文件。在外部表(原始日志表)的基础上做大量的统计分析,用到的中间表、结果表使用内部表存储,数据通过SELECT+INSERT进入内部表。

操作案例

分别创建老师与学生外部表,并向表中加载数据

  1. 创建老师表:

    1
    create external table teacher(t_id string,t_name string) row format delimited fields terminated by '\t';
  2. 创建学生表

    1
    create external table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields fields terminated by '\t';
  3. 从本地文件系统向表中加载数据

    1
    load data local inpath '/export/servers/hivedatas/student.csv' into table student;
  4. 加载数据并覆盖已有数据

    1
    load data local inpath '/export/servers/hivedatas/student.csv' overwrite into table student;
  5. 从hdfs文件系统向表加载数据(需要提前将数据上传到hdfs文件系统)

    1
    2
    3
    4
    $ cd /root/hivedatas
    $ hdfs dfs -mkdir -p /hivedatas
    $ hdfs dfs -put teacher.csv /hivedatas/
    $ local data input 'hivedatas/teacher.csv' into table teacher;

如果删掉student表,hdfs的数据仍然存在,并且重新创建表之后,表中就直接存在数据了,因为我们的student表使用的是外部表,drop table之后,表当中的数据依然保留在hdfs上面。

分区表

在大数据中,最常用的一种思想就是分治,我们可以把大的文件切割划分成一个个的小文件,这样每次操作一个小的文件就很容易了,同样的道理,在hive中也是支持这种思想的,就是我么你可以把大的数据,按照每月,或者天进行切分成一个个的小的文件,这样去操作小的文件就很容易了。

创建分区表语法

1
create table score(s_id string,c_id string,s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

创建一个表带多个分区

1
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';

加载数据到分区表中

1
load data local inpath '/export/servers/hivedatas/score.csv' into table score partition(month='201910');

加载数据到一个多分区的表中

1
load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2019',month='10',day='07');

多分区联合查询(使用union all)

1
select * form score where month='201910' union all select * from score where month='201909';

查看分区

1
show partitions score;

添加一个分区

1
alter table score add partition(month='201910');

同时添加多个分区

1
alter table score add partition(month='201010') partition(month='201011');

删除分区

1
alter table score drop partition(month='201010');

分桶表

分桶,就是将数据按照指定的字段进行划分到多个文件当中去,分桶就是MapReduce中的分区。

开启hive的分桶功能

1
set hive.enforce.bucketing=true;

设置reduce的个数

1
set mapreduce.job.reduces=1;

创建分桶表

1
create table course(c_id string,c_name string,t_id string) clustered by(c_id) into 1 buckets row format delimited fields terminated by '\t';

桶表的数据加载,由于桶表的数据加载通过hdfs dfs -put文件或通过load data均不好使,只能通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去。

创建普通表

1
create table course_common(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

普通表中加载数据

1
load data local inpath '/hivedatas/course.csv' into table course_common;

通过insert overwrite给桶表中加载数据

1
insert overwrite table course select * from course_common cluster by(c_id);

修改表

重命名

1
alter table old_table_name rename to new_table_name;

增加/修改列信息

  • 查询表结构

    1
    desc table_name;
  • 添加列

    1
    alter table table_name add columns(col_name data_type,...);
  • 更新列

    1
    alter table table_name change clomns col_name new_col_name new_data_type;

删除表

1
drop table tableName;

Hive查询语法

SELECT

1
2
3
4
5
6
7
8
SELECT [ALL | DISTINCT] select_expr,select_expr,...
FORM table_reference
[WHERE where_condition]
[GROUP BY col_list [HVAING condition]]
[ClUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY | ORDER BY col_list]
]
[LIMIT number]
  1. order by会对输入的全局排序,因此只有一个reducer,会导致当输入规模较大时,需要较长的计算时间。
  2. sort by不是全局排序,其在数据进入reducer前reducer前完成排序,因此,如果用sort by进行排序,并且设置mapred.reduce.task>1, 则sort by只保证每个reducer的输出有序,不保证全局有序。
  3. distribute by(字段)根据指定的字段数据分到不同reducer,且分发算法是hash散列。
  4. cluster by(字段)除了具有distribute by的功能外,还会对该字段进行排序。

因此,如果distribute和sort字段是同一个时,此时,cluster by=distribute by + sort by

查询语法

全表查询

1
select * from table_name;

选择特定列查询

1
select col_name1,col_name2,... from table_name;

列别名

  1. 重命名一个列
  2. 便于计算
  3. 紧跟列明,也可以在列明和别名之间加入关键字as
1
select col_name1 as name1, col_name2 from table_name

常用函数

  • 求总行数(count)

    1
    2
    ## count(1) 等价于 count(*)
    select count(1) form table_name;
  • 求分数的最大值(max)

    1
    select max(s_score) from score;
  • 求分数的最小值(min)

    1
    select min(s_score) from score;
  • 求分数的总和(sum)

    1
    select sum(s_score) from score;
  • 求分数的平均值(avg)

    1
    select avg(s_score) from score;

LIMIT语句

用于限制返回的行数

1
select * from score limit 1;

WHERE语句

  1. 使用where子句,将不满足条件的行过滤掉
  2. where子句紧随from子句
1
select * from score where s_score > 60;

比较运算符(BETWEEN/IN/IS NULL)

操作符 支持的数据类型 描述
A=B 基本数据类型 如果A等于B则返回TRUE,反之返回FALSE
A<=>B 基本数据类型 如果A和B都为NULL,则返回TRUE,其他的和等号(=)操作符的结果一致,如果任一为NULL则结果为NULL
A<>B, A!=B 基本数据类型 A或者B为NULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE
A<B 基本数据类型 A或者B为NULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE
A<=B 基本数据类型 A或者B为NULL,则返回NULL;如果A小于等于B,则返回TRUE,反之返回FALSE
A>B 基本数据类型 A或者B为NULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE
A>=B 基本数据类型 A或者B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE
A [NOT] BETWEEN B AND C 基本数据类型 如果A,B或者C任一为NULL,则结果为NULL。如果A的值大于等于B而且小于或等于C,则结果为TRUE,反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL 所有数据类型 如果A等于NULL,则返回TRUE,反之返回FALSE
A IS NOT NULL 所有数据类型 如果A不等于NULL,则返回TRUE,反之返回FALSE
IN(数值1, 数值2) 所有数据类型 使用 IN运算显示列表中的值
A [NOT] LIKE B STRING 类型 B是一个SQL下的简单正则表达式,如果A与其匹配的话,则返回TRUE;反之返回FALSE。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母’x’结尾,而‘%x%’表示A包含有字母’x’,可以位于开头,结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B STRING 类型 B是一个正则表达式,如果A与其匹配,则返回TRUE;反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。

LIKE和RLIKE

  1. 使用LIKE运算选择类似的值
  2. 选择条件可以包含字符或数字
    • %:代表零个或多个字符(任意个字符)
    • _:代表一个字符
  3. RLIKE子句是Hive中这个功能的一个扩展,其可以通过Java的正则表达式这个更强大的语言来指定匹配条件

逻辑运算符(AND/OR/NOT)

操作符 含义
AND 逻辑并
OR 逻辑或
NOT 逻辑否

分组

GROUP BY语句

GROUP BY语句通常会和聚合函数一起使用,按照一个或者多个队列结果进行分组,然后对每个组执行聚合操作。

HAVING语句

  1. having与where不同点
    1. where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据
    2. where后面不能写分组函数,而having后面可以使用分组函数
    3. having只用于group by分组统计语句

JOIN语句

等值JOIN

Hive支持通常的SQL JOIN语句,但是只支持等值连接,不支持非等值连接。举例:

1
select s.s_id,s.s_score,stu.s_name,stu.s_birth from score s left join student stu on s.s_id=stu.s_id;

内连接(INNER JOIN)

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。举例:

1
select * from teacher t inner join course c on t.t_id=c.t_id;

左外连接(LEFT OUTER JOIN)

JOIN操作符左边表中复合WHERE子句的所有记录将会被返回。

1
select * from teacher t left join course c on t.t_id = c.t_id;

右外连接(RIGHT OUTER JOIN)

JOIN操作符右边表中复合WHERE子句的所有记录将会被返回。

1
select * from teacher t right join course c on t.t_id = c.t_id;

多表连接

注意:连接n个表,至少需要n-1个连接条件。例如:连接三个表 ,至少需要两个连接条件。

多表连接查询,查询老师对应的课程,以及对应的分数,对应的学生

1
select * from teacher t left join course c on t.t_id = c.t_id left join score s on s.c_id=c.c_id left join student stu on s.s_id = stu.s_id;

排序

全局排序(ORDER BY)

ORDER BY:全局排序,一个reduce

  1. 使用ORDER BY子句排序ASC(升序,默认),DESC(降序)

  2. ORDER BY子句在SELECT语句的结尾

  3. 举例

    1
    select * from student s left join score sco on s.s_id=sco.s_id order by sco.s_score DESC;

按照别名排序

按照分数的平均值排序

1
select s_id,avg(s_score) avg from score group by s_id order by avg;

多个列排序

按照学生id和平均成绩进行排序

1
select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg;

每个MR内部排序(SORT BY)局部排序

Sort By:每个MapReduce内部进行排序,对全局结果集来说不是排序。

  1. 设置reduce个数

    1
    set mapreduce.job.reduces=1;
  2. 查看设置reduce个数

    1
    set mapreduce.job.reduces;
  3. 查询成绩按照成绩降序排列

    1
    select * from score sort by s_score;
  4. 将查询结果导入到文件中(按照成绩降序排列)

    1
    insert overwrite local directory '/hivedatas/sort' select * from score sort by s_socre;

分区排序(DISTRIBUTE BY)

Distribute By:类似MR中partition,进行分区,结合sort by使用。

注意,Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前

对于distribute by进行测试,一定要分配多reduce进行处理,否则无法看到distribute by的效果。

CLUSTER BY

当distribute by和sort by字段相同时,可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序,不能指定排序规则为ASC或者DESC

以下两种写法等价

1
2
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

内置函数

内容较多,见《Hive官方文档》:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

  1. 查看系统自带的函数

    1
    hive> show functions;
  2. 显示自带的函数的用法

    1
    hive> desc function upper;
  3. 详细显示自带的函数的用法

    1
    hive> desc function extended upper;

Hive自定义函数

  1. Hive 自带了一些函数,比如:max/min等,但是数量有限,自己可以通过自定义UDF来方便的扩展。

  2. 当Hive提供的内置函数无法满足你的业务处理需要时,此时就可以考虑使用用户自定义函数(UDF:user-defined function)。

  3. 根据用户自定义函数类别分为以下三种:

    1. UDF(User-Defined-Function):一进一出
    2. UDAF(User-Defined Aggregation Function):聚集函数,多进一出,类似于:count/max/min
    3. UDTF(User-Defined Table-Generating Functions):一进多出,如lateral view explore()
  4. 官方文档地址:https://cwiki.apache.org/confluence/display/Hive/HivePlugins

  5. 编程步骤:

    1. 继承org.apache.hadoop.hive.ql.UDF
    2. 需要实现evaluate函数;evaluate函数支持重载;
  6. 注意事项

    1. UDF必须要有返回类型,可以返回null,但是返回类型不能为void;
    2. UDF中常用Text/LongWritable等类型,不推荐使用java类型;

UDF开发实例

简单UDF示例

  1. 创建maven java 工程,导入jar包;

    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
    54
    55
    56
    57
    <repositories>
    <repository>
    <id>cloudera</id>
    <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
    </repository>
    </repositories>
    <dependencies>
    <dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-common</artifactId>
    <version>2.6.0-cdh5.14.0</version>
    </dependency>
    <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>1.1.0-cdh5.14.0</version>
    </dependency>
    </dependencies>
    <build>
    <plugins>
    <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>3.0</version>
    <configuration>
    <source>1.8</source>
    <target>1.8</target>
    <encoding>UTF-8</encoding>
    </configuration>
    </plugin>
    <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-shade-plugin</artifactId>
    <version>2.2</version>
    <executions>
    <execution>
    <phase>package</phase>
    <goals>
    <goal>shade</goal>
    </goals>
    <configuration>
    <filters>
    <filter>
    <artifact>*:*</artifact>
    <excludes>
    <exclude>META-INF/*.SF</exclude>
    <exclude>META-INF/*.DSA</exclude>
    <exclude>META-INF/*/RSA</exclude>
    </excludes>
    </filter>
    </filters>
    </configuration>
    </execution>
    </executions>
    </plugin>
    </plugins>
    </build>
  2. 开发java类继承UDF,并重载evaluate 方法;

    1
    2
    3
    4
    5
    6
    7
    8
    9
    public class WjQiXiGeUDF extends UDF {
    public Text evaluate(final Text s) {
    if (null == s) {
    return null;
    }
    //返回大写字母
    return new Text(s.toString().toUpperCase());
    }
    }
  3. 将我们的项目打包,并上传到hive的lib目录下;

  4. 添加我们的jar包;

    1
    2
    $ cd /hive-1.1.0-cdh5.14.0/lib
    $ mv original-hive_udf-1.0-SNAPSHOT.jar udf.jar

    Hive的客户端添加jar包

    1
    add jar /hive-1.1.0-cdh5.14.0/lib/udf.jar
  5. 设置函数与我们的自定义函数关联;

    1
    create temporary function tolowercase as 'cn.wjqixige.udf.WjQiXiGeUDF'
  6. 使用自定义函数

    1
    select tolowercase('abc');
-------------本文结束感谢您的阅读-------------

本文标题:hive的概念与基本操作

文章作者:Mr.wj

发布时间:2019年12月15日 - 10:52

最后更新:2019年12月15日 - 11:13

原始链接:https://www.wjqixige.cn/2019/12/15/hive的概念与基本操作/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Mr.wj wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!