Spark-sql解析SQL插入错误

Question

object Test {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .appName("Spark SQL Example")
      .master("local")
      .getOrCreate()
//    val peopleDF = spark.read.json("yy/people.json")
//
//    peopleDF.write.parquet("people.parquet")
    val parquetFileDF = spark.read.parquet("people.parquet")
    parquetFileDF.createOrReplaceTempView("parquetFile")
    val namesDF = spark.sql("SELECT * FROM parquetFile")
    namesDF.show()
    val namesDF1 = spark.sql("insert into TABLE parquetFile (idx, name, age) values (200, "hello", 78)")
  }
}

代码完成，输出如下!

16/09/12 20:50:22 INFO CodeGenerator: Code generated in 16.608273 ms
+----+---+-------+
| age|idx|   name|
+----+---+-------+
|null|100|Michael|
|  30|200|   Andy|
|  19|100| Justin|
+----+---+-------+

---

16/09/12 20:50:22 INFO SparkSqlParser: Parsing command: insert into TABLE parquetFile (idx, name, age) values (200, "hello", 78)
Exception in thread "main" org.apache.spark.sql.catalyst.parser.ParseException: 
mismatched input 'idx' expecting {'(', 'SELECT', 'FROM', 'VALUES', 'TABLE', 'INSERT', 'MAP', 'REDUCE'}(line 1, pos 31)
== SQL ==
insert into TABLE parquetFile (idx, name, age) values (200, "hello", 78)
-------------------------------^^^
at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:197)
at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:99)
at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:46)
at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:53)
at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:582)
at Test$.main(Test.scala:32)
at Test.main(Test.scala)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
16/09/12 20:50:22 INFO SparkContext: Invoking stop() from shutdown hook
16/09/12 20:50:22 INFO SparkUI: Stopped Spark web UI at http://10.100.26.199:4040
16/09/12 20:50:22 INFO MapOutputTrackerMasterEndpoint: MapOutputTrackerMasterEndpoint stopped!
16/09/12 20:50:22 INFO MemoryStore: MemoryStore cleared
16/09/12 20:50:22 INFO BlockManager: BlockManager stopped
16/09/12 20:50:22 INFO BlockManagerMaster: BlockManagerMaster stopped
16/09/12 20:50:22 INFO OutputCommitCoordinator$OutputCommitCoordinatorEndpoint: OutputCommitCoordinator stopped!
16/09/12 20:50:22 INFO SparkContext: Successfully stopped SparkContext
16/09/12 20:50:22 INFO ShutdownHookManager: Shutdown hook called
16/09/12 20:50:22 INFO ShutdownHookManager: Deleting directory /tmp/spark-7229faa1-ed36-4989-a087-eb453e9f9295
Process finished with exit code 1

Answer 1

首先，你是在临时视图上调用INSERT，而不是在某个表上。

其次，应该是INSERT INTO TableName而不是INSERT INTO TABLE TableName

Answer 2

我在我的场景中得到了相同的错误。请参考以下

sql:

insert into Employee (id, name, age) SELECT id, name, age from Employee2

修复使用Below语句

的问题

insert into Employee

SELECT id, name, age

注释:我们不需要在插入语句中分别指定所有的列，相反，我们可以改变选择(它可能是一个火花的要求)无论如何，它为我工作

Answer 3

我也有同样的问题。插入到TableName和删除列名规范工作。我希望它也能与列名一起工作，所以我将集群更改为:

8.1, Spark: 3.1.1 , Single Node, Scala 2.12, Standard DS3 V2