How to convert json array<String> to csv in spark sql

Question

I have tried this query to get required experience from linkedin data.

click here for my data

 Dataset<Row> filteredData = spark
                    .sql("select full_name ,experience from (select *, explode(experience['title']) exp from tempTable )"
                            + "  a where lower(exp) like '%developer%'");

But I got this error:

click here for error I got

and finally I tried but I got more rows with the same name .

Dataset<Row> filteredData = spark
                    .sql("select full_name ,explode(experience) from (select *, explode(experience['title']) exp from tempTable )"
                            + "  a where lower(exp) like '%developer%'");

Please give me hint, how to convert array of string to comma separated string in the same column.

can u share more information about the code and sample data

Gabber
– Gabber

2016-09-22 09:19:00 +00:00
Commented Sep 22, 2016 at 9:19 — Gabber
– Gabber, Commented Sep 22, 2016 at 9:19
actually i am writing code in spark

Mr.spark
– Mr.spark

2016-09-23 08:30:24 +00:00
Commented Sep 23, 2016 at 8:30 — Mr.spark
– Mr.spark, Commented Sep 23, 2016 at 8:30
thanks,but can u share ur sample code in json format

Gabber
– Gabber

2016-09-23 12:38:14 +00:00
Commented Sep 23, 2016 at 12:38 — Gabber
– Gabber, Commented Sep 23, 2016 at 12:38

Gabber · Accepted Answer · 2016-09-23 11:37:45Z

2

You can apply UDF for making a comma separate string

Create UDF like this

def mkString(value: WrappedArray[String]): String = value.mkString(",")

Register UDF in sparkSQL context

sqlContext.udf.register("mkstring", mkString _)

Apply it on SparkSQL query

sqlContext.sql(select mkstring(columnName) from tableName)

it will return comma separate value of array

edited Sep 23, 2016 at 11:37

answered Sep 22, 2016 at 9:10

Gabber

7,2593 gold badges35 silver badges47 bronze badges

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

3 Comments

Mr.spark Over a year ago

if the column has array of <String > use concat_ws(string delimiter, array<string>) else concat_ws(',',collect_set(cast(date as string)))

Mr.spark Over a year ago

Thanks man you guided me . :) i did in a way how you suggest .. :)

Gabber Over a year ago

@AshisParajuli if it is hlpful for u so plz accept the answer

Collectives™ on Stack Overflow

How to convert json array<String> to csv in spark sql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related