Queries

Time Series

The RTDIP SDK enables users to perform complex queries, including aggregation on datasets within the Platform. Please find below the various types of queries available for specific dataset types. These SDK Functions are also supported by the RTDIP API Docker Image.

Raw

Raw facilitates performing raw extracts of time series data, typically filtered by a Tag Name or Device Name and an event time.

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .raw(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        start_date="2023-01-01",
        end_date="2023-01-31",
        include_bad_data=True,
    )
)

print(data)

Resample

Resample enables changing the frequency of time series observations. This is achieved by providing the following parameters:

Time Interval Rate - The time interval rate
Time Interval Unit - The time interval unit (second, minute, day, hour)
Aggregation Method - Aggregations including first, last, avg, min, max

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)
data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .resample(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        start_date="2023-01-01",
        end_date="2023-01-31",
        time_interval_rate="15",
        time_interval_unit="minute",
        agg_method="first",
    )
)

print(data)

Interpolate

Interpolate - takes resampling one step further to estimate the values of unknown data points that fall between existing, known data points. In addition to the resampling parameters, interpolation also requires:

Interpolation Method - Forward Fill, Backward Fill or Linear

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .interpolate(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        start_date="2023-01-01",
        end_date="2023-01-31",
        time_interval_rate="15",
        time_interval_unit="minute",
        agg_method="first",
        interpolation_method="forward_fill",
    )
)

print(data)

Interpolate At Time

Interpolation at Time - works out the linear interpolation at a specific time based on the points before and after. This is achieved by providing the following parameter:

Timestamps - A list of timestamp or timestamps

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .interpolation_at_time(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        timestamp_filter=["2023-01-01T09:30:00", "2023-01-02T12:00:00"],
    )
)

print(data)

Time Weighted Average

Time Weighted Averages provide an unbiased average when working with irregularly sampled data. The RTDIP SDK requires the following parameters to perform time weighted average queries:

Time Interval Rate - The time interval rate
Time Interval Unit - The time interval unit (second, minute, day, hour)
Window Length - Adds a longer window time for the start or end of specified date to cater for edge cases
Step - Data points with step "enabled" or "disabled". The options for step are "true", "false" or "metadata" as string types. For "metadata", the query requires that the TagName has a step column configured correctly in the meta data table

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .time_weighted_average(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        start_date="2023-01-01",
        end_date="2023-01-31",
        time_interval_rate="15",
        time_interval_unit="minute",
        step="true",
    )
)

print(data)

Circular Averages

Circular Averages computes the circular average for samples in a range. The RTDIP SDK requires the following parameters to perform circular average queries:

Time Interval Rate - The time interval rate
Time Interval Unit - The time interval unit (second, minute, day, hour)
Lower Bound - The lower boundary for the sample range
Upper Bound - The upper boundary for the sample range

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .circular_average(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        start_date="2023-01-01",
        end_date="2023-01-31",
        time_interval_rate="15",
        time_interval_unit="minute",
        lower_bound="0",
        upper_bound="360",
    )
)

print(data)

Circular Standard Deviation

Circular Standard Deviations computes the circular standard deviations for samples assumed to be in the range. The RTDIP SDK requires the following parameters to perform circular average queries:

Time Interval Rate - The time interval rate
Time Interval Unit - The time interval unit (second, minute, day, hour)
Lower Bound - The lower boundary for the sample range
Upper Bound - The upper boundary for the sample range

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .circular_standard_deviation(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        start_date="2023-01-01",
        end_date="2023-01-31",
        time_interval_rate="15",
        time_interval_unit="minute",
        lower_bound="0",
        upper_bound="360",
    )
)

print(data)

Latest

Latest queries provides the latest event values. The RTDIP SDK requires the following parameters to retrieve the latest event values: - TagNames - A list of tag names

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .latest(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
    )
)

print(data)

Plot

Plot enables changing the frequency of time series observations and performing Average, Min, Max, First, Last and StdDev aggregations. This is achieved by providing the following parameters:

Time Interval Rate - The time interval rate
Time Interval Unit - The time interval unit (second, minute, day, hour)

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .plot(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        start_date="2023-01-01",
        end_date="2023-01-31",
        time_interval_rate="15",
        time_interval_unit="minute",
    )
)

print(data)

Summary

Summary computes a summary of statistics (Avg, Min, Max, Count, StDev, Sum, Variance).

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .summary(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
        start_date="2023-01-01",
        end_date="2023-01-31",
    )
)

print(data)

Metadata

Metadata queries provide contextual information for time series measurements and include information such as names, descriptions and units of measure.

from rtdip_sdk.authentication.azure import DefaultAuth
from rtdip_sdk.connectors import DatabricksSQLConnection
from rtdip_sdk.queries import TimeSeriesQueryBuilder

auth = DefaultAuth().authenticate()
token = auth.get_token("2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default").token
connection = DatabricksSQLConnection("{server_hostname}", "{http_path}", token)

data = (
    TimeSeriesQueryBuilder()
    .connect(connection)
    .source("{tablename_or_path}")
    .metadata(
        tagname_filter=["{tag_name_1}", "{tag_name_2}"],
    )
)

print(data)

← Previous Next →