http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> start time: Sun Jun 14 09:35:25 AM ...
# questions-and-troubleshooting
l
@Rocky start time: Sun Jun 14 093525 AM +07 2026, server uptime: 093525 up 183 days, 21:48, 6 users, load average: 0.40, 0.08, 0.03 Run with JEMALLOC_CONF: 'percpu_arenapercpu,oversize threshold0,muzzy_decay_ms5000,dirty decay ms5000,metadata_thpauto,background threadtrue,proftrue,prof activefalse' 4.0.10 RELEASE (build 5b36164 distro centos arch x86_64) query_id:019f2208-2bfe-77c2-91df-73ca361abefd, fragment_instance:019f2208-2bfe-77c2-91df-73ca361abf03, plan_node_id:0 * Aborted at 1782982390 (unix time) try "date -d @1782982390" if you are using GNU date * PC: @ 0x8ef0a5b starrocks::BinaryColumnBase<unsigned int>::append(starrocks::Column const&, unsigned long, unsigned long) * SIGSEGV (@0x0) received by PID 1088387 (TID 0x154cef1ab640) LWP(1088744) from PID 0; stack trace: * @ 0x154d89c8f218 __pthread_once_slow @ 0xdee2e94 google::(anonymous namespace)::FailureSignalHandler(int, siginfo_t*, void*) @ 0x154d8b2c76ce PosixSignals::chained_handler(int, siginfo_t*, void*) [clone .part.0] @ 0x154d8b2c81c6 JVM_handle_linux_signal @ 0x154d89c3ebf0 (/usr/lib64/libc.so.6+0x3ebef) @ 0x8ef0a5b starrocks::BinaryColumnBase<unsigned int>::append(starrocks::Column const&, unsigned long, unsigned long) @ 0x8e57dd9 starrocks:Chunk:append(starrocks::Chunk const&, unsigned long, unsigned long) @ 0x778cb38 starrocks:ChunkPipelineAccumulator:push(std:shared ptr&lt;starrocks:Chunk> const&) @ 0xa28877b starrocks:pipelineChunkAccumulateOperator:push_chunk(starrocks::RuntimeState*, std:shared ptr&lt;starrocks:Chunk> const&) @ 0xa1f61ba starrocks:pipelinePipelineDriver:process(starrocks::RuntimeState*, int) @ 0xa71d3cd starrocks:pipelineGlobalDriverExecutor:_worker_thread() @ 0x8673f77 starrocks:ThreadPool:dispatch_thread() @ 0x866ac70 starrocks:🧵:supervise_thread(void*) @ 0x154d89c8a002 start_thread @ 0x154d89d0f070 __clone3 [1782982390.207][thread: 23420173203008] je_mallctl execute purge success [1782982390.207][thread: 23420173203008] je_mallctl execute dontdump success
r
Hi, I'm Rocky. Based on your stack trace and version info, this is a known issue. Issue Summary The 
SIGSEGV
in 
starrocks::BinaryColumnBase<unsigned int>::append
typically occurs when the system attempts to append data to a column that has an uninitialized or empty internal byte buffer (
_bytes
). This is often triggered during pipeline processing when using operators like 
ChunkAccumulateOperator
(seen in your stack trace), which are common in materialized view refreshes, window functions, or complex joins. Analysis * Version: You are running 4.0.10 (Build 
5b36164
), which was released around May 9, 2026. * Known Bug: A specific fix for a BE crash in 
BinaryColumnBase::append
when 
_bytes
is empty was documented in mid-May 2026. This fix was targeted for the 4.0.11 patch release. * Timeline: Your crash occurred on June 14, 2026. StarRocks 4.0.11 was released on June 5, 2026, specifically to address several pipeline-related crashes and nested type append failures. Recommendation Upgrade to StarRocks 4.0.11 or a later patch version. The release notes for 4.0.11 and subsequent patches include critical stability fixes for the pipeline engine and column appending logic that match the behavior in your logs. If you cannot upgrade immediately, you can try to identify if the query involves specific complex types (like JSON or nested Arrays) or window functions, as these are the most common triggers for this specific code path. However, a version upgrade is the only definitive fix for this segmentation fault. For more details, you can refer to the StarRocks v4.0 Release Notes.
l
@Rocky SET GLOBAL enable_spill = false; how this properties efftect starrocks cluster
r
In StarRocks, 
SET GLOBAL enable_spill = false;
(which is the default setting) disables the "Spill to Disk" feature cluster-wide for all new sessions. Since your previous log showed a segmentation fault (crash) rather than an Out-of-Memory (OOM) error, changing this setting likely won't fix that specific crash (which I identified as a bug fixed in v4.0.11). Here is how this setting affects your cluster: 1. Risk of Out-of-Memory (OOM) When 
enable_spill
is 
false
, StarRocks performs all computations (joins, sorts, aggregations) entirely in memory. * Effect: If a query requires more memory than the 
mem_limit
(session) or the BE's available memory, StarRocks will terminate the query with an OOM error rather than trying to save it by using disk space. * Impact: Large ETL tasks or complex queries on massive datasets are more likely to fail if they exceed physical RAM. 2. Performance and Latency * Effect: Queries that fit in memory will run at maximum speed. * Comparison: Spilling to disk can cause a 10x or greater increase in latency because disk I/O is much slower than RAM. By keeping this 
false
, you ensure that queries either run fast or fail quickly, preventing a single "heavy" query from grinding your disk I/O to a halt and slowing down the rest of the cluster. 3. Resource Predictability * Effect: It prevents "noisy neighbor" issues where one massive spill-to-disk task consumes all disk IOPS, affecting the performance of standard storage read/write operations for other users. 4. Affected Operators This setting specifically impacts memory-intensive operators: * Aggregations (e.g., 
GROUP BY
on high-cardinality columns) * Sorts (e.g., 
ORDER BY
on large result sets) * Joins (specifically Hash Joins) * CTEs When should you change it? You should only set 
enable_spill = true
if: * You have long-running background tasks (like building a large Materialized View) that must complete, and you are willing to accept very slow performance to avoid OOM failures. * You have high-performance local disks (NVMe SSDs) dedicated to spilling (
spill_local_storage_dir
). Summary: For most real-time analytics use cases, keeping 
enable_spill = false
is recommended to maintain low latency and predictable performance. If your queries are failing with OOM, it is usually better to optimize the query or increase memory before enabling spilling. References • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md • knowledge/usage/best-practice.md
l
@Rocky SELECT COUNT(1) AS cnt FROM dwh_link.dwh_customer_ld_summary_upd a JOIN dmapp.dtm_customer_ld_summary b ON a.sys_run_date = b.sys_run_date AND a.ld_no = b.ld_no WHERE a.sys_run_date = '2026-04-12' and a.ld_no='0000000209' AND (a.max_dpd_history <> b.max_dpd_history) why starrock not compare null and not null value
r
In StarRocks (which follows standard SQL logic), 
NULL
is not treated as a specific value, but rather as an unknown state. Because of this, standard comparison operators like 
=
and 
<>
do not work with 
NULL
as you might expect. Why the comparison fails When you write 
a.max_dpd_history <> b.max_dpd_history
, SQL evaluates the result using Three-Valued Logic (True, False, or Unknown): * Case 1: Both are numbers (e.g., 
1 <> 2
). Result: 
TRUE
. * Case 2: Both are the same (e.g., 
1 <> 1
). Result: 
FALSE
. * Case 3: One or both are 
NULL
(e.g., 
1 <> NULL
). Result: 
NULL
(Unknown). In a 
WHERE
clause, only rows that evaluate strictly to 
TRUE
are returned. Since 
NULL
is not 
TRUE
, the row is filtered out even though the values are "different" to a human. ────────── How to fix it (3 ways) 1. Use the Null-Safe Equality Operator (
<=>
) The 
<=>
operator (common in MySQL and supported by StarRocks) performs an equality check that handles 
NULL
correctly. It returns 
1
(true) if both are the same (even if both are 
NULL
) and 
0
(false) if they are different. To find differences, use `NOT`:
Copy code
sql
AND NOT (a.max_dpd_history <=> b.max_dpd_history)
2. Use 
IFNULL
or 
COALESCE
 Replace 
NULL
with a default value that doesn't exist in your data (like 
-1
or a very large number) so the 
<>
operator can compare them:
Copy code
sql
AND IFNULL(a.max_dpd_history, -999) <> IFNULL(b.max_dpd_history, -999)
3. Use Explicit 
IS NULL
logic This is the most "standard" SQL way but makes the query longer:
Copy code
sql
AND (
    a.max_dpd_history <> b.max_dpd_history 
    OR (a.max_dpd_history IS NULL AND b.max_dpd_history IS NOT NULL)
    OR (a.max_dpd_history IS NOT NULL AND b.max_dpd_history IS NULL)
)
Recommendation For your specific query, Option 1 (
NOT <=>
) is the cleanest and most efficient way to ensure StarRocks counts rows where one value is a number and the other is 
NULL
. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • release_notes/release-4.0.md • data_source/feature-support-data-lake-analytics.md • release_notes/release-3.2.md • sql-reference/sql-functions/json-functions/overview-of-json-functions-and-operators.md • sql-reference/sql-functions/condition-functions/nullif.md • release_notes/release-3.1.md
l
@Rocky UPDATE dmapp.dtm_customer_ld_summary SET max_dpd_history = a.max_dpd_history FROM dwh_link.dwh_customer_ld_summary_upd AS a WHERE a.sys_run_date = dmapp.dtm_customer_ld_summary.sys_run_date AND a.ld_no = dmapp.dtm_customer_ld_summary.ld_no AND a.sys_run_date = %s AND IFNULL(a.max_dpd_history, 0) <> IFNULL(dmapp.dtm_customer_ld_summary.max_dpd_history, 0) this sql correct
r
Yes, the logic and syntax of your SQL statement are correct for StarRocks, provided that your target table uses the Primary Key storage model. Here is a breakdown of why this works and one important requirement: 1. Correct Syntax for StarRocks 3.0+ Your query uses the Multi-table UPDATE syntax (
UPDATE ... FROM ... WHERE
), which was introduced in StarRocks v3.0. * The 
FROM
clause: Correctly references the source table (
dwh_link.dwh_customer_ld_summary_upd
). * The 
SET
clause: Correctly assigns the value from the source table (
a.max_dpd_history
) to the target column. * The Join logic: The 
WHERE
clause correctly joins the tables on 
sys_run_date
and 
ld_no
. 2. Handling NULLs correctly The addition of 
IFNULL(..., 0) <> IFNULL(..., 0)
is a good way to ensure that: * If a value was 
NULL
and is now 
5
, it will be updated. * If a value was 
5
and is now 
NULL
, it will be updated. * If both are 
NULL
, it will not trigger an unnecessary update (since 
0 <> 0
is false). _(Note: Ensure 
0
is not a valid data value for 
max_dpd_history
. If 
0
is a valid value, use a unique placeholder like 
-9999
)._ 3. Critical Requirement: Primary Key Table In StarRocks, the 
UPDATE
statement is only supported for Primary Key tables. * If 
dmapp.dtm_customer_ld_summary
was created as a 
DUPLICATE KEY
or 
UNIQUE KEY
table, this statement will return an error: 
Only Primary Key table support update
. * If it is not a Primary Key table, you must use an 
INSERT INTO ... SELECT
(Upsert) instead. 4. Minor Optimization (Alias) To make the query cleaner, you can alias the target table as well:
Copy code
sql
UPDATE dmapp.dtm_customer_ld_summary AS t
SET max_dpd_history = a.max_dpd_history
FROM dwh_link.dwh_customer_ld_summary_upd AS a
WHERE a.sys_run_date = t.sys_run_date
  AND a.ld_no = t.ld_no
  AND a.sys_run_date = %s
  AND NOT (a.max_dpd_history <=> t.max_dpd_history)
_Tip: Using 
NOT (a.max_dpd_history <=> t.max_dpd_history)
is the "Null-safe" way to write your 
IFNULL
logic without needing to pick a dummy value like 
0
._ Summary If 
dtm_customer_ld_summary
is a Primary Key table, your SQL is correct and ready to run. If it is any other table type, you should use 
INSERT INTO ... SELECT
to` perform the update. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • faq/Others.md • loading/InsertInto.md
l
@Rocky -- DMAPP.DTM_CSS_RAW_DATA_FLUP definition CREATE TABLE "DMAPP"."DTM_CSS_RAW_DATA_FLUP" ( "SYS_RUN_DATE" DATE, "LEAD_ID" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "PIC" VARCHAR2(100) COLLATE "USING_NLS_COMP", "PRODUCT" VARCHAR2(255) COLLATE "USING_NLS_COMP", "PRODUCT_CODE" VARCHAR2(255) COLLATE "USING_NLS_COMP", "DROP_OFF_STAGE" VARCHAR2(100) COLLATE "USING_NLS_COMP", "LEAD_ASSIGN_DATE" TIMESTAMP (6) NOT NULL ENABLE, "APP_CODE" VARCHAR2(255) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "APPROVAL_DATE" TIMESTAMP (6), "FIRST_CALL_DATE" TIMESTAMP (6), "FIRST_CALL_VALID" VARCHAR2(20) COLLATE "USING_NLS_COMP", "FIRST_CONNECTED_CALL_DATE" TIMESTAMP (6), "FIRST_CONNECTED_CALL_NO" NUMBER, "FIRST_CONNECTED_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "FIRST_CONNECTED_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "3RD_LATEST_CALL_DATE" TIMESTAMP (6), "3RD_LATEST_CONNECT_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "3RD_LATEST_CONNECT_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "3RD_LATEST_CONSULT_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "3RD_LATEST_CONSULT_DETAIL" VARCHAR2(500) COLLATE "USING_NLS_COMP", "3RD_LATEST_CONSULT_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "3RD_LATEST_DISB_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "2ND_LATEST_CALL_DATE" TIMESTAMP (6), "2ND_LATEST_CONNECT_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "2ND_LATEST_CONNECT_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "2ND_LATEST_CONSULT_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "2ND_LATEST_CONSULT_DETAIL" VARCHAR2(500) COLLATE "USING_NLS_COMP", "2ND_LATEST_CONSULT_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "2ND_LATEST_DISB_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "LATEST_CALL_DATE" TIMESTAMP (6), "LATEST_CONNECT_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "LATEST_CONNECT_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LATEST_CONSULT_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "LATEST_CONSULT_DETAIL" VARCHAR2(500) COLLATE "USING_NLS_COMP", "LATEST_CONSULT_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "LATEST_DISB_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "IS_COMPLETED" VARCHAR2(50) COLLATE "USING_NLS_COMP", "DISBURSEMENT_DATE" TIMESTAMP (6), "DISBURSEMENT_AMT" NUMBER, "TOTAL_CALL_NUM" NUMBER, "DISBURSE_CALL_NO" NUMBER, "FINAL_CONNECT_DATE" TIMESTAMP (6), "FINAL_CONNECT_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "FINAL_CONNECT_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "FINAL_CONSULT_RESULT" VARCHAR2(500) COLLATE "USING_NLS_COMP", "FINAL_CONSULT_DETAIL" VARCHAR2(500) COLLATE "USING_NLS_COMP", "FINAL_CONSULT_STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "FINAL_DISB_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP" ) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_DATA_16K" ; to starrock dupplicate table
r
To convert your Oracle-style DDL to a StarRocks Duplicate Key table, you need to map the data types (e.g., 
VARCHAR2
to 
VARCHAR
, 
NUMBER
to 
DECIMAL
or 
INT
) and define the StarRocks-specific distribution properties. In a Duplicate Key table, the columns defined in the key are used for sorting, which greatly speeds up queries filtering by those columns. StarRocks DDL (Duplicate Key)
Copy code
sql
CREATE TABLE dmapp.dtm_css_raw_data_flup (
    sys_run_date DATE,
    lead_id VARCHAR(50) NOT NULL,
    app_code VARCHAR(255) NOT NULL,
    lead_assign_date DATETIME NOT NULL,
    pic VARCHAR(100),
    product VARCHAR(255),
    product_code VARCHAR(255),
    drop_off_stage VARCHAR(100),
    approval_date DATETIME,
    first_call_date DATETIME,
    first_call_valid VARCHAR(20),
    first_connected_call_date DATETIME,
    first_connected_call_no INT,
    first_connected_result VARCHAR(500),
    first_connected_status VARCHAR(255),
    3rd_latest_call_date DATETIME,
    3rd_latest_connect_result VARCHAR(500),
    3rd_latest_connect_status VARCHAR(255),
    3rd_latest_consult_result VARCHAR(500),
    3rd_latest_consult_detail VARCHAR(500),
    3rd_latest_consult_status VARCHAR(255),
    3rd_latest_disb_status VARCHAR(50),
    2nd_latest_call_date DATETIME,
    2nd_latest_connect_result VARCHAR(500),
    2nd_latest_connect_status VARCHAR(255),
    2nd_latest_consult_result VARCHAR(500),
    2nd_latest_consult_detail VARCHAR(500),
    2nd_latest_consult_status VARCHAR(255),
    2nd_latest_disb_status VARCHAR(50),
    latest_call_date DATETIME,
    latest_connect_result VARCHAR(500),
    latest_connect_status VARCHAR(255),
    latest_consult_result VARCHAR(500),
    latest_consult_detail VARCHAR(500),
    latest_consult_status VARCHAR(255),
    latest_disb_status VARCHAR(50),
    is_completed VARCHAR(50),
    disbursement_date DATETIME,
    disbursement_amt DECIMAL(38, 2),
    total_call_num INT,
    disburse_call_no INT,
    final_connect_date DATETIME,
    final_connect_result VARCHAR(500),
    final_connect_status VARCHAR(255),
    final_consult_result VARCHAR(500),
    final_consult_detail VARCHAR(500),
    final_consult_status VARCHAR(255),
    final_disb_status VARCHAR(50)
) 
ENGINE=OLAP
DUPLICATE KEY(sys_run_date, lead_id, app_code)
PARTITION BY RANGE(sys_run_date) (
    START ("2024-01-01") END ("2026-01-01") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(lead_id) BUCKETS 16;
Key Changes Explained: 1. Data Type Mapping: * 
VARCHAR2
$\rightarrow$ 
VARCHAR
. * 
TIMESTAMP(6)
$\rightarrow$ 
DATETIME
. * 
NUMBER
(for IDs/Counts) $\rightarrow$ 
INT
or 
BIGINT
. * 
NUMBER
(for Amounts) $\rightarrow$ 
DECIMAL(38, 2)
. 2. Duplicate Key: I placed 
sys_run_date
, 
lead_id
, and 
app_code
in the 
DUPLICATE KEY
clause. StarRocks will store the data sorted by these columns, making filters on them extremely fast. 3. Partitioning: Added 
PARTITION BY RANGE(sys_run_date)
. This is highly recommended for large tables in StarRocks to allow for partition pruning and easier data lifecycle management (dropping old days). 4. Distribution: Added 
DISTRIBUTED BY HASH(lead_id)
. This distributes the data across the BE nodes. I chose 
lead_id
as the distribution key to ensure data for the same lead is co-located. 5. Removed Oracle-specific syntax: Clauses like 
PCTFREE
, 
TABLESPACE
, 
STORAGE
, and 
COLLATE
are not used in StarRocks and were removed. Important Note on Updating If you plan to use the 
UPDATE
statement you mentioned in your previous message on this table, Duplicate Key tables do not support the 
UPDATE
statement. * If you need to update rows frequently, change 
DUPLICATE KEY
to 
PRIMARY KEY
. * If you only need to insert new data or overwrite whole partitions, keep it as 
DUPLICATE KEY
.
l
@Rocky -- DMAPP.DTM_MB_EKYC_DATA definition CREATE TABLE "DMAPP"."DTM_MB_EKYC_DATA" ( "SYS_RUN_DATE" DATE, "ID" NUMBER, "BIRTH_OF_DATE" VARCHAR2(255) COLLATE "USING_NLS_COMP", "CREATED_BY" VARCHAR2(255) COLLATE "USING_NLS_COMP", "CREATED_DATE" DATE, "DEVICE_ID" VARCHAR2(255) COLLATE "USING_NLS_COMP", "DOMICILE_ADDESS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "EKYC_DATA" CLOB COLLATE "USING_NLS_COMP", "ID_CARD_NUMBER" VARCHAR2(255) COLLATE "USING_NLS_COMP", "ISSUE_DATE" DATE, "ISSUE_PLACE" VARCHAR2(255) COLLATE "USING_NLS_COMP", "MOBILE_PHONE" VARCHAR2(255) COLLATE "USING_NLS_COMP", "PERMANENT_ADDRESS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "CONTRACT_NUMBER" VARCHAR2(255) COLLATE "USING_NLS_COMP", "STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP" ) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_DATA_16K" LOB ("EKYC_DATA") STORE AS SECUREFILE ( TABLESPACE "DM_DATA_16K" ENABLE STORAGE IN ROW CHUNK 16384 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 212992 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ; to starrock table
r
To convert your Oracle 
DTM_MB_EKYC_DATA
table to a StarRocks Duplicate Key table, you need to map Oracle's data types and storage settings to StarRocks' distributed architecture. StarRocks DDL (Duplicate Key)
Copy code
sql
CREATE TABLE dmapp.dtm_mb_ekyc_data (
    sys_run_date DATE,
    id BIGINT,
    created_date DATETIME,
    id_card_number VARCHAR(255),
    birth_of_date VARCHAR(255),
    created_by VARCHAR(255),
    device_id VARCHAR(255),
    domicile_addess VARCHAR(255),
    ekyc_data JSON, -- Use JSON if the CLOB contains JSON data, otherwise use STRING
    issue_date DATETIME,
    issue_place VARCHAR(255),
    mobile_phone VARCHAR(255),
    permanent_address VARCHAR(255),
    contract_number VARCHAR(255),
    status VARCHAR(255)
) 
ENGINE=OLAP
DUPLICATE KEY(sys_run_date, id)
PARTITION BY RANGE(sys_run_date) (
    START ("2024-01-01") END ("2026-01-01") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(id) BUCKETS 8;
Key Mapping & Design Decisions: 1. Data Type Mapping: * 
NUMBER
$\rightarrow$ 
BIGINT
: Used for the 
ID
column. If your IDs can have decimals, use 
DECIMAL(38,2)
. * 
DATE
(Oracle) $\rightarrow$ 
DATETIME
: In Oracle, 
DATE
includes time (HHMMSS). StarRocks' 
DATE
type is only YYYY-MM-DD, so I used 
DATETIME
for 
created_date
and 
issue_date
to preserve the time information. I kept 
sys_run_date
as 
DATE
assuming it's used for daily partitioning. * 
CLOB
$\rightarrow$ 
JSON
(or 
STRING
): * If 
EKYC_DATA
contains JSON objects, the 
JSON
 type is much faster for querying specific fields. * If it is just plain long text, use 
STRING
. 2. Duplicate Key: * Defined as 
(sys_run_date, id)
. Data will be physically stored and sorted by these columns, which significantly speeds up queries that filter by date or ID. 3. Partitioning: * Added 
PARTITION BY RANGE(sys_run_date)
. This is a best practice in StarRocks for managing daily data loads and allowing the engine to skip old data during queries. 4. Distribution: * 
DISTRIBUTED BY HASH(id)
. This spreads the data across your cluster nodes based on the ID. 
8
buckets is a safe starting point for small-to-medium tables; for very large tables, you might increase this. 5. Clean Up: * Removed Oracle-specific storage parameters like 
PCTFREE
, 
TABLESPACE
, 
SECUREFILE
, and 
LOB
settings, as StarRocks manages storage automatically (and more efficiently) via its columnar engine. Note on Updates If you plan to use 
UPDATE
statements on this table (like in your previous questions), remember that Duplicate Key tables do not support the 
UPDATE
command. If you need updates, you must change the table type to 
PRIMARY KEY
:
Copy code
sql
-- Change this line if you need to use UPDATE statements
PRIMARY KEY(sys_run_date, id)
References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/data-types/semi_structured/JSON.md • table_design/Data_distribution.md • sql-reference/data-types/numeric-types/DECIMAL.md • administration/Management.md • table_design/StarRocks_table_design.md • sql-reference/data-types/README.md • table_design/table_types/primary_key_table.md References • data_source/External_table.md
l
@Rocky need to dynamic partition, as data from 2021 and auto inscrease day by day
r
For StarRocks 3.0 and newer, the recommended way to handle your requirement is Expression Partitioning (also known as Automatic Partitioning). This is much simpler than the legacy "Dynamic Partitioning" because you don't need to configure background properties or pre-create partitions. StarRocks will automatically create a new partition whenever data for a new day is loaded, whether that data is from 2021 or next year. Recommended DDL (StarRocks 3.0+)
Copy code
sql
CREATE TABLE dmapp.dtm_mb_ekyc_data (
    sys_run_date DATE NOT NULL,  -- Partition column must be NOT NULL
    id BIGINT,
    birth_of_date VARCHAR(255),
    created_by VARCHAR(255),
    created_date DATETIME,
    device_id VARCHAR(255),
    domicile_addess VARCHAR(255),
    ekyc_data JSON,
    id_card_number VARCHAR(255),
    issue_date DATETIME,
    issue_place VARCHAR(255),
    mobile_phone VARCHAR(255),
    permanent_address VARCHAR(255),
    contract_number VARCHAR(255),
    status VARCHAR(255)
) 
ENGINE=OLAP
DUPLICATE KEY(sys_run_date, id)
-- Automatically creates daily partitions based on the data loaded
PARTITION BY date_trunc('day', sys_run_date)
DISTRIBUTED BY HASH(id) BUCKETS 8;
Why this fits your needs: 1. Data from 2021: As soon as you load your historical 2021 data, StarRocks will automatically create the required historical partitions. You don't need to manually define them. 2. Auto-increase: When your scheduled job loads tomorrow's data, StarRocks will automatically create the partition for tomorrow. 3. No Maintenance: You don't need to manage a "future buffer" (e.g., creating 3 days in advance) like you did in older versions. ────────── Legacy Approach (For versions < 3.0) If you are on an older version of StarRocks, you must use the Dynamic Partitioning properties. This requires you to define at least one starting partition manually.
Copy code
sql
CREATE TABLE dmapp.dtm_mb_ekyc_data (
    sys_run_date DATE NOT NULL,
    id BIGINT,
    -- ... other columns ...
) 
ENGINE=OLAP
DUPLICATE KEY(sys_run_date, id)
-- You must define at least one manual partition for the starting point
PARTITION BY RANGE(sys_run_date) (
    PARTITION p20210101 VALUES LESS THAN ("2021-01-02")
)
DISTRIBUTED BY HASH(id) BUCKETS 8
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.end" = "3",          -- Creates 3 days into the future
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.history_partition_num" = "2000" -- Set high enough to cover 2021 to now
);
Summary of Differences * Expression Partitioning (3.0+): Highly recommended. "Just works" based on incoming data. * Dynamic Partitioning (Legacy): Managed by a background thread. Requires setting 
history_partition_num
to create past partitions and 
end
to create future ones in advance. References • table_design/data_distribution/expression_partitioning.md • table_design/data_distribution/dynamic_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • table_design/data_distribution/expression_partitioning.md
l
@Rocky great, how to PARTITION BY date_trunc('day', sys_run_date) but by month
r
To partition your table by month using expression partitioning, you simply change the first argument of the 
date_trunc
function from 
'day'
to 
'month'
. Updated DDL (Monthly Partitioning)
Copy code
sql
CREATE TABLE dmapp.dtm_mb_ekyc_data (
    sys_run_date DATE NOT NULL,  -- Must be NOT NULL for expression partitioning
    id BIGINT,
    birth_of_date VARCHAR(255),
    created_by VARCHAR(255),
    created_date DATETIME,
    device_id VARCHAR(255),
    domicile_addess VARCHAR(255),
    ekyc_data JSON,
    id_card_number VARCHAR(255),
    issue_date DATETIME,
    issue_place VARCHAR(255),
    mobile_phone VARCHAR(255),
    permanent_address VARCHAR(255),
    contract_number VARCHAR(255),
    status VARCHAR(255)
) 
ENGINE=OLAP
DUPLICATE KEY(sys_run_date, id)
-- Automatically creates 1 partition per month based on the data loaded
PARTITION BY date_trunc('month', sys_run_date)
DISTRIBUTED BY HASH(id) BUCKETS 8;
How this handles your requirements: 1. Historical Data (2021): When you load data from 
2021-01-15
, StarRocks will automatically create a partition named 
p202101
covering the entire month of January 2021. 2. Auto-Increase: As you move into new months (e.g., February, March), StarRocks detects the new month in the incoming data and creates the corresponding monthly partition automatically. 3. Performance: Monthly partitioning is often better than daily partitioning if you have years of data, as it prevents "metadata explosion" (having too many small partitions), while still allowing the engine to skip months you aren't querying. Pro-Tip: Partition Retention If you want to automatically delete data older than a certain number of months, you can add this property to the DDL:
Copy code
sql
PROPERTIES (
    "partition_live_number" = "36" -- Keeps only the last 36 monthly partitions
)
References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-functions/date-time-functions/date_trunc.md References • table_design/data_distribution/expression_partitioning.md
l
@Rocky i have a table in starrock, and have kafka to stream_load data to that table, does MATERIALIZED VIEW auto update when new data come?
r
Yes, StarRocks Materialized Views (MV) can auto-update when new data arrives via Kafka (Routine Load or Stream Load), but the behavior depends on the type of Materialized View you are using. 1. Synchronous Materialized View (Rollup) If you created a simple MV (often called a "Rollup") using the older syntax 
CREATE MATERIALIZED VIEW mv_name AS SELECT ...
, it updates synchronously. * Behavior: The MV is updated in the same transaction as your Kafka load. When the Kafka batch is committed to the base table, it is guaranteed to be in the MV at the exact same time. * Limitation: It only supports a single table and a limited set of aggregate functions (like 
SUM
, 
MIN
, 
MAX
, 
COUNT
). It does not support joins. 2. Asynchronous Materialized View (StarRocks 2.4+) If you are using the more powerful Async MV (
CREATE MATERIALIZED VIEW
with a 
REFRESH
clause), it supports joins and complex logic, but updates work differently. * Automatic Trigger (
REFRESH ASYNC
): If you define the MV with 
REFRESH ASYNC
, StarRocks will automatically trigger a background refresh task every time data is committed to the base table (including Kafka loads). * Scheduled Trigger (
REFRESH ASYNC EVERY (INTERVAL ...)
): If your Kafka stream is very high-frequency (e.g., loading every few seconds), a refresh task triggering on every commit might consume too many resources. In this case, it is better to use a scheduled interval, like:
Copy code
sql
    REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
This ensures the MV stays fresh within 60 seconds without overloading the system. 3. Key Optimization: Partitioned Refresh If your base table and MV are both partitioned (e.g., by your 
sys_run_date
), StarRocks is smart enough to perform a Partitioned Refresh. * When Kafka loads data into the "today" partition, StarRocks will only refresh the "today" partition in the MV. * It will not re-calculate your entire history from 2021, making the "auto-update" very fast and efficient. Summary Table Feature | Sync MV (Rollup) | Async MV Auto-update? | Yes (Instant) | Yes (Triggered background task) Multi-table Joins? | No | Yes Data Consistency | Strong (always matches base) | Eventual (refreshes after load) Best For | Simple aggregations | Complex reporting / OBT creation Rocky's Recommendation: If your Kafka stream is constant, use an Asynchronous Materialized View with a Partitioned Refresh and a short interval (e.g., 1-5 minutes). This provides the best balance between data freshness and cluster performance. References • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • quick_start/routine-load.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
l
@Rocky CREATE MATERIALIZED VIEW dbmcman.ep_transaction_loan AS SELECT * FROM dbmcman.ep_transaction WHERE "type" = 'LOAN' AND action_type = 'OUT' AND response_code = '00' AND "action" = 'EPG71'; correct
r
To make this work as a functional, auto-updating view that you can query directly, you need to use the Asynchronous Materialized View syntax. Your current statement is missing two required components: Refresh Strategy and Data Distribution. Corrected Syntax (Recommended) Assuming you want the view to update automatically whenever new data arrives in `ep_transaction`:
Copy code
sql
CREATE MATERIALIZED VIEW dbmcman.ep_transaction_loan 
-- 1. Define how it updates (ASYNC means it triggers when base data changes)
REFRESH ASYNC 
-- 2. Define how data is distributed (usually same as base table's hash key)
DISTRIBUTED BY HASH(id) 
AS 
SELECT * 
FROM dbmcman.ep_transaction 
WHERE `type` = 'LOAN' 
  AND action_type = 'OUT' 
  AND response_code = '00' 
  AND `action` = 'EPG71';
Key Improvements: 1. 
REFRESH ASYNC
: This tells StarRocks to automatically start a background refresh job when the base table receives new data (like from your Kafka stream). 2. 
DISTRIBUTED BY
: Required for Asynchronous MVs. You should use a high-cardinality column (like a primary ID) to ensure data is spread evenly across the cluster. 3. Backticks for Keywords: While double quotes (
"type"
) can work depending on your SQL mode, it is standard in StarRocks to use backticks (`` 
type
``) for reserved keywords. 4. *`SELECT `: This is supported in StarRocks 3.0+. However, if you add columns to your base table later, they won't automatically appear in the MV until you rebuild it. It is usually safer to list the specific columns you need. Pro-Tip: Partitioning for Performance Since your data goes back to 2021, if the base table 
ep_transaction
is partitioned (e.g., by date), you should partition the Materialized View the same way. This allows StarRocks to perform an Incremental Refresh, meaning it will only refresh the specific month/day that just arrived from Kafka, rather than recalculating all data since 2021.
Copy code
sql
CREATE MATERIALIZED VIEW dbmcman.ep_transaction_loan 
PARTITION BY (sys_run_date) -- Matches base table partition column
REFRESH ASYNC 
DISTRIBUTED BY HASH(id) 
AS 
SELECT * FROM dbmcman.ep_transaction ...
References • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md References • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
Open in Slack
PreviousNext
Powered by