Spring Boot + Apache Doris: Real-Time Data Warehousing Pitfalls and OLAP Optimization

Why Choose Apache Doris?

The team evaluated ClickHouse, Hologres, and Elasticsearch but selected Apache Doris for its minimal architecture (only FE and BE processes), no dependency on ZooKeeper or HDFS, vectorized execution, and CBO optimizer. Doris offers better standard SQL support than ClickHouse (avoiding OOM on joins) and lower storage costs with faster aggregations than Elasticsearch, making it suitable for both real-time dashboards and high-concurrency point queries.

Spring Boot Connection Pool Configuration and Pitfalls

Doris is MySQL protocol compatible, so the MySQL JDBC driver (8.0.33) works directly. However, OLAP queries are slower (seconds to tens of seconds) and resource-intensive, requiring different HikariCP settings than OLTP.

Key HikariCP Settings

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://doris-fe-host:9030/your_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: your_password
    hikari:
      max-lifetime: 10800000
      idle-timeout: 1800000
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      data-source-properties:
        useServerPrepStmts: true
        cachePrepStmts: true
        prepStmtCacheSize: 250
        rewriteBatchedStatements: true

Critical pitfalls: max-lifetime must be less than Doris server's wait_timeout (default 8 hours); 3-4 hours recommended to avoid "Communications link failure". MyBatis-Plus pagination plugin may misbehave on complex analytical queries; prefer native LIMIT/OFFSET or cursor-based pagination.

Data Model Selection: Don't Create Tables Blindly

Doris has three data models; choosing wrong leads to poor query performance that's hard to fix later.

3.1 Duplicate (Detail Model)

Best for append-only logs and flow data; highest load performance.

3.2 Aggregate (Aggregation Model)

Suitable for fixed-dimension reports (e.g., daily PV/UV). Automatically aggregates same-key data on load (Sum, Max, etc.), saving storage and avoiding runtime computation.

3.3 Unique (Primary Key Model)

For update-heavy scenarios like CDC order status sync. Major pitfall: Early versions used Merge-on-Read (MoR), causing query performance to collapse with frequent updates. Since version 1.2, must enable Merge-on-Write (MoW) by adding "enable_unique_key_merge_on_write" = "true" in table properties to merge during load, dramatically improving query speed.

CREATE TABLE dwd_order_info (
  order_id BIGINT,
  user_id BIGINT,
  order_status TINYINT,
  create_time DATETIME
)
UNIQUE KEY(order_id, user_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 16
PROPERTIES (
  "enable_unique_key_merge_on_write" = "true",
  "replication_num" = "3"
);

Real-Time Data Ingestion Methods

4.1 Stream Load: Micro-batch Push

HTTP API push for Spring Boot internal micro-batches. Connect directly to BE nodes (port 8040) for load balancing. Example Java implementation using HttpClient with JSON payload; for large data, stream file directly to avoid OOM.

public void streamLoad(String tableName, List<Map<String, Object>> dataList) {
  String loadUrl = String.format("http://%s:%s/api/%s/%s/_stream_load",
    dorisBeHost, "8040", dbName, tableName);
  String jsonData = JSON.toJSONString(dataList);
  HttpPut put = new HttpPut(loadUrl);
  put.setHeader("Expect", "100-continue");
  put.setHeader("Authorization", "Basic " + Base64.getEncoder().encodeToString("root:password".getBytes()));
  put.setHeader("Content-Type", "application/json");
  put.setHeader("format", "json");
  put.setHeader("strip_outer_array", "true");
  put.setEntity(new StringEntity(jsonData, StandardCharsets.UTF_8));
  // Execute and check Status == Success
}

4.2 Routine Load: Kafka Continuous Subscription

Zero-code ingestion when data is already in Kafka; Doris consumes automatically.

4.3 Flink-Doris-Connector: Enterprise Standard

For Flink-based real-time computing, use Flink-Doris-Connector with two-phase commit (2PC) for Exactly-Once semantics. Set sink.batch.size around 10,000 to balance latency and throughput.

Materialized Views: Query Acceleration "Physical Plugin"

Materialized views pre-compute complex query results. Doris supports synchronous (single-table only) and asynchronous (multi-table join, scheduled refresh) views. Asynchronous views are preferred.

CREATE MATERIALIZED VIEW mv_dws_sales_daily
BUILD IMMEDIATE
REFRESH ASYNC
PARTITION BY RANGE(date_trunc('day', order_time)) ()
DISTRIBUTED BY HASH(store_id) BUCKETS 16
PROPERTIES (
  "replication_num" = "3",
  "auto_refresh_partitions_limit" = "3"
)
AS
SELECT
  date_trunc('day', o.order_time) as dt,
  s.store_id,
  SUM(o.pay_amount) as total_pay,
  COUNT(DISTINCT o.user_id) as uv
FROM dwd_order_info o
JOIN dim_store s ON o.store_id = s.store_id
GROUP BY dt, s.store_id;

Transparent rewrite: Business code still queries base tables ( dwd_order_info, dim_store); Doris CBO automatically routes to the materialized view, reducing query time from seconds to milliseconds without code changes.

Multi-Table Join Optimization: Colocate Join Is a Game-Changer

OLAP multi-table joins often cause performance issues. Doris supports Broadcast, Shuffle, but Colocate Join is most efficient: when two tables share the same distribution key, bucket count, and co-located data, joins execute locally without network shuffle.

-- Table A
CREATE TABLE table_a (
  id INT, user_id INT, val INT
) DISTRIBUTED BY HASH(user_id) BUCKETS 10
PROPERTIES ("colocate_with" = "group_user");

-- Table B
CREATE TABLE table_b (
  id INT, user_id INT, info STRING
) DISTRIBUTED BY HASH(user_id) BUCKETS 10
PROPERTIES ("colocate_with" = "group_user");

Pitfalls: 1) Bucket counts must match exactly. 2) During cluster scaling, Colocate groups temporarily invalidate; queries may fall back to Shuffle Join. Schedule scaling during low-traffic periods. Doris also enables Runtime Filter by default, pushing filters down during Hash Join automatically.

Resource Isolation: Prevent Business Teams from Running "Large Queries"

Shared clusters risk rogue queries consuming all CPU/memory. Doris 1.2 introduced Resource Groups for hard CPU/memory isolation.

CREATE RESOURCE GROUP rg_report
FOR USER 'report_user'
WITH CPU_SHARE = '20', MEM_LIMIT = '30%';

Large Query Interception

Set global limits and query queue:

SET GLOBAL max_user_connections = 50;
SET GLOBAL query_queue_max_queued_queries = 100;

In application code, use session variable SET query_timeout = 30 to kill slow queries that could cause OOM.

Data Updates and Compaction Tuning

CDC real-time sync means frequent updates. With Unique model + MoW, background Compaction becomes critical. If updates outpace compaction, version accumulation slows queries or causes errors.

Ops advice: Monitor BE's tablet compaction score. If persistently high, increase compaction_task_num_per_disk and base_compaction_num_threads_per_disk in BE config to allocate more merge threads.

BI Tool Integration Final Steps

9.1 Apache Superset

Uses MySQL protocol. In SQL Lab, increase Query Timeout to 300s+ to prevent complex report timeouts. Enable Superset data caching for slowly changing wide tables to reduce Doris load.

9.2 DataGrip / DBeaver Freeze Issue

Developers experience metadata loading hangs. Cause: client scans all databases/tables/columns via JDBC metadata. Fix: In DataGrip data source settings, disable Introspect using JDBC metadata or uncheck Introspect all schemas under Options → Driver, loading only current schema.

Cluster Operations: Never Use DROP for Decommissioning

Scaling out is simple: ALTER SYSTEM ADD BACKEND triggers auto-rebalance. For scaling in, always use DECOMMISSION, never DROP.

-- Safe decommission: migrates data first, then removes
ALTER SYSTEM DECOMMISSION BACKEND "old_be_host:9050";

-- DANGEROUS: direct drop loses data irreversibly
-- ALTER SYSTEM DROP BACKEND "old_be_host:9050";

This is a hard-learned lesson; verify commands before execution.

Closing Thoughts

Doris's MPP architecture and minimal ops save significant effort, but it's no silver bullet. Wrong data model, misconfigured Colocate Join, or neglected compaction tuning still yield poor performance. Real-time data warehousing is 30% tooling, 70% design and tuning. Recommend thorough stress testing pre-launch and vigilant monitoring post-launch. Hope these battle scars help others avoid detours and leave on time.