topic Re: How to store SQL query result data on a local disk? in Data Engineering

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

DipakBachhav — Fri, 15 Jul 2022 21:57:55 GMT

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

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

AmanSehgal — Sat, 16 Jul 2022 03:02:45 GMT

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

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

DipakBachhav — Sat, 16 Jul 2022 18:07:44 GMT

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

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

axb0 — Sun, 17 Jul 2022 15:28:48 GMT

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

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

AmanSehgal — Sun, 17 Jul 2022 23:51:45 GMT

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..

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

Vidula — Mon, 05 Sep 2022 08:23:07 GMT

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!