n8nflow.net logo

Generate BigQuery SQL from Natural Language Queries using GPT-4o Chat

by Robert Breen•Updated: Last update 2 months ago•Source: n8n.io

Getting Started

Give business users a chat box; get back valid BigQuery SQL and live query results.
The workflow:

  1. Captures a plain-language question from a chat widget or internal portal.
  2. Fetches the current table + column schema from your BigQuery dataset (via INFORMATION_SCHEMA).
  3. Feeds both the schema and the question to GPT-4o so it can craft a syntactically correct SQL query using only fields that truly exist.
  4. Executes the AI-generated SQL in BigQuery and returns the results.
  5. Stores a short-term memory by session, enabling natural follow-up questions.

Perfect for analysts, customer-success teams, or any stakeholder who needs data without writing SQL.

⚙️ Setup Instructions

  1. Import the workflow

    • n8n → Workflows → Import from File (or Paste JSON) → Save

  2. Add credentials

ServiceWhere to create credentialsNode(s) to update
OpenAI<https://platform.openai.com> → Create API keyOpenAI Chat Model
Google BigQueryGoogle Cloud Console → IAM & Admin → Service Account JSON keyGoogle BigQuery (schema + query)

  1. Point the schema fetcher to your dataset

    • In Google BigQuery1 you’ll see:

           SELECT table_name, column_name, data_type

      FROM n8nautomation-453001.email_leads_schema.INFORMATION_SCHEMA.COLUMNS

    • Replace n8nautomation-453001.email_leads_schema with YOUR_PROJECT.YOUR_DATASET.

    • Keep the rest of the query the same—BigQuery’s INFORMATION_SCHEMA always surfaces table_name, column_name, and data_type.

  2. Update the execution node

    • Open Google BigQuery (the second BigQuery node).
    • In Project ID select your project.
    • The SQL Query field is already {{ $json.output.query }} so it will run whatever the AI returns.

  3. (Optional)Embed the chat interface

  4. Test end-to-end

    • Open the embedded chat widget.
    • Ask: “How many distinct email leads were created last week?”
    • After a few seconds the workflow will return a table of results—or an error if the schema lacks the requested fields.
    • As specific questions about your data

  5. Activate

    • Toggle Active so the chat assistant is available 24/7.

🧩 Customization Ideas

  • Row-limit safeguard : automatically append LIMIT 1000 to every query.
  • Chart rendering : send query results to Google Sheets + Looker Studio for instant visuals.
  • Slack bot : forward both the question and the SQL result to a Slack channel for team visibility.
  • Schema caching : store the INFORMATION_SCHEMA result for 24 hours to cut BigQuery costs.

Contact