Neat. I keep wanting to build something like this for GitHub audit logs, but at ~5 tb, probably a little much
Show HN: 22 GB of Hacker News in SQLite
171–180 of 229 posts
Re: Show HN: 22 GB of Hacker News in SQLite
#172threw some heatmaps together of post volume and average score by day and time (15min intervals)
story volume (all time): https://ibb.co/pBTTRznP
average score (all time): https://ibb.co/KcvVjx8p
story volume (since 2020): https://ibb.co/cKC5d7Pp
average score (since 2020): https://ibb.co/WpN20kfh
Re: Show HN: 22 GB of Hacker News in SQLite
#173I tried "select * from items limit 10" and it is slowly iterating through the shards without returning. I got up to 60 shards before I stopped. Selecting just one shard makes that query return instantly. As mentioned elsewhere I think duckdb can work faster by only reading the part of a parquet file it needs over http. I was getting an error that the users and user_domains tables aren't available, but you just need t…
Doesn't `LIMIT` just limit the amount of rows returned, rather than the amount read & processed?
That depends on the query. SQLite tries to use LIMIT to restrict the amount of reading that it does. It is often successful at that. But some queries, by their very nature, logically require reading the whole input in order to compute the correct answer, regardless of whether or not there is a LIMIT clause.
Re: Show HN: 22 GB of Hacker News in SQLite
#174threw some heatmaps together of post volume and average score by day and time (15min intervals) story volume (all time): https://ibb.co/pBTTRznP average score (all time): https://ibb.co/KcvVjx8p story volume (since 2020): https://ibb.co/cKC5d7Pp average score (since 2020): https://ibb.co/WpN20kfh
added median too.
median score (all time): https://ibb.co/gZV5QVMG
median score (since 2020): https://ibb.co/Gfv8T7k8
Re: Show HN: 22 GB of Hacker News in SQLite
#175threw some heatmaps together of post volume and average score by day and time (15min intervals) story volume (all time): https://ibb.co/pBTTRznP average score (all time): https://ibb.co/KcvVjx8p story volume (since 2020): https://ibb.co/cKC5d7Pp average score (since 2020): https://ibb.co/WpN20kfh
can confirm, i'm usually very generous with upvotes on sundays at noon
Re: Show HN: 22 GB of Hacker News in SQLite
#176Absolutely love this!! I have been analyzing a lot of HN data lately [1] so I backtested my hypothesis on your dataset and ran some stats: https://philippdubach.com/standalone/hackerbook-stats/
Re: Show HN: 22 GB of Hacker News in SQLite
#177Similar in spirit to a recent tool I recently posted Show HN on, https://exopriors.com/scry . You can use Claude Code to SQL+vector query HackerNews and many other high quality public commons sites, exceptionally well-indexed and usually 5+ minute query timeout limits, so you can run seriously large research queries, to rapidly refine your worldview (particular because you can do easily to EXHAUSTIVE exploration).
I like your concept of indexing high quality sources for RAG. For many queries we might not need the usual search engines.
Re: Show HN: 22 GB of Hacker News in SQLite
#178Absolutely love this!! I have been analyzing a lot of HN data lately [1] so I backtested my hypothesis on your dataset and ran some stats: https://philippdubach.com/standalone/hackerbook-stats/ [1] https://news.ycombinator.com/item?id=46434575
That is so cool! I love that this passion fun project inspired and helped your paper. So cool!
Re: Show HN: 22 GB of Hacker News in SQLite
#179Is it a thing that the design is almost unusable on a mobile phone? The tech making this possible is beyond cool, but it's just presented in such a brutal way for phone users, even though fixing it would be super simple.
Re: Show HN: 22 GB of Hacker News in SQLite
#180Is there a public dump of the data anywhere that this is based upon, or have they scraped it themselves? Such as DB might be entertaining to play with, and the threadedness of comments would be useful for beginners to practise efficient recursive queries (more so than the StackExchange dumps, for instance).
Yes, you can see the download HN bash script in the repository now that simply extract the data to your local machine from BigQuery and saves it as a series of gzip JSON files
Ah, the repo was 404ing for me last time I checked (seems fine now) so I couldn't inspect that. I'll have a play later.