Introduction: When SQL Meets the Internet
What if I told you that DuckDB isn’t just for analyzing local files?
It can pull data directly from the internet, parse JSON, HTML, and merge results from multiple APIs into a single table. That means no Python crawlers, no requests library, no Flask service — one line of SQL completes the entire pipeline: fetch → clean → analyze → export.
In this article, I’ll show you how to build a real-time data product: automatically scrape and analyze Hacker News trending topics, then generate shareable HTML reports.
The core value of this approach is that it compresses what used to require multiple people, days of work, and several tools into a single SQL file. Whether you are a data analyst, indie developer, or product manager, you can get started quickly.
How httpfs Works Under the Hood
DuckDB has a built-in httpfs extension that, combined with read_json and html_extract functions, enables you to:
- Fetch remote data over HTTP/HTTPS directly from SQL queries
- Auto-parse JSON, CSV, and HTML formats without external libraries
- Clean and aggregate data during queries using standard SQL
- Export results as Parquet, CSV, or HTML in one command
The entire process is dependency-free and pure SQL.
Traditional Approach vs. DuckDB httpfs
| Dimension | Traditional Python Crawler | DuckDB httpfs Approach |
|---|---|---|
| Tech Stack | Python + requests + BeautifulSoup + pandas | Pure SQL |
| Dependencies | pip install requests beautifulsoup4 pandas | INSTALL httpfs |
| Code Lines | 50–200 lines | 5–20 lines |
| Learning Curve | Requires Python programming | SQL knowledge sufficient |
| Execution Speed | Limited by Python interpreter | DuckDB columnar engine optimized |
| Deployment | Needs Python environment | Anywhere DuckDB runs |
| Best For | Complex anti-scraping, dynamic pages | Structured APIs, static web pages |
| Maintenance | High (dependency updates, error handling) | Low (just change SQL) |
Why Can DuckDB Access the Network Directly?
Many developers are surprised the first time they hear that DuckDB can read network data directly. In reality, the httpfs extension uses libcurl under the hood to handle HTTP requests, then streams remote files through DuckDB’s streaming read mechanism as if they were local files. This means:
- Streaming reads: No need to download the entire file first; parse while reading
- Caching support: Configure a local cache directory to avoid repeated requests
- Authentication: Supports Bearer Token, Basic Auth, and other common methods
- Timeout control: Set connection and read timeouts to prevent hanging
Step 1: Enable the HTTP Extension and Fetch Data
-- Load the httpfs extension (download on first use)
LOAD httpfs;
INSTALL httpfs;
-- Fetch latest posts directly from the Hacker News API
-- HN API returns a JSON array
SELECT
json_extract_scalar(item, '$.id') AS post_id,
json_extract_scalar(item, '$.title') AS title,
json_extract_scalar(item, '$.url') AS url,
CAST(json_extract_scalar(item, '$.score') AS INTEGER) AS score,
CAST(json_extract_scalar(item, '$.time') AS BIGINT) AS timestamp,
CAST(json_extract_scalar(item, '$.descendants') AS INTEGER) AS comments,
json_extract_scalar(item, '$.type') AS post_type
FROM (
SELECT json_each(read_json_auto('https://hacker-news.firebaseio.com/v0/topstories.json?print=pretty')) AS item
)
LIMIT 300;
Note: HN’s API returns a list of IDs, not full post details. To get each post’s complete information, you would need recursive calls via the json function. For demonstration, let’s read a single post’s full details:
-- Get detailed information for a specific post
SELECT
json_extract_scalar(data, '$.id') AS id,
json_extract_scalar(data, '$.title') AS title,
json_extract_scalar(data, '$.url') AS url,
CAST(json_extract_scalar(data, '$.score') AS INTEGER) AS score,
CAST(json_extract_scalar(data, '$.time') AS TIMESTAMP) AS created_at,
CAST(json_extract_scalar(data, '$.descendants') AS INTEGER) AS comment_count
FROM read_json_auto('https://hacker-news.firebaseio.com/v0/item/38495123.json');
Step 2: Batch Scrape and Cache to a Local Database
In production, you would not call APIs inside every SQL query. A better approach: batch-fetch first, store in a DuckDB database, then analyze.
Here is a complete bash script that demonstrates batch-fetching HN top stories and loading them into a local DuckDB database:
#!/bin/bash
# fetch-hn.sh — Batch-fetch Hacker News top stories and load into DuckDB
# 1. Get the top stories ID list
curl -s 'https://hacker-news.firebaseio.com/v0/topstories.json' > hn_ids.json
# 2. Use Python to batch-fetch details (HN has no batch endpoint)
python3 << 'PYEOF'
import json, urllib.request, time
with open('hn_ids.json') as f:
ids = json.load(f)[:50]
results = []
for i, nid in enumerate(ids):
try:
url = 'https://hacker-news.firebaseio.com/v0/item/' + str(nid) + '.json'
with urllib.request.urlopen(url, timeout=5) as resp:
data = json.loads(resp.read())
results.append(data)
time.sleep(0.1)
except Exception:
pass
with open('hn_posts.json', 'w') as f:
json.dump(results, f)
print("Saved " + str(len(results)) + " posts")
PYEOF
# 3. Read and analyze with DuckDB
duckdb << 'SQLEOF'
ATTACH 'hn.db' AS hn;
CREATE TABLE hn.posts AS
SELECT
id,
title,
url,
score,
FROM_UNIXTIME(time) AS created_at,
descendants AS comment_count,
type,
by_ AS author
FROM read_json_auto('hn_posts.json');
-- View data overview
SELECT COUNT(*) AS total_posts,
ROUND(AVG(score), 1) AS avg_score,
ROUND(AVG(comment_count), 1) AS avg_comments,
MIN(FROM_UNIXTIME(time)) AS earliest,
MAX(FROM_UNIXTIME(time)) AS latest
FROM hn.posts;
SQLEOF
echo "Done"
Production Environment Optimization Tips
When deploying in production, consider adding these optimizations:
- Incremental fetching: Record the maximum ID last fetched, only pull new data
- Error retry: Implement exponential backoff for failed requests
- Deduplication: Use
DISTINCTor unique indexes to avoid duplicate inserts - Scheduled jobs: Use cron or systemd timer for periodic execution
- Monitoring alerts: Send notifications on fetch failures (email/Slack/WeChat)
Step 3: Deep Analysis — Find Undervalued High-Potential Posts
This is where the real business value lies. By analyzing posting patterns on HN, you can discover which types of content attract the most attention — invaluable for content creators, indie developers, and product teams.
-- Analysis 1: Posts and average scores by hour of day
SELECT
EXTRACT(HOUR FROM created_at) AS hour_of_day,
COUNT(*) AS post_count,
ROUND(AVG(score), 1) AS avg_score,
ROUND(AVG(comment_count), 1) AS avg_comments,
-- Engagement rate = comments / score
ROUND(AVG(comment_count::DOUBLE / NULLIF(score, 0)), 2) AS engagement_rate
FROM hn.posts
GROUP BY hour_of_day
ORDER BY avg_score DESC;
-- Analysis 2: Find undervalued high-potential posts
-- Rule: score < 100 but comments > 20 (heated discussion, not yet discovered)
WITH potential_hits AS (
SELECT
id,
title,
url,
score,
comment_count,
ROUND(comment_count::DOUBLE / NULLIF(score, 0), 2) AS engagement_ratio,
LENGTH(title) AS title_length
FROM hn.posts
WHERE score > 0
AND comment_count > 10
AND score < 200
)
SELECT
*,
CASE
WHEN engagement_ratio > 2.0 THEN 'Extremely High Engagement'
WHEN engagement_ratio > 1.0 THEN 'High Potential'
ELSE 'Normal'
END AS opportunity_level
FROM potential_hits
WHERE engagement_ratio > 0.5
ORDER BY engagement_ratio DESC
LIMIT 20;
-- Analysis 3: Title length vs. score relationship
-- Test the hypothesis: do shorter titles perform better?
SELECT
CASE
WHEN title_length <= 30 THEN 'Very Short (<30 chars)'
WHEN title_length <= 60 THEN 'Short (30-60 chars)'
WHEN title_length <= 100 THEN 'Medium (60-100 chars)'
ELSE 'Long (>100 chars)'
END AS title_bucket,
COUNT(*) AS posts,
ROUND(AVG(score), 1) AS avg_score,
ROUND(AVG(comment_count), 1) AS avg_comments,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) AS median_score
FROM hn.posts
GROUP BY title_bucket
ORDER BY avg_score DESC;
Interpreting the Analysis Results
Through these three analyses, you can gain the following insights:
- Best posting times: Find the time slots with the highest average scores and schedule your content accordingly
- Undervalued content: Posts with high engagement but low scores indicate good content that lacks exposure
- Title strategy: Optimize your titles based on the correlation between title length and scores
These insights have direct practical value for content creators, product managers, and marketing teams.
Step 4: Generate an HTML Report
DuckDB can render SQL results directly into styled HTML — no template engine required.
-- Generate the complete analysis report
SELECT html_report FROM (
SELECT '''<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Hacker News Data Analysis Report - ''' || (SELECT MAX(created_at)::DATE FROM hn.posts) || '''</title>
<style>
body { font-family: sans-serif; max-width: 960px; margin: 0 auto; padding: 20px; background: #fafafa; }
.card { background: white; border-radius: 8px; padding: 24px; margin: 16px 0; box-shadow: 0 1px 3px rgba(0,0,0,0.1); }
h1 { color: #ff6600; }
h2 { color: #333; border-bottom: 2px solid #ff6600; padding-bottom: 8px; }
table { width: 100%; border-collapse: collapse; margin: 12px 0; }
th { background: #ff6600; color: white; padding: 10px; text-align: left; }
td { padding: 8px 10px; border-bottom: 1px solid #eee; }
tr:hover { background: #f5f5f5; }
.stat-grid { display: grid; grid-template-columns: repeat(4, 1fr); gap: 16px; margin: 16px 0; }
.stat-box { text-align: center; padding: 16px; background: #f8f9fa; border-radius: 8px; }
.stat-value { font-size: 28px; font-weight: bold; color: #ff6600; }
.stat-label { font-size: 12px; color: #666; margin-top: 4px; }
</style>
</head>
<body>
<h1>Hacker News Data Analysis Report</h1>
<p>Data Date: ''' || (SELECT MAX(created_at)::DATE FROM hn.posts) || ''' | Source: Hacker News Firebase API</p>
<div class="stat-grid">
<div class="stat-box"><div class="stat-value">''' || (SELECT COUNT(*) FROM hn.posts) || '''</div><div class="stat-label">Total Posts</div></div>
<div class="stat-box"><div class="stat-value">''' || (SELECT ROUND(AVG(score), 0) FROM hn.posts) || '''</div><div class="stat-label">Avg Score</div></div>
<div class="stat-box"><div class="stat-value">''' || (SELECT ROUND(AVG(comment_count), 0) FROM hn.posts) || '''</div><div class="stat-label">Avg Comments</div></div>
<div class="stat-box"><div class="stat-value">''' || (SELECT MAX(score) FROM hn.posts) || '''</div><div class="stat-label">Highest Score</div></div>
</div>
<div class="card"><h2>Top 10 High-Potential Posts</h2>
<table>
<tr><th>#</th><th>Title</th><th>Score</th><th>Comments</th><th>Engagement Ratio</th><th>Level</th></tr>''' ||
(SELECT STRING_AGG(
'<tr><td>' || ROW_NUMBER() OVER () || '</td><td>' || title || '</td><td>' || score || '</td><td>' || comment_count || '</td><td>' || ROUND(engagement_ratio, 2) || '</td><td>' || opportunity_level || '</td></tr>',
'\n'
) FROM (
SELECT
title, score, comment_count,
ROUND(comment_count::DOUBLE / NULLIF(score, 0), 2) AS engagement_ratio,
CASE
WHEN comment_count::DOUBLE / NULLIF(score, 0) > 2.0 THEN 'Extremely High Engagement'
WHEN comment_count::DOUBLE / NULLIF(score, 0) > 1.0 THEN 'High Potential'
ELSE 'Normal'
END AS opportunity_level
FROM hn.posts
WHERE score > 0 AND comment_count > 10
ORDER BY engagement_ratio DESC
LIMIT 10
) sub) || '''
</table></div>
<div class="card"><h2>Hourly Posting Activity</h2>
<table>
<tr><th>Time Slot</th><th>Posts</th><th>Avg Score</th><th>Avg Comments</th><th>Engagement Rate</th></tr>''' ||
(SELECT STRING_AGG(
'<tr><td>' || hour_of_day || ':00-' || (hour_of_day+1) || ':00</td><td>' || post_count || '</td><td>' || avg_score || '</td><td>' || avg_comments || '</td><td>' || engagement_rate || '</td></tr>',
'\n'
) FROM (
SELECT
EXTRACT(HOUR FROM created_at)::INTEGER AS hour_of_day,
COUNT(*) AS post_count,
ROUND(AVG(score), 1) AS avg_score,
ROUND(AVG(comment_count), 1) AS avg_comments,
ROUND(AVG(comment_count::DOUBLE / NULLIF(score, 0)), 2) AS engagement_rate
FROM hn.posts
GROUP BY hour_of_day
ORDER BY avg_score DESC
) sub) || '''
</table></div>
<p style="color:#999;font-size:12px;margin-top:40px;">Generated by DuckDB - Real-time data pipeline with read_json_auto + httpfs</p>
</body>
</html>'''
);
Save the output to hn_report.html and open it in your browser — you will have a polished analytics report.
Monetization Ideas
This technique can spawn multiple revenue streams:
1. Industry Intelligence SaaS
Use the same pattern to scrape data from different platforms (Reddit, Twitter, Product Hunt) and deliver daily market trend reports for entrepreneurs. Subscription model: $15–$40/month.
Target customers:
- Indie developers looking for product ideas
- Startup teams monitoring competitor moves
- Investors discovering early-stage projects
2. Content Creation Assistant
Help content creators analyze trending topic patterns across platforms, identify optimal posting times and title strategies. Charge B2B content teams.
Feature highlights:
- Trending topic prediction
- Title A/B testing recommendations
- Best posting time suggestions
3. Investment Research Dashboard
Scrape funding news, product launches, user feedback, and automatically generate due diligence reports. Charge per report: $500–$5,000 for VCs and angel investors.
Data sources:
- Crunchbase API
- Product Hunt
- GitHub Trending
- Public social media feeds
4. Automated Competitive Monitoring
Schedule regular data collection and analysis against any public API or website, producing competitive landscape reports. A universal product sellable to operations teams across industries.
Implementation steps:
- Define monitoring targets (competitor websites, social media, job boards)
- Build the data collection pipeline (DuckDB httpfs + cron)
- Design analysis models (sentiment analysis, trend detection, anomaly alerts)
- Generate visualized reports (HTML/PDF/email delivery)
Why This Skill Is Valuable
Most data analysts only know how to work with local files (CSV, Parquet, Excel). But the real-world business value lies in real-time data — whether you can rapidly acquire external data and convert it into insights determines whether your product is a “weekly report” or a “live dashboard.”
DuckDB’s HTTP extension lets you accomplish what previously required Python + requests + BeautifulSoup + a database + a backend framework — all with SQL. This is not just an efficiency gain; it is a leap in product form factor: from “analyzing historical data” to “monitoring the live world.”
Learn this one trick, and you have the complete capability to build lightweight data products.
Next Steps
- Try it now: Install DuckDB’s httpfs extension and scrape an API you care about
- Build a pipeline: Schedule data collection with cron, store in a local database
- Productize: Build a small SaaS or service based on your analysis results
- Share outcomes: Automate report delivery to users and build subscription revenue
Remember: The best way to learn is by doing. Start now — connect to the internet with a single line of SQL!
Full runnable code and detailed tutorial published on duckdblab.org, including more API scraping tips and automated deployment guides.