Tables

Overview

There is only one table today and that is the STREAM table.

STREAM Table
cname	Component name.	String
cuid	Component UID.	String
cid	Component ID.	String
ccreatedDate	Component creation date.	Long
ctemplateUid	Component Template UID.	String
folderPath	Component repository folder path. Example: /Components/West.	String
name	Stream name.	String
uid	Stream UID. Indexed.	String
id	Stream ID. Indexed.	String
description	Stream description.	String
timeZoneId	The Time Zone Id. List of Time Zones	String
unitUid	Unit UID.	String
streamType	Stream type: intvl_stream, rdm_stream, point_stream.	String
derivationType	Stream derivation type: NONE, FROM_EXPRESSION, FROM_RSS, FROM_AGGREGATION.	String
baseCycleUid	Stream base cycle UID.	String
rollupCalendarUid	Stream rollup calendar UID.	String
timeFilterUid	Stream time filter UID.	String
defaultRollupMethod	Stream default rollup method.	String
sampleValueType	Stream sample value type.	String
startDate	Stream's earliest sample datetime as epoch milliseconds. The first interval's start datetime for intervals streams.	Long
endDate	Stream's latest sample datetime as epoch milliseconds.. The last interval's end datetime for intervals streams.	Long
completedDate	Stream completed date as epoch milliseconds. This usually matches a stream's endDate unless it is a derived stream and it has dependents with cycles smaller than its base cycle. The completed date tracks the datetime the stream was derived up to.	Long
lastUpdated	The datetime any stream sample was added, updated, or deleted.	Long
lastValue	Stream's latest sample value	Varies
lastValueType	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
timeSd	Stream interval sample start datetime as epoch milliseconds.	Long
timeEd	Stream interval sample end datetime as epoch milliseconds.	Long

About the STREAM Table

GroveStreams stores production stream data in three columnar tables: Component, Stream, and Sample tables

Querying for data in three tables can be difficult for SQL novices as it would involve a lot of joins. We have created one table to simplify queries called the STREAM table. It has all three table's information contained within it.

The STREAM table is created just for GS SQL queries and is automatically updated as information changes in the production tables. The sample and sample time columns are not actually stored in the STREAM table but are the actual production sample tables. The GS SQL engine will automatically modify query statements injecting an inner join with the sample table so that you don't have to.

This design has many benefits, but has a couple of quirks:
1. Error messages may refer to the sample table
2. The data in the stream table is not replicated for each sample, but only for each stream. For example, a query for all individual component names, such as "SELECT cname FROM Stream", will return the same component name for each of its streams. It will not return a component name for each sample belonging to the component's streams.

Tip: Add DISTINCT to eliminate duplicate component names: "SELECT DISTINCT cname FROM Stream"