Generative AI Google Brain

open-models-use-cases-bigquery-ml-llama-inference

3 min read

Use Any OSS Gen AI Model Against Your BigQuery Data

Source notebook

Repo path: open-models/use-cases/bigquery_ml_llama_inference.ipynb · Open on GitHub · advanced

Deploys Llama 3.3 70B on Vertex AI and calls it from BigQuery ML for medical transcript analytics.

Summary

This notebook demonstrates an end-to-end workflow for serving Meta’s Llama 3.3 70B model on Vertex AI and registering the endpoint as a BigQuery remote model. It loads medical transcripts into BigQuery, uses ML.GENERATE_TEXT to extract age, gender, and disease JSON, then queries the structured outputs for analytics. It also covers required setup, IAM, GPU quota considerations, and cleanup.

Key code patterns

Initialize Vertex AI with staging bucket

aiplatform.init(
    project=PROJECT_ID,
    location=REGION,
    staging_bucket=STAGING_BUCKET,
)

Sets the project, region, and Cloud Storage staging path before model deployment.

Configure vLLM serving container

vllm_args = [
    "python", "-m", "vllm.entrypoints.api_server",
    "--host=0.0.0.0", "--port=8080",
    f"--model={model_id}",
    f"--tensor-parallel-size={accelerator_count}",
    f"--gpu-memory-utilization={gpu_memory_utilization}",
    f"--max-model-len={max_model_len}",
]

Runs the open model behind a Vertex AI endpoint using vLLM with GPU parallelism and context limits.

Create BigQuery remote model

query = f"""
CREATE OR REPLACE MODEL demo_dataset.llama_3_3
REMOTE WITH CONNECTION `{PROJECT_ID}.us.demo_conn`
OPTIONS(endpoint='{ENDPOINT_ID}');
"""
query_job = client.query(query)
query_job.result()

Registers the Vertex AI endpoint so BigQuery ML can invoke it from SQL.

Generate structured JSON with BigQuery ML

ML.GENERATE_TEXT(
  MODEL `demo_dataset.llama_3_3`,
  (SELECT CONCAT('Extract the Gender, Age...', input_text) AS prompt
   FROM demo_dataset.medical_transcript),
  STRUCT(0 AS temperature, 0.001 AS top_p, 1 AS top_k,
         128 AS max_output_tokens, TRUE AS flatten_json_output))

Uses a deterministic prompt and generation settings to extract structured fields from transcripts.

Analyze generated JSON

SELECT disease, count(*) AS occurrence
FROM parsed_data, UNNEST(diseases) AS disease
WHERE LOWER(gender) = 'female' AND age >= 30
GROUP BY disease
ORDER BY occurrence DESC
LIMIT 3;

Turns LLM-extracted fields into standard SQL analytics over age, gender, and disease.

Models & APIs used

  • Models: Llama-3.3-70B-Instruct, meta-llama/Llama-3.3-70B-Instruct
  • APIs / services: Vertex AI, BigQuery, Cloud Storage, Compute Engine API
  • SDKs / libraries: google-cloud-aiplatform, google-cloud-bigquery

When to use this

Use this pattern when you need BigQuery SQL workflows to call a deployed open-source LLM for batch or micro-batch extraction over tabular data.

Gotchas & caveats

  • H100 custom model serving quota is 0 by default and must be requested for H100 deployment.
  • The Cloud Storage bucket must be in the same region as the notebook region; a multi-region bucket is not considered a match.
  • The Llama 3.3 Model Garden agreement must be accepted before using the shared model artifact URI.
  • BigQuery uses a cloud resource connection service account that needs roles/aiplatform.user on the project.
  • IAM propagation can cause remote model creation to fail temporarily; the notebook says to wait and retry.
  • Deploying the Llama 3.3 endpoint usually takes 15 to 30 minutes.
  • The BigQuery dataset and connection are created in location us, while Vertex AI deployment uses REGION.

Best practices

  • Check accelerator quota before deploying the model endpoint.
  • Use a dedicated Cloud Storage bucket or create a temporary regional bucket for experiment artifacts.
  • Grant only the needed service accounts access to Cloud Storage and Vertex AI.
  • Use low temperature and constrained output tokens for structured extraction.
  • Persist generated outputs to a BigQuery table before downstream analytics.
  • Delete Vertex AI endpoints, models, buckets, BigQuery datasets, and connections to avoid ongoing charges.

Graph View

Backlinks