[Python] How to write a pyspark dataframe to an SQL database with columns required to be UUID...

I'm trying to write data from a PySpark DataFrame to an SQL database. The table has a column that is of type UUID. When I try to write the data, I get the following error:

ERROR: column "area_id" is of type uuid but expression is of type character varying


I know I can cast types in spark like this:

df = df.withColumn('area_id', col('area_id').cast(StringType()))


But there is no UUIDType or anything similar. How can I achieve this? Or is there a workaround?

I tried also this:

df = df.withColumn('area_id', expr('CAST(area_id AS uuid)'))


But that did not work either:

ParseException:
[UNSUPPORTED_DATATYPE] Unsupported data type "UUID".(line 1, pos 16)

== SQL ==
CAST(area_id AS uuid)
----------------^^^


It seems spark itself cannot handle this, so I'm looking into casting the types inside the database right now, using on insert triggers.

But is spark really not capable of handling UUID type conversions, or is there some workaround?

Continue reading...