How to execute spark sql multiline query when stored as a string variable?

Question

I have code like so with a multiline query

  val hiveInsertIntoTable = spark.read.text(fileQuery).collect()
  hiveInsertIntoTable.foreach(println)

  val actualQuery = hiveInsertIntoTable(0).mkString
  println(actualQuery)


  spark.sql(s"truncate table $tableTruncate")
  spark.sql(actualQuery)

Whenever I try to execute actual query I get an error.

org.apache.spark.sql.catalyst.parser.ParseException:
no viable alternative at input '<EOF>'(line 1, pos 52)
== SQL ==
insert into wera_tacotv_esd.lac_asset_table_pb_hive

----------------------------------------------------^^^

and the end of the query  .... ;    (terminates in a ;)

The query is actually about 450 lines

I tried to wrap the variable in triple quotes but that didn't work either.

Any help is appreciated.

I am using spark 2.1 and scala 2.11

We'll need to see this query... at least the first 53 characters of it... — ronhash
– ronhash, Commented May 22, 2018 at 21:50
You were right ronhash the error was a combinatrion of the current anser and the fact that I had a ; at the end of the query — javadev
– javadev, Commented May 24, 2018 at 13:50

user9831128 · Accepted Answer · 2018-05-22 21:52:11Z

1

Three problems:

hiveInsertIntoTable is an Array[org.apache.spark.sql.Row] - not very useful structure.
You take only the first row of it hiveInsertIntoTable(0)
Even if you took all rows, concatenating with empty string (.mkString) wouldn't work well.

Either:

val actualQuery = spark.read.text(path).as[String].collect.mkString("\n")

or

val actualQuery = spark.sparkContext.wholeTextFiles(path).values.first()

answered May 22, 2018 at 21:52

user9831128

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

Collectives™ on Stack Overflow

How to execute spark sql multiline query when stored as a string variable?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related