Tabular data from DB to MAP Data structure

Question

I am fetching few values from DB and want to create a nested map data structure out of this. The tabular data looks like this

+---------+--------------+----------------+------------------+----------------+-----------------------+
| Cube_ID | Dimension_ID | Dimension_Name | Partition_Column | Display_name   | Dimension_Description |
+---------+--------------+----------------+------------------+----------------+-----------------------+
|       1 |            1 | Reporting_Date | Y                | Reporting_Date | Reporting_Date        |
|       1 |            2 | Platform       | N                | Platform       | Platform              |
|       1 |            3 | Country        | N                | Country        | Country               |
|       1 |            4 | OS_Version     | N                | OS_Version     | OS_Version            |
|       1 |            5 | Device_Version | N                | Device_Version | Device_Version        |
+---------+--------------+----------------+------------------+----------------+-----------------------+

I want to create a nested structure something like this

{
    CubeID = "1": {
        Dimension ID = "1": [
            {
                "Name": "Reporting_Date",
                "Partition_Column": "Y"
                "Display": "Reporting_Date"
            }
        ]
        Dimension ID = "2": [
            {
                "Name": "Platform",
                "Column": "N"
                "Display": "Platform"
            }
        ]
    },
    CubeID = "2": {
        Dimension ID = "1": [
            {
                "Name": "Reporting_Date",
                "Partition_Column": "Y"
                "Display": "Reporting_Date"
            }
        ]
        Dimension ID = "2": [
            {
                "Name": "Platform",
                "Column": "N"
                "Display": "Platform"
            }
        ]
    }
}        

I have the result set from DB using the following. I am able to populate individual columns, but not sure how to create a map for later computation

while (rs.next()) {
      val Dimension_ID = rs.getInt("Dimension_ID")
      val Dimension_Name = rs.getString("Dimension_Name")
      val Partition_Column = rs.getString("Partition_Column")
      val Display_name = rs.getString("Display_name")
      val Dimension_Description = rs.getString("Dimension_Description")
}

I believe I should write a case class for this, but I am not sure how to create a case class and load values to the case class.

Thanks for the help. I can provide any other info needed. Let me know

Answer 1

Background

you can define data class something as below,

case class Dimension(
    dimensionId: Long,
    name: String,
    partitionColumn: String,
    display: String
)

case class Record(
    cubeId: Int,
    dimension: Dimension
)

case class Data(records: List[Record])

And this is how you can construct data,

val data =
  Data(
    List(
      Record(
        cubeId = 1,
        dimension = Dimension(
          dimensionId = 1,
          name = "Reporting_Date",
          partitionColumn = "Y",
          display = "Reporting_Date"
        )
      ),
      Record(
        cubeId = 2,
        dimension = Dimension(
          dimensionId = 1,
          name = "Platform",
          partitionColumn = "N",
          display = "Platform"
        )
      )
    )
  )

Now to your question, since you are using JDBC you have to construct list of records in a mutable way or use scala Iterator. I will write below mutable way to construct above data class but you can explore more.

import scala.collection.mutable.ListBuffer
var mutableData = new ListBuffer[Record]()

while (rs.next()) {
  mutableData += Record(
    cubeId = rs.getIn("Cube_ID"),
    dimension = Dimension(
      dimensionId = rs.getInt("Dimension_ID"),
      name = rs.getString("Dimension_Name"),
      partitionColumn = rs.getString("Partition_Column"),
      display = rs.getString("Dimension_Description")
    )
  )
}

val data = Data(records = mutableData.toList)

Also read - Any better way to convert SQL ResultSet to Scala List