topic Re: How do I just write a CSV file to a lakehouse with a notebook? in Data Engineering

How do I just write a CSV file to a lakehouse with a notebook?

arpost — Fri, 26 Jul 2024 17:09:13 GMT

Greetings, all. I'm exploring Fabric notebooks and have a question. I am trying to write a simple CSV file to a lakehouse using the following code:

spark.createDataFrame(dataframe).write.mode("overwrite").csv(LakehousePath + FilePath + FileName_NoType + '_altered')

When I write this, however, I get a folder with files in it rather than a single .csv file:

How do I just save a normal csv file with the name I've chosen?

Re: How do I just write a CSV file to a lakehouse with a notebook?

frithjof_v — Fri, 26 Jul 2024 21:22:40 GMT

One alternative is to convert to Pandas:

# Sample data data = [ ("Alice", 34), ("Bob", 45), ("Catherine", 29) ] # Define the schema columns = ["Name", "Age"] # Create DataFrame df = spark.createDataFrame(data, columns) # Convert to Pandas dataframe df_pandas = df.toPandas() # Write DataFrame to CSV file df_pandas.to_csv('/lakehouse/default/Files/myFile.csv', header=True, index = False)

Read and write data with Pandas - Microsoft Fabric | Microsoft Learn

Keep in mind Pandas cannot handle the same amount of data as Spark, so I guess converting to Pandas should only be done if the data is below a certain size (I have no idea what that "limit" is and what would be the consequense of breaking that limit).

Perhaps, if you are going to use pandas dataframe before saving to csv, you can might as well just use pandas dataframe everywhere in your Notebook (instead of spark dataframe). Then you don't need to convert.
I don't really know but perhaps that would make sense.

At least when you are working with small amounts of data I think you can just go with pandas.

I found some other information/discussion about the topic, however I didn't find a simpler solution:

writing spark dataframe as CSV to a repo - Databricks Community - 60003

scala - Write single CSV file using spark-csv - Stack Overflow

Write in Single CSV file - Databricks Community - 29551

How to Save PySpark Dataframe to a Single Output File | Engineering for Data Science

Solved: Simply writing a dataframe to a CSV file (non-part... - Databricks Community - 27818

Solved: How do I create a single CSV file from multiple pa... - Databricks Community - 29962

python - Write to a CSV file using Microsoft Fabric - Stack Overflow

Re: How do I just write a CSV file to a lakehouse with a notebook?

Anonymous — Mon, 29 Jul 2024 01:36:54 GMT

Hi @arpost ,

Thanks for the reply from @frithjof_v .

The reason you get a folder containing files instead of a single .csv file after writing the code is because Apache Spark's default behavior is to process the data in a distributed fashion and write the results to multiple partial files instead of a single file.

To write CSV files to lakehouse, why not choose the no-code way of upload file, besides the alternative provided by frithjof_v ? This way is easier and faster.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: How do I just write a CSV file to a lakehouse with a notebook?

arpost — Mon, 29 Jul 2024 13:43:57 GMT

@Anonymous, this is for an automated export process, so a manual upload of data wouldn't apply.

Re: How do I just write a CSV file to a lakehouse with a notebook?

Anonymous — Tue, 30 Jul 2024 02:14:34 GMT

Hi @arpost ,

I would like to know why the “upload” option is used for export? I often use this option to upload a csv file and then use it, here are my test steps:

First, here's my cvs file.

I created a lakehouse, which was just created empty.

I used upload to upload the csv file to the lakehouse and after successful upload it shows the below image.

To use it, you can select the load to table option and then manipulate the table.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

Re: How do I just write a CSV file to a lakehouse with a notebook?

frithjof_v — Tue, 30 Jul 2024 05:10:17 GMT

Here is some more information/discussion regarding Pandas: https://www.reddit.com/r/MicrosoftFabric/s/ZXrtR0nbvk

Re: How do I just write a CSV file to a lakehouse with a notebook?

arpost — Tue, 30 Jul 2024 13:10:37 GMT

@Anonymous, when running an automated process that is generating and/or altering data, there isn't a place for a manual upload because the data being produced and exported to a file is being generated via code rather than by a human. This can then be tied into and invoked by a data pipeline as an intermediary step to do things like (1) pull data, (2) create a file and upload to lakehouse, and (3) send file via email.

In my scenario, I don't need to manually drop a file somewhere but instead need the process to produce the file and drop it so it can be picked up.