PySpark sql write dataFrame that has a json column to mysql JDBC

Question

I am trying to bulk write a DataFrame to mysql JDBC database. I am using databricks/pyspark.sql to write the DataFrames to the table. This table has a column that accepts json data(binary data). I did transform the json object to a StructType with following structure:

json object structure and conversion to dataframe:

schema_dict = {'fields': [
    {'metadata': {}, 'name': 'dict', 'nullable': True, 'type': {"containsNull": True, "elementType":{'fields': [
      {'metadata': {}, 'name': 'y1', 'nullable': True, 'type': 'integer'},
      {'metadata': {}, 'name': 'y2', 'nullable': True, 'type': 'integer'}
    ],"type": 'struct'}, "type": 'array'}}
], 'type': 'struct'}

cSchema = StructType([StructField("x1", IntegerType()),StructField("x2", IntegerType()),StructField("x3", IntegerType()),StructField("x4", TimestampType()), StructField("x5", IntegerType()), StructField("x6", IntegerType()),
                     StructField("x7", IntegerType()), StructField("x8", TimestampType()), StructField("x9", IntegerType()), StructField("x10", StructType.fromJson(schema_dict))])
df = spark.createDataFrame(parsedList,schema=cSchema)

The output dataframe:

df:pyspark.sql.dataframe.DataFrame
x1:integer
x2:integer
x3:integer
x4:timestamp
x5:integer
x6:integer
x7:integer
x8:timestamp
x9:integer
x10:struct
    dict:array
        element:struct
              y1:integer
              y2:integer

Now I am trying to write this dataframe to mysql table using the mysql table.

import urllib 
from pyspark.sql import SQLContext
from pyspark.sql.functions import regexp_replace, col
sqlContext = SQLContext(sc)
sqlContext

driver = "org.mariadb.jdbc.Driver"
url = "jdbc:mysql://dburl?rewriteBatchedStatements=true"
trial = "dbname.tablename"
user = "dbuser"
password = "dbpassword"
properties = {
    "user": user,
    "password": password,
    "driver": driver
}
df.write.jdbc(url=url, table=trial, mode="append", properties = properties)

I am getting this error:

An error occurred while calling o2118.jdbc.
: org.apache.spark.SparkException: Job aborted due to stage failure: Task 15 in stage 176.0 failed 4 times, most recent failure: Lost task 15.3 in stage 176.0 (TID 9528, 10.168.231.82, executor 5): java.lang.IllegalArgumentException: Can't get JDBC type for struct>>

Any ideas on how to write a dataframe that has a json column to mysql table? or how to solve this issue?

I am using Databricks 5.5 LTS (includes Apache Spark 2.4.3, Scala 2.11)

PySpark sql write dataFrame that has a json column to mysql JDBC

0 Answers