| name | sql-usage |
| description | Timeplus streaming SQL covering stream types, EMIT policies, window functions, JOINs, materialized views, external streams, and UDFs. Make sure to use this skill for any SQL-related question including writing queries, debugging SQL errors, understanding streaming behavior, or designing stream processing pipelines, even if the user doesn't explicitly mention streaming SQL. |
Streaming SQL
Source of truth: https://docs.timeplus.com | Raw markdown: https://github.com/timeplus-io/docs/tree/main/docs
Naming rules
| Element | Style | Example |
|---|
| Keywords | UPPERCASE | SELECT, CREATE STREAM, EMIT, JOIN |
| Functions | lowercase | count(), tumble(), date_diff_within() |
| Data types | lowercase | int64, float64, string, datetime64 |
| Identifiers | lowercase_with_underscores | event_time, user_id |
| Reserved fields | _tp_ prefix | _tp_time (event timestamp), _tp_delta (changelog) |
Stream type decision table
| Need | Type | Syntax |
|---|
| Immutable events, time-series, high throughput | append (default) | CREATE STREAM ... ORDER BY |
| Updates/upserts, point queries, KV (first choice) | mutable | CREATE MUTABLE STREAM ... PRIMARY KEY |
| Version history, ASOF JOINs | versioned_kv | CREATE STREAM ... PRIMARY KEY ... SETTINGS mode='versioned_kv' |
CDC semantics, track deletes via _tp_delta | changelog_kv | CREATE STREAM ... PRIMARY KEY ... SETTINGS mode='changelog_kv' |
| External source (Kafka, Pulsar, etc.) | external | CREATE EXTERNAL STREAM ... SETTINGS type='kafka' |
Full details → references/stream-types.md
Query modes
SELECT FROM stream → streaming (continuous, future events)SELECT FROM table(stream) → historical (batch scan, returns once)
Three trigger types:
| Query type | Trigger |
|---|
| Non-aggregation (tail/filter/transform) | When events arrive |
| Window aggregation | Window end + watermark |
| Global aggregation | Fixed interval (default 2s if EMIT PERIODIC omitted) |
Window functions quick reference
| Function | Signature | Use case |
|---|
tumble | tumble(stream, [time_col], interval, [tz]) | Fixed non-overlapping windows |
hop | hop(stream, [time_col], slide, size, [tz]) | Sliding/overlapping windows |
session | session(stream, [time_col], MAXSPAN x AND TIMEOUT y) | Inactivity-based windows |
time_col defaults to _tp_time if omitted- Intervals:
1s, 5m, 2h, 3d, 1w, 1M, 1q, 1y
window_start, window_end auto-generated (left-closed, right-open [))- Hop: slide and size must use same unit; slide > size is unsupported
- Window nesting: max 2 levels; window-over-global is unsupported
EMIT policy quick reference
| Context | Policy | Effect |
|---|
| Window | EMIT AFTER WINDOW CLOSE | Default for windowed agg |
| Window | EMIT AFTER WINDOW CLOSE WITH DELAY 2s | Allow late events |
| Window | EMIT AFTER WINDOW CLOSE WITH DELAY 1s AND TIMEOUT 3s | Late events + force-close |
| Window | EMIT ON UPDATE | Emit when agg value changes per key |
| Window | EMIT ON UPDATE WITH BATCH 2s | Batched update detection |
| Global | EMIT PERIODIC 5s | Default (2s), batch periodic output |
| Global | EMIT PERIODIC 5s REPEAT | Emit even without new events |
| Global | EMIT ON UPDATE | Immediate on every change |
| Global | EMIT CHANGELOG | With _tp_delta (+1/-1) |
| Global | EMIT PER EVENT | Per-event (debug only, no parallelism) |
| Global | EMIT AFTER KEY EXPIRE ... WITH MAXSPAN x AND TIMEOUT y | Tracing/span aggregation |
Full formal syntax → references/emit-policies.md
JOIN quick reference
| Pattern | Syntax key | Use case |
|---|
| Static enrichment | stream JOIN table(lookup) | Enrich with historical data |
| Dynamic enrichment | append JOIN versioned_kv USING(k) | Latest version auto-picked |
| Bidirectional | mutable JOIN mutable | Both sides updatable |
| Range (time-bounded) | stream JOIN stream ... AND date_diff_within(2m) | Bounded stream-to-stream |
| ASOF | append ASOF JOIN versioned_kv ON ... AND t1 >= t2 | Closest version match |
| LATEST | append LATEST JOIN versioned_kv ON ... | Latest value only |
| Direct lookup | stream JOIN mutable ... SETTINGS join_algorithm='direct' | PK/index lookup, no full load |
| Dictionary | stream JOIN dict ... SETTINGS join_algorithm='direct' | External source lookup |
Supported: INNER, LEFT, FULL. Unsupported: RIGHT, CROSS.
Strictness: ALL (default), ASOF, LATEST.
Full examples → references/join-patterns.md
Materialized view checklist
Full config → references/mv-production.md
External stream (Kafka) quick reference
CREATE EXTERNAL STREAM events(raw string)
SETTINGS type='kafka', brokers='host:9092', topic='events';
Key settings: data_format, security_protocol, sasl_mechanism, kafka_schema_registry_url
Virtual columns: _tp_message_key, _tp_message_headers, _tp_sn (offset), _tp_shard (partition)
Query options: SETTINGS shards='0,2', seek_to='earliest'
Full details → references/external-streams.md
UDF quick reference
CREATE FUNCTION udf_name(param type) RETURNS type LANGUAGE JAVASCRIPT AS $$ ... $$;
Scalar: receives array of values (batched), returns array.
UDAF: implement initialize, process, finalize, serialize, deserialize, merge.
Full details → references/udf.md
References
