[jira] [Commented] (SPARK-23012) Support for predicate pushdown and partition pruning when left joining large Hive tables
[
https://issues.apache.org/jira/browse/SPARK-23012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16787968#comment-16787968
]
Rick Kramer commented on SPARK-23012:
-
[~Saurabh Santhosh] We worked around it by materializing the hive view as a
cached table and used the table from spark.
I did retest this when 2.3.0 was released and I'm pretty sure the issue was
still there.
Good to know the 2.4 fixes it.
> Support for predicate pushdown and partition pruning when left joining large
> Hive tables
>
>
> Key: SPARK-23012
> URL: https://issues.apache.org/jira/browse/SPARK-23012
> Project: Spark
> Issue Type: Improvement
> Components: Optimizer
>Affects Versions: 2.2.0
>Reporter: Rick Kramer
>Priority: Major
>
> We have a hive view which left outer joins several large, partitioned orc
> hive tables together on date. When the view is used in a hive query, hive
> pushes date predicates down into the joins and prunes the partitions for all
> tables. When I use this view from pyspark, the predicate is only used to
> prune the left-most table and all partitions from the additional tables are
> selected.
> For example, consider two partitioned hive tables a & b joined in a view:
> create table a (
>a_val string
> )
> partitioned by (ds string)
> stored as orc;
> create table b (
>b_val string
> )
> partitioned by (ds string)
> stored as orc;
> create view example_view as
> select
> a_val
> , b_val
> , ds
> from a
> left outer join b on b.ds = a.ds
> Then in pyspark you might try to query from the view filtering on ds:
> spark.table('example_view').filter(F.col('ds') == '2018-01-01')
> If table a and b are large, this results in a plan that filters a on ds =
> 2018-01-01, but selects scans all partitions of table b.
> If the join in the view is changed to an inner join, the predicate gets
> pushed down to a & b and the partitions are pruned as you'd expect.
> In practice, the view is fairly complex and contains a lot of business logic
> we'd prefer not to replicate in pyspark if we can avoid it.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-23012) Support for predicate pushdown and partition pruning when left joining large Hive tables
[
https://issues.apache.org/jira/browse/SPARK-23012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16787716#comment-16787716
]
Saurabh Santhosh commented on SPARK-23012:
--
[~yumwang] [~reks95]
Hi, tested this in Spark 2.4.0 and its woking fine :)
> Support for predicate pushdown and partition pruning when left joining large
> Hive tables
>
>
> Key: SPARK-23012
> URL: https://issues.apache.org/jira/browse/SPARK-23012
> Project: Spark
> Issue Type: Improvement
> Components: Optimizer
>Affects Versions: 2.2.0
>Reporter: Rick Kramer
>Priority: Major
>
> We have a hive view which left outer joins several large, partitioned orc
> hive tables together on date. When the view is used in a hive query, hive
> pushes date predicates down into the joins and prunes the partitions for all
> tables. When I use this view from pyspark, the predicate is only used to
> prune the left-most table and all partitions from the additional tables are
> selected.
> For example, consider two partitioned hive tables a & b joined in a view:
> create table a (
>a_val string
> )
> partitioned by (ds string)
> stored as orc;
> create table b (
>b_val string
> )
> partitioned by (ds string)
> stored as orc;
> create view example_view as
> select
> a_val
> , b_val
> , ds
> from a
> left outer join b on b.ds = a.ds
> Then in pyspark you might try to query from the view filtering on ds:
> spark.table('example_view').filter(F.col('ds') == '2018-01-01')
> If table a and b are large, this results in a plan that filters a on ds =
> 2018-01-01, but selects scans all partitions of table b.
> If the join in the view is changed to an inner join, the predicate gets
> pushed down to a & b and the partitions are pruned as you'd expect.
> In practice, the view is fairly complex and contains a lot of business logic
> we'd prefer not to replicate in pyspark if we can avoid it.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-23012) Support for predicate pushdown and partition pruning when left joining large Hive tables
[
https://issues.apache.org/jira/browse/SPARK-23012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16787493#comment-16787493
]
Saurabh Santhosh commented on SPARK-23012:
--
[~yumwang] Thanks for the quick response. Will check and let you know :)
> Support for predicate pushdown and partition pruning when left joining large
> Hive tables
>
>
> Key: SPARK-23012
> URL: https://issues.apache.org/jira/browse/SPARK-23012
> Project: Spark
> Issue Type: Improvement
> Components: Optimizer
>Affects Versions: 2.2.0
>Reporter: Rick Kramer
>Priority: Major
>
> We have a hive view which left outer joins several large, partitioned orc
> hive tables together on date. When the view is used in a hive query, hive
> pushes date predicates down into the joins and prunes the partitions for all
> tables. When I use this view from pyspark, the predicate is only used to
> prune the left-most table and all partitions from the additional tables are
> selected.
> For example, consider two partitioned hive tables a & b joined in a view:
> create table a (
>a_val string
> )
> partitioned by (ds string)
> stored as orc;
> create table b (
>b_val string
> )
> partitioned by (ds string)
> stored as orc;
> create view example_view as
> select
> a_val
> , b_val
> , ds
> from a
> left outer join b on b.ds = a.ds
> Then in pyspark you might try to query from the view filtering on ds:
> spark.table('example_view').filter(F.col('ds') == '2018-01-01')
> If table a and b are large, this results in a plan that filters a on ds =
> 2018-01-01, but selects scans all partitions of table b.
> If the join in the view is changed to an inner join, the predicate gets
> pushed down to a & b and the partitions are pruned as you'd expect.
> In practice, the view is fairly complex and contains a lot of business logic
> we'd prefer not to replicate in pyspark if we can avoid it.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-23012) Support for predicate pushdown and partition pruning when left joining large Hive tables
[
https://issues.apache.org/jira/browse/SPARK-23012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16787485#comment-16787485
]
Yuming Wang commented on SPARK-23012:
-
[~Saurabh Santhosh] Could you try Spark 2.4.0 please?
> Support for predicate pushdown and partition pruning when left joining large
> Hive tables
>
>
> Key: SPARK-23012
> URL: https://issues.apache.org/jira/browse/SPARK-23012
> Project: Spark
> Issue Type: Improvement
> Components: Optimizer
>Affects Versions: 2.2.0
>Reporter: Rick Kramer
>Priority: Major
>
> We have a hive view which left outer joins several large, partitioned orc
> hive tables together on date. When the view is used in a hive query, hive
> pushes date predicates down into the joins and prunes the partitions for all
> tables. When I use this view from pyspark, the predicate is only used to
> prune the left-most table and all partitions from the additional tables are
> selected.
> For example, consider two partitioned hive tables a & b joined in a view:
> create table a (
>a_val string
> )
> partitioned by (ds string)
> stored as orc;
> create table b (
>b_val string
> )
> partitioned by (ds string)
> stored as orc;
> create view example_view as
> select
> a_val
> , b_val
> , ds
> from a
> left outer join b on b.ds = a.ds
> Then in pyspark you might try to query from the view filtering on ds:
> spark.table('example_view').filter(F.col('ds') == '2018-01-01')
> If table a and b are large, this results in a plan that filters a on ds =
> 2018-01-01, but selects scans all partitions of table b.
> If the join in the view is changed to an inner join, the predicate gets
> pushed down to a & b and the partitions are pruned as you'd expect.
> In practice, the view is fairly complex and contains a lot of business logic
> we'd prefer not to replicate in pyspark if we can avoid it.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-23012) Support for predicate pushdown and partition pruning when left joining large Hive tables
[
https://issues.apache.org/jira/browse/SPARK-23012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16787484#comment-16787484
]
Saurabh Santhosh commented on SPARK-23012:
--
[~reks95] How did you fix this issue?
> Support for predicate pushdown and partition pruning when left joining large
> Hive tables
>
>
> Key: SPARK-23012
> URL: https://issues.apache.org/jira/browse/SPARK-23012
> Project: Spark
> Issue Type: Improvement
> Components: Optimizer
>Affects Versions: 2.2.0
>Reporter: Rick Kramer
>Priority: Major
>
> We have a hive view which left outer joins several large, partitioned orc
> hive tables together on date. When the view is used in a hive query, hive
> pushes date predicates down into the joins and prunes the partitions for all
> tables. When I use this view from pyspark, the predicate is only used to
> prune the left-most table and all partitions from the additional tables are
> selected.
> For example, consider two partitioned hive tables a & b joined in a view:
> create table a (
>a_val string
> )
> partitioned by (ds string)
> stored as orc;
> create table b (
>b_val string
> )
> partitioned by (ds string)
> stored as orc;
> create view example_view as
> select
> a_val
> , b_val
> , ds
> from a
> left outer join b on b.ds = a.ds
> Then in pyspark you might try to query from the view filtering on ds:
> spark.table('example_view').filter(F.col('ds') == '2018-01-01')
> If table a and b are large, this results in a plan that filters a on ds =
> 2018-01-01, but selects scans all partitions of table b.
> If the join in the view is changed to an inner join, the predicate gets
> pushed down to a & b and the partitions are pruned as you'd expect.
> In practice, the view is fairly complex and contains a lot of business logic
> we'd prefer not to replicate in pyspark if we can avoid it.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-23012) Support for predicate pushdown and partition pruning when left joining large Hive tables
[
https://issues.apache.org/jira/browse/SPARK-23012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16787483#comment-16787483
]
Saurabh Santhosh commented on SPARK-23012:
--
[~yumwang] Any update on this? We are also having the same issue. Can you tell
me in which version this is fixed?
Thanks
> Support for predicate pushdown and partition pruning when left joining large
> Hive tables
>
>
> Key: SPARK-23012
> URL: https://issues.apache.org/jira/browse/SPARK-23012
> Project: Spark
> Issue Type: Improvement
> Components: Optimizer
>Affects Versions: 2.2.0
>Reporter: Rick Kramer
>Priority: Major
>
> We have a hive view which left outer joins several large, partitioned orc
> hive tables together on date. When the view is used in a hive query, hive
> pushes date predicates down into the joins and prunes the partitions for all
> tables. When I use this view from pyspark, the predicate is only used to
> prune the left-most table and all partitions from the additional tables are
> selected.
> For example, consider two partitioned hive tables a & b joined in a view:
> create table a (
>a_val string
> )
> partitioned by (ds string)
> stored as orc;
> create table b (
>b_val string
> )
> partitioned by (ds string)
> stored as orc;
> create view example_view as
> select
> a_val
> , b_val
> , ds
> from a
> left outer join b on b.ds = a.ds
> Then in pyspark you might try to query from the view filtering on ds:
> spark.table('example_view').filter(F.col('ds') == '2018-01-01')
> If table a and b are large, this results in a plan that filters a on ds =
> 2018-01-01, but selects scans all partitions of table b.
> If the join in the view is changed to an inner join, the predicate gets
> pushed down to a & b and the partitions are pruned as you'd expect.
> In practice, the view is fairly complex and contains a lot of business logic
> we'd prefer not to replicate in pyspark if we can avoid it.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-23012) Support for predicate pushdown and partition pruning when left joining large Hive tables
[
https://issues.apache.org/jira/browse/SPARK-23012?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16612881#comment-16612881
]
Yuming Wang commented on SPARK-23012:
-
It seems the following PR resolves your issue:
https://github.com/apache/spark/pull/20816
> Support for predicate pushdown and partition pruning when left joining large
> Hive tables
>
>
> Key: SPARK-23012
> URL: https://issues.apache.org/jira/browse/SPARK-23012
> Project: Spark
> Issue Type: Improvement
> Components: Optimizer
>Affects Versions: 2.2.0
>Reporter: Rick Kramer
>Priority: Major
>
> We have a hive view which left outer joins several large, partitioned orc
> hive tables together on date. When the view is used in a hive query, hive
> pushes date predicates down into the joins and prunes the partitions for all
> tables. When I use this view from pyspark, the predicate is only used to
> prune the left-most table and all partitions from the additional tables are
> selected.
> For example, consider two partitioned hive tables a & b joined in a view:
> create table a (
>a_val string
> )
> partitioned by (ds string)
> stored as orc;
> create table b (
>b_val string
> )
> partitioned by (ds string)
> stored as orc;
> create view example_view as
> select
> a_val
> , b_val
> , ds
> from a
> left outer join b on b.ds = a.ds
> Then in pyspark you might try to query from the view filtering on ds:
> spark.table('example_view').filter(F.col('ds') == '2018-01-01')
> If table a and b are large, this results in a plan that filters a on ds =
> 2018-01-01, but selects scans all partitions of table b.
> If the join in the view is changed to an inner join, the predicate gets
> pushed down to a & b and the partitions are pruned as you'd expect.
> In practice, the view is fairly complex and contains a lot of business logic
> we'd prefer not to replicate in pyspark if we can avoid it.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
