RE: Bucket pruning
Hi, Can someone clarify whether hive approach to split follows what is essentially a UNIX/Linux command? For example the following command will split a a_larg_file into partitions (sub files) of 250,000 bytes each called hql01.dat, hql02.dat and so forth tar cz ./a_large_file |split -d -b 25 hqlfile HTH Mich Talebzadeh http://talebzadehmich.wordpress.com Publications due shortly: 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: matshyeq [mailto:matsh...@gmail.com] Sent: 23 March 2015 10:41 To: user Cc: Daniel Haviv Subject: Re: Bucket pruning To me there's practically very little difference between partitioning and bucketing (partitioning defines split criteria explicitly whereas bucketing somewhat implicitly) . Hive however recognises the latter as a separate feature and handles the two in quite different way. There's already a feature request proposition to unify and bring the optimisations across (so it would address the "bucket pruning" issue I believe you're having): https://issues.apache.org/jira/browse/HIVE-9523 Probably best if you vote for it so it gets some traction… Regards ~Maciek On Fri, Mar 13, 2015 at 12:22 PM, cobby wrote: hi, thanks for the detailed response. i will experiment with your suggested orc bloom filter solution. it seems to me the obvious, most straight forward solution is to add support for hash partitioning. so i can do something like: create table T() partitioned by (x into num_partitions,..). upon insert hash(x) determines which partition to put the record in. upon select, the query processor can now hash on x and scan only that partition (this optimization will probably work only on = and other discrete filtering but thats true for partitioning in general). it seems all of this can be done early in the query plan phase and have no effect on underling infra. regards,cobby. > On 12 במרץ 2015, at 23:05, Gopal Vijayaraghavan wrote: > > Hi, > > No and it¹s a shame because we¹re stuck on some compatibility details with > this. > > The primary issue is the fact that the InputFormat is very generic and > offers no way to communicate StorageDescriptor or bucketing. > > The split generation for something SequenceFileInputFormat lives inside > MapReduce, where it has no idea about bucketing. > > So InputFormat.getSplits(conf) returns something relatively arbitrary, > which contains a mixture of files when CombineInputFormat is turned on. > > I have implemented this twice so far for ORC (for custom Tez jobs, with > huge wins) by using an MRv2 PathFilter over the regular OrcNewInputFormat > implementation, by turning off combine input and using Tez grouping > instead. > > But that has proved to be very fragile for a trunk feature, since with > schema evolution of partitioned tables older partitions may be bucketed > with a different count from a newer partition - so the StorageDescriptor > for each partition has to be fetched across before we can generate a valid > PathFilter. > > The SARGs are probably a better way to do this eventually as they can > implement IN_BUCKET(1,2) to indicate 1 of 2 instead of the ³0_1² > PathFilter which is fragile. > > > Right now, the most fool-proof solution we¹ve hit upon was to apply the > ORC bloom filter to the bucket columns, which is far safer as it does not > care about the DDL - but does a membership check on the actual metadata & > prunes deeper at the stripe-level if it is sorted as well. > > That is somewhat neat since this doesn¹t need any new options for querying > - it automatically(*) kicks in for your query pattern. > > Cheers, > Gopal > (*) - conditions apply - there¹s a threshold for file-size for these > filters to be evaluated during planning (to prevent HS2 from burning CPU). > > > From: Daniel Haviv > Reply-To: "user@hive.apache.org" > Date: Thursday, March 12, 2015 at 2:36 AM > To: "user@hive.apache.org" > Subject: Bucket pruning > > > Hi, > We created a bucketed table and when we select in the following way: > select * > from testtble > where bucket_col ='X'; > > We observe that there all of the table is being read and not just the > specific bucket. > > Does Hive support such a feature ? > > > Thanks, > Daniel > >
Re: Bucket pruning
To me there's practically very little difference between partitioning and bucketing (partitioning defines split criteria explicitly whereas bucketing somewhat implicitly) . Hive however recognises the latter as a separate feature and handles the two in quite different way. There's already a feature request proposition to unify and bring the optimisations across (so it would address the "bucket pruning" issue I believe you're having): https://issues.apache.org/jira/browse/HIVE-9523 Probably best if you vote for it so it gets some traction… Regards ~Maciek On Fri, Mar 13, 2015 at 12:22 PM, cobby wrote: > hi, thanks for the detailed response. > i will experiment with your suggested orc bloom filter solution. > > it seems to me the obvious, most straight forward solution is to add > support for hash partitioning. so i can do something like: > > create table T() > partitioned by (x into num_partitions,..). > > upon insert hash(x) determines which partition to put the record in. upon > select, the query processor can now hash on x and scan only that partition > (this optimization will probably work only on = and other discrete > filtering but thats true for partitioning in general). > it seems all of this can be done early in the query plan phase and have no > effect on underling infra. > > regards,cobby. > > > > > On 12 במרץ 2015, at 23:05, Gopal Vijayaraghavan > wrote: > > > > Hi, > > > > No and it¹s a shame because we¹re stuck on some compatibility details > with > > this. > > > > The primary issue is the fact that the InputFormat is very generic and > > offers no way to communicate StorageDescriptor or bucketing. > > > > The split generation for something SequenceFileInputFormat lives inside > > MapReduce, where it has no idea about bucketing. > > > > So InputFormat.getSplits(conf) returns something relatively arbitrary, > > which contains a mixture of files when CombineInputFormat is turned on. > > > > I have implemented this twice so far for ORC (for custom Tez jobs, with > > huge wins) by using an MRv2 PathFilter over the regular OrcNewInputFormat > > implementation, by turning off combine input and using Tez grouping > > instead. > > > > But that has proved to be very fragile for a trunk feature, since with > > schema evolution of partitioned tables older partitions may be bucketed > > with a different count from a newer partition - so the StorageDescriptor > > for each partition has to be fetched across before we can generate a > valid > > PathFilter. > > > > The SARGs are probably a better way to do this eventually as they can > > implement IN_BUCKET(1,2) to indicate 1 of 2 instead of the ³0_1² > > PathFilter which is fragile. > > > > > > Right now, the most fool-proof solution we¹ve hit upon was to apply the > > ORC bloom filter to the bucket columns, which is far safer as it does not > > care about the DDL - but does a membership check on the actual metadata & > > prunes deeper at the stripe-level if it is sorted as well. > > > > That is somewhat neat since this doesn¹t need any new options for > querying > > - it automatically(*) kicks in for your query pattern. > > > > Cheers, > > Gopal > > (*) - conditions apply - there¹s a threshold for file-size for these > > filters to be evaluated during planning (to prevent HS2 from burning > CPU). > > > > > > From: Daniel Haviv > > Reply-To: "user@hive.apache.org" > > Date: Thursday, March 12, 2015 at 2:36 AM > > To: "user@hive.apache.org" > > Subject: Bucket pruning > > > > > > Hi, > > We created a bucketed table and when we select in the following way: > > select * > > from testtble > > where bucket_col ='X'; > > > > We observe that there all of the table is being read and not just the > > specific bucket. > > > > Does Hive support such a feature ? > > > > > > Thanks, > > Daniel > > > > >
Re: Bucket pruning
hi, thanks for the detailed response. i will experiment with your suggested orc bloom filter solution. it seems to me the obvious, most straight forward solution is to add support for hash partitioning. so i can do something like: create table T() partitioned by (x into num_partitions,..). upon insert hash(x) determines which partition to put the record in. upon select, the query processor can now hash on x and scan only that partition (this optimization will probably work only on = and other discrete filtering but thats true for partitioning in general). it seems all of this can be done early in the query plan phase and have no effect on underling infra. regards,cobby. > On 12 במרץ 2015, at 23:05, Gopal Vijayaraghavan wrote: > > Hi, > > No and it¹s a shame because we¹re stuck on some compatibility details with > this. > > The primary issue is the fact that the InputFormat is very generic and > offers no way to communicate StorageDescriptor or bucketing. > > The split generation for something SequenceFileInputFormat lives inside > MapReduce, where it has no idea about bucketing. > > So InputFormat.getSplits(conf) returns something relatively arbitrary, > which contains a mixture of files when CombineInputFormat is turned on. > > I have implemented this twice so far for ORC (for custom Tez jobs, with > huge wins) by using an MRv2 PathFilter over the regular OrcNewInputFormat > implementation, by turning off combine input and using Tez grouping > instead. > > But that has proved to be very fragile for a trunk feature, since with > schema evolution of partitioned tables older partitions may be bucketed > with a different count from a newer partition - so the StorageDescriptor > for each partition has to be fetched across before we can generate a valid > PathFilter. > > The SARGs are probably a better way to do this eventually as they can > implement IN_BUCKET(1,2) to indicate 1 of 2 instead of the ³0_1² > PathFilter which is fragile. > > > Right now, the most fool-proof solution we¹ve hit upon was to apply the > ORC bloom filter to the bucket columns, which is far safer as it does not > care about the DDL - but does a membership check on the actual metadata & > prunes deeper at the stripe-level if it is sorted as well. > > That is somewhat neat since this doesn¹t need any new options for querying > - it automatically(*) kicks in for your query pattern. > > Cheers, > Gopal > (*) - conditions apply - there¹s a threshold for file-size for these > filters to be evaluated during planning (to prevent HS2 from burning CPU). > > > From: Daniel Haviv > Reply-To: "user@hive.apache.org" > Date: Thursday, March 12, 2015 at 2:36 AM > To: "user@hive.apache.org" > Subject: Bucket pruning > > > Hi, > We created a bucketed table and when we select in the following way: > select * > from testtble > where bucket_col ='X'; > > We observe that there all of the table is being read and not just the > specific bucket. > > Does Hive support such a feature ? > > > Thanks, > Daniel > >
Re: Bucket pruning
Hi, No and it¹s a shame because we¹re stuck on some compatibility details with this. The primary issue is the fact that the InputFormat is very generic and offers no way to communicate StorageDescriptor or bucketing. The split generation for something SequenceFileInputFormat lives inside MapReduce, where it has no idea about bucketing. So InputFormat.getSplits(conf) returns something relatively arbitrary, which contains a mixture of files when CombineInputFormat is turned on. I have implemented this twice so far for ORC (for custom Tez jobs, with huge wins) by using an MRv2 PathFilter over the regular OrcNewInputFormat implementation, by turning off combine input and using Tez grouping instead. But that has proved to be very fragile for a trunk feature, since with schema evolution of partitioned tables older partitions may be bucketed with a different count from a newer partition - so the StorageDescriptor for each partition has to be fetched across before we can generate a valid PathFilter. The SARGs are probably a better way to do this eventually as they can implement IN_BUCKET(1,2) to indicate 1 of 2 instead of the ³0_1² PathFilter which is fragile. Right now, the most fool-proof solution we¹ve hit upon was to apply the ORC bloom filter to the bucket columns, which is far safer as it does not care about the DDL - but does a membership check on the actual metadata & prunes deeper at the stripe-level if it is sorted as well. That is somewhat neat since this doesn¹t need any new options for querying - it automatically(*) kicks in for your query pattern. Cheers, Gopal (*) - conditions apply - there¹s a threshold for file-size for these filters to be evaluated during planning (to prevent HS2 from burning CPU). From: Daniel Haviv Reply-To: "user@hive.apache.org" Date: Thursday, March 12, 2015 at 2:36 AM To: "user@hive.apache.org" Subject: Bucket pruning Hi, We created a bucketed table and when we select in the following way: select * from testtble where bucket_col ='X'; We observe that there all of the table is being read and not just the specific bucket. Does Hive support such a feature ? Thanks, Daniel
Bucket pruning
Hi, We created a bucketed table and when we select in the following way: select * from testtble where bucket_col ='X'; We observe that there all of the table is being read and not just the specific bucket. Does Hive support such a feature ? Thanks, Daniel
