Home Server Server Primers Datasources and Adaptors Writing Queries for the Relational Adaptor

Writing Queries for the Relational Adaptor

There are 3 important types of parameters for the Relational Adaptor.

Type of parameter Parameters of this type Explanation
Connection Parameters
  • Connection Type
  • Server
  • Database*
  • Use Trusted Connection
  • User ID
  • Password

Connection parameters are used to connect to a database.

*Database is only required is the Connection Type is Microsoft SQL Server. 

For Oracle, you may need to set the server to the relevant TNS of your datasource. E.g.
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.com)(PORT=1522))(CONNECT_DATA=(SID = t721)))
Query Parameters
  • Available Tags Query
  • Single Point Raw Query
  • Historical Raw Query
 The query parameters are used to fetch tags and tag values. These parameters are SQL queries that are usually expressed with keywords specific to the Relational Adaptor, to make your queries more easily understood.
Column Parameters Required:

  • Tag Name Column
  • Data Timestamp Column
  • Data Value Column (used only for Narrow type)
  • Query Type
  • Available Tags Query
  • Single Point Raw Query
  • Historical Raw Query

Optional:

  • Tag Description Column
  • Tag Unit Column
  • Tag Maximum Value Column
  • Tag Minimum Value Column
  • Data Confidence Column
  • Put Select Query
  • Put Insert Statement
  • Put Update Statement
 These parameters specify the column names that will define your timeseries query results.

  • Tag Maximum Value Column – If not provided, the adaptor will return null maximum value.
  • Tag Minimum Value Column – If not provided, the adaptor will return null minimum value.
  • Data Confidence Column – If not provided, the adaptor will return 100 as the default confidence value.

Keywords

When the Relational Adaptor identifies any of these keywords in a query, it replaces them with the corresponding request’s field.

Keyword Description Narrow or Wide Read or Write
CONFIDENCE() Confidence of the value that is being inserted/updated. Both Write
ENDTIME() The request’s end time. Both Read
STARTTIME() The request’s start time. Both Read
TAGNAME() Name of the tag that is being inserted/updated in a narrow table. Narrow Write
TAGLIST() The request’s entity names. Used in Narrow query requests. Narrow Read
TAGFIELD() Name of the column/field that is being inserted/updated in a wide table. Wide Write
TAGFIELDLIST() The request’s entity names (translated to field names). Used in Wide query requests. Wide Read
NAMELIST() The request’s entity names. Used in Wide query requests. Wide Read
NAME() Name of the entity whose value is being inserted/updated in a wide table. Wide Write
SAMPLEINTERVAL() Sample interval of the get request as a number of seconds (it can have a fractional part, so a half second interval would be passed to the query as 0.5). Both Read
SAMPLEMETHOD() Sample method of the get request as a string. Both Read
TIMESTAMP() Timestamp of the value that is being inserted/updated. Both Write
VALUE() The value that is being inserted/updated. Both Write

Example Query using Column Parameters

SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_METRICS.PRODUCTION_DATE,
  PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL, PRV_PRODUCTION_METRICS.CONFIDENCE, 
  PRV_PRODUCTION_METRICS.MIN, PRV_PRODUCTION_METRICS.MAX
FROM PRV_PRODUCTION_METRICS, PRV_PRODUCTION_HIERARCHIES
WHERE PRV_PRODUCTION_METRICS.PRODUCTION_ENTITY_ID = PRV_PRODUCTION_HIERARCHIES.PRODUCTION_ENTITY_ID
AND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (NAMELIST())
AND PRV_PRODUCTION_METRICS.PRODUCTION_DATE BETWEEN TRUNC(STARTTIME()) 
AND TRUNC(ENDTIME())
ORDER BY PRV_PRODUCTION_METRICS.PRODUCTION_DATE ASC

Where:

Tag Name Column = AREA_NAME
Data Timestamp Column = PRODUCTION_DATE
Data Value Column = PROD_OIL_ALLOC_GROSS_VOL
Data Confidence Column = CONFIDENCE
Tag Minimum Value Column = MIN
Tag Maximum Value Column = MAX

It is important that you order the returned results in date order, oldest time first.

PUT Queries

There are 3 main rules for being able to write values back to a source database.

  1. ‘Allow Write’ must be set to True.
  2. The available tags query must contain the value column with the correct data type.
  3. Run Tag Discovery at least once with such an available tags query.

About the Available Tags Query

The Available Tags Query (also known as the Discovery Query) is used to return a list of tags. It must always contain the Value column, even for narrow queries. This is because the adaptor will store the Value column’s type in the native names of tags, so that the adaptor will know how to convert incoming values to what the database expects.

This has 2 important consequences:

  • If the Value column is not part of the query, the type would be missing from the native name. When an adaptor receives a Put request for a tag which doesn’t have the data type stored in its native name, it throws an InvalidConfigurationException. Therefore, if you had an existing read-only datasource that you want to turn into a read-write datasource, you must update the query and run the available tags query again, so that the adaptor can store the required types in the native names.
  • If the adaptor cannot determine the type of a column (e.g. sql_variant type) or it determines the type incorrectly (as can happen with ODBC), you can override the column type in the available tags query to provide the adaptor with instructions on how to handle the specific column. To do this, use the CONVERT() function (if using MSSQL) or the CAST() function (if using Oracle).
    Examples:
    SELECT …, CONVERT(int, SqlVariantValueColumn)…
    SELECT …, CAST(VALUE_COLUMN AS date) …

If writing values is not required, then the Value column can be omitted from narrow queries.

Narrow Queries

A relational query can be in one of two forms: Narrow or Wide. Narrow queries return a single Value column. If you configure your datasource’s query type to Narrow, you need to provide the Value column in your query.

E.g. In the following query, PROD_OIL_ALLOC_GROSS_VOL is the Value column:

SELECT PRV_PRODUCTION_METRICS.NAME, PRV_PRODUCTION_METRICS.PROD_DATE, 
   PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL
FROM PRV_PRODUCTION_METRICS 
WHERE PRV_PRODUCTION_METRICS.NAME IN (NAMELIST())
ORDER BY PRV_PRODUCTION_METRICS.PROD_DATE ASC

Here are some more examples.

Available Tags Query

This query returns a list of tags.

SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME
FROM PRV_PRODUCTION_HIERARCHIES

Single Point Raw Query

This is an example of a single point raw query using TAGLIST() and STARTTIME() keywords.

SELECT PRVPH.AREA_NAME, PRVPM.PRODUCTION_DATE, SUM(PRVPM.PROD_OIL_ALLOC_GROSS_VOL) 
AS PROD_OIL_ALLOC_GROSS_VOL
FROM PRV_PRODUCTION_METRICS PRVPM, PRV_PRODUCTION_HIERARCHIES PRVPH,
   (SELECT PRVPHMT.AREA_NAME AS NAME,MAX(PRVPMMT.PRODUCTION_DATE) AS MAX_TIMESTAMP
   FROM PRV_PRODUCTION_METRICS PRVPMMT, PRV_PRODUCTION_HIERARCHIES PRVPHMT
   WHERE PRVPMMT.PRODUCTION_ENTITY_ID = PRVPHMT.PRODUCTION_ENTITY_ID
   AND PRVPHMT.AREA_NAME IN (TAGLIST())
   AND PRVPMMT.PRODUCTION_DATE <= STARTTIME()
   AND PRVPMMT.READING_TYPE = 'DAILY'
   GROUP BY PRVPHMT.AREA_NAME) PRVPMMAXTIME
WHERE PRVPM.PRODUCTION_ENTITY_ID = PRVPH.PRODUCTION_ENTITY_ID
AND PRVPM.PRODUCTION_DATE = PRVPMMAXTIME.MAX_TIMESTAMP
AND PRVPH.AREA_NAME = PRVPMMAXTIME.NAME
AND PRVPH.AREA_NAME IN (TAGLIST())
GROUP BY PRVPH.AREA_NAME, PRVPM.PRODUCTION_DATE

Historical Raw Query

This is an example of a historical raw query using TAGLIST(), STARTTIME(), and ENDTIME() keywords.

SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_METRICS.PRODUCTION_DATE, 
   PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL
FROM PRV_PRODUCTION_METRICS, PRV_PRODUCTION_HIERARCHIES
WHERE PRV_PRODUCTION_METRICS.PRODUCTION_ENTITY_ID = PRV_PRODUCTION_HIERARCHIES.PRODUCTION_ENTITY_ID
AND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (TAGLIST())
AND PRV_PRODUCTION_METRICS.PRODUCTION_DATE BETWEEN TRUNC(STARTTIME()) 
AND TRUNC(ENDTIME())
ORDER BY PRV_PRODUCTION_METRICS.PRODUCTION_DATE ASC

Put Select Query

In this type of query, the adaptor does not care about the actual value in the database. It just has to know whether it exists or not. Whether you use “SELECT NULL” or “SELECT Value()” or “SELECT ‘Wednesday’” doesn’t matter. The adaptor never inspects the returned value. It just checks whether the query returned any row or not so that it can decide whether to execute an INSERT or an UPDATE statement.

SELECT NULL
FROM NarrowTable
WHERE Tag = TagName()
AND Timestamp = Timestamp()

Put Insert Statement

INSERT INTO NarrowTable
Tag, Timestamp, Confidence, Value()
VALUES (TagName(), Timestamp(), Confidence(), Value())

Put Update Statement

UPDATE NarrowTable
SET Confidence = Confidence(), Value = Value()
WHERE Tag = TagName()
AND Timestamp = Timestamp()

 

Wide Queries

Wide queries return more than one Value column.

E.g. In the following wide query, PROD_OIL_ALLOC_GROSS_VOL and PROD_OIL_TARGET_GROSS_VOL are the Value columns:

SELECT PRV_PRODUCTION_METRICS.NAME AS NAME, PRV_PRODUCTION_METRICS.PROD_DATE AS TIMESTAMP, 
   PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL, PRV_PRODUCTION_METRICS.PROD_OIL_TARGET_GROSS_VOL
FROM PRV_PRODUCTION_METRICS
WHERE PRV_PRODUCTION_METRICS.NAME IN (NAMELIST())
ORDER BY PRV_PRODUCTION_METRICS.PROD_DATE ASC

Here are some more examples.

Available Tags Query

This query returns a list of tags.

SELECT DISTINCT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, 0 
AS PROD_OIL_ALLOC_GROSS_VOL, 0 AS PROD_OIL_TARGET_GROSS_VOL
FROM PRV_PRODUCTION_HIERARCHIES
WHERE PRV_PRODUCTION_HIERARCHIES.AREA_NAME IS NOT NULL

Single Point Query

This is an example of a single point query using NAMELIST() and STARTTIME() keywords.

SELECT PRVPH.AREA_NAME, PRVPM.PRODUCTION_DATE,
SUM(PRVPM.PROD_OIL_ALLOC_GROSS_VOL) AS PROD_OIL_ALLOC_GROSS_VOL,
SUM(PRVPM.PROD_OIL_TARGET_GROSS_VOL) AS PROD_OIL_TARGET_GROSS_VOL,
SUM(PRVPM.PROD_GAS_ALLOC_GROSS_VOL) AS PROD_GAS_ALLOC_GROSS_VOL,
SUM(PRVPM.PROD_GAS_TARGET_GROSS_VOL) AS PROD_GAS_TARGET_GROSS_VOL,
FROM PRV_PRODUCTION_METRICS PRVPM, PRV_PRODUCTION_HIERARCHIES PRVPH,
  (SELECT&nbsp;PRVPHMT.AREA_NAME AS NAME, 
   MAX(PRVPMMT.PRODUCTION_DATE) AS MAX_TIMESTAMP FROM PRV_PRODUCTION_METRICS PRVPMMT, 
   PRV_PRODUCTION_HIERARCHIES PRVPHMT
  WHERE&nbsp;PRVPHMT.AREA_NAME IN (NAMELIST())
  AND PRVPMMT.PRODUCTION_DATE <= TRUNC(STARTTIME())
  GROUP BY PRVPHMT.AREA_NAME) PRVPMMAXTIME
WHERE PRVPM.PRODUCTION_ENTITY_ID = PRVPH.PRODUCTION_ENTITY_ID
AND PRVPM.PRODUCTION_DATE = PRVPMMAXTIME.MAX_TIMESTAMP
AND STARTTIME() < PRVPMMAXTIME.MAX_TIMESTAMP
AND PRVPH.AREA_NAME = PRVPMMAXTIME.NAME
AND PRVPH.AREA_NAME IN (NAMELIST())
GROUP BY PRVPH.AREA_NAME, PRVPM.PRODUCTION_DATE

Single Point Query

This is an example of a single point query using TAGFIELDLIST(), NAMELIST(), and STARTTIME() keywords.

SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_HIERARCHIES.START_DATE, TAGFIELDLIST()
FROM PRV_PRODUCTION_HIERARCHIES
WHERE PRV_PRODUCTION_HIERARCHIES.START_DATE >= TRUNC(STARTTIME())&nbsp;
AND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (NAMELIST())

Historical Raw Query

This is an example of a historical raw query using NAMELIST(), STARTTIME(), and ENDTIME() keywords.

SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_METRICS.PRODUCTION_DATE, 
   PRV_PRODUCTION_METRICS.PROD_OIL_ALLOC_GROSS_VOL, PRV_PRODUCTION_METRICS.PROD_OIL_TARGET_GROSS_VOL
FROM PRV_PRODUCTION_METRICS, PRV_PRODUCTION_HIERARCHIES
WHERE PRV_PRODUCTION_METRICS.PRODUCTION_ENTITY_ID = PRV_PRODUCTION_HIERARCHIES.PRODUCTION_ENTITY_ID
AND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (NAMELIST())
AND PRV_PRODUCTION_METRICS.PRODUCTION_DATE BETWEEN STARTTIME() AND ENDTIME()
ORDER BY PRV_PRODUCTION_METRICS.PRODUCTION_DATE ASC

Historical Raw Query

This is an example of a historical raw query using NAMELIST(), TAGFIELDLIST(), STARTTIME(), and ENDTIME() keywords.

SELECT PRV_PRODUCTION_HIERARCHIES.AREA_NAME, PRV_PRODUCTION_HIERARCHIES.START_DATE, TAGFIELDLIST()
FROM PRV_PRODUCTION_HIERARCHIES
WHERE PRV_PRODUCTION_HIERARCHIES.START_DATE BETWEEN STARTTIME() AND ENDTIME())
AND PRV_PRODUCTION_HIERARCHIES.AREA_NAME IN (NAMELIST())
ORDER BY PRV_PRODUCTION_METRICS.PRODUCTION_DATE ASC

Put Select Query

In this type of query, the adaptor does not care about the actual value in the database. It just has to know whether it exists or not. Whether you use “SELECT NULL” or “SELECT Value()” or “SELECT ‘Wednesday’” doesn’t matter. The adaptor never inspects the returned value. It just checks whether the query returned any row or not so that it can decide whether to execute an INSERT or an UPDATE statement.

SELECT NULL
FROM WideTable
WHERE EntityName = Name()
AND Timestamp = Timestamp()

Put Insert Statement

INSERT INTO WideTable
(EntityName, Timestamp, Confidence, TagField())
VALUES (Name(), Timestamp(), Confidence(), Value())

Put Update Statement

UPDATE WideTable
SET Confidence = Confidence(), TagField() = Value()
WHERE EntityName = Name()
AND Timestamp = Timestamp()

 

Comments are closed