thclark
commented
1 year ago Queries
aerosense-tools
get_sensor_data
data_query = f"""
SELECT *
FROM `{DATASET_NAME}.sensor_data_{sensor_type_reference}`
WHERE datetime BETWEEN @start AND @finish
AND installation_reference = @installation_reference
AND node_id = @node_id
ORDER BY datetime
"""get_sensor_data_at_datetime
query = f"""
SELECT *
FROM `{DATASET_NAME}.sensor_data_{sensor_type_reference}`
WHERE datetime >= @start_datetime
AND datetime < @finish_datetime
AND installation_reference = @installation_reference
AND node_id = @node_id
ORDER BY datetime
"""get_aggregated_connection_statistics
query = f"""
SELECT datetime, filtered_rssi, raw_rssi, tx_power, allocated_heap_memory
FROM `{DATASET_NAME}.connection_statistics_agg`
WHERE datetime BETWEEN @start AND @finish
AND installation_reference = @installation_reference
AND node_id = @node_id
ORDER BY datetime
"""get_microphone_metadata
query = f"""
SELECT *
FROM `{DATASET_NAME}.microphone_data`
WHERE datetime BETWEEN @start AND @finish
AND installation_reference = @installation_reference
AND node_id = @node_id
"""get_installations
query = f"""
SELECT reference, turbine_id, location
FROM `{DATASET_NAME}.installation`
ORDER BY reference
"""get_sensor_types
query = f"""
SELECT name, metadata
FROM `{DATASET_NAME}.sensor_type`
ORDER BY name
"""get_nodes
query = f"""
SELECT node_id FROM `{DATASET_NAME}.sensor_data`
WHERE installation_reference = @installation_reference
GROUP BY node_id
ORDER BY node_id
"""
cortadocodes
time-trader
Feature request
We need to reduce spiralling table costs. I thought I’d look into the best strategies for sorting this beforehand, and figured that rather than barfing out suggestions in the meeting it might be better to put my thoughts down on paper.
Background:
BigQuery is columnar, so any query for multiple column values must traverse every row in the dataset, even if you’re filtering out rows. This means that if you query for data in the last 24 hours, thousands of values from before 24 hours ago still get loaded whilst processing the query. We’d not anticipated this, and it was pointed out by Yuriy late last year so we’ve known about it for a while.
Our research back then suggested that clustering and partitioning might be good strategies for reducing this (they both have pros/cons) but we felt it wasn’t worth it at that stage, as the DB was costing on average less than a hundred bucks a month. There’s no point spending a few thousand pounds to save a few hundred.
Today’s research:
I’ve spent today planning and researching more deeply into clustering and partitioning. I think that:
I think we should:
Cluster the sensor_data table and possibly the microphone_data table while we’re at it. Cluster indexes should be in one of these orders:
To optimise costs on current queries (reviewed below):
To optimise costs for more scalable system in future
But we can’t apply clustering to the existing table(s) meaning we need a table migration
Plan of Action
conection_statistics_aggpoints to connection_statistics(meanwhile) update aerosense-tools, the data-gateway ingress and the dashboard to point to the new tablesdeploy the updated set of toolsI think if we all do this as a group, we could block out a whole day and have it just done. It’ll be inefficient on our time doing it that way, but it’ll get it over with, avoiding asynchronous communication delays and spanning across many days.