InfluxDB® Schema
Database & Retention Policies
Database: The default database is "winccoa".
Retention Policies: Each configured archive group for the InfluxDB® backend maps 1:1 to a retention policy. The name of the retention policy is the "Group Name" as configured in the GUI.
The retention period of all retention policies in the database is infinite. This is to prevent InfluxDB® from deleting the retention policies on its own. Instead, the user-specific retention period is used in the backend to check if a shard in such a retention period has expired. If so, the backend backups the shard before it is deleted.
Valuetype
The valuetype is used to convert the read value back to the original WinCC OA value and indicates which column should be read.
| valuetype | WinCC OA Element Type | InfluxDB® Column Suffix and Type |
| 0 | bool | bool |
| 1 | int | int |
| 2 | unsigned int | int |
| 3 | float | float or float_special |
| 4 | string | string |
| 5 | blob | N/A |
| 6 | lang string | string |
| 7 | char | int |
| 8 | bit32 | int |
| 9 | bit64 | int |
| 10 | time | int |
| 11 | dpid | string |
| 12 | long | int |
| 13 | unsigned long | int |
| all | dyn | dyn |
Notes
- If the dyn column is not null, it has priority
- if float_special is not null, it has priority over float
- float_special is stored as string
The column with suffix _float_special contains the special float values values #inf and nan since InfluxDB® cannot map them to float values.
Storage of DYNs
If the element type is dyn, it is always stored in the column with the "dyn_" suffix.
To fit all elements into one column, a custom format is used.
This format is a repeated sequence of:
NumberOfBytes:<Bytes>
Example
5:Hello6:World!
The langString data type uses a format derived from the sequence above.
A langString is an array of strings. However, by default, it is saved in the column with the "string_" suffix but using the dyn storage format.
This makes a dyn_LangString an array of an array, which then layers this format.
Example
14:5:Hello5:Hallo9:3:You2:Du
14 bytes are the first entry in the array, which is
5:Hello5:Hallo.
Followed by 9 bytes of the second entry, which is 3:You2:Du.
Each of these entries is a single LangString, which again is a string array itself.
Events/Values
This is the schema of the value history measurement ("table") in InfluxDB®.
| Column Name | Type | Datatype | Description |
| time | - | - | Time when the value was changed |
| name | tag | - | Name of the DPE |
| variabletype | tag | - | WinCC OA Type |
| alias | tag | - |
Important:
Line breaks (\n) in
the alias attribute of a DP element are not supported in the
Influx database® and must be avoided! |
| valuetype | field | int | See value type table |
| original_value_suffix | field | - | Suffix depending on the valuetype |
| _status64 | field | int | |
| _manager | field | int | |
| _user | field | int | |
| _unit | field | string | Attribute of the _common config of a DPE. |
| _comment | field | langString | Attribute of the _common config of a DPE. |
Alarms/Alerts
This is the schema of the alert history measurement ("table") in InfluxDB®.
Langstrings in Alarms/Alerts
Langstring in alarms or alerts are different than the langStrings in events/values. For alarms and alerts each lang string entry gets its own column instead of using the dyn storage format. This is necessary since a direct and simple comparison is needed for filtering within WinCC OA.
The format for this is:
columnName_LangIdSuffix
Example
_abbr_3
Would indicate that this column holds the 4th (0-based index) language as defined in the project.
| Column Name | Type | Data type | Description |
| time | - | - | Time the alarm occurred |
| name | tag | string | Name of the DPE |
| variabletype | tag | string | WinCC OA Type |
| alias | tag | string | |
| _dpeid_dp | tag | string | Datapoint ID |
| _dpeid_el | tag | string | Element ID |
| alarmvalue_type | field | int | See value type table |
| alarmvalue_suffix | field | * | Suffix depends on the alarmvalue_type |
| _abbr_ID | field | string | Is a langString |
| _ack_state | field | int | |
| _ack_time | field | float | |
| _ack_type | field | int | |
| _ackable | field | bool | |
| _alert_color | field | string | |
| _alert_font_style | field | string | |
| _alert_fore_color | field | string | |
| _alert_id | field | string | |
| _class | field | string | |
| _comment | field | string | |
| _dest | field | int | |
| _dest_text_ID | field | string | Is a langString |
| _direction | field | bool | |
| _inact_ack | field | bool | |
| _panel | field | string | |
| _partn_idx | field | int | |
| _partner | field | string | |
| _prior | field | int | |
| _single_ack | field | bool | |
| _state | field | int | |
| _system_time | field | string | |
| _text_ID | field | string | Is a langString |
| _value_status64 | field | int | |
| _visible | field | bool | |
| countOfValidAddValues | field | int | Number of add values |
| _add_value_n_type | field | int | See value type table |
| _add_value_n_suffix | field | * | Suffix depends on _add_value_n_type |
General Notes
- A query must at least select one field, otherwise no result will be returned.
- Unsigned integers are not natively supported for the database. Therefore, a WHERE for variables of this type might provide unexpected query results.
- Requesting an "unsigned int" from the database, requires to manually cast the result value to an unsigned type.
- The
timecolumn is always inUTCtime and it is always retrieved as the first column. - If a dpQuery with
SORT BY 0 GROUP BY SECS(<num>)is used to get downsampling data, a result is not returned in an InfluxDB® backend where correction values are set.
Quoting rules
When querying the InfluxDB® directly and not using WinCC OA query functions (dbQuery, dpGetPeriod), the following rules apply.
| For | Use | Example |
| SELECT | double-qoutes |
SELECT "_visible"
|
| FROM | no qoutes |
FROM Events.Event
|
| WHERE column name | double-qoutes |
WHERE "_state"
|
| WHERE string or tag value | single-qoutes |
WHERE ... = 'text'
|
| WHERE numeric value | no qoutes |
WHERE ... > 15
|
| Regular Expressions | forward-slash |
WHERE ... =~ /^[0-9]?/
|
Example Queries
Checking if the database generally works:
SELECT * FROM Event.Event LIMIT 10
Requesting float values:
SELECT "name", "original_value_float", "original_value_float_special" FROM Event.Event WHERE "valuetype" = 3
Requesting all values from a specific type:
SELECT * FROM Event.Event WHERE "variabletype" = 'ExampleDP_Float'
Using a regular expression to request all "_text" columns for alerts:
SELECT "name", /^_text.*/ FROM Alert.Alert
