You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@spark.apache.org by wangshuang <cn...@qq.com> on 2017/07/13 10:43:04 UTC
[SQL] Syntax "case when" doesn't be supported in JOIN
I'm trying to execute hive sql on spark sql (Also on spark thriftserver), For
optimizing data skew, we use "case when" to handle null.
Simple sql as following:
SELECT a.col1
FROM tbl1 a
LEFT OUTER JOIN tbl2 b
ON
* CASE
WHEN a.col2 IS NULL
TNEN cast(rand(9)*1000 - 9999999999 as string)
ELSE
a.col2 END *
= b.col3;
But I get the error:
== Physical Plan ==
*org.apache.spark.sql.AnalysisException: nondeterministic expressions are
only allowed in
Project, Filter, Aggregate or Window, found:*
(((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * CAST(1000 AS
DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE a.`nav_tcdt` END =
c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND (c.`cur_flag` =
1))
in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as int)
= 9)) && (cur_flag#77 = 1))
;;
GlobalLimit 10
+- LocalLimit 10
+- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string) IN
(cast(19596 as string),cast(20134 as string),cast(10997 as string)) &&
nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21 END],
[date_id#7]
+- Filter (date_id#7 = 2017-07-12)
+- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as int)
= 9)) && (cur_flag#77 = 1))
:- SubqueryAlias a
: +- SubqueryAlias tmp_lifan_trfc_tpa_hive
: +- CatalogRelation `tmp`.`tmp_lifan_trfc_tpa_hive`,
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7, chanl_id#8L,
pltfm_id#9, city_id#10, sessn_id#11, gu_id#12, nav_refer_page_type_id#13,
nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19, nav_page_value#20,
nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25, nav_tcd#26,
nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
detl_refer_page_value#30, ... 33 more fields]
+- SubqueryAlias c
+- SubqueryAlias dim_site_categ_ext
+- CatalogRelation `dw`.`dim_site_categ_ext`,
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [site_categ_skid#64L,
site_categ_type#65, site_categ_code#66, site_categ_name#67,
site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L, sort_seq#71L,
site_categ_srch_name#72, vsbl_flg#73, delet_flag#74, etl_batch_id#75L,
updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L, bkgrnd_categ_id#79L,
site_categ_id#80, site_categ_parnt_id#81]
Does spark sql not support syntax "case when" in JOIN? Additional, my spark
version is 2.2.0.
Any help would be greatly appreciated.
--
View this message in context: http://apache-spark-developers-list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-supported-in-JOIN-tp21953.html
Sent from the Apache Spark Developers List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Herman van Hövell tot Westerflier <hv...@databricks.com>.
Just move the case expression into an underlying select clause.
On Thu, Jul 13, 2017 at 3:10 PM, Chang Chen <ba...@gmail.com> wrote:
> Hi Wenchen
>
> Yes. We also find this error is caused by Rand. However, this is classic
> way to solve data skew in Hive. Is there any equivalent way in Spark?
>
> Thanks
> Chang
>
>
> On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <cl...@gmail.com> wrote:
>
>> It’s not about case when, but about rand(). Non-deterministic expressions
>> are not allowed in join condition.
>>
>> > On 13 Jul 2017, at 6:43 PM, wangshuang <cn...@qq.com> wrote:
>> >
>> > I'm trying to execute hive sql on spark sql (Also on spark
>> thriftserver), For
>> > optimizing data skew, we use "case when" to handle null.
>> > Simple sql as following:
>> >
>> >
>> > SELECT a.col1
>> > FROM tbl1 a
>> > LEFT OUTER JOIN tbl2 b
>> > ON
>> > * CASE
>> > WHEN a.col2 IS NULL
>> > TNEN cast(rand(9)*1000 - 9999999999 as string)
>> > ELSE
>> > a.col2 END *
>> > = b.col3;
>> >
>> >
>> > But I get the error:
>> >
>> > == Physical Plan ==
>> > *org.apache.spark.sql.AnalysisException: nondeterministic expressions
>> are
>> > only allowed in
>> > Project, Filter, Aggregate or Window, found:*
>> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * CAST(1000 AS
>> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE a.`nav_tcdt`
>> END =
>> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>> (c.`cur_flag` =
>> > 1))
>> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
>> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as
>> int)
>> > = 9)) && (cur_flag#77 = 1))
>> > ;;
>> > GlobalLimit 10
>> > +- LocalLimit 10
>> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string) IN
>> > (cast(19596 as string),cast(20134 as string),cast(10997 as string)) &&
>> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21 END],
>> > [date_id#7]
>> > +- Filter (date_id#7 = 2017-07-12)
>> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
>> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as
>> int)
>> > = 9)) && (cur_flag#77 = 1))
>> > :- SubqueryAlias a
>> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>> > : +- CatalogRelation `tmp`.`tmp_lifan_trfc_tpa_hive`,
>> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
>> chanl_id#8L,
>> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>> nav_refer_page_type_id#13,
>> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
>> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>> nav_page_value#20,
>> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
>> nav_tcd#26,
>> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>> > detl_refer_page_value#30, ... 33 more fields]
>> > +- SubqueryAlias c
>> > +- SubqueryAlias dim_site_categ_ext
>> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
>> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> [site_categ_skid#64L,
>> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
>> sort_seq#71L,
>> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74, etl_batch_id#75L,
>> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L, bkgrnd_categ_id#79L,
>> > site_categ_id#80, site_categ_parnt_id#81]
>> >
>> > Does spark sql not support syntax "case when" in JOIN? Additional, my
>> spark
>> > version is 2.2.0.
>> > Any help would be greatly appreciated.
>> >
>> >
>> >
>> >
>> > --
>> > View this message in context: http://apache-spark-developers
>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>> supported-in-JOIN-tp21953.html
>> > Sent from the Apache Spark Developers List mailing list archive at
>> Nabble.com.
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>> >
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>>
>>
>
--
Herman van Hövell
Software Engineer
Databricks Inc.
hvanhovell@databricks.com
+31 6 420 590 27
databricks.com
[image: http://databricks.com] <http://databricks.com/>
[image: Announcing Databricks Serverless. The first serverless data science
and big data platform. Watch the demo from Spark Summit 2017.]
<http://go.databricks.com/announcing-databricks-serverless>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Chang Chen <ba...@gmail.com>.
Sorry, I didn't express clearly. I think the evaluation order doesn't
matter in the context of join implementation(sort or hash based). it should
only refer to join key.
Thanks
Chang
On Tue, Jul 18, 2017 at 7:57 AM, Liang-Chi Hsieh <vi...@gmail.com> wrote:
>
> Evaluation order does matter. A non-deterministic expression can change its
> output due to internal state which may depend on input order.
>
> MonotonicallyIncreasingID is an example for the stateful expression. Once
> you change the row order, the evaluation results are different.
>
>
>
> Chang Chen wrote
> > I see.
> >
> > Actually, it isn't about evaluation order which user can't specify. It's
> > about how many times we evaluate the non-deterministic expression for the
> > same row.
> >
> > For example, given the SQL:
> >
> > SELECT a.col1
> > FROM tbl1 a
> > LEFT OUTER JOIN tbl2 b
> > ON
> > CASE WHEN a.col2 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string)
> > ELSE a.col2 END
> > =
> > CASE WHEN b.col3 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string)
> > ELSE b.col3 END;
> >
> > I think if we exactly evaluate join key one time for each row of a and
> b
> > in the whole pipeline, even if the result isn't deterministic, but the
> > computation is correct.
> >
> > Thanks
> > Chang
> >
> >
> > On Mon, Jul 17, 2017 at 10:49 PM, Liang-Chi Hsieh <
>
> > viirya@
>
> > > wrote:
> >
> >>
> >> IIUC, the evaluation order of rows in Join can be different in different
> >> physical operators, e.g., Sort-based and Hash-based.
> >>
> >> But for non-deterministic expressions, different evaluation orders
> change
> >> results.
> >>
> >>
> >>
> >> Chang Chen wrote
> >> > I see the issue. I will try https://github.com/apache/
> spark/pull/18652,
> >> I
> >> > think
> >> >
> >> > 1 For Join Operator, the left and right plan can't be
> >> non-deterministic.
> >> > 2 If Filter can support non-deterministic, why not join condition?
> >> > 3 We can't push down or project non-deterministic expression, since it
> >> may
> >> > change semantics.
> >> >
> >> > Actually, the real problem is #2. If the join condition could be
> >> > non-deterministic, then we needn't insert project.
> >> >
> >> > Thanks
> >> > Chang
> >> >
> >> >
> >> >
> >> >
> >> > On Mon, Jul 17, 2017 at 3:59 PM, 蒋星博 <
> >>
> >> > jiangxb1987@
> >>
> >> > > wrote:
> >> >
> >> >> FYI there have been a related discussion here:
> >> https://github.com/apache/
> >> >> spark/pull/15417#discussion_r85295977
> >> >>
> >> >> 2017-07-17 15:44 GMT+08:00 Chang Chen <
> >>
> >> > baibaichen@
> >>
> >> > >:
> >> >>
> >> >>> Hi All
> >> >>>
> >> >>> I don't understand the difference between the semantics, I found
> >> Spark
> >> >>> does the same thing for GroupBy non-deterministic. From Map-Reduce
> >> point
> >> >>> of
> >> >>> view, Join is also GroupBy in essence .
> >> >>>
> >> >>> @Liang Chi Hsieh
> >> >>> <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
> >> >>>
> >> >>> in which situation, semantics will be changed?
> >> >>>
> >> >>> Thanks
> >> >>> Chang
> >> >>>
> >> >>> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <
> >>
> >> > viirya@
> >>
> >> > >
> >> >>> wrote:
> >> >>>
> >> >>>>
> >> >>>> Thinking about it more, I think it changes the semantics only under
> >> >>>> certain
> >> >>>> scenarios.
> >> >>>>
> >> >>>> For the example SQL query shown in previous discussion, it looks
> the
> >> >>>> same
> >> >>>> semantics.
> >> >>>>
> >> >>>>
> >> >>>> Xiao Li wrote
> >> >>>> > If the join condition is non-deterministic, pushing it down to
> the
> >> >>>> > underlying project will change the semantics. Thus, we are unable
> >> to
> >> >>>> do it
> >> >>>> > in PullOutNondeterministic. Users can do it manually if they do
> >> not
> >> >>>> care
> >> >>>> > the semantics difference.
> >> >>>> >
> >> >>>> > Thanks,
> >> >>>> >
> >> >>>> > Xiao
> >> >>>> >
> >> >>>> >
> >> >>>> >
> >> >>>> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
> >> >>>>
> >> >>>> > baibaichen@
> >> >>>>
> >> >>>> > >:
> >> >>>> >
> >> >>>> >> It is tedious since we have lots of Hive SQL being migrated to
> >> >>>> Spark.
> >> >>>> >> And
> >> >>>> >> this workaround is equivalent to insert a Project between Join
> >> >>>> operator
> >> >>>> >> and its child.
> >> >>>> >>
> >> >>>> >> Why not do it in PullOutNondeterministic?
> >> >>>> >>
> >> >>>> >> Thanks
> >> >>>> >> Chang
> >> >>>> >>
> >> >>>> >>
> >> >>>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
> >> >>>>
> >> >>>> > viirya@
> >> >>>>
> >> >>>> > > wrote:
> >> >>>> >>
> >> >>>> >>>
> >> >>>> >>> A possible workaround is to add the rand column into tbl1 with
> a
> >> >>>> >>> projection
> >> >>>> >>> before the join.
> >> >>>> >>>
> >> >>>> >>> SELECT a.col1
> >> >>>> >>> FROM (
> >> >>>> >>> SELECT col1,
> >> >>>> >>> CASE
> >> >>>> >>> WHEN col2 IS NULL
> >> >>>> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
> >> >>>> >>> ELSE
> >> >>>> >>> col2
> >> >>>> >>> END AS col2
> >> >>>> >>> FROM tbl1) a
> >> >>>> >>> LEFT OUTER JOIN tbl2 b
> >> >>>> >>> ON a.col2 = b.col3;
> >> >>>> >>>
> >> >>>> >>>
> >> >>>> >>>
> >> >>>> >>> Chang Chen wrote
> >> >>>> >>> > Hi Wenchen
> >> >>>> >>> >
> >> >>>> >>> > Yes. We also find this error is caused by Rand. However, this
> >> is
> >> >>>> >>> classic
> >> >>>> >>> > way to solve data skew in Hive. Is there any equivalent way
> >> in
> >> >>>> Spark?
> >> >>>> >>> >
> >> >>>> >>> > Thanks
> >> >>>> >>> > Chang
> >> >>>> >>> >
> >> >>>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
> >> >>>> >>>
> >> >>>> >>> > cloud0fan@
> >> >>>> >>>
> >> >>>> >>> > > wrote:
> >> >>>> >>> >
> >> >>>> >>> >> It’s not about case when, but about rand().
> Non-deterministic
> >> >>>> >>> expressions
> >> >>>> >>> >> are not allowed in join condition.
> >> >>>> >>> >>
> >> >>>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
> >> >>>> >>>
> >> >>>> >>> > cn_wss@
> >> >>>> >>>
> >> >>>> >>> > > wrote:
> >> >>>> >>> >> >
> >> >>>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
> >> >>>> >>> >> thriftserver), For
> >> >>>> >>> >> > optimizing data skew, we use "case when" to handle null.
> >> >>>> >>> >> > Simple sql as following:
> >> >>>> >>> >> >
> >> >>>> >>> >> >
> >> >>>> >>> >> > SELECT a.col1
> >> >>>> >>> >> > FROM tbl1 a
> >> >>>> >>> >> > LEFT OUTER JOIN tbl2 b
> >> >>>> >>> >> > ON
> >> >>>> >>> >> > * CASE
> >> >>>> >>> >> > WHEN a.col2 IS NULL
> >> >>>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999
> >> as
> >> >>>> >>> string)
> >> >>>> >>> >> > ELSE
> >> >>>> >>> >> > a.col2 END *
> >> >>>> >>> >> > = b.col3;
> >> >>>> >>> >> >
> >> >>>> >>> >> >
> >> >>>> >>> >> > But I get the error:
> >> >>>> >>> >> >
> >> >>>> >>> >> > == Physical Plan ==
> >> >>>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
> >> >>>> >>> expressions
> >> >>>> >>> >> are
> >> >>>> >>> >> > only allowed in
> >> >>>> >>> >> > Project, Filter, Aggregate or Window, found:*
> >> >>>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
> >> >>>> CAST(1000
> >> >>>> >>> AS
> >> >>>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
> >> >>>> >>> a.`nav_tcdt`
> >> >>>> >>> >> END
> >> >>>> >>> >> =
> >> >>>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
> >> >>>> >>> >> (c.`cur_flag`
> >> >>>> >>> >> =
> >> >>>> >>> >> > 1))
> >> >>>> >>> >> > in operator Join LeftOuter, (((CASE WHEN
> >> isnull(nav_tcdt#25)
> >> >>>> THEN
> >> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999
> as
> >> >>>> >>> double))
> >> >>>> >>> as
> >> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
> >> >>>> >>> (cast(nav_tcd#26
> >> >>>> >>> as
> >> >>>> >>> >> int)
> >> >>>> >>> >> > = 9)) && (cur_flag#77 = 1))
> >> >>>> >>> >> > ;;
> >> >>>> >>> >> > GlobalLimit 10
> >> >>>> >>> >> > +- LocalLimit 10
> >> >>>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as
> >> >>>> string) IN
> >> >>>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
> >> >>>> string))
> >> >>>> >>> &&
> >> >>>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE
> >> nav_tpa_id#21
> >> >>>> >>> END],
> >> >>>> >>> >> > [date_id#7]
> >> >>>> >>> >> > +- Filter (date_id#7 = 2017-07-12)
> >> >>>> >>> >> > +- Join LeftOuter, (((CASE WHEN
> isnull(nav_tcdt#25)
> >> >>>> THEN
> >> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999
> as
> >> >>>> >>> double))
> >> >>>> >>> as
> >> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
> >> >>>> >>> (cast(nav_tcd#26
> >> >>>> >>> as
> >> >>>> >>> >> int)
> >> >>>> >>> >> > = 9)) && (cur_flag#77 = 1))
> >> >>>> >>> >> > :- SubqueryAlias a
> >> >>>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
> >> >>>> >>> >> > : +- CatalogRelation
> >> >>>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
> >> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> >> >>>> [date_id#7,
> >> >>>> >>> >> chanl_id#8L,
> >> >>>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
> >> >>>> >>> >> nav_refer_page_type_id#13,
> >> >>>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15,
> >> >>>> nav_refer_tpa_id#16,
> >> >>>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
> >> >>>> >>> >> nav_page_value#20,
> >> >>>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24,
> >> >>>> nav_tcdt#25,
> >> >>>> >>> >> nav_tcd#26,
> >> >>>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
> >> >>>> >>> >> > detl_refer_page_value#30, ... 33 more fields]
> >> >>>> >>> >> > +- SubqueryAlias c
> >> >>>> >>> >> > +- SubqueryAlias dim_site_categ_ext
> >> >>>> >>> >> > +- CatalogRelation
> >> `dw`.`dim_site_categ_ext`,
> >> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> >> >>>> >>> >> [site_categ_skid#64L,
> >> >>>> >>> >> > site_categ_type#65, site_categ_code#66,
> site_categ_name#67,
> >> >>>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69,
> >> leaf_flg#70L,
> >> >>>> >>> >> sort_seq#71L,
> >> >>>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
> >> >>>> >>> etl_batch_id#75L,
> >> >>>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
> >> >>>> >>> bkgrnd_categ_id#79L,
> >> >>>> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
> >> >>>> >>> >> >
> >> >>>> >>> >> > Does spark sql not support syntax "case when" in JOIN?
> >> >>>> Additional,
> >> >>>> >>> my
> >> >>>> >>> >> spark
> >> >>>> >>> >> > version is 2.2.0.
> >> >>>> >>> >> > Any help would be greatly appreciated.
> >> >>>> >>> >> >
> >> >>>> >>> >> >
> >> >>>> >>> >> >
> >> >>>> >>> >> >
> >> >>>> >>> >> > --
> >> >>>> >>> >> > View this message in context:
> >> http://apache-spark-developers
> >> >>>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
> >> >>>> >>> >> be-supported-in-JOIN-tp21953.html
> >> >>>> >>> >> > Sent from the Apache Spark Developers List mailing list
> >> >>>> archive
> >> >>>> at
> >> >>>> >>> >> Nabble.com.
> >> >>>> >>> >> >
> >> >>>> >>> >> >
> >> ------------------------------------------------------------
> >> >>>> >>> ---------
> >> >>>> >>> >> > To unsubscribe e-mail:
> >> >>>> >>>
> >> >>>> >>> > dev-unsubscribe@.apache
> >> >>>> >>>
> >> >>>> >>> >> >
> >> >>>> >>> >>
> >> >>>> >>> >>
> >> >>>> >>> >> ------------------------------
> ------------------------------
> >> >>>> ---------
> >> >>>> >>> >> To unsubscribe e-mail:
> >> >>>> >>>
> >> >>>> >>> > dev-unsubscribe@.apache
> >> >>>> >>>
> >> >>>> >>> >>
> >> >>>> >>> >>
> >> >>>> >>>
> >> >>>> >>>
> >> >>>> >>>
> >> >>>> >>>
> >> >>>> >>>
> >> >>>> >>> -----
> >> >>>> >>> Liang-Chi Hsieh | @viirya
> >> >>>> >>> Spark Technology Center
> >> >>>> >>> http://www.spark.tc/
> >> >>>> >>> --
> >> >>>> >>> View this message in context: http://apache-spark-developers
> >> >>>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
> >> >>>> >>> supported-in-JOIN-tp21953p21961.html
> >> >>>> >>> Sent from the Apache Spark Developers List mailing list archive
> >> at
> >> >>>> >>> Nabble.com.
> >> >>>> >>>
> >> >>>> >>> ------------------------------------------------------------
> >> >>>> ---------
> >> >>>> >>> To unsubscribe e-mail:
> >> >>>>
> >> >>>> > dev-unsubscribe@.apache
> >> >>>>
> >> >>>> >>>
> >> >>>> >>>
> >> >>>> >>
> >> >>>>
> >> >>>>
> >> >>>>
> >> >>>>
> >> >>>>
> >> >>>> -----
> >> >>>> Liang-Chi Hsieh | @viirya
> >> >>>> Spark Technology Center
> >> >>>> http://www.spark.tc/
> >> >>>> --
> >> >>>> View this message in context: http://apache-spark-developers
> >> >>>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
> >> >>>> supported-in-JOIN-tp21953p21973.html
> >> >>>> Sent from the Apache Spark Developers List mailing list archive at
> >> >>>> Nabble.com.
> >> >>>>
> >> >>>>
> >> ---------------------------------------------------------------------
> >> >>>> To unsubscribe e-mail:
> >>
> >> > dev-unsubscribe@.apache
> >>
> >> >>>>
> >> >>>>
> >> >>>
> >> >>
> >>
> >>
> >>
> >>
> >>
> >> -----
> >> Liang-Chi Hsieh | @viirya
> >> Spark Technology Center
> >> http://www.spark.tc/
> >> --
> >> View this message in context: http://apache-spark-
> >> developers-list.1001551.n3.nabble.com/SQL-Syntax-case-
> >> when-doesn-t-be-supported-in-JOIN-tp21953p21982.html
> >> Sent from the Apache Spark Developers List mailing list archive at
> >> Nabble.com.
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe e-mail:
>
> > dev-unsubscribe@.apache
>
> >>
> >>
>
>
>
>
>
> -----
> Liang-Chi Hsieh | @viirya
> Spark Technology Center
> http://www.spark.tc/
> --
> View this message in context: http://apache-spark-
> developers-list.1001551.n3.nabble.com/SQL-Syntax-case-
> when-doesn-t-be-supported-in-JOIN-tp21953p21988.html
> Sent from the Apache Spark Developers List mailing list archive at
> Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Liang-Chi Hsieh <vi...@gmail.com>.
Evaluation order does matter. A non-deterministic expression can change its
output due to internal state which may depend on input order.
MonotonicallyIncreasingID is an example for the stateful expression. Once
you change the row order, the evaluation results are different.
Chang Chen wrote
> I see.
>
> Actually, it isn't about evaluation order which user can't specify. It's
> about how many times we evaluate the non-deterministic expression for the
> same row.
>
> For example, given the SQL:
>
> SELECT a.col1
> FROM tbl1 a
> LEFT OUTER JOIN tbl2 b
> ON
> CASE WHEN a.col2 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string)
> ELSE a.col2 END
> =
> CASE WHEN b.col3 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string)
> ELSE b.col3 END;
>
> I think if we exactly evaluate join key one time for each row of a and b
> in the whole pipeline, even if the result isn't deterministic, but the
> computation is correct.
>
> Thanks
> Chang
>
>
> On Mon, Jul 17, 2017 at 10:49 PM, Liang-Chi Hsieh <
> viirya@
> > wrote:
>
>>
>> IIUC, the evaluation order of rows in Join can be different in different
>> physical operators, e.g., Sort-based and Hash-based.
>>
>> But for non-deterministic expressions, different evaluation orders change
>> results.
>>
>>
>>
>> Chang Chen wrote
>> > I see the issue. I will try https://github.com/apache/spark/pull/18652,
>> I
>> > think
>> >
>> > 1 For Join Operator, the left and right plan can't be
>> non-deterministic.
>> > 2 If Filter can support non-deterministic, why not join condition?
>> > 3 We can't push down or project non-deterministic expression, since it
>> may
>> > change semantics.
>> >
>> > Actually, the real problem is #2. If the join condition could be
>> > non-deterministic, then we needn't insert project.
>> >
>> > Thanks
>> > Chang
>> >
>> >
>> >
>> >
>> > On Mon, Jul 17, 2017 at 3:59 PM, 蒋星博 <
>>
>> > jiangxb1987@
>>
>> > > wrote:
>> >
>> >> FYI there have been a related discussion here:
>> https://github.com/apache/
>> >> spark/pull/15417#discussion_r85295977
>> >>
>> >> 2017-07-17 15:44 GMT+08:00 Chang Chen <
>>
>> > baibaichen@
>>
>> > >:
>> >>
>> >>> Hi All
>> >>>
>> >>> I don't understand the difference between the semantics, I found
>> Spark
>> >>> does the same thing for GroupBy non-deterministic. From Map-Reduce
>> point
>> >>> of
>> >>> view, Join is also GroupBy in essence .
>> >>>
>> >>> @Liang Chi Hsieh
>> >>> <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
>> >>>
>> >>> in which situation, semantics will be changed?
>> >>>
>> >>> Thanks
>> >>> Chang
>> >>>
>> >>> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <
>>
>> > viirya@
>>
>> > >
>> >>> wrote:
>> >>>
>> >>>>
>> >>>> Thinking about it more, I think it changes the semantics only under
>> >>>> certain
>> >>>> scenarios.
>> >>>>
>> >>>> For the example SQL query shown in previous discussion, it looks the
>> >>>> same
>> >>>> semantics.
>> >>>>
>> >>>>
>> >>>> Xiao Li wrote
>> >>>> > If the join condition is non-deterministic, pushing it down to the
>> >>>> > underlying project will change the semantics. Thus, we are unable
>> to
>> >>>> do it
>> >>>> > in PullOutNondeterministic. Users can do it manually if they do
>> not
>> >>>> care
>> >>>> > the semantics difference.
>> >>>> >
>> >>>> > Thanks,
>> >>>> >
>> >>>> > Xiao
>> >>>> >
>> >>>> >
>> >>>> >
>> >>>> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
>> >>>>
>> >>>> > baibaichen@
>> >>>>
>> >>>> > >:
>> >>>> >
>> >>>> >> It is tedious since we have lots of Hive SQL being migrated to
>> >>>> Spark.
>> >>>> >> And
>> >>>> >> this workaround is equivalent to insert a Project between Join
>> >>>> operator
>> >>>> >> and its child.
>> >>>> >>
>> >>>> >> Why not do it in PullOutNondeterministic?
>> >>>> >>
>> >>>> >> Thanks
>> >>>> >> Chang
>> >>>> >>
>> >>>> >>
>> >>>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
>> >>>>
>> >>>> > viirya@
>> >>>>
>> >>>> > > wrote:
>> >>>> >>
>> >>>> >>>
>> >>>> >>> A possible workaround is to add the rand column into tbl1 with a
>> >>>> >>> projection
>> >>>> >>> before the join.
>> >>>> >>>
>> >>>> >>> SELECT a.col1
>> >>>> >>> FROM (
>> >>>> >>> SELECT col1,
>> >>>> >>> CASE
>> >>>> >>> WHEN col2 IS NULL
>> >>>> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
>> >>>> >>> ELSE
>> >>>> >>> col2
>> >>>> >>> END AS col2
>> >>>> >>> FROM tbl1) a
>> >>>> >>> LEFT OUTER JOIN tbl2 b
>> >>>> >>> ON a.col2 = b.col3;
>> >>>> >>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>> Chang Chen wrote
>> >>>> >>> > Hi Wenchen
>> >>>> >>> >
>> >>>> >>> > Yes. We also find this error is caused by Rand. However, this
>> is
>> >>>> >>> classic
>> >>>> >>> > way to solve data skew in Hive. Is there any equivalent way
>> in
>> >>>> Spark?
>> >>>> >>> >
>> >>>> >>> > Thanks
>> >>>> >>> > Chang
>> >>>> >>> >
>> >>>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>> >>>> >>>
>> >>>> >>> > cloud0fan@
>> >>>> >>>
>> >>>> >>> > > wrote:
>> >>>> >>> >
>> >>>> >>> >> It’s not about case when, but about rand(). Non-deterministic
>> >>>> >>> expressions
>> >>>> >>> >> are not allowed in join condition.
>> >>>> >>> >>
>> >>>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>> >>>> >>>
>> >>>> >>> > cn_wss@
>> >>>> >>>
>> >>>> >>> > > wrote:
>> >>>> >>> >> >
>> >>>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
>> >>>> >>> >> thriftserver), For
>> >>>> >>> >> > optimizing data skew, we use "case when" to handle null.
>> >>>> >>> >> > Simple sql as following:
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> > SELECT a.col1
>> >>>> >>> >> > FROM tbl1 a
>> >>>> >>> >> > LEFT OUTER JOIN tbl2 b
>> >>>> >>> >> > ON
>> >>>> >>> >> > * CASE
>> >>>> >>> >> > WHEN a.col2 IS NULL
>> >>>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999
>> as
>> >>>> >>> string)
>> >>>> >>> >> > ELSE
>> >>>> >>> >> > a.col2 END *
>> >>>> >>> >> > = b.col3;
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> > But I get the error:
>> >>>> >>> >> >
>> >>>> >>> >> > == Physical Plan ==
>> >>>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
>> >>>> >>> expressions
>> >>>> >>> >> are
>> >>>> >>> >> > only allowed in
>> >>>> >>> >> > Project, Filter, Aggregate or Window, found:*
>> >>>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
>> >>>> CAST(1000
>> >>>> >>> AS
>> >>>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
>> >>>> >>> a.`nav_tcdt`
>> >>>> >>> >> END
>> >>>> >>> >> =
>> >>>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>> >>>> >>> >> (c.`cur_flag`
>> >>>> >>> >> =
>> >>>> >>> >> > 1))
>> >>>> >>> >> > in operator Join LeftOuter, (((CASE WHEN
>> isnull(nav_tcdt#25)
>> >>>> THEN
>> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>> >>>> >>> double))
>> >>>> >>> as
>> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>> >>>> >>> (cast(nav_tcd#26
>> >>>> >>> as
>> >>>> >>> >> int)
>> >>>> >>> >> > = 9)) && (cur_flag#77 = 1))
>> >>>> >>> >> > ;;
>> >>>> >>> >> > GlobalLimit 10
>> >>>> >>> >> > +- LocalLimit 10
>> >>>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as
>> >>>> string) IN
>> >>>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
>> >>>> string))
>> >>>> >>> &&
>> >>>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE
>> nav_tpa_id#21
>> >>>> >>> END],
>> >>>> >>> >> > [date_id#7]
>> >>>> >>> >> > +- Filter (date_id#7 = 2017-07-12)
>> >>>> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
>> >>>> THEN
>> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>> >>>> >>> double))
>> >>>> >>> as
>> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>> >>>> >>> (cast(nav_tcd#26
>> >>>> >>> as
>> >>>> >>> >> int)
>> >>>> >>> >> > = 9)) && (cur_flag#77 = 1))
>> >>>> >>> >> > :- SubqueryAlias a
>> >>>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>> >>>> >>> >> > : +- CatalogRelation
>> >>>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
>> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> >>>> [date_id#7,
>> >>>> >>> >> chanl_id#8L,
>> >>>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>> >>>> >>> >> nav_refer_page_type_id#13,
>> >>>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15,
>> >>>> nav_refer_tpa_id#16,
>> >>>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>> >>>> >>> >> nav_page_value#20,
>> >>>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24,
>> >>>> nav_tcdt#25,
>> >>>> >>> >> nav_tcd#26,
>> >>>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>> >>>> >>> >> > detl_refer_page_value#30, ... 33 more fields]
>> >>>> >>> >> > +- SubqueryAlias c
>> >>>> >>> >> > +- SubqueryAlias dim_site_categ_ext
>> >>>> >>> >> > +- CatalogRelation
>> `dw`.`dim_site_categ_ext`,
>> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> >>>> >>> >> [site_categ_skid#64L,
>> >>>> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>> >>>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69,
>> leaf_flg#70L,
>> >>>> >>> >> sort_seq#71L,
>> >>>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
>> >>>> >>> etl_batch_id#75L,
>> >>>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
>> >>>> >>> bkgrnd_categ_id#79L,
>> >>>> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
>> >>>> >>> >> >
>> >>>> >>> >> > Does spark sql not support syntax "case when" in JOIN?
>> >>>> Additional,
>> >>>> >>> my
>> >>>> >>> >> spark
>> >>>> >>> >> > version is 2.2.0.
>> >>>> >>> >> > Any help would be greatly appreciated.
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> > --
>> >>>> >>> >> > View this message in context:
>> http://apache-spark-developers
>> >>>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>> >>>> >>> >> be-supported-in-JOIN-tp21953.html
>> >>>> >>> >> > Sent from the Apache Spark Developers List mailing list
>> >>>> archive
>> >>>> at
>> >>>> >>> >> Nabble.com.
>> >>>> >>> >> >
>> >>>> >>> >> >
>> ------------------------------------------------------------
>> >>>> >>> ---------
>> >>>> >>> >> > To unsubscribe e-mail:
>> >>>> >>>
>> >>>> >>> > dev-unsubscribe@.apache
>> >>>> >>>
>> >>>> >>> >> >
>> >>>> >>> >>
>> >>>> >>> >>
>> >>>> >>> >> ------------------------------------------------------------
>> >>>> ---------
>> >>>> >>> >> To unsubscribe e-mail:
>> >>>> >>>
>> >>>> >>> > dev-unsubscribe@.apache
>> >>>> >>>
>> >>>> >>> >>
>> >>>> >>> >>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>> -----
>> >>>> >>> Liang-Chi Hsieh | @viirya
>> >>>> >>> Spark Technology Center
>> >>>> >>> http://www.spark.tc/
>> >>>> >>> --
>> >>>> >>> View this message in context: http://apache-spark-developers
>> >>>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>> >>>> >>> supported-in-JOIN-tp21953p21961.html
>> >>>> >>> Sent from the Apache Spark Developers List mailing list archive
>> at
>> >>>> >>> Nabble.com.
>> >>>> >>>
>> >>>> >>> ------------------------------------------------------------
>> >>>> ---------
>> >>>> >>> To unsubscribe e-mail:
>> >>>>
>> >>>> > dev-unsubscribe@.apache
>> >>>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>> -----
>> >>>> Liang-Chi Hsieh | @viirya
>> >>>> Spark Technology Center
>> >>>> http://www.spark.tc/
>> >>>> --
>> >>>> View this message in context: http://apache-spark-developers
>> >>>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>> >>>> supported-in-JOIN-tp21953p21973.html
>> >>>> Sent from the Apache Spark Developers List mailing list archive at
>> >>>> Nabble.com.
>> >>>>
>> >>>>
>> ---------------------------------------------------------------------
>> >>>> To unsubscribe e-mail:
>>
>> > dev-unsubscribe@.apache
>>
>> >>>>
>> >>>>
>> >>>
>> >>
>>
>>
>>
>>
>>
>> -----
>> Liang-Chi Hsieh | @viirya
>> Spark Technology Center
>> http://www.spark.tc/
>> --
>> View this message in context: http://apache-spark-
>> developers-list.1001551.n3.nabble.com/SQL-Syntax-case-
>> when-doesn-t-be-supported-in-JOIN-tp21953p21982.html
>> Sent from the Apache Spark Developers List mailing list archive at
>> Nabble.com.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe e-mail:
> dev-unsubscribe@.apache
>>
>>
-----
Liang-Chi Hsieh | @viirya
Spark Technology Center
http://www.spark.tc/
--
View this message in context: http://apache-spark-developers-list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-supported-in-JOIN-tp21953p21988.html
Sent from the Apache Spark Developers List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Xiao Li <ga...@gmail.com>.
When users call rand(seed) with a specific seed number, users expect the
results should be deterministic no matter whether this is pushed down or
not. rand(seed) is stateful. Thus, the order of predicates in the same join
condition even matters. For example, in the same join condition, if the
first predicate is false and the second one is skipped. If we simply push
it down to the child, the number of rand(seed) calls is different.
Thanks,
Xiao
2017-07-17 9:28 GMT-07:00 Chang Chen <ba...@gmail.com>:
> I see.
>
> Actually, it isn't about evaluation order which user can't specify. It's
> about how many times we evaluate the non-deterministic expression for the
> same row.
>
> For example, given the SQL:
>
> SELECT a.col1
> FROM tbl1 a
> LEFT OUTER JOIN tbl2 b
> ON
> CASE WHEN a.col2 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string)
> ELSE a.col2 END
> =
> CASE WHEN b.col3 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string)
> ELSE b.col3 END;
>
> I think if we exactly evaluate join key one time for each row of a and
> b in the whole pipeline, even if the result isn't deterministic, but the
> computation is correct.
>
> Thanks
> Chang
>
>
> On Mon, Jul 17, 2017 at 10:49 PM, Liang-Chi Hsieh <vi...@gmail.com>
> wrote:
>
>>
>> IIUC, the evaluation order of rows in Join can be different in different
>> physical operators, e.g., Sort-based and Hash-based.
>>
>> But for non-deterministic expressions, different evaluation orders change
>> results.
>>
>>
>>
>> Chang Chen wrote
>> > I see the issue. I will try https://github.com/apache/spark/pull/18652,
>> I
>> > think
>> >
>> > 1 For Join Operator, the left and right plan can't be non-deterministic.
>> > 2 If Filter can support non-deterministic, why not join condition?
>> > 3 We can't push down or project non-deterministic expression, since it
>> may
>> > change semantics.
>> >
>> > Actually, the real problem is #2. If the join condition could be
>> > non-deterministic, then we needn't insert project.
>> >
>> > Thanks
>> > Chang
>> >
>> >
>> >
>> >
>> > On Mon, Jul 17, 2017 at 3:59 PM, 蒋星博 <
>>
>> > jiangxb1987@
>>
>> > > wrote:
>> >
>> >> FYI there have been a related discussion here:
>> https://github.com/apache/
>> >> spark/pull/15417#discussion_r85295977
>> >>
>> >> 2017-07-17 15:44 GMT+08:00 Chang Chen <
>>
>> > baibaichen@
>>
>> > >:
>> >>
>> >>> Hi All
>> >>>
>> >>> I don't understand the difference between the semantics, I found Spark
>> >>> does the same thing for GroupBy non-deterministic. From Map-Reduce
>> point
>> >>> of
>> >>> view, Join is also GroupBy in essence .
>> >>>
>> >>> @Liang Chi Hsieh
>> >>> <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
>> >>>
>> >>> in which situation, semantics will be changed?
>> >>>
>> >>> Thanks
>> >>> Chang
>> >>>
>> >>> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <
>>
>> > viirya@
>>
>> > >
>> >>> wrote:
>> >>>
>> >>>>
>> >>>> Thinking about it more, I think it changes the semantics only under
>> >>>> certain
>> >>>> scenarios.
>> >>>>
>> >>>> For the example SQL query shown in previous discussion, it looks the
>> >>>> same
>> >>>> semantics.
>> >>>>
>> >>>>
>> >>>> Xiao Li wrote
>> >>>> > If the join condition is non-deterministic, pushing it down to the
>> >>>> > underlying project will change the semantics. Thus, we are unable
>> to
>> >>>> do it
>> >>>> > in PullOutNondeterministic. Users can do it manually if they do not
>> >>>> care
>> >>>> > the semantics difference.
>> >>>> >
>> >>>> > Thanks,
>> >>>> >
>> >>>> > Xiao
>> >>>> >
>> >>>> >
>> >>>> >
>> >>>> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
>> >>>>
>> >>>> > baibaichen@
>> >>>>
>> >>>> > >:
>> >>>> >
>> >>>> >> It is tedious since we have lots of Hive SQL being migrated to
>> >>>> Spark.
>> >>>> >> And
>> >>>> >> this workaround is equivalent to insert a Project between Join
>> >>>> operator
>> >>>> >> and its child.
>> >>>> >>
>> >>>> >> Why not do it in PullOutNondeterministic?
>> >>>> >>
>> >>>> >> Thanks
>> >>>> >> Chang
>> >>>> >>
>> >>>> >>
>> >>>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
>> >>>>
>> >>>> > viirya@
>> >>>>
>> >>>> > > wrote:
>> >>>> >>
>> >>>> >>>
>> >>>> >>> A possible workaround is to add the rand column into tbl1 with a
>> >>>> >>> projection
>> >>>> >>> before the join.
>> >>>> >>>
>> >>>> >>> SELECT a.col1
>> >>>> >>> FROM (
>> >>>> >>> SELECT col1,
>> >>>> >>> CASE
>> >>>> >>> WHEN col2 IS NULL
>> >>>> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
>> >>>> >>> ELSE
>> >>>> >>> col2
>> >>>> >>> END AS col2
>> >>>> >>> FROM tbl1) a
>> >>>> >>> LEFT OUTER JOIN tbl2 b
>> >>>> >>> ON a.col2 = b.col3;
>> >>>> >>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>> Chang Chen wrote
>> >>>> >>> > Hi Wenchen
>> >>>> >>> >
>> >>>> >>> > Yes. We also find this error is caused by Rand. However, this
>> is
>> >>>> >>> classic
>> >>>> >>> > way to solve data skew in Hive. Is there any equivalent way in
>> >>>> Spark?
>> >>>> >>> >
>> >>>> >>> > Thanks
>> >>>> >>> > Chang
>> >>>> >>> >
>> >>>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>> >>>> >>>
>> >>>> >>> > cloud0fan@
>> >>>> >>>
>> >>>> >>> > > wrote:
>> >>>> >>> >
>> >>>> >>> >> It’s not about case when, but about rand(). Non-deterministic
>> >>>> >>> expressions
>> >>>> >>> >> are not allowed in join condition.
>> >>>> >>> >>
>> >>>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>> >>>> >>>
>> >>>> >>> > cn_wss@
>> >>>> >>>
>> >>>> >>> > > wrote:
>> >>>> >>> >> >
>> >>>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
>> >>>> >>> >> thriftserver), For
>> >>>> >>> >> > optimizing data skew, we use "case when" to handle null.
>> >>>> >>> >> > Simple sql as following:
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> > SELECT a.col1
>> >>>> >>> >> > FROM tbl1 a
>> >>>> >>> >> > LEFT OUTER JOIN tbl2 b
>> >>>> >>> >> > ON
>> >>>> >>> >> > * CASE
>> >>>> >>> >> > WHEN a.col2 IS NULL
>> >>>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999 as
>> >>>> >>> string)
>> >>>> >>> >> > ELSE
>> >>>> >>> >> > a.col2 END *
>> >>>> >>> >> > = b.col3;
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> > But I get the error:
>> >>>> >>> >> >
>> >>>> >>> >> > == Physical Plan ==
>> >>>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
>> >>>> >>> expressions
>> >>>> >>> >> are
>> >>>> >>> >> > only allowed in
>> >>>> >>> >> > Project, Filter, Aggregate or Window, found:*
>> >>>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
>> >>>> CAST(1000
>> >>>> >>> AS
>> >>>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
>> >>>> >>> a.`nav_tcdt`
>> >>>> >>> >> END
>> >>>> >>> >> =
>> >>>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>> >>>> >>> >> (c.`cur_flag`
>> >>>> >>> >> =
>> >>>> >>> >> > 1))
>> >>>> >>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
>> >>>> THEN
>> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>> >>>> >>> double))
>> >>>> >>> as
>> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>> >>>> >>> (cast(nav_tcd#26
>> >>>> >>> as
>> >>>> >>> >> int)
>> >>>> >>> >> > = 9)) && (cur_flag#77 = 1))
>> >>>> >>> >> > ;;
>> >>>> >>> >> > GlobalLimit 10
>> >>>> >>> >> > +- LocalLimit 10
>> >>>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as
>> >>>> string) IN
>> >>>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
>> >>>> string))
>> >>>> >>> &&
>> >>>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE
>> nav_tpa_id#21
>> >>>> >>> END],
>> >>>> >>> >> > [date_id#7]
>> >>>> >>> >> > +- Filter (date_id#7 = 2017-07-12)
>> >>>> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
>> >>>> THEN
>> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>> >>>> >>> double))
>> >>>> >>> as
>> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>> >>>> >>> (cast(nav_tcd#26
>> >>>> >>> as
>> >>>> >>> >> int)
>> >>>> >>> >> > = 9)) && (cur_flag#77 = 1))
>> >>>> >>> >> > :- SubqueryAlias a
>> >>>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>> >>>> >>> >> > : +- CatalogRelation
>> >>>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
>> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> >>>> [date_id#7,
>> >>>> >>> >> chanl_id#8L,
>> >>>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>> >>>> >>> >> nav_refer_page_type_id#13,
>> >>>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15,
>> >>>> nav_refer_tpa_id#16,
>> >>>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>> >>>> >>> >> nav_page_value#20,
>> >>>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24,
>> >>>> nav_tcdt#25,
>> >>>> >>> >> nav_tcd#26,
>> >>>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>> >>>> >>> >> > detl_refer_page_value#30, ... 33 more fields]
>> >>>> >>> >> > +- SubqueryAlias c
>> >>>> >>> >> > +- SubqueryAlias dim_site_categ_ext
>> >>>> >>> >> > +- CatalogRelation
>> `dw`.`dim_site_categ_ext`,
>> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> >>>> >>> >> [site_categ_skid#64L,
>> >>>> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>> >>>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69,
>> leaf_flg#70L,
>> >>>> >>> >> sort_seq#71L,
>> >>>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
>> >>>> >>> etl_batch_id#75L,
>> >>>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
>> >>>> >>> bkgrnd_categ_id#79L,
>> >>>> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
>> >>>> >>> >> >
>> >>>> >>> >> > Does spark sql not support syntax "case when" in JOIN?
>> >>>> Additional,
>> >>>> >>> my
>> >>>> >>> >> spark
>> >>>> >>> >> > version is 2.2.0.
>> >>>> >>> >> > Any help would be greatly appreciated.
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> >
>> >>>> >>> >> > --
>> >>>> >>> >> > View this message in context:
>> http://apache-spark-developers
>> >>>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>> >>>> >>> >> be-supported-in-JOIN-tp21953.html
>> >>>> >>> >> > Sent from the Apache Spark Developers List mailing list
>> >>>> archive
>> >>>> at
>> >>>> >>> >> Nabble.com.
>> >>>> >>> >> >
>> >>>> >>> >> > ------------------------------
>> ------------------------------
>> >>>> >>> ---------
>> >>>> >>> >> > To unsubscribe e-mail:
>> >>>> >>>
>> >>>> >>> > dev-unsubscribe@.apache
>> >>>> >>>
>> >>>> >>> >> >
>> >>>> >>> >>
>> >>>> >>> >>
>> >>>> >>> >> ------------------------------------------------------------
>> >>>> ---------
>> >>>> >>> >> To unsubscribe e-mail:
>> >>>> >>>
>> >>>> >>> > dev-unsubscribe@.apache
>> >>>> >>>
>> >>>> >>> >>
>> >>>> >>> >>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>> -----
>> >>>> >>> Liang-Chi Hsieh | @viirya
>> >>>> >>> Spark Technology Center
>> >>>> >>> http://www.spark.tc/
>> >>>> >>> --
>> >>>> >>> View this message in context: http://apache-spark-developers
>> >>>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>> >>>> >>> supported-in-JOIN-tp21953p21961.html
>> >>>> >>> Sent from the Apache Spark Developers List mailing list archive
>> at
>> >>>> >>> Nabble.com.
>> >>>> >>>
>> >>>> >>> ------------------------------------------------------------
>> >>>> ---------
>> >>>> >>> To unsubscribe e-mail:
>> >>>>
>> >>>> > dev-unsubscribe@.apache
>> >>>>
>> >>>> >>>
>> >>>> >>>
>> >>>> >>
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>> -----
>> >>>> Liang-Chi Hsieh | @viirya
>> >>>> Spark Technology Center
>> >>>> http://www.spark.tc/
>> >>>> --
>> >>>> View this message in context: http://apache-spark-developers
>> >>>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>> >>>> supported-in-JOIN-tp21953p21973.html
>> >>>> Sent from the Apache Spark Developers List mailing list archive at
>> >>>> Nabble.com.
>> >>>>
>> >>>> ------------------------------------------------------------
>> ---------
>> >>>> To unsubscribe e-mail:
>>
>> > dev-unsubscribe@.apache
>>
>> >>>>
>> >>>>
>> >>>
>> >>
>>
>>
>>
>>
>>
>> -----
>> Liang-Chi Hsieh | @viirya
>> Spark Technology Center
>> http://www.spark.tc/
>> --
>> View this message in context: http://apache-spark-developers
>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>> be-supported-in-JOIN-tp21953p21982.html
>> Sent from the Apache Spark Developers List mailing list archive at
>> Nabble.com.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>>
>>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Chang Chen <ba...@gmail.com>.
I see.
Actually, it isn't about evaluation order which user can't specify. It's
about how many times we evaluate the non-deterministic expression for the
same row.
For example, given the SQL:
SELECT a.col1
FROM tbl1 a
LEFT OUTER JOIN tbl2 b
ON
CASE WHEN a.col2 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string)
ELSE a.col2 END
=
CASE WHEN b.col3 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string)
ELSE b.col3 END;
I think if we exactly evaluate join key one time for each row of a and b
in the whole pipeline, even if the result isn't deterministic, but the
computation is correct.
Thanks
Chang
On Mon, Jul 17, 2017 at 10:49 PM, Liang-Chi Hsieh <vi...@gmail.com> wrote:
>
> IIUC, the evaluation order of rows in Join can be different in different
> physical operators, e.g., Sort-based and Hash-based.
>
> But for non-deterministic expressions, different evaluation orders change
> results.
>
>
>
> Chang Chen wrote
> > I see the issue. I will try https://github.com/apache/spark/pull/18652,
> I
> > think
> >
> > 1 For Join Operator, the left and right plan can't be non-deterministic.
> > 2 If Filter can support non-deterministic, why not join condition?
> > 3 We can't push down or project non-deterministic expression, since it
> may
> > change semantics.
> >
> > Actually, the real problem is #2. If the join condition could be
> > non-deterministic, then we needn't insert project.
> >
> > Thanks
> > Chang
> >
> >
> >
> >
> > On Mon, Jul 17, 2017 at 3:59 PM, 蒋星博 <
>
> > jiangxb1987@
>
> > > wrote:
> >
> >> FYI there have been a related discussion here:
> https://github.com/apache/
> >> spark/pull/15417#discussion_r85295977
> >>
> >> 2017-07-17 15:44 GMT+08:00 Chang Chen <
>
> > baibaichen@
>
> > >:
> >>
> >>> Hi All
> >>>
> >>> I don't understand the difference between the semantics, I found Spark
> >>> does the same thing for GroupBy non-deterministic. From Map-Reduce
> point
> >>> of
> >>> view, Join is also GroupBy in essence .
> >>>
> >>> @Liang Chi Hsieh
> >>> <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
> >>>
> >>> in which situation, semantics will be changed?
> >>>
> >>> Thanks
> >>> Chang
> >>>
> >>> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <
>
> > viirya@
>
> > >
> >>> wrote:
> >>>
> >>>>
> >>>> Thinking about it more, I think it changes the semantics only under
> >>>> certain
> >>>> scenarios.
> >>>>
> >>>> For the example SQL query shown in previous discussion, it looks the
> >>>> same
> >>>> semantics.
> >>>>
> >>>>
> >>>> Xiao Li wrote
> >>>> > If the join condition is non-deterministic, pushing it down to the
> >>>> > underlying project will change the semantics. Thus, we are unable to
> >>>> do it
> >>>> > in PullOutNondeterministic. Users can do it manually if they do not
> >>>> care
> >>>> > the semantics difference.
> >>>> >
> >>>> > Thanks,
> >>>> >
> >>>> > Xiao
> >>>> >
> >>>> >
> >>>> >
> >>>> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
> >>>>
> >>>> > baibaichen@
> >>>>
> >>>> > >:
> >>>> >
> >>>> >> It is tedious since we have lots of Hive SQL being migrated to
> >>>> Spark.
> >>>> >> And
> >>>> >> this workaround is equivalent to insert a Project between Join
> >>>> operator
> >>>> >> and its child.
> >>>> >>
> >>>> >> Why not do it in PullOutNondeterministic?
> >>>> >>
> >>>> >> Thanks
> >>>> >> Chang
> >>>> >>
> >>>> >>
> >>>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
> >>>>
> >>>> > viirya@
> >>>>
> >>>> > > wrote:
> >>>> >>
> >>>> >>>
> >>>> >>> A possible workaround is to add the rand column into tbl1 with a
> >>>> >>> projection
> >>>> >>> before the join.
> >>>> >>>
> >>>> >>> SELECT a.col1
> >>>> >>> FROM (
> >>>> >>> SELECT col1,
> >>>> >>> CASE
> >>>> >>> WHEN col2 IS NULL
> >>>> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
> >>>> >>> ELSE
> >>>> >>> col2
> >>>> >>> END AS col2
> >>>> >>> FROM tbl1) a
> >>>> >>> LEFT OUTER JOIN tbl2 b
> >>>> >>> ON a.col2 = b.col3;
> >>>> >>>
> >>>> >>>
> >>>> >>>
> >>>> >>> Chang Chen wrote
> >>>> >>> > Hi Wenchen
> >>>> >>> >
> >>>> >>> > Yes. We also find this error is caused by Rand. However, this is
> >>>> >>> classic
> >>>> >>> > way to solve data skew in Hive. Is there any equivalent way in
> >>>> Spark?
> >>>> >>> >
> >>>> >>> > Thanks
> >>>> >>> > Chang
> >>>> >>> >
> >>>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
> >>>> >>>
> >>>> >>> > cloud0fan@
> >>>> >>>
> >>>> >>> > > wrote:
> >>>> >>> >
> >>>> >>> >> It’s not about case when, but about rand(). Non-deterministic
> >>>> >>> expressions
> >>>> >>> >> are not allowed in join condition.
> >>>> >>> >>
> >>>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
> >>>> >>>
> >>>> >>> > cn_wss@
> >>>> >>>
> >>>> >>> > > wrote:
> >>>> >>> >> >
> >>>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
> >>>> >>> >> thriftserver), For
> >>>> >>> >> > optimizing data skew, we use "case when" to handle null.
> >>>> >>> >> > Simple sql as following:
> >>>> >>> >> >
> >>>> >>> >> >
> >>>> >>> >> > SELECT a.col1
> >>>> >>> >> > FROM tbl1 a
> >>>> >>> >> > LEFT OUTER JOIN tbl2 b
> >>>> >>> >> > ON
> >>>> >>> >> > * CASE
> >>>> >>> >> > WHEN a.col2 IS NULL
> >>>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999 as
> >>>> >>> string)
> >>>> >>> >> > ELSE
> >>>> >>> >> > a.col2 END *
> >>>> >>> >> > = b.col3;
> >>>> >>> >> >
> >>>> >>> >> >
> >>>> >>> >> > But I get the error:
> >>>> >>> >> >
> >>>> >>> >> > == Physical Plan ==
> >>>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
> >>>> >>> expressions
> >>>> >>> >> are
> >>>> >>> >> > only allowed in
> >>>> >>> >> > Project, Filter, Aggregate or Window, found:*
> >>>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
> >>>> CAST(1000
> >>>> >>> AS
> >>>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
> >>>> >>> a.`nav_tcdt`
> >>>> >>> >> END
> >>>> >>> >> =
> >>>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
> >>>> >>> >> (c.`cur_flag`
> >>>> >>> >> =
> >>>> >>> >> > 1))
> >>>> >>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
> >>>> THEN
> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
> >>>> >>> double))
> >>>> >>> as
> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
> >>>> >>> (cast(nav_tcd#26
> >>>> >>> as
> >>>> >>> >> int)
> >>>> >>> >> > = 9)) && (cur_flag#77 = 1))
> >>>> >>> >> > ;;
> >>>> >>> >> > GlobalLimit 10
> >>>> >>> >> > +- LocalLimit 10
> >>>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as
> >>>> string) IN
> >>>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
> >>>> string))
> >>>> >>> &&
> >>>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE
> nav_tpa_id#21
> >>>> >>> END],
> >>>> >>> >> > [date_id#7]
> >>>> >>> >> > +- Filter (date_id#7 = 2017-07-12)
> >>>> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
> >>>> THEN
> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
> >>>> >>> double))
> >>>> >>> as
> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
> >>>> >>> (cast(nav_tcd#26
> >>>> >>> as
> >>>> >>> >> int)
> >>>> >>> >> > = 9)) && (cur_flag#77 = 1))
> >>>> >>> >> > :- SubqueryAlias a
> >>>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
> >>>> >>> >> > : +- CatalogRelation
> >>>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> >>>> [date_id#7,
> >>>> >>> >> chanl_id#8L,
> >>>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
> >>>> >>> >> nav_refer_page_type_id#13,
> >>>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15,
> >>>> nav_refer_tpa_id#16,
> >>>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
> >>>> >>> >> nav_page_value#20,
> >>>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24,
> >>>> nav_tcdt#25,
> >>>> >>> >> nav_tcd#26,
> >>>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
> >>>> >>> >> > detl_refer_page_value#30, ... 33 more fields]
> >>>> >>> >> > +- SubqueryAlias c
> >>>> >>> >> > +- SubqueryAlias dim_site_categ_ext
> >>>> >>> >> > +- CatalogRelation
> `dw`.`dim_site_categ_ext`,
> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> >>>> >>> >> [site_categ_skid#64L,
> >>>> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
> >>>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
> >>>> >>> >> sort_seq#71L,
> >>>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
> >>>> >>> etl_batch_id#75L,
> >>>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
> >>>> >>> bkgrnd_categ_id#79L,
> >>>> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
> >>>> >>> >> >
> >>>> >>> >> > Does spark sql not support syntax "case when" in JOIN?
> >>>> Additional,
> >>>> >>> my
> >>>> >>> >> spark
> >>>> >>> >> > version is 2.2.0.
> >>>> >>> >> > Any help would be greatly appreciated.
> >>>> >>> >> >
> >>>> >>> >> >
> >>>> >>> >> >
> >>>> >>> >> >
> >>>> >>> >> > --
> >>>> >>> >> > View this message in context: http://apache-spark-developers
> >>>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
> >>>> >>> >> be-supported-in-JOIN-tp21953.html
> >>>> >>> >> > Sent from the Apache Spark Developers List mailing list
> >>>> archive
> >>>> at
> >>>> >>> >> Nabble.com.
> >>>> >>> >> >
> >>>> >>> >> > ------------------------------------------------------------
> >>>> >>> ---------
> >>>> >>> >> > To unsubscribe e-mail:
> >>>> >>>
> >>>> >>> > dev-unsubscribe@.apache
> >>>> >>>
> >>>> >>> >> >
> >>>> >>> >>
> >>>> >>> >>
> >>>> >>> >> ------------------------------------------------------------
> >>>> ---------
> >>>> >>> >> To unsubscribe e-mail:
> >>>> >>>
> >>>> >>> > dev-unsubscribe@.apache
> >>>> >>>
> >>>> >>> >>
> >>>> >>> >>
> >>>> >>>
> >>>> >>>
> >>>> >>>
> >>>> >>>
> >>>> >>>
> >>>> >>> -----
> >>>> >>> Liang-Chi Hsieh | @viirya
> >>>> >>> Spark Technology Center
> >>>> >>> http://www.spark.tc/
> >>>> >>> --
> >>>> >>> View this message in context: http://apache-spark-developers
> >>>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
> >>>> >>> supported-in-JOIN-tp21953p21961.html
> >>>> >>> Sent from the Apache Spark Developers List mailing list archive at
> >>>> >>> Nabble.com.
> >>>> >>>
> >>>> >>> ------------------------------------------------------------
> >>>> ---------
> >>>> >>> To unsubscribe e-mail:
> >>>>
> >>>> > dev-unsubscribe@.apache
> >>>>
> >>>> >>>
> >>>> >>>
> >>>> >>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> -----
> >>>> Liang-Chi Hsieh | @viirya
> >>>> Spark Technology Center
> >>>> http://www.spark.tc/
> >>>> --
> >>>> View this message in context: http://apache-spark-developers
> >>>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
> >>>> supported-in-JOIN-tp21953p21973.html
> >>>> Sent from the Apache Spark Developers List mailing list archive at
> >>>> Nabble.com.
> >>>>
> >>>> ---------------------------------------------------------------------
> >>>> To unsubscribe e-mail:
>
> > dev-unsubscribe@.apache
>
> >>>>
> >>>>
> >>>
> >>
>
>
>
>
>
> -----
> Liang-Chi Hsieh | @viirya
> Spark Technology Center
> http://www.spark.tc/
> --
> View this message in context: http://apache-spark-
> developers-list.1001551.n3.nabble.com/SQL-Syntax-case-
> when-doesn-t-be-supported-in-JOIN-tp21953p21982.html
> Sent from the Apache Spark Developers List mailing list archive at
> Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Liang-Chi Hsieh <vi...@gmail.com>.
IIUC, the evaluation order of rows in Join can be different in different
physical operators, e.g., Sort-based and Hash-based.
But for non-deterministic expressions, different evaluation orders change
results.
Chang Chen wrote
> I see the issue. I will try https://github.com/apache/spark/pull/18652, I
> think
>
> 1 For Join Operator, the left and right plan can't be non-deterministic.
> 2 If Filter can support non-deterministic, why not join condition?
> 3 We can't push down or project non-deterministic expression, since it may
> change semantics.
>
> Actually, the real problem is #2. If the join condition could be
> non-deterministic, then we needn't insert project.
>
> Thanks
> Chang
>
>
>
>
> On Mon, Jul 17, 2017 at 3:59 PM, 蒋星博 <
> jiangxb1987@
> > wrote:
>
>> FYI there have been a related discussion here: https://github.com/apache/
>> spark/pull/15417#discussion_r85295977
>>
>> 2017-07-17 15:44 GMT+08:00 Chang Chen <
> baibaichen@
> >:
>>
>>> Hi All
>>>
>>> I don't understand the difference between the semantics, I found Spark
>>> does the same thing for GroupBy non-deterministic. From Map-Reduce point
>>> of
>>> view, Join is also GroupBy in essence .
>>>
>>> @Liang Chi Hsieh
>>> <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
>>>
>>> in which situation, semantics will be changed?
>>>
>>> Thanks
>>> Chang
>>>
>>> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <
> viirya@
> >
>>> wrote:
>>>
>>>>
>>>> Thinking about it more, I think it changes the semantics only under
>>>> certain
>>>> scenarios.
>>>>
>>>> For the example SQL query shown in previous discussion, it looks the
>>>> same
>>>> semantics.
>>>>
>>>>
>>>> Xiao Li wrote
>>>> > If the join condition is non-deterministic, pushing it down to the
>>>> > underlying project will change the semantics. Thus, we are unable to
>>>> do it
>>>> > in PullOutNondeterministic. Users can do it manually if they do not
>>>> care
>>>> > the semantics difference.
>>>> >
>>>> > Thanks,
>>>> >
>>>> > Xiao
>>>> >
>>>> >
>>>> >
>>>> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
>>>>
>>>> > baibaichen@
>>>>
>>>> > >:
>>>> >
>>>> >> It is tedious since we have lots of Hive SQL being migrated to
>>>> Spark.
>>>> >> And
>>>> >> this workaround is equivalent to insert a Project between Join
>>>> operator
>>>> >> and its child.
>>>> >>
>>>> >> Why not do it in PullOutNondeterministic?
>>>> >>
>>>> >> Thanks
>>>> >> Chang
>>>> >>
>>>> >>
>>>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
>>>>
>>>> > viirya@
>>>>
>>>> > > wrote:
>>>> >>
>>>> >>>
>>>> >>> A possible workaround is to add the rand column into tbl1 with a
>>>> >>> projection
>>>> >>> before the join.
>>>> >>>
>>>> >>> SELECT a.col1
>>>> >>> FROM (
>>>> >>> SELECT col1,
>>>> >>> CASE
>>>> >>> WHEN col2 IS NULL
>>>> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
>>>> >>> ELSE
>>>> >>> col2
>>>> >>> END AS col2
>>>> >>> FROM tbl1) a
>>>> >>> LEFT OUTER JOIN tbl2 b
>>>> >>> ON a.col2 = b.col3;
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>> Chang Chen wrote
>>>> >>> > Hi Wenchen
>>>> >>> >
>>>> >>> > Yes. We also find this error is caused by Rand. However, this is
>>>> >>> classic
>>>> >>> > way to solve data skew in Hive. Is there any equivalent way in
>>>> Spark?
>>>> >>> >
>>>> >>> > Thanks
>>>> >>> > Chang
>>>> >>> >
>>>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>>>> >>>
>>>> >>> > cloud0fan@
>>>> >>>
>>>> >>> > > wrote:
>>>> >>> >
>>>> >>> >> It’s not about case when, but about rand(). Non-deterministic
>>>> >>> expressions
>>>> >>> >> are not allowed in join condition.
>>>> >>> >>
>>>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>>>> >>>
>>>> >>> > cn_wss@
>>>> >>>
>>>> >>> > > wrote:
>>>> >>> >> >
>>>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
>>>> >>> >> thriftserver), For
>>>> >>> >> > optimizing data skew, we use "case when" to handle null.
>>>> >>> >> > Simple sql as following:
>>>> >>> >> >
>>>> >>> >> >
>>>> >>> >> > SELECT a.col1
>>>> >>> >> > FROM tbl1 a
>>>> >>> >> > LEFT OUTER JOIN tbl2 b
>>>> >>> >> > ON
>>>> >>> >> > * CASE
>>>> >>> >> > WHEN a.col2 IS NULL
>>>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999 as
>>>> >>> string)
>>>> >>> >> > ELSE
>>>> >>> >> > a.col2 END *
>>>> >>> >> > = b.col3;
>>>> >>> >> >
>>>> >>> >> >
>>>> >>> >> > But I get the error:
>>>> >>> >> >
>>>> >>> >> > == Physical Plan ==
>>>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
>>>> >>> expressions
>>>> >>> >> are
>>>> >>> >> > only allowed in
>>>> >>> >> > Project, Filter, Aggregate or Window, found:*
>>>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
>>>> CAST(1000
>>>> >>> AS
>>>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
>>>> >>> a.`nav_tcdt`
>>>> >>> >> END
>>>> >>> >> =
>>>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>>>> >>> >> (c.`cur_flag`
>>>> >>> >> =
>>>> >>> >> > 1))
>>>> >>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
>>>> THEN
>>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>>>> >>> double))
>>>> >>> as
>>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>>>> >>> (cast(nav_tcd#26
>>>> >>> as
>>>> >>> >> int)
>>>> >>> >> > = 9)) && (cur_flag#77 = 1))
>>>> >>> >> > ;;
>>>> >>> >> > GlobalLimit 10
>>>> >>> >> > +- LocalLimit 10
>>>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as
>>>> string) IN
>>>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
>>>> string))
>>>> >>> &&
>>>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21
>>>> >>> END],
>>>> >>> >> > [date_id#7]
>>>> >>> >> > +- Filter (date_id#7 = 2017-07-12)
>>>> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
>>>> THEN
>>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>>>> >>> double))
>>>> >>> as
>>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>>>> >>> (cast(nav_tcd#26
>>>> >>> as
>>>> >>> >> int)
>>>> >>> >> > = 9)) && (cur_flag#77 = 1))
>>>> >>> >> > :- SubqueryAlias a
>>>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>>>> >>> >> > : +- CatalogRelation
>>>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
>>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>>>> [date_id#7,
>>>> >>> >> chanl_id#8L,
>>>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>>>> >>> >> nav_refer_page_type_id#13,
>>>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15,
>>>> nav_refer_tpa_id#16,
>>>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>>>> >>> >> nav_page_value#20,
>>>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24,
>>>> nav_tcdt#25,
>>>> >>> >> nav_tcd#26,
>>>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>>>> >>> >> > detl_refer_page_value#30, ... 33 more fields]
>>>> >>> >> > +- SubqueryAlias c
>>>> >>> >> > +- SubqueryAlias dim_site_categ_ext
>>>> >>> >> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
>>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>>>> >>> >> [site_categ_skid#64L,
>>>> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>>>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
>>>> >>> >> sort_seq#71L,
>>>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
>>>> >>> etl_batch_id#75L,
>>>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
>>>> >>> bkgrnd_categ_id#79L,
>>>> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
>>>> >>> >> >
>>>> >>> >> > Does spark sql not support syntax "case when" in JOIN?
>>>> Additional,
>>>> >>> my
>>>> >>> >> spark
>>>> >>> >> > version is 2.2.0.
>>>> >>> >> > Any help would be greatly appreciated.
>>>> >>> >> >
>>>> >>> >> >
>>>> >>> >> >
>>>> >>> >> >
>>>> >>> >> > --
>>>> >>> >> > View this message in context: http://apache-spark-developers
>>>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>>>> >>> >> be-supported-in-JOIN-tp21953.html
>>>> >>> >> > Sent from the Apache Spark Developers List mailing list
>>>> archive
>>>> at
>>>> >>> >> Nabble.com.
>>>> >>> >> >
>>>> >>> >> > ------------------------------------------------------------
>>>> >>> ---------
>>>> >>> >> > To unsubscribe e-mail:
>>>> >>>
>>>> >>> > dev-unsubscribe@.apache
>>>> >>>
>>>> >>> >> >
>>>> >>> >>
>>>> >>> >>
>>>> >>> >> ------------------------------------------------------------
>>>> ---------
>>>> >>> >> To unsubscribe e-mail:
>>>> >>>
>>>> >>> > dev-unsubscribe@.apache
>>>> >>>
>>>> >>> >>
>>>> >>> >>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>>
>>>> >>> -----
>>>> >>> Liang-Chi Hsieh | @viirya
>>>> >>> Spark Technology Center
>>>> >>> http://www.spark.tc/
>>>> >>> --
>>>> >>> View this message in context: http://apache-spark-developers
>>>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>>>> >>> supported-in-JOIN-tp21953p21961.html
>>>> >>> Sent from the Apache Spark Developers List mailing list archive at
>>>> >>> Nabble.com.
>>>> >>>
>>>> >>> ------------------------------------------------------------
>>>> ---------
>>>> >>> To unsubscribe e-mail:
>>>>
>>>> > dev-unsubscribe@.apache
>>>>
>>>> >>>
>>>> >>>
>>>> >>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> -----
>>>> Liang-Chi Hsieh | @viirya
>>>> Spark Technology Center
>>>> http://www.spark.tc/
>>>> --
>>>> View this message in context: http://apache-spark-developers
>>>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>>>> supported-in-JOIN-tp21953p21973.html
>>>> Sent from the Apache Spark Developers List mailing list archive at
>>>> Nabble.com.
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe e-mail:
> dev-unsubscribe@.apache
>>>>
>>>>
>>>
>>
-----
Liang-Chi Hsieh | @viirya
Spark Technology Center
http://www.spark.tc/
--
View this message in context: http://apache-spark-developers-list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-supported-in-JOIN-tp21953p21982.html
Sent from the Apache Spark Developers List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Chang Chen <ba...@gmail.com>.
I see the issue. I will try https://github.com/apache/spark/pull/18652, I
think
1 For Join Operator, the left and right plan can't be non-deterministic.
2 If Filter can support non-deterministic, why not join condition?
3 We can't push down or project non-deterministic expression, since it may
change semantics.
Actually, the real problem is #2. If the join condition could be
non-deterministic, then we needn't insert project.
Thanks
Chang
On Mon, Jul 17, 2017 at 3:59 PM, 蒋星博 <ji...@gmail.com> wrote:
> FYI there have been a related discussion here: https://github.com/apache/
> spark/pull/15417#discussion_r85295977
>
> 2017-07-17 15:44 GMT+08:00 Chang Chen <ba...@gmail.com>:
>
>> Hi All
>>
>> I don't understand the difference between the semantics, I found Spark
>> does the same thing for GroupBy non-deterministic. From Map-Reduce point of
>> view, Join is also GroupBy in essence .
>>
>> @Liang Chi Hsieh
>> <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
>>
>> in which situation, semantics will be changed?
>>
>> Thanks
>> Chang
>>
>> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <vi...@gmail.com>
>> wrote:
>>
>>>
>>> Thinking about it more, I think it changes the semantics only under
>>> certain
>>> scenarios.
>>>
>>> For the example SQL query shown in previous discussion, it looks the same
>>> semantics.
>>>
>>>
>>> Xiao Li wrote
>>> > If the join condition is non-deterministic, pushing it down to the
>>> > underlying project will change the semantics. Thus, we are unable to
>>> do it
>>> > in PullOutNondeterministic. Users can do it manually if they do not
>>> care
>>> > the semantics difference.
>>> >
>>> > Thanks,
>>> >
>>> > Xiao
>>> >
>>> >
>>> >
>>> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
>>>
>>> > baibaichen@
>>>
>>> > >:
>>> >
>>> >> It is tedious since we have lots of Hive SQL being migrated to Spark.
>>> >> And
>>> >> this workaround is equivalent to insert a Project between Join
>>> operator
>>> >> and its child.
>>> >>
>>> >> Why not do it in PullOutNondeterministic?
>>> >>
>>> >> Thanks
>>> >> Chang
>>> >>
>>> >>
>>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
>>>
>>> > viirya@
>>>
>>> > > wrote:
>>> >>
>>> >>>
>>> >>> A possible workaround is to add the rand column into tbl1 with a
>>> >>> projection
>>> >>> before the join.
>>> >>>
>>> >>> SELECT a.col1
>>> >>> FROM (
>>> >>> SELECT col1,
>>> >>> CASE
>>> >>> WHEN col2 IS NULL
>>> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
>>> >>> ELSE
>>> >>> col2
>>> >>> END AS col2
>>> >>> FROM tbl1) a
>>> >>> LEFT OUTER JOIN tbl2 b
>>> >>> ON a.col2 = b.col3;
>>> >>>
>>> >>>
>>> >>>
>>> >>> Chang Chen wrote
>>> >>> > Hi Wenchen
>>> >>> >
>>> >>> > Yes. We also find this error is caused by Rand. However, this is
>>> >>> classic
>>> >>> > way to solve data skew in Hive. Is there any equivalent way in
>>> Spark?
>>> >>> >
>>> >>> > Thanks
>>> >>> > Chang
>>> >>> >
>>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>>> >>>
>>> >>> > cloud0fan@
>>> >>>
>>> >>> > > wrote:
>>> >>> >
>>> >>> >> It’s not about case when, but about rand(). Non-deterministic
>>> >>> expressions
>>> >>> >> are not allowed in join condition.
>>> >>> >>
>>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>>> >>>
>>> >>> > cn_wss@
>>> >>>
>>> >>> > > wrote:
>>> >>> >> >
>>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
>>> >>> >> thriftserver), For
>>> >>> >> > optimizing data skew, we use "case when" to handle null.
>>> >>> >> > Simple sql as following:
>>> >>> >> >
>>> >>> >> >
>>> >>> >> > SELECT a.col1
>>> >>> >> > FROM tbl1 a
>>> >>> >> > LEFT OUTER JOIN tbl2 b
>>> >>> >> > ON
>>> >>> >> > * CASE
>>> >>> >> > WHEN a.col2 IS NULL
>>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999 as
>>> >>> string)
>>> >>> >> > ELSE
>>> >>> >> > a.col2 END *
>>> >>> >> > = b.col3;
>>> >>> >> >
>>> >>> >> >
>>> >>> >> > But I get the error:
>>> >>> >> >
>>> >>> >> > == Physical Plan ==
>>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
>>> >>> expressions
>>> >>> >> are
>>> >>> >> > only allowed in
>>> >>> >> > Project, Filter, Aggregate or Window, found:*
>>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
>>> CAST(1000
>>> >>> AS
>>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
>>> >>> a.`nav_tcdt`
>>> >>> >> END
>>> >>> >> =
>>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>>> >>> >> (c.`cur_flag`
>>> >>> >> =
>>> >>> >> > 1))
>>> >>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
>>> THEN
>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>>> >>> double))
>>> >>> as
>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>>> >>> (cast(nav_tcd#26
>>> >>> as
>>> >>> >> int)
>>> >>> >> > = 9)) && (cur_flag#77 = 1))
>>> >>> >> > ;;
>>> >>> >> > GlobalLimit 10
>>> >>> >> > +- LocalLimit 10
>>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as
>>> string) IN
>>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
>>> string))
>>> >>> &&
>>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21
>>> >>> END],
>>> >>> >> > [date_id#7]
>>> >>> >> > +- Filter (date_id#7 = 2017-07-12)
>>> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>>> >>> double))
>>> >>> as
>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>>> >>> (cast(nav_tcd#26
>>> >>> as
>>> >>> >> int)
>>> >>> >> > = 9)) && (cur_flag#77 = 1))
>>> >>> >> > :- SubqueryAlias a
>>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>>> >>> >> > : +- CatalogRelation
>>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
>>> >>> >> chanl_id#8L,
>>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>>> >>> >> nav_refer_page_type_id#13,
>>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
>>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>>> >>> >> nav_page_value#20,
>>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
>>> >>> >> nav_tcd#26,
>>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>>> >>> >> > detl_refer_page_value#30, ... 33 more fields]
>>> >>> >> > +- SubqueryAlias c
>>> >>> >> > +- SubqueryAlias dim_site_categ_ext
>>> >>> >> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>>> >>> >> [site_categ_skid#64L,
>>> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
>>> >>> >> sort_seq#71L,
>>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
>>> >>> etl_batch_id#75L,
>>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
>>> >>> bkgrnd_categ_id#79L,
>>> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
>>> >>> >> >
>>> >>> >> > Does spark sql not support syntax "case when" in JOIN?
>>> Additional,
>>> >>> my
>>> >>> >> spark
>>> >>> >> > version is 2.2.0.
>>> >>> >> > Any help would be greatly appreciated.
>>> >>> >> >
>>> >>> >> >
>>> >>> >> >
>>> >>> >> >
>>> >>> >> > --
>>> >>> >> > View this message in context: http://apache-spark-developers
>>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>>> >>> >> be-supported-in-JOIN-tp21953.html
>>> >>> >> > Sent from the Apache Spark Developers List mailing list archive
>>> at
>>> >>> >> Nabble.com.
>>> >>> >> >
>>> >>> >> > ------------------------------------------------------------
>>> >>> ---------
>>> >>> >> > To unsubscribe e-mail:
>>> >>>
>>> >>> > dev-unsubscribe@.apache
>>> >>>
>>> >>> >> >
>>> >>> >>
>>> >>> >>
>>> >>> >> ------------------------------------------------------------
>>> ---------
>>> >>> >> To unsubscribe e-mail:
>>> >>>
>>> >>> > dev-unsubscribe@.apache
>>> >>>
>>> >>> >>
>>> >>> >>
>>> >>>
>>> >>>
>>> >>>
>>> >>>
>>> >>>
>>> >>> -----
>>> >>> Liang-Chi Hsieh | @viirya
>>> >>> Spark Technology Center
>>> >>> http://www.spark.tc/
>>> >>> --
>>> >>> View this message in context: http://apache-spark-developers
>>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>>> >>> supported-in-JOIN-tp21953p21961.html
>>> >>> Sent from the Apache Spark Developers List mailing list archive at
>>> >>> Nabble.com.
>>> >>>
>>> >>> ------------------------------------------------------------
>>> ---------
>>> >>> To unsubscribe e-mail:
>>>
>>> > dev-unsubscribe@.apache
>>>
>>> >>>
>>> >>>
>>> >>
>>>
>>>
>>>
>>>
>>>
>>> -----
>>> Liang-Chi Hsieh | @viirya
>>> Spark Technology Center
>>> http://www.spark.tc/
>>> --
>>> View this message in context: http://apache-spark-developers
>>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>>> supported-in-JOIN-tp21953p21973.html
>>> Sent from the Apache Spark Developers List mailing list archive at
>>> Nabble.com.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>>>
>>>
>>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by 蒋星博 <ji...@gmail.com>.
FYI there have been a related discussion here:
https://github.com/apache/spark/pull/15417#discussion_r85295977
2017-07-17 15:44 GMT+08:00 Chang Chen <ba...@gmail.com>:
> Hi All
>
> I don't understand the difference between the semantics, I found Spark
> does the same thing for GroupBy non-deterministic. From Map-Reduce point of
> view, Join is also GroupBy in essence .
>
> @Liang Chi Hsieh
> <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
>
> in which situation, semantics will be changed?
>
> Thanks
> Chang
>
> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <vi...@gmail.com> wrote:
>
>>
>> Thinking about it more, I think it changes the semantics only under
>> certain
>> scenarios.
>>
>> For the example SQL query shown in previous discussion, it looks the same
>> semantics.
>>
>>
>> Xiao Li wrote
>> > If the join condition is non-deterministic, pushing it down to the
>> > underlying project will change the semantics. Thus, we are unable to do
>> it
>> > in PullOutNondeterministic. Users can do it manually if they do not care
>> > the semantics difference.
>> >
>> > Thanks,
>> >
>> > Xiao
>> >
>> >
>> >
>> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
>>
>> > baibaichen@
>>
>> > >:
>> >
>> >> It is tedious since we have lots of Hive SQL being migrated to Spark.
>> >> And
>> >> this workaround is equivalent to insert a Project between Join
>> operator
>> >> and its child.
>> >>
>> >> Why not do it in PullOutNondeterministic?
>> >>
>> >> Thanks
>> >> Chang
>> >>
>> >>
>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
>>
>> > viirya@
>>
>> > > wrote:
>> >>
>> >>>
>> >>> A possible workaround is to add the rand column into tbl1 with a
>> >>> projection
>> >>> before the join.
>> >>>
>> >>> SELECT a.col1
>> >>> FROM (
>> >>> SELECT col1,
>> >>> CASE
>> >>> WHEN col2 IS NULL
>> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
>> >>> ELSE
>> >>> col2
>> >>> END AS col2
>> >>> FROM tbl1) a
>> >>> LEFT OUTER JOIN tbl2 b
>> >>> ON a.col2 = b.col3;
>> >>>
>> >>>
>> >>>
>> >>> Chang Chen wrote
>> >>> > Hi Wenchen
>> >>> >
>> >>> > Yes. We also find this error is caused by Rand. However, this is
>> >>> classic
>> >>> > way to solve data skew in Hive. Is there any equivalent way in
>> Spark?
>> >>> >
>> >>> > Thanks
>> >>> > Chang
>> >>> >
>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>> >>>
>> >>> > cloud0fan@
>> >>>
>> >>> > > wrote:
>> >>> >
>> >>> >> It’s not about case when, but about rand(). Non-deterministic
>> >>> expressions
>> >>> >> are not allowed in join condition.
>> >>> >>
>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>> >>>
>> >>> > cn_wss@
>> >>>
>> >>> > > wrote:
>> >>> >> >
>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
>> >>> >> thriftserver), For
>> >>> >> > optimizing data skew, we use "case when" to handle null.
>> >>> >> > Simple sql as following:
>> >>> >> >
>> >>> >> >
>> >>> >> > SELECT a.col1
>> >>> >> > FROM tbl1 a
>> >>> >> > LEFT OUTER JOIN tbl2 b
>> >>> >> > ON
>> >>> >> > * CASE
>> >>> >> > WHEN a.col2 IS NULL
>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999 as
>> >>> string)
>> >>> >> > ELSE
>> >>> >> > a.col2 END *
>> >>> >> > = b.col3;
>> >>> >> >
>> >>> >> >
>> >>> >> > But I get the error:
>> >>> >> >
>> >>> >> > == Physical Plan ==
>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
>> >>> expressions
>> >>> >> are
>> >>> >> > only allowed in
>> >>> >> > Project, Filter, Aggregate or Window, found:*
>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
>> CAST(1000
>> >>> AS
>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
>> >>> a.`nav_tcdt`
>> >>> >> END
>> >>> >> =
>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>> >>> >> (c.`cur_flag`
>> >>> >> =
>> >>> >> > 1))
>> >>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>> >>> double))
>> >>> as
>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>> >>> (cast(nav_tcd#26
>> >>> as
>> >>> >> int)
>> >>> >> > = 9)) && (cur_flag#77 = 1))
>> >>> >> > ;;
>> >>> >> > GlobalLimit 10
>> >>> >> > +- LocalLimit 10
>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string)
>> IN
>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
>> string))
>> >>> &&
>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21
>> >>> END],
>> >>> >> > [date_id#7]
>> >>> >> > +- Filter (date_id#7 = 2017-07-12)
>> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>> >>> double))
>> >>> as
>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>> >>> (cast(nav_tcd#26
>> >>> as
>> >>> >> int)
>> >>> >> > = 9)) && (cur_flag#77 = 1))
>> >>> >> > :- SubqueryAlias a
>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>> >>> >> > : +- CatalogRelation
>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
>> >>> >> chanl_id#8L,
>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>> >>> >> nav_refer_page_type_id#13,
>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>> >>> >> nav_page_value#20,
>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
>> >>> >> nav_tcd#26,
>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>> >>> >> > detl_refer_page_value#30, ... 33 more fields]
>> >>> >> > +- SubqueryAlias c
>> >>> >> > +- SubqueryAlias dim_site_categ_ext
>> >>> >> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> >>> >> [site_categ_skid#64L,
>> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
>> >>> >> sort_seq#71L,
>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
>> >>> etl_batch_id#75L,
>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
>> >>> bkgrnd_categ_id#79L,
>> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
>> >>> >> >
>> >>> >> > Does spark sql not support syntax "case when" in JOIN?
>> Additional,
>> >>> my
>> >>> >> spark
>> >>> >> > version is 2.2.0.
>> >>> >> > Any help would be greatly appreciated.
>> >>> >> >
>> >>> >> >
>> >>> >> >
>> >>> >> >
>> >>> >> > --
>> >>> >> > View this message in context: http://apache-spark-developers
>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>> >>> >> be-supported-in-JOIN-tp21953.html
>> >>> >> > Sent from the Apache Spark Developers List mailing list archive
>> at
>> >>> >> Nabble.com.
>> >>> >> >
>> >>> >> > ------------------------------------------------------------
>> >>> ---------
>> >>> >> > To unsubscribe e-mail:
>> >>>
>> >>> > dev-unsubscribe@.apache
>> >>>
>> >>> >> >
>> >>> >>
>> >>> >>
>> >>> >> ------------------------------------------------------------
>> ---------
>> >>> >> To unsubscribe e-mail:
>> >>>
>> >>> > dev-unsubscribe@.apache
>> >>>
>> >>> >>
>> >>> >>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> -----
>> >>> Liang-Chi Hsieh | @viirya
>> >>> Spark Technology Center
>> >>> http://www.spark.tc/
>> >>> --
>> >>> View this message in context: http://apache-spark-developers
>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>> >>> supported-in-JOIN-tp21953p21961.html
>> >>> Sent from the Apache Spark Developers List mailing list archive at
>> >>> Nabble.com.
>> >>>
>> >>> ---------------------------------------------------------------------
>> >>> To unsubscribe e-mail:
>>
>> > dev-unsubscribe@.apache
>>
>> >>>
>> >>>
>> >>
>>
>>
>>
>>
>>
>> -----
>> Liang-Chi Hsieh | @viirya
>> Spark Technology Center
>> http://www.spark.tc/
>> --
>> View this message in context: http://apache-spark-developers
>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>> be-supported-in-JOIN-tp21953p21973.html
>> Sent from the Apache Spark Developers List mailing list archive at
>> Nabble.com.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>>
>>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Liang-Chi Hsieh <vi...@gmail.com>.
I created a draft pull request for explaining the cases:
https://github.com/apache/spark/pull/18652
Chang Chen wrote
> Hi All
>
> I don't understand the difference between the semantics, I found Spark
> does
> the same thing for GroupBy non-deterministic. From Map-Reduce point of
> view, Join is also GroupBy in essence .
>
> @Liang Chi Hsieh
> <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
>
> in which situation, semantics will be changed?
>
> Thanks
> Chang
>
> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <
> viirya@
> > wrote:
>
>>
>> Thinking about it more, I think it changes the semantics only under
>> certain
>> scenarios.
>>
>> For the example SQL query shown in previous discussion, it looks the same
>> semantics.
>>
>>
>> Xiao Li wrote
>> > If the join condition is non-deterministic, pushing it down to the
>> > underlying project will change the semantics. Thus, we are unable to do
>> it
>> > in PullOutNondeterministic. Users can do it manually if they do not
>> care
>> > the semantics difference.
>> >
>> > Thanks,
>> >
>> > Xiao
>> >
>> >
>> >
>> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
>>
>> > baibaichen@
>>
>> > >:
>> >
>> >> It is tedious since we have lots of Hive SQL being migrated to Spark.
>> >> And
>> >> this workaround is equivalent to insert a Project between Join
>> operator
>> >> and its child.
>> >>
>> >> Why not do it in PullOutNondeterministic?
>> >>
>> >> Thanks
>> >> Chang
>> >>
>> >>
>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
>>
>> > viirya@
>>
>> > > wrote:
>> >>
>> >>>
>> >>> A possible workaround is to add the rand column into tbl1 with a
>> >>> projection
>> >>> before the join.
>> >>>
>> >>> SELECT a.col1
>> >>> FROM (
>> >>> SELECT col1,
>> >>> CASE
>> >>> WHEN col2 IS NULL
>> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
>> >>> ELSE
>> >>> col2
>> >>> END AS col2
>> >>> FROM tbl1) a
>> >>> LEFT OUTER JOIN tbl2 b
>> >>> ON a.col2 = b.col3;
>> >>>
>> >>>
>> >>>
>> >>> Chang Chen wrote
>> >>> > Hi Wenchen
>> >>> >
>> >>> > Yes. We also find this error is caused by Rand. However, this is
>> >>> classic
>> >>> > way to solve data skew in Hive. Is there any equivalent way in
>> Spark?
>> >>> >
>> >>> > Thanks
>> >>> > Chang
>> >>> >
>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>> >>>
>> >>> > cloud0fan@
>> >>>
>> >>> > > wrote:
>> >>> >
>> >>> >> It’s not about case when, but about rand(). Non-deterministic
>> >>> expressions
>> >>> >> are not allowed in join condition.
>> >>> >>
>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>> >>>
>> >>> > cn_wss@
>> >>>
>> >>> > > wrote:
>> >>> >> >
>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
>> >>> >> thriftserver), For
>> >>> >> > optimizing data skew, we use "case when" to handle null.
>> >>> >> > Simple sql as following:
>> >>> >> >
>> >>> >> >
>> >>> >> > SELECT a.col1
>> >>> >> > FROM tbl1 a
>> >>> >> > LEFT OUTER JOIN tbl2 b
>> >>> >> > ON
>> >>> >> > * CASE
>> >>> >> > WHEN a.col2 IS NULL
>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999 as
>> >>> string)
>> >>> >> > ELSE
>> >>> >> > a.col2 END *
>> >>> >> > = b.col3;
>> >>> >> >
>> >>> >> >
>> >>> >> > But I get the error:
>> >>> >> >
>> >>> >> > == Physical Plan ==
>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
>> >>> expressions
>> >>> >> are
>> >>> >> > only allowed in
>> >>> >> > Project, Filter, Aggregate or Window, found:*
>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
>> CAST(1000
>> >>> AS
>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
>> >>> a.`nav_tcdt`
>> >>> >> END
>> >>> >> =
>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>> >>> >> (c.`cur_flag`
>> >>> >> =
>> >>> >> > 1))
>> >>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25)
>> THEN
>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>> >>> double))
>> >>> as
>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>> >>> (cast(nav_tcd#26
>> >>> as
>> >>> >> int)
>> >>> >> > = 9)) && (cur_flag#77 = 1))
>> >>> >> > ;;
>> >>> >> > GlobalLimit 10
>> >>> >> > +- LocalLimit 10
>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string)
>> IN
>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
>> string))
>> >>> &&
>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21
>> >>> END],
>> >>> >> > [date_id#7]
>> >>> >> > +- Filter (date_id#7 = 2017-07-12)
>> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>> >>> double))
>> >>> as
>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>> >>> (cast(nav_tcd#26
>> >>> as
>> >>> >> int)
>> >>> >> > = 9)) && (cur_flag#77 = 1))
>> >>> >> > :- SubqueryAlias a
>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>> >>> >> > : +- CatalogRelation
>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
>> >>> >> chanl_id#8L,
>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>> >>> >> nav_refer_page_type_id#13,
>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>> >>> >> nav_page_value#20,
>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
>> >>> >> nav_tcd#26,
>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>> >>> >> > detl_refer_page_value#30, ... 33 more fields]
>> >>> >> > +- SubqueryAlias c
>> >>> >> > +- SubqueryAlias dim_site_categ_ext
>> >>> >> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> >>> >> [site_categ_skid#64L,
>> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
>> >>> >> sort_seq#71L,
>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
>> >>> etl_batch_id#75L,
>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
>> >>> bkgrnd_categ_id#79L,
>> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
>> >>> >> >
>> >>> >> > Does spark sql not support syntax "case when" in JOIN?
>> Additional,
>> >>> my
>> >>> >> spark
>> >>> >> > version is 2.2.0.
>> >>> >> > Any help would be greatly appreciated.
>> >>> >> >
>> >>> >> >
>> >>> >> >
>> >>> >> >
>> >>> >> > --
>> >>> >> > View this message in context: http://apache-spark-developers
>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>> >>> >> be-supported-in-JOIN-tp21953.html
>> >>> >> > Sent from the Apache Spark Developers List mailing list archive
>> at
>> >>> >> Nabble.com.
>> >>> >> >
>> >>> >> > ------------------------------------------------------------
>> >>> ---------
>> >>> >> > To unsubscribe e-mail:
>> >>>
>> >>> > dev-unsubscribe@.apache
>> >>>
>> >>> >> >
>> >>> >>
>> >>> >>
>> >>> >> ------------------------------------------------------------
>> ---------
>> >>> >> To unsubscribe e-mail:
>> >>>
>> >>> > dev-unsubscribe@.apache
>> >>>
>> >>> >>
>> >>> >>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> -----
>> >>> Liang-Chi Hsieh | @viirya
>> >>> Spark Technology Center
>> >>> http://www.spark.tc/
>> >>> --
>> >>> View this message in context: http://apache-spark-developers
>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>> >>> supported-in-JOIN-tp21953p21961.html
>> >>> Sent from the Apache Spark Developers List mailing list archive at
>> >>> Nabble.com.
>> >>>
>> >>> ---------------------------------------------------------------------
>> >>> To unsubscribe e-mail:
>>
>> > dev-unsubscribe@.apache
>>
>> >>>
>> >>>
>> >>
>>
>>
>>
>>
>>
>> -----
>> Liang-Chi Hsieh | @viirya
>> Spark Technology Center
>> http://www.spark.tc/
>> --
>> View this message in context: http://apache-spark-
>> developers-list.1001551.n3.nabble.com/SQL-Syntax-case-
>> when-doesn-t-be-supported-in-JOIN-tp21953p21973.html
>> Sent from the Apache Spark Developers List mailing list archive at
>> Nabble.com.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe e-mail:
> dev-unsubscribe@.apache
>>
>>
-----
Liang-Chi Hsieh | @viirya
Spark Technology Center
http://www.spark.tc/
--
View this message in context: http://apache-spark-developers-list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-supported-in-JOIN-tp21953p21976.html
Sent from the Apache Spark Developers List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Chang Chen <ba...@gmail.com>.
Hi All
I don't understand the difference between the semantics, I found Spark does
the same thing for GroupBy non-deterministic. From Map-Reduce point of
view, Join is also GroupBy in essence .
@Liang Chi Hsieh <https://plus.google.com/u/0/103179362592085650735?prsrc=4>
in which situation, semantics will be changed?
Thanks
Chang
On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh <vi...@gmail.com> wrote:
>
> Thinking about it more, I think it changes the semantics only under certain
> scenarios.
>
> For the example SQL query shown in previous discussion, it looks the same
> semantics.
>
>
> Xiao Li wrote
> > If the join condition is non-deterministic, pushing it down to the
> > underlying project will change the semantics. Thus, we are unable to do
> it
> > in PullOutNondeterministic. Users can do it manually if they do not care
> > the semantics difference.
> >
> > Thanks,
> >
> > Xiao
> >
> >
> >
> > 2017-07-16 20:07 GMT-07:00 Chang Chen <
>
> > baibaichen@
>
> > >:
> >
> >> It is tedious since we have lots of Hive SQL being migrated to Spark.
> >> And
> >> this workaround is equivalent to insert a Project between Join operator
> >> and its child.
> >>
> >> Why not do it in PullOutNondeterministic?
> >>
> >> Thanks
> >> Chang
> >>
> >>
> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
>
> > viirya@
>
> > > wrote:
> >>
> >>>
> >>> A possible workaround is to add the rand column into tbl1 with a
> >>> projection
> >>> before the join.
> >>>
> >>> SELECT a.col1
> >>> FROM (
> >>> SELECT col1,
> >>> CASE
> >>> WHEN col2 IS NULL
> >>> THEN cast(rand(9)*1000 - 9999999999 as string)
> >>> ELSE
> >>> col2
> >>> END AS col2
> >>> FROM tbl1) a
> >>> LEFT OUTER JOIN tbl2 b
> >>> ON a.col2 = b.col3;
> >>>
> >>>
> >>>
> >>> Chang Chen wrote
> >>> > Hi Wenchen
> >>> >
> >>> > Yes. We also find this error is caused by Rand. However, this is
> >>> classic
> >>> > way to solve data skew in Hive. Is there any equivalent way in
> Spark?
> >>> >
> >>> > Thanks
> >>> > Chang
> >>> >
> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
> >>>
> >>> > cloud0fan@
> >>>
> >>> > > wrote:
> >>> >
> >>> >> It’s not about case when, but about rand(). Non-deterministic
> >>> expressions
> >>> >> are not allowed in join condition.
> >>> >>
> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
> >>>
> >>> > cn_wss@
> >>>
> >>> > > wrote:
> >>> >> >
> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark
> >>> >> thriftserver), For
> >>> >> > optimizing data skew, we use "case when" to handle null.
> >>> >> > Simple sql as following:
> >>> >> >
> >>> >> >
> >>> >> > SELECT a.col1
> >>> >> > FROM tbl1 a
> >>> >> > LEFT OUTER JOIN tbl2 b
> >>> >> > ON
> >>> >> > * CASE
> >>> >> > WHEN a.col2 IS NULL
> >>> >> > TNEN cast(rand(9)*1000 - 9999999999 as
> >>> string)
> >>> >> > ELSE
> >>> >> > a.col2 END *
> >>> >> > = b.col3;
> >>> >> >
> >>> >> >
> >>> >> > But I get the error:
> >>> >> >
> >>> >> > == Physical Plan ==
> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
> >>> expressions
> >>> >> are
> >>> >> > only allowed in
> >>> >> > Project, Filter, Aggregate or Window, found:*
> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) *
> CAST(1000
> >>> AS
> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
> >>> a.`nav_tcdt`
> >>> >> END
> >>> >> =
> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
> >>> >> (c.`cur_flag`
> >>> >> =
> >>> >> > 1))
> >>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
> >>> double))
> >>> as
> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
> >>> (cast(nav_tcd#26
> >>> as
> >>> >> int)
> >>> >> > = 9)) && (cur_flag#77 = 1))
> >>> >> > ;;
> >>> >> > GlobalLimit 10
> >>> >> > +- LocalLimit 10
> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string)
> IN
> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as
> string))
> >>> &&
> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21
> >>> END],
> >>> >> > [date_id#7]
> >>> >> > +- Filter (date_id#7 = 2017-07-12)
> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
> >>> double))
> >>> as
> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
> >>> (cast(nav_tcd#26
> >>> as
> >>> >> int)
> >>> >> > = 9)) && (cur_flag#77 = 1))
> >>> >> > :- SubqueryAlias a
> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
> >>> >> > : +- CatalogRelation
> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
> >>> >> chanl_id#8L,
> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
> >>> >> nav_refer_page_type_id#13,
> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
> >>> >> nav_page_value#20,
> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
> >>> >> nav_tcd#26,
> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
> >>> >> > detl_refer_page_value#30, ... 33 more fields]
> >>> >> > +- SubqueryAlias c
> >>> >> > +- SubqueryAlias dim_site_categ_ext
> >>> >> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> >>> >> [site_categ_skid#64L,
> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
> >>> >> sort_seq#71L,
> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
> >>> etl_batch_id#75L,
> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
> >>> bkgrnd_categ_id#79L,
> >>> >> > site_categ_id#80, site_categ_parnt_id#81]
> >>> >> >
> >>> >> > Does spark sql not support syntax "case when" in JOIN?
> Additional,
> >>> my
> >>> >> spark
> >>> >> > version is 2.2.0.
> >>> >> > Any help would be greatly appreciated.
> >>> >> >
> >>> >> >
> >>> >> >
> >>> >> >
> >>> >> > --
> >>> >> > View this message in context: http://apache-spark-developers
> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
> >>> >> be-supported-in-JOIN-tp21953.html
> >>> >> > Sent from the Apache Spark Developers List mailing list archive at
> >>> >> Nabble.com.
> >>> >> >
> >>> >> > ------------------------------------------------------------
> >>> ---------
> >>> >> > To unsubscribe e-mail:
> >>>
> >>> > dev-unsubscribe@.apache
> >>>
> >>> >> >
> >>> >>
> >>> >>
> >>> >> ------------------------------------------------------------
> ---------
> >>> >> To unsubscribe e-mail:
> >>>
> >>> > dev-unsubscribe@.apache
> >>>
> >>> >>
> >>> >>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> -----
> >>> Liang-Chi Hsieh | @viirya
> >>> Spark Technology Center
> >>> http://www.spark.tc/
> >>> --
> >>> View this message in context: http://apache-spark-developers
> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
> >>> supported-in-JOIN-tp21953p21961.html
> >>> Sent from the Apache Spark Developers List mailing list archive at
> >>> Nabble.com.
> >>>
> >>> ---------------------------------------------------------------------
> >>> To unsubscribe e-mail:
>
> > dev-unsubscribe@.apache
>
> >>>
> >>>
> >>
>
>
>
>
>
> -----
> Liang-Chi Hsieh | @viirya
> Spark Technology Center
> http://www.spark.tc/
> --
> View this message in context: http://apache-spark-
> developers-list.1001551.n3.nabble.com/SQL-Syntax-case-
> when-doesn-t-be-supported-in-JOIN-tp21953p21973.html
> Sent from the Apache Spark Developers List mailing list archive at
> Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Liang-Chi Hsieh <vi...@gmail.com>.
Thinking about it more, I think it changes the semantics only under certain
scenarios.
For the example SQL query shown in previous discussion, it looks the same
semantics.
Xiao Li wrote
> If the join condition is non-deterministic, pushing it down to the
> underlying project will change the semantics. Thus, we are unable to do it
> in PullOutNondeterministic. Users can do it manually if they do not care
> the semantics difference.
>
> Thanks,
>
> Xiao
>
>
>
> 2017-07-16 20:07 GMT-07:00 Chang Chen <
> baibaichen@
> >:
>
>> It is tedious since we have lots of Hive SQL being migrated to Spark.
>> And
>> this workaround is equivalent to insert a Project between Join operator
>> and its child.
>>
>> Why not do it in PullOutNondeterministic?
>>
>> Thanks
>> Chang
>>
>>
>> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <
> viirya@
> > wrote:
>>
>>>
>>> A possible workaround is to add the rand column into tbl1 with a
>>> projection
>>> before the join.
>>>
>>> SELECT a.col1
>>> FROM (
>>> SELECT col1,
>>> CASE
>>> WHEN col2 IS NULL
>>> THEN cast(rand(9)*1000 - 9999999999 as string)
>>> ELSE
>>> col2
>>> END AS col2
>>> FROM tbl1) a
>>> LEFT OUTER JOIN tbl2 b
>>> ON a.col2 = b.col3;
>>>
>>>
>>>
>>> Chang Chen wrote
>>> > Hi Wenchen
>>> >
>>> > Yes. We also find this error is caused by Rand. However, this is
>>> classic
>>> > way to solve data skew in Hive. Is there any equivalent way in Spark?
>>> >
>>> > Thanks
>>> > Chang
>>> >
>>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>>>
>>> > cloud0fan@
>>>
>>> > > wrote:
>>> >
>>> >> It’s not about case when, but about rand(). Non-deterministic
>>> expressions
>>> >> are not allowed in join condition.
>>> >>
>>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>>>
>>> > cn_wss@
>>>
>>> > > wrote:
>>> >> >
>>> >> > I'm trying to execute hive sql on spark sql (Also on spark
>>> >> thriftserver), For
>>> >> > optimizing data skew, we use "case when" to handle null.
>>> >> > Simple sql as following:
>>> >> >
>>> >> >
>>> >> > SELECT a.col1
>>> >> > FROM tbl1 a
>>> >> > LEFT OUTER JOIN tbl2 b
>>> >> > ON
>>> >> > * CASE
>>> >> > WHEN a.col2 IS NULL
>>> >> > TNEN cast(rand(9)*1000 - 9999999999 as
>>> string)
>>> >> > ELSE
>>> >> > a.col2 END *
>>> >> > = b.col3;
>>> >> >
>>> >> >
>>> >> > But I get the error:
>>> >> >
>>> >> > == Physical Plan ==
>>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
>>> expressions
>>> >> are
>>> >> > only allowed in
>>> >> > Project, Filter, Aggregate or Window, found:*
>>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * CAST(1000
>>> AS
>>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE
>>> a.`nav_tcdt`
>>> >> END
>>> >> =
>>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>>> >> (c.`cur_flag`
>>> >> =
>>> >> > 1))
>>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>>> double))
>>> as
>>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>>> (cast(nav_tcd#26
>>> as
>>> >> int)
>>> >> > = 9)) && (cur_flag#77 = 1))
>>> >> > ;;
>>> >> > GlobalLimit 10
>>> >> > +- LocalLimit 10
>>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string) IN
>>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as string))
>>> &&
>>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21
>>> END],
>>> >> > [date_id#7]
>>> >> > +- Filter (date_id#7 = 2017-07-12)
>>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as
>>> double))
>>> as
>>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) &&
>>> (cast(nav_tcd#26
>>> as
>>> >> int)
>>> >> > = 9)) && (cur_flag#77 = 1))
>>> >> > :- SubqueryAlias a
>>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>>> >> > : +- CatalogRelation
>>> `tmp`.`tmp_lifan_trfc_tpa_hive`,
>>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
>>> >> chanl_id#8L,
>>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>>> >> nav_refer_page_type_id#13,
>>> >> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
>>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>>> >> nav_page_value#20,
>>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
>>> >> nav_tcd#26,
>>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>>> >> > detl_refer_page_value#30, ... 33 more fields]
>>> >> > +- SubqueryAlias c
>>> >> > +- SubqueryAlias dim_site_categ_ext
>>> >> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
>>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>>> >> [site_categ_skid#64L,
>>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
>>> >> sort_seq#71L,
>>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
>>> etl_batch_id#75L,
>>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
>>> bkgrnd_categ_id#79L,
>>> >> > site_categ_id#80, site_categ_parnt_id#81]
>>> >> >
>>> >> > Does spark sql not support syntax "case when" in JOIN? Additional,
>>> my
>>> >> spark
>>> >> > version is 2.2.0.
>>> >> > Any help would be greatly appreciated.
>>> >> >
>>> >> >
>>> >> >
>>> >> >
>>> >> > --
>>> >> > View this message in context: http://apache-spark-developers
>>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>>> >> be-supported-in-JOIN-tp21953.html
>>> >> > Sent from the Apache Spark Developers List mailing list archive at
>>> >> Nabble.com.
>>> >> >
>>> >> > ------------------------------------------------------------
>>> ---------
>>> >> > To unsubscribe e-mail:
>>>
>>> > dev-unsubscribe@.apache
>>>
>>> >> >
>>> >>
>>> >>
>>> >> ---------------------------------------------------------------------
>>> >> To unsubscribe e-mail:
>>>
>>> > dev-unsubscribe@.apache
>>>
>>> >>
>>> >>
>>>
>>>
>>>
>>>
>>>
>>> -----
>>> Liang-Chi Hsieh | @viirya
>>> Spark Technology Center
>>> http://www.spark.tc/
>>> --
>>> View this message in context: http://apache-spark-developers
>>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>>> supported-in-JOIN-tp21953p21961.html
>>> Sent from the Apache Spark Developers List mailing list archive at
>>> Nabble.com.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe e-mail:
> dev-unsubscribe@.apache
>>>
>>>
>>
-----
Liang-Chi Hsieh | @viirya
Spark Technology Center
http://www.spark.tc/
--
View this message in context: http://apache-spark-developers-list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-supported-in-JOIN-tp21953p21973.html
Sent from the Apache Spark Developers List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Xiao Li <ga...@gmail.com>.
If the join condition is non-deterministic, pushing it down to the
underlying project will change the semantics. Thus, we are unable to do it
in PullOutNondeterministic. Users can do it manually if they do not care
the semantics difference.
Thanks,
Xiao
2017-07-16 20:07 GMT-07:00 Chang Chen <ba...@gmail.com>:
> It is tedious since we have lots of Hive SQL being migrated to Spark. And
> this workaround is equivalent to insert a Project between Join operator
> and its child.
>
> Why not do it in PullOutNondeterministic?
>
> Thanks
> Chang
>
>
> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <vi...@gmail.com> wrote:
>
>>
>> A possible workaround is to add the rand column into tbl1 with a
>> projection
>> before the join.
>>
>> SELECT a.col1
>> FROM (
>> SELECT col1,
>> CASE
>> WHEN col2 IS NULL
>> THEN cast(rand(9)*1000 - 9999999999 as string)
>> ELSE
>> col2
>> END AS col2
>> FROM tbl1) a
>> LEFT OUTER JOIN tbl2 b
>> ON a.col2 = b.col3;
>>
>>
>>
>> Chang Chen wrote
>> > Hi Wenchen
>> >
>> > Yes. We also find this error is caused by Rand. However, this is classic
>> > way to solve data skew in Hive. Is there any equivalent way in Spark?
>> >
>> > Thanks
>> > Chang
>> >
>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>>
>> > cloud0fan@
>>
>> > > wrote:
>> >
>> >> It’s not about case when, but about rand(). Non-deterministic
>> expressions
>> >> are not allowed in join condition.
>> >>
>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>>
>> > cn_wss@
>>
>> > > wrote:
>> >> >
>> >> > I'm trying to execute hive sql on spark sql (Also on spark
>> >> thriftserver), For
>> >> > optimizing data skew, we use "case when" to handle null.
>> >> > Simple sql as following:
>> >> >
>> >> >
>> >> > SELECT a.col1
>> >> > FROM tbl1 a
>> >> > LEFT OUTER JOIN tbl2 b
>> >> > ON
>> >> > * CASE
>> >> > WHEN a.col2 IS NULL
>> >> > TNEN cast(rand(9)*1000 - 9999999999 as string)
>> >> > ELSE
>> >> > a.col2 END *
>> >> > = b.col3;
>> >> >
>> >> >
>> >> > But I get the error:
>> >> >
>> >> > == Physical Plan ==
>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic
>> expressions
>> >> are
>> >> > only allowed in
>> >> > Project, Filter, Aggregate or Window, found:*
>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * CAST(1000
>> AS
>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE a.`nav_tcdt`
>> >> END
>> >> =
>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>> >> (c.`cur_flag`
>> >> =
>> >> > 1))
>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double))
>> as
>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26
>> as
>> >> int)
>> >> > = 9)) && (cur_flag#77 = 1))
>> >> > ;;
>> >> > GlobalLimit 10
>> >> > +- LocalLimit 10
>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string) IN
>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as string))
>> &&
>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21 END],
>> >> > [date_id#7]
>> >> > +- Filter (date_id#7 = 2017-07-12)
>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double))
>> as
>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26
>> as
>> >> int)
>> >> > = 9)) && (cur_flag#77 = 1))
>> >> > :- SubqueryAlias a
>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>> >> > : +- CatalogRelation `tmp`.`tmp_lifan_trfc_tpa_hive`,
>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
>> >> chanl_id#8L,
>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>> >> nav_refer_page_type_id#13,
>> >> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>> >> nav_page_value#20,
>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
>> >> nav_tcd#26,
>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>> >> > detl_refer_page_value#30, ... 33 more fields]
>> >> > +- SubqueryAlias c
>> >> > +- SubqueryAlias dim_site_categ_ext
>> >> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> >> [site_categ_skid#64L,
>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
>> >> sort_seq#71L,
>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74,
>> etl_batch_id#75L,
>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L,
>> bkgrnd_categ_id#79L,
>> >> > site_categ_id#80, site_categ_parnt_id#81]
>> >> >
>> >> > Does spark sql not support syntax "case when" in JOIN? Additional,
>> my
>> >> spark
>> >> > version is 2.2.0.
>> >> > Any help would be greatly appreciated.
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > View this message in context: http://apache-spark-developers
>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>> >> be-supported-in-JOIN-tp21953.html
>> >> > Sent from the Apache Spark Developers List mailing list archive at
>> >> Nabble.com.
>> >> >
>> >> > ------------------------------------------------------------
>> ---------
>> >> > To unsubscribe e-mail:
>>
>> > dev-unsubscribe@.apache
>>
>> >> >
>> >>
>> >>
>> >> ---------------------------------------------------------------------
>> >> To unsubscribe e-mail:
>>
>> > dev-unsubscribe@.apache
>>
>> >>
>> >>
>>
>>
>>
>>
>>
>> -----
>> Liang-Chi Hsieh | @viirya
>> Spark Technology Center
>> http://www.spark.tc/
>> --
>> View this message in context: http://apache-spark-developers
>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-
>> supported-in-JOIN-tp21953p21961.html
>> Sent from the Apache Spark Developers List mailing list archive at
>> Nabble.com.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>>
>>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Chang Chen <ba...@gmail.com>.
It is tedious since we have lots of Hive SQL being migrated to Spark. And
this workaround is equivalent to insert a Project between Join operator
and its child.
Why not do it in PullOutNondeterministic?
Thanks
Chang
On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh <vi...@gmail.com> wrote:
>
> A possible workaround is to add the rand column into tbl1 with a projection
> before the join.
>
> SELECT a.col1
> FROM (
> SELECT col1,
> CASE
> WHEN col2 IS NULL
> THEN cast(rand(9)*1000 - 9999999999 as string)
> ELSE
> col2
> END AS col2
> FROM tbl1) a
> LEFT OUTER JOIN tbl2 b
> ON a.col2 = b.col3;
>
>
>
> Chang Chen wrote
> > Hi Wenchen
> >
> > Yes. We also find this error is caused by Rand. However, this is classic
> > way to solve data skew in Hive. Is there any equivalent way in Spark?
> >
> > Thanks
> > Chang
> >
> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
>
> > cloud0fan@
>
> > > wrote:
> >
> >> It’s not about case when, but about rand(). Non-deterministic
> expressions
> >> are not allowed in join condition.
> >>
> >> > On 13 Jul 2017, at 6:43 PM, wangshuang <
>
> > cn_wss@
>
> > > wrote:
> >> >
> >> > I'm trying to execute hive sql on spark sql (Also on spark
> >> thriftserver), For
> >> > optimizing data skew, we use "case when" to handle null.
> >> > Simple sql as following:
> >> >
> >> >
> >> > SELECT a.col1
> >> > FROM tbl1 a
> >> > LEFT OUTER JOIN tbl2 b
> >> > ON
> >> > * CASE
> >> > WHEN a.col2 IS NULL
> >> > TNEN cast(rand(9)*1000 - 9999999999 as string)
> >> > ELSE
> >> > a.col2 END *
> >> > = b.col3;
> >> >
> >> >
> >> > But I get the error:
> >> >
> >> > == Physical Plan ==
> >> > *org.apache.spark.sql.AnalysisException: nondeterministic expressions
> >> are
> >> > only allowed in
> >> > Project, Filter, Aggregate or Window, found:*
> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * CAST(1000 AS
> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE a.`nav_tcdt`
> >> END
> >> =
> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
> >> (c.`cur_flag`
> >> =
> >> > 1))
> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double))
> as
> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26
> as
> >> int)
> >> > = 9)) && (cur_flag#77 = 1))
> >> > ;;
> >> > GlobalLimit 10
> >> > +- LocalLimit 10
> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string) IN
> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as string)) &&
> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21 END],
> >> > [date_id#7]
> >> > +- Filter (date_id#7 = 2017-07-12)
> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double))
> as
> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26
> as
> >> int)
> >> > = 9)) && (cur_flag#77 = 1))
> >> > :- SubqueryAlias a
> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
> >> > : +- CatalogRelation `tmp`.`tmp_lifan_trfc_tpa_hive`,
> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
> >> chanl_id#8L,
> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
> >> nav_refer_page_type_id#13,
> >> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
> >> nav_page_value#20,
> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
> >> nav_tcd#26,
> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
> >> > detl_refer_page_value#30, ... 33 more fields]
> >> > +- SubqueryAlias c
> >> > +- SubqueryAlias dim_site_categ_ext
> >> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> >> [site_categ_skid#64L,
> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
> >> sort_seq#71L,
> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74, etl_batch_id#75L,
> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L, bkgrnd_categ_id#79L,
> >> > site_categ_id#80, site_categ_parnt_id#81]
> >> >
> >> > Does spark sql not support syntax "case when" in JOIN? Additional, my
> >> spark
> >> > version is 2.2.0.
> >> > Any help would be greatly appreciated.
> >> >
> >> >
> >> >
> >> >
> >> > --
> >> > View this message in context: http://apache-spark-developers
> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
> >> be-supported-in-JOIN-tp21953.html
> >> > Sent from the Apache Spark Developers List mailing list archive at
> >> Nabble.com.
> >> >
> >> > ---------------------------------------------------------------------
> >> > To unsubscribe e-mail:
>
> > dev-unsubscribe@.apache
>
> >> >
> >>
> >>
> >> ---------------------------------------------------------------------
> >> To unsubscribe e-mail:
>
> > dev-unsubscribe@.apache
>
> >>
> >>
>
>
>
>
>
> -----
> Liang-Chi Hsieh | @viirya
> Spark Technology Center
> http://www.spark.tc/
> --
> View this message in context: http://apache-spark-developers
> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
> be-supported-in-JOIN-tp21953p21961.html
> Sent from the Apache Spark Developers List mailing list archive at
> Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Liang-Chi Hsieh <vi...@gmail.com>.
A possible workaround is to add the rand column into tbl1 with a projection
before the join.
SELECT a.col1
FROM (
SELECT col1,
CASE
WHEN col2 IS NULL
THEN cast(rand(9)*1000 - 9999999999 as string)
ELSE
col2
END AS col2
FROM tbl1) a
LEFT OUTER JOIN tbl2 b
ON a.col2 = b.col3;
Chang Chen wrote
> Hi Wenchen
>
> Yes. We also find this error is caused by Rand. However, this is classic
> way to solve data skew in Hive. Is there any equivalent way in Spark?
>
> Thanks
> Chang
>
> On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <
> cloud0fan@
> > wrote:
>
>> It’s not about case when, but about rand(). Non-deterministic expressions
>> are not allowed in join condition.
>>
>> > On 13 Jul 2017, at 6:43 PM, wangshuang <
> cn_wss@
> > wrote:
>> >
>> > I'm trying to execute hive sql on spark sql (Also on spark
>> thriftserver), For
>> > optimizing data skew, we use "case when" to handle null.
>> > Simple sql as following:
>> >
>> >
>> > SELECT a.col1
>> > FROM tbl1 a
>> > LEFT OUTER JOIN tbl2 b
>> > ON
>> > * CASE
>> > WHEN a.col2 IS NULL
>> > TNEN cast(rand(9)*1000 - 9999999999 as string)
>> > ELSE
>> > a.col2 END *
>> > = b.col3;
>> >
>> >
>> > But I get the error:
>> >
>> > == Physical Plan ==
>> > *org.apache.spark.sql.AnalysisException: nondeterministic expressions
>> are
>> > only allowed in
>> > Project, Filter, Aggregate or Window, found:*
>> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * CAST(1000 AS
>> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE a.`nav_tcdt`
>> END
>> =
>> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND
>> (c.`cur_flag`
>> =
>> > 1))
>> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
>> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as
>> int)
>> > = 9)) && (cur_flag#77 = 1))
>> > ;;
>> > GlobalLimit 10
>> > +- LocalLimit 10
>> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string) IN
>> > (cast(19596 as string),cast(20134 as string),cast(10997 as string)) &&
>> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21 END],
>> > [date_id#7]
>> > +- Filter (date_id#7 = 2017-07-12)
>> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
>> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
>> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as
>> int)
>> > = 9)) && (cur_flag#77 = 1))
>> > :- SubqueryAlias a
>> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
>> > : +- CatalogRelation `tmp`.`tmp_lifan_trfc_tpa_hive`,
>> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
>> chanl_id#8L,
>> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12,
>> nav_refer_page_type_id#13,
>> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
>> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
>> nav_page_value#20,
>> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
>> nav_tcd#26,
>> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
>> > detl_refer_page_value#30, ... 33 more fields]
>> > +- SubqueryAlias c
>> > +- SubqueryAlias dim_site_categ_ext
>> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
>> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
>> [site_categ_skid#64L,
>> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
>> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
>> sort_seq#71L,
>> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74, etl_batch_id#75L,
>> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L, bkgrnd_categ_id#79L,
>> > site_categ_id#80, site_categ_parnt_id#81]
>> >
>> > Does spark sql not support syntax "case when" in JOIN? Additional, my
>> spark
>> > version is 2.2.0.
>> > Any help would be greatly appreciated.
>> >
>> >
>> >
>> >
>> > --
>> > View this message in context: http://apache-spark-developers
>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
>> be-supported-in-JOIN-tp21953.html
>> > Sent from the Apache Spark Developers List mailing list archive at
>> Nabble.com.
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe e-mail:
> dev-unsubscribe@.apache
>> >
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe e-mail:
> dev-unsubscribe@.apache
>>
>>
-----
Liang-Chi Hsieh | @viirya
Spark Technology Center
http://www.spark.tc/
--
View this message in context: http://apache-spark-developers-list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-supported-in-JOIN-tp21953p21961.html
Sent from the Apache Spark Developers List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Chang Chen <ba...@gmail.com>.
Hi Wenchen
Yes. We also find this error is caused by Rand. However, this is classic
way to solve data skew in Hive. Is there any equivalent way in Spark?
Thanks
Chang
On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan <cl...@gmail.com> wrote:
> It’s not about case when, but about rand(). Non-deterministic expressions
> are not allowed in join condition.
>
> > On 13 Jul 2017, at 6:43 PM, wangshuang <cn...@qq.com> wrote:
> >
> > I'm trying to execute hive sql on spark sql (Also on spark
> thriftserver), For
> > optimizing data skew, we use "case when" to handle null.
> > Simple sql as following:
> >
> >
> > SELECT a.col1
> > FROM tbl1 a
> > LEFT OUTER JOIN tbl2 b
> > ON
> > * CASE
> > WHEN a.col2 IS NULL
> > TNEN cast(rand(9)*1000 - 9999999999 as string)
> > ELSE
> > a.col2 END *
> > = b.col3;
> >
> >
> > But I get the error:
> >
> > == Physical Plan ==
> > *org.apache.spark.sql.AnalysisException: nondeterministic expressions
> are
> > only allowed in
> > Project, Filter, Aggregate or Window, found:*
> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * CAST(1000 AS
> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE a.`nav_tcdt` END
> =
> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND (c.`cur_flag`
> =
> > 1))
> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as
> int)
> > = 9)) && (cur_flag#77 = 1))
> > ;;
> > GlobalLimit 10
> > +- LocalLimit 10
> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string) IN
> > (cast(19596 as string),cast(20134 as string),cast(10997 as string)) &&
> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21 END],
> > [date_id#7]
> > +- Filter (date_id#7 = 2017-07-12)
> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as
> int)
> > = 9)) && (cur_flag#77 = 1))
> > :- SubqueryAlias a
> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
> > : +- CatalogRelation `tmp`.`tmp_lifan_trfc_tpa_hive`,
> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7,
> chanl_id#8L,
> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12, nav_refer_page_type_id#13,
> > nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19,
> nav_page_value#20,
> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25,
> nav_tcd#26,
> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
> > detl_refer_page_value#30, ... 33 more fields]
> > +- SubqueryAlias c
> > +- SubqueryAlias dim_site_categ_ext
> > +- CatalogRelation `dw`.`dim_site_categ_ext`,
> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> [site_categ_skid#64L,
> > site_categ_type#65, site_categ_code#66, site_categ_name#67,
> > site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L,
> sort_seq#71L,
> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74, etl_batch_id#75L,
> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L, bkgrnd_categ_id#79L,
> > site_categ_id#80, site_categ_parnt_id#81]
> >
> > Does spark sql not support syntax "case when" in JOIN? Additional, my
> spark
> > version is 2.2.0.
> > Any help would be greatly appreciated.
> >
> >
> >
> >
> > --
> > View this message in context: http://apache-spark-developers
> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-
> be-supported-in-JOIN-tp21953.html
> > Sent from the Apache Spark Developers List mailing list archive at
> Nabble.com.
> >
> > ---------------------------------------------------------------------
> > To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>
>
Re: [SQL] Syntax "case when" doesn't be supported in JOIN
Posted by Wenchen Fan <cl...@gmail.com>.
It’s not about case when, but about rand(). Non-deterministic expressions are not allowed in join condition.
> On 13 Jul 2017, at 6:43 PM, wangshuang <cn...@qq.com> wrote:
>
> I'm trying to execute hive sql on spark sql (Also on spark thriftserver), For
> optimizing data skew, we use "case when" to handle null.
> Simple sql as following:
>
>
> SELECT a.col1
> FROM tbl1 a
> LEFT OUTER JOIN tbl2 b
> ON
> * CASE
> WHEN a.col2 IS NULL
> TNEN cast(rand(9)*1000 - 9999999999 as string)
> ELSE
> a.col2 END *
> = b.col3;
>
>
> But I get the error:
>
> == Physical Plan ==
> *org.apache.spark.sql.AnalysisException: nondeterministic expressions are
> only allowed in
> Project, Filter, Aggregate or Window, found:*
> (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * CAST(1000 AS
> DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE a.`nav_tcdt` END =
> c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND (c.`cur_flag` =
> 1))
> in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
> cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
> string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as int)
> = 9)) && (cur_flag#77 = 1))
> ;;
> GlobalLimit 10
> +- LocalLimit 10
> +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as string) IN
> (cast(19596 as string),cast(20134 as string),cast(10997 as string)) &&
> nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE nav_tpa_id#21 END],
> [date_id#7]
> +- Filter (date_id#7 = 2017-07-12)
> +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) THEN
> cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as double)) as
> string) ELSE nav_tcdt#25 END = site_categ_id#80) && (cast(nav_tcd#26 as int)
> = 9)) && (cur_flag#77 = 1))
> :- SubqueryAlias a
> : +- SubqueryAlias tmp_lifan_trfc_tpa_hive
> : +- CatalogRelation `tmp`.`tmp_lifan_trfc_tpa_hive`,
> org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [date_id#7, chanl_id#8L,
> pltfm_id#9, city_id#10, sessn_id#11, gu_id#12, nav_refer_page_type_id#13,
> nav_refer_page_value#14, nav_refer_tpa#15, nav_refer_tpa_id#16,
> nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19, nav_page_value#20,
> nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, nav_tcdt#25, nav_tcd#26,
> nav_tci#27, nav_tce#28, detl_refer_page_type_id#29,
> detl_refer_page_value#30, ... 33 more fields]
> +- SubqueryAlias c
> +- SubqueryAlias dim_site_categ_ext
> +- CatalogRelation `dw`.`dim_site_categ_ext`,
> org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [site_categ_skid#64L,
> site_categ_type#65, site_categ_code#66, site_categ_name#67,
> site_categ_parnt_skid#68L, site_categ_kywrd#69, leaf_flg#70L, sort_seq#71L,
> site_categ_srch_name#72, vsbl_flg#73, delet_flag#74, etl_batch_id#75L,
> updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L, bkgrnd_categ_id#79L,
> site_categ_id#80, site_categ_parnt_id#81]
>
> Does spark sql not support syntax "case when" in JOIN? Additional, my spark
> version is 2.2.0.
> Any help would be greatly appreciated.
>
>
>
>
> --
> View this message in context: http://apache-spark-developers-list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be-supported-in-JOIN-tp21953.html
> Sent from the Apache Spark Developers List mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe e-mail: dev-unsubscribe@spark.apache.org
>
---------------------------------------------------------------------
To unsubscribe e-mail: dev-unsubscribe@spark.apache.org