How to query a table whose name is stored in another table in SQL?

Question

The situation is as follows:

I have tens of thousands of sensors (say, 100,000). Each sensor produces regular or irregular time values ​​in the form (timestamp, value) . The step width can be less than 1 second, so during the year there can be millions of pairs (timestamp, value) for a particular sensor, forming a time series on the sensor. The user can request values ​​for a period of time (from, to) for such a time series of the sensor.

Saving all values ​​in one table ( sensor_id, timestamp, value) fills the table with literally billions of values ​​/ rows per month. This overloads traditional open source databases (MySQL, PostgreSQL).

I am going to create a table for each sensor time interval (timestamp, value) and indicate what is in the sensor table (sensor_id, sensor_name, sensor_table_name) . Thus, there will be 100,000 tables with every million rows.

Is it possible to get values ​​directly using the sensor_table_name column in my sensor directly, or do I need to make two queries, one to get sensor_table_name and one to get values ​​from this table?

Answer 1

If you use the convention for sensor table names, you won’t need to run a query to find out which table queries a specific sensor.

For example, if your Wolverine967 sensor identifier, and your naming convention for these tables is the Sensor_ + Sensor ID, you will immediately know that you can query the Sensor_Wolverine967 table.

Answer 2

Sounds like a better solution for you than dynamically creating SQL statements with a name that matches the gauge will use table splitting . You can split the sensor name and this will work fine; but if you do not intend to keep billions of readings per year forever (without summing them up), then you might want to split by date range to make possible data cleaning much easier.

This will probably work better than generating SQL statements on the fly and should be easier to manage.

Answer 3

I am afraid that you will need to make two queries if you use a regular relational database to get the sensor_name_name and one to get the values ​​from this table.