3 ms·
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
by JoshMandel 11y ago
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.
- fhoffa 11y agoYou are right - I'll prepare a new release with that data. My oversight, sorry! :)
- JoshMandel 11y agoNot 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.
- fhoffa 11y agoBtw, 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)