frank-luongt/skills/codex/bigquery-ai

<!-- AUTO-GENERATED by export-skills.py — DO NOT EDIT -->

---

name: bigquery-ai description: BigQuery AI and ML patterns for Gemini-powered analytics. Use when building natural language SQL, ML.GENERATE_TEXT pipelines, Cortex Framework data foundations, or grounding agents in warehouse data.

Platform Note: This skill was designed for multi-agent execution. In Codex, treat sub-agent instructions as sequential steps to complete thoroughly within a single agent context.

BigQuery AI for Gemini-Powered Analytics

Use BigQuery as the data foundation for AI agents with Gemini-powered analytics, natural language SQL, and Cortex Framework enterprise data models.

When to Use

• Building natural language to SQL agents over enterprise data
• Running ML.GENERATE_TEXT for in-database LLM inference
• Deploying Cortex Framework data foundations (SAP, Salesforce, marketing)
• Creating embeddings and vector search in BigQuery for RAG
• Grounding AI agents in structured warehouse data

Patterns

1. ML.GENERATE_TEXT (In-Database LLM Inference)

-- Summarize customer feedback directly in BigQuery
SELECT
  feedback_id,
  customer_id,
  feedback_text,
  ml_generate_text_result['candidates'][0]['content']['parts'][0]['text'] AS summary
FROM
  ML.GENERATE_TEXT(
    MODEL `my_project.my_dataset.gemini_model`,
    (SELECT feedback_id, customer_id, feedback_text,
     CONCAT('Summarize this customer feedback in 2 sentences: ', feedback_text) AS prompt
     FROM `my_project.my_dataset.customer_feedback`
     WHERE DATE(created_at) = CURRENT_DATE()),
    STRUCT(256 AS max_output_tokens, 0.2 AS temperature)
  );

2. Create Remote Model Connection

-- Connect BigQuery to Vertex AI Gemini model
CREATE OR REPLACE MODEL `my_project.my_dataset.gemini_model`
  REMOTE WITH CONNECTION `my_project.us.my_connection`
  OPTIONS (ENDPOINT = 'gemini-2.0-flash');

3. Vector Search for RAG

-- Create embeddings for knowledge base articles
CREATE OR REPLACE TABLE `my_project.my_dataset.article_embeddings` AS
SELECT
  article_id,
  title,
  content,
  ml_generate_embedding_result['predictions'][0]['embeddings']['values'] AS embedding
FROM
  ML.GENERATE_EMBEDDING(
    MODEL `my_project.my_dataset.embedding_model`,
    (SELECT article_id, title, content FROM `my_project.articles`),
    STRUCT(TRUE AS flatten_json_output)
  );

-- Similarity search
SELECT
  base.article_id,
  base.title,
  distance
FROM
  VECTOR_SEARCH(
    TABLE `my_project.my_dataset.article_embeddings`,
    'embedding',
    (SELECT ml_generate_embedding_result['predictions'][0]['embeddings']['values'] AS embedding
     FROM ML.GENERATE_EMBEDDING(
       MODEL `my_project.my_dataset.embedding_model`,
       (SELECT 'How do I reset my password?' AS content)
     )),
    top_k => 5,
    distance_type => 'COSINE'
  );

4. Cortex Framework for SAP

-- Cortex Framework pre-built SAP data model
-- After deploying Cortex Framework, query normalized SAP data:

-- Sales performance from SAP S/4HANA
SELECT
  MaterialText_MAKTX AS product,
  SoldToParty_KUNAG AS customer,
  SUM(NetPrice_NETWR) AS revenue,
  COUNT(SalesDocument_VBELN) AS order_count
FROM `cortex_sap.SalesOrders`
WHERE DATE(CreationDate_ERDAT) BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY 1, 2
ORDER BY revenue DESC
LIMIT 20;

5. Python Client for Agent Integration

from google.cloud import bigquery

client = bigquery.Client()

def query_warehouse(sql: str) -> list[dict]:
    """Execute a BigQuery query and return results as dicts.
    Used as a tool function for AI agents.
    """
    query_job = client.query(sql)
    results = query_job.result()
    return [dict(row) for row in results]

# Agent tool: natural language -> SQL -> results
def answer_data_question(question: str) -> str:
    """Convert natural language to SQL and execute."""
    from google import genai

    ai_client = genai.Client(vertexai=True, project="my-project", location="us-central1")

    schema_context = get_table_schemas()  # Your schema loader

    response = ai_client.models.generate_content(
        model="gemini-2.0-flash",
        contents=f"""Given this schema:\n{schema_context}\n\n
        Generate a BigQuery SQL query to answer: {question}
        Return ONLY the SQL, no explanation.""",
    )

    sql = response.text.strip().strip("```sql").strip("```")
    results = query_warehouse(sql)
    return str(results[:20])

Anti-Patterns

• Running unbounded ML.GENERATE_TEXT queries -- always LIMIT to control costs
• Skipping column-level security for PII columns -- use BigQuery column-level access controls
• Embedding credentials in SQL -- use service accounts and IAM
• Processing one row at a time -- BigQuery excels at batch; batch LLM calls where possible

References

• BigQuery ML.GENERATE_TEXT
• BigQuery Vector Search
• Google Cloud Cortex Framework
• BigQuery MCP Server

<!-- Source: .faos/custom/skills/cloud/gcp/bigquery-ai/SKILL.md -->