Datasets exist in TABLE_STORAGE view but unable to query

Google Cloud Data Analytics
1

Hi,

I’ve recently looked into using the INFORMATION_SCHEMA.TABLE_SCHEMA metadata to analyse storage costs ahead of the incoming price rises for BigQuery.

Running the following query:

select * from region-europe-west2.INFORMATION_SCHEMA.TABLE_STORAGE
Returns a lot of rows with dataset names (table_schema) that are not visible in the UI or via a bq show command and do not have the deleted field set to true against them.
These datasets all appear to have active storage against them, but were likely deleted a long time ago.

I am a project owner and just to be sure I have the BigQuery Admin role set against my username.

Why would the TABLE_STORAGE view return these tables? How do I access (and delete) them?

Thanks

1 Like
2

Hi @wcollinscw ,

Welcome back to Google Cloud Community.

Here are some possible reasons for what you are experiencing.

  • The tables may not have been entirely removed by BigQuery. BigQuery occasionally delays the actual deletion of tables. The tables may still be kept for a few times even after being erased using the UI or using the bq show command.

  • To access and delete these tables, you can use the bq rm command in the Cloud SDK. This command can be used to delete tables that are not visible in the UI or via a bq show command. You will need to provide the full table path, including the project ID, dataset name (table_schema), and table name.
    When a table is deleted, there could be some time before the deleted field is set to true. BigQuery sets this column to signal that a table has been deleted and can now be removed without risk. There might be a lag in setting this column, which would result in the deleted tables still showing up in the metadata.

    BigQuery’s INFORMATION_SCHEMA.TABLE_STORAGE view offers information on how much storage your project’s tables are using. It’s possible that the view is returning metadata about removed tables that are still being used for storage.

    The bq rm command in the Cloud SDK can be used to access and remove these tables; it can also be used to delete tables that are not available in the UI or via a bq show command. The project ID, dataset name (table_schema), and table name must all be included in the whole table path.

    Here are some documentation that might help you.
    https://cloud.google.com/bigquery/docs/information-schema-table-storage?_ga=2.180361703.-1392753435.1676655686

    https://cloud.google.com/bigquery/docs/information-schema-intro?_ga=2.180361703.-1392753435.1676655686

    https://cloud.google.com/bigquery/docs/tables?_ga=2.180361703.-1392753435.1676655686

3

Hi @Aris_O

Thanks for your reply.

I’ve just tried to run some bq rm commands on tables which:

  • exist in the TABLE_STORAGE view
  • cannot be seen in bq show (BigQuery error in show operation: Not Found: Dataset [datasetname])

… and I get a similar not found error when trying the remove commands.

As mentioned before, I have the BigQuery Admin role on the project.

These tables and datasets were deleted months ago.

Anything else you can recommend trying?

Thanks

1 Like
4

Same issue here cannot remove dataset/tables not visible in UI/bq command but visible in TABLE_STORAGE

5

Hi @Aris_O

Do we have Information_schema.table_storage table at Dataset level? Like below

SELECT *
FROM project-level.Dataset-level.INFORMATION_SCHEMA.TABLE_STORAGE;