BigQuery dataset (queryable dump)

@gwern-bot: Seems to be working now. There's timestamps up...

2020-01-07T23:01:33-05:00

Seems to be working now. There's timestamps up to 2020, and exporting & downloading the whole table yields JSONs which look ok.

@throwaway10: Clicking the bigtable link gives the error...

2019-10-02T14:26:14-04:00

Clicking the bigtable link gives the error "Unable to find table: danbooru-data:danbooru.posts"

@gwern-bot: I'm glad to hear that! I was starting to work...

2018-11-20T16:05:59-05:00

I'm glad to hear that! I was starting to work on a Danbooru2018 dump, and the BQ mirror being broken was the biggest obstacle.

@Allynay: Okay, got access again, BQ is up and running...

2018-11-15T13:04:23-05:00

Okay, got access again, BQ is up and running once more!

@Allynay: @norainu yep, I’m aware. I’ve lost access to...

2018-11-01T17:48:11-04:00

@norainu yep, I’m aware. I’ve lost access to the database the dump is based on and I’m working with Albert to restore access. Unfortunately the script did not fail at all gracefully when it discovered it couldn’t connect so BQ is empty until this is fixed.

@norainu: @Allynay, sorry to bother you again but the...

2018-10-18T19:01:30-04:00

@Allynay, sorry to bother you again but the BigQuery database is currently empty. The details tab shows Last Modified: today, so I guess the script still runs daily but fails immediately for some reason.

@hitro: Is it still active? SELECT count(*) FROM...

2018-10-09T01:07:10-04:00

Is it still active?

SELECT count(*) FROM [danbooru-data:danbooru.posts]

returns 0.

@Danaii: I'm trying to make a query to see which...

2018-09-22T10:00:56-04:00

I'm trying to make a query to see which character tags are most used for the given copyright tag (i.e., the most popular characters). How would I make this?

@gwern-bot: FWIW, a JSON dump of the BQ mirror is available...

2018-03-17T18:48:11-04:00

FWIW, a JSON dump of the BQ mirror is available in the Danbooru2017 torrent I released last month: /forum_posts/142978

@BlooAoiBlue: I'm new to BigQuery and had the same problem as...

2018-03-16T00:05:33-04:00

I'm new to BigQuery and had the same problem as kevo (red banner at the top of the page saying "Unable to find table: danbooru-data:danbooru.posts").

I resolved it by going to the Google APIs dashboard (https://console.developers.google.com/apis/dashboard) (you may need to create a dummy project to be able to access it) then clicking "Enable APIs and services" and enabling the BigQuery API.

I hope this helps anyone else who may have trouble :)

@Allynay: > kevo said: > > @Allynay is this resource...

2017-12-18T21:45:19-05:00

kevo said:

@Allynay is this resource still available? When I try to access the dump, I get a message

Thanks.

Yep, it's still there. Can you share the query you're trying to run? Maybe your query is the newer (not Legacy) SQL? If you're using the new SQL you need to use "`danbooru-data.danbooru.posts`" instead.

@kevo: @Allynay is this resource still available? When...

2017-12-13T20:59:09-05:00

@Allynay is this resource still available? When I try to access the dump, I get a message

Unable to find table: danbooru-data:danbooru.posts

Thanks.

@kittey: Good find. I think it might be related to the...

2017-09-19T08:12:21-04:00

Good find. I think it might be related to the *_(cosplay) auto-tagger. See issue #3307.

@Allynay: Okay, figured it out. For some reason there's a...

2017-09-18T22:11:48-04:00

Okay, figured it out. For some reason there's a post that had a tag that was in the posts table but not in the tags table. I assumed that would never happened so the script was written to shit itself if it did, since it needed the tags table for the tag ID and category.

If a post is found with a tag that was missing from the tags table, it will now have a tag with ID 0, its original name and category -42.

For reference, the bad posts and tags were: 2351218, 1858377 and 2540508 with anila_(granblue_fantasy)), idunn_(p&d) and mika respectively.

The table should be kept up to date now. Sorry it took so long to respond.

@Allynay: > norainu said: > > @Allynay, the posts table...

2017-09-18T14:28:25-04:00

norainu said:

@Allynay, the posts table is still updating every 24 hours, but it seems to be getting only a random subset of the posts each time. Today it has 1,419,809 rows, about half what it should, and over the last few days it's ranged from 400,000 to 1,600,000. Do you know what's going on? (Thank you for all your work, regardless.)

Thanks for letting me know, I'll have a look tonight and see what's going on. I suspect the script I'm running is crapping out early for some reason.

@norainu: @Allynay, the posts table is still updating...

2017-09-07T20:56:41-04:00

@Allynay, the posts table is still updating every 24 hours, but it seems to be getting only a random subset of the posts each time. Today it has 1,419,809 rows, about half what it should, and over the last few days it's ranged from 400,000 to 1,600,000. Do you know what's going on? (Thank you for all your work, regardless.)

@evazion: Crossposting forum #122158 here for reference: ...

2016-11-04T13:56:30-04:00

Crossposting forum #122158 here for reference:

Missing cosplay tags (*_(cosplay) -cosplay)

BigQuery: https://bigquery.cloud.google.com/savedquery/657582419813:dbddc4af3565484c9773d076744061be
Spreadsheet: https://docs.google.com/spreadsheets/d/1UXnVGTQ6DUkMh5elzWaEf7luan3MxYXLGIHVb-EzJWw/edit?usp=sharing.

SELECT
  CONCAT("post #", STRING(id)),
  CONCAT("http://danbooru.me/posts/", STRING(id))
FROM [danbooru-data:danbooru.posts]
WHERE
  id     IN (SELECT id FROM [danbooru-data:danbooru.posts] WHERE REGEXP_MATCH(tags.name, r'.*_\(cosplay\)')) AND
  id NOT IN (SELECT id FROM [danbooru-data:danbooru.posts] WHERE tags.name = 'cosplay') AND
  TRUE

@evazion: I posted this in topic #13112 but I guess I...

2016-09-30T19:36:35-04:00

I posted this in topic #13112 but I guess I should post it here too. This is a dump of nearly everything publicly available from the API:

BigQuery Dump

https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.artist_commentary_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.artist_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.note_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.post_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.wiki_pages_versions

This should cover everything except bans and mod actions (not available in the API due to a bug), pool versions (skipped it out of laziness), and posts (OP already has that covered). I haven't automated this though so everything's about a week or two out of date at this point.

@albert: For the curious a flattened post version table...

2016-09-29T15:58:06-04:00

For the curious a flattened post version table has finished syncing now and is available for query. You can contact me for access (I don't want to make it public because it has ip addresses).

@Fred1515: Just popping in to say how awesome and...

2016-09-29T15:35:39-04:00

Just popping in to say how awesome and informative this is, you guys.

Not to mention how useful it can be: revealing trends, approval biases, user habits, user activity graphs when considering promotions, etc. So many things.

At times like this I wish I knew more than the most basic knowledge required to appreciate a good spreadsheet..