Querying Data in Azure Data Explorer Xavier Morera HELPING - PowerPoint PPT Presentation

Querying Data in Azure Data Explorer Xavier Morera HELPING DEVELOPERS UNDERSTAND SEARCH & BIG DATA @xmorera www.xaviermorera.com

A Query

Query Results

Executing Queries Kusto Web UI Integration with Kusto Explorer other products

Getting to Know the Kusto Query Language

A Kusto query is a read-only request to process data and return results

A Kusto query is a read-only request to process data and return results

StormEvents | where StartTime > datetime(2007-02-01) | where EventType == 'Flood' and State == 'CALIFORNIA’ | project StartTime, EndTime , State , EventType | take 10; A Kusto Query Plain text statement Data-flow model - Designed to make the syntax easy to read, author, and automate

s h s Kusto query - Uses schema entities - Organized in a hierarchy • Similar to SQL’s databases, tables, and columns Functions are supported - Stored, query-defined, and built-in

StormEvents | where StartTime > datetime(2007-02-01) | where EventType == 'Flood' and State == 'CALIFORNIA’ | project StartTime, EndTime , State , EventType | take 10; A Kusto Query

StormEvents | where StartTime > datetime(2007-02-01) | where EventType == 'Flood' and State == 'CALIFORNIA’ | project StartTime, EndTime , State , EventType | take 10; A Kusto Query Sequence of query statements

StormEvents | where StartTime > datetime(2007-02-01) | where EventType == 'Flood' and State == 'CALIFORNIA’ | project StartTime, EndTime , State , EventType | take 10; A Kusto Query Sequence of query statements - Delimited by a semicolon (;)

StormEvents | where StartTime > datetime(2007-02-01) | where EventType == 'Flood' and State == 'CALIFORNIA’ | project StartTime, EndTime , State , EventType | take 10; A Kusto Query Sequence of query statements - Delimited by a semicolon (;) At least one statement being a tabular expression statement

StormEvents | where StartTime > datetime(2007-02-01) | where EventType == 'Flood' and State == 'CALIFORNIA’ | project StartTime, EndTime , State , EventType | take 10; Tabular Expression Statement Structure of a tabular expression statement

StormEvents | where StartTime > datetime(2007-02-01) | where EventType == 'Flood' and State == 'CALIFORNIA’ | project StartTime, EndTime , State , EventType | take 10; Tabular Expression Statement Structure of a tabular expression statement - Tabular data sources - Database is implicit • Part of the connection information

StormEvents | where StartTime > datetime(2007-02-01) | where EventType == 'Flood' and State == 'CALIFORNIA’ | project StartTime, EndTime , State , EventType | take 10; Statement Syntax Data flow from one tabular query operator to another Through a set of data transformation operators - Optionally, a render instruction as the last statement Bound together by a pipe (|) delimiter

Kusto Query source1 | operator1 | operator2 | [renderInstruction]

KQL & SQL

KQL SQL StormEvents Select * FROM StormEvents StormEvents SELECT EpisodeId, State | where notnull(State) FROM StormEvents | project EpisodeId, State, WHERE State IS NOT NULL StormEvents SELECT TOP 100 State, | summarize Count=count() by COUNT(*) as Count State FROM StormEvents | project State, Count GROUP BY State | sort by Count desc ORDER BY Count DESC | take int(100),

t h s Demo SQL to Kusto query translation Using EXPLAIN

Querying Azure Data Explorer, the help Cluster, and the Sample Database

The help Cluster and Sample Database Data Explorer provides a help cluster - Created to aid learning Contains sample data - StormEvents - Other databases - External tables - Functions No need for you to spin up a cluster to test

t h s Demo The help Cluster and Sample Databases

https://help.kusto.windows.net/

Control Commands

Control Commands Retrieve data not in the database tables Requests to modify the state Three mechanisms to tell apart from queries - Language level • Start with a dot (.) - Protocol level • Different endpoint - API level • Different functions

.show cluster A Control Command Control command to show cluster information

.show cluster | count A Control Command Control command to show cluster information Command can be combined with a query - Must start with the command - Query cannot contain a command

.show cluster capacity databases commands tables table schema queries

.create table .create-merge table .rename table .drop table Table Management Create a table in the current database - Or extend an existing table Rename And drop

t h s Demo Control commands

The Kusto Query Language (KQL)

Types of Commands Filter Date / Time Sort / Aggregate Column Related Format Data

Filter Commands [T [T] | ] | s search ch [ki [kind=Ca CaseSens nsitivi vity] ] T | where Predicate T [i [in ( (Ta TableSources)] )] Se SearchPredic icate where search

where search Filters on a predicate Searches all columns - In the specified table Similar to SQL • Or all tables - For the value StormEvents StormEvents | where DamageCrops > 0 | search "heavy rain"

Predicates and, or, ==, !=, =~, !~, <, >... has, !has, contains, !contains, startswith, endswith, matches... between, not-between, distinct in, !in case

has contains Looks for a specific word Looks for any substring match Better performance StormEvents StormEvents | where EventType | where EventType has "Storm" contains "Storm"

Date and Time now ago totimespan datetime_add, datetime_diff... dayofyear, dayofmonth, dayofweek...

Sort and Aggregate sort by summarize top order by make-series count join dcount range union dcountif

Columns project project-away extend

Format Data Commands format_datetime lookup format_timespan mv-expand parse

t h s Demo The Basics of KQL

https://docs.microsoft.com /en-us/azure/data- explorer/kql-quick- reference

More KQL Operators

t h s Demo More KQL Operators

s h s Bind names to expressions Name can be used - To refer to its bound value - Within current scope let When previously defined - Innermost let state binding used Improve modularity - Break complex expressions

let Bind names to expressions let Name = ScalarExpression

let Bind names to expressions let Name = TabularExpression

let Bind names to expressions let Name = FunctionDefinitionExpression

let Bind names to expressions let f=(a:int, b:string) { strcat(b, ":", a) }

Advanced KQL

Advanced KQL Time series analysis Machine learning

Time Series Analysis Analyze time series - Discover deviations - Compared to a baseline Native support - Creation, manipulation, and analysis - Of time-series data Enables near real-time monitoring

Time Series Analysis Native series support let min_t = toscalar(TABLE| summarize min(TIMESTAMP)); let max_t = toscalar(TABLE| summarize max(TIMESTAMP)); let dt = 1h; TABLE | make-series num=count() default=0 on TIMESTAMP in range(min_t, max_t, dt) by COLUMN | render timechart

Time Series Anomaly Detection Step by step let min_t = datetime(2017-01-05); let max_t = datetime(2017-02-03 22:00); let dt = 2h; demo_make_series2 | make-series num=avg(num) on TimeStamp from min_t to max_t step dt by sid | where sid == 'TS1’ | extend (anomalies, score, baseline) = series_decompose_anomalies(num, 1.5, -1, 'linefit’) | render anomalychart with(anomalycolumns=anomalies, title='Web app. traffic of a month, anomalies')

Time Analysis Complex Functions series_fir series_fit_2lines series_fit_line series_iir series_periods_detect series_periods_validate

Machine Learning Perform Root Cause Analysis - Complex and lengthy process Three Machine Learning plugins - Autocluster - Basket - Diffpatterns

t h s Demo Advanced KQL

Querying External Tables

Querying External Tables Query data stored outside a Kusto cluster External tables - Recognized as database entities - Just like regular Kusto tables Create external table - Data lake - Different formats supported

t h s Demo Querying External Tables

Data All data

Partitioned Data 2020 2019 2018

Create External Table with Partition (.create | .alter) external table TableName ( Schema ) kind = (blob | adl) [partition by ( Partitions ) [pathformat = ( PathFormat )]] dataformat = Format ( StorageConnectionString [, ...] ) [with (PropertyName = Value , ... )]

Querying Data in Azure Monitor and Using the Flow Kusto Connector

Query Data in Azure Monitor Proxy Cluster (ADX Proxy) - Enables cross product queries Data Explorer - The Azure Monitor Service • Application Insights (AI) • Log Analytics (LA) Create a connection from ADX Web UI - To Application Insights/Log Analytics - Using virtual cluster