Minimizing queries on a database

Question

I am working with a large dataset (~5GB) stored in a Google BigQuery database. My goal is to create a web app which works a little bit like an Excel spreadsheet where users start off by seeing the full dataset (or at least they think they do - the first 10,000 rows should suffice). They then would go on to apply a number of different filters to the dataset to reduce it to a manageable size (100-1000 rows) and continue by clicking on any one of those rows which opens a detailed view.

Here's the problem: Google BigQuery charges me based on data filtered, not on data returned. So the cost of querying the entire database with a really complex filter is equal to the cost of just querying a single row (which would be necessary for the detail view).

How should I go about minimising the number of queries?

So far I've come up with the following: Store all the data displayed in the web app on my application server (i.e. the 10,000 rows addressed above) and then service the detail view requests from that data.

Is there a better solution for this?

Answer 1

Seems to me like for this kind of application, rather than pretending to the user that you are showing them all the data, you could instead show them a pre-selected random sample of the data to "assist with query design"

Google charge by "data scanned", so if you can reduce the size of this sample dataset to say 10Mb you can do 100,000 queries before you hit the 1TB free limit.

Once the user is happy with their query they can then click a button to run it against the real dataset. Maybe put in some UI annoyances to discourage users using that as standard.

Answer 2

First of all, you should look into sharding, clustering, or partitioning the data. All of these techniques can reduce query costs.

Sharding splits data across multiple tables. Typically, you split by some large grouping value and put that in the table name. So “sales” becomes “sales_midwest”, “sales_northeast”, etc. Then you either make your query smart enough to pick the right shard or use wildcard tables and the _table_suffix pseudo column.

Clustering is similar indices on a traditional relational database. You specify columns that are likely to be useful in queries.

Partitioning specifies either an integer or date column. Behind the scenes, BigQuery stores each partition in a different table. If the where clause references the partition column, then BigQuery only scans the relevant partitions. That means you only get billed for those partitions.

Depending on the nature of your data, one or more of these techniques could help.

Otherwise, BigQuery lets you save the results of one query into a destination table. You just set the destination table using whichever SDK or API you are using.

You can also set the destination table to automatically expire after a set amount of time. That requires a separate API call to BigQuery before you submit the actual query job. This step would add noticeable lag to an interactive system. Alternatively, you can add a background process to clean up the result tables.

All of this adds considerable complexity. Before implementing any of it, you should estimate how many queries you actually expect. Compute the cost of running the full table scan on every one of those. I’d expect the number will either be so low that you can confidently ignore the costs or high enough that you can have a meaningful conversation comparing development cost to BigQuery cost.

Another technique is to consider the columns you use. BigQuery charges based on columns used by the query. Do you need all columns all the time? You could potentially slash the cost considerably by limiting the columns. Obviously, you still must allow access to everything. If every user needs every column all the time then this won’t work. I can imagine at least a few users might prefer working with fewer columns until they have the final results. As with any other idea, the savings may or may not be worth the effort.