Data types

• 1: Null values
• 2: Scalar data types
• 3: The bool data type
• 4: The datetime data type
• 5: The decimal data type
• 6: The dynamic data type
• 7: The guid data type
• 8: The int data type
• 9: The long data type
• 10: The real data type
• 11: The string data type
• 12: The timespan data type

1 - Null values

All scalar data types in Kusto have a special value that represents a missing value. This value is called the null value, or null.

Null literals

The null value of a scalar type T is represented in the query language by the null literal T(null).

The following query returns a single row full of null values:

print bool(null), datetime(null), dynamic(null), guid(null), int(null), long(null), real(null), double(null), timespan(null)

Predicates on null values

The scalar function isnull() can be used to determine if a scalar value is the null value. The corresponding function isnotnull() can be used to determine if a scalar value isn’t the null value.

Equality and inequality of null values

• Equality (==): Applying the equality operator to two null values yields bool(null). Applying the equality operator to a null value and a non-null value yields bool(false).
• Inequality (!=): Applying the inequality operator to two null values yields bool(null). Applying the inequality operator to a null value and a non-null value yields bool(true).

For example:

datatable(val:int)[5, int(null)]
| extend IsBiggerThan3 = val > 3
| extend IsBiggerThan3OrNull = val > 3 or isnull(val)
| extend IsEqualToNull = val == int(null)
| extend IsNotEqualToNull = val != int(null)

Output

Null values and aggregation functions

When applying the following operators to entities that include null values, the null values are ignored and don’t factor into the calculation:

• dcount()
• dcountif()
• make_bag()
• make_bag_if()
• make_list()
• make_list_if()
• make_set()
• make_set_if()
• stdev()
• stdevif()
• sum()
• sumif()
• variance()
• varianceif()

Null values and the where operator

The where operator use Boolean expressions to determine if to emit each input record to the output. This operator treats null values as if they’re bool(false). Records for which the predicate returns the null value are dropped and don’t appear in the output.

For example:

datatable(ival:int, sval:string)[5, "a", int(null), "b"]
| where ival != 5

Output

Null values and binary operators

Binary operators are scalar operators that accept two scalar values and produce a third value. For example, greater-than (>) and Boolean AND (&&) are binary operators.

For all binary operators, except as noted in Exceptions to this rule, the rule is as follows:

If one or both of the values input to the binary operator are null values, then the output of the binary operator is also the null value. In other words, the null value is “sticky”.

Exceptions to this rule

• For the equality (==) and inequality (!=) operators, if one of the values is null and the other value isn’t null, then the result is either bool(false) or bool(true), respectively.
• For the logical AND (&&) operator, if one of the values is bool(false), the result is also bool(false).
• For the logical OR (||) operator, if one of the values is bool(true), the result is also bool(true).

For example:

datatable(val:int)[5, int(null)]
| extend Add = val + 10
| extend Multiply = val * 10

Output

Null values and the logical NOT (!) operator

The logical NOT operator not() yields the value bool(null) if the argument is the null value.

Null values and the in operator

• The in operator behaves like a logical OR of equality comparisons.
• The !in operator behaves like a logical AND of inequality comparisons.

Null values and data ingestion

For most data types, a missing value in the data source produces a null value in the corresponding table cell. However, columns of type string and CSV (or CSV-like) data formats are an exception to this rule, and a missing value produces an empty string.

For example:

.create table T(a:string, b:int)

.ingest inline into table T
[,]
[ , ]
[a,1]

T
| project a, b, isnull_a=isnull(a), isempty_a=isempty(a), stlen_a=strlen(a), isnull_b=isnull(b)

Output

2 - Scalar data types

Every data value, like the value of an expression or a function parameter, has a data type which is either a scalar data type or a user-defined record. A scalar data type is one of the built-in predefined types in Supported data types. A user-defined record is an ordered sequence of name and scalar-data-type pairs, like the data type of a row in a table.

As in most languages, the data type determines what calculations and manipulations can be run against a value. For example, if you have a value that is of type string, you won’t be able to perform arithmetic calculations against it.

Supported data types

In Kusto Query Language, most of the data types follow standard conventions and have names you’ve probably seen before. The following table shows the full list:

While most of the data types are standard, you might be less familiar with types like dynamic or timespan, and guid.

• Dynamic has a structure similar to JSON, but with one key difference: It can store Kusto Query Language-specific data types that traditional JSON can’t, such as a nested dynamic value, or timespan.

• Timespan is a data type that refers to a measure of time such as hours, days, or seconds. Don’t confuse timespan with datetime, which evaluates to an actual date and time, not a measure of time. The following table shows a list of timespan suffixes.

• GUID is a datatype representing a 128-bit, globally unique identifier, which follows the standard format of [8]-[4]-[4]-[4]-[12], where each [number] represents the number of characters and each character can range from 0-9 or a-f.

Null values

All nonstring data types can be null. When a value is null, it indicates an absence or mismatch of data. For example, if you try to input the string abc into an integer column, it results in the null value. To check if an expression is null, use the isnull() function.

For more information, see Null values.

3 - The bool data type

The bool data type can be: true (1), false (0), or null.

bool literals

To specify a bool literal, use one of the following syntax options:

Boolean operators

The bool data type supports all of the logical operators: equality (==), inequality (!=), logical-and (and), and logical-or (or).

Related content

• tobool()

4 - The datetime data type

The datetime data type represents an instant in time, typically expressed as a date and time of day. Values range from 00:00:00 (midnight), January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.) in the Gregorian calendar.

Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the GregorianCalendar calendar (excluding ticks that would be added by leap seconds). For example, a ticks value of 31241376000000000 represents the date, Friday, January 01, 0100 12:00:00 midnight. This is sometimes called “a moment in linear time”.

datetime literals

To specify a datetime literal, use one of the following syntax options:

The now() and ago() special functions

Kusto provides two special functions, now() and ago(), to allow queries to reference the time at which the query starts execution.

Supported formats

There are several formats for datetime that are supported as datetime() literals and the todatetime() function.

ISO 8601

RFC 822

RFC 850

Sortable

Related content

• todatetime()
• ago()
• between

5 - The decimal data type

The decimal data type represents a 128-bit wide, decimal number.

decimal literals

To specify a decimal literal, use one of the following syntax options:

|Syntax|Description|Example| |–|–| |decimal(number)|A decimal number represented by one or more digits, followed by a decimal point, and then one or more digits.|decimal(1.0)| |decimal(numbereexponent)|A decimal number represented by scientific notation.|decimal(1e5) is equivalent to 100,000| |decimal(null)|Represents the null value.||

Related content

• todecimal()
• toreal()

6 - The dynamic data type

The dynamic scalar data type can be any of the following values:

• An array of dynamic values, holding zero or more values with zero-based indexing.
• A property bag that maps unique string values to dynamic values. The property bag has zero or more such mappings (called “slots”), indexed by the unique string values. The slots are unordered.
• A value of any of the primitive scalar data types: bool, datetime, guid, int, long, real, string, and timespan.
• Null. For more information, see Null values.

Dynamic literals

To specify a dynamic literal, use one of the following syntax options:

Dynamic object accessors

To subscript a dictionary, use either the dot notation (dict.key) or the brackets notation (dict["key"]). When the subscript is a string constant, both options are equivalent.

In the examples below dict and arr are columns of dynamic type:

Accessing a sub-object of a dynamic value yields another dynamic value, even if the sub-object has a different underlying type. Use the gettype function to discover the actual underlying type of the value, and any of the cast function listed below to cast it to the actual type.

Casting dynamic objects

After subscripting a dynamic object, you must cast the value to a simple type.

Cast functions are:

• tolong()
• todouble()
• todatetime()
• totimespan()
• tostring()
• toguid()
• parse_json()

Building dynamic objects

Several functions enable you to create new dynamic objects:

• bag_pack() creates a property bag from name/value pairs.
• pack_array() creates an array from list of values (can be list of columns, for each row it will create an array from the specified columns).
• range() creates an array with an arithmetic series of numbers.
• zip() pairs “parallel” values from two arrays into a single array.
• repeat() creates an array with a repeated value.

Additionally, there are several aggregate functions which create dynamic arrays to hold aggregated values:

• buildschema() returns the aggregate schema of multiple dynamic values.
• make_bag() returns a property bag of dynamic values within the group.
• make_bag_if() returns a property bag of dynamic values within the group (with a predicate).
• make_list() returns an array holding all values, in sequence.
• make_list_if() returns an array holding all values, in sequence (with a predicate).
• make_list_with_nulls() returns an array holding all values, in sequence, including null values.
• make_set() returns an array holding all unique values.
• make_set_if() returns an array holding all unique values (with a predicate).

Operators and functions over dynamic types

For a complete list of scalar dynamic/array functions, see dynamic/array functions.

Indexing for dynamic data

Every field is indexed during data ingestion. The scope of the index is a single data shard.

To index dynamic columns, the ingestion process enumerates all “atomic” elements within the dynamic value (property names, values, array elements) and forwards them to the index builder. Otherwise, dynamic fields have the same inverted term index as string fields.

Examples

Dynamic property bag

The following query creates a dynamic property bag.

print o=dynamic({"a":123, "b":"hello", "c":[1,2,3], "d":{}})
| extend a=o.a, b=o.b, c=o.c, d=o.d

For convenience, dynamic literals that appear in the query text itself may also include other Kusto literals with types: datetime, timespan, real, long, guid, bool, and dynamic. This extension over JSON isn’t available when parsing strings (such as when using the parse_json function or when ingesting data), but it enables you to do the following:

print d=dynamic({"a": datetime(1970-05-11)})

To parse a string value that follows the JSON encoding rules into a dynamic value, use the parse_json function. For example:

• parse_json('[43, 21, 65]') - an array of numbers
• parse_json('{"name":"Alan", "age":21, "address":{"street":432,"postcode":"JLK32P"}}') - a dictionary
• parse_json('21') - a single value of dynamic type containing a number
• parse_json('"21"') - a single value of dynamic type containing a string
• parse_json('{"a":123, "b":"hello", "c":[1,2,3], "d":{}}') - gives the same value as o in the example above.

Ingest data into dynamic columns

The following example shows how you can define a table that holds a dynamic column (as well as a datetime column) and then ingest single record into it. It also demonstrates how you can encode JSON strings in CSV files.

// dynamic is just like any other type:
.create table Logs (Timestamp:datetime, Trace:dynamic)

// Everything between the "[" and "]" is parsed as a CSV line would be:
// 1. Since the JSON string includes double-quotes and commas (two characters
//    that have a special meaning in CSV), we must CSV-quote the entire second field.
// 2. CSV-quoting means adding double-quotes (") at the immediate beginning and end
//    of the field (no spaces allowed before the first double-quote or after the second
//    double-quote!)
// 3. CSV-quoting also means doubling-up every instance of a double-quotes within
//    the contents.

.ingest inline into table Logs
  [2015-01-01,"{""EventType"":""Demo"", ""EventValue"":""Double-quote love!""}"]

Output

Related content

• For an example on how to query using dynamic objects and object accessors, see Map values from one set to another.

7 - The guid data type

The guid data type represents a 128-bit globally unique value.

guid literals

To specify a guid literal, use one of the following syntax options:

Related content

• toguid()

8 - The int data type

The int data type represents a signed, 32-bit wide, integer.

int literals

To specify an int literal, use one of the following syntax options:

|Syntax|Description|Example| |–|–| |int(number)|A positive integer.|int(2)| |int(-number)|A negative integer.|int(-2)| |int(null)|Represents the null value.||

Related content

• toint()

9 - The long data type

The long data type represents a signed, 64-bit wide, integer.

By default, integers and integers represented with hexadecimal syntax are of type long.

long literals

To specify a long literal, use one of the following syntax options:

|Syntax|Description|Example| |–|–| |number|An integer. You don’t need to wrap the integer with long() because integers are by default of type long.|12| |0xhex|An integer represented with hexadecimal syntax.|0xf is equivalent to 15| |long(-number)|A negative integer.|long(-1)| |long(null)|Represents the null value.||

Related content

• tolong()
• To convert the long type into a hex string, see tohex() function.

10 - The real data type

The real data type represents a 64-bit wide, double-precision, floating-point number.

By default, decimal numbers and numbers represented with scientific notation are of type real.

real literals

To specify a real literal, use one of the following syntax options:

Related content

• toreal()

11 - The string data type

The string data type represents a sequence of zero or more Unicode characters.

For information on string query operators, see String operators.

string literals

A string literal is a string enclosed in quotes. You can use double quotes or single quotes to encode string literals in query text. With double quotes, you must escape nested double quote characters with a backslash (\). With single quotes, you must escape nested single quote characters, and you don’t need to escape double quotes.

Use the backslash character to escape the enclosing quote characters, tab characters (\t), newline characters (\n), and the backslash itself (\\).

Verbatim string literals

Verbatim string literals are string literals prepended with the @ character, which serves as a verbatim identifier. In this form, the backslash character (\) stands for itself and isn’t an escape character. In verbatim string literals, double quotes are escaped with double quotes and single quotes are escaped with single quotes.

For an example, see Verbatim string.

Multi-line string literals

Indicate a multi-line string literals by a “triple-backtick chord” (```) at the beginning and end of the literal.

For an example, see Multi-line string literal.

Concatenation of separated string literals

In a Kusto query, when two or more adjacent string literals have no separation between them, they’re automatically combined to form a new string literal. Similarly, if the string literals are separated only by whitespace or comments, they’re also combined to form a new string literal.

For an example, see Concatenated string literals.

Obfuscated string literals

Queries are stored for telemetry and analysis. To safeguard sensitive information like passwords and secrets, you can mark a string as an obfuscated string literal. These marked strings are logged in obfuscated form replaced with asterisks (*) in the query text.

An obfuscated string literal is created by prepending an h or an H character in front of a standard or verbatim string literal.

For an example, see Obfuscated string literal.

Examples

String literal with quotes

The following example demonstrates how to use quotes within string literals encompassed by single quotes and double quotes. For more information, see String literals.

print
    s1 = 'string with "double quotes"',
    s2 = "string with 'single quotes'"

Output

String literal with backslash escaping

The following example creates a regular expression pattern using backslashes to escape special characters. For more information, see String literals.

print pattern = '\\n.*(>|\'|=|\")[a-zA-Z0-9/+]{86}=='

Output

String literal with Unicode

The following example shows that a backslash is needed to include a Unicode character in a string literal.

print space = "Hello\u00A0World"

Output

Verbatim string literal

The following example creates a path in which the backslashes are part of the path instead of escape characters. To do this, the string @ sign is prepended to the string, creating a verbatim string literal.

print myPath = @'C:\Folder\filename.txt'

Output

Multi-line string literal

The following example shows the syntax for a multi-line string literal, which uses newlines and tabs to style a code block. For more information, see Multi-line string literals.

print program = ```
  public class Program {
    public static void Main() {
      System.Console.WriteLine("Hello!");
    }
  }```

Output

Concatenated string literals

The following expressions all yield a string of length 13. For more information, see Concatenation of separated string literals.

print 
    none = strlen("Hello"', '@"world!"),
    whitespace = strlen("Hello" ', ' @"world!"),
    whitespaceAndComment = strlen("Hello" 
        // Comment
        ', '@"world!"
    );

Output

Obfuscated string literal

In the following query output, the h string is visible in your results. However, in tracing or telemetry, the h string is stored in an obfuscated form and substituted with asterisks in the log. For more information, see Obfuscated string literals.

print blob="https://contoso.blob.core.windows.net/container/blob.txt?"
    h'sv=2012-02-12&se=2013-04-13T0...'

Output

Related content

• String operators

12 - The timespan data type

The timespan data type represents a time interval.

timespan literals

To specify a timespan literal, use one of the following syntax options:

timespan operators

Two values of type timespan may be added, subtracted, and divided. The last operation returns a value of type real representing the fractional number of times one value can fit the other.

Examples

The following example calculates how many seconds are in a day in several ways:

print
    result1 = 1d / 1s,
    result2 = time(1d) / time(1s),
    result3 = 24 * 60 * time(00:01:00) / time(1s)

This example converts the number of seconds in a day (represented by an integer value) to a timespan unit:

print 
    seconds = 86400
| extend t = seconds * 1s

Related content

• totimespan function
• make-timespan function