top of page
Search

Building a Dynamic Parameterized Query UI for ClickHouse

  • Writer: LK Rajat
    LK Rajat
  • Jul 3
  • 4 min read
A task which was a part of Clickhouse's Intern Tasks 2024/2025. link to repository: https://github.com/rajatmohan22/ClickHouseUI

Introduction


As part of ClickHouse’s 2024-25 intern program, I set out to build ClickHouseUI—a lightweight, full-stack application that empowers users to explore their ClickHouse data interactively, without ever writing raw SQL. In this deep-dive blog, I’ll walk you through the motivations, architecture, key features, challenges overcome, and lessons learned across both frontend and backend.

Whether you’re an analyst looking for a slick way to slice and dice your tables or a developer aiming to scaffold your own data-exploration tooling, you’ll find insights and patterns that you can reapply to your own ClickHouse integrations.


Why ClickHouseUI?


  • Democratize Data AccessNot every stakeholder is fluent in SQL. ClickHouseUI bridges that gap by offering button-driven filters, grouping, sorting, and charting.


  • Leverage ClickHouse’s StrengthsFrom interactive ad-hoc queries to lightning-fast analytical workloads, ClickHouse excels at sub-second response times on large datasets. ClickHouseUI surfaces that power in a friendly interface.


  • Rapid PrototypingInterns and developers alike can spin up a working UI in under an hour and iterate through new features in minutes


Project Goals


  1. Schema Discovery

    • List databases and tables dynamically via ClickHouse’s HTTP API.


  2. Parameterized Query Builder

    • Compose filters, sorts, group-bys, aggregates, and date ranges through intuitive controls.


  3. AI-Powered Suggestions

    • Offer click-to-run descriptive-statistic queries (powered by OpenAI) based on your schema.


  4. Smart Charting

    • One-click renderings: histograms, bar/line/pie charts, scatter plots, bubble maps.


  5. Modular & Extensible

    • Clean separation: EJS templates + vanilla JS on the frontend; Express routes on the backend.


Architecture Overview


Frontend


  • Templating: EJS for server-rendered pages, with Bootstrap for layout.

  • State Management: All “blocks” (Filter, Sort, Group, Aggregate, Date Range, Select Columns) live in a queryBlocks[] array.

  • Dynamic Controls:

    • Buttons wired to functions like addFilter(), addGroup(), etc.

    • Each injection uses a reusable createDropdown(label, controls[]) helper to maintain consistent Bootstrap grid alignment.

  • Query Submission:

    • showQueryResult() serializes each block into JSON (e.g. {type:"Filter",column:"age",value:"30"}) and POSTs to /param.

  • AI & Charts:

    • /ai suggestions spawn quick-run SQL buttons.

    • Chart buttons build small forms (X/Y axes, aggregation) and POST to /chart, rendering Chart.js configs on a <canvas>.


Backend


  • Express Server with JSON-body parsing.


  • ClickHouse HTTP: Raw SQL sent over HTTP POST, with FORMAT TabSeparatedWithNames for tabular results.


  • Parameterized Query Route (/param)

    • Select Columns: Honors an explicit multiselect block if present.

    • Filter / Date Range: Translates blocks into WHERE clauses using column = 'value' or column BETWEEN toDateTime('start') AND toDateTime('end').

    • Group / Aggregate: Collects GROUP BY keys and FUNC(column) AS alias parts.

    • Sort By: Appends an ORDER BY clause.

    • Fallback: If no explicit selections or groupings, does SELECT *.


  • Chart Route (/chart)

    • Builds tailor-made aggregations per chart type (e.g. heatmap ⇒ two-dimensional aggregation).

    • Returns a Chart.js config object for immediate rendering.


Key Features & Intern Milestones


  1. Dynamic Schema Fetching

    • Intern Task: Hooked into ClickHouse’s SHOW DATABASES and SHOW TABLES IN db via HTTP, then populated <select>s on the fly.

  2. WYSIWYG Query Blocks

    • Built a composable query-builder area, where each new block visually represents part of the SQL.

    • Implemented “Undo” & “Clear” actions to remove or reset blocks.

  3. Date Range Without Flatpickr

    • Swapped out a heavy date-picker library for native <input type="date"> controls + “Last 7/30/90 days” presets—keeping the bundle lightweight.

  4. Select Columns Multiselect

    • Added a multiselect control that injects a Select Columns block; backend honors that by overriding SELECT *.

  5. AI-Driven Descriptive Statistics

    • Leveraged OpenAI’s GPT models to suggest 10 common analytics questions (e.g. “Average order value by day”), converting them to one-click SQL and UI buttons.

  6. Chart.js Integration

    • Mapped each chart type to a SQL pattern (e.g. GROUP BY xAxis) and transformed ClickHouse’s tabular output into Chart.js JSON configs for bar, line, pie, scatter, heatmap, and radar.


Challenges & Solutions


  • “Not under aggregate” Errors

    • ClickHouse requires that any non-aggregated column in a SELECT appear in your GROUP BY. We solved this by dynamically building the GROUP BY clause from the user’s Group/Select blocks, and falling back to SELECT * only when no grouping/aggregation was used.

  • Date Range Validation

    • Ensured users can’t submit an incomplete date-range block—added frontend alerts (“Please choose both start & end dates”) and backend guards before SQL assembly.

  • Dynamic Column Types

    • Abstracted all column metadata fetches so any table schema (numbers, strings, datetimes) seamlessly populates filters, axes selectors, and multiselects.


Looking Ahead


  • Role-Based Views: Show/hide certain analytics features based on user permissions.

  • Advanced Charts: Add box-plots and heatmaps via Chart.js plugins.

  • Custom SQL Blocks: Let power users inject raw SQL snippets safely.

  • Caching & Pagination: For extremely large tables, add lazy loading and result caching.


Conclusion


ClickHouseUI crystallizes how intern-built tooling can harness ClickHouse’s raw power and expose it through a polished, button-driven interface. From zero to a fully parameterized query-and-chart system in weeks, this project demonstrates:

  • Speed of Development with Express, EJS, and vanilla JS.

  • Scalability of ClickHouse’s HTTP API for both tabular and JSON output.

  • Extensibility, thanks to a modular block architecture and clear separation of concerns.

If you’re embarking on your own ClickHouse integration, take inspiration from this intern project: start small, iterate fast, and let the database’s speed do the heavy lifting.


— Built during ClickHouse’s Intern Tasks 2024.

 
 
 

Comments

bottom of page