spark-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Volodymyr Glushak (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (SPARK-24430) CREATE VIEW with UNION statement: Failed to recognize predicate 'UNION'.
Date Thu, 31 May 2018 08:33:00 GMT

     [ https://issues.apache.org/jira/browse/SPARK-24430?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Volodymyr Glushak updated SPARK-24430:
--------------------------------------
    Description: 
When I executes following SQL statement:
{code:java}
spark.sql('CREATE VIEW view_12 AS

SELECT * FROM (

  SELECT * FROM table1

  UNION ALL

  SELECT * FROM table2

) UT')

{code}
 

It successfully creates view in HIVE, which I can query via Apache Spark.

However if I'm trying to query the same view directly via HIVE, I've got an error:
{code:java}
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException
line 6:6 Failed to recognize predicate 'UNION'.

Failed rule: 'identifier' in subquery source

{code}
 

*Investigation*

Under hood, spark generate following SQL statement for this view:
{code:java}
CREATE VIEW `view_12` AS

SELECT *

FROM (SELECT * FROM

            (

                   (SELECT * FROM (SELECT * FROM `db1`.`table1`) AS gen_subquery_0)

                             UNION ALL

                    (SELECT * FROM (SELECT  * FROM `db1`.`tabl2`) AS gen_subquery_1)

            ) AS UT

          ) AS UT

{code}
If I try to execute this statement in HIVE it fails with the same reason.

The easiest way to fix it, is to unwrap outer queries on lines 5 and 7.

  was:
When I executes following SQL statement:

{code}

spark.sql('CREATE VIEW view_12 AS

SELECT * FROM (

  SELECT * FROM table1

  UNION ALL

  SELECT * FROM table2

) UT')

{code}

 

It successfully creates view in HIVE, which I can query via Apache Spark.

However if I'm trying to query the same view directly via HIVE, I've got an error:

{code}

org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException
line 6:6 Failed to recognize predicate 'UNION'.

Failed rule: 'identifier' in subquery source

{code}

 

*Investigation*

Under hood, spark generate following SQL statement for this view:

{code}

CREATE VIEW `view_12` AS

SELECT *

FROM (SELECT * FROM

            (

                   (SELECT * FROM (SELECT * FROM `db1`.`table1`) AS gen_subquery_0)

                             UNION ALL

                    (SELECT * FROM (SELECT  * FROM `db1`.`tabl2`) AS gen_subquery_1)

            ) AS UT

          ) AS UT

{code}

If I try to executes this statement in HIVE it fails with the same reason.

The easiest way to fix it, is to unwrap outer queries on lines 5 and 7.


> CREATE VIEW with UNION statement: Failed to recognize predicate 'UNION'.
> ------------------------------------------------------------------------
>
>                 Key: SPARK-24430
>                 URL: https://issues.apache.org/jira/browse/SPARK-24430
>             Project: Spark
>          Issue Type: Request
>          Components: Spark Core, SQL
>    Affects Versions: 2.2.1
>            Reporter: Volodymyr Glushak
>            Priority: Major
>
> When I executes following SQL statement:
> {code:java}
> spark.sql('CREATE VIEW view_12 AS
> SELECT * FROM (
>   SELECT * FROM table1
>   UNION ALL
>   SELECT * FROM table2
> ) UT')
> {code}
>  
> It successfully creates view in HIVE, which I can query via Apache Spark.
> However if I'm trying to query the same view directly via HIVE, I've got an error:
> {code:java}
> org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED:
ParseException line 6:6 Failed to recognize predicate 'UNION'.
> Failed rule: 'identifier' in subquery source
> {code}
>  
> *Investigation*
> Under hood, spark generate following SQL statement for this view:
> {code:java}
> CREATE VIEW `view_12` AS
> SELECT *
> FROM (SELECT * FROM
>             (
>                    (SELECT * FROM (SELECT * FROM `db1`.`table1`) AS
gen_subquery_0)
>                              UNION ALL
>                     (SELECT * FROM (SELECT  * FROM `db1`.`tabl2`)
AS gen_subquery_1)
>             ) AS UT
>           ) AS UT
> {code}
> If I try to execute this statement in HIVE it fails with the same reason.
> The easiest way to fix it, is to unwrap outer queries on lines 5 and 7.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org


Mime
View raw message