Tables
| Template Tables (TEQ) | system.stream | system.active_event |
| system.user_notification | system.system_notification | system.job_notification |
| system.org_users | Views |
Template Tables (TEQ™)
Template tables are dynamic virtual tables defined by your component templates. When you use a component template ID as a table name in the FROM clause, the GS SQL engine enters TEQ mode and treats the template as a table, its streams as columns, and its components as rows. This is the recommended query style for organizations that use component templates.See the TEQ Guide for complete documentation, examples, and FK JOIN syntax.
DML Support: Template tables support INSERT, UPDATE, and DELETE.
- INSERT INTO template — Creates new components from the template. Stream column values are appended as samples at NOW.
- UPDATE template SET ... — Appends new stream values at NOW for matching components.
- DELETE FROM template — Removes matching components and all their streams, samples, and index entries.
Component System Columns — always available in every template table. In template diagrams, these columns are hidden by default — click the display system columns toggle to show them (they appear grayed out):
| Column | Type | Description |
| _component_name | String | Component name |
| _component_uid | String | Component UID (unique identifier) |
| _component_id | String | Component ID (programmatic identifier) |
| _component_created_date | Long | Component creation date (epoch milliseconds) |
| _folder_path | String | Folder path containing the component |
Stream Columns — dynamic, determined by the stream IDs defined in each template. By default, stream columns return the
_last_value for each component.
Use per-column temporal parameters (e.g., temperature(range(last=100))) to query
time-series data.
Type Resolution: Stream values are automatically cast to GS SQL types based on the stream's configured value type:
| Stream Value Type | GS SQL Type |
| DOUBLE, FLOAT | DOUBLE |
| LONG, INTEGER, SHORT, DATETIME | LONG |
| BIG_DECIMAL | BIGDECIMAL |
| BOOLEAN | BOOLEAN |
| STRING | STRING |
Discovering Available Template Tables: In Observation Studio, go to the Tools tab to view template diagrams showing all template tables and their FK relationships. Programmatically, use the Component Template API to list templates and their stream definitions.
Views
Views are saved GS SQL queries that can be used as tables in the FROM clause of any other GS SQL query, exactly like views in PostgreSQL, Oracle, SQL Server, or any other relational database. When you reference a view by name in a FROM clause, the GS SQL engine inlines the view's stored SQL as a subquery before executing the outer query. Views are first-class queryable objects — not metadata, not UI artifacts.Two kinds of views, both queryable as tables:
- Local views —
CREATE VIEW name AS SELECT ...(no CONNECTION clause). The view body is GS SQL and may reference template tables, system tables, or other views. - External views —
CREATE VIEW name CONNECTION conn AS 'native SQL'. The view body is native SQL executed against the remote JDBC connection on every access.
CREATE VIEW active_sensors AS |
SELECT * FROM active_sensors WHERE temperature > 100; |
SELECT _component_name, temperature(range(last=100)) FROM active_sensors; |
Where views can be used: anywhere a table name is valid — SELECT FROM, JOIN, subqueries, and inside other views. Per-column temporal parameters (e.g.,
temperature(range(sd=-1d)))
flow through views to the underlying template table.
Nesting: Views can reference other views — local views, external views, template tables, and system tables — up to 10 levels deep. Circular references are detected and rejected.
Security: Views use SECURITY DEFINER semantics — the view body executes with the view owner's permissions, not the caller's.
Catalog scope: Views are not catalog-scoped. A view that references a template by name resolves that template against whatever catalog is active at query time.
See the CREATE VIEW reference in the DDL guide for full syntax, materialized views, replace semantics, and connection options.
System Tables
The following system tables provide direct access to GroveStreams data. Use these tables when you need system-level access, are not using component templates, or need to query events, notifications, or jobs.DML Support: The system.sample table supports INSERT OR REPLACE, UPDATE, and DELETE. The system.stream, system.active_event, system.org_users, and notification tables are read-only — DML operations on these tables are blocked. See the Grammar Reference for full syntax.
system.stream
| system.stream Table | |||
| Column | Description | Type | Indexed |
| _component_name | Component name. | String | ✓ |
| _component_uid | Component UID. Globally unique. | String | ✓ |
| _component_id | Component ID. Unique within an organization. Optional. When missing it will be ('') not NULL. | String | ✓ |
| _component_created_date | Component creation date as epoch milliseconds. | Long | |
| _template_uid | Component Template UID. Optional. When missing it will be ('') not NULL. | String | ✓ |
| _template_id | Component Template ID (e.g., 'sensor', 'meter'). Stored and indexed for fast filtering. | String | ✓ |
| _template_name | Component Template display name. Virtual column resolved at query time from _template_uid. | String | |
| _catalog_uid | Catalog UID. Identifies which catalog this stream's template belongs to. Every stream has a catalog; the default catalog is used for streams without an explicit catalog assignment. | String | ✓ |
| _catalog_id | Catalog ID. Virtual column resolved at query time from _catalog_uid. | String | |
| _catalog_name | Catalog display name. Virtual column resolved at query time from _catalog_uid. | String | |
| _folder_path | Component repository folder path. Example: /Components/West. | String | ✓ |
| _name | Stream name. | String | ✓ |
| _uid | Stream UID. Globally unique. | String | ✓ |
| _id | Stream ID. Unique within a component. Optional. When missing it will be ('') not NULL. | String | ✓ |
| _description | Stream description. Optional. When missing it will be ('') not NULL. | String | |
| _time_zone_id | The Time Zone Id. List of Time Zones | String | |
| _unit_uid | Unit UID. Optional. When missing it will be ('') not NULL. | String | |
| _unit_id | Unit ID. Virtual column resolved at query time from _unit_uid. | String | |
| _unit_name | Unit display name. Virtual column resolved at query time from _unit_uid. | String | |
| _stream_type | Stream type: intvl_stream, rdm_stream, point_stream. | String | |
| _derivation_type | Indicates whether and how the stream is derived. | String | |
| _base_cycle_uid | Stream base cycle UID. Optional. When missing it will be ('') not NULL. | String | |
| _base_cycle_id | Base cycle ID. Virtual column resolved at query time from _base_cycle_uid. | String | |
| _base_cycle_name | Base cycle display name. Virtual column resolved at query time from _base_cycle_uid. | String | |
| _rollup_calendar_uid | Stream rollup calendar UID. Optional. When missing it will be ('') not NULL. | String | |
| _rollup_calendar_id | Rollup calendar ID. Virtual column resolved at query time from _rollup_calendar_uid. | String | |
| _rollup_calendar_name | Rollup calendar display name. Virtual column resolved at query time from _rollup_calendar_uid. | String | |
| _time_filter_uid | Stream time filter UID. Optional. When missing it will be ('') not NULL. | String | |
| _time_filter_id | Time filter ID. Virtual column resolved at query time from _time_filter_uid. | String | |
| _time_filter_name | Time filter display name. Virtual column resolved at query time from _time_filter_uid. | String | |
| _default_rollup_method | Stream default rollup method. | String | |
| _sample_value_type | Stream sample value type. | String | |
| _start_date | Stream's earliest sample datetime as epoch milliseconds. The first interval's start datetime for intervals streams. | Long | |
| _end_date | Stream's latest sample datetime as epoch milliseconds. The last interval's end datetime for intervals streams. | Long | |
| _completed_date | Stream completed date as epoch milliseconds. For non-derived streams this matches _end_date. For derived streams it indicates how far derivation has progressed. | Long | |
| _last_updated | The datetime any stream sample was added, updated, or deleted. | Long | |
| _last_value | Stream's latest sample value. | Varies | |
| _last_value_type | Stream's latest sample's value type: Double, Long, String, Boolean, BigDecimal. | String | |
| _sample | Stream sample value. | Varies | |
| _time | Stream sample datetime as epoch milliseconds. Will be the interval end datetime for interval streams. | Long | |
| _time_sd | Stream interval sample start datetime as epoch milliseconds. | Long | |
| _time_ed | Stream interval sample end datetime as epoch milliseconds. | Long | |
About the system.stream Table
The system.stream table provides a unified view of component, stream, and sample data for GS SQL queries. It is automatically kept up to date as data changes.Permissions. Rows are filtered per-component against the content-store: a row is included only if the calling session has read rights (
R) on the row's component. There is no capability gate — access is governed by the user's content-store rights on each component. Components the session cannot read are simply omitted from the result set.
A couple of things to note:
1. Error messages may occasionally refer to internal table names
2. Stream metadata (like component name) is returned once per stream, not once per sample. For example, a query for all component names, such as "SELECT _component_name FROM system.stream", will return the same component name for each of its streams, not for each sample.
Tip: Add DISTINCT to eliminate duplicate component names: "SELECT DISTINCT _component_name FROM system.stream"
Indexes
The system.stream table has secondary indexes that the GS SQL query engine uses to speed up queries with WHERE clause filters. When a query filters on an indexed column, the engine automatically selects the best available index instead of scanning the entire table.Available indexes:
| Columns | Notes |
| _component_name | Component name lookup |
| _component_id, _id | Composite: component ID + stream ID |
| _name | Stream name lookup |
| _component_uid | Component UID lookup |
| _uid | Stream UID lookup |
| _folder_path | Folder path lookup. Supports LIKE 'prefix%' |
| _template_stream_uid | Template stream UID lookup |
| _template_uid | Component template UID lookup |
| _template_id | Component template ID lookup (e.g., 'sensor', 'meter') |
| _catalog_uid | Catalog UID lookup |
Supported operators: =, >, >=, <, <=, BETWEEN, LIKE 'prefix%', STARTSWITH('prefix')
BETWEEN, LIKE 'prefix%', and STARTSWITH('prefix') leverage indexes on all string-type indexed columns.The composite (_component_id, _id) index is used when the WHERE clause filters on both _component_id and _id together. Leftmost prefix matching means filtering on _component_id alone also benefits from this index. For best performance, use AND-connected equality filters on indexed columns.
system.active_event
| system.active_event
Table |
||
| _component_name | Component name. | String |
| _component_uid | Component UID. Globally unique. | String |
| _component_id | Component ID. Unique within an organization. Optional. When missing it will be ('') not NULL. | String |
| _component_icon | Component icon. | String |
| _stream_name | Stream name. | String |
| _stream_uid | Stream UID. Globally unique. | String |
| _sample_time | Stream sample datetime, as epoch milliseconds, that triggered the event. Will be the interval end datetime for interval streams. | Long |
| _sample_time_sd | Stream interval sample start datetime, as epoch milliseconds, that triggered the event. | Long |
| _sample_time_ed | Stream interval sample end datetime, as epoch milliseconds, that triggered the event. | Long |
| _sample_value | Stream sample value that triggered the event. | String |
| _sample_value_type | Stream sample value type (INTEGER, DOUBLE, DATETIME, ...) | String |
| _event_location_latitude | The component location's latitude. | Double |
| _event_location_longitude | The component's location's longitude. | Double |
| _event_location_elevation | The component's location's elevation. | Double |
| _event_location_has_elevation | The component location elevation indicator. | Boolean |
| _event_location_description | The component location's description. Optional. When missing it will be ('') not NULL. | String |
| _event_location_street | The component location's street. Optional. When missing it will be ('') not NULL. | String |
| _event_location_city | The component's locations's city. Optional. When missing it will be ('') not NULL. | String |
| _event_location_state_prov | The component location's state or province. Optional. When missing it will be ('') not NULL. | String |
| _event_location_postal_code | The component location's postal code. Optional. When missing it will be ('') not NULL. | String |
| _event_location_country | The component location's country. Optional. When missing it will be ('') not NULL. | String |
| _event_uid | The event UID. Globally unique. | String |
| _event_trigger_date | The event's datetime as epoch milliseconds. Could be different than the _sample_time if the stream is an interval stream. | Long |
| _event_delivery_state | NO_TRIGGER, DELAYING, TRIGGERED | String |
| _event_delivery_date | The event's delivery date datetime as epoch milliseconds. Could be later than the actual _event_trigger_date if delay is used. | Long |
| _event_delivery_frequency | The event's delivery frequency in milliseconds. | Long |
| _event_delivery_dwell | The event's dwelling time in milliseconds. | Long |
| _event_delay_amount | The event's delay amount in milliseconds. | Long |
| _event_category | INFO, WARN, CRITICAL | String |
| _event_name | The name of the event. | String |
| _event_icon | The icon of the event. | String |
About the system.active_event Table
The system.active_event table holds component stream events that are active. An event row will be removed when the event is no longer active.Permissions. Rows are filtered per-component against the content-store: a row is included only if the calling session has read rights (
R) on the event's component. There is no capability gate — events on components the session cannot read are simply omitted.
system.user_notification
| system.user_notification
Table |
||
| _uid | Notification UID. Globally unique. | String |
| _date | Notification date as epoch milliseconds. | Long |
| _subject | Notification subject. | String |
| _body | Notification body. | String |
| _acknowledge | Whether the notification has been acknowledged by the user. | Boolean |
| _component_uid | Associated event stream's component UID. | String |
| _stream_uid | Associated event component stream's UID. | String |
| _category_type | Message category type (e.g. INFO, WARN, CRITICAL). | String |
| _event_uid | Associated component event UID. | String |
| _action_uid | Associated action package UID. This is an optional field and will be '' (not NULL) when missing. | String |
About the system.user_notification Table
The system.user_notification table holds component stream event notifications for users. Notifications can be acknowledged by users.Permissions. Each session sees only its own notifications — the scan is bound to the calling user's row in the underlying store, so there is no capability gate but also no way to read another user's notifications through this table.
system.system_notification
| system.system_notification
Table |
||
| _uid | Notification UID. Globally unique. | String |
| _date | Notification date as epoch milliseconds. | Long |
| _subject | Notification subject. | String |
| _body | Notification body. | String |
| _category_type | Message category type (e.g. INFO, WARN, CRITICAL). | String |
| _acknowledge | Whether the notification has been acknowledged by the user. | Boolean |
About the system.system_notification Table
The system.system_notification table holds system notifications and will be marked acknowledged if read by anyone. System notifications are used to indicate the starting and stopping of long system processes and to record any exceptions that occur.Permissions. Querying
system.system_notification requires the GSN_SYS ('System Notifications') group capability. Org owners always pass; other sessions need the capability granted via group membership.
system.job_notification
| system.job_notification
Table |
||
| _uid | Job UID. Globally unique. | String |
| _name | Job name. | String |
| _process_queue_uid | Associated queue UID that started the job. This is an optional field and will be '' (not NULL) when missing. | String |
| _id | Job ID. Used by the underlying job infrastructure. | String |
| _start_date | Job start date as epoch milliseconds. | Long |
| _end_date | Job end date as epoch milliseconds. | Long |
| _status | Job status: SETTING_UP, RUNNING, CLEANING_UP, FINISHED_SUCCESS, FINISHED_FAILURE, FINISHED_CANCELLED | String |
| _acknowledged | Whether the job notification has been acknowledged. | Boolean |
| _cancelled | Whether the job has been cancelled. | Boolean |
| _progress | Job progress. Ranges from 0.0 to 1.0 | Double |
| _error_count | Number of errors that occurred while processing the job. | Long |
About the system.job_notification Table
The system.job_notification table holds information in regards to Job processes which are long running, cpu/memory intensive and can be processed by many processes on many servers. Notification rows are created when a job starts. The row is updated as the job runs and finishes. The list of detailed job exceptions are not held within this table.Some job examples are: Importing, Reconciling, AI Forecast Training (or Fitting), Stream Aggregation, Stream Derivation
Permissions. Querying
system.job_notification requires the GSN_JOB ('Job Notifications') group capability. Org owners always pass; other sessions need the capability granted via group membership.
system.org_users
| system.org_users
Table |
||
| _user_uid | User UID. Globally unique. Stable across orgs — the same user has the same _user_uid in every org they belong to. Use this as the FK target from your application templates (e.g., drivers.user_uid → org_users._user_uid). |
String |
| _first_name | User's first name. Reflects the user record's current value — not snapshotted on the membership row. If you need historical display values, denormalize at write time onto your application template (e.g., loads.driver_name_at_assignment). |
String |
| _last_name | User's last name. Concatenate with _first_name at query time to build a display name (e.g., SELECT _first_name + ' ' + _last_name AS display_name FROM org_users). |
String |
About the system.org_users Table
The system.org_users table is a per-org, read-only projection of org membership. Each row represents one user who belongs to the calling org. A user who belongs to multiple orgs has one row in each org'ssystem.org_users; the _user_uid is the same across all of them.
Read-only. INSERT, UPDATE, and DELETE against this table are rejected. To add or remove users from an org, use the existing user-management HTTP API. The table reflects current membership: a row appears when a user is added to the org, and disappears when they are removed or delete their GroveStreams account.
Permissions. Querying
system.org_users requires the MANAGE_USERS capability — the same gate as the existing list-users HTTP endpoint. Org owners always pass. Non-admin members querying this table will get an access-denied error. If your application needs the user list available to non-admin sessions (e.g., a driver picker for non-admin dispatchers), have an admin-side process snapshot _user_uid, _first_name, _last_name into a sibling template that those sessions can read directly.
Aliases. The same table can be referenced as
system.org_users, orgusers, or org_users.
Sibling-template pattern for app metadata. Application-level state about users (license_class, home_depot, app-specific status, etc.) does not belong on
system.org_users. The pattern is to define your own template that holds the metadata and FK-references _user_uid:
CREATE TABLE drivers (
driver_uid PRIMARY KEY,
user_uid FK -> org_users._user_uid,
app_status, -- your state machine, your problem
license_class,
home_depot,
cdl_expiry
);
-- Active drivers, joined to current name
SELECT d.driver_uid,
ou._first_name + ' ' + ou._last_name AS driver_name,
d.license_class
FROM drivers d
JOIN org_users ou ON ou._user_uid = d.user_uid
WHERE d.app_status = 'active';
Removed users. When a user leaves the org or deletes their GroveStreams account, their row in
system.org_users disappears immediately. FK references from your application templates (e.g., loads.driver_uid) are not enforced and will dangle. Use LEFT JOIN if your queries need to tolerate this, or denormalize the display name at write time if you need historical display to keep resolving.
