topic Re: write from a Dataframe to a CSV file, CSV file is blank in Data Engineering

write from a Dataframe to a CSV file, CSV file is blank

Nik — Tue, 04 Sep 2018 17:03:05 GMT

i am reading from a text file from a blob

val sparkDF = spark.read.format(file_type)
.option("header", "true")
.option("inferSchema", "true")
.option("delimiter", file_delimiter)
.load(wasbs_string + "/" + PR_FileName)

Then i test my Dataframe

sparkDF.createOrReplaceTempView("tblLookupTable")
//sparkDF.show()
//sparkDF.show(10)
//sparkDF.take(5)

do some other things like

val sqlDF = spark.sql("SELECT * FROM tblLookupTable") //sqlDF.printSchema() //sqlDF.show(1000)

up to this point everything works finally i want to write it to another blob

it works but the csv file is empty, why ????? can someone help

sqlDF.write.format("com.databricks.spark.csv") .option("header", "true") .option("inferSchema", "true") .option("delimiter", "|") .save("wasbs: //MyRootName@MyBlobName.blob.core.windows.net/MyPathName/mydata.csv")

it works with no data , and yes it does create the temp folder "mydata.csv" and the file mydata.csv

but the file has no Headers and no data .

thanks

Re: write from a Dataframe to a CSV file, CSV file is blank

User16829051266 — Tue, 04 Sep 2018 17:59:04 GMT

Hey Nik,

Can you do a file listing on that directory ".../MyPathName/mydata.csv/" and post the names of the files here?

Your data should be located in the CSV file(s) that begin with "part-00000-tid-xxxxx.csv", with each partition in a separate csv file unless when writing the file, you specify with:

sqlDF.coalesce(1).write.format("com.databricks.spark.csv")...

Re: write from a Dataframe to a CSV file, CSV file is blank

Nik — Tue, 04 Sep 2018 19:13:16 GMT

I only have one file in the folder "MyFolder" its a simple read me file

sqlDF.write.format(file_type) .option("header", "true") .option("inferSchema", "true") .option("delimiter", "|") .save("wasbs://MyContainer@MyBlob.blob.core.windows.net/MyFolder/mydata.csv")

..i do not see "part-00000-tid-xxxxx.csv" , by the way i am saving into a blob folder, as i had mentioned the file is getting created in the blob folder but with no data

Re: write from a Dataframe to a CSV file, CSV file is blank

User16829051266 — Tue, 04 Sep 2018 19:32:34 GMT

Can you look in folder "mydata.csv"? In this context mydata.csv should be a folder, not a file.

Re: write from a Dataframe to a CSV file, CSV file is blank

User16829051266 — Tue, 04 Sep 2018 19:40:43 GMT

It would be helpful to provide a screenshot of your Blob storage to see what your directory looks like.

Re: write from a Dataframe to a CSV file, CSV file is blank

Nik — Tue, 04 Sep 2018 19:46:02 GMT

ok , let me run it first

Re: write from a Dataframe to a CSV file, CSV file is blank

Nik — Tue, 04 Sep 2018 19:51:45 GMT

Yes you are right the file in the root is empty but the file with the name ...

part-00001-tid-4180453911607372978-2f22edb4-c9ca-47c8-8791-81cb9b71824c-8-c000.csv

has the data, BUT WHY??????

I asked the function to save into ....

wasbs://MyContainer@MyBlob.blob.core.windows.net/MyFolder/mydata.csv

do you know another way to save the data in the right folder? and the right file?

Re: write from a Dataframe to a CSV file, CSV file is blank

Nik — Tue, 04 Sep 2018 19:57:54 GMT

I tried with

sqlDF.write.format("com.databricks.spark.csv")

same thing the csv file is empty

Re: write from a Dataframe to a CSV file, CSV file is blank

Nik — Tue, 04 Sep 2018 20:04:57 GMT

screenshot

Re: write from a Dataframe to a CSV file, CSV file is blank

Nik — Tue, 04 Sep 2018 20:06:26 GMT

In python they have something like [-1] that will save the file in the previous folder, do we have this in Scala?

Re: write from a Dataframe to a CSV file, CSV file is blank

Nik — Wed, 05 Sep 2018 02:16:28 GMT

I have noticed that the data have been split into to 2 CSV file in the folder, WHY is that??????

Re: write from a Dataframe to a CSV file, CSV file is blank

lalithagutthi — Tue, 11 Sep 2018 18:47:27 GMT

Hi, I have the same issue about what Nik has been facing. what could be the solution and i am expecting one csv file rather than containing a folder with unknown files?

Re: write from a Dataframe to a CSV file, CSV file is blank

lalithagutthi — Thu, 13 Sep 2018 15:34:43 GMT

Can i have update on this? I had same issue and not sure about resolution.

Re: write from a Dataframe to a CSV file, CSV file is blank

Nik — Sun, 16 Sep 2018 22:30:17 GMT

We Are going to work on this next week, please wait , thanks

Re: write from a Dataframe to a CSV file, CSV file is blank

User16829051266 — Sun, 16 Sep 2018 23:06:10 GMT

The number of files written correspond to the number of partitions in the Spark dataframe. To reduce the number to 1 file, use coalesce():

sqlDF.coalesce(1).write.csv(<file-path>)...

Re: write from a Dataframe to a CSV file, CSV file is blank

lalithagutthi — Mon, 17 Sep 2018 00:08:06 GMT

I do not want the folder. for example, if I were given test.csv, I am expecting CSV file. But, it's showing test.csv folder which contains multiple supporting files. moreover, the data file is coming with a unique name, which difficult to my call in ADF for identifiying name.

Re: write from a Dataframe to a CSV file, CSV file is blank

manojlukhi — Sat, 09 Feb 2019 08:51:11 GMT

Hey Nik /Maggi

here are my observations

1. you cannot pass file name in databricks api to other storage service

2. datalake /blob decides file names

3. you can rename files after saving them

Here is solution for you

###### Write your data frame to a single file with default name to a temp location "Part000-XXXXX"
TempFilePath = "wasbs://<DirectoryName>@<Subscription>.blob.core.windows.net/test"
Matrixdatadf.coalesce(1).write\
.mode("overwrite")\
.format("com.databricks.spark.csv")\
.option("header", "true")\
.save(TempFilePath)\
####### now read file from temp location write it to new location with new name and delete temp directory 
readPath = "wasbs://<DirectoryName>@<Subscription>.blob.core.windows.net/test"
writePath = "wasbs://<DirectoryName>@<Subscription>.blob.core.windows.net/MYfolder/ResultFiles"
file_list = dbutils.fs.ls(readPath) #### List out all files in temp directory
for i in file_list:
    file_path = i[0]
    file_name = i[1]
file_name
fname = "test.csv"
for i in file_list:
            if i[1].startswith("part-00000"): #### find your temp file name 
                 read_name = i[1]
# #####Move it outside to the new_dir folder and rename
dbutils.fs.mv(readPath+"/"+read_name, writePath+"/"+fname)
# #Remove the empty folder
dbutils.fs.rm(readPath , recurse= True)
<br>

Will be happy to help if some other help required

Re: write from a Dataframe to a CSV file, CSV file is blank

Iyyappan — Thu, 16 May 2019 17:08:04 GMT

@Maggie Chu @lalitha gutthi Do you have any solution for this issue. Am facing same problem, a folder is getting created with read only mode. But not files inside it. I’m using spark 2.3.1

Re: write from a Dataframe to a CSV file, CSV file is blank

Iyyappan — Fri, 17 May 2019 03:02:37 GMT

I got the answer,- Both input file directory & output file directory should not be same

Re: write from a Dataframe to a CSV file, CSV file is blank

nl09 — Thu, 25 Jun 2020 16:15:52 GMT

Create temp folder inside output folder. Copy file part-00000* with the file name to output folder. Delete the temp folder. Python code snippet to do the same.

fpath=output+'/'+'temp'
def file_exists(path):
  try:
    dbutils.fs.ls(path)
    return True
  except Exception as e:
    if 'java.io.FileNotFoundException' in str(e):
      return False
    else:
      raise
if file_exists(fpath):
  dbutils.fs.rm(fpath)
  spark.sql(query).coalesce(1).write.csv(fpath)
else:
  spark.sql(query).coalesce(1).write.csv(fpath)
fname=([x.name for x in dbutils.fs.ls(fpath) if x.name.startswith('part-00000')])
dbutils.fs.cp(fpath+"/"+fname[0], output+"/"+"somefile.csv")
dbutils.fs.rm(fpath, True)

----