使用 GPT-4o 聊天从自然语言查询生成 BigQuery SQL

• •OpenAI API Key

{
  "meta": {
    "instanceId": "efb474b59b0341d7791932605bd9ff04a6c7ed9941fdd53dc4a2e4b99a6f9439"
  },
  "nodes": [
    {
      "id": "1045c9ed-ad7c-45b8-94f7-27139c158f92",
      "name": "简单记忆",
      "type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
      "position": [
        580,
        80
      ],
      "parameters": {
        "sessionKey": "={{ $('Embedable chat for users to ask questions of bigquery').item.json.sessionId }}",
        "sessionIdType": "customKey"
      },
      "typeVersion": 1.3
    },
    {
      "id": "3fb1381a-42b6-4459-86f1-9f4c25aba299",
      "name": "OpenAI 聊天模型",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        440,
        80
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o",
          "cachedResultName": "gpt-4o"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "ghJTvay8CvwXDsXz",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "640fb030-18d8-405b-ab1d-37d1fd625ef8",
      "name": "结构化输出解析器",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        720,
        60
      ],
      "parameters": {
        "jsonSchemaExample": "{\n\t\"query\": \"sql query and no other text\"\n}"
      },
      "typeVersion": 1.2
    },
    {
      "id": "cd9edc59-cb69-4e32-8984-026f6c0c0331",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -460,
        -620
      ],
      "parameters": {
        "width": 1700,
        "height": 1000,
        "content": "对话式数据查询：即时 BigQuery SQL 生成器"
      },
      "typeVersion": 1
    },
    {
      "id": "9fab1e8c-d541-4972-976e-34e596818a9f",
      "name": "便签 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1280,
        -620
      ],
      "parameters": {
        "color": 5,
        "width": 780,
        "height": 1000,
        "content": ""
      },
      "typeVersion": 1
    },
    {
      "id": "18e74b72-1776-4d60-a81e-a2c5f589794d",
      "name": "可嵌入聊天界面，供用户向 BigQuery 提问",
      "type": "@n8n/n8n-nodes-langchain.chatTrigger",
      "position": [
        -380,
        -280
      ],
      "webhookId": "20173599-7d16-408b-aab0-6252b05a516b",
      "parameters": {
        "options": {}
      },
      "typeVersion": 1.1
    },
    {
      "id": "b57350a1-cd14-424a-b027-381619e738f8",
      "name": "输出架构中所有表和列名",
      "type": "n8n-nodes-base.googleBigQuery",
      "position": [
        -200,
        -80
      ],
      "parameters": {
        "options": {},
        "sqlQuery": "SELECT \n  table_name,\n  column_name,\n  data_type\nFROM `n8nautomation-453001.email_leads_schema.INFORMATION_SCHEMA.COLUMNS`\n",
        "projectId": {
          "__rl": true,
          "mode": "list",
          "value": "n8nautomation-453001",
          "cachedResultUrl": "https://console.cloud.google.com/bigquery?project=n8nautomation-453001",
          "cachedResultName": "n8nAutomation"
        }
      },
      "credentials": {
        "googleBigQueryOAuth2Api": {
          "id": "92PxWUCndZ2LZK34",
          "name": "Google BigQuery account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "4d8e1a77-bc7b-417a-a528-635c92d7dd16",
      "name": "合并到一个字段",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        -40,
        -200
      ],
      "parameters": {
        "options": {},
        "aggregate": "aggregateAllItemData"
      },
      "typeVersion": 1
    },
    {
      "id": "177be4fc-97a4-429c-8f3d-27349aad9fdd",
      "name": "将表名和列转换为供代理使用的单一文本",
      "type": "n8n-nodes-base.code",
      "position": [
        140,
        -300
      ],
      "parameters": {
        "jsCode": "return [\n  {\n    json: {\n      text: items.map(item => JSON.stringify(item.json)).join('\\n'),\n    },\n  },\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "650de27a-f596-4b13-9ef7-8c86494fd9ce",
      "name": "将表名与用户问题结合",
      "type": "n8n-nodes-base.merge",
      "position": [
        280,
        -440
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineAll"
      },
      "typeVersion": 3.2
    },
    {
      "id": "82289b5e-96bf-41c9-91d0-ee70238c57da",
      "name": "AI 代理 - 编写 SQL 查询",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        460,
        -160
      ],
      "parameters": {
        "text": "=user question: {{ $('Embedable chat for users to ask questions of bigquery').item.json.chatInput }}\nTable and column names: {{ $json.text }}",
        "options": {
          "systemMessage": "=You are a helpful AI assistant that writes valid SQL queries for Google BigQuery.\n\nYou will be given:\n- A user’s question,\n- A list of available table names and column names. {{ $json.text }}\n\nYour task is to:\n1. Write a syntactically correct BigQuery SQL query that best answers the user's question,\n2. Only use table and column names that appear in the provided schema — do not guess or invent names,\n3. Make the best possible guess about which table and columns to use *from the given list only*,\n4. Return your output in a strict JSON format with one key: \"query\".\n\n⚠️ Do NOT invent table or column names.\n⚠️ If a relevant field does not exist, make the best effort to answer with what's available, or omit that part.\n⚠️ Do NOT include any explanation, notes, or comments — only the final JSON.\n\n---\n\n\n**this schema must be written before the table name  Schema:**\n\n`n8nautomation-453001.email_leads_schema.\n\noutput data in json like this. \n{\n\t\"query\": \"sql query and no other text\"\n} "
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2
    },
    {
      "id": "adc7ad4c-4a17-4be7-975b-cdc2be4c116e",
      "name": "对架构运行查询",
      "type": "n8n-nodes-base.googleBigQuery",
      "onError": "continueErrorOutput",
      "position": [
        820,
        -280
      ],
      "parameters": {
        "options": {},
        "sqlQuery": "{{ $json.output.query }}",
        "projectId": {
          "__rl": true,
          "mode": "list",
          "value": "n8nautomation-453001",
          "cachedResultUrl": "https://console.cloud.google.com/bigquery?project=n8nautomation-453001",
          "cachedResultName": "n8nAutomation"
        }
      },
      "credentials": {
        "googleBigQueryOAuth2Api": {
          "id": "92PxWUCndZ2LZK34",
          "name": "Google BigQuery account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "ab6bb5b4-8ea1-40ac-a293-213a8f03b114",
      "name": "请用户尝试其他问题",
      "type": "n8n-nodes-base.code",
      "position": [
        1080,
        40
      ],
      "parameters": {
        "jsCode": "return [\n  {\n    json: {\n      message: \"That query didn't work. Try another question.\"\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    }
  ],
  "pinData": {},
  "connections": {
    "Simple Memory": {
      "ai_memory": [
        [
          {
            "node": "AI Agent - Write SQL Query",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "AI Agent - Write SQL Query",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Combine into one field": {
      "main": [
        [
          {
            "node": "Convert table names and columns into single text for agent",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Run query against schema": {
      "main": [
        [],
        [
          {
            "node": "Ask User to try another question",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Structured Output Parser": {
      "ai_outputParser": [
        [
          {
            "node": "AI Agent - Write SQL Query",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "AI Agent - Write SQL Query": {
      "main": [
        [
          {
            "node": "Run query against schema",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "combine the table names with user question": {
      "main": [
        [
          {
            "node": "AI Agent - Write SQL Query",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Output all table, and column names in your schema": {
      "main": [
        [
          {
            "node": "Combine into one field",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Embedable chat for users to ask questions of bigquery": {
      "main": [
        [
          {
            "node": "Output all table, and column names in your schema",
            "type": "main",
            "index": 0
          },
          {
            "node": "combine the table names with user question",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert table names and columns into single text for agent": {
      "main": [
        [
          {
            "node": "combine the table names with user question",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  }
}