BigQuery TableResult casting options



How can I cast the TableResult to the following format List<Map<String, Any>>

Map contains the columns and its value respectively. Multiple rows are added to the list.

I tried something like this, but it throws an error -> com.google.cloud.bigquery.TableResult cannot be cast to java.util.List

            val queryConfig = QueryJobConfiguration.newBuilder(
                modSql
            )
                .setUseLegacySql(false).build()
            val queryJob = bigQuery.create(JobInfo.newBuilder(queryConfig).build())
            val result = queryJob.getQueryResults()
            return result as List<Map<String, Any>>;

How can we replicate something similar to jdbc template. For example with jdbc template we can do this way

jdbc.query(sql, rowMapper)

and this returns the result in specific rowMapper format. How can we achieve this using BigQuery?

Answer

If you want to have a similar to Spring’s API, you need to:

  1. Declare the same interface:
fun interface RowMapper<T> {
    fun mapRow(row: FieldValueList, rowNum: Int): T
}
  1. Come up with some way to include RowMapper into the query result transformation pipeline. For instance, via extension method for TableResult class:
fun <T> TableResult.mapWith(rowMapper: RowMapper<T>): Iterable<T> = Iterable {
    iterator {
        yieldAll(iterateAll().withIndex().map { (index, row) -> rowMapper.mapRow(row, index) })
    }
}

RowMapper implementation returning column names mapped to their values:

class RowMapperToMap(schema: Schema) : RowMapper<Map<String, Any>> {
    private val fieldsNames = schema.fields.map { it.name }

    override fun mapRow(row: FieldValueList, rowNum: Int) = fieldsNames.associateWith { row.get(it).value }
}

Usage:

val result = queryJob.getQueryResults()
return result.mapWith(RowMapperToMap(result.schema))


Source: stackoverflow