http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Error: NULL value in non-nullable c...
# questions-and-troubleshooting
l
@Rocky Error: NULL value in non-nullable column 'app_code'. CREATE TABLE 
dtm_pcb_data_result_extract_new
( 
request_date
datetime NOT NULL COMMENT "Ngày yêu cầu", 
app_code
varchar(50) NOT NULL COMMENT "Mã hồ sơ", 
id
bigint(20) NOT NULL COMMENT "ID định danh", 
response_date
datetime NULL COMMENT "Ngày phản hồi", 
js_path
varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON", 
js_value
varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON" ) ENGINE=OLAP DUPLICATE KEY(
request_date
, 
app_code
)
r
The error "NULL value in non-nullable column 'app_code'" occurs because the data you are attempting to load or insert contains a 
NULL
value for the 
app_code
column, which you defined as 
NOT NULL
in your schema. This usually happens in two scenarios: 1. Missing or Explicit NULLs: The source data (CSV, JSON, or source table) actually contains a 
NULL
value for that field. 2. Failed Data Conversion: If 
strict_mode
is disabled (default), StarRocks attempts to convert invalid data types to 
NULL
. If that conversion results in a 
NULL
for a non-nullable column, the load fails with this error. Solutions 1. Use 
ifnull()
to provide a default value If you want to handle these 
NULL
values during the loading process without changing your data source, you can map the column using the 
ifnull()
function. * For Stream Load: Add a 
columns
header to your command:
Copy code
bash
    -H "columns: request_date, tmp_app_code, id, ..., app_code = ifnull(tmp_app_code, 'DEFAULT_CODE')"
* For INSERT INTO ... SELECT: Wrap the source column in 
COALESCE
or `IFNULL`:
Copy code
sql
    INSERT INTO dtm_pcb_data_result_extract_new
    SELECT request_date, COALESCE(app_code, 'UNKNOWN'), id, ...
    FROM source_table;
2. Enable Strict Mode and skip bad rows If you prefer to simply skip the rows that have 
NULL
values instead of failing the entire job, you can use 
strict_mode
combined with 
max_filter_ratio
. * Stream Load:
Copy code
bash
    -H "strict_mode: true" -H "max_filter_ratio: 0.1"
(This allows up to 10% of rows to be filtered out if they contain invalid NULLs). 3. Alter the table schema If 
app_code
can legally be 
NULL
in your business logic, you can modify the table to allow 
NULL
values:
Copy code
sql
ALTER TABLE dtm_pcb_data_result_extract_new MODIFY COLUMN app_code varchar(50) NULL;
_Note: Since 
app_code
is part of your 
DUPLICATE KEY
, ensure that allowing NULLs here won't affect your query performance or data modeling expectations._ 4. Check for hidden NULLs in source files If you are loading from a CSV, ensure there are no trailing delimiters or empty fields where 
app_code
is expected. For example, in a CSV: 
2023-01-01,,100,...
→ The second field is an empty string/NULL. If your load configuration interprets empty strings as NULLs, this will trigger the error. References • loading/load_concept/strict_mode.md • faq/loading/Stream_load_faq.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/Load_to_Primary_Key_tables.md
l
@Rocky Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-01 085041, 'DSAE2492212252976245', 1942580, 2023-01-01 085042, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.1.profiles.29.default', '0'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-01 144514, 'DSA07B12212312993995', 1942719, 2023-01-01 144515, 'RI_Req_Output.creditHistory.contract.nonInstalments.acnoInstAmounts.utilization', '0'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-01 095735, 'DS9657A2301012994409', 1942631, 2023-01-01 095735, 'RI_Req_Output.subject.0.matched.person.dateOfBirth', '20121969'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-01 210930, 'DSCFFC42301012994879', 1942842, 2023-01-01 210932, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.1.totalAmount', '31980000'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 204053, 'DS16DB92301022996293', 1943682, 2023-01-02 204053, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.1.unpaidDueInstalmentsAmount', '0'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 174352, 'DG327C42301022996102', 1943596, 2023-01-02 174354, 'RI_Req_Output.creditHistory.contract.cards.grantedContract.2.profiles.26.status', '1'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 102357, 'DS6FB1C2212312993570', 1943120, 2023-01-02 102357, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.7.commonData.encryptedFICode', 'F06'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 150107, 'DSE71C72212182946165', 1943450, 2023-01-02 150108, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.7.profiles.15.default', '0'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 211107, 'DSEC07A2212272983347', 1943698, 2023-01-02 211110, 'RI_Req_Output.creditHistory.contract.instalments.notGrantedContract.4.contractPhase', 'RQ'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 150006, 'DSE87D82212272981293', 1943449, 2023-01-02 150006, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.0.profiles.10.status', '2'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 174352, 'DG327C42301022996102', 1943596, 2023-01-02 174354, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.7.unpaidDueInstalmentsNumber', '0'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 090307, 'DSD27F72212302991448', 1943003, 2023-01-02 090307, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.2.yearOfManufacturing', NULL] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 203441, '000000006217003', 1943679, 2023-01-02 203441, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.1.commonData.dateOfLastUpdate', '30112022'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 204845, 'DSB3DDD2301022996077', 1943689, 2023-01-02 204845, 'RI_Req_Output.creditHistory.contract.cards.grantedContract.0.commonData.ficontractCode', NULL] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 161244, 'DGA77312301022995973', 1943515, 2023-01-02 161245, 'RI_Req_Output.creditHistory.contract.cards.grantedContract.2.commonData.dateOfLastUpdate', '30112022'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 170644, 'DS9BCC02301022996064', 1943562, 2023-01-02 170644, 'RI_Req_Output.creditHistory.contract.cards.grantedContract.1.expirationDateofNextInstallment', '20102022'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 173525, 'DS917DC2212282983617', 1943588, 2023-01-02 173525, 'RI_Req_Output.creditHistory.contract.instalments.grantedContract.6.profiles.4.status', '1'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 153321, 'DS6E2A32212202955402', 1943481, 2023-01-02 153322, 'RI_Req_Output.creditHistory.contract.instalments.notGrantedContract.0.typeOfFinancing', '10'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 211730, 'DS80A022212202958451', 1943705, 2023-01-02 211731, 'RI_Req_Output.creditHistory.contract.cards.grantedContract.3.profiles.16.referenceMonth', '07'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 154442, 'DS479292301022995841', 1943490, 2023-01-02 154442, 'RI_Req_Output.subject.0.matched.person.reference.1.type', 'PN'] Error: The row is out of partition ranges. Please add a new partition.. Row: [2023-01-02 160356, 'DSDA41E2212292986860', 1943509, 2023-01-02 160358, 'RI_Req_Output.subject.0.matched.person.historicalAddress.main.1.fullAddress', 'DUY TRUNG, DUY XUYEN, QUANG NAM']
r
This error occurs because your table is partitioned (likely by the 
request_date
column), but there are no partitions defined to hold the dates in your data (e.g., 
2023-01-01
and 
2023-01-02
). In StarRocks, if you define a partitioned table, any data loaded must fall within an existing partition range, or the load will fail. Solution 1: Use Automatic Partitioning (Recommended) If you are using StarRocks v3.1 or later, you can modify your table to use Expression Partitioning. This allows StarRocks to create partitions automatically when new data arrives. You would need to recreate the table (or use a backup) with a 
PARTITION BY
clause:
Copy code
sql
CREATE TABLE `dtm_pcb_data_result_extract_new` (
  `request_date` datetime NOT NULL COMMENT "Ngày yêu cầu",
  `app_code` varchar(50) NOT NULL COMMENT "Mã hồ sơ",
  ...
) ENGINE=OLAP
DUPLICATE KEY(`request_date`, `app_code`)
-- This will automatically create daily partitions based on request_date
PARTITION BY date_trunc("day", request_date)
DISTRIBUTED BY HASH(`app_code`);
Solution 2: Manually Add Partitions If you prefer to manage partitions manually, you must add the missing ranges before loading the data:
Copy code
sql
ALTER TABLE dtm_pcb_data_result_extract_new 
ADD PARTITION p20230101 VALUES LESS THAN ("2023-01-02"),
ADD PARTITION p20230102 VALUES LESS THAN ("2023-01-03");
Solution 3: Add a "Catch-all" Partition If you don't want to manage daily partitions and just want the data to load, you can add a single partition with a very large range. However, this is generally not recommended for large datasets as it defeats the performance benefits of partitioning. How to check existing partitions To see which partitions currently exist and what ranges they cover, run:
Copy code
sql
SHOW PARTITIONS FROM dtm_pcb_data_result_extract_new;
Summary: Your current data has dates from early January 2023. You need to ensure partitions exist for those specific dates before the load will succeed.
l
@Rocky review this table design CREATE TABLE dmapp.
dtm_pcb_data_result_extract_pk
( 
id
bigint(20) NOT NULL COMMENT "ID định danh", 
request_date
datetime NOT NULL COMMENT "Ngày yêu cầu", 
app_code
varchar(50) NULL COMMENT "Mã hồ sơ", 
response_date
datetime NULL COMMENT "Ngày phản hồi", 
js_path
varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON", 
js_value
varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON" ) ENGINE=OLAP PRIMARY KEY(
id
, 
request_date
) PARTITION BY date_trunc("month", request_date) DISTRIBUTED BY HASH(
app_code
) BUCKETS 8 PROPERTIES ( "replication_num" = "1", "fast_schema_evolution" = "true" );
r
Your table design has one critical error regarding the Primary Key table requirements in StarRocks, along with a few recommendations for production readiness. 1. Critical Fix: Distribution Key must be in Primary Key In StarRocks, for a Primary Key table, the 
PRIMARY KEY
columns must include all columns used in the 
DISTRIBUTED BY HASH
clause and the 
PARTITION BY
clause. * Problem: You are distributing by 
app_code
, but 
app_code
is not in your Primary Key. This will result in an error when creating the table. * Problem: Columns in the Primary Key cannot be 
NULL
. Your 
app_code
is currently defined as 
NULL
. * Solution: Add 
app_code
to the Primary Key and change it to 
NOT NULL
. 2. High Availability: Replication Number * Problem: 
"replication_num" = "1"
means there is only one copy of your data. If a single Backend (BE) node fails or is restarted, your data will be unavailable, and if the disk fails, the data is lost. * Solution: For production, always use 
"replication_num" = "3"
. Use 
1
only for local development or testing. 3. Bucketing Recommendation * Problem: 
BUCKETS 8
is a fixed value. If your data grows significantly, 8 buckets may be too few, leading to large tablets that slow down compaction and queries. * Solution: Use Automatic Bucketing by removing the 
BUCKETS 8
syntax. StarRocks will automatically determine the number of buckets based on your cluster size and data volume. 4. Memory Management: Persistent Index Primary Key tables store an index in memory to handle upserts. If your table becomes very large (hundreds of millions of rows), this can consume significant RAM. * Recommendation: Ensure 
"enable_persistent_index" = "true"
is set (this is the default in newer versions). This offloads the index to disk (SSD recommended) to save memory. ────────── Improved Table Design Here is the corrected SQL incorporating these fixes:
Copy code
sql
CREATE TABLE dmapp.`dtm_pcb_data_result_extract_pk` (
  `id` bigint(20) NOT NULL COMMENT "ID định danh",
  `request_date` datetime NOT NULL COMMENT "Ngày yêu cầu",
  `app_code` varchar(50) NOT NULL COMMENT "Mã hồ sơ", -- Changed to NOT NULL
  `response_date` datetime NULL COMMENT "Ngày phản hồi",
  `js_path` varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON",
  `js_value` varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON"
) ENGINE=OLAP
PRIMARY KEY(`id`, `request_date`, `app_code`) -- Added app_code here
PARTITION BY date_trunc("month", request_date)
DISTRIBUTED BY HASH(`app_code`) -- Now valid because it's in the PK
PROPERTIES (
  "replication_num" = "3",               -- Set to 3 for production
  "fast_schema_evolution" = "true",
  "enable_persistent_index" = "true"      -- Ensures memory efficiency
);
Summary of Changes: 1. Primary Key: Expanded to 
(id, request_date, app_code)
to satisfy the requirement that distribution and partition columns must be part of the PK. 2. Nullability: Changed 
app_code
to 
NOT NULL
(required for PK columns). 3. Replication: Increased to 
3
for data safety. 4. Buckets: Removed 
BUCKETS 8
to let StarRocks manage tablet sizing automatically. References • knowledge/usage/best-practice.md • best_practices/bucketing.md • table_design/data_distribution/feature-support-data-distribution.md • table_design/data_distribution/Data_distribution.md
l
@Rocky create primary key table for this oracle 2GB table, /
-- QTDM.MAT_FPD_DATA_DTL definition CREATE TABLE "QTDM"."MAT_FPD_DATA_DTL" ( "CONTRACT_NO" VARCHAR2(30) COLLATE "USING_NLS_COMP", "LD_NO" VARCHAR2(20) COLLATE "USING_NLS_COMP", "LMS_CIF_NO" VARCHAR2(12) COLLATE "USING_NLS_COMP", "LOS_APP_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "APP_CODE_REFERENCE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CONTRACT_TYPE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "DISB_DATE" DATE, "DISB_MONTH" VARCHAR2(7) COLLATE "USING_NLS_COMP", "DISB_MONTH_ADJ" VARCHAR2(7) COLLATE "USING_NLS_COMP", "LOAN_AMT" NUMBER, "AMT_BAND" CHAR(3) COLLATE "USING_NLS_COMP", "FIRST_PAYMENT_DATE" DATE, "FIRST_PAYMENT_MONTH" VARCHAR2(7) COLLATE "USING_NLS_COMP", "SECOND_PAYMENT_DATE" DATE, "THIRD_PAYMENT_DATE" DATE, "FOURTH_PAYMENT_DATE" DATE, "LOAN_TERM" NUMBER, "TERM_BAND" VARCHAR2(6) COLLATE "USING_NLS_COMP", "INT_RATE" NUMBER(13,8), "EMI" NUMBER, "EMI_AMOUNT" NUMBER, "INSURANCE_FEE" NUMBER, "INSURANCE_RATE" NUMBER, "MATURITY" DATE, "MATURITY_MONTH" VARCHAR2(7) COLLATE "USING_NLS_COMP", "PAY_OFF_DATE" DATE, "PAY_OFF_MONTH" VARCHAR2(7) COLLATE "USING_NLS_COMP", "WRITEOFF_DATE" DATE, "WO_MONTH" VARCHAR2(7) COLLATE "USING_NLS_COMP", "PRODUCT_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_GROUP" VARCHAR2(7) COLLATE "USING_NLS_COMP", "PRODUCT_GROUP_SORT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_GROUP_SORT_XS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_LINE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_IS_VIP" VARCHAR2(6) COLLATE "USING_NLS_COMP", "CUSTOMER_NAME" VARCHAR2(250) COLLATE "USING_NLS_COMP", "GENDER" VARCHAR2(50) COLLATE "USING_NLS_COMP", "DATE_OF_BIRTH" DATE, "CUS_AGE" NUMBER, "AGE_BAND" VARCHAR2(5) COLLATE "USING_NLS_COMP", "CUSTOMER_TYPE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "SCORE_BAND_APPROVED" VARCHAR2(50) COLLATE "USING_NLS_COMP", "APP_CODE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "CREATED_DATE" DATE, "STATUS_CREATED_DATE" DATE, "APPLICATION_DATE" DATE, "SALE_SUBMIT_DATE" DATE, "APPROVAL_DATE" DATE, "SIGN_CONTRACT_DATE" DATE, "LAST_UPDATED_DATE" DATE, "LAST_UPDATED_BY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "DETAIL_LAST_UPDATED_DATE" DATE, "DETAIL_LAST_UPDATED_BY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "STATUS_LAST_UPDATED_DATE" DATE, "STATUS_LAST_UPDATED_BY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "TEMPORARY_ADDRESS_PROVINCE" VARCHAR2(250) COLLATE "USING_NLS_COMP", "TEMPORARY_ADDRESS_PROVINCE_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "TEMPORARY_ADDRESS_DISTRICT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "TEMPORARY_ADDRESS_DISTRICT_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "TEMPORARY_ADDRESS_WARD" VARCHAR2(50) COLLATE "USING_NLS_COMP", "TEMPORARY_ADDRESS_WARD_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PERMANENT_ADDRESS_PROVINCE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PERMANENT_ADDRESS_PROVINCE_LBL" VARCHAR2(180) COLLATE "USING_NLS_COMP", "PERMANENT_ADDRESS_DISTRICT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PERMANENT_ADDRESS_DISTRICT_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PERMANENT_ADDRESS_WARD_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "TEMPORARY_ADDRESS_REGION" VARCHAR2(50) COLLATE "USING_NLS_COMP", "STAYING_PROVINCE_NAME_ADJ" VARCHAR2(50) COLLATE "USING_NLS_COMP", "STAYING_ADD_PROVINCE" VARCHAR2(200) COLLATE "USING_NLS_COMP", "STAYING_REGION" VARCHAR2(50) COLLATE "USING_NLS_COMP", "SALE_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "SALE_HR_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "DATA_SOURCE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CRM_SOURCE_CODE" VARCHAR2(200) COLLATE "USING_NLS_COMP", "PARTNER_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PARTNER_SCORE_MIN" NUMBER(8,2), "PARTNER_SCORE_MAX" NUMBER(8,2), "CHANNEL_DETAIL" VARCHAR2(32) COLLATE "USING_NLS_COMP", "CHANNEL" VARCHAR2(11) COLLATE "USING_NLS_COMP", "CHANNEL_OLD" VARCHAR2(13) COLLATE "USING_NLS_COMP", "SUB_CHANNEL" VARCHAR2(13) COLLATE "USING_NLS_COMP", "SALE_CHANNEL" VARCHAR2(8) COLLATE "USING_NLS_COMP", "THIRD_PARTIES" VARCHAR2(12) COLLATE "USING_NLS_COMP", "LAST_FIELD_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "FIELD_ACTION" NUMBER, "CCN_BEGIN_DATE" DATE, "CCN_END_DATE" DATE, "CUSTOMER_INCOME" NUMBER(20,2), "INCOME_FIN" NUMBER, "INCOME_BAND" CHAR(3) COLLATE "USING_NLS_COMP", "INCOME_TYPE" CHAR(15) COLLATE "USING_NLS_COMP", "BUREAU_INFO" VARCHAR2(6) COLLATE "USING_NLS_COMP", "JOB_TYPE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "FINAL_SCORE" NUMBER(38,0), "CUSTOMER_RANK" VARCHAR2(100) COLLATE "USING_NLS_COMP", "LATEST_SCORING" VARCHAR2(4000) COLLATE "USING_NLS_COMP", "LATEST_RANK_SCORE" VARCHAR2(4000) COLLATE "USING_NLS_COMP", "LATEST_RANK_SCORE_NEW" VARCHAR2(50) COLLATE "USING_NLS_COMP", "SCORE_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "SCORE_LBL_SORT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "SCORE_GROUP" VARCHAR2(50) COLLATE "USING_NLS_COMP", "S37_RESULT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "S37_RESULT_LBL" VARCHAR2(250) COLLATE "USING_NLS_COMP", "PCB_RESULT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PCB_RESULT_LBL" VARCHAR2(250) COLLATE "USING_NLS_COMP", "PCB_HAS_RESULT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CIC_RESULT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CIC_RESULT_LBL" VARCHAR2(100) COLLATE "USING_NLS_COMP", "CIC_HAS_RESULT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PTI_CA" NUMBER(20,2), "PTI_APPROVAL" NUMBER(20,2), "PTI_APP" NUMBER(20,2), "DTI_CA" NUMBER(20,2), "DTI_APPROVAL" NUMBER(20,2), "DTI_APP" NUMBER(20,2), "EDUCATION" VARCHAR2(50) COLLATE "USING_NLS_COMP", "JOB" VARCHAR2(50) COLLATE "USING_NLS_COMP", "EXPERIENCE" NUMBER, "EXP_BAND" CHAR(3) COLLATE "USING_NLS_COMP", "LIVING_TIME" NUMBER, "MARITAL_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "ACCOMMODATION_TYPE_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "INCOME_RESOURCE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_METHOD" VARCHAR2(50) COLLATE "USING_NLS_COMP", "DISBURSEMENT_TYPE" VARCHAR2(21) COLLATE "USING_NLS_COMP", "PRODUCT_CLASS" VARCHAR2(24) COLLATE "USING_NLS_COMP", "FIRST_DATE_DPD_1" DATE, "FIRST_DATE_DPD_31" DATE, "FIRST_DATE_DPD_61" DATE, "FIRST_DATE_DPD_91" DATE, "FIRST_DATE_DPD_181" DATE, "FIRST_DATE_DPD_361" DATE, "APP_SEQUENCE_" NUMBER, "MONTH_INTERVAL_BY_APP" NUMBER, "LOAN_SEQUENCE_" NUMBER, "MONTH_INTERVAL_BY_LOAN" NUMBER, "VIDEO_CALL" NUMBER, "FIELD" NUMBER, "FIELD_WAIVED" NUMBER, "CBSCORE_BAND" VARCHAR2(9) COLLATE "USING_NLS_COMP", "ACCOUNT_NUMBER" VARCHAR2(35) COLLATE "USING_NLS_COMP", "CBSCORE_HIT_TYPE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "LOS_CIF_NO" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CONTRACT_STATUS" VARCHAR2(20) COLLATE "USING_NLS_COMP", "FPD00_OS_DAILY" NUMBER, "FPD00_OS_EOM" NUMBER, "FPD00_BASE_AMT" NUMBER, "FPD00_BASE_CASE" NUMBER, "FPD00_BASE_OS_D" NUMBER, "FPD00_BASE_OS_EOM" NUMBER, "FPD01_OS_DAILY" NUMBER, "FPD01_OS_EOM" NUMBER, "FPD01_BASE_AMT" NUMBER, "FPD01_BASE_CASE" NUMBER, "FPD01_BASE_OS_D" NUMBER, "FPD01_BASE_OS_EOM" NUMBER, "FPD05_OS_DAILY" NUMBER, "FPD05_OS_EOM" NUMBER, "FPD05_BASE_AMT" NUMBER, "FPD05_BASE_CASE" NUMBER, "FPD05_BASE_OS_D" NUMBER, "FPD05_BASE_OS_EOM" NUMBER, "FPD10_OS_DAILY" NUMBER, "FPD10_OS_EOM" NUMBER, "FPD10_BASE_AMT" NUMBER, "FPD10_BASE_CASE" NUMBER, "FPD10_BASE_OS_D" NUMBER, "FPD10_BASE_OS_EOM" NUMBER, "FPD15_OS_DAILY" NUMBER, "FPD15_OS_EOM" NUMBER, "FPD15_BASE_AMT" NUMBER, "FPD15_BASE_CASE" NUMBER, "FPD15_BASE_OS_D" NUMBER, "FPD15_BASE_OS_EOM" NUMBER, "FPD30_OS_DAILY" NUMBER, "FPD30_OS_EOM" NUMBER, "FPD30_BASE_AMT" NUMBER, "FPD30_BASE_CASE" NUMBER, "FPD30_BASE_OS_D" NUMBER, "FPD30_BASE_OS_EOM" NUMBER, "FPD60_OS_DAILY" NUMBER, "FPD60_OS_EOM" NUMBER, "FPD60_BASE_AMT" NUMBER, "FPD60_BASE_CASE" NUMBER, "FPD60_BASE_OS_D" NUMBER, "FPD60_BASE_OS_EOM" NUMBER, "NST90_OS_DAILY" NUMBER, "NST90_OS_EOM" NUMBER, "NST90_BASE_AMT" NUMBER, "NST90_BASE_CASE" NUMBER, "NST90_BASE_OS_D" NUMBER, "NST90_BASE_OS_EOM" NUMBER ) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_MHQTRR" ; CREATE INDEX "QTDM"."IDX_MAT_FPD_DATA_DTL_CONTRACT_NO" ON "QTDM"."MAT_FPD_DATA_DTL" ("CONTRACT_NO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DM_MHQTRR" ; @Rocky
r
For a 2GB Oracle table, a Primary Key table in StarRocks is a great choice as it supports high-performance upserts and real-time updates. Since 2GB is relatively small for StarRocks, the design should focus on simplicity and enabling automatic features. Here is the recommended design based on your previous schema: Recommended StarRocks SQL
Copy code
sql
CREATE TABLE dmapp.dtm_pcb_data_result_extract_pk (
    -- 1. Use BIGINT for Oracle's NUMBER(ID)
    `id` bigint(20) NOT NULL COMMENT "ID định danh",
    `request_date` datetime NOT NULL COMMENT "Ngày yêu cầu",
    -- 2. Distribution column must be NOT NULL in PK tables
    `app_code` varchar(50) NOT NULL COMMENT "Mã hồ sơ", 
    `response_date` datetime NULL COMMENT "Ngày phản hồi",
    `js_path` varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON",
    `js_value` varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON"
) ENGINE=OLAP
-- 3. PK must include all Partition and Distribution columns
PRIMARY KEY(`id`, `request_date`, `app_code`)
-- 4. Automatic Partitioning by month
PARTITION BY date_trunc("month", `request_date`)
-- 5. Automatic Bucketing (Recommended for small 2GB tables)
DISTRIBUTED BY HASH(`app_code`)
PROPERTIES (
    "replication_num" = "3",               -- Standard for production
    "enable_persistent_index" = "true",    -- Saves RAM for PK tables
    "fast_schema_evolution" = "true"
);
Why this design for a 2GB table? 1. Primary Key Constraints: In StarRocks PK tables, any column used in 
PARTITION BY
(
request_date
) or 
DISTRIBUTED BY
(
app_code
) must be part of the Primary Key definition. 2. Automatic Bucketing: By omitting 
BUCKETS X
, StarRocks will automatically determine the best tablet count for your 2GB of data based on your cluster size. This prevents "small file" issues. 3. Automatic Partitioning: Using 
date_trunc
ensures StarRocks creates new monthly partitions automatically as data arrives, preventing the "out of partition range" error you saw earlier. 4. Persistent Index: Since Primary Key tables store their index in memory, setting 
"enable_persistent_index" = "true"
ensures that as the table grows beyond 2GB, it won't consume excessive RAM (it offloads the index to disk/SSD). 5. Oracle Mapping: * 
VARCHAR2
maps to 
VARCHAR
. * 
NUMBER
(for IDs) maps to 
BIGINT
. * 
DATE/TIMESTAMP
maps to 
DATETIME
. Loading the 2GB data Since the table is only 2GB, you can load it very quickly using: * External Catalog: Use a JDBC Catalog to 
INSERT INTO ... SELECT
directly from Oracle. * DataX / SeaTunnel: Standard ETL tools for migrating from Oracle to StarRocks. * Stream Load: If you export the Oracle table to a CSV or JSON file first.
This Oracle table is a wide "detail" table used for tracking loan performance (FPD - First Payment Default). To migrate this to StarRocks effectively, we should use a Primary Key table (since loan statuses like 
CONTRACT_STATUS
and 
PAY_OFF_DATE
likely update over time) or a Duplicate Key table if you are just appending snapshots. Below is the optimized StarRocks design. Key Design Choices: 1. Table Model: I used the Primary Key model to support efficient updates (UPSERTs) as loan details change. 2. Primary Key: Defined as 
(CONTRACT_NO, DISB_DATE)
. In StarRocks PK tables, the partition column must be part of the Primary Key. 3. Partitioning: Partitioned by month on 
DISB_DATE
using Automatic Partitioning. This is ideal for financial data where queries usually filter by disbursement period. 4. Data Types: * Oracle 
NUMBER
$\rightarrow$ 
DECIMAL(20, 2)
for amounts (to maintain precision). * Oracle 
NUMBER(13,8)
$\rightarrow$ 
DECIMAL(13, 8)
for interest rates. * Oracle 
VARCHAR2
$\rightarrow$ 
VARCHAR
. 5. Distribution: Distributed by 
HASH(CONTRACT_NO)
to ensure data is spread evenly across the cluster. ────────── StarRocks DDL
Copy code
sql
CREATE TABLE "QTDM"."MAT_FPD_DATA_DTL" (
    -- Primary Identifiers (Must be NOT NULL for PK)
    "CONTRACT_NO" VARCHAR(30) NOT NULL COMMENT "Số hợp đồng",
    "DISB_DATE" DATE NOT NULL COMMENT "Ngày giải ngân",
    
    -- Other Identifiers
    "LD_NO" VARCHAR(20) NULL,
    "LMS_CIF_NO" VARCHAR(12) NULL,
    "LOS_APP_CODE" VARCHAR(50) NULL,
    "APP_CODE" VARCHAR(20) NULL,
    "APP_CODE_REFERENCE" VARCHAR(50) NULL,
    
    -- Loan Info
    "CONTRACT_TYPE" VARCHAR(50) NULL,
    "DISB_MONTH" VARCHAR(7) NULL,
    "LOAN_AMT" DECIMAL(20, 2) NULL,
    "LOAN_TERM" DECIMAL(20, 2) NULL,
    "INT_RATE" DECIMAL(13, 8) NULL,
    "EMI" DECIMAL(20, 2) NULL,
    "EMI_AMOUNT" DECIMAL(20, 2) NULL,
    "INSURANCE_FEE" DECIMAL(20, 2) NULL,
    "MATURITY" DATE NULL,
    "PAY_OFF_DATE" DATE NULL,
    "WRITEOFF_DATE" DATE NULL,
    
    -- Product & Customer Info
    "PRODUCT_CODE" VARCHAR(50) NULL,
    "PRODUCT_GROUP" VARCHAR(7) NULL,
    "CUSTOMER_NAME" VARCHAR(250) NULL,
    "DATE_OF_BIRTH" DATE NULL,
    "CUSTOMER_INCOME" DECIMAL(20, 2) NULL,
    "MARITAL_STATUS" VARCHAR(50) NULL,
    
    -- Scoring & Results
    "FINAL_SCORE" BIGINT NULL,
    "PCB_RESULT" VARCHAR(50) NULL,
    "CIC_RESULT" VARCHAR(50) NULL,
    "SCORE_BAND_APPROVED" VARCHAR(50) NULL,
    
    -- DPD / Delinquency Tracking (FPD Metrics)
    "FIRST_DATE_DPD_1" DATE NULL,
    "FIRST_DATE_DPD_31" DATE NULL,
    "FPD30_BASE_AMT" DECIMAL(20, 2) NULL,
    "NST90_BASE_AMT" DECIMAL(20, 2) NULL,
    
    -- Audit Fields
    "CREATED_DATE" DATE NULL,
    "APPLICATION_DATE" DATE NULL,
    "APPROVAL_DATE" DATE NULL,
    "LAST_UPDATED_DATE" DATE NULL,
    "CONTRACT_STATUS" VARCHAR(20) NULL
    
    -- Note: I have truncated the 100+ columns for brevity, 
    -- but you should include all of them following the pattern above.
) ENGINE=OLAP
PRIMARY KEY("CONTRACT_NO", "DISB_DATE")
PARTITION BY DATE_TRUNC("month", "DISB_DATE")
DISTRIBUTED BY HASH("CONTRACT_NO")
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true",
    "fast_schema_evolution" = "true"
);
Important Considerations: 1. Column Names: StarRocks is case-sensitive for column names if quoted (e.g., 
"CONTRACT_NO"
). It is usually easier to use lowercase without quotes in StarRocks unless you strictly need to match the Oracle case. 2. The "Number" Type: Oracle's 
NUMBER
without precision can be very large. If any columns are meant to be simple integers, use 
INT
or 
BIGINT
. For currency, always use 
DECIMAL
. 3. Replication: Ensure 
"replication_num" = "3"
is used for production environments. 4. Performance: If you frequently query by 
LMS_CIF_NO
or 
APP_CODE
in addition to the primary key, you can add Bitmap Indices on those columns to speed up filtering: ```sql
ALTER TABLE "QTDM"."MAT_FPD_DATA_DTL" ADD INDEX idx_cif (LMS_CIF_NO) USING BITMAP; ``` Would you like me to help with the mapping for the remaining DPD (DPD) or DPD) columns?
l
@Rocky this 2GB table should used partition?
r
For a 2GB table, partitioning is optional but often recommended if you plan to manage data by time. In StarRocks, 2GB is considered a very small dataset. Here is how to decide: 1. When you should NOT partition (Keep it simple) If the table is a dimension or lookup table (e.g., a list of branch codes, product categories) and it stays around 2GB, you do not need partitioning. A single partition with 8-16 buckets is more than enough. * Why: Partitioning adds metadata overhead. For very small tables, the performance gain from "partition pruning" is negligible compared to the complexity of managing partitions. 2. When you SHOULD still partition (Lifecycle & Growth) Even if the table is only 2GB now, you should use partitioning if: * Data Retention (TTL): You need to delete old data regularly (e.g., "keep only the last 2 years"). It is much faster to 
DROP PARTITION
than to run a 
DELETE
query. * Growth: If this table grows by 2GB every month, it will be 24GB in a year and 100GB+ in a few years. Starting with monthly partitions now prevents a painful migration later. * Query Patterns: If 99% of your queries filter by 
DISB_DATE
, partitioning by month allows StarRocks to skip reading 90% of the data on disk. 3. Recommendation for your FPD table Given your previous table was a Loan Detail (FPD) table with 100+ columns: * Recommendation: Use Monthly Partitioning. * Reason: Financial data almost always has a lifecycle (data matures, or you only care about recent years) and is queried by specific time periods (e.g., "Performance for Jan 2024"). Summary Rule of Thumb: * Total Size < 10GB: No partition needed unless for TTL. * Total Size > 50GB or > 50 million rows: Partitioning is highly recommended. * Per Partition Size: Aim for 10GB - 100GB per partition for optimal performance. Since your table is only 2GB total, a single partition is technically fine, but "Monthly" is a safer design for future-proofing. How to check if your 2GB table is "too small" for its current partitions: Run 
SHOW TABLETS FROM table_name;
. If you have hundreds of tablets and each one is only a few MBs, you have "over-partitioned" the table, which can slow down the FE (Frontend). Aim for tablet sizes between 100MB and 1GB (compressed). References • best_practices/partitioning.md • knowledge/usage/best-practice.md • best_practices/query_tuning/schema_tuning.md
l
@Rocky this sql correct? SUBMIT TASK qtdm.mat_fpd_data_dtl SCHEDULE START(2026-06-06T08:15) EVERY(1 MONTHS) AS insert into qtdm.mat_fpd_data_dtl by name select * from 
dmapp_link
.`mat_fpd_data_dtl_link`;
r
The SQL you provided has a few syntax errors and formatting issues. Specifically, the 
SCHEDULE
clause requires the 
INTERVAL
keyword, and the 
START
time should be a quoted string. Corrected SQL
Copy code
sql
SUBMIT TASK mat_fpd_task
SCHEDULE START('2026-06-06 08:15:00') EVERY(INTERVAL 1 MONTH)
AS
INSERT INTO qtdm.mat_fpd_data_dtl BY NAME 
SELECT * FROM `dmapp_link`.`your_db_name`.`mat_fpd_data_dtl_link`;
Key Corrections: 1. 
EVERY
Syntax: You must include the 
INTERVAL
keyword. Use 
EVERY(INTERVAL 1 MONTH)
instead of 
EVERY(1 MONTHS)
. 2. 
START
Syntax: The start time must be a string enclosed in single quotes. Use the format 
'YYYY-MM-DD HH:MM:SS'
. 3. External Table Reference: If 
dmapp_link
is a Catalog, you must use a 3-part name: 
catalog.database.table
. If it is just a database in your local StarRocks cluster, the 2-part name is fine. 4. 
BY NAME
: This is supported from StarRocks v3.3.1 onwards. Ensure your cluster is on this version or newer. 5. Task Name: I changed the task name to 
mat_fpd_task
. While you can use dots in a task name, it's safer to use a simple identifier to avoid confusion with database/table names. Additional Notes: * Version Requirement: Periodic scheduling using 
SCHEDULE
was introduced in StarRocks v3.3. * Monitoring: You can check if the task was created using:
Copy code
sql
    SELECT * FROM INFORMATION_SCHEMA.tasks WHERE task_name = 'mat_fpd_task';
* Execution History: To see if the monthly runs are succeeding, use:
Copy code
sql
    SELECT * FROM INFORMATION_SCHEMA.task_runs WHERE task_name = 'mat_fpd_task';
References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • integrations/airflow.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'}.
r
The error occurs because the 
SUBMIT TASK
 scheduler in StarRocks (currently up to v3.3/v3.4) does not support 
MONTH
or 
WEEK
as time units. It only supports 
SECOND
, 
MINUTE
, 
HOUR
, and 
DAY
. Additionally, your original SQL was missing the mandatory 
INTERVAL
keyword. Recommended Workaround Since 
MONTH
is not supported, you should use 
30 DAY
. Note that this will run every 30 days, rather than on the specific same day of each calendar month.
Copy code
sql
SUBMIT TASK mat_fpd_task
SCHEDULE START('2026-06-06 08:15:00') EVERY(INTERVAL 30 DAY)
AS
INSERT INTO qtdm.mat_fpd_data_dtl BY NAME 
SELECT * FROM `dmapp_link`.`your_db_name`.`mat_fpd_data_dtl_link`;
Why your original SQL failed: 1. Unsupported Unit: As indicated by your error message, 
MONTH
(or 
MONTHS
) is not recognized by the task engine. 2. Missing 
INTERVAL
: The syntax must be 
EVERY(INTERVAL <value> <unit>)
. 3. Start Time Format: The 
START
timestamp must be enclosed in single quotes. If you strictly need "Monthly" (e.g., the 6th of every month): If 
30 DAY
is not precise enough for your financial reporting requirements, the built-in StarRocks scheduler cannot handle calendar-month logic yet. In this case, it is recommended to use an external orchestrator like Apache Airflow, DolphinScheduler, or even a simple Cron job calling the StarRocks SQL client. How to manage the task: * Check if task is running: 
SELECT * FROM information_schema.task_runs WHERE task_name = 'mat_fpd_task';
* Drop the task: 
DROP TASK mat_fpd_task;
* Change schedule: You must 
DROP
and 
SUBMIT
again to change the interval. References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • administration/management/FE_configuration.md
l
@Rocky SQL Error [1064] [42000]: Build Exec OlapScanNode fail, scan info is invalid
r
Sorry, I encountered an error while processing your request: 503 UNAVAILABLE. {'error': {'code': 503, 'message': 'Deadline expired before operation could complete.', 'status': 'UNAVAILABLE'}}
l
@Rocky got this error when query table: SQL Error [1064] [42000]: Build Exec OlapScanNode fail, scan info is invalid
r
The error 
Build Exec OlapScanNode fail, scan info is invalid
usually occurs because the StarRocks Frontend (FE) cannot find a healthy or queryable tablet replica to scan for your query. Here are the most common causes and how to troubleshoot them: 1. Backend (BE) Nodes are Down If one or more BE nodes are offline, the query planner may fail to find the data it needs. * Check BE Status:
Copy code
sql
    SHOW PROC '/backends';
Ensure that the 
Alive
column is 
true
for all nodes and 
LastHeartbeat
is recent. 2. Unhealthy Tablets Tablets (data shards) can become "unhealthy" if replicas are lost or inconsistent. * Check Overall Cluster Health:
Copy code
sql
    SHOW PROC '/statistic';
Look at the 
UnhealthyTablets
column. If it is greater than 0, you have data integrity issues. * Find the Specific Tablet: If the error message includes a specific tablet ID (e.g., 
...tablet: 12345
), run:
Copy code
sql
    SHOW TABLET 12345;
Follow the 
DetailCmd
in the output to see the state of each replica. Look for replicas marked as 
BAD
, 
VERSION_ERROR
, or those that are not 
ALIVE
. 3. External Catalog Issues If you are querying an External Table (e.g., Hive, Iceberg, or a JDBC catalog), this error often means the FE failed to fetch the scan range or metadata from the remote source. * Action: Try refreshing the catalog metadata:
Copy code
sql
    REFRESH EXTERNAL TABLE your_table_name;
    -- Or for the whole catalog
    REFRESH CATALOG your_catalog_name;
* Check if the network between StarRocks and the external data source (HMS, S3, etc.) is stable. 4. Metadata Inconsistency If you recently performed a 
DROP BACKEND ... FORCE
or manually modified metadata, the FE's view of where data is stored might be incorrect. * Action: In many cases, the cluster will try to repair itself automatically. You can try to force a repair on a specific table:
Copy code
sql
    ADMIN REPAIR TABLE database_name.table_name;
5. High Load or Timeout If the FE is under extreme memory pressure or experiencing Full GC, the planning phase might fail to build the scan node correctly. * Check FE Logs: Look at 
fe.log
and 
fe.warn.log
for any 
Exception
or 
Full GC
messages around the time of the error. Summary of next steps: 1. Run 
SHOW PROC '/backends';
to ensure all BEs are alive. 2. Run 
SHOW PROC '/statistic';
to check for unhealthy tablets. 3. If this is an external table, run 
REFRESH CATALOG <name>;
. References • faq/Sql_faq.md • release_notes/release-2.5.md • knowledge/admin_and_operation/shared_data_compaction.md
l
@Rocky DELETE FROM LPS.KBV_SUMMARY WHERE SYS_RUN_DATE>= DATE_SUB(CURDATE(), INTERVAL 3 DAY) what wrong?
r
The issue with your 
DELETE
statement depends on the table engine (Model) of 
LPS.KBV_SUMMARY
. In StarRocks, the 
DELETE
statement has different restrictions based on the table type: 1. For Duplicate Key, Aggregate, or Unique Key Tables In these table models, StarRocks has a strict restriction: the right side of a comparison in the 
WHERE
clause must be a literal value (a constant string or number). It cannot be a function or an expression. * Why it fails: 
DATE_SUB(CURDATE(), INTERVAL 3 DAY)
is an expression/function, which is not supported for these table types. * The Error you likely see: 
Right expr of binary predicate should be value
. * Solution: You must calculate the date string beforehand and pass it as a literal.
Copy code
sql
    -- If today is 2026-06-09, use the actual date string:
    DELETE FROM LPS.KBV_SUMMARY WHERE SYS_RUN_DATE >= '2026-06-06';
2. For Primary Key Tables If your table is a Primary Key table, it supports "full 
DELETE...WHERE
semantics" (since v2.3). In this case, your original syntax should theoretically work. If it is still failing on a Primary Key table, check: * Version: Ensure you are on StarRocks 2.3 or higher. * Syntax: Ensure there are no hidden characters (like the 
>
in your snippet, which should be 
>
). Recommended Alternatives If you are trying to "refresh" data for the last 3 days, using 
DELETE
followed by 
INSERT
is often less efficient than 
INSERT OVERWRITE
:
Copy code
sql
-- This automatically replaces data for the specified logic in one atomic operation
INSERT OVERWRITE LPS.KBV_SUMMARY 
SELECT * FROM source_table 
WHERE SYS_RUN_DATE >= DATE_SUB(CURDATE(), INTERVAL 3 DAY);
Summary of Constraints for 
DELETE
(Non-Primary Key): * Only AND is supported: You cannot use 
OR
in a single 
DELETE
statement. * Only Key Columns: For Aggregate/Unique tables, you can only delete based on columns defined as keys. * Literal Values Only: As mentioned, functions like 
NOW()
, 
CURDATE()
, or 
DATE_SUB()
are not allowed in the 
WHERE
clause. References • faq/Sql_faq.md • faq/operation_maintenance_faq.md • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • table_design/table_types/primary_key_table.md
l
@Rocky GRANT SELECT ON TABLE cpm.ofs_lead_css_mv IN DATABASE cpm TO 'thaoqth'@'%'; this one correct
r
Your SQL statement has a few syntax issues for StarRocks. Specifically, the 
IN DATABASE
clause is redundant when you use the 
db.object
notation, and the 
USER
keyword is missing before the user identity. If 
ofs_lead_css_mv
is a Materialized View, use this syntax:
Copy code
sql
GRANT SELECT ON MATERIALIZED VIEW cpm.ofs_lead_css_mv TO USER 'thaoqth'@'%';
What was wrong: 1. Missing 
USER
keyword: In StarRocks, you must specify whether you are granting to a 
USER
or a 
ROLE
. 2. Redundant `IN DATABASE`: The 
IN DATABASE
clause is only used when granting privileges on all objects in a database (e.g., 
ON ALL TABLES IN DATABASE cpm
). When granting to a specific table or view, you just use 
database_name.object_name
. 3. Object Type: While 
ON TABLE
sometimes works for SELECT, it is best practice to use 
ON MATERIALIZED VIEW
for MVs to ensure all relevant privileges (like 
REFRESH
) are handled correctly. Alternative (if you want to grant on all tables/MVs in that DB): If you wanted to grant SELECT on every table or every MV in the 
cpm
database, you would use:
Copy code
sql
-- For all tables
GRANT SELECT ON ALL TABLES IN DATABASE cpm TO USER 'thaoqth'@'%';

-- For all materialized views
GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE cpm TO USER 'thaoqth'@'%';
References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md
l
@Rocky i can select view : select * from cpm.ofs_lead_css_mv; but can not grant: GRANT SELECT ON VIEW cpm.ofs_lead_css_mv TO USER 'thaoqth'@'%'; Reason: SQL Error [1064] [42000]: Getting analyzing error. Detail message: cannot find view ofs_lead_css_mv in db cpm.
r
The error occurs because StarRocks distinguishes between a Logical View and a Materialized View in its permission system. Since your object name ends in 
_mv
, it is almost certainly a Materialized View. In StarRocks, you cannot use the 
VIEW
keyword to grant permissions on a 
MATERIALIZED VIEW
. The Correct Syntax Use 
MATERIALIZED VIEW
instead of `VIEW`:
Copy code
sql
GRANT SELECT ON MATERIALIZED VIEW cpm.ofs_lead_css_mv TO USER 'thaoqth'@'%';
Why the previous one failed: 1. Keyword Mismatch: If you created the object using 
CREATE MATERIALIZED VIEW
, StarRocks stores it as a Materialized View object. The 
GRANT ... ON VIEW
syntax only looks for standard views created with 
CREATE VIEW
. 2. Explicit Type: StarRocks requires you to be explicit about the object type (Table, View, or Materialized View) during a 
GRANT
if you are targeting a specific object. How to verify the object type: If you are unsure what the object is, run:
Copy code
sql
-- This will show whether the 'Table_type' is BASE TABLE, VIEW, or MATERIALIZED VIEW
SHOW FULL TABLES FROM cpm LIKE 'ofs_lead_css_mv';
* If it says 
VIEW
, use 
GRANT SELECT ON VIEW ...
* If it says 
MATERIALIZED VIEW
, use 
GRANT SELECT ON MATERIALIZED VIEW ...
* If it says 
BASE TABLE
, use 
GRANT SELECT ON TABLE ...
References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • data_source/catalog/hive_catalog.md
l
@Rocky how to assign resource like cpu cores, memory limit to user or role, as each user cannot exeet total 20% total server resource
r
To limit a user or role to 20% of your cluster's resources, you use Resource Groups. This feature allows you to define CPU and memory quotas and bind them to specific users or roles using "Classifiers." 1. Enable Resource Groups First, ensure the resource group feature is enabled (it is enabled by default in v3.1+):
Copy code
sql
SET GLOBAL enable_pipeline_engine = true;
2. Create the Resource Group Use the 
CREATE RESOURCE GROUP
statement. To limit a user to 20% of total resources, you will configure 
mem_limit
and 
cpu_weight
.
Copy code
sql
CREATE RESOURCE GROUP user_limit_group
TO 
    (user='thaoqth'), -- Bind to a specific user
    (role='analytics_role') -- Or bind to a role
WITH (
    "mem_limit" = "20%",       -- Hard limit: 20% of total BE memory
    "cpu_weight" = "20",       -- Relative weight for CPU scheduling
    "concurrency_limit" = "10" -- Optional: limit concurrent queries
);
How the Limits Work: * Memory (
mem_limit
): This is a hard limit. If the group's queries exceed 20% of the BE's memory, StarRocks will trigger a spill to disk or cancel the query to protect the system. * CPU (
cpu_weight
): This is a soft limit (relative weight). * If other groups are idle, this user can use more than 20% of the CPU to avoid wasting resources. * If the system is under full load, StarRocks ensures this group gets exactly its weighted share (e.g., if you have another group with 
cpu_weight = 80
, this group is guaranteed 20% of CPU cycles). * CPU Hard Limit (
exclusive_cpu_cores
): Starting from v3.3.5, if you want a hard cap on CPU regardless of system idle time, you can use 
exclusive_cpu_cores
instead of 
cpu_weight
. You must specify an integer number of cores (e.g., if your BE has 40 cores, 20% would be 
8
). 3. Modifying an Existing Group If you already have a group and want to adjust it:
Copy code
sql
ALTER RESOURCE GROUP user_limit_group WITH (
    "mem_limit" = "20%",
    "cpu_weight" = "20"
);
4. Verification To see which resource group a user is assigned to and the current limits:
Copy code
sql
-- View all resource groups and their classifiers
SHOW RESOURCE GROUPS ALL;
Important Notes: * Classifiers: The 
TO
clause creates a "Classifier." If a user matches multiple groups, StarRocks uses a weight-based matching system to pick the most specific one. * Multiple Users: You can assign multiple users or even IP ranges to the same group so they share the 20% bucket. References • administration/management/resource_management/resource_group.md • sql-reference/sql-statements/administration/CREATE_RESOURCE_GROUP.md • sql-reference/sql-statements/administration/ALTER_RESOURCE_GROUP.md References • administration/management/resource_management/resource_group.md
l
@Rocky
Copy code
Query:
  Summary:
     - Query ID: 019eab9c-fd57-7c0d-9dcf-589cb1f7fe74
     - Start Time: 2026-06-09 16:00:53 (+07:00)
     - End Time: 2026-06-09 16:02:23 (+07:00)
     - Total: 1m30s
     - Query Type: Query
     - Query State: Running
     - StarRocks Version: 4.0.10-5b36164
     - User: root
     - Default Db: dmapp
     - Sql Statement: INSERT into dmapp.losrep_application by name select * from `dmapp_link`.`losrepdb_application_link` a 
where a.id > (select MAX(b.id ) from dmapp.losrep_application b) or a.last_updated_date > (select DATE_SUB(MAX(c.last_updated_date), INTERVAL 1 DAY) from dmapp.losrep_application c)

     - Warehouse
     - Variables: parallel_fragment_exec_instance_num=1,max_parallel_scan_instance_num=-1,pipeline_dop=0,enable_adaptive_sink_dop=true,enable_runtime_adaptive_dop=false,runtime_profile_report_interval=10,resource_group=default_wg
     - NonDefaultSessionVariables: {"enable_materialized_view_rewrite":{"defaultValue":true,"actualValue":false},"enable_local_shuffle_agg":{"defaultValue":true,"actualValue":false},"enable_adaptive_sink_dop":{"defaultValue":false,"actualValue":true},"group_concat_max_len":{"defaultValue":1024,"actualValue":1000000},"use_compute_nodes":{"defaultValue":-1,"actualValue":0}}
  Planner:
  Execution:
     - Topology: {"rootId":11,"nodes":[{"id":11,"name":"PROJECT","properties":{"sinkIds":[],"displayMem":false},"children":[10]},{"id":10,"name":"NEST_LOOP_JOIN","properties":{"displayMem":true},"children":[5,9]},{"id":5,"name":"PROJECT","properties":{"displayMem":false},"children":[4]},{"id":9,"name":"EXCHANGE","properties":{"displayMem":true},"children":[8]},{"id":4,"name":"NEST_LOOP_JOIN","properties":{"displayMem":true},"children":[0,3]},{"id":8,"name":"PROJECT","properties":{"sinkIds":[9],"displayMem":false},"children":[7]},{"id":0,"name":"JDBC_SCAN","properties":{"displayMem":false},"children":[]},{"id":3,"name":"EXCHANGE","properties":{"displayMem":true},"children":[2]},{"id":7,"name":"AGGREGATION","properties":{"displayMem":true},"children":[6]},{"id":2,"name":"AGGREGATION","properties":{"sinkIds":[3],"displayMem":true},"children":[1]},{"id":6,"name":"EMPTY_SET","properties":{"displayMem":false},"children":[]},{"id":1,"name":"EMPTY_SET","properties":{"displayMem":false},"children":[]}]}
     - FrontendProfileMergeTime: 1.337ms
     - QueryAllocatedMemoryUsage: 11.675 GB
     - QueryCumulativeCpuTime: 27s156ms
     - QueryCumulativeNetworkTime: 0ns
     - QueryCumulativeOperatorTime: 1m4s
     - QueryCumulativeScanTime: 59s803ms
     - QueryDeallocatedMemoryUsage: 11.618 GB
     - QueryExecutionWallTime: 1m
     - QueryPeakMemoryUsagePerNode: 98.047 MB
     - QueryPeakScheduleTime: 14.526ms
     - QuerySpillBytes: 0.000 B
     - QuerySumMemoryUsage: 98.047 MB
     - ResultDeliverTime: 0ns
    Fragment 0:
       - BackendAddresses: 10.0.27.72:9060
       - InstanceIds: 019eab9c-fd57-7c0d-9dcf-589cb1f7fe75
       - BackendNum: 1
       - BackendProfileMergeTime: 1.632ms
       - InitialProcessDriverCount: 49
       - InitialProcessMem: 12.513 GB
       - InstanceAllocatedMemoryUsage: 11.675 GB
       - InstanceDeallocatedMemoryUsage: 11.618 GB
       - InstanceNum: 1
       - InstancePeakMemoryUsage: 97.685 MB
       - JITCounter: 0
       - JITTotalCostTime: 0ns
       - QueryMemoryLimit: -1.000 B
      Pipeline (id=0):
         - IsGroupExecution: false
         - ActiveTime: 23.951us
           - __MAX_OF_ActiveTime: 56.179us
           - __MIN_OF_ActiveTime: 6.876us
         - BlockByInputEmpty: 2
           - __MAX_OF_BlockByInputEmpty: 1
           - __MIN_OF_BlockByInputEmpty: 0
         - BlockByOutputFull: 0
         - BlockByPrecondition: 0
         - DegreeOfParallelism: 5
         - DriverTotalTime: 3.955ms
           - __MAX_OF_DriverTotalTime: 4.011ms
           - __MIN_OF_DriverTotalTime: 3.900ms
         - PeakDriverQueueSize: 10
           - __MAX_OF_PeakDriverQueueSize: 4
           - __MIN_OF_PeakDriverQueueSize: 0
         - PendingTime: 3.828ms
           - __MAX_OF_PendingTime: 3.834ms
           - __MIN_OF_PendingTime: 3.822ms
           - InputEmptyTime: 3.831ms
             - __MAX_OF_InputEmptyTime: 3.843ms
             - __MIN_OF_InputEmptyTime: 3.822ms
             - FirstInputEmptyTime: 3.824ms
               - __MAX_OF_FirstInputEmptyTime: 3.829ms
               - __MIN_OF_FirstInputEmptyTime: 3.822ms
         - ScheduleCount: 7
           - __MAX_OF_ScheduleCount: 2
           - __MIN_OF_ScheduleCount: 1
         - TotalDegreeOfParallelism: 5
         - YieldByLocalWait: 0
         - YieldByPreempt: 0
         - YieldByTimeLimit: 0
        NESTLOOP_JOIN_BUILD (plan_node_id=10):
          CommonMetrics:
             - OperatorTotalTime: 14.283us
               - __MAX_OF_OperatorTotalTime: 33.076us
               - __MIN_OF_OperatorTotalTime: 7.124us
             - OutputChunkBytes: 0.000 B
             - PullChunkNum: 0
             - PullRowNum: 0
             - PullTotalTime: 0ns
             - PushChunkNum: 1
               - __MAX_OF_PushChunkNum: 1
               - __MIN_OF_PushChunkNum: 0
             - PushRowNum: 1
               - __MAX_OF_PushRowNum: 1
               - __MIN_OF_PushRowNum: 0
             - PushTotalTime: 871ns
               - __MAX_OF_PushTotalTime: 4.359us
               - __MIN_OF_PushTotalTime: 0ns
             - RuntimeFilterNum: 0
             - RuntimeInFilterNum: 0
          UniqueMetrics:
             - NumBuilders: 5
             - BuildChunks: 3
               - __MAX_OF_BuildChunks: 1
               - __MIN_OF_BuildChunks: 0
             - BuildRows: 4
               - __MAX_OF_BuildRows: 1
               - __MIN_OF_BuildRows: 0
        EXCHANGE_SOURCE (plan_node_id=9):
          CommonMetrics:
             - ConjunctsInputRows: 1
             - ConjunctsOutputRows: 1
             - ConjunctsTime: 812ns
             - JoinRuntimeFilterEvaluate: 0
             - JoinRuntimeFilterHashTime: 0ns
             - JoinRuntimeFilterInputRows: 0
             - JoinRuntimeFilterOutputRows: 0
             - JoinRuntimeFilterTime: 0ns
             - OperatorTotalTime: 14.712us
               - __MAX_OF_OperatorTotalTime: 43.285us
               - __MIN_OF_OperatorTotalTime: 4.851us
             - OutputChunkBytes: 9.000 B
               - __MAX_OF_OutputChunkBytes: 9.000 B
               - __MIN_OF_OutputChunkBytes: 0.000 B
             - PullChunkNum: 1
               - __MAX_OF_PullChunkNum: 1
               - __MIN_OF_PullChunkNum: 0
             - PullRowNum: 1
               - __MAX_OF_PullRowNum: 1
               - __MIN_OF_PullRowNum: 0
             - PullTotalTime: 8.474us
               - __MAX_OF_PullTotalTime: 42.370us
               - __MIN_OF_PullTotalTime: 0ns
             - PushChunkNum: 0
             - PushRowNum: 0
             - PushTotalTime: 0ns
             - RuntimeFilterNum: 0
             - RuntimeInFilterNum: 0
          UniqueMetrics:
             - BufferUnplugCount: 0
             - BytesPassThrough: 0.000 B
             - BytesReceived: 30.000 B
               - __MAX_OF_BytesReceived: 30.000 B
               - __MIN_OF_BytesReceived: 0.000 B
             - ClosureBlockCount: 0
             - ClosureBlockTime: 0ns
             - DecompressChunkTime: 431ns
               - __MAX_OF_DecompressChunkTime: 2.157us
               - __MIN_OF_DecompressChunkTime: 0ns
             - DeserializeChunkTime: 2.849us
               - __MAX_OF_DeserializeChunkTime: 14.249us
               - __MIN_OF_DeserializeChunkTime: 0ns
             - PeakBufferMemoryBytes: 30.000 B
               - __MAX_OF_PeakBufferMemoryBytes: 30.000 B
               - __MIN_OF_PeakBufferMemoryBytes: 0.000 B
             - ReceiverProcessTotalTime: 3.247us
               - __MAX_OF_ReceiverProcessTotalTime: 16.236us
               - __MIN_OF_ReceiverProcessTotalTime: 0ns
             - RequestReceived: 1
               - __MAX_OF_RequestReceived: 1
               - __MIN_OF_RequestReceived: 0
             - WaitLockTime: 16ns
               - __MAX_OF_WaitLockTime: 84ns
               - __MIN_OF_WaitLockTime: 0ns
      Pipeline (id=1):
         - IsGroupExecution: false
         - ActiveTime: 14.711us
           - __MAX_OF_ActiveTime: 36.647us
           - __MIN_OF_ActiveTime: 3.005us
         - BlockByInputEmpty: 0
         - BlockByOutputFull: 0
         - BlockByPrecondition: 0
         - DegreeOfParallelism: 5
         - DriverTotalTime: 4.362ms
           - __MAX_OF_DriverTotalTime: 4.390ms
           - __MIN_OF_DriverTotalTime: 4.333ms
         - PeakDriverQueueSize: 10
           - __MAX_OF_PeakDriverQueueSize: 4
           - __MIN_OF_PeakDriverQueueSize: 0
         - PendingTime: 4.288ms
           - __MAX_OF_PendingTime: 4.289ms
           - __MIN_OF_PendingTime: 4.286ms
           - InputEmptyTime: 4.288ms
             - __MAX_OF_InputEmptyTime: 4.289ms
             - __MIN_OF_InputEmptyTime: 4.286ms
             - FirstInputEmptyTime: 4.288ms
               - __MAX_OF_FirstInputEmptyTime: 4.289ms
               - __MIN_OF_FirstInputEmptyTime: 4.286ms
         - ScheduleCount: 5
           - __MAX_OF_ScheduleCount: 1
           - __MIN_OF_ScheduleCount: 1
         - TotalDegreeOfParallelism: 5
         - YieldByLocalWait: 0
         - YieldByPreempt: 0
         - YieldByTimeLimit: 0
        NESTLOOP_JOIN_BUILD (plan_node_id=4):
          CommonMetrics:
             - OperatorTotalTime: 11.591us
               - __MAX_OF_OperatorTotalTime: 23.106us
               - __MIN_OF_OperatorTotalTime: 6.490us
             - OutputChunkBytes: 0.000 B
             - PullChunkNum: 0
             - PullRowNum: 0
             - PullTotalTime: 0ns
             - PushChunkNum: 1
               - __MAX_OF_PushChunkNum: 1
               - __MIN_OF_PushChunkNum: 0
             - PushRowNum: 1
               - __MAX_OF_PushRowNum: 1
               - __MIN_OF_PushRowNum: 0
             - PushTotalTime: 1.070us
               - __MAX_OF_PushTotalTime: 5.352us
               - __MIN_OF_PushTotalTime: 0ns
             - RuntimeFilterNum: 0
             - RuntimeInFilterNum: 0
          UniqueMetrics:
             - NumBuilders: 5
             - BuildChunks: 2
               - __MAX_OF_BuildChunks: 1
               - __MIN_OF_BuildChunks: 0
             - BuildRows: 2
               - __MAX_OF_BuildRows: 1
               - __MIN_OF_BuildRows: 0
        EXCHANGE_SOURCE (plan_node_id=3):
          CommonMetrics:
             - ConjunctsInputRows: 1
             - ConjunctsOutputRows: 1
             - ConjunctsTime: 968ns
             - JoinRuntimeFilterEvaluate: 0
             - JoinRuntimeFilterHashTime: 0ns
             - JoinRuntimeFilterInputRows: 0
             - JoinRuntimeFilterOutputRows: 0
             - JoinRuntimeFilterTime: 0ns
             - OperatorTotalTime: 9.840us
               - __MAX_OF_OperatorTotalTime: 25.328us
               - __MIN_OF_OperatorTotalTime: 3.899us
             - OutputChunkBytes: 9.000 B
               - __MAX_OF_OutputChunkBytes: 9.000 B
               - __MIN_OF_OutputChunkBytes: 0.000 B
             - PullChunkNum: 1
r
The query profile shows that your query is struggling primarily due to a 
NEST_LOOP_JOIN
 and a slow 
JDBC_SCAN
. In StarRocks, when you use an 
OR
condition combined with subqueries in a 
WHERE
clause, the optimizer often fails to use a Hash Join and falls back to a Nested Loop Join. This forces the engine to evaluate the subqueries repeatedly or in an inefficient manner, which is why your query has been running for 1 minute and 30 seconds. Optimization Strategies 1. Rewrite using a CTE and CROSS JOIN (Recommended) By calculating the 
MAX
values once in a Common Table Expression (CTE), you avoid scanning the target table 
dmapp.losrep_application
twice and allow the optimizer to treat these values as constants.
Copy code
sql
WITH constants AS (
  SELECT 
    MAX(id) as max_id, 
    DATE_SUB(MAX(last_updated_date), INTERVAL 1 DAY) as max_date 
  FROM dmapp.losrep_application
)
INSERT INTO dmapp.losrep_application BY NAME
SELECT a.* 
FROM dmapp_link.losrepdb_application_link a
CROSS JOIN constants c
WHERE a.id > c.max_id OR a.last_updated_date > c.max_date;
2. Rewrite using UNION ALL If the 
OR
still causes performance issues, split the query into two branches. This allows each branch to use simple filters that the JDBC driver can potentially push down to the source database.
Copy code
sql
INSERT INTO dmapp.losrep_application BY NAME
SELECT * FROM (
    SELECT * FROM dmapp_link.losrepdb_application_link
    WHERE id > (SELECT MAX(id) FROM dmapp.losrep_application)
    UNION  -- Use UNION to deduplicate rows that might satisfy both conditions
    SELECT * FROM dmapp_link.losrepdb_application_link
    WHERE last_updated_date > (SELECT DATE_SUB(MAX(last_updated_date), INTERVAL 1 DAY) FROM dmapp.losrep_application)
) t;
Key Issues Found in Profile: * Nested Loop Join: Your topology shows nodes 10 and 4 are 
NEST_LOOP_JOIN
. This is the "slow path" for joins. Hash joins are much faster but cannot always be used with 
OR
subqueries. * JDBC Scan Bottleneck: 
QueryCumulativeScanTime
is ~60 seconds. Since you are reading from an external database via JDBC, StarRocks is limited by the network and the source database's performance. Reducing the amount of data fetched (by pushing down filters) is critical. * NULL Handling: Note that if 
dmapp.losrep_application
is empty, the 
MAX()
subqueries will return 
NULL
. Comparisons like 
id > NULL
always result in 
NULL
(false), so the query will insert 0 rows. Use 
COALESCE(MAX(id), 0)
if you want to handle the empty table case. * Memory: Your query is using ~11.6 GB of cumulative memory. While not excessive for a large insert, the inefficiency of the join type is causing it to hold resources longer than necessary. One-line fix to try first: Set the following variable before running your query to see if the optimizer can pick a better join strategy:
Copy code
sql
SET enable_cbo_push_down_subquery_to_jdbc = true;
(Note: Availability depends on the specific StarRocks version/build you are using.) References • sql-reference/sql-statements/data-manipulation/INSERT.md • administration/query_profile.md • using_starrocks/Join_optimization.md References • sql-reference/sql-statements/data-manipulation/INSERT.md • administration/query_profile.md • using_starrocks/Join_optimization.md • sql-reference/sql-statements/data-manipulation/INSERT.md • administration/query_profile.md
l
@Rocky how to submit multiple tasks, which run in sequence as task1 --> task2 --> ...
r
StarRocks provides an asynchronous task system via the 
SUBMIT TASK
statement (supported since v2.5 for background execution and v3.3 for scheduling), but it does not currently have a native internal syntax for task dependencies (e.g., 
TASK2 AFTER TASK1
). To run multiple tasks in sequence, you have two primary options: 1. External Orchestration (Recommended) The official and most flexible way to handle task sequences (task1 → task2) is using an external orchestrator like Apache Airflow. StarRocks has a dedicated Airflow integration that allows you to: * Define a Directed Acyclic Graph (DAG) for your tasks. * Use the 
SUBMIT TASK
statement to run long-running 
INSERT
or 
CTAS
jobs. * Monitor task completion before starting the next one. Example Airflow logic:
Copy code
python
# Task 1: Submit and wait
task1 = MySqlOperator(task_id='etl_part_1', sql="SUBMIT TASK t1 AS INSERT INTO...")
# Task 2: Runs only after task 1 succeeds
task2 = MySqlOperator(task_id='etl_part_2', sql="SUBMIT TASK t2 AS INSERT INTO...")

task1 >> task2
2. Materialized View Dependencies If your tasks are data transformations that can be modeled as Asynchronous Materialized Views (MVs), StarRocks does manage these dependencies internally. * If 
MV2
is defined as 
SELECT ... FROM MV1
, StarRocks understands the dependency. * When you refresh or schedule these MVs, the system ensures they are updated in the correct order or triggered based on base table changes. 3. Simple Scripting (Manual Sequence) If you do not want to use an orchestrator, you can run tasks in sequence using a simple shell script or a SQL client that supports script execution:
Copy code
bash
# Execute task 1 synchronously (don't use SUBMIT TASK for synchronous wait)
mysql -e "INSERT INTO table1 SELECT..." 
# Execute task 2 after task 1 finishes
mysql -e "INSERT INTO table2 SELECT..."
_Note: If you use 
SUBMIT TASK
, the command returns immediately. To run in sequence via a script, you must query 
INFORMATION_SCHEMA.task_runs
to verify 
task1
is in the 
SUCCESS
state before triggering 
task2
._ Monitoring Your Tasks You can track the progress of any submitted task by querying:
Copy code
sql
-- View task definitions
SELECT * FROM INFORMATION_SCHEMA.tasks;

-- View execution history and status (PENDING, RUNNING, FAILED, SUCCESS)
SELECT * FROM INFORMATION_SCHEMA.task_runs ORDER BY create_time DESC;
References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • integrations/airflow.md • using_starrocks/async_mv/Materialized_view.md References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • integrations/airflow.md
l
@Rocky this one not work CREATE RESOURCE GROUP bui_wg TO (db in ('cpm', 'dm_ds', 'dm_fin', 'dmapp', 'losrep', 'lps', 'mobilecus', 'ofs', 'ops', 'ptkd', 'qtdm', 'telesale', 'uw' )) WITH ( 'mem_limit' = '80%' );
r
The reason your query is failing is likely due to two things: the syntax used for the 
db
classifier and the missing mandatory CPU requirement. 1. Fix the 
db
Syntax The 
db
classifier does not support the 
IN (...)
syntax. Instead, you should provide the database names as a single string separated by commas. 2. Add a CPU Requirement A resource group must have a CPU allocation (either 
cpu_weight
for relative priority or 
exclusive_cpu_cores
for a hard limit). If you don't specify one, the statement will fail. Corrected SQL:
Copy code
sql
CREATE RESOURCE GROUP bui_wg
TO 
    -- Use a single string with comma-separated database names
    (db='cpm,dm_ds,dm_fin,dmapp,losrep,lps,mobilecus,ofs,ops,ptkd,qtdm,telesale,uw')
WITH (
    "cpu_weight" = "10",  -- Mandatory: define the CPU priority
    "mem_limit" = "80%"
);
Alternative: Multiple Classifiers If you prefer, you can also define each database as a separate classifier. This is functionally equivalent:
Copy code
sql
CREATE RESOURCE GROUP bui_wg
TO 
    (db='cpm'), 
    (db='dm_ds'), 
    (db='dm_fin'),
    -- ... add others ...
    (db='uw')
WITH (
    "cpu_weight" = "10",
    "mem_limit" = "80%"
);
Troubleshooting Tip: If it still doesn't work, check for the following: * Privileges: Ensure you have the 
SYSTEM-level CREATE RESOURCE GROUP
privilege. * Pipeline Engine: Ensure the pipeline engine is enabled (it is by default in v3.1+, but required for resource groups to function).
Copy code
sql
    SET GLOBAL enable_pipeline_engine = true;
* Existing Name: Verify that a resource group named 
bui_wg
doesn't already exist. You can check with 
SHOW RESOURCE GROUPS ALL;
. References • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • administration/management/resource_management/resource_group.md References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md
2 Views
Open in Slack
PreviousNext
Powered by