Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Tell HN: Full Hacker News dataset now available on BigQuery

news.ycombinator.com

Post reply on HN
Print view View original

11–20 of 48 posts

Re: Tell HN: Full Hacker News dataset now available on BigQuery

#12

Post by venning »

fhoffa wrote: post #5

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?

Re: Tell HN: Full Hacker News dataset now available on BigQuery

#14

Post by rm999 »

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] = ...

Re: Tell HN: Full Hacker News dataset now available on BigQuery

#16

Post by fhoffa »

rm999 wrote: post #14

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 :)

Re: Tell HN: Full Hacker News dataset now available on BigQuery

#17

Post by fhoffa »

venning wrote: post #12
fhoffa wrote: post #5

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).

Re: Tell HN: Full Hacker News dataset now available on BigQuery

#18

Post by fhoffa »

catshirt wrote: post #11
[deleted] wrote: post #10

[deleted]

in what way is it similar?

Parent comment was deleted, but given the grandparent comment I'll guess you are asking how is BigQuery different to Redshift?

There is an ongoing conversation about this on reddit: https://www.reddit.com/r/bigdata/comments/3jnam1/whats_your_...

Re: Tell HN: Full Hacker News dataset now available on BigQuery

#19

Post by JoshMandel »

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.
Post reply on HN