Unable to Read CSV file into Spark Dataframe using custom Schema

Viewed 33

I am new to PySpark. I am trying to read a csv file into a DataFrame through the PySpark Shell (on EMR).

I am able to load the data into the DF properly without using a custom schema. However, I am failing to interpret the same data correctly using a custom-schema (using StructType & StructField).

Can anyone please identify where I may be going wrong ? The terminal output is shown below:-

>>> myDF = spark.read.format("csv").option(delimiter=',').load("s3://my_bucket/tmp/test_read.csv")

>>> myDF.show()

+------+---+---------+----------+
|   _c0|_c1|      _c2|       _c3|
+------+---+---------+----------+
|  John| 45|      USA|1985/01/05|
| David| 33|  England|2003/05/19|
|Travis| 56|    Japan|1976/08/12|
|   Tim| 75|Australia|2005/12/18|
| Harry| 35|   France|1980/10/16|
+------+---+---------+----------+

As seen above the CSV gets read correctly. But it breaks when I use a custom-schema to read the same file (shown below):

>>> from pyspark.sql import StructType, StructField, StringType, IntegerType, DateType 

>>> struct_new = StructType([StructField("name", StringType()),StructField("age", IntegerType()),StructField("country", StringType()),StructField("dob", DateType())])
    
>>> myDF = spark.read.format("csv").options(delimiter=',').schema(struct_new).load("s3://my_bucket/tmp/test_read.csv")
    
>>> myDF.printSchema()
    
    root
    |-- name: string (nullable = true)
    |-- age: integer (nullable = true)
    |-- country: string (nullable = true)
    |-- dob: date (nullable = true)
    
>>> myDF.show()
        
+----+----+-------+----+
|name| age|country| dob|
+----+----+-------+----+
|null|null|   null|null|
|null|null|   null|null|
|null|null|   null|null|
|null|null|   null|null|
|null|null|   null|null|
 +----+----+-------+----+

As shown above, the data is being displayed as Nulls when I use the custom schema.

Can anyone please help me fix this. I understand that I must be missing something very basic or obvious over here, so request you to please excuse my naivety as I have just started on Pyspark.

Happy to provide additional information.

Thanks.

1 Answers

dateformat seems to be the problem. If you add dateformat option as below it should be ok.

spark.read.format("csv").options(delimiter=',').options(dateFormat= "yyyy/MM/dd").schema(struct_new).load("s3://my_bucket/tmp/test_read.csv").options(dateFormat= "yyyy/MM/dd")
Related