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