CODEWITHSUNDEEP
scalaapache-sparknestedapache-spark-sql
Feynman27
Feynman27

Reputation: 3267

Flattening a very nested Spark Scala dataframe

I have a very nested dataframe that I'm trying to flatten. The original schema looks like:

 |-- _History: struct (nullable = true)
 |    |-- Article: array (nullable = true)
 |    |    |-- element: struct (containsNull = true)
 |    |    |    |-- Id: string (nullable = true)
 |    |    |    |-- Timestamp: long (nullable = true)
 |    |-- Channel: struct (nullable = true)
 |    |    |-- Music: array (nullable = true)
 |    |    |    |-- element: long (containsNull = true)
 |    |    |-- Sports: array (nullable = true)
 |    |    |    |-- element: long (containsNull = true)
 |    |    |-- Style: array (nullable = true)
 |    |    |    |-- element: long (containsNull = true)

I'm able to flatten most fields using the recursive function:

implicit class DataFrameFlattener(df: DataFrame) {
  def flattenSchema: DataFrame = {
    df.select(flatten(Nil, df.schema): _*)
  }

  protected def flatten(path: Seq[String], schema: DataType): Seq[Column] = schema match {
    case s: StructType => s.fields.flatMap(f => flatten(path :+ f.name, f.dataType))
    case other => col(path.map(n => s"`$n`").mkString(".")).as(path.mkString(".")) :: Nil
  } 
}

However, this doesn't seem to be able to flatten _History.Article.Id and _History.Article.Timstamp in the schema above. Why is this and how do I flatten these two fields into their own columns within the dataframe?

Upvotes: 1

Views: 2784

Answers (2)

Mohammad Rijwan
Mohammad Rijwan

Reputation: 357

Using scala spark you can flatten the json recursively:

import org.apache.spark.sql.{ Row, SaveMode, SparkSession, DataFrame }
def recurs(df: DataFrame): DataFrame = {
  if(df.schema.fields.find(_.dataType match {
    case ArrayType(StructType(_),_) | StructType(_) => true
    case _ => false
  }).isEmpty) df
  else {
    val columns = df.schema.fields.map(f => f.dataType match {
      case _: ArrayType => explode(col(f.name)).as(f.name)
      case s: StructType => col(s"${f.name}.*")
      case _ => col(f.name)
    })
    recurs(df.select(columns:_*))
  }
}
val df = spark.read.json(json_location)
flatten_df = recurs(df)
flatten_df.show()

This will create the array in perticular column.

#

If you don't want the array and append in another row there is another one:

def flattenDataframe(df: DataFrame): DataFrame = {
    //getting all the fields from schema
    val fields = df.schema.fields
    val fieldNames = fields.map(x => x.name)
    //length shows the number of fields inside dataframe
    val length = fields.length
    for (i <- 0 to fields.length - 1) {
      val field = fields(i)
      val fieldtype = field.dataType
      val fieldName = field.name
      fieldtype match {
        case arrayType: ArrayType =>
          val fieldName1 = fieldName
          val fieldNamesExcludingArray = fieldNames.filter(_ != fieldName1)
          val fieldNamesAndExplode = fieldNamesExcludingArray ++ Array(s"explode_outer($fieldName1) as $fieldName1")
          //val fieldNamesToSelect = (fieldNamesExcludingArray ++ Array(s"$fieldName1.*"))
          val explodedDf = df.selectExpr(fieldNamesAndExplode: _*)
          return flattenDataframe(explodedDf)

        case structType: StructType =>
          val childFieldnames = structType.fieldNames.map(childname => fieldName + "." + childname)
          val newfieldNames = fieldNames.filter(_ != fieldName) ++ childFieldnames
          val renamedcols = newfieldNames.map(x => (col(x.toString()).as(x.toString().replace(".", "_").replace("$", "_").replace("__", "_").replace(" ", "").replace("-", ""))))
          val explodedf = df.select(renamedcols: _*)
          return flattenDataframe(explodedf)
        case _ =>
      }
    }
    df
  }

Just call this like the previous one, import some libraries if I missed.

Upvotes: 1

Feynman27
Feynman27

Reputation: 3267

I found a work around: create two new columns of the flattened fields:

val flatDF = df
    .withColumn("_History.Article.Id", df("`_History.Article`.Id")
    .withColumn("_History.Article.Timestamp", df("`_History.Article`.Timestamp")

Upvotes: 0

Related Questions