Advanced Time Series Data Processing with InDriver
Time series data refers to a sequence of data points collected or recorded at successive time intervals. These data points typically measure the same variable (such as temperature, sales figures, or stock prices) over some time, allowing for the analysis of patterns, and trends, and forecasting future values based on historical data.
Example of data points
By collecting data every minute from a smart energy meter, the time series can include multiple values for each timestamp, like it is shown below:
2024-02-21 09:55:00
{
"power1": 312.8,
"power2": 281.56,
"power3": 10.16,
"voltage1": 238.72,
"voltage2": 240.94,
"voltage3": 240.05,
"current1": 1.42,
"current2": 1.46,
"current3": 0.16,
"energy1": 1699355,
"energy2": 1734052.1,
"energy3": 542968.2,
"energy_total": 3976375.3,
"power_total": 604.45
}
2024-02-21 09:56:00
{
"power1": 322.8,
"power2": 241.5,
"power3": 12.16,
"voltage1": 238.12,
"voltage2": 239.76,
"voltage3": 224.67,
"current1": 1.50,
"current2": 1.46,
"current3": 0.20,
"energy1": 1699357,
"energy2": 1734058.3,
"energy3": 542969.3,
"energy_total": 3976395.3,
"power_total": 614.25
}
2024-02-21 09:57:00
{
"power1": 334.5,
"power2": 234.78,
"power3": 11.12,
"voltage1": 236.32,
"voltage2": 245.64,
"voltage3": 238,01,
"current1": 1.34,
"current2": 1.56,
"current3": 0.44,
"energy1": 1699359,
"energy2": 1734064.5,
"energy3": 542971.3,
"energy_total": 3976440.1,
"power_total": 623.45
}
2024-02-21 09:58:00
{
"power1": 362.8,
"power2": 241.56,
"power3": 15.16,
"voltage1": 234.34,
"voltage2": 241.56,
"voltage3": 242.03,
"current1": 1.99,
"current2": 1.87,
"current3": 0.14,
"energy1": 1699373,
"energy2": 1734066.3,
"energy3": 542975.2,
"energy_total": 3976495.8,
"power_total": 620.76
}
2024-02-21 09:59:00
{
"power1": 342.8,
"power2": 251.56,
"power3": 16.16,
"voltage1": 248.72,
"voltage2": 249.94,
"voltage3": 248.05,
"current1": 1.44,
"current2": 1.45,
"current3": 0.17,
"energy1": 1699389,
"energy2": 1734077.6,
"energy3": 542999.2,
"energy_total": 3976489.7,
"power_total": 601.51
}
power_total
Selected variables, such as eg. 'power_total', can be easily charted and used in further calculations.
Universal SQL table structure
By utilizing a JSON data column, each data point can comprise multiple, structured values, making this a versatile solution for collecting a wide variety of data.
Efficient Time Series Data Management with InDriver:
Key Features Explained
JSON
InDriver streamlines the collection of time series data from various sources.
It features Device Read/Write functions, as well as REST API and SQL Query execution functions, which return JSON objects. These objects can be easily processed (modified, trimmed, or expanded) and reused as input.
The InDriver onHook script example demonstrates a REST API request that returns JSON, used as input for the sqlExecute function.
This script, running at set intervals, fetches and logs data to a time series SQL table with ease.
let ts= InDriver.hookTs();
RestApi.sendRequest('shelly');
if (RestApi.isSucceeded()) {
const json = RestApi.getData('shelly');
const jsonObj = JSON.parse(json);
InDriver.sqlExecute("azureserver",
"insert into public.shelly (source, ts, data ) values ('Shelly','"+ts.toISOString()+"',$$"+json+"$$);");
}
24/7
Uninterrupted Stability
InDriver tasks guarantee continuous and stable data collection, without any disruptions or data loss. Connections to both sources and destinations are constantly monitored, and automatic reconnections are triggered in case of failures.
Clock
Time-Synchronized Data Collection
Data is collected at exact intervals, ensuring synchronization with the clock. This includes precise collection at full seconds, minutes, quarter-hours, hours, or days, all aligned with local time.
//call onHook script every full 10s, eg. 00:00:00, 00:00:10, 00:00:20...
InDriver.installHook(10000)
//call onHook script every full hour, eg. 00:00:00, 01:00:00, 02:00:00...
InDriver.installHook(3600000)
Real-Time Availability
With efficient, low-level thread implementations, InDriver tasks achieve near real-time execution. The histogram below displays the measured latency between the real-time clock and the expected hook timestamp.
Synchronization
Synchronized Sources
Data collection occurs simultaneously across all sources, eliminating delays or discrepancies.
The example below showcases a 'begin...commit' code block, within which all 'readDevice' functions are executed simultaneously, guaranteeing synchronized device data collection and logging.
ModbusApi.begin()
// Execute the following read function simultaneously after commitWait()
ModbusApi.readDevice( 'MoxaOne','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')
ModbusApi.readDevice( 'MoxaTwo','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')
ModbusApi.readDevice( 'MoxaThree','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')
ModbusApi.commitWait()
if (ModbusApi.isSucceeded()) {
let data = ModbusApi.getAllData()
let ts = InDriver.hookTs()
InDriver.sqlExecute("AzurePGSQL",["select tsapiinsert('public','modbus','3xIOLogicE1212', '", ts.toUTCString(), "','", data, "' );"])
InDriver.sendMessage(ts, '["device data","arch"]', data)
}
Completion
Aggregation
Built-In Aggregation Algorithm
InDriver's built-in aggregation algorithm ensures that any missing data, potentially lost due to network disruptions, server failures, or device unavailability, is seamlessly interpolated at precise, clock-synchronized timestamps. This high-availability feature guarantees that data remains continuous, time-synchronized, and neatly organized into intervals. The algorithm facilitates aggregation at common intervals—such as 1 minute, 15 minutes, 1 hour, and 1 day—generating separate tables that not only include interpolated values but also provide aggregated statistics like value, minimum, maximum, average, delta, and delta over one hour.
This entire process is efficiently executed with just a single call to the InDriver TSApi.
-
onStartup
InDriver.import("TsApi");
//set aggregation interval as 10 minutes
InDriver.installHook(600000)
// Define aggregator for 'shelly' table on 'azureserver' SQL server
// Time zone: 'Europe/Warsaw'
// Source: 'Shelly' time series data
TsApi.defineAggregator("AGG","azureserver","shelly","Europe/Warsaw",'["Shelly"]');
-
onHook
// onHook called every 10 minutes to aggregate new values
TsApi.aggregate("AGG");
JavaScript
Custom Algorithms
Leveraging your understanding of JavaScript basics, you can easily develop bespoke algorithms for data acquisition and processing. Our comprehensive library of pre-built functions simplifies your workflow, enabling you to achieve your objectives with minimal code.
Below is a concise code snippet demonstrating how to extract values from JSON data collected from a smart meter:
let ts= InDriver.hookTs();
RestApi.sendRequest('shelly');
if (RestApi.isSucceeded()) {
const json = RestApi.getData('shelly');
const jsonObj = JSON.parse(text);
//Extract energy and power values for each phase
let energy1 = Math.abs(jsonObj .data.device_status.emeters[0].total_returned);
let energy2 = Math.abs(jsonObj .data.device_status.emeters[1].total_returned);
let energy3 = Math.abs(jsonObj .data.device_status.emeters[2].total_returned);
let power1 = Math.abs(jsonObj .data.device_status.emeters[0].power);
let power2 = Math.abs(jsonObj .data.device_status.emeters[1].power);
let power3 = Math.abs(jsonObj .data.device_status.emeters[2].power);
//Build smart meter time series data point
let data = {
power1 : power1,
power2 : power2,
power3 : power3,
voltage1 : Math.abs(jsonObj .data.device_status.emeters[0].voltage),
voltage2 : Math.abs(jsonObj .data.device_status.emeters[1].voltage),
voltage3 : Math.abs(jsonObj .data.device_status.emeters[2].voltage),
current1 : Math.abs(jsonObj .data.device_status.emeters[0].current),
current2 : Math.abs(jsonObj .data.device_status.emeters[1].current),
current3 : Math.abs(jsonObj .data.device_status.emeters[2].current),
energy1 : energy1,
energy2 : energy2,
energy3 : energy3,
energy_total: energy1 + energy2 + energy3,
power_total: power1 + power2 + power3
}
let shelly = {Shelly:data};
let list = [];
list.push(shelly);
// Ensure data changes are detected. This smart meter sends updates irregularly, often repeating values in subsequent requests.
let currentJSON = JSON.stringify(list);
if (lastJSON !=currentJSON) {
// Log JSON object with extracted values to database
InDriver.sqlExecute("azureserver", "insert into public.shelly (source, ts, data ) values ('Shelly','"+ts.toISOString()+"',$$"+currentJSON+"$$);");
lastJSON = currentJSON;
InDriver.debug(currentJSON)
}
}
Real Example - Smart Meter Data Acquisition
This example illustrates the comprehensive use case of InDriver for time series data acquisition, processing, interpolation, aggregation, and logging. It demonstrates how InDriver facilitates seamless integration and manipulation of data streams, ensuring efficient handling of time series data from initial collection to final storage, all while maintaining data integrity and providing valuable insights through statistical analysis.