http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hello
r
Hello! I'm Rocky, your StarRocks assistant. How can I help you today?
l
@Rocky CREATE TABLE "DMAPP"."DTM_STAFF_LOAN" ( "ID" NUMBER(19,0), "SYS_RUN_DATE" DATE, "LOS_CIF_NO" VARCHAR2(20) COLLATE "USING_NLS_COMP", "ID_CARD_NUMBER" VARCHAR2(12) COLLATE "USING_NLS_COMP", "DEPARTMENT" VARCHAR2(200) COLLATE "USING_NLS_COMP", "TITLE" VARCHAR2(200) COLLATE "USING_NLS_COMP", "STAFF_NAME" VARCHAR2(200) COLLATE "USING_NLS_COMP", "WORKING_STATUS" VARCHAR2(20) COLLATE "USING_NLS_COMP", "ONBOARD_DATE" DATE, "LEAVE_DATE" DATE, "HR_CODE" VARCHAR2(20) COLLATE "USING_NLS_COMP" ) to primary key ID table
r
Based on the Oracle DDL provided, here is the corresponding StarRocks Primary Key table definition. In StarRocks, Oracle's 
NUMBER(19,0)
is typically mapped to 
BIGINT
, and Oracle's 
DATE
type (which includes time) is mapped to 
DATETIME
.
Copy code
sql
CREATE TABLE dmapp.dtm_staff_loan (
    id              BIGINT NOT NULL,
    sys_run_date    DATETIME,
    los_cif_no      VARCHAR(20),
    id_card_number  VARCHAR(12),
    department      VARCHAR(200),
    title           VARCHAR(200),
    staff_name      VARCHAR(200),
    working_status  VARCHAR(20),
    onboard_date    DATE,
    leave_date      DATE,
    hr_code         VARCHAR(20)
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "enable_persistent_index" = "true"
);
Key Mapping Details: * Data Types: * 
NUMBER(19,0)
is mapped to 
BIGINT
. If your IDs can exceed 
9,223,372,036,854,775,807
, use 
DECIMAL(19,0)
instead. * 
DATE
in Oracle includes time. I've used 
DATETIME
for 
SYS_RUN_DATE
and 
DATE
for the others (assuming onboard/leave dates don't need time precision). * 
VARCHAR2
maps directly to 
VARCHAR
. * Primary Key Model: The 
PRIMARY KEY (id)
clause enables the Primary Key engine, which supports real-time updates and deletes. * Distribution: Primary Key tables require 
DISTRIBUTED BY HASH
, and the bucket key must be a subset of the primary key columns (in this case, 
id
). * Persistence: The property 
"enable_persistent_index" = "true"
is recommended for Primary Key tables to keep the index on disk, preventing high memory usage as the table grows. References • data_source/External_table.md • data_source/catalog/maxcompute_catalog.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/paimon_catalog.md • sql-reference/data-types/semi_structured/VARIANT.md • loading/Flink-connector-starrocks.md
l
@Rocky CREATE TABLE "DMAPP"."DTM_CUSTOMER_RATING" ( "SYS_RUN_DATE" DATE, "LOS_CIF_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP", "SCORE" VARCHAR2(255) COLLATE "USING_NLS_COMP", "STATUS" VARCHAR2(255) COLLATE "USING_NLS_COMP", "CREATED_DATE" DATE ) to primary key SYS_RUN_DATE, LOS_CIF_NO table
r
Based on your Oracle DDL, here is the StarRocks Primary Key table definition. In StarRocks, the Primary Key columns must be defined first in the column list and must be marked as 
NOT NULL
.
Copy code
sql
CREATE TABLE dmapp.dtm_customer_rating (
    sys_run_date   DATE NOT NULL,
    los_cif_no     VARCHAR(255) NOT NULL,
    score          VARCHAR(255),
    status         VARCHAR(255),
    created_date   DATETIME
) 
PRIMARY KEY (sys_run_date, los_cif_no)
DISTRIBUTED BY HASH(los_cif_no)
PROPERTIES (
    "enable_persistent_index" = "true"
);
Key Considerations: * Data Types: * `sys_run_date`: Used 
DATE
(YYYY-MM-DD). If your Oracle source contains time components (hours/minutes/seconds) that are necessary for uniqueness, use 
DATETIME
instead. * `created_date`: Used 
DATETIME
as Oracle 
DATE
types typically include time. * Primary Key Constraints: * The primary key columns (
sys_run_date
, 
los_cif_no
) are placed at the beginning of the table definition. * By default, the total encoded length of the primary key columns should not exceed 128 bytes. If 
los_cif_no
contains very long strings, ensure the total combined size of the key stays within this limit. * Distribution: I used 
DISTRIBUTED BY HASH(los_cif_no)
. In a Primary Key table, the bucket key must be a subset of the primary key columns. * Persistence: The property 
"enable_persistent_index" = "true"
ensures that the primary key index is stored on disk (SSD recommended), preventing high memory consumption as the table grows. References • table_design/table_types/primary_key_table.md • table_design/table_types/table_capabilities.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/StarRocks_table_design.md • administration/management/resource_management/Query_Queues.md • loading/Etl_in_starrocks.md References • table_design/table_types/primary_key_table.md • table_design/table_types/table_capabilities.md
l
@Rocky CREATE TABLE "DMAPP"."DTM_DEBT_LETTER_TEMPLATE" ( "LETTER_TEMPLATE_ID" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "LETTER_CODE" VARCHAR2(20) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "DPD_FROM" NUMBER, "MIN_AMOUNT" NUMBER, "NOTE" VARCHAR2(200) COLLATE "USING_NLS_COMP", "DPD_TO" NUMBER(10,0) ) to duplicate table
r
To convert your Oracle DDL to a StarRocks Duplicate Key table, you can use the following SQL. In StarRocks, a Duplicate Key table is the best choice for storing raw detail data or logs where you want to keep every record even if they have identical values in the key columns.
Copy code
sql
CREATE TABLE dmapp.dtm_debt_letter_template (
    letter_template_id  VARCHAR(60) NOT NULL, -- Increased length to handle UTF-8 bytes
    letter_code         VARCHAR(60) NOT NULL,
    dpd_from            DECIMAL(38, 9),      -- Oracle NUMBER maps to DECIMAL for high precision
    min_amount          DECIMAL(38, 9),
    note                VARCHAR(600),
    dpd_to              BIGINT               -- Oracle NUMBER(10,0) fits in BIGINT
) 
ENGINE=olap
DUPLICATE KEY(letter_template_id, letter_code)
DISTRIBUTED BY HASH(letter_template_id)
PROPERTIES (
    "replication_num" = "3"
);
Key Mapping Notes: * Duplicate Key Model: The 
DUPLICATE KEY(letter_template_id, letter_code)
clause defines the sort order. Unlike Primary Key tables, this model allows multiple rows with the same keys to coexist. * VARCHAR Length: In StarRocks, 
VARCHAR(n)
defines the length in bytes, whereas Oracle's 
VARCHAR2(n)
defines it in characters. To safely accommodate UTF-8 characters (which can take up to 3 bytes each), I have tripled the lengths (e.g., 
VARCHAR(20)
becomes 
VARCHAR(60)
). * NUMBER Mapping: * Unspecified 
NUMBER
: Mapped to 
DECIMAL(38, 9)
to preserve high precision and scale. If these columns are used for scientific calculations where precision is less critical than performance, you could use 
DOUBLE
. * 
NUMBER(10,0)
: Mapped to 
BIGINT
. A 10-digit number can exceed the maximum value of a standard 32-bit 
INT
(2.1 billion), so 
BIGINT
is safer. * Distribution: 
DISTRIBUTED BY HASH(letter_template_id)
ensures data is spread across the cluster. Choose a column with high cardinality for the best distribution. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • data_source/External_table.md • table_design/table_types/duplicate_key_table.md • sql-reference/data-types/semi_structured/Map.md • table_design/StarRocks_table_design.md • loading/Flink-connector-starrocks.md References • data_source/External_table.md • data_source/catalog/maxcompute_catalog.md • data_source/feature-support-data-lake-analytics.md • sql-reference/data-types/semi_structured/VARIANT.md • data_source/catalog/paimon_catalog.md • knowledge/usage/best-practice.md • table_design/StarRocks_table_design.md
l
@Rocky -- DMAPP.DTM_DEBT_LD_CASE_MASKING definition CREATE TABLE "DMAPP"."DTM_DEBT_LD_CASE_MASKING" ( "SYS_RUN_DATE" DATE NOT NULL ENABLE, "ID" NUMBER NOT NULL ENABLE, "LOS_CIF_NO" VARCHAR2(25) COLLATE "USING_NLS_COMP", "LMS_CIF_NO" VARCHAR2(12) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "TEN_KHACH_HANG" VARCHAR2(250) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "SO_HOP_DONG" VARCHAR2(15) COLLATE "USING_NLS_COMP", "USER_CODE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "USER_NAME" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "NGAY_MASKING" DATE, "FLAG_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "FLAG_DESC" VARCHAR2(250) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "UPDATE_USER" VARCHAR2(20) COLLATE "USING_NLS_COMP", "UPLOAD_DATE" DATE, "STATUS" VARCHAR2(5) COLLATE "USING_NLS_COMP", "ALLOCATE" VARCHAR2(10) COLLATE "USING_NLS_COMP" ) to primary key ID table
r
To convert the 
DTM_DEBT_LD_CASE_MASKING
Oracle DDL to a StarRocks Primary Key table with 
ID
as the primary key, use the following definition:
Copy code
sql
CREATE TABLE dmapp.dtm_debt_ld_case_masking (
    id              BIGINT NOT NULL,
    sys_run_date    DATETIME NOT NULL,
    los_cif_no      VARCHAR(75),
    lms_cif_no      VARCHAR(36) NOT NULL,
    ten_khach_hang  VARCHAR(750) NOT NULL,
    so_hop_dong     VARCHAR(45),
    user_code       VARCHAR(60),
    user_name       VARCHAR(150) NOT NULL,
    ngay_masking    DATETIME,
    flag_code       VARCHAR(150) NOT NULL,
    flag_desc       VARCHAR(750) NOT NULL,
    update_user     VARCHAR(60),
    upload_date     DATETIME,
    status          VARCHAR(15),
    allocate        VARCHAR(30)
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "enable_persistent_index" = "true"
);
Key Mapping Notes: * Column Order: In StarRocks Primary Key tables, the primary key column (
id
) must be defined first in the list of columns. * Data Types: * 
NUMBER
: Mapped to 
BIGINT
for the ID. * 
DATE
: Oracle's 
DATE
type includes time, so it is mapped to 
DATETIME
in StarRocks to ensure no data loss. * 
VARCHAR(n)
: StarRocks 
VARCHAR
lengths are in bytes. I have tripled the lengths (e.g., 
VARCHAR2(250)
to 
VARCHAR(750)
) to safely accommodate 3-byte UTF-8 characters often found in names (
ten_khach_hang
). * Primary Key Engine: * `PRIMARY KEY (id)`: Enables the Primary Key model, which is optimized for real-time updates and deletes. * `DISTRIBUTED BY HASH(id)`: Uses the primary key for data distribution, which is required. * `"enable_persistent_index" = "true"`: Recommended to keep the primary key index on disk (SSD) rather than entirely in memory, which allows the table to scale without excessive RAM bottlenecks. References • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • loading/Flink-connector-starrocks.md • sql-reference/data-types/numeric/BIGINT.md • sql-reference/data-types/date-time/DATETIME.md
l
@Rocky how to set default replication_num = 2
r
To set the default replication number to 2 in StarRocks, you can modify the Frontend (FE) configuration. This will apply to any new tables created without a specific 
replication_num
property. 1. Set Dynamically (No Restart Required) You can change the setting immediately using the 
ADMIN
command in your SQL client. Note that this change will be lost if the FE restarts unless you also update the configuration file.
Copy code
sql
ADMIN SET FRONTEND CONFIG ("default_replication_num" = "2");
2. Set Permanently in Configuration File To ensure the setting persists after a restart, add or update the parameter in the 
fe.conf
file of each FE node: 1. Locate your 
fe/conf/fe.conf
file. 2. Add or modify the following line:
Copy code
properties
    default_replication_num = 2
3. Restart the FE service to apply the change. 3. Set per Table (Recommended) Even if the global default is 3, you can specify 2 replicas for a specific table during creation:
Copy code
sql
CREATE TABLE my_table (
    id BIGINT,
    data VARCHAR(100)
)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "replication_num" = "2"
);
Important Notes: * Availability: Using 
replication_num = 2
is generally discouraged for production environments because if one BE node goes down, you lose redundancy. If a second node fails or undergoes maintenance, the data becomes unavailable. * Existing Tables: Changing the global 
default_replication_num
does not change the replica count for existing tables. To change an existing table to 2 replicas, use:
Copy code
sql
    -- For non-partitioned tables
    ALTER TABLE table_name SET ("replication_num" = "2");

    -- For partitioned tables (modifies all existing partitions)
    ALTER TABLE table_name MODIFY PARTITION (*) SET ("replication_num" = "2");
References • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/resource_management/Replica.md References • administration/management/FE_configuration.md • administration/management/resource_management/Replica.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/BE_configuration.md • faq/operation_maintenance_faq.md
l
@Rocky correct? update dwh.af_extract_mapping set af_extract_mapping.AF_SRC_CONN_ID = 'DTM_PRO' where af_extract_mapping..PROJECT_NAME ='DMAPP_PTKD_ORACLE_TO_STARROCKS';
r
No, the SQL you provided has several syntax errors and formatting issues. Here is the corrected version and the reasons for the changes: Corrected SQL
Copy code
sql
UPDATE dwh.af_extract_mapping 
SET AF_SRC_CONN_ID = 'DTM_PRO' 
WHERE PROJECT_NAME = 'DMAPP_PTKD_ORACLE_TO_STARROCKS';
Key Corrections: 1. Removed Slack Formatting: Your snippet contained URL-like artifacts (e.g., 
<http://dwh.af|dwh.af>
). In SQL, you should use the literal table name: 
dwh.af_extract_mapping
. 2. Removed Double Dots: You had 
af_extract_mapping..PROJECT_NAME
. Standard SQL and StarRocks use a single dot (
table.column
) or no dot if the column is unique to the table in the context. 3. Cleaned Column Names: Removed the table name prefix from inside the column name (e.g., 
<http://af_extract_mapping.AF|af_extract_mapping.AF>_SRC_CONN_ID
was simplified to 
AF_SRC_CONN_ID
). Important StarRocks Requirements: * Primary Key Model: In StarRocks, you can only perform an 
UPDATE
statement on tables created with the Primary Key model. If this table was created as a Duplicate, Aggregate, or Unique Key table, the 
UPDATE
command will fail. * Where Clause: The 
WHERE
clause is highly recommended to avoid updating all rows in the table. * Column Order: Ensure 
AF_SRC_CONN_ID
is not part of the Primary Key itself, as Primary Key columns cannot be updated directly; you would need to delete and re-insert the row instead. References • sql-reference/sql-statements/data-manipulation/UPDATE.md • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • faq/loading_faq.md • loading/Flink-connector-starrocks.md
l
@Rocky CREATE TABLE public.logcall_crm ( id serial4 NOT NULL, queue_id varchar(50) NULL, phone_number varchar(20) NULL, ext_answer varchar(20) NULL, call_status varchar(50) NULL, id_snowflake varchar(250) NULL, id_data int8 NULL, campaign_id varchar(250) NULL, start_time timestamp NULL, ringing_time int4 NULL, waiting_time int4 NULL, talking_time int4 NULL, created_at timestamp DEFAULT CURRENT_TIMESTAMP NULL, "data" jsonb NULL, update_at timestamp DEFAULT CURRENT_TIMESTAMP NULL, is_send varchar(255) NULL, destination_caller varchar(255) NULL, recording_url varchar(255) NULL, ext_no_answer _varchar NULL, CONSTRAINT logcall_crm_pkey PRIMARY KEY (id) ); CREATE INDEX idx_id_snowflake ON public.logcall_crm USING btree (id_snowflake); to starrock primary key ID table
r
To convert the PostgreSQL DDL for 
logcall_crm
to a StarRocks Primary Key table, use the following definition. StarRocks DDL
Copy code
sql
CREATE TABLE public.logcall_crm (
    id                 INT NOT NULL AUTO_INCREMENT,
    queue_id           VARCHAR(150),
    phone_number       VARCHAR(60),
    ext_answer         VARCHAR(60),
    call_status        VARCHAR(150),
    id_snowflake       VARCHAR(750),
    id_data            BIGINT,
    campaign_id        VARCHAR(750),
    start_time         DATETIME,
    ringing_time       INT,
    waiting_time       INT,
    talking_time       INT,
    created_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
    data               JSON,
    update_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_send            VARCHAR(765),
    destination_caller VARCHAR(765),
    recording_url      VARCHAR(765),
    ext_no_answer      ARRAY<VARCHAR(255)>
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "enable_persistent_index" = "true",
    "replication_num" = "3"
);

-- Creating a Bitmap Index for id_snowflake to mimic the B-tree index
CREATE INDEX idx_id_snowflake ON public.logcall_crm (id_snowflake) USING BITMAP;
Conversion Notes: 1. 
serial4
to 
AUTO_INCREMENT
: * The PostgreSQL 
serial4
is mapped to 
INT
with the 
AUTO_INCREMENT
attribute. In StarRocks Primary Key tables, 
AUTO_INCREMENT
columns must be part of the primary key. 2. 
jsonb
to 
JSON
: * StarRocks provides a native 
JSON
type that is optimized for storage and query performance. 3. 
_varchar
to 
ARRAY<VARCHAR>
: * PostgreSQL's array type 
_varchar
is converted to 
ARRAY<VARCHAR(255)>
in StarRocks. 4. 
timestamp
to 
DATETIME
: * StarRocks 
DATETIME
is the appropriate equivalent for PostgreSQL's 
timestamp
. 5. 
VARCHAR
Lengths: * I have tripled the 
VARCHAR
lengths (e.g., 
50
to 
150
) because StarRocks 
VARCHAR
limits are in bytes, while PostgreSQL limits are in characters. This ensures multi-byte UTF-8 characters do not cause overflows. 6. Secondary Index: * StarRocks uses Bitmap Indexes instead of B-tree indexes for secondary column acceleration. I've added a Bitmap index for 
id_snowflake
. 7. Primary Key Requirements: * The 
id
column is defined first. * 
DISTRIBUTED BY HASH(id)
is used to ensure even data distribution across the cluster. * 
"enable_persistent_index" = "true"
is added to keep the primary key index on disk, saving memory while maintaining high performance. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md • sql-reference/data-types/semi-structured/JSON.md • sql-reference/data-types/semi-structured/Array.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_INDEX.md
l
@Rocky 3 phone_number varchar(20) Masking khi lên StarRocks 0909323812 ******3812 • Giữ nguyên 4 ký tự cuối. • Thay thế toàn bộ các ký tự còn lại bằng dấu *. Mỗi ký tự là 1 dấu * • Không thay đổi độ dài chuỗi.
r
To achieve this masking logic in StarRocks (keeping the last 4 characters and replacing the rest with 
*
while maintaining the original string length), you can use the 
LPAD
and 
RIGHT
functions. SQL Expression Use the following expression in your 
SELECT
statement:
Copy code
sql
SELECT LPAD(RIGHT(phone_number, 4), LENGTH(phone_number), '*') AS masked_phone
FROM your_table;
How it works: 1. 
RIGHT(phone_number, 4)
: Extracts the last 4 characters of the phone number (e.g., 
"3812"
). 2. 
LENGTH(phone_number)
: Calculates the total length of the original string (e.g., 
10
). 3. *`LPAD(..., length, '*')`*: Takes the 4 characters from Step 1 and pads them on the left with 
*
until the string reaches the original total length. Example Result: Original 
phone_number
 | Masked Result (
masked_phone
) 
0909323812
| 
******3812
12345678901
| 
*******7890
9999
| 
9999
(No masking if length $\le$ 4) Alternative using 
CONCAT
and `REPEAT`: If you prefer a more manual approach:
Copy code
sql
SELECT CONCAT(REPEAT('*', LENGTH(phone_number) - 4), RIGHT(phone_number, 4)) 
FROM your_table;
Recommendation for persistent masking: If you want this to happen automatically for all users, you can create a View:
Copy code
sql
CREATE VIEW public.v_logcall_crm AS
SELECT 
    id,
    LPAD(RIGHT(phone_number, 4), LENGTH(phone_number), '*') AS phone_number,
    -- ... other columns ...
FROM public.logcall_crm;
References • sql-reference/sql-functions/string-functions/lpad.md • sql-reference/sql-functions/string-functions/right.md • sql-reference/sql-functions/string-functions/length.md • sql-reference/sql-functions/string-functions/repeat.md References • sql-reference/sql-functions/string-functions/lpad.md • sql-reference/sql-functions/string-functions/rpad.md • sql-reference/sql-functions/string-functions/left.md • sql-reference/sql-functions/string-functions/ltrim.md • sql-reference/sql-functions/string-functions/strleft.md • sql-reference/sql-functions/string-functions/strright.md • sql-reference/sql-functions/string-functions/right.md • sql-reference/sql-functions/string-functions/rtrim.md
l
@Rocky how to backup and restore starrock like oracle insreasemental, backup new data each day
r
StarRocks does not have a native "incremental level" backup feature identical to Oracle's. Instead, StarRocks achieves incremental backups through Partition-level Backups. The recommended strategy is to partition your tables by day and back up only the new partition each day. 1. Prerequisite: Create a Repository Before backing up, you must define a remote storage location (S3, HDFS, GCS, or MinIO).
Copy code
sql
CREATE REPOSITORY my_backup_repo
WITH BROKER
ON LOCATION "<s3a://my-bucket/starrocks_backups>"
PROPERTIES(
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "aws.s3.region" = "us-east-1"
);
2. Daily Incremental Backup (Partition-based) If your table is partitioned by date (e.g., 
p20231027
), you can back up only that specific "new data" partition.
Copy code
sql
-- Back up only the partition for today
BACKUP SNAPSHOT my_db.daily_snapshot_20231027
TO my_backup_repo
ON (my_table PARTITION (p20231027));
Note: Although the 
type
property defaults to 
"full"
, performing a full backup of a single partition effectively acts as an incremental backup for the whole table. 3. Incremental Restore When restoring, you can choose to restore only the specific missing partitions.
Copy code
sql
RESTORE SNAPSHOT my_db.daily_snapshot_20231027
FROM my_backup_repo
ON (my_table PARTITION (p20231027));
Key Differences from Oracle Incremental: Feature | Oracle Incremental | StarRocks Backup Mechanism | Changed block tracking | Partition-level snapshot Granularity | Block/Datafile | Table or Partition Automation | RMAN manages levels | Manual/Scripted (back up latest partition) Storage | Backupsets/Image copies | Snapshots in Remote Repository Best Practices for "Daily" Backups: 1. Partitioning: Design your tables using Range Partitioning on a date column. This is required to isolate "new data" for backup. 2. Automation: Use a scheduler (like Cron, Airflow, or DolphinScheduler) to trigger the 
BACKUP
command daily, dynamically injecting the partition name. 3. Monitoring: Use 
SHOW BACKUP
to check the status of your asynchronous backup jobs. 4. Shared-data Clusters: If you are using a Shared-data (Cloud-native) cluster, the traditional 
BACKUP
command is not supported. In that architecture, data is already in S3/OSS, and you should use the cloud provider's snapshot/versioning features instead. References • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/BACKUP.md • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • faq/Exporting_faq.md References • administration/management/Backup_and_restore.md • faq/Exporting_faq.md
l
@Rocky how if normal table, not partition
r
For a normal (non-partitioned) table, StarRocks does not support native incremental backups. Every time you run the 
BACKUP
command, StarRocks will perform a Full Backup of the entire table. If you cannot partition your table, here are the three best strategies to handle "incremental" data movement or backups: 1. The "Manual Export" Strategy (Workaround) While the 
BACKUP
command doesn't support incremental logic for normal tables, the 
EXPORT
command allows you to filter data using a 
WHERE
clause. You can export just the new rows to S3/HDFS each day.
Copy code
sql
-- Manually export only today's data to CSV/Parquet on S3
EXPORT TABLE logcall_crm 
WHERE created_at >= '2023-10-27 00:00:00' 
  AND created_at < '2023-10-28 00:00:00'
TO "s3a://my-bucket/incremental_backups/20231027/"
PROPERTIES ("format" = "parquet")
WITH BROKER (...);
2. The "Backup Table" Strategy If you want to keep a snapshot of "new data" inside StarRocks before backing it up, you can use an 
INSERT INTO ... SELECT
to move daily data into a temporary "buffer" table, back that up, and then truncate it. 1. Create a buffer table with the same schema. 2. Insert new data: 
INSERT INTO log_buffer SELECT * FROM logcall_crm WHERE created_at >= ...
3. Back up the buffer: 
BACKUP SNAPSHOT db.snapshot_new_data TO repo ON (log_buffer);
4. Truncate buffer: 
TRUNCATE TABLE log_buffer;
3. The Recommended Way: Convert to Partitioned In StarRocks, partitions are the primary unit of data management. If your table grows large enough to require incremental backups, it is highly recommended to convert it to a Partitioned Table. Even for Primary Key tables (like your 
logcall_crm
), you can partition by a date column (e.g., 
created_at
). This enables the native 
BACKUP ... PARTITION (p20231027)
syntax mentioned earlier. Example of how your DDL should look for incremental support:
Copy code
sql
CREATE TABLE public.logcall_crm (
    id                 INT NOT NULL,
    created_at         DATETIME NOT NULL,
    ...
) 
PRIMARY KEY (id, created_at) -- Partition column must be in PK
PARTITION BY RANGE(created_at) (
    START ("2023-01-01") END ("2024-01-01") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(id);
Summary Comparison Feature | Normal Table | Partitioned Table 
BACKUP
Command | Always Full | Can be Partition-specific (Incremental) Restore Speed | Slow (must restore all) | Fast (can restore specific days) Daily Overhead | High (full data scan) | Low (only scans new partition) Conclusion: For a "normal" table, you must either back up the whole table every day or use the 
EXPORT
workaround. If daily incremental backups are a requirement, Partitioning is the correct architectural choice in StarRocks. References • unloading/Export.md • faq/Exporting_faq.md
l
@Rocky CREATE TABLE public.crm_lead_datas ( id int8 DEFAULT nextval('crm_lead_datas_seq'::regclass) NOT NULL, campaign_id varchar(100) DEFAULT ''::character varying NOT NULL, mobiles varchar(255) DEFAULT ''::character varying NOT NULL, menu_id int4 DEFAULT 0 NOT NULL, action_id int4 DEFAULT 0 NOT NULL, lead_id int8 DEFAULT 0 NOT NULL, product_offer_id int8 DEFAULT 0 NOT NULL, updated_date timestamp DEFAULT CURRENT_TIMESTAMP NULL, call_attempts int4 DEFAULT 0 NULL, active bool DEFAULT true NULL, customer_id varchar(255) NULL, last_call_status varchar(255) NULL, created_at timestamp DEFAULT CURRENT_TIMESTAMP NULL, ext_number varchar(20) NULL, queue_number varchar(20) NULL, callback_time timestamp DEFAULT CURRENT_TIMESTAMP NULL, last_time_note_code timestamp NULL, last_note_code varchar NULL, data_auto_processed jsonb NULL, next_action varchar(20) DEFAULT ''::character varying NULL, flag int4 NULL, CONSTRAINT crm_lead_datas_pkey PRIMARY KEY (id) ); create primary key ID table
r
To create the table in StarRocks using the Primary Key model with 
id
as the primary key, use the following DDL. Since you previously asked about incremental backups, I have included a version with Partitioning at the end. In StarRocks, the partition column must be part of the Primary Key to enable partition-level incremental backups. Option 1: Basic Primary Key Table (Single Partition) Use this if you do not need partition-level management. Note that 
BACKUP
will always be a full backup for this table.
Copy code
sql
CREATE TABLE public.crm_lead_datas (
    id                   BIGINT NOT NULL AUTO_INCREMENT,
    campaign_id          VARCHAR(100) NOT NULL DEFAULT '',
    mobiles              VARCHAR(255) NOT NULL DEFAULT '',
    menu_id              INT NOT NULL DEFAULT 0,
    action_id            INT NOT NULL DEFAULT 0,
    lead_id              BIGINT NOT NULL DEFAULT 0,
    product_offer_id     BIGINT NOT NULL DEFAULT 0,
    updated_date         DATETIME DEFAULT CURRENT_TIMESTAMP,
    call_attempts        INT DEFAULT 0,
    active               BOOLEAN DEFAULT TRUE,
    customer_id          VARCHAR(255),
    last_call_status     VARCHAR(255),
    created_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
    ext_number           VARCHAR(20),
    queue_number         VARCHAR(20),
    callback_time        DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_time_note_code  DATETIME,
    last_note_code       VARCHAR(1024), -- StarRocks requires a length for VARCHAR
    data_auto_processed  JSON,          -- Maps from Postgres jsonb
    next_action          VARCHAR(20) DEFAULT '',
    flag                 INT
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "replicated_storage" = "true" -- Required when using AUTO_INCREMENT
);
Option 2: Partitioned Primary Key Table (Recommended for Incremental Backup) If you want to use the incremental backup strategy (backing up new data each day), you must partition the table by a date column (like 
created_at
). Note: In the Primary Key model, any column used for partitioning must be included in the 
PRIMARY KEY
definition.
Copy code
sql
CREATE TABLE public.crm_lead_datas (
    id                   BIGINT NOT NULL AUTO_INCREMENT,
    created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- ... (other columns same as above) ...
    flag                 INT
) 
PRIMARY KEY (id, created_at) -- created_at must be here to allow partitioning
PARTITION BY RANGE(created_at) (
    START ("2024-01-01") END ("2025-01-01") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "replicated_storage" = "true"
);
Key Changes from Postgres: 1. 
AUTO_INCREMENT
: Replaces 
nextval
. It requires 
BIGINT
and the property 
"replicated_storage" = "true"
. 2. 
DATETIME
: Used instead of 
timestamp
. 3. 
BOOLEAN
: StarRocks supports 
BOOLEAN
(internally 
TINYINT
). 4. 
JSON
: Replaces 
jsonb
. 5. 
VARCHAR
: You must specify a maximum length (e.g., 
VARCHAR(1024)
or 
VARCHAR(65533)
). References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • table_design/table_types/primary_key_table.md References • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md
l
@Rocky SQL Error [1064] [42000]: Getting syntax error at line 5, column 38. Detail message: Unexpected input '0', the most similar input is {'CURRENT_TIMESTAMP', 'NULL', SINGLE_QUOTED_TEXT, DOUBLE_QUOTED_TEXT, '('}. Getting syntax error at line 5, column 38. Detail message: Unexpected input '0', the most similar input is {'CURRENT_TIMESTAMP', 'NULL', SINGLE_QUOTED_TEXT, DOUBLE_QUOTED_TEXT, '('}.
r
The error occurs because you are using PostgreSQL-specific syntax (like 
int4
, 
int8
, 
nextval
, and type casting 
::
) and StarRocks requires numeric default values to be enclosed in quotes (e.g., 
DEFAULT '0'
). Here is the corrected StarRocks DDL for your Primary Key table. Corrected StarRocks DDL (Single Partition) If you want a standard table where 
id
is the primary key:
Copy code
sql
CREATE TABLE crm_lead_datas (
    id                   BIGINT NOT NULL AUTO_INCREMENT,
    campaign_id          VARCHAR(100) NOT NULL DEFAULT '',
    mobiles              VARCHAR(255) NOT NULL DEFAULT '',
    menu_id              INT NOT NULL DEFAULT '0',
    action_id            INT NOT NULL DEFAULT '0',
    lead_id              BIGINT NOT NULL DEFAULT '0',
    product_offer_id     BIGINT NOT NULL DEFAULT '0',
    updated_date         DATETIME DEFAULT CURRENT_TIMESTAMP,
    call_attempts        INT DEFAULT '0',
    active               BOOLEAN DEFAULT '1', -- 1 for true, 0 for false
    customer_id          VARCHAR(255),
    last_call_status     VARCHAR(255),
    created_at           DATETIME DEFAULT CURRENT_TIMESTAMP,
    ext_number           VARCHAR(20),
    queue_number         VARCHAR(20),
    callback_time        DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_time_note_code  DATETIME,
    last_note_code       VARCHAR(1024), 
    data_auto_processed  JSON,           -- Maps from jsonb
    next_action          VARCHAR(20) DEFAULT '',
    flag                 INT
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "replicated_storage" = "true"        -- Required for AUTO_INCREMENT
);
For Incremental Backups (Partitioned) As mentioned before, if you want to perform daily incremental backups, you should partition the table by 
created_at
. In a Primary Key table, the partition column must be part of the Primary Key.
Copy code
sql
CREATE TABLE crm_lead_datas_partitioned (
    id                   BIGINT NOT NULL AUTO_INCREMENT,
    created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- ... other columns same as above ...
    flag                 INT
) 
PRIMARY KEY (id, created_at) -- PK must include the partition column
PARTITION BY RANGE(created_at) (
    START ("2024-01-01") END ("2025-01-01") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "replicated_storage" = "true"
);
Key Changes Applied: 1. Data Types: Changed 
int8
to 
BIGINT
, 
int4
to 
INT
, 
timestamp
to 
DATETIME
, and 
jsonb
to 
JSON
. 2. Default Values: Enclosed numeric defaults in quotes (e.g., 
DEFAULT '0'
). 3. Auto Increment: Replaced 
DEFAULT nextval(...)
with the 
AUTO_INCREMENT
keyword. 4. Varchar Length: In StarRocks, 
VARCHAR
must have a length (e.g., 
VARCHAR(1024)
). 5. Booleans: Used 
BOOLEAN
with 
DEFAULT '1'
(for true). How to Backup Incrementally now? Once you have the partitioned version (Option 2), you can run your daily backup script:
Copy code
sql
BACKUP SNAPSHOT my_db.daily_backup_20231027
TO my_backup_repo
ON (crm_lead_datas_partitioned PARTITION (p20231027));
References • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • sql-reference/data-types/semi_structured/JSON.md • administration/management/Backup_and_restore.md References • sql-reference/sql-statements/keywords.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md
l
@Rocky should i using masterial view
r
Whether you should use a Materialized View (MV) depends on your goal. In StarRocks, an MV is not a replacement for your table; it is a "helper" that lives on top of it. Here is a breakdown of when to use an MV for your 
crm_lead_datas
table: 1. Use an MV if you need Query Acceleration If you find yourself running the same complex queries frequently, an MV can pre-calculate the results. * Example: You often count leads per campaign or calculate daily call success rates. * Benefit: Instead of scanning millions of rows, StarRocks reads the pre-aggregated results from the MV. 2. Do NOT use an MV for "Backups" While an MV stores a physical copy of data, it is not a backup tool. * If you delete data from your base table (
crm_lead_datas
), the data in the Materialized View will also be deleted (or updated) to stay in sync. * For backups, you should stick to the 
BACKUP
and 
RESTORE
commands or the 
EXPORT
method mentioned earlier. 3. Synchronous vs. Asynchronous MVs If you decide to use one, you have two choices for your Primary Key table: Type | Best For | Behavior Synchronous MV | Simple filters/aggregations | Updates instantly when the base table changes. No extra management. Asynchronous MV | Complex joins/multi-table | Updates on a schedule (e.g., every 10 mins). More powerful but has a delay. Recommendation for your 
crm_lead_datas
table: * Keep the table as a Primary Key table (as we defined in the previous step). This is your "Source of Truth" where your CRM data actually lives. * Create an MV only if you have specific dashboards or reports that are running slowly. Example: Creating a Synchronous MV for quick daily reporting If you want to quickly see how many calls are made per day per campaign:
Copy code
sql
CREATE MATERIALIZED VIEW daily_campaign_stats AS
SELECT 
    campaign_id, 
    TO_DATE(created_at) as call_date, 
    COUNT(id) as total_calls,
    SUM(call_attempts) as total_attempts
FROM crm_lead_datas
GROUP BY campaign_id, call_date;
StarRocks will now automatically keep this "summary" updated whenever you insert new leads. Summary: * Table: Stores your raw CRM data. * MV: Only use it as a "performance booster" for heavy queries. * Backup: Stick to the 
BACKUP
command on the base table. References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/Materialized_view.md table. References • best_practices/primarykey_table.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • table_design/table_design.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
l
@Rocky CREATE TABLE public.extension_history_status ( id serial4 NOT NULL, ext varchar(20) NOT NULL, ext_status varchar(50) NOT NULL, queue_logins _text NULL, change_reason varchar(100) NULL, start_time timestamptz DEFAULT now() NULL, end_time timestamptz NULL, CONSTRAINT extension_history_status_pkey PRIMARY KEY (id) ); to starrock duplicate key id table
r
To convert your PostgreSQL table to a Duplicate Key table in StarRocks, use the following DDL. In the Duplicate Key model, StarRocks stores multiple rows with the same key values without enforcing uniqueness, which is ideal for log-like data or change history. StarRocks DDL (Duplicate Key Model)
Copy code
sql
CREATE TABLE public.extension_history_status (
    id             BIGINT NOT NULL AUTO_INCREMENT,
    ext            VARCHAR(60) NOT NULL,        -- Increased length for byte-count (UTF-8)
    ext_status     VARCHAR(150) NOT NULL,
    queue_logins   ARRAY<STRING>,               -- Maps from Postgres _text
    change_reason  VARCHAR(300),
    start_time     DATETIME DEFAULT CURRENT_TIMESTAMP,
    end_time       DATETIME
) 
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "replicated_storage" = "true"               -- Required for AUTO_INCREMENT
);
Key Considerations for the Conversion: 1. 
AUTO_INCREMENT
: * In StarRocks, the column used for 
AUTO_INCREMENT
must be 
BIGINT
. Although your Postgres table uses 
serial4
(4-byte INT), you must use 
BIGINT
here. * The property 
"replicated_storage" = "true"
is mandatory when using 
AUTO_INCREMENT
to ensure ID consistency across replicas. 2. Duplicate Key Model: * The 
DUPLICATE KEY(id)
clause specifies that the table is sorted by 
id
. Unlike the Primary Key model, this allows multiple rows with the same 
id
 if needed. * The columns in the 
DUPLICATE KEY
clause should generally be the first columns defined in the table for optimal performance. 3. Data Types: * 
ARRAY<STRING>
: This is the direct equivalent of the PostgreSQL 
_text
(text array) type. * 
DATETIME
: StarRocks uses 
DATETIME
for timestamps. Note that StarRocks 
DATETIME
does not store timezone offsets like `timestamptz`; it is generally recommended to store everything in UTC. * 
VARCHAR(n)
: In StarRocks, 
n
represents bytes, while in Postgres it represents characters. If your data contains multi-byte characters (like UTF-8), it is safer to triple the length (e.g., 
VARCHAR(20)
becomes 
VARCHAR(60)
). 4. Defaults: * 
now()
is replaced by 
CURRENT_TIMESTAMP
. References • sql-reference/sql-statements/table_bucket_part_index/auto_increment.md • table_design/table_types/duplicate_key_table.md • sql-reference/data-types/semi_structured/Array.md • data_source/External_table.md (PostgreSQL Mapping section)) References • data_source/External_table.md • data_source/catalog/maxcompute_catalog.md • data_source/feature-support-data-lake-analytics.md • knowledge/usage/best-practice.md • data_source/catalog/paimon_catalog.md • faq/Others.md
l
@Rocky to starrocks view CREATE OR REPLACE FORCE EDITIONABLE VIEW "DMAPP"."DTM_LOS_LOG_CBSCORE_STEP" ("SYS_RUN_DATE", "APP_CODE", "CB_SCORE", "CB_RANK", "CREATED_DATE", "SCORE_STAGE", "CURRENT_STAGE", "CBHITTYPE") DEFAULT COLLATION "USING_NLS_COMP" AS SELECT "SYS_RUN_DATE","APP_CODE","CB_SCORE","CB_RANK","CREATED_DATE","SCORE_STAGE","CURRENT_STAGE","CBHITTYPE" FROM dwh_link.`ft_los_log_cbscore_parse_link`;
r
To convert your Oracle-style view to StarRocks, you need to remove the Oracle-specific keywords (
FORCE
, 
EDITIONABLE
, 
COLLATION
) and use standard SQL syntax. Here is the StarRocks DDL:
Copy code
sql
CREATE OR REPLACE VIEW dtm_los_log_cbscore_step AS
SELECT 
    sys_run_date,
    app_code,
    cb_score,
    cb_rank,
    created_date,
    score_stage,
    current_stage,
    cbhittype
FROM dwh_link.ft_los_log_cbscore_parse_link;
Key Adjustments Made: 1. Removed Oracle Keywords: Keywords like 
FORCE
, 
EDITIONABLE
, and 
DEFAULT COLLATION "USING_NLS_COMP"
are not supported and are unnecessary in StarRocks. 2. Simplified Identifiers: Removed double quotes. In StarRocks, identifiers are case-insensitive by default. If you must preserve specific casing or use reserved words, use backticks (`` ` ``) instead of double quotes. 3. Source Reference: The reference 
dwh_link.ft_los_log_cbscore_parse_link
assumes: * 
dwh_link
is a Database name in StarRocks, OR * 
dwh_link
is an External Catalog (e.g., an Oracle Catalog you previously created). If 
dwh_link
is a catalog, you may need to use the three-part name format: 
catalog_name.database_name.table_name
. Important Notes: * Data Types: StarRocks will automatically inherit the data types from the source table (
ft_los_log_cbscore_parse_link
). * Performance: If the source table is an external table (via a Catalog), queries on this view will fetch data from the source system in real-time. If performance is slow, consider using an Asynchronous Materialized View instead of a standard View to cache the data locally in StarRocks. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_VIEW.md • using_starrocks/async_mv/Materialized_view.md • data_source/catalog/oracle_catalog.md
l
@Rocky alter this table CRM_LEAD_DATAS add colume SYS_RUN_DATE with default is current date.
r
To add the 
SYS_RUN_DATE
column with the current date as the default value, use the following 
ALTER TABLE
statement:
Copy code
sql
ALTER TABLE CRM_LEAD_DATAS 
ADD COLUMN SYS_RUN_DATE DATE DEFAULT CURRENT_TIMESTAMP;
Key Details: * Data Type: I used 
DATE
since you requested "current date". In StarRocks, 
DATE
stores only the year, month, and day (
YYYY-MM-DD
). * Default Value: 
CURRENT_TIMESTAMP
is the standard functional default in StarRocks. Even when used with a 
DATE
column, StarRocks will automatically extract the date portion and store it. * Version Note: Functional defaults (like 
CURRENT_TIMESTAMP
) are supported in StarRocks version 2.1 and later. If you are using StarRocks v3.3 or later, you can also use the expression syntax:
Copy code
sql
ALTER TABLE CRM_LEAD_DATAS 
ADD COLUMN SYS_RUN_DATE DATE DEFAULT (curdate());
References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md References • faq/Others.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/data-types/date-types/DATE.md • administration/management/timezone.md
l
@Rocky CREATE MATERIALIZED VIEW mv_group_list_action REFRESH ASYNCHRONOUS START ("2026-01-01 010000") EVERY (INTERVAL 1 DAY) -- Tự động làm mới mỗi ngày AS SELECT APP_CODE, CASE WHEN TASK_CODE IN ('SALE_INIT') THEN 'SALE_INIT' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD_EKYC') THEN 'CUSTOMER_EKYC' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD') THEN 'CUSTOMER_ONBOARD' WHEN TASK_CODE IN ('SALE') THEN 'SALE_PROCESSING' WHEN TASK_CODE IN ('SALE_LEAD') THEN 'SALE_LEAD' WHEN TASK_CODE IN ('DE', 'DE_LEAD') THEN 'DE' WHEN TASK_CODE IN ('DC','DC_AUTO') THEN 'DC' WHEN TASK_CODE IN ('CB','CB_AUTO') THEN 'CB' WHEN TASK_CODE IN ('CA') THEN 'CA' WHEN TASK_CODE IN ('FAB', 'FAH', 'FAO', 'FAS') THEN 'FA' WHEN TASK_CODE IN ('AP', 'AP_ONLINE', 'AP_ONLINE_DONE','AP_ONLINE_START','AP_ONLINE_END', 'AP_AUTO' ) THEN 'AP' WHEN TASK_CODE IN ('AF') THEN 'AF' WHEN TASK_CODE IN ('CUSTOMER') THEN 'CUSTOMER' WHEN TASK_CODE IN ('OP1', 'OP2', 'OPA') THEN 'OP' WHEN TASK_CODE IN ('OFFER_REVIEWER') THEN 'OR' END AS TASK_CODE, CURRENT_DECISION, TASK_STATUS, FINISH_DATE, TASK_NAME, ID, TASK_CODE AS ACTUAL_TASK_CODE, DELEGATE_DATE, DATE_TRUNC('month', DELEGATE_DATE) AS DELEGATE_MONTH, DATE_TRUNC('month', FINISH_DATE) AS FINISH_MONTH, ROW_NUMBER() OVER( PARTITION BY APP_CODE, DATE_TRUNC('month', DELEGATE_DATE), CASE WHEN TASK_CODE IN ('SALE_INIT') THEN 'SALE_INIT' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD_EKYC') THEN 'CUSTOMER_EKYC' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD') THEN 'CUSTOMER_ONBOARD' WHEN TASK_CODE IN ('SALE') THEN 'SALE_PROCESSING' WHEN TASK_CODE IN ('SALE_LEAD') THEN 'SALE_LEAD' WHEN TASK_CODE IN ('DE', 'DE_LEAD') THEN 'DE' WHEN TASK_CODE IN ('DC','DC_AUTO') THEN 'DC' WHEN TASK_CODE IN ('CB','CB_AUTO') THEN 'CB' WHEN TASK_CODE IN ('CA') THEN 'CA' WHEN TASK_CODE IN ('FAB', 'FAH', 'FAO', 'FAS') THEN 'FA' WHEN TASK_CODE IN ('AP', 'AP_ONLINE', 'AP_ONLINE_DONE','AP_ONLINE_START','AP_ONLINE_END', 'AP_AUTO' ) THEN 'AP' WHEN TASK_CODE IN ('AF') THEN 'AF' WHEN TASK_CODE IN ('CUSTOMER') THEN 'CUSTOMER' WHEN TASK_CODE IN ('OP1', 'OP2', 'OPA') THEN 'OP' WHEN TASK_CODE IN ('OFFER_REVIEWER') THEN 'OR' END ORDER BY ID DESC ) AS ORD, ROW_NUMBER() OVER( PARTITION BY APP_CODE, CASE WHEN TASK_CODE IN ('SALE_INIT') THEN 'SALE_INIT' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD_EKYC') THEN 'CUSTOMER_EKYC' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD') THEN 'CUSTOMER_ONBOARD' WHEN TASK_CODE IN ('SALE') THEN 'SALE_PROCESSING' WHEN TASK_CODE IN ('SALE_LEAD') THEN 'SALE_LEAD' WHEN TASK_CODE IN ('DE', 'DE_LEAD') THEN 'DE' END ORDER BY ID DESC ) AS PRE_DC_DESC, ROW_NUMBER() OVER( PARTITION BY APP_CODE, CASE WHEN TASK_CODE IN ('SALE_INIT') THEN 'SALE_INIT' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD_EKYC') THEN 'CUSTOMER_EKYC' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD') THEN 'CUSTOMER_ONBOARD' WHEN TASK_CODE IN ('SALE') THEN 'SALE_PROCESSING' WHEN TASK_CODE IN ('SALE_LEAD') THEN 'SALE_LEAD' WHEN TASK_CODE IN ('DE', 'DE_LEAD') THEN 'DE' END ORDER BY ID ASC ) AS PRE_DC_ASC, ROW_NUMBER() OVER(PARTITION BY APP_CODE ORDER BY ID ASC) AS ORD_STAGE FROM dmapp.DTM_LOS_APP_PROCESSING_HIST; need this view auto update when data table update, not like above
r
To make the Materialized View (MV) update automatically whenever the base table data changes (instead of on a fixed daily schedule), you should use the 
REFRESH ASYNC
 strategy without the 
START
or 
EVERY
clauses. In StarRocks, 
REFRESH ASYNC
(without a schedule) tells the system to monitor the base tables and trigger a refresh task as soon as a new data version is detected. Updated DDL ```sql CREATE MATERIALIZED VIEW mv_group_list_action REFRESH ASYNC -- Automatically triggers when base table data changes DISTRIBUTED BY HASH(ID) -- Required for Async MVs AS SELECT APP_CODE, CASE WHEN TASK_CODE IN ('SALE_INIT') THEN 'SALE_INIT' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD_EKYC') THEN 'CUSTOMER_EKYC' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD') THEN 'CUSTOMER_ONBOARD' WHEN TASK_CODE IN ('SALE') THEN 'SALE_PROCESSING' WHEN TASK_CODE IN ('SALE_LEAD') THEN 'SALE_LEAD' WHEN TASK_CODE IN ('DE', 'DE_LEAD') THEN 'DE' WHEN TASK_CODE IN ('DC','DC_AUTO') THEN 'DC' WHEN TASK_CODE IN ('CB','CB_AUTO') THEN 'CB' WHEN TASK_CODE IN ('CA') THEN 'CA' WHEN TASK_CODE IN ('FAB', 'FAH', 'FAO', 'FAS') THEN 'FA' WHEN TASK_CODE IN ('AP', 'AP_ONLINE', 'AP_ONLINE_DONE','AP_ONLINE_START','AP_ONLINE_END', 'AP_AUTO' ) THEN 'AP' WHEN TASK_CODE IN ('AF') THEN 'AF' WHEN TASK_CODE IN ('CUSTOMER') THEN 'CUSTOMER' WHEN TASK_CODE IN ('OP1', 'OP2', 'OPA') THEN 'OP' WHEN TASK_CODE IN ('OFFER_REVIEWER') THEN 'OR' END AS TASK_CODE, CURRENT_DECISION, TASK_STATUS, FINISH_DATE, TASK_NAME, ID, TASK_CODE AS ACTUAL_TASK_CODE, DELEGATE_DATE, DATE_TRUNC('month', DELEGATE_DATE) AS DELEGATE_MONTH, DATE_TRUNC('month', FINISH_DATE) AS FINISH_MONTH, -- Window functions are supported in Async MVs ROW_NUMBER() OVER( PARTITION BY APP_CODE, DATE_TRUNC('month', DELEGATE_DATE), CASE WHEN TASK_CODE IN ('SALE_INIT') THEN 'SALE_INIT' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD_EKYC') THEN 'CUSTOMER_EKYC' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD') THEN 'CUSTOMER_ONBOARD' WHEN TASK_CODE IN ('SALE') THEN 'SALE_PROCESSING' WHEN TASK_CODE IN ('SALE_LEAD') THEN 'SALE_LEAD' WHEN TASK_CODE IN ('DE', 'DE_LEAD') THEN 'DE' WHEN TASK_CODE IN ('DC','DC_AUTO') THEN 'DC' WHEN TASK_CODE IN ('CB','CB_AUTO') THEN 'CB' WHEN TASK_CODE IN ('CA') THEN 'CA' WHEN TASK_CODE IN ('FAB', 'FAH', 'FAO', 'FAS') THEN 'FA' WHEN TASK_CODE IN ('AP', 'AP_ONLINE', 'AP_ONLINE_DONE','AP_ONLINE_START','AP_ONLINE_END', 'AP_AUTO' ) THEN 'AP' WHEN TASK_CODE IN ('AF') THEN 'AF' WHEN TASK_CODE IN ('CUSTOMER') THEN 'CUSTOMER' WHEN TASK_CODE IN ('OP1', 'OP2', 'OPA') THEN 'OP' WHEN TASK_CODE IN ('OFFER_REVIEWER') THEN 'OR' END ORDER BY ID DESC ) AS ORD, ROW_NUMBER() OVER( PARTITION BY APP_CODE, CASE WHEN TASK_CODE IN ('SALE_INIT') THEN 'SALE_INIT' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD_EKYC') THEN 'CUSTOMER_EKYC' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD') THEN 'CUSTOMER_ONBOARD' WHEN TASK_CODE IN ('SALE') THEN 'SALE_PROCESSING' WHEN TASK_CODE IN ('SALE_LEAD') THEN 'SALE_LEAD' WHEN TASK_CODE IN ('DE', 'DE_LEAD') THEN 'DE' END ORDER BY ID DESC ) AS PRE_DC_DESC, ROW_NUMBER() OVER( PARTITION BY APP_CODE, CASE WHEN TASK_CODE IN ('SALE_INIT') THEN 'SALE_INIT' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD_EKYC') THEN 'CUSTOMER_EKYC' WHEN TASK_CODE IN ('CUSTOMER_ONBOARD') THEN 'CUSTOMER_ONBOARD' WHEN TASK_CODE IN ('SALE') THEN 'SALE_PROCESSING' WHEN TASK_CODE IN ('SALE_LEAD') THEN 'SALE_LEAD' WHEN TASK_CODE IN ('DE', 'DE_LEAD') THEN 'DE' END ORDER BY ID ASC
) AS PRE_DC_ASC, ROW_NUMBER() OVER(PARTITION BY APP_CODE ORDER BY ID ASC) AS ORD_STAGE FROM dmapp.DTM_LOS_APP_PROCESSING_HIST; ``` Important Considerations: 1. Requirement for 
REFRESH ASYNC
: This "trigger on change" behavior only works if 
dmapp.DTM_LOS_APP_PROCESSING_HIST
is an internal StarRocks table (OLAP table). If the base table is an External Table (e.g., from Hive, MySQL, or Postgres), StarRocks cannot automatically detect data changes, and you must use a schedule (like 
EVERY 1 MINUTE
). 2. Full Refresh vs. Partitioned Refresh: * Since your query uses complex Window Functions (
ROW_NUMBER()
), StarRocks will likely perform a Full Refresh of the entire MV every time the base table changes. * If your base table is very large, refreshing the whole view on every single update might be heavy. To optimize this, you can add 
PARTITION BY DATE_TRUNC('month', DELEGATE_DATE)
to the MV definition. This allows StarRocks to refresh only the months where data actually changed. 3. Distribution Key: Asynchronous MVs require a 
DISTRIBUTED BY
clause. I used 
HASH(ID)
, but you should choose the column that best fits your common query patterns. References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.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 CREATE TABLE "PTKD"."CHIKH_APPLICATION_TRACKING_V3" ( "ID" NUMBER(20,0) NOT NULL ENABLE, "APP_UID" VARCHAR2(50) COLLATE "USING_NLS_COMP", "APP_NUMBER" NUMBER(11,0), "SALE_CODE" VARCHAR2(200) COLLATE "USING_NLS_COMP", "SALE_NAME" VARCHAR2(250) COLLATE "USING_NLS_COMP", "APP_CODE" VARCHAR2(20) COLLATE "USING_NLS_COMP", "DC_FIRST_DATE" DATE, "DC_FIRST_MONTH" DATE, "CB_FIRST_DATE" DATE, "CA_FIRST_DATE" DATE, "FA_FIRST_DATE" DATE, "AP_FIRST_DATE" DATE, "AF_FIRST_DATE" DATE, "OP_FIRST_DATE" DATE, "CHANNEL" VARCHAR2(7) COLLATE "USING_NLS_COMP", "BUSINESS_LINE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "BUSINESS_PROCESS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "LOAN_PURPOSE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_CATEGORY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "SCHEME_PRODUCT" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CONTRACT_TYPE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "LOAN_TERM" VARCHAR2(50) COLLATE "USING_NLS_COMP", "YEAR_INTEREST" NUMBER(5,2), "INSURANCE_COMPANY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "INSURANCE_RATE" NUMBER(5,2), "INSURANCE_FEE" NUMBER(20,2), "LOAN_CONSUMER" NUMBER(20,2), "LOAN_AMOUNT" NUMBER(20,2), "APPLICATION_DATE" DATE, "APPROVAL_DATE" DATE, "SIGN_CONTRACT_DATE" DATE, "DISBURSEMENT_DATE" DATE, "DISBURSEMENT_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CREATED_DATE" DATE, "SALE_SUBMIT_DATE" DATE, "CREATED_BY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "LAST_UPDATED_DATE" DATE, "LAST_UPDATED_BY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "STATUS_ID" NUMBER(20,0), "STATUS_CREATED_DATE" DATE, "STATUS_LAST_UPDATED_DATE" DATE, "STATUS_FINISHED_DATE" DATE, "STATUS_CREATED_BY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "STATUS_LAST_UPDATED_BY" VARCHAR2(50) COLLATE "USING_NLS_COMP", "STEP_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CURRENT_STEP" VARCHAR2(200) COLLATE "USING_NLS_COMP", "CURRENT_CALL" VARCHAR2(200) COLLATE "USING_NLS_COMP", "CURRENT_DECISION" VARCHAR2(250) COLLATE "USING_NLS_COMP", "CURRENT_REASON" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CURRENT_REASON_LBL" VARCHAR2(250) COLLATE "USING_NLS_COMP", "CURRENT_REASON_DETAIL" VARCHAR2(250) COLLATE "USING_NLS_COMP", "CURRENT_REASON_DETAIL_LBL" VARCHAR2(250) COLLATE "USING_NLS_COMP", "CURRENT_COMMENT" VARCHAR2(4000) COLLATE "USING_NLS_COMP", "FIRST_SALE_DELEGATE_DATE" DATE, "FIRST_SALE_FINISH_DATE" DATE, "FIRST_SALE_STATUS" VARCHAR2(16) COLLATE "USING_NLS_COMP", "FIRST_SALE_LEAD_DELEGATE_DATE" DATE, "FIRST_SALE_LEAD_FINISH_DATE" DATE, "FIRST_SALE_LEAD_STATUS" VARCHAR2(17) COLLATE "USING_NLS_COMP", "FIRST_DE_DELEGATE_DATE" DATE, "FIRST_DE_FINISH_DATE" DATE, "FIRST_DE_STATUS" VARCHAR2(10) COLLATE "USING_NLS_COMP", "FIRST_MONTH_MAX_DC_DELEGATE_DATE" DATE, "FIRST_MONTH_MAX_DC_FINISH_DATE" DATE, "FIRST_MONTH_MAX_DC_STATUS" VARCHAR2(16) COLLATE "USING_NLS_COMP", "FIRST_MONTH_MAX_CB_DELEGATE_DATE" DATE, "FIRST_MONTH_MAX_CB_FINISH_DATE" DATE, "FIRST_MONTH_MAX_CB_STATUS" VARCHAR2(14) COLLATE "USING_NLS_COMP", "FIRST_MONTH_MAX_CA_DELEGATE_DATE" DATE, "FIRST_MONTH_MAX_CA_FINISH_DATE" DATE, "FIRST_MONTH_MAX_CA_STATUS" VARCHAR2(10) COLLATE "USING_NLS_COMP", "FIRST_MONTH_MAX_FA_DELEGATE_DATE" DATE, "FIRST_MONTH_MAX_FA_FINISH_DATE" DATE, "FIRST_MONTH_MAX_FA_STATUS" VARCHAR2(10) COLLATE "USING_NLS_COMP", "FIRST_MONTH_MAX_AP_DELEGATE_DATE" DATE, "FIRST_MONTH_MAX_AP_FINISH_DATE" DATE, "FIRST_MONTH_MAX_AP_STATUS" VARCHAR2(15) COLLATE "USING_NLS_COMP", "FIRST_MONTH_MAX_AF_DELEGATE_DATE" DATE, "FIRST_MONTH_MAX_AF_FINISH_DATE" DATE, "FIRST_MONTH_MAX_AF_STATUS" VARCHAR2(15) COLLATE "USING_NLS_COMP", "FIRST_MONTH_MAX_OP_DELEGATE_DATE" DATE, "FIRST_MONTH_MAX_OP_FINISH_DATE" DATE, "FIRST_MONTH_MAX_OP_STATUS" VARCHAR2(16) COLLATE "USING_NLS_COMP", "CURRENT_SALE_DELEGATE_DATE" DATE, "CURRENT_SALE_FINISH_DATE" DATE, "CURRENT_SALE_STATUS" VARCHAR2(16) COLLATE "USING_NLS_COMP", "CURRENT_SALE_LEAD_DELEGATE_DATE" DATE, "CURRENT_SALE_LEAD_FINISH_DATE" DATE, "CURRENT_SALE_LEAD_STATUS" VARCHAR2(17) COLLATE "USING_NLS_COMP", "CURRENT_DE_DELEGATE_DATE" DATE, "CURRENT_DE_FINISH_DATE" DATE, "CURRENT_DE_STATUS" VARCHAR2(10) COLLATE "USING_NLS_COMP", "CURRENT_DC_DELEGATE_DATE" DATE, "CURRENT_DC_FINISH_DATE" DATE, "CURRENT_DC_STATUS" VARCHAR2(16) COLLATE "USING_NLS_COMP", "CURRENT_CB_DELEGATE_DATE" DATE, "CURRENT_CB_FINISH_DATE" DATE, "CURRENT_CB_STATUS" VARCHAR2(14) COLLATE "USING_NLS_COMP", "CURRENT_CA_DELEGATE_DATE" DATE, "CURRENT_CA_FINISH_DATE" DATE, "CURRENT_CA_STATUS" VARCHAR2(10) COLLATE "USING_NLS_COMP", "CURRENT_FA_DELEGATE_DATE" DATE, "CURRENT_FA_FINISH_DATE" DATE, "CURRENT_FA_STATUS" VARCHAR2(10) COLLATE "USING_NLS_COMP", "CURRENT_AP_DELEGATE_DATE" DATE, "CURRENT_AP_FINISH_DATE" DATE, "CURRENT_AP_STATUS" VARCHAR2(15) COLLATE "USING_NLS_COMP", "CURRENT_AF_DELEGATE_DATE" DATE, "CURRENT_AF_FINISH_DATE" DATE, "CURRENT_AF_STATUS" VARCHAR2(15) COLLATE "USING_NLS_COMP", "CURRENT_OP_DELEGATE_DATE" DATE, "CURRENT_OP_FINISH_DATE" DATE, "CURRENT_OP_STATUS" VARCHAR2(16) COLLATE "USING_NLS_COMP", "DC_ENTER_COUNT" NUMBER, "LOAN_AMOUNT_REQUESTED" NUMBER(20,2), "TOTAL_AMOUNT_REQUESTED" NUMBER(20,2), "PRODUCT_METHOD" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_GROUP" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_CODE_1" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CUSTOMER_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "LMS_CIF" VARCHAR2(50) COLLATE "USING_NLS_COMP", "DATE_OF_BIRTH" DATE, "WORKING_ADDRESS_PROVINCE_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "TEMPORARY_ADDRESS_PROVINCE_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PERMANENT_ADDRESS_PROVINCE_LBL" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CONTRACT_NUMBER" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PRODUCT_CLASS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CUSTOMER_NAME" VARCHAR2(250) COLLATE "USING_NLS_COMP", "GENDER" VARCHAR2(50) COLLATE "USING_NLS_COMP", "MARITAL_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "EDUCATION" VARCHAR2(50) COLLATE "USING_NLS_COMP", "ID_CARD_TYPE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "ID_CARD_NUMBER" VARCHAR2(50) COLLATE "USING_NLS_COMP", "JOB" VARCHAR2(50) COLLATE "USING_NLS_COMP", "JOB_POSITION" VARCHAR2(250) COLLATE "USING_NLS_COMP", "DTI" NUMBER(20,2), "PTI" NUMBER(20,2), "AVG_INCOME" NUMBER(20,2), "TYPE_STATEMENT" VARCHAR2(250) COLLATE "USING_NLS_COMP", "DISBURSEMENT_TYPE_LBL" VARCHAR2(250) COLLATE "USING_NLS_COMP", "APP_CODE_REFERENCE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CARD_LIMIT" NUMBER(20,2), "CALLCARD_LIMIT" NUMBER(20,2), "LOAN_PURPOSE_CLASSIFICATION_LBL" VARCHAR2(300) COLLATE "USING_NLS_COMP", "PARTNER_ID" VARCHAR2(50) COLLATE "USING_NLS_COMP", "PARTNER_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP", "UW_FIRST_DATE" DATE, "FIRST_SALE_INIT_DELEGATE_DATE" DATE, "FIRST_SALE_INIT_FINISH_DATE" DATE, "FIRST_SALE_INIT_STATUS" VARCHAR2(25) COLLATE "USING_NLS_COMP", "CURRENT_SALE_INIT_DELEGATE_DATE" DATE, "CURRENT_SALE_INIT_FINISH_DATE" DATE, "CURRENT_SALE_INIT_STATUS" VARCHAR2(25) COLLATE "USING_NLS_COMP", "FIRST_CUSTOMER_EKYC_DELEGATE_DATE" DATE, "FIRST_CUSTOMER_EKYC_FINISH_DATE" DATE, "FIRST_CUSTOMER_EKYC_STATUS" VARCHAR2(25) COLLATE "USING_NLS_COMP", "CURRENT_CUSTOMER_EKYC_DELEGATE_DATE" DATE, "CURRENT_CUSTOMER_EKYC_FINISH_DATE" DATE, "CURRENT_CUSTOMER_EKYC_STATUS" VARCHAR2(25) COLLATE "USING_NLS_COMP", "FIRST_PRECHECK_DELEGATE_DATE" DATE, "FIRST_PRECHECK_FINISH_DATE" DATE, "FIRST_PRECHECK_STATUS" VARCHAR2(25) COLLATE "USING_NLS_COMP", "CURRENT_PRECHECK_DELEGATE_DATE" DATE, "CURRENT_PRECHECK_FINISH_DATE" DATE, "CURRENT_PRECHECK_STATUS" VARCHAR2(25) COLLATE "USING_NLS_COMP", "CURRENT_CUSTOMER_DELEGATE_DATE" DATE, "CURRENT_CUSTOMER_FINISH_DATE" DATE, "CURRENT_CUSTOMER_SIGN_STATUS" VARCHAR2(25) COLLATE "USING_NLS_COMP", "COLLATERAL_TYPE" VARCHAR2(100) COLLATE "USING_NLS_COMP", "FIRST_OR_DATE" DATE, "FIRST_MONTH_MAX_OR_DELEGATE_DATE" DATE, "FIRST_MONTH_MAX_OR_FINISH_DATE" DATE, "FIRST_MONTH_MAX_OR_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP", "CURRENT_OR_DELEGATE_DATE" DATE, "CURRENT_OR_FINISH_DATE" DATE, "CURRENT_OR_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP" ) to starrock primary key APP_CODE table
r
To convert your Oracle DDL to a StarRocks Primary Key table, you need to map Oracle's data types to StarRocks equivalents and remove Oracle-specific keywords like 
COLLATE
, 
ENABLE
, and 
USING_NLS_COMP
. Key Conversion Rules: 1. Data Types: _ 
VARCHAR2(n)
→ `VARCHAR(n_3)`: StarRocks measures 
VARCHAR
in bytes, while Oracle often measures in characters. Multiplying by 3 is a safe way to handle UTF-8 multibyte characters (like Vietnamese). * 
DATE
→ `DATETIME`: Oracle's 
DATE
type includes time. In StarRocks, 
DATE
is only 
YYYY-MM-DD
. 
DATETIME
is usually the better choice for "Date" columns in financial or tracking systems. * 
NUMBER(p, s)
→ 
DECIMAL(p, s)
. * 
NUMBER(11,0)
→ 
BIGINT
. 2. Primary Key: The table must use the 
PRIMARY KEY
clause and include a 
DISTRIBUTED BY HASH
clause on the primary key column. 3. Nullability: Primary key columns in StarRocks must be 
NOT NULL
. StarRocks DDL ```sql CREATE TABLE PTKD.CHIKH_APPLICATION_TRACKING_V3 ( APP_CODE VARCHAR(60) NOT NULL COMMENT "Primary Key", ID DECIMAL(20,0) NOT NULL, APP_UID VARCHAR(150), APP_NUMBER BIGINT, SALE_CODE VARCHAR(600), SALE_NAME VARCHAR(750), DC_FIRST_DATE DATETIME, DC_FIRST_MONTH DATETIME, CB_FIRST_DATE DATETIME, CA_FIRST_DATE DATETIME, FA_FIRST_DATE DATETIME, AP_FIRST_DATE DATETIME, AF_FIRST_DATE DATETIME, OP_FIRST_DATE DATETIME, CHANNEL VARCHAR(21), BUSINESS_LINE VARCHAR(150), BUSINESS_PROCESS VARCHAR(150), LOAN_PURPOSE VARCHAR(150), PRODUCT_CATEGORY VARCHAR(150), SCHEME_PRODUCT VARCHAR(150), CONTRACT_TYPE VARCHAR(150), LOAN_TERM VARCHAR(150), YEAR_INTEREST DECIMAL(5,2), INSURANCE_COMPANY VARCHAR(150), INSURANCE_RATE DECIMAL(5,2), INSURANCE_FEE DECIMAL(20,2), LOAN_CONSUMER DECIMAL(20,2), LOAN_AMOUNT DECIMAL(20,2), APPLICATION_DATE DATETIME, APPROVAL_DATE DATETIME, SIGN_CONTRACT_DATE DATETIME, DISBURSEMENT_DATE DATETIME, DISBURSEMENT_STATUS VARCHAR(150), CREATED_DATE DATETIME, SALE_SUBMIT_DATE DATETIME, CREATED_BY VARCHAR(150), LAST_UPDATED_DATE DATETIME, LAST_UPDATED_BY VARCHAR(150), STATUS_ID DECIMAL(20,0), STATUS_CREATED_DATE DATETIME, STATUS_LAST_UPDATED_DATE DATETIME, STATUS_FINISHED_DATE DATETIME, STATUS_CREATED_BY VARCHAR(150), STATUS_LAST_UPDATED_BY VARCHAR(150), STEP_CODE VARCHAR(150), CURRENT_STEP VARCHAR(600), CURRENT_CALL VARCHAR(600), CURRENT_DECISION VARCHAR(750), CURRENT_REASON VARCHAR(150), CURRENT_REASON_LBL VARCHAR(750), CURRENT_REASON_DETAIL VARCHAR(750), CURRENT_REASON_DETAIL_LBL VARCHAR(750), CURRENT_COMMENT VARCHAR(4000), -- Max bytes for VARCHAR is 1,048,576 FIRST_SALE_DELEGATE_DATE DATETIME, FIRST_SALE_FINISH_DATE DATETIME, FIRST_SALE_STATUS VARCHAR(48), FIRST_SALE_LEAD_DELEGATE_DATE DATETIME, FIRST_SALE_LEAD_FINISH_DATE DATETIME, FIRST_SALE_LEAD_STATUS VARCHAR(51), FIRST_DE_DELEGATE_DATE DATETIME, FIRST_DE_FINISH_DATE DATETIME, FIRST_DE_STATUS VARCHAR(30), FIRST_MONTH_MAX_DC_DELEGATE_DATE DATETIME, FIRST_MONTH_MAX_DC_FINISH_DATE DATETIME, FIRST_MONTH_MAX_DC_STATUS VARCHAR(48), FIRST_MONTH_MAX_CB_DELEGATE_DATE DATETIME, FIRST_MONTH_MAX_CB_FINISH_DATE DATETIME, FIRST_MONTH_MAX_CB_STATUS VARCHAR(42), FIRST_MONTH_MAX_CA_DELEGATE_DATE DATETIME, FIRST_MONTH_MAX_CA_FINISH_DATE DATETIME, FIRST_MONTH_MAX_CA_STATUS VARCHAR(30), FIRST_MONTH_MAX_FA_DELEGATE_DATE DATETIME, FIRST_MONTH_MAX_FA_FINISH_DATE DATETIME, FIRST_MONTH_MAX_FA_STATUS VARCHAR(30), FIRST_MONTH_MAX_AP_DELEGATE_DATE DATETIME, FIRST_MONTH_MAX_AP_FINISH_DATE DATETIME, FIRST_MONTH_MAX_AP_STATUS VARCHAR(45), FIRST_MONTH_MAX_AF_DELEGATE_DATE DATETIME, FIRST_MONTH_MAX_AF_FINISH_DATE DATETIME, FIRST_MONTH_MAX_AF_STATUS VARCHAR(45), FIRST_MONTH_MAX_OP_DELEGATE_DATE DATETIME,
FIRST_MONTH_MAX_OP_FINISH_DATE DATETIME, FIRST_MONTH_MAX_OP_STATUS VARCHAR(48), CURRENT_SALE_DELEGATE_DATE DATETIME, CURRENT_SALE_FINISH_DATE DATETIME, CURRENT_SALE_STATUS VARCHAR(48), CURRENT_SALE_LEAD_DELEGATE_DATE DATETIME, CURRENT_SALE_LEAD_FINISH_DATE DATETIME, CURRENT_SALE_LEAD_STATUS VARCHAR(51), CURRENT_DE_DELEGATE_DATE DATETIME, CURRENT_DE_FINISH_DATE DATETIME, CURRENT_DE_STATUS VARCHAR(30), CURRENT_DC_DELEGATE_DATE DATETIME, CURRENT_DC_FINISH_DATE DATETIME, CURRENT_DC_STATUS VARCHAR(48), CURRENT_CB_DELEGATE_DATE DATETIME, CURRENT_CB_FINISH_DATE DATETIME, CURRENT_CB_STATUS VARCHAR(42), CURRENT_CA_DELEGATE_DATE DATETIME, CURRENT_CA_FINISH_DATE DATETIME, CURRENT_CA_STATUS VARCHAR(30), CURRENT_FA_DELEGATE_DATE DATETIME, CURRENT_FA_FINISH_DATE DATETIME, CURRENT_FA_STATUS VARCHAR(30), CURRENT_AP_DELEGATE_DATE DATETIME, CURRENT_AP_FINISH_DATE DATETIME, CURRENT_AP_STATUS VARCHAR(45), CURRENT_AF_DELEGATE_DATE DATETIME, CURRENT_AF_FINISH_DATE DATETIME, CURRENT_AF_STATUS VARCHAR(45), CURRENT_OP_DELEGATE_DATE DATETIME, CURRENT_OP_FINISH_DATE DATETIME, CURRENT_OP_STATUS VARCHAR(48), DC_ENTER_COUNT BIGINT, LOAN_AMOUNT_REQUESTED DECIMAL(20,2), TOTAL_AMOUNT_REQUESTED DECIMAL(20,2), PRODUCT_METHOD VARCHAR(150), PRODUCT_GROUP VARCHAR(150), PRODUCT_CODE_1 VARCHAR(150), CUSTOMER_CODE VARCHAR(150), LMS_CIF VARCHAR(150), DATE_OF_BIRTH DATETIME, WORKING_ADDRESS_PROVINCE_LBL VARCHAR(150), TEMPORARY_ADDRESS_PROVINCE_LBL VARCHAR(150), PERMANENT_ADDRESS_PROVINCE_LBL VARCHAR(150), CONTRACT_NUMBER VARCHAR(150), PRODUCT_CLASS VARCHAR(150), CUSTOMER_NAME VARCHAR(750), GENDER VARCHAR(150), MARITAL_STATUS VARCHAR(150), EDUCATION VARCHAR(150), ID_CARD_TYPE VARCHAR(150), ID_CARD_NUMBER VARCHAR(150), JOB VARCHAR(150), JOB_POSITION VARCHAR(750), DTI DECIMAL(20,2), PTI DECIMAL(20,2), AVG_INCOME DECIMAL(20,2), TYPE_STATEMENT VARCHAR(750), DISBURSEMENT_TYPE_LBL VARCHAR(750), APP_CODE_REFERENCE VARCHAR(150), CARD_LIMIT DECIMAL(20,2), CALLCARD_LIMIT DECIMAL(20,2), LOAN_PURPOSE_CLASSIFICATION_LBL VARCHAR(900), PARTNER_ID VARCHAR(150), PARTNER_CODE VARCHAR(150), UW_FIRST_DATE DATETIME, FIRST_SALE_INIT_DELEGATE_DATE DATETIME, FIRST_SALE_INIT_FINISH_DATE DATETIME, FIRST_SALE_INIT_STATUS VARCHAR(75), CURRENT_SALE_INIT_DELEGATE_DATE DATETIME, CURRENT_SALE_INIT_FINISH_DATE DATETIME, CURRENT_SALE_INIT_STATUS VARCHAR(75), FIRST_CUSTOMER_EKYC_DELEGATE_DATE DATETIME, FIRST_CUSTOMER_EKYC_FINISH_DATE DATETIME, FIRST_CUSTOMER_EKYC_STATUS VARCHAR(75), CURRENT_CUSTOMER_EKYC_DELEGATE_DATE DATETIME, CURRENT_CUSTOMER_EKYC_FINISH_DATE DATETIME, CURRENT_CUSTOMER_EKYC_STATUS VARCHAR(75), FIRST_PRECHECK_DELEGATE_DATE DATETIME, FIRST_PRECHECK_FINISH_DATE DATETIME, FIRST_PRECHECK_STATUS VARCHAR(75), CURRENT_PRECHECK_DELEGATE_DATE DATETIME, CURRENT_PRECHECK_FINISH_DATE DATETIME, CURRENT_PRECHECK_STATUS VARCHAR(75), CURRENT_CUSTOMER_DELEGATE_DATE DATETIME, CURRENT_CUSTOMER_FINISH_DATE DATETIME, CURRENT_CUSTOMER_SIGN_STATUS VARCHAR(75), COLLATERAL_TYPE VARCHAR(300), FIRST_OR_DATE DATETIME, FIRST_MONTH_MAX_OR_DELEGATE_DATE DATETIME, FIRST_MONTH_MAX_OR_FINISH_DATE DATETIME, FIRST_MONTH_MAX_OR_STATUS VARCHAR(150), CURRENT_OR_DELEGATE_DATE DATETIME, CURRENT_OR_FINISH_DATE DATETIME, CURRENT_OR_STATUS VARCHAR(150) ) PRIMARY KEY (APP_CODE) DISTRIBUTED BY HASH(APP_CODE) PROPERTIES ( "replication_num" = "3", -- Change to 1 for single-node development "enable_persistent_index" = "true" ); ``` Notes: * Persistent Index: I enabled 
enable_persistent_index
= 
true
. This is recommended for Primary Key tables to keep the index on disk, saving memory while maintaining high performance.
* Case Sensitivity: Unlike Oracle, StarRocks table names can be case-sensitive depending on your configuration. I removed the double quotes for standard compatibility, which usually treats them as lowercase internally. * Storage: Primary Key tables in StarRocks automatically handle updates and deletions based on the 
APP_CODE
. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md • sql-reference/data-types/numeric-types/DECIMAL.md References • knowledge/usage/best-practice.md • data_source/External_table.md • faq/Others.md • sql-reference/System_limit.md • sql-reference/data-types/string-type/VARCHAR.md • data_source/feature-support-data-lake-analytics.md
2 Views
Open in Slack
PreviousNext
Powered by