GPT-4o チャットで自然言語クエリから BigQuery SQL を生成

• •OpenAI API Key

{
  "meta": {
    "instanceId": "efb474b59b0341d7791932605bd9ff04a6c7ed9941fdd53dc4a2e4b99a6f9439"
  },
  "nodes": [
    {
      "id": "1045c9ed-ad7c-45b8-94f7-27139c158f92",
      "name": "Simple Memory",
      "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 Chat Model",
      "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": "Structured Output Parser",
      "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": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -460,
        -620
      ],
      "parameters": {
        "width": 1700,
        "height": 1000,
        "content": "Talk-to-Data: Instant BigQuery SQL Generator\n### Need more help?\n- **LinkedIn:** https://www.linkedin.com/in/robertbreen\n- **Email:** rbreen@ynteractive.com  "
      },
      "typeVersion": 1
    },
    {
      "id": "9fab1e8c-d541-4972-976e-34e596818a9f",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1280,
        -620
      ],
      "parameters": {
        "color": 5,
        "width": 780,
        "height": 1000,
        "content": "\n#### ⚙️ Setup Instructions\n\n1. **Import the workflow**  \n   - n8n → **Workflows → Import from File** (or **Paste JSON**) → **Save**\n\n2. **Add credentials**  \n   | Service | Where to create credentials | Node(s) to update |\n   |---------|----------------------------|-------------------|\n   | **OpenAI** | <https://platform.openai.com> → Create API key | **OpenAI Chat Model** |\n   | **Google BigQuery** | Google Cloud Console → IAM & Admin → Service Account JSON key | **Google BigQuery** (schema + query) |\n\n3. **Point the schema fetcher to your dataset**  \n   - In **Google BigQuery1** you’ll see:  \n     ```sql\n     SELECT table_name, column_name, data_type\n     FROM `n8nautomation-453001.email_leads_schema.INFORMATION_SCHEMA.COLUMNS`\n     ```  \n   - Replace **`n8nautomation-453001.email_leads_schema`** with **`YOUR_PROJECT.YOUR_DATASET`**.  \n   - Keep the rest of the query the same—BigQuery’s `INFORMATION_SCHEMA` always surfaces `table_name`, `column_name`, and `data_type`.\n\n4. **Update the execution node**  \n   - Open **Google BigQuery** (the second BigQuery node).  \n   - In **Project ID** select your project.  \n   - The **SQL Query** field is already `{{ $json.output.query }}` so it will run whatever the AI returns.\n\n5. **(Optional)Embed the chat interface**  \n\n6. **Test end-to-end**  \n   - Open the embedded chat widget.  \n   - Ask: *“How many distinct email leads were created last week?”*  \n   - After a few seconds the workflow will return a table of results—or an error if the schema lacks the requested fields.\n   - As specific questions about your data\n\n8. **Activate**  \n   - Toggle **Active** so the chat assistant is available 24/7.\n\n"
      },
      "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": "1つのフィールドに結合",
      "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": {
    "1045c9ed-ad7c-45b8-94f7-27139c158f92": {
      "ai_memory": [
        [
          {
            "node": "82289b5e-96bf-41c9-91d0-ee70238c57da",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "3fb1381a-42b6-4459-86f1-9f4c25aba299": {
      "ai_languageModel": [
        [
          {
            "node": "82289b5e-96bf-41c9-91d0-ee70238c57da",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "4d8e1a77-bc7b-417a-a528-635c92d7dd16": {
      "main": [
        [
          {
            "node": "177be4fc-97a4-429c-8f3d-27349aad9fdd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "adc7ad4c-4a17-4be7-975b-cdc2be4c116e": {
      "main": [
        [],
        [
          {
            "node": "ab6bb5b4-8ea1-40ac-a293-213a8f03b114",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "640fb030-18d8-405b-ab1d-37d1fd625ef8": {
      "ai_outputParser": [
        [
          {
            "node": "82289b5e-96bf-41c9-91d0-ee70238c57da",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "82289b5e-96bf-41c9-91d0-ee70238c57da": {
      "main": [
        [
          {
            "node": "adc7ad4c-4a17-4be7-975b-cdc2be4c116e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "650de27a-f596-4b13-9ef7-8c86494fd9ce": {
      "main": [
        [
          {
            "node": "82289b5e-96bf-41c9-91d0-ee70238c57da",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b57350a1-cd14-424a-b027-381619e738f8": {
      "main": [
        [
          {
            "node": "4d8e1a77-bc7b-417a-a528-635c92d7dd16",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "18e74b72-1776-4d60-a81e-a2c5f589794d": {
      "main": [
        [
          {
            "node": "b57350a1-cd14-424a-b027-381619e738f8",
            "type": "main",
            "index": 0
          },
          {
            "node": "650de27a-f596-4b13-9ef7-8c86494fd9ce",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "177be4fc-97a4-429c-8f3d-27349aad9fdd": {
      "main": [
        [
          {
            "node": "650de27a-f596-4b13-9ef7-8c86494fd9ce",
            "type": "main",
            "index": 1
          }
        ]
      ]
    }
  }
}