Eventhouse CLI

.create table Events (
    Timestamp: datetime, EventType: string,
    UserId: string, Properties: dynamic, Duration: real
)

.create-merge table Events (
    Timestamp: datetime, EventType: string, UserId: string,
    Properties: dynamic, Duration: real,
    Region: string       // new column — added safely
)

.alter-merge table Events (NewColumn: string)       // add column
.rename column Events.OldName to NewName             // rename column
.drop column Events.OldColumn                        // drop column (irreversible)
.drop table Events ifexists                          // drop table

.rename table OldName to NewName

// Atomic swap (Blue-Green pattern)
.rename tables NewEvents = Events, Events_Backup = Events_Old, Events = NewEvents

.ingest inline into table Events <|
2025-01-15T10:00:00Z,Login,user1,{},0.5
2025-01-15T10:01:00Z,Click,user2,{"page":"home"},0.2

// Append query results
.set-or-append Events <|
    OtherTable
    | where Timestamp > ago(1d)
    | project Timestamp, EventType, UserId, Properties, Duration

// Replace table content with query results
.set-or-replace Events <|
    StagingEvents | where IsValid == true

// From Blob Storage with CSV mapping
.ingest into table Events (
    h'https://myaccount.blob.core.windows.net/container/events.csv.gz;impersonate'
) with (format="csv", ingestionMappingReference="EventsCsvMapping", ignoreFirstRecord=true)

// From OneLake
.ingest into table Events (
    h'abfss://[email protected]/lakehouse.Lakehouse/Files/events.parquet;impersonate'
) with (format="parquet")

.alter table Events policy streamingingestion enable

.create table Events ingestion csv mapping "EventsCsvMapping"
'['
'  {"column":"Timestamp",  "datatype":"datetime", "ordinal":0},'
'  {"column":"EventType",  "datatype":"string",   "ordinal":1},'
'  {"column":"UserId",     "datatype":"string",   "ordinal":2},'
'  {"column":"Properties", "datatype":"dynamic",  "ordinal":3},'
'  {"column":"Duration",   "datatype":"real",     "ordinal":4}'
']'

.create table Events ingestion json mapping "EventsJsonMapping"
'['
'  {"column":"Timestamp",  "path":"$.timestamp",  "datatype":"datetime"},'
'  {"column":"EventType",  "path":"$.eventType",  "datatype":"string"},'
'  {"column":"UserId",     "path":"$.userId",     "datatype":"string"},'
'  {"column":"Properties", "path":"$.properties", "datatype":"dynamic"},'
'  {"column":"Duration",   "path":"$.duration",   "datatype":"real"}'
']'

.show table Events ingestion csv mappings
.show table Events ingestion json mappings

// Table-level: 365-day retention
.alter table Events policy retention
```{"SoftDeletePeriod":"365.00:00:00","Recoverability":"Enabled"}```

// Database-level default (applies to tables without override)
.alter database MyDatabase policy retention
```{"SoftDeletePeriod":"90.00:00:00","Recoverability":"Enabled"}```

.alter table Events policy caching hot = 30d
.alter database MyDatabase policy caching hot = 7d    // database default

.alter table Events policy partitioning

.alter table Events policy merge
```{"RowCountUpperBoundForMerge": 1000000, "MaxExtentsToMerge": 20}```

// Create (with optional backfill of existing data)
.create materialized-view with (backfill=true) EventCounts on table Events {
    Events
    | summarize Count = count(), LastSeen = max(Timestamp) by EventType
}

// Alter
.alter materialized-view EventCounts on table Events {
    Events
    | summarize Count = count(), LastSeen = max(Timestamp), FirstSeen = min(Timestamp) by EventType
}

// Lifecycle
.show materialized-view EventCounts statistics
.disable materialized-view EventCounts
.enable materialized-view EventCounts
.drop materialized-view EventCounts

.create-or-alter function with (
    docstring = "Get events for a specific user in time range",
    folder = "Analytics"
) GetUserEvents(userId: string, lookback: timespan = 1d) {
    Events
    | where Timestamp > ago(lookback) and UserId == userId
    | sort by Timestamp desc
}

.show function GetUserEvents
.drop function GetUserEvents

// Usage
GetUserEvents("user123", 7d) | count

// Target table
.create table ParsedEvents (
    Timestamp: datetime, EventType: string, UserId: string, PageName: string
)

// Transformation function
.create-or-alter function ParseRawEvents() {
    RawEvents
    | extend Parsed = parse_json(RawData)
    | project
        Timestamp = todatetime(Parsed.timestamp),
        EventType = tostring(Parsed.eventType),
        UserId = tostring(Parsed.userId),
        PageName = tostring(Parsed.properties.page)
}

// Attach update policy
.alter table ParsedEvents policy update
@'[{"IsEnabled":true,"Source":"RawEvents","Query":"ParseRawEvents()","IsTransactional":true}]'

.create external table ExternalSales (
    OrderDate: datetime,
    ProductId: string,
    Quantity: int,
    Amount: real
) kind=storage
dataformat=parquet
(
    h'abfss://[email protected]/lakehouse.Lakehouse/Tables/sales;impersonate'
)

external_table("ExternalSales")
| where OrderDate > ago(30d)
| summarize TotalAmount = sum(Amount) by ProductId
| top 10 by TotalAmount desc

// Add a viewer
.add database MyDatabase viewers ('[email protected]')

// Add an admin
.add database MyDatabase admins ('[email protected]')

// Add table-level admin
.add table Events admins ('[email protected]')

// Show current principals
.show database MyDatabase principals