Question

I am trying to define a nested .json schema in pyspark, but cannot get the ddl_schema string to work.

Usually in SQL this would be ROW, I have tried STRUCT below but can't get the data type correct this is the error...

ParseException: 
mismatched input '(' expecting {<EOF>, ',', 'COMMENT', NOT}(line 6, pos 15)

== SQL ==

    driverId INT,
    driverRef STRING,
    number STRING,
    code STRING,
    name STRUCT(forename STRING, surname STRING),
---------------^^^
    dob DATE,
    nationality STRING,
    url STRING

Data Sample

             -------- ---------- ------ ---- -------------------- ---------- ----------- -------------------- 
            |driverId| driverRef|number|code|                name|       dob|nationality|                 url|
             -------- ---------- ------ ---- -------------------- ---------- ----------- -------------------- 
            |       1|  hamilton|    44| HAM|   {Lewis, Hamilton}|1985-01-07|    British|http://en.wikiped...|

Code Sample

        mnt = "/mnt/dev/root"
        env = "raw"
        path = "formula1/drivers"
        fileFormat = "json"
        
        inPath = f"{mnt}/{env.upper()}/{path}.{fileFormat}"
        
        
        options = {'header': 'True'}
        
        ddl_schema = """
            driverId INT,
            driverRef STRING,
            number STRING,
            code STRING,
            name STRUCT(forename STRING, surname STRING),
            dob DATE,
            nationality STRING,
            url STRING
        """
        
        drivers_df = (spark
                       .read
                       .options(**options)
                       .schema(ddl_schema)
                       .format(fileFormat)
                       .load(inPath)
                     )

CodePudding user response：

You are using the wrong syntax for STRUCT.
Here is the right one:

name STRUCT<forename:STRING,surname:STRING>

https://spark.apache.org/docs/latest/sql-ref-datatypes.html
(search for Complex types and choose the SQL tab)