Ocient HTTP Query API

The Ocient Hyperscale Data Warehouse™ provides an HTTP Query API that enables the execution of SQL statements. This page uses command-line examples using curl.

The HTTP Query API returns data in JSON format by default. The default header value is Accept: application/json.

API Documentation

The API follows OpenAPI Specification (Swagger®) guidelines, which you can access in these formats from any SQL Node address {sql_node_address}:

• YAML format: /openapi.yaml endpoint
• JSON format: /openapi.json endpoint

YAML Download Example

curl https://{sql_node_address}/openapi.yaml

JSON Download Example

curl https://{sql_node_address}/openapi.json

You can use these specifications with various OpenAPI tools for code generation, testing, and documentation.

Connection Setup

Before using the API, you must establish an open port for a SQL Node.

The connectivity pool defines connection settings for SQL Nodes, including their open port and address. For details, see CONNECTIVITY POOL.

Enable the OpenAPI Port

To open a port, execute this ALTER CONNECTIVITY POOL SQL statement on a connectivity pool in your system. Change the connectivity pool and node values in the example according to your system.

ALTER CONNECTIVITY_POOL benchmark_sql_connectivity_pool
    ALTER PARTICIPANT "benchmark-sql0" SET openapi_port 443;

Check Port Configuration

Verify your port configuration by executing this query.

SELECT c.name, n.name, openapi_port
    FROM sys.connectivity_pool_participants AS cp
    LEFT JOIN sys.connectivity_pools AS c ON cp.id = c.id
    LEFT JOIN sys.nodes AS n ON cp.node_id = n.id;

The output displays all configured connectivity pools with their associated nodes and OpenAPI ports.

SSL Certificate Setup

The Ocient® HTTP Query API supports two options for handling SSL certificates.

Secure Connection Using TLS

You can use your own certificates for TLS-secured connections by configuring certificate files in your Ocient install. For details, see Secure Connections Using TLS.

Unsecured Connection

For quick testing or in non-production environments, you can bypass certificate validation by adding the -k or --insecure flag to your curl command.

Example

curl -k -u 'admin@system:admin' https://<sql-node-address>/v1/execute/system \
    -d '{"statement":"SELECT * FROM sys.dummy2;","format":"collection"}'

Authentication Methods

The Ocient HTTP Query API accepts user credentials in various formats designed for different use cases.

Simple API Requests

For simple queries, you can include user credentials in the request.

In this example, the request includes credentials after the -u flag.

curl -u 'admin@system:admin' https://sql_node/v1/execute/system \
    -d '{"statement":"SELECT * FROM sys.dummy2;", "format": "collection"}'

Bearer Token Authentication

For continuous access, you can obtain an authentication token by making a request to the login endpoint. Replace the text in this example with the address of your SQL Node, your username, your password, and the name of your database.

curl -X POST https://sql_node/v1/login \
    -H "Content-Type: application/json" \
    -d '{
        "username": "admin@system",
        "password": "admin",
        "database": "system"
    }'

This request returns an authentication token that you can add to subsequent requests.

curl -X POST https://sql_node/v1/execute \
    -H "Authorization: Bearer TEST_ACCESS_TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
        "statement": "SELECT * FROM sys.dummy2;",
        "database": "system",
        "format": "collection"
    }'

OpenID Connect (SSO)

For SSO authentication, you can use OpenID Connect by targeting the sso_authentication endpoint with a callback path. Replace the text in this example with the name of your instance, the name of your database, and your callback path.

curl -X POST https://ocient-instance/v1/sso_authentication \
    -H "Content-Type: application/json" \
    -d '{
        "database": "system",
        "sso_callback_path": "/app_callback"
    }'

After the redirection and authentication are complete, you can include your token in subsequent requests.

curl -X POST https://ocient-instance/v1/sso_token \
    -H "Content-Type: application/json" \
    -d '{
        "id_token": "TEST_ID_TOKEN",
        "database": "system"
    }'

API Endpoint Documentation

The Ocient HTTP Query API supports these endpoints.

For API requests, JSON data must use double quotes (") for all keys and string values. Single quotes should enclose the JSON payload, as shown in this example (see the payload section following the -d flag).

curl -X POST \
    https://my_sql_node.com/v1/execute \
    -H "Content-Type: application/json" \
    -d '{ "query": "SELECT * FROM my_table WHERE status = \"active\"", "database": "my_database" }'

Single or double quotes are acceptable for parameters not nested in JSON objects.

execute

POST https://{sql_node}/v1/execute/{database}

Executes a SQL statement and returns the results. Supports requests for both regular and streaming responses.

With streaming, results return as they become available using the HTTP chunked transfer encoding. Each chunk contains a valid JSON object that you can parse independently.

For details on streaming, including large-response configuration, see the header parameters.

You can specify the database in the request body. If you do not specify the database, the Ocient System defaults to the database specified in the authentication token or system settings. If you specify a database in the request, this value overrides any alternate database value specified as a body parameter.

Path Parameters

Query Parameters

Header Parameters

Body Parameters

Example Request

curl -u 'admin@system:admin' https://my_sql_node.com/v1/execute/system \
    -d '{"statement":"SELECT * FROM sys.dummy2", "format": "collection"}'

Example Response

{
    "query_id": "ac4dd039-e385-400b-a428-fd1fdcd02e30",
    "status": {
        "reason": "The operation completed successfully",
        "sql_state": "00000",
        "vendor_code": 0
    },
    "data": [
        {
            "c1": 1
        },
        {
            "c1": 2
        }
    ]
}

execute

GET https://{sql_node}/v1/execute/{database}

Alternative GET method for executing SQL statements. This method passes parameters as URL query parameters. This method does not support the params body parameter.

Specify which database to access in the query parameters. If you do not specify a database, the connection defaults to the database from the authentication token or system settings.

This method is most suitable for simple, read-only queries where the statement can be safely included in a URL. For complex queries or those with parameters, use the POST method instead.

Path Parameters

Query Parameters

Header Parameters

Example Request

curl -u 'admin@system:admin' \
    'https://my_sql_node.com/v1/execute?statement=SELECT%20*%20FROM%20sys.dummy2&database=system&format=collection'

Example Response

{
    "query_id": "2d5af900-cb02-44f4-b974-6264d2133e96",
    "status": {
        "reason": "The operation completed successfully",
        "sql_state": "00000",
        "vendor_code": 0
    },
    "data": [
        {
            "c1": 1
        },
        {
            "c1": 2
        }
    ]
}

info

GET https://{sql_node}/v1/info

Returns basic system version information about the Ocient System and the HTTP Query API server.

You can use this endpoint to verify connectivity and to check compatible versions.

Path Parameters

Example Request

curl -u 'admin@system:admin' \
    'https://my_sql_node.com/v1/info'

Example Response

{
    "default_database": "system",
    "openapi_version": "1.1.0",
    "status": {
        "vendor_code": 0,
        "sql_state": "00000",
        "reason": "The operation completed successfully"
    }
}

login

POST https://{sql_node}/v1/login

Authenticates a user with a username and password and then returns a token for use in subsequent API calls. This request also sets a session cookie.

Include the returned access token in the authorization header for subsequent requests in the format: Authorization: Bearer {token}.

Path Parameters

Body Parameters

Example Request

curl -X POST https://my_sql_node.com/v1/login \
    -H "Content-Type: application/json" \
    -d '{
        "username": "admin",
        "password": "admin",
        "database": "system"
    }'

Example Response

{
    "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
    "username": "jdoe",
    "database": "retail_analytics",
    "expires_in": 3600,
    "status": {
        "reason": "Authentication successful",
        "sql_state": "00000",
        "vendor_code": 0
    }
}

logout

POST https://{sql_node}/v1/logout

Log out from a SQL session. This clears any associated cookies, but does not invalidate any access tokens.

This endpoint terminates only the cookie-based session. Any bearer tokens that were previously issued continue to work until they expire.

You must discard any stored bearer tokens to complete the log out in a client application.

Path Parameters

Body Parameters

Example Request

curl -X POST https://my_sql_node.com/v1/logout
    -d '{
        "database": "retail_analytics"
    }

Example Response

{
  "status": {
    "reason": "Logged out successfully",
    "sql_state": "00000",
    "vendor_code": 0
  }
}

sso_authentication

POST https://{sql_node}/v1/sso_authentication

Initiates the OpenID Connect authentication process by redirecting to the authorization server.

This endpoint begins the standard OpenID Connect authentication process:

1. The client calls this endpoint with a callback path.
2. The server responds with a redirect to the identity provider.
3. The user authenticates with the identity provider.
4. The identity provider redirects back to the callback endpoint.
5. The client can exchange the authorization code for an access token.

Body Parameters

Example Request

curl -X POST https://my_sql_node.com/v1/sso_authentication \
    -H "Content-Type: application/json" \
    -d '{
        "database": "retail_analytics",
        "sso_callback_path": "/auth/callback",
        "do_redirect": true
    }'

Example Response

{
  "status": {
    "reason": "Query executed successfully",
    "sql_state": "00000",
    "vendor_code": 0
  }
}

{
  "message": "Ain't no cake like that."
}

callback

GET https://{sql_node}/v1/callback

Provides the authentication token for the OpenID Connect authentication process. The authorization server redirects to callback path after successful authentication.

This endpoint receives the authorization code from the OpenID provider and exchanges it for a token. The provider redirects the user to the application callback URL specified in the initial authentication request.

The client application should not call this endpoint directly. The OpenID provider automatically calls this endpoint in the authentication process.

Path Parameters

Query Parameters

Example Request

curl -X GET "https://my_sql_node.com/v1/callback?code=4/P7q7W91a-oMsCeLvIaQm6bTrgtp7&state=fRJfv29f3v39Jf39dJf93jf"

Example Response

{
  "status": {
    "reason": "Query executed successfully",
    "sql_state": "00000",
    "vendor_code": 0
  }
}

sso_token

POST https://{sql_node}/v1/sso_token

Exchange an OpenID Connect identifier token or access token for an Ocient access token.

This endpoint allows clients to directly exchange tokens without following the full browser-based authentication process. This exchange is useful for server-to-server scenarios or when the client already has a valid OpenID token from another process.

Path Parameters

Body Parameters

Example Request

curl -X POST https://my_sql_node.com/v1/sso_token \
    -H "Content-Type: application/json" \
    -d '{
        "id_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
        "database": "retail_analytics"
    }'

Example Response

{
  "id_token": "eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...",
  "database": "retail_analytics"
}

sso_device_grant

POST https://{sql_node}/v1/sso_device_grant

Retrieve an OpenID device grant code that the Ocient System can verify and use with the sso_device_grant_verify endpoint.

The device grant process is intended for devices with limited input capabilities or no web browser:

1. Call this endpoint to retrieve a user code and verification URI.
2. Display the user code and verification URI to the user.
3. The user visits the verification URI on another device and enters the code.
4. Call the sso_device_grant_verify endpoint to check if the user has completed verification.

Body Parameters

Example Request

curl -X POST https://my_sql_node.com/v1/sso_device_grant \
    -H "Content-Type: application/json" \
    -d '{
        "database": "retail_analytics"
    }'

Example Response

{
  "verification_uri_complete": "https://auth.example.com/device?code=BCDFGHJK",
  "verification_uri": "https://auth.example.com/device",
  "user_code": "BCDFGHJK",
  "status": {
    "reason": "Device authorization initiated",
    "sql_state": "00000",
    "vendor_code": 0
  }
}

sso_device_grant_verify

POST https://{sql_node}/v1/sso_device_grant_verify

Verify a previous device grant request and return an authorization token.

After initiating a device grant process with the sso_device_grant endpoint, use this endpoint to check if the user has completed the verification process. If the verification is successful, the endpoint returns an authorization token that you can use for subsequent API calls.

You can call this endpoint multiple times until one of these outcomes:

The user completes the verification (returns 200 OK with a token).

The timeout is reached (returns error).

The verification is canceled (returns error).

Path Parameters

Body Parameters

Example Request

curl -X POST https://my_sql_node.com/v1/sso_device_grant_verify \
    -H "Content-Type: application/json" \
    -d '{
        "user_code": "BCDFGHJK",
        "timeout": 60,
        "database": "retail_analytics"
    }'

Example Response

{
  "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJqZG9lIiwiZGIiOiJyZXRhaWxfYW5hbHl0aWNzIiwiZXhwIjoxNjM1NDYxNzEyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c",
  "username": "jdoe",
  "database": "retail_analytics",
  "status": {
    "reason": "Authentication successful",
    "sql_state": "00000",
    "vendor_code": 0
  }
}

token_refresh

POST https://{sql_node}/v1/token_refresh

Refreshes an existing access token, extending its validity period. Call this endpoint before the current token expires to maintain uninterrupted access.

Use the expires_in value from the login or previous refresh response to determine when to refresh the token. A common practice is to refresh when the token has half of its time remaining.

On success, the endpoint returns the 200 response and a JSON object containing a new access token and metadata about the refreshed session. Replace the access token in your client with the new token returned by the endpoint, and use the expires_in value to decide when to refresh again.

Path Parameters

Header Parameters

Body Parameters

Example Request

curl -X POST https://your-ocient-instance/v1/token_refresh \
    -H "Authorization: Bearer YOUR_CURRENT_TOKEN" \
    -H "Content-Type: application/json" \
    -d '{
        "database": "retail_analytics"
    }'

Example Response

{
    "access_token": "eyJhb...",
    "username": "jdoe",
    "database": "retail_analytics",
    "expires_in": 3600,
    "status": {
        "reason": "Token refreshed successfully",
        "sql_state": "00000",
        "vendor_code": 0
    }
}

HTTP API Query Examples

These examples demonstrate how you can sequence API endpoints for common use cases.

Simple Queries

This example query format is appropriate for simple read-only queries. The example provides user credentials admin@system:admin in the request. The https://sql_node/v1/execute/system API call specifies the execute endpoint with the SQL Node address sql_node and the system database. In this example, the SQL statement is SELECT * FROM sys.dummy2;. The collection format option returns the result in JSON format.

curl -u 'admin@system:admin' https://sql_node/v1/execute/system \
    -d '{"statement":"SELECT * FROM sys.dummy2;", "format": "collection"}'

Large Result Set Queries

Specify the content type as JSON data with the Content-Type header option. When working with large amounts of data, enable streaming using the application/stream+json value with the Accept header option. The request also compresses the response using gzip compression with the Accept-Encoding header option. In this example, the SQL statement is SELECT * FROM large_table;. The example specifies the fetch_size value to set the number of rows each chunk of streaming data contains to 1000.

curl -u 'admin@system:admin' https://sql_node/v1/execute/system \
    -H "Content-Type: application/json" \
    -H "Accept: application/stream+json" \
    -H "Accept-Encoding: gzip" \
    -d '{
        "statement": "SELECT * FROM large_table;",
        "fetch_size": 1000
    }'

Token Authentication Script

This example uses a bash script to retrieve a token for the subsequent curl query. Use the login endpoint to retrieve the token. Specify the username, password, and database name. Then, use the execute endpoint to execute the SELECT * FROM large_table; SQL statement. Specify the token with the Authorization header option. The gunzip command decompresses the gzip-compressed API response so it is readable in JSON format.

# Retrieve the token
TOKEN=$(curl -s -X POST https://sql_node/v1/login \
    -H "Content-Type: application/json" \
    -d '{
        "username": "admin@system",
        "password": "admin",
        "database": "system"
    }' | jq -r '.access_token')
# Then use the token for a query with streaming and compression
curl -X POST https://sql_node/v1/execute \
    -H "Authorization: Bearer $TOKEN" \
    -H "Content-Type: application/json" \
    -H "Accept: application/stream+json" \
    -H "Accept-Encoding: gzip" \
    -d '{
        "statement": "SELECT * FROM large_table;",
        "database": "system",
        "fetch_size": 1000
    }' | gunzip

Save Results to a File

This example saves the query results in a tables.json file. The example uses the SELECT * FROM sys.tables; SQL statement.

curl -u 'admin@system:admin' https://sql_node/v1/execute/system \
    -H "Content-Type: application/json" \
    -d '{
        "statement": "SELECT * FROM sys.tables;",
        "format": "collection"
    }' | jq '.data' > tables.json

Use a Wrapper Script

This example specifies the connection parameters in the ocient-query.sh file. To run the file at the command line, you must also include an argument for executing the SQL statement (for example: ./ocient-query.sh "SELECT * FROM my_table;".

#!/bin/bash
# ocient-query.sh
HOST="sql-node.com"
USER="admin"
PASSWORD="admin"
DATABASE="system"
QUERY="$1"
curl -s -u "${USER}@${DATABASE}:${PASSWORD}" "https://${HOST}/v1/execute/${DATABASE}" \
    -H "Content-Type: application/json" \
    -d "{
        \"statement\": \"${QUERY}\",
        \"format\": \"collection\"
    }" | jq '.data'

Related Links

Swagger

General SQL Syntax