Nice trove to pore through when I find the time.

I like to use Twitter to analyze HN datasets. It's mostly limited to links, because that's what I'm after mostly.

https://twitter.com/newsycombinator https://twitter.com/HackerNews .. And a few other accounts. Try to avoid Bitly wrapped links.

Use something like Greptweet to harvest the tweets and parse out any noise.

That will not get you accurate results for HN data analysis since a) those accounts only tweet important links so analysis will be biased b) you can only get 3200 tweets at a time. (This is a Twitter API limitation)

You have to look at both the good and the bad.

Hi! I'm the one that loaded this dataset into BigQuery. Feel free to ask any questions :).

The notebook with sample queries and visualizations:

https://github.com/fhoffa/notebooks/blob/master/analyzing%20...

What is the "comment_ranking" data you mention in the notebook?

Hacker News chose to hide comment scores some time ago, but I still wanted to find a way to rank comments. The good news is that the API gives you a "kids" column that ranks comments in the order they should be displayed - that's how I can find what's the top comment for each post (as shown in the linked notebook).

Is that column independent of "gravity"?

There are items in there that are deleted/[dead] on HN (and not very recent). How come?

Is this OK from a privacy perspective?

Why wouldn't it be? All the data provided by the HN API is public.

Except email, there is nothing private on HN to raise privacy issue. Public internet is written in ink.

Perhaps not in the content. But I'm not so sure there might not be actual legal issues (in jurisdiction that have privacy/data protection laws) with regards to stuff like being able to see patterns in login-times (based on post meta data), and similar. It's tenuous -- but just because things are a) on-line in the form of the main hn site, and b) accessible through the api, doesn't automatically mean that giving raw access to the data doesn't carry with it implications for whether or not the data should be considered personal/(more)sensitive.

I'm assuming this data set offers something the API doesn't (that's the point of uploading it, right? Other than just being an easy way for others to "back up" the hn data) -- and that can very well mean there are privacy/personal data implications.

I'm mostly stating this because of the principle, not because I think it's anything wrong with this concrete upload of hn data (and wrong != legal/illegal anyway).

As an example, at least in Norway, while you may have two separate databases that are linked to people, such as a database of cars passing through toll booths, and credit card charges from a large bar/restaurant chain -- it would generally be illegal to link the two.

It's sort of a moot point. It's not especially difficult to scrape HN and compile a dataset like this for yourself.

It's a little like saying it's easy enough to make your own wine and sell it to minors. It is, but that doesn't make it legal.

Very cool, thanks! Looking forward to playing around with this.

FYI, you named a column a reserved sql keyword ('by'). For future reference, and for others reading this: this is bad database design and makes it harder to use the table. You can get around this by wrapping the column name in brackets, like:

select ... where [by] = ...

I didn't name the column "by", I just gave the field the name that the API uses for it.

And to make everyone's lives easier (including mine), I copied the [by] column to an [author] column, so you can do a

select ... where author = ...

instead :)

This makes a really nice introduction to BigQuery (which is to say: BigQuery is nicely discoverable, given an easy-to-understand dataset).

Is there a good way to find the story to which a comment belongs? This dataset raises the issue of recursive query (e.g. "with recursive" in SQLite or PostgreSQL, or "connect by" in Oracle). The only approach I see in BigQuery is specifying a fixed level with something scary like:

  SELECT p0.text, s.id, s.title
  FROM
    [fh-bigquery:hackernews.comments] p0
  JOIN EACH [fh-bigquery:hackernews.comments] p1 ON p1.id=p0.parent
  JOIN EACH [fh-bigquery:hackernews.comments] p2 ON p2.id=p1.parent
  JOIN EACH [fh-bigquery:hackernews.comments] p3 ON p3.id=p2.parent
  JOIN EACH [fh-bigquery:hackernews.comments] p4 ON p4.id=p3.parent
  JOIN EACH [fh-bigquery:hackernews.stories] s ON s.id=p4.parent
  WHERE
    REGEXP_MATCH(p0.text, '(?i)bigquery')
  ORDER BY
    p0.time DESC

For this particular data set: linking each comment to its story might be a good denormalization.

You are right - I'll prepare a new release with that data.

My oversight, sorry! :)

Not an oversight — just a different use case for the data! And I wasn't sure if BigQuery had a generic approach here, but it looks like not.

All of the github event data is also on bigquery as well. https://www.githubarchive.org/#bigquery

Now for someone to analyze github <-> HN correlations.