Unable to Execute INFORMATION_SCHEMA.TABLE_STORAGE Query

Google Cloud Data Analytics
1

I am unable to run a query against INFORMATION_SCHEMA.TABLE_STORAGE in BigQuery.

Details

  • All queries are executed from the BigQuery Studio query editor.
  • I (the executor) have the Owner role assigned to my IAM on the project.
  • Almost all datasets and tables are located in the US region.

What I Tried

  • SELECT * FROM region-us.INFORMATION_SCHEMA.TABLE_STORAGE → Encountered the error described below.
  • Changed the region to region-us-central1 and region-asia-northeast1 → Error message remained the same.
  • Changed TABLE_STORAGE to TABLE_STORAGE_BY_PROJECT → Error message remained the same.
  • Tried other similar schema about table storage: SELECT * FROM region-us.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE → Error message remained the same.
  • Tried other region-specific schema: SELECT * FROM region-us.INFORMATION_SCHEMA.JOBS → Executed without any issues.

Error Message

Access Denied: Table MY-PROJECT:region-us.INFORMATION_SCHEMA.TABLE_STORAGE: User does not have permission to query table MY-PROJECT:region-us.INFORMATION_SCHEMA.TABLE_STORAGE, or perhaps it does not exist.

Any help would be greatly appreciated.

2

Having this same issue!

1 Like
3

Hi @snhryt ,

Welcome to Google Cloud Community!

The error you encountered suggests that you may lack the necessary permissions to query the INFORMATION_SCHEMA.TABLE_STORAGE view. Even if you have the owner role, you will still need the following roles and permissions to query metadata views in BigQuery.

Required Roles:

  • roles/bigquery.admin
  • roles/bigquery.dataEditor
  • roles/bigquery.metadataViewer
  • roles/bigquery.dataViewer

Required IAM Permissions:

  • bigquery.tables.get
  • bigquery.tables.list

I hope the above information is helpful.

1 Like
4

Hi @caryna

Thank you for your response! Adding the roles you mentioned allowed me to execute the query successfully. (I’ve also come to realize that some permissions are not included even in the owner role)

1 Like