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
Recommend
More recommend
