Strategies for Cost Reduction¶

In most scenarios, the primary cost from using BigQuery is data usage billed from running queries against a dataset. Optimizing your queries can help lower costs and improve the performance of dashboards and reports -- Google has several well-documented suggestions (1, 2), and we provide some Altmetric-specific examples below.

Minimize outputs¶

Keep a close eye on which data from your queries is actually being put to use in your visualizations, and trim any extraneous information being returned. For example, imagine we're evaluating a journal that has published, say, 3101 papers since 2010. Is that a lot? We can provide some context by counting how many papers have been published at all journals:

1
2
3
4

SELECT source.id, source.title, COUNT(id) as totcount
FROM `altmetric.data.research_outputs`
WHERE  year>=2010
GROUP BY source.id, source.title

This uses 2.01 GB of data and returns results like this:

However, if we're just building a "total papers since 2010" histogram, all those titles aren't being used for anything. We can change our query to omit titles:

1
2
3
4

SELECT source.title, COUNT(id) as totcount
FROM `altmetric.data.research_outputs`
WHERE  year>=2010
GROUP BY source.title

This reduces data usage to 1.61 GB.

Combine multiple queries¶

If two separate (but similar) queries power different charts in your dashboard, you can probably cut costs in half by reducing the number of queries. As you build out more visualizations, keep an eye out for ways to append more fields onto existing data sources, rather than creating new ones. Imagine you are collecting overall measurements across all journals. It may start with a query like this, to tally all research outputs and posts:

1
2
3
4
5
6
7

SELECT year,
    COUNT(DISTINCT id) as total_pubs,
    SUM(ARRAY_LENGTH(mentions.post_ids)) as total_mentions
FROM `altmetric.data.research_outputs`
WHERE  year>=2010
GROUP BY 1
ORDER BY 1 ASC

This requires 3.5 GB of data usage and returns results in which each row lists the year, with total publications and total mentions.

Then, farther down in a dashboard, perhaps you create another query for summing news mentions:

1
2
3
4
5
6
7

SELECT year,
    COUNT(DISTINCT id) as total_pubs,
    SUM(mentions.msm) as news_mentions
FROM `altmetric.data.research_outputs`
WHERE  year>=2010
GROUP BY 1
ORDER BY 1 ASC

This adds another 979.43 MB of data usage to the dashboard, with each row of this separate data source listing the yearly total publications and total news mentions.

As written, these two queries account for nearly 4.5 GB of data usage. However, the first query can be modified to cover both use cases, like this:

1
2
3
4
5
6
7
8

SELECT year,
    COUNT(DISTINCT id) as total_pubs,
    SUM(mentions.msm) as news_mentions,
    SUM(ARRAY_LENGTH(mentions.post_ids)) as total_mentions
FROM `altmetric.data.research_outputs`
WHERE  year>=2010
GROUP BY 1
ORDER BY 1 ASC

This returns one row for each year, including total publications, total mentions, and news mentions for all research output going back to 2010 -- with total data usage of 3.69 GB, a 20% reduction.

Store results from a query in a table¶

One way you may be able to reduce costs is by pre-computing the results of expensive queries. For example, imagine we are an editor at Journal X, and we want to monitor the attention sources that most frequently mention papers published in our journal. A query like this would give you the information you want:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT attention_source_id AS source_id, COUNT(p.id) as mentions
-- grab a list of all posts,
FROM `altmetric.data.posts` p,
-- and unpack the list of IDs of research outputs cited:
    UNNEST(research_outputs_ids) as ro_id
-- then, we associate each citing paper with its journal:
JOIN `altmetric.data.research_outputs` ro ON ro_id=ro.id
INNER JOIN `altmetric.data.dim_publications` pub ON(identifiers.dimensions.publication_id=pub.id)
-- we only want information for Journal X papers after 2010:
WHERE  ro.year>=2010 AND
    pub.source.id = 'JOURNAL_X_ID'
GROUP BY source_id

This query would return a table in which each row lists a single attention source and how many times that source has mentioned publications from Journal X -- perfect for generating a Looker Studio table or pie chart. However, this requires parsing a lot of data -- about 10.3 GB per query. That's 10 GB every time the Looker Studio dashboard is refreshed, which, if it's used frequently, can add up quickly.

If you don't need up-to-the-minute accuracy, saving the results of this query in a table can both save you money and load your dashboard more quickly. Google maintains detailed documentation about how to configure a scheduled query -- in short, from the BigQuery SQL Workspace, rather than clicking the "Run" button, you should click the "Schedule" button. The critical piece here is to select a destination table for your query, otherwise it will just periodically execute a SELECT statement and not store it anywhere. When you are configuring the scheduled query, be sure to select "Overwrite table" rather than "append."

Once you have the information stored in a table (called, let's say, precomputed_mentions,) you can go back to your dashboard and modify the data source to query this new table. Now, rather than the tricky (and expensive!) query above, where we join the whole publications table to itself, we can use this instead:

1
2

SELECT source_id, mentions
FROM precomputed_mentions

That's it! And since you already did all the heavy-duty processing, this now only uses 55 MB of data -- a 99.5 percent reduction, not including the cost of running the initial query once to populate the "precomputed_mentions" table.

Store results from multiple queries in a table¶

In the previous example, we used a new table to store information about the journals citing Journal X. But what if we also want to store information about citations to Journal Y and Journal Z? Two more journals could just mean two more scheduled queries and two more precomputed tables, but this isn't a particularly scalable solution for more than that. A slightly different approach is to use a single table to store the results of multiple queries -- if all the queries have the same output fields, this could be much more efficient, particularly if you can populate the entire table with a single query.

We can modify the query above, which calculates incoming citations to a single journal, to instead calculate incoming citations to all journals:

1
2
3
4
5
6
7

SELECT attention_source_id AS source_id, source.id as journal, COUNT(p.id) as mentions
FROM `altmetric.data.posts` p,
    UNNEST(research_outputs_ids) as ro_id
JOIN `altmetric.data.research_outputs` ro ON ro_id=ro.id
INNER JOIN `altmetric.data.dim_publications` pub ON(identifiers.dimensions.publication_id=pub.id)
WHERE  ro.year>=2010
GROUP BY source_id, journal

There is only one small change to the original query: Rather than having a WHERE clause that specifies a single value for source.id, we instead add journal as one of the outputs. The result is a table containing all incoming citations to all journals, with minimal changes to the query required to fetch the results in Looker Studio:

1
2
3

SELECT source_id, mentions
FROM precomputed_mentions
WHERE journal='JOURNAL_X_id'

Add clustering¶

For tables that are larger than 1 GB, you may be able to improve performance by using clustered tables. BigQuery splits your data across multiple storage blocks; clustering allows you to define how BigQuery does the splitting. Specify a field that you're most likely to use when segmenting the data for individual queries. For example, if we were defining a table with information about 3 million publications, we'd probably use the journal field for clustering -- if we're querying that publications table, the most likely filter we'll be applying is on journal. Once this is set up (upon creation of the dataset), BigQuery can then use this field as a shortcut. If you request all papers published in Journal X, BigQuery uses block pruning to reduce overhead: Since clustering is done using the journal field, it will check whether a block has any papers from Journal X before actually searching through the data, enabling the database to skip entire storage blocks rather than taking the time (and data usage!) to look through a block that doesn't have any relevant info.