Cloud

BigQuery delivers optimized search/lookup query performance by efficiently pruning irrelevant files. However, in some cases, additional column information is required for search indexes to further optimize query performance. To help, we recently announced indexing with column granularity, which lets BigQuery pinpoint relevant data within columns, for faster search queries and lower costs. 

BigQuery arranges table data into one or more physical files, each holding N rows. This data is stored in a columnar format, meaning each column has its own dedicated file block. You can learn more about this in the BigQuery Storage Internals blog. The default search index is at the file level, which means it maintains mappings from a data token to all the files containing it. Thus, at query time, the search index helps reduce the search space by only scanning those relevant files. This file-level indexing approach excels when search tokens are selective, appearing in only a few files. However, scenarios arise where search tokens are selective within specific columns but common across others, causing these tokens to appear in most files, and thus diminishing the effectiveness of file-level indexes.

For example, imagine a scenario where we have a collection of technical articles stored in a simplified table named TechArticles with two columns — Title and Content. And let’s assume that the data is distributed across four files, as shown below.

Our goal is to search for articles specifically related to Google Cloud Logging. Note that:

• The tokens “google”, “cloud”, and “logging” appear in every file.

• Those three tokens  also appear in the “Title” column, but only in the first file.

• Therefore, the combination of the three tokens is common overall, but highly selective in the “Title” column.

Now, let’s say, we create a search index on both columns of the table with the following DDL statement:

CREATE SEARCH INDEX myIndex ON myDataset.TechArticles(Title, Content);

The search index stores the mapping of data tokens to the data files containing the tokens, without any column information; the index looks like the following (showing the three tokens of interest: “google”, “cloud”, and “logging”):

With the usual query SELECT * FROM TechArticles WHERE SEARCH(Title, "Google Cloud Logging"), using the index without column information, BigQuery ends up scanning all four files, adding unnecessary processing and latency to your query.

Indexing with column granularity

Indexing with column granularity, a new public preview feature in BigQuery, addresses this challenge by adding column information in the indexes. This lets BigQuery leverage the indexes to pinpoint relevant data within columns, even when the search tokens are prevalent across the table’s files.

Let’s go back to the above example. Now we can create the index with COLUMN granularity as follows:

CREATE SEARCH INDEX myIndex ON myDataset.TechArticles(Title, Content)
OPTIONS (default_index_column_granularity = 'COLUMN');

The index now stores the column information associated with each data token. The index is as follows:

Using the same query SELECT * FROM TechArticles WHERE SEARCH(Title, "Google Cloud Logging") as above but using the index with column information, BigQuery now only needs to scan file1 since the index lookup is the intersection of the following:

• Files where Token=’google’ AND Column=’Title’ (file1)

• Files where Token=’cloud’ AND Column=’Title’ (file1, file2, file3, and file4)

• Files where Token’=’logging’ AND Column=’Title’ (file1).

Performance improvement benchmark results

We benchmarked query performance on a 1TB table containing Google Cloud Logging data of an internal Google test project with the following query:

SELECT COUNT(*)
FROM `dataset.log_1T`
WHERE SEARCH((logName, trace, labels, metadata), 'appengine');

In this benchmark query, the token ‘appengine’ appears infrequently in the columns used for query filtering, but is more common in other columns. The default search index already helped reduce a large portion of the search space, resulting in half the execution time, reducing processed bytes and slot usage. By employing column granularity indexing, the improvements are even more significant.

In short, column-granularity indexing in BigQuery offers the following benefits: 

• Enhanced query performance: By precisely identifying relevant data within columns, column-granularity indexing significantly accelerates query execution, especially for queries with selective search tokens within specific columns.

• Improved cost efficiency: Index pruning results in reduced bytes processed and/or slot time, translating to improved cost efficiency.

This is particularly valuable in scenarios where search tokens are selective within specific columns but common across others, or where queries frequently filter or aggregate data based on specific columns.

Best practices and getting started

Indexing with column granularity represents a significant advancement in BigQuery’s indexing capabilities, letting you achieve greater query performance and cost efficiency.

For best results, consider the following best practices:

• Identify high-impact columns: Analyze your query patterns to identify columns that are frequently used in filters or aggregations and would benefit from column-granularity indexing.

• Monitor performance: Continuously monitor query performance and adjust your indexing strategy as needed.

• Consider indexing and storage costs: While column-granularity indexing can optimize query performance, be mindful of potential increases in indexing and storage costs.

To get started, simply enable indexing with column granularity. For more information, refer to the CREATE SEARCH INDEX DDL documentation.

At Google Cloud Next 25, we announced a major step forward in geospatial analytics: Earth Engine in BigQuery. This new capability unlocks Earth Engine raster analytics directly in BigQuery, making advanced analysis of geospatial datasets derived from satellite imagery accessible to the SQL community.

Before we get into the details of this new capability and how it can power your use cases, it’s helpful to distinguish between two types of geospatial data and where Earth Engine and BigQuery have historically excelled: 

• Raster data: This type of data represents geographic information as a grid of cells, or pixels, where each pixel stores a value that represents a specific attribute such as elevation, temperature, or land cover. Satellite imagery is a prime example of raster data. Earth Engine excels at storing and processing raster data, enabling complex image analysis and manipulation.

• Vector data: This type of data represents geographic features such as points, lines, or polygons. Vector data is ideal for representing discrete objects like buildings, roads, or administrative boundaries. BigQuery is highly efficient at storing and querying vector data, making it well-suited for large-scale geographic analysis.

Earth Engine and BigQuery are both powerful platforms in their own right. By combining their geospatial capabilities, we are bringing the best of both raster and vector analytics to one place. That’s why we created Earth Engine in BigQuery, an extension to BigQuery’s current geospatial capabilities that will broaden access to raster analytics and make it easier than ever before to answer a wide range of real-world enterprise problems. 

Earth Engine in BigQuery: Key features

You can use the two key features of Earth Engine in BigQuery to perform raster analytics in BigQuery:

• A new function in BigQuery: Run ST_RegionStats(), a new BigQuery geography function that lets you efficiently extract statistics from raster data within specified geographic boundaries.

• New Earth Engine datasets in BigQuery Sharing (formerly Analytics Hub): Access a growing collection of Earth Engine datasets in BigQuery Sharing (formerly Analytics Hub), simplifying data discovery and access. Many of these datasets are analysis-ready, immediately usable for deriving statistics for an area of interest, and providing valuable information such as elevation, emissions, or risk prediction. 

Five easy steps to raster analytics

The new ST_RegionStats() function is similar to Earth Engine’s reduceRegion function, which allows you to compute statistics for one or more regions of an image. The ST_RegionStats() function is a new addition to BigQuery’s set of geography functions invoked as part of any BigQuery SQL expression. It takes an area of interest (e.g., a county, parcel of land, or zip code) indicated by a geography and an Earth Engine-accessible raster image and computes a set of aggregate values for the pixels that intersect with the specified geography. Examples of aggregate statistics for an area of interest would be maximum flood depth or average methane emissions for a certain county. 

These are the five steps to developing meaningful insights for an area of interest:

1. Identify a BigQuery table with vector data: This could be data representing administrative boundaries (e.g., counties, states), customer locations, or any other geographic areas of interest. You can pull a dataset from BigQuery public datasets or use your own based on your needs.

2. Identify a raster dataset: You can discover Earth Engine raster datasets in BigQuery Sharing, or you can use raster data stored as a Cloud GeoTiff or Earth Engine image asset. This can be any raster dataset that contains the information you want to analyze within the vector boundaries.

3. Use ST_RegionStats() to bring raster data into BigQuery: The ST_RegionStats() geography function takes the raster data (raster_id), vector geometries (geography), and optional band (band_name) as inputs and calculates aggregate values (e.g., mean, min, max, sum, count) on the intersecting raster data and vector feature. 

4. Analyze the results: You can use the output of running ST_RegionStats() to analyze the relationship between the raster data and the vector features, generating valuable insights about an area of interest.

5. Visualize the results: Geospatial analysis is usually most impactful when visualized on a map. Tools like BigQuery Geo Viz allow you to easily create interactive maps that display your analysis results, making it easier to understand spatial patterns and communicate findings.

Toward data-driven decision making

The availability of Earth Engine in BigQuery opens up new possibilities for scaled data-driven decision-making across various geospatial and sustainability use cases, by enabling raster analytics on datasets that were previously unavailable in BigQuery. These datasets can be used with the new ST_RegionStats() geography function for a variety of use cases, such as calculating different land cover types within specific administrative boundaries or analyzing the average elevation suitability within proposed development areas. You can also find sample queries for these datasets in BigQuery Sharing’s individual dataset pages. For example, if you navigate to the GRIDMET CONUS Drought Indices dataset page, you can find a sample query for calculating mean Palmer Drought Severity Index (PDSI) for each county in California, used to monitor drought conditions across the United States.

Let’s take a deeper look at some of the use cases that this new capability unlocks:

1. Climate, physical risk, and disaster response

Raster data can provide critical insights on weather patterns and natural disaster monitoring. Many of the raster datasets available in BigQuery Sharing provide derived data on flood mapping, wildfire risk assessment, drought conditions, and more. These insights can be used for disaster risk and response, urban planning, infrastructure development, transportation management, and more. For example, you could use the Wildfire Risk to Communities dataset for predictive analytics, allowing you to assess wildfire hazard risk, exposure of communities, and vulnerability factors, so you can develop effective resilience strategies. For flood mapping, you could use the Global River Flood Hazard dataset to understand regions in the US that have the highest predicted inundation depth, or water height above ground surface.

2. Sustainable sourcing and agriculture 

Raster data also provides insights on land cover and land use over time. Several of the new Earth Engine datasets in BigQuery include derived data on terrain, elevation, and land-cover classification, which are critical inputs for supply chain management and assessing agriculture and food security. For businesses that operate in global markets, sustainable sourcing requires bringing transparency and visibility to supply chains, particularly as regulatory requirements are shifting commitments to deforestation-free commodity production from being voluntary to mandatory. With the new Forest Data Partnership maps for cocoa, palm and rubber, you can analyze where commodities are grown over time, and add in the Forest Persistence or the JRC Global Forest Cover datasets to understand if those commodities are being grown in areas that had not been deforested or degraded before 2020. With a simple SQL query, you could, for instance, determine the estimated fraction of Indonesia’s land area that had undisturbed forest in 2020.  

3. Methane emissions monitoring 

Reducing methane emissions from the oil and gas industry is crucial to slow the rate of climate change. The MethaneSAT L4 Area Sources dataset, which can be used as an Earth Engine Image asset with the  ST_RegionStats() function, provides insights into small, dispersed area emissions of methane from various sources. This type of diffuse but widespread emissions can make up the majority of methane emissions in an oil and gas basin. You can analyze the location, magnitude, and trends of these emissions to identify hotspots, inform mitigation efforts, and understand how emissions are characterized across large areas, such as basins. 

4. Custom use cases

In addition to these datasets, you can bring your own raster datasets via Cloud Storage GeoTiffs or Earth Engine image assets, to support other use cases, while still benefiting from BigQuery’s scalability and analytical tools.

Bringing it all together with an example 

Let’s take a look at a more advanced example based on modeled wildfire risk and AI-driven weather forecasting technology. The SQL below uses the Wildfire Risk to Communities dataset listed in BigQuery Sharing, which is designed to help communities understand and mitigate their exposure to wildfire. The data contains bands that index the likelihood and consequence of wildfire across the landscape. Using geometries from a public dataset of census-designated places, you can compute values from this dataset using ST_RegionStats() to compare communities’ relative risk exposures. You can also combine weather data from WeatherNext Graph forecasts to see how imminent fire weather is predicted to affect those communities. 

To start, head to the BigQuery Sharing console, click “Search listings”, filter to “Climate and environment,” select the “Wildfire Risk to Community” dataset (or search for the dataset in the search bar), and click “Subscribe” to add the Wildfire Risk dataset to your BigQuery project. Then search for “WeatherNext Graph” and subscribe to the WeatherNext Graph dataset.

With these subscriptions in place, run a query to combine these datasets across many communities with a single query. You can break this task into subqueries using the SQL WITH statement for clarity:

First, select the input tables that you subscribed to in the previous step.

Second, compute the weather forecast using WeatherNext Graph forecast data for a specific date and for the places of interest. The result is the average and maximum wind speeds within each community. 

Third, use the ST_RegionStats() function to sample the Wildfire Risk to Community raster data for each community. Since we are only concerned with computing mean values within regions, you can set the scale to 1 kilometer in the function options in order to use lower-resolution overviews and thus reduce compute time. To compute at the full resolution of the raster (in this case, 30 meters), you can leave this option out. 

The result is a table containing the mean values of wildfire risk for both bands within each community and wind speeds projected over the course of a day. In addition, you can combine the computed values for wildfire risk, wildfire consequence, and maximum wind speed into a single composite index to show relative wildfire exposure for a selected day in Colorado.

WITH
 -- Step 1: Select inputs from datasets that we've subscribed to
 wildfire_raster AS (
   SELECT
     id
   FROM
     `wildfire_risk_to_community_v0_mosaic.fire`
 ),
 places AS (
   SELECT
     place_id,
     place_name,
     place_geom AS geo,
   FROM
     `bigquery-public-data.geo_us_census_places.places_colorado`
 ),


 -- Step 2: Compute the weather forecast using WeatherNext Graph forecast data
 weather_forecast AS (
   SELECT
     ANY_VALUE(place_name) AS place_name,
     ANY_VALUE(geo) AS geo,
     AVG(SQRT(POW(t2.`10m_u_component_of_wind`, 2)
              + POW(t2.`10m_v_component_of_wind`, 2))) AS average_wind_speed,
     MAX(SQRT(POW(t2.`10m_u_component_of_wind`, 2)
              + POW(t2.`10m_v_component_of_wind`, 2))) AS maximum_wind_speed
   FROM
     `weathernext_graph_forecasts.59572747_4_0` AS t1,
     t1.forecast AS t2
   JOIN
     places
   ON
     ST_INTERSECTS(t1.geography_polygon, geo)
   WHERE
     t1.init_time = TIMESTAMP('2025-04-28 00:00:00 UTC')
     AND t2.hours < 24
   GROUP BY
     place_id
 ),


 -- Step 3: Combine with wildfire risk for each community
 wildfire_risk AS (
   SELECT
     geo,
     place_name,
     ST_REGIONSTATS(                      -- Wildfire likelihood
       geo,                               -- Place geometry
       (SELECT id FROM wildfire_raster),  -- Raster ID
       'RPS',                             -- Band name (Risk to Potential Structures)
       OPTIONS => JSON '{"scale": 1000}'  -- Computation resolution in meters
     ).mean AS wildfire_likelihood,
     ST_REGIONSTATS(                      -- Wildfire consequence
       geo,                               -- Place geometry
       (SELECT id FROM wildfire_raster),  -- Raster ID
       'CRPS',                            -- Band name (Conditional Risk to Potential Structures)
       OPTIONS => JSON '{"scale": 1000}'  -- Computation resolution in meters
     ).mean AS wildfire_consequence,
     weather_forecast.* EXCEPT (geo, place_name)
   FROM
     weather_forecast
 )


 -- Step 4: Compute a composite index of relative wildfire risk.
SELECT
  *,
  PERCENT_RANK() OVER (ORDER BY wildfire_likelihood)
    * PERCENT_RANK() OVER (ORDER BY wildfire_consequence)
    * PERCENT_RANK() OVER (ORDER BY average_wind_speed)
    AS relative_risk
FROM
  wildfire_risk

You can save this output in Google Sheets to visualize how wildfire risk and consequences are related among communities statewide.

Alternatively, you can visualize relative wildfire risk exposure in BigQuery GeoViz with the single composite index to show relative wildfire exposure for a selected day in Colorado.

What’s next for Earth Engine in BigQuery?

Earth Engine in BigQuery marks a significant advancement in geospatial analytics, and we’re excited to further expand raster analytics in BigQuery, making sustainability decision-making easier than ever before. Learn more about this new capability in the BigQuery documentation for working with raster data, and stay tuned for new Earth Engine capabilities in BigQuery in the near future!