Setting up query API endpoints

Easily spin up REST API endpoints from your saved queries

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 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.

1. 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, which contains approximately 4.5 billion records. Follow the steps in section “Create 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.

   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”.

2. 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:

   curl -H "Content-Type: application/json" -s --user '<key_id>:<key_secret>' '<API-endpoint>?format=JSONEachRow&param_year=<value>'
   
3. 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.

4. 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:

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	?max_threads=8

Headers

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

Supported formats

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:

SELECT database, name AS num_tables FROM system.tables LIMIT 3;

Version 1

cURL

curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'Content-Type: application/json' \
-d '{ "format": "JSONEachRow" }'

JavaScript

fetch(
  "https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run",
  {
    method: "POST",
    headers: {
      Authorization: "Basic <base64_encoded_credentials>",
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      format: "JSONEachRow",
    }),
  }
)
  .then((response) => response.json())
  .then((data) => console.log(data))
  .catch((error) => console.error("Error:", error));

Response

{
  "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

GET (cURL)

curl 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?format=JSONEachRow' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'x-clickhouse-endpoint-version: 2'

Response

{"database":"INFORMATION_SCHEMA","num_tables":"COLUMNS"}
{"database":"INFORMATION_SCHEMA","num_tables":"KEY_COLUMN_USAGE"}
{"database":"INFORMATION_SCHEMA","num_tables":"REFERENTIAL_CONSTRAINTS"}

POST (cURL)

curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?format=JSONEachRow' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'Content-Type: application/json' \
-H 'x-clickhouse-endpoint-version: 2'

JavaScript

fetch(
  "https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?format=JSONEachRow",
  {
    method: "POST",
    headers: {
      Authorization: "Basic <base64_encoded_credentials>",
      "Content-Type": "application/json",
      "x-clickhouse-endpoint-version": "2",
    },
  }
)
  .then((response) => response.json())
  .then((data) => console.log(data))
  .catch((error) => console.error("Error:", error));

Response

{"database":"INFORMATION_SCHEMA","num_tables":"COLUMNS"}
{"database":"INFORMATION_SCHEMA","num_tables":"KEY_COLUMN_USAGE"}
{"database":"INFORMATION_SCHEMA","num_tables":"REFERENTIAL_CONSTRAINTS"}

Request with query variables and version 2 on JSONCompactEachRow format

Query API Endpoint SQL:

SELECT name, database FROM system.tables WHERE match(name, {tableNameRegex: String}) AND database = {database: String};

GET (cURL)

curl 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?format=JSONCompactEachRow&param_tableNameRegex=query.*&param_database=system' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'x-clickhouse-endpoint-version: 2'

Response

["query_cache", "system"]
["query_log", "system"]
["query_views_log", "system"]

POST (cURL)

curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?format=JSONCompactEachRow' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'Content-Type: application/json' \
-H 'x-clickhouse-endpoint-version: 2' \
-d '{ "queryVariables": { "tableNameRegex": "query.*", "database": "system" } }'

JavaScript

fetch(
  "https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?format=JSONCompactEachRow",
  {
    method: "POST",
    headers: {
      Authorization: "Basic <base64_encoded_credentials>",
      "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));

Response

["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:

CREATE TABLE default.t_arr
(
    `arr` Array(Array(Array(UInt32)))
)
ENGINE = MergeTree
ORDER BY tuple()

Query API Endpoint SQL:

INSERT INTO default.t_arr VALUES ({arr: Array(Array(Array(UInt32)))});

cURL

curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'Content-Type: application/json' \
-H 'x-clickhouse-endpoint-version: 2' \
-d '{
  "queryVariables": {
    "arr": [[[12, 13, 0, 1], [12]]]
  }
}'

JavaScript

fetch(
  "https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run",
  {
    method: "POST",
    headers: {
      Authorization: "Basic <base64_encoded_credentials>",
      "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));

Response

OK

Request with ClickHouse settings max_threads set to 8

Query API Endpoint SQL:

SELECT * FROM system.tables;

GET (cURL)

curl 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?max_threads=8' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'x-clickhouse-endpoint-version: 2'

POST (cURL)

curl -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?max_threads=8,' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'Content-Type: application/json' \
-H 'x-clickhouse-endpoint-version: 2' \

JavaScript

fetch(
  "https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?max_threads=8",
  {
    method: "POST",
    headers: {
      Authorization: "Basic <base64_encoded_credentials>",
      "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:

SELECT name, database FROM system.tables;

TypeScript

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/<endpoint id>/run?format=JSONEachRow";
const openApiKeyId = "<myOpenApiKeyId>";
const openApiKeySecret = "<myOpenApiKeySecret>";
// Usage example
fetchAndLogChunks(endpointUrl, openApiKeyId, openApiKeySecret).catch((err) =>
  console.error(err)
);

Output

> 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:

"user_id","json","name"
"1","{""name"":""John"",""age"":30}","John"
"2","{""name"":""Jane"",""age"":25}","Jane"

Create Table SQL:

create table default.my_first_table
(
    user_id String,
    json String,
    name String,
) ENGINE = MergeTree()
ORDER BY user_id;

Query API Endpoint SQL:

INSERT INTO default.my_first_table

cat ./samples/my_first_table_2024-07-11.csv | curl --user '<openApiKeyId:openApiKeySecret>' \
                                                   -X POST \
                                                   -H 'Content-Type: application/octet-stream' \
                                                   -H 'x-clickhouse-endpoint-version: 2' \
                                                   "https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run?format=CSV" \
                                                   --data-binary @-