bigquery-ai

# BigQuery AI (`bigquery-ai`) BigQuery AI brings foundation models (Gemini, TimesFM, TabFM, EmbeddingGemma, Text/Multimodal Embeddings) and pre-trained Google Cloud AI services directly into SQL and Python (`bigframes.bigquery.ai`) with zero model training. This skill packages a verified, field-tested 4-Lens reference distilled from live-executing all 29 functions and 14 composed workflows in `bigquery-ai/`. ## Decision Tree (Lens 1 → Lens 2) 1. **Do you want free-form or structured schema output per row** (summaries, multi-field JSON/STRUCT extraction, typed scalars, token counting)? → see [`reference/general-purpose-generation.md`](reference/general-purpose-generation.md) 2. **Do you want a managed, zero-config judgment per row or group** (boolean filter `AI.IF`, numeric rating `AI.SCORE`, fixed-category label `AI.CLASSIFY`, or group synthesis `AI.AGG`)? → see [`reference/managed-functions.md`](reference/managed-functions.md) 3. **Do you need to embed text/multimodal content, compute pairwise similarity, or run vector/hybrid RRF search?** → see [`reference/embeddings-and-search.md`](reference/embeddings-and-search.md) 4. **Do you need zero-training predictions** — forecast a time series (`AI.FORECAST` on TimesFM 2.5 / 3.0), detect anomalies (`AI.DETECT_ANOMALIES`), or predict tabular targets without training (`AI.PREDICT` + `AI.EVALUATE` on TabFM)? → see [`reference/predictive-ai.md`](reference/predictive-ai.md) 5. **Do you need to explain why a summable metric moved (`AI.KEY_DRIVERS`), or estimate the causal impact of an intervention (`AI.CAUSAL_EFFECT`)?** → see [`reference/augmented-analytics.md`](reference/augmented-analytics.md) (which also routes `AI.CAUSAL_EFFECT` to [`../bigquery-ml/reference/workflows-and-pipelines.md`](../bigquery-ml/reference/workflows-and-pipelines.md)) 6. **Are you extracting structured records from PDFs/images or passing `ObjectRef` media into `AI.*` functions?** → see [`reference/document-processing.md`](reference/document-processing.md) 7. **Do you need a pre-trained Cloud AI service (`REMOTE_SERVICE_TYPE`) with a fixed API schema and per-row status column** (`ML.TRANSLATE`, `ML.UNDERSTAND_TEXT`, `ML.ANNOTATE_IMAGE`, `ML.TRANSCRIBE`, `ML.PROCESS_DOCUMENT`)? → see [`reference/cloud-ai-service-models.md`](reference/cloud-ai-service-models.md) 8. **Are you composing multiple capabilities into an end-to-end application** (RAG, hybrid catalog search, document intelligence, log triage, content moderation, metric diagnostics)? → see [`reference/workflows.md`](reference/workflows.md) If the task is ambiguous between zero-training foundation functions (`bigquery-ai`), trained models (`bigquery-ml`), or production orchestration/monitoring (`bigquery-mlops`), consult [`../bigquery-data-science/SKILL.md`](../bigquery-data-science/SKILL.md) first. ## Composing With Sister Skills (`bigquery-ml`, `bigquery-mlops`, `bigquery-data-science`) | When Your `bigquery-ai` Task Also Needs... | Combine With... | Exact Handoff Guide | | --- | --- | --- | | Feeding `AI.EMBED` or `AI.GENERATE_TABLE` features into a supervised classifier/regressor, distilling `AI.CLASSIFY` teacher labels into a fast student model (`BOOSTED_TREE_*` / `LOGISTIC_REG`), custom holiday/hierarchical forecasting (`ARIMA_PLUS`), `> 12`-dimension attribution (`CONTRIBUTION_ANALYSIS`), or causal inference (`AI.CAUSAL_EFFECT`, DiD, Synthetic Control) | **[`bigquery-ml`](../bigquery-ml/SKILL.md)** | [`../bigquery-data-science/reference/cross-skill-playbooks.md`](../bigquery-data-science/reference/cross-skill-playbooks.md) (Playbooks 1–4) | | Scheduling recurring `AI.EMBED` + `ALTER VECTOR INDEX` or `AI.FORECAST` pipelines across the 8 orchestrators, monitoring `ARRAY<FLOAT64>` embedding or output drift (`ML.VALIDATE_DATA_DRIFT`), deterministically scoring saved `AI.PREDICT` / `AI.CLASSIFY` tables (`ML.METRICS`), or joining features point-in-time (`ML.ENTITY_FEATURES_AT_TIME`) | **[`bigquery-mlops`](../bigquery-mlops/SKILL.md)** | [`../bigquery-mlops/reference/orchestration-and-pipelines.md`](../bigquery-mlops/reference/orchestration-and-pipelines.md) · [`../bigquery-mlops/reference/monitoring-drift-and-evaluation.md`](../bigquery-mlops/reference/monitoring-drift-and-evaluation.md) · [`../bigquery-mlops/reference/feature-store-and-temporal-joins.md`](../bigquery-mlops/reference/feature-store-and-temporal-joins.md) | ## Tool Wrapper & Generator Rules (SQL + `bigframes.bigquery.ai`) - **Dual Surface & Pinned Models**: Always pin the explicit `endpoint` or `model` parameter (`'gemini-2.5-flash'`, `'TimesFM 2.5'`, `'TimesFM 3.0'`, `'embeddinggemma-300m'`, `'text-embedding-005'`, `'gemini-embedding-001'`) from `skills/pinned-models.json` so silent default upgrades never shift outputs. Pair SQL calls with `bigframes.bigquery.ai` / `bigframes.ml.llm` wrappers when generating Python notebooks. - **Per-Statement Syntax Lookup (Lens 3)**: Before emitting `AI.*`, `VECTOR_SEARCH`, or `OBJ.*` calls, inspect [`syntax/INDEX.md`](syntax/INDEX.md) (or run `bigquery-data-science lookup <SYMBOL>`) to read the exact `< 25 KB` syntax card in `syntax/*.md`. - **Direct `ObjectRef` Input**: Pass `ObjectRef` columns (from an Object Table's `ref` column, `OBJ.LIST(...)`, or `OBJ.MAKE_REF(uri, connection)`) directly into `AI.GENERATE*`, `AI.CLASSIFY`, `AI.SCORE`, `AI.IF`, `AI.AGG`, and `AI.EMBED` without wrapping in `OBJ.GET_ACCESS_URL`. ## Reviewer Verification Checklist & Rubric Before returning any `bigquery-ai` SQL or Python code, verify against this checklist: - [ ] **No Retired `AI.PARSE_DOCUMENT`**: Reject any query using `AI.PARSE_DOCUMENT` (offline since 2026-06-01); replace with `ML.PROCESS_DOCUMENT` or `AI.GENERATE` + `output_schema`. - [ ] **No Non-Existent `HYBRID_SEARCH` TVF**: Use `VECTOR_SEARCH(..., options => '{"lexical_search_columns": [...]}')` or `AI.SEARCH(..., mode => 'HYBRID')` and size `top_k >= R/10` for target semantic rank `R`. - [ ] **`output_schema` Field Access**: When `output_schema` is passed to `AI.GENERATE` or `AI.GENERATE_TABLE`, select the schema field names directly (never `.result`, which disappears when `output_schema` is specified). - [ ] **Subquery Sampling Before `LIMIT`**: Materialize or subquery-limit input rows *before* invoking per-row `AI.*` scalar functions so `LIMIT` does not evaluate and bill the entire table. - [ ] **Per-Row Status Check on Cloud AI TVFs**: Queries calling `ML.PROCESS_DOCUMENT`, `ML.TRANSLATE`, `ML.UNDERSTAND_TEXT`, `ML.ANNOTATE_IMAGE`, or `ML.TRANSCRIBE` inspect `<function>_status` because per-row failures do not fail the BigQuery job. ## Cross-Cutting Gotchas (Apply Across Functions) - **`AI.PARSE_DOCUMENT` is offline and its documentation has been withdrawn** (since 2026-06-01). Always use `ML.PROCESS_DOCUMENT` (via a `CLOUD_AI_DOCUMENT_V1` remote model) or `AI.GENERATE` over `ObjectRef`. - **`output_schema` replaces the `result` field entirely** in `AI.GENERATE` and `AI.GENERATE_TABLE`. - **`LIMIT` / `OFFSET` on an outer `SELECT` does not reduce billed LLM calls** — filter or `LIMIT` inside a CTE/subquery before calling scalar `AI.*` functions. - **Pass `ObjectRef` directly to multimodal `AI.*` functions** — `OBJ.GET_ACCESS_URL` is only needed when generating short-lived signed URLs (`<= 6` hours; never persist signed URLs). - **There is no standalone `HYBRID_SEARCH` function** — hybrid RRF retrieval (`distance = 1 - (1/(60 + rank_vector) + 1/(61 + rank_lexical))`) is enabled via `lexical_search_columns` on `VECTOR_SEARCH` or `mode => 'HYBRID'` on `AI.SEARCH` over a `10 * top_k` semantic pool. - **Pin `TimesFM 2.5` or `TimesFM 3.0` on `AI.FORECAST`, `AI.DETECT_ANOMALIES`, and `AI.EVALUATE`**; materialize `AI.PREDICT` (TabFM) outputs because TabFM has no version pin and is non-deterministic across runs. - **Use `AI.COUNT_TOKENS` as a free pre-flight check** before running large paid generative batches. - **Cloud AI service models (`REMOTE_SERVICE_TYPE`) are handles, not trained models**: `CREATE MODEL` does not validate IAM permissions (`roles/serviceusage.serviceUsageConsumer` on the connection's service account is mandatory), `ML.EVALUATE` is rejected, and errors surface per row in `<function>_status`. ## 4-Lens Index - **Lens 2 — Use-Case Decision Guides (`reference/`)**: - [`reference/general-purpose-generation.md`](reference/general-purpose-generation.md) — `AI.GENERATE`, `AI.GENERATE_TEXT`, `AI.GENERATE_TABLE`, `AI.GENERATE_BOOL`, `AI.GENERATE_DOUBLE`, `AI.GENERATE_INT`, `ML.GENERATE_TEXT`, `AI.COUNT_TOKENS` - [`reference/managed-functions.md`](reference/managed-functions.md) — `AI.IF`, `AI.SCORE`, `AI.CLASSIFY`, `AI.AGG` - [`reference/embeddings-and-search.md`](reference/embeddings-and-search.md) — `AI.EMBED`, `AI.GENERATE_EMBEDDING`, `ML.GENERATE_EMBEDDING`, `AI.SIMILARITY`, `VECTOR_SEARCH`, `AI.SEARCH`, and hybrid RRF search - [`reference/predictive-ai.md`](reference/predictive-ai.md) — `AI.FORECAST`, `AI.DETECT_ANOMALIES`, `AI.PREDICT`, `AI.EVALUATE` (TimesFM & TabFM) - [`reference/augmented-analytics.md`](reference/augmented-analytics.md) — `AI.KEY_DRIVERS` and routing for `AI.CAUSAL_EFFECT` - [`reference/document-processing.md`](reference/document-processing.md) — `ML.PROCESS_DOCUMENT`, `AI.PARSE_DOCUMENT` (retired), Object Tables, `OBJ.LIST`, `OBJ.MAKE_REF`, `OBJ.FETCH_METADATA`, `OBJ.GET_ACCESS_URL` - [`reference/cloud-ai-service-models.md`](reference/cloud-ai-service-models.md) — `ML.TRANSLATE`, `ML.UNDERSTAND_TEXT`, `ML.ANNOTATE_IMAGE`, `ML.TRANSCRIBE`, `ML.PROCESS_DOCUMENT` - [`reference/workflows.md`](reference/workflows.md) — 14 composed end-to-end workflows (RAG, hybrid catalog search, document intelligence, tabular prediction, etc.) - **Lens 3 — Per-Statement Syntax Cards (`syntax/`, < 25 KB each)**: [`syntax/INDEX.md`](syntax/INDEX.md) (44 H2-sliced syntax cards) - **Lens 4 — Worked Walkthroughs (`narrative/`)**: 43 extracted notebook narratives in `narrative/*.md` (e.g., [`narrative/ai_forecast.md`](narrative/ai_forecast.md), [`narrative/ai_generate.md`](narrative/ai_generate.md), [`narrative/catalog_search.md`](narrative/catalog_search.md))