SQL and Dashboards

Use moi-cli sql and moi-cli dashboard to browse database and table metadata, run SQL queries and manage workbooks, build and refresh data dashboard charts, and inspect the workspace task execution overview. Complete Configuration and Authentication before running commands; omit --workspace-id when a default workspace is already saved in your local configuration.

Browse metadata and run SQL queries

Before running queries, list accessible databases, tables, and column schemas in the target workspace:

moi-cli -auth pat -o sql meta-databases --workspace-id "$WORKSPACE_ID"
moi-cli -auth pat -o sql meta-db-tables --workspace-id "$WORKSPACE_ID" --db-name analytics
moi-cli -auth pat -o sql meta-table-columns --workspace-id "$WORKSPACE_ID" \
  --db-name analytics --table-name daily_sales

moi-cli sql provides an asynchronous query pipeline (execute-query → describe-query → query-result) as well as an inline sql run helper when included in your client build (verify with moi-cli sql --help):

# Option 1: Run a query and receive columns, rows, rows_affected, and truncated inline
moi-cli -auth pat -o sql run \
  --workspace-id "$WORKSPACE_ID" \
  --db analytics \
  --query 'SELECT * FROM daily_sales LIMIT 20'

# Option 2: Submit asynchronously -> poll status for statement_id -> fetch or export rows
SUBMIT_JSON="$(moi-cli -auth pat -o sql execute-query \
  --workspace-id "$WORKSPACE_ID" \
  --db-name analytics \
  --query 'SELECT product_id, SUM(amount) AS total FROM daily_sales GROUP BY product_id')"

export QUERY_ID="$(printf '%s' "$SUBMIT_JSON" | jq -r '.query_id')"

DESC_JSON="$(moi-cli -auth pat -o sql describe-query \
  --workspace-id "$WORKSPACE_ID" \
  --query-id "$QUERY_ID")"

export STATEMENT_ID="$(printf '%s' "$DESC_JSON" | jq -r '.statement_id // empty')"

moi-cli -auth pat -o sql query-result \
  --workspace-id "$WORKSPACE_ID" \
  --statement-id "$STATEMENT_ID" \
  --limit 100 --offset 0

Common sql subcommands and identifier mappings:

Scenario

Subcommands

Key flags and behavior

Submit, poll, and cancel queries

execute-query (alias execute), describe-query (alias describe), kill-query (alias kill)

execute-query returns query_id; pass --query-id to describe-query or kill-query, and extract statement_id from describe-query once execution succeeds.

Read rows, details, and profiles

query-result (alias result), download-query-result, query-detail, query-profile

Pass --statement-id "$STATEMENT_ID" (query_id is not accepted here); download-query-result writes rows to --output result.csv.

Inspect query history

query-history-overview, query-history (alias history)

Filter historical statements by time range, --status, --databases, --users, or --min-duration.

Browse and drop metadata

meta-databases, meta-db-tree, meta-db-tables (alias meta-tables), meta-table-info, meta-table-columns, meta-table-column-stats, meta-table-ddl, drop-meta-database, drop-meta-table, drop-meta-view

Read table statistics and DDL; drop-meta-* drops the target database, table, or view immediately, so verify --db-name, --table-name, and dependencies beforehand.

WebSocket URL and handshake check

query-web-socket-url, connect-query-web-socket

query-web-socket-url returns the WebSocket endpoint URL without connecting; connect-query-web-socket tests handshake connectivity without opening an interactive SQL shell.

Warning

Exit code 0 from sql execute-query means only that the query submission was accepted. SQL syntax errors, permission denials, and runtime failures are recorded on the query record—always verify status and error details in describe-query before calling query-result. In addition, each CLI invocation uses an independent database connection and cannot span BEGIN / COMMIT transactions across separate CLI commands.

Manage SQL workbooks and versions

Use the workbook subcommands under sql to maintain saved SQL scripts and version snapshots:

# Search workbooks and inspect workbook details and versions
moi-cli -auth pat -o sql workbooks --workspace-id "$WORKSPACE_ID" --search "sales" --page-size 20
moi-cli -auth pat -o sql workbook-detail --workspace-id "$WORKSPACE_ID" --workbook-id "$WORKBOOK_ID"
moi-cli -auth pat -o sql workbook-versions --workspace-id "$WORKSPACE_ID" --workbook-id "$WORKBOOK_ID"

Operation

Subcommands

Description

Create, rename, and delete workbooks

create-workbook --name <name>, update-workbook --workbook-id <id> --name <name>, delete-workbook --workbook-id <id>

Create a workbook, rename an existing workbook, or delete an unused workbook.

Create, update, and save versions

create-workbook-version, update-workbook-version, save-workbook-version

Check --help on the target subcommand for --workbook-id, --version-id, and SQL content flags rather than concatenating unescaped multiline SQL into shell arguments.

Create dashboards and run charts

Before creating a dashboard, look up the positive integer Catalog table_id values for your source tables (see Catalogs, Volumes, and Data Sharing for broader Catalog operations) and inspect the input schema via moi-cli schema:

moi-cli -auth pat -o catalog search-data-dashboard-tables \
  --workspace-id "$WORKSPACE_ID" \
  --search "daily_sales"

moi-cli schema dashboard create
moi-cli -auth pat -o dashboard list --workspace-id "$WORKSPACE_ID" --json '{}'

Save the dashboard name and source table IDs as dashboard.json (for cross-database dashboards, ensure the caller has read access to every listed table):

{
  "name": "Sales Analytics",
  "sources": [
    {
      "table_id": 123
    },
    {
      "table_id": 456
    }
  ]
}

Create the dashboard and save the returned DASHBOARD_ID:

DASHBOARD_JSON="$(moi-cli -auth pat -o dashboard create \
  --workspace-id "$WORKSPACE_ID" \
  --json-file dashboard.json)"

export DASHBOARD_ID="$(printf '%s' "$DASHBOARD_JSON" | jq -r '.id')"

Draft and preview chart SQL

Generate a SQL draft from a natural-language question with generate-sql, verify the table qualifiers and filter logic, and dry-run it with preview-sql:

# Draft SQL from a prompt (returns draft text without executing the query)
DRAFT_JSON="$(moi-cli -auth pat -o dashboard generate-sql \
  --workspace-id "$WORKSPACE_ID" \
  --dashboard-id "$DASHBOARD_ID" \
  --question 'Monthly sales total by product')"

export SQL_TEXT="$(printf '%s' "$DRAFT_JSON" | jq -r '.sql_text')"

# Preview query results against dashboard sources
moi-cli -auth pat -o dashboard preview-sql \
  --workspace-id "$WORKSPACE_ID" \
  --dashboard-id "$DASHBOARD_ID" \
  --sql-text "$SQL_TEXT"

Create charts, run queries, and read results

In chart payloads, chart_config and layout_config are JSON-encoded strings. Inspect their per-chart-type fields offline with moi-cli schema:

moi-cli schema dashboard create-chart
moi-cli schema dashboard create-chart --argument json.chart_config
moi-cli schema dashboard create-chart --argument json.layout_config

Prepare chart.json:

{
  "name": "Sales Details",
  "chart_type": "table",
  "sql_text": "SELECT * FROM analytics.daily_sales LIMIT 10",
  "chart_config": "{}",
  "layout_config": "{}",
  "refresh_interval": "manual",
  "alert_enabled": false
}

Create the chart, trigger execution, and read or export the result rows:

CHART_JSON="$(moi-cli -auth pat -o dashboard create-chart \
  --workspace-id "$WORKSPACE_ID" \
  --dashboard-id "$DASHBOARD_ID" \
  --json-file chart.json)"

export CHART_ID="$(printf '%s' "$CHART_JSON" | jq -r '.id')"

# Run a single chart (use dashboard refresh to refresh all charts on the dashboard)
moi-cli -auth pat -o dashboard run-chart \
  --workspace-id "$WORKSPACE_ID" \
  --dashboard-id "$DASHBOARD_ID" \
  --chart-id "$CHART_ID"

# Read the latest chart result or export it to a local file
moi-cli -auth pat -o dashboard chart-result \
  --workspace-id "$WORKSPACE_ID" \
  --dashboard-id "$DASHBOARD_ID" \
  --chart-id "$CHART_ID"

moi-cli -auth pat -o dashboard download-chart-result \
  --workspace-id "$WORKSPACE_ID" \
  --dashboard-id "$DASHBOARD_ID" \
  --chart-id "$CHART_ID" \
  --output chart-result.csv

Additional dashboard and chart management subcommands include get, update, delete, update-chart, update-chart-order, delete-chart, drill-through, and download-drill-through (inspect flags via moi-cli dashboard <subcommand> --help).

Warning

dashboard update replaces dashboard metadata as a whole: omitting description clears an existing description, and removing a source table still referenced by a chart fails with DATA_DASHBOARD_SOURCE_IN_USE. When calling update-chart-order, supply a complete permutation of all chart_ids belonging to the dashboard.

Inspect workspace task execution overview

Read aggregate background task counts for the current workspace with dashboard task-execution-overview:

moi-cli -auth pat -o dashboard task-execution-overview --workspace-id "$WORKSPACE_ID"

The response reports the observation window along with running, succeeded, failed, pending, and total task counts (zero-valued count fields may be omitted in JSON output). Because this endpoint returns aggregate counts only, list and inspect individual runs through workflow, connector, or agent when diagnosing a specific failure.

Limitations and notes

  • Write statements (INSERT, UPDATE, DELETE, DDL) and drop-meta-* commands modify workspace tables and metadata directly; verify target database names and role permissions before execution.

  • dashboard run-chart and dashboard refresh return once execution is accepted; if chart-result is still running, poll with the existing DASHBOARD_ID and CHART_ID under a bounded retry limit rather than creating duplicate charts.

  • Pass --query-param 'at=<RFC3339Nano-timestamp>' to dashboard chart-result when reading a historical chart result snapshot.

Last updated on