http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hôle
r
Hello! I'm Rocky. How can I help you with StarRocks today?
l
@Rocky INSERT INTO 
losrep
.
application
by name SELECT * FROM 
dmapp
.
losrep_application
a where a.id > (select MAX(b.id ) from 
losrep
.
application
b) or a.last_updated_date > (select DATE_SUB(MAX(c.last_updated_date), INTERVAL 1 DAY) from losrep.application c) this query will run correct if application tabe have same colume as source losrep_application table, if source table add more colume, then this will failled, how to by pass new colume from source, but still using select *
r
To ensure your 
INSERT INTO ... BY NAME
query is resilient to new columns added to the source table while still using a wildcard-style selection, you have a few options depending on your StarRocks version. *1. Use 
SELECT * EXCLUDE
(StarRocks v4.0+)* Starting with StarRocks v4.0, you can use the 
EXCLUDE
keyword to select all columns except for the ones you want to ignore. This allows you to "bypass" specific new columns without listing every single desired column:
Copy code
sql
INSERT INTO `losrep`.`application` BY NAME 
SELECT * EXCLUDE (new_column_name) 
FROM `dmapp`.`losrep_application` a 
WHERE a.id > (SELECT MAX(b.id) FROM `losrep`.`application` b)
   OR a.last_updated_date > (SELECT DATE_SUB(MAX(c.last_updated_date), INTERVAL 1 DAY) FROM losrep.application c);
2. Explicit Column Listing (Recommended Best Practice) For versions prior to v4.0, StarRocks' 
BY NAME
clause requires the columns produced by the 
SELECT
statement to have a corresponding home in the target table. If 
SELECT *
returns more columns than the target table contains, the query will likely fail with a column count mismatch or mapping error. The most robust way to prevent failures when the source schema changes is to explicitly list the columns in the 
SELECT
statement. This ensures that only the columns that exist in both tables are ever pulled:
Copy code
sql
INSERT INTO `losrep`.`application` BY NAME 
SELECT id, last_updated_date, col1, col2 -- explicitly list columns
FROM `dmapp`.`losrep_application` a 
...
3. Note on 
FILES()
vs. Internal Tables If you were loading data from external files (like S3/HDFS) using the 
FILES()
function, StarRocks provides a property called 
enable_push_down_schema
. When set to 
true
, 
SELECT *
is automatically "reshaped" to only include columns that exist in the target table:
Copy code
sql
-- This works only for FILES(), not currently for table-to-table
INSERT INTO target_table PROPERTIES ("enable_push_down_schema" = "true") 
SELECT * FROM FILES(...);
For internal table-to-table transfers (as in your case), this automatic "reshaping" is not yet standard for regular 
SELECT *
. Summary Recommendation * If you are on v4.0+: Use 
SELECT * EXCLUDE (names_of_new_columns)
. * If you want full automation: There is currently no "auto-trim" property for table-to-table 
INSERT
that ignores unknown columns in a 
SELECT *
. You should list the specific columns you need. This is generally preferred for production pipelines as it prevents silent data loss or unexpected behavior when schemas evolve. * Alternative: You can create a View on the source table that only exposes the columns currently in the destination. If the source table adds columns, the View remains stable and the 
INSERT
won't fail. References • administration/management/FE_configuration.md • loading/InsertInto.md • sql-reference/sql-functions/table-functions/files.md
l
@Rocky Use 
SELECT * EXCLUDE (names_of_new_columns), this way not work, as i don't known new colume name in future
r
To handle a changing source schema where new columns are added but you still want to use a 
SELECT *
style query without it failing, there are a few strategies in StarRocks. The Problem When you use 
INSERT INTO ... BY NAME SELECT _ FROM source_table
, StarRocks expands the 
_
to all columns currently in the source table. If the source table adds a new column that does not exist in the target table, the query will fail because the result set of the 
SELECT
statement contains columns that the 
INSERT
engine doesn't know where to place. Solution 1: Use a Stable View (Recommended for Automation) If you want to avoid updating your SQL query every time the source changes, you can create a View that acts as a filter. This allows your 
INSERT
query to remain 
SELECT *
while the View handles the column mapping. 1. Create a view that selects only the columns that currently exist in your destination table:
Copy code
sql
    CREATE VIEW losrep.v_application_source AS
    SELECT id, last_updated_date, col1, col2 -- list only destination columns
    FROM dmapp.losrep_application;
2. Change your query to select from the view:
Copy code
sql
    INSERT INTO `losrep`.`application` BY NAME
    SELECT * FROM `losrep`.`v_application_source` a
    WHERE a.id > (...) OR a.last_updated_date > (...);
Why this works: If the source table 
dmapp.losrep_application
adds a new column, the View remains stable and ignores it. Your 
INSERT
query will continue to run correctly. You only need to update the View definition if you actually want to start including a new column in your destination. Solution 2: Explicit Column Selection (Best Practice) In production ETL pipelines, it is generally considered a best practice to explicitly list the columns in your 
SELECT
statement rather than using 
*
. While it requires more typing initially, it makes your pipeline "immune" to schema changes in the source table.
Copy code
sql
INSERT INTO `losrep`.`application` BY NAME 
SELECT id, last_updated_date, col1, col2 -- Explicitly list columns
FROM `dmapp`.`losrep_application` a 
...
*Solution 3: StarRocks v4.0+ `SELECT * EXCLUDE`* If you are on StarRocks v4.0 or later, you can use the 
EXCLUDE
keyword. However, this still requires you to know the names of the columns you want to ignore.
Copy code
sql
SELECT * EXCLUDE (new_col1, new_col2) FROM ...
Since you mentioned you won't know the new column names in the future, Solution 1 (The View) is your best option for a "set and forget" approach. Note on 
FILES()
vs Internal Tables It's worth noting that StarRocks has a feature for loading from external files (like S3/HDFS) where 
SELECT *
is automatically trimmed to match the target table when using 
BY NAME
. This behavior is not yet the default for internal table-to-table 
INSERT
operations, which is why your query is currently failing. Summary * Best for automation: Create a View with a fixed column list and use 
SELECT *
on that View. * Most robust: Explicitly list your columns in the 
SELECT
statement. * Avoid: Using 
SELECT *
directly on a table that is expected to grow its schema beyond the target table's schema. References • loading/InsertInto.md
l
@Rocky how to create index in starrock tables, sample detail
r
StarRocks provides several types of indexes to optimize query performance. These range from automatic internal indexes to specialized secondary indexes. 1. Prefix Index (Sparse Index) This is StarRocks' most important built-in index. It is created automatically based on the columns defined in the 
ORDER BY
(or 
KEY
) clause. It works best for range queries and prefix filtering. * Rule: The first 36 bytes of the sort key are used to create this sparse index. * Sample (during Table Creation):
Copy code
sql
    CREATE TABLE sales_records (
        event_time DATETIME NOT NULL,
        site_id INT DEFAULT '1',
        city_code VARCHAR(20),
        revenue BIGINT SUM
    )
    ENGINE=OLAP
    AGGREGATE KEY(event_time, site_id, city_code) -- Sort Key definition
    DISTRIBUTED BY HASH(site_id);
_In this example, filtering by 
event_time
or 
event_time + site_id
will be extremely fast._ ────────── 2. Bitmap Index Best for low-cardinality columns (columns with few unique values, like 
gender
, 
province
, or 
order_status
). * Sample (New Table):
Copy code
sql
    CREATE TABLE users (
        user_id INT,
        gender VARCHAR(10),
        INDEX idx_gender (gender) USING BITMAP
    ) DISTRIBUTED BY HASH(user_id);
* Sample (Existing Table):
Copy code
sql
    CREATE INDEX idx_gender ON users (gender) USING BITMAP;
    -- OR
    ALTER TABLE users ADD INDEX idx_gender (gender) USING BITMAP;
────────── 3. Bloom Filter Index Best for high-cardinality columns (columns with many unique values like 
user_id
or 
order_id
) where you perform many point queries (e.g., 
WHERE id = 123
). * Sample (New Table):
Copy code
sql
    CREATE TABLE orders (
        order_id BIGINT,
        customer_id BIGINT
    )
    DISTRIBUTED BY HASH(order_id)
    PROPERTIES ("bloom_filter_columns" = "order_id, customer_id");
* Sample (Existing Table):
Copy code
sql
    ALTER TABLE orders SET ("bloom_filter_columns" = "order_id, customer_id");
────────── 4. Full-Text Inverted Index (GIN) Available in newer versions (v3.3+), this is used for high-performance text searching, similar to Elasticsearch. * Sample:
Copy code
sql
    CREATE TABLE logs (
        id BIGINT,
        log_message STRING,
        INDEX idx_message (log_message) USING GIN ("parser" = "english")
    ) DISTRIBUTED BY HASH(id);
────────── 5. N-Gram Bloom Filter Index Optimizes 
LIKE
queries for string columns (e.g., 
WHERE col LIKE '%keyword%'
). * Sample:
Copy code
sql
    ALTER TABLE my_table
    ADD INDEX idx_ngram (column_name)
    USING NGRAMBF ("gram_num" = "4", "bloom_filter_size" = "1024");
────────── 6. Vector Index Used for AI and similarity search (e.g., finding similar images or text embeddings). * Sample:
Copy code
sql
    CREATE TABLE image_vectors (
        id BIGINT,
        embedding ARRAY<FLOAT>,
        INDEX idx_vector (embedding) USING VECTOR (
            "index_type" = "hnsw",
            "metric_type" = "l2_distance",
            "dim" = "512"
        )
    ) DISTRIBUTED BY HASH(id);
Summary Table Index Type | Best For | Requirement Prefix Index | Range queries on sort keys | Automatic (via 
ORDER BY
) Bitmap | Low-cardinality columns | 
USING BITMAP
Bloom Filter | High-cardinality point queries | 
PROPERTIES
or 
SET
GIN | Full-text search | 
USING GIN
N-Gram | 
LIKE
queries | 
USING NGRAMBF
Vector | Embedding similarity | 
USING VECTOR
Note: You can check existing indexes on a table using:
Copy code
sql
SHOW INDEX FROM table_name;
-- or
SHOW CREATE TABLE table_name;
References • sql-reference/sql-statements/table_bucket_part_index/CREATE_INDEX.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
l
@Rocky SELECT ... INTO OUTFILE detail what this mean how to used this
r
In StarRocks, the 
SELECT ... INTO OUTFILE
statement is used to synchronously export the results of a query to a file in remote storage (such as AWS S3, HDFS, or Azure Blob Storage). Unlike the 
EXPORT
command which runs in the background (asynchronously), 
INTO OUTFILE
is a blocking operation—your SQL client will wait until the export is finished before returning a result. 1. Basic Syntax
Copy code
sql
SELECT column1, column2, ...
FROM table_name
INTO OUTFILE "<s3://bucket/path/file_prefix_>"
[FORMAT AS CSV]
[PROPERTIES (
    "key" = "value",
    ...
)];
2. Key Parameters * Destination Path: A URI starting with 
s3://
, 
hdfs://
, 
ks3://
, etc. If the path ends with a string (not a 
/
), that string is used as the prefix for the generated files. * FORMAT: Usually 
CSV
. (Note: Some versions also support Parquet via newer unloading methods like 
INSERT INTO FILES
). * PROPERTIES: Used to define storage credentials and file formatting: * Column Separator: 
"column_separator" = ","
* Line Delimiter: 
"line_delimiter" = "\n"
* Storage Credentials: Required to access your cloud storage (e.g., 
aws.s3.access_key
). 3. Usage Examples Exporting to AWS S3 This example exports query results as a CSV to an S3 bucket:
Copy code
sql
SELECT id, name, price 
FROM sales_data
INTO OUTFILE "<s3://my-bucket/exports/sales_2023_>"
FORMAT AS CSV
PROPERTIES (
    "column_separator" = ",",
    "line_delimiter" = "\n",
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "aws.s3.region" = "us-east-1"
);
Exporting to HDFS
Copy code
sql
SELECT * FROM users
INTO OUTFILE "<hdfs://namenode:9000/user/data/users_export_>"
FORMAT AS CSV
PROPERTIES (
    "column_separator" = "\t",
    "hadoop.security.authentication" = "simple",
    "username" = "hdfs_user"
);
4. Important Considerations * Synchronous: The connection remains open until the file is written. This is great for scripts but can timeout on very large exports. For massive datasets, use the 
EXPORT
command or 
INSERT INTO FILES
. * File Splitting: If the result set is large, StarRocks will automatically split the data into multiple files (e.g., 
sales_2023_0.csv
, 
sales_2023_1.csv
). * Overwrite: By default, StarRocks will not overwrite existing files. If files with the same name already exist in the destination, the query will fail. * Local Files: You cannot export directly to a local disk on the FE/BE nodes via this command. To save to local disk, you typically use a standard SQL client's redirect (e.g., 
mysql -e "SELECT..." > file.csv
) or mount a network drive (NFS) and treat it as a file protocol. 5. Modern Alternative (v3.2+) If you are on StarRocks v3.2 or later, it is recommended to use 
INSERT INTO FILES()
, which provides a more flexible interface for unloading data:
Copy code
sql
INSERT INTO FILES(
    "path" = "<s3://my-bucket/unload/>",
    "format" = "parquet",
    "aws.s3.access_key" = "xxx",
    "aws.s3.secret_key" = "yyy"
)
SELECT * FROM my_table;
References • unloading/unload_using_insert_into_files.md • sql-reference/sql-statements/loading_unloading/unloading/EXPORT.md • sql-reference/sql-functions/table-functions/files.md References • unloading/unload_using_insert_into_files.md • sql-reference/sql-functions/table-functions/files.md • sql-reference/sql-statements/loading_unloading/unloading/EXPORT.md
l
@Rocky if starrock cluster size is 10 TB, when backup full cluste to MinIO, what size of storage in MinIO
r
When backing up a StarRocks cluster to MinIO (or any S3-compatible storage), the final storage size in MinIO depends on how you define "10 TB cluster size." Here is the breakdown of how StarRocks calculates backup storage: 1. One Replica Only (The 1/3 Factor) In a typical StarRocks cluster, data is stored with 3 replicas by default for high availability. * If your "10 TB" refers to Disk Usage: If your BE nodes show a total of 10 TB of used disk space, this usually includes all 3 replicas. Since a backup only needs to store one copy of the data to be valid, the size in MinIO would be approximately 3.3 TB. * If your "10 TB" refers to Logical Data: If you have 10 TB of unique data (which would occupy ~30 TB on disk with 3 replicas), the backup in MinIO will be approximately 10 TB. 2. Compression StarRocks stores data on disk in a compressed format (using LZ4, Zstandard, or Snappy by default). * The 
BACKUP
command uploads these already-compressed segment files directly to MinIO. * There is no additional compression performed during the backup process itself, but since the source files are already compressed, the storage in MinIO will reflect that same high compression ratio (typically 3:1 to 5:1 compared to raw text/CSV). 3. Metadata and Snapshots The 
BACKUP
process creates a snapshot. This includes: * Segment files: The bulk of the data. * Metadata files: Very small files (kilobytes to megabytes) describing the table schema and partition info. * Version history: StarRocks backups are snapshots of a specific point in time. If you run multiple backups, each one is independent (unless using specific incremental partition strategies manually), so each full backup will take up the same amount of space. Summary Table Your Scenario | Physical Disk Usage (BEs) | Estimated MinIO Storage 10 TB Disk (3x Replicas) | 10 TB | ~3.3 TB 10 TB Logical Data | 30 TB | ~10 TB Important Notes * Granularity: As of recent versions (v3.4+), StarRocks supports backing up at the Database, Table, or Partition level. To back up a "full cluster," you would typically perform a backup at the database level for each of your databases. * Shared-Data (Cloud-Native): If you are using the Shared-data mode (where data already lives in S3/MinIO), the 
BACKUP
and 
RESTORE
commands work differently or may not be supported in the same way, as the primary storage is already remote. How to check your actual data size: Run this query to see the logical data size (excluding replicas):
Copy code
sql
SELECT sum(DATA_LENGTH) FROM information_schema.tables;
The result of this query is the best estimate for your backup size in MinIO. References • administration/management/Backup_and_restore.md • deployment/plan_cluster.md • sql-reference/sql-statements/backup_restore/BACKUP.md References • deployment/plan_cluster.md • administration/management/Backup_and_restore.md • faq/Exporting_faq.md
l
@Rocky UPDATE dmapp.dtm_customer_ld_summary SET max_customer_status_1_year = a.max_customer_status_1_year FROM dwh.dwh_customer_ld_summary_upd2 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 need update one more colume max_customer_status_3_year
r
To update multiple columns in StarRocks using a 
FROM
clause, you simply separate the assignments in the 
SET
clause with a comma. Here is the updated SQL:
Copy code
sql
UPDATE dmapp.dtm_customer_ld_summary
SET 
    max_customer_status_1_year = a.max_customer_status_1_year,
    max_customer_status_3_year = a.max_customer_status_3_year -- Added this line
FROM dwh.dwh_customer_ld_summary_upd2 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;
Key Requirements for Update: 1. Table Type: This syntax works best on tables with a Primary Key model. If your table uses the Duplicate or Aggregate model, ensure your StarRocks version and configuration support multi-table updates (most recent versions do). 2. Join Columns: Ensure that 
sys_run_date
and 
ld_no
are part of the primary key for the best performance. 3. Column Names: Make sure the column 
max_customer_status_3_year
exists in both the target and the source table (or adjust the alias 
a.
if the source column name is different). References • sql-reference/sql-statements/data-manipulation/UPDATE.md • table_design/table_types/primary_key_table.md
l
@Rocky sp_merge_monthly_dpd_bymonth 2026-06-15 113924 PERIODICAL START(2025-01-01T08:50) EVERY(1 DAYS) default_catalog modelling "INSERT INTO modelling.MONTHLY_DPD ( REPORT_MONTH, LD_NO, CONTRACT_NO, RUN_DATE, MAX_DPD_IN_MONTH, MAX_CA_ACCT_BALANCE, LAST_PAYMENT_DATE, DUE_DATE_OF_MON ) SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m') AS REPORT_MONTH, LD_NO, CONTRACT_NO, NOW() AS RUN_DATE, MAX(OVER_DUE_DAYS_LD) AS MAX_DPD_IN_MONTH, MIN(CA_ACCT_BALANCE) AS MAX_CA_ACCT_BALANCE, MAX(LAST_PAYMENT_DATE) AS LAST_PAYMENT_DATE, MIN(DUE_DATE_OF_MON) AS DUE_DATE_OF_MON FROM dmapp.DTM_CUSTOMER_LD_SUMMARY WHERE SYS_RUN_DATE >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-01') AND SYS_RUN_DATE <= DATE_SUB(CURDATE(), INTERVAL 1 DAY) GROUP BY LD_NO, CONTRACT_NO" change this task sp_merge_monthly_dpd_bymonth from 1 day to first day every month
r
To change your task 
sp_merge_monthly_dpd_bymonth
to run on the first day of every month instead of every day, you need to drop the existing task and resubmit it with the updated schedule. StarRocks (as of current versions) does not support a direct 
ALTER TASK ... SCHEDULE
statement to change the interval. Steps to modify the task: 1. Drop the existing task:
Copy code
sql
    DROP TASK sp_merge_monthly_dpd_bymonth;
2. Submit the task with the new monthly schedule: Use 
EVERY(INTERVAL 1 MONTH)
and set the 
START
time to the 1st of the month (the scheduler will use this to align all future runs to the 1st).
Copy code
sql
    SUBMIT TASK sp_merge_monthly_dpd_bymonth
    SCHEDULE START('2025-01-01 08:50:00') EVERY(INTERVAL 1 MONTH)
    AS
    INSERT INTO modelling.MONTHLY_DPD (
        REPORT_MONTH,
        LD_NO,
        CONTRACT_NO,
        RUN_DATE,
        MAX_DPD_IN_MONTH,
        MAX_CA_ACCT_BALANCE,
        LAST_PAYMENT_DATE,
        DUE_DATE_OF_MON
    )
    SELECT
        DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m') AS REPORT_MONTH,
        LD_NO,
        CONTRACT_NO,
        NOW()                        AS RUN_DATE,
        MAX(OVER_DUE_DAYS_LD)        AS MAX_DPD_IN_MONTH,
        MIN(CA_ACCT_BALANCE)         AS MAX_CA_ACCT_BALANCE,
        MAX(LAST_PAYMENT_DATE)       AS LAST_PAYMENT_DATE,
        MIN(DUE_DATE_OF_MON)         AS DUE_DATE_OF_MON
    FROM dmapp.DTM_CUSTOMER_LD_SUMMARY
    WHERE SYS_RUN_DATE >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-01')
      AND SYS_RUN_DATE <= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
    GROUP BY LD_NO, CONTRACT_NO;
Why this works: * Schedule Alignment: By setting 
EVERY(INTERVAL 1 MONTH)
and a 
START
date that falls on the 1st (like 
2025-01-01
), the task will automatically run on the 1st of every subsequent month. * Date Logic: Your existing 
WHERE
clause is already perfect for a monthly run. When the task runs on the 1st of July, 
DATE_SUB(CURDATE(), INTERVAL 1 DAY)
becomes the last day of June. The logic then filters 
SYS_RUN_DATE
from the 1st of June to the 30th of June, correctly aggregating the entire previous month's data. Notes: * Version Note: If you are using StarRocks v4.1 or later, you can check the 
ALTER TASK
command, but currently, it is primarily used to 
SUSPEND
, 
RESUME
, or modify session properties (like timeouts), not the core schedule interval. * Verification: After submitting, you can verify the next scheduled run time by querying:
Copy code
sql
    SELECT * FROM INFORMATION_SCHEMA.tasks WHERE task_name = 'sp_merge_monthly_dpd_bymonth';
References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/sql-statements/loading_unloading/ETL/ALTER_TASK.md • administration/management/FE_configuration.md References • sql-reference/sql-statements/loading_unloading/ETL/ALTER_TASK.md • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • release_notes/release-3.3.md
l
@Rocky SQL Error [1064] [42000]: Getting syntax error at line 2, column 55. Detail message: Unexpected input 'MONTH', the most similar input is {'MINUTE', 'SECOND', 'HOUR', 'DAY'}. Getting syntax error at line 2, column 55. Detail message: Unexpected input 'MONTH', the most similar input is {'MINUTE', 'SECOND', 'HOUR', 'DAY'}.
r
The syntax error occurs because the StarRocks task scheduler only supports 
SECOND
, 
MINUTE
, 
HOUR
, and 
DAY
 as units for the 
EVERY(INTERVAL ...)
clause. The 
MONTH
 unit is currently not supported in the 
SUBMIT TASK
syntax. To achieve a monthly run on the first day of every month, you can use one of the following workarounds: Workaround 1: Run Daily with "First of Month" Logic You can keep the task running every day, but add a condition in your 
WHERE
clause so that it only processes data if today is the 1st of the month. On all other days, the task will technically run but insert 0 rows, consuming negligible resources.
Copy code
sql
SUBMIT TASK sp_merge_monthly_dpd_bymonth
SCHEDULE START('2025-01-01 08:50:00') EVERY(INTERVAL 1 DAY)
AS
INSERT INTO modelling.MONTHLY_DPD (
    REPORT_MONTH, LD_NO, CONTRACT_NO, RUN_DATE, 
    MAX_DPD_IN_MONTH, MAX_CA_ACCT_BALANCE, LAST_PAYMENT_DATE, DUE_DATE_OF_MON
)
SELECT
    DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m') AS REPORT_MONTH,
    LD_NO, CONTRACT_NO, NOW() AS RUN_DATE,
    MAX(OVER_DUE_DAYS_LD), MIN(CA_ACCT_BALANCE), MAX(LAST_PAYMENT_DATE), MIN(DUE_DATE_OF_MON)
FROM dmapp.DTM_CUSTOMER_LD_SUMMARY
WHERE DAY(CURDATE()) = 1  -- This ensures the query only selects data on the 1st of the month
  AND SYS_RUN_DATE >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-01')
  AND SYS_RUN_DATE <= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY LD_NO, CONTRACT_NO;
Workaround 2: Use an External Scheduler If you require a strict monthly schedule without daily "empty" runs, StarRocks' recommendation is to use an external orchestrator like Apache Airflow, DolphinScheduler, or a standard Linux crontab. Example for a Linux crontab (runs at 08:50 on the 1st of every month):
Copy code
bash
50 08 1 * * mysql -h <FE_IP> -P 9030 -u <user> -p<pass> -e "CALL your_stored_procedure_or_sql_logic;"
Why 
MONTH
is not supported The internal scheduler for 
SUBMIT TASK
(and similarly for Materialized Views) is designed for fixed-length intervals. Since months have varying lengths (28, 30, 31 days), a simple interval-based "Every 1 Month" would drift relative to the calendar date. Summary of Units Unit | Valid in Task Schedule? 
SECOND
| Yes 
MINUTE
| Yes 
HOUR
| Yes 
DAY
| Yes 
MONTH
| No 
YEAR
| No References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/sql-functions/date-time-functions/day.md • integrations/airflow.md References • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • using_starrocks/Cost_based_optimizer.md • using_starrocks/caching/query_cache.md
l
@Rocky ALTER table monthly_dpd rename monthly_dpd_old; ALTER table monthly_dpd_new rename monthly_dpd correct?
r
Yes, your syntax is correct.
Copy code
sql
ALTER TABLE monthly_dpd RENAME monthly_dpd_old;
ALTER TABLE monthly_dpd_new RENAME monthly_dpd;
Pro Tip: Atomic Swap If your goal is to replace an old table with a new one without any "downtime" (where the table name doesn't exist for a split second), StarRocks provides an atomic swap command. This is safer because it ensures that 
monthly_dpd
is always available to queries:
Copy code
sql
-- This one command replaces 'monthly_dpd' with 'monthly_dpd_new' 
-- and moves the old data to 'monthly_dpd_new' simultaneously.
ALTER TABLE monthly_dpd SWAP WITH monthly_dpd_new;
After the 
SWAP
, you can then rename the remaining 
monthly_dpd_new
(which now contains the old data) to 
monthly_dpd_old
if you wish to keep it as a backup. Summary * Your Method: Correct, but results in a tiny window where the table name is missing. * SWAP Method: Recommended for production to avoid query failures during the rename process. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/Database/ALTER_DATABASE.md
l
@Rocky create masterial view async like this view CREATE OR REPLACE VIEW 
ep_transaction_loan
(
tran_id
, 
action
, 
action_type
, 
amount
, 
create_date
, 
create_user
, 
currentcy
, 
description
, 
edit_date
, 
edit_user
, 
fee_amount
, 
from_acount
, 
merchant_id
, 
partner_code
, 
product_id
, 
request_id
, 
request_message
, 
response_code
, 
response_des
, 
response_message
, 
status
, 
to_acount
, 
tran_no
, 
transaction_date
, 
type
, 
vat_amount
, 
partner_tran_no
) SECURITY NONE AS SELECT 
dbmcman
.
ep_transaction
.
tran_id
, 
dbmcman
.
ep_transaction
.
action
, 
dbmcman
.
ep_transaction
.
action_type
, 
dbmcman
.
ep_transaction
.
amount
, 
dbmcman
.
ep_transaction
.
create_date
, 
dbmcman
.
ep_transaction
.
create_user
, 
dbmcman
.
ep_transaction
.
currentcy
, 
dbmcman
.
ep_transaction
.
description
, 
dbmcman
.
ep_transaction
.
edit_date
, 
dbmcman
.
ep_transaction
.
edit_user
, 
dbmcman
.
ep_transaction
.
fee_amount
, 
dbmcman
.
ep_transaction
.
from_acount
, 
dbmcman
.
ep_transaction
.
merchant_id
, 
dbmcman
.
ep_transaction
.
partner_code
, 
dbmcman
.
ep_transaction
.
product_id
, 
dbmcman
.
ep_transaction
.
request_id
, 
dbmcman
.
ep_transaction
.
request_message
, 
dbmcman
.
ep_transaction
.
response_code
, 
dbmcman
.
ep_transaction
.
response_des
, 
dbmcman
.
ep_transaction
.
response_message
, 
dbmcman
.
ep_transaction
.
status
, 
dbmcman
.
ep_transaction
.
to_acount
, 
dbmcman
.
ep_transaction
.
tran_no
, 
dbmcman
.
ep_transaction
.
transaction_date
, 
dbmcman
.
ep_transaction
.
type
, 
dbmcman
.
ep_transaction
.
vat_amount
, 
dbmcman
.
ep_transaction
.
partner_tran_no
FROM 
dbmcman
.
ep_transaction
WHERE (((
dbmcman
.
ep_transaction
.
type
= 'LOAN') AND (
dbmcman
.
ep_transaction
.
action_type
= 'OUT')) AND (
dbmcman
.
ep_transaction
.
response_code
= '00')) AND (
dbmcman
.
ep_transaction
.
action
= 'EPG71');
r
To create an Asynchronous Materialized View (MV) in StarRocks based on your logical view, you can use the following SQL. I have added a refresh schedule (e.g., every 1 hour) and a distribution key. Since this is transaction data, it is highly recommended to partition the MV by a date column (like 
transaction_date
) to improve performance and allow for incremental refreshes. Option 1: Basic Asynchronous MV This version refreshes the entire dataset based on the schedule you define.
Copy code
sql
CREATE MATERIALIZED VIEW ep_transaction_loan_mv
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
DISTRIBUTED BY HASH(`tran_id`)
AS
SELECT
    `tran_id`,
    `action`,
    `action_type`,
    `amount`,
    `create_date`,
    `create_user`,
    `currentcy`,
    `description`,
    `edit_date`,
    `edit_user`,
    `fee_amount`,
    `from_acount`,
    `merchant_id`,
    `partner_code`,
    `product_id`,
    `request_id`,
    `request_message`,
    `response_code`,
    `response_des`,
    `response_message`,
    `status`,
    `to_acount`,
    `tran_no`,
    `transaction_date`,
    `type`,
    `vat_amount`,
    `partner_tran_no`
FROM `dbmcman`.`ep_transaction`
WHERE `type` = 'LOAN'
  AND `action_type` = 'OUT'
  AND `response_code` = '00'
  AND `action` = 'EPG71';
Option 2: Optimized (Partitioned) MV If your base table 
dbmcman.ep_transaction
is partitioned by 
transaction_date
, you should also partition the Materialized View. This allows StarRocks to refresh only the changed days rather than the whole table.
Copy code
sql
CREATE MATERIALIZED VIEW ep_transaction_loan_mv
PARTITION BY (`transaction_date`) -- Enables incremental refresh
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
DISTRIBUTED BY HASH(`tran_id`)
AS
SELECT
    `tran_id`,
    -- ... (include all other columns here)
    `transaction_date`
FROM `dbmcman`.`ep_transaction`
WHERE `type` = 'LOAN'
  AND `action_type` = 'OUT'
  AND `response_code` = '00'
  AND `action` = 'EPG71';
Key Differences from a Logical View: 1. Storage: Unlike a regular View, an MV stores the data on disk. 2. Refresh Strategy: 
REFRESH ASYNC
tells StarRocks to update the data in the background. You can specify 
EVERY (INTERVAL 1 DAY)
, 
1 HOUR
, etc. 3. Distribution: You must specify 
DISTRIBUTED BY HASH(...)
to define how data is spread across the cluster. 
tran_id
is usually a good choice for a unique transaction ID. 4. Security: StarRocks MVs use the privileges of the user who created the MV. The 
SECURITY NONE
syntax from your logical view is not used in StarRocks MV syntax. How to check status: After creating it, you can check the refresh status with:
Copy code
sql
SELECT * FROM information_schema.materialized_views WHERE mv_name = 'ep_transaction_loan_mv'\G
-- To see refresh history: SELECT * FROM information_schema.tasks_history WHERE task_name LIKE '%ep_transaction_loan_mv%'; ``` ## References - [sql-reference/sql-statements/table_bucket_part_index/CREATE_MATERIALIZED_VIEW.md](https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_MATERIALIZED_VIEW) - [using_starrocks/async_mv/use_cases.md](https://docs.starrocks.io/docs/using_starrocks/async_mv/use_cases) - [using_starrocks/async_mv/materialized_view.md](https://docs.starrocks.io/docs/using_starrocks/async_mv/materialized_view)
l
@Rocky how to have sync masterial view, as when table update, view auto update
r
To create a Synchronous Materialized View (Sync MV) that updates automatically and simultaneously with the base table, you use a specific syntax that differs from the Asynchronous version. In StarRocks, a Sync MV is essentially an "index" of the base table. Whenever data is loaded into the base table, it is also written to the MV in the same transaction, ensuring the data is always "sync" (ACID consistent). 1. Requirements for Sync MVs * Single Table Only: You cannot use 
JOIN
or query multiple tables. * Default Catalog: The base table must be in the 
default_catalog
. * Version: You must be on v3.1.8 or higher to use the 
WHERE
clause in a Sync MV. 2. Syntax for your Query Since your view is a filtered subset of a single table (
dbmcman.ep_transaction
), you can create a Sync MV like this:
Copy code
sql
CREATE MATERIALIZED VIEW ep_transaction_loan_sync_mv
AS
SELECT
    tran_id,
    action,
    action_type,
    amount,
    create_date,
    create_user,
    currentcy,
    description,
    edit_date,
    edit_user,
    fee_amount,
    from_acount,
    merchant_id,
    partner_code,
    product_id,
    request_id,
    request_message,
    response_code,
    response_des,
    response_message,
    status,
    to_acount,
    tran_no,
    transaction_date,
    type,
    vat_amount,
    partner_tran_no
FROM `dbmcman`.`ep_transaction`
WHERE `type` = 'LOAN'
  AND `action_type` = 'OUT'
  AND `response_code` = '00'
  AND `action` = 'EPG71';
3. How it Works * Auto-Update: You do not need to set a refresh schedule. StarRocks handles it automatically during every 
INSERT
or 
LOAD
to the 
ep_transaction
table. * Query Acceleration (Query Rewrite): You don't usually query the MV name directly. Instead, you continue querying your original table or view. StarRocks' optimizer will detect that the MV already contains the filtered results and automatically rewrite your query to read from the MV instead of the large base table. * Direct Query: If you want to force a query to hit the Sync MV, use the hint:
Copy code
sql
    SELECT * FROM ep_transaction_loan_sync_mv [_SYNC_MV_];
Important Considerations 1. Loading Performance: Because the update is synchronous, adding this MV will slightly increase the time it takes to load data into the base table (as it has to write to two places at once). 2. Storage: This creates a filtered copy of the data on disk. 3. Check Status: Creating a Sync MV is an asynchronous task. You can check if it's finished building with:
Copy code
sql
    SHOW ALTER MATERIALIZED VIEW;
References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/Materialized_view-single_table.md • best_practices/query_tuning/query_planning.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
l
@Rocky SQL Error [1064] [42000]: Do not support create materialized view on primary key table[ep_transaction] Do not support create materialized view on primary key table[ep_transaction]
r
The error occurs because StarRocks Synchronous Materialized Views (also known as Rollups) do not support Primary Key tables. They are only compatible with Duplicate Key and Aggregate tables. To achieve an "auto-updating" view on a Primary Key table, you should use an Asynchronous Materialized View with the 
REFRESH ASYNC
strategy. In StarRocks, 
REFRESH ASYNC
(without a specific time interval) acts as an "on-change" trigger that refreshes the MV whenever data in the base table is updated. Recommended Solution Use the following syntax. Note that Asynchronous MVs require a 
DISTRIBUTED BY
clause:
Copy code
sql
CREATE MATERIALIZED VIEW ep_transaction_loan_mv
REFRESH ASYNC
DISTRIBUTED BY HASH(`tran_id`)
AS
SELECT
    `tran_id`,
    `action`,
    `action_type`,
    `amount`,
    `create_date`,
    `create_user`,
    `currentcy`,
    `description`,
    `edit_date`,
    `edit_user`,
    `fee_amount`,
    `from_acount`,
    `merchant_id`,
    `partner_code`,
    `product_id`,
    `request_id`,
    `request_message`,
    `response_code`,
    `response_des`,
    `response_message`,
    `status`,
    `to_acount`,
    `tran_no`,
    `transaction_date`,
    `type`,
    `vat_amount`,
    `partner_tran_no`
FROM `dbmcman`.`ep_transaction`
WHERE `type` = 'LOAN'
  AND `action_type` = 'OUT'
  AND `response_code` = '00'
  AND `action` = 'EPG71';
Why this works: 1. Primary Key Support: Asynchronous MVs support all StarRocks table types, including Primary Key. 2. Automatic Refresh: The 
REFRESH ASYNC
clause tells StarRocks to automatically trigger a refresh task in the background shortly after it detects a data change in 
ep_transaction
. 3. Background Processing: Unlike Synchronous MVs (which block your load until the view is updated), Asynchronous MVs update in the background, so they won't slow down your data ingestion speed. Monitoring the refresh Since the refresh happens in the background, you can check the status of the update tasks with:
Copy code
sql
SELECT * FROM information_schema.task_runs 
WHERE mv_name = 'ep_transaction_loan_mv' 
ORDER BY create_time DESC;
References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md
l
@Rocky how to setup datalake with starrock, i known starrock, minIO, have heard abou iceberg
r
Setting up a Data Lakehouse with StarRocks, MinIO, and Iceberg involves three main layers: 1. Storage (MinIO): Holds the actual data files (Parquet/ORC). 2. Table Format & Metadata (Iceberg): Manages table versions, snapshots, and schema. You will also need a Metastore (like Hive Metastore, Nessie, or an Iceberg REST server) to track where the Iceberg metadata is located. 3. Compute Engine (StarRocks): Connects to the metadata and storage to query or write data. 1. Prerequisites Ensure you have: * A running MinIO instance with a bucket created (e.g., 
warehouse
). * An Iceberg Metastore. The most common choice is the Hive Metastore (HMS) or a REST Catalog. 2. Connect StarRocks to Iceberg (MinIO) In StarRocks, you create an External Catalog. This allows StarRocks to "see" the databases and tables in your Iceberg catalog. Run this SQL in StarRocks (adjusting the endpoints and credentials):
Copy code
sql
CREATE EXTERNAL CATALOG iceberg_minio
PROPERTIES
(
    "type" = "iceberg",
    "iceberg.catalog.type" = "hive", -- Or "rest" if using an Iceberg REST server
    "hive.metastore.uris" = "<thrift://metastore-host:9083>",
    "aws.s3.enable_ssl" = "false",
    "aws.s3.enable_path_style_access" = "true",
    "aws.s3.endpoint" = "<http://minio-host:9000>",
    "aws.s3.access_key" = "your_minio_access_key",
    "aws.s3.secret_key" = "your_minio_secret_key"
);
3. Querying and Writing Data Once the catalog is created, you can interact with your data lake directly: * View tables:
Copy code
sql
    SHOW DATABASES FROM iceberg_minio;
    SET CATALOG iceberg_minio;
    USE some_database;
    SHOW TABLES;
* Query data:
Copy code
sql
    SELECT * FROM my_iceberg_table LIMIT 10;
* Create & Insert (Lakehouse mode): If your StarRocks version is 3.1+, you can create tables and insert data directly into the Iceberg lake:
Copy code
sql
    CREATE TABLE iceberg_minio.db_name.new_table (
        id INT,
        name STRING
    ) ENGINE=iceberg;

    INSERT INTO iceberg_minio.db_name.new_table VALUES (1, 'Rocky');
4. Performance Optimization: Data Cache Since data lives in MinIO (remote storage), query latency might be higher than local storage. StarRocks features a Data Cache that stores hot data on the BE (Backend) local disks (SSD recommended). To enable it, ensure your BE configuration (
be.conf
) has:
Copy code
properties
block_cache_enable = true
block_cache_disk_path = /path/to/cache/disk
block_cache_mem_size = 4G
block_cache_max_disk_size = 100G
Key Considerations for MinIO * Path-style access: Always set 
"aws.s3.enable_path_style_access" = "true"
for MinIO. * Connectivity: Ensure the StarRocks FE and BE nodes can both reach the MinIO endpoint and the Metastore URI. References • data_source/catalog/iceberg/iceberg_catalog.md • quick_start/iceberg.md • using_starrocks/data_cache.md References • quick_start/iceberg.md • data_source/catalog/iceberg/iceberg_rest_s3.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/hive_catalog.md
l
@Rocky Iceberg Metastore. The most common choice is the Hive Metastore (HMS) or a REST Catalog. detail what this
r
In an Iceberg-based Data Lake, the Metastore (or Catalog) is the "brain" of the operation. While MinIO stores the actual data files (Parquet), the Metastore keeps track of which files belong to which table and which version of that table is the "current" one. Here is the detail on the two most common choices: 1. Hive Metastore (HMS) The Hive Metastore has been the industry standard for over a decade. * How it works: It is a service (usually running as a Java process) that uses a relational database (like MySQL or PostgreSQL) to store the locations of your tables. * For Iceberg: When you query an Iceberg table, StarRocks asks HMS: "Where is the metadata file for table X?". HMS returns a path like 
<s3://bucket/table/metadata/v1.metadata.json>
. * Pros: Very mature, supported by almost every data tool (Spark, Trino, Flink). * Cons: Can be heavy to maintain; it was originally designed for Hive, not modern cloud-native formats. * StarRocks Config: Uses the 
thrift://
protocol (typically port 9083). 2. REST Catalog This is the modern, "cloud-native" way to manage Iceberg tables. Instead of a dedicated database service like HMS, it uses a simple Web API (HTTP). * How it works: StarRocks sends an HTTP request to a REST server to get table information. The REST server can be backed by anything (a database, a cloud service, or even a file system). * Popular Implementations: * Polaris: An open-source REST catalog by Snowflake. * Project Nessie: Provides "Git-like" version control (branching/merging) for your data lake. * Gravitron: A unified metadata lake. * Pros: Easier to secure (uses OAuth2/Tokens), better for multi-cloud setups, and much lighter than HMS. * StarRocks Config: Uses an 
http://
or 
https://
endpoint. ────────── Which one should you choose? Feature | Hive Metastore (HMS) | REST Catalog Setup Complexity | High (Needs MySQL + HMS Service) | Low to Medium (Depends on the server) Modern Features | Limited | High (Atomic transactions, branching) Security | Kerberos (Complex) | OAuth2 / JWT (Standard Web Security) Recommendation | Use if you have a legacy Hadoop/Hive environment. | Recommended for new "Greenfield" projects. How to get started with MinIO? If you are starting from scratch with MinIO, the easiest way to get a Metastore is to run Polaris or Nessie in a Docker container alongside your MinIO. Example StarRocks Catalog for REST (Polaris):
Copy code
sql
CREATE EXTERNAL CATALOG iceberg_rest
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "rest",
    "iceberg.catalog.uri" = "<http://polaris-host:8181/api/catalog>",
    "iceberg.catalog.warehouse" = "minio_warehouse",
    "aws.s3.endpoint" = "<http://minio-host:9000>",
    "aws.s3.access_key" = "admin",
    "aws.s3.secret_key" = "password",
    "aws.s3.enable_path_style_access" = "true"
);
References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_rest_s3.md • quick_start/iceberg.md References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_rest_s3.md • quick_start/iceberg.md • data_source/catalog/hive_catalog.md -catalog) References • data_source/catalog/iceberg/iceberg_catalog.md
l
@Rocky strategy_name` varchar(150) NULL COMMENT "Tên chiến lược", 
strategy_version
varchar(150) NULL COMMENT "Phiên bản chiến lược" ) ENGINE=OLAP PRIMARY KEY(
id
) ORDER BY (created_date, app_code) COMMENT "Thông tin phê duyệt khoản vay LOS" DISTRIBUTED BY HASH(
app_code
) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" );
r
The SQL snippet you provided for a Primary Key table in StarRocks is mostly correct, but there is one critical requirement regarding the bucketing column that you should check. Important: Bucketing Column Constraint In StarRocks Primary Key tables, the columns used in 
DISTRIBUTED BY HASH(...)
must be a subset of the Primary Key. In your snippet: * Primary Key: 
id
* Distribution Key: 
app_code
This will cause an error unless 
app_code
is also part of the primary key. To fix this, you have two options: 1. Change Distribution: Use 
DISTRIBUTED BY HASH(id)
(Recommended if 
id
is a unique ID). 2. Change Primary Key: Use 
PRIMARY KEY(id, app_code)
(Only if the combination defines uniqueness). Breakdown of your Properties * 
ENGINE=OLAP
: Correct, the standard engine. * 
ORDER BY (created_date, app_code)
: This is excellent. Since v3.0, StarRocks allows the Sort Key (
ORDER BY
) to be different from the Primary Key. Sorting by 
created_date
will significantly speed up queries filtered by time. * 
replicated_storage
= "true": This is a good choice. It enables "single leader replication" (available since v2.5), which reduces CPU and IO usage during data ingestion by only writing to the primary replica and then syncing. * 
replication_num
= "2": Valid, but note that for production environments, 3 is the recommended standard for high availability. * 
fast_schema_evolution
= "true": Correct, this allows for near-instant column additions/deletions. Corrected Example Assuming 
id
is your unique identifier, the corrected structure would look like this:
Copy code
sql
CREATE TABLE your_table_name (
  `id` bigint NOT NULL,
  `app_code` varchar(50) NOT NULL,
  `created_date` datetime NOT NULL,
  -- ... other columns ...
  `strategy_name` varchar(150) NULL COMMENT "Tên chiến lược",
  `strategy_version` varchar(150) NULL COMMENT "Phiên bản chiến lược"
) ENGINE=OLAP
PRIMARY KEY(`id`)
ORDER BY (created_date, app_code)
COMMENT "Thông tin phê duyệt khoản vay LOS"
DISTRIBUTED BY HASH(`id`) -- Changed to match Primary Key
BUCKETS 8
PROPERTIES (
    "compression" = "LZ4",
    "fast_schema_evolution" = "true",
    "replicated_storage" = "true",
    "replication_num" = "2"
);
One Final Tip: Persistent Index If this table will be very large (hundreds of millions of rows), consider adding 
"enable_persistent_index" = "true"
to your 
PROPERTIES
. This prevents the Primary Key index from consuming too much RAM by storing it on disk. References • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md • table_design/StarRocks_table_design.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/StarRocks_table_design.md • table_design/table_types/primary_key_table.md
l
@Rocky SQL Error [1064] [42000]: Getting syntax error at line 56, column 0. Detail message: Unexpected input 'COMMENT', the most similar input is {<EOF>, ';'}. Getting syntax error at line 56, column 0. Detail message: Unexpected input 'COMMENT', the most similar input is {<EOF>, ';'}.
r
The syntax error occurs because StarRocks requires a specific order for the clauses in a 
CREATE TABLE
statement. Specifically, the 
COMMENT
and 
DISTRIBUTED BY
clauses must come before the 
ORDER BY
clause. Additionally, as mentioned previously, your Distribution Key (
app_code
) must be a subset of your Primary Key (
id
). The Corrected SQL Structure Here is the correct order for your statement:
Copy code
sql
CREATE TABLE your_table_name (
  `id` bigint NOT NULL,
  `app_code` varchar(50) NOT NULL,
  `created_date` datetime NOT NULL,
  -- ... other columns ...
  `strategy_name` varchar(150) NULL COMMENT "Tên chiến lược",
  `strategy_version` varchar(150) NULL COMMENT "Phiên bản chiến lược"
) ENGINE=OLAP
PRIMARY KEY(`id`)
COMMENT "Thông tin phê duyệt khoản vay LOS"  -- 1. Comment comes after Key
DISTRIBUTED BY HASH(`id`)                -- 2. Distributed By comes next (must use 'id')
BUCKETS 8
ORDER BY (created_date, app_code)         -- 3. Order By comes after Distribution
PROPERTIES (                             -- 4. Properties is always last
    "compression" = "LZ4",
    "fast_schema_evolution" = "true",
    "replicated_storage" = "true",
    "replication_num" = "2"
);
Why this fix works: 1. Clause Order: StarRocks follows this sequence: 
[PRIMARY KEY]
-> 
[COMMENT]
-> 
[PARTITION BY]
-> 
[DISTRIBUTED BY]
-> 
[ORDER BY]
-> 
[PROPERTIES]
. 2. PK Constraint: In a Primary Key table, the column in 
DISTRIBUTED BY HASH(...)
must be one of the columns defined in the 
PRIMARY KEY(...)
. Since your PK is only 
id
, you must distribute by 
id
. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/StarRocks_table_design.md • table_design/table_types/primary_key_table.md
l
@Rocky 2026-07-16, 084807 UTC] {extract_dag_creator.py:222} INFO - 📄 Executing pre-extract SQL: delete from dmapp.DTM_CUSTOMER_ADDRESS a WHERE a.sys_run_date >= date '2010-01-01' [2026-07-16, 084807 UTC] {taskinstance.py:3313} ERROR - Task failed with exception Traceback (most recent call last): File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/taskinstance.py", line 768, in _execute_task *result = _execute_callable(context=context, *execute_callable_kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/taskinstance.py", line 734, in _execute_callable return ExecutionCallableRunner( ^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/operator_helpers.py", line 252, in run *return self.func(*args, *kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/models/baseoperator.py", line 424, in wrapper *return func(self, *args, *kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/operators/python.py", line 238, in execute return_value = self.execute_callable() ^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/operators/python.py", line 256, in execute_callable *return runner.run(*self.op_args, *self.op_kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/operator_helpers.py", line 252, in run *return self.func(*args, *kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/airflow/dags/bags/dwh-dmapp-starrocks/airflow_dags/extract_framework/extract_dag_creator.py", line 223, in _extract_table_to_target cur.execute(sql) File "/home/airflow/.local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute res = self._query(mogrified_query) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/airflow/.local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query db.query(q) File "/home/airflow/.local/lib/python3.12/site-packages/MySQLdb/connections.py", line 280, in query _mysql.connection.query(self, query) MySQLdb.ProgrammingError: (1064, "Getting syntax error at line 1, column 39. Detail message: Unexpected input 'a', the most similar input is {<EOF>, ';'}.") [2026-07-16, 084807 UTC] {taskinstance.py:907} DEBUG - Task Duration set to 5.692543
r
The error is caused by the table alias 
a
 in your 
DELETE
statement. StarRocks does not support assigning an alias directly after the table name in the 
FROM
clause of a 
DELETE
statement. The Fix Simply remove the alias 
a
and reference the column directly: Incorrect:
Copy code
sql
delete from dmapp.DTM_CUSTOMER_ADDRESS a WHERE a.sys_run_date >= date '2010-01-01';
Correct:
Copy code
sql
delete from dmapp.DTM_CUSTOMER_ADDRESS WHERE sys_run_date >= date '2010-01-01';
Why this happened In StarRocks, the 
DELETE
syntax expects the table name to be followed immediately by the 
WHERE
clause (or a 
PARTITION
/ 
USING
clause). It does not recognize the 
table_name alias
pattern common in some other SQL dialects. * For simple deletes: Reference column names directly in the 
WHERE
clause. * For deletes with joins (Primary Key tables only): If you need to join other tables to determine which rows to delete, use the 
USING
clause (available from v3.0+). References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md index/DELETE) References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md
l
@Rocky select * from losrep.application_status b where b.current_decision ='DISBURSED' and app_code in (select distinct a.app_code FROM losrep.application a WHERE a.product_category LIKE 'ZALO%' AND a.created_date >= *DATE_SUB*(*CURDATE*(), INTERVAL 7 DAY)) SELECT *TO_CHAR*(a.CREATED_DATE, 'HH24') AS hour, *TRUNC*(a.CREATED_DATE) AS _created_date_, a.* FROM dmapp.DTM_CUS_APPLICATIONS a WHERE *TRUNC*(a.CREATED_DATE) >= *DATE*'2026-07-06' AND *TRUNC*(a.CREATED_DATE) < *DATE*'2026-07-16' AND a.PRODUCT_CATEGORY LIKE 'ZALO%' AND a.STEP_CODE ='DONE' rewrite to starrock
r
To rewrite these queries for StarRocks, you need to replace the Oracle/Postgres-style functions (
TO_CHAR
, 
TRUNC
) with StarRocks-native date functions. Query 1 This query is already mostly compatible with StarRocks. 
DATE_SUB
, 
CURDATE
, and 
INTERVAL
are supported as-is.
Copy code
sql
SELECT * 
FROM losrep.application_status b
WHERE b.current_decision = 'DISBURSED'
  AND app_code IN (
      SELECT DISTINCT a.app_code 
      FROM losrep.application a
      WHERE a.product_category LIKE 'ZALO%'
        AND a.created_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
  );
Query 2 For this query, you need to replace 
TO_CHAR
with 
DATE_FORMAT
and 
TRUNC
with the 
DATE()
function.
Copy code
sql
SELECT
   DATE_FORMAT(a.CREATED_DATE, '%H') AS hour, -- HH24 equivalent
   DATE(a.CREATED_DATE) AS created_date,      -- TRUNC equivalent
   a.*
FROM dmapp.DTM_CUS_APPLICATIONS a
WHERE DATE(a.CREATED_DATE) >= DATE '2026-07-06'
  AND DATE(a.CREATED_DATE) < DATE '2026-07-16'
  AND a.PRODUCT_CATEGORY LIKE 'ZALO%'
  AND a.STEP_CODE = 'DONE';
Key Changes Summary: 1. 
TO_CHAR(date, 'HH24')
 $\rightarrow$ 
DATE_FORMAT(date, '%H')
: StarRocks uses C-style format strings where 
%H
represents the 24-hour format. 2. 
TRUNC(date)
 $\rightarrow$ 
DATE(date)
: In StarRocks, the 
DATE()
function extracts the date part and removes the time component, serving the same purpose as 
TRUNC
on a timestamp. 3. Date Literals: StarRocks supports the 
DATE 'YYYY-MM-DD'
syntax used in your second query. References • sql-reference/sql-functions/date-time-functions/date_format.md • sql-reference/sql-functions/date-time-functions/date.md • sql-reference/sql-functions/date-time-functions/date_sub.md References • sql-reference/sql-functions/date-time-functions/date_sub.md • sql-reference/sql-functions/date-time-functions/days_sub.md • sql-reference/sql-functions/date-time-functions/minutes_sub.md • sql-reference/sql-functions/date-time-functions/seconds_sub.md • sql-reference/sql-functions/date-time-functions/weeks_sub.md • sql-reference/sql-functions/date-time-functions/hours_sub.md • sql-reference/sql-functions/date-time-functions/years_sub.md • sql-reference/sql-functions/date-time-functions/months_sub.md
l
@Rocky CREATE TABLE LOSREP.APPLICATION_HISTORY ( ID NUMBER(20,0), PROCESS_UID VARCHAR2(50), APP_UID VARCHAR2(50), APP_NUMBER NUMBER(20,0), APP_CODE VARCHAR2(50), TASK_UID VARCHAR2(50), TASK_INDEX NUMBER(5,0), DELEGATE_DATE DATE, INIT_DATE DATE, FINISH_DATE DATE, CURRENT_USER VARCHAR2(50), CURRENT_DECISION VARCHAR2(50), TASK_STATUS VARCHAR2(50), SYS_ACTION VARCHAR2(50), PARENT_TASK_UID VARCHAR2(50), STEP_CODE VARCHAR2(50), STEP_CODE_DETAIL VARCHAR2(50), STEP_CODE_DETAIL_LBL VARCHAR2(250), CUSTOMIZE_INDEX NUMBER(20,0), PREVIOUS_STEP VARCHAR2(50), this table have: 69,161,484 need stream data using kafka realtime, so should i used primary key or duplicatekey, as data no need to dup, just primary key colume
r
For your scenario with ~69 million rows and real-time Kafka streaming, you should use the Primary Key table. Why Primary Key table? 1. Deduplication & Uniqueness: You mentioned you have a primary key column and do not want duplicate data. The Primary Key table automatically handles "upserts" (Insert or Update). If a record with an existing 
ID
comes from Kafka, StarRocks will replace the old one with the new one. In a Duplicate Key table, both records would be stored as separate rows. 2. Real-time Streaming: Primary Key tables are optimized for high-frequency real-time updates and deletes from systems like Kafka. 3. Query Performance: Because it uses a "Delete+Insert" strategy instead of "Merge-on-Read," query performance is typically 3–10 times faster than the older Unique Key model. Recommended Configuration for 69M Rows With ~70 million rows, you should ensure the Persistent Index is enabled (it is 
true
by default in recent versions) to keep memory usage low by storing the index on disk (preferably SSD). Here is the recommended StarRocks SQL for your table:
Copy code
sql
CREATE TABLE APPLICATION_HISTORY (
    ID BIGINT NOT NULL,
    PROCESS_UID VARCHAR(50),
    APP_UID VARCHAR(50),
    APP_NUMBER BIGINT,
    APP_CODE VARCHAR(50),
    TASK_UID VARCHAR(50),
    TASK_INDEX INT,
    DELEGATE_DATE DATETIME,
    INIT_DATE DATETIME,
    FINISH_DATE DATETIME,
    CURRENT_USER VARCHAR(50),
    CURRENT_DECISION VARCHAR(50),
    TASK_STATUS VARCHAR(50),
    SYS_ACTION VARCHAR(50),
    PARENT_TASK_UID VARCHAR(50),
    STEP_CODE VARCHAR(50),
    STEP_CODE_DETAIL VARCHAR(50),
    STEP_CODE_DETAIL_LBL VARCHAR(250),
    CUSTOMIZE_INDEX BIGINT,
    PREVIOUS_STEP VARCHAR(50)
) ENGINE=OLAP
PRIMARY KEY(ID)                     -- Ensures no duplicates based on ID
DISTRIBUTED BY HASH(ID) BUCKETS 16  -- Distribute by PK for even data spread
ORDER BY (INIT_DATE, APP_CODE)      -- Define sort key for query acceleration
PROPERTIES (
    "enable_persistent_index" = "true", -- Keeps memory usage low for 69M rows
    "compression" = "LZ4"
);
Key Considerations: * Data Types: Oracle's 
NUMBER(20,0)
should be mapped to 
BIGINT
. 
DATE
should be mapped to 
DATETIME
or 
DATE
depending on whether you need the time component. * Bucketing: For 69 million rows, starting with 16 to 32 buckets is a good rule of thumb. If you expect this to grow into the hundreds of millions, you can increase the bucket count. * Sort Key (
ORDER BY
): Choose columns often used in your 
WHERE
clauses (e.g., 
INIT_DATE
) to speed up your specific queries. References • table_design/table_types/primary_key_table.md • loading/Kafka-connector-starrocks.md • best_practices/primarykey_table.md References • table_design/table_types/primary_key_table.md • table_design/table_types/duplicate_key_table.md • table_design/StarRocks_table_design.md • table_design/table_types/table_types.md
l
@Rocky CREATE TABLE 
dtm_application_history
( 
id
bigint(20) NOT NULL COMMENT "PK", 
app_uid
varchar(50) NULL COMMENT "", 
app_number
bigint(20) NULL COMMENT "", 
app_code
varchar(50) NULL COMMENT "", 
task_uid
varchar(50) NULL COMMENT "", 
delegate_date
datetime NULL COMMENT "", 
process_uid
varchar(50) NULL COMMENT "", 
task_index
smallint(6) 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 "", 
parent_task_uid
varchar(50) NULL COMMENT "", 
step_code
varchar(50) NULL COMMENT "", 
step_code_detail
varchar(50) NULL COMMENT "", 
step_code_detail_lbl
varchar(250) NULL COMMENT "", 
customize_index
bigint(20) NULL COMMENT "", 
previous_step
varchar(50) NULL COMMENT "", INDEX idx_task_status (
task_status
) USING BITMAP COMMENT 'Filter by task_status', INDEX idx_app_code (
app_code
) USING BITMAP COMMENT 'Filter by app_code' ) redessign this 70 milion rows table, 5GB data.
r
For a table with 70 million rows (5GB) receiving real-time data from Kafka, the Primary Key (PK) model is the best choice. It ensures uniqueness, supports high-frequency upserts, and provides the best query performance. Here is the redesigned DDL for StarRocks 3.x, incorporating best practices for partitioning, sorting, and indexing: Optimized DDL
Copy code
sql
CREATE TABLE `dtm_application_history` (
  `id` BIGINT NOT NULL COMMENT "Natural Primary Key",
  `app_uid` VARCHAR(50) NULL,
  `app_number` BIGINT NULL,
  `app_code` VARCHAR(50) NULL,
  `task_uid` VARCHAR(50) NULL,
  `delegate_date` DATETIME NULL,
  `process_uid` VARCHAR(50) NULL,
  `task_index` SMALLINT NULL,
  `init_date` DATETIME NOT NULL COMMENT "Partition column must be NOT NULL",
  `finish_date` DATETIME NULL,
  `current_user_1` VARCHAR(50) NULL,
  `current_decision` VARCHAR(50) NULL,
  `task_status` VARCHAR(50) NULL,
  `sys_action` VARCHAR(50) NULL,
  `parent_task_uid` VARCHAR(50) NULL,
  `step_code` VARCHAR(50) NULL,
  `step_code_detail` VARCHAR(50) NULL,
  `step_code_detail_lbl` VARCHAR(250) NULL,
  `customize_index` BIGINT NULL,
  `previous_step` VARCHAR(50) NULL,
  -- Bitmap indices for low-cardinality filtering
  INDEX idx_task_status (`task_status`) USING BITMAP,
  INDEX idx_current_decision (`current_decision`) USING BITMAP
) ENGINE=OLAP
PRIMARY KEY(`id`)
-- Partitioning by time is critical for managing 70M+ rows
PARTITION BY RANGE(`init_date`) (
    START ("2024-01-01") END ("2026-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(`id`) BUCKETS 16
-- Sort Key decoupled from PK to optimize specific query filters
ORDER BY (`init_date`, `app_code`)
PROPERTIES (
    "enable_persistent_index" = "true",
    "replication_num" = "3",
    "compression" = "LZ4"
);
Key Design Improvements: 1. Primary Key Model: Using 
PRIMARY KEY(id)
allows the Kafka stream to perform upserts. If an ID already exists, StarRocks will update the row instead of creating a duplicate. 2. Persistent Index: Enabled via 
"enable_persistent_index" = "true"
. For 70 million rows, this keeps the primary key index on disk (SSD) rather than consuming several GBs of RAM. 3. Range Partitioning: Partitioning by 
init_date
(Monthly) allows StarRocks to "prune" data during queries. If you search for data from July, it will only scan the July partition, ignoring the other 4.5GB of data. 4. Decoupled Sort Key: By using 
ORDER BY (init_date, app_code)
, you tell StarRocks to store the data physically sorted by these columns. This creates a Prefix Index that makes filtering by date or application code extremely fast. 5. Bitmap Indices: * Keep 
task_status
as a Bitmap index if it has low cardinality (e.g., 'PENDING', 'COMPLETED'). * I also added 
current_decision
as a Bitmap index candidate based on your previous queries. _ _Note: Do not create Bitmap indices on high-cardinality columns (like IDs or unique codes) as the prefix/primary index handles those more efficiently.* 6. Bucketing: 16 buckets for 5GB of data results in tablet sizes of ~300MB, which is within the recommended 100MB–1GB range for StarRocks. Tips for Kafka Loading: * Use the StarRocks Kafka Connector or Routine Load. * Since this is a Primary Key table, ensure your Kafka source provides the 
id
column. * If your Kafka stream can contain deletes, you can set the 
__op
column in your load job to handle row deletions automatically. References • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • best_practices/primarykey_table.md • loading/RoutineLoad.md References
l
@Rocky using PARTITION BY RANGE(
init_date
) will auto create new partion?
r
No, using 
PARTITION BY RANGE
does not automatically create new partitions by itself. In StarRocks, you have two ways to handle automatic partition creation: 1. The Recommended Way: Expression Partitioning (v3.0+) If you want StarRocks to automatically create a new partition whenever data for a new month arrives (e.g., from your Kafka stream), you should use Expression Partitioning. This is much simpler than the legacy method. Replace 
PARTITION BY RANGE(init_date)
with:
Copy code
sql
PARTITION BY date_trunc('month', init_date)
Example DDL update:
Copy code
sql
CREATE TABLE `dtm_application_history` (
  ...
  `init_date` DATETIME NOT NULL,
  ...
) ENGINE=OLAP
PRIMARY KEY(`id`)
-- This will automatically create partitions like p202401, p202402, etc. as data arrives
PARTITION BY date_trunc('month', init_date)
DISTRIBUTED BY HASH(`id`) BUCKETS 16
ORDER BY (`init_date`, `app_code`)
PROPERTIES (
    "partition_live_number" = "24", -- Optional: automatically drops partitions older than 24 months
    "enable_persistent_index" = "true"
);
2. The Legacy Way: Dynamic Partitioning If you must use 
PARTITION BY RANGE
, you have to enable the 
dynamic_partition
properties in the 
PROPERTIES
section. This method requires you to define at least one initial partition manually and then StarRocks will manage future ones.
Copy code
sql
...
PARTITION BY RANGE(init_date) (
    PARTITION p202401 VALUES LESS THAN ("2024-02-01")
)
DISTRIBUTED BY HASH(`id`) BUCKETS 16
PROPERTIES (
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "MONTH",
    "dynamic_partition.end" = "3",      -- Create partitions for the next 3 months in advance
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "16"
);
Why use Expression Partitioning instead? * Zero Maintenance: You don't need to configure complex properties. * Handles Backfills: If you suddenly load data from 2 years ago, it will create those partitions automatically. The "Legacy" dynamic partitioning usually only creates partitions for the "future" relative to the current time. * Simplified DDL: Much cleaner and less prone to configuration errors. Recommendation: Since you are setting this up for a real-time Kafka stream with 70M rows, use Expression Partitioning (
date_trunc
). It is more robust for high-velocity streaming data. 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 if not set partition_live_number, how many partition will exist
r
If you do not set 
partition_live_number
, StarRocks will not automatically delete any partitions. Here is what will happen as your Kafka data flows in: 1. Unlimited Growth Partitions will continue to be created for every new month (or whatever time unit you chose) as data arrives. They will persist in the system forever unless you manually delete them using 
ALTER TABLE ... DROP PARTITION
. 2. System Limits While there is no "automatic cleanup," there are safety limits to prevent the system from crashing: * Table Limit: By default, StarRocks (v3.3.2+) limits a single table to 100,000 partitions (
max_partition_number_per_table
). * Batch Limit: A single load job (like a batch from Kafka) can only create up to 4,096 partitions at once. This prevents a single typo in a date (e.g., year 0025 instead of 2025) from accidentally creating thousands of partitions. 3. Performance Impact If you accumulate too many partitions (e.g., several thousand): * FE Memory: The FrontEnd (FE) stores metadata for every partition. Having tens of thousands of partitions can increase memory usage and slow down the FE. * Query Planning: Even though StarRocks is great at partition pruning, having an excessive number of partitions makes the "planning" phase of a query slower. Recommendation For your table with 70 million rows and monthly partitioning: * 70M rows / 12 months = ~5.8M rows per partition. This is a very healthy size for StarRocks. * If you need to keep history forever: Leave 
partition_live_number
unset. You will have ~120 partitions after 10 years, which is perfectly fine. * If you only need a rolling window: Set 
partition_live_number
(e.g., 
24
for two years). This keeps the database lean and automatically handles your data lifecycle. Summary: If you don't set it, they stay forever. You only need to set it if you want StarRocks to act as a "TTL" (Time To Live) mechanism to automatically purge old data. References • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • best_practices/partitioning.md • table_design/data_distribution/expression_partitioning.md • best_practices/query_tuning/schema_tuning.md • knowledge/admin_and_operation/shared_data_compaction.md
l
@Rocky SQL Error [1064] [42000]: Getting analyzing error. Detail message: The partition column could not be aggregated column and unique table's partition column must be key column. Getting analyzing error. Detail message: The partition column could not be aggregated column and unique table's partition column must be key column.
r
This error occurs because, in StarRocks Primary Key (and Unique Key) tables, the partition column must be part of the Primary Key. StarRocks needs the partition column in the PK so that when an update comes in (upsert), it can immediately determine which partition the data belongs to without scanning the entire table. The Fix To use 
init_date
for partitioning, you must include it in your 
PRIMARY KEY
definition. Optimized DDL with the fix:
Copy code
sql
CREATE TABLE `dtm_application_history` (
  `id` bigint(20) NOT NULL COMMENT "PK",
  `app_uid` varchar(50) NULL,
  `app_number` bigint(20) NULL,
  `app_code` varchar(50) NULL,
  `task_uid` varchar(50) NULL,
  `delegate_date` datetime NULL,
  `process_uid` varchar(50) NULL,
  `task_index` smallint(6) NULL,
  `init_date` datetime NOT NULL, -- Must be NOT NULL
  `finish_date` datetime NULL,
  `current_user_1` varchar(50) NULL,
  `current_decision` varchar(50) NULL,
  `task_status` varchar(50) NULL,
  `sys_action` varchar(50) NULL,
  `parent_task_uid` varchar(50) NULL,
  `step_code` varchar(50) NULL,
  `step_code_detail` varchar(50) NULL,
  `step_code_detail_lbl` varchar(250) NULL,
  `customize_index` bigint(20) NULL,
  `previous_step` varchar(50) NULL,
  INDEX idx_task_status (`task_status`) USING BITMAP,
  INDEX idx_app_code (`app_code`) USING BITMAP
) ENGINE=OLAP
-- FIX: init_date must be here
PRIMARY KEY(`id`, `init_date`) 
-- Automatic partitioning
PARTITION BY date_trunc('month', `init_date`)
DISTRIBUTED BY HASH(`id`) BUCKETS 16
ORDER BY (`init_date`, `app_code`)
PROPERTIES (
    "enable_persistent_index" = "true"
);
Important Considerations: 1. Uniqueness Scope: By adding 
init_date
to the Primary Key, the 
id
is technically only guaranteed to be unique within that specific month. However, if your application logic guarantees that 
id
is always unique (like a UUID or auto-incrementing sequence), this change won't cause any issues. 2. Updates: If you ever update the 
init_date
of an existing row, StarRocks will treat it as a delete + insert because the row is moving to a different partition. 3. NOT NULL Requirement: Partition columns in a Primary Key table cannot contain NULL values. Ensure 
init_date
is marked as 
NOT NULL
. What if I can't add 
init_date
to the PK? If your 
id
is not unique across dates and you cannot include the date in the PK, you have two options: * Remove Partitioning: For only 5GB of data, partitioning is actually optional. StarRocks can handle 70M rows in a single "unpartitioned" table very easily using just the 
id
as the PK and 
DISTRIBUTED BY HASH(id)
. * Duplicate ID: If you have the same 
id
occurring in different months, the version with 
PRIMARY KEY(id, init_date)
will keep both rows. If you only want the "latest" version regardless of the date, you must either remove partitioning or use a different table model (like the Duplicate Key model, though you lose the upsert capability). References • table_design/table_types/primary_key_table.md • faq/operation_maintenance_faq.md • table_design/data_distribution/expression_partitioning.md References • table_design/table_types/primary_key_table.md • faq/operation_maintenance_faq.md • table_design/data_distribution/expression_partitioning.md
l
@Rocky i extract source oracle database with this sql: select ID, PROCESS_UID, APP_UID, APP_NUMBER, APP_CODE, TASK_UID, TASK_INDEX, DELEGATE_DATE, INIT_DATE, FINISH_DATE, CURRENT_USER, CURRENT_DECISION, TASK_STATUS, SYS_ACTION, PARENT_TASK_UID, STEP_CODE, STEP_CODE_DETAIL, STEP_CODE_DETAIL_LBL, CUSTOMIZE_INDEX, PREVIOUS_STEP from losrep.application_history a WHERE a.DELEGATE_DATE >= sysdate -1 but stream load to starrocks got this error: RuntimeError: Stream Load failed [Fail]: Getting syntax error at line 1, column 118. Detail message: Unexpected input 'current_user', the most similar input is {a legal identifier}.
r
The error occurs because 
CURRENT_USER
is a reserved keyword in StarRocks (it is a built-in function that returns the name of the current user). When you use it in a Stream Load without escaping it, the StarRocks parser thinks you are trying to call the function instead of identifying a column. Solution 1: Escape the column name (Recommended) You must wrap the column name in backticks (`` ` ``) in both your Table DDL and your Stream Load 
columns
configuration. 1. Update your Table DDL:
Copy code
sql
CREATE TABLE ... (
  ...
  `current_user` varchar(50) NULL, -- Wrap in backticks
  ...
)
2. Update your Stream Load command: If you are using a 
curl
command or a script, find the 
columns
header and add backticks:
Copy code
bash
-H "columns: id, process_uid, ..., `current_user`, current_decision, ..."
_Note: If you are using a shell script, you may need to escape the backticks like `\`current_user\``._ ────────── Solution 2: Rename the column To avoid potential issues with other tools or future queries, it is often easier to rename the column to something that isn't a reserved keyword, such as 
user_id
or 
app_user
. Updated Oracle SQL for extraction:
Copy code
sql
select
  ID,
  ...
  CURRENT_USER as APP_USER, -- Rename during extract
  ...
from losrep.application_history ...
Updated StarRocks DDL:
Copy code
sql
CREATE TABLE `dtm_application_history` (
  ...
  `app_user` varchar(50) NULL, -- Use the new name
  ...
)
Why this happened In StarRocks, keywords like 
SELECT
, 
TABLE
, 
CURRENT_USER
, and 
TIMESTAMP
have special meanings. If you want to use them as names for your own tables or columns, the system requires backticks to distinguish them from the built-in commands. References • sql-reference/sql-statements/keywords.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • loading/StreamLoad.md References • sql-reference/sql-statements/keywords.md • release_notes/release-2.5.md • release_notes/release-3.0.md • administration/management/FE_configuration.md • sql-reference/sql-statements/account-management/CREATE_USER.md • sql-reference/System_limit.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/user_privs/authorization/privilege_item.md
2 Views
Open in Slack
PreviousNext
Powered by