KQL OPER

 

Understanding KQL Operators

Let's take a look at Kusto Query Language (KQL) operators and how they can help build powerful queries.

Understanding KQL Operators

Kusto Query Language (KQL) is a powerful query language that is used in a wide variety of Microsoft products, such as Azure Monitor, Log Analytics, and Microsoft Sentinel. 

Like any language, there are some core parts you need to learn to be able to use it effectively. 

Below, we’ll explore some of the most commonly used KQL operators and how they can be used to write powerful queries. 

KQL Operators

When writing queries in Kusto Query Language (KQL) operators are the building blocks.  They allow you to filter, shape, aggregate, and manipulate data efficiently, making it easy to derive insights and identify trends. 

Project

The project operator allows you to shape the results of a query output by allowing you to select or rename the columns that appear in the output. 

This is useful when you have a dataset that has a lot of columns and you only want to focus on the data from three or four columns. 

Summarize

The summarize operator can be used for aggregating data.  It performs operations such as counting, averaging, summing or finding minimum and maximum values over specified groups or the entire dataset you are querying. 

Using the StormEvents dataset I can summarize how many of each type of storm event happened. 

I could also use the summarize operator to calculate the average property damage for each combination of state and event type. 

Extend

The extend operator gives you the power to create new columns or modify existing ones.  Now remember that KQL is a read-only language, so the extend operator only affects the output results, not the original dataset. 

In the example below I am creating a new column by multiplying what is in the “TotalCost” column. 

Where

The where filter helps to give you precise results in your query, by only showing rows based on specific conditions. 

In the example below, I am filtering so that the State column equals Florida. You can also see I am using the project operator and only showing certain columns. 

Distinct

The distinct operator is used to return unique values from a specific column or a combination of columns in a dataset.  It effectively removes duplicates, showing only distinct rows based on the columns' specified. 

In the example here I only want to see the distinct enters for the EventType in the StormEvents dataset. 

Join

The join operator combines rows from two datasets based on a matching condition. 

In the example below I am joining the Products and SalesFact tables using the ProductKey column.  I am then able to build out the result that shows me the Product name, Product Key, Sales Amount and Total Cost. 

Order By

The order by operator can be used to sort the rows of a dataset based on one or more columns in either ascending or descending order.  It helps you organise your data for better analysis. 

Using the StormEvents dataset again I can order the results of my query based on the DamageProperty column in descending order and project out the columns I need to see. 

 

I can use it multiple times in my query though, I can first group by State in ascending order and then sort by Damage Property in descending order within each state.  The result is the output is organised alphabetically by state, and within each state, events are sorted by the highest damage first. 

 Render

The render operator must be the last operator in a query. It doesn't modify data instead it visualises the data in different forms. There are eleven different visualisations such as area chart, line chart, pie chart or time chart.

Conclusion

These Kusto Query Language (KQL) operators will help you write powerful queries and pull out the data you are looking for within a dataset. 

There is a lot to learn with KQL, understanding the operators is just the start.  Stay tuned for more KQL insights here!

Sign up for Techielass - A blog by Sarah Lean

Technical…Practical

Must Watch YouTube Videos for Databricks Platform Administrators

 While written word is clearly the medium of choice for this platform, sometimes a picture or a video can be worth 1,000 words.

Below are 16 videos I’d recommend for any Databricks administrators covering the topics of general platform administration, automation, networking, and security.

Administration:

• Administrator Best Practices and Tips for Future-Proofing your Databricks Account by Siddharth Bhai, Gaurav Bhatnagar, and Vicky Avison
• Best Practices for Setting up Databricks SQL at Enterprise Scale by Siddharth Bhai, Samrat Ray, and Jeremy Lewallen
• A Technical Deep Dive into Unity Catalog’s Practitioner Playbook by Zeashan Pappa
• Migration from Hive Metastore to Unity Catalog by Arthur Dooner

Automation:

• Databricks Asset Bundles: A Standard, Unified Approach to Deploying Data Products on Databricks by Rafi Kurlansik and Pieter Noordhuis
• Terraform Templates for Secure Databricks Deployments from Day Zero by JD Braun
• Getting Started with Databricks Terraform Modules by Yassine Essawabi and Youssef Mrini
• How to Setup Databricks Unity Catalog with Terraform by Himanshu Arora and Youssef Mrini
• CI/CD in Databricks with Azure DevOps by Ravi Shankar

Networking:

• Deploying a Databricks Workspace on AWS with PrivateLink by JD Braun
• Azure Databricks Networking Security by Arthur Dooner

Security:

• Security Best Practices for the Lakehouse by David Veuve
• Security Best Practices and Tools to Build a Secure Lakehouse by Anindita Mahapatra and Arun Pamulapati
• Enterprise-Grade Security and Compliance on the Databricks Data Intelligence Platform by Filippo Seracini and Alex Esibov
• Data Warehousing Performance, Scale and Security with Databricks SQL by Alex Esibov and Jeremy Lewallen
• Isolate the Lakehouse: Deploying an Isolated Architecture on Databricks by JD Braun

Administration — Videos:

Administrator Best Practices and Tips for Future-Proofing your Databricks Account by Siddharth Bhai, Gaurav Bhatnagar, and Vicky Avison

Best Practices for Setting up Databricks SQL at Enterprise Scale by Siddharth Bhai, Samrat Ray, and Jeremy Lewallen

A Technical Deep Dive into Unity Catalog’s Practitioner Playbook by Zeashan Pappa

Migration from Hive Metastore to Unity Catalog by Arthur Dooner

Automation — Videos:

Databricks Asset Bundles: A Standard, Unified Approach to Deploying Data Products on Databricks by Rafi Kurlansik and Pieter Noordhuis

Terraform Templates for Secure Databricks Deployments from Day Zero by JD Braun

Getting Started with Databricks Terraform Modules by Yassine Essawabi and Youssef Mrini

How to setup Databricks Unity Catalog with Terraform by Himanshu Arora and Youssef Mrini

CI/CD in Databricks with Azure Devops by Ravi Shankar

Networking Videos:

Deploying a Databricks Workspace on AWS with PrivateLink by JD Braun

Azure Databricks Networking Security by Arthur Dooner

Security — Videos:

Security Best Practices for the Lakehouse by David Veuve

Security Best Practices and Tools to Build a Secure Lakehouse by Anindita Mahapatra and Arun Pamulapati

Enterprise-Grade Security and Compliance on the Databricks Data Intelligence Platform by Filippo Seracini and Alex Esibov

Data Warehousing Performance, Scale and Security with Databricks SQL by Alex Esibov and Jeremy Lewallen

Isolate the Lakehouse: Deploying an Isolated Architecture on Databricks by JD Braun

Python List Tuple Dictionary Set properties table

 

Sure! Here's a comparison table highlighting the key properties of Python's main data structures: List, Tuple, Dictionary, and Set:

Property	List	Tuple	Dictionary	Set
Syntax	[ ]	( )	{ }	{ }
Ordered	Yes	Yes	Yes (from Python 3.7+)	No
Mutable	Yes	No	Yes	Yes
Duplicates Allowed	Yes	Yes	No (keys)	No
Access	By index	By index	By key	No direct access
Use Case	General-purpose collection	Fixed collection of items	Key-value pairs	Unique items
Example	[1, 2, 3]	(1, 2, 3)	{'a': 1, 'b': 2}	{1, 2, 3}

Additional Details:

• List: Ideal for ordered collections that need to be modified frequently.
• Tuple: Useful for fixed collections of items, often used for returning multiple values from functions.
• Dictionary: Best for associative arrays where you need a mapping of unique keys to values.
• Set: Perfect for collections of unique items, often used for membership testing and eliminating duplicates.

------------------------

Comprehentions

Sure! Here are examples of list and tuple comprehensions in Python:

List Comprehension

List comprehensions provide a concise way to create lists. The syntax is [expression for item in iterable if condition].

Example:

# Create a list of squares of even numbers from 0 to 9
squares = [x**2 for x in range(10) if x % 2 == 0]
print(squares)  # Output: [0, 4, 16, 36, 64]

Tuple Comprehension

Python does not have a direct syntax for tuple comprehensions. However, you can achieve similar results using generator expressions inside the tuple() function.

Example:

# Create a tuple of squares of even numbers from 0 to 9
squares = tuple(x**2 for x in range(10) if x % 2 == 0)
print(squares)  # Output: (0, 4, 16, 36, 64)

In this example, the generator expression (x**2 for x in range(10) if x % 2 == 0) is passed to the tuple() function to create a tuple[1][2].

If you have any more questions or need further examples, feel free to ask!

References

[1] Python Tuple Comprehension: Step-by-Step Guide (Examples) - codingem.com

[2] 5 Examples of Python List of Tuples - AskPython

Absolutely! Here are some more complex examples of list and tuple comprehensions in Python:

Complex List Comprehension

Let's create a list of tuples where each tuple contains a number and its square, but only for even numbers between 0 and 20.

Example:

# List of tuples (number, square) for even numbers between 0 and 20
even_squares = [(x, x**2) for x in range(21) if x % 2 == 0]
print(even_squares)
# Output: [(0, 0), (2, 4), (4, 16), (6, 36), (8, 64), (10, 100), (12, 144), (14, 196), (16, 256), (18, 324), (20, 400)]

Nested List Comprehension

Now, let's create a 3x3 matrix using nested list comprehensions.

Example:

# 3x3 matrix
matrix = [[row * col for col in range(3)] for row in range(3)]
print(matrix)
# Output: [[0, 0, 0], [0, 1, 2], [0, 2, 4]]

Complex Tuple Comprehension

As mentioned earlier, Python doesn't have direct tuple comprehensions, but we can use generator expressions within the tuple() function. Let's create a tuple of tuples where each inner tuple contains a number and its cube, but only for odd numbers between 0 and 20.

Example:

# Tuple of tuples (number, cube) for odd numbers between 0 and 20
odd_cubes = tuple((x, x**3) for x in range(21) if x % 2 != 0)
print(odd_cubes)
# Output: ((1, 1), (3, 27), (5, 125), (7, 343), (9, 729), (11, 1331), (13, 2197), (15, 3375), (17, 4913), (19, 6859))

Nested Tuple Comprehension

Let's create a tuple of tuples representing a 3x3 identity matrix using nested generator expressions.

Example:

# 3x3 identity matrix
identity_matrix = tuple(tuple(1 if row == col else 0 for col in range(3)) for row in range(3))
print(identity_matrix)
# Output: ((1, 0, 0), (0, 1, 0), (0, 0, 1))

These examples demonstrate how you can use comprehensions to create complex data structures efficiently and concisely. If you have any more questions or need further examples, feel free to ask!

Sure! Here are examples of dictionary and set comprehensions in Python:

Dictionary Comprehension

Dictionary comprehensions provide a concise way to create dictionaries. The syntax is {key: value for item in iterable if condition}.

Example 1: Creating a dictionary where keys are numbers from 1 to 5 and values are their squares.

squares = {x: x**2 for x in range(1, 6)}
print(squares)
# Output: {1: 1, 2: 4, 3: 9, 4: 16, 5: 25}

Example 2: Creating a dictionary from two lists using zip().

keys = ['a', 'b', 'c']
values = [1, 2, 3]
dictionary = {k: v for k, v in zip(keys, values)}
print(dictionary)
# Output: {'a': 1, 'b': 2, 'c': 3}

Example 3: Filtering items in a dictionary comprehension.

# Create a dictionary of even numbers and their squares from 1 to 10
even_squares = {x: x**2 for x in range(1, 11) if x % 2 == 0}
print(even_squares)
# Output: {2: 4, 4: 16, 6: 36, 8: 64, 10: 100}

Set Comprehension

Set comprehensions provide a concise way to create sets. The syntax is {expression for item in iterable if condition}.

Example 1: Creating a set of squares of numbers from 1 to 5.

squares_set = {x**2 for x in range(1, 6)}
print(squares_set)
# Output: {1, 4, 9, 16, 25}

Example 2: Creating a set from a list with duplicates removed.

numbers = [1, 2, 2, 3, 4, 4, 5]
unique_numbers = {x for x in numbers}
print(unique_numbers)
# Output: {1, 2, 3, 4, 5}

Example 3: Filtering items in a set comprehension.

# Create a set of even numbers from 1 to 10
even_numbers = {x for x in range(1, 11) if x % 2 == 0}
print(even_numbers)
# Output: {2, 4, 6, 8, 10}

These examples demonstrate how you can use comprehensions to create and manipulate dictionaries and sets efficiently. If you have any more questions or need further examples, feel free to ask!

Yes, set comprehension is definitely possible in Python! It allows you to create sets in a concise and readable way, similar to list and dictionary comprehensions. The syntax is {expression for item in iterable if condition}.

Here's a quick example to illustrate:

Example: Creating a set of squares of even numbers from 0 to 9.

even_squares = {x**2 for x in range(10) if x % 2 == 0}
print(even_squares)
# Output: {0, 4, 16, 36, 64}

In this example, the set comprehension iterates over the range of numbers from 0 to 9, squares each number, and includes it in the set only if the number is even.

If you have any more questions or need further examples, feel free to ask!

--------------------------

In Python, the * and ** operators have multiple uses depending on the context in which they are used. Here’s a breakdown of their different functionalities:

1. Multiplication and Exponentiation

• * (Multiplication): Used to multiply two numbers.

  result = 3 * 4
  print(result)  # Output: 12

• ** (Exponentiation): Used to raise a number to the power of another number.

  result = 2 ** 3
  print(result)  # Output: 8

2. Unpacking Iterables

• * (Unpacking): Used to unpack elements from iterables like lists or tuples.

  numbers = [1, 2, 3, 4]
  print(*numbers)  # Output: 1 2 3 4

• ** (Unpacking): Used to unpack key-value pairs from dictionaries.

  def print_values(a, b, c):
      print(a, b, c)

  values = {'a': 1, 'b': 2, 'c': 3}
  print_values(**values)  # Output: 1 2 3

3. Variable-Length Arguments in Functions

• *args: Used to pass a variable number of non-keyword arguments to a function.

  def sum_all(*args):
      return sum(args)

  print(sum_all(1, 2, 3, 4))  # Output: 10

• **kwargs: Used to pass a variable number of keyword arguments to a function.

  def print_details(**kwargs):
      for key, value in kwargs.items():
          print(f"{key}: {value}")

  print_details(name="Alice", age=30, city="New York")
  # Output:
  # name: Alice
  # age: 30
  # city: New York

These operators are quite powerful and versatile, making them essential tools in Python programming. If you have any more questions or need further examples, feel free to ask!

In Python, the * and ** operators are used in function definitions to allow for flexible argument passing. Here's how they work:

*args

The *args syntax is used to pass a variable number of non-keyword arguments to a function. It allows you to handle functions with an arbitrary number of arguments.

Example:

def print_numbers(*args):
    for number in args:
        print(number)

print_numbers(1, 2, 3, 4)
# Output:
# 1
# 2
# 3
# 4

In this example, *args collects all the positional arguments passed to the function into a tuple.

**kwargs

The **kwargs syntax is used to pass a variable number of keyword arguments to a function. It allows you to handle functions with an arbitrary number of keyword arguments.

Example:

def print_details(**kwargs):
    for key, value in kwargs.items():
        print(f"{key}: {value}")

print_details(name="Alice", age=30, city="New York")
# Output:
# name: Alice
# age: 30
# city: New York

In this example, **kwargs collects all the keyword arguments passed to the function into a dictionary.

Combining *args and **kwargs

You can use both *args and **kwargs in the same function to accept both positional and keyword arguments.

Example:

def print_all(*args, **kwargs):
    for arg in args:
        print(arg)
    for key, value in kwargs.items():
        print(f"{key}: {value}")

print_all(1, 2, 3, name="Alice", age=30)
# Output:
# 1
# 2
# 3
# name: Alice
# age: 30

In this example, *args collects the positional arguments into a tuple, and **kwargs collects the keyword arguments into a dictionary.

Using * and ** for Argument Unpacking

You can also use * and ** to unpack iterables and dictionaries when calling a function.

Example:

def add(a, b, c):
    return a + b + c

numbers = (1, 2, 3)
print(add(*numbers))  # Output: 6

details = {'a': 1, 'b': 2, 'c': 3}
print(add(**details))  # Output: 6

In this example, *numbers unpacks the tuple into positional arguments, and **details unpacks the dictionary into keyword arguments.

These features make Python functions highly flexible and powerful. If you have any more questions or need further examples, feel free to ask!