I did some tests on Hive running on MR to get rid of Spark effects.
In an ORC table that has been partitioned, partition elimination with
predicate push down works and the query is narrowed to the partition
itself. I can see that from the number of rows within that partition.
For example below sales table is ORC partitioned by year and month. For
year = 1999 and month = 8 , there are 124,284 rows
explain extended select count(1) from sales where year = 1999 and month =8;
Map 1
Map Operator Tree:
TableScan
alias: sales
Statistics: *Num rows: 124284* Data size: 1184497 Basic
stats: COMPLETE Column stats: NONE
GatherStats: false
Select Operator
Statistics: Num rows: 124284 Data size: 1184497 Basic
stats: COMPLETE Column stats: NONE
Group By Operator
That is the only time I have seen through explain plan that partition
elimination is working.
HTH
Dr Mich Talebzadeh
LinkedIn *
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
http://talebzadehmich.wordpress.com
On 16 March 2016 at 20:57, Wietsma, Tristan A. <
tristan.wiet...@capitalone.com> wrote:
> Regarding bloom filters,
> https://issues.apache.org/jira/plugins/servlet/mobile#issue/SPARK-12417
>
>
>
> Sent with Good (www.good.com)
> ------------------------------
> *From:* Joseph <wxy81...@sina.com>
> *Sent:* Wednesday, March 16, 2016 9:46:25 AM
> *To:* user
> *Cc:* user; user
> *Subject:* Re: Re: The build-in indexes in ORC file does not work.
>
>
> terminal_type =0, 260,000,000 rows, almost cover half of the whole data.
>
> terminal_type =25066, just 3800 rows.
>
>
>
> orc
> tblproperties("orc.compress"="SNAPPY","orc.compress.size"="262141","orc.stripe.size"="268435456","orc.row.index.stride"="
> *100000*","orc.create.index"="true","orc.bloom.filter.columns"="");
>
>
> The table "gprs" has sorted by terminal_type. Before sort, I have
> another table named "gprs_orc", I use sparkSQL to sort the data as follows:
>
> (before do this, I set hive.enforce.sorting=true)
>
> sql> INSERT INTO TABLE gprs SELECT * FROM gprs_orc sort by terminal_type ;
>
> Because the table gprs_orc has 800 files, so generate 800 Tasks， and
> create 800 files also in table gprs. But I am not sure whether each file be
> sorted separately or not.
>
>
> I have tried bloom filter ,but it makes no improvement。I know about tez,
> but never use, I will try it later.
>
>
> The following is my test in hive 1.2.1:
>
> 1. enable *hive.optimize.index.filter* and *hive.optimize.ppd:*
>
> select count(*) from gprs where terminal_type=25080; will not scan
> data Time taken: 353.345 seconds
>
> select count(*) from gprs where terminal_type=25066; just scan a
> few row groups Time taken: 354.860 seconds
>
> select count(*) from gprs where terminal_type=0; scan half
> of the data Time taken: 378.312 seconds
>
>
> 2. *disable *hive.optimize.index.filter and hive.optimize.ppd:
>
> select count(*) from gprs where terminal_type=25080; scan all the
> data Time taken: 389.700 seconds
>
> select count(*) from gprs where terminal_type=25066; scan all the
> data Time taken: 386.600 seconds
>
> select count(*) from gprs where terminal_type=0; scan all
> the data Time taken: 395.968 seconds
>
>
> The following is my environment:
> 3 nodes, 12 cpu cores per node, 48G memory free per node, 4
> disks per node, 3 replications per block , hadoop 2.7.2, hive 1.2.1
>
>
> ------------------------------
> Joseph
>
>
> *From:* Jörn Franke <jornfra...@gmail.com>
> *Date:* 2016-03-16 20:27
> *To:* Joseph <wxy81...@sina.com>
> *CC:* user <user@spark.apache.org>; user <u...@hive.apache.org>
> *Subject:* Re: The build-in indexes in ORC file does not work.
> Not sure it should work. How many rows are affected? The data is sorted?
> Have you tried with Tez? Tez has some summary statistics that tells you if
> you use push down. Maybe you need to use HiveContext.
> Perhaps a bloom filter could make sense for you as well.
>
> On 16 Mar 2016, at 12:45, Joseph <wxy81...@sina.com> wrote:
>
> Hi,
>
> I have only one table named "gprs", it has 560,000,000 rows, and 57
> columns. The block size is 256M, total ORC file number is 800, each of
> them is about 51M.
>
> my query statement is :
> select count(*) from gprs where terminal_type = 25080;
> select * from gprs where terminal_type = 25080;
>
> In the gprs table, the "terminal_type" column's value is in [0, 25066]
>
> ------------------------------
> Joseph
>
>
> *From:* Jörn Franke <jornfra...@gmail.com>
> *Date:* 2016-03-16 19:26
> *To:* Joseph <wxy81...@sina.com>
> *CC:* user <user@spark.apache.org>; user <u...@hive.apache.org>
> *Subject:* Re: The build-in indexes in ORC file does not work.
> How much data are you querying? What is the query? How selective it is
> supposed to be? What is the block size?
>
> On 16 Mar 2016, at 11:23, Joseph <wxy81...@sina.com> wrote:
>
> Hi all,
>
> I have known that ORC provides three level of indexes within each file,
> *file level, stripe level, and row level*.
>
> The file and stripe level statistics are in the file footer so that they are
> easy to access to determine if the rest of the file needs to be read at all.
>
> Row level indexes include both column statistics for each row group and
> position for seeking to the start of the row group.
>
> The following is my understanding:
>
> 1. The file and stripe level indexes are forcibly generated, we can not
> control them.
>
> 2. The row level indexes can be configured by "orc.create.index"(whether to
> create row indexes) and "orc.row.index.stride"(number of rows between index
> entries).
>
> 3. Each Index has statistics of min, max for each column, so sort data by the
> filter column will bring better performance.
>
> 4. To use any one of the three level of indexes,we should enable predicate
> push-down by setting
> *spark.sql.orc.filterPushdown=true* (in sparkSQL) or
> *hive.optimize.ppd=true* (in hive).
>
> *But I found the ** build-in **indexes in ORC files
> did not work both in spark 1.5.2 and hive 1.2.1:*
> First, when the query statement with where clause did't match any record (the
> filter column had a value beyond the range of data), the performance when
> enabled
> predicate push-down was almost the same with when disabled predicate
> push-down. I think, when the filter column has a value beyond the range of
> data, all of the orc files will not be scanned if use file level indexes, so
> the performance should improve obviously.
>
> The second, when enabled "orc.create.index" and sorted data by filter
> column and where clause can only match a few records, the performance when
> enabled
> predicate push-down was almost the same with when disabled predicate
> push-down.
>
> The third, when enabled predicate push-down and "orc.create.index", the
> performance when
> filter column had a value beyond the range of data
> was almost the same with when filter column had a value covering almost
> the whole data.
>
> So, has anyone used ORC's build-in indexes before (especially in spark
> SQL)? What's my issue?
>
> Thanks!
>
> ------------------------------
> Joseph
>
>
> ------------------------------
>
> The information contained in this e-mail is confidential and/or
> proprietary to Capital One and/or its affiliates and may only be used
> solely in performance of work or services for Capital One. The information
> transmitted herewith is intended only for use by the individual or entity
> to which it is addressed. If the reader of this message is not the intended
> recipient, you are hereby notified that any review, retransmission,
> dissemination, distribution, copying or other use of, or taking of any
> action in reliance upon this information is strictly prohibited. If you
> have received this communication in error, please contact the sender and
> delete the material from your computer.
>
