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

@xmorera www.xaviermorera.com

HELPING DEVELOPERS UNDERSTAND SEARCH & BIG DATA

Xavier Morera

Querying Data in Azure Data Explorer

A Query

Query Results

Kusto Web UI

Executing Queries

Integration with

• ther products

Kusto Explorer

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

source1 | operator1 | operator2 | [renderInstruction]

Kusto Query

KQL & SQL

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

KQL SQL

Demo

t h s

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

Demo

t h s

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

commands queries table schema tables databases capacity cluster

.show

.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

Demo

t h s

Control commands

The Kusto Query Language (KQL)

Format Data Column Related Sort / Aggregate Date / Time Filter

Types of Commands

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

Filter Commands

Filters on a predicate Similar to SQL Searches all columns

• In the specified table
• Or all tables
• For the value

where search

StormEvents | search "heavy rain" StormEvents | where DamageCrops > 0

and, or, ==, !=, =~, !~, <, >...

Predicates

has, !has, contains, !contains, startswith, endswith, matches... between, not-between, distinct in, !in case

Looks for a specific word Better performance Looks for any substring match

has contains

StormEvents | where EventType contains "Storm" StormEvents | where EventType has "Storm"

now

Date and Time

ago totimespan datetime_add, datetime_diff... dayofyear, dayofmonth, dayofweek...

range join union count dcount dcountif summarize make-series top sort by

• rder by

Sort and Aggregate

extend project-away project

Columns

parse mv-expand lookup format_datetime format_timespan

Format Data Commands

Demo

t h s

The Basics of KQL

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

More KQL Operators

Demo

t h s

More KQL Operators

s h s

Bind names to expressions Name can be used

• To refer to its bound value
• Within current scope

When previously defined

• Innermost let state binding used

Improve modularity

• Break complex expressions

let

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) }

let

Bind names to expressions

Advanced KQL

Machine learning Time series analysis

Advanced KQL

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

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 Analysis

Native series support

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 Series Anomaly Detection

Step by step

Time Analysis Complex Functions

series_fir series_fit_line series_periods_detect series_fit_2lines series_iir series_periods_validate

Machine Learning

Perform Root Cause Analysis

• Complex and lengthy process

Three Machine Learning plugins

• Autocluster
• Basket
• Diffpatterns

Demo

t h s

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

Demo

t h s

Querying External Tables

Data

All data

Partitioned Data

2020 2019 2018

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

Create External Table with Partition

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

https://ade.loganalytics.io/ subscriptions/ <subscription-id>/ resourcegroups/ <resource-group-name>/ providers/ microsoft.operationalinsights/ workspaces/ <workspace-name>

Log Analytics

https://ade.applicationinsights.io/ subscriptions/ <subscription-id>/ resourcegroups/ <resource-group-name>/ providers/ microsoft.insights/ components/ <ai-app-name>

Application Insights

https://aka.ms/LADemo

Flow Connector

Azure Data Explorer flow connector

• Allows to use flow capabilities
• Microsoft Power Automate

Run Kusto queries and commands

• Automatically on a schedule
• Or triggered task

Useful to send reports, notifications, control command or export/import data

Exporting Data

Kusto Explorer

• r Web UI

ingest from query .export

Client-side

Exporting Data

Service-side (pull) Service-side (push)

Demo

t h s

Exporting Data

t h s

What is a query?

• A question
• Answered with the information

available at hand Query in Data Explorer

• Using Kusto Query Language
• Web UI, Kusto Explorer, and other

products (integrations)

• Sample database available (help)

Takeaway

t h s

KQL

• Control commands
• Retrieve information
• Modify service state
• Read-only query

Basic

• Filter, date/time, sort/aggregate,

column related, and format data Advanced KQL

• Time series analysis and machine

learning

Takeaway