KQL Database definition

Article
11/13/2024

This article provides a breakdown of the structure for KQL Database definition items.

Supported formats

KQLDatabaseDefinition items support the JSON format.

Definition parts

The definition of a KQL Database item is constructed from three parts: the item part, the platform part, and the KQL database schema. Each part contains the following:

Path: The path to the file that contains the JSON definition.
Payload: For the platform part, see Platform part. For the DatabaseProperties part, see Example of DatabaseProperties.json part decoded from Base64. For the DatabaseSchema part, see Example of DatabaseSchema.kql part decoded from Base64.
PayloadType: InlineBase64

Example of DatabaseProperties.json part decoded from Base64

The JSON file describing the KQL database has the following properties:

Property	Type	Required	Description
`databaseType`	string	✅	Kind of the database, currently only ReadWrite is supported.
`parentEventhouseItemId`	guid	✅	Item ID of the parent eventhouse.
`oneLakeCachingPeriod`	string	❌	The amount of time the data is cached in OneLake, making it available for faster queries. The expected format is ISO 8601 Time span, where P356D represents 356 days.
`oneLakeStandardStoragePeriod`	string	❌	The amount of time the data is stored in OneLake storage, making it available for queries. The expected format is ISO 8601 Time span, where P356D represents 356 days.

{
  "databaseType": "ReadWrite",
  "parentEventhouseItemId": "eff8ccbe-b44b-4101-9fd2-a99fc33543d0", 
  "oneLakeCachingPeriod": "P36500D", 
  "oneLakeStandardStoragePeriod": "P365000D" 
}

Platform part

The platform payload is optional. The platform part is a file that contains the Eventhouse metadata information.

Create Item with definition respects the platform file if provided.
Get Item definition always returns the platform file.
Update Item definition accepts the platform file if provided, if you set a new URL parameter updateMetadata=true.

Example of DatabaseSchema.kql part decoded from Base64

The KQL database schema is a KQL script that defines the tables, functions, materialized views, and more.


.create-merge table MyLogs2 (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int) 
.create-merge table MyLogs3 (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int) 
.create-merge table MyLogs7 (Level:string, Timestamp:datetime, UserId:string, TraceId:string, Message:string, ProcessId:int)

Definition example

{
"parts": [
    {
        "path": "DatabaseProperties.json",
        "payload": "ewogICJkYXRhYmFzZVR5cGUiOiAiUmVhZFdyaXRlIiwKICAicGFyZW50RXZlbnRob3VzZUl0ZW1JZCI6ICI1YjIxODc3OC1lN2E1LTRkNzMtODE4Ny1mMTA4MjQwNDc4MzYiLAogICJvbmVMYWtlQ2FjaGluZ1BlcmlvZCI6ICJQMzY1MDBEIiwKICAib25lTGFrZVN0YW5kYXJkU3RvcmFnZVBlcmlvZCI6ICJQMzY1MDBEIgp9",
        "payloadType": "InlineBase64"
    },
    {
        "path": "DatabaseSchema.kql",
        "payload": "Ly8gS1FMIHNjcmlwdAovLyBVc2UgbWFuYWdlbWVudCBjb21tYW5kcyBpbiB0aGlzIHNjcmlwdCB0byBjb25maWd1cmUgeW91ciBkYXRhYmFzZSBpdGVtcywgc3VjaCBhcyB0YWJsZXMsIGZ1bmN0aW9ucywgbWF0ZXJpYWxpemVkIHZpZXdzLCBhbmQgbW9yZS4KCi5jcmVhdGUtbWVyZ2UgdGFibGUgTXlMb2dzIChMZXZlbDpzdHJpbmcsIFRpbWVzdGFtcDpkYXRldGltZSwgVXNlcklkOnN0cmluZywgVHJhY2VJZDpzdHJpbmcsIE1lc3NhZ2U6c3RyaW5nLCBQcm9jZXNzSWQ6aW50KQ==",
        "payloadType": "InlineBase64"
    },
    {
        "path": ".platform",
        "payload": "ZG90UGxhdGZvcm1CYXNlNjRTdHJpbmc=",
        "payloadType": "InlineBase64"
    }
]
}

Share via