http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hello
r
Hello! I'm Rocky, your StarRocks assistant. How can I help you today?
l
@Rocky i see alot of fix and update on starrocks 4.0, and have bug fix to 4.0.13, why starrocks 4.1 not have such update and fix?
r
The reason StarRocks 4.0 has more patch updates (reaching v4.0.13) compared to StarRocks 4.1 is primarily due to their release timelines and maintenance lifecycle: 1. Release Timeline * StarRocks 4.0 was released in late 2025 (around October/November). It has been in production for several months longer than v4.1. * StarRocks 4.1 is the newer minor version, released in May 2026. Because 4.0 has been available longer, it has gone through more cycles of community feedback, bug discovery, and subsequent patch releases. 2. Backporting Strategy When a bug is fixed in the StarRocks codebase, the fix is typically applied to the latest development branch and then backported to older, actively maintained minor versions (like 4.0). * Many of the fixes you see in v4.0.13 were already included in the initial v4.1.0 release or its early patches. * The high number of patches for 4.0 ensures that users who haven't yet upgraded to the newer 4.1 architecture still receive critical stability improvements. 3. Stability vs. Features * 4.0 is currently treated as a mature, stable branch for users who prioritize long-term consistency. * 4.1 is the "Feature" branch, introducing major new capabilities like Adaptive Layout and Iceberg Variant support. While it has fewer patches now, it will continue to receive updates as it matures. Summary of Current Status: * v4.0.13: The latest stable patch for the 4.0 series. * v4.1.x: The latest feature-rich version (currently around v4.1.3). Most "fixes" in 4.0.13 are already present in the 4.1 line. If you are starting a new project, v4.1 is recommended for the latest features and performance optimizations. If you are already on 4.0, staying on the latest patch (v4.0.13) is the safest path until you are ready for the architectural changes introduced in 4.1. References • release_notes/release-4.0.md
l
@Rocky show grants to 'dmapp_read_only'@'%';
r
To view the privileges for a specific user in StarRocks, the correct syntax uses 
FOR
 instead of 
TO
. Correct Syntax
Copy code
sql
SHOW GRANTS FOR 'dmapp_read_only'@'%';
Key Details * Permissions: You must have the 
user_admin
system role to view the privileges of another user. Any user can run 
SHOW GRANTS;
(without arguments) to see their own privileges. * Alternative (System View): You can also query the system metadata directly for a more structured view:
Copy code
sql
    SELECT * FROM sys.grants_to_users
    WHERE GRANTEE = "'dmapp_read_only'@'%'";
* Identity Format: Ensure the user identity (
'user'@'host'
) matches exactly how it was created. If you aren't sure of the exact host, you can check all users first with 
SHOW ALL AUTHENTICATION;
. References • sql-reference/sql-statements/account-management/SHOW_GRANTS.md • sql-reference/sys/grants_to_users.md References • sql-reference/sql-statements/account-management/SHOW_GRANTS.md • sql-reference/sys/grants_to_users.md • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/account-management/GRANT.md • sql-reference/sql-statements/account-management/SHOW_AUTHENTICATION.md • administration/user_privs/authorization/privilege_faq.md
l
@Rocky SHOW GRANTS FOR role 'dmapp_read_only'@'%';
r
The syntax you provided is slightly incorrect because StarRocks distinguishes between users and roles. 1. If 
'dmapp_read_only'@'%'
is a User User identities in StarRocks include the host (e.g., 
@'%'
). For users, you do not use the 
ROLE
keyword. Correct Syntax:
Copy code
sql
SHOW GRANTS FOR 'dmapp_read_only'@'%';
2. If 
dmapp_read_only
is a Role Roles in StarRocks are usually just names (like 
read_only_role
) and do not have a host part (
@'%'
). If you have a role named 
dmapp_read_only
, use the following: Correct Syntax:
Copy code
sql
SHOW GRANTS FOR ROLE dmapp_read_only;
Summary of Differences Target | Syntax Example User | 
SHOW GRANTS FOR 'user_name'@'host';
Role | 
SHOW GRANTS FOR ROLE role_name;
Current User | 
SHOW GRANTS;
Note: To run this for another user or role, you must have the 
user_admin
system role. If you are unsure whether it's a user or a role, you can check with 
SHOW ALL AUTHENTICATION;
(for users) or 
SHOW ROLES;
(for roles). References • sql-reference/sql-statements/account-management/SHOW_GRANTS.md • administration/user_privs/authorization/User_privilege.md References • sql-reference/sql-statements/account-management/SHOW_GRANTS.md • sql-reference/sys/grants_to_users.md • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/account-management/GRANT.md • sql-reference/sys/grants_to_roles.md
l
@Rocky CREATE TABLE dmapp.
xsell_eligible_new
( 
report_date
date NOT NULL COMMENT "Ngày báo cáo", 
los_app_code
varchar(30) NULL COMMENT "Mã đơn vay LOS", 
flag_duration_apply
int(11) NULL COMMENT "Cờ thời hạn áp dụng", 
flag_9_payment_period
int(11) NULL COMMENT "Cờ chu kỳ thanh toán thứ 9", 
final_eligible
varchar(26) NULL COMMENT "Trạng thái đủ điều kiện cuối cùng", 
max_amount
bigint(20) NULL COMMENT "Số tiền tối đa", 
product
varchar(26) NULL COMMENT "Sản phẩm", 
bscore_rank
varchar(26) NULL COMMENT "Xếp hạng điểm BSCORE" ) ENGINE=OLAP PRIMARY KEY(
report_date
, 
los_app_code
) PARTITION BY trunct(
month
,
report_date
) DISTRIBUTED BY HASH(
los_app_code
) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" );
r
The SQL you provided has a few small syntax errors and constraint requirements for StarRocks. Below is the corrected version and a summary of the changes: Corrected SQL
Copy code
sql
CREATE TABLE dmapp.`xsell_eligible_new` (
  `report_date` date NOT NULL COMMENT "Ngày báo cáo",
  `los_app_code` varchar(30) NOT NULL COMMENT "Mã đơn vay LOS", -- Must be NOT NULL for Primary Key
  `flag_duration_apply` int(11) NULL COMMENT "Cờ thời hạn áp dụng",
  `flag_9_payment_period` int(11) NULL COMMENT "Cờ chu kỳ thanh toán thứ 9",
  `final_eligible` varchar(26) NULL COMMENT "Trạng thái đủ điều kiện cuối cùng",
  `max_amount` bigint(20) NULL COMMENT "Số tiền tối đa",
  `product` varchar(26) NULL COMMENT "Sản phẩm",
  `bscore_rank` varchar(26) NULL COMMENT "Xếp hạng điểm BSCORE"
) ENGINE=OLAP
PRIMARY KEY(`report_date`, `los_app_code`)
PARTITION BY date_trunc('month', `report_date`) -- Corrected function name and syntax
DISTRIBUTED BY HASH(`los_app_code`) BUCKETS 8
PROPERTIES (
    "compression" = "LZ4",
    "fast_schema_evolution" = "true",
    "replicated_storage" = "true",
    "replication_num" = "2"
);
Key Changes Explained: 1. Primary Key Nullability: In StarRocks, all columns defined in the 
PRIMARY KEY
(in your case, 
report_date
and 
los_app_code
) must be defined as 
NOT NULL
. 2. Partitioning Syntax: * The function name is 
date_trunc
, not 
trunct
. * The time unit (e.g., 
'month'
) must be enclosed in single quotes. 3. Data Distribution Requirement: For Primary Key tables, all columns used for partitioning (
report_date
) and bucketing (
los_app_code
) must be part of the Primary Key definition. Your schema already satisfies this. 4. Properties: * 
replicated_storage
= 
true
is the default in newer versions (v3.0+), enabling single-leader replication for better performance. * 
fast_schema_evolution
= 
true
is highly recommended for Primary Key tables to allow near-instant column additions/deletions. References • table_design/table_types/primary_key_table.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • knowledge/usage/best-practice.md • administration/management/resource_management/Replica.md
l
@Rocky update dwh.af_extract_mapping set STATUS_FLAG = 0 where UPPER(a.SRC_SCHEMA) ='PTKD' ; correct
r
In StarRocks, the 
UPDATE
statement has a few specific requirements. Based on your input, there are two main issues: the Slack-formatted table name and an undefined table alias (
a
). Corrected SQL Assuming 
dwh
is your database and 
af_extract_mapping
is your table:
Copy code
sql
UPDATE dwh.af_extract_mapping 
SET STATUS_FLAG = 0 
WHERE UPPER(SRC_SCHEMA) = 'PTKD';
Key Requirements & Fixes: 1. Remove the Alias: You used 
a.SRC_SCHEMA
, but the alias 
a
was not defined. In a single-table 
UPDATE
, you can simply refer to the column name directly. 2. Primary Key Table: In StarRocks, the 
UPDATE
statement is only supported for Primary Key tables. If your table uses the Duplicate, Aggregate, or Unique key models, you cannot use 
UPDATE
. 3. Slack Artifacts: Ensure the table name doesn't include the 
<http://...|...>
characters, which are often added automatically when copying text from Slack. 4. WHERE Clause: The 
WHERE
clause is mandatory in StarRocks (unless you are performing a partial update in column mode in v3.1+ or use 
WHERE true
) to prevent accidental full-table updates. Your use of 
UPPER()
is perfectly valid. Using an Alias (Multi-table syntax) If you specifically want to use an alias (e.g., for complex joins), you must use the 
FROM
clause:
Copy code
sql
UPDATE dwh.af_extract_mapping
SET STATUS_FLAG = 0
FROM dwh.af_extract_mapping AS a
WHERE UPPER(a.SRC_SCHEMA) = 'PTKD'
  AND dwh.af_extract_mapping.primary_key_col = a.primary_key_col;
Note: The single-table version provided first is much more efficient. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/table_types/primary_key_table.md References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • loading/SQL_transaction.md • loading/Load_to_Primary_Key_tables.md
l
@Rocky why with the same number of data, table xsell_eligible need 8GB but xsell_eligible_old only need 1.6GB to store CREATE TABLE 
xsell_eligible_old
( 
report_date
date NOT NULL COMMENT "Ngày báo cáo", 
los_app_code
varchar(30) NULL COMMENT "Mã đơn vay LOS", 
flag_duration_apply
int(11) NULL COMMENT "Cờ thời hạn áp dụng", 
flag_9_payment_period
int(11) NULL COMMENT "Cờ chu kỳ thanh toán thứ 9", 
final_eligible
varchar(26) NULL COMMENT "Trạng thái đủ điều kiện cuối cùng", 
max_amount
bigint(20) NULL COMMENT "Số tiền tối đa", 
product
varchar(26) NULL COMMENT "Sản phẩm", 
bscore_rank
varchar(26) NULL COMMENT "Xếp hạng điểm BSCORE" ) ENGINE=OLAP DUPLICATE KEY(
report_date
) PARTITION BY RANGE(
report_date
) (PARTITION p202303 VALUES [("2023-03-01"), ("2023-04-01")), PARTITION p202304 VALUES [("2023-04-01"), ("2023-05-01")), PARTITION p202305 VALUES [("2023-05-01"), ("2023-06-01")), PARTITION p202306 VALUES [("2023-06-01"), ("2023-07-01")), PARTITION p202307 VALUES [("2023-07-01"), ("2023-08-01")), PARTITION p202308 VALUES [("2023-08-01"), ("2023-09-01")), PARTITION p202309 VALUES [("2023-09-01"), ("2023-10-01")), PARTITION p202310 VALUES [("2023-10-01"), ("2023-11-01")), PARTITION p202311 VALUES [("2023-11-01"), ("2023-12-01")), PARTITION p202312 VALUES [("2023-12-01"), ("2024-01-01")), PARTITION p202401 VALUES [("2024-01-01"), ("2024-02-01")), PARTITION p202402 VALUES [("2024-02-01"), ("2024-03-01")), PARTITION p202403 VALUES [("2024-03-01"), ("2024-04-01")), PARTITION p202404 VALUES [("2024-04-01"), ("2024-05-01")), PARTITION p202405 VALUES [("2024-05-01"), ("2024-06-01")), PARTITION p202406 VALUES [("2024-06-01"), ("2024-07-01")), PARTITION p202407 VALUES [("2024-07-01"), ("2024-08-01")), PARTITION p202408 VALUES [("2024-08-01"), ("2024-09-01")), PARTITION p202409 VALUES [("2024-09-01"), ("2024-10-01")), PARTITION p202410 VALUES [("2024-10-01"), ("2024-11-01")), PARTITION p202411 VALUES [("2024-11-01"), ("2024-12-01")), PARTITION p202412 VALUES [("2024-12-01"), ("2025-01-01")), PARTITION p202501 VALUES [("2025-01-01"), ("2025-02-01")), PARTITION p202502 VALUES [("2025-02-01"), ("2025-03-01")), PARTITION p202503 VALUES [("2025-03-01"), ("2025-04-01")), PARTITION p202504 VALUES [("2025-04-01"), ("2025-05-01")), PARTITION p202505 VALUES [("2025-05-01"), ("2025-06-01")), PARTITION p202506 VALUES [("2025-06-01"), ("2025-07-01")), PARTITION p202507 VALUES [("2025-07-01"), ("2025-08-01")), PARTITION p202508 VALUES [("2025-08-01"), ("2025-09-01")), PARTITION p202509 VALUES [("2025-09-01"), ("2025-10-01")), PARTITION p202510 VALUES [("2025-10-01"), ("2025-11-01")), PARTITION p202511 VALUES [("2025-11-01"), ("2025-12-01")), PARTITION p202512 VALUES [("2025-12-01"), ("2026-01-01")), PARTITION p202601 VALUES [("2026-01-01"), ("2026-02-01")), PARTITION p202602 VALUES [("2026-02-01"), ("2026-03-01")), PARTITION p202603 VALUES [("2026-03-01"), ("2026-04-01")), PARTITION p202604 VALUES [("2026-04-01"), ("2026-05-01")), PARTITION p202605 VALUES [("2026-05-01"), ("2026-06-01")), PARTITION p202606 VALUES [("2026-06-01"), ("2026-07-01")), PARTITION p202607 VALUES [("2026-07-01"), ("2026-08-01")), PARTITION p202608 VALUES [("2026-08-01"), ("2026-09-01")), PARTITION p202609 VALUES [("2026-09-01"), ("2026-10-01")), PARTITION p202610 VALUES [("2026-10-01"), ("2026-11-01")), PARTITION p202611 VALUES [("2026-11-01"), ("2026-12-01")), PARTITION p202612 VALUES [("2026-12-01"), ("2027-01-01")), PARTITION p202701 VALUES [("2027-01-01"), ("2027-02-01")), PARTITION p202702 VALUES [("2027-02-01"), ("2027-03-01")), PARTITION p202703 VALUES [("2027-03-01"), ("2027-04-01"))) DISTRIBUTED BY HASH(
los_app_code
) BUCKETS 32 PROPERTIES ( "compression" = "LZ4", "dynamic_partition.enable" = "true", "dynamic_partition.end" = "6", "dynamic_partition.history_partition_num" = "36", "dynamic_partition.prefix" = "p", "dynamic_partition.start" = "-48", "dynamic_partition.start_day_of_month" = "1", "dynamic_partition.time_unit" = "MONTH", "dynamic_partition.time_zone" = "Asia/Ho_Chi_Minh", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "1" ); CREATE TABLE 
xsell_eligible
( 
report_date
date NOT NULL COMMENT "Ngày báo cáo", 
los_app_code
varchar(30) NOT NULL COMMENT "Mã đơn vay LOS", 
flag_duration_apply
int(11) NULL COMMENT "Cờ thời hạn áp dụng", 
flag_9_payment_period
int(11) NULL COMMENT "Cờ chu kỳ thanh toán thứ 9", 
final_eligible
varchar(26) NULL COMMENT "Trạng thái đủ điều kiện cuối cùng", 
max_amount
bigint(20) NULL COMMENT "Số tiền tối đa", 
product
varchar(26) NULL COMMENT "Sản phẩm", 
bscore_rank
varchar(26) NULL COMMENT "Xếp hạng điểm BSCORE" ) ENGINE=OLAP PRIMARY KEY(
report_date
, 
los_app_code
) PARTITION BY date_trunc('month', 
report_date
) DISTRIBUTED BY HASH(
los_app_code
) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "enable_persistent_index" = "true", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" );
r
The difference in storage size (1.6GB vs 8GB) is primarily driven by three factors: Replication, the Persistent Index, and Primary Key metadata. Here is the breakdown of why the new table occupies more space: 1. Replication (2x Multiplier) * 
xsell_eligible_old
: 
replication_num = 1
. You only store one copy of the data. * 
xsell_eligible
: 
replication_num = 2
. You are storing two full copies of the data across your cluster. * Impact: This immediately doubles the storage requirement. If the old table were also at 2 replicas, it would occupy ~3.2GB. 2. Persistent Index (Significant Overhead) You enabled 
"enable_persistent_index" = "true"
on the new Primary Key table. * How it works: To support fast upserts/deletes, StarRocks maintains a mapping of every Primary Key to its physical location. By enabling the persistent index, this mapping is saved to disk. * Storage Cost: The formula for persistent index storage is roughly: 
(Primary Key Size + 8 bytes) _ Number of Rows _ 0.5 (compression)
* In your case: Your Primary Key is 
(report_date, los_app_code)
. If you have millions of rows, this index can easily add several gigabytes of data to the table's total footprint on disk. 3. Primary Key Metadata & Delete Vectors The Primary Key model is more complex than the Duplicate Key model: * Delete Vectors: StarRocks stores "Delete Vectors" to track which rows have been updated or deleted. These are small but add up. * Hidden Columns: Primary Key tables include internal metadata columns (like 
__op
for operation types and versioning info) to manage row-level updates. * Duplicate Key (Old Table): This is a simple append-only model. It doesn't need an index or delete vectors, making it the most storage-efficient model available in StarRocks. Summary Comparison Feature | 
xsell_eligible_old
(Duplicate) | 
xsell_eligible
(Primary Key) Replicas | 1 copy | 2 copies (2x size) Indexing | No PK Index | Disk-based Persistent Index Upserts | Not supported | Supported (requires metadata) Storage Goal | Maximum compression | Fast updates/deletes Recommendation: If you do not need to perform 
UPDATE
or 
DELETE
operations on this data, the Duplicate Key model (your "old" table) is the correct choice for saving disk space and memory. Only use the Primary Key model if you need real-time data synchronization with upsert logic. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md
l
@Rocky CREATE TABLE 
dtm_los_app_processing_hist_new
( 
ID
varchar(20) COMMENT "", 
SYS_RUN_DATE
datetime NULL COMMENT "", 
PROCESS_UID
varchar(50) NULL COMMENT "", 
APP_UID
varchar(50) NULL COMMENT "", 
APP_NUMBER
decimal(20, 0) NULL COMMENT "", 
APP_CODE
varchar(50) NULL COMMENT "", 
TASK_UID
varchar(50) NULL COMMENT "", 
TASK_INDEX
decimal(5, 0) NULL COMMENT "", 
DELEGATE_DATE
datetime NULL COMMENT "", 
INIT_DATE
datetime NULL COMMENT "", 
FINISH_DATE
datetime NULL COMMENT "", 
CURRENT_USER_1
varchar(50) NULL COMMENT "", 
CURRENT_DECISION
varchar(50) NULL COMMENT "", 
TASK_STATUS
varchar(50) NULL COMMENT "", 
SYS_ACTION
varchar(50) NULL COMMENT "", 
TASK_NAME
varchar(250) NULL COMMENT "", 
TASK_CODE
varchar(50) NULL COMMENT "", 
TIME_PROCESSING_1
decimal(20, 0) NULL COMMENT "", 
TIME_PROCESSING_DEFAULT
decimal(20, 0) NULL COMMENT "", 
LAST_SYN_DATE
datetime NULL COMMENT "", 
TIME_TAT_1
decimal(20, 0) NULL COMMENT "", 
TIME_TAT_DEFAULT
decimal(20, 0) NULL COMMENT "" ) ENGINE=OLAP PRIMARY KEY(
ID
) PARTITION BY date_trunc('month', SYS_RUN_DATE) DISTRIBUTED BY HASH(
SYS_RUN_DATE
) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" ); my table need to have primary key ID, and partition by SYS_RUN_DATE, but can not have partition colume not in key
r
In StarRocks, the Primary Key model has a strict requirement: all columns used in partitioning (
PARTITION BY
) and bucketing (
DISTRIBUTED BY
) must be part of the Primary Key. To fix this, you must include 
SYS_RUN_DATE
in your Primary Key definition. Corrected SQL
Copy code
sql
CREATE TABLE `dtm_los_app_processing_hist_new` (
  `ID` varchar(20) NOT NULL COMMENT "", -- Must be NOT NULL
  `SYS_RUN_DATE` datetime NOT NULL COMMENT "", -- Must be NOT NULL and part of PK
  `PROCESS_UID` varchar(50) NULL COMMENT "",
  `APP_UID` varchar(50) NULL COMMENT "",
  `APP_NUMBER` decimal(20, 0) NULL COMMENT "",
  `APP_CODE` varchar(50) NULL COMMENT "",
  `TASK_UID` varchar(50) NULL COMMENT "",
  `TASK_INDEX` decimal(5, 0) NULL COMMENT "",
  `DELEGATE_DATE` datetime NULL COMMENT "",
  `INIT_DATE` datetime NULL COMMENT "",
  `FINISH_DATE` datetime NULL COMMENT "",
  `CURRENT_USER_1` varchar(50) NULL COMMENT "",
  `CURRENT_DECISION` varchar(50) NULL COMMENT "",
  `TASK_STATUS` varchar(50) NULL COMMENT "",
  `SYS_ACTION` varchar(50) NULL COMMENT "",
  `TASK_NAME` varchar(250) NULL COMMENT "",
  `TASK_CODE` varchar(50) NULL COMMENT "",
  `TIME_PROCESSING_1` decimal(20, 0) NULL COMMENT "",
  `TIME_PROCESSING_DEFAULT` decimal(20, 0) NULL COMMENT "",
  `LAST_SYN_DATE` datetime NULL COMMENT "",
  `TIME_TAT_1` decimal(20, 0) NULL COMMENT "",
  `TIME_TAT_DEFAULT` decimal(20, 0) NULL COMMENT ""
) ENGINE=OLAP
PRIMARY KEY(`ID`, `SYS_RUN_DATE`) -- Added SYS_RUN_DATE here
PARTITION BY date_trunc('month', SYS_RUN_DATE)
DISTRIBUTED BY HASH(`ID`) BUCKETS 8 -- Recommended: Bucket by ID instead of date to avoid skew
PROPERTIES (
    "compression" = "LZ4",
    "fast_schema_evolution" = "true",
    "replicated_storage" = "true",
    "replication_num" = "2"
);
Why this is necessary: 1. Constraint Requirement: StarRocks stores data in "Tablets." To efficiently locate a row for an update (Upsert), it needs to know exactly which partition and which bucket the row belongs to using only the Primary Key. If the partition column isn't in the PK, the system would have to search every single partition to find the row, which is too slow. 2. Uniqueness Semantics: If 
ID
is already unique across your entire dataset, then the combination of 
(ID, SYS_RUN_DATE)
will also be unique. This satisfies the StarRocks requirement without allowing duplicate `ID`s for the same date. 3. Bucketing Suggestion: I changed 
DISTRIBUTED BY HASH(SYS_RUN_DATE)
to 
DISTRIBUTED BY HASH(ID)
. Using a date for bucketing often causes data skew, where one bucket becomes much larger than others if many records share the same date. 
ID
usually has better cardinality. What if you absolutely cannot add the date to the PK? If your logic requires that an 
ID
must be unique across all dates (e.g., an update to an 
ID
should move it from an old date partition to a new one), the Primary Key model cannot support partitioning on a non-PK column. In that case, you have two options: * Remove Partitioning: Keep only the Primary Key on 
ID
and use a large number of buckets. * Use Unique Key Model: While the Primary Key model is faster for updates, the Unique Key model (if used with a 
UNIQUE KEY
definition) has similar constraints in many versions but is more flexible with how data is merged. However, for most StarRocks use cases, the composite PK 
(ID, SYS_RUN_DATE)
is the standard solution. References • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
l
@Rocky what index should add to DTM_LOS_APP_PROCESSING_HIST when query using app_code alot: select * FROM dmapp.DTM_LOS_APP_PROCESSING_HIST where app_code= 'DS4FC702608099766474'
r
To optimize queries that frequently filter by 
app_code
(like 
WHERE app_code = '...'
), the best index to add in StarRocks is a Bloom Filter Index. Why Bloom Filter? * High Cardinality: 
app_code
(like unique IDs) usually has high cardinality (many distinct values). Bloom filters are specifically designed to speed up equality searches (
=
) and 
IN
queries on such columns. * Storage Efficiency: They are very compact and help the system skip scanning data files and pages that definitely do not contain the target value. * Non-Key Column: Since your Primary Key is 
ID
, 
app_code
does not benefit from the automatic Prefix Index. A Bloom Filter fills this gap. How to add it: You can add the index to your existing table using an 
ALTER TABLE
statement:
Copy code
sql
ALTER TABLE dmapp.DTM_LOS_APP_PROCESSING_HIST 
SET ("bloom_filter_columns" = "app_code");
Note: This is an asynchronous operation. You can check the progress using 
SHOW ALTER TABLE COLUMN;
. ────────── Alternative: Prefix Index (Sort Key) If you are recreating the table and 
app_code
is your most frequent filter (even more than 
ID
), you should place it as the first column in the table definition or include it early in the 
PRIMARY KEY
. In StarRocks, the first 36 bytes of the sort key form a Prefix Index. If 
app_code
is the first column, StarRocks can locate the data almost instantly without needing any manual indexes. Comparison for your use case: Index Type | Best For | Recommendation Bloom Filter | High cardinality, equality filters (
=
, 
IN
). | Best choice to add to your current table. Prefix Index | First columns in the table schema. | Best if you can move 
app_code
to the 1st position. Bitmap Index | Low to medium cardinality (e.g., Status, Gender). | Not recommended for unique-ish codes. How to verify it's working: After the index is built, run your query and check the Query Profile. Look for the metric 
BloomFilterFilterRows
. If it shows a high number, it means the index is successfully skipping unnecessary data. References • table_design/indexes/Bloomfilter_index.md • best_practices/query_tuning/schema_tuning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • table_design/indexes/Bitmap_index.md • knowledge/usage/best-practice.md
l
@Rocky alter table dmapp.
dtm_los_app_processing_hist
rename `dtm_los_app_processing_hist_old`; SQL Error [1064] [42000]: Table name[dtm_los_app_processing_hist_old] is already used Table name[dtm_los_app_processing_hist_old] is already used
r
This error occurs because a table (or view) named 
dtm_los_app_processing_hist_old
already exists in your 
dmapp
database. StarRocks does not allow you to rename a table to a name that is already taken. Depending on what you are trying to achieve, here are the three ways to fix this: Option 1: Drop the existing "old" table first If you no longer need the existing 
dtm_los_app_processing_hist_old
table, drop it before running the rename command:
Copy code
sql
DROP TABLE dmapp.dtm_los_app_processing_hist_old;

ALTER TABLE dmapp.dtm_los_app_processing_hist 
RENAME dtm_los_app_processing_hist_old;
Option 2: Use an Atomic SWAP (Recommended for ETL) If you have a "new" version of the table and you want it to become the "live" version while moving the current "live" version to "old," the most efficient way is a SWAP. This is an atomic operation and ensures no downtime.
Copy code
sql
-- This swaps the names of the two tables instantly
ALTER TABLE dmapp.dtm_los_app_processing_hist 
SWAP WITH dmapp.dtm_los_app_processing_hist_old;
_Note: After this command, your new data is in 
dtm_los_app_processing_hist
and your old data is in 
dtm_los_app_processing_hist_old
._ Option 3: Choose a different name If you want to keep the existing old table, choose a unique name for the rename (e.g., add a date suffix):
Copy code
sql
ALTER TABLE dmapp.dtm_los_app_processing_hist 
RENAME dtm_los_app_processing_hist_backup_20231027;
How to verify what exists: You can check all tables with similar names using:
Copy code
sql
SHOW TABLES FROM dmapp LIKE 'dtm_los_app_processing_hist%';
References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • faq/Others.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • knowledge/usage/best-practice.md • faq/Others.md
3 Views
Open in Slack
PreviousNext
Powered by