Lokql CLI tool

lokql - Local KQL Command-Line Tool

lokql is a command-line application for working with the Kusto Query Language (KQL) on local data files. It allows you to query CSV, JSON, Parquet, and other file formats using KQL without needing Azure Data Explorer or other cloud services.

Overview

lokql is part of the Kusto-Loco project and provides three main modes of operation:

1. Run Mode - Execute scripts and commands non-interactively
2. Explore Mode - Interactive REPL for exploring data
3. Convert Mode - Convert data files between formats

Installation

lokql is available as part of the Kusto-Loco project. Build from source or obtain the compiled binary for your platform.

Command-Line Syntax

Common Options

All modes support the following options:

Option	Description
--data <path>	Default folder to load/save data/results to (defaults to current directory)
--args <arg0> <arg1> ...	Passes arguments to scripts as arg0, arg1, etc.
-f <script> [<script2> ...]	Runs one or more script files
-c <command> [<command2> ...]	Runs one or more commands
-l <file> [<file2> ...]	Loads data files

Modes

Run Mode (Default)

Execute scripts and commands without entering interactive mode:

lokql run [options]

Explore Mode

Run scripts/commands then enter an interactive REPL:

lokql explore [options]

Convert Mode

Convert a data file from one format to another:

lokql convert -i <input> -o <output> [-f <format>]

Options:

• -i - Input file (required)
• -o - Output file (required)
• -f - Format (optional)

Interactive Commands

When in explore mode, lokql provides a rich set of commands (all commands start with a .):

Data Loading & Management

Command	Description	Example
.load <file>	Load a data file (CSV, TSV, JSON, Parquet, text)	.load data.csv .load -f c:\temp\data.csv
.load -f <file>	Force reload if table already exists	.load -f data.csv
.listtables	List all available tables	.ls or .tables
.drop <table>	Remove a table from context	.drop myTable
.rename <current> <new>	Rename an existing table	.rename oldName newName
.materialize <name>	Convert query result to permanent table	.mat summary
.addtable <name>	Add a new table	.addtable myTable
.syn <table> <synonym>	Create a synonym for a table	.syn mytable t

Query Execution

Command	Description	Example
<kql-query>	Execute a KQL query	products | summarize count() by Category
.query <file>	Run a query from a file	.query analysis.kql
.query <file> -p <prefix>	Run query with prefix	.query query.kql -p "products |"
.script <file>	Execute a script file	.script setup.kql
.macro <name> [params]	Run a defined macro	.macro myMacro param1 param2

Data Export

Command	Description	Example
.save <file>	Save last query result to file	.save output.csv
.save <file> <result>	Save named result to file	.save report.json myResult
.save -n <file>	Save without headers (CSV/text)	.save -n data.txt
.savequery <file>	Save previous query to file	.savequery myQuery.kql
.savequery <file> -c <comment>	Save query with comment	.savequery q.kql -c "Sales analysis"

Rendering & Visualization

Command	Description	Example
.render <file>	Render result as HTML and open in browser	.render chart.html
.render <file> --saveOnly	Save HTML without opening	.render report.html --saveOnly
.format	Format the display output	.format

Configuration & Settings

Command	Description	Example
.cd <path>	Change data directory path	.cd C:\mydata
.set <name> <value>	Set a configuration value	.set timeout 30
.set <name>	Remove a setting	.set timeout
.settings [filter]	Display current settings	.settings or .settings path
.knownsettings	List all known settings	.knownsettings

Results Management

Command	Description	Example
.results	Display list of stored query results	.results
.push <name>	Store current result with name	.push myResult
.pull <name>	Retrieve and display stored result	.pull myResult

Utilities

Command	Description	Example
.echo <text>	Write text to console	.echo Loading data...
.sleep [seconds]	Pause execution (default 1 second)	.sleep 5
.exit	Exit the application	.exit
.listfiles	List files in data directory	.listfiles
.fileformats	Show supported file formats	.fileformats
.getclipboard	Load data from clipboard	.getclipboard

Advanced Features

Command	Description	Example
.adx	Query Azure Data Explorer	.adx <connection>
.appinsights	Query Application Insights	.appinsights <workspace>
.loganalytics	Query Log Analytics	.loganalytics <workspace>
.defender	Query Microsoft Defender	.defender
.arg	Query Azure Resource Graph	.arg
.copilot	AI assistance features	.copilot
.geteventlog	Get Windows event log data	.geteventlog
.pivotColumnsToRows	Pivot columns to rows	See help for details
.pivotRowsToColumns	Pivot rows to columns	See help for details
.startreport pptx <template>	Start building a PowerPoint report	.startreport pptx template.pptx
.addtoreport	Add content to report	.addtoreport
.finishreport	Complete and save report	.finishreport

Usage Examples

Example 1: Quick Data Exploration

Load a CSV file and run a simple query:

# Non-interactive
lokql run -l sales.csv -c "sales | summarize total=sum(Amount) by Category"

# Interactive mode
lokql explore -l sales.csv

In explore mode:

KQL> sales | take 10
KQL> sales | summarize Revenue=sum(Amount) by Region | order by Revenue desc

Example 2: Multi-File Analysis

Load multiple files and join them:

lokql explore -l products.csv -l reviews.csv

Then in the REPL:

KQL> products \
   > | join reviews on ProductId \
   > | summarize AvgRating=avg(Score) by Category \
   > | render columnchart

Example 3: Using Scripts

Create a script file analysis.kql:

.echo Loading data files...
.load products.csv
.load sales.csv

.echo Running analysis...
sales 
| join products on ProductId
| summarize TotalRevenue=sum(Amount) by Category
| order by TotalRevenue desc
| take 10

Run the script:

lokql run -f analysis.kql --data C:\mydata

Example 4: Data Conversion

Convert CSV to Parquet:

lokql convert -i data.csv -o data.parquet

Convert JSON to CSV:

lokql convert -i data.json -o data.csv

Example 5: Passing Arguments

Create a parameterized script filtered-analysis.kql:

products 
| where Category == '$arg0'
| summarize count() by SubCategory

Run with arguments:

lokql run -f filtered-analysis.kql --args Electronics

Example 6: Complex Pipeline

lokql explore --data /data/sales \
  -l transactions.csv \
  -l customers.csv \
  -c "transactions | take 5" \
  -c ".push initialData"

Example 7: Materializing Results

In explore mode:

KQL> products | where Price > 100
KQL> .materialize expensive_products
KQL> expensive_products | summarize count() by Category

Example 8: Saving and Reusing Queries

KQL> products | summarize count() by Category | order by count_
KQL> .savequery category-counts.kql -c "Count products by category"
KQL> .render category-chart.html

Later, reuse the query:

KQL> .query category-counts.kql

Example 9: Working with Multiple Formats

KQL> .load data.json
KQL> .load data.parquet  
KQL> .load data.csv
KQL> .listtables
KQL> data_json | union data_parquet | union data_csv | summarize count()

Example 10: Generating Reports

lokql explore -l sales.csv

KQL> sales | summarize Revenue=sum(Amount) by Region | render columnchart
KQL> .save q1-sales.html
KQL> sales | where Region == "North" | take 100
KQL> .save north-details.csv

Tips & Tricks

Line Continuation

Use \ or | at the end of a line to continue to the next line in interactive mode:

KQL> products \
   > | where Category == 'Electronics' \
   > | summarize count()

Using Settings Variables

Access settings in queries using the arg0, arg1 syntax set via --args:

lokql run -f query.kql --args 2024 Electronics

In query:

sales 
| where Year == toint($arg0)
| where Category == '$arg1'

File Path Handling

• If path is not rooted, files are searched for in the path set by --data or .cd
• Use quotes for paths with spaces: .load "my data.csv"

Table Name Conventions

• Table names default to the filename without extension
• Use .load -as <name> to specify a custom table name
• Special characters in table names are automatically escaped

Getting Help

In explore mode, use .help to see available commands and their descriptions.

Supported File Formats

lokql supports:

• CSV (.csv)
• TSV (.tsv)
• JSON (.json)
• Parquet (.parquet)
• Text (.txt) - Creates a single "Line" column
• Excel (.xlsx, .xls) - via .loadexcel command

File format is typically detected from extension, but can be explicitly specified when needed.

KQL Query Support

lokql supports a comprehensive set of KQL operators and functions including:

• Filtering: where, take, limit
• Projection: project, extend, distinct
• Aggregation: summarize, count, sum, avg, min, max
• Sorting: order by, sort by, top
• Joins: join, union
• Time series: bin, datetime functions
• String operations: String manipulation and pattern matching
• Rendering: render with various chart types (columnchart, piechart, linechart, etc.)

See the main Kusto-Loco documentation for complete KQL operator support.

Exit Codes

• 0 - Success
• Non-zero - Error occurred

See Also

• LokqlDX - GUI application for data exploration
• Kusto-Loco Documentation
• KQL Quick Reference
• Powershell Integration

Contributing

Contributions are welcome! See the Contributing page for details.

License

See the main repository for license information.