Turn natural language into BigQuery SQL with n8n + GPT-4o

Chat input → fetch BigQuery schema → GPT-4o generates SQL → execute query → return results

Who

Analysts, customer-success staff, or business users who need data but don't write SQL

You get

Ask questions in natural language via a chat widget and get executable BigQuery SQL plus live results

You need

n8n · google-bigquery

Steps

1. Import the workflow

   In n8n, go to Workflows → Import from File (or Paste JSON), upload the template workflow.json, and save.

2. Add credentials

   Create two credentials in n8n and assign them to the relevant nodes: 1) OpenAI: create an openAiApi credential with your API key; select it in the OpenAI Chat Model node. 2) Google BigQuery: download a Service Account JSON key from Google Cloud Console → IAM & Admin, create a googleBigQueryOAuth2Api credential, and select it in both Google BigQuery nodes (schema fetch and query execution).

3. Point the schema fetcher to your dataset

   Open the first Google BigQuery node (usually named Output all table, and column names in your schema). Replace the project and dataset in its SQL with your own: change `n8nautomation-453001.email_leads_schema` to `YOUR_PROJECT.YOUR_DATASET`, keeping `INFORMATION_SCHEMA.COLUMNS` and column names unchanged.

   Execution prompt · Source text。Google BigQuery node (schema fetch) SQL Query field; replace project/dataset before running

   Copy prompt

   SELECT 
     table_name,
     column_name,
     data_type
   FROM `n8nautomation-453001.email_leads_schema.INFORMATION_SCHEMA.COLUMNS`

4. Update the execution node

   Open the second Google BigQuery node (Run query against schema). Select your project in Project ID; confirm the SQL Query field is already `{{ $json.output.query }}` so it runs whatever the AI returns.

5. Configure the AI Agent prompts

   Open the AI Agent - Write SQL Query node and verify its parameters: - The user input template (text) receives the chat question and schema text. - The systemMessage defines model behavior: use only provided schema tables/columns, do not invent fields, output strict JSON {"query":"..."}. Note: the systemMessage hardcodes the schema prefix `n8nautomation-453001.email_leads_schema.`; you must replace this with your own project.dataset.

   Execution prompt · Source text。AI Agent - Write SQL Query node text field (user input template)

   Copy prompt

   =user question: {{ $('Embedable chat for users to ask questions of bigquery').item.json.chatInput }}
   Table and column names: {{ $json.text }}

   Execution prompt · Source text。AI Agent - Write SQL Query node options.systemMessage field

   Copy prompt

   =You are a helpful AI assistant that writes valid SQL queries for Google BigQuery.
   
   You will be given:
   - A user’s question,
   - A list of available table names and column names. {{ $json.text }}
   
   Your task is to:
   1. Write a syntactically correct BigQuery SQL query that best answers the user's question,
   2. Only use table and column names that appear in the provided schema — do not guess or invent names,
   3. Make the best possible guess about which table and columns to use *from the given list only*,
   4. Return your output in a strict JSON format with one key: "query".
   
   ⚠️ Do NOT invent table or column names.
   ⚠️ If a relevant field does not exist, make the best effort to answer with what's available, or omit that part.
   ⚠️ Do NOT include any explanation, notes, or comments — only the final JSON.
   
   ---
   
   
   **this schema must be written before the table name  Schema:**
   
   `n8nautomation-453001.email_leads_schema.
   
   output data in json like this. 
   {
   	"query": "sql query and no other text"
   }

6. Test end-to-end and activate

   Open the embedded chat widget and ask a natural-language question (e.g., "How many distinct email leads were created last week?"). After a few seconds the workflow returns a results table, or an error if the schema lacks the requested fields. Once tested, toggle the workflow to Active so it stays available.

You are an n8n workflow setup assistant. The user wants to replicate the natural-language-to-BigQuery-SQL template. First confirm they have: 1) n8n instance access, 2) OpenAI API key, 3) Google Cloud project with BigQuery dataset permissions. Then guide them in this order: import workflow.json → add openAiApi and googleBigQueryOAuth2Api credentials → replace project.dataset in the schema fetch node → confirm project and SQL Query variable in the execution node → check if the hardcoded schema prefix in the AI Agent systemMessage needs replacement → suggest a test question and remind them to activate. Do not generate or send API keys for the user.