Joseph Doherty
32f26272ae
Five tools under one repo, all docs organized per DOCS-GUIDE.md: - aalogcli: .NET 4.8 / x86 CliFx CLI for reading System Platform binary logs (*.aaLGX) for LLM debugging, built on aaOpenSource/aaLog. Commands: last, tail, range, unread, fields. Stable JSON envelope under --llm-json. Build template under lib/build/ for rebuilding aaLogReader.dll. - aot: ArchestrA Object Toolkit 2014 v4.0 reference material. Dev guide (Markdown converted from CHM), API reference for the ArchestrA.Toolkit namespace, and the Monitor / Watchdog VS sample solutions. - graccesscli: .NET 4.8 / x86 CliFx CLI that automates Galaxy configuration via the ArchestrA GRAccess COM interop. Includes session daemon, IPC protocol, and llm-json envelope contract. - grdb: SQL/DDL exploration of the Galaxy Repository database. DDL captures, reusable queries, hierarchy / contained-name <-> tag-name translation notes. - histdb: LLM-oriented reference for AVEVA Historian retrieval. INSQL linked-server, extension tables, every wwXxx time-domain extension, every retrieval mode, alarm/event SQL recipes, REST API. Distilled from the 243-page Historian Retrieval Guide. Root contains: - CLAUDE.md: thin index pointing into each tool's README. - DOCS-GUIDE.md: doctrine for organizing docs for LLM consumption. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
151 lines
6.3 KiB
Markdown
151 lines
6.3 KiB
Markdown
# Overview — connecting and querying
|
|
|
|
## What's actually behind a Historian query
|
|
|
|
```
|
|
client (SSMS / app / aalog-equivalent)
|
|
│ T-SQL
|
|
▼
|
|
Microsoft SQL Server ◄── normal SQL Server tables (Tag, AnalogTag, EngineeringUnit, etc.)
|
|
│ OLE DB
|
|
▼
|
|
INSQL linked server ◄── this is the AVEVA Historian OLE DB Provider, name "INSQL"
|
|
│ HCAL
|
|
▼
|
|
Retrieval subsystem ◄── reads .aaLGX history blocks on disk, returns rowsets
|
|
```
|
|
|
|
Configuration and event metadata live in real SQL Server tables. **Time-series ("history") data lives in binary history blocks on disk** and is presented through `INSQL` as if it were a SQL table. That's why every history query goes through the linked server.
|
|
|
|
## The extension tables
|
|
|
|
The "virtual" tables exposed by `INSQL`:
|
|
|
|
| Linked-server path | Local view | Purpose |
|
|
| --- | --- | --- |
|
|
| `INSQL.Runtime.dbo.History` | `History` | Tall format. One row per (tag, timestamp). The default for everything historical. |
|
|
| `INSQL.Runtime.dbo.Live` | `Live` | Most recent value per tag. Fast snapshot. |
|
|
| `INSQL.Runtime.dbo.WideHistory` | `WideHistory` | Wide format — one row per timestamp, one column per tag. **Must be queried via `OPENQUERY`**. Up to 1024 columns. |
|
|
| `INSQL.Runtime.dbo.AnalogSummaryHistory` | `AnalogSummaryHistory` | Pre-summarized analog data. |
|
|
| `INSQL.Runtime.dbo.StateSummaryHistory` | `StateSummaryHistory` | Pre-summarized state-tag data. |
|
|
| `INSQL.Runtime.dbo.HistoryBlock` | `HistoryBlock` | Metadata about the on-disk blocks (no `WHERE` required). |
|
|
| `INSQL.Runtime.dbo.Events` | `Events` | Alarm/event records. See [`06-alarms-events.md`](06-alarms-events.md). |
|
|
|
|
**Legacy backward-compatibility tables** (still supported, not preferred):
|
|
`AnalogHistory`, `DiscreteHistory`, `StringHistory`, `AnalogLive`, `DiscreteLive`, `StringLive`, plus `v_AlarmHistory`, `v_AlarmHistory2`, `v_EventHistory`, `v_AlarmEventHistoryInternal2`, `v_AlarmEventHistory2`. Use the unified `History` / `Live` / `Events` tables for new work.
|
|
|
|
## Linking `INSQL` to SQL Server
|
|
|
|
Done automatically by the Historian installer. If you ever need to redo it manually:
|
|
|
|
```sql
|
|
EXEC sp_addlinkedserver @server='INSQL', @srvproduct='', @provider='INSQL';
|
|
EXEC sp_serveroption 'INSQL', 'collation compatible', true;
|
|
EXEC sp_addlinkedsrvlogin 'INSQL', 'TRUE', NULL, NULL, NULL;
|
|
|
|
-- Alias (used when joining the legacy analog and discrete tables in one query):
|
|
EXEC sp_addlinkedserver @server='INSQLD', @srvproduct='', @provider='INSQL';
|
|
EXEC sp_serveroption 'INSQLD', 'collation compatible', true;
|
|
EXEC sp_addlinkedsrvlogin 'INSQLD', 'TRUE', NULL, NULL, NULL;
|
|
```
|
|
|
|
The `INSQL` OLE DB provider cannot run standalone — it must be hosted by SQL Server.
|
|
|
|
## Four ways to issue a SELECT
|
|
|
|
You'll see all four in the wild; each has a niche.
|
|
|
|
### 1. Four-part naming (preferred for `History`, `Live`, `Events`)
|
|
|
|
```sql
|
|
SELECT *
|
|
FROM INSQL.Runtime.dbo.History
|
|
WHERE TagName = 'SysTimeSec'
|
|
AND DateTime >= '2001-09-12 12:59:00'
|
|
AND DateTime <= '2001-09-12 13:00:00';
|
|
```
|
|
|
|
The four parts: `linked_server.catalog.schema.object_name`. For `INSQL` the catalog is **always `Runtime`** and the schema is **always `dbo`**.
|
|
|
|
### 2. Provider view (sugar over four-part)
|
|
|
|
Each extension table has a SQL Server view of the same name — `History`, `Live`, `Events`, etc.
|
|
|
|
```sql
|
|
SELECT *
|
|
FROM History
|
|
WHERE TagName = 'SysTimeSec'
|
|
AND DateTime >= '2001-09-12 12:59:00'
|
|
AND DateTime <= '2001-09-12 13:00:00';
|
|
```
|
|
|
|
Backward-compatibility views use `v_<name>`.
|
|
|
|
### 3. `OPENQUERY` (required for `WideHistory`, useful for pass-through)
|
|
|
|
```sql
|
|
SELECT * FROM OPENQUERY(INSQL, '
|
|
SELECT DateTime, SysTimeSec, ReactTemp
|
|
FROM WideHistory
|
|
WHERE DateTime >= "2001-09-12 12:59:00"
|
|
AND DateTime <= "2001-09-12 13:00:00"
|
|
');
|
|
```
|
|
|
|
The body is sent verbatim to `INSQL`. Quoting is **double quotes inside, single quote around the whole body.** Hard caps and quirks:
|
|
|
|
- 8000-character body limit.
|
|
- DateTime format must be `yyyy-mm-dd hh:mm:ss.fff`.
|
|
- `ORDER BY` and `GROUP BY` don't work *inside* the body (SQL Server doesn't see them) — apply them outside.
|
|
- Joins inside the body are unsupported. Place them outside.
|
|
- Variables (`@foo`) cannot appear inside the body.
|
|
|
|
### 4. `OPENROWSET` (one-off ad-hoc)
|
|
|
|
```sql
|
|
SELECT * FROM OPENROWSET('INSQL', '', '
|
|
SELECT DateTime, Quality, QualityDetail, Value
|
|
FROM History
|
|
WHERE TagName IN ("SysTimeSec")
|
|
AND DateTime >= "2001-09-12 12:59:00"
|
|
AND DateTime <= "2001-09-12 13:00:00"
|
|
');
|
|
```
|
|
|
|
Requires that ad-hoc distributed queries be enabled on the SQL Server (`sp_configure 'Ad Hoc Distributed Queries', 1`). Generally not recommended in production code.
|
|
|
|
## When to use which style
|
|
|
|
| Scenario | Style |
|
|
| --- | --- |
|
|
| Tall queries against `History`, `Live`, `Events` | **Four-part** or **view** |
|
|
| `WideHistory` (one column per tag) | **OPENQUERY** (mandatory) |
|
|
| Heavy filtering you want SQL Server to optimize | **Four-part** (the optimizer can see the predicates) |
|
|
| Forcing a predicate down to `INSQL` that the optimizer is dropping | **OPENQUERY** (literal pass-through) |
|
|
| Joining a SQL Server table against a Historian extension table | Four-part, with **`INNER REMOTE JOIN`** |
|
|
| One-off scripts on a server with ad-hoc DQ enabled | **OPENROWSET** |
|
|
|
|
## Date format gotcha
|
|
|
|
For four-part queries on **non-English SQL Server installations** (French, German, etc.), the default datetime format may be `yyyy-dd-mm hh:mm:ss.fff` instead of `yyyy-mm-dd ...`. Use `CONVERT(...)` explicitly when in doubt — output formatting also depends on the OS regional settings.
|
|
|
|
For `OPENQUERY`, always use `yyyy-mm-dd hh:mm:ss.fff` regardless of locale.
|
|
|
|
## "No `WHERE` clause" error
|
|
|
|
If you get:
|
|
|
|
```
|
|
Server: Msg 7320, Level 16, State 2, Line 1
|
|
Could not execute query against OLE DB provider 'INSQL'.
|
|
[OLE/DB provider returned message: InSQL did not receive a WHERE clause from SQL Server. ...]
|
|
```
|
|
|
|
…the SQL Server query optimizer truncated the `WHERE` clause as "superfluous." Workaround: add a redundant condition such as `AND wwRetrievalMode = 'delta'`. See [`02-syntax-limits.md`](02-syntax-limits.md#where-clause-anomalies).
|
|
|
|
## Next
|
|
|
|
- [`02-syntax-limits.md`](02-syntax-limits.md) — what works and what doesn't, plus the `wwXxx` extensions overview.
|
|
- [`03-retrieval-modes.md`](03-retrieval-modes.md) — pick a `wwRetrievalMode`.
|
|
- [`05-query-recipes.md`](05-query-recipes.md) — practical patterns.
|