`picomp2` Command

Description

Accepts archived data retrieved from the picomp2 table using CSV format with a pre-defined column order.

The uploaded data is stored as a series. If the PI Point data type is string, digital, or timestamp, their values are stored in the x: text field.

Syntax

picomp2 z:${timezone} e:${entity} i:${ignore-defaults} t:${tag-name}=${tag-value}
... pointtypex, picomp2 column values ...

Fields

Name	Type	Description
`e`	string	[Required] Default entity name.
`i`	boolean	Ignore default values: `status` = `0`, `status_test` = `GOOD`, `flags` = `false`, `annotations` = `null` or empty. Default: `true` (ignore defaults).
`z`	string	Time Zone applied when parsing dates, for example EST. Default time zone: UTC. Default date format: `yyyy-MM-dd HH:mm:ss`.
`t`	string	One or multiple series or message tag `key=value` pairs, for example: `t:location=SVL`.

Columns

pointtypex
tag
time
_index
value
status
status_text
questionable
substituted
annotated
annotations

Data Types

The following data types are supported for the value column.

String
Digital
Int16
Int32
Float16
Float32
Float64
Timestamp

PI Query

The order of columns in the uploaded content corresponds to results of the following query.

SELECT TOP 10 TAGTYPE(tag) as pointtypex,
        tag,
        time,
        _index,
        CASE TAGTYPE(tag)
          WHEN 'digital' THEN CAST(DIGSTRING(CAST(value as Int32)) as VARIANT)
          ELSE value
        END AS value,
        status,
        DIGSTRING(status) AS status_text,
        questionable, substituted, annotated, annotations
FROM piarchive..picomp2
WHERE tag = 'sinusoid'
  ORDER BY time

| pointtypex | tag      | time                | _index | value | status | status_text | questionable | substituted | annotated | annotations |
|------------|----------|---------------------|--------|-------|--------|-------------|--------------|-------------|-----------|-------------|
| float64    | sinusoid | 2016-08-24 17:43:44 | 1      | 0.92  | 0      | Good        | false        | false       | false     | null        |

An _index column, which represents a sample order for a given timestamp, is ignored if <= 1. If _index exceeds 1, the column is added as series tag.
tag, value, and annotations column values must be properly escaped if the value contains a comma, double quote, or line break.
Numbers must be formatted using dot (.) as the decimal separator and without the grouping separator.
Empty fields and fields with literal null values are ignored.

series Command Mappings

Command Field	Input Field
Metric Name	`tag` column. Whitespace characters contained in the tag name replaced with underscore.
Entity Name	`e` entity field.
Time	`time` column.
Value	`value` column if numeric.
Text	`value` column if text.
Series Tags	`status` and `status_text` columns if not default. `_index` column if `>= 1`. `questionable`, `substituted`, `annotated` columns if not `false`. `annotations` column if not null/empty.

ABNF Syntax

Rules inherited from Base ABNF.

command = "picomp2" MSP entity [MSP timezone] [MSP ignore-defaults] *(MSP tag)
entity = "e:" NAME
ignore-defaults = "i:" BOOLEAN
timezone = "z:" TIMEZONE
tag = "t:" NAME "=" VALUE

Example - Store All Fields

picomp2 e:default t:environment=prod
Float64,sinusoid,2016-10-09 17:43:44,1,0.92,0,false,false,false,
Float64,sinusoid,2016-10-09 17:43:58,1,0.97,0,false,false,false,

Example - Store Base Fields

picomp2 e:default t:environment=prod
Float64,sinusoid,2016-10-09 17:43:44,,0.92,,,,,
Float64,sinusoid,2016-10-09 17:43:58,,0.97,,,,,

picomp2 Query Results

| pointtypex | tag                          | time                | _index | value    | raw_value | status_string | status | questionable | substituted | annotated | annotations |
|------------|------------------------------|---------------------|--------|----------|-----------|---------------|--------|--------------|-------------|-----------|-------------|
| Float32    | Memory_Avail MBytes          | 2016-09-20 12:57:49 | 1      | null     | null      | Pt Created    | -253   | false        | false       | false     | null        |
| Float32    | Memory_Avail MBytes          | 2016-10-11 15:38:00 | 1      | 6139.0   | 6139.0    | Good          | 0      | false        | false       | false     | null        |
| Float32    | Memory_Avail MBytes          | 2016-10-11 15:38:00 | 2      | 6139.1   | 6139.1    | Good          | 0      | false        | false       | false     | null        |
| Float32    | Memory_Avail MBytes          | 2016-10-11 15:38:00 | 3      | 6139.2   | 6139.2    | Good          | 0      | false        | false       | false     | null        |
| Float32    | Memory_Avail MBytes          | 2016-10-11 15:38:01 | 1      | 6141.0   | 6141.0    | Good          | 0      | false        | false       | false     | null        |
| Digital    | BA:ACTIVE.1                  | 2016-08-24 15:02:55 | 1      | null     | null      | Pt Created    | -253   | false        | false       | false     | null        |
| Digital    | BA:ACTIVE.1                  | 2016-08-24 15:03:17 | 1      | Inactive | -65536    | Good          | 0      | false        | false       | false     | null        |
| Digital    | BA:ACTIVE.1                  | 2016-08-24 15:04:17 | 1      | Active   | -65537    | Good          | 0      | false        | false       | false     | null        |
| Digital    | BA:ACTIVE.1                  | 2016-08-24 16:15:17 | 1      | Inactive | -65536    | Good          | 0      | false        | false       | false     | null        |
| Int32      | CDEP158                      | 2016-08-24 15:01:09 | 1      | null     | null      | Shutdown      | -254   | false        | false       | false     | null        |
| Int32      | CDEP158                      | 2016-08-24 15:03:17 | 1      | 0        | 0         | Good          | 0      | false        | false       | false     | null        |
| Int32      | CDEP158                      | 2016-08-24 15:43:17 | 1      | 12       | 12        | Good          | 0      | false        | false       | false     | null        |
| Int32      | CDEP158                      | 2016-08-24 16:21:20 | 1      | null     | null      | Shutdown      | -254   | false        | false       | false     | null        |
| Int32      | CDEP158                      | 2016-08-24 16:23:30 | 1      | 1        | 1         | Good          | 0      | false        | false       | false     | null        |
| Int32      | CDEP158                      | 2016-08-24 22:28:30 | 1      | 43       | 43        | Good          | 0      | false        | false       | false     | null        |
|------------|------------------------------|---------------------|--------|----------|-----------|---------------|--------|--------------|-------------|-----------|-------------|

picomp2 Commands

series e:default d:2016-09-20T12:57:49Z m:Memory_Avail_MBytes=NaN t:status_text="Pt Created" t:status=-253
series e:default d:2016-10-11T15:38:00Z m:Memory_Avail_MBytes=6139.0
series e:default d:2016-10-11T15:38:00Z m:Memory_Avail_MBytes=6139.1 t:_index=2
series e:default d:2016-10-11T15:38:00Z m:Memory_Avail_MBytes=6139.2 t:_index=3
series e:default d:2016-10-11T15:38:01Z m:Memory_Avail_MBytes=6141.0
series e:default d:2016-08-24T15:02:55Z x:BA:ACTIVE.1="" t:status_text="Pt Created" t:status=-253
series e:default d:2016-08-24T15:03:17Z x:BA:ACTIVE.1=Inactive
series e:default d:2016-08-24T15:04:17Z x:BA:ACTIVE.1=Active
series e:default d:2016-08-24T16:15:17Z x:BA:ACTIVE.1=Inactive
series e:default d:2016-08-24T15:01:09Z m:CDEP158=NaN t:status_text="Shutdown" t:status=-254
series e:default d:2016-08-24T15:03:17Z m:CDEP158=0
series e:default d:2016-08-24T15:43:17Z m:CDEP158=12
series e:default d:2016-08-24T16:21:20Z m:CDEP158=NaN t:status_text="Shutdown" t:status=-254
series e:default d:2016-08-24T16:23:30Z m:CDEP158=1
series e:default d:2016-08-24T22:28:30Z m:CDEP158=43

SELECT entity, metric AS pitag, datetime, value, ISNULL(text, '') AS svalue,
  ISNULL(tags._index, '1') AS '_index', ISNULL(tags.status, '0') AS status, ISNULL(tags.status_text, 'Good') AS status_text,
  ISNULL(tags.questionable, 'false') AS questionable, ISNULL(tags.substituted, 'false') AS substituted, ISNULL(tags.annotated, 'false') AS annotated,
  ISNULL(tags.annotations, '') AS annotations
FROM atsd_series
  WHERE metric IN ('Memory_Avail_MBytes', 'BA:ACTIVE.1', 'CDEP158')
AND entity = 'default'

| entity  | pitag               | datetime             | value  | svalue   | _index | status | status_text | questionable | substituted | annotated | annotations |
|---------|---------------------|----------------------|--------|----------|--------|--------|-------------|--------------|-------------|-----------|-------------|
| default | memory_avail_mbytes | 2016-09-20T12:57:49Z | NaN    |          | 1      | -253   | Pt Created  | false        | false       | false     |             |
| default | memory_avail_mbytes | 2016-10-11T15:38:00Z | 6139.0 |          | 1      | 0      | Good        | false        | false       | false     |             |
| default | memory_avail_mbytes | 2016-10-11T15:38:00Z | 6139.1 |          | 2      | 0      | Good        | false        | false       | false     |             |
| default | memory_avail_mbytes | 2016-10-11T15:38:00Z | 6139.2 |          | 3      | 0      | Good        | false        | false       | false     |             |
| default | memory_avail_mbytes | 2016-10-11T15:38:01Z | 6141.0 |          | 1      | 0      | Good        | false        | false       | false     |             |
| default | ba:active.1         | 2016-08-24T15:02:55Z | NaN    |          | 1      | -253   | Pt Created  | false        | false       | false     |             |
| default | ba:active.1         | 2016-08-24T15:03:17Z | NaN    | Inactive | 1      | 0      | Good        | false        | false       | false     |             |
| default | ba:active.1         | 2016-08-24T15:04:17Z | NaN    | Active   | 1      | 0      | Good        | false        | false       | false     |             |
| default | ba:active.1         | 2016-08-24T16:15:17Z | NaN    | Inactive | 1      | 0      | Good        | false        | false       | false     |             |
| default | cdep158             | 2016-08-24T15:01:09Z | NaN    |          | 1      | -254   | Shutdown    | false        | false       | false     |             |
| default | cdep158             | 2016-08-24T15:03:17Z | 0.0    |          | 1      | 0      | Good        | false        | false       | false     |             |
| default | cdep158             | 2016-08-24T15:43:17Z | 12.0   |          | 1      | 0      | Good        | false        | false       | false     |             |
| default | cdep158             | 2016-08-24T16:21:20Z | NaN    |          | 1      | -254   | Shutdown    | false        | false       | false     |             |
| default | cdep158             | 2016-08-24T16:23:30Z | 1.0    |          | 1      | 0      | Good        | false        | false       | false     |             |
| default | cdep158             | 2016-08-24T22:28:30Z | 43.0   |          | 1      | 0      | Good        | false        | false       | false     |             |

# picomp2 Command

# Description

# Syntax

# Fields

# Columns

# Data Types

# PI Query

# series Command Mappings

# ABNF Syntax

# Example - Store All Fields

# Example - Store Base Fields

# picomp2 Query Results

# picomp2 Commands