http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> -- losrep.application_status defini...
# questions-and-troubleshooting
l
@Rocky -- losrep.application_status definition CREATE TABLE 
application_status
( 
id
decimal(20, 0) NULL COMMENT "", 
app_uid
varchar(1048576) NULL COMMENT "", 
app_number
decimal(11, 0) NULL COMMENT "", 
process_id
varchar(1048576) NULL COMMENT "", 
process_name
varchar(1048576) NULL COMMENT "", 
created_date
datetime NULL COMMENT "", 
last_updated_date
datetime NULL COMMENT "", 
finished_date
datetime NULL COMMENT "", 
created_by
varchar(1048576) NULL COMMENT "", 
last_updated_by
varchar(1048576) NULL COMMENT "", 
step_code
varchar(1048576) NULL COMMENT "", 
current_decision
varchar(1048576) NULL COMMENT "", 
current_decision_lbl
varchar(1048576) NULL COMMENT "", 
current_reason
varchar(1048576) NULL COMMENT "", 
current_reason_lbl
varchar(1048576) NULL COMMENT "", 
current_reason_detail
varchar(1048576) NULL COMMENT "", 
current_reason_detail_lbl
varchar(1048576) NULL COMMENT "", 
current_comment
varchar(1048576) NULL COMMENT "", 
app_code
varchar(1048576) NULL COMMENT "", 
partner_callback_status_code
varchar(1048576) NULL COMMENT "", 
partner_callback_status_des
varchar(1048576) NULL COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(
id
, 
app_uid
) DISTRIBUTED BY RANDOM PROPERTIES ( "bucket_size" = "1073741824", "compression" = "LZ4", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "1" ); user usually query this table using app_code colume, how to speedup this table
r
To speed up queries that filter by the 
app_code
column, you should optimize the physical layout of the data so StarRocks can prune irrelevant data more effectively. Here are the most effective strategies for your table: 1. Change Distribution to Hash Bucketing The current table uses 
DISTRIBUTED BY RANDOM
, which forces StarRocks to scan all buckets for every query. By changing this to 
HASH(app_code)
, StarRocks can use Bucket Pruning to only scan the specific bucket where that 
app_code
exists. * Action: If you are on StarRocks 3.2 or later, you can modify the bucketing key. If not, you will need to recreate the table. * SQL (3.2+):
Copy code
sql
    ALTER TABLE application_status SET DISTRIBUTED BY HASH(app_code);
2. Optimize the Sort Key (Prefix Index) StarRocks stores data in sorted order based on the 
DUPLICATE KEY
(or 
ORDER BY
clause). It also builds a Prefix Index (sparse index) on the first 36 bytes of these columns. Currently, your sort key starts with 
id
. Since you filter by 
app_code
, it should be the first column in the sort key to enable Sort Key Pruning. * Action: Modify the sort key to place 
app_code
first. * SQL (3.3+):
Copy code
sql
    ALTER TABLE application_status ORDER BY (app_code, id, app_uid);
_Note: For older versions, you must recreate the table with 
DUPLICATE KEY(app_code, id, app_uid)
._ 3. Add a Bloom Filter Index Bloom filters are highly effective for high-cardinality string columns (like IDs or codes) used in equality filters (
WHERE app_code = '...'
). They allow StarRocks to skip data blocks that definitely do not contain the target value. * Action: Add a Bloom Filter index to the 
app_code
column. * SQL:
Copy code
sql
    ALTER TABLE application_status SET ("bloom_filter_columns" = "app_code");
4. Optimize the 
app_code
Column Length You have defined 
app_code
as 
varchar(1048576)
. While StarRocks handles large Varchars, it is a best practice to set a more realistic length (e.g., 
VARCHAR(255)
). * Why? In the Prefix Index (which is limited to 36 bytes), a very wide Varchar column can consume the entire 36-byte limit, preventing subsequent columns in your sort key (like 
id
) from being indexed. Summary of the Optimized Schema If you were to recreate the table for maximum performance, it would look like this:
Copy code
sql
CREATE TABLE `application_status` (
  `app_code` varchar(255) NULL, -- Moved up and shortened
  `id` decimal(20, 0) NULL,
  `app_uid` varchar(1048576) NULL,
  ...
) ENGINE=OLAP
DUPLICATE KEY(`app_code`, `id`) -- app_code is first for prefix indexing
DISTRIBUTED BY HASH(`app_code`) -- Enables bucket pruning
PROPERTIES (
"bloom_filter_columns" = "app_code", -- Fast equality filtering
"fast_schema_evolution" = "true",
"replicated_storage" = "true",
"replication_num" = "1"
);
Recommendation: If the table is large, start by adding the Bloom Filter and changing the Sort Key (if on v3.3+). These will provide the most immediate performance boost for filtering. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md • table_design/table_types/duplicate_key_table.md • table_design/table_types/table_types.md
l
@Rocky update this table to primary key for better permance
r
To upgrade this table to a Primary Key (PK) table, you must recreate it, as StarRocks does not support changing the table model via 
ALTER TABLE
. A Primary Key table will significantly improve performance for updates (upserts) and point lookups. For your specific query pattern (
WHERE app_code = ...
), the PK model allows you to decouple the sort order from the primary key, ensuring the fastest possible data pruning. Important Constraints for Primary Key Tables 1. No 
DECIMAL
in PK: Primary key columns do not support the 
DECIMAL
type. Since your 
id
is 
decimal(20,0)
, you should use 
LARGEINT
(which supports up to 38 digits). 2. Bucketing Requirement: The primary key must include the columns used in 
DISTRIBUTED BY HASH
. 3. NOT NULL: All columns in the Primary Key must be defined as 
NOT NULL
. Optimized Primary Key Table Schema
Copy code
sql
CREATE TABLE `application_status_pk` (
  `app_code` varchar(255) NOT NULL COMMENT "", -- Moved to front, shortened for prefix index
  `id` LARGEINT NOT NULL COMMENT "",           -- Changed from DECIMAL to LARGEINT
  `app_uid` varchar(100) NOT NULL COMMENT "",  -- Primary keys should be concise
  `app_number` decimal(11, 0) NULL COMMENT "",
  `process_id` varchar(1048576) NULL COMMENT "",
  `process_name` varchar(1048576) NULL COMMENT "",
  `created_date` datetime NULL COMMENT "",
  `last_updated_date` datetime NULL COMMENT "",
  `finished_date` datetime NULL COMMENT "",
  `created_by` varchar(1048576) NULL COMMENT "",
  `last_updated_by` varchar(1048576) NULL COMMENT "",
  `step_code` varchar(1048576) NULL COMMENT "",
  `current_decision` varchar(1048576) NULL COMMENT "",
  `current_decision_lbl` varchar(1048576) NULL COMMENT "",
  `current_reason` varchar(1048576) NULL COMMENT "",
  `current_reason_lbl` varchar(1048576) NULL COMMENT "",
  `current_reason_detail` varchar(1048576) NULL COMMENT "",
  `current_reason_detail_lbl` varchar(1048576) NULL COMMENT "",
  `current_comment` varchar(1048576) NULL COMMENT "",
  `partner_callback_status_code` varchar(1048576) NULL COMMENT "",
  `partner_callback_status_des` varchar(1048576) NULL COMMENT ""
) ENGINE=OLAP
PRIMARY KEY(`app_code`, `id`, `app_uid`) -- app_code included to allow Hash Distribution
DISTRIBUTED BY HASH(`app_code`)          -- Enables Bucket Pruning for app_code filters
ORDER BY (`app_code`)                    -- Enables Sort Key (Prefix Index) Pruning
PROPERTIES (
"enable_persistent_index" = "true",      -- Recommended: saves memory, uses SSD for index
"compression" = "LZ4",
"replicated_storage" = "true",
"replication_num" = "1"
);
Why this is faster for 
app_code
queries: * Bucket Pruning: By using 
DISTRIBUTED BY HASH(app_code)
, StarRocks only searches the specific tablet containing that code, rather than scanning the entire table. * Sort Key Pruning: The 
ORDER BY (app_code)
clause ensures data is physically sorted by 
app_code
. This allows StarRocks to use the Prefix Index to skip over massive amounts of data blocks within a tablet. * Persistent Index: Setting 
enable_persistent_index = true
ensures that primary key lookups and updates remain fast even as the table grows, without consuming excessive RAM. * Reduced Memory Overhead: Shortening the 
VARCHAR
length of 
app_code
from the max size to a realistic one (e.g., 255) ensures the Prefix Index (36-byte limit) isn't exhausted by a single column. Migration Step After creating the new table, migrate your data:
Copy code
sql
INSERT INTO application_status_pk SELECT * FROM application_status;
-- Verify and then swap names ALTER TABLE application_status RENAME application_status_old; ALTER TABLE application_status_pk RENAME application_status; ``` ## References - [table_design/table_types/primary_key_table.md](https://docs.starrocks.io/docs/table_design/table_types/primary_key_table) - [best_practices/primarykey_table.md](https://docs.starrocks.io/docs/best_practices/primarykey_table)
- [table_design/table_types/table_capabilities.md](https://docs.starrocks.io/docs/table_design/table_types/table_capabilities) ## References - [best_practices/primarykey_table.md](https://docs.starrocks.io/docs/best_practices/primarykey_table)
l
@Rocky i see data in this table, only id is not having null data, all other colume may have null value
r
Because the Primary Key (PK) table requires all primary key columns to be 
NOT NULL
, and your 
app_code
contains null values, we must adapt the strategy. In StarRocks (v3.0+), you can decouple the Sort Key from the Primary Key. This is the key to speeding up your queries on 
app_code
even when it is not part of the primary key. Optimized Primary Key Table Schema Since 
id
is your only guaranteed non-null column, it will serve as the Primary Key. We will use 
LARGEINT
because the Primary Key model does not support the 
DECIMAL
type for PK columns.
Copy code
sql
CREATE TABLE `application_status` (
  `id` LARGEINT NOT NULL COMMENT "",           -- Changed to LARGEINT (supports 38 digits)
  `app_code` varchar(255) NULL COMMENT "",      -- Kept NULL, but shortened for efficiency
  `app_uid` varchar(1048576) NULL COMMENT "",
  `app_number` decimal(11, 0) NULL COMMENT "",
  `process_id` varchar(1048576) NULL COMMENT "",
  `process_name` varchar(1048576) NULL COMMENT "",
  `created_date` datetime NULL COMMENT "",
  `last_updated_date` datetime NULL COMMENT "",
  `finished_date` datetime NULL COMMENT "",
  `created_by` varchar(1048576) NULL COMMENT "",
  `last_updated_by` varchar(1048576) NULL COMMENT "",
  `step_code` varchar(1048576) NULL COMMENT "",
  `current_decision` varchar(1048576) NULL COMMENT "",
  `current_decision_lbl` varchar(1048576) NULL COMMENT "",
  `current_reason` varchar(1048576) NULL COMMENT "",
  `current_reason_lbl` varchar(1048576) NULL COMMENT "",
  `current_reason_detail` varchar(1048576) NULL COMMENT "",
  `current_reason_detail_lbl` varchar(1048576) NULL COMMENT "",
  `current_comment` varchar(1048576) NULL COMMENT "",
  `partner_callback_status_code` varchar(1048576) NULL COMMENT "",
  `partner_callback_status_des` varchar(1048576) NULL COMMENT ""
) ENGINE=OLAP
PRIMARY KEY(`id`)                       -- Only id can be the PK if others are NULL
DISTRIBUTED BY HASH(`id`)               -- Required: Distribution key must be part of PK
ORDER BY (`app_code`, `id`)             -- SPEEDUP: Sorts data by app_code for fast pruning
PROPERTIES (
"enable_persistent_index" = "true",     -- Highly recommended for Primary Key tables
"bloom_filter_columns" = "app_code",    -- SPEEDUP: Fast skip for app_code filters
"compression" = "LZ4",
"replicated_storage" = "true",
"replication_num" = "1"
);
Why this works for your data: 1. Decoupled Sort Key (
ORDER BY
): By putting 
app_code
first in the 
ORDER BY
clause, StarRocks physically sorts the data by this column. Even though 
app_code
isn't in the Primary Key, StarRocks will use the Prefix Index to skip millions of rows during a 
WHERE app_code = '...'
query. 2. Type Conversion: 
decimal(20, 0)
is converted to 
LARGEINT
. Primary Key tables have strict type requirements for PK columns; 
DECIMAL
is generally not allowed, but 
LARGEINT
covers the same range (up to 38 digits). 3. Bloom Filter: Adding 
app_code
to 
bloom_filter_columns
allows StarRocks to quickly verify if a data block contains a specific 
app_code
without reading the full block from disk. Alternative (Maximum Performance) If you want even faster performance (Bucket Pruning), you could replace 
NULL
values in 
app_code
with an empty string 
''
or a placeholder like 
'N/A'
. If you do this, you can define the column as 
NOT NULL
and use: * 
PRIMARY KEY(app_code, id)
* 
DISTRIBUTED BY HASH(app_code)
This would allow StarRocks to prune entire tablets, which is the fastest possible way to query. However, the schema above is the best choice if you must keep the data as 
NULL
. References • table_design/table_types/primary_key_table.md • sql-reference/data-types/numeric/LARGEINT.md • best_practices/primarykey_table.md References
l
@Rocky does colum order of application_status_pk is difference with application_status cause insert into mismatch data colume ? INSERT INTO application_status_pk SELECT * FROM application_status;
r
Yes, if the column order in your new table is different from the original table, using 
INSERT INTO ... SELECT *
will cause a data mismatch or a type error. By default, StarRocks matches columns by their positional order, not by their names. Since you moved 
app_code
to the beginning of the table for better performance, a 
SELECT *
from the old table will try to put the old second column (
app_uid
) into the new second column (
app_code
). To migrate the data safely, you have two options: Option 1: Use 
BY NAME
(Recommended for StarRocks v3.4.0+) If you are on version 3.4 or later, you can use the 
BY NAME
clause. This automatically matches columns with the same name, regardless of their position.
Copy code
sql
INSERT INTO application_status_pk BY NAME
SELECT * FROM application_status;
Option 2: List Columns Explicitly (For All Versions) If you are on an older version, you must explicitly list the columns in the correct order for the target table. This is the safest way to ensure data goes into the right place.
Copy code
sql
INSERT INTO application_status_pk (
  id, 
  app_code, 
  app_uid, 
  app_number, 
  process_id, 
  process_name, 
  created_date, 
  last_updated_date, 
  finished_date, 
  created_by, 
  last_updated_by, 
  step_code, 
  current_decision, 
  current_decision_lbl, 
  current_reason, 
  current_reason_lbl, 
  current_reason_detail, 
  current_reason_detail_lbl, 
  current_comment, 
  partner_callback_status_code, 
  partner_callback_status_des
)
SELECT 
  id, 
  app_code, 
  app_uid, 
  app_number, 
  process_id, 
  process_name, 
  created_date, 
  last_updated_date, 
  finished_date, 
  created_by, 
  last_updated_by, 
  step_code, 
  current_decision, 
  current_decision_lbl, 
  current_reason, 
  current_reason_lbl, 
  current_reason_detail, 
  current_reason_detail_lbl, 
  current_comment, 
  partner_callback_status_code, 
  partner_callback_status_des
FROM application_status;
Important Notes: 1. Type Casting: StarRocks will automatically cast your original 
id
(DECIMAL) to the new 
id
(LARGEINT) during the insert, provided the values fit. 2. Null Handling: If you find any 
NULL
values in the 
id
column during the migration, the 
INSERT
will fail because the Primary Key must be 
NOT NULL
. You can use 
IFNULL(id, 0)
if you need to provide a default value for rows with missing IDs. 3. Speed Tip: For large tables, you can set the session variable 
set insert_timeout = 3600;
before running the 
INSERT
to prevent the operation from timing out. References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • knowledge/usage/best-practice.md • loading/InsertInto.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_AS_SELECT.md
l
@Rocky how to index if user usually query last_updated_date like this SELECT * FROM dmapp_link.losrepdb_application_status a where a.last_updated_date > (select max(c.last_updated_date ) from losrep.application_status c)
r
To optimize the performance of queries filtering by 
last_updated_date
, you should use Partitioning and Sort Keys. These features allow StarRocks to skip reading entire sections of data that don't meet your criteria. 1. Partition by Date (Best for Range Queries) Partitioning physically separates your data into folders based on time. When you run 
last_updated_date > (subquery)
, StarRocks will perform Partition Pruning, meaning it won't even look at partitions (days/months) that are older than your filter. 2. Include in Sort Key (
ORDER BY
) The 
ORDER BY
clause defines how data is sorted within each partition. If 
last_updated_date
is in the sort key, StarRocks can use its Prefix Index to skip blocks of data within a file, making the "greater than" search extremely fast. Recommended Schema (Primary Key Table) Assuming you are using the Primary Key model discussed earlier:
Copy code
sql
CREATE TABLE `application_status` (
  `id` LARGEINT NOT NULL,
  `last_updated_date` datetime NOT NULL, -- Must be NOT NULL if used in Partition Key
  `app_code` varchar(255) NULL,
  ... other columns ...
) ENGINE=OLAP
PRIMARY KEY(`id`)
-- 1. Use Partitioning for high-level data skipping
PARTITION BY date_trunc('day', `last_updated_date`) 
DISTRIBUTED BY HASH(`id`) 
-- 2. Put last_updated_date first in ORDER BY for range query speed
ORDER BY (`last_updated_date`, `app_code`) 
PROPERTIES (
    "enable_persistent_index" = "true",
    "replication_num" = "1"
);
Why this is faster for your query: 1. Partition Pruning: In your query 
a.last_updated_date > (SELECT max...)
, StarRocks will calculate the max value and then immediately ignore all partitions where the maximum date is already less than that value. 2. Sort Key Pruning: Within the active partitions, because the data is physically sorted by 
last_updated_date
, the storage engine can perform a binary-search-like lookup to find exactly where the "greater than" records start. 3. Runtime Filters: For your specific subquery pattern, StarRocks will generate a Runtime Filter. This means it calculates the 
MAX(last_updated_date)
once and "pushes" that value down to the scan node of the first table, preventing it from reading unnecessary data into memory. Optimization Tip for the Subquery If the subquery 
(select max(c.last_updated_date) from losrep.application_status c)
is slow because that table is huge, make sure 
losrep.application_status
also has 
last_updated_date
in its Sort Key (
ORDER BY
). This allows StarRocks to find the 
MAX
value in milliseconds by just looking at the very last block of the last partition. References • table_design/data_distribution/expression_partitioning.md • best_practices/partitioning.md • best_practices/query_tuning/schema_tuning.md References • table_design/data_distribution/expression_partitioning.md • best_practices/partitioning.md • best_practices/query_tuning/schema_tuning.md • table_design/table_types/primary_key_table.md -table) -usage-table) -design-model) • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
l
@Rocky Sort Key (
ORDER BY
) like ORDER BY (
last_updated_date
, 
app_code
) can update to table as alredy created with ORDER BY (
app_code
)
r
Yes, you can update the Sort Key (
ORDER BY
) of an existing table without recreating it, provided you are on StarRocks v3.0 or later. SQL Syntax To change the sort key from 
(app_code)
to 
(last_updated_date, app_code)
, run:
Copy code
sql
ALTER TABLE application_status ORDER BY (last_updated_date, app_code);
How it works: 1. Asynchronous Operation: The change happens in the background. You don't need to lock the table, and you can continue to read/write data. 2. Immediate effect for new data: Any new data loaded after this command will be physically sorted by the new key. 3. Background re-sorting for old data: StarRocks will gradually re-sort existing data into the new order during its background Compaction process. 4. Query Acceleration: Your queries filtering by 
last_updated_date
will start getting faster as more data is re-sorted. Monitoring the Progress Since this is an asynchronous task, you can check its status using:
Copy code
sql
SHOW ALTER TABLE COLUMN FROM application_status;
Look for the 
State
column. Once it says 
FINISHED
, the schema metadata is fully updated. Important Restrictions: * Version: This feature requires v3.0+ for Primary Key tables and v3.3+ for other table types (Duplicate, Unique, Aggregate). * Column Existence: The columns must already exist in the table. * Types: You cannot change the data type of the columns while reordering them. * Primary Key Tables: If it is a Primary Key table, the Primary Key definition remains the same; only the physical sorting changes. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/table_types/primary_key_table.md • table_design/sort_key.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/table_types/primary_key_table.md • table_design/sort_key.md • knowledge/usage/best-practice.md • best_practices/primarykey_table.md • table_design/indexes/Prefix_index_sort_key.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • best_practices/query_tuning/schema_tuning.md • table_design/data_distribution/expression_partitioning.md • best_practices/partitioning.md • administration/management/resource_management/Runtime_Filter.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_AS_SELECT.md • sql-reference/data-types/numeric/LARGEINT.md
l
@Rocky any difference between ORDER BY (
last_updated_date
, 
app_code
) and ORDER BY (
app_code
, 
last_updated_date
)
r
Yes, there is a significant difference in how StarRocks will handle your queries based on the order of these columns. In StarRocks, the 
ORDER BY
clause (Sort Key) defines the physical sorting of data on disk and determines the Prefix Index. This index is what allows StarRocks to skip massive amounts of data without reading it. 1. 
ORDER BY (last_updated_date, app_code)
 * Best for your query: This is the ideal choice for your 
last_updated_date > (select max...)
query. * How it works: Data is physically sorted by time first. The Prefix Index will contain the 
last_updated_date
. * Performance: When you filter by date range, StarRocks uses a binary search on the Prefix Index to jump directly to the correct starting point in the data files. It will skip all the older data blocks entirely. 2. 
ORDER BY (app_code, last_updated_date)
 * Worst for your query: This will likely result in a slow, full-table scan (or many more small scans) for your specific query. * How it works: Data is sorted by 
app_code
first. All records for 
app_code = 'A'
are stored together, then all for 
app_code = 'B'
, and so on. * Performance: Because 
last_updated_date
is the second column, StarRocks cannot use the Prefix Index to skip data based on date alone. To find records where 
last_updated_date > X
, it would have to look inside every single 
app_code
group to see if any records match, which is much slower. Key Rules for Sort Keys: 1. Leading Column Rule: The most frequently used filter column (especially range filters like 
>
, 
<
, 
BETWEEN
) should be the first column in the 
ORDER BY
list. 2. The "Blocker" Rule: If you put a 
VARCHAR
column first (like 
app_code
), it can sometimes "cut off" the Prefix Index. The Prefix Index only stores the first 36 bytes of the sort key. If a 
VARCHAR
is at the start, StarRocks often stops there and doesn't include the following columns in the index. 3. Cardinality: If you have multiple equal-frequency filters, put the one with the higher cardinality (more unique values) first. Recommendation For your specific query pattern, you should definitely use:
Copy code
sql
ALTER TABLE application_status_pk ORDER BY (last_updated_date, app_code);
This ensures that your incremental data loading (based on the max date) remains fast even as your table grows to millions or billions of rows. References • table_design/indexes/Prefix_index_sort_key.md • table_design/table_types/primary_key_table.md
l
@Rocky runWorker(ThreadPoolExecutor.java:1144) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) at java.base/java.lang.Thread.run(Thread.java:1583) Caused by: java.time.format.DateTimeParseException: Text '20Aug25' could not be parsed at index 0 at java.base/java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:2108) at java.base/java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1936) at com.starrocks.common.util.DateUtils.parseStringWithDefaultHSM(DateUtils.java:221) at com.starrocks.common.util.DateUtils.parseStrictDateTime(DateUtils.java:160) at com.starrocks.connector.PartitionUtil.convertToDateLiteral(PartitionUtil.java:662) ... 35 more 2026-05-27 135408.255+07:00 WARN (thrift-server-pool-19880|60327) [LeaderImpl.finishTask():232] cannot find task. type: PUBLISH_VERSION, backendId: 10002, signature: 2406566 2026-05-27 135408.255+07:00 WARN (thrift-server-pool-19140|58269) [LeaderImpl.finishTask():232] cannot find task. type: PUBLISH_VERSION, backendId: 3731846, signature: 2406566 2026-05-27 135419.524+07:00 WARN (thrift-server-pool-19672|59760) [OlapTableSink.createLocation():916] Tablet 3735753 replicas [[replicaId=3735754, BackendId=3731846, version=14, versionHash=0, minReadableVersion=0, lastReportVersion=14, dataSize=19138, rowCount=97, lastFailedVersion=-1, lastFailedVersionHash=0, lastSuccessVersion=14, lastSuccessVersionHash=0, lastFailedTimestamp=-1, schemaHash=1777191955, minReadableVersion=0, state=NORMAL], [replicaId=3735755, BackendId=10002, version=14, versionHash=0, minReadableVersion=0, lastReportVersion=14, dataSize=19138, rowCount=97, lastFailedVersion=-1, lastFailedVersionHash=0, lastSuccessVersion=14, lastSuccessVersionHash=0, lastFailedTimestamp=-1, schemaHash=1777191955, minReadableVersion=0, state=NORMAL]] all has write fail flag 2026-05-27 135419.524+07:00 WARN (thrift-server-pool-19672|59760) [OlapTableSink.createLocation():916] Tablet 3735756 replicas [[replicaId=3735757, BackendId=3731846, version=14, versionHash=0, minReadableVersion=0, lastReportVersion=14, dataSize=20864, rowCount=99, lastFailedVersion=-1, lastFailedVersionHash=0, lastSuccessVersion=14, lastSuccessVersionHash=0, lastFailedTimestamp=-1, schemaHash=1777191955, minReadableVersion=0, state=NORMAL], [replicaId=3735758, BackendId=10002, version=14, versionHash=0, minReadableVersion=0, lastReportVersion=14, dataSize=20864, rowCount=99, lastFailedVersion=-1, lastFailedVersionHash=0, lastSuccessVersion=14, lastSuccessVersionHash=0, lastFailedTimestamp=-1, schemaHash=1777191955, minReadableVersion=0, state=NORMAL]] all has write fail flag 2026-05-27 135428.690+07:00 WARN (tablet checker|66) [TabletChecker.doCheckOnePartition():529] tablet: 1722394 is in unhealthy state: REPLICA_MISSING, but there are no healthy replicas, can not repair 2026-05-27 135439.089+07:00 WARN (starrocks-mysql-nio-pool-2716|59837) [ColumnFilterConverter$ColumnFilterVisitor.visitBinaryPredicate():528] build column filter failed. com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: create string to date literal failed:21Aug25. at com.starrocks.connector.PartitionUtil.convertToDateLiteral(PartitionUtil.java:665) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter.convertLiteral(ColumnFilterConverter.java:648) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter$ColumnFilterVisitor.visitBinaryPredicate(ColumnFilterConverter.java:505) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter$ColumnFilterVisitor.visitBinaryPredicate(ColumnFilterConverter.java:480) at com.starrocks.sql.optimizer.operator.scalar.BinaryPredicateOperator.accept(BinaryPredicateOperator.java:58) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter.convertColumnFilter(ColumnFilterConverter.java:262) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter.convertColumnFilter(ColumnFilterConverter.java:172) at com.starrocks.sql.optimizer.operator.logical.LogicalScanOperator$Builder.build(LogicalScanOperator.java:269) at com.starrocks.sql.optimizer.operator.logical.LogicalScanOperator$Builder.build(LogicalScanOperator.java:250) at com.starrocks.sql.optimizer.rule.transformation.PushDownPredicateToExternalTableScanRule.transform(PushDownPredicateToExternalTableScanRule.java:81) at com.starrocks.sql.optimizer.task.RewriteTreeTask.applyRules(RewriteTreeTask.java:118) at com.starrocks.sql.optimizer.task.RewriteTreeTask.applyRules(RewriteTreeTask.java:111) at com.starrocks.sql.optimizer.task.RewriteTreeTask.rewrite(RewriteTreeTask.java:91) at com.starrocks.sql.optimizer.task.RewriteTreeTask.rewrite(RewriteTreeTask.java:94) at com.starrocks.sql.optimizer.task.RewriteTreeTask.rewrite(RewriteTreeTask.java:94) at com.starrocks.sql.optimizer.task.RewriteTreeTask.rewrite(RewriteTreeTask.java:94) at com.starrocks.sql.optimizer.task.RewriteTreeTask.rewrite(RewriteTreeTask.java:94) at com.starrocks.sql.optimizer.task.RewriteTreeTask.rewrite(RewriteTreeTask.java:94) at com.starrocks.sql.optimizer.task.RewriteTreeTask.rewrite(RewriteTreeTask.java:94) at com.starrocks.sql.optimizer.task.RewriteTreeTask.execute(RewriteTreeTask.java:74) at com.starrocks.sql.optimizer.task.TaskScheduler.executeTasks(TaskScheduler.java:43) at com.starrocks.sql.optimizer.task.TaskScheduler.rewriteIterative(TaskScheduler.java:54) at com.starrocks.sql.optimizer.QueryOptimizer.logicalRuleRewrite(QueryOptimizer.java:567) at com.starrocks.sql.optimizer.QueryOptimizer.rewriteAndValidatePlan(QueryOptimizer.java:819) at com.starrocks.sql.optimizer.QueryOptimizer.optimizeByCost(QueryOptimizer.java:256) at com.starrocks.sql.optimizer.QueryOptimizer.optimize(QueryOptimizer.java:212) at com.starrocks.sql.InsertPlanner.buildExecPlan(InsertPlanner.java:584) at com.starrocks.sql.InsertPlanner.plan(InsertPlanner.java:359) at com.starrocks.sql.StatementPlanner.planInsertStmt(StatementPlanner.java:280) at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:163) at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:108) at com.starrocks.qe.StmtExecutor.generateExecPlan(StmtExecutor.java:714) at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:827) at com.starrocks.qe.ConnectProcessor.executeQueryAttempt(ConnectProcessor.java:550) at com.starrocks.qe.ConnectProcessor.runWithParserStageRetry(ConnectProcessor.java:447) at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:384) at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:761) at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:1141) at com.starrocks.mysql.nio.MySQLReadListener.handleRequest(MySQLReadListener.java:152) at com.starrocks.mysql.nio.MySQLReadListener.lambda$handleEvent$0(MySQLReadListener.java:92) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) at java.base/java.lang.Thread.run(Thread.java:1583) Caused by: java.time.format.DateTimeParseException: Text '21Aug25' could not be parsed at index 0 at java.base/java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:2108) at java.base/java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1936) at com.starrocks.common.util.DateUtils.parseStringWithDefaultHSM(DateUtils.java:221) at com.starrocks.common.util.DateUtils.parseStrictDateTime(DateUtils.java:160) at com.starrocks.connector.PartitionUtil.convertToDateLiteral(PartitionUtil.java:662) ... 42 more 2026-05-27 135439.090+07:00 WARN (starrocks-mysql-nio-pool-2716|59837) [ColumnFilterConverter$ColumnFilterVisitor.visitBinaryPredicate():528] build column filter failed. com.starrocks.sql.analyzer.SemanticException: Getting analyzing error. Detail message: create string to date literal failed:21Aug25. at com.starrocks.connector.PartitionUtil.convertToDateLiteral(PartitionUtil.java:665) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter.convertLiteral(ColumnFilterConverter.java:648) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter$ColumnFilterVisitor.visitBinaryPredicate(ColumnFilterConverter.java:505) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter$ColumnFilterVisitor.visitBinaryPredicate(ColumnFilterConverter.java:480) at com.starrocks.sql.optimizer.operator.scalar.BinaryPredicateOperator.accept(BinaryPredicateOperator.java:58) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter.convertColumnFilter(ColumnFilterConverter.java:262) at com.starrocks.sql.optimizer.operator.ColumnFilterConverter.convertColumnFilter(ColumnFilterConverter.java:172) at com.starrocks.sql.optimizer.operator.logical.LogicalScanOperator$Builder.build(LogicalScanOperator.java:269) at com.starrocks.sql.optimizer.operator.logical.LogicalScanOperator$Builder.build(LogicalScanOperator.java:250) at
r
The error 
java.time.format.DateTimeParseException: Text '20Aug25' could not be parsed
occurs because StarRocks is trying to automatically convert a string value (likely from an external table partition or a 
VARCHAR
column) into a 
DATE
type to perform a comparison, but it does not recognize the 
DDMonYY
 format (e.g., 
20Aug25
). This typically happens during Predicate Pushdown when you compare a 
VARCHAR
column to a 
DATE
result from a subquery. StarRocks attempts to cast the column's values to 
DATE
to match the subquery's type, and the parser fails on your non-standard format. Solution 1: Explicitly Parse the Date (Recommended) You should use 
str_to_date
with the specific format 
%d%b%y
to tell StarRocks how to interpret the string. Update your query like this:
Copy code
sql
SELECT * 
FROM dmapp_link.losrepdb_application_status a 
WHERE str_to_date(a.last_updated_date, '%d%b%y') > (
    SELECT max(c.last_updated_date) 
    FROM losrep.application_status c
);
* `%d`: Day of month (01-31) * `%b`: Abbreviated month name (Jan, Aug, etc.) * `%y`: 2-digit year (25) Solution 2: Fix External Table Schema If 
dmapp_link.losrepdb_application_status
is an external table (e.g., Hive or JDBC) and the column 
last_updated_date
is defined as 
DATE
in StarRocks but contains strings like 
'20Aug25'
in the source: 1. Change the column type to 
VARCHAR
 in your StarRocks external table/catalog definition. 2. Use the 
str_to_date
function shown above in your queries. Why Solution 1 is necessary for correctness: You cannot simply compare these as strings (e.g., 
a.last_updated_date > '20Aug25'
) because string comparison is alphabetical. For example, 
'01Sep24'
would be considered "smaller" than 
'20Aug24'
alphabetically, even though it is a later date. Converting both sides to a proper 
DATE
type ensures the comparison follows chronological order. Note on Performance If 
last_updated_date
is a partition column in your external table, using a function like 
str_to_date(column)
on the left side of the operator may prevent partition pruning in some older StarRocks versions. If you notice the query becomes very slow: 1. Verify if you can change the source format to standard 
YYYY-MM-DD
. 2. Or, if the number of partitions is small, try to rewrite the filter to compare the subquery result formatted as a string back to the column (though this only works for equality 
=
and not for range 
>
). References • sql-reference/sql-functions/date-time-functions/str_to_date.md • sql-reference/sql-functions/date-time-functions/date_format.md • data_source/External_table.md References • data_source/file_external_table.md • data_source/External_table.md • sql-reference/sql-functions/date-time-functions/to_tera_date.md • sql-reference/sql-functions/date-time-functions/to_tera_timestamp.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
l
@Rocky my table dtm_customer_ld_summary_update20052026 is streamming 1 year data from source, should i use this table with inserted data to update another table
r
Whether you should use your streaming table (
dtm_customer_ld_summary_update20052026
) to update another table depends on your goals (one-time migration vs. recurring synchronization). In StarRocks, the best practice for "updating" one table from another is to use the Primary Key table model and the 
INSERT INTO ... SELECT
 syntax. 1. Recommended Approach: Primary Key Table + UPSERT If your target table is a Primary Key table, an 
INSERT INTO
statement automatically acts as an UPSERT. It will update existing rows (based on the primary key) and insert new ones. Why this is better than a standard `UPDATE`: * Performance: 
INSERT INTO ... SELECT
is highly optimized in StarRocks as a batch load operation. A standard 
UPDATE ... FROM
is often slower for millions of rows. * Atomicity: It runs as a single transaction. SQL Example:
Copy code
sql
-- This will update existing records and insert new ones in the target table
INSERT INTO target_table 
SELECT * FROM dtm_customer_ld_summary_update20052026;
2. Handling "1 Year of Data" (Large Volume) Since you are moving one year of data, the operation might take a long time and could time out in a standard SQL session. * Use Asynchronous Tasks: Use 
SUBMIT TASK
to run the update in the background. You can monitor it via 
information_schema.task_runs
.
Copy code
sql
    SUBMIT TASK AS
    INSERT INTO target_table SELECT * FROM dtm_customer_ld_summary_update20052026;
* Batch by Partition: If your tables are partitioned by date, it is much safer and faster to update one partition at a time.
Copy code
sql
    INSERT INTO target_table PARTITION (p202401)
    SELECT * FROM dtm_customer_ld_summary_update20052026
    WHERE event_date >= '2024-01-01' AND event_date < '2024-02-01';
3. Should you do this at all? (Workflow Best Practice) * If this is a one-time thing: Yes, using the 
INSERT INTO ... SELECT
method above is the standard way to move/sync data between StarRocks tables. * If this is a recurring process: It is usually better to stream directly from the source into your final target table using Routine Load (Kafka) or Flink CDC. Loading into a "staging" table first and then moving it to a second table adds unnecessary overhead, storage cost, and latency. Summary Checklist 1. Target Table Type: Ensure the target table is a Primary Key table so the 
INSERT
performs an update. 2. Partial Updates: If you only want to update some columns, specify them in the 
INSERT
statement: 
INSERT INTO target (id, score) SELECT id, score FROM source;
(Available in v3.3.1+). 3. Timeout: If the query fails with a timeout, increase the 
insert_timeout
session variable or use 
SUBMIT TASK
. References • loading/InsertInto.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • faq/loading/Insert_into_faq.md • knowledge/usage/best-practice.md References • loading/InsertInto.md • faq/loading/Insert_into_faq.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • knowledge/usage/best-practice.md
l
@Rocky my update table dtm_customer_ld_summary_update20052026 only have 8 colum why target table dtm_customer_ld_summary have 300 columes so i thinks insert it's not fit
r
If your target table (
dtm_customer_ld_summary
) is a Primary Key table, the fact that it has 300 columns while your update table has only 8 is not a problem. StarRocks is designed to handle this through Partial Updates. You have two main options to update those 8 columns without touching the other 292 columns: Option 1: Multi-Table 
UPDATE
(Recommended for SQL) Starting from StarRocks v3.0, you can use the 
UPDATE ... FROM
syntax to join your source and target tables. This is perfect for your case where you want to update specific columns in a large table using a smaller staging table. Example:
Copy code
sql
UPDATE dtm_customer_ld_summary target
SET 
    target.col_a = source.col_a,
    target.col_b = source.col_b,
    -- ... add the other 6 columns here
    target.last_updated = source.last_updated
FROM dtm_customer_ld_summary_update20052026 source
WHERE target.customer_id = source.customer_id; -- Join on your Primary Key
* Performance Tip: Since you are updating 8 columns out of 300 (< 30%), StarRocks will likely use "Column Mode" partial updates automatically (v3.1+), which is extremely fast for this exact scenario. Option 2: Partial 
INSERT INTO
 You can also use 
INSERT INTO
by specifying only the columns you want to update. For this to work as a partial update, you must enable the session variable first. Example:
Copy code
sql
-- 1. Enable partial updates for this session
SET partial_update_mode = 'column';

-- 2. Insert only the columns you have
INSERT INTO dtm_customer_ld_summary (customer_id, col_a, col_b, ...)
SELECT customer_id, col_a, col_b, ... 
FROM dtm_customer_ld_summary_update20052026;
* Behavior: If the 
customer_id
already exists, StarRocks will update only the columns you listed and leave the other 292 columns unchanged. If it doesn't exist, it will insert a new row (filling the other columns with their default values). Which one should you use? * Use Option 1 (
UPDATE
) if you only want to update rows that already exist in the target table. * Use Option 2 (
INSERT
) if you want to Upsert (update existing rows AND insert new ones that don't exist yet). Important Note: These features only work if 
dtm_customer_ld_summary
is a Primary Key table. If it is a Duplicate Key or Unique Key table, you cannot perform partial updates this way; you would have to read all 300 columns, modify 8, and overwrite the whole rows, which is much slower. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/Load_to_Primary_Key_tables.md • table_design/table_types/primary_key_table.md • loading/InsertInto.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
l
@Rocky yes, target update table is primary key ENGINE=OLAP PRIMARY KEY(
sys_run_date
, 
ld_no
) COMMENT "Table luu so lieu chi tiet hop dong vay theo ngay" PARTITION BY RANGE(
sys_run_date
) so i will using Option 2: Partial 
INSERT INTO
r
Since your target table is a Primary Key table and you are updating a small number of columns (8 out of 300) across a large number of rows (1 year of data), Option 2 (Partial 
INSERT INTO
) in Column Mode is the most efficient choice. In StarRocks, when you perform an 
INSERT INTO
on a Primary Key table and specify only a subset of columns, it behaves as a Partial Upsert. Step-by-Step Implementation To update your 300-column table using your 8-column source table, follow these steps: 1. Configure the Session for High Performance Since you are updating only ~2.6% of the columns, you should force Column Mode. This mode is significantly faster for your scenario because StarRocks only reads and writes the affected column files rather than the entire 300-column row.
Copy code
sql
-- Use Column Mode for large batch updates with few columns
SET partial_update_mode = 'column';

-- Increase timeout for 1 year of data (e.g., set to 1 hour = 3600s)
SET insert_timeout = 3600;
2. Execute the Partial Insert You must explicitly list the columns you want to update in the 
INSERT
clause. Crucially, you must include the Primary Key columns (
sys_run_date
, 
ld_no
) so StarRocks knows which rows to update.
Copy code
sql
INSERT INTO dtm_customer_ld_summary (
    sys_run_date, 
    ld_no, 
    col3, col4, col5, col6, col7, col8  -- Your 6 other columns
)
SELECT 
    sys_run_date, 
    ld_no, 
    col3, col4, col5, col6, col7, col8 
FROM dtm_customer_ld_summary_update20052026;
Why this works: * Existing Rows: If a row with the same 
sys_run_date
and 
ld_no
already exists, StarRocks will only update the 6 other columns you specified. The other 292 columns in the target table will remain untouched. * New Rows: If the 
sys_run_date
and 
ld_no
do not exist, StarRocks will insert a new row. The 292 columns not mentioned in your query will be filled with their defined 
DEFAULT
values (or 
NULL
if no default is set). Important Considerations: 1. Partition Pruning: Since your table is partitioned by 
sys_run_date
and it is also part of your Primary Key, StarRocks will efficiently locate the correct partitions to update. 2. Memory Limit: If you get a "Memory Limit Exceeded" error, it's likely because the "1 year of data" is too large for a single transaction. In that case, run the 
INSERT
in smaller batches (e.g., month by month) using a 
WHERE
clause:
Copy code
sql
    INSERT INTO ... SELECT ... WHERE sys_run_date >= '2024-01-01' AND sys_run_date < '2024-02-01';
3. Background Execution: For such a large operation, consider using the 
SUBMIT TASK
command (v3.0+) to run it as an asynchronous background job:
Copy code
sql
    SUBMIT TASK AS
    INSERT INTO dtm_customer_ld_summary (...) SELECT ... FROM ...;
References • loading/Load_to_Primary_Key_tables.md • loading/InsertInto.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/table_types/primary_key_table.md References • loading/Load_to_Primary_Key_tables.md • sql-reference/System_variable.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md
2 Views
Open in Slack
PreviousNext
Powered by