CREATE OR REPLACE MODEL `triple-shift-469512-k6.github_issues_dataset.textembedding`
REMOTE WITH CONNECTION `projects/triple-shift-469512-k6/locations/us/connections/bqml_connection`
OPTIONS (
  ENDPOINT = 'gemini-embedding-001'
);

kind: source
spec:
  name: github
  path: cloudquery/github
  registry: cloudquery
  version: "v14.1.1"
  tables: ["github_issues"]
  destinations: ["bigquery"]
  spec:
    access_token: "${GITHUB_TOKEN}" # Personal Access Token, required if not using App Authentication.
    repos: ["cloudquery/cloudquery"]  
    table_options:
      github_issues:
        state: "open" # e.g. "open, all, closed", defaults to `all`

---
kind: destination
spec:
  name: bigquery
  path: cloudquery/bigquery
  registry: cloudquery
  version: "v4.3.9"
  write_mode: "append"
  spec:
    project_id: ai-playground
    dataset_id: github_issues

    text_embeddings:
      tables:
        - source_table_name: github_issues
          target_table_name: github_issues_embeddings
          embed_columns: ["title", "body"] # list of columns to embed
      text_splitter:
        recursive_text: # default text splitter
          chunk_size: 1000
          chunk_overlap: 500
      remote_model_name: "textembedding"

cloudquery sync github_to_bigquery.yaml

$ cloudquery sync github_to_bigquery.yaml
Loading spec(s) from github_to_bigquery.yaml
Starting sync for: github (cloudquery/[email protected]) -> [bigquery (cloudquery/[email protected])]
Sync completed successfully. Resources: 157, Errors: 0, Warnings: 0, Time: 31s

from google import genai
from google.cloud import bigquery
import sys
import os
from dotenv import load_dotenv

load_dotenv()

PROJECT_ID = os.getenv("PROJECT_ID")
LOCATION = os.getenv("LOCATION")
DATASET_ID = os.getenv("DATASET_ID")
TARGET_TABLE_ID = os.getenv("TARGET_TABLE_ID", "github_issues_embeddings")
REMOTE_MODEL_NAME = os.getenv("REMOTE_MODEL_NAME", "textembedding")
GEMINI_MODEL = os.getenv("GEMINI_MODEL", "gemini-2.5-flash")
TOP_K = int(os.getenv("TOP_K", "5"))

def generate_query_embedding(query_text):
    """Generate embedding for the search query using the remote model."""
    
    embedding_query = f"""
    SELECT
      ml_generate_embedding_result AS embedding
    FROM
      ML.GENERATE_EMBEDDING(
        MODEL `{PROJECT_ID}.{DATASET_ID}.{REMOTE_MODEL_NAME}`,
        (
          SELECT '{query_text}' AS content, 'query' AS title
        )
      )
    """
    
    client = bigquery.Client()
    query_job = client.query(embedding_query)
    results = query_job.result()
    
    for row in results:
        return row.embedding
    
    raise ValueError("No embedding generated for query")

def search_documents(query_text, top_k=None):
    """Search for relevant documents using VECTOR_SEARCH with cosine distance."""
    
    if top_k is None:
        top_k = TOP_K
    
    client = bigquery.Client()
    query_embedding = generate_query_embedding(query_text)
    search_query = f"""
    SELECT
      base.chunk_id as chunk_id,
      base.chunk_text as chunk_text
    FROM VECTOR_SEARCH(
      TABLE `{PROJECT_ID}.{DATASET_ID}.{TARGET_TABLE_ID}`,
      'embedding',
      (
        SELECT ARRAY<FLOAT64>{query_embedding} AS embedding
      ),
      'embedding',
      top_k => {top_k},
      distance_type => 'COSINE'
    )
    """
    
    query_job = client.query(search_query)
    results = query_job.result()

    return [
        {
            'chunk_id': row.chunk_id,
            'chunk_text': row.chunk_text,
        }
        for row in results
    ]

def generate_response(query_text, search_results):
    """Generate a response using Gemini based on search results."""
    
    if not search_results:
        return "I couldn't find any relevant information to answer your question."
    
    client = genai.Client(vertexai=PROJECT_ID, project=PROJECT_ID, location=LOCATION)
    
    context = "\n\n".join([
        f"(chunk {result['chunk_id']}): {result['chunk_text']}"
        for result in search_results
    ])
    
    prompt = f"""
    Based on the following context, please answer the user's question. 
    If the context doesn't contain enough information to answer the question, 
    please say so clearly.
    
    Context:
    {context}
    
    User Question: {query_text}
    
    Please provide a comprehensive answer based on the context provided.
    """
    
    response = client.models.generate_content(model=GEMINI_MODEL, contents=prompt)
    return response.text

def main():
    query = sys.argv[1]
    print(generate_response(query, search_documents(query)))

if __name__ == "__main__":
    main()

$ python search.py "Summarise any feature requests for the BigQuery destination plugin"
Based on the provided context, here are the feature requests for the BigQuery destination plugin:

1.  **Add overwrite or overwrite-delete-stale write modes:**
    *   **Problem:** Currently lacks support for incremental syncs in the BigQuery destination plugin.
    *   **Solution:** Introduce `write` or `overwrite-delete-stale` write modes to enable incremental data synchronization.

2.  **Full CDC (Change Data Capture) support for PostgreSQL -> BigQuery:**
    *   **Problem:** Existing CDC support from PostgreSQL to BigQuery is partial, specifically missing handling for `DELETE` operations, `UPDATE` operations, and DML (Data Manipulation Language) for creating tables. There's also a need for benchmarking and stress testing to ensure BigQuery limits are not hit under high load.
    *   **Solution:** Enhance CDC to fully support `DELETE`, `UPDATE`, and DML operations, and ensure performance under high load, potentially by exploring solutions like Datastream or Debezium.

3.  **Support time types in BigQuery destination:**
    *   **Problem:** Inserting Arrow `time64_ns` types into BigQuery results in an error on Linux.
    *   **Solution:** Enable proper support for time types, specifically `time64_ns`, to be inserted into BigQuery without errors.