hive视图和mysql视图有什么区别_Hive中视图机制的初步使用及分析

作者: 大圆那些事 | 文章可以转载,请以超链接形式标明文章原始出处和作者信息

本文是对Hive中逻辑视图的介绍,通过一个简单的视图例子,说明其使用方法及执行过程。

Hive 0.6版本及以上支持视图(View,详见Hive的RELEASE_NOTES.txt),Hive View具有以下特点:

1)View是逻辑视图,暂不支持物化视图(后续将在1.0.3版本以后支持);

2)View是只读的,不支持LOAD/INSERT/ALTER。需要改变View定义,可以是用Alter View;

3)View内可能包含ORDER BY/LIMIT语句,假如一个针对View的查询也包含这些语句, 则View中的语句优先级高;

4)支持迭代View。

CDH4中自带的Hive版本为0.10.0,支持的View是逻辑视图,因此本质上来说View只是为了使用上的方便,从执行效率上来说没有区别,甚至可能因为要多一次对MetaStore元数据的操作效率略有下降(这里只是一种理论上的推测,实际可能看不出太大区别)。

下面是简单的验证过程(感兴趣的可以看下,以下过程如有问题,可以一起交流):

1)创建一个测试表:

hive> create table test (id int, name string);

OK

Time taken:0.19seconds

hive>desc test;

OKid intnamestringTime taken:0.16 seconds

2)创建一个View之前,使用explain命令查看创建View的命令是如何被Hive解释执行的:

hive> explain create view test_view (id, name_length) as select id, length(name) from test;

OK

ABSTRACT SYNTAX TREE:

(TOK_CREATEVIEW (TOK_TABNAME test_view) (TOK_TABCOLNAME (TOK_TABCOLid TOK_NULL) (TOK_TABCOL name_length TOK_NULL)) (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME test))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL id)) (TOK_SELEXPR (TOK_FUNCTION length (TOK_TABLE_OR_COL name)))))))

STAGE DEPENDENCIES:

Stage-0is a root stage

STAGE PLANS:

Stage: Stage-0Create View Operator:

Create Viewif not exists: falseor replace:falsecolumns:id int, name_length intexpanded text: SELECT `id` AS `id`, `_c1` AS `name_length` FROM (select `test`.`id`, length(`test`.`name`) from `default`.`test`) `test_view`

name: test_view

original text:select id, length(name) from test

Time taken:0.088 seconds

可见,创建View的过程解释后并没有实际执行Map Reduce的Stage,只包含一个Create View Operator的Stage,这个阶段只是对MySQL MetaStore进行元数据操作,记录View的相关元数据而已。

3)接下来,实际创建这个View:

hive> create view test_view (id, name_length) as select id, length(name) from test;

OK

Time taken:0.1 seconds

4)执行这个View之前,先explain查看实际被翻译后的执行过程:

hive> explain selectname_length from test_view;

OK

ABSTRACT SYNTAX TREE:

(TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME test_view))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_TABLE_OR_COL name_length)))))

STAGE DEPENDENCIES:

Stage-1is a root stage

Stage-0is a root stage

STAGE PLANS:

Stage: Stage-1Map Reduce

Alias->Map Operator Tree:

test_view:test_view:test

TableScan

alias: test

Select Operator

expressions:expr: length(name)

type:intoutputColumnNames: _col1

Select Operator

expressions:expr: _col1

type:intoutputColumnNames: _col1

Select Operator

expressions:expr: _col1

type:intoutputColumnNames: _col0

File Output Operator

compressed:falseGlobalTableId:0table:

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

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

Stage: Stage-0Fetch Operator

limit:-1Time taken:0.107 seconds

可以看出,对View进行的查找过程,实际还是对原始test表进行的查询操作(分为Stage-0和Stage-1两个阶段)。

5)最后,实际对这个View执行一次查询,显示Stage-1阶段对原始表test进行了MapReduce过程:

hive> selectname_length from test_view;

Total MapReduce jobs= 1Launching Job1 out of 1Number of reduce tasks is set to0 since there's no reduce operator

Starting Job = job_201303092253_0057, Tracking URL = http://jobtracker.host:50030/jobdetails.jsp?jobid=job_201303092253_0057

Kill Command = /usr/lib/hadoop/bin/hadoop job -killjob_201303092253_0057

Hadoop job informationfor Stage-1: number of mappers: 1; number of reducers: 0

2013-03-13 22:43:39,044 Stage-1 map = 0%, reduce = 0%

2013-03-13 22:43:42,074 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.73sec2013-03-13 22:43:43,086 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.73sec2013-03-13 22:43:44,098 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.73sec2013-03-13 22:43:45,113 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 0.73sec

MapReduce Total cumulative CPUtime: 730msec

Ended Job=job_201303092253_0057

MapReduce Jobs Launched:

Job0: Map: 1 Cumulative CPU: 0.73 sec HDFS Read: 250 HDFS Write: 0SUCCESS

Total MapReduce CPU Time Spent:730msec

OK

Time taken:15.793 seconds

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值