hive-issues mailing list archives

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

    [ https://issues.apache.org/jira/browse/HIVE-15026?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15594648#comment-15594648
] 

Carlos Martinez Moller commented on HIVE-15026:
-----------------------------------------------

In our production we have an old version and in consequence I can't try Hive on Spark with
the real query.

But I prepared a TestCase to check locally as I have a higher version.

>From my TestCase, using Hive on Spark the file is read twice. It is not generating an
intermediate RDD. I don't know if I may be missing a parameter which would control this, I
have my parameters set to the default values.

I upload:
- TestCase if you want to play with it
- Screenshot of the Stages of the Job in Spark, where you can see that twice 523K were read
(The size of the data I play with is 523K) Each SubSelect is working only with a single row
of the table.
- Explain Plan (You can generate it with the TestCase) where you can see that two Map Tasks
are generated, one for each SubSelect. There is none for the "logical view" of the WITH clause.

Creating temp tables is a workaround we thought of. But it would be nice to be able to create
a single Select that can execute an optimized plan, otherwise it implies a bit of development
to do something that could be done with a single optimized SQL, this is the reason of the
Jira.

> 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 and simplifying the
queries for the example. Hope they make sense and that the proposal I am doing can be understood.
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