Bagaimana untuk menghantar Tatasusunan Struktur dalam pertanyaan berparameter Bigquery

Dalam Bigquery Google, pertanyaan SQL boleh diparameterkan. Jika anda tidak biasa dengan konsep ini, ia pada asasnya bermakna anda boleh menulis pertanyaan SQL sebagai templat berparameter seperti ini:

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

Dan luluskan nilai secara berasingan. Ini mempunyai banyak faedah:

• Pertanyaan lebih mudah dibaca berbanding apabila ia dibina dengan penggabungan rentetan
• Kod ini lebih mantap dan berindustri
• Ia merupakan perlindungan yang hebat terhadap serangan suntikan SQL (wajib XKCD)

Pengiriman parameter pertanyaan daripada skrip Python kelihatan mudah... pada pandangan pertama. Contohnya:

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")
])

Contoh di atas memasukkan nilai ringkas ("Skalar") dalam lajur A dan B. Tetapi anda juga boleh menghantar parameter yang lebih kompleks:

• Array (ArrayQueryParameter)
• Struct (StructQueryParameter)

Masalah timbul apabila anda ingin memasukkan tatasusunan struct : terdapat banyak gotcha, hampir tiada dokumentasi dan sangat sedikit sumber pada subjek di web. Matlamat artikel ini adalah untuk mengisi jurang ini.

Bagaimana untuk mengekalkan tatasusunan struct dalam bigquery menggunakan pertanyaan berparameter

Mari kita tentukan objek berikut yang ingin kita simpan dalam jadual destinasi kita

from dataclasses import dataclass

@dataclass
class Country:
    name: str
    capital_city: str

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

dengan menggunakan pertanyaan berparameter ini

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

Percubaan pertama dengan mengikuti dokumentasi cetek ialah

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

yang akan gagal dengan teruk

AttributeError: objek 'dict' tidak mempunyai atribut 'to_api_repr'

Gotcha n°1: Nilai ArrayQueryParameter mestilah contoh StructQueryParameter

Ternyata bahawa hujah ketiga pembina - nilai- mestilah koleksi contoh StructQueryParameter, bukan nilai yang dikehendaki secara langsung. Jadi mari kita bina mereka:

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])
]))

Kali ini ia berfungsi... Sehingga anda cuba menetapkan tatasusunan kosong

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

ValueError: Tiada maklumat jenis item struct terperinci untuk tatasusunan kosong, sila berikan contoh StructQueryParameterType.

Gotcha n°2: Sediakan jenis struktur penuh sebagai hujah kedua

Mesej ralat cukup jelas: "RECORD" tidak mencukupi untuk Bigquery mengetahui perkara yang perlu dilakukan dengan tatasusunan kosong anda. Ia memerlukan struktur yang terperinci sepenuhnya. Jadilah

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

(Perhatikan bagaimana susunan hujah ...ParameterType constructor adalah sebaliknya bagi ...Parameter constructor. Hanya satu lagi perangkap di jalan...)

Dan kini ia berfungsi untuk tatasusunan kosong juga, yay !

Satu gotcha terakhir yang perlu diketahui: setiap submedan StructQueryParameterType mesti mempunyai nama, walaupun parameter kedua (nama) adalah pilihan dalam pembina. Ia sebenarnya wajib untuk subbidang, jika tidak, anda akan mendapat jenis ralat baharu

Nama medan struct kosong

Saya rasa itu sahaja yang perlu kita ketahui untuk melengkapkan penggunaan tatasusunan rekod dalam parameter pertanyaan, saya harap ini membantu!

---

Terima kasih kerana membaca! Saya Matthieu, jurutera data di Stack Labs.
Jika anda ingin menemui Platform Data Stack Labs atau menyertai pasukan Kejuruteraan Data yang berminat, sila hubungi kami.

---

Foto denys Nevozhai sur Unsplash

Atas ialah kandungan terperinci Bagaimana untuk menghantar Tatasusunan Struktur dalam pertanyaan berparameter Bigquery. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!