How to insert a data frame which has a column array<array<double>> in Postgresql?

Viewed 436

I am trying to store a dataframe with a nested schema in Postgresql. Can someone please help me and explain how to store the columns (coordinates) and (user_mentions) in Postgres? I've read that postgres can store array type but I'm getting an error when trying to write to the DB . I'm not entirely sure if my table is created correctly.

Error:

Exception in thread "main" java.lang.IllegalArgumentException: Can't get JDBC type for array<array<double>>

DataFrame Schema:

root
 |-- created_at: string (nullable = true)
 |-- id: long (nullable = true)
 |-- text: string (nullable = true)
 |-- source: string (nullable = true)
 |-- user_id: long (nullable = true)
 |-- in_reply_to_status_id: string (nullable = true)
 |-- in_reply_to_user_id: long (nullable = true)
 |-- lang: string (nullable = true)
 |-- retweet_count: long (nullable = true)
 |-- reply_count: long (nullable = true)
 |-- coordinates: array (nullable = true)
 |    |-- element: array (containsNull = true)
 |    |    |-- element: double (containsNull = true)
 |-- hashtags: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- user_mentions: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- id: long (nullable = true)
 |    |    |-- id_str: string (nullable = true)
 |    |    |-- indices: array (nullable = true)
 |    |    |    |-- element: long (containsNull = true)
 |    |    |-- name: string (nullable = true)
 |    |    |-- screen_name: string (nullable = true)

Postgres Table Creation:

create table test-table (created_at varchar, id int, text text, source text, user_id int, in_reply_to_status_id varchar, in_reply_to_user_id int, lang varchar, retweet_count int, reply_count int, coordinates double precision[][], hashtags text[], user_mentions text[]);

Spark Scala Code:

   val df_1 = df.select(col("created_at"), col("id"), col("text"), col("source"), col("user.id").as("user_id"),
      col("in_reply_to_status_id"), col("in_reply_to_user_id"),
      col("lang"), col("retweet_count"), col("reply_count"), col("place.bounding_box.coordinates"),
      col("entities.hashtags"), col("entities.user_mentions")).withColumn("coordinates", explode(col("coordinates")))

    df_1.show(truncate = false)
    df_1.printSchema()

    df_1.write
      .format("jdbc")
      .option("url", "postgres_url")
      .option("dbtable", "xxx.mytable")
      .option("user", "user")
      .option("password", "pass")
      .save()

Sample Input:

Coordinates Column:

[[80.063341, 26.348309], [80.063341, 30.43339], [88.2027, 30.43339], [88.2027, 26.348309]]

User_Mentions:

[[123456789, 123456789, [0, 15], Name, ScreenName]]
1 Answers

Spark has only support for reading and writing single dimension arrays with JDBC. You can convert your data to have more than one row (i.e. explode it to have double[] in multiple rows) or you can convert your data from double[][] to a comma-separated string[] or plain string.

e.g. [[1, 2], [3, 4]] can be converted to ["1,2", "3,4"]

Related