hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From cobby cohen <ququr...@yahoo.com>
Subject Re: view over partitioned table
Date Tue, 17 Mar 2015 15:51:29 GMT
we did empirical test on a partitioned table with ~50 million rows, and filtering from a view
(which includes an explode() on an array column) vs filtering the underlining table on a specific
partition yielding similar results.i also noticed that an "explain dependency" statement on
both the view and the table shows the analizer does indeed pick up the right partition on
both cases (view and table).
      From: Mich Talebzadeh <mich@peridale.co.uk>
 To: user@hive.apache.org; 'cobby cohen' <ququrico@yahoo.com> 
 Sent: Monday, March 16, 2015 9:36 PM
 Subject: RE: view over partitioned table
   
#yiv3664821214 #yiv3664821214 -- _filtered #yiv3664821214 {font-family:Helvetica;panose-1:2
11 6 4 2 2 2 2 2 4;} _filtered #yiv3664821214 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv3664821214
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv3664821214 {font-family:Tahoma;panose-1:2
11 6 4 3 5 4 4 2 4;}#yiv3664821214 #yiv3664821214 p.yiv3664821214MsoNormal, #yiv3664821214
li.yiv3664821214MsoNormal, #yiv3664821214 div.yiv3664821214MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv3664821214
a:link, #yiv3664821214 span.yiv3664821214MsoHyperlink {color:blue;text-decoration:underline;}#yiv3664821214
a:visited, #yiv3664821214 span.yiv3664821214MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv3664821214
p.yiv3664821214MsoAcetate, #yiv3664821214 li.yiv3664821214MsoAcetate, #yiv3664821214 div.yiv3664821214MsoAcetate
{margin:0cm;margin-bottom:.0001pt;font-size:8.0pt;}#yiv3664821214 p.yiv3664821214msonormal,
#yiv3664821214 li.yiv3664821214msonormal, #yiv3664821214 div.yiv3664821214msonormal {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv3664821214
p.yiv3664821214msochpdefault, #yiv3664821214 li.yiv3664821214msochpdefault, #yiv3664821214
div.yiv3664821214msochpdefault {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv3664821214
span.yiv3664821214msohyperlink {}#yiv3664821214 span.yiv3664821214msohyperlinkfollowed {}#yiv3664821214
span.yiv3664821214emailstyle17 {}#yiv3664821214 p.yiv3664821214msonormal1, #yiv3664821214
li.yiv3664821214msonormal1, #yiv3664821214 div.yiv3664821214msonormal1 {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv3664821214
span.yiv3664821214msohyperlink1 {color:blue;text-decoration:underline;}#yiv3664821214 span.yiv3664821214msohyperlinkfollowed1
{color:purple;text-decoration:underline;}#yiv3664821214 span.yiv3664821214emailstyle171 {color:windowtext;}#yiv3664821214
p.yiv3664821214msochpdefault1, #yiv3664821214 li.yiv3664821214msochpdefault1, #yiv3664821214
div.yiv3664821214msochpdefault1 {margin-right:0cm;margin-left:0cm;font-size:10.0pt;}#yiv3664821214
span.yiv3664821214BalloonTextChar {}#yiv3664821214 span.yiv3664821214EmailStyle29 {color:windowtext;}#yiv3664821214
.yiv3664821214MsoChpDefault {font-size:10.0pt;} _filtered #yiv3664821214 {margin:72.0pt 72.0pt
72.0pt 72.0pt;}#yiv3664821214 div.yiv3664821214WordSection1 {}#yiv3664821214 Hi Cobby,  In
your statement and I quote:  “Empirical tests, by the show, suggest that indeed, partition
optimization does take place. “  Do you have an example of this please?   Thanks,  Mich
 Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and Coherence Cache
 NOTE: The information in this email is proprietary and confidential. This message is for
the designated recipient only, if you are not the intended recipient, you should destroy it
immediately. Any information in this message shall not be understood as given or endorsed
by Peridale Ltd, its subsidiaries or their employees, unless expressly so stated. It is the
responsibility of the recipient to ensure that this email is virus free, therefore neither
Peridale Ltd, its subsidiaries nor their employees accept any responsibility.  

From: cobby cohen [mailto:ququrico@yahoo.com] 
Sent: 16 March 2015 16:14
To: user@hive.apache.org
Subject: Re: view over partitioned table  hi,i am using CDH 5.2 (hive 13).as for partitioned
views, to my understanding they are more about abstracting a non partitioned back end. My
questing is related to how and if the query optimizer can detect possible uses of partitioned
column, even if it might be "masked" by a view (or multiple view).Empirical tests, by the
show, suggest that indeed, partition optimization does take place.  regards, cobby.  From:
"Moore, Douglas" <Douglas.Moore@thinkbiganalytics.com>
To: "user@hive.apache.org" <user@hive.apache.org>; 'cobby cohen' <ququrico@yahoo.com>

Sent: Monday, March 16, 2015 4:19 PM
Subject: Re: view over partitioned table  Mich,  What version of Hive are you running?Have
you seen this?https://cwiki.apache.org/confluence/display/Hive/PartitionedViews  - Douglas
 From: Mich Talebzadeh <mich@peridale.co.uk>
Reply-To: <user@hive.apache.org>
Date: Sun, 15 Mar 2015 19:01:57 +0000
To: <user@hive.apache.org>, 'cobby cohen' <ququrico@yahoo.com>
Subject: RE: view over partitioned table  OK, This is the way I read it. Crearte table t1
.. partitioned by date will use horizontal partitioning as per common with any RDBMS say Oracle. The
view I will create it as follows: hive> create view v1 as select * from t1;OKTime taken:
0.073 seconds hive> analyze table t1 partition (date) compute statistics;Query ID = hduser_20150315184646_af3890b2-4079-4f81-b38b-da9af67b2751Total
jobs = 1Launching Job 1 out of 1-----Stage-Stage-0: Map: 1   Cumulative CPU: 0.6 sec  
HDFS Read: 299 HDFS Write: 0 SUCCESSTotal MapReduce CPU Time Spent: 600 msecOK hive> insert
into table t1 partition (date)  values (1,'2015-04-04');hive> insert into table t1 partition
(date)  values (1,'2015-05-04'); Help to update stats hive> analyze table t1 partition
(date) compute statistics; select from the view after exiting hive and back  hive> explain
select id from v1 where date = '2015-04-04';OKSTAGE DEPENDENCIES:  Stage-0 is a root stage STAGE
PLANS:  Stage: Stage-0    Fetch Operator      limit: -1      Processor Tree:       
TableScan          alias: t1          Statistics: Num rows: 1 Data size:
1 Basic stats: COMPLETE Column stats: NONE          Select Operator           
expressions: id (type: string)            outputColumnNames: _col0           
Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: NONE           
ListSink Time taken: 0.627 seconds, Fetched: 17 row(s)  Now select the same from base table hive>
explain select id from t1 where date = '2015-04-04';OKSTAGE DEPENDENCIES:  Stage-0 is a root
stage STAGE PLANS:  Stage: Stage-0    Fetch Operator      limit: -1      Processor
Tree:        TableScan          alias: t1          Statistics: Num
rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: NONE          Select Operator           
expressions: id (type: string)            outputColumnNames: _col0           
Statistics: Num rows: 1 Data size: 1 Basic stats: COMPLETE Column stats: NONE           
ListSink Time taken: 0.597 seconds, Fetched: 17 row(s) The plan looks the same but it is
slower on the view HTH Mich  NOTE: The information in this email is proprietary and confidential.
This message is for the designated recipient only, if you are not the intended recipient,
you should destroy it immediately. Any information in this message shall not be understood
as given or endorsed by Peridale Ltd, its subsidiaries or their employees, unless expressly
so stated. It is the responsibility of the recipient to ensure that this email is virus free,
therefore neither Peridale Ltd, its subsidiaries nor their employees accept any responsibility. From:
cobby cohen [mailto:ququrico@yahoo.com] 
Sent: 15 March 2015 17:52
To: user@hive.apache.org
Subject: view over partitioned table in the following case: create table T1(id string)partitioned
by (date date); create view V1(id, date)select id, date from T1 if i do the followingselect
id from V1 where date = '2015-04-04'will the query optimize on T1's partitioning? and what
if V1 is a bit more complex, further depending on other views (all of which expose the partitioned
column as-is)? thx!  

  
Mime
View raw message