Data discovery query examples
- Last UpdatedOct 07, 2025
- 4 minute read
- PI System
- Adapter for RDBMS 1.1
- Adapters
A discovery against the data source of an RDBMS adapter requires you to specify the query parameter. In the discovery query, you must specify the QueryId. For more information, see Configure queries. This is the query that is executed against the data source.
The adapter parses the result set for data selection items. To allow the adapter to create more accurate data selection items, you may also provide other key=value pairs. You can add the discovered items to the data selection.
Query string
The string of the query parameter must contain string items in one of the following formats:
queryId=Tanks;indexColumn=SAMPLETIME;selectColumn=ASSET;initialTime=MM/dd/yyyy h:mm:ss a
queryId=Tanks;indexColumn=SampleTime;valueColumns=Temperature{UoM=TU},Pressure{UoM=PU}, Volume{UoM=VU};InitialTime=11/11/2020 3:46:00 AM
|
String item |
Required |
Description |
|---|---|---|
|
queryId |
Required |
The identifier of the query to reference in the Queries configuration facet. The results of this query determine the discovery results. |
|
indexColumn |
Optional |
Column containing the timestamp. The adapter uses this column to populate the IndexColumn property of newly discovered data selection items. Default: "" (empty string) |
|
selectColumn |
Optional |
Column containing the selectValue. The adapter uses this column to populate the SelectColumn property of the newly discovered selection item. The adapter also uses this column to distinguish selection items from unique rows in the result set. Default: "" (empty string) |
|
initialTime |
Optional |
The timestamp that is used to substitute the ?LST? parameter (if used) in your query during discovery. Default: One hour earlier than current time. |
|
valueColumns |
Optional |
Indicates the column used for importing data. UOM is an extended property that can be introduced in the braces following the valueColumns name. For example: valueColumns = Temperature{UOM=EngineeringUnit} where EngineeringUnit is the column containing the UOM, such as K (Kelvin), F (Fahrenheit), or C (Celsius). |
Query rules
The following rules apply for specifying the query string:
-
The query is made up of key=value pairs.
-
Pairs are separated with a semicolon (;).
-
Keys and values are separated with an equals (=).
-
The discovery query must reference a defined query from the Queries configuration facet.
-
Columns are case-insensitive.
Note: The data source might contain tens of thousands of metrics. Ensure that the query only returns data for the selection items you are interested in.
Simple Discovery Query to obtain UOM
|
SampleTime |
Temperature |
TU |
Pressure |
PU |
Volume |
VU |
|---|---|---|---|---|---|---|
|
2020-11-11 04:00:00 |
110.928 |
K |
20000 |
Pa |
227124.71 |
L |
|
2020-11-11 05:00:00 |
111.483 |
K |
20000 |
Pa |
227124.71 |
L |
|
2020-11-11 06:00:00 |
110.928 |
K |
20000 |
Pa |
227124.71 |
L |
SQL query
"SELECT TEMPERATURE, TU, PRESSURE, PU, VOLUME, VU, SAMPLETIME FROM TANK1 WHERE SAMPLETIME > ?LST? ORDER BY SAMPLETIME ASC "
Discovery query
"QueryId=Tanks;IndexColumn=SampleTime;ValueColumns=Temperature{UoM=TU},Pressure{UoM=PU}, Volume{UoM=VU};InitialTime=11/11/2020 3:46:00 AM"
The subsequent data selection configuration for this file structure would be:
[
{
"valueColumn": "Temperature",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tanks",
"selectColumn": null,
"selectValue": null,
"scheduleId": "1",
"selected": true,
"uom": "K",
"name": null,
"streamId": "Tanks.Temperature",
"dataFilterId": null,
},
{
"valueColumn": "Pressure",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tanks",
"selectColumn": null,
"selectValue": null,
"scheduleId": "1",
"uom": "Pas",
"selected": true,
"name": null,
"streamId": "Tanks.Pressure",
"dataFilterId": null
},
{
"valueColumn": "Volume",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tanks",
"selectColumn": null,
"selectValue": null,
"scheduleId": "1",
"uom": "Ltr",
"selected": true,
"name": null,
"streamId": "Tanks.Volume",
"dataFilterId": null
}
]
Discovery Query with selectColumn to obtain UOM
|
SampleTime |
Asset |
Temperature |
TU |
Pressure |
PU |
Volume |
VU |
|---|---|---|---|---|---|---|---|
|
2020-11-11 04:00:00 |
Tank01 |
110.928 |
K |
20000 |
Pa |
227124.71 |
L |
|
2020-11-11 05:00:00 |
Tank02 |
111.483 |
K |
20000 |
Pas |
227124.71 |
L |
|
2020-11-11 06:00:00 |
Tank01 |
110.928 |
K |
20000 |
Pas |
227124.71 |
Ltr |
SQL query
"SELECT ASSET, TEMPERATURE, TU, PRESSURE, PU, VOLUME, VU, SAMPLETIME FROM TANK1 WHERE ASSET=”Tank01” SAMPLETIME > ?LST? ORDER BY SAMPLETIME ASC"
Discovery query
"QueryId=Tank1; IndexColumn=SampleTime; SelectColumn=ASSET; ValueColumns=Temperature{UoM=TU},Pressure{UoM=PU}, Volume{UoM=VU} ; InitialTime=11/11/2020 3:46:00 AM"
The subsequent data selection configuration for this file structure would be:
[
{
"valueColumn": "Temperature",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tank1",
"selectColumn": "Asset",
"selectValue": "Tank01",
"scheduleId": "1",
"uom": "K",
"selected": true,
"name": null,
"streamId": "Tank1.Tank01.Temperature",
"dataFilterId": null,
},
{
"valueColumn": "Pressure",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tank1",
"selectColumn": "Asset",
"selectValue": "Tank01",
"scheduleId": "1",
"uom": "Pas",
"selected": true,
"name": null,
"streamId": "Tank1.Tank01.Pressure",
"dataFilterId": null,
},
{
"valueColumn": "Volume",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tank1",
"selectColumn": "Asset",
"selectValue": "Tank01",
"scheduleId": "1",
"uom": "Ltr",
"selected": true,
"name": null,
"streamId": "Tank1.Tank01.Volume",
"dataFilterId": null
}
]
Discovery query with custom UOM column
|
SampleTime |
Temperature |
Pressure |
Volume |
|---|---|---|---|
|
2020-11-11 04:00:00 |
110.928 |
20000 |
227124.71 |
|
2020-11-11 05:00:00 |
111.483 |
20000 |
227124.71 |
|
2020-11-11 06:00:00 |
110.928 |
20000 |
227124.71 |
SQL query
"SELECT ASSET, TEMPERATURE, TU, PRESSURE, PU, VOLUME, VU, SAMPLETIME FROM TANK1 WHERE ASSET=”Tank01” SAMPLETIME > ?LST? ORDER BY SAMPLETIME ASC"
Discovery query
"QueryId=Tank1; IndexColumn=SampleTime; SelectColumn=ASSET; ValueColumns=Temperature{UoM=TU},Pressure{UoM=PU}, Volume{UoM=VU} ; InitialTime=11/11/2020 3:46:00 AM"
The subsequent data selection configuration for this file structure would be:
[
{
"valueColumn": "Temperature",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tanks",
"selectColumn": null,
"selectValue": null,
"scheduleId": "1",
"uom": K,
"selected": true,
"name": null,
"streamId": "Tanks.Temperature",
"dataFilterId": null
},
{
"valueColumn": "Pressure",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tanks",
"selectColumn": null,
"selectValue": null,
"scheduleId": "1",
"uom": Pas,
"selected": true,
"name": null,
"streamId": "Tanks.Pressure",
"dataFilterId": null
},
{
"valueColumn": "Volume",
"dataColumns": null,
"indexColumn": "SampleTime",
"queryId": "Tanks",
"selectColumn": null,
"selectValue": null,
"scheduleId": "1",
"uom": "Ltr",
"selected": true,
"name": null,
"streamId": "Tanks.Volume",
"dataFilterId": null,
}
]