sql-usage

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

• Stateless test default: use MatView without INTO, then verify with table(mv)
• Create target stream FIRST only when you need an extra sink stream
• Use explicit INTO target only when a target stream is required by the scenario
• Configure checkpointing: SETTINGS checkpoint_interval=30
• High-cardinality: SETTINGS default_hash_table='hybrid', max_hot_keys=10000
• Schema evolution (with target stream): ALTER STREAM target + ALTER VIEW ... MODIFY QUERY
• Cleanup order: DROP VIEW → (if created) DROP STREAM target → DROP STREAM source

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

• Stream type details and examples
• All EMIT policies with formal syntax
• JOIN patterns with full examples
• Production materialized view configuration
• External streams (Kafka, Pulsar)
• User-defined functions (UDF/UDAF)