[
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-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
