Hive之——查询

转载请注明出处：https://blog.csdn.net/l1028386804/article/details/88384579

select.. from 语句
比如：employees表如下：

create table employees(
name string,
salary float,
subordinates array<string>,
deductions map<string, float>,
address struct<street:string, city:string, state:string, zip:int>
)
partitioned by (country string, state string);

查询name和salary：

hive> select name, salary from employees;
binghe	100000.0

查询name和subordinates

hive> select name, subordinates from employees;
binghe	["Mary Smith", "Todd Jones"]

查询name和deductions：

hive> select name, deductions from employees;
binghe	{"Federal Taxes":0.2, "State Taxes":0.05, "Insurance":0.1}

查询name和address

hive> select name, address from employees;
binghe	{"street": "1 Michigan Ave.", "city": "Chicago", "state" : "IL", "zip": 60600}

选择subordinates数组中的第一个元素查询

hive> select name, subordinates[0] from employees;
binghe	Mary Smith

注意：引用一个不存在的元素将会返回NULL。同时，提取出的STRING数据类型的值不再加引号

引用MAP元素，也可以使用ARRAY[...]语法

hive> select name, deductions["State Taxes"] from employees;
binghe	0.05

引用struct,可以使用“点”符号

hive> select name, address.city from employees;
binghe	Chicago

使用正则表达式来指定列
从表stocks中选择symbol列和所有列名以price作为前缀的列:

hive> select symbol, `price.*` from stocks;

使用列值进行计算

hive> select upper(name), salary, deductions["Federal Taxes"], round(salary * (1 - deductions["Federal Taxes"])) from employees;
BINGHE	100000.0	0.2	80000

注: round()函数返回一个DOUBLE类型的最近整数。

通过设置属性hive.map.aggr值为true来提高聚合的性能

hive> set hive.map.aggr=true;
hive> select count(*), avg(salary) from employees;

表生成函数
表生成函数可以将单列扩展成多列或多行。
将employees表中每行记录中的subordinates字段内容转换成0个或者多个新的记录行。如果subordinates字段内容为空，则不会产生新的记录；如果不为空，则数组的每个元素将产生一行新纪录:

hive> select explode(subordinates) as sub from employees;
Mary Smith
Todd Jones
Bill King

这里，使用as sub子句定义了列别名sub。当使用表生成函数时，Hive要求使用列别名。

嵌套select语句

from (
select upper(name), salary, deductions["Federal Taxes"] as fed_taxes, round(salary * (1 - deductions["Federal Taxes"])) as salary_fed_taxes from employees
) e 
select e.name, e.salary_fed_taxes 
where e.salary_fed_taxes > 70000;

CASE ... WHEN ... THEN 句式

select name, salary,
case
when salary < 50000.0 then 'low'
when salary >= 50000.0 and salary < 70000.0 then 'middle'
when salary >= 70000.0 and  salary < 100000.0 then 'high'
else 'very hign'
end as bracket from employees;

什么情况下避免进行MapReduce
本地模式不用执行MapReduce，例如：

select * from employees;

对于where语句中的过滤条件只是分区字段(无论是否使用limit语句限制输出的记录条数),也是无需MapReduce过程:

select * from employees 
where country = 'US' and state = 'CA' 
limit 100;

如果属性hive.exec.mode.local.auto的值设置为true的话，Hive还会尝试使用本地模式执行其他的操作.

set hive.exec.mode.local.auto=true;

否则，Hive使用MapReduce来执行其他所有的查询
建议将set hive.exec.mode.local.auto=true;加到$HOME/.hiverc配置文件中。

从employees表中查找所有住址的街道名称中含有单词Chicago或Ontario的雇员名称和街道信息

hive> select name, address.street from employees where address.street rlike '.*(Chicago|Ontario).*';

等价于：

hive> select name, address.street from employees where address.street like '%Chicago%' or address.street like '%Ontario%';

group by 语句

select year(ymd), avg(price_close) form stocks where exchange = 'NASDAQ' and symbol = 'AAPL' group by year(ymd);

having语句

select year(ymd), avg(price_close) form stocks where exchange = 'NASDAQ' and symbol = 'AAPL' group by year(ymd) having avg(price_close) > 50.0;

JOIN语句
JOIN ... on
Hive支持通常的SQL JOIN语句，但是只支持等值连接。

JOIN优化
当对3个或者更多个表进行JOIN连接时，如果每个ON子句都使用相同的连接键的话，那么只会产生一个MapReduce Job。
Hive假定查询中最后一个表是最大的那个表，因此，用户需要保证连续查询中的表的大小是从左到右依次增加的。

Hive提供了一个“标记”机制来显示的告之查询优化器哪张表是大表：

select /*+streamtable(s)*/ s.ymd, s.symbol, s.price_close, d.dividend from stocks s join dividends d on s.ymd = d.ymd and s.symbol = d.symbol where s.symbol = 'AAPL';

这里，Hive将尝试将表stocks作为驱动表，即使其在查询中不是位于最后面的。

left outer join
select ... left outer join ...
join左边表中符合where子句的所有记录将会被返回。join操作符右边表中如果没有符合on后面连接条件的记录时，那么从右边表指定选择的列的值将会是NULL。

对于是否可以将where语句中的内容放置到on语句里，至少知道分过滤条件是否可以放置在on语句中。对于外连接(outer join)来说是不可以这样的，但是对于内连接(inner join)是其作用的。有一个适用于所有种类的连接解决方案，就是使用嵌套select语句。

select s.ymd, s.symbol, s.price_close, d.dividend from 
(select * from stocks where symbol = 'AAPL' and exchange = 'NASDAQ') s 
left outer join 
(select * from dividends where symbol = 'AAPL' and exchange = 'NASDAQ') d
on s.ymd = d.ymd;

嵌套select语句会按照要求执行“下推”过程，在数据进行连接操作之前会先进行分区过滤。

right outer join
右外连接(right outer join) 会返回右表所有符合where语句的记录。左表中匹配不上的字段值用NULL代替；

full outer join
返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

left semi-join
左半开连接(left semi-join)会返回左边表的记录，前提是其记录对于右边表满足on语句中的判定条件。
注意： left semi-join中，select和where语句中不能引用到右边表中的字段。

Hive不支持右半开连接(right semi-join)

笛卡尔积JOIN
笛卡尔积是一种连接，表示左表的行数乘以右表的行数等于笛卡尔结果集的大小。

map-side JOIN
如果所有表只有一张表是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中。Hive可以在map端执行连接过程(成为map-side JOIN)，这是因为Hive可以和内存中的小表进行逐一匹配，从而省略掉城规连接操作所需要的reduce过程。

在Hive v0.7之前要加 /*+mapjoin */ 标记

select /*+mapjoin(d) */ s.ymd, s.symbol, s.price_close, d.dividend 
from stocks s join dividends d on s.ymd = d.ymd and s.symbol = d.symbol 
where s.symbol = 'AAPL';

在Hive v0.7之后的版本
不需要加 /*+mapjoin */ 标记，但是需要设置hive.auto.convert.join的值为true

set hive.auto.convert.join=true;

select s.ymd, s.symbol, s.price_close, d.dividend 
from stocks s join dividends d on s.ymd = d.ymd and s.symbol = d.symbol 
where s.symbol = 'AAPL';

用户也可以配置能够使用这个优化的小表的大小。如下是这个属性的默认值(单位是字节)

hive.mapjoin.smalltable.filesize=25000000

可以将这些属性设置在$HOME/.hiverc文件中。

注意：Hive对于右外连接和全外连接不支持这个优化。

如果表中的数据是分桶的，那么对于大表，在特定的情况下同样可以使用这个优化。简单的说，表中的数据必须是按照on语句中的键进行分桶的，而且其中一张表的分桶个数必须是另一张表分桶个数的若干倍。当满足这些条件时，那么Hive可以在map阶段按照分桶数据进行连接。因此这种情况下，不需要先获取到表中所有的内容，之后才去和另一张表中每隔分桶进行匹配连接。
不过，这个优化需要设置参数hive.optimize.bucketmapjoin为true才可以开启这个优化。

set hive.optimize.bucketmapjoin=true;

如果涉及的分桶表都具有相同的分桶数，而且数据是按照连接键或桶的键进行排序的，那么这时Hive可以执行一个更快的分类-合并连接(sort-merge join)，这个优化需要设置下面的属性:

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;

order by操作可能会导致运行时间过长，如果属性hive.mapred.mode的值是strict的话，那么Hive要求这样的语句必须加油limit语句进行限制。默认情况下，这个属性是nonstrict,也就是不会有限制。

类型转换
对数值型数据进行类型转换，关键字是cast

select name, salary from employees where cast(salary as float) < 100000.0

类型转换函数的语法是cast(value as type)
如果上例中的salary字段的值不是合法的浮点数字符串的话，Hive会返回NULL。
注意：将浮点数转换成整数的推荐方式是使用round()或者floor()函数，而不是使用类型转换函数cast。

类型转换binary
binary类型只支持binary类型转换为string类型。如果值是数值的话。可以嵌套cat()对其进行类型转换，如下，b字段类型是binary

select (2.0 * cast(cast(b as string) as double)) from src;

也可以将string类型转换为binary类型。

抽样查询
有时需要的是一个具有代表性的查询结果而不是全部结果
比如numbers表只有number字段，其值是1到10.
可以使用rand()函数进行抽样，这个函数会返回一个随机值。

hive> select * from numbers tablesample(bucket 3 out of 10 on rand()) s;
2
4
hive> select * from numbers tablesample(bucket 3 out of 10 on rand()) s;
7
10
hive> select * from numbers tablesample(bucket 3 out of 10 on rand()) s;
3
9

如果是按照指定的列，而非rand()函数进行分桶的话，那么同一语句多次执行的返回值是相同的:

hive> select * from numbers tablesample(bucket 3 out of 10 on number) s;
2
hive> select * from numbers tablesample(bucket 3 out of 10 on number) s;
2
hive> select * from numbers tablesample(bucket 3 out of 10 on number) s;
2

分桶语句中的分母表示的是数据将会被散列的桶的个数，而分子表示将会选择的桶的个数:

hive> select * from numbers tablesample(bucket 1 out 2 on number) s;
2
4
6
8
10
hive> select * from numbers tablesample(bucket 2 out 2 on number) s;
1
3
5
7
9

数据块抽样
Hive提供了另外一种按照抽样百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样

hive> select * from numbersflat tablesample(0.1 percent) s;

这种抽样的最小抽样单元是一个HDFS数据块，因此，如果表的数据大小小于普通的块大小128MB的话，那么将会返回所有行。

基于百分比的抽样方式提供了一个变量，用于控制基于数据块的调优的种子信息:

<property>
	<name>hive.sample.seednumber</name>
	<value>0</value>
</property>

分桶表的输入裁剪
如下的查询和上述tablesample操作相同

hive> select * from numbersflat where number % 2 == 0;
2
4
6
8
10

对于大多数类型的表确实这样，抽样会扫描表中所有的数据，然后在每N行中抽取一行数据。不过，如果tablesample语句中指定的列和clustered by语句中指定的列相同，那么tablesample查询就只会扫描涉及到的表的哈希分区下的数据:

hive> create table numbers_bucketed(number int) clustered by (number) into 3 buckets;
hive> set hive.enforce.bucketing=true;
hive> insert overwrite table numbers_bucketed select number from numbers;
hive> dfs -ls /user/hive/warehouse/mydb.db/numbers_bucketed;
....
hive> dfs -cat /user/hive/warehouse/mydb.db/numbers_bucketed/000001_0;
1
7
10
4

这个表已经聚集成3个桶了，下面的这个查询会高效的仅对其中一个数据桶进行抽样:

hive> select * from numbers_bucketed tablesample(bucket 2 out of 3 on number) s;
1
7
10
4

union all
union all可以将2个或多个表进行合并，每一个union子查询都必须具有相同的列，而且对应的每个字段的字段类型必须是一致的。例如：如果第2个字段是float类型的，那么其他所有子查询的第2个字段必须都是float类型的。
比如:

select log.ymd, log.level, log.message 
from(
select l1.tmd, l1.level,
l1.message, 'Log1' as source 
from log1 l1 
union all
select l2.ymd, l2.level,
l2.message, 'Log2' as source 
from log1 l2
) log
sort by log.ymd asc;

union 也可以用于同一个源表的数据合并，从逻辑上讲，可以使用一个select和where语句来获得相同的结果。这个技术便于将一个长的复杂的where讵分割成2个或多个union子查询。不过除非源表建立了索引，否则，这个查询将会对同一份源数据进行多次拷贝分发。例如:

from(
from src select src.key, src.value where src.key < 100
union all
from src select src.* where src.key > 110
) unioninput
insert overwrite directory '/tmp/union.out' select unioninput.*;