位置:首页 > 九章学会Hive - Hive的查询 >

九章学会Hive - Hive的查询

作者:小牛君|发布时间:2017-06-16

小牛学堂的课程大纲最近进行了再一次升级,并且同时推出Java大数据平台开发班、Python爬虫与数据挖掘班、Spark项目班、Spark大神班、机器学习算法实战班、BI数据分析实战班, 目前这类人群凤毛麟角,导致这个行业的平均薪资极高,为此小牛学堂集合了行业的诸多大牛开设对应班级,为想学习的同学提供机会!
如果想了解详细情况,请联系 今日值班讲师 或者直接加入千人QQ群进行咨询:210992946

以下是本文正文:


1.  hive查询

like和rlike

like    _表示一个任意字符,%表示0个或多个任意字符

rlike   完全兼容java正则表达式

 

浮点数比较

cast(0.2 as float)转换

尽量使用roundfloorceil转换为整数

 

hive数据类型

Number Types

Type

size

Postfix

Example

TINYINT

1byte

Y

100Y

SMALLINT

2byte

S

100S

INT

4byte


100

BIGINT

8byte

L

100L

 

FLOAT        4byte单精度     

DOUBLE     8byte双精度

 

DECIMAL type 基于Java's BigDecimal

CREATE TABLE foo (

  a DECIMAL, -- Defaults to decimal(10,0)

  b DECIMAL(9, 7)

)

DECIMAL(9, 7)表示最大精度是9小数位是7,9代表十进制数值总长度

hive 0.12.0 0.13.0 Decimal类型不兼容

需要使用如下语句修正

ALTER TABLE foo CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);

 

 

字符串string使用单引号或双引号表示

varchar是指定最大长度的字符串

Char是指定长度的字符串,超过长度的字符串会被截断,少于长度的会在尾部补充空白

 

cast(string as date)     如果字符串是'YYYY-MM-DD'格式, 将返回 year/month/day date类型.否则返回NULL.

cast(date as string)     返回 'YYYY-MM-DD'格式的字符串.

 

Allowed Implicit Conversions


void

boolean

tinyint

smallint

int

bigint

float

double

decimal

string

varchar

timestamp

date

binary

void to

true

true

true

true

true

true

true

true

true

true

true

true

true

true


boolean to

false

true

false

false

false

false

false

false

false

false

false

false

false

false


tinyint to

false

false

true

true

true

true

true

true

true

true

true

false

false

false


smallint to

false

false

false

true

true

true

true

true

true

true

true

false

false

false


int to

false

false

false

false

true

true

true

true

true

true

true

false

false

false


bigint to

false

false

false

false

false

true

true

true

true

true

true

false

false

false


float to

false

false

false

false

false

false

true

true

true

true

true

false

false

false


double to

false

false

false

false

false

false

false

true

true

true

true

false

false

false


decimal to

false

false

false

false

false

false

false

false

true

true

true

false

false

false


string to

false

false

false

false

false

false

false

true

true

true

true

false

false

false


varchar to

false

false

false

false

false

false

false

true

true

true

true

false

false

false


timestamp to

false

false

false

false

false

false

false

false

false

true

true

true

false

false


date to

false

false

false

false

false

false

false

false

false

true

true

false

true

false


binary to

false

false

false

false

false

false

false

false

false

false

false

false

false

true


 

 

 

Hive的排序

order by 排序使用全局排序只有一个 reducer task 运行。

sort by 排序是在每个 reducer task 里面进行局部排序,不做全局排序。

cluster by ... distribute by ... sort by ...的简写。

 

distribute by s.symbol

 sort by s.symbol asc,s.ymd asc;

 

distribute by 表示分区字段,指定的字段相同字段内容会进入同一个reducer

 sort by 指定排序列。

asc是默认值,可以省略

 

 

having

对于聚合函数的结果只能通过having过滤,否则需要通过嵌套select子查询进行where过滤

 

 

表连接

连接共有:

join(inner join),left join(left outer join),right join(right outer join),full join(full outer join)

 

注意:

hive的连接条件一定要在on中,否则会笛卡尔积

不允许笛卡尔积查询

set hive.mapred.mode=strict;

允许

set hive.mapred.mode=nonstrict;

 

还有LEFT SEMI-JOIN

left semi join in/exists 子查询的一种更高效的实现。

hive 当前没有实现 in/exists 子查询,所以你可以用 left semi join 重写你的子查询语句。

left semi join 的限制是, join 子句中右边的表只能在on 子句中设置过滤条件,在 where 子句、select 子句或其他地方过滤都不行。

 

select a.key, a.value from a

where a.key in (select b.key from b);

可以被重写为:

select a.key, a.value from a

left semi join b on (a.key = b.key)

 

但是hive不支持

select s.ymd, a.symbol,s.price_close from stocks s

where s.ymd,s.symbol in

(select d.ymd,d.symbol from dividends d);

只能使用left semi join

select s.ymd, a.symbol,s.price_close from stocks s

left semi join dividends d on s.ymd=d.ymd and s.symbol=d.symbol;

hive只支持等值连接

 

select

  e.id, e.name, e.age, if(e.sex==1, '','') as gender,

  s.salary, d.name from t_dept d

  join t_employee e on d.id = e.deptid

  join t_salary s on e.id = s.empid

where e.name in ('张三','韩梅梅') and s.salary > 5000;

Hive 会对每一对 JOIN 连接对象启动一个 MapReduce 任务。

本例中,

会首先启动一个 MR Job

  对表 d 和表 e 进行连接操作,

然后会在启动一个 MR Job

  将第一个MRJob的输出和表 c 进行连接操作。

最后对结果进行过滤

可改写成先过滤,再连接

select

  e.id, e.name, e.age,

  if(e.sex==1, '','') as gender,

  s.salary, d.name

from t_dept d

join

  (select * from t_employee where name in ('张三','韩梅梅')) e

on d.id = e.deptid

join

  (select * from t_salary where salary > 5000) s

on e.id = s.empid;

 

 

 

 

join优化

使连续查询的表的大小从左到右依次增加,hive在对每行操作时,会尝试把其他表缓存起来

也可以通过 /*+STREAMTABLE(d)*/ 显式指定大表

 

mapjoin

Hive v0.12.0 版本,设置如下参数

set hive.auto.convert.join=true;

hive0.11 在表的大小符合设置时

  hive.mapjoin.smalltable.filesize=25000000

默认会自动把 join 转换为 map join

是否将多个 mapjoin 合并成一个

set hive.auto.convert.join.noconditionaltask=true;

多个mapjoin合并成一个mapjoin时,其表的总的大小须小于该值

set hive.auto.convert.join.noconditionaltask.size=10000;

 

/*+ MAPJOIN(d)*/显式指定小表

使用默认启动该优化的方式如果出现默名奇妙的BUG(比如MAPJOIN并不起作用),

就设置不忽略mapjoin标记,不自动进行mapjoin.

set hive.ignore.mapjoin.hint=fase;

set hive.auto.convert.join=fase;

然后在查询语句中显式指定:

select /*+ MAPJOIN(d)*/ e.id, e.name,   e.age,

    if(e.sex==1, '','') as gender,

    s.salary, d.name

from t_dept d

inner join t_employee e on d.id =   e.deptid

inner join t_salary s on e.id = s.empid;

 

select /*+MAPJOIN(smallTableTwo)*/ idOne,   idTwo, value FROM

  (   select /*+MAPJOIN(smallTableOne)*/ idOne, idTwo, value FROM

      bigTable JOIN smallTableOne

      on bigTable.idOne = smallTableOne.idOne) firstjoin

JOIN smallTableTwo

ON firstjoin.idTwo = smallTableTwo.idTwo;

 

 

bucketmapjoin

对两个表在joinkey上都做hash bucket, 之后可以用bucket mapjoin, 因为bucket划分数据后,相同hash值的key都放在了同一个bucket, join, 小表将会被复制到所有节点, map加载小表的bucket到内存hashtable, 与大表对应的bucket文件进行join.

开启桶表的mapjoin

set hive.optimize.bucketmapjoin=true;

操作例子:

 创建表, 并以id字段划分桶, 桶个数为20

(也就是在插入数据时会生成20个数据文件对应20个桶)

create table test_mapjoin(id int)   clustered by (id) into 20 buckets;

插入300万行数据

set hive.enforce.bucketing=true;

insert overwrite table test_mapjoin   select * from ids limit 3000000;

使用 bucket mapjoin

set hive.optimize.bucketmapjoin=true;

select /*+mapjoin(a)*/ count(*) from   test_mapjoin a join test_mapjoin b on a.id = b.id;

 

 

sort-merge-bucket (smb) join

 

a为:

create table a(key int, othera string)

clustered by(key) into 4 buckets

row format delimited

fields terminated by '\001'

collection items terminated by '\002'

map keys terminated by '\003'

stored as sequencefile;

b为:

create table b(key int, otherb string)

clustered by(key) into 32 buckets

row format delimited

fields terminated by '\001'

collection items terminated by '\002'

map keys terminated by '\003'

stored as sequencefile;

 

因为join列都是分桶列,则可开启bucketmapjoin

set hive.optimize.bucketmapjoin = true;

select /*+ mapjoin(b) */ a.key, a.value from a join b on a.key = b.key;

这样,join的过程是,表abucket 1只会与表b中的bucket 1进行join,而不再考虑表b中的其他bucket 2~32

 

如果上述表具有相同的bucket,如都是32个,而且还是排序的,亦即,

clustered by(key) sorted by(key) into 32 buckets

设置如下参数

set hive.input.format=org.apache.hadoop.hive.ql.io.bucketizedhiveinputformat;

set hive.optimize.bucketmapjoin = true;

set hive.optimize.bucketmapjoin.sortedmerge = true;

则上述join语句会执行一个sort-merge-bucket (smb) join

 

 

数据倾斜

null值过多导致数据倾斜

解决方法 1 userid 为空的不参与关联,然后与为空的结果合并

select * from log l

 left join user u

   on l.userid is not null

   and l.userid = u.userid

union all

select * from log where userid is null;

 

解决方法 2 :赋与空值分新的 key

select * from log l

left join user u on

  case when l.userid is null

    &nbs


了解更多详情请联系 今日值班讲师 或者直接加入千人QQ群进行咨询:210992946

分享到: