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 的正则表达式这个更强大的语言来指定匹配条件。