cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

How to store SQL query result data on a local disk?

DipakBachhav
New Contributor III

I am a newbie to data bricks and trying to write results into the excel/ CSV file using the below command but getting 

DataFrame' object has no attribute 'to_csv'

 errors while executing.

I am using a notebook to execute my SQL queries and now want to store results in the CSV or excel file

%python
 
 
 
df =spark.sql ("""select * from customer""") 

and now I want to store the query results in the excel/csv file.I have tried the below code but it's not working

df.coalesce(1).write.option("header","true").option("sep",",").mode("overwrite").csv("file:///C:/New folder/mycsv.csv")      

AND

df.write.option("header", "true").csv("file:///C:/New folder/mycsv.csv")

5 REPLIES 5

AmanSehgal
Honored Contributor III

Are you using Databricks on AWS, Azure or GCP?

If AWS, then you need to create an S3 bucket and mount that S3 bucket on dbfs location.

I'm assuming that customer table exists in your databricks account.

Next, run this code and it will write your df to S3 location.

Lastly, download the csv file from your S3 location to local.

df = spark.sql("select * from customers")
df.write.option("header",true).csv(<dbfs_path>)

More about dbfs: here

No, I am not using AWS. I am using Azure and want to download SQL results to the local drive. your above post not helpful

AmanSehgal
Honored Contributor III

If your query results are upto 1000 records, then you can download from the cell.. for more than that you'll have to write it to your cloud storage and then download from there.. Else you can run query in batches of 1000 to download it..

axb0
New Contributor III
New Contributor III

Use โ€œdownload query resultsโ€ in your DBSQL or notebook cell to download to local disk.

Vidula
Honored Contributor

Hi there @Dipak Bachhavโ€‹ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.