[ 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