import requests
import pandas as pd
from io import StringIO

resp = requests.get('https://example.com/data.csv')
df = pd.read_csv(StringIO(resp.text))

INSTALL httpfs; -- Install once (built-in since DuckDB 1.0)
LOAD httpfs; -- Load per session

-- Now query any URL directly
SELECT * FROM read_csv_auto('https://example.com/data.csv');

-- NYC taxi sample data on GitHub
INSTALL httpfs;
LOAD httpfs;

SELECT 
 VendorID,
 COUNT(*) AS trips,
 ROUND(AVG(total_amount), 2) AS avg_amount,
 ROUND(SUM(total_amount), 2) AS total_revenue
FROM 'https://github.com/duckdb/duckdb-data/raw/main/nyc-taxi-data.parquet'
WHERE total_amount > 0
GROUP BY VendorID
ORDER BY total_revenue DESC;

-- Read all CSVs for a given month
SELECT 
 strftime(date, '%Y-%m-%d') AS day,
 COUNT(*) AS records,
 AVG(speed) AS avg_speed
FROM read_csv_auto(
 'https://data.gov.example/traffic/2026/05/*.csv'
)
GROUP BY day
ORDER BY day;

-- Recursive pattern — all directories, all CSVs
SELECT *
FROM read_csv_auto(
 'https://data.gov.example/traffic/**/*.csv'
);

-- More precise: only May 2026 data
SELECT *
FROM read_csv_auto(
 'https://data.gov.example/traffic/2026/05/traffic_*.csv'
);

-- Query sales data on S3 with column pruning
SELECT 
 region,
 SUM(revenue) AS total_revenue,
 COUNT(DISTINCT customer_id) AS customers
FROM read_parquet(
 's3://my-bucket/sales/2026/*/*.parquet'
)
WHERE revenue > 0
GROUP BY region
ORDER BY total_revenue DESC;

#!/usr/bin/env python3
"""
DuckDB Data Acquisition + Analysis Demo
Fetches remote Parquet/CSV via HTTP, runs SQL analysis, exports HTML report
Prerequisites: pip install duckdb pandas
"""

import duckdb
import time
import os

def main():
 # Connect to in-memory database
 con = duckdb.connect()
 
 # Enable httpfs extension
 con.execute("INSTALL httpfs")
 con.execute("LOAD httpfs")
 
 # Configuration (optional)
 con.execute("SET httpfs_timeout = 30")
 con.execute("SET httpfs_retry_count = 3")
 
 # ========== Example 1: GitHub Public Data ==========
 print("=" * 60)
 print("📦 Example 1: GitHub Public Dataset Query")
 print("=" * 60)
 
 start = time.time()
 
 result = con.execute("""
 SELECT 
 VendorID,
 payment_type,
 COUNT(*) AS trips,
 ROUND(AVG(total_amount), 2) AS avg_amount,
 ROUND(SUM(total_amount), 2) AS total_revenue
 FROM 'https://github.com/duckdb/duckdb-data/raw/main/nyc-taxi-data.parquet'
 WHERE total_amount > 0
 AND total_amount < 500
 GROUP BY VendorID, payment_type
 ORDER BY total_revenue DESC
 LIMIT 15
 """).fetchdf()
 
 elapsed = time.time() - start
 print(f"✅ Query completed in {elapsed:.2f}s")
 print(f"📊 {len(result)} rows returned\n")
 print(result.to_string(index=False))
 print()
 
 # ========== Example 2: Remote CSV Analysis ==========
 print("=" * 60)
 print("📄 Example 2: Remote CSV Streaming Analysis")
 print("=" * 60)
 
 start = time.time()
 
 result2 = con.execute("""
 SELECT 
 column0 AS year,
 COUNT(*) AS records
 FROM read_csv_auto(
 'https://raw.githubusercontent.com/plotly/datasets/master/gapminder_unfiltered.csv'
 )
 WHERE column0 > 2000
 GROUP BY year
 ORDER BY year
 """).fetchdf()
 
 elapsed2 = time.time() - start
 print(f"✅ CSV remote query completed in {elapsed2:.2f}s")
 print(f"📊 {len(result2)} rows returned\n")
 print(result2.to_string(index=False))
 print()
 
 # ========== Generate HTML Report ==========
 print("=" * 60)
 print("📝 Generating HTML Report")
 print("=" * 60)
 
 html_report = f"""
 <!DOCTYPE html>
 <html>
 <head><meta charset="utf-8">
 <title>DuckDB Data Acquisition Report</title>
 <style>
 body {{ font-family: -apple-system, sans-serif; max-width: 900px; margin: 40px auto; padding: 0 20px; }}
 h1 {{ color: #0d9488; }}
 table {{ border-collapse: collapse; width: 100%; margin: 20px 0; }}
 th, td {{ border: 1px solid #ddd; padding: 8px; text-align: left; }}
 th {{ background: #0d9488; color: white; }}
 tr:nth-child(even) {{ background: #f5f5f5; }}
 .summary {{ background: #f0fdf4; padding: 15px; border-radius: 8px; margin: 20px 0; }}
 </style>
 </head>
 <body>
 <h1>🦆 DuckDB Data Acquisition Report</h1>
 <p>Generated: {time.strftime('%Y-%m-%d %H:%M:%S')}</p>
 
 <div class="summary">
 <h2>Example 1: NYC Taxi Data Analysis</h2>
 <p>Query time: {elapsed:.2f}s | Rows: {len(result)}</p>
 </div>
 
 {result.to_html(index=False)}
 
 <div class="summary">
 <h2>Example 2: Remote CSV Analysis</h2>
 <p>Query time: {elapsed2:.2f}s | Rows: {len(result2)}</p>
 </div>
 
 {result2.to_html(index=False)}
 
 <hr>
 <p><em>Powered by DuckDB httpfs — query remote data without downloading</em></p>
 </body>
 </html>
 """
 
 output_path = "duckdb_remote_report.html"
 with open(output_path, "w", encoding="utf-8") as f:
 f.write(html_report)
 
 print(f"✅ Report saved: {os.path.abspath(output_path)}")
 
 con.close()
 print("\n🎉 All done!")

if __name__ == "__main__":
 main()

pip install duckdb pandas
python3 duckdb_remote_data.py

# Query remote Parquet, output as CSV
duckdb -c "INSTALL httpfs; LOAD httpfs; COPY (
 SELECT VendorID, COUNT(*) AS cnt
 FROM 'https://github.com/duckdb/duckdb-data/raw/main/nyc-taxi-data.parquet'
 GROUP BY VendorID
) TO '/tmp/results.csv' (HEADER, DELIMITER ',');"

# Or just print to terminal
duckdb -c "
INSTALL httpfs; LOAD httpfs;
SELECT VendorID, COUNT(*) AS cnt
FROM 'https://github.com/duckdb/duckdb-data/raw/main/nyc-taxi-data.parquet'
GROUP BY VendorID;
"

# crontab entry: fetch latest data at 8 AM daily
0 8 * * * duckdb -c "INSTALL httpfs; LOAD httpfs; COPY (SELECT * FROM read_parquet('https://data-bucket.s3.amazonaws.com/daily/*.parquet') WHERE date = current_date) TO '/tmp/daily_report.csv' (HEADER);"

curl -I -H "Range: bytes=0-100" https://your-data-url

-- S3 authentication
SET s3_region = 'us-east-1';
SET s3_access_key_id = 'your_key';
SET s3_secret_access_key = 'your_secret';

-- Bearer Token (for some APIs)
SET httpfs_bearer_token = 'your_token';