Google BigQuery에서는 SQL 쿼리를 매개변수화할 수 있습니다. 이 개념에 익숙하지 않다면 기본적으로 다음과 같이 SQL 쿼리를 매개변수화된 템플릿으로 작성할 수 있다는 의미입니다.

INSERT INTO mydataset.mytable(columnA, columnB)
    VALUES (@valueA, @valueB)

그리고 값을 별도로 전달합니다. 여기에는 수많은 이점이 있습니다.

• 문자열 연결로 생성했을 때보다 쿼리가 더 읽기 쉽습니다.
• 코드가 더욱 강력해지고 산업화되었습니다.
• SQL 주입 공격에 대한 탁월한 보호 기능입니다(필수 XKCD).

Python 스크립트에서 쿼리 매개변수를 전달하는 방법은 언뜻 보기에 간단해 보입니다. 예를 들어:

from google.cloud.bigquery import (
    Client,
    ScalarQueryParameter,
    ArrayQueryParameter,
    StructQueryParameter,
    QueryJobConfig,
)

client=Client()

client.query("
INSERT INTO mydataset.mytable(columnA, columnB)
    VALUES (@valueA, @valueB)
", job_config=QueryJobConfig(
    query_parameters=[
        ScalarQueryParameter("valueA","STRING","A"), 
        ScalarQueryParameter("valueB","STRING","B")
])

위 예에서는 열 A와 B에 간단한("스칼라") 값을 삽입합니다. 그러나 더 복잡한 매개변수를 전달할 수도 있습니다.

• 배열(ArrayQueryParameter)
• 구조체(StructQueryParameter)

구조체 배열을 삽입하려고 할 때 문제가 발생합니다. 문제가 많고 문서가 거의 없으며 웹 주제에 대한 리소스가 거의 없습니다. 이 글의 목표는 이러한 격차를 메우는 것입니다.

매개변수화된 쿼리를 사용하여 BigQuery에서 구조체 배열을 유지하는 방법

대상 테이블에 저장하려는 다음 개체를 정의해 보겠습니다.

from dataclasses import dataclass

@dataclass
class Country:
    name: str
    capital_city: str

@dataclass
class Continent:
    name: str
    countries: list[Country]

이 매개변수화된 쿼리를 호출하여

query = UPDATE continents SET countries=@countries WHERE name="Oceania"

얕은 문서를 따르는 첫 번째 시도는 다음과 같습니다.

client.query(query, 
    job_config=QueryJobConfig(query_parameters=[
        ArrayQueryParameter("countries", "RECORD", [
             {name="New Zealand", capital_city="Wellington"},
             {name="Fiji", capital_city="Suva"} ...]
]))

비참하게 실패할 것입니다

속성 오류: 'dict' 개체에 'to_api_repr' 속성이 없습니다.

문제 1번: ArrayQueryParameter의 값은 StructQueryParameter의 인스턴스여야 합니다.

생성자의 세 번째 인수인 값은 원하는 값이 아니라 StructQueryParameter 인스턴스의 컬렉션이어야 합니다. 그럼 빌드해 봅시다:

client.query(query, 
job_config=QueryJobConfig(query_parameters=[
    ArrayQueryParameter("countries", "RECORD", [
    StructQueryParameter("countries",
        ScalarQueryParameter("name", "STRING", ct.name), 
        ScalarQueryParameter("capital_city", "STRING", ct.capital_city)
    )
    for ct in countries])
]))

이번에는 작동합니다... 빈 배열을 설정하려고 할 때까지

client.query(query, 
    job_config=QueryJobConfig(
    query_parameters=[
        ArrayQueryParameter("countries", "RECORD", [])
]))

ValueError: 빈 배열에 대한 자세한 구조체 항목 유형 정보가 누락되었습니다. StructQueryParameterType 인스턴스를 제공하세요.

문제 2번: 전체 구조 유형을 두 번째 인수로 제공하세요.

오류 메시지는 매우 명확합니다. 'RECORD'만으로는 BigQuery가 빈 배열로 무엇을 해야 할지 알 수 없습니다. 완전히 상세한 구조가 필요합니다. 그렇게 하세요

client.query(query, job_config=QueryJobConfig(query_parameters=[
    ArrayQueryParameter("countries",
        StructQueryParameterType(
            ScalarQueryParameterType("STRING","name"),
            ScalarQueryParameterType("STRING","capital_city")
        ), [])
]))

(...ParameterType 생성자의 인수 순서가 ...Parameter 생성자의 반대 순서인지 확인하세요. 또 다른 함정일 뿐입니다...)

이제 빈 배열에서도 작동합니다. 앗!

알고 있어야 할 마지막 사항: StructQueryParameterType의 모든 하위 필드에는 이름이 있어야 합니다. 두 번째 매개변수(이름)가 생성자에서 선택사항인 경우에도 마찬가지입니다. 실제로 하위 필드에는 필수입니다. 그렇지 않으면 새로운 종류의 오류가 발생합니다

빈 구조체 필드 이름

쿼리 매개변수에서 레코드 배열의 사용법을 완료하기 위해 알아야 할 전부라고 생각합니다. 이것이 도움이 되기를 바랍니다!

읽어주셔서 감사합니다! 저는 Stack Labs의 데이터 엔지니어 Matthieu입니다.
Stack Labs 데이터 플랫폼을 알아보고 싶거나 열정적인 데이터 엔지니어링 팀에 합류하고 싶다면 저희에게 연락주세요.

Denys Nevozhai sur Unsplash의 사진