DuckDB 直接查询 Web API：HTTP 扩展 + JSON 函数实战

-- 启动 DuckDB
$ duckdb

-- 安装并加载 HTTP 扩展
INSTALL http;
LOAD http;

-- 查看可用函数
SHOW ALL FUNCTIONS LIKE '%http%';

-- 加载 HTTP 扩展
INSTALL http;
LOAD http;

-- 直接查询 GitHub 用户信息
SELECT 
    json_extract_scalar(value, '$.login') AS username,
    json_extract_scalar(value, '$.avatar_url') AS avatar,
    json_extract_scalar(value, '$.public_repos') AS repos,
    json_extract_scalar(value, '$.followers') AS followers
FROM json_each(
    http_get(
        'https://api.github.com/repos/duckdb/duckdb',
        {'headers': {'Accept': 'application/vnd.github.v3+json'}}
    ),
    '$.contributors'
) AS t(value);

-- 使用 Open-Meteo 免费天气 API（无需 API Key）
SELECT 
    json_extract_scalar(value, '$.time') AS date,
    json_extract_scalar(value, '$.weather_code') AS weather_code,
    json_extract_scalar(value, '$.temperature_2m_max') AS temp_max,
    json_extract_scalar(value, '$.temperature_2m_min') AS temp_min,
    json_extract_scalar(value, '$.precipitation_sum') AS precipitation
FROM json_each(
    http_get('https://archive-api.open-meteo.com/v1/archive?latitude=39.9&longitude=116.3&start_date=2025-01-01&end_date=2025-01-31&daily=temperature_2m_max,temperature_2m_min,precipitation_sum&timezone=Asia/Shanghai'),
    '$.daily.time'
) AS t(time);

-- POST 请求示例：提交数据到 webhook
SELECT http_post(
    'https://hooks.slack.com/services/YOUR/WEBHOOK/URL',
    '{"text":"DuckDB 报告已生成"}',
    {'headers': {'Content-Type': 'application/json'}}
);

-- PUT 请求（通过 http_post 模拟）
SELECT http_post(
    'https://api.example.com/resource/123',
    '{"name":"updated","status":"active"}',
    {'method': 'PUT', 'headers': {'Content-Type': 'application/json'}}
);

-- 解析多层嵌套的电商 API 响应
WITH api_response AS (
    SELECT http_get(
        'https://api.example.com/orders?limit=100'
    ) AS raw_data
),
parsed_json AS (
    SELECT 
        json_extract(raw_data, '$.orders') AS orders_json
    FROM api_response
),
order_items AS (
    SELECT 
        json_extract_scalar(order_val, '$.id') AS order_id,
        json_extract_scalar(order_val, '$.customer.name') AS customer_name,
        json_extract_scalar(order_val, '$.customer.email') AS customer_email,
        json_extract_scalar(order_val, '$.status') AS status,
        json_extract_scalar(order_val, '$.total') AS total_amount
    FROM parsed_json,
    json_each(parsed_json.orders_json) AS t(order_val)
)
SELECT 
    status,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount::DOUBLE), 2) AS total_revenue,
    ROUND(AVG(total_amount::DOUBLE), 2) AS avg_order_value
FROM order_items
GROUP BY status
ORDER BY total_revenue DESC;

-- 直接从 URL 读取 Parquet 文件
SELECT * FROM read_parquet('https://example.com/data/dataset.parquet');

-- 读取多个文件（glob 模式）
SELECT COUNT(*) FROM read_parquet('https://storage.example.com/logs/*.parquet');

-- 读取远程 CSV（自动检测分隔符）
SELECT * FROM read_csv_auto('https://example.com/data/sales.csv');

-- 读取远程 JSON 文件
SELECT * FROM read_json_auto('https://example.com/data/users.json');

-- 组合：从 API 获取 Parquet 并分析
SELECT 
    region,
    SUM(revenue) AS total_revenue,
    AVG(order_count) AS avg_orders
FROM read_parquet('https://api.analytics.example.com/export?format=parquet')
WHERE date >= '2025-01-01'
GROUP BY region
ORDER BY total_revenue DESC;

-- Step 1: 从多个数据源聚合
WITH competitor_data AS (
    -- 来源1: 应用商店评论 API
    SELECT 
        json_extract_scalar(item, '$.product_name') AS product,
        json_extract_scalar(item, '$.rating') AS rating,
        json_extract_scalar(item, '$.review_date') AS review_date,
        json_extract_scalar(item, '$.source') AS source
    FROM json_each(
        http_get('https://api.review-tracker.com/v1/products?ids=101,102,103'),
        '$.reviews'
    ) AS t(item)
),
-- 来源2: 社交媒体提及
social_mentions AS (
    SELECT 
        json_extract_scalar(m, '$.mention_text') AS text,
        json_extract_scalar(m, '$.sentiment') AS sentiment,
        json_extract_scalar(m, '$.platform') AS platform,
        json_extract_scalar(m, '$.timestamp') AS mentioned_at
    FROM json_each(
        http_get('https://api.social-tracker.com/v1/mentions?q=competitor'),
        '$.results'
    ) AS t(m)
),
-- 合并分析
analysis AS (
    SELECT 
        product,
        AVG(rating::DOUBLE) AS avg_rating,
        COUNT(*) AS review_count,
        MIN(review_date) AS first_review,
        MAX(review_date) AS last_review
    FROM competitor_data
    GROUP BY product
)
-- 最终输出：按评分排序的竞品列表
SELECT 
    a.product,
    a.avg_rating,
    a.review_count,
    a.first_review,
    a.last_review,
    CASE 
        WHEN a.avg_rating >= 4.5 THEN '🟢 强势'
        WHEN a.avg_rating >= 4.0 THEN '🟡 稳定'
        ELSE '🔴 预警'
    END AS status
FROM analysis a
ORDER BY a.avg_rating DESC;

-- 缓存 API 响应到临时表
CREATE TEMP TABLE cached_github_repos AS
SELECT * FROM json_each(
    http_get('https://api.github.com/users/duckdb/repos'),
    '$[*]'
) AS t(value);

-- 后续分析直接查询缓存
SELECT 
    json_extract_scalar(value, '$.name') AS repo_name,
    json_extract_scalar(value, '$.stargazers_count') AS stars,
    json_extract_scalar(value, '$.language') AS language
FROM cached_github_repos
WHERE json_extract_scalar(value, '$.stargazers_count')::BIGINT > 1000
ORDER BY stars DESC;

-- 并行读取多个 Parquet 文件（DuckDB 自动并行化）
SELECT 
    file_name,
    COUNT(*) AS row_count,
    SUM(size_bytes) AS total_size
FROM parquet_metadata('s3://bucket/data/*.parquet')
GROUP BY file_name
ORDER BY total_size DESC;

-- 直接在读取时过滤，减少数据传输
SELECT * FROM read_parquet('https://storage.example.com/large-dataset.parquet')
WHERE date >= '2025-06-01' AND category = 'electronics';