Database

1. Overview

Friendly QoE Monitoring operates with four databases:

Database Engine Description

Main DB

MySQL / Oracle

Core IoT ACS data — CPE devices, product class groups, monitored parameters, user accounts

UI DB

MySQL / Oracle

QoE-specific data — KPI definitions, custom views, device groups, group templates, alarm thresholds

Quartz DB

MySQL / Oracle

Scheduler persistence — job definitions, triggers, cluster coordination

ClickHouse

ClickHouse

Time-series storage — KPI measurements, diagnostic results, alarm history

2. Configuration (MySQL / Oracle)

The application uses Spring profiles to switch between MySQL and Oracle databases. Set the active profile via the SPRING_PROFILES_ACTIVE environment variable.

2.1. MySQL Profile

spring:
  profiles: mysql
  datasource:
    main:
      url: ${MAIN_DB_URL}
      username: ${MAIN_DB_USERNAME}
      password: ${MAIN_DB_PASSWORD}
    ui:
      url: ${UI_DB_URL}
      username: ${UI_DB_USERNAME}
      password: ${UI_DB_PASSWORD}

2.2. Oracle Profile

spring:
  profiles: oracle
  datasource:
    main:
      url: ${MAIN_DB_URL}
      username: ${MAIN_DB_USERNAME}
      password: ${MAIN_DB_PASSWORD}
    ui:
      url: ${UI_DB_URL}
      username: ${UI_DB_USERNAME}
      password: ${UI_DB_PASSWORD}

2.3. Externalized SQL

Database queries are stored in sql.properties files with separate variants for MySQL and Oracle. When modifying queries:

  1. Check sql.properties for the externalized query

  2. Update both MySQL and Oracle variants if applicable

  3. Test with both profiles if the query differs between dialects

3. ClickHouse Time-Series Storage

ClickHouse stores all time-series monitoring data. It is optimized for high-volume analytical queries over large datasets.

3.1. Connection Configuration

clickhouse:
  url: ${CLICKHOUSE_DB_URL}
  username: ${CLICKHOUSE_DB_USERNAME}
  password: ${CLICKHOUSE_DB_PASSWORD}

3.2. Database Name

The ClickHouse database name is ftacs_qoe_ui_data.

3.3. Monitoring Data Tables

Table Engine Description

cpe_data

MergeTree

Raw monitored parameter values received from devices (created, serial, name_id, value, periodic, location_id, orig_name_id). Partitioned by date, ordered by serial, name_id, created.

kpi_data

MergeTree

Calculated KPI values displayed on graphs (created, serial, kpi_id, value, periodic, kpi_value_num, name_id, name_ids_in_formula). Partitioned by date, ordered by serial, kpi_id, created.

kpi_data_in_alarm

MergeTree

KPI data points that triggered an alarm. Same structure as kpi_data with additional level (alarm severity) and group_id columns.

cpe_monitor_history

ReplacingMergeTree

Monitor start/stop timestamps per device and parameter (started, finished, serial, name_id).

user_activity

MergeTree

User action audit log (created, username, type_id, session_id, description).

schema_version

TinyLog

Database schema version tracking (version, created).

3.4. Alarm Tables

Table Engine Description

kpi_threshold_history_repl

ReplacingMergeTree

Single device alarm history (id, created, updated, serial, kpi_id, child_kpi_id, value, threshold_condition, state, level, isp_id, group_id).

serials_in_group_alarm

ReplacingMergeTree

Devices participating in group alarms (group_id, kpi_id, child_kpi_id, serials array, isp_id, cnt, created). Partitioned by group_id.

3.5. Diagnostic Tables

Table Engine Description

diag_speed_test

ReplacingMergeTree

Upload/Download diagnostic results — FCC (created, serial, completed, name, value, value_total, file_size, test_bytes, total_bytes, num_conn, bom_time, eom_time, gpv_sent, ip_addr, login_name, host_url).

diag_ip_ping

ReplacingMergeTree

IPPing diagnostic results — FCC (created, serial, completed, value, success_cnt, failure_cnt, avg_resp_time, gpv_sent, ip_addr, login_name, host).

diag_trace

ReplacingMergeTree

Trace route diagnostic state (id UUID, created, serial, completed, data_size, max_hops, host, gpv_sent, ip_addr, login_name).

diag_trace_hops

MergeTree

Trace route hop-by-hop results (diag_trace_id, created, hop, error_code, host, host_address, times).

diag_udp_echo

ReplacingMergeTree

UDP echo diagnostic results (id UUID, created, serial, completed, success_cnt, failure_cnt, avg_resp_time, min_resp_time, max_resp_time, gpv_sent, ip_addr, login_name, host).

diag_udp_echo_packet

MergeTree

UDP echo individual packet results (udp_echo_id, created, serial, num, success, send_time, receive_time, gen_sn, resp_sn, rcv_ts, reply_ts, failure_cnt).

3.6. Wi-Fi Tables

Table Engine Description

wifi_cpe_data

ReplacingMergeTree

Wi-Fi Neighboring Diagnostic results and channel change history (created, completed, serial, name_id, ssid, channel, changing, thresh_ssid, thresh_signal, prev_channel, isp_id).

wifi_collisions

MergeTree

Detected Wi-Fi channel collisions (created, serial, name_id, ssid, channel, signal).

wifi_cpe_diagnostic_sent

ReplacingMergeTree

Wi-Fi diagnostic task tracking (created, serial, completed, gpv_sent).

3.7. User Experience Tables

Table Engine Description

user_exp_host

MergeTree

User experience host data (created, serial, name_id, mac, name, interface_type, layer1, layer3, addr, active).

user_exp_assoc_device

MergeTree

User experience associated device data (created, serial, name_id, mac, rssi, signal).

host_data

MergeTree

Connected hosts data — ETL feature (created, serial, name_id, name, mac, ipv4, ipv6, connectivity, ssid, bssid, rssi).

router_data

MergeTree

Router device data — ETL feature (created, serial, mac, ipv4, ipv6, ssid24, bssid24, ssid5, bssid5, byte_sent, byte_received).

3.8. Cached Data from Main DB

Table Engine Description

ft_cpe_info

ReplacingMergeTree

Device product data (serial, group_id, product_class, manufacturer_name, created).

ft_qoe_cpe_info

Join(ANY, LEFT, serial)

QoE CPE data (serial, mac, periodic, product_class, manufacturer_name, last_connection, updated).

ft_cust_device

Join(ANY, LEFT, serial)

Account information (serial, login_name, name, location, telephone, userid, userstatus, user_tag, zip, latitude, longitude, updated, cust1-cust20).

ft_cpe_domain_info

Join(ANY, LEFT, serial)

Domain data (id, isp_id, serial, updated, isp_name).

Tables with Join engine are populated from the Main DB via JDBC and serve as lookup tables for accelerating ClickHouse queries. Corresponding temporary tables (tmp_ft_qoe_cpe_info, tmp_ft_cust_device, tmp_ft_cpe_domain_info) with Memory engine are used during the sync process.

3.9. Materialized Views

View Engine Description

kpi_data_aggregated

AggregatingMergeTree

Pre-aggregated KPI data in 5-minute intervals for Device Groups page. Stores avg, min, max states per kpi_id, kpi_value_num, serial.

kpi_data_latest

ReplacingMergeTree

Latest KPI value per device and KPI. Automatically updated when kpi_data receives new rows.

cpe_data_latest

ReplacingMergeTree

Latest raw parameter value per device and parameter. Automatically updated when cpe_data receives new rows.
← Back | Main Page