Spark SQL insert into select issue

Question

INSERT INTO hive_table SELECT b.id,a.id FROM hive_table_1 a,hive_table_2 b WHERE a.id BETWEEN b.id1 AND b.id2;

execute such SQL on spark-sql got errors:

ERROR CliDriver: org.apache.spark.sql.AnalysisException: missing TABLE at 'hive_table' near '<EOF>'; line 1 pos 12
at org.apache.spark.sql.hive.HiveQl$.createPlan(HiveQl.scala:289)
at org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:41)
at org.apache.spark.sql.hive.ExtendedHiveQlParser$$anonfun$hiveQl$1.apply(ExtendedHiveQlParser.scala:40)

But run ok in hive-cli or beeline. Any suggestions?

no,if so it would be another error message.In fact,I'd got nothing about the exception on google. — cwalet

Coder55 Coder55 · Accepted Answer · 2015-07-09T07:11:38

There seems to be a typo in your query - you write a,id instead of a.id to access the field id of the table a.

This code should work properly:

INSERT INTO hive_table SELECT b.id,a.id
FROM hive_table_1 a,hive_table_2 b 
WHERE a.id BETWEEN b.id1 AND b.id2;

I found your error: spark does not pick hive-site.xml from scratch, You have to use a workaround. Possible workarounds can be found there

Spark SQL insert into select issue

1 Answers