> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Easily spin up REST API endpoints from your saved queries
# Setting up query API endpoints
export const Image = ({img, alt, size}) => {
return
;
};
The **Query API Endpoints** feature allows you to create an API endpoint directly from any saved SQL query in the ClickHouse Cloud console. You'll be able to access API endpoints via HTTP to execute your saved queries without needing to connect to your ClickHouse Cloud service via a native driver.
Pre-requisites
Before proceeding, ensure you have:
* An API key with appropriate permissions
* An Admin Console Role
You can follow this guide to [create an API key](/products/cloud/features/admin-features/api/openapi) if you don't yet have one.
**Minimum permissions**
To query an API endpoint, the API key needs `Member` organization role with `Query Endpoints` service access. The database role is configured when you create the endpoint.
Create a saved query
If you have a saved query, you can skip this step.
Open a new query tab. For demonstration purposes, we'll use the [youtube dataset](/get-started/sample-datasets/youtube-dislikes), which contains approximately 4.5 billion records.
Follow the steps in section ["Create table"](/get-started/sample-datasets/youtube-dislikes#create-the-table) to create the table on your Cloud service and insert data to it.
**`LIMIT` the number of rows**
The example dataset tutorial inserts a lot of data - 4.65 billion rows which can take some time to insert.
For the purposes of this guide we recommend to use the `LIMIT` clause to insert a smaller amount of data,
for example 10 million rows.
As an example query, we'll return the top 10 uploaders by average views per video in a user-inputted `year` parameter.
```sql highlight={11} theme={null}
WITH sum(view_count) AS view_sum,
round(view_sum / num_uploads, 2) AS per_upload
SELECT
uploader,
count() AS num_uploads,
formatReadableQuantity(view_sum) AS total_views,
formatReadableQuantity(per_upload) AS views_per_video
FROM
youtube
WHERE
toYear(upload_date) = {year: UInt16}
GROUP BY uploader
ORDER BY per_upload desc
LIMIT 10
```
Note that this query contains a parameter (`year`) which is highlighted in the snippet above.
You can specify query parameters using `{ }` together with the type of the parameter.
The SQL console query editor automatically detects ClickHouse query parameter expressions and provides an input for each parameter.
Let's quickly run this query to make sure that it works by specifying the year `2010` in the query variables input box on the right side of the SQL editor:
Next, save the query:
More documentation around saved queries can be found in section ["Saving a query"](/products/cloud/features/sql-console-features/sql-console#saving-a-query).
Configuring the query API endpoint
Query API endpoints can be configured directly from query view by clicking the **Share** button and selecting `API Endpoint`.
You'll be prompted to specify which API keys should be able to access the endpoint:
After selecting an API key, you will be asked to:
* Select the Database role that will be used to run the query (`Full access`, `Read only` or `Create a custom role`)
* Specify cross-origin resource sharing (CORS) allowed domains
After selecting these options, the query API endpoint will automatically be provisioned.
An example `curl` command will be displayed so you can send a test request:
The curl command displayed in the interface is given below for convenience:
```bash theme={null}
curl -H "Content-Type: application/json" -s --user ':' '?format=JSONEachRow¶m_year='
```
Query API parameters
Query parameters in a query can be specified with the syntax `{parameter_name: type}`. These parameters will be automatically detected and the example request payload will contain a `queryVariables` object through which you can pass these parameters.
Testing and monitoring
Once a Query API endpoint is created, you can test that it works by using `curl` or any other HTTP client:
After you've sent your first request, a new button should appear immediately to the right of the **Share** button. Clicking it will open a flyout containing monitoring data about the query:
Implementation details
This endpoint executes queries on your saved Query API endpoints.
It supports multiple versions, flexible response formats, parameterized queries, and optional streaming responses (version 2 only).
**Endpoint:**
```text theme={null}
GET /query-endpoints/{queryEndpointId}/run
POST /query-endpoints/{queryEndpointId}/run
```
HTTP methods
| Method | Use Case | Parameters |
| -------- | ------------------------------------------ | -------------------------------------------------------------- |
| **GET** | Simple queries with parameters | Pass query variables via URL parameters (`?param_name=value`) |
| **POST** | Complex queries or when using request body | Pass query variables in request body (`queryVariables` object) |
**When to use GET:**
* Simple queries without complex nested data
* Parameters can be easily URL-encoded
* Caching benefits from HTTP GET semantics
**When to use POST:**
* Complex query variables (arrays, objects, large strings)
* When request body is preferred for security/privacy
* Streaming file uploads or large data
Authentication
**Required:** Yes\
**Method:** Basic Auth using OpenAPI Key/Secret\
**Permissions:** Appropriate permissions for the query endpoint
Request configuration
URL parameters
| Parameter | Required | Description |
| ----------------- | -------- | -------------------------------------------------- |
| `queryEndpointId` | **Yes** | The unique identifier of the query endpoint to run |
Query parameters
| Parameter | Required | Description | Example |
| --------------------- | -------- | -------------------------------------------------------------------------------------- | ------------------------ |
| `format` | No | Response format (supports all ClickHouse formats) | `?format=JSONEachRow` |
| `param_:name` | No | Query variables when request body is a stream. Replace `:name` with your variable name | `?param_year=2024` |
| `request_timeout` | No | Query timeout in milliseconds (default: 30000) | `?request_timeout=60000` |
| `:clickhouse_setting` | No | Any supported [ClickHouse setting](/reference/settings/session-settings) | `?max_threads=8` |
| Header | Required | Description | Values |
| ------------------------------- | -------- | ----------------------------------------------------------- | ------------------------------------------- |
| `x-clickhouse-endpoint-version` | No | Specifies the endpoint version | `1` or `2` (defaults to last saved version) |
| `x-clickhouse-endpoint-upgrade` | No | Triggers endpoint version upgrade (use with version header) | `1` to upgrade |
***
Request body
Parameters
| Parameter | Type | Required | Description |
| ---------------- | ------ | -------- | --------------------------------- |
| `queryVariables` | object | No | Variables to be used in the query |
| `format` | string | No | Response format |
| Version | Supported Formats |
| ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **Version 2** | All ClickHouse-supported formats |
| **Version 1 (limited)** | TabSeparated
TabSeparatedWithNames
TabSeparatedWithNamesAndTypes
JSON
JSONEachRow
CSV
CSVWithNames
CSVWithNamesAndTypes |
***
Responses
Success
**Status:** `200 OK`\
The query was successfully executed.
Error codes
| Status Code | Description |
| ------------------ | -------------------------------------------------- |
| `400 Bad Request` | The request was malformed |
| `401 Unauthorized` | Missing authentication or insufficient permissions |
| `404 Not Found` | The specified query endpoint wasn't found |
Error handling best practices
* Ensure valid authentication credentials are included in the request
* Validate the `queryEndpointId` and `queryVariables` before sending
* Implement graceful error handling with appropriate error messages
***
Upgrading endpoint versions
To upgrade from version 1 to version 2:
1. Include the `x-clickhouse-endpoint-upgrade` header set to `1`
2. Include the `x-clickhouse-endpoint-version` header set to `2`
This enables access to version 2 features including:
* Support for all ClickHouse formats
* Response streaming capabilities
* Enhanced performance and functionality
Examples
Basic request
**Query API Endpoint SQL:**
```sql theme={null}
SELECT database, name AS num_tables FROM system.tables LIMIT 3;
```
Version 1
```bash theme={null}
curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints//run' \
--user '' \
-H 'Content-Type: application/json' \
-d '{ "format": "JSONEachRow" }'
```
```javascript theme={null}
fetch(
"https://console-api.clickhouse.cloud/.api/query-endpoints//run",
{
method: "POST",
headers: {
Authorization: "Basic ",
"Content-Type": "application/json",
},
body: JSON.stringify({
format: "JSONEachRow",
}),
}
)
.then((response) => response.json())
.then((data) => console.log(data))
.catch((error) => console.error("Error:", error));
```
```json title="Response" theme={null}
{
"data": {
"columns": [
{
"name": "database",
"type": "String"
},
{
"name": "num_tables",
"type": "String"
}
],
"rows": [
["INFORMATION_SCHEMA", "COLUMNS"],
["INFORMATION_SCHEMA", "KEY_COLUMN_USAGE"],
["INFORMATION_SCHEMA", "REFERENTIAL_CONSTRAINTS"]
]
}
}
```
Version 2
```bash theme={null}
curl 'https://console-api.clickhouse.cloud/.api/query-endpoints//run?format=JSONEachRow' \
--user '' \
-H 'x-clickhouse-endpoint-version: 2'
```
```application/x-ndjson title="Response" theme={null}
{"database":"INFORMATION_SCHEMA","num_tables":"COLUMNS"}
{"database":"INFORMATION_SCHEMA","num_tables":"KEY_COLUMN_USAGE"}
{"database":"INFORMATION_SCHEMA","num_tables":"REFERENTIAL_CONSTRAINTS"}
```
```bash theme={null}
curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints//run?format=JSONEachRow' \
--user '' \
-H 'Content-Type: application/json' \
-H 'x-clickhouse-endpoint-version: 2'
```
```javascript theme={null}
fetch(
"https://console-api.clickhouse.cloud/.api/query-endpoints//run?format=JSONEachRow",
{
method: "POST",
headers: {
Authorization: "Basic ",
"Content-Type": "application/json",
"x-clickhouse-endpoint-version": "2",
},
}
)
.then((response) => response.json())
.then((data) => console.log(data))
.catch((error) => console.error("Error:", error));
```
```application/x-ndjson title="Response" theme={null}
{"database":"INFORMATION_SCHEMA","num_tables":"COLUMNS"}
{"database":"INFORMATION_SCHEMA","num_tables":"KEY_COLUMN_USAGE"}
{"database":"INFORMATION_SCHEMA","num_tables":"REFERENTIAL_CONSTRAINTS"}
```
**Query API Endpoint SQL:**
```sql theme={null}
SELECT name, database FROM system.tables WHERE match(name, {tableNameRegex: String}) AND database = {database: String};
```
```bash theme={null}
curl 'https://console-api.clickhouse.cloud/.api/query-endpoints//run?format=JSONCompactEachRow¶m_tableNameRegex=query.*¶m_database=system' \
--user '' \
-H 'x-clickhouse-endpoint-version: 2'
```
```application/x-ndjson title="Response" theme={null}
["query_cache", "system"]
["query_log", "system"]
["query_views_log", "system"]
```
```bash theme={null}
curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints//run?format=JSONCompactEachRow' \
--user '' \
-H 'Content-Type: application/json' \
-H 'x-clickhouse-endpoint-version: 2' \
-d '{ "queryVariables": { "tableNameRegex": "query.*", "database": "system" } }'
```
```javascript theme={null}
fetch(
"https://console-api.clickhouse.cloud/.api/query-endpoints//run?format=JSONCompactEachRow",
{
method: "POST",
headers: {
Authorization: "Basic ",
"Content-Type": "application/json",
"x-clickhouse-endpoint-version": "2",
},
body: JSON.stringify({
queryVariables: {
tableNameRegex: "query.*",
database: "system",
},
}),
}
)
.then((response) => response.json())
.then((data) => console.log(data))
.catch((error) => console.error("Error:", error));
```
```application/x-ndjson title="Response" theme={null}
["query_cache", "system"]
["query_log", "system"]
["query_views_log", "system"]
```
Request with array in the query variables that inserts data into a table
**Table SQL:**
```SQL theme={null}
CREATE TABLE default.t_arr
(
`arr` Array(Array(Array(UInt32)))
)
ENGINE = MergeTree
ORDER BY tuple()
```
**Query API Endpoint SQL:**
```sql theme={null}
INSERT INTO default.t_arr VALUES ({arr: Array(Array(Array(UInt32)))});
```
```bash theme={null}
curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints//run' \
--user '' \
-H 'Content-Type: application/json' \
-H 'x-clickhouse-endpoint-version: 2' \
-d '{
"queryVariables": {
"arr": [[[12, 13, 0, 1], [12]]]
}
}'
```
```javascript theme={null}
fetch(
"https://console-api.clickhouse.cloud/.api/query-endpoints//run",
{
method: "POST",
headers: {
Authorization: "Basic ",
"Content-Type": "application/json",
"x-clickhouse-endpoint-version": "2",
},
body: JSON.stringify({
queryVariables: {
arr: [[[12, 13, 0, 1], [12]]],
},
}),
}
)
.then((response) => response.json())
.then((data) => console.log(data))
.catch((error) => console.error("Error:", error));
```
```text title="Response" theme={null}
OK
```
Request with ClickHouse settings `max_threads` set to 8
**Query API Endpoint SQL:**
```sql theme={null}
SELECT * FROM system.tables;
```
```bash theme={null}
curl 'https://console-api.clickhouse.cloud/.api/query-endpoints//run?max_threads=8' \
--user '' \
-H 'x-clickhouse-endpoint-version: 2'
```
```bash theme={null}
curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints//run?max_threads=8,' \
--user '' \
-H 'Content-Type: application/json' \
-H 'x-clickhouse-endpoint-version: 2' \
```
```javascript theme={null}
fetch(
"https://console-api.clickhouse.cloud/.api/query-endpoints//run?max_threads=8",
{
method: "POST",
headers: {
Authorization: "Basic ",
"Content-Type": "application/json",
"x-clickhouse-endpoint-version": "2",
},
}
)
.then((response) => response.json())
.then((data) => console.log(data))
.catch((error) => console.error("Error:", error));
```
Request and parse the response as a stream
**Query API Endpoint SQL:**
```sql theme={null}
SELECT name, database FROM system.tables;
```
```typescript theme={null}
async function fetchAndLogChunks(
url: string,
openApiKeyId: string,
openApiKeySecret: string
) {
const auth = Buffer.from(`${openApiKeyId}:${openApiKeySecret}`).toString(
"base64"
);
const headers = {
Authorization: `Basic ${auth}`,
"x-clickhouse-endpoint-version": "2",
};
const response = await fetch(url, {
headers,
method: "POST",
body: JSON.stringify({ format: "JSONEachRow" }),
});
if (!response.ok) {
console.error(`HTTP error! Status: ${response.status}`);
return;
}
const reader = response.body as unknown as Readable;
reader.on("data", (chunk) => {
console.log(chunk.toString());
});
reader.on("end", () => {
console.log("Stream ended.");
});
reader.on("error", (err) => {
console.error("Stream error:", err);
});
}
const endpointUrl =
"https://console-api.clickhouse.cloud/.api/query-endpoints//run?format=JSONEachRow";
const openApiKeyId = "";
const openApiKeySecret = "";
// Usage example
fetchAndLogChunks(endpointUrl, openApiKeyId, openApiKeySecret).catch((err) =>
console.error(err)
);
```
```shell title="Output" theme={null}
> npx tsx index.ts
> {"name":"COLUMNS","database":"INFORMATION_SCHEMA"}
> {"name":"KEY_COLUMN_USAGE","database":"INFORMATION_SCHEMA"}
...
> Stream ended.
```
Insert a stream from a file into a table
Create a file `./samples/my_first_table_2024-07-11.csv` with the following content:
```csv theme={null}
"user_id","json","name"
"1","{""name"":""John"",""age"":30}","John"
"2","{""name"":""Jane"",""age"":25}","Jane"
```
**Create Table SQL:**
```sql theme={null}
create table default.my_first_table
(
user_id String,
json String,
name String,
) ENGINE = MergeTree()
ORDER BY user_id;
```
**Query API Endpoint SQL:**
```sql theme={null}
INSERT INTO default.my_first_table
```
```bash theme={null}
cat ./samples/my_first_table_2024-07-11.csv | curl --user '' \
-X POST \
-H 'Content-Type: application/octet-stream' \
-H 'x-clickhouse-endpoint-version: 2' \
"https://console-api.clickhouse.cloud/.api/query-endpoints//run?format=CSV" \
--data-binary @-
```