SQL REST APIedit

The SQL REST API accepts SQL in a JSON document, executes it, and returns the results. For example:

POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}

Which returns:

     author      |        name        |  page_count   | release_date
-----------------+--------------------+---------------+------------------------
Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z
Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z
Frank Herbert    |Dune                |604            |1965-06-01T00:00:00.000Z
Alastair Reynolds|Revelation Space    |585            |2000-03-15T00:00:00.000Z
James S.A. Corey |Leviathan Wakes     |561            |2011-06-02T00:00:00.000Z

Using Kibana Console. If you are using Kibana Console. (which is highly recommended), take advantage of the triple quotes """ when creating the query. This not only automatically escapes double quotes (") inside the query string but also support multi-line as shown below: images/sql/rest/console-triple-quotes.png

Response Data Formatsedit

While the textual format is nice for humans, computers prefer something more structured.

Elasticsearch SQL can return the data in the following formats which can be set either through the format property in the URL or by setting the Accept HTTP header:

Note

The URL parameter takes precedence over the Accept HTTP header. If neither is specified then the response is returned in the same format as the request.

format

Accept HTTP header

Description

Human Readable

csv

text/csv

Comma-separated values

json

application/json

JSON (JavaScript Object Notation) human-readable format

tsv

text/tab-separated-values

Tab-separated values

txt

text/plain

CLI-like representation

yaml

application/yaml

YAML (YAML Ain’t Markup Language) human-readable format

Binary Formats

cbor

application/cbor

Concise Binary Object Representation

smile

application/smile

Smile binary data format similar to CBOR

POST /_xpack/sql?format=json
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 5
}

Which returns:

{
    "columns": [
        {"name": "author",       "type": "text"},
        {"name": "name",         "type": "text"},
        {"name": "page_count",   "type": "short"},
        {"name": "release_date", "type": "datetime"}
    ],
    "rows": [
        ["Peter F. Hamilton",  "Pandora's Star",       768, "2004-03-02T00:00:00.000Z"],
        ["Vernor Vinge",       "A Fire Upon the Deep", 613, "1992-06-01T00:00:00.000Z"],
        ["Frank Herbert",      "Dune",                 604, "1965-06-01T00:00:00.000Z"],
        ["Alastair Reynolds",  "Revelation Space",     585, "2000-03-15T00:00:00.000Z"],
        ["James S.A. Corey",   "Leviathan Wakes",      561, "2011-06-02T00:00:00.000Z"]
    ],
    "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8="
}

Paginating through a large responseedit

Using the example above, onu can continue to the next page by sending back the cursor field. In case of text format the cursor is returned as Cursor http header.

POST /_xpack/sql?format=json
{
    "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklQMHhRUEtld3RsNnFtYU1hQQ==:BAFmBGRhdGUBZgVsaWtlcwFzB21lc3NhZ2UBZgR1c2Vy9f///w8="
}

Which looks like:

{
    "rows" : [
        ["Dan Simmons",        "Hyperion",             482,  "1989-05-26T00:00:00.000Z"],
        ["Iain M. Banks",      "Consider Phlebas",     471,  "1987-04-23T00:00:00.000Z"],
        ["Neal Stephenson",    "Snow Crash",           470,  "1992-06-01T00:00:00.000Z"],
        ["Frank Herbert",      "God Emperor of Dune",  454,  "1981-05-28T00:00:00.000Z"],
        ["Frank Herbert",      "Children of Dune",     408,  "1976-04-21T00:00:00.000Z"]
    ],
    "cursor" : "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWODRMaXBUaVlRN21iTlRyWHZWYUdrdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl9f///w8="
}

Note that the columns object is only part of the first page.

You’ve reached the last page when there is no cursor returned in the results. Like Elasticsearch’s scroll, SQL may keep state in Elasticsearch to support the cursor. Unlike scroll, receiving the last page is enough to guarantee that the Elasticsearch state is cleared.

To clear the state earlier, you can use the clear cursor command:

POST /_xpack/sql/close
{
    "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklQMHhRUEtld3RsNnFtYU1hQQ==:BAFmBGRhdGUBZgVsaWtlcwFzB21lc3NhZ2UBZgR1c2Vy9f///w8="
}

Which will like return the

{
    "succeeded" : true
}

Filtering using Elasticsearch query DSLedit

You can filter the results that SQL will run on using a standard Elasticsearch query DSL by specifying the query in the filter parameter.

POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "filter": {
        "range": {
            "page_count": {
                "gte" : 100,
                "lte" : 200
            }
        }
    },
    "fetch_size": 5
}

Which returns:

    author     |                name                |  page_count   | release_date
---------------+------------------------------------+---------------+------------------------
Douglas Adams  |The Hitchhiker's Guide to the Galaxy|180            |1979-10-12T00:00:00.000Z

Supported REST parametersedit

In addition to the query and fetch_size, a request a number of user-defined fields for specifying the request time-outs or localization information (such as timezone).

The table below lists the supported parameters:

name

Default value

Description

query

Mandatory

SQL query to execute

fetch_size

1000

The maximum number of rows (or entries) to return in one response

filter

none

Optional Elasticsearch query DSL for additional filtering.

request_timeout

90s

The timeout before the request fails.

page_timeout

45s

The timeout before a pagination request fails.

time_zone

Z (or UTC)

Time-zone in ISO 8601 used for executing the query on the server. More information available here.

Do note that most parameters (outside the timeout ones) make sense only during the initial query - any follow-up pagination request only requires the cursor parameter as explained in the pagination chapter. That’s because the query has already been executed and the calls are simply about returning the found results - thus the parameters are simply ignored.