StarRocks — Analytical Data Warehouse Guide

StarRocks — Analytical Data Warehouse Guide

StarRocks is an MPP analytical database with columnar storage, a fully vectorized execution engine, a cost-based optimizer (CBO), and MySQL protocol compatibility. It supports real-time ingestion, sub-second queries, and zero-migration data lake analytics via external catalogs.

Before writing DDL or loading data, read the relevant reference files:

• references/table-design.rst — Table types, partitioning, bucketing, indexing quick-ref
• references/data-loading.rst — Loading methods comparison and patterns
• references/query-acceleration.rst — Materialized views, CBO, join strategies, caching

---

Table Design

Choose the Right Table Type

Type	Use When	Key Behaviour
Duplicate Key	Raw logs, append-only data	Rows stored as-is; sort key for scan efficiency
Aggregate	Pre-aggregated metrics	Rows with same key auto-aggregate (SUM, MAX, MIN, etc.)
Unique Key	Dimension tables, dedup	Latest row per key wins (merge-on-read)
Primary Key	Real-time upserts, CDC	Latest row per key wins (delete+insert, better read perf)

Default choice: Primary Key for mutable data, Duplicate Key for append-only.

Partitioning

Partition by time or a low-cardinality dimension that aligns with query predicates. Use expression-based partitioning for automatic partition creation:

CREATE TABLE events (
    event_time  DATETIME NOT NULL,
    user_id     BIGINT,
    event_type  VARCHAR(64),
    payload     JSON
)
PRIMARY KEY (event_time, user_id)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS 16
PROPERTIES ("replication_num" = "3");

Bucketing

• Hash bucketing — use when queries filter on specific columns (e.g., user_id)
• Random bucketing — use for append-only tables without clear filter columns
• Bucket count: aim for 100 MB–1 GB per tablet after compression

Sort Key (Table Clustering)

The sort key is the highest-leverage physical design knob. Place the most frequently filtered columns first. For Primary Key tables, the primary key IS the sort key.

Indexing

Index Type	Best For
Prefix index	Range scans on leading sort key columns (automatic)
Bitmap index	Low-cardinality columns in WHERE clauses
Bloom filter	High-cardinality equality lookups
Ngram bloom filter	LIKE '%substring%' queries on text columns

---

Data Loading

Method Selection

Method	Source	Best For
Stream Load	HTTP push	Small batches, real-time micro-batch
Broker Load	S3, HDFS, GCS	Large-scale batch import from object storage
INSERT INTO	SQL	Small inserts, ETL within StarRocks
Routine Load	Kafka	Continuous streaming ingestion
Spark connector	Spark	ETL pipelines via Spark jobs
Flink connector	Flink	Streaming ETL via Flink
Pipe	S3 (continuous)	Auto-discovery of new files in a bucket
INSERT INTO FILES	StarRocks → S3/HDFS	Data export to remote storage

Loading Best Practices

• Use Primary Key tables for CDC/upsert workloads
• Transform data during load with column expressions to avoid post-load ETL
• Enable strict mode to reject rows that fail type conversion
• For Kafka: one Routine Load job per topic partition group; monitor via information_schema.routine_load_jobs

See references/data-loading.rst for detailed patterns per method.

---

Data Unloading

• EXPORT — CSV to HDFS/S3; simple but limited formats
• INSERT INTO FILES — Parquet/ORC/CSV to S3/HDFS with partitioned output
• Arrow Flight SQL — High-throughput programmatic access (v3.5+)
• Spark/Flink connectors — Read StarRocks tables as DataFrames

Prefer INSERT INTO FILES for bulk export with format control:

INSERT INTO FILES (
    "path" = "s3://bucket/export/",
    "format" = "parquet",
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "..."
)
SELECT * FROM my_table WHERE dt = '2024-01-01';

---

Query Acceleration

Cost-Based Optimizer (CBO)

Collect statistics so the CBO can choose optimal plans:

-- Full collection (run periodically or after large loads)
ANALYZE TABLE my_table;

-- Sample-based for large tables
ANALYZE SAMPLE TABLE my_table;

-- Check stats freshness
SHOW COLUMN STATS my_table;

Materialized Views

Synchronous MVs — single-table, auto-refreshed on load, transparent rewrite:

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', order_time) AS dt, SUM(amount) AS total
FROM orders
GROUP BY date_trunc('day', order_time);

Asynchronous MVs — multi-table, scheduled refresh, query rewrite:

CREATE MATERIALIZED VIEW mv_user_orders
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS
SELECT u.name, COUNT(*) AS order_count, SUM(o.amount) AS total
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

Join Optimizations

• Colocate join — eliminates shuffle when joined tables share the same distribution (same bucket columns and count). Set "colocate_with" property.
• Skew join — handles data skew by broadcasting skew values
• Lateral join — use with unnest() for array/JSON expansion

Caching

• Query cache — caches query results; useful for repeated dashboard queries
• Data cache — caches remote storage data locally (shared-data mode)

See references/query-acceleration.rst for the full acceleration toolkit.

---

Catalog & Schema Management

Internal vs External Catalogs

• Internal catalog (default_catalog) — StarRocks-managed tables
• External catalogs — query Hive, Iceberg, Hudi, Delta Lake, JDBC without data movement

-- Create a Hive external catalog
CREATE EXTERNAL CATALOG hive_catalog
PROPERTIES (
    "type" = "hive",
    "hive.metastore.uris" = "thrift://metastore:9083"
);

-- Query across catalogs
SELECT s.user_id, h.page_views
FROM default_catalog.analytics.sessions s
JOIN hive_catalog.web.pageviews h ON s.user_id = h.user_id;

Information Schema

Use information_schema views for metadata queries:

• tables / columns — table and column metadata
• materialized_views — MV definitions and refresh status
• loads / load_tracking_logs — load job status and errors
• routine_load_jobs — Kafka ingestion job monitoring
• be_tablets — tablet distribution across BE nodes
• partitions_meta — partition details

---

Resource Groups

Isolate workloads for multi-tenant environments:

-- Create a resource group for ETL workloads
CREATE RESOURCE GROUP etl_group
TO (user = 'etl_user')
WITH (cpu_weight = 4, mem_limit = '40%', concurrency_limit = 20);

-- Create a resource group for dashboard queries
CREATE RESOURCE GROUP dashboard_group
TO (user = 'dashboard_user')
WITH (cpu_weight = 8, mem_limit = '30%', concurrency_limit = 50);

---

Best Practices Summary

1. Partition by time — align with query predicates and retention policies
2. Hash-bucket on the most filtered column — reduces scan width
3. Sort key = most selective filter columns first — maximises prefix index hit rate
4. Collect CBO stats after bulk loads — stale stats produce bad plans
5. Use async MVs for cross-table aggregates — offload repeated joins to refresh time
6. Primary Key tables for CDC — better read performance than Unique Key
7. Colocate tables that join frequently — eliminates network shuffle
8. Monitor via information_schema — loads, routine_load_jobs, be_tablets

---

References

• StarRocks Introduction
• Best Practices
• Table Design
• Data Loading
• Data Unloading
• Information Schema
• Query Acceleration
• references/table-design.rst — Table types, partitioning, bucketing, indexing quick-ref
• references/data-loading.rst — Loading methods comparison and patterns
• references/query-acceleration.rst — Materialized views, CBO, join strategies, caching