Database Reference
In-Depth Information
SELECT
running.name AppName,
AVG(running.memory.total) MemUsage,
COUNT(running.name) Running
FROM (TABLE_DATE_RANGE(logs.device_,
DATE_ADD(CURRENT_TIMESTAMP(),
-1, 'DAY'),
CURRENT_TIMESTAMP()))
WHERE
(TIMESTAMP_TO_SEC(CURRENT_TIMESTAMP()) -
TIMESTAMP_TO_SEC(ts)) < 60 * 60 * 6
GROUP BY 1
ORDER BY 3 DESC
LIMIT 100;
This is a simple query capturing application usage over a 6-hour rolling
window. Note that this query is not cacheable because of the use of
CURRENT_TIMESTAMP
and the continuously updated source tables. The
cost of this query is going to be proportional to the number of records
in the source tables, which is expected to be large. If you plan for the
page displaying this table to be accessed frequently, for example, once per
minute, you would need to run this query approximately 1,500 times a day.
If it is reasonable for the contents of the page to be up to 1 hour behind the
current contents of the source table, fewer distinct queries need to be run.
One simple way to do this is to tweak the query so that BigQuery considers
it auto-cacheable. For example, you could generate a timestamp that is
rounded to a 1-hour boundary and include it as a constant expression in
place of
CURRENT_TIMESTAMP()
in the query. This works well at handling
queries that generate identical results. However, this does not work if the
underlying tables are changing frequently because the query needs to be
rerun at least as frequently as the underlying table is changing. For a table
that is continuously updated via streaming inserts, this implies that the
query can never be cached.
You can structure the dashboard data generation differently to cache results
in a manner tuned to the application. The general idea is to run the query
from a background process that stores the results in a known location.
The dashboard rendering code can then simply retrieve the results rather
than actually issue the query. The details of how you trigger the periodic
