val parquetDF = session.read.parquet("s3a://test/ovd").selectExpr("id", "topic", "update_id", "blob")
Trying to read parquet file and dump into Postgres. One of the column in postgres table is of JSONB datatype and in parquet it is in String format.
parquetDF.write.format("jdbc")
.option("driver", "org.postgresql.Driver")
.option("url", "jdbc:postgresql://localhost:5432/db_metamorphosis?binaryTransfer=true&stringtype=unspecified")
.option("dbtable", "entitlements.general")
.option("user", "mdev")
.option("password", "")
.option("stringtype", "unspecified")
.mode(SaveMode.Append)
.save()
And it fails with this erorr :
Caused by: org.postgresql.util.PSQLException: ERROR: column "blob" is of type jsonb but expression is of type character
Hint: You will need to rewrite or cast the expression.
Position: 85
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
... 16 more
Someone on SO suggested to put stringtype=unspecified
as then Postgres will decided the datatype for string, but it seems to be not working.
<scala.major.version>2.12</scala.major.version>
<scala.version>${scala.major.version}.8</scala.version>
<spark.version>2.4.0</spark.version>
<postgres.version>9.4-1200-jdbc41</postgres.version>