Store images in Kusto and visualize them with Power BI or Azure Data Explorer Dashboards

[Hauke]

How to Visualize Images Stored in Kusto​

Introduction​

Kusto is a fast and scalable database designed to ingest, store, and analyze large volumes of structured and semi-structured data. For non-structured data like images, Azure Storage is typically the best choice. Databases can reference image data on storage via a URL, meaning images are not directly stored in Kusto. However, there are scenarios where storing image data in Kusto is beneficial. In this blog post, we will explore when it makes sense to store images in Kusto, how to store them, and how to visualize this data using Azure Data Explorer dashboards or Power BI.

​

Why store images in Kusto?​

Although Kusto doesn’t support binary data types, there are still compelling reasons to store images in Azure Data Explorer. For dashboards and reports that require images, visualization tools might not support secure access to external storage. By leveraging identities and network segregation via managed private endpoints, storing all data in one location simplifies both access and security. However, it’s important to note that Kusto the best technology for storing large-scale images.

​

How to store images in Kusto​

Kusto does not support binary data types, so images must be encoded in base64. This encoding converts the data into a non-human-readable string of 64 English characters. When storing an image in Kusto using base64, it is saved as a string.The default size limit for a string in Kusto is 1 MB (see Kusto Documentation for string datatype.) By default, all columns in Kusto are indexed. For columns storing images, you should disable indexing and may need to increase the default size limit. Below is an example of creating an image table, disabling indexing, and increasing the string size limit to 2 MB using the the BigObject encoding type:

// create image table
.create table image (file_name:string, img_original_base64 : string )

// This policy disables the index of the image column and overrides MaxValueSize property in the encoding Policy to 2 MB:
.alter column image.img_original_base64 policy encoding type='BigObject'

The maximum size for a string in Kusto is 32 MB. For more details, refer to the documentation on the encoding policy.

​

Ingest images to Kusto​

You can use all available ingestion methods for the Kusto database, depending on the deployment (PaaS or SaaS). Ensure that the image data is converted to a binary string and encoded to base64, as described in the previous section. You can find a Python example in the Gist referenced at the end of this article.

​

Display images in Azure Data Explorer Dashboards​

Once you've ingested image data into a Kusto table, you might want to visualize it using Azure Data Explorer Dashboards. Markdown visuals are an effective way to display images. Typically, images are displayed from a storage location using the following markdown pattern:

![alt text](path/to/image.png)

For images stored in Kusto, the process is similar. Instead of linking to a storage location, you use the field containing the base64-encoded string of the image. Here's how you can do it:

| extend image=strcat("![image](data:image/png;base64,", img_original_base64, ")" )

This method embeds the image directly into the dashboard using the base64-encoded string from your data. If you have multiple images to display you can make use of a function generating a markdown from a Kusto query. The function logic has been shared on stackoverflow by Daniel Dror:

.create-or-alter function with (folder = "Gold layer", docstring = "function converts a table to markdown", skipvalidation = "true") table_to_markdown(t:(*)) {
let schema = t | getschema;
let headers = schema | project ColumnName | summarize make_list(ColumnName) | extend String = strcat('| ', strcat_array(list_ColumnName, ' | '), ' |') | project String, Order=1;
let upper_divider = schema | project ColumnName, Sep = '---' | summarize Cols=make_list(Sep) | extend String = strcat('| ', strcat_array(Cols, ' | '), ' |') | project String, Order=2;
let data = t | extend Cols=pack_array(*) | extend String = strcat('| ', strcat_array(Cols, ' | '), ' |') | project String, Order=3;
headers 
| union upper_divider
| union data
| order by Order asc 
| summarize Rows=make_list(String) 
| project array_strcat(Rows, '\r\n')
}

With invoking this function, you can easily display a table of images in Azure Data Explorer dashboards. The following query is used in combination with a markdown visual:

image
| project file_name, img_original_base64
| extend ingestion_time=ingestion_time() 
| summarize arg_max(ingestion_time, *) by file_name// remove duplicates
| extend image=strcat("![image](data:image/png;base64,", img_original_base64, ")" )
| project file_name, image 
| order by file_name desc 
| invoke table_to_markdown()

This is an example how your data can be visualized using the markdown visualization:



Markdown visualization in Azure Data Explorer Dashboards

 

Display images in Power BI​

Power BI allows the integration of images from a database, a process that is well-documented in a Guy in a Cube YouTube video, which is referenced at the end of this article. By default, Power BI supports image URLs, but what if you want to display images stored as strings? Given Power BI's limitation of a 32k string size, a creative workaround is necessary. This involves splitting the strings and then reconstructing them using DAX logic, a technique thoroughly explained in the aforementioned Guy in a Cube video.

To handle large image strings that exceed Power BI's capacity, a split string function in Kusto can be employed. This function divides the image string representation into multiple rows, which is essential for visualization tools that have string size restrictions. Here's how the function looks

//helper function that splits large image string representation into several rows
//this is needed for visualization tools with limitation on string sizes
.create-or-alter function with (folder = "Gold layer", docstring="split image string representation to several rows if PowerBI string size limitation is hit", skipvalidation = "true") image_report ()
{
let max_length=32766; //maximum PowerBI string length
image
| project file_name, img_original_base64
| extend ingestion_time=ingestion_time() 
| summarize arg_max(ingestion_time, *) by file_name // remove duplicates
| extend parts = range(0, strlen(img_original_base64) - 1, max_length)
| mv-expand parts // rows needed for each substring (1 if length < max_length)
| extend img_original_base64_part = substring(img_original_base64, toint(parts), max_length), order=toint(parts)/max_length
| project file_name, img_original_base64_part, order
}

Following the split, DAX logic is used to concatenate the substrings back into the final image:

image = IF (HASONEVALUE(Images[file_name]), "data:image/png;base64, " &CONCATENATEX(Images, 'Images'[img_original_base64_part],,Images[order],ASC) )

This approach ensures that even with Power BI's string size limitations, images can be effectively displayed by leveraging Kusto's split string function and DAX's concatenation capabilities.

​

Conclusion​

The integration of images into Kusto and their visualization through Power BI or Azure Data Explorer Dashboards offers a unique approach to managing and displaying non-structured data. While Kusto is primarily designed for structured and semi-structured data, it can accommodate images through base64 encoding, albeit with some limitations due to the absence of binary data types. This method is particularly useful for dashboards and reports that require secure access to images without relying on external storage solutions.

The process involves encoding images into a base64 string, ingesting them into Kusto, and then utilizing visualization tools like Power BI to display the images. This approach ensures that all data, including images, can be securely accessed and managed in one centralized location, simplifying both access and security protocols.

However, it's crucial to recognize that Kusto is not optimized for storing large-scale images, and this method should be reserved for scenarios where the benefits outweigh the limitations. By following the guidelines and techniques outlined in this blog post, users can effectively store and visualize images within Kusto, enhancing their data analysis and reporting capabilities in a secure and efficient manner.

​

References​

• Column encoding policy
• Generate a markdown from a kusto query result, stackoverflow
• Power BI Display images in a table, matrix, or slicer in a report
• Using Images from a Data Base in Power BI, You Tube
• Gist with code examples

Continue reading...