GS SQL DDL - LLM Agent Reference
This is a compact DDL reference for AI agents. For full documentation see: gsqlddl.html, gsqlgrammar.html.
For DML (INSERT, UPDATE, DELETE), SELECT queries, temporal parameters, functions, and operators see: gsql_llm_reference.html.
Organization-Level DDL Permissions
Configured via Organization Information window (Query tab) or REST API (PUT /api/org/{orgUid}). When disabled, the operation returns an error.
Component & Template Permissions:
gsqlAllowCreateTable (default: true) -- CREATE TABLE
gsqlAllowAlterTable (default: true) -- ALTER TABLE
gsqlAllowDropTable (default: false) -- DROP TABLE
gsqlAllowDropTableCascade (default: false) -- DROP TABLE CASCADE (deletes all linked components)
Shared Conventions
- All DDL is case insensitive
- Resource names can be identifiers or quoted strings
- WITH options use name = value pairs, parentheses optional: WITH (NAME = 'x', DESCRIPTION = 'y') or WITH NAME = 'x', DESCRIPTION = 'y'
- Common WITH options across most resource types: NAME, DESCRIPTION, FOLDER_PATH, TIMEZONE_ID
- Catalog ↔ PG schema mapping (over the ODBC/JDBC adapter): GS catalogs surface as PG schemas. The default catalog (and any template not assigned to a catalog) maps to PG schema public. Other catalog names pass through unchanged. Inbound queries against public.<table> resolve to default-catalog templates.
1. CREATE TABLE
-- Column-list form: define a component template
CREATE TABLE [IF NOT EXISTS] table_name (
column_id [value_type] [stream_type] [column_options...] [, ...]
) [WITH (options)]
-- CTAS form: build a result-cached object from a SELECT (PG / dbt "table" materialization).
-- Lowered to CREATE MATERIALIZED VIEW under the hood — the resulting object is queryable
-- by name, drop with DROP MATERIALIZED VIEW.
CREATE TABLE [IF NOT EXISTS] table_name AS [(] select_stmt [)] [WITH (options)]
The column-list form creates a new component template. The table name becomes the template ID.
Value types (optional, default DOUBLE): DOUBLE, LONG, STRING, BOOLEAN, BIGDECIMAL, FILE.
PostgreSQL/ANSI aliases also accepted: INT/INTEGER/INT2/INT4/INT8/SMALLINT/BIGINT (→ LONG), REAL/FLOAT4/FLOAT8 (→ DOUBLE), TEXT/VARCHAR (→ STRING), NUMERIC/DECIMAL (→ BIGDECIMAL), BOOL (→ BOOLEAN), TIMESTAMP/TIMESTAMPTZ (→ LONG, epoch millis).
Stream types (optional, default from org stream defaults): REGULAR (regular datetime stream, also accepts RDM), POINT (single value), INTERVAL (start/end datetime, requires BASE_CYCLE)
Column options (all optional, fall back to org stream defaults):
NAME 'display_name' -- stream display name
DESCRIPTION 'text' -- stream description
BASE_CYCLE 'cycle_id' -- base cycle (required for INTERVAL). Also: CYCLE
ROLLUP_CALENDAR 'calendar_id' -- rollup calendar
ROLLUP_METHOD 'method' -- rollup aggregation (AVG, SUM, MIN, MAX, LAST, FIRST, NONE)
ROLLUP method_name -- alternative syntax (no quotes, no underscore)
DELETE_PROFILE 'profile_id' -- automatic data pruning
REFERENCES template_id -- foreign key to another template
PRIMARY_KEY true|false -- mark stream as the template's primary key; auto-indexed in silv for sub-second WHERE lookups; drives default name-expression generation
PRIMARY KEY -- PostgreSQL/ANSI two-token form, equivalent to PRIMARY_KEY true
INDEXED true|false -- maintain last-value in the silv compound secondary index for sub-second WHERE on non-PK columns; small write amp per value change, so index only filter columns
MAX_STRING_LENGTH n -- max chars for STRING streams (default 255, max 2200000)
Derived streams (auto-computed columns):
DERIVED AS 'expression' DEPENDS ON (dep1, dep2, ...)
Dependency types:
FROM: sibling dependency -- var FROM column_id (same table)
VIA: FK chain dependency -- var VIA fk_col.target_template.target_col (supports multi-hop: fk1.tmpl1.fk2.tmpl2.col)
Per-dependent options (after FROM or VIA):
CYCLE 'cycle_id' -- aggregate at this cycle before passing to expression
OFFSET n -- e.g. -1 = previous interval, -2 = two intervals back
FUNCTION 'fn' -- aggregation function (AVG, SUM, MIN, MAX, FIRST, LAST, etc.)
FILL_FORWARD -- carry forward the last known value when gaps exist
Expression-level options (after the DEPENDS ON clause):
ALLOW_NULLS -- allow null values in expression evaluation
NULL_AS_ZERO -- treat nulls as zero in expression evaluation
NO_AUTO_RECALC -- do not auto-recalculate when dependencies change
Derived streams cannot use POINT stream type.
Aggregation streams (stream group aggregation):
AGGREGATION agg_function STREAM GROUP 'group_id' [aggregation_options...]
Aggregation options:
CALC_RANGE n unit -- calculation range (unit: MINUTE, HOUR, DAY, WEEK, MONTH, YEAR)
CALC_RANGE_TO n unit -- calculation range end
PERCENT_GAP n -- maximum gap percentage allowed
SG_CYCLE 'cycle_id' -- stream group cycle override
SG_FUNCTION 'fn' -- stream group aggregation function override
NO_AUTO_RECALC -- do not auto-recalculate
WITH options (all optional):
CNAME = 'name' -- template display name (defaults to table name)
DESCRIPTION = 'text' -- template description
TIMEZONE_ID = 'timezone' -- template timezone (falls back to user timezone, then UTC)
FOLDER_PATH = 'path' -- template folder (defaults to '/ComponentTemplates')
NAME_EXPRESSION = '{stream_id} ...' -- dynamic component name from stream values, e.g. '{first_name} {last_name}'
Examples:
CREATE TABLE sensor (temperature DOUBLE, humidity DOUBLE);
CREATE TABLE meter (
kwh DOUBLE INTERVAL BASE_CYCLE 'hour' ROLLUP_METHOD 'SUM',
voltage DOUBLE REGULAR,
status STRING POINT
) WITH (CNAME = 'Energy Meter', TIMEZONE_ID = 'America/New_York');
CREATE TABLE IF NOT EXISTS meter (kwh DOUBLE, customerUid STRING REFERENCES customer);
CREATE TABLE employee (
first_name STRING PRIMARY_KEY true,
last_name STRING PRIMARY_KEY true,
department STRING,
salary DOUBLE
) WITH (CNAME = 'Employee', NAME_EXPRESSION = '{first_name} {last_name}');
CREATE TABLE device (
reading DOUBLE INTERVAL NAME 'Sensor Reading' BASE_CYCLE 'hour'
ROLLUP_CALENDAR 'standard_rc' ROLLUP_METHOD 'AVG' DELETE_PROFILE 'retain_1yr'
);
-- Derived stream with sibling dependencies
CREATE TABLE sensor (
temperature DOUBLE,
humidity DOUBLE,
heat_index DOUBLE DERIVED AS '-42.379 + 2.049*t + 10.143*h'
DEPENDS ON (t FROM temperature, h FROM humidity)
);
-- Derived stream with FK chain (VIA) dependency
CREATE TABLE meter (
kwh DOUBLE,
customerUid STRING REFERENCES customer,
billed DOUBLE DERIVED AS 'kwh * rate'
DEPENDS ON (kwh FROM kwh, rate VIA customerUid.customer.energy_rate)
);
-- Derived with expression options
CREATE TABLE sensor (
a DOUBLE,
b DOUBLE DERIVED AS 'x' DEPENDS ON (x FROM a) ALLOW_NULLS NULL_AS_ZERO NO_AUTO_RECALC
);
-- Derived with OFFSET (previous interval)
CREATE TABLE meter (
kwh DOUBLE,
kwh_delta DOUBLE DERIVED AS 'current - prev'
DEPENDS ON (current FROM kwh, prev FROM kwh OFFSET -1)
);
-- Derived with CYCLE and FUNCTION (daily sum)
CREATE TABLE meter (
kwh DOUBLE,
kwh_daily DOUBLE DERIVED AS 'ds' DEPENDS ON (ds FROM kwh CYCLE 'day' FUNCTION 'SUM')
);
-- Derived with CYCLE, OFFSET, and FUNCTION (yesterday's total)
CREATE TABLE meter (
kwh DOUBLE,
yesterday_total DOUBLE DERIVED AS 'yt'
DEPENDS ON (yt FROM kwh CYCLE 'day' OFFSET -1 FUNCTION 'SUM')
);
-- PostgreSQL/ANSI shorthand: bare PG types and inline `PRIMARY KEY`
-- (equivalent to the GS-native `STRING PRIMARY_KEY true`):
CREATE TABLE employee (
first_name TEXT PRIMARY KEY,
last_name TEXT PRIMARY KEY,
department VARCHAR,
salary NUMERIC,
hire_date TIMESTAMP,
age INT
);
-- Derived with FILL_FORWARD
CREATE TABLE meter (
kwh DOUBLE,
rate STRING REFERENCES rate_schedule,
cost DOUBLE DERIVED AS 'kwh * r'
DEPENDS ON (kwh FROM kwh, r VIA rate.rate_schedule.price FILL_FORWARD)
);
2. ALTER TABLE
ALTER TABLE table_name action [, action ...] [RECONCILE NOW]
Actions:
ADD [COLUMN] column_def -- add a stream definition (same options as CREATE TABLE)
DROP [COLUMN] column_id -- remove a stream definition
MODIFY [COLUMN] column_def -- change column options
SET option = value [, ...] -- change template properties
RENAME COLUMN old_id TO new_id -- rename a stream id within the template
RENAME TO new_name -- rename the table or view (PG-aliased: works on views too)
DDL changes affect templates only. Use RECONCILE NOW to push changes to all linked components.
SET options: CNAME, DESCRIPTION, TIMEZONE_ID, FOLDER_PATH, REFERENCE_DATE
RENAME TO is mutually exclusive with other actions in the same statement (PG semantics). Following PostgreSQL, ALTER TABLE … RENAME TO also accepts a view as the target — equivalent to ALTER VIEW … RENAME TO.
Examples:
ALTER TABLE sensor ADD COLUMN pressure DOUBLE;
ALTER TABLE meter ADD COLUMN demand DOUBLE INTERVAL BASE_CYCLE 'hour' ROLLUP_METHOD 'MAX';
ALTER TABLE sensor DROP COLUMN humidity;
ALTER TABLE meter MODIFY COLUMN kwh ROLLUP_METHOD 'AVG' DELETE_PROFILE 'retain_2yr';
ALTER TABLE sensor SET CNAME = 'Environmental Sensor', DESCRIPTION = 'Updated description';
-- Add a derived column
ALTER TABLE sensor ADD COLUMN heat_index DOUBLE DERIVED AS '-42.379 + 2.049*t + 10.143*h'
DEPENDS ON (t FROM temperature, h FROM humidity);
-- Push template changes to all linked components
ALTER TABLE sensor RECONCILE NOW;
-- Multiple actions in one statement
ALTER TABLE meter ADD COLUMN demand DOUBLE, DROP COLUMN old_field, SET CNAME = 'Updated Meter' RECONCILE NOW;
-- Rename a table
ALTER TABLE sensor RENAME TO env_sensor;
-- Rename a view via the PG-aliased form (equivalent to ALTER VIEW)
ALTER TABLE active_sensors RENAME TO live_sensors;
-- Rename a column (stream id)
ALTER TABLE sensor RENAME COLUMN temp TO temperature;
3. DROP TABLE
DROP TABLE [IF EXISTS] table_name [CASCADE]
Deletes a component template. IF EXISTS suppresses the error if the template does not exist.
CASCADE also deletes all linked components (and their streams/samples). Without CASCADE, linked components are preserved (become unlinked).
Examples:
DROP TABLE sensor;
DROP TABLE IF EXISTS sensor;
DROP TABLE IF EXISTS sensor CASCADE;
3a. ALTER ORG
ALTER ORG SET propertyName = TRUE | FALSE
Toggles org-level GS SQL behavior settings. Requires org owner or admin privileges (per-tool gsqlAllow flags do not apply). Unknown property names return an error.
Supported properties:
- gsqlMergeSampleCols -- when TRUE, multiple _sample() calls in one SELECT that share range/cycle/timeFilter/gapFill/offsets merge into a single row per time-bucket. When FALSE, each _sample() emits its own tuple set with NULLs in the other sample columns. New orgs default to TRUE (standard SQL behavior); orgs created before this feature default to FALSE to preserve their existing query semantics.
Examples:
ALTER ORG SET gsqlMergeSampleCols = TRUE;
ALTER ORG SET gsqlMergeSampleCols = FALSE;
4. CREATE CYCLE
CREATE CYCLE [IF NOT EXISTS] cycle_id INTERVAL number unit [WITH (options)]
Creates a new cycle definition. Cycles define time intervals for rollups, interval streams, and aggregation.
Interval units: MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR
Examples:
CREATE CYCLE fifteen_min INTERVAL 15 MINUTE;
CREATE CYCLE IF NOT EXISTS hourly INTERVAL 1 HOUR;
CREATE CYCLE daily INTERVAL 1 DAY WITH (NAME = 'Daily Cycle', DESCRIPTION = 'One day intervals');
CREATE CYCLE monthly INTERVAL 1 MONTH;
CREATE CYCLE yearly INTERVAL 1 YEAR WITH (FOLDER_PATH = '/Tools/Cycles');
5. ALTER CYCLE
ALTER CYCLE cycle_id [INTERVAL number unit] [SET option = value [, ...]]
Modifies an existing cycle. Can change the interval and/or properties.
Examples:
ALTER CYCLE fifteen_min INTERVAL 30 MINUTE;
ALTER CYCLE daily SET NAME = 'Daily', DESCRIPTION = 'Updated daily cycle';
ALTER CYCLE hourly INTERVAL 2 HOUR SET NAME = 'Two Hour Cycle';
6. DROP CYCLE
DROP CYCLE [IF EXISTS] cycle_id
Examples:
DROP CYCLE fifteen_min;
DROP CYCLE IF EXISTS old_cycle;
7. CREATE ROLLUP CALENDAR
CREATE ROLLUP CALENDAR [IF NOT EXISTS] calendar_id
CYCLES ('cycle1', 'cycle2', ...)
[WITH (options)]
Creates a rollup calendar that defines a hierarchy of cycles for automatic aggregation.
Examples:
CREATE ROLLUP CALENDAR standard_rc CYCLES ('hour', 'day', 'month', 'year');
CREATE ROLLUP CALENDAR IF NOT EXISTS energy_rc CYCLES ('fifteen_min', 'hour', 'day', 'month', 'year')
WITH (NAME = 'Energy Rollup Calendar');
8. ALTER ROLLUP CALENDAR
ALTER ROLLUP CALENDAR calendar_id [CYCLES ('cycle1', ...)] [SET option = value [, ...]]
Examples:
ALTER ROLLUP CALENDAR standard_rc CYCLES ('hour', 'day', 'week', 'month', 'year');
ALTER ROLLUP CALENDAR standard_rc SET NAME = 'Standard Calendar';
9. DROP ROLLUP CALENDAR
DROP ROLLUP CALENDAR [IF EXISTS] calendar_id
Examples:
DROP ROLLUP CALENDAR standard_rc;
DROP ROLLUP CALENDAR IF EXISTS old_calendar;
10. CREATE STREAM GROUP
CREATE STREAM GROUP [IF NOT EXISTS] group_id [AS 'description'] [WITH (options)]
Creates a stream group for organizing streams for aggregation operations.
Examples:
CREATE STREAM GROUP all_meters AS 'All electricity meters';
CREATE STREAM GROUP IF NOT EXISTS building_temps AS 'Building temperature sensors'
WITH (NAME = 'Building Temperatures', FOLDER_PATH = '/Tools/StreamGroups');
11. ALTER STREAM GROUP
ALTER STREAM GROUP group_id [AS 'description'] [SET option = value [, ...]]
Examples:
ALTER STREAM GROUP all_meters AS 'All electricity meters - updated';
ALTER STREAM GROUP all_meters SET NAME = 'All Meters';
12. DROP STREAM GROUP
DROP STREAM GROUP [IF EXISTS] group_id
Examples:
DROP STREAM GROUP all_meters;
DROP STREAM GROUP IF EXISTS old_group;
13. CREATE RUNNABLE
CREATE RUNNABLE [IF NOT EXISTS] runnable_id
[SCHEDULE schedule_id]
[ITEMS ('item_ref', ...)]
[WITH (options)]
Creates a runnable job that can be scheduled or manually triggered. Items define what the runnable processes.
Item reference formats:
STREAM GROUP 'group_id' [CYCLE 'cycle_id'] [STATS] -- process a stream group
STREAM 'comp_id'.'stream_id' [CYCLE 'cycle_id'] [STATS] -- process a specific stream
STATS keyword triggers statistics recalculation for the referenced items.
Examples:
CREATE RUNNABLE daily_rollup
SCHEDULE daily_schedule
ITEMS (STREAM GROUP 'all_meters' CYCLE 'day');
CREATE RUNNABLE IF NOT EXISTS recalc_stats
ITEMS (STREAM GROUP 'all_meters' STATS, STREAM 'meter1'.'kwh' CYCLE 'hour' STATS)
WITH (NAME = 'Recalculate Statistics');
CREATE RUNNABLE manual_job WITH (NAME = 'Manual Job', DESCRIPTION = 'Run on demand');
14. ALTER RUNNABLE
ALTER RUNNABLE runnable_id
[SCHEDULE schedule_id | NO SCHEDULE]
[ITEMS ('item_ref', ...)]
[SET option = value [, ...]]
Use NO SCHEDULE to remove an existing schedule.
Examples:
ALTER RUNNABLE daily_rollup SCHEDULE new_schedule;
ALTER RUNNABLE daily_rollup NO SCHEDULE;
ALTER RUNNABLE daily_rollup ITEMS (STREAM GROUP 'all_meters' CYCLE 'day', STREAM GROUP 'all_meters' CYCLE 'month');
ALTER RUNNABLE daily_rollup SET NAME = 'Updated Rollup Job';
15. DROP RUNNABLE
DROP RUNNABLE [IF EXISTS] runnable_id
16. RUN RUNNABLE
RUN RUNNABLE runnable_id
Immediately executes a runnable job.
Examples:
RUN RUNNABLE daily_rollup;
RUN RUNNABLE recalc_stats;
17. CREATE AGENT
CREATE AGENT [IF NOT EXISTS] agent_id
[SCHEDULE cycle_id]
[PROMPT 'prompt text']
[WITH (options)]
Options: NAME, FOLDER_PATH, PROFILE_UID (SystemLLMProfile UID, defaults to system default), MAX_ITERATIONS (default 50), MAX_TIMEOUT_MS (default 600000), ENABLED (default true), RESULT_COMPONENT_ID, RESULT_STREAM_ID.
AI Agents are schedulable LLM-powered jobs. They run prompts with full tool access (GS SQL, component CRUD, docs search, etc.). Results are stored in lastResultSummary and optionally written to an RDM stream (RESULT_COMPONENT_ID + RESULT_STREAM_ID). Scheduling is paused for the current release — agents can be defined and run manually via RUN AGENT, but cron dispatch returns when the Process Queue redesign ships. When scheduling is enabled, max 2 scheduled tool agents execute concurrently per org; this limit gates only the cron path (not interactive chat, MCP ask_grovestreams, or manual runs).
Examples:
CREATE AGENT sleep_cycle
SCHEDULE 'day'
PROMPT 'You are a memory maintenance agent...'
WITH (NAME = 'Sleep Cycle Agent', MAX_ITERATIONS = 30, MAX_TIMEOUT_MS = 300000);
CREATE AGENT IF NOT EXISTS daily_report
PROMPT 'Summarize active initiatives and recent observations'
WITH (NAME = 'Daily Report', RESULT_COMPONENT_ID = 'reports', RESULT_STREAM_ID = 'daily_summary');
18. ALTER AGENT
ALTER AGENT agent_id
[SCHEDULE cycle_id | NO SCHEDULE]
[PROMPT 'new prompt']
[SET (options)]
Examples:
ALTER AGENT sleep_cycle PROMPT 'Updated maintenance instructions...';
ALTER AGENT sleep_cycle SCHEDULE 'hour';
ALTER AGENT sleep_cycle NO SCHEDULE;
ALTER AGENT sleep_cycle SET (NAME = 'Updated Agent', MAX_ITERATIONS = 50, ENABLED = false);
19. DROP AGENT
DROP AGENT [IF EXISTS] agent_id
20. RUN AGENT
RUN AGENT agent_id
Triggers immediate execution. Same as right-clicking "Run Agent Now" in the UI.
Examples:
RUN AGENT sleep_cycle;
RUN AGENT daily_report;
21. CREATE ENTITY DIAGRAM
CREATE ENTITY DIAGRAM [IF NOT EXISTS] diagram_id
[WITH (options)]
[PLACE NODE 'template_id' POSITION (x, y) [SIZE (width, height)]]
...
[PLACE VIEW 'view_id' POSITION (x, y) [SIZE (width, height)]]
...
[RELATIONSHIP 'template.column' REFERENCES 'target_template' [CARDINALITY 'card'] [WAYPOINTS ((x,y), ...)]]
...
Creates an entity diagram for visualizing template relationships. Layouts are shared (not per-user).
PLACE NODE: positions a template node on the diagram canvas.
PLACE VIEW: positions a saved View on the diagram canvas. Views are first-class queryable objects (usable in any FROM clause); placing them on a diagram makes the relationship between a view and the templates it queries legible. View nodes render with a dashed border and a "V" badge.
RELATIONSHIP: draws a relationship line between templates based on a FK (REFERENCES) column.
CARDINALITY: label for the relationship (e.g. '1:N', 'N:1', '1:1').
WAYPOINTS: intermediate points for routing the relationship line.
WITH options include: NAME, DESCRIPTION, FOLDER_PATH, CANVAS_WIDTH, CANVAS_HEIGHT
Examples:
CREATE ENTITY DIAGRAM energy_model
WITH (NAME = 'Energy Model', CANVAS_WIDTH = 1200, CANVAS_HEIGHT = 800)
PLACE NODE 'meter' POSITION (100, 100) SIZE (200, 150)
PLACE NODE 'customer' POSITION (500, 100) SIZE (200, 150)
PLACE VIEW 'active_meters' POSITION (300, 350)
RELATIONSHIP 'meter.customerUid' REFERENCES 'customer' CARDINALITY '1:N';
CREATE ENTITY DIAGRAM IF NOT EXISTS simple_diagram
PLACE NODE 'sensor' POSITION (100, 100)
PLACE NODE 'building' POSITION (400, 100)
RELATIONSHIP 'sensor.buildingUid' REFERENCES 'building'
WAYPOINTS ((250, 100), (350, 100));
22. ALTER ENTITY DIAGRAM
ALTER ENTITY DIAGRAM diagram_id
[SET option = value [, ...]]
[PLACE NODE ...]
[PLACE VIEW ...]
[RELATIONSHIP ...]
[REMOVE NODE 'template_id']
[REMOVE VIEW 'view_id']
Can add/reposition nodes (templates and views), add/update relationships, and remove nodes or views.
REMOVE VIEW only removes the view from the diagram — it does not delete the view itself.
Examples:
ALTER ENTITY DIAGRAM energy_model
PLACE NODE 'transformer' POSITION (300, 300) SIZE (200, 150)
PLACE VIEW 'high_load_meters' POSITION (600, 300)
RELATIONSHIP 'meter.transformerUid' REFERENCES 'transformer' CARDINALITY 'N:1';
ALTER ENTITY DIAGRAM energy_model REMOVE NODE 'old_template';
ALTER ENTITY DIAGRAM energy_model REMOVE VIEW 'old_view';
ALTER ENTITY DIAGRAM energy_model SET CANVAS_WIDTH = 1600, CANVAS_HEIGHT = 1000;
23. DROP ENTITY DIAGRAM
DROP ENTITY DIAGRAM [IF EXISTS] diagram_id
Examples:
DROP ENTITY DIAGRAM energy_model;
DROP ENTITY DIAGRAM IF EXISTS old_diagram;
24. CREATE CONNECTION
CREATE CONNECTION [IF NOT EXISTS] connection_id
TYPE 'type'
HOST 'hostname'
[PORT port_number]
DATABASE 'database_name'
USERNAME 'user'
PASSWORD 'password'
[WITH (options)]
Creates an external database connection for use with materialized views and views.
Examples:
CREATE CONNECTION pg_warehouse
TYPE 'postgresql'
HOST 'db.example.com'
PORT 5432
DATABASE 'analytics'
USERNAME 'reader'
PASSWORD 'secret123';
CREATE CONNECTION IF NOT EXISTS mysql_source
TYPE 'mysql'
HOST 'mysql.internal'
DATABASE 'inventory'
USERNAME 'app_user'
PASSWORD 'pass'
WITH (NAME = 'MySQL Inventory DB');
25. ALTER CONNECTION
ALTER CONNECTION connection_id SET option = value [, ...]
Examples:
ALTER CONNECTION pg_warehouse SET PASSWORD = 'new_secret', HOST = 'new-db.example.com';
ALTER CONNECTION pg_warehouse SET NAME = 'Production Warehouse';
26. DROP CONNECTION
DROP CONNECTION [IF EXISTS] connection_id
Examples:
DROP CONNECTION pg_warehouse;
DROP CONNECTION IF EXISTS old_connection;
27. CREATE MATERIALIZED VIEW
CREATE [OR REPLACE] MATERIALIZED VIEW [IF NOT EXISTS] view_name
[CONNECTION connection_id]
AS (select_stmt | (select_stmt) | 'sql_string')
[WITH (options)]
The select_stmt may be wrapped in parentheses for PG / dbt compatibility.
OR REPLACE drops any existing view of the same name (and its cached results) and recreates from the new definition. Use IF NOT EXISTS to skip silently when one already exists.
Creates a materialized view that caches query results. The query can be a GS SQL SELECT or an external SQL string (when using CONNECTION). Results are stored and must be refreshed explicitly.
Examples:
-- Materialized view from GS SQL
CREATE MATERIALIZED VIEW daily_summary
AS SELECT _component_name, kwh(cycleId='day', stat='sum', range(currentCycle='month')) FROM meter;
-- Materialized view from external database
CREATE MATERIALIZED VIEW external_rates
CONNECTION pg_warehouse
AS 'SELECT rate_id, rate_value, effective_date FROM rate_schedules WHERE active = true'
WITH (NAME = 'External Rate Data');
CREATE MATERIALIZED VIEW IF NOT EXISTS customer_list
CONNECTION pg_warehouse
AS 'SELECT id, name, region FROM customers';
28. ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW view_name action [, action ...]
Actions:
SET option = value [, ...]
AS (select_stmt | 'sql_string')
CONNECTION connection_id
RENAME TO new_view_name
RENAME TO is mutually exclusive with other actions in the same statement (PG semantics).
Examples:
ALTER MATERIALIZED VIEW daily_summary AS SELECT _component_name, kwh(cycleId='day', stat='avg', range(currentCycle='month')) FROM meter;
ALTER MATERIALIZED VIEW external_rates SET NAME = 'Updated Rates';
ALTER MATERIALIZED VIEW external_rates CONNECTION new_warehouse;
ALTER MATERIALIZED VIEW daily_summary RENAME TO daily_kwh;
29. DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW [IF EXISTS] view_name [CASCADE]
CASCADE is accepted for PG / dbt compatibility but is a no-op (GS has no cascading view dependencies to drop).
30. REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW view_name [WITH (options)]
Re-executes the view's query and updates the cached results.
Examples:
REFRESH MATERIALIZED VIEW daily_summary;
REFRESH MATERIALIZED VIEW external_rates;
31. CREATE VIEW
CREATE [OR REPLACE] VIEW [IF NOT EXISTS] view_name
[CONNECTION connection_id]
AS (select_stmt | (select_stmt) | 'sql_string')
[WITH (options)]
The select_stmt may be wrapped in parentheses for PG / dbt compatibility.
Creates a live (non-materialized) view. The query is executed on each access. OR REPLACE overwrites an existing view with the same name.
Examples:
CREATE VIEW active_sensors AS SELECT _component_name, temperature FROM sensor WHERE temperature IS NOT NULL;
CREATE OR REPLACE VIEW meter_summary
AS SELECT _component_name, kwh, voltage FROM meter WHERE kwh > 0;
CREATE VIEW external_lookup
CONNECTION pg_warehouse
AS 'SELECT product_id, product_name FROM products'
WITH (NAME = 'Product Lookup');
32. ALTER VIEW
ALTER VIEW view_name action [, action ...]
Actions:
SET option = value [, ...]
AS (select_stmt | 'sql_string')
CONNECTION connection_id
RENAME TO new_view_name
RENAME TO is mutually exclusive with other actions in the same statement (PG semantics).
Examples:
ALTER VIEW active_sensors AS SELECT _component_name, temperature, humidity FROM sensor WHERE temperature IS NOT NULL;
ALTER VIEW active_sensors SET NAME = 'Active Environmental Sensors';
ALTER VIEW active_sensors RENAME TO live_sensors;
33. DROP VIEW
DROP VIEW [IF EXISTS] view_name [CASCADE]
CASCADE is accepted for PG / dbt compatibility but is a no-op (GS has no cascading view dependencies to drop).
Examples:
DROP VIEW active_sensors;
DROP VIEW IF EXISTS old_view;
DROP VIEW IF EXISTS old_view CASCADE;
34. CREATE DASHBOARD
CREATE DASHBOARD [IF NOT EXISTS] dashboard_id (
widget_type ['name'] [widget_clauses...],
...
) [WITH (options)]
Widget types: LINE_CHART, BAR_CHART, COL_CHART, STACKED_BAR_CHART, STACKED_COL_CHART, PIE_CHART, GAUGE, BIG_NUMBER, LEVEL, TABLE_VIEW, GRID_VIEW, PIVOT_VIEW, TABLESQL_VIEW, PIVOTSQL_VIEW, BB_MAP, TRACK_MAP, URL, HTML, SS_RAD, SS_RADBAR, SS_SINGLE, SS_ODOM, SS_WIND, PIVOT_BAR, PIVOT_COL, PIVOT_LINE, PIVOT_STACKED_BAR, PIVOT_STACKED_COL, LLM_VIEW, DASH_LINK, STREAM_FORM2, RSS_FEED, RANGE
Widget clauses:
STREAMS ('componentId/streamId' [CYCLE 'id' STATS 'fn,...'], ...) -- stream references
STREAM_GROUP 'group_id' -- reference a stream group
SQL 'gsql_query' -- GS SQL query for SQL-based widgets
HEIGHT n -- widget height in pixels
COLSPAN n -- number of columns to span
RANGE_TYPE HEARTBEAT | FIXED | CYCLE -- how the time range is determined
POLLING n -- polling interval in seconds
VIEW_LAST n -- number of data points to retrieve and display. Without CYCLE, retrieves n raw samples. With CYCLE, retrieves n rolled-up intervals. (DURATION is accepted as alias)
CYCLE 'cycle_id' -- optional rollup interval for VIEW_LAST (e.g., 'hour', 'day'), or cycle for CYCLE range type
STATS 'fn,...' -- statistics to display
EXTRA_DATA 'json' -- widget-specific JSON configuration
Stream references use 'componentId/streamId' format. Per-stream CYCLE and STATS are valid on chart, gauge, big number, level, and table widget types.
EXTRA_DATA accepts a JSON string for widget-specific config. For SQL widgets (TABLESQL_VIEW, PIVOTSQL_VIEW), use it to specify the query:
EXTRA_DATA '{"sqlType": "sql", "gsql": "SELECT _component_name, temperature FROM sensor"}'
EXTRA_DATA '{"sqlType": "query", "query": {"name": "my_saved_view"}}'
WITH options: name, folder_path, columns, min_columns, col_width, theme, bg_color, title, title_style, title_height, border_size, border_color, border_radius, align, gutter_x, gutter_y, padding_x, padding_y, hide_titles, hide_tools, headers_style
Examples:
CREATE DASHBOARD temp_monitor (
LINE_CHART 'Temperature' STREAMS ('Sensor_A/temperature', 'Sensor_B/temperature') HEIGHT 400 COLSPAN 2,
BIG_NUMBER 'Current' STREAMS ('Sensor_A/temperature') HEIGHT 150
) WITH (columns = 2, theme = 'white', title = 'Temperature Monitor');
CREATE DASHBOARD sql_report (
TABLESQL_VIEW 'Report' HEIGHT 400 SQL 'SELECT _component_name, temperature, humidity FROM sensor'
);
CREATE DASHBOARD sql_report2 (
TABLESQL_VIEW 'Report' HEIGHT 400 EXTRA_DATA '{"sqlType": "sql", "gsql": "SELECT _component_name, _last_value FROM sensor", "stripeRows": true}'
);
35. ALTER DASHBOARD
ALTER DASHBOARD dashboard_id action [, action ...]
Actions:
SET (option = value, ...)
ADD (widget_type ['name'] [widget_clauses...], ...)
Examples:
ALTER DASHBOARD temp_monitor SET (theme = 'black', columns = 4);
ALTER DASHBOARD temp_monitor ADD (GAUGE 'Pressure' STREAMS ('Pump_1/psi') HEIGHT 200);
36. DROP DASHBOARD
DROP DASHBOARD [IF EXISTS] dashboard_id
Examples:
DROP DASHBOARD temp_monitor;
DROP DASHBOARD IF EXISTS temp_monitor;
37. ANALYZE STATS
ANALYZE STATS
Forces a synchronous recompute of per-org table statistics (per-template / per-catalog stream-row counts) used by the query planner to choose scan strategies.
Stats are maintained automatically. ANALYZE STATS is rarely needed; use it after a mass schema change, when troubleshooting a planner choice, or in tests.
Org-scoped, no table argument, no options. Reads only.
Examples:
ANALYZE STATS;