Metadata indexing for BigQuery tables

This document describes column metadata indexing in BigQuery andexplains how to allocate dedicated resources to improve index freshness andquery performance.

BigQuery automatically indexes metadata forBigQuery tables exceeding 1 GiB. This metadata includes filelocation, partitioning information, and column-level attributes, whichBigQuery uses to optimize and accelerate your queries.

By default, metadata indexing in BigQuery is a free backgroundoperation and requires no action on your part. However, index freshnessdepends on available free resources and doesn't haveperformance service level objectives (SLOs). If index freshness is critical foryour use case, we recommend configuring aBACKGROUND reservation,which shares resources across background optimization jobs.

View the metadata index refresh time

To see the last metadata index refresh time of a table, query theLAST_METADATA_INDEX_REFRESH_TIME column of theINFORMATION_SCHEMA.TABLE_STORAGE view.To do so, do the following:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   SELECTproject_id,project_number,table_name,last_metadata_index_refresh_timeFROM[PROJECT_ID.]region-REGION.INFORMATION_SCHEMA.TABLE_STORAGE;

   Replace the following:

   • PROJECT_ID: the ID of your Google Cloud project.If not specified, the default project is used.
   • REGION: theregionwhere the project is located—for example,region-us.

3. Clickplay_circleRun.

View column metadata index usage

To view whether the column metadata index was used after a job completes, checktheTableMetadataCacheUsage propertyof theJob resource. If theunusedReason field is empty (not populated), the columnmetadata index was used. If it is populated, the accompanyingexplanationfield provides a reason why the column metadata index wasn't used.

You can also view column metadata index usage with themetadata_cache_statistics field in theINFORMATION_SCHEMA.JOBS view.

For example, the following displays column metadata index usage for themy-jobjob:

SELECTmetadata_cache_statisticsFROM`region-US`.INFORMATION_SCHEMA.JOBSWHEREjob_id='my-job';

As another example, the following displays the number of jobs that used columnmetadata index for themy-table table:

SELECTCOUNT(*)FROM`region-US`.INFORMATION_SCHEMA.JOBS,UNNEST(metadata_cache_statistics.table_metadata_cache_usage)ASstatsWHEREstats.table_reference.table_id='my-table'ANDstats.table_reference.dataset_id='my-dataset'ANDstats.table_reference.project_id='my-project'ANDstats.unusedReasonISNULL;

Set up dedicated indexing resources

To set up resources for metadata indexing updates in your project, you firstneed to have a reservation assigned to your project. To do so, do the following:

1. Create aBACKGROUND reservation.
2. Assign your project to the reservation.

After setting up your reservation, select one of the following methods to assignslots to your metadata indexing job. By default, slots that you allocate in thismanner are shared with other jobs if the slots are idle. For more information,seeIdle slots.

bqmk\--project_id=ADMIN_PROJECT_ID\--location=LOCATION\--reservation_assignment\--reservation_id=RESERVATION_NAME\--assignee_id=PROJECT_ID\--job_type=BACKGROUND\--assignee_type=PROJECT

View indexing job information

After you set up your dedicated indexing jobs, you can view informationabout the indexing jobs with theJOBS view.The following SQL sample shows the five most recent refresh jobs inPROJECT_NAME.

SELECT*FROMregion-us.INFORMATION_SCHEMA.JOBSWHEREproject_id='PROJECT_NAME'ANDSEARCH(job_id,'`metadata_cache_refresh`')ORDERBYcreation_timeDESCLIMIT5;

ReplacePROJECT_NAME with the name of the projectcontaining your metadata indexing jobs.

Configure metadata indexing alerts

The Cloud Monitoring alerting process notifies you when yourBigQuery performance doesn't meet defined criteria. For moreinformation, seeAlerting overview. With metadataindexing, you can configure alerts for slot usage and staleness.

Slot usage alert

This alert notifies you when your background reservation exceeds a definedpercentage of its allocation. The default value is 95%. You can configure thisalert for a specific reservation or for every background reservation. When thisalert triggers, we recommend that youincrease your reservation size.

To configure this alert for every background reservation, do the following:

1. Set up aMonitoring notification channel if you haven't already.

2. Go to theIntegrations page.

   Go to Integrations

3. Find theBigQuery integration and clickView details.

4. In theAlerts tab, selectSlot Usage - Background Metadata Cache Slot Usage Too High.

5. Optional: To customize this alert further, clickShow options> Customize alert policy.

6. ForConfigure notifications, select your notification channel.

7. ClickCreate.

Staleness alert

This alert notifies you when the average column metadata index stalenessincreases too much compared to the existing average. The default threshold isif the average over 4 hours is more than double the previous average for morethan 30 minutes. When this alert triggers, we recommend that youincrease your reservation sizeor create abackground reservationif you don't have one.

To configure this alert, do the following:

1. Set up aMonitoring notification channel if you haven't already.

2. Go to theIntegrations page.

   Go to Integrations

3. Find theBigQuery integration and clickView details.

4. In theAlerts tab, selectColumn Metadata Index Staleness - Too Much Percent Increase.

5. Optional: To customize this alert further, clickShow options> Customize alert policy.

6. ForConfigure notifications, select your notification channel.

7. ClickCreate.

Limitations

Metadata query performance enhancements only apply toSELECT,INSERT, andCREATE TABLE AS SELECT statements. Data manipulation language (DML) statementswon't see improvements from metadata indexing.

What's next

• Learn how to see all jobs in your project with theJOBS view.
• Learn how toview slot capacity and utilization.