vtlim commented on code in PR #14711: URL: https://github.com/apache/druid/pull/14711#discussion_r1279979758
########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. Review Comment: ```suggestion Submits a SQL-based query in the JSON request body. Returns a JSON object with the query results and optional metadata for the results. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. Review Comment: ```suggestion In this topic, `http://ROUTER_IP:ROUTER_PORT` is a placeholder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. + + If working with an older version of Druid, set `header` to `true` to verify compatibility. Druid returns the HTTP header `X-Druid-SQL-Header-Included: yes` when the client connects to an older Druid version with support for the `typesHeader` and `sqlTypesHeader` parameters. Additionally, Druid returns a `X-Druid-SQL-Query-Id` HTTP header with the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) if specified, else Druid will generate a SQL query ID. +* `typesHeader`: Adds Druid runtime type information in the header. Can only be set when `header` is also `true`. +* `sqlTypesHeader`: Adds SQL type information in the header. Can only be set when `header` is also `true`. +* `context`: JSON object containing optional [SQL query context parameters](../querying/sql-query-context.md), such as to set the query ID, time zone, and whether to use an approximation algorithm for distinct count. +* `parameters`: List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types. Review Comment: `````suggestion * `parameters`: List of query parameters for parameterized queries. Each element in the array should be a JSON object containing the parameter's SQL data type and the parameter value. For a list of supported SQL types, see [Data types](../querying/sql-data-types.md). ````` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. Review Comment: "Sent with" in this line and the following ones should be "Returns" ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. + + If working with an older version of Druid, set `header` to `true` to verify compatibility. Druid returns the HTTP header `X-Druid-SQL-Header-Included: yes` when the client connects to an older Druid version with support for the `typesHeader` and `sqlTypesHeader` parameters. Additionally, Druid returns a `X-Druid-SQL-Query-Id` HTTP header with the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) if specified, else Druid will generate a SQL query ID. +* `typesHeader`: Adds Druid runtime type information in the header. Can only be set when `header` is also `true`. +* `sqlTypesHeader`: Adds SQL type information in the header. Can only be set when `header` is also `true`. +* `context`: JSON object containing optional [SQL query context parameters](../querying/sql-query-context.md), such as to set the query ID, time zone, and whether to use an approximation algorithm for distinct count. +* `parameters`: List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types. + +#### Responses + +<!--DOCUSAURUS_CODE_TABS--> + +<!--200 SUCCESS--> -There are a variety of [SQL query context parameters](../querying/sql-query-context.md) you can provide by adding a "context" map, -like: +*Successfully submitted query* + +<!--400 BAD REQUEST--> + +*Error thrown due to bad query. Returns a JSON object detailing the error with the following format:* ```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "context" : { - "sqlTimeZone" : "America/Los_Angeles" - } + "error": "A well-defined error code.", + "errorMessage": "A message with additional details about the error.", + "errorClass": "Class of exception that caused this error.", + "host": "The host on which the error occurred." } ``` +<!--500 INTERNAL SERVER ERROR--> -Parameterized SQL queries are also supported: +*Request not sent due to unexpected conditions. Returns a JSON object detailing the error with the following format:* ```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = ? AND __time > ?", - "parameters": [ - { "type": "VARCHAR", "value": "bar"}, - { "type": "TIMESTAMP", "value": "2000-01-01 00:00:00" } - ] + "error": "A well-defined error code.", + "errorMessage": "A message with additional details about the error.", + "errorClass": "Class of exception that caused this error.", + "host": "The host on which the error occurred." } ``` -Metadata is available over HTTP POST by querying [metadata tables](../querying/sql-metadata-tables.md). +<!--END_DOCUSAURUS_CODE_TABS--> + +--- -### Responses -#### Result formats +#### Sample request -Druid SQL's HTTP POST API supports a variety of result formats. You can specify these by adding a `resultFormat` parameter, like: +The following example retrieves all rows in the `wikipedia` datasource where the `user` is `BlueMoon2662`. The query is assigned the ID `request01` using the `sqlQueryId` parameter. Optional properties `header`, `typesHeader`, and `sqlTypesHeader` are included to add type information to the response. + +<!--DOCUSAURUS_CODE_TABS--> + +<!--cURL--> + +```shell +curl "http://ROUTER_IP:ROUTER_PORT/druid/v2/sql"; \ +--header 'Content-Type: application/json' \ +--data '{ + "query": "SELECT * FROM wikipedia WHERE user='\''BlueMoon2662'\''", + "context" : {"sqlQueryId" : "request01"}, + "header" : true, + "typesHeader" : true, + "sqlTypesHeader" : true +}' +``` + +<!--HTTP--> + +```HTTP +POST /druid/v2/sql HTTP/1.1 +Host: http://ROUTER_IP:ROUTER_PORT +Content-Type: application/json +Content-Length: 192 -```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "resultFormat" : "array" + "query": "SELECT * FROM wikipedia WHERE user='BlueMoon2662'", + "context" : {"sqlQueryId" : "request01"}, + "header" : true, + "typesHeader" : true, + "sqlTypesHeader" : true } ``` -To request a header with information about column names, set `header` to true in your request. -When you set `header` to true, you can optionally include `typesHeader` and `sqlTypesHeader` as well, which gives -you information about [Druid runtime and SQL types](../querying/sql-data-types.md) respectively. You can request all these headers -with a request like: +<!--END_DOCUSAURUS_CODE_TABS--> + +#### Sample response + +<details> + <summary>Click to show sample response</summary> ```json -{ - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "resultFormat" : "array", - "header" : true, - "typesHeader" : true, - "sqlTypesHeader" : true -} +[ + { + "__time": { + "type": "LONG", + "sqlType": "TIMESTAMP" + }, + "channel": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "cityName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "comment": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "countryIsoCode": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "countryName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "isAnonymous": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isMinor": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isNew": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isRobot": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isUnpatrolled": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "metroCode": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "namespace": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "page": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "regionIsoCode": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "regionName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "user": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "delta": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "added": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "deleted": { + "type": "LONG", + "sqlType": "BIGINT" + } + }, + { + "__time": "2015-09-12T00:47:53.259Z", + "channel": "#ja.wikipedia", + "cityName": "", + "comment": "/* 対戦通算成績と得失点 */", + "countryIsoCode": "", + "countryName": "", + "isAnonymous": 0, + "isMinor": 1, + "isNew": 0, + "isRobot": 0, + "isUnpatrolled": 0, + "metroCode": 0, + "namespace": "Main", + "page": "アルビレックス新潟の年度別成績一覧", + "regionIsoCode": "", + "regionName": "", + "user": "BlueMoon2662", + "delta": 14, + "added": 14, + "deleted": 0 + } +] ``` +</details> -The following table shows supported result formats: +### Cancel a query -|Format|Description|Header description|Content-Type| -|------|-----------|------------------|------------| -|`object`|The default, a JSON array of JSON objects. Each object's field names match the columns returned by the SQL query, and are provided in the same order as the SQL query.|If `header` is true, the first row is an object where the fields are column names. Each field's value is either null (if `typesHeader` and `sqlTypesHeader` are false) or an object that contains the Druid type as `type` (if `typesHeader` is true) and the SQL type as `sqlType` (if `sqlTypesHeader` is true).|application/json| -|`array`|JSON array of JSON arrays. Each inner array has elements matching the columns returned by the SQL query, in order.|If `header` is true, the first row is an array of column names. If `typesHeader` is true, the next row is an array of Druid types. If `sqlTypesHeader` is true, the next row is an array of SQL types.|application/json| -|`objectLines`|Like `object`, but the JSON objects are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `object`.|text/plain| -|`arrayLines`|Like `array`, but the JSON arrays are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `array`, except the rows are separated by newlines.|text/plain| -|`csv`|Comma-separated values, with one row per line. Individual field values may be escaped by being surrounded in double quotes. If double quotes appear in a field value, they will be escaped by replacing them with double-double-quotes like `""this""`. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `array`, except the lists are in CSV format.|text/csv| +Cancels a query on the Router or the Broker with the associated `sqlQueryId`. Queries can only be canceled with a valid `sqlQueryId`. The `sqlQueryId` must be set in the query context when the query is submitted. Note that Druid does not enforce unique `sqlQueryId` in the query context. Druid cancels all requests that use the same query ID. -If `typesHeader` is set to true, [Druid type](../querying/sql-data-types.md) information is included in the response. Complex types, -like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, -or as `COMPLEX` if the particular type name is unknown or mixed. If `sqlTypesHeader` is set to true, -[SQL type](../querying/sql-data-types.md) information is included in the response. It is possible to set both `typesHeader` and -`sqlTypesHeader` at once. Both parameters require that `header` is also set. +When you cancel a query, Druid handles the cancellation in a best-effort manner. Druid immediately marks the query as canceled and aborts the query execution as soon as possible. However, the query may continue running for a short time after you make the cancellation request. -To aid in building clients that are compatible with older Druid versions, Druid returns the HTTP header -`X-Druid-SQL-Header-Included: yes` if `header` was set to true and if the version of Druid the client is connected to -understands the `typesHeader` and `sqlTypesHeader` parameters. This HTTP response header is present irrespective of -whether `typesHeader` or `sqlTypesHeader` are set or not. +Cancellation requests require READ permission on all resources used in the SQL query. -Druid returns the SQL query identifier in the `X-Druid-SQL-Query-Id` HTTP header. -This query id will be assigned the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) -if specified, else Druid will generate a SQL query id for you. -#### Errors +#### URL -Errors that occur before the response body is sent will be reported in JSON, with an HTTP 500 status code, in the -same format as [native Druid query errors](../querying/querying.md#query-errors). If an error occurs while the response body is -being sent, at that point it is too late to change the HTTP status code or report a JSON error, so the response will -simply end midstream and an error will be logged by the Druid server that was handling your request. +<code class="deleteAPI">DELETE</code> <code>/druid/v2/sql/:sqlQueryId</code> -As a caller, it is important that you properly handle response truncation. This is easy for the `object` and `array` -formats, since truncated responses will be invalid JSON. For the line-oriented formats, you should check the -trailer they all include: one blank line at the end of the result set. If you detect a truncated response, either -through a JSON parsing error or through a missing trailing newline, you should assume the response was not fully -delivered due to an error. +#### Responses -## Cancel a query +<!--DOCUSAURUS_CODE_TABS--> -You can use the HTTP DELETE method to cancel a SQL query on either the Router or the Broker. When you cancel a query, Druid handles the cancellation in a best-effort manner. It marks the query canceled immediately and aborts the query execution as soon as possible. However, your query may run for a short time after your cancellation request. +<!--202 SUCCESS--> -Druid SQL's HTTP DELETE method uses the following syntax: -``` -DELETE https://ROUTER:8888/druid/v2/sql/{sqlQueryId} -``` +*Successfully deleted query* -The DELETE method requires the `sqlQueryId` path parameter. To predict the query id you must set it in the query context. Druid does not enforce unique `sqlQueryId` in the query context. If you issue a cancel request for a `sqlQueryId` active in more than one query context, Druid cancels all requests that use the query id. +<!--403 FORBIDDEN--> -For example if you issue the following query: -```bash -curl --request POST 'https://ROUTER:8888/druid/v2/sql' \ ---header 'Content-Type: application/json' \ ---data-raw '{"query" : "SELECT sleep(CASE WHEN sum_added > 0 THEN 1 ELSE 0 END) FROM wikiticker WHERE sum_added > 0 LIMIT 15", -"context" : {"sqlQueryId" : "myQuery01"}}' +*Authorization failure* + +<!--404 NOT FOUND--> + +*Invalid `sqlQueryId` or query was completed before cancellation request* + +<!--END_DOCUSAURUS_CODE_TABS--> + +--- + +#### Sample request + +The following example cancels a request with specified query ID `request01`. Review Comment: ```suggestion The following example cancels a request with the query ID `request01`. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. + + If working with an older version of Druid, set `header` to `true` to verify compatibility. Druid returns the HTTP header `X-Druid-SQL-Header-Included: yes` when the client connects to an older Druid version with support for the `typesHeader` and `sqlTypesHeader` parameters. Additionally, Druid returns a `X-Druid-SQL-Query-Id` HTTP header with the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) if specified, else Druid will generate a SQL query ID. +* `typesHeader`: Adds Druid runtime type information in the header. Can only be set when `header` is also `true`. +* `sqlTypesHeader`: Adds SQL type information in the header. Can only be set when `header` is also `true`. Review Comment: ```suggestion * `sqlTypesHeader`: Adds SQL type information in the header. Requires that `header` is also `true`. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. Review Comment: ```suggestion * `header`: Boolean value that determines whether to return information on column names. When set to `true`, the Druid returns the column names as the first row of the results. To also get information on the column types, set `typesHeader` or `sqlTypesHeader` to true. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. + + If working with an older version of Druid, set `header` to `true` to verify compatibility. Druid returns the HTTP header `X-Druid-SQL-Header-Included: yes` when the client connects to an older Druid version with support for the `typesHeader` and `sqlTypesHeader` parameters. Additionally, Druid returns a `X-Druid-SQL-Query-Id` HTTP header with the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) if specified, else Druid will generate a SQL query ID. +* `typesHeader`: Adds Druid runtime type information in the header. Can only be set when `header` is also `true`. +* `sqlTypesHeader`: Adds SQL type information in the header. Can only be set when `header` is also `true`. +* `context`: JSON object containing optional [SQL query context parameters](../querying/sql-query-context.md), such as to set the query ID, time zone, and whether to use an approximation algorithm for distinct count. +* `parameters`: List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types. + Review Comment: Making the example more prominent with the property name will be easier to follow. Another option that could help users is to include the parameterized query as a separate example, similar to what was in the docs  `````suggestion For example: ``` "parameters": [ { "type": "TIMESTAMP", "value": "2019-08-21 00:00:00" } ] ``` ````` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. + + If working with an older version of Druid, set `header` to `true` to verify compatibility. Druid returns the HTTP header `X-Druid-SQL-Header-Included: yes` when the client connects to an older Druid version with support for the `typesHeader` and `sqlTypesHeader` parameters. Additionally, Druid returns a `X-Druid-SQL-Query-Id` HTTP header with the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) if specified, else Druid will generate a SQL query ID. +* `typesHeader`: Adds Druid runtime type information in the header. Can only be set when `header` is also `true`. Review Comment: ```suggestion * `typesHeader`: Adds Druid runtime type information in the header. Requires that `header` is also `true`. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. + + If working with an older version of Druid, set `header` to `true` to verify compatibility. Druid returns the HTTP header `X-Druid-SQL-Header-Included: yes` when the client connects to an older Druid version with support for the `typesHeader` and `sqlTypesHeader` parameters. Additionally, Druid returns a `X-Druid-SQL-Query-Id` HTTP header with the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) if specified, else Druid will generate a SQL query ID. +* `typesHeader`: Adds Druid runtime type information in the header. Can only be set when `header` is also `true`. +* `sqlTypesHeader`: Adds SQL type information in the header. Can only be set when `header` is also `true`. +* `context`: JSON object containing optional [SQL query context parameters](../querying/sql-query-context.md), such as to set the query ID, time zone, and whether to use an approximation algorithm for distinct count. +* `parameters`: List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types. + +#### Responses + +<!--DOCUSAURUS_CODE_TABS--> + +<!--200 SUCCESS--> -There are a variety of [SQL query context parameters](../querying/sql-query-context.md) you can provide by adding a "context" map, -like: +*Successfully submitted query* + +<!--400 BAD REQUEST--> + +*Error thrown due to bad query. Returns a JSON object detailing the error with the following format:* ```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "context" : { - "sqlTimeZone" : "America/Los_Angeles" - } + "error": "A well-defined error code.", + "errorMessage": "A message with additional details about the error.", + "errorClass": "Class of exception that caused this error.", + "host": "The host on which the error occurred." } ``` +<!--500 INTERNAL SERVER ERROR--> -Parameterized SQL queries are also supported: +*Request not sent due to unexpected conditions. Returns a JSON object detailing the error with the following format:* ```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = ? AND __time > ?", - "parameters": [ - { "type": "VARCHAR", "value": "bar"}, - { "type": "TIMESTAMP", "value": "2000-01-01 00:00:00" } - ] + "error": "A well-defined error code.", + "errorMessage": "A message with additional details about the error.", + "errorClass": "Class of exception that caused this error.", + "host": "The host on which the error occurred." } ``` -Metadata is available over HTTP POST by querying [metadata tables](../querying/sql-metadata-tables.md). +<!--END_DOCUSAURUS_CODE_TABS--> + +--- -### Responses -#### Result formats +#### Sample request -Druid SQL's HTTP POST API supports a variety of result formats. You can specify these by adding a `resultFormat` parameter, like: +The following example retrieves all rows in the `wikipedia` datasource where the `user` is `BlueMoon2662`. The query is assigned the ID `request01` using the `sqlQueryId` parameter. Optional properties `header`, `typesHeader`, and `sqlTypesHeader` are included to add type information to the response. Review Comment: ```suggestion The following example retrieves all rows in the `wikipedia` datasource where the `user` is `BlueMoon2662`. The query is assigned the ID `request01` using the `sqlQueryId` context parameter. The optional properties `header`, `typesHeader`, and `sqlTypesHeader` are set to true to include type information to the response. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. + + If working with an older version of Druid, set `header` to `true` to verify compatibility. Druid returns the HTTP header `X-Druid-SQL-Header-Included: yes` when the client connects to an older Druid version with support for the `typesHeader` and `sqlTypesHeader` parameters. Additionally, Druid returns a `X-Druid-SQL-Query-Id` HTTP header with the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) if specified, else Druid will generate a SQL query ID. +* `typesHeader`: Adds Druid runtime type information in the header. Can only be set when `header` is also `true`. +* `sqlTypesHeader`: Adds SQL type information in the header. Can only be set when `header` is also `true`. +* `context`: JSON object containing optional [SQL query context parameters](../querying/sql-query-context.md), such as to set the query ID, time zone, and whether to use an approximation algorithm for distinct count. +* `parameters`: List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types. + +#### Responses + +<!--DOCUSAURUS_CODE_TABS--> + +<!--200 SUCCESS--> -There are a variety of [SQL query context parameters](../querying/sql-query-context.md) you can provide by adding a "context" map, -like: +*Successfully submitted query* + +<!--400 BAD REQUEST--> + +*Error thrown due to bad query. Returns a JSON object detailing the error with the following format:* ```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "context" : { - "sqlTimeZone" : "America/Los_Angeles" - } + "error": "A well-defined error code.", + "errorMessage": "A message with additional details about the error.", + "errorClass": "Class of exception that caused this error.", + "host": "The host on which the error occurred." } ``` +<!--500 INTERNAL SERVER ERROR--> -Parameterized SQL queries are also supported: +*Request not sent due to unexpected conditions. Returns a JSON object detailing the error with the following format:* ```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = ? AND __time > ?", - "parameters": [ - { "type": "VARCHAR", "value": "bar"}, - { "type": "TIMESTAMP", "value": "2000-01-01 00:00:00" } - ] + "error": "A well-defined error code.", + "errorMessage": "A message with additional details about the error.", + "errorClass": "Class of exception that caused this error.", + "host": "The host on which the error occurred." } ``` -Metadata is available over HTTP POST by querying [metadata tables](../querying/sql-metadata-tables.md). +<!--END_DOCUSAURUS_CODE_TABS--> + +--- -### Responses -#### Result formats +#### Sample request -Druid SQL's HTTP POST API supports a variety of result formats. You can specify these by adding a `resultFormat` parameter, like: +The following example retrieves all rows in the `wikipedia` datasource where the `user` is `BlueMoon2662`. The query is assigned the ID `request01` using the `sqlQueryId` parameter. Optional properties `header`, `typesHeader`, and `sqlTypesHeader` are included to add type information to the response. + +<!--DOCUSAURUS_CODE_TABS--> + +<!--cURL--> + +```shell +curl "http://ROUTER_IP:ROUTER_PORT/druid/v2/sql"; \ +--header 'Content-Type: application/json' \ +--data '{ + "query": "SELECT * FROM wikipedia WHERE user='\''BlueMoon2662'\''", + "context" : {"sqlQueryId" : "request01"}, + "header" : true, + "typesHeader" : true, + "sqlTypesHeader" : true +}' +``` + +<!--HTTP--> + +```HTTP +POST /druid/v2/sql HTTP/1.1 +Host: http://ROUTER_IP:ROUTER_PORT +Content-Type: application/json +Content-Length: 192 -```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "resultFormat" : "array" + "query": "SELECT * FROM wikipedia WHERE user='BlueMoon2662'", + "context" : {"sqlQueryId" : "request01"}, + "header" : true, + "typesHeader" : true, + "sqlTypesHeader" : true } ``` -To request a header with information about column names, set `header` to true in your request. -When you set `header` to true, you can optionally include `typesHeader` and `sqlTypesHeader` as well, which gives -you information about [Druid runtime and SQL types](../querying/sql-data-types.md) respectively. You can request all these headers -with a request like: +<!--END_DOCUSAURUS_CODE_TABS--> + +#### Sample response + +<details> + <summary>Click to show sample response</summary> ```json -{ - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "resultFormat" : "array", - "header" : true, - "typesHeader" : true, - "sqlTypesHeader" : true -} +[ + { + "__time": { + "type": "LONG", + "sqlType": "TIMESTAMP" + }, + "channel": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "cityName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "comment": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "countryIsoCode": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "countryName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "isAnonymous": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isMinor": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isNew": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isRobot": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isUnpatrolled": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "metroCode": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "namespace": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "page": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "regionIsoCode": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "regionName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "user": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "delta": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "added": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "deleted": { + "type": "LONG", + "sqlType": "BIGINT" + } + }, + { + "__time": "2015-09-12T00:47:53.259Z", + "channel": "#ja.wikipedia", + "cityName": "", + "comment": "/* 対戦通算成績と得失点 */", + "countryIsoCode": "", + "countryName": "", + "isAnonymous": 0, + "isMinor": 1, + "isNew": 0, + "isRobot": 0, + "isUnpatrolled": 0, + "metroCode": 0, + "namespace": "Main", + "page": "アルビレックス新潟の年度別成績一覧", + "regionIsoCode": "", + "regionName": "", + "user": "BlueMoon2662", + "delta": 14, + "added": 14, + "deleted": 0 + } +] ``` +</details> -The following table shows supported result formats: +### Cancel a query -|Format|Description|Header description|Content-Type| -|------|-----------|------------------|------------| -|`object`|The default, a JSON array of JSON objects. Each object's field names match the columns returned by the SQL query, and are provided in the same order as the SQL query.|If `header` is true, the first row is an object where the fields are column names. Each field's value is either null (if `typesHeader` and `sqlTypesHeader` are false) or an object that contains the Druid type as `type` (if `typesHeader` is true) and the SQL type as `sqlType` (if `sqlTypesHeader` is true).|application/json| -|`array`|JSON array of JSON arrays. Each inner array has elements matching the columns returned by the SQL query, in order.|If `header` is true, the first row is an array of column names. If `typesHeader` is true, the next row is an array of Druid types. If `sqlTypesHeader` is true, the next row is an array of SQL types.|application/json| -|`objectLines`|Like `object`, but the JSON objects are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `object`.|text/plain| -|`arrayLines`|Like `array`, but the JSON arrays are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `array`, except the rows are separated by newlines.|text/plain| -|`csv`|Comma-separated values, with one row per line. Individual field values may be escaped by being surrounded in double quotes. If double quotes appear in a field value, they will be escaped by replacing them with double-double-quotes like `""this""`. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `array`, except the lists are in CSV format.|text/csv| +Cancels a query on the Router or the Broker with the associated `sqlQueryId`. Queries can only be canceled with a valid `sqlQueryId`. The `sqlQueryId` must be set in the query context when the query is submitted. Note that Druid does not enforce unique `sqlQueryId` in the query context. Druid cancels all requests that use the same query ID. Review Comment: Regarding this line: >The `sqlQueryId` must be set in the query context when the query is submitted. Technically it doesn't have to be set, right? The original docs state, >Druid returns the SQL query identifier in the X-Druid-SQL-Query-Id HTTP header. This query id will be assigned the value of sqlQueryId from the [query context parameters](https://druid.apache.org/docs/latest/querying/sql-query-context.html) if specified, else Druid will generate a SQL query id for you. So if you didn't send it with the query, you could still get it from the HTTP header to delete later. ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. Review Comment: ```suggestion * `resultFormat`: String that indicates the format to return query results. Select one of the following formats: ``` Make it clear that these are options for `resultFormat` and not subproperties ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. Review Comment: ```suggestion The request body takes the following properties: * `query`: SQL query string. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. Review Comment: ```suggestion * `csv`: Returns comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. Review Comment: This line should go under `typesHeader` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). Review Comment: ```suggestion You can also use this endpoint to query [metadata tables](../querying/sql-metadata-tables.md). ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. Review Comment: What do you think about including example snippets for when these Boolean values are set to true? Something like: ### for the header description ````` When `header` is `true`, Druid includes a row similar to the following: * For an `object` or `objectLines` result format: ```json <EXAMPLE> ``` * For an `array`, `arrayLines`, or `csv` result format: ```json <EXAMPLE> ``` ````` ### for the typesHeader description ````` When `header` is `true` and `typesHeader` is true, Druid includes a row similar to the following: * For an `object` or `objectLines` result format: ```json <EXAMPLE> ``` * For an `array`, `arrayLines`, or `csv` result format: ```json <EXAMPLE> ``` ````` And then also something similar for sqlTypesHeader. That way we can capture some of the information from the results format table: > If header is true, the first row is an object where the fields are column names. Each field's value is either null (if typesHeader and sqlTypesHeader are false) or an object that contains the Druid type as type (if typesHeader is true) and the SQL type as sqlType (if sqlTypesHeader is true). ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. Review Comment: Though this can get unwieldy. Consider other ways to format and present this information too. ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. + + Complex types, like sketches, will be reported as `COMPLEX<typeName>` if a particular complex type name is known for that field, or as `COMPLEX` if the particular type name is unknown or mixed. + + If working with an older version of Druid, set `header` to `true` to verify compatibility. Druid returns the HTTP header `X-Druid-SQL-Header-Included: yes` when the client connects to an older Druid version with support for the `typesHeader` and `sqlTypesHeader` parameters. Additionally, Druid returns a `X-Druid-SQL-Query-Id` HTTP header with the value of `sqlQueryId` from the [query context parameters](../querying/sql-query-context.md) if specified, else Druid will generate a SQL query ID. +* `typesHeader`: Adds Druid runtime type information in the header. Can only be set when `header` is also `true`. +* `sqlTypesHeader`: Adds SQL type information in the header. Can only be set when `header` is also `true`. +* `context`: JSON object containing optional [SQL query context parameters](../querying/sql-query-context.md), such as to set the query ID, time zone, and whether to use an approximation algorithm for distinct count. +* `parameters`: List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types. + +#### Responses + +<!--DOCUSAURUS_CODE_TABS--> + +<!--200 SUCCESS--> -There are a variety of [SQL query context parameters](../querying/sql-query-context.md) you can provide by adding a "context" map, -like: +*Successfully submitted query* + +<!--400 BAD REQUEST--> + +*Error thrown due to bad query. Returns a JSON object detailing the error with the following format:* ```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "context" : { - "sqlTimeZone" : "America/Los_Angeles" - } + "error": "A well-defined error code.", + "errorMessage": "A message with additional details about the error.", + "errorClass": "Class of exception that caused this error.", + "host": "The host on which the error occurred." } ``` +<!--500 INTERNAL SERVER ERROR--> -Parameterized SQL queries are also supported: +*Request not sent due to unexpected conditions. Returns a JSON object detailing the error with the following format:* ```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = ? AND __time > ?", - "parameters": [ - { "type": "VARCHAR", "value": "bar"}, - { "type": "TIMESTAMP", "value": "2000-01-01 00:00:00" } - ] + "error": "A well-defined error code.", + "errorMessage": "A message with additional details about the error.", + "errorClass": "Class of exception that caused this error.", + "host": "The host on which the error occurred." } ``` -Metadata is available over HTTP POST by querying [metadata tables](../querying/sql-metadata-tables.md). +<!--END_DOCUSAURUS_CODE_TABS--> + +--- -### Responses -#### Result formats +#### Sample request -Druid SQL's HTTP POST API supports a variety of result formats. You can specify these by adding a `resultFormat` parameter, like: +The following example retrieves all rows in the `wikipedia` datasource where the `user` is `BlueMoon2662`. The query is assigned the ID `request01` using the `sqlQueryId` parameter. Optional properties `header`, `typesHeader`, and `sqlTypesHeader` are included to add type information to the response. + +<!--DOCUSAURUS_CODE_TABS--> + +<!--cURL--> + +```shell +curl "http://ROUTER_IP:ROUTER_PORT/druid/v2/sql"; \ +--header 'Content-Type: application/json' \ +--data '{ + "query": "SELECT * FROM wikipedia WHERE user='\''BlueMoon2662'\''", + "context" : {"sqlQueryId" : "request01"}, + "header" : true, + "typesHeader" : true, + "sqlTypesHeader" : true +}' +``` + +<!--HTTP--> + +```HTTP +POST /druid/v2/sql HTTP/1.1 +Host: http://ROUTER_IP:ROUTER_PORT +Content-Type: application/json +Content-Length: 192 -```json { - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "resultFormat" : "array" + "query": "SELECT * FROM wikipedia WHERE user='BlueMoon2662'", + "context" : {"sqlQueryId" : "request01"}, + "header" : true, + "typesHeader" : true, + "sqlTypesHeader" : true } ``` -To request a header with information about column names, set `header` to true in your request. -When you set `header` to true, you can optionally include `typesHeader` and `sqlTypesHeader` as well, which gives -you information about [Druid runtime and SQL types](../querying/sql-data-types.md) respectively. You can request all these headers -with a request like: +<!--END_DOCUSAURUS_CODE_TABS--> + +#### Sample response + +<details> + <summary>Click to show sample response</summary> ```json -{ - "query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'", - "resultFormat" : "array", - "header" : true, - "typesHeader" : true, - "sqlTypesHeader" : true -} +[ + { + "__time": { + "type": "LONG", + "sqlType": "TIMESTAMP" + }, + "channel": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "cityName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "comment": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "countryIsoCode": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "countryName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "isAnonymous": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isMinor": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isNew": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isRobot": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "isUnpatrolled": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "metroCode": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "namespace": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "page": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "regionIsoCode": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "regionName": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "user": { + "type": "STRING", + "sqlType": "VARCHAR" + }, + "delta": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "added": { + "type": "LONG", + "sqlType": "BIGINT" + }, + "deleted": { + "type": "LONG", + "sqlType": "BIGINT" + } + }, + { + "__time": "2015-09-12T00:47:53.259Z", + "channel": "#ja.wikipedia", + "cityName": "", + "comment": "/* 対戦通算成績と得失点 */", + "countryIsoCode": "", + "countryName": "", + "isAnonymous": 0, + "isMinor": 1, + "isNew": 0, + "isRobot": 0, + "isUnpatrolled": 0, + "metroCode": 0, + "namespace": "Main", + "page": "アルビレックス新潟の年度別成績一覧", + "regionIsoCode": "", + "regionName": "", + "user": "BlueMoon2662", + "delta": 14, + "added": 14, + "deleted": 0 + } +] ``` +</details> -The following table shows supported result formats: +### Cancel a query -|Format|Description|Header description|Content-Type| -|------|-----------|------------------|------------| -|`object`|The default, a JSON array of JSON objects. Each object's field names match the columns returned by the SQL query, and are provided in the same order as the SQL query.|If `header` is true, the first row is an object where the fields are column names. Each field's value is either null (if `typesHeader` and `sqlTypesHeader` are false) or an object that contains the Druid type as `type` (if `typesHeader` is true) and the SQL type as `sqlType` (if `sqlTypesHeader` is true).|application/json| -|`array`|JSON array of JSON arrays. Each inner array has elements matching the columns returned by the SQL query, in order.|If `header` is true, the first row is an array of column names. If `typesHeader` is true, the next row is an array of Druid types. If `sqlTypesHeader` is true, the next row is an array of SQL types.|application/json| -|`objectLines`|Like `object`, but the JSON objects are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `object`.|text/plain| -|`arrayLines`|Like `array`, but the JSON arrays are separated by newlines instead of being wrapped in a JSON array. This can make it easier to parse the entire response set as a stream, if you do not have ready access to a streaming JSON parser. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `array`, except the rows are separated by newlines.|text/plain| -|`csv`|Comma-separated values, with one row per line. Individual field values may be escaped by being surrounded in double quotes. If double quotes appear in a field value, they will be escaped by replacing them with double-double-quotes like `""this""`. To make it possible to detect a truncated response, this format includes a trailer of one blank line.|Same as `array`, except the lists are in CSV format.|text/csv| +Cancels a query on the Router or the Broker with the associated `sqlQueryId`. Queries can only be canceled with a valid `sqlQueryId`. The `sqlQueryId` must be set in the query context when the query is submitted. Note that Druid does not enforce unique `sqlQueryId` in the query context. Druid cancels all requests that use the same query ID. Review Comment: ```suggestion Cancels a query on the Router or the Broker with the associated `sqlQueryId`. Queries can only be canceled with a valid `sqlQueryId`. The `sqlQueryId` must be set in the query context when the query is submitted. Note that Druid does not enforce a unique `sqlQueryId` in the query context. Druid cancels all requests that use the same query ID. ``` ########## docs/api-reference/sql-api.md: ########## @@ -23,167 +23,287 @@ sidebar_label: Druid SQL ~ under the License. --> -> Apache Druid supports two query languages: Druid SQL and [native queries](../querying/querying.md). -> This document describes the SQL language. +Apache Druid supports two query languages: [Druid SQL](../querying/sql.md) and [native queries](../querying/querying.md). This topic describes the SQL language. -You can submit and cancel [Druid SQL](../querying/sql.md) queries using the Druid SQL API. -The Druid SQL API is available at `https://ROUTER:8888/druid/v2/sql`, where `ROUTER` is the IP address of the Druid Router. +In this topic, `http://ROUTER_IP:ROUTER_PORT` is a place holder for your Router service address and port. Replace it with the information for your deployment. For example, use `http://localhost:8888` for quickstart deployments. -## Submit a query +## Query from Historicals -To use the SQL API to make Druid SQL queries, send your query to the Router using the POST method: -``` -POST https://ROUTER:8888/druid/v2/sql/ -``` +### Submit a query -Submit your query as the value of a "query" field in the JSON object within the request payload. For example: -```json -{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} -``` +Submit a SQL-based query in the JSON request body. Returns a JSON object with the database results and a set of header metadata associated with the query. -### Request body - -|Property|Description|Default| -|--------|----|-----------| -|`query`|SQL query string.| none (required)| -|`resultFormat`|Format of query results. See [Responses](#responses) for details.|`"object"`| -|`header`|Whether or not to include a header row for the query result. See [Responses](#responses) for details.|`false`| -|`typesHeader`|Whether or not to include type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`sqlTypesHeader`|Whether or not to include SQL type information in the header. Can only be set when `header` is also `true`. See [Responses](#responses) for details.|`false`| -|`context`|JSON object containing [SQL query context parameters](../querying/sql-query-context.md).|`{}` (empty)| -|`parameters`|List of query parameters for parameterized queries. Each parameter in the list should be a JSON object like `{"type": "VARCHAR", "value": "foo"}`. The type should be a SQL type; see [Data types](../querying/sql-data-types.md) for a list of supported SQL types.|`[]` (empty)| - -You can use _curl_ to send SQL queries from the command-line: - -```bash -$ cat query.json -{"query":"SELECT COUNT(*) AS TheCount FROM data_source"} - -$ curl -XPOST -H'Content-Type: application/json' http://ROUTER:8888/druid/v2/sql/ -d @query.json -[{"TheCount":24433}] -``` +This endpoint also supports querying metadata by using [metadata tables](../querying/sql-metadata-tables.md). + +#### URL + +<code class="postAPI">POST</code> <code>/druid/v2/sql</code> + +#### Request body + +* `query`: SQL query string. +* `resultFormat`: Format of query results. + * `object`: Returns a JSON array of JSON objects with the HTTP header `Content-Type: application/json`. + * `array`: Returns a JSON array of JSON arrays with the HTTP header `Content-Type: application/json`. + * `objectLines`: Returns newline-delimited JSON objects with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `arrayLines`: Returns newline-delimited JSON arrays with a trailing blank line. Sent with the HTTP header `Content-Type: text/plain`. + * `csv`: Returns a comma-separated values with one row per line and a trailing blank line. Sent with the HTTP header `Content-Type: text/csv`. +* `header`: Adds a header row with information on column names for the query result when set to `true`. You can optionally include `typesHeader` and `sqlTypesHeader`. Review Comment: What do you think about including example snippets for when these Boolean values are set to true? Something like: ### for the header description ````` When `header` is `true`, Druid includes a row similar to the following: * For an `object` or `objectLines` result format: ```json <EXAMPLE> ``` * For an `array`, `arrayLines`, or `csv` result format: ```json <EXAMPLE> ``` ````` ### for the typesHeader description ````` When `header` is `true` and `typesHeader` is true, Druid includes a row similar to the following: * For an `object` or `objectLines` result format: ```json <EXAMPLE> ``` * For an `array`, `arrayLines`, or `csv` result format: ```json <EXAMPLE> ``` ````` And then also something similar for sqlTypesHeader. That way we can capture some of the information from the results format table: > If header is true, the first row is an object where the fields are column names. Each field's value is either null (if typesHeader and sqlTypesHeader are false) or an object that contains the Druid type as type (if typesHeader is true) and the SQL type as sqlType (if sqlTypesHeader is true). -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
