Convert Json keys into Columns in Spark

Question

I have written a code which reads the data and picks the second element from the tuple. The second element happens to be a JSON. Code to get the JSON:

import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.conf.Configuration;
import    com.amazon.traffic.emailautomation.cafe.purchasefilter.util.CodecAwareManifestFileSystem;
import com.amazon.traffic.emailautomation.cafe.purchasefilter.util.CodecAwareManifestInputFormat;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import amazon.emr.utils.manifest.input.ManifestItemFileSystem;
import amazon.emr.utils.manifest.input.ManifestInputFormat;
import org.apache.hadoop.mapreduce.lib.input.TextInputFormat ;
import scala.Tuple2;

val configuration = new Configuration(sc.hadoopConfiguration);
ManifestItemFileSystem.setImplementation(configuration);
ManifestInputFormat.setInputFormatImpl(configuration, classOf[TextInputFormat]);
val linesRdd1 = sc.newAPIHadoopFile("location", classOf[ManifestInputFormat[LongWritable,Text]], classOf[LongWritable], classOf[Text], configuration).map(tuple2 =>  tuple2._2.toString());

Below is an example:

{"data":   {"marketplaceId":7,"customerId":123,"eventTime":1471206800000,"asin":"4567","type":"OWN","region":"NA"},"uploadedDate":1471338703958}

Now, i want to create a data frame which has the json keys like marketplaceId, customerId etc as columns and the rows having its value. I am not sure how to proceed with this? Can someone help me with pointer which can help me achieve the same?

score 0 · Answer 1 · answered Nov 14 '17 at 13:45

You can create a scala object for marshalling/unmarshalling JSON using this link https://coderwall.com/p/o--apg/easy-json-un-marshalling-in-scala-with-jackson

And then use that object for reading JSON data using case class in scala:

import org.apache.spark.{SparkConf, SparkContext}

object stackover {
  case class Data(
                   marketplaceId: Double,
                   customerId: Double,
                   eventTime: Double,
                   asin: String,
                   `type`: String,
                   region: String
                 )
  case class R00tJsonObject(
                            data: Data,
                            uploadedDate: Double
                           )

  def main(args: Array[String]): Unit = {
    val conf = new SparkConf(true)
    conf.setAppName("example");
    conf.setMaster("local[*]")

    val sc = new SparkContext(conf)
    val data = sc.textFile("test1.json")
    val parsed = data.map(row => JsonUtil.readValue[R00tJsonObject](row))

    parsed.map(rec => (rec.data, rec.uploadedDate, rec.data.customerId, 
rec.data.marketplaceId)).collect.foreach(println)
 }
 }

Output:

(Data(7.0,123.0,1.4712068E12,4567,OWN,NA),1.471338703958E12,123.0,7.0)

Convert Json keys into Columns in Spark

1 Answers1