Skip to main content
Skip to main content
Edit this page

Setting up query API endpoints

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
  • an Admin Console Role.

You can follow this guide to create an API key if you don't yet have one.

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.

Tip
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
-- highlight-next-line
  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 curly brackets { } 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".

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 key(s) 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>'

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:

GET /query-endpoints/{queryEndpointId}/run
POST /query-endpoints/{queryEndpointId}/run

HTTP methods​

MethodUse CaseParameters
GETSimple queries with parametersPass query variables via URL parameters (?param_name=value)
POSTComplex queries or when using request bodyPass 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​

ParameterRequiredDescription
queryEndpointIdYesThe unique identifier of the query endpoint to run

Query parameters​

ParameterRequiredDescriptionExample
formatNoResponse format (supports all ClickHouse formats)?format=JSONEachRow
param_:nameNoQuery variables when request body is a stream. Replace :name with your variable name?param_year=2024
:clickhouse_settingNoAny supported ClickHouse setting?max_threads=8

Headers​

HeaderRequiredDescriptionValues
x-clickhouse-endpoint-versionNoSpecifies the endpoint version1 or 2 (defaults to last saved version)
x-clickhouse-endpoint-upgradeNoTriggers endpoint version upgrade (use with version header)1 to upgrade

Request body​

Parameters​

ParameterTypeRequiredDescription
queryVariablesobjectNoVariables to be used in the query
formatstringNoResponse format

Supported formats​

VersionSupported Formats
Version 2All 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 CodeDescription
400 Bad RequestThe request was malformed
401 UnauthorizedMissing authentication or insufficient permissions
404 Not FoundThe specified query endpoint was not 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 -X POST 'https://console-api.clickhouse.cloud/.api/query-endpoints/<endpoint id>/run' \
--user '<openApiKeyId:openApiKeySecret>' \
-H 'Content-Type: application/json' \
-d '{ "format": "JSONEachRow" }'

Version 2​

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

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

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'

["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 -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]]]
  }
}'

Request with ClickHouse settings max_threads set to 8​

Query API Endpoint SQL:

SELECT * FROM system.tables;

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

Request and parse the response as a stream`​

Query API Endpoint SQL:

SELECT name, database FROM system.tables;

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)
);

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