> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/concepts/foundations/activation/apis/getting-started/querying-data-products/semantic-sql.md).

# Semantic SQL

Semantic SQL lets you query semantic and metric models with SQL syntax. Use it when you want SQL ergonomics with governed measures, dimensions, segments, and joins.

**Endpoint:** `POST /api/v1/query/semantic/sql`

```json
{
  "sql": "SELECT MEASURE(users.total_users), users.plan_type FROM users GROUP BY users.plan_type"
}
```

### Response lifecycle

`POST /api/v1/query/semantic/sql` with `{ "sql": "..." }` returns `202 Accepted` and a statement `id`. Poll `GET /api/v1/query/statement/{id}` until `status` is `SUCCESS` or `FAILED`. Fetch rows from `GET /api/v1/query/statement/{id}/result?format=json` only after `SUCCESS`. Do not call `/result` when `FAILED`.

Status codes, deduplication, and export formats: see [Statement lifecycle](/concepts/foundations/activation/apis/getting-started/querying-data-products/statement-lifecycle.md).

Public endpoints compile to **regular queries** only. Each statement must transpile to the REST shape in [REST](/concepts/foundations/activation/apis/getting-started/querying-data-products/rest.md). Outer queries that wrap an inner semantic `SELECT` and arbitrary warehouse SQL against semantic tables are not supported. If the transpiler cannot rewrite your SQL, the request fails.

### Model mapping

<table><thead><tr><th width="243.82470703125">Concept</th><th>SQL</th></tr></thead><tbody><tr><td>Semantic or metric model</td><td>Table name in <code>FROM</code></td></tr><tr><td>Dimension</td><td>Column in <code>SELECT</code> / <code>GROUP BY</code></td></tr><tr><td>Measure</td><td><code>MEASURE(model.column)</code></td></tr><tr><td>Segment</td><td>Boolean column: <code>WHERE seg IS TRUE</code></td></tr></tbody></table>

### Basic queries

```sql
SELECT users.plan_type, MEASURE(users.total_users)
FROM users
GROUP BY users.plan_type;
```

Segment:

```sql
SELECT MEASURE(subscriptions.total_arr)
FROM subscriptions
WHERE active_subscriptions IS TRUE;
```

Time filter:

```sql
SELECT DATE_TRUNC('month', users.signup_date) AS month, MEASURE(users.total_users)
FROM users
WHERE users.signup_date >= '2025-01-01'
GROUP BY 1
ORDER BY 1;
```

Metric model:

```sql
SELECT product_category, DATE_TRUNC('day', ts) AS day, MEASURE(measure)
FROM store_revenue
WHERE orders_completed_orders IS TRUE
GROUP BY 1, 2;
```

### WHERE and HAVING

Dimension filters go in `WHERE`. Measure filters go in `HAVING`. The logical structure matches [Filters in REST](/concepts/foundations/activation/apis/getting-started/querying-data-products/rest.md).

### Joins

**`CROSS JOIN`** declares a semantic join. Do not write an `ON` clause; the transpiler resolves the condition from model metadata.

```sql
SELECT users.plan_type, MEASURE(usage_events.total_events)
FROM users
CROSS JOIN usage_events
GROUP BY users.plan_type;
```

**`__joinField`** is a virtual column for BI tools that require explicit join keys:

```sql
SELECT p.name, MEASURE(o.count)
FROM orders o
LEFT JOIN products p ON o.__joinField = p.__joinField
GROUP BY 1;
```

Same semantic resolution as `CROSS JOIN`. Appears in MySQL schema discovery (`DESCRIBE users` lists `__joinField`).

For ambiguous graphs, set `"joinHints"` in [REST](/concepts/foundations/activation/apis/getting-started/querying-data-products/rest.md) or order `CROSS JOIN` carefully. Directionality, fan-out, and path rules are covered in [Cross-model queries](/concepts/foundations/activation/apis/getting-started/querying-data-products/cross-model-queries.md).

### Aggregated vs ungrouped

With `GROUP BY`, every measure must be aggregated (`MEASURE(...)` or a matching aggregate) and every non-aggregated column must appear in `GROUP BY`.

Without `GROUP BY`, the query runs in ungrouped mode (row-level). REST equivalent: `"ungrouped": true`. Primary keys of involved models may be required.

### Supported SQL

Semantic SQL accepts a subset of SQL oriented toward regular semantic queries:

* **Comparison:** `=`, `<>`, `<`, `>`, `<=`, `>=`, `IN`, `NOT IN`, `IS NULL`, `IS NOT NULL`, `LIKE` / `ILIKE`
* **String:** `LOWER`, `UPPER`, `CONCAT`, `SUBSTRING`, `TRIM`, `STARTS_WITH`, `||`
* **Date/time:** `DATE_TRUNC`, `EXTRACT`, date literals in `WHERE`
* **Aggregates:** `MEASURE(col)` for any measure type; `SUM`, `COUNT`, `AVG`, `MIN`, `MAX` when they match the measure's aggregation type
* **Conditional:** `CASE`, `COALESCE` where the transpiler accepts them in filter or projection context

Window functions, nested outer queries over semantic tables, and expressions that don't transpile to the REST query shape will fail at submit time.

### REST vs Semantic SQL

<table><thead><tr><th width="245.514404296875">Use Semantic SQL when</th><th>Use REST when</th></tr></thead><tbody><tr><td>You prefer SQL syntax</td><td>Structured programmatic building</td></tr><tr><td>Custom <code>HAVING</code> or filter logic</td><td><code>compareDateRange</code></td></tr><tr><td>Familiar SQL tooling</td><td><code>total</code> row count, <code>joinHints</code></td></tr><tr><td>MySQL/BI client</td><td>Default time-dimension helpers</td></tr></tbody></table>

### MySQL interface

Connect any MySQL client to port `3307` (dev) or `3306` (prod). SSL required. Password is your DataOS API key or JWT.

```bash
mysql -h 127.0.0.1 -P 3307 -u <username> -p'<api-key>' \
  --ssl-mode=REQUIRED --enable-cleartext-plugin public
```

`SHOW TABLES` and `DESCRIBE` list semantic and metric models. Queries run through the MySQL wire interface use the same statement API: submit, poll until `SUCCESS`, then fetch `/result`. Schema comes from `/api/v1/metadata/semantic` and refreshes when the fingerprint changes.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://v2.dataos.info/concepts/foundations/activation/apis/getting-started/querying-data-products/semantic-sql.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.