how to store query results to new database and table in databricks

Question

I am a newbie to azure databricks and trying to store query results in the database. I want to create a new database and table on the fly. if it exists then remove existing DB and tables and store query results in the newly created database. Currently, I am using a notebook to write my query in the azure databricks.

df =spark.sql ("""select * from tableName""");

Answer 1

If you already have data in the Dataframe, then you just need to write this data into a table using the overwrite mode - it will create a table if it doesn't exist, or overwrite previous data. You just need to make sure that database exists:

db_name = "mydb"
table_name = "mytable"
spark.sql(f"create database if not exists {db_name}")
df =spark.sql ("""select * from tableName""");
df.write.mode("overwrite").saveAsTable(f"{db_name}.{table_name}")

Although you can also do it using only SQL - just make sure that database exists, and then use the CREATE OR REPLACE TABLE SQL command:

CREATE OR REPLACE TABLE mydb.mytable AS
select * from tableName