3

If I run the following in spark 1.6 it works fine, but in spark 2.2 I am getting the following error. Anyone know why and how to fix the syntax?

sqlContext.sql("SELECT a.* FROM table1 a Lateral View explode(attributes) attributes_table as attributesfull inner join table2 c ON a.iID = c.iID")

org.apache.spark.sql.catalyst.parser.ParseException:
mismatched input 'inner' expecting {<EOF>, ',', 'WHERE', 'GROUP', 'ORDER', 'HAVING', 'LIMIT', 'LATERAL', 'WINDOW', 'UNION', 'EXCEPT', 'MINUS', 'INTERSECT', 'SORT', 'CLUSTER', 'DISTRIBUTE'}(line 1, pos 92)

== SQL ==
SELECT a.* FROM table1 a Lateral View explode(attributes) attributes_table as attributesfull inner join table2 c ON a.iID = c.iID
---------------------------------------------------------------------------------------------^^^

  at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:217)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:114)
  at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:48)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:68)
  at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:637)
  at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:691)
  ... 50 elided

I'm thinking i can maybe get around it by using a table just for the exploded values and joining to that but would like to know why it no longer works or any better ideas.

Breandán
  • 1,855
  • 22
  • 34
  • it is invalid syntax to use a `join` in the same query as a `lateral view`.Wonder how it worked with Spark 1.6 – Vamsi Prabhala Apr 23 '19 at 17:20
  • 1
    You can use a `join` in the same query as a `lateral view`, just the `lateral view` needs to come after the `join`. To workaround this (if you need to join a column in the lateral view) you can do the following: `select t1.*, t2.* from TABLE_NAME1 t1 inner join (select t1.primary_key, explode_record.* from TABLE_NAME1 t1 lateral view explode(t1.explode_field) as explode_record) lv on lv.primary_key = t1.primary_key inner join TABLE_NAME2 t2 on t2.key = lv.other_key` – Tim Ludwinski Feb 27 '20 at 16:59

0 Answers0