fabric-eventhouse

# Fabric Eventhouse / KQL Database KQL management and query patterns specific to Fabric Eventhouse. Assumes familiarity with KQL — focus is on Fabric-specific behavior and az-rest-driven workflows. ## Connection - **Cluster URI**: each KQL Database has its own `queryServiceUri`, `https://<cluster>.kusto.fabric.microsoft.com`. Discover with `GET /v1/workspaces/{wsId}/kqlDatabases` (returns `queryServiceUri` + `databaseName` per item). - **Token audience**: `https://kusto.kusto.windows.net/.default` for all direct access. A wrong audience surfaces as a 401 *"Request is invalid and cannot be processed"*, not an auth error. - **Query endpoint**: `POST {clusterUri}/v1/rest/query`, body `{"db":"<dbName>", "csl":"<KQL>"}`. - **KQL `|` breaks shell escaping** — write the JSON body to a temp file and pass `--body @<file>`, never inline. Worked `az rest` invocation and the REST item-definition envelope: [references/programmatic-access.md](references/programmatic-access.md). ## Schema Discovery ```kql .show tables .show table T schema as json // column names + types .show table T details // row count, extent count, size .show functions .show materialized-views .show database principals // who has what role ``` ## Schema Evolution - **`.create-merge table`** is the safe / idempotent form — adds missing columns, never drops existing. Prefer over `.create table` for repeatable deployments. - `.alter-merge table T (NewCol: string)` — add column. - `.rename column T.OldName to NewName` — rename. - `.drop column T.OldCol` — irreversible; fails if column is used in materialized view or function (drop dependents first). - `.drop table T ifexists` — guarded drop. - **Atomic blue-green swap** via `.rename tables A=B, B=C, C=A` (single command, atomic). **With OneLake availability ON the surface narrows**: adding and deleting columns work (April 2026+), but altering a column type, renaming a table, applying RLS, and deleting/truncating/purging data do **not** — toggle availability off, change, toggle back on. Full matrix: [references/onelake-availability.md](references/onelake-availability.md). ## Ingestion | Command | Use for | |---|---| | `.ingest inline into table T <\|` | Small data / testing | | `.set-or-append T <\|` | Append KQL query results | | `.set-or-replace T <\|` | Replace table contents from a query | | `.ingest into table T (h'abfss://...;impersonate')` | Blob / ADLS / OneLake files — **the `;impersonate` suffix is required** | **Streaming ingestion is per-table and must be enabled first**: `.alter table Events policy streamingingestion enable`. **Pair every DDL change on a streaming-ingested table with a cache clear.** Nodes cache each table's streaming-ingestion schema. The refresh after a `.create-merge` is automatic but asynchronous and takes minutes, and until it lands every ingest keeps writing the old shape — a new column arrives **empty on every row, with a success response and a matching record count.** Nothing errors anywhere. ```kql .clear table T cache streamingingestion schema // one table .clear database cache streamingingestion schema // every table, one call ``` Reach for the database-scoped form after a bulk operation — one call across 19 renamed tables against 19 round trips. It also collapses the cold-table window in which a freshly created table answers `520` to every ingest (measured 76 s, 2026-09-14). Both return one row per node; a `Status=Failed` row is safely retryable. > **Schema-associated Eventstream destinations** auto-create one table per schema > named `{CloudEventType}_{CloudEventSchemaVersion}` (e.g. `Orders_v1`) — don't > create these by hand. See `fabric-eventstream`. Full command syntax and CSV/JSON mapping definitions: [references/ingestion.md](references/ingestion.md). ## Policies | Policy | Purpose | Notes | |---|---|---| | **Retention** | Soft-delete period + recoverability | JSON body with `SoftDeletePeriod` and `Recoverability` | | **Caching** | Hot (SSD) vs cold tier window | `.alter table T policy caching hot = 30d` | | **Partitioning** | Hash key for extent pruning | Hash on column with `XxHash64`, `MaxPartitionCount` | | **Merge** | Background extent merge thresholds | `RowCountUpperBoundForMerge`, `MaxExtentsToMerge` | | **Streaming ingestion** | Enable streaming endpoint | `.alter table T policy streamingingestion enable` | Database-level policies (`.alter database MyDB policy ...`) act as defaults; table-level overrides them. Check effective policy with `.show table T policy retention`. ## Materialized Views ```kql .create materialized-view with (backfill=true) EventCounts on table Events { Events | summarize Count = count(), LastSeen = max(Timestamp) by EventType } // Lifecycle .show materialized-view EventCounts statistics .disable materialized-view EventCounts .enable materialized-view EventCounts .drop materialized-view EventCounts // Query a materialized view materialized_view("EventCounts") | where EventType == "Login" ``` ## Stored Functions and Update Policies `.create-or-alter function` takes `docstring`, `folder`, and default parameter values — see [references/REFERENCE.md](references/REFERENCE.md). The Fabric-specific piece is wiring one as an **update policy**, an automatic transform applied on ingestion into the source table: ```kql .create-or-alter function ParseRawEvents() { RawEvents | extend Parsed = parse_json(RawData) | project Timestamp = todatetime(Parsed.timestamp), UserId = tostring(Parsed.userId) } .alter table ParsedEvents policy update @'[{"IsEnabled":true,"Source":"RawEvents","Query":"ParseRawEvents()","IsTransactional":true}]' ``` `IsTransactional: true` makes the source-row insert and the transform atomic — failed transform aborts both. ## External Tables (OneLake / ADLS) ```kql .create external table ExternalSales ( OrderDate: datetime, ProductId: string, Quantity: int, Amount: real ) kind=storage dataformat=parquet ( h'abfss://workspace@onelake.dfs.fabric.microsoft.com/lakehouse.Lakehouse/Tables/sales;impersonate' ) external_table("ExternalSales") | where OrderDate > ago(30d) | summarize sum(Amount) by ProductId ``` ## Permission Model | Role | Query | Ingest | Admin | |---|---|---|---| | `viewer` | ✅ | ❌ | ❌ | | `user` | ✅ | ❌ | ❌ | | `ingestor` | ❌ | ✅ | ❌ | | `admin` | ✅ | ✅ | ✅ | ```kql .add database MyDB viewers ('aaduser=user@contoso.com') .add database MyDB admins ('aaduser=admin@contoso.com') .add table T admins ('aaduser=tableadmin@contoso.com') ``` Layered with **Fabric workspace roles** (Admin/Member/Contributor/Viewer). `restrict access` and security functions provide RLS. ## Query Patterns | Pattern | Why | |---|---| | **Always filter by time first** — `where Timestamp > ago(...)` | Enables extent pruning | | Use `has` over `contains` | `has` uses term index (fast); `contains` is substring scan (slow) | | `project` early to drop unused columns | Reduces memory | | `summarize` with `bin(Timestamp, 1h)` | Efficient time bucketing | | `take 100` for exploration | Avoids full scans | | `materialize()` reused subexpressions | Caches intermediate result | | Avoid `*` in `project` | Explicit column list survives schema changes | ### String matching speed | Operator | Indexed | Case-Sensitive | Speed | |---|---|---|---| | `==` | ✅ | Yes | Fastest | | `=~` | ❌ partial | No | Medium | | `has` | ✅ | No | Fast | | `has_cs` | ✅ | Yes | Fast | | `startswith` | partial | No | Medium | | `contains` | ❌ | No | Slow | | `matches regex` | ❌ | Yes | Slowest | ## Monitoring ```kql .show queries // currently running .show commands | where StartedOn > ago(1h) .show journal // management ops history .show ingestion failures | where FailedOn > ago(24h) .show table T extents // per-table extent count + size .show database datastats // DB size, extent count, row count ``` ## Cross-Database Queries ```kql database("OtherDB").OtherTable | take 10 ``` ## Graph semantics KQL can do graph analysis **inside the KQL engine** — `make-graph` for a transient graph, `graph()` over a persistent snapshot, then `graph-match` / `graph-shortest-paths`. This is **not** the standalone Fabric **GraphModel** item, which is a separate workload using GQL / ISO-39075 (see `fabric-graph`). Same word, different engine. Operators, snapshot management and limits, and the openCypher/GQL-over-KQL preview: [references/graph-operators.md](references/graph-operators.md). ## Gotchas | Issue | Cause | Fix | |---|---|---| | `Request is invalid and cannot be processed` (401) | Wrong token audience | Use `https://kusto.kusto.windows.net/.default` | | `dynamic` column shows as string | Stored as string, not dynamic | Wrap with `parse_json(col)` or `todynamic()` | | `Forbidden (403)` on management commands | Insufficient role | Need `admin` or `ingestor` database role | | OneLake / ADLS ingest auth fails | Missing `;impersonate` on URI | Append `;impersonate` to the storage URI | | Materialized view stuck at 0% | No new source data or backfill pending | `.show materialized-view MV statistics` | | `.drop column` fails | Column referenced by materialized view or function | Drop dependents first | | Streaming ingestion errors | Streaming policy not enabled | `.alter table T policy streamingingestion enable` | | External table returns no data | Path / format / schema mismatch | Verify `abfss://` path, `dataformat=`, and column types match source | | Retention deleting data too soon | Table-level policy overrides DB default | `.show table T policy retention` | | `dcount()` returns approximate value | HyperLogLog by design | `dcount(col, 4)` for higher accuracy (costly), or `T \| distinct col \| count` for exact | | `.show tables details` reports `TotalRowCount 0` for every table after a rename or other structural change | Cached, asynchronously-recomputed statistic — the data is untouched | Verify before concluding anything: `union withsource=SourceTable T1, T2, … \| summarize count() by SourceTable`. Reproduced 2026-09-15 across 19 tables | | `.show database policy mirroring` / `policy update` → *"Syntax error: A recognition error occurred"* | Those policy `.show` commands are **table-scoped only** — unlike the streaming-ingestion cache clear, which is database-scoped | `.show table T policy mirroring`. There is no multi-table form either — `.show tables (A, B) policy mirroring` fails the same way | ## Remote MCP server (preview) A hosted HTTP MCP server per KQL database gives schema discovery, NL→KQL, and query execution. **It connects from Claude Code** — through a `headersHelper` command supplying an `az` bearer at the `https://api.fabric.microsoft.com` audience, measured 2026-09-14 on CLI 2.1.268 against this exact workspace/item-bound shape over two rounds. What fails is Claude Code's **automatic** OAuth flow, which registers via Dynamic Client Registration that Microsoft's auth server doesn't support — a fact about the flow, not about the endpoint. Watch the failure text on this URL shape: a helper that yields no credential prints `Error dialing <url>`, which reads like a network fault and is an auth one. The local `fabric-rti-mcp` (`uvx microsoft-fabric-rti-mcp`) stays worth preferring for breadth rather than reachability — it also reaches ADX and Eventstream. URL pattern, auth requirements, and the config block: [references/remote-mcp.md](references/remote-mcp.md). ## Reference - Microsoft Learn: [Eventhouse overview](https://learn.microsoft.com/fabric/real-time-intelligence/eventhouse) - Full MS Learn link bundle (KQL management commands / ingestion / mappings / materialized views / update policies / monitoring / REST query API): [references/REFERENCE.md](references/REFERENCE.md) ## See also - fabric-auth skill — `kusto.kusto.windows.net/.default` audience details - fabric-cli skill — `fab` CLI for Eventhouse item creation, principals, exports - fabric-rest-api skill — `kqlDatabases` listing endpoint and pagination - fabric-graph skill — the *other* graph workload: the standalone Fabric **GraphModel** item (GQL / ISO-39075 over OneLake). Different engine; the KQL graph operators above do NOT apply there.