How to convert array of strings to string column?

Question

I'd like to save a JSON to a table in MySQL.

After a bit of reading I found out that the path to load the data into mysql is json->dataframe->mysql.

{"name":"Johny","hobbies":["swiming","cooking"]}
{"name":"James","hobbies":["baseketball","fishing"]}
{"name":"Tom","hobbies":["singing","football"]}

I read the json file be using below command:

val df = sqlContext.read.json("test.json")
df.show()
df.printSchema()

and output:

+--------------------+-----+                                                    
|             hobbies| name|
+--------------------+-----+
|  [swiming, cooking]|Johny|
|[baseketball, fis...|James|
| [singing, football]|  Tom|
+--------------------+-----+

root
 |-- hobbies: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- name: string (nullable = true)

When using below command:

df.registerTempTable("mytable")
sqlContext.
  sql("SELECT * FROM mytable").
  write.
  mode(SaveMode.Append).
  jdbc(url,"jsontest",prop)

I am getting below error:

java.lang.IllegalArgumentException: Can't get JDBC type for array

How can I convert the array of strings to one string like swiming, cooking in the DataFrame?

What Spark version do you use?

Jacek Laskowski
– Jacek Laskowski

2017-06-28 04:04:11 +00:00
Commented Jun 28, 2017 at 4:04 — Jacek Laskowski
– Jacek Laskowski, Commented Jun 28, 2017 at 4:04

koiralo · Accepted Answer · 2017-06-28 08:27:12Z

4

Convert the array of string to a string using a simple udf

 import org.apache.spark.sql.functions._

 val value = udf((arr: Seq[String]) => arr.mkString(","))

 val newDf = df.withColumn("hobbies", value($"hobbies"))

Or you can also use the concat_ws function as as said by Jacek

df.withColumn("hobbies", concat_ws(col("hobbies")))

Output:

+--------------------+-----+                                                    
|             hobbies| name|
+--------------------+-----+
|  swiming, cooking  |Johny|
|baseketball, fishing|James|
| singing, football  |  Tom|
+--------------------+-----+

Then save the newDF as

newDF.write.mode(SaveMode.Append).jdbc(url,"jsontest",prop)

edited Jun 28, 2017 at 8:27

answered Jun 28, 2017 at 3:05

koiralo

23.2k6 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jacek Laskowski Over a year ago

What's $"hobbies".mkString?

willeiwang Over a year ago

Thanks Shankar Koiralafor you reply.but I get the error.Error:(45, 41) value $ is not a member of StringContext val newDF = df.withColumn("hobbies",$"hobbies".mkString)

willeiwang Over a year ago

Thanks for your time anyway.

willeiwang Over a year ago

I can't build it successfully.

willeiwang Over a year ago

I import the functions.but I get the error.Error:(47, 48) value $ is not a member of StringContext val newDf = df.withColumn("hobbies", value($"hobbies"))

|

Jacek Laskowski · Accepted Answer · 2017-06-28 04:04:02Z

How can I convert the array of strings to one string like swiming, cooking in the DataFrame?

You should use the built-in concat_ws function.

concat_ws(sep: String, exprs: Column*): Column Concatenates multiple input string columns together into a single string column, using the given separator.

A solution would then be as follows.

val hobbies = Seq(
  (Array("swiming","cooking"), "Johny"),
  (Array("baseketball","fishing"), "James"),
  (Array("singing","football"), "Tom")
).toDF("hobbies", "name")

val solution = hobbies.select(concat_ws(",", $"hobbies") as "hobbies", $"name")
scala> solution.show
+-------------------+-----+
|            hobbies| name|
+-------------------+-----+
|    swiming,cooking|Johny|
|baseketball,fishing|James|
|   singing,football|  Tom|
+-------------------+-----+

Collectives™ on Stack Overflow

How to convert array of strings to string column?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related