Danbooru

BigQuery dataset (queryable dump)

Posted under General

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

seems many people can't access the db queries- now you must use FROM `danbooru-data.danbooru.posts` instead of [danbooru-data.danbooru.posts]. And with this all simple request like image size can be written and applied. example for 1 special image :
SELECT id, parent_id, has_children, tags, image_width, image_height
FROM `danbooru-data.danbooru.posts`
WHERE (image_width =1024) LIMIT 10
or switch in 'More' - 'Settings' to old SQL dialect to use examples posted above.
(now Im looking for some help in this new dialect to make queries in posts tag.names. Would be glad to see example of " WHERE tag.name = 'some_tag' " here, thanks)

kex234 said:

seems many people can't access the db queries- now you must use FROM `danbooru-data.danbooru.posts` instead of [danbooru-data.danbooru.posts]. And with this all simple request like image size can be written and applied. example for 1 special image :
SELECT id, parent_id, has_children, tags, image_width, image_height
FROM `danbooru-data.danbooru.posts`
WHERE (image_width =1024) LIMIT 10
or switch in 'More' - 'Settings' to old SQL dialect to use examples posted above.
(now Im looking for some help in this new dialect to make queries in posts tag.names. Would be glad to see example of " WHERE tag.name = 'some_tag' " here, thanks)

You have to unnest the tags. For example:

SELECT
  count(*) as total,
  p.uploader_id as uploader_id
FROM
  danbooru-data.danbooru.posts AS p,
  UNNEST(tags) AS t
WHERE
  t.name = "scenery"
GROUP BY
  uploader_id
ORDER BY
  total DESC
LIMIT
  10;

I've also updated the opening post to explain how to select the new database.

Is the BQ ever got updated?

querying from 'danbooru-data.danbooru.posts' can only got me the latest post as far as 2021-10-05, while viewing gs://danbooru_public still got updated till now.
what happened?

Soberoatmeal said:

Is the BQ ever got updated?

querying from 'danbooru-data.danbooru.posts' got me 2021-10-05 as the latest post, while viewing gs://danbooru_public got updated till now.
what happened?

You need to query danbooru1.danbooru_public.posts, see the link at the top of the original post (I've made the first line bigger).

nonamethanks said:

You need to query danbooru1.danbooru_public.posts, see the link at the top of the original post (I've made the first line bigger).

I'm sorry beforehand, I already tried to view the new link but the page just won't load for some reason.

Soberoatmeal said:

I'm sorry beforehand, I already tried to view the new link but the page just won't load for some reason.

What do you mean by "won't load"? Do you have a Google Cloud account? Are you logged in?

1 2 3