Pyspark nullable uuid type uuid but expression is of type character varying

user_579477 0
Viewed 1143

Given a table design with a non-nullable uuid column AND a nullable uuid column, how does one insert using python 3.7.9 with Pyspark 2.4.3 dataframe and postgresql-42.2.18.jar driver?

table_df = spark.read.format('jdbc) \
                     .option('driver', 'org.postgresql.Driver') \
                     .option('dbtable', 'example_table') \
                     .load()

table_df.printSchema()

root
 |-- id: string (nullable = false)
 |-- created: timestamp (nullable = true)
 |-- modified: timestamp (nullable = true)
 |-- example_uuid: string (nullable = true)


from pyspark.sql.functions import when, lit, col

from pyspark.sql.types import NullType, StringType

def replace(column, value):
  return when (column == value, lit(None).cast(NullType())).otherwise(column.cast(StringType()))

example_df = tasklog_df.withColumn("example_uuid", replace(col("example_uuid"), "NULL"))

example_df.write.mode('append').format('jbdc') \
                .option('driver', 'org.postgresql.Driver')\
                .option('stringtype', 'unspecified') \
                .save()

This results in Pyspark trying to insert

INSERT INTO example_table
 ("id",
 "created",
 "modified",
 "example_uuid") 
VALUES 
 ('b49a90aa-a415-4aeb-a7ed-bfc42e43f5c7',
 '2020-03-29 02:00:11.06534-07',
 '2020-03-29 02:00:11.065361-07',
 NULL)

Which then results in the infamous

ERROR: column "example_uuid" is of type uuid but expression is of type character
  Hint: You will need to rewrite or cast the expression.

I have cast the data. Pyspark isn't generating the correct INSERT statement or the postgres driver is treating the word NULL as a character instead of a keyword. I need to use the .option('stringtype', 'unspecified') in order to not have Pyspark complain about the id column being a uuid.

The lit(None).cast(NullType()) seems to be doing nothing. There's no entry in pyspark.sql.types for a uuid type.

Without the option('stringtype', 'unspecified') then Pyspark throws the error:

Caused by: org.postgresql.util.PSQLException: ERROR: column "id" is of type uuid but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

The only approach left appears to be to split the dataframe into two dataframes, one with example_uuid field that contains NULL and one where the example_uuid field is a uuid. Then remove the example_uuid field from the dataframe with the NULLs so that it doesn't throw an error when saving to the table. This seems like a lot of wasted effort when Pyspark should just support uuid types. Advice or suggestions?

pyspark apache-spark postgresql python
3 Answers

Having this in the pyspark will require that corresponding type should be added to the Spark SQL, and it could be significant effort to add, as it will be require changes in optimizer & other parts. As workaround this should be done in the JDBC driver (but this could be against JDBC specification), or in the JDBC connector in Spark itself, but then it won't be database agnostic. For example, Spark Cassandra Connector is automatically cast the "compatible" when saving data into Cassandra, and this allows it to support UUIDs when reading/writing, although in Spark they are represented as strings.

Alex_Ott_186270

I know this is nasty and impacts on storage, but until this is fixed (i.e. supporting UUID data type and/or capable of writing NULLs for UUID fields), I opted to store a zero GUID (00000000-0000-0000-0000-000000000000).

.withColumn("nullable_uuid_field", coalesce($"nullable_uuid_field", lit("00000000-0000-0000-0000-000000000000")))

user_111251580

I personally ended up splitting my writes and relying on the db to set the null.

insert(to_insert.where(F.col("col_name").isNull()).drop("col_name"))
insert(to_insert.where(F.col("col_name").isNotNull()))
user_64483840
Related