Does Spark Supports With Clause?
The WITH statement is not the problem, but rather the INSERT INTO statement that's causing trouble.
Here's a working example that uses the .insertInto() style instead of the "INSERT INTO" SQL:
val s = Seq((1,"foo"), (2, "bar"))
s: Seq[(Int, String)] = List((1,foo), (2,bar))
val df = s.toDF("id", "name")
df.registerTempTable("df")
sql("CREATE TABLE edf_final (id int, name string)")
val e = sql("WITH edf AS (SELECT id+1, name FROM df cook) SELECT * FROM edf")
e.insertInto("edf_final")
Another option is to use the df.write.mode("append").saveAsTable("edf_final")
style.
Relevant SO: "INSERT INTO ..." with SparkSQL HiveContext
Ganesh Kumar
I have been Java Developer since Three Years and i am good at java/J2EE Technologie, also strong in sql and backend databases like netezza,teradata,hadoop,postgres..
Updated on June 16, 2022Comments
-
Ganesh Kumar almost 2 years
I have table
employee_1
in spark with attributes id and name(with data), and another tableemployee_2
with same attributes, i want to load the data by increasing the id values with +1My With Clause shown below:
WITH EXP AS (SELECT ALIASNAME.ID+1 ID, ALIASNAME.NAME NAME FROM employee_1 ALIASNAME) INSERT INTO TABLE employee_2 SELECT * FROM EXP;
Steps of execution:
I have a file(with data) in HDFS location.
- Creating RDD based on hdfs location.
- RDD to Hive temp table
- from temp table to Hive Target (employee_2).
when i am running with test program from backend its succeeding. but data is not loading.
employee_2
is empty.Note: If you run the above with clause in Hive it will succeed and data will load. But in spark it won't in 1.6 ?