컬럼형 데이터베이스에서 컬럼 데이터 가져오기

Source: Dev.to

Problem

Fetching BigQuery data row‑by‑row in C# and manually building arrays leads to:

• 대규모 데이터셋에서 성능 저하
• 높은 CPU 사용량
• 높은 메모리 사용량
• 대용량 테이블에 비효율적

You ideally want a structure like:

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#, let BigQuery return a single 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

• 단일 행만 반환됩니다.
• 집계가 BigQuery에 의해 병렬로 수행됩니다.
• 더 빠르고 메모리 효율적입니다.
• C#에서 필요한 처리량이 최소화됩니다.

C# Example: Reading Aggregated Arrays from BigQuery

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

public class DealData
{
    public List Season { get; set; } = new();
    public List countryOfProduction { get; set; } = new();
    public List productionGroup { get; set; } = new();
    public List supplierName { get; set; } = new();
    public List 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(),
        countryOfProduction = row["countryOfProduction"].ToList(),
        productionGroup = row["productionGroup"].ToList(),
        supplierName = row["supplierName"].ToList(),
        dealTypes = row["dealTypes"].ToList()
    };

    return JsonConvert.SerializeObject(dealData);
}

Summary

• BigQuery는 데이터를 컬럼형식으로 저장하며 분석 작업에 최적화되어 있습니다.
• C#에서 행을 반복하는 것을 피하십시오.
• ARRAY_AGG를 사용해 BigQuery가 컬럼별 집계를 수행하도록 합니다.
• 그러면 C#은 배열을 포함한 단일 행을 읽게 되며, 빠르고 깔끔하며 효율적입니다.