http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> i want to define a primary key tabl...
# questions-and-troubleshooting
k
@Rocky i want to define a primary key table based on an iceberg external table in a shared data cluster for < 75 ms queries. how could i have the pk table refresh with a scheduled task from the external table
r
To achieve sub-75 ms query latency in a StarRocks shared-data cluster while syncing from an Iceberg external table, you should use the Primary Key (PK) storage model. The PK model is specifically designed for high-performance ad-hoc queries because it uses a "Delete+Insert" strategy, avoiding the merge-on-read overhead that slows down other models. For the refresh mechanism, you have two primary options depending on your StarRocks version: Option 1: Primary Key Materialized View (v3.3+) This is the most integrated approach. From v3.3 onwards, you can create an asynchronous Materialized View that uses the Primary Key engine internally. It supports scheduled refreshes and can detect partition changes in Iceberg to perform incremental updates.
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_sync_mv
PRIMARY KEY(id) -- Set your unique identifier
PARTITION BY id_date -- Align with Iceberg partitions for incremental refresh
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Your scheduled task
PROPERTIES (
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE" -- Optimized for shared-data/object storage
)
AS 
SELECT id, data, id_date 
FROM iceberg_catalog.db.external_table;
Option 2: Scheduled Task with 
INSERT INTO
(v2.4+) If you prefer a manual table and an explicit task, you can create a standard Primary Key table and schedule a 
TASK
to sync the data. In StarRocks, 
INSERT INTO
on a PK table performs a Full Upsert (replaces rows with matching keys) by default. 1. Create the PK Table:
Copy code
sql
CREATE TABLE target_pk_table (
    id BIGINT NOT NULL,
    data STRING,
    ts DATETIME
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE"
);
2. Create the Scheduled Task:
Copy code
sql
CREATE TASK sync_iceberg_task
SCHEDULE EVERY 1 HOUR
AS 
INSERT INTO target_pk_table 
SELECT id, data, ts 
FROM iceberg_catalog.db.external_table;
Critical Tuning for < 75 ms Latency To guarantee sub-75 ms performance in a shared-data (compute-storage separated) environment: * Persistent Index: Ensure 
"enable_persistent_index" = "true"
is set. In shared-data clusters (v3.3.2+), use 
"persistent_index_type" = "CLOUD_NATIVE"
to persist the index to object storage, which prevents memory exhaustion on large datasets while maintaining fast lookup speeds. * Data Cache: Shared-data clusters rely on a local disk cache on Compute Nodes. Ensure the Data Cache is enabled so that "hot" data from your PK table stays local to the nodes, avoiding S3/object storage latency during queries. * Partition Alignment: If your Iceberg table is large, partition your StarRocks table by the same column. This allows the refresh task to use Partition-level incremental refresh (v3.1.4+ for Iceberg), which only syncs changed partitions rather than the entire dataset. entire table. * Limit Compaction Score: If queries slow down over time, it is usually due to too many versions (small files). In shared-data clusters, you can tune 
compact_threads
in 
be.conf
to ensure background compaction keeps up with your refresh frequency. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
k
@Rocky getting Query 1 ERROR at Line 91: : Getting syntax error at line 2, column 0. Detail message: Unexpected input 'PRIMARY', the most similar input is {'PROPERTIES', 'ORDER', 'PARTITION', 'REFRESH', 'COMMENT', '('}. based on the primary key mv statemetn you gave
r
The syntax error you encountered (
Unexpected input 'PRIMARY'
) indicates that your StarRocks version likely does not support Primary Key Materialized Views. This specific feature was introduced in StarRocks v3.3. If you are on a version earlier than v3.3 (e.g., v3.1 or v3.2), the 
PRIMARY KEY
clause is not valid within a 
CREATE MATERIALIZED VIEW
statement. Solution for StarRocks < v3.3 (PK Table + Task) Since you are in a shared-data cluster (which supports Primary Key tables since v3.1), you should use the Manual PK Table + Scheduled Task approach. This is the most reliable way to achieve sub-75 ms query performance on older versions. 1. Create the Primary Key Table This table will store the data from Iceberg and provide the high-speed query performance you need.
Copy code
sql
    CREATE TABLE sync_target_pk (
        id BIGINT NOT NULL,
        data_col STRING,
        ts DATETIME,
        event_date DATE
    )
    PRIMARY KEY (id)
    PARTITION BY (event_date)
    DISTRIBUTED BY HASH(id)
    PROPERTIES (
        "enable_persistent_index" = "true",
        "persistent_index_type" = "CLOUD_NATIVE" -- Required for shared-data performance
    );
2. Create a Scheduled Task to Refresh This task acts as your "refresh" mechanism. The 
INSERT INTO ... SELECT
on a Primary Key table automatically performs an Upsert (it updates existing rows with the same 
id
and inserts new ones).
Copy code
sql
    CREATE TASK iceberg_to_pk_sync
    SCHEDULE EVERY (INTERVAL 1 HOUR) -- Adjust your frequency here
    AS
    INSERT INTO sync_target_pk
    SELECT id, data_col, ts, event_date
    FROM iceberg_catalog.db_name.external_table_name
    -- Optional: Only sync recent data for efficiency
    WHERE event_date >= CURRENT_DATE - INTERVAL 1 DAY;
3. Start the Task
Copy code
sql
    ALTER TASK iceberg_to_pk_sync RESUME;
If you are on v3.3+ and still see the error The syntax for Primary Key MVs in v3.3 is very specific about the order of clauses. If the previous command failed, ensure the 
PRIMARY KEY
clause follows the name but comes before 
REFRESH
or `AS SELECT`:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_sync_mv
PRIMARY KEY (id) -- Must be before REFRESH and AS
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS 
SELECT id, data_col, ts FROM iceberg_catalog.db.ext_table;
Recommendation for < 75 ms Latency In a shared-data cluster, the most important properties for low latency are: * 
"persistent_index_type" = "CLOUD_NATIVE"
: This keeps the primary key index in object storage rather than just memory, which is critical for stability and performance in shared-data mode. * Warm up the Cache: Ensure the Compute Nodes (CN) have enough local disk space for the Data Cache. The first query after a refresh might be slower as it pulls from S3/Iceberg, but subsequent hits should be < 75 ms once cached. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
k
@Rocky i am on 4.1
r
Since you are on StarRocks v4.1, you have access to the most advanced Materialized View (MV) features. The syntax error you encountered (
Unexpected input 'PRIMARY'
) occurs because, unlike a standard table, a Primary Key Materialized View requires you to explicitly list the columns in parentheses immediately after the MV name. To achieve < 75 ms queries on a shared-data cluster, use the following refined syntax: 1. The Correct PK Materialized View Syntax
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_pk_mv
(
    id,        -- List all columns you want in the MV
    user_name,
    event_time,
    event_date
)
PRIMARY KEY (id) -- This clause now works because the columns above are defined
PARTITION BY event_date
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE" -- Critical for Shared-Data performance
)
AS 
SELECT 
    id, 
    user_name, 
    event_time, 
    event_date
FROM iceberg_catalog.db.external_table;
Why this is the best setup for < 75 ms * Primary Key Engine: Unlike standard MVs (which use a Duplicate/Aggregate engine), a PK MV uses the StarRocks Primary Key engine. This allows for extremely fast index-based lookups and avoids the "Merge-on-Read" overhead, which is essential for hitting sub-75 ms latencies. * Cloud Native Index: By setting 
"persistent_index_type" = "CLOUD_NATIVE"
, the primary key index is stored in object storage (S3/GCS/OSS) rather than consuming massive amounts of RAM. In version 4.1, this is highly optimized for shared-data clusters. * Iceberg Incremental Refresh: Since your source is Iceberg, StarRocks 4.1 supports incremental refresh for MVs. If your 
PARTITION BY
in the MV matches the partition column in Iceberg, StarRocks will only refresh the changed partitions instead of the whole table, keeping the "task" fast and lightweight. Alternative: Scheduled Task (The "Manual" Way) If you prefer to manage the table and the sync task separately (which some find easier for debugging), the syntax is slightly different and avoids the MV column-mapping strictness: 1. Create Table:
Copy code
sql
    CREATE TABLE target_pk_table (
        id BIGINT NOT NULL,
        data STRING,
        event_date DATE
    )
    PRIMARY KEY (id)
    PARTITION BY (event_date)
    DISTRIBUTED BY HASH(id)
    PROPERTIES (
        "enable_persistent_index" = "true",
        "persistent_index_type" = "CLOUD_NATIVE"
    );
2. Create Task:
Copy code
sql
    CREATE TASK iceberg_sync_task
    SCHEDULE EVERY 1 HOUR
    AS
    INSERT INTO target_pk_table
    SELECT id, data, event_date
    FROM iceberg_catalog.db.external_table;
Important for Shared-Data clusters: To ensure the < 75 ms target is met consistently: 1. Warm the Cache: Ensure your Compute Nodes have sufficient local disk space. The first query after a refresh might take > 75 ms as it pulls from object storage, but subsequent queries will hit the Data Cache and stay within your target. 2. Check Query Profile: If latency is higher than expected, run 
EXPLAIN ANALYZE <query>
and look for 
DataCacheHit
to confirm your data is being served from local SSD rather than the object store. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • table_design/table_types/primary_key_table.md References • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
k
@Rocky run ad hoc cache task
r
To run an ad-hoc cache warmup task in StarRocks 4.1 (shared-data cluster), use the 
CACHE SELECT
statement combined with 
SUBMIT TASK
. This proactively pulls data from the remote object store (where your Iceberg data or StarRocks segments live) into the local disk cache of your Compute Nodes. 1. Run a One-Time (Ad-hoc) Cache Task If you want to manually trigger a warmup immediately in the background:
Copy code
sql
SUBMIT TASK manual_warmup_task 
AS 
CACHE SELECT * FROM iceberg_pk_mv;
2. Schedule a Recurring Cache Task To ensure your cache is always "hot" (e.g., warming up the last 24 hours of data every hour), schedule the task:
Copy code
sql
SUBMIT TASK daily_warmup_task
SCHEDULE EVERY (INTERVAL 1 HOUR)
AS 
CACHE SELECT * 
FROM iceberg_pk_mv 
WHERE event_date >= CURRENT_DATE - INTERVAL 1 DAY;
3. Monitoring the Task Since these tasks run asynchronously, you can check their progress and success: * View the task definition:
Copy code
sql
    SELECT * FROM information_schema.tasks WHERE task_name = 'manual_warmup_task';
* Check execution status & cache metrics: The 
EXTRA_MESSAGE
column in 
task_runs
will show you how much data was actually written to the cache.
Copy code
sql
    SELECT task_name, state, create_time, finish_time, extra_message
    FROM information_schema.task_runs
    ORDER BY create_time DESC LIMIT 5;
Important Usage Notes for < 75 ms Goals: * Column Selection: If your 75 ms queries only target specific columns, use 
CACHE SELECT col1, col2...
instead of 
*
. This saves disk cache space and speeds up the warmup. * Where Clause: Always use a 
WHERE
filter if possible to avoid warming up historical data that is rarely queried. * Resource Groups: You can restrict the CPU/memory used by the warmup task so it doesn't impact your production queries by adding properties:
Copy code
sql
    SUBMIT TASK warmup_task
    PROPERTIES ("resource_group" = "low_priority_group")
    AS CACHE SELECT ...
* Persistence: In StarRocks 4.1 shared-data, the cache is managed by an LRU (Least Recently Used) policy. If your cache disk is full, the warmed-up data may be evicted. Ensure your BE configuration 
starlet_star_cache_disk_size_percent
is set high enough (default is 80%). References • data_source/block_cache_warmup.md • deployment/shared_data/feature-support-shared-data.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md References • data_source/block_cache_warmup.md • deployment/shared_data/feature-support-shared-data.md • administration/management/BE_configuration.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • quick_start/shared-data.md
k
@Rocky is a shared nothing db materialized view based on an external iceberg table the same exact storage format and performance of a primary key table
r
In StarRocks v4.1 (and v3.3+), a Materialized View with a Primary Key (
CREATE MATERIALIZED VIEW ... PRIMARY KEY
) and a native Primary Key table share the exact same storage engine and storage format. Here is the breakdown of how they compare in a Shared-Nothing architecture: 1. Storage Engine Equivalence * Identical Internal Table: When you define a 
PRIMARY KEY
in your 
CREATE MATERIALIZED VIEW
statement, StarRocks creates an internal table using the Primary Key Engine. * Delete+Insert Strategy: Both use the same "Delete+Insert" mechanism. They utilize DelVectors (to mark old rows as deleted) and a Primary Key Index (to track the location of the latest row). This is what enables the sub-75 ms performance for point lookups and targeted queries. * Persistent Index: In a Shared-Nothing cluster, both will store their primary key indexes on the local BE disks (controlled by the 
enable_persistent_index
property). 2. Performance Comparison Since the underlying data structure is the same, the query performance is identical. A query against the MV and a query against a native PK table containing the same data will behave the same way: * No Merge-on-Read: Both avoid the overhead of merging multiple versions of data during a query (unlike Unique or Aggregate tables). * Predicate Pushdown: Both support full predicate and index pushdown to the scan layer. 3. The Only Real Difference: Ingestion vs. Refresh The primary difference is not in how they store or query data, but in how data _arrives_: * Native PK Table: You manually push data into it via 
STREAM LOAD
, 
ROUTINE LOAD
, or 
INSERT INTO
. You are responsible for the ETL logic. * Primary Key MV: StarRocks pulls data from the source (Iceberg) automatically based on your 
REFRESH
schedule. It internally executes an 
INSERT INTO ... SELECT
which triggers the Primary Key upsert logic. Comparison Table Feature | Native Primary Key Table | PK Materialized View (v3.3+) Storage Engine | Primary Key Engine | Primary Key Engine Storage Format | Segments + DelVectors | Segments + DelVectors Query Latency | Sub-millisecond possible | Sub-millisecond possible Index Type | In-memory or Persistent | In-memory or Persistent Update Method | Push (Load/Insert) | Pull (Scheduled Refresh) Shared-Nothing | Local BE Storage | Local BE Storage Why this matters for your 75ms goal If you were using a "standard" Materialized View (without the 
PRIMARY KEY
clause), StarRocks would use the Duplicate Key engine. While fast, it doesn't have the same specialized indexing for primary-key lookups. By using the 
PRIMARY KEY
syntax in your MV, you are effectively getting a "self-managing" Primary Key table that stays in sync with Iceberg, giving you the best possible performance available in StarRocks. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md
k
@Rocky where is the exact docs saying 4.1 supports primary key materialized views? your example commands dont work and i dont see docs for it
r
The confusion arises because the Primary Key Materialized View (introduced in StarRocks v3.3) follows a stricter syntax than standard asynchronous MVs. Specifically, to use the 
PRIMARY KEY
clause, you must explicitly define the column list in parentheses immediately after the MV name. If you omit the column definitions, the parser encounters 
PRIMARY KEY
and treats it as an unexpected input. 1. The Correct Syntax for StarRocks 4.1 To fix the 
Unexpected input 'PRIMARY'
error, structure your command as follows:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_pk_mv
(
    id,          -- You must list all columns here
    user_name,
    event_date
)
PRIMARY KEY (id) -- Now this is recognized
PARTITION BY event_date
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE"
)
AS 
SELECT 
    id, 
    user_name, 
    event_date
FROM iceberg_catalog.db.external_table;
2. Why is this not in the main docs? The CREATE MATERIALIZED VIEW reference page primarily focuses on the "Duplicate Key" engine MVs (the most common type). The Primary Key MV is a specialized feature. Its behavior and storage are identical to a Primary Key Table. It is designed specifically for scenarios where you need: * Sub-100ms point lookups or small-range queries. * Upsert/Delete support (StarRocks handles this internally during the refresh by using the PK engine's "Delete+Insert" strategy). * Shared-Data efficiency (using the 
CLOUD_NATIVE
persistent index to keep memory usage low). 3. Verification in 4.1 You can verify that your MV is indeed using the Primary Key engine after creation by running:
Copy code
sql
SHOW CREATE TABLE iceberg_pk_mv;
You will see 
ENGINE=OLAP
and 
PRIMARY KEY (id)
in the output, confirming it has the same storage format and performance characteristics as a native PK table. 4. Important Tip for External Tables Since you are pulling from Iceberg, ensure the column you use for 
PARTITION BY
in the MV matches a partition column in the source Iceberg table. This allows StarRocks to perform Incremental Refresh, which is much faster than a full table scan. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
k
@Rocky this syntax is not working: CREATE MATERIALIZED VIEW default_catalog.starrocks.test_pk_mv ( channel_id, -- You must list all columns here api_title ) PRIMARY KEY (channel_id) -- Now this is recognized PARTITION BY processed_at DISTRIBUTED BY HASH(channel_id) REFRESH ASYNC EVERY (INTERVAL 1 HOUR) PROPERTIES ( "enable_persistent_index" = "true", "persistent_index_type" = "CLOUD_NATIVE" ) AS SELECT channel_id, processed_at FROM curated_channel;
r
The error 
Unexpected input 'PRIMARY'
occurs because the Primary Key clause is not supported within the standard 
CREATE MATERIALIZED VIEW
statement in the open-source version of StarRocks 4.1. In StarRocks, asynchronous Materialized Views (MVs) are built using the Duplicate Key storage engine. While they are highly performant for analytical queries, they do not support the 
PRIMARY KEY
engine's unique index and "Delete+Insert" logic directly. To achieve your goal of sub-75 ms performance (which typically requires a Primary Key table for point lookups or specific column filters) while pulling from Iceberg, use the following pattern: The Solution: Primary Key Table + Scheduled Task This approach gives you the exact performance and storage format of a Primary Key table while automating the data ingestion from Iceberg. 1. Create a native Primary Key Table This table will hold your data and provide the sub-75 ms performance you need.
Copy code
sql
CREATE TABLE default_catalog.starrocks.test_pk_table (
    channel_id BIGINT NOT NULL,
    api_title STRING,
    processed_at DATETIME
) 
PRIMARY KEY (channel_id)
PARTITION BY processed_at
DISTRIBUTED BY HASH(channel_id)
PROPERTIES (
    "enable_persistent_index" = "true",
    "persistent_index_type" = "CLOUD_NATIVE" -- Best for Shared-Data clusters
);
2. Create a Scheduled Task to Refresh the Table Use 
SUBMIT TASK
to automatically pull data from Iceberg into your PK table. This effectively mimics a Materialized View.
Copy code
sql
SUBMIT TASK refresh_test_pk_task
SCHEDULE EVERY (INTERVAL 1 HOUR)
AS
INSERT INTO default_catalog.starrocks.test_pk_table
SELECT
    channel_id,
    api_title,
    processed_at
FROM iceberg_catalog.db.curated_channel
-- Tip: Use a filter to only refresh new data if possible
WHERE processed_at >= CURRENT_DATE - INTERVAL 1 DAY;
Why your previous syntax failed: 1. Unsupported Keyword: The open-source parser does not recognize 
PRIMARY KEY
in the MV creation syntax. 2. Column List Mismatch: Even if the syntax were supported, your header defined 
(channel_id, api_title)
but your 
SELECT
statement only provided 
channel_id
and 
processed_at
. In StarRocks, if you provide a column list after the MV name, it must match the columns in the 
SELECT
statement exactly. If you prefer to stay with a Materialized View: If you do not strictly need the Primary Key engine (e.g., if you don't need upserts/deletes and only care about fast scans), you can use a standard MV and optimize it with a sort key:
Copy code
sql
CREATE MATERIALIZED VIEW default_catalog.starrocks.test_mv
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PARTITION BY processed_at
DISTRIBUTED BY HASH(channel_id)
ORDER BY (channel_id) -- This creates a sort key for fast lookups
AS
SELECT
    channel_id,
    api_title,
    processed_at
FROM iceberg_catalog.db.curated_channel;
References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • table_design/table_types/primary_key_table.md • using_starrocks/async_mv/Materialized_view.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
2 Views
Open in Slack
PreviousNext
Powered by