<span id="SQL 与数据看板"></span>

# 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](../configuration-authentication.md) before running commands; omit `--workspace-id` when a default workspace is already saved in your local configuration.

<span id="浏览元数据与执行-sql-查询"></span>

## Browse metadata and run SQL queries

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

```bash
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`):

```bash
# 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.
:::

<span id="管理-sql-工作簿与版本"></span>

## Manage SQL workbooks and versions

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

```bash
# 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. |

(cli-data-dashboards)=
<span id="创建数据看板与查询图表"></span>

## 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](catalog-files.md) for broader Catalog operations) and inspect the input schema via `moi-cli schema`:

```bash
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):

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

Create the dashboard and save the returned `DASHBOARD_ID`:

```bash
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')"
```

<span id="生成和预览图表-sql"></span>

### 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`:

```bash
# 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"
```

<span id="创建图表刷新运行与读取结果"></span>

### 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`:

```bash
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`:

```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:

```bash
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.
:::

(cli-task-execution-overview)=
<span id="查看工作区任务执行概览"></span>

## Inspect workspace task execution overview

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

```bash
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.

<span id="限制和注意事项"></span>

## 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.
