Retool BigQuery Search App

This repository contains a Retool application that provides a frontend for searching a BigQuery database using both keyword search and semantic vector search.

Contents

• newsletter_search_app.json: The exported Retool application (JSON).
• keyword_search.sql: The SQL query used for the keyword search functionality.
• vector_search.sql: The SQL query used for the vector search functionality.
• BIGQUERY_SETUP.md: Detailed instructions on how to set up the BigQuery database and Vector Search.

Setup Instructions

1. Import the Retool App

1. Log in to your Retool account.
2. On the Apps page, click Create new -> From JSON.
3. Upload the newsletter_search_app.json file from this repository.
4. Name your app (e.g., "BigQuery Search").
5. Click Create app.

2. Configure the BigQuery Resource

The app requires a BigQuery resource to connect to your data.

1. In Retool, go to Resources.
2. Create a new BigQuery resource.
3. Name the resource YOUR_BIGQUERY_RESOURCE (or update the resource name in the app to match your created resource).
4. Configure the authentication (Service Account is recommended) to allow access to your Google Cloud Project.
5. Important: Ensure the service account has permissions to run BigQuery jobs and access the dataset where your data and models reside.

3. Update App Configuration

1. Open the imported app in Retool.
2. You may see errors initially because the resource name or project IDs might not match.
3. If you named your resource differently than YOUR_BIGQUERY_RESOURCE, you will need to update the resource for each query (keywordSearch, vectorSearchQuery, paginationTestQuery, query1).
4. The SQL queries in the app contain placeholders YOUR_PROJECT_ID and YOUR_DATASET_ID. You must replace these with your actual GCP Project ID and BigQuery Dataset ID.
   • Select the keywordSearch query and update the SQL.
   • Select the vectorSearchQuery query and update the SQL.
   • Select the paginationTestQuery query and update the SQL.

Features

• Keyword Search: Filters results based on text matches in title, summary, source, and various entity fields.
• Vector Search: Uses BigQuery's ML.DISTANCE function to find semantically similar records based on embeddings.