Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Setup Guide

Execute Schema in ClickHouse

Method 1: ClickHouse Client (Local)

Basic execution:

clickhouse-client < docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

With database selection:

clickhouse-client --database=syn < docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

With output to file:

clickhouse-client < docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql > setup_output.log 2>&1

Method 2: ClickHouse Client (Remote Server)

Connect to production server:

clickhouse-client \
  --host=clickhouse-prod.example.com \
  --port=9000 \
  --user=admin \
  --password=your_secure_password \
  --database=syn \
  < docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

Example with AWS ClickHouse:

clickhouse-client \
  --host=clickhouse.c123abc.aws.clickhouse.cloud \
  --port=9440 \
  --user=default \
  --password=your_secure_password \
  --secure \
  < docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

Example with custom port and SSL:

clickhouse-client \
  --host=analytics-db.company.com \
  --port=9440 \
  --user=analytics_user \
  --password=your_secure_password \
  --ssl \
  --database=syn \
  < docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

Using environment variables (more secure):

export CLICKHOUSE_HOST=clickhouse-prod.example.com
export CLICKHOUSE_PORT=9000
export CLICKHOUSE_USER=admin
export CLICKHOUSE_PASSWORD=your_secure_password
export CLICKHOUSE_DATABASE=syn

clickhouse-client < docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

Execute single query from CLI:

clickhouse-client \
  --host=clickhouse-prod.example.com \
  --user=admin \
  --password=your_secure_password \
  --query="SELECT COUNT(*) FROM syn.analytics_revenue"

Method 3: HTTP API

Basic execution:

curl -X POST http://localhost:8123/ \
  --data-binary @docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

Remote server with authentication:

curl -X POST http://clickhouse-prod.example.com:8123/ \
  --user admin:your_secure_password \
  --data-binary @docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

With database parameter:

curl -X POST "http://localhost:8123/?database=syn" \
  --data-binary @docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql

Method 4: Using cat with pipe

cat docs/analytics-dashboard-implementation/clickhouse_analytics_schema.sql | curl -X POST http://localhost:8123/ --data-binary @-

Verify Installation

Using ClickHouse Client

List all analytics tables:

clickhouse-client --query="SELECT database, name, engine FROM system.tables WHERE database='syn' AND (name LIKE 'analytics_%' OR name LIKE 'mv_%')"

Check row counts for each table:

clickhouse-client --query="
SELECT 'Revenue' as table_name, COUNT(*) as row_count FROM syn.analytics_revenue
UNION ALL
SELECT 'Bookings', COUNT(*) FROM syn.analytics_bookings_covers
UNION ALL
SELECT 'Capacity', COUNT(*) FROM syn.analytics_capacity
"

Check data range:

clickhouse-client --query="
SELECT
  'Revenue' as metric,
  COUNT(*) as total_rows,
  COUNT(DISTINCT restaurant_id) as unique_restaurants,
  MIN(date) as earliest_date,
  MAX(date) as latest_date
FROM syn.analytics_revenue
UNION ALL
SELECT
  'Bookings',
  COUNT(*),
  COUNT(DISTINCT restaurant_id),
  MIN(date),
  MAX(date)
FROM syn.analytics_bookings_covers
UNION ALL
SELECT
  'Capacity',
  COUNT(*),
  COUNT(DISTINCT restaurant_id),
  MIN(date),
  MAX(date)
FROM syn.analytics_capacity
"

View sample revenue data (today):

clickhouse-client --query="
SELECT
  restaurant_id,
  date,
  hour,
  revenue
FROM syn.analytics_revenue
WHERE date = today()
ORDER BY hour DESC
LIMIT 24
"

View sample bookings data (today):

clickhouse-client --query="
SELECT
  restaurant_id,
  date,
  hour,
  bookings_count,
  confirmed_covers,
  avg_party_size
FROM syn.analytics_bookings_covers
WHERE date = today()
ORDER BY hour DESC
LIMIT 24
"

Using HTTP API

Verify tables exist:

curl "http://localhost:8123/?query=SELECT+database,name,engine+FROM+system.tables+WHERE+database='syn'"

Check table row counts:

curl "http://localhost:8123/?query=SELECT+COUNT(*)FROM+syn.analytics_revenue"

For Production Environment

Remote verification with authentication:

clickhouse-client \
  --host=clickhouse-prod.example.com \
  --user=admin \
  --password=your_secure_password \
  --query="SELECT database, name FROM system.tables WHERE database='syn' LIMIT 10"

Check if materialized views are working:

clickhouse-client \
  --host=clickhouse-prod.example.com \
  --user=admin \
  --password=your_secure_password \
  --query="SELECT name, type FROM system.tables WHERE database='syn' AND name LIKE 'mv_%'"

Monitor MV insertion logs:

clickhouse-client \
  --host=clickhouse-prod.example.com \
  --user=admin \
  --password=your_secure_password \
  --query="SELECT * FROM system.mutations WHERE database='syn' AND table LIKE 'analytics%'"

What Gets Created

Tables:

  • syn.analytics_revenue - Hourly revenue
  • syn.analytics_bookings_covers - Hourly bookings and covers
  • syn.analytics_capacity - Hourly capacity utilization

Materialized Views:

  • syn.mv_analytics_revenue - Feeds analytics_revenue table
  • syn.mv_analytics_bookings_covers - Feeds analytics_bookings_covers table
  • syn.mv_analytics_capacity - Feeds analytics_capacity table

Data:

  • Last 90 days of historical data
  • Automatically updated from booking_production reservations

Data Update Frequency

  • Real-time: MVs automatically update when new reservations are added
  • Latency: < 5 seconds from booking to analytics
  • No backfill needed: MVs handle continuous updates

Troubleshooting

Connection Issues

Test connection to ClickHouse:

clickhouse-client --host=clickhouse-prod.example.com --user=admin --password=your_password --query="SELECT version()"

Check if port is accessible:

telnet clickhouse-prod.example.com 9000

For HTTPS connections (port 9440):

clickhouse-client \
  --host=clickhouse-prod.example.com \
  --port=9440 \
  --secure \
  --user=admin \
  --password=your_password \
  --query="SELECT 1"

Database Issues

Create syn database if not exists:

clickhouse-client --query="CREATE DATABASE IF NOT EXISTS syn"

Check if tables were created:

clickhouse-client --query="SHOW TABLES IN syn"

Check for errors in materialized views:

clickhouse-client --query="
SELECT
  name,
  definition
FROM system.tables
WHERE database='syn'
  AND name LIKE 'mv_%'
"

View table creation details:

clickhouse-client --query="SHOW CREATE TABLE syn.analytics_revenue"

Data Issues

Check if data is being inserted:

clickhouse-client --query="
SELECT
  COUNT(*) as total_rows,
  MAX(date) as latest_date
FROM syn.analytics_revenue
"

Find empty hours (potential data issues):

clickhouse-client --query="
SELECT
  date,
  hour,
  COUNT(*) as restaurant_count
FROM syn.analytics_revenue
WHERE date = today()
GROUP BY date, hour
ORDER BY hour
"

Check if MVs are capturing new data:

clickhouse-client --query="
SELECT
  date,
  COUNT(*) as record_count
FROM syn.analytics_revenue
WHERE date >= today() - 1
GROUP BY date
ORDER BY date DESC
"

Performance Issues

Check table size:

clickhouse-client --query="
SELECT
  name,
  formatReadableSize(total_bytes) as size,
  formatReadableSize(total_compressed_bytes) as compressed_size
FROM system.tables
WHERE database='syn'
"

Optimize tables (cleanup old versions):

clickhouse-client --query="OPTIMIZE TABLE syn.analytics_revenue FINAL"
clickhouse-client --query="OPTIMIZE TABLE syn.analytics_bookings_covers FINAL"
clickhouse-client --query="OPTIMIZE TABLE syn.analytics_capacity FINAL"

Check query performance:

clickhouse-client --query="
SELECT
  restaurant_id,
  date,
  SUM(revenue) as total_revenue
FROM syn.analytics_revenue
WHERE restaurant_id = 1597 AND date >= today() - 30
GROUP BY restaurant_id, date
" --format=JSON