SQL service (gRPC)#

Executes read-only SQL queries against Nominal telemetry tables.

Endpoints#

Export#

gRPC /nominal.sql.v1.SqlService/Export

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

Request: SqlServiceExportRequest

query string required

At most 100,000 characters.

workspace_rid string required

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

parquet_options SqlServiceParquetOptions

Only valid with format PARQUET. Unset writes zstd with the server’s default row group size.

Fields of SqlServiceParquetOptions

row_group_rows integer (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

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

presigned_url string: Presigned URL for the exported file, written to object storage in the requested format.
query_id string: Server-generated id for this query execution, also used as the data store query_id.

Request

Python

import grpc
from nominal.protos.sql.v1 import sql_pb2, sql_pb2_grpc

channel = grpc.secure_channel("api.gov.nominal.io:443", grpc.ssl_channel_credentials())
sql_service = sql_pb2_grpc.SqlServiceStub(channel)
auth = [("authorization", "Bearer <api key>")]

request = sql_pb2.SqlServiceExportRequest(
    query="<query>",
    workspace_rid="<workspace rid>",
)
response = sql_service.Export(request, metadata=auth)
print(response)

package main

import (
	"context"
	"crypto/tls"
	"fmt"
	"log"

	sqlv1 "github.com/nominal-io/nominal-api-protos-go/nominal/protos/sql/v1"
	"google.golang.org/grpc"
	"google.golang.org/grpc/credentials"
	"google.golang.org/grpc/metadata"
)

func main() {
	conn, err := grpc.NewClient("api.gov.nominal.io:443",
		grpc.WithTransportCredentials(credentials.NewTLS(&tls.Config{})))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close()
	sqlService := sqlv1.NewSqlServiceClient(conn)
	ctx := metadata.AppendToOutgoingContext(context.Background(), "authorization", "Bearer <api key>")

	request := &sqlv1.SqlServiceExportRequest{
		Query:        "<query>",
		WorkspaceRid: "<workspace rid>",
	}
	response, err := sqlService.Export(ctx, request)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(response)
}

Response

{
  "presigned_url": "<presigned url>",
  "query_id": "<query id>"
}

Get SQL catalog#

gRPC /nominal.sql.v1.SqlService/GetSqlCatalog

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

Request: GetSqlCatalogRequest

A message with no fields.

Response: GetSqlCatalogResponse

sql_catalog SqlCatalog

Fields of SqlCatalog

tables array of SqlCatalogTable

Fields of SqlCatalogTable

name string

columns array of 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

data_type SqlCatalogDataType

Fields of SqlCatalogDataType

one of scalar, array_element, map

scalar

scalar 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

array_element

array_element SqlCatalogDataType

map

map SqlCatalogMapType

Fields of SqlCatalogMapType

key SqlCatalogDataType
value SqlCatalogDataType

functions array of SqlCatalogFunction

Fields of SqlCatalogFunction

name string

Uppercase function name, e.g. AVG, COUNT, DATE_BIN.

kind 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

supports_over boolean

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

min_args integer

Minimum number of arguments.

max_args integer

Maximum number of arguments; unset when the function is variadic.

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

return_type_family 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

Python

import grpc
from nominal.protos.sql.v1 import sql_pb2, sql_pb2_grpc

channel = grpc.secure_channel("api.gov.nominal.io:443", grpc.ssl_channel_credentials())
sql_service = sql_pb2_grpc.SqlServiceStub(channel)
auth = [("authorization", "Bearer <api key>")]

request = sql_pb2.GetSqlCatalogRequest()
response = sql_service.GetSqlCatalog(request, metadata=auth)
print(response)

package main

import (
	"context"
	"crypto/tls"
	"fmt"
	"log"

	sqlv1 "github.com/nominal-io/nominal-api-protos-go/nominal/protos/sql/v1"
	"google.golang.org/grpc"
	"google.golang.org/grpc/credentials"
	"google.golang.org/grpc/metadata"
)

func main() {
	conn, err := grpc.NewClient("api.gov.nominal.io:443",
		grpc.WithTransportCredentials(credentials.NewTLS(&tls.Config{})))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close()
	sqlService := sqlv1.NewSqlServiceClient(conn)
	ctx := metadata.AppendToOutgoingContext(context.Background(), "authorization", "Bearer <api key>")

	request := &sqlv1.GetSqlCatalogRequest{}
	response, err := sqlService.GetSqlCatalog(ctx, request)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(response)
}

Response

{
  "sql_catalog": {
    "tables": [
      {
        "name": "<name>",
        "columns": [
          {
            "name": "<name>",
            "type": "<type>",
            "nullable": true,
            "data_type": {
              "scalar": "SQL_CATALOG_SCALAR_TYPE_VARCHAR"
            }
          }
        ]
      }
    ],
    "functions": [
      {
        "name": "<name>",
        "kind": "SQL_CATALOG_FUNCTION_KIND_SCALAR",
        "supports_over": true,
        "min_args": 1,
        "max_args": 1,
        "argument_type_families": [
          "<argument type families>"
        ],
        "return_type_family": "<return type family>"
      }
    ]
  }
}

Query#

gRPC /nominal.sql.v1.SqlService/Query server streaming

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

Request: SqlServiceQueryRequest

query string required

At most 100,000 characters.

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

workspace_rid string required

result_format 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: a stream of SqlServiceQueryResponse

query_id string: Server-generated id for this query execution, also used as the data store query_id.
payload bytes: Response bytes encoded according to SqlServiceQueryRequest.result_format. Defaults to an Arrow IPC stream when no result_format is specified.

Request

Python

import grpc
from nominal.protos.sql.v1 import sql_pb2, sql_pb2_grpc

channel = grpc.secure_channel("api.gov.nominal.io:443", grpc.ssl_channel_credentials())
sql_service = sql_pb2_grpc.SqlServiceStub(channel)
auth = [("authorization", "Bearer <api key>")]

request = sql_pb2.SqlServiceQueryRequest(
    query="<query>",
    workspace_rid="<workspace rid>",
)
for response in sql_service.Query(request, metadata=auth):
    print(response)

package main

import (
	"context"
	"crypto/tls"
	"fmt"
	"io"
	"log"

	sqlv1 "github.com/nominal-io/nominal-api-protos-go/nominal/protos/sql/v1"
	"google.golang.org/grpc"
	"google.golang.org/grpc/credentials"
	"google.golang.org/grpc/metadata"
)

func main() {
	conn, err := grpc.NewClient("api.gov.nominal.io:443",
		grpc.WithTransportCredentials(credentials.NewTLS(&tls.Config{})))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close()
	sqlService := sqlv1.NewSqlServiceClient(conn)
	ctx := metadata.AppendToOutgoingContext(context.Background(), "authorization", "Bearer <api key>")

	request := &sqlv1.SqlServiceQueryRequest{
		Query:        "<query>",
		WorkspaceRid: "<workspace rid>",
	}
	stream, err := sqlService.Query(ctx, request)
	if err != nil {
		log.Fatal(err)
	}
	for {
		response, err := stream.Recv()
		if err == io.EOF {
			break
		}
		if err != nil {
			log.Fatal(err)
		}
		fmt.Println(response)
	}
}

Each response message

{
  "query_id": "<query id>",
  "payload": "<payload>"
}

Errors#

Errors shows how these errors are sent.

Code	Reason	Message
`CANCELLED`	`SQL_ERROR_QUERY_CANCELLED`	The query was cancelled
`INVALID_ARGUMENT`	`SQL_ERROR_DATASET_STORAGE_TYPE_NOT_SUPPORTED`	Datasets with this storage type are not yet supported by SQL queries
`INVALID_ARGUMENT`	`SQL_ERROR_EXPORT_NOT_SUPPORTED_FOR_BACKEND`	SQL export is not supported for this query backend
`INVALID_ARGUMENT`	`SQL_ERROR_INVALID_QUERY`	SQL query is invalid
`INVALID_ARGUMENT`	`SQL_ERROR_QUERY_TIMEOUT`	The query timed out before it could complete
`INVALID_ARGUMENT`	`SQL_ERROR_RESULT_SIZE_EXCEEDED`	The query result exceeded the maximum result size
`NOT_FOUND`	`SQL_ERROR_DATASETS_NOT_FOUND`	One or more requested datasets could not be found in the specified workspace
`NOT_FOUND`	`SQL_ERROR_DATA_NOT_FOUND_FOR_TYPE`	No data of the requested type exists for one or more of the requested datasets
`PERMISSION_DENIED`	`SQL_ERROR_PERMISSION_DENIED`	The caller does not have permission to perform this request
`RESOURCE_EXHAUSTED`	`SQL_ERROR_RESOURCE_EXHAUSTED`	The query exceeded a resource limit
`FAILED_PRECONDITION`	`SQL_ERROR_EXPORT_NOT_CONFIGURED`	SQL export is not configured for this deployment
`INTERNAL`	`SQL_ERROR_EXECUTION_FAILED`	An unexpected internal error occurred while executing the SQL query
`INTERNAL`	`SQL_ERROR_QUERY_SCOPE_TOO_LARGE`	The query spans more datasets than can be planned at once
`UNAUTHENTICATED`	`SQL_ERROR_UNAUTHENTICATED`	The request could not be authenticated

Types#

SqlCatalog#

The queryable logical SQL surface.

tables array of SqlCatalogTable
functions array of 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
data_type SqlCatalogDataType

SqlCatalogDataType#

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

Set at most one of the fields below.

one of scalar, array_element, map

scalar

scalar SqlCatalogScalarType

array_element

array_element SqlCatalogDataType

map

map SqlCatalogMapType

SqlCatalogFunction#

name string: Uppercase function name, e.g. AVG, COUNT, DATE_BIN.
kind SqlCatalogFunctionKind
supports_over boolean: Whether calls may carry an OVER clause. Always true for WINDOW functions, which require one.
min_args integer: Minimum number of arguments.
max_args integer: Maximum number of arguments; unset when the function is variadic.
argument_type_families 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.
return_type_family 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

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

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.

row_group_rows integer (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: 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, MIT License.