איך לאמן מודלים של ML ב-BigQuery SQL מבלי להעביר נתונים

שמתם לב שכשבונים מודל חיזוי נטישת לקוחות על נתונים היסטוריים ב-BigQuery, צריך לייצא עשרות מיליוני שורות לאשכול חיצוני? Pandas קורסת, צינורות נתונים נשברים, וגרסאות מודל אובדות. פתרנו את זה אחרת: BigQuery ML מאפשר לאמן מודלים ישירות ב-SQL, בלי להעתיק נתונים. בניית אב-טיפוס מהירה פי 3 בהשוואה לייצוא ל-Spark, ועלויות התשתית יורדות ב-70%—חיסכון של עד 50,000 דולר בשנה על מחשוב. איך זה עובד ומתי כדאי לעבור ל-Vertex AI—נפרט בהמשך.

טכנולוגיות: Python (bigframes, skl2bq), SQL, Vertex AI Pipelines, Docker. מתחת למכסה המנוע—אלגוריתמים סטנדרטיים של Google: מרגרסיה לוגיסטית פשוטה ועד XGBoost ו-ARIMA. מגובה על ידי BigQuery ML עם קנה מידה אוטומטי ואופטימיזציית slots מובנית. רקורד שלנו—30+ פרויקטים על GCP, כולל פינטק ומסחר אלקטרוני שבהם החיסכון בתקציב הגיע ל-70%. BigQuery ML מקצר את זמן הפיתוח פי 3 ואת העלויות ב-70% בהשוואה לגישות מסורתיות. הלקוחות שלנו חוסכים בממוצע 12,000 דולר בשנה על עלויות מחשוב.

בעיות שאנחנו פותרים

העברת נתונים מ-BigQuery לתשתית ML נפרדת היא צוואר בקבוק. תוצאות אופייניות:

• דליפות זיכרון: pandas נכשלת על 50M+ שורות—איבוד של עד 30% זמן על הנדסת נתונים.
• סחיפת נתונים: המודל מתאמן על תמונת מצב אחת, ומנבא על התפלגות חדשה—המדדים יורדים ב-15-20% ברבעון.
• אין MLOps: גרסאות מודל לא מנוטרות, אימון ידני חוזר—סיכון להתיישנות.

BigQuery ML מבטל את הבעיות האלה עם שורת CREATE MODEL אחת. נתונים אף פעם לא עוזבים את האחסון, ניהול גרסאות עובר דרך Git + תמונות מצב של BQ, וצינורות נפרסים ל-Vertex AI בלחיצה אחת. אנחנו גם משתמשים ב-DATA_SPLIT_METHOD לפיצול אוטומטי של אימון/בדיקה—זיהוי סחיפה מתרחש בשלב הוולידציה.

איך אנחנו עושים את זה: מ-SQL לצינורות ייצור

מודלים בסיסיים דרך SQL

-- Логистическая регрессия для churn prediction
CREATE OR REPLACE MODEL `project.ml_models.churn_model`
OPTIONS(
    model_type='LOGISTIC_REG',
    input_label_cols=['churned'],
    l2_reg=0.1,
    max_iterations=50,
    data_split_method='AUTO_SPLIT',
    enable_global_explain=TRUE
) AS
SELECT
    user_id,
    days_since_last_session,
    avg_session_duration_sec,
    purchases_last_30d,
    support_tickets_count,
    subscription_months,
    churned
FROM `project.features.user_churn_training`
WHERE split_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

-- Оценка модели
SELECT * FROM ML.EVALUATE(MODEL `project.ml_models.churn_model`, (
    SELECT * FROM `project.features.user_churn_test`
));

-- Предсказания
SELECT
    u.user_id,
    pred.predicted_churned,
    pred.predicted_churned_probs[OFFSET(1)].prob as churn_probability
FROM ML.PREDICT(MODEL `project.ml_models.churn_model`, (
    SELECT * FROM `project.features.users_current`
)) pred
JOIN `project.raw.users` u USING(user_id)
WHERE pred.predicted_churned_probs[OFFSET(1)].prob > 0.7
ORDER BY churn_probability DESC;

-- Feature importance через SHAP
SELECT * FROM ML.GLOBAL_EXPLAIN(MODEL `project.ml_models.churn_model`);

אופטימיזציית היפרפרמטרים מובנית—-- Логистическая регрессия для churn prediction CREATE OR REPLACE MODEL `project.ml_models.churn_model` OPTIONS( model_type='LOGISTIC_REG', input_label_cols=['churned'], l2_reg=0.1, max_iterations=50, data_split_method='AUTO_SPLIT', enable_global_explain=TRUE ) AS SELECT user_id, days_since_last_session, avg_session_duration_sec, purchases_last_30d, support_tickets_count, subscription_months, churned FROM `project.features.user_churn_training` WHERE split_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY); -- Оценка модели SELECT * FROM ML.EVALUATE(MODEL `project.ml_models.churn_model`, (SELECT * FROM `project.features.user_churn_test`)) -- Предсказания SELECT u.user_id, pred.predicted_churned, pred.predicted_churned_probs[OFFSET(1)].prob as churn_probability FROM ML.PREDICT(MODEL `project.ml_models.churn_model`, (SELECT * FROM `project.features.users_current`)) pred JOIN `project.raw.users` u USING(user_id) WHERE pred.predicted_churned_probs[OFFSET(1)].prob > 0.7 ORDER BY churn_probability DESC; -- Feature importance через SHAP SELECT * FROM ML.GLOBAL_EXPLAIN(MODEL `project.ml_models.churn_model`); מקבל CREATE MODEL, num_parallel_tree, learn_rate. כוונון עובר אוטומטית דרך max_iterations או כוונון ידני ל-gradient boosting. פרטים נוספים בתיעוד של BigQuery ML.

Gradient Boosted Trees ו-AutoML Tables

-- Gradient Boosted Trees (XGBoost под капотом)
CREATE OR REPLACE MODEL `project.ml_models.revenue_forecast`
OPTIONS(
    model_type='BOOSTED_TREE_REGRESSOR',
    num_parallel_tree=1,
    max_tree_depth=6,
    subsample=0.8,
    colsample_bytree=0.8,
    learn_rate=0.05,
    max_iterations=200,
    early_stop=TRUE,
    min_rel_progress=0.001,
    data_split_method='RANDOM',
    data_split_eval_fraction=0.2,
    input_label_cols=['revenue_next_30d']
) AS
SELECT * EXCEPT(user_id, split_date)
FROM `project.features.revenue_training`;

-- Time Series Forecasting с ARIMA_PLUS
CREATE OR REPLACE MODEL `project.ml_models.sales_forecast`
OPTIONS(
    model_type='ARIMA_PLUS',
    time_series_timestamp_col='date',
    time_series_data_col='daily_revenue',
    holiday_region='RU',
    auto_arima=TRUE,
    data_frequency='DAILY'
) AS
SELECT date, daily_revenue
FROM `project.analytics.daily_revenue`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
ORDER BY date;

-- Прогноз на 30 дней вперёд
SELECT *
FROM ML.FORECAST(MODEL `project.ml_models.sales_forecast`, STRUCT(30 AS horizon, 0.9 AS confidence_level));

ARIMA_PLUS בוחר אוטומטית סדר ועונתיות, מתחשב בחגים (פרמטר AUTO_ML). אידיאלי לחיזוי מכירות או עומס—דיוק גבוה ב-10-15% מכוונון ידני.

Python ב-BigQuery דרך Colab Enterprise

# BigQuery DataFrame API (pandas-compatible)
import bigframes.pandas as bpd
from bigframes.ml.ensemble import RandomForestClassifier
from bigframes.ml.pipeline import Pipeline
from bigframes.ml.preprocessing import StandardScaler

bpd.options.bigquery.project = "your-project"
bpd.options.bigquery.location = "EU"

# Загрузка данных — работаем с BigQuery как с pandas
df = bpd.read_gbq("SELECT * FROM `project.features.training_data`")

# Train/test split
train_df, test_df = df.train_test_split(test_size=0.2, random_state=42)
X_train = train_df.drop(columns=["label"])
y_train = train_df["label"]

# BigFrames ML Pipeline
pipeline = Pipeline([
    ("scaler", StandardScaler()),
    ("model", RandomForestClassifier(n_estimators=100, random_state=42))
])
pipeline.fit(X_train, y_train)

# Оценка
from bigframes.ml.metrics import accuracy_score
predictions = pipeline.predict(test_df.drop(columns=["label"]))
accuracy = accuracy_score(test_df["label"], predictions)
print(f"Accuracy: {accuracy:.4f}")

# Сохранение в BigQuery ML Registry
pipeline.to_gbq("project.ml_models.rf_classifier")

BigFrames מחקה את התחביר של pandas, אבל החישוב מתרחש בצד של BigQuery. אין צורך למשוך נתונים ל-Jupyter—הכל נשאר בענן.

כוונון עדין ומודלים מותאמים אישית

BigQuery ML תומך בייבוא מודלי TensorFlow (SavedModel) להסקה דרך -- Gradient Boosted Trees (XGBoost под капотом) CREATE OR REPLACE MODEL `project.ml_models.revenue_forecast` OPTIONS( model_type='BOOSTED_TREE_REGRESSOR', num_parallel_tree=1, max_tree_depth=6, subsample=0.8, colsample_bytree=0.8, learn_rate=0.05, max_iterations=200, early_stop=TRUE, min_rel_progress=0.001, data_split_method='RANDOM', data_split_eval_fraction=0.2, input_label_cols=['revenue_next_30d'] ) AS SELECT * EXCEPT(user_id, split_date) FROM `project.features.revenue_training`; -- Time Series Forecasting с ARIMA_PLUS CREATE OR REPLACE MODEL `project.ml_models.sales_forecast` OPTIONS( model_type='ARIMA_PLUS', time_series_timestamp_col='date', time_series_data_col='daily_revenue', holiday_region='RU', auto_arima=TRUE, data_frequency='DAILY' ) AS SELECT date, daily_revenue FROM `project.analytics.daily_revenue` WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY) ORDER BY date; -- Прогноз на 30 дней вперёд SELECT * FROM ML.FORECAST(MODEL `project.ml_models.sales_forecast`, STRUCT(30 AS horizon, 0.9 AS confidence_level)); . זה מאפשר שימוש בכוונון עדין על נתונים בתוך BQ בלי העתקה. לארכיטקטורות מורכבות יותר (transformers, LLM), Vertex AI עם LoRA וקוונטיזציה מתאים יותר.

Vertex AI Pipelines + BigQuery

from google.cloud import bigquery, aiplatform
from kfp import dsl
from kfp.v2.google.cloud import bigquery as kfp_bq

@dsl.pipeline(name="bq-ml-pipeline", pipeline_root="gs://ml-artifacts/pipelines")
def bq_ml_training_pipeline(
    project: str,
    dataset: str,
    model_name: str
):
    # Шаг 1: Подготовка данных
    extract_op = kfp_bq.BigqueryQueryJobOp(
        project=project,
        location="EU",
        query=f"""
        CREATE OR REPLACE TABLE `{project}.{dataset}.training_features` AS
        SELECT * FROM `{project}.features.user_features`
        WHERE dt >= DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)
        """
    )

    # Шаг 2: Обучение модели
    train_op = kfp_bq.BigqueryCreateModelJobOp(
        project=project,
        location="EU",
        query=f"""
        CREATE OR REPLACE MODEL `{project}.{dataset}.{model_name}`
        OPTIONS(model_type='BOOSTED_TREE_CLASSIFIER', input_label_cols=['label']) AS
        SELECT * EXCEPT(user_id) FROM `{project}.{dataset}.training_features`
        """
    ).after(extract_op)

    # Шаг 3: Оценка и регистрация
    evaluate_op = kfp_bq.BigqueryEvaluateModelJobOp(
        project=project,
        location="EU",
        model=train_op.outputs["model"]
    ).after(train_op)

    # Запуск пайплайна
    aiplatform.init(project="your-project", location="europe-west4")
    job = aiplatform.PipelineJob(
        display_name="bq-ml-pipeline",
        template_path="pipeline.json",
        parameter_values={"project": "your-project", "dataset": "ml", "model_name": "churn_v2"}
    )
    job.run()

Kubeflow Pipelines מטפל באורקסטרציה: הכנת נתונים, אימון, הערכה. הצינור רץ לפי לוח זמנים או טריגר. ניטור—מדדים מובנים של Vertex AI.

עלות מול ביצועים

BigQuery ML הוא אופטימלי לצוותים שיודעים SQL עם נתונים שכבר ב-GCP. הסף למעבר ל-Vertex AI Custom: צורך בארכיטקטורות לא סטנדרטיות (transformers, פונקציות loss מותאמות) או דרישות השהיה <10ms להסקה מקוונת.

מהירות פיתוח בהשוואה

BigQuery ML מאיץ את פיתוח המודלים פי 3 בהשוואה לצינורות ML מסורתיים. החיסכון הממוצע בתקציב מגיע עד 70% על ידי ביטול אשכול נפרד.

מדדי ביצועים אופייניים

• השהיית שאילתה: p50 < 2 שניות, p99 < 10 שניות עבור 100M שורות.
• תפוקה: עד 2 מיליארד שורות בשעה על slot יחיד.
• דיוק מודל: AUC >0.85 לחיזוי נטישה, RMSE <0.1 לרגרסיה.

מתי לבחור BigQuery ML לעומת Vertex AI?

אם המודל שלכם מתאים לאלגוריתמים סטנדרטיים—BQ ML נותן פיתוח מהיר פי 3 והפחתת עלויות של 50%. Vertex AI Custom מוצדק לארכיטקטורות מותאמות (LLM, GAN) או הסקה מקוונת עם השהיה נמוכה. אנחנו часто משלבים: BQ ML לבסיסים מהירים, ואז מעבירים ל-Vertex AI לייצור.

איך BigQuery ML פותר את העברת הנתונים?

הנתונים נשארים ב-BigQuery, והמודל מתאמן שם גם כן—holiday_region='RU' עובד כמו # BigQuery DataFrame API (pandas-compatible) import bigframes.pandas as bpd from bigframes.ml.ensemble import RandomForestClassifier from bigframes.ml.pipeline import Pipeline from bigframes.ml.preprocessing import StandardScaler bpd.options.bigquery.project = "your-project" bpd.options.bigquery.location = "EU" # Загрузка данных — работаем с BigQuery как с pandas df = bpd.read_gbq("SELECT * FROM `project.features.training_data`") # Train/test split train_df, test_df = df.train_test_split(test_size=0.2, random_state=42) X_train = train_df.drop(columns=["label"]) y_train = train_df["label"] # BigFrames ML Pipeline pipeline = Pipeline([ ("scaler", StandardScaler()), ("model", RandomForestClassifier(n_estimators=100, random_state=42)) ]) pipeline.fit(X_train, y_train) # Оценка from bigframes.ml.metrics import accuracy_score predictions = pipeline.predict(test_df.drop(columns=["label"])) accuracy = accuracy_score(test_df["label"], predictions) print(f"Accuracy: {accuracy:.4f}") # Сохранение в BigQuery ML Registry pipeline.to_gbq("project.ml_models.rf_classifier") . תחזיות אפשר לכתוב ישירות לטבלה. אין העתקות, ניהול גרסאות דרך Git-model ב-BQ Model Registry. זה מבטל סחיפת נתונים שנגרמת מתמונות מצב שונות ומפחית את השהיית הצינור ב-30-40%.

התהליך שלנו

1. ניתוח: ביקורת נתונים ב-BigQuery, בחירת מדדים, הגדרת baseline. זמן אופייני: 1-2 ימים.
2. עיצוב: בחירת אלגוריתם, עיצוב features, הגדרת ניסויי A/B.
3. יישום: סקריפטים של SQL, צינורות Python, בדיקות על נתונים היסטוריים.
4. בדיקות: וולידציה על מקטע holdout, בדיקת עמידות תחת עומס.
5. פריסה: רישום מודל, הגדרת ניטור, אימון חוזר אוטומטי לפי לוח זמנים.

מה כלול (תוצרים)

• אב-טיפוס מודל BigQuery ML (SQL או bigframes).
• תיעוד: דרכון מודל עם נתוני אימון, מדדים וגבולות הסקה.
• גישה: הגדרת פרויקט והרשאות.
• אינטגרציה: עם Vertex AI Pipelines (Kubeflow).
• תפעול: דשבורדים, התראות וניטור.
• הדרכה: הכשרת צוות על BigFrames וצינורות.
• תמיכה: סיוע לאחר פריסה למשך 30 יום.

למהנדסים שלנו יש 7+ שנות ניסיון ב-ML והם סיפקו 30+ פרויקטים על GCP (כולל BigQuery ML לפינטק ומסחר אלקטרוני). אנחנו מבטיחים לוחות זמנים קבועים ותהליך שקוף. גישה זו אידיאלית לזרימות עבודה אנליטיות של Google Cloud, מאפשרת מודלי SQL ML ללא ייצוא נתונים, ומשתלבת עם MLOps ב-GCP דרך Vertex AI.