Skip to main content
Version: Latest-3.2

HTTP SQL API

Description​

StarRocks v3.2.0 introduces the HTTP SQL API for users to perform various types of queries using HTTP. Currently, this API supports SELECT, SHOW, EXPLAIN, and KILL statements.

Syntax using the curl command:

curl -X POST 'http://<fe_ip>:<fe_http_port>/api/v1/catalogs/<catalog_name>/databases/<database_name>/sql' \
   -u '<username>:<password>'  -d '{"query": "<sql_query>;", "sessionVariables":{"<var_name>":<var_value>}}' \
   --header "Content-Type: application/json"

Request message​

Request line​

POST 'http://<fe_ip>:<fe_http_port>/api/v1/catalogs/<catalog_name>/databases/<database_name>/sql'
FieldDescription
fe_ipFE node IP address.
fe_http_portFE HTTP port.
catalog_nameThe catalog name. In v3.2.0, you can use this API to query only StarRocks internal tables, which means <catalog_name> can only be set to default_catalog. Since v3.2.1, you can use this API to query tables in external catalogs.
database_nameThe database name. If no database name is specified in the request line and a table name is used in the SQL query, you must prefix the table name with its database name, for example, database_name.table_name.

  • Query data across databases in a specified catalog. If a table is used in the SQL query, you must prefix the table name with its database name.

    POST /api/v1/catalogs/<catalog_name>/sql

  • Query data from a specified catalog and database.

    POST /api/v1/catalogs/<catalog_name>/databases/<database_name>/sql

Authentication method​

Authorization: Basic <credentials>

Basic authentication is used, that is, enter the username and password for credentials (-u '<username>:<password>'). If no password is set for the username, you can pass in only <username>: and leave the password empty. For example, if the root account is used, you can enter -u 'root:'.

Request body​

-d '{"query": "<sql_query>;", "sessionVariables":{"<var_name>":<var_value>}}'
FieldDescription
queryThe SQL query, in STRING format. Only SELECT, SHOW, EXPLAIN, and KILL statements are supported. You can run only one SQL query for an HTTP request.
sessionVariablesThe session variable you want to set for the query, in JSON format. This field is optional. Default is empty. The session variable you set takes effect for the same connection and becomes ineffective when the connection is closed.

Request header​

--header "Content-Type: application/json"

This header indicates that the request body is a JSON string.

Response message​

Status code​

  • 200: HTTP request succeeded and the server is normal before data is sent to the client.
  • 4xx: HTTP request error, which indicates a client error.
  • 500 Internal Server Error: HTTP request succeeded but the server encounters an error before data is sent to the client.
  • 503: HTTP request succeeded but the FE cannot provide service.

Response header​

content-type indicates the format of the response body. Newline delimited JSON is used, which means the response body consists of multiple JSON objects that are separated by \n.

Description
content-typeThe format is Newline delimited JSON, defaults to "application/x-ndjson charset=UTF-8".
X-StarRocks-Query-IdQuery ID.

Response body​

Failed before the request is sent​

Request fails on the client side or the server encounters an error before it returns data to the client. The response body is in the following format, where msg is the error information.

{
   "status":"FAILED",
   "msg":"xxx"
}

Failed after the request is sent​

Part of the result is returned and the HTTP status code is 200. Data sending is suspended, the connection is closed, and the error is logged.

Succeeded​

Each row in the response message is a JSON object. JSON objects separated by \n.

  • For a SELECT statement, the following JSON objects are returned.
ObjectDescription
connectionIdConnection ID. You can cancel a query that is pending for a long time by calling KILL <connectionId>.
metaRepresents a column. The key is meta and the value is a JSON array, where each object in the array represents a column.
dataThe data row, where the key is data and the value is a JSON array which contains a row of data.
statisticsStatistical information of the query.
  • For a SHOW statement, meta, data, and statistics are returned.
  • For the EXPLAIN statement, an explain object is returned to show detailed execution plan of the query.

The following example uses \n as the separator. StarRocks transmits data using HTTP chunked mode. Each time the FE obtains a data chunk, it streams the data chunk to the client. The client can parse data by row, which eliminates the need for data caching and the need to wait for the entire data, reducing memory consumption for the client.

{"connectionId": 7}\n
{"meta": [
    {
      "name": "stock_symbol",
      "type": "varchar"
    },
    {
      "name": "closing_price",
      "type": "decimal64(8, 2)"
    },
    {
      "name": "closing_date",
      "type": "datetime"
    }
  ]}\n
{"data": ["JDR", 12.86, "2014-10-02 00:00:00"]}\n
{"data": ["JDR",14.8, "2014-10-10 00:00:00"]}\n
...
{"statistics": {"scanRows": 0,"scanBytes": 0,"returnRows": 9}}

Examples​

Run a SELECT query​

  • Query data from a StarRocks internal table (catalog_name is default_catalog).

    curl -X POST 'http://127.0.0.1:8030/api/v1/catalogs/default_catalog/databases/test/sql' -u 'root:' -d '{"query": "select * from agg;"}' --header "Content-Type: application/json"

    Result:

    {"connectionId":49}
    {"meta":[{"name":"no","type":"int(11)"},{"name":"k","type":"decimal64(10, 2)"},{"name":"v","type":"decimal64(10, 2)"}]}
    {"data":[1,"10.00",null]}
    {"data":[2,"10.00","11.00"]}
    {"data":[2,"20.00","22.00"]}
    {"data":[2,"25.00",null]}
    {"data":[2,"30.00","35.00"]}
    {"statistics":{"scanRows":0,"scanBytes":0,"returnRows":5}}

  • Query data from an Iceberg table.

    curl -X POST 'http://172.26.93.145:8030/api/v1/catalogs/iceberg_catalog/databases/ywb/sql' -u 'root:' -d '{"query": "select * from iceberg_analyze;"}' --header "Content-Type: application/json"

    Result:

    {"connectionId":13}
    {"meta":[{"name":"k1","type":"int(11)"},{"name":"k2","type":"int(11)"}]}
    {"data":[1,2]}
    {"data":[1,1]}
    {"statistics":{"scanRows":0,"scanBytes":0,"returnRows":2}}

Cancel a query​

To cancel a query that run an unexpected long time, you can close the connection. StarRocks will cancel this query when it detects the connection is closed.

You can also call KILL connectionId to cancel this query. For example:

curl -X POST 'http://127.0.0.1:8030/api/v1/catalogs/default_catalog/databases/test/sql' -u 'root:' -d '{"query": "kill 17;"}' --header "Content-Type: application/json"

You can obtain the connectionId from the response body or by calling SHOW PROCESSLIST. For example:

curl -X POST 'http://127.0.0.1:8030/api/v1/catalogs/default_catalog/databases/test/sql' -u 'root:' -d '{"query": "show processlist;"}' --header "Content-Type: application/json"

Run a query with a session variable set for this query​

curl -X POST 'http://127.0.0.1:8030/api/v1/catalogs/default_catalog/databases/test/sql' -u 'root:'  -d '{"query": "SHOW VARIABLES;", "sessionVariables":{"broadcast_row_limit":14000000}}'  --header "Content-Type: application/json"