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

21–30 of 48 posts

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

#21

Post by fhoffa »

JoshMandel wrote: post #19

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

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

My oversight, sorry! :)

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

#22

Post by JoshMandel »

fhoffa wrote: post #21
JoshMandel wrote: post #19

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

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.

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

#23

Post by brudgers »

fhoffa wrote: post #17
venning wrote: post #12

Earlier quoted context omitted.

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"?

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

#25

Post by fhoffa »

brudgers wrote: post #23
fhoffa wrote: post #17

Earlier quoted context omitted.

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"?

I guess it incorporates gravity - it's whichever way Hacker News tells its clients to rank the comments by.

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

#27

Post by catshirt »

fhoffa wrote: post #18
catshirt wrote: post #11

Earlier quoted context omitted.

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

the deleted comment asked how the BigQuery data set was different than http://hn.algolia.com

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

#29

Post by rndn »

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

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

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

#30

Post by fhoffa »

JoshMandel wrote: post #22
fhoffa wrote: post #21

Earlier quoted context omitted.

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.

Btw, I really like your query.

I modified it to get the story for up to 7 levels of recursion:

  SELECT p0.id, s.id, s.title, level
  FROM (
    SELECT p0.id, p0.parent, p2.id, p3.id, p4.id, COALESCE(p7.parent, p6.parent, p5.parent, p4.parent, p3.parent, p2.parent, p1.parent, p0.parent) story_id,
           GREATEST(IF(p7.parent IS null, -1, 7), IF(p6.parent IS null, -1, 6), IF(p5.parent IS null, -1, 5), IF(p4.parent IS null, -1, 4), IF(p3.parent IS null, -1, 3),
                    IF(p2.parent IS null, -1, 2), IF(p1.parent IS null, -1, 1), 0) level
    FROM    [fh-bigquery:hackernews.comments] p0
    LEFT JOIN EACH [fh-bigquery:hackernews.comments] p1 ON p1.id=p0.parent
    LEFT JOIN EACH [fh-bigquery:hackernews.comments] p2 ON p2.id=p1.parent
    LEFT JOIN EACH [fh-bigquery:hackernews.comments] p3 ON p3.id=p2.parent
    LEFT JOIN EACH [fh-bigquery:hackernews.comments] p4 ON p4.id=p3.parent
    LEFT JOIN EACH [fh-bigquery:hackernews.comments] p5 ON p5.id=p4.parent
    LEFT JOIN EACH [fh-bigquery:hackernews.comments] p6 ON p6.id=p5.parent
    LEFT JOIN EACH [fh-bigquery:hackernews.comments] p7 ON p7.id=p6.parent
    HAVING level=0
    LIMIT 100
  ) a
  LEFT JOIN EACH [fh-bigquery:hackernews.stories] s
  ON s.id=a.story_id
(having so many left joins consumes a lot of resources, so to run it massively I would look for a different strategy)
Post reply on HN