INFORMATION_SCHEMA.SHARED_DATASET_USAGE view

TheINFORMATION_SCHEMA.SHARED_DATASET_USAGE view contains the near real-timemetadata about consumption of your shared dataset tables. To get started withsharing your data across organizations, seeBigQuery sharing (formerly Analytics Hub).

Required roles

To get the permission that you need to query theINFORMATION_SCHEMA.SHARED_DATASET_USAGE view, ask your administrator to grant you theBigQuery Data Owner (roles/bigquery.dataOwner) IAM role on your source project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the bigquery.datasets.listSharedDatasetUsage permission, which is required to query theINFORMATION_SCHEMA.SHARED_DATASET_USAGE view.

You might also be able to get this permission withcustom roles or otherpredefined roles.

Schema

The underlying data is partitioned by thejob_start_time column and clustered byproject_id anddataset_id.

TheINFORMATION_SCHEMA.SHARED_DATASET_USAGE has the following schema:

Column nameData typeValue
project_idSTRING(Clustering column) The ID of the project that contains the shared dataset.
dataset_idSTRING(Clustering column) The ID of the shared dataset.
table_idSTRINGThe ID of the accessed table.
data_exchange_idSTRINGThe resource path of the data exchange.
listing_idSTRINGThe resource path of the listing.
job_start_timeTIMESTAMP(Partitioning column) The start time of this job.
job_end_timeTIMESTAMPThe end time of this job.
job_idSTRINGThe job ID. For example,bquxjob_1234.
job_project_numberINTEGERThe number of the project this job belongs to.
job_locationSTRINGThe location of the job.
linked_project_numberINTEGERThe project number of the subscriber's project.
linked_dataset_idSTRINGThe linked dataset ID of the subscriber's dataset.
subscriber_org_numberINTEGERThe organization number in which the job ran. This is the organization number of the subscriber. This field is empty for projects that don't have an organization.
subscriber_org_display_nameSTRINGA human-readable string that refers to the organization in which the job ran. This is the organization number of the subscriber. This field is empty for projects that don't have an organization.
job_principal_subjectSTRINGThe principal identifier (user email ID, service account, group email ID, domain) of users who execute jobs and queries against linked datasets.
num_rows_processedINTEGERThe total number of rows processed by the base tables that are referenced by the queried resource.
total_bytes_processedINTEGERThe total number of bytes processed by the base tables that are referenced by the queried resource.
shared_resource_idSTRINGThe ID of the queried resource (table, view, or routine).
shared_resource_typeSTRINGThe type of the queried resource. For example,TABLE,EXTERNAL_TABLE,VIEW,MATERIALIZED_VIEW,TABLE_VALUED_FUNCTION, orSCALAR_FUNCTION.
referenced_tablesRECORD REPEATEDContainsproject_id,dataset_id,table_id, andprocessed_bytes fields of the base table.

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Data retention

TheINFORMATION_SCHEMA.SHARED_DATASET_USAGE view contains runningjobs and the job history of the past 180 days.

Scope and syntax

Queries against this view must include aregion qualifier.If you don't specify a regional qualifier, metadata is retrieved from the USregion. The following table explains the region scope for this view:

View NameResource scopeRegion scope
[PROJECT_ID.]INFORMATION_SCHEMA.SHARED_DATASET_USAGEProject levelUS region
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.SHARED_DATASET_USAGEProject levelREGION
Replace the following:
  • Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
  • REGION: anydataset region name. For example,`region-us`.

    Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

Examples

To run the query against a project other than your default project, add theproject ID in the following format:

PROJECT_ID.region-REGION_NAME.INFORMATION_SCHEMA.SHARED_DATASET_USAGE

For example,myproject.region-us.INFORMATION_SCHEMA.SHARED_DATASET_USAGE.

Get the total number of jobs executed on all shared tables

The following example calculates total jobs run bysubscribers for a project:

SELECTCOUNT(DISTINCTjob_id)ASnum_jobsFROM`region-us`.INFORMATION_SCHEMA.SHARED_DATASET_USAGE

The result is similar to the following:

+------------+| num_jobs   |+------------+| 1000       |+------------+

To check the total jobs run by subscribers, use theWHERE clause:

  • For datasets, useWHERE dataset_id = "...".
  • For tables, useWHERE dataset_id = "..." AND table_id = "...".

Get the most used table based on the number of rows processed

The following query calculates the most used table based on the number of rowsprocessed by subscribers.

SELECTdataset_id,table_id,SUM(num_rows_processed)ASusage_rowsFROM`region-us`.INFORMATION_SCHEMA.SHARED_DATASET_USAGEGROUPBY1,2ORDERBY3DESCLIMIT1

The output is similar to the following:

+---------------+-------------+----------------+| dataset_id    | table_id      | usage_rows     |+---------------+-------------+----------------+| mydataset     | mytable     | 15             |+---------------+-------------+----------------+

Find the top organizations that consume your tables

The following query calculates the top subscribers based on the number of bytesprocessed from your tables. You can also use thenum_rows_processed column asa metric.

SELECTsubscriber_org_number,ANY_VALUE(subscriber_org_display_name)ASsubscriber_org_display_name,SUM(total_bytes_processed)ASusage_bytesFROM`region-us`.INFORMATION_SCHEMA.SHARED_DATASET_USAGEGROUPBY1

The output is similar to the following:

+--------------------------+--------------------------------+----------------+|subscriber_org_number     | subscriber_org_display_name    | usage_bytes    |+-----------------------------------------------------------+----------------+| 12345                    | myorganization                 | 15             |+--------------------------+--------------------------------+----------------+

For subscribers without an organization, you can usejob_project_numberinstead ofsubscriber_org_number.

Get usage metrics for your data exchange

If yourdata exchangeand source dataset are in different projects, followthese step to view the usage metrics for your data exchange:

  1. Find alllistings thatbelong to your data exchange.
  2. Retrieve the source dataset attached to the listing.
  3. To view the usage metrics for your data exchange, use the following query:
SELECT*FROMsource_project_1.`region-us`.INFORMATION_SCHEMA.SHARED_DATASET_USAGEWHEREdataset_id='source_dataset_id'ANDdata_exchange_id="projects/4/locations/us/dataExchanges/x1"UNIONALLSELECT*FROMsource_project_2.`region-us`.INFORMATION_SCHEMA.SHARED_DATASET_USAGEWHEREdataset_id='source_dataset_id'ANDdata_exchange_id="projects/4/locations/us/dataExchanges/x1"

Get usage metrics for shared views

The following query displays the usage metrics for all of the shared viewspresent in a project:

SELECTproject_id,dataset_id,table_id,num_rows_processed,total_bytes_processed,shared_resource_id,shared_resource_type,referenced_tablesFROM`myproject`.`region-us`.INFORMATION_SCHEMA.SHARED_DATASET_USAGEWHEREshared_resource_type='VIEW'

The output is similar to the following:

+---------------------+----------------+----------+--------------------+-----------------------+--------------------+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|     project_id      |   dataset_id   | table_id | num_rows_processed | total_bytes_processed | shared_resource_id | shared_resource_type |                                                                                                              referenced_tables                                                                                                              |+---------------------+----------------+----------+--------------------+-----------------------+--------------------+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|     myproject       | source_dataset | view1    |                  6 |                    38 | view1              | VIEW                 | [{"project_id":"myproject","dataset_id":"source_dataset","table_id":"test_table","processed_bytes":"21"},{"project_id":"bq-dataexchange-exp","dataset_id":"other_dataset","table_id":"other_table","processed_bytes":"17"}]                 |+---------------------+----------------+----------+--------------------+-----------------------+--------------------+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Get usage metrics for shared table valued functions

The following query displays the usage metrics for all of the shared tablevalued functions present in a project:

SELECTproject_id,dataset_id,table_id,num_rows_processed,total_bytes_processed,shared_resource_id,shared_resource_type,referenced_tablesFROM`myproject`.`region-us`.INFORMATION_SCHEMA.SHARED_DATASET_USAGEWHEREshared_resource_type='TABLE_VALUED_FUNCTION'

The output is similar to the following:

+---------------------+----------------+----------+--------------------+-----------------------+--------------------+-----------------------+---------------------------------------------------------------------------------------------------------------------+|     project_id      |   dataset_id   | table_id | num_rows_processed | total_bytes_processed | shared_resource_id | shared_resource_type  |                                                  referenced_tables                                                  |+---------------------+----------------+----------+--------------------+-----------------------+--------------------+-----------------------+---------------------------------------------------------------------------------------------------------------------+|     myproject       | source_dataset |          |                  3 |                    45 | provider_exp       | TABLE_VALUED_FUNCTION | [{"project_id":"myproject","dataset_id":"source_dataset","table_id":"test_table","processed_bytes":"45"}]           |+---------------------+----------------+----------+--------------------+-----------------------+--------------------+-----------------------+---------------------------------------------------------------------------------------------------------------------+

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-19 UTC.