大数据培训:Hive日常操作分享

Hive 表基本操作

1.创建表

CREATE TABLE if NOT EXISTS table_name

(

column_name1 string comment '字段名的释义',

column_name2 int comment '字段名的释义',

column_name3 int comment '字段名的释义',

column_name4 decimal(22,6) comment '字段名的释义',

...

)COMMENT '表名释义'

PARTITIONED BY (

`column1` string

,`column2` string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\n'

STORED AS orc;

大数据培训:Hive日常操作分享

 

2.查看表结构

两种方式均可查看表结构:

1.show create table table_name;

2.desc table_name;

3.重命名表名

语法:

alter table old_table_name rename to new_table_name;

实例:

//将表名为test1的表修改其表名为test2

alter table test1 rename to test2;

4.删除表

语法:

drop table table_name;

实例:

drop table test1;

5.增加列

语法:

alter table table_name add columns (columns_name1 string, columns_name2 int);

实例:

alter table test1 add columns (name string, age int);

6.修改列

语法1:

alter table table_name modify column_name column_type;

语法2:

alter table table_name change old_column_name new_column_name column_type;

实例1:

alter table test001 modify t_age int;

实例2:

alter table test001 change age t_age string;

7.删除列

语法:

alter table table_name drop column column_name;

实例:

alter table test002 drop column t_age;

8.清空表

语法:

//只清空表数据,不删除表结构

truncate table table_name;

实例:

truncate table test_003;

Hive 表查询语法

SELECT [ALL | DISTINCT] select_expression, select_expression, ...

FROM table_name

[WHERE where_condition]

[GROUP BY column_list [HAVING condition]]

[CLUSTER BY column_list | [DISTRIBUTE BY column_list][SORT BY | ORDER BY column_list]

]

[LIMIT number]

排序关键字:

order by(全局排序)

会对输入做全局排序,因此只有一个 reducer,因为多个 reducer 无法保证全局有序,当只有一个 reducer 时,会导致当输入规模较大时,耗费较长的计算时间。

order by asc:表示按照升序排列,不指定时默认按照升序排列

order by desc:表示按照倒序排列

注意:

order by 受 hive.mapred.mode 的影响,在 strict 模式下,必须使用 limit 对排序的数据量进行限制,因为数据量很大只有一个 reducer 的话,大数据培训会出现 OOM 或者运行时间超长的情况,所以 strict 模式下,不使用 limit 则会报错。

sort by(分区内排序)

其在数据进入 reducer 前完成排序,即在数据进入 reducer 之前为每个 reducer 都生成一个排序后的文件。

因此,如果用 sort by 进行排序,并且设置 mapreduce.job.reduces > 1,则 sort by 只能保证每个 reducer 的输出有序,不能保证全局有序。

sort by 不受 hive.mapred.mode 参数设置的影响,使用 sort by 可以指定执行的 reduce 个数,通过命令:set mapred.reduce.tasks = 3来指定。

distribute by(数据分配)

用于控制在 map 端如何拆分数据给 reduce 端,类似于 MapReduce 中分区 Partition 对数据进行分区。

依据 distribute by 后面的列来将数据分发给相应的 reducer,采用的是 hash 算法+取余数的方法。

sort by 为每个 reduce 生成一个排序文件,在有些情况下,需要控制某些特定的行应该到分配到哪个 reducer,这通常是为了进行后续的聚合操作。

distribute by 也能实现这个功能。因此,distribute by 经常和 sort by 配合使用。

cluster by

其同时具有 distribute by 与 sort by 的功能,但是排序只能是升序排序,不能指定排序规则为 ASC 或者 DESC。

当分区字段和排序字段相同 cluster by 可以简化 distribute by + sort by 的 SQL 写法,也就是说当 distribute by 和 sort by 字段相同时,可以使用cluster by 代替 distribute by 和 sort by。

Hive 常用函数

1.count

# 求总行数,count属于统计函数,count(1)等价于count(*)

select count(1) from test001;

2.max

# 求分数的最大值

select max(score) from test_score;

3.min

# 求分数的最小值

select min(score) from test_score;

4.sum

# 求分数的总和

select sum(score) from test_score;

5.avg

# 求分数的平均值

select avg(score) from test_score;

Hive 查询常用子句

1.where子句

where条件查询:按照“条件表达式”指定的条件进行查询。

条件表达式分为:比较运算符、逻辑运算符

比较运算符:<、<=、=、!= 或 <>、>=、>、in、between

逻辑运算符:not 或 !、or 或 ||、and 或 &&

2.group by子句

group by 分组:按照“属性名”指定的字段进行分组。

group by 子句通常和 count()、sum() 等聚合函数一起使用。

就是将一个“数据集”划分成若干个“小区域”,然后针对若干个“小区域”进行数据处理;

group by 语句中 select 指定的字段必须是“分组依据字段”,其他字段若想出现在 select 中则必须包含在聚合函数中。

3.having子句

having 筛选子句:有 group by 才能 having 子句,只有满足“条件表达式”中指定的条件的才能够输出。

having 子句在聚合后对组记录进行筛选,所有 having 必须和 group by 一起使用。

where 和 having 的区别:

(1). where 子句作用于表和视图,对列发挥作用,having 子句针对查询结果中的列发挥作用,筛选数据,对组进行聚合操作。

(2). where 在分组和聚集计算之前选取输入行(它控制哪些行进入聚集计算),而 having 在分组和聚集之后选取分组的行。

(3). 因此,where 子句不能包含聚集函数;因为试图用聚集函数判断哪些行输入给聚集运算是没有意义的。而 having 子句总是包含聚集函数。

(4).having 只用于 group by 分组统计语句。

4.order by子句

order by 排序子句:按照“属性名”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出,默认是按照“asc”来排序,即升序。

(1). order by column_name;//默认升序排列

(2). order by column_name desc;//降序排列

(3). order by column_name asc;//升序排列,与默认一样

(4). order by rand() //随机排列

5.limit子句

按照限制结果集输出限定结果

limit [offset,] N offset为偏移量,可选,不写则相当于limit 0、N,N 取出条目

例子:select * from 表名 limit 3,5;//输出第4到8条数据

6.like和rlike子句

(1). 使用 like 运算选择类似的值

(2). 选择条件可以包含字符或数字

%:代表零个或多个字符(任意个字符)

_:代表一个字符

rlike 子句是 hive 中这个功能的一个扩展,其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。

文章来源于无量测试之道


推荐阅读:

大数据开发之Hive SQL内置函数

大数据开发技术-Hive实战

大数据开发之数仓工具Hive(一)

大数据开发之离线分析工具Hive

上一篇:leetcode 73: 矩阵置零


下一篇:【数据库基础】SQL语句理解与练习