自然语言处理谷歌Bigquery数据库，text-to-SQL in Bigquery

6+1=61

于 2024-04-19 08:59:33 发布

阅读量208

点赞数 1

文章标签：数据库 python nlp

本文链接：https://blog.csdn.net/weixin_42093329/article/details/137949224

版权

实现目标：使用自然语言处理我在Bigquery中建立的数据库，并最终将结果用HTML 表格展现出来。

五分钟简易构建，用的第三方付费网站实现Text-to-SQL，调用的API接口，有7天免费试用。目前看来准确率尚可。网站链接：https://www.text2sql.ai/app

首先创建Google Cloud 项目，并创建自己的Bigquery数据库（或者使用公开数据库）

1. setup

# @title Setup
from google.colab import auth
from google.cloud import bigquery
from google.colab import data_table
import requests
import json
import pandas as pd

project = '**********' # 你的project ID
location = 'US' # Location inserted based on the query results selected to explore
client = bigquery.Client(project=project, location=location)
data_table.enable_dataframe_formatter()
auth.authenticate_user()

2. 调用API，获取SQL查询语句

#输入你想实现的命令（自然语言）
prompt = "最近3天的记录都有哪些" # @param{type: "string"}

# API endpoint URL
url = "https://www.text2sql.ai/api/sql/generate"

# 输入自己的token
token = "*******"

# Request headers
headers = {
    "Authorization": f"Bearer {token}",
    "Content-Type": "application/json"
}

# Request body (prompt and schema)
data = {
    "prompt": prompt ,
    "type": "bigquery",
    "schema": "*******" #填写你的数据库结构
}

# Convert data dictionary to JSON string
payload = json.dumps(data)

try:
    # Make POST request to the API endpoint
    response = requests.post(url, headers=headers, data=payload)

    # Check if request was successful (status code 200)
    if response.status_code == 200:
        # Parse JSON response
        result = response.json()

        # Extract SQL command from the response
        sql_command = result.get("output", "No SQL command generated")

        # Display the generated SQL command
        print("Generated SQL command:")
        print(sql_command)
    else:
        print(f"Request failed with status code {response.status_code}")
except requests.exceptions.RequestException as e:
    print(f"Request error: {e}")

3. Bigquery 查询

query_job = client.query(sql_command)

# 将查询结果转换为 Pandas DataFrame
results_df = query_job.to_dataframe()

# 显示查询结果
display(results_df)

4.生成HTML表格（可选）

html_table = results_df.to_html(index=False)  # 将 DataFrame 转换为 HTML 表格

# 创建 HTML 页面内容
html_content = f"""
<!DOCTYPE html>
<html>
<head>
    <title>Query Results</title>
    <style>
        table {{
            border-collapse: collapse;
            width: 100%;
        }}
        th, td {{
            border: 1px solid black;
            padding: 8px;
            text-align: left;
        }}
        th {{
            background-color: #f2f2f2;
        }}
    </style>
</head>
<body>
    <h2>Query Results</h2>
    {html_table}  <!-- 插入 HTML 表格 -->
</body>
</html>
"""

# 将 HTML 内容保存到文件中
with open('query_results.html', 'w') as f:
    f.write(html_content)

print(html_content)