Native Integration
Watch this space, coming soon
Using GenAI via SQL
BigQuery now supports direct calls to large language models (LLMs) using the ML.GENERATE_TEXT() function, powered by Vertex AI.
🔧 Prerequisites
Before using LLM completions:
Enable the Vertex AI API in your GCP project.
Ensure your BigQuery project is in a supported region (e.g.,
us-central1).Assign the required IAM roles to your service account:
Vertex AI UserBigQuery Data Editor(if writing results to a table)
🧪 Basic Example
SELECT ML.GENERATE_TEXT( MODEL `your-project.us-central1.text-bison@001`, STRUCT('Write a tweet about SQL analytics' AS prompt) ) AS generated_text;This sends a prompt to the LLM and returns the completion.
⚙️ Customizing the Response
Use additional parameters for more control:
SELECT ML.GENERATE_TEXT( MODEL `your-project.us-central1.text-bison@001`, STRUCT( 'Summarise this product review:' AS prompt, 0.7 AS temperature, 256 AS max_output_tokens ) ) AS output;
temperature: randomness of response (0.0 = deterministic, 1.0 = creative)max_output_tokens: length limit of the reply
📦 Example with Table Data
SELECT review_text, ML.GENERATE_TEXT( MODEL `your-project.us-central1.text-bison@001`, STRUCT(review_text AS prompt, 0.5 AS temperature, 128 AS max_output_tokens) ) AS summary FROM my_dataset.product_reviews;
Each row sends a prompt to the LLM and returns the result.
📘 Model Options
You can use:
text-bison@001– best for general promptschat-bison@001– optimized for chat-style interactions