Skip to content

Puchaczov/Musoq.CLI

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

34 Commits
scripts
scripts
 
 
LICENSE
LICENSE
 
 
README.md
README.md
 
 

Repository files navigation

Musoq CLI Cookbook

GitHub license

Musoq.CLI is a powerful command-line interface that brings the magic of Musoq to your fingertips. Query various data sources with ease, wherever they reside!

🌟 Features

  • 🖥️ Spin up a Musoq server
  • 🔍 Query diverse data sources
  • 🔄 Seamless server-client interaction
  • 📊 Multiple output formats (Raw, CSV, JSON, Interpreted JSON, Yaml, Interpreted Yaml)
  • 🚫 No additional dependencies required

🚀 Easy Install / Update / Remove

Install / Update

Powershell:

irm https://raw.githubusercontent.com/Puchaczov/Musoq.CLI/refs/heads/main/scripts/powershell/install.ps1 | iex

Shell using curl:

curl -fsSL https://raw.githubusercontent.com/Puchaczov/Musoq.CLI/refs/heads/main/scripts/bash/install.sh | sudo bash

Shell using wget:

wget -qO- https://raw.githubusercontent.com/Puchaczov/Musoq.CLI/refs/heads/main/scripts/bash/install.sh | sudo bash

Remove

Powershell:

irm https://raw.githubusercontent.com/Puchaczov/Musoq.CLI/refs/heads/main/scripts/powershell/remove.ps1 | iex

Shell using curl:

curl -fsSL https://raw.githubusercontent.com/Puchaczov/Musoq.CLI/refs/heads/main/scripts/bash/remove.sh | sudo bash

Shell using wget:

wget -qO- https://raw.githubusercontent.com/Puchaczov/Musoq.CLI/refs/heads/main/scripts/bash/remove.sh | sudo sh

🏃 Quick Start

With Server In Background

  1. 📥 Install Musoq.CLI using the easy installation script above
  2. 🖥️ Open any terminal
  3. 🏃‍♂️ Run the server in background:
    • Windows & Linux: Musoq serve
  4. 🔍 Run queries as needed
  5. 🛑 To quit the server: Musoq quit

With Server In Foreground

  1. 📥 Install Musoq.CLI using the easy installation script above
  2. 🖥️ Open one terminal and run the server:
    • Windows & Linux: Musoq serve --wait-until-exit
  3. 🖥️ Open another terminal
  4. 🔍 Run a query:
    • Windows & Linux: Musoq run query "select 1 from #system.dual()"
  5. 🛑 To quit the server: Musoq quit

Table of Contents

Quick Reference

Common Commands

Task Command
Run simple query musoq run "SELECT 1 FROM #system.dual()"
Run query as JSON musoq run "SELECT 1 FROM #system.dual()" --format json
Run query as CSV musoq run "SELECT 1 FROM #system.dual()" --format csv
Run query from file musoq run query.sql
Run query with command execution musoq run "SELECT 1 as Test FROM #system.dual()" --execute "powershell -command 1+2"
Process piped text echo "data" | musoq run "SELECT * FROM #stdin.TextBlock()"
Flatten JSON from stdin echo '{"key":"value"}' | musoq run "SELECT * FROM #stdin.JsonFlat()"
Flatten YAML from stdin echo 'key: value' | musoq run "SELECT * FROM #stdin.YamlFlat()"
Convert YAML to JSON echo 'key: value' | musoq run "SELECT y.Path, y.Value FROM #stdin.YamlFlat() y" --format interpreted_json
Convert JSON to YAML echo '{"key":"value"}' | musoq run "SELECT j.Path, j.Value FROM #stdin.JsonFlat() j" --format yaml
Set environment variable musoq set environment-variable "VAR_NAME" "value"
Set agent name musoq set agent-name "my-agent"
List tools musoq tool list
Show tool details musoq tool show tool-name
Execute tool musoq tool execute tool-name --param1 value1
List Python plugins musoq python list
Create Python plugin musoq python create plugin_name [template]
Read Python plugin musoq python read plugin_name
Rename Python plugin musoq python update old_name new_name
Delete Python plugin musoq python delete plugin_name
Show plugins folder musoq python folder
Get data sources musoq get data-sources
Check server status musoq get is-running
Get environment variables musoq get environment-variables
Create bucket musoq bucket create bucket-name
View query history musoq log
Encode image musoq image encode file.png
Start server musoq serve
Start server (foreground) musoq serve --wait-until-exit
Stop server musoq quit

Output Formats

Format Flag Options Use Case
Table (default) None or --format table --execute Human-readable output
JSON --format json --execute API integration, parsing
CSV --format csv --unquoted, --no-header, --execute Excel, data analysis
Raw --format raw --execute Debugging, type inspection

Note: The --execute option works with all output formats to post-process results with shell commands.

Python Plugin Templates

Template Use Case
basic Simple data sources with static or computed data
api REST API-based data sources

Configuration Commands

Category Set Command Clear Command
Environment Variable set environment-variable NAME VALUE clear environment-variable NAME
Log Path set log-path PATH clear log-path

Common Data Sources

Data Source Syntax Example
System dual #system.dual() SELECT 1 FROM #system.dual()
Files #system.files(path, recursive) SELECT Name FROM #system.files('C:\\', false)
Directories #system.directory(path, recursive) SELECT Name FROM #system.directory('C:\\', false)
Stdin text #stdin.TextBlock() SELECT Value FROM #stdin.TextBlock()
Stdin regex #stdin.Regex(pattern) SELECT r.name, r.age FROM #stdin.Regex('(?<name>\w+)\s+(?<age>\d+)') r
Stdin JSON #stdin.JsonFlat() SELECT j.Path, j.Value FROM #stdin.JsonFlat() j
Stdin YAML #stdin.YamlFlat() SELECT y.Path, y.Value FROM #stdin.YamlFlat() y
Python plugin #schema.datasource() SELECT * FROM #mydata.items()

File Locations

Resource Default Location
Settings file ~/.musoq/settings.json
Python plugins ~/.musoq/Python/Scripts/
Environment variables ~/.musoq/appsettings.json
Tools ~/.musoq/Tools/

Server Management

Start Server

Start the local Musoq agent server in the background.

Command (background mode):

musoq serve

Command (foreground mode - wait until exit):

musoq serve --wait-until-exit
  • serve: Starts the local agent API server as a background process. The server handles query execution, plugin management, and all other operations. The command returns immediately after starting the server.
  • serve --wait-until-exit: Starts the server in foreground mode and blocks until the server is explicitly stopped. Useful for debugging or when you want to keep the server running in a terminal session.

Note: Most CLI commands require the server to be running. The server starts automatically when needed in many scenarios.

Stop Server

Gracefully shut down the running server.

Command:

musoq quit

Expected Output:

Server shutdown initiated

Query Execution - Basics

Basic Query Execution

Execute SQL queries directly from the command line.

Command:

musoq run "select 20001 from #system.dual()"

Expected Output:

┌───────┐
│ 20001 │
├───────┤
│ 20001 │
└───────┘

Output Formats

Musoq supports multiple output formats for different use cases.

JSON Format

Command:

musoq run "select 20002 from #system.dual()" --format json

Expected Output:

[{"20002":20002}]

CSV Format

Command:

musoq run "select 20003 from #system.dual()" --format csv

Expected Output:

20003
20003

CSV Options:

# Unquoted output (no quotes around string values)
musoq run "select 'text' from #system.dual()" --format csv --unquoted

# No header row
musoq run "select 20003 from #system.dual()" --format csv --no-header

Raw Format

Command:

musoq run "select 20004 from #system.dual()" --format raw

Expected Output:

Columns:
[{"name":"20004","type":"System.Int32","order":0}]
Rows:
[[{"value":20004}]]

Interpreted JSON Format

Command:

echo '{"name":"Alice","age":30}' | musoq run "
  SELECT j.Path, j.Value 
  FROM #stdin.JsonFlat() j
" --format interpreted_json

Expected Output:

[{"name":"Alice","age":30}]

Reconstructed JSON Format

Command:

echo '{"user":{"name":"Alice"}}' | musoq run "
  SELECT j.Path as Path, j.Value as Value
  FROM #stdin.JsonFlat() j
" --format reconstructed_json

Expected Output:

{"user":{"name":"Alice"}}

Interpreted YAML Format

Command:

echo '{"name":"Alice","age":30}' | musoq run "
  SELECT j.Path, j.Value 
  FROM #stdin.JsonFlat() j
" --format interpreted_yaml

Expected Output:

- name: Alice
  age: 30

Reconstructed YAML Format

Command:

echo '[{"name":"Alice"},{"name":"Bob"}]' | musoq run "
  SELECT j.Path as Path, j.Value as Value
  FROM #stdin.JsonFlat() j
" --format reconstructed_yaml

Expected Output:

- name: Alice
- name: Bob

Expression-Only Queries

Execute simple expressions without requiring FROM clause syntax.

Command:

musoq run "1 + 2"

Expected Output:

┌───────┐
│ 1 + 2 │
├───────┤
│ 3     │
└───────┘

Running Queries from Files

Execute SQL queries stored in files for better organization and reusability.

Command:

musoq run path/to/query.sql

Example query.sql:

select 20006 from #system.dual()

Expected Output:

┌───────┐
│ 20006 │
├───────┤
│ 20006 │
└───────┘

Information Retrieval

Get Data Sources

List all available data sources and their versions.

Command:

musoq get data-sources

Expected Output:

Name,Version,FullName
api,1.1.0.0,Musoq.Cloud.DataSources.ExternalApi
memorymapped,1.1.0.0,Musoq.Cloud.DataSources.MemoryMapped
system,6.2.15.0,Musoq.DataSources.System

Get Server Version

Check the version of the running Musoq server.

Command:

musoq get server-version

Expected Output:

Server version: 1.0.0

Get Environment Variables

List all configured environment variables used by plugins.

Show Masked Values (default)

Command:

musoq get environment-variables

Expected Output:

Name,Value,Assemblies
TEST_VAR,***cretValue42,"Sample.Tools.Assembly"
API_KEY,***123,"Musoq.Cloud.DataSources.ExternalApi"

Show Sensitive Values

Command:

musoq get environment-variables --show-sensitive true

Expected Output:

Name,Value,Assemblies
TEST_VAR,SecretValue42,"Sample.Tools.Assembly"
API_KEY,sk-abc123,"Musoq.Cloud.DataSources.ExternalApi"

Get Environment Variables File Path

Display the path to the environment variables configuration file.

Command:

musoq get environment-variables-file-path

Expected Output:

~/.musoq/appsettings.json

Check Server Status

Verify if the Musoq server is currently running.

Command:

musoq get is-running

Expected Output (when running):

Server is up and running

Expected Output (when not running):

Server is not running

Exit codes:

  • 0 - Server is running
  • 1 - Server is not running

Get Server Port

Retrieve the port number the server is listening on.

Command:

musoq get server-port

Expected Output:

5000

Get Licenses

Display license information for dependencies.

Command:

musoq get licenses

Local Configuration

Setting Environment Variables

Set environment variables for data source plugins and tools.

Command:

musoq set environment-variable --name "API_TOKEN" --value "secret-token-value"

Expected Output:

Environment variable set successfully

Advanced Query Features

Piped Text Processing

Process text data piped from stdin using Musoq queries.

Read and Split Text by Lines

Command:

echo "Line 1
Line 2
Line 3" | musoq run "select s.Value from #stdin.TextBlock() t cross apply t.SplitByNewLines(t.Value) s" --format csv

Expected Output:

s.Value
"Line 1"
"Line 2"
"Line 3"

Filter Text by Length

Command:

echo "Short
This is a much longer line of text
Medium line here" | musoq run "select s.Value, Length(s.Value) as Len from #stdin.TextBlock() t cross apply t.SplitByNewLines(t.Value) s where Length(s.Value) > 20" --format csv

Expected Output:

s.Value,Len
"This is a much longer line of text",34

Count Lines

Command:

echo "Line 1
Line 2
Line 3" | musoq run "select Count(s.Value) as LineCount from #stdin.TextBlock() t cross apply t.SplitByNewLines(t.Value) s" --format csv

Expected Output:

LineCount
3

Regex Pattern Matching

Extract with Named Capture Groups

Extract structured data from text using regex patterns with named capture groups.

Command:

echo "John 30
Alice 25
Bob 35" | musoq run "select r.name, r.age from #stdin.Regex('(?<name>\w+)\s+(?<age>\d+)') r" --format csv

Expected Output:

r.name,r.age
"John","30"
"Alice","25"
"Bob","35"

Extract with Unnamed Groups

Use unnamed capture groups when you don't need custom column names.

Command:

echo "John 30
Alice 25" | musoq run "select r.column1, r.column2 from #stdin.Regex('(\w+)\s+(\d+)') r" --format csv

Expected Output:

r.column1,r.column2
John,30
Alice,25

Extract Email Addresses

Find and extract email addresses from text.

Command:

echo "Contact [email protected] for details
Reach out to [email protected]
Email [email protected]" | musoq run "select r.email from #stdin.Regex('(?<email>[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})') r" --format csv

Expected Output:

r.email
[email protected]
[email protected]
[email protected]

Parse Log Files

Extract structured information from log file entries.

Command:

echo "2024-01-01 10:30:00 ERROR Something went wrong
2024-01-01 10:31:00 INFO Process started
2024-01-01 10:32:00 WARN Memory low" | musoq run "select r.timestamp, r.level, r.message from #stdin.Regex('(?<timestamp>\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2})\s+(?<level>\w+)\s+(?<message>.+)') r" --format csv

Expected Output:

r.timestamp,r.level,r.message
"2024-01-01 10:30:00",ERROR,Something went wrong
"2024-01-01 10:31:00",INFO,Process started
"2024-01-01 10:32:00",WARN,Memory low

Filter Regex Results

Combine regex extraction with SQL filtering.

Command:

echo "John 30
Alice 25
Bob 35
Charlie 28" | musoq run "select r.name, r.age from #stdin.Regex('(?<name>\w+)\s+(?<age>\d+)') r where ToInt32(r.age) > 28" --format csv

Expected Output:

r.name,r.age
"John","30"
"Bob","35"

Extract Multiple Matches Per Line

Find all occurrences of a pattern within text, even multiple matches on the same line.

Command:

echo "The prices are $10.50 and $25.99 today" | musoq run "select r.price from #stdin.Regex('\$(?<price>\d+\.\d+)') r" --format csv

Expected Output:

r.price
10.50
25.99

Parse URLs

Extract components from URLs using regex.

Command:

echo "Visit https://www.example.com/path
Check http://test.org/page
See https://github.com/user/repo" | musoq run "select r.protocol, r.domain from #stdin.Regex('(?<protocol>https?)://(?<domain>[^/]+)') r" --format csv

Expected Output:

r.protocol,r.domain
"https","www.example.com"
"http","test.org"
"https","github.com"

Aggregate Regex Results

Perform SQL aggregations on extracted data.

Command:

echo "John 30
Alice 25
Bob 35
Charlie 28" | musoq run "select Count(r.name) as TotalCount from #stdin.Regex('(?<name>\w+)\s+(?<age>\d+)') r" --format csv

Expected Output:

TotalCount
4

Mix Named and Unnamed Groups

Combine named and unnamed capture groups in the same pattern.

Command:

echo "John 30 Developer
Alice 25 Manager" | musoq run "select r.name, r.column2, r.role from #stdin.Regex('(?<name>\w+)\s+(\d+)\s+(?<role>\w+)') r" --format csv

Expected Output:

r.name,r.column2,r.role
|"John","30","Developer"
"Alice","25","Manager"

Note: All extracted values are strings. Use conversion functions like ToInt32(), ToDateTime(), etc., when you need to perform operations requiring specific types.

JSON Flattening and Processing

Basic JSON Flattening

Flatten JSON structures into path/value pairs for easy querying and manipulation.

Command:

echo '{"user": {"name": "Alice", "age": 30}, "tags": ["dev", "ops"]}' | musoq run "SELECT Path, Value FROM #stdin.JsonFlat() j" --format csv

Expected Output:

j.Path,j.Value
"user.name","Alice"
"user.age","30"
"tags[0]","dev"
"tags[1]","ops"

Filter Object Properties

Select specific properties from JSON objects while excluding others.

Command:

echo '{"id":123,"name":"Alice","password":"secret","email":"[email protected]"}' | musoq run "
  SELECT j.Path, j.Value 
  FROM #stdin.JsonFlat() j 
  WHERE j.Path LIKE 'id' OR j.Path LIKE 'name' OR j.Path LIKE 'email'
" --format interpreted_json

Expected Output:

{"id":123,"name":"Alice","email":"[email protected]"}

Filter Array Elements

Filter elements from JSON arrays based on conditions.

Command:

echo '{"items":[0,5,10]}' | musoq run "
  SELECT j.Path, j.Value 
  FROM #stdin.JsonFlat() j 
  WHERE j.Value <> '5'
" --format reconstructed_json

Expected Output:

{"items":[0,10]}

Modify JSON Values

Transform values while maintaining JSON structure.

Command:

echo '{"name":"Alice","age":30,"city":"New York"}' | musoq run "
  SELECT j.Path, 
    CASE WHEN j.Path = 'age' THEN '35' ELSE j.Value END as Value
  FROM #stdin.JsonFlat() j
" --format reconstructed_json

Expected Output:

{"name":"Alice","age":35,"city":"New York"}

Filter Nested Object Properties

Remove sensitive data from nested JSON structures.

Command:

echo '{"user":{"name":"Bob","email":"[email protected]","password":"secret123"},"timestamp":"2024-01-01"}' | musoq run "
  SELECT j.Path, j.Value
  FROM #stdin.JsonFlat() j
  WHERE j.Path LIKE '%name%' OR j.Path LIKE '%email%' OR j.Path LIKE 'timestamp'
" --format reconstructed_json

Expected Output:

{"user":{"name":"Bob","email":"[email protected]"},"timestamp":"2024-01-01"}

Modify Array Elements

Transform values within arrays.

Command:

echo '{"numbers":[10,20,30]}' | musoq run "
  SELECT j.Path,
    CASE 
      WHEN Contains(j.Path, 'numbers[') THEN ToString(ToInt32(j.Value) * 2)
      ELSE j.Value
    END as Value
  FROM #stdin.JsonFlat() j
" --format reconstructed_json

Expected Output:

{"numbers":[20,40,60]}

Query Nested Arrays

Work with complex nested structures containing arrays.

Command:

echo '{"users":[{"name":"Alice","scores":[85,90,95]},{"name":"Bob","scores":[70,75,80]}]}' | musoq run "
  SELECT j.Path, j.Value
  FROM #stdin.JsonFlat() j
  WHERE j.Path LIKE '%scores%'
" --format csv

Expected Output:

j.Path,j.Value
"users[0].scores[0]","85"
"users[0].scores[1]","90"
"users[0].scores[2]","95"
"users[1].scores[0]","70"
"users[1].scores[1]","75"
"users[1].scores[2]","80"

Property Renaming

Rename properties while filtering.

Command:

echo '{"user_name":"Alice","user_email":"[email protected]","user_age":30}' | musoq run "
  SELECT 
    CASE 
      WHEN j.Path = 'user_name' THEN 'name'
      WHEN j.Path = 'user_email' THEN 'email'
      ELSE j.Path
    END as Path,
    j.Value
  FROM #stdin.JsonFlat() j
  WHERE j.Path LIKE 'user_name' OR j.Path LIKE 'user_email'
" --format reconstructed_json

Expected Output:

{"name":"Alice","email":"[email protected]"}

YAML Flattening and Processing

Basic YAML Flattening

Flatten YAML structures into path/value pairs for easy querying and manipulation.

Command:

echo 'user:
  name: Alice
  age: 30
tags:
  - dev
  - ops' | musoq run "SELECT y.Path, y.Value FROM #stdin.YamlFlat() y" --format csv

Expected Output:

y.Path,y.Value
"user.name",Alice
"user.age",30
"tags[0]",dev
"tags[1]",ops

Convert YAML to JSON

Transform YAML data into JSON format using interpreted_json output.

Command:

echo 'user:
  name: Alice
  age: 30
  email: [email protected]
active: true' | musoq run "
  SELECT y.Path, y.Value 
  FROM #stdin.YamlFlat() y
" --format interpreted_json

Expected Output:

[{"user":{"name":"Alice","age":30,"email":"[email protected]"},"active":true}]

Convert JSON to YAML

Transform JSON data into YAML format.

Command:

echo '{"user":{"name":"Bob","age":25,"email":"[email protected]"},"active":false}' | musoq run "
  SELECT j.Path, j.Value
  FROM #stdin.JsonFlat() j
" --format yaml

Expected Output:

- j.Path: user.name
  j.Value: Bob
- j.Path: user.age
  j.Value: 25
- j.Path: user.email
  j.Value: [email protected]
- j.Path: active
  j.Value: false

Filter YAML Properties

Select specific properties from YAML objects while excluding others.

Command:

echo 'id: 123
name: Alice
password: secret
email: [email protected]' | musoq run "
  SELECT y.Path, y.Value 
  FROM #stdin.YamlFlat() y 
  WHERE y.Path LIKE 'id' OR y.Path LIKE 'name' OR y.Path LIKE 'email'
" --format interpreted_json

Expected Output:

[{"id":123,"name":"Alice","email":"[email protected]"}]

Modify YAML Array Values

Transform values within YAML arrays.

Command:

echo 'numbers:
  - 10
  - 20
  - 30' | musoq run "
  SELECT y.Path,
    CASE 
      WHEN Contains(y.Path, 'numbers[') THEN ToString(ToInt32(y.Value) * 2)
      ELSE y.Value
    END as Value
  FROM #stdin.YamlFlat() y
" --format interpreted_json

Expected Output:

[{"numbers":[20,40,60]}]

Query Nested YAML Arrays

Work with complex nested YAML structures containing arrays.

Command:

echo 'users:
  - name: Alice
    scores:
      - 85
      - 90
      - 95
  - name: Bob
    scores:
      - 70
      - 75
      - 80' | musoq run "
  SELECT y.Path, y.Value
  FROM #stdin.YamlFlat() y
  WHERE y.Path LIKE '%scores%'
" --format csv

Expected Output:

y.Path,y.Value
"users[0].scores[0]",85
"users[0].scores[1]",90
"users[0].scores[2]",95
"users[1].scores[0]",70
"users[1].scores[1]",75
"users[1].scores[2]",80

Convert YAML Arrays to JSON

Transform YAML arrays containing objects into JSON format.

Command:

echo 'users:
  - name: Alice
    role: Developer
  - name: Bob
    role: Manager' | musoq run "
  SELECT y.Path, y.Value
  FROM #stdin.YamlFlat() y
" --format interpreted_json

Expected Output:

[{"users":[{"name":"Alice","role":"Developer"},{"name":"Bob","role":"Manager"}]}]

Count YAML Properties

Perform aggregate operations on flattened YAML.

Command:

echo 'name: Alice
age: 30
city: New York' | musoq run "
  SELECT Count(y.Path) as PropertyCount 
  FROM #stdin.YamlFlat() y
" --format csv

Expected Output:

PropertyCount
3

Convert YAML to JSON (Reconstructed Format)

Convert YAML to JSON preserving exact structure using reconstructed_json format.

Command:

echo 'user:
  name: Alice
  age: 30
  email: [email protected]
active: true' | musoq run "
  SELECT y.Path as Path, y.Value as Value
  FROM #stdin.YamlFlat() y
" --format reconstructed_json

Expected Output:

{"user":{"name":"Alice","age":30,"email":"[email protected]"},"active":true}

Convert JSON to YAML (Reconstructed Format)

Convert JSON to YAML preserving exact structure using reconstructed_yaml format.

Command:

echo '{"user":{"name":"Bob","age":25,"email":"[email protected]"},"active":false}' | musoq run "
  SELECT j.Path as Path, j.Value as Value
  FROM #stdin.JsonFlat() j
" --format reconstructed_yaml

Expected Output:

user:
  name: Bob
  age: 25
  email: [email protected]
active: false

Convert Root-Level JSON Array to YAML

Convert JSON arrays at root level to YAML, preserving array structure.

Command:

echo '[{"name":"Alice","age":30},{"name":"Bob","age":25}]' | musoq run "
  SELECT j.Path as Path, j.Value as Value
  FROM #stdin.JsonFlat() j
" --format reconstructed_yaml

Expected Output:

- name: Alice
  age: 30
- name: Bob
  age: 25

Convert Root-Level YAML Array to JSON

Convert YAML arrays at root level to JSON, preserving array structure.

Command:

echo '- name: Alice
  age: 30
- name: Bob
  age: 25' | musoq run "
  SELECT y.Path as Path, y.Value as Value
  FROM #stdin.YamlFlat() y
" --format reconstructed_json

Expected Output:

[{"name":"Alice","age":30},{"name":"Bob","age":25}]

Round-Trip JSON Array Conversion

Verify that JSON arrays can be flattened and reconstructed without loss.

Command:

echo '[{"name":"Alice","age":30},{"name":"Bob","age":25}]' | musoq run "
  SELECT j.Path as Path, j.Value as Value
  FROM #stdin.JsonFlat() j
" --format reconstructed_json

Expected Output:

[{"name":"Alice","age":30},{"name":"Bob","age":25}]

Post-Processing with --execute

Execute shell commands for each row of query results using template variables. The --execute option allows you to process query output with external commands or scripts.

Basic Command Execution

Command:

musoq run "select 1 as Test from #system.dual()" --execute "powershell -command 1+2"

Using Template Variables

Template variables use the {{ column_name }} syntax to access values from query result columns.

Command:

musoq run "select 'John' as name, 30 as age from #system.dual()" --execute "echo Hello {{ name }}, you are {{ age }} years old" --format csv

Expected Output:

name,age,Expression,Result
John,30,"echo Hello John, you are 30 years old","Hello John, you are 30 years old"
  • Executes a command template for each row
  • Replaces {{ name }} with "John" and {{ age }} with "30"
  • Adds the evaluated expression and its result as new columns
  • Works only with table and csv format

File System Operations

Command (Windows):

musoq run "select 'test.txt' as filename from #system.dual()" --execute "powershell -command Test-Path {{ filename }}" --format json

Command (Linux/macOS):

musoq run "select 'test.txt' as filename from #system.dual()" --execute "test -f {{ filename }} && echo true || echo false" --format json

Processing Multiple Rows

Command:

musoq run "
  select s.Value as line 
  from #stdin.TextBlock() t 
  cross apply t.SplitByNewLines(t.Value) s
" --execute "echo Processing: {{ line }}" --format csv < input.txt
  • Reads lines from stdin
  • Executes the echo command for each line
  • Shows both original data and execution results

Case-Insensitive Variable Matching

Template variables are case-insensitive - {{ Name }}, {{ name }}, and {{ NAME }} all reference the same column.

Command:

musoq run "select 'Alice' as Name from #system.dual()" --execute "echo Hello {{ name }}" --format json

Working with Different Output Formats

The --execute option works with all output formats:

JSON Format:

musoq run "select 'test' as value from #system.dual()" --execute "echo {{ value }}" --format json

CSV Format:

musoq run "select 'test' as value from #system.dual()" --execute "echo {{ value }}" --format csv

Table Format:

musoq run "select 'test' as value from #system.dual()" --execute "echo {{ value }}"

Raw Format:

musoq run "select 'test' as value from #system.dual()" --execute "echo {{ value }}" --format raw

Platform-Specific Commands

Commands are executed using the appropriate shell for your platform:

Windows (PowerShell):

musoq run "select 'C:\temp' as path from #system.dual()" --execute "powershell -command Get-ChildItem {{ path }}"

Linux/macOS (sh):

musoq run "select '/tmp' as path from #system.dual()" --execute "ls -la {{ path }}"

Important Notes

  1. Variable Substitution: Missing variables in templates remain as {{ variable_name }} in the output
  2. Error Handling: Command execution errors are captured in the Result column
  3. Performance: Commands execute sequentially for each row - consider performance for large datasets
  4. Shell Syntax: Commands use the native shell syntax for your platform (cmd.exe on Windows, /bin/sh on Linux/macOS)
  5. Escaping: Values are substituted as-is - be careful with special characters in shell commands

Bucket Management

Create Bucket

Create a named storage bucket for query results.

Command:

musoq bucket create my-bucket

Expected Output:

Bucket 'my-bucket' created successfully

Delete Bucket

Remove a bucket and its contents.

Command:

musoq bucket delete my-bucket

Expected Output:

Bucket 'my-bucket' deleted successfully

Using Buckets in Queries

Store query results in a bucket for later retrieval.

Step 1: Create the bucket

musoq bucket create my-results

Step 2: Run query with bucket

musoq run "select 30001 from #system.dual()" --bucket my-results

Expected Output:

┌───────┐
│ 30001 │
├───────┤
│ 30001 │
└───────┘

Query with Bucket Storage

Runs a query agains data stored in a specified bucket.

Command:

musoq run "select 20005 from #system.dual()" --bucket test-bucket

Expected Output:

┌───────┐
│ 20005 │
├───────┤
│ 20005 │
└───────┘

Python Plugin Management

Note: Python plugins use project-based architecture. Each plugin is a directory containing main.py and optional supporting files like requirements.txt.

List Python Plugins

View all available Python data source plugin projects.

Command:

musoq python list

Expected Output (when no plugins exist):

Name,Description,Created,Modified

Expected Output (with plugins):

Name,Description,Created,Modified
alpha-script,Alpha script for testing,2024-10-21 10:30:00,2024-10-21 10:30:00
beta-script,Beta script for testing,2024-10-21 10:35:00,2024-10-21 10:35:00

Read Python Plugin

Display the contents of a Python plugin project's main.py file.

Command:

musoq python read my_plugin

Expected Output:

# Project: my_plugin
# Description: My plugin description
# Created: 2024-10-21 10:30:00
# Modified: 2024-10-21 10:30:00

class DataPlugin:
    def schema_name(self):
        return "mydata"
    # ... rest of plugin code

Create Python Plugin

Create a new Python plugin project from a template.

Command:

musoq python create my_plugin

With specific template:

musoq python create my_plugin basic
musoq python create my_plugin api

Expected Output:

Project 'my_plugin' created successfully from template 'basic'.

~/.musoq/Python/Scripts/my_plugin/
├── main.py          # Plugin implementation (from template)
├── requirements.txt # Optional: Python dependencies
└── project.json     # Optional: Project metadata

The project folder is automatically opened in your system's default file explorer.

Available templates:

  • basic (default): Simple data source template
  • api: Template for API-based data sources

Plugin structure example (v.2):

"""
Basic Python Plugin Template (v.2)

This template provides the simplest structure for a Musoq Python plugin.
It demonstrates a single data source with basic columns.

SQL Usage:
    SELECT * FROM #{schema_name}.items()
    SELECT * FROM #{schema_name}.items(10)  -- With minimum_id parameter
"""

class DataPlugin:
    """Basic plugin with a single data source."""
    
    def schema_name(self):
        """Return the schema name used in SQL queries: #schema_name.method()"""
        return "{schema_name}"
    
    def data_sources(self):
        """Return list of available data source method names."""
        return ["items"]
    
    def schemas(self):
        """Return dictionary mapping data source names to their column schemas.
        
        Returns:
            dict: {method_name: {column: type, ...}, ...}
        """
        return {
            "items": {
                "id": "int",
                "name": "str",
                "value": "float",
                "active": "bool"
            }
        }
    
    def initialize(self):
        """Initialize the plugin - called once when plugin is loaded."""
        pass
    
    def get_required_env_vars(self, method_name):
        """Return dictionary of environment variables for the specified method.
        
        Args:
            method_name: The data source method name
            
        Returns:
            dict: Variable name -> is_required (bool)
        """
        if method_name == "items":
            return {
                # "API_KEY": True,     # Example: required variable
                # "ENDPOINT": False,   # Example: optional variable
            }
        return {}
    
    def get_required_execute_arguments(self, method_name):
        """Return list of parameter definitions for the specified method.
        
        Args:
            method_name: The data source method name
            
        Returns:
            list: List of (parameter_name, parameter_type) tuples
        """
        if method_name == "items":
            return [
                ("minimum_id", "int"),  # Optional parameter with default
            ]
        return []
    
    def execute(self, method_name, environment_variables, *args):
        """Execute the specified data source method.
        
        Args:
            method_name: The data source method name to execute
            environment_variables: Dictionary of environment variables (key-value pairs)
            *args: Parameters passed to the method
            
        Yields:
            dict: Row data with keys matching the schema
        """
        if method_name == "items":
            yield from self._get_items(environment_variables, *args)
        else:
            raise ValueError(f"Unknown method: {method_name}")
    
    def _get_items(self, environment_variables, *args):
        """Generate sample items data.
        
        Args:
            environment_variables: Dictionary of environment variables
            *args: Optional minimum_id parameter
        """
        minimum_id = int(args[0]) if len(args) > 0 else 1
        
        for i in range(minimum_id, minimum_id + 10):
            yield {
                "id": i,
                "name": f"Item {i}",
                "value": i * 10.5,
                "active": i % 2 == 0
            }
    
    def dispose(self):
        """Cleanup resources - called when plugin is unloaded."""
        pass

plugin = DataPlugin()

Rename Python Plugin

Rename an existing Python plugin project.

Command:

musoq python update old_name new_name

Expected Output:

Python project renamed from 'old_name' to 'new_name' successfully

Delete Python Plugin

Remove a Python plugin project from the system.

Command:

musoq python delete my_plugin

Expected Output:

Python plugin 'my_plugin' deleted successfully

Show Plugin Directory

Display or open the Python plugins directory.

Command:

musoq python folder

Expected Output:

~/.musoq/Python/Scripts/

Open in file explorer:

musoq python folder --open

Open specific project:

musoq python folder my_plugin --open

Using Python Plugins in Queries

Execute queries using your custom Python plugins as data sources.

Prerequisite: Create a Python plugin with schema name "mydata" and data sources ["items", "summary"]

Command:

musoq run "select id, name, value from #mydata.items()" --format csv

With parameters:

musoq run "select * from #mydata.items(100)" --format csv

Multiple data sources:

musoq run "select * from #mydata.summary()" --format csv

Expected Output:

id,name,value
42,"Answer","Meaning"

Note: The server must be restarted after creating a plugin for it to be discovered, or the plugin must exist before server startup.

Tool Management

List Tools

View all available tools with their metadata.

List All Tools

Command:

musoq tool list

Expected Output:

Name,Description,ParameterCount
alpha-tool,Tool for alpha scenarios,2
beta-tool,Secondary analyzer,0

Filter Tools by Search Term

Command:

musoq tool list --search "alpha"

Expected Output:

Name,Description,ParameterCount
alpha-tool,Tool for alpha scenarios,2

Show Tool Details

Display detailed information about a specific tool.

Command:

musoq tool show alpha-tool

Expected Output:

Name: alpha-tool
Description: Tool for alpha scenarios
Query: SELECT 10001 FROM #system.dual()
Output Format: json

Parameters:
  - firstParam (string) [Required]
    Description: Primary value to process
  - optionalFlag (bool)
    Description: Optional flag controlling execution
    Default: enabled

Execute Tool

Run a tool with specified parameters.

Execute with Debug Output

Command:

musoq tool execute my-tool --debug

Expected Output:

Executing query: SELECT 'debug-output' as Result FROM #system.dual()

┌──────────────┐
│ Result       │
├──────────────┤
│ debug-output │
└──────────────┘

Execute with Parameters

Command:

musoq tool execute data-processor --param1 "value1" --param2 "value2"

Parameter formats:

  • --param value - Named parameter with value
  • --flag - Boolean flag (sets to true)
  • param value - Positional parameter

Parameter Types and Syntax

Different parameter types require different syntax in the query template:

Type Query Syntax Example
string '{{ param }}' '{{ path }}'
int {{ param }} {{ count }}
long {{ param }} {{ size }}
bool {{ param }} {{ recursive }}
float {{ param }} {{ threshold }}

Key Rule: String parameters need quotes in SQL, numeric/boolean parameters don't.

Tool YAML File Format

Tools are defined as YAML files stored in ~/.musoq/Tools/ directory (e.g., C:\Users\<YourUser>\.musoq\Tools\ on Windows).

Complete YAML Structure

File: ~/.musoq/Tools/example-tool.yaml

name: tool-name
description: Brief description of what the tool does
query: |
  SELECT
    Column1,
    '{{ string_param }}' as Text,
    {{ numeric_param }} as Number
  FROM #datasource.method('{{ path }}', {{ boolean_param }})
  WHERE SomeCondition = {{ value }}
  ORDER BY Column1 DESC
parameters:
  - name: param1
    type: string
    required: true
    description: Description of parameter 1
  - name: param2
    type: int
    required: false
    default: 100
    description: Description of parameter 2
  - name: param3
    type: bool
    required: false
    default: true
    description: Description of parameter 3
output:
  format: table

YAML Field Specifications

Field Required Type Description Example
name ✅ Yes string Unique tool identifier (no spaces) disk-usage
description ✅ Yes string Brief explanation of tool purpose Analyze disk usage by file extension
query ✅ Yes string SQL query with parameter placeholders See examples below
parameters ✅ Yes array List of parameter definitions See parameter format
output.format ✅ Yes string Output format (table, json, csv) table

Parameter Definition Format

Each parameter in the parameters array must have:

- name: parameter_name        # Parameter identifier
  type: parameter_type        # string, int, long, bool, float
  required: true/false        # Is this parameter mandatory?
  default: default_value      # Default value (optional params only)
  description: help_text      # What this parameter does

Parameter Types:

  • string - Text values (use quotes in query: '{{ param }}')
  • int - Integer numbers (no quotes: {{ param }})
  • long - Long integers (no quotes: {{ param }})
  • bool - Boolean values (no quotes: {{ param }})
  • float - Floating point numbers (no quotes: {{ param }})

Example 1: Simple Greeting Tool

File: ~/.musoq/Tools/greeting.yaml

name: greeting
description: Display a personalized greeting
query: |
  SELECT
    '{{ name }}' as Name,
    'Hello, {{ name }}!' as Greeting,
    {{ age }} as Age
  FROM #system.dual()
parameters:
  - name: name
    type: string
    required: true
    description: Your name
  - name: age
    type: int
    required: false
    default: 0
    description: Your age
output:
  format: table

Usage:

musoq tool execute greeting -- --name "Alice" --age 30

Logging and History

View Query History

Display recent query execution logs with results.

Command:

musoq log

Expected Output (no history):

No query execution logs found

Expected Output (with history):

Recent Query Execution Logs

[2024-10-21 14:30:15] SUCCESS (125ms)
Query: SELECT 1 FROM #system.dual()
Rows: 1

[2024-10-21 14:30:20] SUCCESS (98ms)
Query: SELECT 2 FROM #system.dual()
Rows: 1

[2024-10-21 14:30:25] SUCCESS (103ms)
Query: SELECT 3 FROM #system.dual()
Rows: 1

Limit Log Output

Control the number of log entries displayed.

Command:

musoq log --count 2

Expected Output:

Recent Query Execution Logs (2)

[2024-10-21 14:30:25] SUCCESS (103ms)
Query: SELECT 3 FROM #system.dual()
Rows: 1

[2024-10-21 14:30:20] SUCCESS (98ms)
Query: SELECT 2 FROM #system.dual()
Rows: 1

View Timestamps and Duration

Query logs include detailed timing information.

Command:

musoq log

Output includes:

  • Timestamp: [2024-10-21 12:34:56] - When the query was executed
  • Status: SUCCESS or FAILED - Execution result
  • Duration: 125ms - How long the query took to execute

Base64 Encoding

Encode Image to Base64

Convert image files to base64-encoded strings.

Encode Regular File

Command:

musoq image encode path/to/image.png

Expected Output:

iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNk+M9QDwADhgGAWjR9awAAAABJRU5ErkJggg==

Encode Small Binary File

Command:

musoq image encode path/to/small-file.bin

Expected Output:

iVBORw0KGgo=

Note: Despite the command name "image encode", it can encode any binary file, not just images.

Troubleshooting

Server Not Running

# Check if server is running
musoq get is-running

# If not running, start it
musoq serve

Plugin Not Found

# List available data sources
musoq get data-sources

# For Python plugins, ensure server was restarted after creation
musoq quit
musoq serve

Query Execution Failed

# Check recent logs for error details
musoq log --count 5

Configuration Issues

# View environment variables
musoq get environment-variables

# View environment variables file path
musoq get environment-variables-file-path

# Clear and reset an environment variable
musoq clear environment-variable "VAR_NAME"
musoq set environment-variable "VAR_NAME" "new-value"

Python Plugin Issues

# List all Python plugin projects
musoq python list

# Read plugin content to verify structure
musoq python read plugin_name

# Show plugins directory location
musoq python folder

# Open plugins directory in file explorer
musoq python folder --open

About

No description, website, or topics provided.

Resources

Readme

License

MIT license
Activity

Stars

4 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases 45

0.21.3 Latest
Dec 1, 2025
+ 44 releases

Packages

No packages published

Languages