Feature Req: Data accessibility with API Endpoints

[bspowell]

Overview of Problem

Currently, user have the ability to analyze their data within the platform or export their data via CSV. However, the limitation with Helios comes when users look to share their data with other teams or to other tools like internal dashboards for further analysis. Addressing these limitations would significantly enhance data sharing and accessibility, enabling users to leverage their Helios data more effectively across their organization and in various external tools.

Solution

To create an endpoint, users could utilize materialized views which are created as subsets of the source data. This data can then be retrieved by creating an API endpoint within the backend routes. Users can define those endpoints to accept parameters, execute preset queries, and return results in a standardized format. External platforms or users can then access the data using a HTTP POST request and providing basic credentials.

The introduction of API endpoints in Helios would enhance user capabilities by allowing them to expose query results as accessible HTTP endpoints. This would enable seamless integration of Helios data into other applications and services.

Example POST Request

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

Example 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"]
   ]
 }
}

Supporting Links

https://clickhouse.com/docs/en/get-started/query-endpoints