Fetching column data from a column database

#dotnet #bigquery #gcp

BigQuery Columnar Storage & Efficient JSON Fetching (C#)

Using bigquery?

Yes — BigQuery is a columnar, distributed, serverless analytical database.
It stores data in a columnar format (Capacitor), which makes it extremely fast for analytical queries, aggregations, and large scans.

Problem

If you fetch BigQuery data row-by-row in C#, and manually build arrays, it becomes:

Slow for large datasets
CPU-heavy
Memory-heavy
Inefficient for high-volume tables

Example of what you want:

export interface DealData {
  Season: number[],
  countryOfProduction: string[],
  productionGroup: string[],
  supplierName: string[],
  dealTypes: string[]
}

Best Practice: Let BigQuery Build Arrays (Using ARRAY_AGG)

Instead of retrieving many rows and aggregating in C#, BigQuery can directly return one row with arrays for each column using ARRAY_AGG.

Optimized BigQuery SQL

SELECT
  ARRAY_AGG(Season) AS Season,
  ARRAY_AGG(countryOfProduction) AS countryOfProduction,
  ARRAY_AGG(productionGroup) AS productionGroup,
  ARRAY_AGG(supplierName) AS supplierName,
  ARRAY_AGG(dealTypes) AS dealTypes
FROM your_dataset.your_table;

Benefits

Only 1 row returned
BigQuery handles the parallel aggregation
Faster and more memory-efficient
Minimal work in C#

C# Example: Reading Aggregated Arrays from BigQuery

using Google.Cloud.BigQuery.V2;
using Newtonsoft.Json;

public class DealData
{
public List<int> Season { get; set; } = new();
public List<string> countryOfProduction { get; set; } = new();
public List<string> productionGroup { get; set; } = new();
public List<string> supplierName { get; set; } = new();
public List<string> dealTypes { get; set; } = new();
}

public async Task<string> GetDealDataAsync()
{
  BigQueryClient client = BigQueryClient.Create("your-project-id");

      string query = @"
          SELECT
            ARRAY_AGG(Season) AS Season,
            ARRAY_AGG(countryOfProduction) AS countryOfProduction,
            ARRAY_AGG(productionGroup) AS productionGroup,
            ARRAY_AGG(supplierName) AS supplierName,
            ARRAY_AGG(dealTypes) AS dealTypes
          FROM your_dataset.your_table
      ";

    var result = client.ExecuteQuery(query, null);
    var row = result.First();

    DealData dealData = new DealData
    {
        Season = row["Season"].ToList<int>(),
        countryOfProduction = row["countryOfProduction"].ToList<string>(),
        productionGroup = row["productionGroup"].ToList<string>(),
        supplierName = row["supplierName"].ToList<string>(),
        dealTypes = row["dealTypes"].ToList<string>()
    };

    return JsonConvert.SerializeObject(dealData);

}

Summary

BigQuery is columnar, highly optimized for analytical workloads.
Avoid looping through BigQuery rows in C#.
Use ARRAY_AGG to let BigQuery perform column-wise aggregation.
C# then reads a single row with arrays → fast, clean, efficient.

DEV Community