How to insert json datatypes from dataframe to sql table using Scala

This is for Hadoop eco system like HDFS, Map reduce, Hive, Hbase, Pig, sqoop,sqoop2, Avro, solr, hcatalog, impala, Oozie, Zoo Keeper and Hadoop distribution like Cloudera, Hortonwork etc.
ramverma
Posts: 435
Joined: Sat Aug 23, 2014 5:34 pm
Contact:

How to insert json datatypes from dataframe to sql table using Scala

Postby ramverma » Mon Feb 26, 2018 2:31 am

I ahem requirement to insert json and club datatypes from dataframe to sql database table. I tried many ways but it looks like I have to develope UDF to insert son datatypes from DATAFRAME to SQL.

I see that json object is show as string in dataframe. But when I insert it into son column of sql table it is giving me type casting error in Scala.


hadoopuser
Posts: 42
Joined: Mon Jul 21, 2014 7:40 pm
Contact:

Re: How to insert json datatypes from dataframe to sql table using Scala

Postby hadoopuser » Mon Feb 26, 2018 2:35 am

You can use below codes to insert any kinds of object from DataFrame to SQL.
You can just set the prop.setProperty("stringtype", "unspecified") and it will insert the son in sql DB table.
- dfJsonClob is DataFrame and I used postgresql for son table.
prop.setProperty("driver", "org.postgresql.Driver")
prop.setProperty("user", "user1")
prop.setProperty("password", "pass")
prop.setProperty("stringtype", "unspecified")
dfJsonClob.write.mode("append").jdbc("jdbc:postgresql://localhost/db", "JSONtable", prop)


Return to “Hadoop and Big Data”

Who is online

Users browsing this forum: No registered users and 1 guest