4 ms·
Awesome. I imagine this being useful for things that quickly check if something exists on HN or watch for new items etc. Though I think this needs clarificatio
by minxomat 7y ago
Awesome. I imagine this being useful for things that quickly check if something exists on HN or watch for new items etc.
Though I think this needs clarification:
> but it's based on Firebase
The entire HN dataset is also available as a public BigQuery dataset, which enables much more intricate queries. For example, the following query means "Get all Show HNs with more than 5 or more points and 5 or more comments, along with the decoded submission title and all decoded top-level comments which are neither dead nor deleted" (and page):
CREATE TEMPORARY FUNCTION
HTML_DECODE(enc STRING)
RETURNS STRING
LANGUAGE js AS """
var decodeHtmlEntity = function(str) {
return str.replace(/&#(\\d+);/g, function(match, dec) {
return String.fromCharCode(dec);
}).replace(/&#x([a-fA-F0-9]+);/g, function(match, hex) {
return String.fromCharCode(parseInt(hex, 16));
});
};
try {
return decodeHtmlEntity(enc);;
} catch (e) { return null }
return null;
""";
WITH
top_shows AS (
SELECT id, HTML_DECODE(title) AS dtitle
FROM `bigquery-public-data.hacker_news.stories`
WHERE descendants >= 5 AND score >= 5 AND title LIKE "Show HN:%"),
first_comments AS (
SELECT
parent,
HTML_DECODE(REGEXP_REPLACE(text, r"(</?[a-z]+>)|(<a href=\")|(\" rel=\"nofollow\">.+?</a>)", " ")) AS dcomment
FROM `bigquery-public-data.hacker_news.comments`
WHERE dead IS NULL AND deleted IS NULL )
SELECT
top_shows.id, top_shows.dtitle, first_comments.dcomment
FROM top_shows JOIN first_comments
ON first_comments.parent = top_shows.id
LIMIT 1000 OFFSET 0
So if you need to answer specific questions like this, which could return 500k+ rows, it's better to use BigQuery than stressing the API.