BigQuery AI & ML

BigQuery integrates with Vertex AI to provide powerful machine learning and generative AI capabilities directly within SQL queries using built-in functions like AI.FORECAST, AI.KEY_DRIVERS, AI.DETECT_ANOMALIES, and AI.GENERATE.

[!IMPORTANT] You MUST read and follow the global constraints and mandatory function routing rules in ai_function_best_practices.md before writing any BQML AI/ML SQL query.

Reference Directory

• Best Practices: ai_function_best_practices.md - Global constraints, connection settings, and mandatory syntax checks for BQML queries in notebooks.

Functions Reference

• AI.AGG (ai_agg.md)
  • SQL Shape: Aggregate
  • Requires CREATE MODEL: No
  • Usage: Multi-row semantic aggregation with GROUP BY; synthesizes 1 summary string across many rows (unlike per-row AI.GENERATE).
• AI.CAUSAL_EFFECT (ai_causal_effect.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Quantifies counterfactual post-intervention impact on a time series after a specific treatment timestamp.
• AI.CLASSIFY (ai_classify.md)
  • SQL Shape: Scalar
  • Requires CREATE MODEL: No
  • Usage: Semantic classification of text or multimodal unstructured data into predefined category labels.
• AI.DETECT_ANOMALIES (ai_detect_anomalies.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Zero-shot TimesFM detector for isolated time-series point anomalies/outliers (use ML.DETECT_CHANGE_POINTS for sustained regime shifts).
• AI.EVALUATE (ai_evaluate.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Zero-shot TimesFM time-series forecast evaluation/backtesting against a historical holdout split.
• AI.FORECAST (ai_forecast.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Zero-shot TimesFM time-series forecasting without training a custom model.
• AI.GENERATE (ai_generate.md)
  • SQL Shape: Scalar
  • Requires CREATE MODEL: No
  • Usage: Default for per-row LLM text or multimodal structured extraction (output_schema); returns STRUCT. Prefer over AI.GENERATE_TABLE.
• AI.GENERATE_EMBEDDING (ai_generate_embedding.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: Yes
  • Usage: Batch generates ARRAY<FLOAT64> vector embeddings for text or visual content using a remote embedding model.
• AI.GENERATE_TABLE (ai_generate_table.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: Yes
  • Usage: Table-valued structured extraction (FROM clause); only use instead of AI.GENERATE when a remote model + TVF is explicitly needed.
• AI.IF (ai_if.md)
  • SQL Shape: Scalar
  • Requires CREATE MODEL: No
  • Usage: Evaluates boolean semantic conditions (BOOL) on text or multimodal data for WHERE/CASE/JOIN filtering (do not use AI.GENERATE_TABLE).
• AI.KEY_DRIVERS (ai_key_drivers.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Default one-step attribution of dimensional segments driving metric changes (interest_label_col TRUE vs. FALSE). Prefer over CONTRIBUTION_ANALYSIS.
• AI.PREDICT (ai_predict.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Zero-shot TabFM foundation model for tabular classification or regression without training and managing custom models.
• AI.SCORE (ai_score.md)
  • SQL Shape: Scalar
  • Requires CREATE MODEL: No
  • Usage: Assigns a continuous FLOAT64 semantic score to text or multimodal data for ranking or sorting (ORDER BY).
• AI.SEARCH (ai_search.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Simplified semantic search using a raw string query on tables with autonomous background embeddings (AI.EMBED generated column).
• AI.SIMILARITY (ai_similarity.md)
  • SQL Shape: Scalar
  • Requires CREATE MODEL: No
  • Usage: Computes an on-the-fly semantic similarity score (FLOAT64) between two text or multimodal inputs without stored embeddings.
• Remote Models (remote_models.md)
  • SQL Shape: CREATE MODEL
  • Requires CREATE MODEL: Yes
  • Usage: Creates a Vertex AI REMOTE WITH CONNECTION DEFAULT model entity for functions that require one (AI.GENERATE_TABLE, AI.GENERATE_EMBEDDING, VECTOR_SEARCH).
• CONTRIBUTION_ANALYSIS (ml_contribution_analysis.md)
  • SQL Shape: CREATE MODEL
  • Requires CREATE MODEL: Yes
  • Usage: Model-based driver analysis (CREATE MODEL ... OPTIONS(MODEL_TYPE='CONTRIBUTION_ANALYSIS') + ML.GET_INSIGHTS). Use AI.KEY_DRIVERS unless a MODEL entity is required.
• ML.CORRELATION (ml_correlation.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Calculates statistical correlation coefficients between numeric columns, optionally sliced by dimensions.
• ML.DESCRIBE_DATA (ml_describe_data.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Generates descriptive statistics (e.g., count, mean, median, min, max, stddev, quantiles, nulls, top values) for columns in a table or subquery.
• ML.DETECT_CHANGE_POINTS (ml_detect_change_points.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Detects structural breaks or sustained regime shifts ([begin_timestamp, end_timestamp] intervals) in a time series.
• ML.SEASONALITY (ml_seasonality.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Extracts recurring periodic/seasonal components from a historical time series.
• ML.TREND (ml_trend.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: No
  • Usage: Extracts the smoothed long-term trend component from a historical time series.
• VECTOR_SEARCH (vector_search.md)
  • SQL Shape: TVF
  • Requires CREATE MODEL: Yes
  • Usage: Searches pre-computed ARRAY<FLOAT64> embedding columns between a base table and query table (paired with AI.GENERATE_EMBEDDING, unlike AI.SEARCH).