BigQuery ML and AI Functions in SQL

Data analysts already know SQL. BigQuery ML lets them train models and use generative AI without moving data out of BigQuery.

flowchart LR
  T["Table shop.customers"] -- "CREATE MODEL" --> M["Model shop.churn_model<br/>logistic regression"]
  M -- "ML.PREDICT" --> P["Churn probability per customer"]
  CONN["Connection vertex-conn<br/>service account"] --> RM["Remote model shop.gemini"]
  R["Table shop.reviews"] -- "AI.GENERATE / ML.GENERATE_TEXT" --> RM
  RM --> S["Sentiment and summary per review"]
  %% step T: Training data is an ordinary BigQuery table.
  %% step M: CREATE MODEL trains a model with SQL; BigQuery handles the infrastructure.
  %% step P: ML.PREDICT scores new rows, and ML.EVALUATE reports accuracy metrics.
  %% step CONN: A Cloud resource connection has its own service account, which needs the Vertex AI User role.
  %% step RM: A remote model points BigQuery at a Gemini or embedding model on Agent Platform / Vertex AI.
  %% step R: Generative AI functions send each row’s text to the model inside a query.
  %% step S: Results come back as columns you can store, join and chart like any other data.

Classic ML in SQL

CREATE OR REPLACE MODEL shop.churn_model
OPTIONS (model_type = 'logistic_reg', input_label_cols = ['churned']) AS
SELECT tenure_months, orders_90d, avg_basket, support_tickets, churned
FROM shop.customers_training;

SELECT * FROM ML.EVALUATE(MODEL shop.churn_model);
SELECT customer_id, predicted_churned_probs
FROM ML.PREDICT(MODEL shop.churn_model, TABLE shop.customers_current);

Model types include linear and logistic regression, boosted trees, k-means clustering, time-series forecasting (ARIMA_PLUS, TimesFM) and matrix factorisation for recommendations.

Generative AI in SQL

1. Create a Cloud resource connection and grant its service account roles/aiplatform.user.
2. Create a remote model that points at a Gemini or embedding endpoint.
3. Call it from SQL.

CREATE OR REPLACE MODEL shop.gemini
REMOTE WITH CONNECTION `asia-southeast1.vertex-conn`
OPTIONS (endpoint = 'gemini-3.5-flash');

SELECT review_id, ml_generate_text_llm_result AS sentiment
FROM ML.GENERATE_TEXT(MODEL shop.gemini,
  (SELECT review_id, CONCAT('Classify the sentiment as positive, neutral or negative: ', text) AS prompt
   FROM shop.reviews LIMIT 100),
  STRUCT(TRUE AS flatten_json_output, 20 AS max_output_tokens));

Newer AI functions (for example AI.GENERATE, AI.GENERATE_BOOL, AI.CLASSIFY) make common tasks shorter, and ML.GENERATE_EMBEDDING plus VECTOR_SEARCH power semantic search over tables.

Cost and safety

• Remote model calls are billed by the model (tokens) plus BigQuery processing; test on LIMIT 100 before running on millions of rows.
• Store results in a table instead of calling the model again in every dashboard refresh.
• The connection’s service account is an identity: grant only what it needs.

bq mk --connection --location=asia-southeast1 --connection_type=CLOUD_RESOURCE vertex-conn
bq show --connection dl-data-prod.asia-southeast1.vertex-conn

Traps: forgetting to grant the connection’s service account Vertex AI User gives permission errors; running a generative function over a whole table without LIMIT can be expensive; model endpoint names change, so keep them in one place.