Spark SQL in databricks

Question

How can I use if else statement in spark SQL in databricks

Eg:-

if ( drop table if exist table name )

else

create table if not exist table name(

)

Using delta

insert into table name if not exist 

else update table

Can you please help me with this?

Answer 1

In sparkSql, we can use CASE when and then statement as below:

val df = spark.createDataFrame(Seq(( 2, 9), ( 1, 5),( 1, 1),( 1, 2),( 2, 8))).toDF("y", "x")

df.createOrReplaceTempView("test")

spark.sql("select CASE WHEN y = 2 THEN 'A' ELSE 'B' END AS flag, x from test").show

The output will be as below:

Spark 2.0.0

df: org.apache.spark.sql.DataFrame = [y: int, x: int]

+----+---+

|flag| x|

+----+---+

| A| 9|

| B| 5|

| B| 1|

| B| 2|

| A| 8|

+----+---+

Now, regarding your query, we can create a Hive table as below

sqlContext.sql("CREATE TABLE IF NOT EXISTS employee(id INT, name STRING, age INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'")

Similarly, we can drop a Hive table in the SQL context statement itself.