# 安装 Docker
curl -fsSL https://get.docker.com | sh
sudo systemctl enable --now docker

# 安装 Docker Compose
sudo curl -L "https://github.com/docker/compose/releases/latest/download/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
docker compose version

mkdir -p ~/metabase-ai && cd ~/metabase-ai

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    country VARCHAR(50),
    is_premium BOOLEAN DEFAULT FALSE
);

-- 创建产品表
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 创建订单表
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入示例用户数据
INSERT INTO users (username, email, country, is_premium) VALUES
('张三', 'zhangsan@example.com', 'CN', TRUE),
('李四', 'lisi@example.com', 'CN', FALSE),
('王五', 'wangwu@example.com', 'US', TRUE),
('赵六', 'zhaoliu@example.com', 'JP', FALSE),
('钱七', 'qianqi@example.com', 'KR', TRUE),
('孙八', 'sunba@example.com', 'CN', FALSE),
('周九', 'zhoujiu@example.com', 'DE', TRUE),
('吴十', 'wushi@example.com', 'FR', FALSE),
('郑十一', 'zheng11@example.com', 'CN', TRUE),
('陈十二', 'chen12@example.com', 'US', FALSE) ON CONFLICT DO NOTHING;

-- 插入示例产品数据
INSERT INTO products (name, category, price, stock) VALUES
('机械键盘', '电子产品', 299.00, 150),
('无线鼠标', '电子产品', 89.00, 300),
('显示器支架', '办公用品', 199.00, 80),
('USB-C 集线器', '电子产品', 149.00, 200),
('人体工学椅', '办公家具', 1299.00, 50),
('降噪耳机', '电子产品', 599.00, 120),
('桌面收纳盒', '办公用品', 49.00, 500),
('摄像头', '电子产品', 399.00, 90),
('笔记本支架', '办公用品', 79.00, 250),
('蓝牙音箱', '电子产品', 249.00, 180) ON CONFLICT DO NOTHING;

-- 插入示例订单数据
INSERT INTO orders (user_id, product_id, quantity, total_amount, status) VALUES
(1, 1, 1, 299.00, 'completed'),
(2, 2, 2, 178.00, 'completed'),
(3, 3, 1, 199.00, 'shipped'),
(1, 6, 1, 599.00, 'pending'),
(4, 5, 1, 1299.00, 'completed'),
(5, 4, 3, 447.00, 'shipped'),
(6, 7, 5, 245.00, 'completed'),
(7, 8, 1, 399.00, 'pending'),
(8, 9, 2, 158.00, 'completed'),
(9, 10, 1, 249.00, 'shipped'),
(2, 1, 1, 299.00, 'completed'),
(3, 6, 2, 1198.00, 'completed'),
(10, 2, 4, 356.00, 'pending'),
(5, 5, 1, 1299.00, 'shipped'),
(1, 9, 2, 158.00, 'completed') ON CONFLICT DO NOTHING;

# docker-compose.yml 片段
services:
  postgres:
    image: postgres:16-alpine
    container_name: mb-postgres
    environment:
      POSTGRES_DB: metabase_db
      POSTGRES_USER: mb_user
      POSTGRES_PASSWORD: mb_secure_pass_2026
    ports:
      - "5432:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    restart: unless-stopped

volumes:
  pg_data:

# 启动数据库
docker compose up -d postgres

# 验证数据
docker exec -it mb-postgres psql -U mb_user -d metabase_db -c "SELECT count(*) FROM users;"
docker exec -it mb-postgres psql -U mb_user -d metabase_db -c "SELECT count(*) FROM orders;"

  metabase:
    image: metabase/metabase:latest
    container_name: mb-metabase
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: metabase_db
      MB_DB_PORT: 5432
      MB_DB_USER: mb_user
      MB_DB_PASS: mb_secure_pass_2026
      MB_DB_HOST: postgres
      MB_SITE_URL: http://your-domain.com:3000
      MB_AUTO_SETUP: "true"
      MB_SECRET_KEY: super-secret-key-change-this
    ports:
      - "3000:3000"
    depends_on:
      - postgres
    restart: unless-stopped

# nl2sql_bridge/app.py
"""NL2SQL Bridge — 自然语言到 SQL 查询的转换层"""

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from typing import Optional, List, Dict, Any
import psycopg2
import json
import os
import httpx

app = FastAPI(title="NL2SQL Bridge", version="1.0.0")

# 数据库配置
DB_CONFIG = {
    "dbname": os.getenv("DB_NAME", "metabase_db"),
    "user": os.getenv("DB_USER", "mb_user"),
    "password": os.getenv("DB_PASS", "mb_secure_pass_2026"),
    "host": os.getenv("DB_HOST", "postgres"),
    "port": os.getenv("DB_PORT", "5432"),
}

# Ollama 配置
OLLAMA_URL = os.getenv("OLLAMA_URL", "http://localhost:11434")
MODEL_NAME = os.getenv("LLM_MODEL", "llama3.2:3b")

class QueryRequest(BaseModel):
    question: str
    context: Optional[str] = None
    max_tokens: int = 1024
    temperature: float = 0.1

class QueryResponse(BaseModel):
    success: bool
    sql: Optional[str] = None
    results: Optional[List[Dict[str, Any]]] = None
    columns: Optional[List[str]] = None
    row_count: Optional[int] = None
    error: Optional[str] = None
    latency_ms: Optional[float] = None

def get_database_schema() -> str:
    """获取数据库表结构，用于 LLM 上下文"""
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()
    cur.execute("""
        SELECT table_name, column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_schema = 'public'
        ORDER BY table_name, ordinal_position;
    """)
    rows = cur.fetchall()
    cur.close()
    conn.close()

    schema = "可用的数据表及字段：\n\n"
    current_table = ""
    for table, col, dtype, nullable in rows:
        if table != current_table:
            schema += f"\n📋 表: {table}\n"
            current_table = table
        nullable_str = "NULL" if nullable == "YES" else "NOT NULL"
        schema += f"  • {col} ({dtype}) [{nullable_str}]\n"
    return schema

def query_ollama(question: str, schema: str) -> str:
    """调用本地 LLM 生成 SQL"""
    prompt = f"""你是一个专业的 SQL 查询生成助手。根据以下数据库结构和用户问题，生成对应的 SQL 查询。

## 数据库结构
{schema}

## 用户问题
{question}

## 要求
1. 只生成 SQL 语句，不要包含其他解释
2. 使用标准的 PostgreSQL 语法
3. 如果问题是关于统计的，使用 COUNT/SUM/AVG 等聚合函数
4. 如果涉及日期，注意使用正确的日期格式
5. 如果问题不明确，生成一个合理的默认查询
6. SQL 必须以分号结尾
7. 只输出 SQL，不要输出 markdown 代码块标记

请生成 SQL："""

    response = httpx.post(
        f"{OLLAMA_URL}/api/generate",
        json={
            "model": MODEL_NAME,
            "prompt": prompt,
            "stream": False,
            "options": {
                "temperature": 0.1,
                "max_tokens": 1024,
            }
        },
        timeout=30.0
    )
    response.raise_for_status()
    return response.json()["response"]

def execute_sql(sql: str) -> Dict[str, Any]:
    """执行 SQL 查询并返回结果"""
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()

    try:
        cur.execute(sql)
        if cur.description:
            columns = [desc[0] for desc in cur.description]
            rows = cur.fetchall()
            results = [dict(zip(columns, row)) for row in rows]
        else:
            columns = []
            results = []
            conn.commit()

        return {
            "success": True,
            "columns": columns,
            "results": results,
            "row_count": len(results),
        }
    except Exception as e:
        return {
            "success": False,
            "error": str(e),
        }
    finally:
        cur.close()
        conn.close()

@app.get("/health")
async def health_check():
    return {"status": "healthy", "service": "nl2sql-bridge"}

@app.post("/query", response_model=QueryResponse)
async def query(request: QueryRequest):
    import time
    start = time.time()

    try:
        # 获取数据库结构
        schema = get_database_schema()

        # 调用 LLM 生成 SQL
        sql = query_ollama(request.question, schema)

        # 清理可能的 markdown 标记
        sql = sql.strip()
        if sql.startswith("```"):
            sql = sql.split("\n", 1)[1].rsplit("`", 1)[0] if "`" in sql.split("\n", 1)[1] else sql.split("\n", 1)[1]
        sql = sql.rstrip(";").strip()

        # 执行查询
        result = execute_sql(sql + ";")
        result["sql"] = sql

        latency = (time.time() - start) * 1000
        result["latency_ms"] = round(latency, 2)

        return QueryResponse(**result)

    except Exception as e:
        latency = (time.time() - start) * 1000
        raise HTTPException(status_code=500, detail=f"查询失败: {str(e)}, 耗时: {round(latency, 2)}ms")

@app.get("/schema")
async def get_schema():
    """获取当前数据库结构（供调试）"""
    return {"schema": get_database_schema()}

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="0.0.0.0", port=8080)

# nl2sql_bridge/Dockerfile
FROM python:3.12-slim

WORKDIR /app

RUN apt-get update && apt-get install -y --no-install-recommends \
    gcc libpq-dev && \
    rm -rf /var/lib/apt/lists/*

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY . .

EXPOSE 8080

CMD ["uvicorn", "app:app", "--host", "0.0.0.0", "--port", "8080"]

# nl2sql_bridge/requirements.txt
fastapi==0.115.0
uvicorn==0.30.6
psycopg2-binary==2.9.9
httpx==0.27.2
pydantic==2.9.1

  nl2sql-bridge:
    build: ./nl2sql_bridge
    container_name: mb-nl2sql
    ports:
      - "8080:8080"
    environment:
      DB_NAME: metabase_db
      DB_USER: mb_user
      DB_PASS: mb_secure_pass_2026
      DB_HOST: postgres
      DB_PORT: "5432"
      OLLAMA_URL: http://ollama:11434
      LLM_MODEL: llama3.2:3b
    depends_on:
      - postgres
      - ollama
    restart: unless-stopped

  ollama:
    image: ollama/ollama:latest
    container_name: mb-ollama
    ports:
      - "11434:11434"
    volumes:
      - ollama_data:/root/.ollama
    restart: unless-stopped

volumes:
  pg_data:
  ollama_data:

# 下载轻量级模型（适合 VPS）
docker exec -it mb-ollama ollama pull llama3.2:3b

# 或者使用更强大的模型（需要更多内存）
# docker exec -it mb-ollama ollama pull qwen2.5:7b

# 用中文提问
curl -X POST http://your-vps-ip:8080/query \
  -H "Content-Type: application/json" \
  -d '{
    "question": "上个月总销售额是多少？按产品类别分组"
  }'

{
  "success": true,
  "sql": "SELECT p.category AS 类别, SUM(o.total_amount) AS 总销售额, COUNT(o.id) AS 订单数 FROM orders o JOIN products p ON o.product_id = p.id WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days' GROUP BY p.category ORDER BY 总销售额 DESC;",
  "results": [
    {"类别": "电子产品", "总销售额": 2345.00, "订单数": 8},
    {"类别": "办公用品", "总销售额": 651.00, "订单数": 4}
  ],
  "columns": ["类别", "总销售额", "订单数"],
  "row_count": 2,
  "latency_ms": 1247.5
}

server {
    listen 80;
    server_name bi.your-domain.com;

    # Metabase
    location / {
        proxy_pass http://localhost:3000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_read_timeout 86400s;
    }

    # NL2SQL API
    location /api/ {
        proxy_pass http://localhost:8080;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }
}

# 使用 Certbot 申请免费 SSL 证书
sudo apt install certbot python3-certbot-nginx -y
sudo certbot --nginx -d bi.your-domain.com

# 在 docker-compose.yml 中添加
  metabase:
    environment:
      # 启用身份验证
      MB_SITE_URL: https://bi.your-domain.com
      # 限制并发会话
      MB_SESSION_DURATION_MINUTES: 480
      # 启用审计日志
      MB_AUDIT_ENABLED: "true"

#!/bin/bash
# backup-metabase.sh
BACKUP_DIR="/opt/backups/metabase"
DATE=$(date +%Y%m%d_%H%M%S)

# 备份 PostgreSQL
docker exec mb-postgres pg_dump -U mb_user metabase_db > "$BACKUP_DIR/db_$DATE.sql"

# 备份 Metabase 应用数据
docker cp mb-metabase:/metabase.db "$BACKUP_DIR/metabase_$DATE.db"

# 保留最近 30 天的备份
find "$BACKUP_DIR" -name "*.sql" -mtime +30 -delete
find "$BACKUP_DIR" -name "*.db" -mtime +30 -delete

echo "Backup completed: $DATE"

crontab -e
# 每天凌晨 3 点备份
0 3 * * * /opt/scripts/backup-metabase.sh >> /var/log/metabase-backup.log 2>&1

# 根据查询复杂度自动选择模型
def select_model(question: str) -> str:
    """简单查询用轻量模型，复杂分析用强大模型"""
    keywords_complex = ["趋势", "对比", "预测", "关联", "回归"]
    if any(kw in question for kw in keywords_complex):
        return "qwen2.5:7b"
    return "llama3.2:3b"

import hashlib
import redis

def cached_query(question: str, sql: str) -> Optional[List]:
    """缓存相同问题的查询结果"""
    cache_key = hashlib.md5(sql.encode()).hexdigest()
    redis_client = redis.Redis(host='cache', port=6379)
    
    cached = redis_client.get(f"nl2sql:{cache_key}")
    if cached:
        return json.loads(cached)
    
    # 执行查询并缓存（TTL 5 分钟）
    result = execute_sql(sql)
    redis_client.setex(f"nl2sql:{cache_key}", 300, json.dumps(result))
    return result

<!-- 嵌入 Metabase 仪表板 -->
<iframe
  src="https://bi.your-domain.com/embed/dashboard/YOUR_DASHBOARD_ID"
  width="100%"
  height="600"
  frameborder="0">
</iframe>