Hive--临时表的三种方式-Toy模板网

这篇具有很好参考价值的文章主要介绍了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退出后，表自动删除。

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

临时表有如下限制：

不支持分区字段
不支持创建索引
在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。	记得给临时表带参数，方便任务回滚！

临时表方法

优点

缺点

使用注意

with as

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

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

3.可读性强

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

2.数据自查的时候，不容易定位问题

如果多次使用，会多次scan表！

create temporary table