面对云端 Parquet 数据：零下载，直接查，只拉需要的列，快 10-50 倍。
面对云端 CSV/JSON 数据：免手动下载，一条 SQL 搞定，适合中小文件。

INSTALL httpfs;   -- 只需安装一次
LOAD httpfs;      -- 每次会话需要加载

-- 查询 TMDB 电影数据集的评分分布
SELECT 
    genre,
    ROUND(AVG(vote_average), 2) AS avg_rating,
    ROUND(AVG(vote_count), 0) AS avg_votes,
    COUNT(*) AS movie_count
FROM read_parquet(
    'https://huggingface.co/datasets/TMDB/tmdb-movie-metadata/resolve/main/data/movies.parquet'
)
WHERE vote_count > 50
GROUP BY genre
ORDER BY avg_rating DESC
LIMIT 10;

-- 直接分析 GitHub 上的公开事件数据
SELECT 
    strftime(date, '%Y-%m') AS month,
    COUNT(*) AS event_count,
    COUNT(DISTINCT repo_name) AS repos
FROM read_csv_auto(
    'https://raw.githubusercontent.com/example/public-data/main/events.csv'
)
WHERE date >= '2026-01-01'
GROUP BY month
ORDER BY month;

-- 查询 S3 上某个日期范围的所有 Parquet 文件
SELECT 
    region,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS customers
FROM read_parquet(
    'https://data-bucket.s3.amazonaws.com/sales/*/2026/05/*/*.parquet'
)
WHERE amount > 0
GROUP BY region
ORDER BY total_revenue DESC;

#!/usr/bin/env python3
"""
DuckDB 远程文件查询演示
功能：从 HuggingFace 远程 Parquet 数据集查询评分 Top 电影
前置条件：pip install duckdb
"""

import duckdb
import sys
import time

def main():
    # 连接到内存数据库
    con = duckdb.connect()
    
    # 启用 httpfs 扩展
    con.execute("INSTALL httpfs")
    con.execute("LOAD httpfs")
    
    # 可选：配置 httpfs 参数
    con.execute("SET httpfs_retry_count = 3")
    con.execute("SET httpfs_timeout = 30")
    
    # 远程 Parquet URL（HuggingFace 上的 TMDB 电影数据）
    remote_url = (
        "https://huggingface.co/datasets/TMDB/"
        "tmdb-movie-metadata/resolve/main/data/movies.parquet"
    )
    
    print(f"🔍 正在远程查询: {remote_url}")
    print(f"⏳ 只传输需要的列，而非整个文件...\n")
    
    start = time.time()
    
    # 查询：只请求需要的列，DuckDB 通过 Range Request 逐列拉取
    result = con.execute(f"""
        SELECT 
            title,
            vote_average,
            vote_count,
            release_date,
            genres
        FROM read_parquet('{remote_url}')
        WHERE vote_count > 100
          AND vote_average > 7.0
        ORDER BY vote_average DESC
        LIMIT 20
    """).fetchdf()
    
    elapsed = time.time() - start
    
    print(f"✅ 查询完成，耗时 {elapsed:.2f} 秒")
    print(f"📊 返回 {len(result)} 条记录\n")
    
    # 显示结果
    print("=" * 80)
    print(f"{'排名':<4} {'电影标题':<40} {'评分':<6} {'票数':<8} {'类型'}")
    print("-" * 80)
    for i, row in result.iterrows():
        title = row['title'][:38] + '..' if len(str(row['title'])) > 38 else row['title']
        genres = str(row['genres'])[:30] if row['genres'] else 'N/A'
        print(f"{i+1:<4} {title:<40} {row['vote_average']:<6.1f} {row['vote_count']:<8} {genres}")
    
    # 导出到本地 CSV
    output_path = "top_movies.csv"
    con.execute(f"""
        COPY (
            SELECT * FROM read_parquet('{remote_url}')
            WHERE vote_count > 100 AND vote_average > 7.0
            ORDER BY vote_average DESC
            LIMIT 20
        ) TO '{output_path}' (HEADER, DELIMITER ',')
    """)
    print(f"\n💾 结果已导出: {output_path}")
    
    # 查询统计信息（利用 Parquet 元数据，几乎零传输）
    stats = con.execute(f"""
        SELECT 
            COUNT(*) AS total_movies,
            ROUND(AVG(vote_average), 2) AS avg_rating,
            ROUND(AVG(vote_count), 0) AS avg_vote_count,
            MIN(release_date) AS earliest,
            MAX(release_date) AS latest
        FROM read_parquet('{remote_url}')
    """).fetchone()
    
    print(f"\n📈 数据集概览（元数据查询）")
    print(f"   电影总数: {stats[0]:,}")
    print(f"   平均评分: {stats[1]}")
    print(f"   平均票数: {stats[2]:,.0f}")
    print(f"   时间范围: {stats[3]} ~ {stats[4]}")
    
    con.close()

if __name__ == "__main__":
    main()

pip install duckdb pandas
python3 duckdb_remote_query.py

# 启动 DuckDB CLI
duckdb

# 在 CLI 中执行
INSTALL httpfs;
LOAD httpfs;

SELECT title, vote_average, vote_count
FROM read_parquet('https://huggingface.co/datasets/TMDB/tmdb-movie-metadata/resolve/main/data/movies.parquet')
WHERE vote_count > 1000
ORDER BY vote_average DESC
LIMIT 10;

-- 设置重试次数（网络不稳定时）
SET httpfs_retry_count = 5;

-- 设置请求超时（秒）
SET httpfs_timeout = 60;

-- 启用 S3 兼容（MinIO、阿里云OSS等）
SET s3_region = 'us-east-1';
SET s3_access_key_id = '...';
SET s3_secret_access_key = '...';
SET s3_endpoint = 'https://my-minio-server.com';

-- 将远程 Parquet 的过滤结果写入本地表
CREATE TABLE local_movies AS
SELECT * FROM read_parquet('https://.../movies.parquet')
WHERE year >= 2020;

-- 现在本地表可以快速反复查询
SELECT genre, COUNT(*) FROM local_movies GROUP BY genre;

-- 远程 Parquet + 本地 CSV 联合分析
SELECT 
    r.region,
    r.revenue,
    l.store_name
FROM read_parquet('https://s3-bucket/revenue/*.parquet') r
JOIN read_csv_auto('local_stores.csv') l
    ON r.store_id = l.store_id
WHERE r.date >= '2026-01-01';

-- AWS S3（需要配置凭证）
SELECT * FROM read_parquet('s3://my-bucket/sales/*.parquet');

-- MinIO / 阿里云OSS / 腾讯云COS
SELECT * FROM read_parquet('s3://my-bucket/data/*.parquet');