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

Hacker News BigQuery Dataset

console.cloud.google.com

Post reply on HN
Print view View original

1–10 of 38 posts

Re: Hacker News BigQuery Dataset

#2

Post by minimaxir »

Looks like it stopped updating as of February 2nd, but otherwise it's pretty reliable, and as noted in the description, it's free. (you probably won't hit the 1TB limit working with this dataset). Here's a few queries I've done recently to answer ad-hoc questions to get an exact answer:

Top posts about bootstrapping (https://news.ycombinator.com/item?id=19258249):

    #standardSQL
    SELECT *
    FROM `bigquery-public-data.hacker_news.full`
    WHERE REGEXP_CONTAINS(title, '[Bb]ootstrap')
    ORDER BY score DESC
    LIMIT 100
Count of YC startup posts over time by month (https://news.ycombinator.com/item?id=19185946):

    #standardSQL
    SELECT TIMESTAMP_TRUNC(timestamp, MONTH) as month_posted,
    COUNT(*) as num_posts_gte_5
    FROM `bigquery-public-data.hacker_news.full`
    WHERE REGEXP_CONTAINS(title, 'YC [S|W][0-9]{2}')
    AND score >= 5
    AND timestamp >= '2015-01-01'
    GROUP BY 1
    ORDER BY 1

Re: Hacker News BigQuery Dataset

#3

Post by avian »

minimaxir wrote: post #2

Looks like it stopped updating as of February 2nd, but otherwise it's pretty reliable, and as noted in the description, it's free. (you probably won't hit the 1TB limit working with this dataset). Here's a few queries I've done recently to answer ad-hoc questions to get an exact answer: Top posts about bootstrapping ( https://news.ycombinator.com/item?id=19258249 ): #standardSQL SELECT * FROM `bigquery-public-data.ha…

One thing that I was missing last time I checked was comment ranking data. Neither score nor rank was there for comments posted in recent years. I understand that upvote counts are not available in the API, but ranking should be (as in, the order the comments appear on the page).

Re: Hacker News BigQuery Dataset

#4

Post by cannabisfarmer »

BigQuery keeps adding useless data.

What we truly need is common crawl data then we can check specific site on our own.

Or wait, BigQuery simply can't handle common crawl size dataset in their public service!

Otherwise there is no reason to not add it, maybe it puts their search engine/ad business in geoparady.

Is there any other Google public dataset BigQuery like platform? Where their direct search engine/ad platform interests don't get in way of Common Crawl like data searching/indexing?

Re: Hacker News BigQuery Dataset

#5

Post by minimaxir »

avian wrote: post #3
minimaxir wrote: post #2

Looks like it stopped updating as of February 2nd, but otherwise it's pretty reliable, and as noted in the description, it's free. (you probably won't hit the 1TB limit working with this dataset). Here's a few queries I've done recently to answer ad-hoc questions to get an exact answer: Top posts about bootstrapping ( https://news.ycombinator.com/item?id=19258249 ): #standardSQL SELECT * FROM `bigquery-public-data.ha…

One thing that I was missing last time I checked was comment ranking data. Neither score nor rank was there for comments posted in recent years. I understand that upvote counts are not available in the API, but ranking should be (as in, the order the comments appear on the page).

There is now a `ranking` field in the full HN dataset, although I haven't played with it and don't know how robust it is.

Re: Hacker News BigQuery Dataset

#6

Post by avian »

minimaxir wrote: post #5
avian wrote: post #3

Earlier quoted context omitted.

One thing that I was missing last time I checked was comment ranking data. Neither score nor rank was there for comments posted in recent years. I understand that upvote counts are not available in the API, but ranking should be (as in, the order the comments appear on the page).

There is now a `ranking` field in the full HN dataset, although I haven't played with it and don't know how robust it is.

There’s a field, yes, but it’s empty.

Re: Hacker News BigQuery Dataset

#7

Post by topicseed »

cannabisfarmer wrote: post #4

BigQuery keeps adding useless data. What we truly need is common crawl data then we can check specific site on our own. Or wait, BigQuery simply can't handle common crawl size dataset in their public service! Otherwise there is no reason to not add it, maybe it puts their search engine/ad business in geoparady. Is there any other Google public dataset BigQuery like platform? Where their direct search engine/ad platfo…

Feel free to load it yourself if you need to query the Common Crawl. And pay for it.

Re: Hacker News BigQuery Dataset

#8

Post by tobr »

A dataset like this is going to have a bunch of personal information in it. When it’s distributed like this, how does that jive with regulations like GDPR? If a HN user would like to delete all their comments, how would that request be forwarded to every user of this dataset?

Re: Hacker News BigQuery Dataset

#9

Post by cobookman »

Top Commentors of all time. tptacek is at 1st place with 33839 comments.

Hacker news is 12 years old. That's an average of 7 comments per day since inception. Wow

    #standardSQL
    SELECT
      author,
      count(DISTINCT id) as `num_comments`
    FROM `bigquery-public-data.hacker_news.comments`
    WHERE id IS NOT NULL
    GROUP BY author
    ORDER BY num_comments DESC
    LIMIT 100;

Re: Hacker News BigQuery Dataset

#10

Post by damajor »

tobr wrote: post #8

A dataset like this is going to have a bunch of personal information in it. When it’s distributed like this, how does that jive with regulations like GDPR? If a HN user would like to delete all their comments, how would that request be forwarded to every user of this dataset?

I support this question. Any comments ?

NB: That's easy to downvote without commenting...

Post reply on HN