10 Kusto Query Best Practices

Kusto Query Language (KQL) is the language used to query Azure’s services. KQL is simple and easy to learn. In this article, we will go over 10 best practices for writing KQL queries.

1. Avoid using the * wildcard

The * wildcard matches zero or more characters, so when you use it in a query, Kusto has to scan the entire table to find all of the matching rows. This can be very slow, especially on large tables.

Instead of using the * wildcard, you should specify the exact column names that you want to select in your query. This will make your queries much faster and more efficient.

2. Use a where clause to filter data before aggregating it

When you’re aggregating data, Kusto will first fetch all of the raw data before it starts to aggregate it. This can be extremely inefficient if you’re working with large datasets.

By using a where clause to filter the data first, you can reduce the amount of data that Kusto has to process, which will make your queries run much faster.

3. Don’t use count() when you can use summarize instead

When you use count(), Kusto will scan all of the rows in your table, which can be time-consuming. On the other hand, summarize only scans the columns that you’re interested in, which is much faster.

So, if you’re only interested in counting the number of rows where a certain column has a certain value, you should use summarize instead of count().

4. Use make-series to create time series charts

When you’re dealing with time-series data, it’s important to be able to visualize the data over time so that you can see patterns and trends. Kusto’s make-series operator makes it easy to create time series charts by automatically creating a series of data points for each timestamp in the range that you specify.

For example, let’s say you have a table of data that contains a timestamp column and a value column. You can use the following query to create a time series chart of the data:

make-series
| extend timestamp = bin(timestamp, 1h)
| project timestamp, value

This query will create a series of data points, one for each hour in the range of timestamps in the table. The resulting chart will show you how the values change over time.

You can also use the make-series operator to downsample data, which can be useful when you’re dealing with large amounts of data. For example, you could use the following query to downsample data from a table that contains a timestamp column and a value column:

make-series
| extend timestamp = bin(timestamp, 1d)
| project timestamp, value

This query will create a series of data points, one for each day in the range of timestamps in the table. The resulting chart will show you how the values change over time, but at a lower resolution than the original data.

The make-series operator is a powerful tool that can be used to create time series charts from data in Kusto tables. When you’re working with time-series data, be sure to use this operator to create charts that will help you visualize the data and spot patterns and trends.

5. Use bin and extend operators for histograms

The bin operator allows you to group together values that are within a certain range. This is especially useful when you’re dealing with large data sets, because it can help you make sense of all the information.

The extend operator, on the other hand, allows you to add additional columns to your query results. This is useful for adding extra information that can help you understand your data better.

For example, let’s say you have a table of data that includes a column for customer names and a column for purchase amounts. You could use the bin operator to group together all the customers who spent between $0 and $100, and then use the extend operator to add a column that shows the average purchase amount for each group.

Doing this would give you a much better understanding of your data, and it would also make it easier to spot trends and patterns.

6. Use let statements to avoid repeating code

Suppose you have a Kusto query that returns data about website visits. The query has a where clause that filters the data to only include visits from users in the United States:

where geo_country == “US”
Now suppose you want to modify the query to also return data for users in Canada. You could simply add another where clause:

where geo_country == “US” or geo_country == “CA”
However, this approach is not ideal because it requires you to repeat the code for the US filter. A better approach would be to use a let statement to define the US filter once, and then reference it in the where clause:

let us_filter = geo_country == “US”;
where us_filter or geo_country == “CA”
This approach is more efficient because you only need to define the US filter once. It’s also easier to read and maintain because the where clause is less cluttered.

7. Use render operator to control chart appearance

The render operator allows you to control various aspects of the chart such as the type of chart, the data that is plotted, the legend, the title, and so on. By using the render operator, you can ensure that your charts are clear and easy to understand.

Furthermore, the render operator allows you to save your queries so that you can reuse them later. This is extremely useful if you need to generate multiple charts from the same data set.

Finally, the render operator allows you to share your charts with others. You can export your charts as images or PDFs, or you can embed them in websites or blog posts.

8. Use project-away operator to remove columns from result set

The project-away operator is used to remove columns from the result set. The main reason for doing this is to reduce the amount of data that is returned, which can help improve performance. In addition, it can also help to simplify the results, making them easier to understand.

For example, suppose you have a query that returns a large number of columns, but you only need to see a few of them. In this case, you could use the project-away operator to remove all of the unnecessary columns, leaving only the ones you need.

Projecting away columns can also be useful for security purposes. For example, if you are querying sensitive data, you may not want to return all of the columns in the result set. In this case, you could use the project-away operator to remove any sensitive columns, ensuring that they are not returned in the results.

9. Use mv-apply to flatten arrays into rows

When you have data that is stored in arrays, it can be difficult to work with because each array element is its own row. This means that if you want to do any kind of analysis on the data, you need to first flatten the arrays into rows.

The mv-apply operator makes this process much easier by allowing you to specify how you want the data to be flattened. For example, you can specify that you want each array element to be its own row, or you can specify that you want all of the data from each array to be concatenated into a single row.

Either way, using mv-apply to flatten arrays into rows will make your life much easier when working with Kusto Query.

10. Use parse operator to convert strings to datetime values

The parse operator is used to convert strings into datetime values, and it’s important to use this operator because it can help prevent errors when querying data. For example, if you have a column in your data that contains dates as strings, and you try to query that data without using the parse operator, you might get an error.

To avoid this type of error, you should always use the parse operator when querying data that contains dates as strings. By doing so, you can be sure that your queries will run smoothly and that you’ll get accurate results.