Using KQL (kusto query language) to explore data from a local file (e.g. Excel, CSV, JSON etc.)

Question

Is there any way to use KQL to query a large local file (10k+ rows) such as Excel, CSV etc. alongside data hosted in Kusto (Azure Data Explorer)?

Here is my scenario: I extensively use KQL to explore data hosted in Kusto (Azure Data Explorer) clusters. Mostly these explorations are very dynamic and in one-off scenarios to investigate situations.

For some data, I just have Excel and CSV files that I want to join with Kusto data. I know I could do this with Pandas, but I'm specifically asking if there's any way to do it with KQL, preferably without setting up a cluster and ingesting the data into a Kusto table.

CSV is a textual data format. Excel is an application that works with multiple data formats, including CSV, but it also as its own proprietary formats, such as the xlsx binary format (which means you can't read it as a plain text). A file system has a setup which links between files suffixes` (e.g., ".csv") and applications. If your default application for ".csv" is excel, then opening a csv file will trigger the launch of Excel, which will display the file content. The CSV file has actually nothing to do with Excel and can be opened by any other application that supports the CSV format. — David דודו Markovitz
– David דודו Markovitz, Commented Nov 23, 2022 at 6:43
@DavidדודוMarkovitz Thanks, my question concerns querying any local file with tabular data using Kusto, CSV files and .xlsx files are just examples. Currently I ingest my Kusto queries and local files into Python with sqlmagic and then process them with Pandas. — Stone
– Stone, Commented Nov 23, 2022 at 19:02

Avnera · Accepted Answer · 2022-11-23 05:51:01Z

3

There are a few ways this can be done, but there is one requirement which is that the data needs to be accessible from the Kusto cluster, for your scenario, the files need to be in Azure Storage. The lightest approach is using the externaldata operator, but you can also set up an external table.

Also please note that you can get your own free cluster to do this processing, to create it go to http://aka.ms/kustofree

answered Nov 23, 2022 at 5:51

Avnera

7,68614 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using KQL (kusto query language) to explore data from a local file (e.g. Excel, CSV, JSON etc.)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related