And now I can analyze it with DuckDB. Behold the fraction of total comments and stories referencing key topics over time!
As part of building hn.unlurker.com, I wrote a HN API client. There are already a bunch of other clients, but I wanted to try the latest Go features and linters on a new project. I’m glad I did; it was a lot of fun.
The client can retrieve active items, lists of items, etc. (comments and stories are called “items” in the HN API). Although I only really needed recent items for my project, for completeness I added “scan” which downloads all the items, in order, from zero to the latest or the other way around.
I wondered — could I just download the whole thing? Extrapolating from a few thousand items, it would only be tens of GiB of JSON. I thought I’d give it a try.
hn scan --no-cache --asc -c- -o full.json
I had to CTRL-C a stalled download a few times, but scan is resumable so after a few hours I was done. I had a 20 GiB JSON file of everything that has ever happened on Hacker News, and I can just re-run the command above to “top it off” any time I need the latest. But what could I do with it?
First I just grepped for things. How many times has the phrase “correct horse battery staple” appeared on the site? Quite a few: 231 times (the last one just today). But grepping stuff is old news, so I thought I’d try out DuckDB.
In the database world, DuckDB is unique: a super-fast embeddable analytics execution engine also available as a command-line tool. I spend most of my day wrangling a different database (there’s the plug my coworkers will be looking for) but I’ve been meaning to try DuckDB and it seemed perfect for this one-off task.
As it turns out, with their new UI for novices like me, it’s a breeze to use. AND LLMs are pretty good at helping craft the SQL queries. I just had to import the data:
CREATE TABLE items AS
SELECT *
FROM read_json_auto('/home/jason/full.json', format='nd', sample_size=-1);
Then query it. Here’s a 12-week moving average of the fraction of total items containing the terms I am interested in:
WITH weekly AS (
SELECT
DATE_TRUNC('week', TO_TIMESTAMP(time)) AS week_start,
COUNT(*) FILTER (WHERE text ILIKE '%python%')::float / NULLIF(COUNT(*),0)
AS python_prop,
COUNT(*) FILTER (WHERE text ILIKE '%javascript%')::float / NULLIF(COUNT(*),0)
AS javascript_prop,
COUNT(*) FILTER (WHERE text ILIKE '%java%')::float / NULLIF(COUNT(*),0)
AS java_prop,
COUNT(*) FILTER (WHERE text ILIKE '%ruby%')::float / NULLIF(COUNT(*),0)
AS ruby_prop,
COUNT(*) FILTER (WHERE text ILIKE '%rust%')::float / NULLIF(COUNT(*),0)
AS rust_prop
FROM items
GROUP BY week_start
)
SELECT
week_start,
AVG(python_prop) OVER (
ORDER BY week_start
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS avg_python_12w,
AVG(javascript_prop) OVER (
ORDER BY week_start
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS avg_javascript_12w,
AVG(java_prop) OVER (
ORDER BY week_start
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS avg_java_12w,
AVG(ruby_prop) OVER (
ORDER BY week_start
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS avg_ruby_12w,
AVG(rust_prop) OVER (
ORDER BY week_start
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS avg_rust_12w
FROM weekly
ORDER BY week_start;
Overall DuckDB seems really great for analyzing data sets of this size.
Now that I have a local download of all Hacker News content, I can train hundreds of LLM-based bots on it and run them as contributors, slowly and inevitably replacing all human text with the output of a chinese room oscillator perpetually echoing and recycling the past.
Or alternatively, I think for this project I am done. Someone else will have to take it to the next logical step.
Thanks for reading! Please check out hn.unlurker.com, take a look at my other articles, or find me on X.