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<mailto:jornfra...@gmail.com>
Date: 2016-03-16 20:27
To: Joseph<mailto:wxy81...@sina.com>
CC: user<mailto:user@spark.apache.org>; user<mailto: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<mailto: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<mailto:jornfra...@gmail.com>
Date: 2016-03-16 19:26
To: Joseph<mailto:wxy81...@sina.com>
CC: user<mailto:user@spark.apache.org>; user<mailto: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<mailto: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.
