spark-sql parse sql insert into error

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)")

  }
}

The code is up and the below is output!,the insert into can't add column name before values.

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

Try to omit the column names and provide values for all columns. In your case: val namesDF1 = spark.sql("insert into TABLE parquetFile values (200, \"hello\", 78)") — nessa.gp
– nessa.gp, Commented Aug 9, 2018 at 10:59

T. Gawęda · Accepted Answer · 2016-09-12 13:29:23Z

1

At first, you are calling INSERT on temp view not on some table.

Secondly, it should be INSERT INTO TableName not INSERT INTO TABLE TableName

answered Sep 12, 2016 at 13:29

T. Gawęda

16.1k5 gold badges51 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SailingYang Over a year ago

hi, thank you very much! I try insert into TABLE parquetFile values (200, \"hello\", 78) can works well,If I add the column name in sql, it will throw this error.

fattah.safa Over a year ago

hi. I have exactly the same error with same scenario. Did you manage to solve it?

nessa.gp Over a year ago

Spark SQL accepts Hive syntax, both INSERT INTO and INSERT INTO TABLE are accepted in Hive.

Bala cse · Accepted Answer · 2019-07-26 08:06:13Z

0

The same error i got in my scenario. Please refer below

Error i got from below sql :

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

Fixed Using the Below statememt

insert into Employee SELECT id , name , age from Employee2

comments : we dnt need to specify all the columns seperately in insert statement instead we can change select (it may be a spark requirement) anyhow its worked for me

answered Jul 26, 2019 at 8:06

Bala cse

1191 silver badge2 bronze badges

1 Comment

Alfran Over a year ago

This worked for me but how will you insert a limited number of columns?

Dharman · Accepted Answer · 2021-05-06 07:13:35Z

0

I was having same problem. Insert into TableName and removing column names specifications worked. I wanted it to work with column name as well so I changed my cluster to following:

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

edited May 6, 2021 at 7:13

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered May 6, 2021 at 7:08

Alfran

1,5511 gold badge11 silver badges19 bronze badges

1 Comment

mmmm Over a year ago

What version was your cluster stuff when it was not working? How did you know to change to these? Thanks

Collectives™ on Stack Overflow

spark-sql parse sql insert into error

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related