Hive--临时表的三种方式

一、临时数据方案

1、with as

1.1 使用demo

with

t1 

as

(

select

imei

,src_pkg

,src_type

,app_version_name

from

bi_quickgame.dw_qgcrpk_boot_di

where

day

= 

'${etl_date}'

group

by

imei, src_pkg, src_type, app_version_name

)

1.2 with as 执行分析

执行sql ：

EXPLAIN

with

t 

as

(

select

regexp_replace(reflect(

"java.util.UUID"

, 

"randomUUID"

), 

"-"

, 

""

) 

AS

id 

--生成一个随机id

,

'zengkun'

as

name

)

select

* 

from

t

union

all

select

* 

from

t

;

拓展：hive reflect 介绍：hive新特性reflect函数介绍 - hustzzl - 博客园

执行结果：

产生2个id

总结：结果中可以看到，产生了两个不一样的id，说明reflect函数被执行了两次，即with as中的子查询被执行了两次。

查看执行路径：

Explain

STAGE DEPENDENCIES:

Stage-1 is a root stage

Stage-0 depends on stages: Stage-1

STAGE PLANS:

Stage: Stage-1

Map Reduce

Map Operator Tree:

TableScan

alias: _dummy_table

Row Limit Per Split: 1

Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: COMPLETE

Select Operator

expressions: regexp_replace(reflect('java.util.UUID','randomUUID'), '-', '') (type: string), 'zengkun' (type: string)

outputColumnNames: _col0, _col1

Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: COMPLETE

Union

Statistics: Num rows: 2 Data size: 2 Basic stats: COMPLETE Column stats: COMPLETE

File Output Operator

compressed: false

Statistics: Num rows: 2 Data size: 2 Basic stats: COMPLETE Column stats: COMPLETE

table:

input format: org.apache.hadoop.mapred.TextInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

TableScan

alias: _dummy_table

Row Limit Per Split: 1

Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: COMPLETE

Select Operator

expressions: regexp_replace(reflect('java.util.UUID','randomUUID'), '-', '') (type: string), 'zengkun' (type: string)

outputColumnNames: _col0, _col1

Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: COMPLETE

Union

Statistics: Num rows: 2 Data size: 2 Basic stats: COMPLETE Column stats: COMPLETE

File Output Operator

compressed: false

Statistics: Num rows: 2 Data size: 2 Basic stats: COMPLETE Column stats: COMPLETE

table:

input format: org.apache.hadoop.mapred.TextInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

Stage: Stage-0

Fetch Operator

limit: -1

Processor Tree:

ListSink

总结: TableScan 了两次，并且是相同的两次

1.3 with as 执行总结

with as 不会减少表的扫描，一个任务中重复使用会执行多次（可以简单理解with AS 生成了个视图)

2、CREATE TEMPORARY TABLE 创建临时表

2.1 使用demo

-- 获取所有除曝光的所有埋点

create

temporary

table

bi_gamecenter_dev.tmp_da_gc_origin_event_di_1 

as

select

t.*

from

bi_gamecenter_dev.dw_gc_oprt_log_h t

where

day

= 

'${etl_date}'

and

not

(  

--delete the exposure data

action

= 

'/clientRequest/userOperationLog'

and

(

fields[

'exposure_stat'

] 

is

not

null

or

fields[

'appexpo'

] 

is

not

null

or

fields[

'search_exposure'

] 

is

not

null

or

fields[

'exposure'

] 

is

not

null

)

);

2.2 TEMPORARY TABLE 介绍

Hive从0.14.0开始提供创建临时表的功能，表只对当前session有效，session退出后，表自动删除。

创建的临时表仅仅在当前会话是可见的，数据将会被存储在用户的暂存目录中，并在会话结束时被删除。如果创建临时表的名字与当前数据库下的一个非临时表相同，则在这个会话中使用这个表名字时将会使用的临时表，而不是非临时表，用户在这个会话内将不能使用原表，除非删除或者重命名临时表。

临时表有如下限制：

1. 不支持分区字段
2. 不支持创建索引
3. 在Hive1.1.0之后临时表可以存储到memory，ssd或者default中，可以通过配置 hive.exec.temporary.table.storage来实现

3、创建真实表作为临时表

直接创建真是表，并且落地hdfs，每次使用过后删除

使用demo：

-- 前天排行榜访问数据

drop

table

IF EXISTS bi_gamecenter_dev.tmp_gc_board_exposure_1_${etl_datekey};

create

table

bi_gamecenter_dev.tmp_gc_board_exposure_1_${etl_datekey} 

as

select

--曝光

origin_event 

as

origin,

app_version_code,

imei

from

bi_gamecenter_dev.dw_gc_exposure_log_d

where

day

= date_sub(

'${etl_date}'

,1) 

and

origin_event 

in

(

'530'

,

'531'

,

'532'

,

'533'

)

-- 过风控

and

risk_flag = 0

;

drop

table

IF EXISTS bi_gamecenter_dev.tmp_gc_board_exposure_1_${etl_datekey};

二、临时表方式对比

临时表大概三种临时表，进行横向对比

临时表方法

优点

缺点

使用注意
with as
1.使用方便，无需关注结果数据内容。

2.写法简易，不需要建表，直接使用即可

3.可读性强

1.如果多次使用，会多次scan表，降低效率

2.数据自查的时候，不容易定位问题
如果多次使用，会多次scan表！create temporary table
1.使用较为方便，单个进程中存在，自动删除。

2.具备复用性，在相同任务中，重复使用结果。

1.任务结束既删除，不方便过程数据校验。

2.无法创建分区表，只能简单创建临时表。
无create table
1.直接创建真实表，并且落地hdfs，方便过程数据校验。

2.可以创建分区临时表等操作，丰富临时表。

1.使用不方便，每次需要先drop再create，完成后再drop。
记得给临时表带参数，方便任务回滚！
总结：

目前使用的三种方式各有优劣，需要在对应场景使用。