Following the previous blog where we discussed some important features of bigquery – Clustering and Partitioning. And we left on a question that, are these the only way to improve query performance? And as you already know the answer is ‘No’.
So, What is that technique/feature? 🤔
Query performance can be optimized/improved by choosing the right/appropriate Table Schema.
What do I mean by choosing the right schema?
Let’s look into EPA PM10 hourly dataset, which is available at BigQuery Public Dataset. This dataset is originally a flatten dataset which means there’s a row for every hourly observation.
Now let’s query that table
Query-1
SELECT
pm10.county_name,
COUNT(DISTINCT pm10.site_num) AS num_instruments
FROM
`bigquery-public-data`.epa_historical_air_quality.pm10_hourly_summary as pm10
WHERE
EXTRACT(YEAR from pm10.date_local) = 2018 AND
pm10.state_name = 'Ohio'
GROUP BY pm10.county_name
![](https://techblogs.42gears.com/wp-content/uploads/2022/02/image4.png)
Query-2
SELECT
MIN(EXTRACT(YEAR from pm10.date_local)) AS year
, MAX(pm10.sample_measurement) AS PM10
FROM
`bigquery-public-data`.epa_historical_air_quality.pm10_hourly_summary as pm10
CROSS JOIN
`bigquery-public-data`.utility_us.us_cities_area as city
WHERE
pm10.state_name = 'Ohio' AND
city.name = 'Columbus, OH' AND
ST_Within( ST_GeogPoint(pm10.longitude, pm10.latitude),
city.city_geom )
GROUP BY EXTRACT(YEAR from pm10.date_local)
ORDER BY year ASC
![](https://techblogs.42gears.com/wp-content/uploads/2022/02/image1.png)
The EPA hourly data is in a table whose rows have an hourly observation. This means that there’s a lot of repeated data about stations, etc. So we can combine a single day’s observation in an array with ARRAY_AGG() i.e. we are reducing the number of rows losslessly (table size will be the same but number of rows will be less).
Let’s create new table with same data but less rows.
CREATE OR REPLACE TABLE advdata.epa AS
SELECT
state_code
, county_code
, site_num
, parameter_code
, poc
, MIN(latitude) as latitude
, MIN(longitude) as longitude
, MIN(datum) as datum
, MIN(parameter_name) as parameter_name
, date_local
, ARRAY_AGG(STRUCT(time_local, date_gmt, sample_measurement, uncertainty, qualifier, date_of_last_change) ORDER BY time_local ASC) AS observations
, STRUCT(MIN(units_of_measure) as units_of_measure
, MIN(mdl) as mdl
, MIN(method_type) as method_type
, MIN(method_code) as method_code
, MIN(method_name) as method_name) AS method
, MIN(state_name) as state_name
, MIN(county_name) as county_name
FROM `bigquery-public-data.epa_historical_air_quality.pm10_hourly_summary`
GROUP BY state_code, county_code, site_num, parameter_code, poc, date_local
Now run Query-1 and Query-2 on newly generated table
Query-1
![](https://techblogs.42gears.com/wp-content/uploads/2022/02/image2.png)
Query-2
For Query-2 we need to change our bigquery query as our schema is changed and we are fetching data from array of nested fields.
SELECT
MIN(EXTRACT(YEAR from pm10.date_local)) AS year
, MAX(pm10obs.sample_measurement) AS PM10
FROM
advdata.epa as pm10,
UNNEST(observations) as pm10obs
CROSS JOIN
`bigquery-public-data`.utility_us.us_cities_area as city
WHERE
city.name = 'Columbus, OH' AND
ST_Within( ST_GeogPoint(pm10.longitude, pm10.latitude),
city.city_geom )
GROUP BY EXTRACT(YEAR from pm10.date_local)
ORDER BY year ASC
![](https://techblogs.42gears.com/wp-content/uploads/2022/02/image3.png)
It is clearly seen from the results that after altering the table schema, scanned data is pretty much less. i.e. storing data in nested and/or array fields has made our query almost 23x and 3x times less expensive respectively!!!😲
But why is it less expensive? 🤔
As already discussed, with the help of ARRAY_AGG() and nesting, no. of rows are reduced and data is organized more efficiently. So, BigQuery will have less rows to scan.
Here, even with scanning less data, the query runtime is not affected. It is due to the computations (max, extract year, ST_Within), which are the bulk of the overhead for this query and the number of rows those are carried out on is the same, so the query speed does not change.
So choose your table schema wisely!!!😀
Clustering, Partitioning and nesting and/or arrays allows BigQuery to organize data more efficiently which leads to higher performance and cheaper costing.
References:
- https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#array_agg
- https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct_type