Data Science w SQL i wdrażanie modeli AI w BigQuery

Przerzucenie tabeli do notebooka ma sens, gdy potrzebujesz własnej architektury modelu albo niestandardowego treningu. Przy prognozie sprzedaży, klasyfikacji klientów czy wsadowej analizie tekstu często buduje jednak niepotrzebny szlak logistyczny. Dane są kopiowane, środowisko wymaga utrzymania, a wynik trzeba ponownie wprowadzić do hurtowni.

BigQuery ML skraca ten szlak. Model tworzysz poleceniem CREATE MODEL, oceniasz przez ML.EVALUATE, a predykcję uruchamiasz w SQL. Funkcje z rodziny AI.* dodają do tego pretrenowane modele prognozujące i zdalne modele generatywne. Prostszy interfejs nie usuwa ryzyka. Nadal odpowiadasz za jakość danych, właściwą metrykę, uprawnienia i zachowanie systemu po błędzie.

BigQuery ML i BigQuery AI: gdzie przebiega granica

BigQuery ML (BQML) obejmuje modele trenowane na danych z hurtowni, między innymi regresję liniową i logistyczną, drzewa wzmacniane, DNN, k-means, faktoryzację macierzy oraz modele szeregów czasowych ARIMA_PLUS i ARIMA_PLUS_XREG. Dla wspieranych typów BigQuery automatyzuje część przygotowania cech, lecz nie podejmuje za Ciebie decyzji o podziale danych ani nie rozpozna biznesowego wycieku informacji.

BigQuery AI rozszerza ten warsztat o funkcje takie jak AI.FORECAST, AI.DETECT_ANOMALIES, AI.EVALUATE i AI.GENERATE_TEXT. Pierwsze trzy mogą korzystać z pretrenowanego TimesFM. Generowanie tekstu wywołuje model przez obiekt remote model i połączenie z usługą zewnętrzną.

To rozróżnienie wpływa na architekturę. Trening BQML odbywa się blisko tabel w BigQuery. Przy remote modelu prompt oraz wybrane pola trafiają do endpointu. Brak ręcznego eksportu nie oznacza, że dane nie są przetwarzane poza BigQuery. Lokalizację datasetu, połączenia i modelu trzeba dobrać zgodnie z dokumentacją, a użycie globalnego endpointu nie daje gwarancji konkretnego regionu przetwarzania.

BigQuery ML kontra klasyczny pipeline

BigQuery wygrywa krótszym szlakiem operacyjnym, szczególnie w analizie wsadowej. Nie usuwa etapów odpowiedzialnych za wiarygodność wyniku. Zbiór testowy, model bazowy, monitoring dryfu i procedura ponownego treningu pozostają częścią wdrożenia.

Prognozowanie sprzedaży z TimesFM krok po kroku

Załóżmy, że tabela myproject.sales.daily_orders zawiera order_date oraz revenue. Celem jest prognoza dziennego przychodu. Zanim wywołasz model, sprawdź duplikaty, braki dat, strefę czasową, zwroty i zmianę definicji przychodu. TimesFM oczekuje uporządkowanego szeregu; sama agregacja nie naprawi brakujących dni.

Krok 1: zbuduj regularny szereg

Poniższy przykład tworzy kalendarz i wypełnia dni bez zamówień zerem. Taka decyzja pasuje do sprzedaży, lecz nie do awarii źródła danych. Jeśli brak oznacza błąd pomiaru, najpierw napraw zasilanie.

CREATE OR REPLACE TABLE `myproject.sales.daily_revenue` AS
WITH calendar AS (
  SELECT day AS ts
  FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE '2024-01-01', CURRENT_DATE() - 1)
  ) AS day
),
revenue AS (
  SELECT
    order_date AS ts,
    SUM(revenue) AS total_revenue
  FROM `myproject.sales.daily_orders`
  WHERE order_date >= DATE '2024-01-01'
    AND order_date < CURRENT_DATE()
  GROUP BY ts
)
SELECT
  calendar.ts,
  COALESCE(revenue.total_revenue, 0) AS total_revenue
FROM calendar
LEFT JOIN revenue USING (ts)
ORDER BY ts;

Krok 2: uruchom prognozę zero-shot

AI.FORECAST nie tworzy obiektu MODEL. Aktualna funkcja domyślnie korzysta z TimesFM 2.5, ale jawne podanie wersji ułatwia audyt zapytania.

SELECT *
FROM AI.FORECAST(
  TABLE `myproject.sales.daily_revenue`,
  data_col => 'total_revenue',
  timestamp_col => 'ts',
  horizon => 180,
  confidence_level => 0.95,
  model => 'TimesFM 2.5'
);

Wynik zawiera między innymi forecast_timestamp, forecast_value, granice przedziału predykcji i ai_forecast_status. Najpierw sprawdź kolumnę statusu. Zapytanie może zwrócić wynik techniczny, choć część szeregów zakończyła się błędem. TimesFM ma też limit kontekstu, więc bardzo długa historia może zostać obcięta, a do prognozy nie zawsze trafią wszystkie wiersze.

Krok 3: porównaj TimesFM z ARIMA_PLUS

Gdy potrzebujesz wyjaśnialności, własnego modelu albo zmiennych zewnętrznych, zbuduj drugi wariant. ARIMA_PLUS_XREG przyda się wtedy, gdy wynik zależy od budżetu reklamowego, cen lub pogody. Prostszy przykład z ARIMA_PLUS wygląda tak:

CREATE OR REPLACE MODEL `myproject.sales.revenue_arima`
OPTIONS(
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'ts',
  time_series_data_col = 'total_revenue',
  auto_arima = TRUE,
  data_frequency = 'AUTO_FREQUENCY',
  holiday_region = 'PL'
) AS
SELECT ts, total_revenue
FROM `myproject.sales.daily_revenue`;

SELECT *
FROM ML.FORECAST(
  MODEL `myproject.sales.revenue_arima`,
  STRUCT(180 AS horizon, 0.95 AS confidence_level)
);

Ustawienie holiday_region = 'PL' pozwala uwzględnić kalendarz polskich świąt. Nie dodaje wiedzy o promocjach, zmianach cen ani przerwach w dostawach. Te informacje muszą znaleźć się w danych i, jeśli mają służyć jako cechy, wymagają modelu obsługującego regresory zewnętrzne.

Krok 4: wykryj anomalie na wydzielonym okresie

AI.DETECT_ANOMALIES wymaga wskazania, który fragment szeregu ma ocenić. Możesz przekazać osobną tabelę docelową, datę początku albo liczbę ostatnich punktów. Wersja bez jednego z tych parametrów jest niepełna.

SELECT *
FROM AI.DETECT_ANOMALIES(
  TABLE `myproject.sales.daily_revenue`,
  data_col => 'total_revenue',
  timestamp_col => 'ts',
  target_last_n_points => 30,
  anomaly_prob_threshold => 0.95,
  model => 'TimesFM 2.5'
);

Odczytaj is_anomaly, anomaly_probability, granice przewidywanego zakresu i ai_detect_anomalies_status. Próg 0.95 nie jest uniwersalny. Dobierz go do kosztu pominiętej anomalii i kosztu fałszywego alarmu. Funkcja ocenia ograniczoną liczbę najnowszych punktów docelowych, dlatego dłuższy audyt trzeba podzielić na okna lub przeprowadzić inną metodą.

Krok 5: oceń wynik na przyszłym oknie

Losowy podział danych fałszuje ocenę szeregu czasowego, ponieważ przyszłość może trafić do treningu. Przygotuj tabelę historyczną kończącą się przed okresem testowym oraz tabelę z rzeczywistymi wartościami dla kolejnych 30 dni.

SELECT *
FROM AI.EVALUATE(
  TABLE `myproject.sales.daily_revenue_train`,
  TABLE `myproject.sales.daily_revenue_actual`,
  data_col => 'total_revenue',
  timestamp_col => 'ts',
  horizon => 30,
  model => 'TimesFM 2.5'
);

Aktualna funkcja zwraca MAE, MSE, RMSE, MAPE, SMAPE, MASE oraz ai_evaluate_status. MAPE zachowuje się źle przy wartościach bliskich zeru, dlatego nie może być jedyną podstawą decyzji. Porównaj wynik z naiwną prognozą, na przykład wartością z poprzedniego tygodnia, i powtórz test na kilku przesuwających się oknach. Dopiero taki backtest pokazuje, czy model wygrywa w różnych sezonach.

Gemini w SQL: konfiguracja remote modelu

BigQuery nie wywoła Gemini bez połączenia. REMOTE WITH CONNECTION DEFAULT działa dopiero wtedy, gdy domyślne połączenie istnieje w danej lokalizacji, jego konto usługi ma wymagane role, a potrzebne API są aktywne.

CREATE OR REPLACE MODEL `myproject.ml.gemini_flash`
REMOTE WITH CONNECTION DEFAULT
OPTIONS(ENDPOINT = 'gemini-2.5-flash');

Dataset z remote modelem oraz dane wejściowe muszą spełniać reguły zgodności lokalizacji. W środowisku produkcyjnym nadaj kontu połączenia minimalny zakres uprawnień i ogranicz kolumny przekazywane do promptu. VPC Service Controls wymaga konfiguracji, nie pojawia się automatycznie wraz z modelem.

Analiza sentymentu z kontrolą statusu

Aktualny AI.GENERATE_TEXT zwraca tekst w kolumnie result oraz stan wywołania w status. Starsza kolumna ml_generate_text_llm_result dotyczyła innego interfejsu i nie powinna trafiać do nowego przykładu.

WITH generated AS (
  SELECT
    ticket_id,
    message,
    result,
    status
  FROM AI.GENERATE_TEXT(
    MODEL `myproject.ml.gemini_flash`,
    (
      SELECT
        ticket_id,
        message,
        CONCAT(
          'Sklasyfikuj komentarz jako POSITIVE, NEGATIVE albo NEUTRAL. ',
          'Zwróć tylko jedną etykietę. Komentarz: ',
          message
        ) AS prompt
      FROM `myproject.support.tickets`
      WHERE DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    ),
    STRUCT(0.0 AS temperature, 10 AS max_output_tokens)
  )
)
SELECT
  ticket_id,
  message,
  UPPER(TRIM(result)) AS sentiment,
  status
FROM generated;

Niska temperatura ogranicza rozrzut odpowiedzi, lecz nie tworzy trwałej gwarancji identycznego wyniku po zmianie wersji modelu. Przed zapisem sprawdź pusty status i waliduj, czy odpowiedź należy do dozwolonego zbioru etykiet. Rekordy z błędem skieruj do kolejki ponowień.

Ekstrakcja JSON bez ślepego zaufania

LLM potrafi zwrócić niepoprawny format, zmyśloną walutę albo datę nieobecną w dokumencie. Do krytycznych danych użyj obsługiwanego schematu odpowiedzi w model_params, bezpiecznego parsera i reguł domenowych. Minimalny wzorzec dalszej obróbki wyniku wygląda tak:

WITH generated AS (
  SELECT invoice_id, result, status
  FROM AI.GENERATE_TEXT(
    MODEL `myproject.ml.gemini_flash`,
    (
      SELECT
        invoice_id,
        CONCAT(
          'Wyodrębnij amount, currency i date. ',
          'Zwróć poprawny JSON, a brakujące pola ustaw na null. Tekst: ',
          raw_description
        ) AS prompt
      FROM `myproject.finance.raw_invoices`
    ),
    STRUCT(0.0 AS temperature, 120 AS max_output_tokens)
  )
),
parsed AS (
  SELECT invoice_id, SAFE.PARSE_JSON(result) AS payload
  FROM generated
  WHERE status = ''
)
SELECT
  invoice_id,
  SAFE_CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC) AS amount,
  JSON_VALUE(payload, '$.currency') AS currency,
  SAFE_CAST(JSON_VALUE(payload, '$.date') AS DATE) AS invoice_date
FROM parsed
WHERE payload IS NOT NULL;

Parser chroni składnię, nie prawdę. Kwotę porównaj z sumą pozycji, walutę z listą ISO, a dokumenty o dużej wartości skieruj do zatwierdzenia przez człowieka. Tekst klienta jest niezaufanym wejściem, więc w promptach trzeba także uwzględnić ryzyko prompt injection i nie udostępniać modelowi narzędzi ani danych zbędnych do zadania.

Jak przenieść zapytanie AI na produkcję

Udany eksperyment w konsoli jest dopiero pierwszym punktem kontrolnym. Produkcyjny proces powinien mieć tabelę wejściową, tabelę wynikową i jednoznaczny identyfikator rekordu. Zaplanowane zapytanie wybiera tylko elementy, które nie mają poprawnego wyniku, zapisuje rezultat wraz z wersją modelu, czasem przetworzenia, statusem i wersją promptu, a błędy ponawia z limitem prób.

To ważne, ponieważ zadanie BigQuery może zakończyć się sukcesem, choć zdalny model zwróci błąd dla części wierszy. Przyczyną bywają limity przepustowości, niedostępny endpoint lub filtr bezpieczeństwa. Status wiersza jest kontraktem produkcyjnym, a nie kolumną diagnostyczną do usunięcia.

Nie zapisuj AI.GENERATE_TEXT jako materialized view. Wywołanie zdalnego modelu ma koszt, może zwrócić inny rezultat i wymaga jawnego sterowania ponowieniami. Lepszym mechanizmem jest scheduled query albo pipeline, który materializuje wyniki i jest idempotentny.

Koszt BigQuery ML i Gemini bez niespodzianek

Cennik ma co najmniej dwie warstwy. BigQuery nalicza przetwarzanie danych zgodnie z modelem rozliczeń projektu, a zdalny endpoint rozlicza wejście, wyjście i w wybranych modelach tokeny rozumowania. Sposób naliczania treningu BQML zależy od rodzaju modelu. Nie każdy algorytm jest zwykłym zapytaniem liczonym wyłącznie za przeskanowane terabajty.

W praktyce kontrola budżetu obejmuje:

• selekcję tylko potrzebnych kolumn i rekordów przed wywołaniem modelu,
• przetwarzanie inkrementalne z ochroną przed ponownym liczeniem sukcesów,
• małe max_output_tokens dopasowane do formatu odpowiedzi,
• pomiar tokenów i kosztu na reprezentatywnej próbce,
• budżety, alerty oraz limity kwot po stronie używanych usług,
• obserwację liczby prób ponawianych po błędach.

Dry run pomaga oszacować bajty przetwarzane przez BigQuery, ale nie obejmuje pełnego kosztu zdalnej inferencji. Konkretnego rachunku nie da się wiarygodnie wyliczyć z samej liczby rekordów. Długość promptu, odpowiedzi, konfiguracja rozumowania, wybrany model i aktualny cennik zmieniają wynik.

Najczęstsze luki przed wdrożeniem

Wyciek danych i zły podział

Cecha utworzona po zdarzeniu, które przewidujesz, potrafi dać znakomitą metrykę i bezużyteczny model. Dla szeregu czasowego dziel dane chronologicznie. Dla klasyfikacji odtwórz stan cech dostępny dokładnie w chwili podejmowania decyzji.

Metryka bez progu biznesowego

Accuracy nie wystarczy przy rzadkich oszustwach, a MAPE nie wystarczy przy zerowej sprzedaży. Zdefiniuj koszt false positive i false negative, skalibruj próg na walidacji, a wynik porównaj z prostą regułą używaną dotychczas.

Brak monitoringu po starcie

Model degraduje się, gdy zmienia się rozkład danych, proces sprzedaży albo znaczenie etykiety. Zapisuj cechy wejściowe, predykcję, wersję modelu i późniejszy wynik rzeczywisty. Ustal alarm dla dryfu, błędów statusu oraz spadku metryki biznesowej.

Dane wrażliwe w promptach

Przekazuj modelowi minimalny zestaw danych, maskuj identyfikatory, kontroluj retencję i region przetwarzania. Dla regulowanych danych decyzję architektoniczną uzgodnij z zespołem bezpieczeństwa oraz prawnym przed uruchomieniem procesu.

Kiedy wybrać Vertex AI albo własny pipeline

• Scoring w czasie transakcji lub requestu aplikacji zwykle wymaga endpointu online z kontrolowanym opóźnieniem.

• Niestandardowe warstwy, funkcje straty i rozproszony trening kierują projekt do Vertex AI, PyTorcha albo TensorFlow.

• Eksperymenty, registry, zatwierdzanie wersji, canary deployment i monitoring wielu endpointów łatwiej prowadzić w dedykowanej platformie.

• Remote model odpada, jeśli polityka zabrania przekazania treści do usługi modelowej, nawet gdy wywołanie rozpoczyna się w SQL.

• Przy ogromnym wolumenie prostszy model klasyfikacyjny, reguły albo własny endpoint mogą pokonać LLM ceną i przewidywalnością.