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

[DipakBachhav]

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")

[AmanSehgal]

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

[DipakBachhav]

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]

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]

Use “download query results” in your DBSQL or notebook cell to download to local disk.

[Vidula]

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!