Hive 的基本运算符 及 查询

测试数据和表

数据

1       Bob     1200
2       Black   2100
3       BigDataBoy      5600

表

create table hive_db(
id int,
name string,
money int
)
row format delimited
fields terminated by '\t';

基本查询格式

select ... from ...

语法格式规范

• HQL 语言大小写不敏感
• HQL 可以写一行，也可以写多行
• 关键字不能被缩写也不能分行
• 各子句一般要分行写
• 使用缩进和换行提高语句的可读性

全表和特定列查询

全表查询

select * from 表名;

特定列查询

select 字段名,字段名 from 表名;

列别名

关键字 as

select 字段名 as 列别名, 字段名 as 列别名 from 表名

算数运算符

hive> select 1+6;
OK
7
Time taken: 0.117 seconds, Fetched: 1 row(s)

hive> select 2 & 6;
OK
2
Time taken: 0.144 seconds, Fetched: 1 row(s)

hive> select  ~6;
OK
-7
Time taken: 0.153 seconds, Fetched: 1 row(s)

常用函数

求行数 count(*) ，count() 还能进行其他的统计

hive> select count(*) from hive_db;  (会跑一个MapReduce任务)
Query ID = root_20200119201909_4eaa334c-5e26-46e4-8ff9-ef0e4a703394
Total jobs = 1
...
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 4.27 sec   HDFS Read: 6799 HDFS Write: 2 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 270 msec
OK
4
Time taken: 131.007 seconds, Fetched: 1 row(s)

最大值 max()

hive> select max(money) from hive_db;
Query ID = root_20200119202349_2d1c7587-554e-4089-8b99-b76cb08ddab1
...
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 4.06 sec   HDFS Read: 6908 HDFS Write: 5 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 60 msec
OK
5600
Time taken: 125.705 seconds, Fetched: 1 row(s)

最小值 min()

hive> select min(money) from hive_db;

总和 sum()

hive> select sum(money) from hive_db;

平均值 avg()

hive> select avg(money) from hive_db;

比较运算（Between/In/ Is Null）

简单例子

# 工资在 2000 到 6000 之间的
hive> select * from hive_db where money between 2000 and 6000;
OK
2    Black    2100
3    BigDataBoy    5600
Time taken: 0.118 seconds, Fetched: 2 row(s)

# 查询 money 为 NULL 的
hive> select * from hive_db where money is null;
OK
NULL    NULL    NULL
Time taken: 0.227 seconds, Fetched: 1 row(s)

# 查询 money 为 2100 和 5600 的数据
hive> select * from hive_db where money in(2100,5600);
OK
2    Black    2100
3    BigDataBoy    5600
Time taken: 0.21 seconds, Fetched: 2 row(s)

Like 和 RLike

Like 使用的是 SQL语法 下的匹配

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

_ 代表一个字符

• %o：匹配 o 开头的
• o%：匹配 o 结尾的
• %o%：匹配 包含 o 的
• _2%：匹配开头 第二位是 2 的
• __2_：匹配 4 位，但第3位 是 2 的

# 匹配 name 字段带 o 的
hive> select * from hive_db where name like '%o%';
OK
1    Bob    1200
3    BigDataBoy    5600
Time taken: 0.092 seconds, Fetched: 2 row(s)

RLike 使用的 Java 下的正则表达式

# 匹配 money 中 带有 12 的
hive> select * from hive_db where money rlike '12';
OK
1    Bob    1200
Time taken: 0.075 seconds, Fetched: 1 row(s)

逻辑运算符（And/Or/Not）

简单例子

# 查询 money 大于 1000 并 name 是 Bob 的
hive> select * from hive_db where money > 1000 and name = 'Bob';
OK
1    Bob    1200
Time taken: 0.145 seconds, Fetched: 1 row(s)

limit 语句

limit 子句用于限定返回的行数

hive> select * from hive_db limit 2;
OK
1    Bob    1200
2    Black    2100
Time taken: 0.196 seconds, Fetched: 2 row(s)

where 语句

将不满足条件的过滤掉

where 子句紧随 from 子句

# 查询 money 大于 2000 的
hive> select * from hive_db where money > 2000;
OK
2    Black    2100
3    BigDataBoy    5600
Time taken: 0.172 seconds, Fetched: 2 row(s)