Eventhouse CLI

# Discover KQL Database query URI
WS_ID="<workspace-id>"
az rest --method GET \
  --url "https://api.fabric.microsoft.com/v1/workspaces/${WS_ID}/kqlDatabases" \
  --resource "https://api.fabric.microsoft.com" \
  | jq '.value[] | {name: .displayName, queryUri: .properties.queryServiceUri}'

# Set connection variables
CLUSTER_URI="https://<cluster>.kusto.fabric.microsoft.com"
DB_NAME="MyDatabase"

# Verify admin access
# Unquoted heredoc: the database name is interpolated into the command text itself.
read -r -d '' CSL <<KQL || true
.show database ${DB_NAME} principals | where Role == 'Admin'
KQL
jq -n --arg db "${DB_NAME}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json
az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/mgmt" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  | jq '.Tables[0].Rows'

# A quoted heredoc keeps the command literal -- no shell expansion, and no
# backslash-escaping even for policies and mappings that contain their own JSON.
read -r -d '' CSL <<'KQL' || true
<KQL management command>
KQL

# jq JSON-escapes the command; ${DB} still expands because it is passed as an argument.
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/mgmt" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  | jq '.Tables[0].Rows'

read -r -d '' CSL <<'KQL' || true
.create-merge table Events (Timestamp: datetime, EventType: string, UserId: string, Properties: dynamic, Duration: real)
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

read -r -d '' CSL <<'KQL' || true
.alter-merge table Events (Region: string)
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

read -r -d '' CSL <<'KQL' || true
.drop table Events ifexists
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

# Real newlines after `<|`, not a literal \n: the heredoc keeps the text verbatim, so a
# backslash-n would reach the server as two characters rather than a row separator.
read -r -d '' CSL <<'KQL' || true
.ingest inline into table Events <|
2025-01-15T10:00:00Z,Login,user1,{},0.5
2025-01-15T10:01:00Z,Click,user2,{},0.2
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

read -r -d '' CSL <<'KQL' || true
.ingest into table Events (h'https://mystorage.blob.core.windows.net/data/events.csv.gz;impersonate') with (format='csv', ingestionMappingReference='EventsCsvMapping', ignoreFirstRecord=true)
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

read -r -d '' CSL <<'KQL' || true
.ingest into table Events (h'abfss://[email protected]/lakehouse.Lakehouse/Files/events.parquet;impersonate') with (format='parquet')
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

read -r -d '' CSL <<'KQL' || true
.set-or-append CleanEvents <| RawEvents | where IsValid == true | project Timestamp, EventType, UserId
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

# Set 365-day retention
read -r -d '' CSL <<'KQL' || true
.alter table Events policy retention '{"SoftDeletePeriod":"365.00:00:00","Recoverability":"Enabled"}'
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

# Keep last 30 days in hot cache
read -r -d '' CSL <<'KQL' || true
.alter table Events policy caching hot = 30d
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

read -r -d '' CSL <<'KQL' || true
.alter table Events policy streamingingestion enable
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

Step 0 → Is the request specific? Does it name a table, operation, and/or schema?
         → NO  → Ask: "What would you like to set up? Options: create tables,
                  configure policies, set up ingestion mappings, create materialized views."
                  STOP — do not proceed until user specifies.
         → YES → Continue to Step 1.
Step 1 → .show tables details                        // what exists?
Step 2 → .show table <TABLE> schema as json          // current columns
Step 3 → .show table <TABLE> policy retention        // current policies
Step 4 → Plan changes (create-merge, alter, etc.)
Step 5 → Execute changes
Step 6 → Verify: .show table <TABLE> schema as json  // confirm changes

Step 1 → Understand requirements from user
Step 2 → Generate KQL management commands
Step 3 → Save to .kql file
Step 4 → Deploy via az rest (one command at a time)
Step 5 → Verify deployed state matches intent

# Create table
read -r -d '' CSL <<'KQL' || true
.create-merge table SensorData (Timestamp: datetime, DeviceId: string, Temperature: real, Humidity: real, Location: dynamic)
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

# Set retention
read -r -d '' CSL <<'KQL' || true
.alter table SensorData policy retention '{"SoftDeletePeriod":"90.00:00:00","Recoverability":"Enabled"}'
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

# Set caching
read -r -d '' CSL <<'KQL' || true
.alter table SensorData policy caching hot = 7d
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

# Create JSON mapping
read -r -d '' CSL <<'KQL' || true
.create table SensorData ingestion json mapping 'SensorJsonMapping' '[{"column":"Timestamp","path":"$.ts","datatype":"datetime"},{"column":"DeviceId","path":"$.deviceId","datatype":"string"},{"column":"Temperature","path":"$.temp","datatype":"real"},{"column":"Humidity","path":"$.humidity","datatype":"real"},{"column":"Location","path":"$.location","datatype":"dynamic"}]'
KQL
jq -n --arg db "${DB}" --arg csl "${CSL}" '{db: $db, csl: $csl}' > /tmp/kql_body.json

// 1. Target table
.create-merge table ParsedLogs (Timestamp: datetime, Level: string, Message: string, Source: string)

// 2. Transform function
.create-or-alter function ParseRawLogs() {
    RawLogs
    | extend J = parse_json(RawMessage)
    | project
        Timestamp = todatetime(J.timestamp),
        Level = tostring(J.level),
        Message = tostring(J.message),
        Source = tostring(J.source)
}

// 3. Attach update policy
.alter table ParsedLogs policy update
@'[{"IsEnabled":true,"Source":"RawLogs","Query":"ParseRawLogs()","IsTransactional":true}]'