Setting up Gemini with Google BigQuery

🔧 Step 0: Prerequisites

• You have a GCP Project

• You have permission to:

  • Enable APIs

  • Manage IAM

  • Use BigQuery and Vertex AI

• BigQuery & Vertex AI are in the same region (e.g. us-central1)

---

📡 Step 1: Enable These APIs

Go to: API Library

Enable:

• BigQuery API

• Vertex AI API

• BigQuery Connection API

---

🔌 Step 2: Create a Vertex AI Connection in BigQuery

1. Go to the BigQuery Console

2. In the left Explorer panel, find your project

3. Click the ⚙️ gear icon next to your project name

4. Click Connections

5. Click + CREATE CONNECTION

6. Select:

   • Connection Type: Vertex AI

   • Region: us-central1 (or your region)

   • Connection ID: vertex_ai_conn

   • Leave service account blank (default will be used)

7. Click Create

---

🛡 Step 3: Grant Required IAM Roles

Assign these to your user or the service account tied to the connection:

Role Name	Purpose
Vertex AI User	To access Gemini
BigQuery Admin	To create/use models
BigQuery Connection User	To access the connection

Go to: IAM Console
Click “Grant Access” → Add principal → Select roles above

---

📦 Step 4: Register Gemini as a Remote Model in BigQuery

Replace placeholders and run this SQL in BigQuery:

CREATE OR REPLACE MODEL `your_project.your_dataset.gemini_model` REMOTE WITH CONNECTION `your_project.us-central1.vertex_ai_conn` OPTIONS ( endpoint = 'publishers/google/models/gemini-1.5-pro-preview-0409', remote_service_type = 'vertex_ai' );

Replace:

• your_project: your GCP project ID

• your_dataset: an existing BigQuery dataset

• us-central1: region used in the connection

• vertex_ai_conn: your connection ID

---

💬 Step 5: Use Gemini with SQL!

Example SQL to generate text:

SELECT ML.GENERATE_TEXT( MODEL `your_project.your_dataset.gemini_model`, STRUCT('Summarize this issue: ' || ticket_text AS prompt) ) AS gemini_response FROM `your_project.support_data.tickets` LIMIT 5;

You can:

• Pass strings or concatenated columns as prompts

• Use in joins, subqueries, even CTEs

---

✨ Bonus: Try with Parameters

SELECT ML.GENERATE_TEXT( MODEL `your_project.your_dataset.gemini_model`, STRUCT( 'What are the top 3 takeaways from this feedback: ' || feedback AS prompt, 200 AS max_output_tokens, 0.7 AS temperature ) ) AS summary FROM `your_project.customer.feedbacks` LIMIT 10;