A simple, modular dashboard to visualize ClickHouse metrics built with React and deployed on Vercel with server-side caching.
- Connects to ClickHouse Cloud via API
- Server-side caching to minimize ClickHouse queries
- Secure handling of credentials (not exposed to frontend)
- Responsive design for all devices
- Simplified UI with single-column metric display
- Modular architecture for easy addition of new metrics
- Deployment to Vercel with serverless functions
- Query Count - Number of queries executed
- Data Processed - Amount of data processed by queries
- Average Query Time - Average query execution time
- Error Rate - Percentage of failed queries
This application follows a two-component architecture with server-side caching:
-
Frontend Dashboard - React application deployed to Vercel
- Visualizes metrics data using Chart.js
- Fetches data from a secure API proxy
-
API Proxy - Serverless API functions that interface with ClickHouse
- Handles authentication securely
- Implements caching to minimize ClickHouse queries
- Executes queries and returns results to the dashboard
- Deployed as Vercel Serverless Functions in the same project
-
Caching System - Reduces load on ClickHouse
- Uses
/tmpdirectory in Vercel functions for file-based caching - Falls back to in-memory caching if file operations fail
- Automatic daily refresh of cache data
- Configurable TTL (time to live) for cache entries
- Uses
-
Clone this repository:
git clone https://github.com/yourusername/clickhouse-metrics-dashboard.git cd clickhouse-metrics-dashboard -
Install dependencies (including API dependencies):
# Install main project dependencies npm install # Install API dependencies cd api && npm install && cd ..
-
Create
.envfile with your configuration:cp .env.example .env # Edit .env with your settings -
Start the development server:
npm start
├── README.md
├── api/
│ ├── cache.js # Cache management implementation
│ ├── cron.js # Automatic refresh functionality
│ ├── metrics.js # Main API endpoint with caching support
│ ├── mock.js # Mock data generator
│ ├── test.js # Cache status API endpoint
│ ├── package.json # API dependencies
│ └── queries/ # Query definitions as JSON
├── public/ # Static assets
├── scripts/
│ └── export-queries.js # Script to export queries from frontend to API
├── src/
│ ├── components/
│ │ ├── Card.js # Card component
│ │ ├── Chart.js # Chart component
│ │ ├── Dashboard.js # Main dashboard component
│ │ ├── Header.js # Simplified header component
│ │ └── MetricWidget.js # Individual metric display
│ ├── services/
│ │ ├── api.js # API service with cache support
│ │ └── metrics.js # Metrics service
│ ├── queries/ # Frontend metric definitions
│ ├── utils/
│ │ ├── config.js # Application configuration
│ │ ├── dates.js # Date utilities
│ │ └── formatter.js # Value formatters
│ └── styles.css # Application styles
└── vercel.json # Vercel deployment configuration
The dashboard implements a server-side caching system to minimize ClickHouse queries:
- First Request: On first request, the system queries ClickHouse and caches the results
- Subsequent Requests: Future requests use cached data (until cache expires)
- Automatic Refresh: Cache is automatically refreshed once per day
- Fallback Mechanism: If ClickHouse queries fail, system uses cached data
Configure caching behavior with these environment variables:
CACHE_TTL_HOURS: How long cache entries are valid (default: 24 hours)CACHE_REFRESH_HOURS: How often the cache is refreshed (default: 24 hours)
When deploying to Vercel's serverless environment:
- File System Limitations: The system uses
/tmpdirectory for caching - In-Memory Fallback: Falls back to in-memory caching if file operations fail
- Ephemeral Storage: Cache might reset between function invocations
Check cache status by visiting:
https://your-deployment-url/api/test
Include your API key in the X-API-Key header.
Force a cache refresh by adding refreshCache=true to your API requests:
https://your-deployment-url/api/metrics?refreshCache=true
To deploy this dashboard, you'll need:
- A Vercel account
- A ClickHouse instance or ClickHouse Cloud account
- Your ClickHouse connection details
-
Install and log in to Vercel CLI:
npm install -g vercel vercel login
-
Deploy with Vercel:
vercel --prod
-
Configure environment variables in Vercel:
- Go to your project in the Vercel dashboard
- Navigate to Settings > Environment Variables
- Add the following environment variables:
CLICKHOUSE_HOST: Your ClickHouse host URLCLICKHOUSE_USER: ClickHouse usernameCLICKHOUSE_PASSWORD: ClickHouse passwordCLICKHOUSE_DATABASE(optional): Database nameAPI_KEY: A secure key for API authenticationREACT_APP_API_URL:/api(relative path)REACT_APP_API_KEY: Same value asAPI_KEYREACT_APP_DASHBOARD_TITLE(optional): Custom dashboard titleCACHE_TTL_HOURS(optional): Cache validity period in hoursCACHE_REFRESH_HOURS(optional): Cache refresh interval in hours
If you encounter issues:
-
Check Vercel Logs:
vercel logs your-deployment-url
-
Check Cache Status: Visit
/api/testto see cache information. -
Use Mock Data Temporarily: Set
USE_MOCK_DATA=truein environment variables during testing.
To add a new metric:
-
Add a new metric query file in
src/queries/:// src/queries/newMetric.js const newMetric = { id: 'newMetricId', name: 'New Metric Name', description: 'Description of the new metric', format: 'formatNumber', // Use existing formatter or add new in formatter.js chartType: 'line', color: '#00BCD4', query: ` SELECT toDate(event_time) AS date, count() AS value FROM your_table WHERE event_time BETWEEN '{from}' AND '{to} 23:59:59' GROUP BY date ORDER BY date ` }; export default newMetric;
-
Import the new metric in
src/queries/index.js:import newMetric from './newMetric'; const allQueries = [ // Existing metrics newMetric ]; export default allQueries;
-
Run the export script to update the API:
npm run export-queries
-
Deploy your changes to Vercel:
vercel --prod
For local development without ClickHouse:
-
Set the
USE_MOCK_DATAenvironment variable totrue:USE_MOCK_DATA=true -
The API will generate mock data instead of querying ClickHouse.
This project is licensed under the MIT License.