في 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. ولكن يمكنك أيضًا تمرير معلمات أكثر تعقيدًا:

• المصفوفات (معلمة استعلام المصفوفة)
• الهياكل (معلمة الاستعلام الهيكلي)

تنشأ المشاكل عندما تريد إدراج مصفوفات من البنيات: هناك العديد من الأخطاء، ولا توجد وثائق تقريبًا وعدد قليل جدًا من الموارد حول هذا الموضوع على الويب. الهدف من هذه المقالة هو سد هذه الفجوة.

كيفية الاستمرار في مجموعة من البنيات في 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.

Gotcha n°2: قم بتوفير نوع البنية الكاملة كوسيطة ثانية

رسالة الخطأ واضحة جدًا: "RECORD" ليس كافيًا لـ Bigquery لمعرفة ما يجب فعله بالمصفوفة الفارغة. انها تحتاج إلى هيكل مفصل بالكامل. فليكن

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

(لاحظ كيف أن ترتيب وسيطات مُنشئ ...ParameterType هو عكس ... مُنشئ المعلمة. مجرد فخ آخر على الطريق...)

والآن يعمل مع المصفوفات الفارغة أيضًا، رائع!

يجب أن تكون على دراية بما يلي: يجب أن يكون لكل حقل فرعي من StructQueryParameterType اسم، حتى لو كانت المعلمة الثانية (الاسم) اختيارية في المُنشئ. إنه في الواقع إلزامي للحقول الفرعية، وإلا فسوف تحصل على نوع جديد من الأخطاء

اسم حقل البنية فارغ

أعتقد أن هذا هو كل ما نحتاج إلى معرفته لإكمال استخدام صفائف السجلات في معلمات الاستعلام، وآمل أن يساعد هذا!

شكرا على القراءة! أنا ماتيو، مهندس بيانات في Stack Labs.
إذا كنت تريد اكتشاف Stack Labs Data Platform أو الانضمام إلى فريق متحمس لهندسة البيانات، فيرجى الاتصال بنا.

صورة دينيس نيفوزاي على Unsplash