Query Result and Data Mapping

For better understanding of mapping principle of the query result with the variable values let’s consider the following measurement:

time	location	signal	source	tsid	val	value_type
2019-09-22 18:20:41	Area1	-	-	Voltage	223.7	analog
2019-09-22 18:21:40	Area1	-	-	Voltage	246.23	analog
2019-09-22 18:23:55	-	true	fire_alarm	Signal	-	discrete
2019-09-22 18:23:59	-	false	fire_alarm	Signal	-	discrete
2019-09-22 18:24:02	-	true	fire_alarm	Signal	-	discrete
2019-09-22 18:24:12	Area1	-	-	Voltage	219.6	analog

In the table shown above location, source, tsid, value_type - are the tags, and signal and val - are the fields. It is necessary to read two values:

• The last value of val field for time series with tags "tsid"='Voltage' AND "value_type"='analog' AND "location"='Area1'

• The last value of signal field for time series with tags "tsid"='Signal' AND "value_type"='discrete' AND "source"='fire_alarm'

For this it is necessary to add two variables with the following addresses:

• tsid='Voltage',value_type='analog',location='Area1'[val]@DOUBLE

• tsid='Signal',value_type='discrete',source='fire_alarm'[signal]@BOOLEAN

In InfluxDB Connector we specify the following query template: SELECT {FIELD_NAMES} FROM {DATABASE_NAME}.{RETENTION_POLICY_NAME}.{MEASUREMENT_NAME} WHERE {WHERE_CONDITION} GROUP BY {TAG_KEYS} ORDER BY DESC LIMIT 1.

For block consisting of two above mentioned addresses the following values for replacements will be generated:

{FIELD_NAMES}: "signal","val"

{WHERE_CONDITION}: ("tsid"='Signal' AND "value_type"='discrete' AND "source"='fire_alarm') OR ("tsid"='Voltage' AND "value_type"='analog' AND "location"='Area1')

{TAG_KEYS}: "tsid","value_type","source","location"

and the result query to database will be as follows:

SELECT "signal","val" FROM "timeseries"."autogen"."tssamples" WHERE  ("tsid"='Signal' AND "value_type"='discrete' AND "source"='fire_alarm') OR ("tsid"='Voltage' AND "value_type"='analog' AND "location"='Area1')  GROUP BY "tsid","value_type","source","location" ORDER BY DESC LIMIT 1

Query to database will return the following result:

time	location	signal	source	tsid	val	value_type
2019-09-22 18:24:02	-	true	fire_alarm	Signal	-	discrete
2019-09-22 18:24:12	Area1	-	-	Voltage	219.6	analog

Next, the driver will find the first entry for each definite address where the set of record tags matches the tags specified in the address, and will assign value of the field specified in the address. If the record can’t be found the variable will be assigned the BadOutOfRange status. Thus for the variable with the address tsid='Voltage',value_type='analog',location='Area1'[val]@DOUBLE the value 219.6 will be assigned, and for the variable with the address tsid='Signal',value_type='discrete',source='fire_alarm'[signal]@BOOLEAN the true value will be assigned.

Note that in Query Template any custom query can be specified, for example: SELECT MAX("val") as "Maximum" FROM "mydb"."myrp"."mymeasurement" GROUP BY "value_type" LIMIT 1. The main requirement here is that the driver could map the result of the query with tags and fields specified in the address. In this case the variable address must be something like this: value_type='temperature'[Maximum]@DOUBLE