# SQL service

Executes read-only SQL queries against Nominal telemetry tables.

OpenAPI file: [`nominal.sql.v1.json`](https://dev.nominal.io/api/_downloads/3908dc3f46a39737f6f83be6ebdf68f8/nominal.sql.v1.json)

## Endpoints

### Get SQL catalog

GET `/sql/v1/catalog`

Get the queryable SQL surface: tables, columns, and allowed functions.

Request

No parameters and no body.

Response

`sqlCatalog` [`SqlCatalog`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalog)
:   ​

    Fields of `SqlCatalog`

    `tables` array of [`SqlCatalogTable`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogtable)
    :   ​

        Fields of `SqlCatalogTable`

        `name` string
        :   ​

        `columns` array of [`SqlCatalogColumn`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogcolumn)
        :   ​

            Fields of `SqlCatalogColumn`

            `name` string
            :   ​

            `type` string
            :   Top-level SQL type name, e.g. TIMESTAMP, DOUBLE, BIGINT, VARCHAR, MAP, or ARRAY. Collection element types are only carried by data_type.

            `nullable` boolean
            :   ​

            `dataType` [`SqlCatalogDataType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogdatatype)
            :   ​

                Fields of `SqlCatalogDataType`

                one of `scalar`, `arrayElement`, `map`
                :   ​

                    **scalar**

                    `scalar` [`SqlCatalogScalarType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogscalartype)
                    :   ​

                        Values of `SqlCatalogScalarType`

                        `SQL_CATALOG_SCALAR_TYPE_UNSPECIFIED`
                        :   ​

                        `SQL_CATALOG_SCALAR_TYPE_VARCHAR`
                        :   ​

                        `SQL_CATALOG_SCALAR_TYPE_BOOLEAN`
                        :   ​

                        `SQL_CATALOG_SCALAR_TYPE_INTEGER`
                        :   ​

                        `SQL_CATALOG_SCALAR_TYPE_BIGINT`
                        :   ​

                        `SQL_CATALOG_SCALAR_TYPE_DOUBLE`
                        :   ​

                        `SQL_CATALOG_SCALAR_TYPE_TIMESTAMP`
                        :   ​

                        `SQL_CATALOG_SCALAR_TYPE_ANY`
                        :   ​

                    **arrayElement**

                    `arrayElement` [`SqlCatalogDataType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogdatatype)
                    :   ​

                    **map**

                    `map` [`SqlCatalogMapType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogmaptype)
                    :   ​

                        Fields of `SqlCatalogMapType`

                        `key` [`SqlCatalogDataType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogdatatype)
                        :   ​

                        `value` [`SqlCatalogDataType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogdatatype)
                        :   ​

    `functions` array of [`SqlCatalogFunction`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogfunction)
    :   ​

        Fields of `SqlCatalogFunction`

        `name` string
        :   Uppercase function name, e.g. AVG, COUNT, DATE_BIN.

        `kind` [`SqlCatalogFunctionKind`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogfunctionkind)
        :   ​

            Values of `SqlCatalogFunctionKind`

            `SQL_CATALOG_FUNCTION_KIND_UNSPECIFIED`
            :   ​

            `SQL_CATALOG_FUNCTION_KIND_SCALAR`
            :   ​

            `SQL_CATALOG_FUNCTION_KIND_AGGREGATE`
            :   ​

            `SQL_CATALOG_FUNCTION_KIND_WINDOW`
            :   Requires an OVER clause.

            `SQL_CATALOG_FUNCTION_KIND_TABLE`
            :   ​

        `supportsOver` boolean
        :   Whether calls may carry an OVER clause. Always true for WINDOW functions, which require one.

        `minArgs` integer
        :   Minimum number of arguments.

        `maxArgs` integer
        :   Maximum number of arguments; unset when the function is variadic.

        `argumentTypeFamilies` array of string
        :   Best-effort operand type families (e.g. NUMERIC, DATETIME, CHARACTER, ANY), one per argument position up to max_args. Empty when the function’s operand rules are not statically known.

        `returnTypeFamily` string
        :   Best-effort type family of the result (e.g. NUMERIC, TIMESTAMP, CHARACTER, BOOLEAN). Unset when the result type depends on the argument types in a way that is not statically known.

Request

**curl**

```shell
curl "https://api.gov.nominal.io/api/sql/v1/catalog" \
  -H "Authorization: Bearer <api key>"
```

**requests**

```python
import requests

response = requests.get(
    "https://api.gov.nominal.io/api/sql/v1/catalog",
    headers={"Authorization": "Bearer <api key>"},
)
response.raise_for_status()
print(response.json())
```

**httpx**

```python
import httpx

response = httpx.get(
    "https://api.gov.nominal.io/api/sql/v1/catalog",
    headers={"Authorization": "Bearer <api key>"},
)
response.raise_for_status()
print(response.json())
```

**aiohttp**

```python
import asyncio

import aiohttp

async def main() -> None:
    async with aiohttp.ClientSession() as session:
        async with session.get(
            "https://api.gov.nominal.io/api/sql/v1/catalog",
            headers={"Authorization": "Bearer <api key>"},
        ) as response:
            response.raise_for_status()
            print(await response.json())

asyncio.run(main())
```

Response: `200`

```json
{
  "sqlCatalog": {
    "tables": [
      {
        "name": "<name>",
        "columns": [
          {
            "name": "<name>",
            "type": "<type>",
            "nullable": true,
            "dataType": {
              "scalar": "SQL_CATALOG_SCALAR_TYPE_VARCHAR"
            }
          }
        ]
      }
    ],
    "functions": [
      {
        "name": "<name>",
        "kind": "SQL_CATALOG_FUNCTION_KIND_SCALAR",
        "supportsOver": true,
        "minArgs": 1,
        "maxArgs": 1,
        "argumentTypeFamilies": [
          "<argument type families>"
        ],
        "returnTypeFamily": "<return type family>"
      }
    ]
  }
}
```

### Query

POST `/sql/v1/query`

Validate, authorize, lower, and execute a bounded SQL query.

Body

`query` string required
:   At most 100,000 characters.

`maxRows` integer
:   Optional cap on the number of rows returned. When unset, results are unbounded (subject to the server-side byte/time limits); data store and mixed queries stream, pure metadata store materializes. Zero returns only the result’s schema. From 0 to 5,000.

`workspaceRid` string required
:   ​

`resultFormat` [`SqlServiceQueryResultFormat`](https://dev.nominal.io/api/rest/sql-service.md#sqlservicequeryresultformat)
:   Encoding for the streamed response payload. Defaults to Arrow IPC stream for backward compatibility.

    Values of `SqlServiceQueryResultFormat`

    `SQL_SERVICE_QUERY_RESULT_FORMAT_UNSPECIFIED`
    :   ​

    `SQL_SERVICE_QUERY_RESULT_FORMAT_ARROW_STREAM`
    :   ​

    `SQL_SERVICE_QUERY_RESULT_FORMAT_JSON_LINES`
    :   ​

    `SQL_SERVICE_QUERY_RESULT_FORMAT_CSV`
    :   ​

Response: `application/octet-stream`

The body is the raw bytes.

Response bytes encoded according to SqlServiceQueryRequest.result_format. Defaults to an Arrow IPC stream when no result_format is specified.

Request

**curl**

```shell
curl -X POST "https://api.gov.nominal.io/api/sql/v1/query" \
  -H "Authorization: Bearer <api key>" \
  -H "Content-Type: application/json" \
  --data '{
  "query": "<query>",
  "workspaceRid": "<workspace rid>"
}' \
  --output "<file>"
```

**requests**

```python
from pathlib import Path

import requests

response = requests.post(
    "https://api.gov.nominal.io/api/sql/v1/query",
    headers={"Authorization": "Bearer <api key>"},
    json={"query": "<query>", "workspaceRid": "<workspace rid>"},
)
response.raise_for_status()
Path("<file>").write_bytes(response.content)
```

**httpx**

```python
from pathlib import Path

import httpx

response = httpx.post(
    "https://api.gov.nominal.io/api/sql/v1/query",
    headers={"Authorization": "Bearer <api key>"},
    json={"query": "<query>", "workspaceRid": "<workspace rid>"},
)
response.raise_for_status()
Path("<file>").write_bytes(response.content)
```

**aiohttp**

```python
import asyncio
from pathlib import Path

import aiohttp

async def main() -> None:
    async with aiohttp.ClientSession() as session:
        async with session.post(
            "https://api.gov.nominal.io/api/sql/v1/query",
            headers={"Authorization": "Bearer <api key>"},
            json={"query": "<query>", "workspaceRid": "<workspace rid>"},
        ) as response:
            response.raise_for_status()
            Path("<file>").write_bytes(await response.read())

asyncio.run(main())
```

Response: `200`

The raw bytes.

### Export

POST `/sql/v1/query/export`

Run an unbounded SQL query. Write the full result to object storage in the requested format, and return a presigned URL.

Body

`query` string required
:   At most 100,000 characters.

`workspaceRid` string required
:   ​

`format` [`SqlServiceExportFormat`](https://dev.nominal.io/api/rest/sql-service.md#sqlserviceexportformat)
:   File format written to object storage. Defaults to CSV.

    Values of `SqlServiceExportFormat`

    `SQL_SERVICE_EXPORT_FORMAT_UNSPECIFIED`
    :   ​

    `SQL_SERVICE_EXPORT_FORMAT_CSV`
    :   CSV with a header row; nulls are empty fields.

    `SQL_SERVICE_EXPORT_FORMAT_PARQUET`
    :   A single Parquet file. Arrow-based readers can read it from the presigned URL. Configure it with SqlServiceExportRequest.parquet_options.

`parquetOptions` [`SqlServiceParquetOptions`](https://dev.nominal.io/api/rest/sql-service.md#sqlserviceparquetoptions)
:   Only valid with format PARQUET. Unset writes zstd with the server’s default row group size.

    Fields of `SqlServiceParquetOptions`

    `rowGroupRows` string (uint64)
    :   Rows per row group, from 1,000 to 100,000,000. A reader skips or fetches whole row groups. Add an ORDER BY to the query so that each group holds a contiguous range of rows. A group closes early if it reaches the server’s byte limit. Unset uses the server default. From 1,000 to 100,000,000.

    `compression` [`SqlServiceParquetCompression`](https://dev.nominal.io/api/rest/sql-service.md#sqlserviceparquetcompression)
    :   Defaults to zstd.

        Values of `SqlServiceParquetCompression`

        `SQL_SERVICE_PARQUET_COMPRESSION_UNSPECIFIED`
        :   ​

        `SQL_SERVICE_PARQUET_COMPRESSION_ZSTD`
        :   ​

        `SQL_SERVICE_PARQUET_COMPRESSION_LZ4`
        :   ​

        `SQL_SERVICE_PARQUET_COMPRESSION_SNAPPY`
        :   ​

        `SQL_SERVICE_PARQUET_COMPRESSION_NONE`
        :   ​

Response

`presignedUrl` string
:   Presigned URL for the exported file, written to object storage in the requested format.

`queryId` string
:   Server-generated id for this query execution, also used as the data store query_id.

Request

**curl**

```shell
curl -X POST "https://api.gov.nominal.io/api/sql/v1/query/export" \
  -H "Authorization: Bearer <api key>" \
  -H "Content-Type: application/json" \
  --data '{
  "query": "<query>",
  "workspaceRid": "<workspace rid>"
}'
```

**requests**

```python
import requests

response = requests.post(
    "https://api.gov.nominal.io/api/sql/v1/query/export",
    headers={"Authorization": "Bearer <api key>"},
    json={"query": "<query>", "workspaceRid": "<workspace rid>"},
)
response.raise_for_status()
print(response.json())
```

**httpx**

```python
import httpx

response = httpx.post(
    "https://api.gov.nominal.io/api/sql/v1/query/export",
    headers={"Authorization": "Bearer <api key>"},
    json={"query": "<query>", "workspaceRid": "<workspace rid>"},
)
response.raise_for_status()
print(response.json())
```

**aiohttp**

```python
import asyncio

import aiohttp

async def main() -> None:
    async with aiohttp.ClientSession() as session:
        async with session.post(
            "https://api.gov.nominal.io/api/sql/v1/query/export",
            headers={"Authorization": "Bearer <api key>"},
            json={"query": "<query>", "workspaceRid": "<workspace rid>"},
        ) as response:
            response.raise_for_status()
            print(await response.json())

asyncio.run(main())
```

Response: `200`

```json
{
  "presignedUrl": "<presigned url>",
  "queryId": "<query id>"
}
```

## Errors

[Errors](https://dev.nominal.io/api.md#errors) shows how these errors are sent.

Each error’s `errorName` is `NominalSqlV1:` followed by its name below.

| Status | Name | Notes |
| --- | --- | --- |
| 400 | `SqlErrorDatasetStorageTypeNotSupported` | Datasets with this storage type are not yet supported by SQL queries |
| 400 | `SqlErrorExportNotSupportedForBackend` | SQL export is not supported for this query backend |
| 400 | `SqlErrorInvalidQuery` | SQL query is invalid |
| 400 | `SqlErrorQueryTimeout` | The query timed out before it could complete |
| 400 | `SqlErrorResultSizeExceeded` | The query result exceeded the maximum result size |
| 401 | `SqlErrorUnauthenticated` | The request could not be authenticated |
| 403 | `SqlErrorPermissionDenied` | The caller does not have permission to perform this request |
| 404 | `SqlErrorDataNotFoundForType` | No data of the requested type exists for one or more of the requested datasets |
| 404 | `SqlErrorDatasetsNotFound` | One or more requested datasets could not be found in the specified workspace |
| 500 | `SqlErrorExecutionFailed` | An unexpected internal error occurred while executing the SQL query |
| 500 | `SqlErrorExportNotConfigured` | SQL export is not configured for this deployment |
| 500 | `SqlErrorQueryCancelled` | The query was cancelled |
| 500 | `SqlErrorQueryScopeTooLarge` | The query spans more datasets than can be planned at once |

A `429` response has no `errorName`: The query exceeded a resource limit.

## Types

### SqlCatalog

The queryable logical SQL surface.

`tables` array of [`SqlCatalogTable`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogtable)
:   ​

`functions` array of [`SqlCatalogFunction`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogfunction)
:   ​

### SqlCatalogColumn

`name` string
:   ​

`type` string
:   Top-level SQL type name, e.g. TIMESTAMP, DOUBLE, BIGINT, VARCHAR, MAP, or ARRAY. Collection element types are only carried by data_type.

`nullable` boolean
:   ​

`dataType` [`SqlCatalogDataType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogdatatype)
:   ​

### SqlCatalogDataType

Full logical type of a column, including collection element types.

Set at most one of the fields below.

one of `scalar`, `arrayElement`, `map`
:   ​

    **scalar**

    `scalar` [`SqlCatalogScalarType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogscalartype)
    :   ​

    **arrayElement**

    `arrayElement` [`SqlCatalogDataType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogdatatype)
    :   ​

    **map**

    `map` [`SqlCatalogMapType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogmaptype)
    :   ​

### SqlCatalogFunction

`name` string
:   Uppercase function name, e.g. AVG, COUNT, DATE_BIN.

`kind` [`SqlCatalogFunctionKind`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogfunctionkind)
:   ​

`supportsOver` boolean
:   Whether calls may carry an OVER clause. Always true for WINDOW functions, which require one.

`minArgs` integer
:   Minimum number of arguments.

`maxArgs` integer
:   Maximum number of arguments; unset when the function is variadic.

`argumentTypeFamilies` array of string
:   Best-effort operand type families (e.g. NUMERIC, DATETIME, CHARACTER, ANY), one per argument position up to max_args. Empty when the function’s operand rules are not statically known.

`returnTypeFamily` string
:   Best-effort type family of the result (e.g. NUMERIC, TIMESTAMP, CHARACTER, BOOLEAN). Unset when the result type depends on the argument types in a way that is not statically known.

### SqlCatalogFunctionKind

`SQL_CATALOG_FUNCTION_KIND_UNSPECIFIED`
:   ​

`SQL_CATALOG_FUNCTION_KIND_SCALAR`
:   ​

`SQL_CATALOG_FUNCTION_KIND_AGGREGATE`
:   ​

`SQL_CATALOG_FUNCTION_KIND_WINDOW`
:   Requires an OVER clause.

`SQL_CATALOG_FUNCTION_KIND_TABLE`
:   ​

### SqlCatalogMapType

`key` [`SqlCatalogDataType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogdatatype)
:   ​

`value` [`SqlCatalogDataType`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogdatatype)
:   ​

### SqlCatalogScalarType

`SQL_CATALOG_SCALAR_TYPE_UNSPECIFIED`
:   ​

`SQL_CATALOG_SCALAR_TYPE_VARCHAR`
:   ​

`SQL_CATALOG_SCALAR_TYPE_BOOLEAN`
:   ​

`SQL_CATALOG_SCALAR_TYPE_INTEGER`
:   ​

`SQL_CATALOG_SCALAR_TYPE_BIGINT`
:   ​

`SQL_CATALOG_SCALAR_TYPE_DOUBLE`
:   ​

`SQL_CATALOG_SCALAR_TYPE_TIMESTAMP`
:   ​

`SQL_CATALOG_SCALAR_TYPE_ANY`
:   ​

### SqlCatalogTable

`name` string
:   ​

`columns` array of [`SqlCatalogColumn`](https://dev.nominal.io/api/rest/sql-service.md#sqlcatalogcolumn)
:   ​

### SqlServiceExportFormat

File format of an exported result. Unspecified means CSV.

`SQL_SERVICE_EXPORT_FORMAT_UNSPECIFIED`
:   ​

`SQL_SERVICE_EXPORT_FORMAT_CSV`
:   CSV with a header row; nulls are empty fields.

`SQL_SERVICE_EXPORT_FORMAT_PARQUET`
:   A single Parquet file. Arrow-based readers can read it from the presigned URL. Configure it with SqlServiceExportRequest.parquet_options.

### SqlServiceParquetCompression

Column compression codec for a Parquet export. Unspecified is zstd.

`SQL_SERVICE_PARQUET_COMPRESSION_UNSPECIFIED`
:   ​

`SQL_SERVICE_PARQUET_COMPRESSION_ZSTD`
:   ​

`SQL_SERVICE_PARQUET_COMPRESSION_LZ4`
:   ​

`SQL_SERVICE_PARQUET_COMPRESSION_SNAPPY`
:   ​

`SQL_SERVICE_PARQUET_COMPRESSION_NONE`
:   ​

### SqlServiceParquetOptions

How a Parquet export is written.

`rowGroupRows` string (uint64)
:   Rows per row group, from 1,000 to 100,000,000. A reader skips or fetches whole row groups. Add an ORDER BY to the query so that each group holds a contiguous range of rows. A group closes early if it reaches the server’s byte limit. Unset uses the server default. From 1,000 to 100,000,000.

`compression` [`SqlServiceParquetCompression`](https://dev.nominal.io/api/rest/sql-service.md#sqlserviceparquetcompression)
:   Defaults to zstd.

### SqlServiceQueryResultFormat

`SQL_SERVICE_QUERY_RESULT_FORMAT_UNSPECIFIED`
:   ​

`SQL_SERVICE_QUERY_RESULT_FORMAT_ARROW_STREAM`
:   ​

`SQL_SERVICE_QUERY_RESULT_FORMAT_JSON_LINES`
:   ​

`SQL_SERVICE_QUERY_RESULT_FORMAT_CSV`
:   ​

Page design based on [Scalar](https://scalar.com), MIT License.