## KSQL API — Overview

Scope: This page summarizes commonly used ksqlDB (KSQL) features as they relate to the Ksql.Linq DSL. For exact syntax and the full list of functions/types, consult the official ksqlDB docs; see also this wiki’s [API-Reference](API-Reference).

### DDL (create/replace/drop)
- `CREATE STREAM ...` / `CREATE TABLE ...`
- `CREATE TABLE AS SELECT ...` (CTAS) and `CREATE STREAM` / `CREATE TABLE`
- Idempotence: prefer `CREATE IF NOT EXISTS` when possible (note: `OR REPLACE` is not always applicable)
- Dropping: `DROP STREAM/TABLE ...` (for schema changes, a drop+create flow is often clearer)

### Queries (SELECT)
- `SELECT ... FROM ...` (point-in-time or streaming)
- `WHERE` filters, `JOIN` (inner/outer as supported), `GROUP BY` for aggregations
- `EMIT CHANGES` enables push queries (streaming results)

### Windowing
- Tumbling windows: `TUMBLING (SIZE n MINUTES|SECONDS|...)`
- Window bounds: `WINDOWSTART`, `WINDOWEND` (exposed via builders, e.g., `g.WindowStart()`)
- Aggregations: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, `TOPK` (where supported)
- Snapshot helpers: `EARLIEST_BY_OFFSET` / `LATEST_BY_OFFSET`
- Note: `HAVING` with tumbling windows can be limited depending on the expression; often a filter can be pushed into `WHERE` or applied post-aggregation

### Functions and types (high-level)
- Strings: `UPPER`, `LOWER`, `SUBSTRING`, `LEN`, `TRIM`, `REPLACE`, `INSTR`, `CONCAT`
- Numbers: `ABS`, `ROUND`, `FLOOR`, `CEIL`, `SQRT`, `POWER`, `SIGN`, `LOG`, `LOG10`, `EXP`
- Date/time: e.g., `DATEADD('minute', 5, ts)` (check docs for availability and behavior)
- Aggregations: `COUNT`, `COUNT_DISTINCT`, `SUM`, `AVG`, `MIN`, `MAX`, `TOPK`
- JSON: `JSON_EXTRACT_STRING`, `JSON_RECORDS`, `JSON_ARRAY_LENGTH`, `JSON_KEYS`
- Conditionals/casts: `CASE`, `COALESCE`, `IFNULL`, `CAST`
- Types: `INTEGER`, `BIGINT`, `BOOLEAN`, `DECIMAL`, `DOUBLE`, `VARCHAR`, `BYTES`, `TIMESTAMP`, `DATE`, `TIME`

### Push vs Pull
- Push (EMIT CHANGES): streaming read, typically consumed via `ForEachAsync`
- Pull (point-in-time): read materialized state via `TimeBucket.ReadAsync<T>` and related helpers

### Caveats and notes
- DECIMAL precision/scale is governed by the Avro schema; align POCO `[KsqlDecimal]` with registry policy
- Some helpers like `DATEDIFF`/`FORMAT` might not be available across all versions; prefer application-side formatting when necessary
- Aggregation cardinality: ensure `GROUP BY` keys are well-chosen; partitioning affects performance
- Post-aggregation filtering may require `HAVING` or an extra step depending on the expression
- Windowed/live designs materialize local state (RocksDB); monitor size and consider periodic maintenance (see [Streamiz-Clear](Streamiz-Clear))

### Examples
- See: [Examples](Examples)
- Query basics: `query-filter`, `view-toquery`
- Windowing: `windowing`, `bar-1m-live-consumer`

### Related
- End-to-end overview: [Overview](Overview), [Sequence-and-Observation](Sequence-and-Observation)
- Modeling and windows: [Streams-and-Tables](Streams-and-Tables), [Tumbling](Tumbling)