hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Carlos Martinez Moller (JIRA)" <j...@apache.org>
Subject [jira] [Created] (HIVE-15026) Option to not merge the views
Date Thu, 20 Oct 2016 16:50:58 GMT
Carlos Martinez Moller created HIVE-15026:
---------------------------------------------

             Summary: Option to not merge the views
                 Key: HIVE-15026
                 URL: https://issues.apache.org/jira/browse/HIVE-15026
             Project: Hive
          Issue Type: Improvement
          Components: Logical Optimizer, Physical Optimizer
            Reporter: Carlos Martinez Moller


Note: I am trying to simplify a real case scenario we are having, but simplifying the queries
for the example. Hope they make sense and that the problem itself can be explained. The real
query is a lot more complex and long.

When performing a query of this type:

------------------------------------------------------------------
SELECT COLUMNA, COLUMNB, MAX (COLUMNC)
FROM TABLE_A
WHERE COLUMNA=1 AND COLUMND='Case 1'
UNION ALL
SELECT COLUMNA, COLUMNB, MAX (COLUMNC)
FROM TABLE_A
WHERE COLUMNA=10 AND COLUMNE='Case 2'
------------------------------------------------------------------

This creates Three Stages. First Stage is FULL SCAN of TABLE_A + Filter (COLUMNA=1/COLUMND='Case
1'),  Second Stage is FULL SCAN of TABLE_A again + Filter (COLUMNA=10/COLUMNE='Case 2'), and
third stage is the UNION ALL.

TABLE_A has 2TB data of information.
But COLUMNA=1 and COLUMNA=10 filter all together only 2GB of information.

So I thought to use:

--------------------------------------------------------------
WITH TEMP_VIEW AS
(SELECT COLUMNA,COLUMNB,COLUMNC,COLUMND
FROM TABLE_A
WHERE COLUMNA=1 AND COLUMNA=10)
SELECT COLUMNA, COLUMNB, MAX (COLUMNC)
FROM TEMP_VIEW
WHERE COLUMNA=1 AND COLUMND='Case 1'
UNION ALL
SELECT COLUMNA, COLUMNB, MAX (COLUMNC)
FROM TEMP_VIEW
WHERE COLUMNA=10 AND COLUMNE='Case 2'
---------------------------------------------------------------

I thought that with this it would create 4 Stages:
- Stage 1: Full Scan of TABLE_A and generate intermediate data
- Stage 2: In the data of Stage 1 Filter (COLUMNA=1/COLUMND='Case 1')
- Stage 3: In the data of Stage 1 Filter (COLUMNA=10/COLUMNE='Case 2')
- Stage 4: Union ALL

With this instead of 4TB being read from disk, only 2TB+4GB (twice going through the view)
would be read (In our case complexity is even bigger and we will be saving 20TB reading)

But it does the same than in the original query. It internally pushes the predicates of the
"WITH" query in the two parts of the UNION.

It would be good to have a control on this, or for the optimizer to choose the best approach
using histogram/statistics information.

For those knowing Oracle RDBMS this is equivalent to the MERGE/NO_MERGE and NEST behaviour:
http://www.dba-oracle.com/t_hint_no_merge.htm as an explanation...

Other approaches for my example could apply, as partitioning by COLUMNA of BUCKETING. But
are not applicable in our case as COLUMNA is not commonly used when accessing this table.

The point of this JIRA is to add a functionality similar to the one of Oracle (not Merging
the query, but generating an in-memory/disk temporary view) both for "WITH" clauses and VIEWS.

This is very very commonly used in Data Ware Houses managing big amounts of data and provides
big performance benefits.







--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message