HTTP API

Query with SQL via the HTTP API

Data may be queried by posting SQL to the /v1/sql API and /v1/firesql API for Firecached data. For documentation on the Spice Firecache see Broken link.

See Tables for a list of tables to query or browse the example queries listed in the menu.

Requirements and limitations

An API key is required for all SQL queries.
Results are limited to 500 rows. Use the Apache Arrow Flight API to fetch up to 1M rows in a single query or the Async HTTP API to fetch results with paging.
Requests are limited to 90 seconds.

SQL Query

POST/v1/sql

Execute a SQL query and return the results.

This endpoint allows users to execute SQL queries directly from an HTTP request. The SQL query is sent as plain text in the request body.

Header parameters

Name	Type	Required	Description
`X-API-Key`	string	Yes	Your project API key. See API keys.
`Accept`	string	Yes	The format of the response, one of 'application/json' (default), 'application/vnd.spiceai.sql.v1+json', 'text/csv' or 'text/plain'.

Request body

Required

SQL query to execute

application/json

any

text/plain

string

Example

SELECT avg(total_amount), avg(tip_amount), count(1), passenger_count FROM my_table GROUP BY passenger_count ORDER BY passenger_count ASC LIMIT 3

Example request

curl -X POST "https://data.spiceai.io/v1/sql" \
  -H "X-API-Key: <project-api-key>" \
  -H "Accept: application/json" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT :foo + 1 AS the_answer",
    "parameters": {
      "foo": 41
    }
  }'

Responses

200OK

SQL query executed successfully

application/json

array of any

Example

[
  {
    "AVG(my_table.tip_amount)": 3.072259971396793,
    "AVG(my_table.total_amount)": 25.327816939456525,
    "COUNT(Int64(1))": 31465,
    "passenger_count": 0
  },
  {
    "AVG(my_table.tip_amount)": 3.3712622884680057,
    "AVG(my_table.total_amount)": 26.205230445474996,
    "COUNT(Int64(1))": 2188739,
    "passenger_count": 1
  },
  {
    "AVG(my_table.tip_amount)": 3.7171302113290854,
    "AVG(my_table.total_amount)": 29.520659930930304,
    "COUNT(Int64(1))": 405103,
    "passenger_count": 2
  }
]

text/csv

string

Example

"AVG(my_table.tip_amount)","AVG(my_table.total_amount)","COUNT(Int64(1))","passenger_count"
3.072259971396793,25.327816939456525,31465,0
3.3712622884680057,26.205230445474996,2188739,1
3.7171302113290854,29.520659930930304,405103,2

text/plain

string

Example

            +----------------------------+----------------------------+----------------+---------------------+
            | "AVG(my_table.tip_amount)"  | "AVG(my_table.total_amount)" | "COUNT(Int64(1))" | "passenger_count"   |
            +----------------------------+----------------------------+----------------+---------------------+
            | 3.072259971396793           | 25.327816939456525         | 31465          | 0                   |
            +----------------------------+----------------------------+----------------+---------------------+
            | 3.3712622884680057          | 26.205230445474996         | 2188739        | 1                   |
            +----------------------------+----------------------------+----------------+---------------------+
            | 3.7171302113290854          | 29.520659930930304         | 405103         | 2                   |
            +----------------------------+----------------------------+----------------+---------------------+

application/vnd.spiceai.sql.v1+json

any

Example

{
  "row_count": 3,
  "schema": {
    "fields": [
      {
        "name": "AVG(my_table.tip_amount)",
        "data_type": "Float64",
        "nullable": false,
        "dict_id": 0,
        "dict_is_ordered": false
      },
      {
        "name": "AVG(my_table.total_amount)",
        "data_type": "Float64",
        "nullable": false,
        "dict_id": 0,
        "dict_is_ordered": false
      },
      {
        "name": "COUNT(Int64(1))",
        "data_type": "Int64",
        "nullable": false,
        "dict_id": 0,
        "dict_is_ordered": false
      },
      {
        "name": "passenger_count",
        "data_type": "Int64",
        "nullable": false,
        "dict_id": 0,
        "dict_is_ordered": false
      }
    ]
  },
  "data": [
    {
      "AVG(my_table.tip_amount)": 3.072259971396793,
      "AVG(my_table.total_amount)": 25.327816939456525,
      "COUNT(Int64(1))": 31465,
      "passenger_count": 0
    },
    {
      "AVG(my_table.tip_amount)": 3.3712622884680057,
      "AVG(my_table.total_amount)": 26.205230445474996,
      "COUNT(Int64(1))": 2188739,
      "passenger_count": 1
    },
    {
      "AVG(my_table.tip_amount)": 3.7171302113290854,
      "AVG(my_table.total_amount)": 29.520659930930304,
      "COUNT(Int64(1))": 405103,
      "passenger_count": 2
    }
  ]
}

400Bad Request
Invalid SQL query or malformed input
text/plain
string
Example
```
Error reading query: invalid UTF-8 sequence
```
500Internal Server Error
Internal server error
text/plain
string
Example
```
Unexpected internal server error occurred
```

OpenAPIspiceai/spiceai v2.3.2