cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
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
Databricks Employee
Databricks Employee

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!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group