Forum Discussion

A_monged's avatar
A_monged
Frequent Visitor
1 year ago
Solved

Pipeline Copy activity CSV to Parquet issue

I have a CSV file in my Lakehouse. When I use the Copy Activity to move it and change the format to Parquet, I see columns with null values, although the CSV file is configured correctly  (with escape and quote characters)!!

i tried both to load file to table in lakehouse and read it as parquet using spark and in both it shows the null columns !!!

 

 

 

data engineering
data pipeline
lakehouse
notebook
  • A_monged's avatar
    A_monged
    1 year ago

    I found the issue to be that many rows with null values were intentionally added to the CSV, and after partitioning, the nulls were the first to be seen.

8 Replies

    • A_monged's avatar
      A_monged
      Frequent Visitor
      1 year ago

      File use comma as delimiter and " as a quote character 

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        So commas are quoted.  But what about linefeeds in your data? are they quoted?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi A_monged ,

     

    Thanks for the reply from lbendlin .

     

    I used PySpark statements in notebook to convert CSV files from lakehouse to parquet files:

    # start SparkSession
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("CSV_to_Parquet").getOrCreate()

# load CSV file
df = spark.read.format("csv").option("header","true").load("Files/orders/2019.csv")
df.show()

# transform to Parquet form and save it
parquet_file_path = "Files/test.parquet"
df.write.parquet(parquet_file_path)

     

    Works fine, as shown below, and my file does not contain null values:

     

    The error may occur because the data types and schemas defined in the CSV file do not match the data types and schemas expected by the Parquet format.

     

    Optionally, you can use the same method as I did to convert the CSV file to a Parquet file.

     

    If you want to save it as a table you can use the following syntax:

    df.write.mode(“overwrite”).saveAsTable(“parquetTestTable”)

     

    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!

    • A_monged's avatar
      A_monged
      Frequent Visitor
      1 year ago

      I tried your approach but still got the same null columns attached table output and sample rows of raw CSV file