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 |
|
|
Read rows, details, and profiles |
|
Pass |
Inspect query history |
|
Filter historical statements by time range, |
Browse and drop metadata |
|
Read table statistics and DDL; |
WebSocket URL and handshake check |
|
|
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 a workbook, rename an existing workbook, or delete an unused workbook. |
Create, update, and save versions |
|
Check |
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) anddrop-meta-*commands modify workspace tables and metadata directly; verify target database names and role permissions before execution.dashboard run-chartanddashboard refreshreturn once execution is accepted; ifchart-resultis still running, poll with the existingDASHBOARD_IDandCHART_IDunder a bounded retry limit rather than creating duplicate charts.Pass
--query-param 'at=<RFC3339Nano-timestamp>'todashboard chart-resultwhen reading a historical chart result snapshot.