http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> SQL Error [5064] [42000]: Failed to...
# questions-and-troubleshooting
l
@Rocky SQL Error [5064] [42000]: Failed to create repository: failed to list remote path: s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info. msg: Fail to get file status: getFileStatus on s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info: software.amazon.awssdk.services.s3.model.S3Exception: Permanent Redirect (Service: S3, Status Code: 308, Request ID: null)null Permanent Redirect (Service: S3, Status Code: 308, Request ID: null) Failed to create repository: failed to list remote path: s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info. msg: Fail to get file status: getFileStatus on s3://uat-backup-starrock/__starrocks_repository_starrocks_minio_repo/__repo_info: software.amazon.awssdk.services.s3.model.S3Exception: Permanent Redirect (Service: S3, Status Code: 308, Request ID: null)null Permanent Redirect (Service: S3, Status Code: 308, Request ID: null)
r
The error SQL Error [5064] ... S3Exception: Permanent Redirect (Status Code: 308) usually indicates a region mismatch between your StarRocks configuration and the actual location of the S3 bucket. In AWS S3, if you attempt to access a bucket using an incorrect regional endpoint, the service returns a 308 redirect to point you to the correct one. To resolve this, please check the following: 1. Correct the Protocol to 
s3a://
 According to the StarRocks documentation, when creating a repository for AWS S3, you must use the 
s3a://
 protocol instead of 
s3://
in the 
ON LOCATION
clause. * Incorrect: 
ON LOCATION "<s3://bucket_name/path>"
* Correct: 
ON LOCATION "<s3a://bucket_name/path>"
2. Explicitly Set the Correct Region Ensure the 
aws.s3.region
property in your 
CREATE REPOSITORY
statement matches the actual AWS region where your bucket resides (e.g., 
us-east-1
, 
eu-west-1
). If this property is missing or incorrect, the underlying AWS SDK may default to 
us-east-1
, leading to a 308 error if the bucket is elsewhere. 3. Example for AWS S3
Copy code
sql
CREATE REPOSITORY my_s3_repo
WITH BROKER
ON LOCATION "<s3a://uat-backup-starrock/backups/>"
PROPERTIES(
    "aws.s3.access_key" = "YOUR_ACCESS_KEY",
    "aws.s3.secret_key" = "YOUR_SECRET_KEY",
    "aws.s3.region" = "us-west-2" -- Must match bucket region
);
4. If Using MinIO (Self-Hosted S3) If you are using MinIO (the error path mentions 
minio_repo
), you must provide the 
aws.s3.endpoint
and ensure the region is set (often 
us-east-1
for MinIO by default, or whatever is configured in your MinIO setup).
Copy code
sql
CREATE REPOSITORY minio_repo
WITH BROKER
ON LOCATION "<s3a://uat-backup-starrock/backups/>"
PROPERTIES(
    "aws.s3.access_key" = "MINIO_USER",
    "aws.s3.secret_key" = "MINIO_PASSWORD",
    "aws.s3.endpoint" = "<http://minio-server:9000>",
    "aws.s3.region" = "us-east-1"
);
Summary Checklist: * Change 
s3://
to 
s3a://
. * Verify the 
aws.s3.region
 matches your bucket's region. * If using a non-AWS S3 provider, ensure 
aws.s3.endpoint
 is specified. References • sql-reference/sql-statements/backup_restore/CREATE_REPOSITORY.md • integrations/streaming/pipe/s3.mdx • faq/operation_maintenance_faq.md • faq/shared_data_faq.md • administration/management/Backup_and_restore.md
l
@Rocky can starrock allow user to grant his created table for other user to select
r
Yes, in StarRocks, a user can grant 
SELECT
privileges on a table they created to another user, but this depends on how their own permissions were assigned. Unlike some traditional databases where the "Owner" automatically has full control over an object, StarRocks (starting from v3.0) uses a strict Role-Based Access Control (RBAC) system where "Ownership" does not implicitly grant the right to share permissions. Requirements to Grant Permissions A user can grant 
SELECT
to others only if one of the following is true: 1. They have the 
user_admin
role: This is the system-wide role required to manage users and grant/revoke privileges. 2. They were granted the privilege `WITH GRANT OPTION`: If an administrator granted the user 
SELECT
(or 
ALL
) on that table/database/catalog with the 
WITH GRANT OPTION
suffix, they are authorized to pass that privilege on to others. Example Scenario If you want User A to be able to grant 
SELECT
on the tables they create in 
db1
to User B: 1. Administrator grants permissions to User A with grant option:
Copy code
sql
-- The administrator must include 'WITH GRANT OPTION'
GRANT SELECT, INSERT, ALTER ON ALL TABLES IN DATABASE db1 TO USER 'user_a'@'%' WITH GRANT OPTION;
2. User A grants SELECT to User B: Now User A can execute the following:
Copy code
sql
GRANT SELECT ON TABLE db1.my_new_table TO USER 'user_b'@'%';
Key Points * No Automatic Grant Option: Simply having the 
CREATE TABLE
privilege on a database does not automatically give the creator the 
GRANT OPTION
for the resulting table. * Manual Assignment: If a user creates a table but was not given permissions 
WITH GRANT OPTION
, an administrator (with the 
user_admin
role) must perform the 
GRANT
statement on their behalf. * Check Permissions: You can check if you have the grant option on a table by running:
Copy code
sql
    SHOW GRANTS;
    -- Or for a specific user (requires user_admin)
    SHOW GRANTS FOR 'user_a'@'%';
Look for 
YES
 in the 
IS_GRANTABLE
column in the 
sys.grants_to_users
view. References • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/privilege_item.md References • administration/user_privs/authorization/privilege_item.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_faq.md
l
@Rocky i already create role like this: GRANT ALL ON DATABASE modelling TO ROLE modelling_admin; how to update this role with grand option
r
To update an existing role with the 
WITH GRANT OPTION
, you simply need to re-run the 
GRANT
statement and append the clause at the end. In StarRocks, granting the same privilege again with the grant option will update the existing permission record. Run this command:
Copy code
sql
GRANT ALL ON DATABASE modelling TO ROLE modelling_admin WITH GRANT OPTION;
What this changes: * Existing Privileges: The 
modelling_admin
role keeps all its existing permissions on the database. * Granting Capability: Any user who is assigned the 
modelling_admin
role can now grant privileges on the 
modelling
database (like 
SELECT
, 
INSERT
, etc.) to other users or roles. How to verify: You can verify that the 
IS_GRANTABLE
status has changed to 
YES
by checking the grants for that role:
Copy code
sql
SHOW GRANTS FOR ROLE modelling_admin;
Look for the row where 
Object_Name
is 
modelling
and check the 
Is_Grantable
column. Important Note on Roles: In the StarRocks RBAC model, when you grant a privilege to a role with the grant option, any user assigned to that role inherits the ability to grant those specific permissions to others. However, the user must have that role active (via 
SET ROLE
) to perform the grant. References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/User_privilege.md • administration/user_privs/authorization/privilege_item.md ) to_use_or_grant_it). References • sql-reference/sql-statements/account-management/GRANT.md
l
@Rocky how to backup starrocks, does starrocks support backup strategy like inscreace or change backup, as having one full cluster backup and then backup change
r
StarRocks supports backup and restore through a snapshot-based mechanism that stores data in remote repositories (AWS S3, HDFS, Google GCS, or MinIO). Does StarRocks support Incremental or "Change" Backups? No, there is no native "incremental" backup mode that automatically detects and backs up only changed rows or modified data within a table. However, you can implement an incremental backup strategy using Partition-Level Backups: * The Strategy: If your tables are partitioned (e.g., by date), you can back up only the new or modified partitions instead of the entire table. * How it works: When you run the 
BACKUP
command, you specify the specific partitions you want to include. * Example:
Copy code
sql
    BACKUP DATABASE my_db SNAPSHOT snapshot_2023_10_27
    TO my_repo
    ON (TABLE my_table PARTITION (p20231027));
How to Perform a Backup 1. Create a Repository First, define where the backup will be stored. For AWS S3, use the 
s3a://
protocol:
Copy code
sql
CREATE REPOSITORY my_s3_repo
WITH BROKER
ON LOCATION "<s3a://my-backup-bucket/starrocks/>"
PROPERTIES(
    "aws.s3.access_key" = "...",
    "aws.s3.secret_key" = "...",
    "aws.s3.region" = "us-east-1"
);
2. Execute the Backup You can back up a whole database, specific tables, or specific partitions.
Copy code
sql
-- Full Database Backup
BACKUP DATABASE my_db SNAPSHOT full_backup_snapshot
TO my_s3_repo;

-- Specific Table Backup
BACKUP DATABASE my_db SNAPSHOT table_snapshot
TO my_s3_repo
ON (TABLE table1, TABLE table2);
3. Monitor the Progress Backup is an asynchronous operation. Check the status with:
Copy code
sql
SHOW BACKUP;
Important Considerations * Shared-data clusters: Native 
BACKUP
and 
RESTORE
commands are not supported for StarRocks shared-data (cloud-native) clusters. * Full vs. Partition: For very large tables, always prefer partition-level backups to reduce resource consumption and retry costs. * Metadata: As of v3.4.0, StarRocks also supports backing up metadata for external catalogs, views, and UDFs. References • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/BACKUP.md • faq/Exporting_faq.md References • administration/management/Backup_and_restore.md • faq/Exporting_faq.md
l
@Rocky how to backup all databases in starrocks cluster,
r
Currently, StarRocks does not support a single command to back up an entire cluster or "all databases" at once. You must initiate a 
BACKUP
job for each database individually. Here is the recommended approach to backing up all databases in a cluster: 1. The Strategy: Individual Database Backups Starting from StarRocks v3.4.0, the 
BACKUP
syntax was simplified to allow backing up all tables within a specific database using a single command. For each database in your cluster, run:
Copy code
sql
BACKUP DATABASE <db_name> SNAPSHOT <snapshot_name>
TO <repository_name>
ON (ALL TABLES);
2. Constraints and Limitations * One Job Per Database: You can only run one 
BACKUP
or 
RESTORE
job per database at a time. However, you can run backup jobs for different databases concurrently (e.g., backing up 
db1
and 
db2
at the same time). * No User/Role Backup: The native 
BACKUP
command does not include user accounts, roles, privileges, or resource groups. You must back these up separately by exporting their creation statements (e.g., using 
SHOW CREATE USER
or scripting). * Shared-Data Clusters: If you are using a StarRocks shared-data (cloud-native) cluster, the 
BACKUP
and 
RESTORE
commands are not supported. 3. How to Automate (Scripting) Since there is no "backup all" command, most administrators use a simple shell script to iterate through the databases. Example Workflow: 1. Get database list: 
SHOW DATABASES;
2. Filter out system databases: Ignore 
information_schema
and 
_statistics
. 3. Run Backup: Execute the 
BACKUP DATABASE...
command for each entry. 4. Monitor: Check progress using 
SHOW BACKUP FROM <db_name>;
. Summary of what is/isn't backed up: Object Type | Supported? Tables (Internal) | Yes (All types: Duplicate, Primary Key, etc.) Partitions | Yes Materialized Views | Yes (Both Sync and Async) Views / UDFs | Yes (v3.4.0+) External Catalog Metadata | Yes (v3.4.0+) Users / Roles / Privileges | No (Must be scripted manually) Resource Groups | No References • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/BACKUP.md • faq/Exporting_faq.md References • administration/management/Backup_and_restore.md • faq/Exporting_faq.md
l
@Rocky after add more RAM to linux VM, should i have to restart starrocks BE
r
Yes, you must restart the StarRocks Backend (BE) for it to recognize and use the additional RAM. Here is why: 1. Fixed Limit at Startup: The BE configuration parameter 
mem_limit
(which defaults to 
90%
of the total system memory) is calculated and set when the BE process starts. It does not dynamically adjust if the underlying OS memory changes. 2. Not Mutable: The 
mem_limit
parameter is categorized as "not mutable" in StarRocks. This means any change to the available system memory (or the configuration file) requires a process restart to take effect. 3. Memory Management: StarRocks manages its own memory pools (for things like the Page Cache, Metadata Cache, and Query Execution). These pools are sized based on the 
mem_limit
calculated at startup. Without a restart, the BE will continue to operate as if it only has the original amount of RAM, potentially leading to unnecessary "Memory Exceed Limit" errors or underutilization of your new hardware. Recommended Steps: 1. Restart the BE: Run the stop and start scripts on the VM:
Copy code
bash
    # From your StarRocks BE bin directory
    ./stop_be.sh
    ./start_be.sh --daemon
2. Verify: After the restart, you can verify the new memory limit by checking the BE's 
<http://be.INFO|be.INFO>
log or by running the following SQL command in your StarRocks client:
Copy code
sql
    SHOW BACKENDS\G
Look at the 
MemUsed
and 
MemLimit
columns to ensure the limit reflects the new VM size. References • administration/management/BE_configuration.md • administration/management/resource_management/Memory_management.md References • administration/management/BE_configuration.md • administration/management/resource_management/Memory_management.md • sql-reference/System_variable.md
l
@Rocky to starrocks table: -- DMAPP.DTM_NOTI_MSG_QUEUE definition -- DDL generated by DBeaver -- WARNING: It may differ from actual native database DDL -- Drop table -- DROP TABLE DMAPP.DTM_NOTI_MSG_QUEUE; CREATE TABLE DMAPP.DTM_NOTI_MSG_QUEUE ( QUEUE_NO NUMBER, JOB_ID VARCHAR2(255), LOADED_FLAG VARCHAR2(255), MESSAGE_CONTENT VARCHAR2(4000), NOTE VARCHAR2(255), PHONE_NO VARCHAR2(255), REQUEST_DATE TIMESTAMP, SEND_DATE TIMESTAMP, SERVICE_CODE VARCHAR2(255), TEMPLATE_CODE VARCHAR2(255), TEMPLATE_DATA CLOB, CONTRACT_NO VARCHAR2(255), "SOURCE" VARCHAR2(255), START_TIME NUMBER, "TYPE" VARCHAR2(255), SERVICE_TYPE VARCHAR2(255), ACTION_CODE VARCHAR2(255), ACTION_CODE_ID VARCHAR2(255), CALLBACK_DATA VARCHAR2(255), COLL_METHOD_CODE VARCHAR2(255), CREATE_USER VARCHAR2(255), CUSTOMER_ID VARCHAR2(255), LMS_CIF_NO VARCHAR2(255), MESSAGE_ID VARCHAR2(255), LOS_CIF_NO VARCHAR2(255), REQUEST_ID VARCHAR2(255), SYS_RUN_DATE DATE, CALL_ID VARCHAR2(255), DESTINATION VARCHAR2(255), DEVICE_TOKEN VARCHAR2(255), DEVICE_USER VARCHAR2(255), EVENT_TYPE VARCHAR2(255), JOIN_URL VARCHAR2(255), OS_TYPE VARCHAR2(255), TOKEN_VOIP VARCHAR2(255), RESPONSE_DATA CLOB ); CREATE INDEX DTM_NOTI_MSG_QUEUE_IDX1 ON DMAPP.DTM_NOTI_MSG_QUEUE (REQUEST_DATE);
r
To convert this Oracle-style DDL to StarRocks, we need to map the data types and define a distributed storage strategy (Data Model, Partitioning, and Bucketing). For a "Message Queue" table, the Duplicate Key model is typically the best fit because it supports high-concurrency ingestion of logs or events. Recommended StarRocks DDL
Copy code
sql
CREATE TABLE DMAPP.DTM_NOTI_MSG_QUEUE (
    QUEUE_NO BIGINT,
    JOB_ID VARCHAR(255),
    LOADED_FLAG VARCHAR(255),
    MESSAGE_CONTENT VARCHAR(4000),
    NOTE VARCHAR(255),
    PHONE_NO VARCHAR(255),
    REQUEST_DATE DATETIME, -- Maps from TIMESTAMP
    SEND_DATE DATETIME,    -- Maps from TIMESTAMP
    SERVICE_CODE VARCHAR(255),
    TEMPLATE_CODE VARCHAR(255),
    TEMPLATE_DATA VARCHAR(65533), -- Maps from CLOB (StarRocks supports up to 1MB)
    CONTRACT_NO VARCHAR(255),
    `SOURCE` VARCHAR(255),        -- Backticks for reserved word
    START_TIME BIGINT,
    `TYPE` VARCHAR(255),          -- Backticks for reserved word
    SERVICE_TYPE VARCHAR(255),
    ACTION_CODE VARCHAR(255),
    ACTION_CODE_ID VARCHAR(255),
    CALLBACK_DATA VARCHAR(255),
    COLL_METHOD_CODE VARCHAR(255),
    CREATE_USER VARCHAR(255),
    CUSTOMER_ID VARCHAR(255),
    LMS_CIF_NO VARCHAR(255),
    MESSAGE_ID VARCHAR(255),
    LOS_CIF_NO VARCHAR(255),
    REQUEST_ID VARCHAR(255),
    SYS_RUN_DATE DATE,
    CALL_ID VARCHAR(255),
    DESTINATION VARCHAR(255),
    DEVICE_TOKEN VARCHAR(255),
    DEVICE_USER VARCHAR(255),
    EVENT_TYPE VARCHAR(255),
    JOIN_URL VARCHAR(255),
    OS_TYPE VARCHAR(255),
    TOKEN_VOIP VARCHAR(255),
    RESPONSE_DATA VARCHAR(65533)  -- Maps from CLOB
) 
DUPLICATE KEY(QUEUE_NO, JOB_ID, REQUEST_DATE) -- Sort keys for fast filtering
PARTITION BY Range(REQUEST_DATE) (
    START ("2024-01-01") END ("2025-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(MESSAGE_ID) BUCKETS 16;

-- Create Bitmap Index for REQUEST_DATE if you query it often but it's not the first sort key
CREATE INDEX idx_request_date ON DMAPP.DTM_NOTI_MSG_QUEUE (REQUEST_DATE) USING BITMAP;
Key Mapping & Design Decisions: 1. Data Types: * `NUMBER`: Mapped to 
BIGINT
(if integers) or 
DECIMAL(38, 9)
if decimal precision is needed. * `VARCHAR2`: Mapped to 
VARCHAR
. StarRocks 
VARCHAR
can handle up to 1,048,576 bytes. * `CLOB`: Mapped to 
VARCHAR(65533)
or higher. If the data is JSON formatted, you could also use the 
JSON
type. * `TIMESTAMP`: Mapped to 
DATETIME
. 2. Duplicate Key Model: This model is used because it preserves all rows (even duplicates) and is optimized for append-only workloads like message queues. 3. Sort Keys: The columns in 
DUPLICATE KEY(...)
act as the primary index (Sort Key). You should put the most frequently filtered columns first. 4. Partitioning: I added 
PARTITION BY RANGE
on 
REQUEST_DATE
. This is critical for performance in "queue" or "log" tables so that StarRocks can drop old data easily and prune partitions during queries. 5. Bucketing: 
DISTRIBUTED BY HASH(MESSAGE_ID)
ensures data is spread evenly across the cluster. If 
MESSAGE_ID
is unique, it prevents data skew. 6. Reserved Words: 
SOURCE
and 
TYPE
are reserved words in StarRocks and must be enclosed in backticks (`` ` ``). How to handle the index? In your original DDL, you had a secondary index on 
REQUEST_DATE
. * If 
REQUEST_DATE
is included in the 
DUPLICATE KEY
(Sort Key), StarRocks creates a "short-key index" for it automatically. * For even faster filtering on non-leading sort keys, I added a 
BITMAP INDEX
statement at the bottom of the script.
l
@Rocky TEMPLATE_DATA , RESPONSE_DATA store json data
r
Since 
TEMPLATE_DATA
and 
RESPONSE_DATA
store JSON data, you should use StarRocks' native JSON data type. Using the 
JSON
type instead of 
VARCHAR
provides better performance because StarRocks stores JSON in an optimized binary format. If you are on StarRocks v4.0+, you can also enable the Flat JSON feature, which automatically extracts common JSON fields into hidden sub-columns to provide query speeds comparable to standard flat columns. Updated DDL for JSON Support
Copy code
sql
CREATE TABLE DMAPP.DTM_NOTI_MSG_QUEUE (
    QUEUE_NO BIGINT,
    JOB_ID VARCHAR(255),
    LOADED_FLAG VARCHAR(255),
    MESSAGE_CONTENT VARCHAR(4000),
    NOTE VARCHAR(255),
    PHONE_NO VARCHAR(255),
    REQUEST_DATE DATETIME,
    SEND_DATE DATETIME,
    SERVICE_CODE VARCHAR(255),
    TEMPLATE_CODE VARCHAR(255),
    TEMPLATE_DATA JSON,      -- Changed to JSON
    CONTRACT_NO VARCHAR(255),
    `SOURCE` VARCHAR(255),
    START_TIME BIGINT,
    `TYPE` VARCHAR(255),
    SERVICE_TYPE VARCHAR(255),
    ACTION_CODE VARCHAR(255),
    ACTION_CODE_ID VARCHAR(255),
    CALLBACK_DATA VARCHAR(255),
    COLL_METHOD_CODE VARCHAR(255),
    CREATE_USER VARCHAR(255),
    CUSTOMER_ID VARCHAR(255),
    LMS_CIF_NO VARCHAR(255),
    MESSAGE_ID VARCHAR(255),
    LOS_CIF_NO VARCHAR(255),
    REQUEST_ID VARCHAR(255),
    SYS_RUN_DATE DATE,
    CALL_ID VARCHAR(255),
    DESTINATION VARCHAR(255),
    DEVICE_TOKEN VARCHAR(255),
    DEVICE_USER VARCHAR(255),
    EVENT_TYPE VARCHAR(255),
    JOIN_URL VARCHAR(255),
    OS_TYPE VARCHAR(255),
    TOKEN_VOIP VARCHAR(255),
    RESPONSE_DATA JSON       -- Changed to JSON
) 
DUPLICATE KEY(QUEUE_NO, JOB_ID, REQUEST_DATE)
PARTITION BY Range(REQUEST_DATE) (
    START ("2024-01-01") END ("2025-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(MESSAGE_ID) BUCKETS 16
PROPERTIES (
    "flat_json.enable" = "true" -- Optional: Enable for v4.0+ for better JSON performance
);
Key Advantages of this setup: 1. Direct Querying: You can query fields inside the JSON using the 
->
operator. * Example: 
SELECT RESPONSE_DATA->'status' FROM DTM_NOTI_MSG_QUEUE;
2. Storage Efficiency: The binary format is more compact than storing raw JSON strings. 3. Automatic Optimization (v4.0+): With 
flat_json.enable
, StarRocks identifies the most frequently queried JSON paths and stores them as separate columns internally, significantly speeding up filtering and aggregation. Important Constraints: * Key Columns: JSON columns cannot be used as Primary/Duplicate keys, Partition keys, or Bucketing keys. * Size Limit: A single JSON value can be up to 16 MB. * Aggregate Tables: JSON columns are not supported in Aggregate Key tables. References • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • using_starrocks/Flat_json.md
l
@Rocky how to query these json colume?
r
In StarRocks, you can query JSON columns using the arrow operator (
->
), path expressions, or specific extraction functions. Here are the most common ways to query your 
TEMPLATE_DATA
and 
RESPONSE_DATA
columns: 1. Extracting a specific field Use the 
->
operator followed by a JSON path (starting with 
$.
).
Copy code
sql
-- Returns the value as a JSON type
SELECT TEMPLATE_DATA->'$.header.title' FROM DTM_NOTI_MSG_QUEUE;

-- Returns the value as a String (VARCHAR)
SELECT get_json_string(RESPONSE_DATA, '$.status.code') FROM DTM_NOTI_MSG_QUEUE;
2. Filtering by JSON values You can use JSON values in your 
WHERE
clause. It is recommended to cast them to the correct SQL type for comparison.
Copy code
sql
-- Filter rows where a specific JSON field matches a value
SELECT * 
FROM DTM_NOTI_MSG_QUEUE 
WHERE CAST(RESPONSE_DATA->'$.success' AS BOOLEAN) = true;

-- Numerical comparison
SELECT * 
FROM DTM_NOTI_MSG_QUEUE 
WHERE CAST(RESPONSE_DATA->'$.retry_count' AS INT) > 3;
3. Handling Arrays inside JSON If your JSON contains an array, you can access elements by index or expand them into rows.
Copy code
sql
-- Get the first item in an array
SELECT TEMPLATE_DATA->'$.items[0].name' FROM DTM_NOTI_MSG_QUEUE;

-- Expand a JSON array into multiple rows (Lateral Join)
SELECT q.QUEUE_NO, item
FROM DTM_NOTI_MSG_QUEUE q, 
     LATERAL json_each(q.TEMPLATE_DATA->'$.items') AS t(item);
4. Summary of Common Functions Function | Usage 
column->'$.path'
 | Standard operator to extract data from a path. 
get_json_string()
 | Extracts a value and returns it as a 
VARCHAR
. 
get_json_int()
 | Extracts a value and returns it as an 
INT
. 
json_exists()
 | Checks if a specific key exists in the JSON. 
json_each()
 | Expands a JSON object or array into multiple rows. Pro-Tip: Generated Columns If you frequently query a specific field inside a large JSON column (e.g., 
RESPONSE_DATA->'$.status'
), you can create a Generated Column. This allows StarRocks to index that specific field, providing much faster query performance than parsing the JSON every time. References • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-functions/json-functions/overview-of-json-functions-and-operators.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_string.md
l
@Rocky select min(REQUEST_DATE) from dmapp.DTM_NOTI_MSG_QUEUE; -- > 2023-01-15 225850.000 REQUEST_DATE from 2023-01-15
r
Since your data starts from January 2023, you should adjust the partition range to ensure all historical data is covered. Here is the updated DDL. I have set the partition start to 
2023-01-01
to capture all January 2023 data and used the 
JSON
type for your specific columns. Updated StarRocks DDL
Copy code
sql
CREATE TABLE DMAPP.DTM_NOTI_MSG_QUEUE (
    QUEUE_NO BIGINT,
    JOB_ID VARCHAR(255),
    LOADED_FLAG VARCHAR(255),
    MESSAGE_CONTENT VARCHAR(4000),
    NOTE VARCHAR(255),
    PHONE_NO VARCHAR(255),
    REQUEST_DATE DATETIME, -- Data starts from 2023-01-15
    SEND_DATE DATETIME,
    SERVICE_CODE VARCHAR(255),
    TEMPLATE_CODE VARCHAR(255),
    TEMPLATE_DATA JSON,      -- Optimized for JSON storage
    CONTRACT_NO VARCHAR(255),
    `SOURCE` VARCHAR(255),
    START_TIME BIGINT,
    `TYPE` VARCHAR(255),
    SERVICE_TYPE VARCHAR(255),
    ACTION_CODE VARCHAR(255),
    ACTION_CODE_ID VARCHAR(255),
    CALLBACK_DATA VARCHAR(255),
    COLL_METHOD_CODE VARCHAR(255),
    CREATE_USER VARCHAR(255),
    CUSTOMER_ID VARCHAR(255),
    LMS_CIF_NO VARCHAR(255),
    MESSAGE_ID VARCHAR(255),
    LOS_CIF_NO VARCHAR(255),
    REQUEST_ID VARCHAR(255),
    SYS_RUN_DATE DATE,
    CALL_ID VARCHAR(255),
    DESTINATION VARCHAR(255),
    DEVICE_TOKEN VARCHAR(255),
    DEVICE_USER VARCHAR(255),
    EVENT_TYPE VARCHAR(255),
    JOIN_URL VARCHAR(255),
    OS_TYPE VARCHAR(255),
    TOKEN_VOIP VARCHAR(255),
    RESPONSE_DATA JSON       -- Optimized for JSON storage
) 
DUPLICATE KEY(QUEUE_NO, JOB_ID, REQUEST_DATE)
PARTITION BY Range(REQUEST_DATE) (
    -- Start from Jan 2023 to accommodate your min(REQUEST_DATE)
    START ("2023-01-01") END ("2025-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(MESSAGE_ID) BUCKETS 16
PROPERTIES (
    "flat_json.enable" = "true" -- Highly recommended for querying JSON fields
);
Why these changes? * Partition Start (
2023-01-01
): Since your earliest record is 
2023-01-15
, starting the partitions at the beginning of that month ensures that the data is correctly distributed into monthly buckets from the very beginning. * Data Retention: If you plan to keep data indefinitely, you can set a later 
END
date. If you want StarRocks to automatically create new partitions as time moves forward, you can later enable Dynamic Partitioning. * Performance: By including 
REQUEST_DATE
in the 
DUPLICATE KEY
, queries filtered by date (like your 
MIN
query or range filters) will be extremely fast because the data is physically sorted by that time. How to add more partitions later? If you reach the 
END
date ("2025-01-01"), you can add new months manually:
Copy code
sql
ALTER TABLE DMAPP.DTM_NOTI_MSG_QUEUE ADD PARTITION p202501 VALUES [('2025-01-01'), ('2025-02-01'));
Or use Dynamic Partitioning to have StarRocks create them for automatically create the next month's partition for you. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/dynamic_partitioning.md
l
@Rocky this table is current size: 54GB, so what BUCKETS will be good
r
For a 54GB table with monthly partitions, your data is distributed as roughly 3GB per partition (assuming ~18 months of data from Jan 2023 to now). In StarRocks, the goal is to keep each tablet (the physical unit of a bucket) between 100MB and 1GB (compressed) or up to 10GB (raw). *Recommended Number of Buckets: 8 to 16* Here is why: 1. Partition Size: With ~3GB per month, setting 
BUCKETS 8
creates tablets of about 375MB each. 
BUCKETS 16
creates tablets of about 190MB. Both are well within the healthy range. 2. Parallelism: You want enough buckets to utilize all your BE (Backend) nodes. A common rule of thumb is $2 \times \text{number of BE nodes}$. * If you have 3 BEs, 8–10 buckets is good. * If you have 5+ BEs, 16 buckets is better. 3. Future Growth: If you expect your monthly data volume to increase (e.g., to 10GB or 20GB per month), 16 buckets gives you more room to grow without the tablets becoming too large. Better Alternative: Automatic Bucketing Since StarRocks v2.5.7, you can let the system decide the bucket count automatically based on your cluster size and data volume. Updated DDL snippet:
Copy code
sql
DISTRIBUTED BY HASH(MESSAGE_ID) -- Remove the "BUCKETS 16" to enable auto-bucketing
*Note: If your version requires a number, 16 is a very safe and standard choice for this data size.* Summary Recommendation * If you have 1–3 BE nodes: Use 8 BUCKETS. * If you have 4+ BE nodes: Use 16 BUCKETS. * Key Choice: 
MESSAGE_ID
is an excellent choice for the distribution key because it has high cardinality, which ensures data is spread evenly across your buckets. Important: Do not set the bucket count too high (e.g., 100+). Having too many small tablets creates unnecessary metadata overhead for the FE (Frontend) and can slow down queries. References • knowledge/usage/best-practice.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md References • knowledge/usage/best-practice.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md • faq/Deploy_faq.md • best_practices/query_tuning/schema_tuning.md • best_practices/partitioning.md
l
@Rocky CREATE TABLE dmapp.dtm_noti_msg_queue ( QUEUE_NO BIGINT, REQUEST_DATE DATETIME, JOB_ID VARCHAR(255), LOADED_FLAG VARCHAR(255), MESSAGE_CONTENT VARCHAR(1024), NOTE VARCHAR(255), PHONE_NO VARCHAR(255), SEND_DATE DATETIME, SERVICE_CODE VARCHAR(255), TEMPLATE_CODE VARCHAR(255), TEMPLATE_DATA JSON, -- Changed to JSON CONTRACT_NO VARCHAR(255), 
SOURCE
VARCHAR(255), START_TIME BIGINT, 
TYPE
VARCHAR(255), SERVICE_TYPE VARCHAR(255), ACTION_CODE VARCHAR(255), ACTION_CODE_ID VARCHAR(255), CALLBACK_DATA VARCHAR(255), COLL_METHOD_CODE VARCHAR(255), CREATE_USER VARCHAR(255), CUSTOMER_ID VARCHAR(255), LMS_CIF_NO VARCHAR(255), MESSAGE_ID VARCHAR(255), LOS_CIF_NO VARCHAR(255), REQUEST_ID VARCHAR(255), SYS_RUN_DATE DATE, CALL_ID VARCHAR(255), DESTINATION VARCHAR(255), DEVICE_TOKEN VARCHAR(255), DEVICE_USER VARCHAR(255), EVENT_TYPE VARCHAR(255), JOIN_URL VARCHAR(255), OS_TYPE VARCHAR(255), TOKEN_VOIP VARCHAR(255), RESPONSE_DATA JSON -- Changed to JSON ) PRIMARY KEY(QUEUE_NO, REQUEST_DATE) PARTITION BY Range(REQUEST_DATE) ( -- Start from Jan 2023 to accommodate your min(REQUEST_DATE) START ("2023-01-01") END ("2025-01-01") EVERY (INTERVAL 1 MONTH) ) DISTRIBUTED BY HASH(queue_no) BUCKETS 8 PROPERTIES ( "replication_num" = "2", "flat_json.enable" = "true" -- Optional: Enable for v4.0+ for better JSON performance ); this table usually query filter by REQUEST_DATE, does this DLL is good
r
This DDL is good, but it can be better depending on whether your data changes after it is written. Here is an evaluation and a refined version based on your query pattern: 1. Primary Key vs. Duplicate Key * Use 
PRIMARY KEY
 (your current choice) only if you frequently UPDATE or DELETE records (e.g., changing a message status from 'PENDING' to 'SENT'). * Use 
DUPLICATE KEY
 if this is a log-style table where you just INSERT new records and never change them. 
DUPLICATE KEY
is more memory-efficient and has faster write performance for high-volume logs. 2. Sorting for 
REQUEST_DATE
 Since you usually filter by 
REQUEST_DATE
, it should be the first column in your key. This creates a "prefix index" that allows StarRocks to skip data very efficiently. 3. Updated Recommendation If this is a typical message queue/log (mostly inserts), use this version:
Copy code
sql
CREATE TABLE dmapp.dtm_noti_msg_queue (
    REQUEST_DATE DATETIME,    -- Moved to first position for better sorting
    QUEUE_NO BIGINT,
    JOB_ID VARCHAR(255),
    LOADED_FLAG VARCHAR(255),
    MESSAGE_CONTENT VARCHAR(4000), -- Increased to 4000 just in case
    NOTE VARCHAR(255),
    PHONE_NO VARCHAR(255),
    SEND_DATE DATETIME,
    SERVICE_CODE VARCHAR(255),
    TEMPLATE_CODE VARCHAR(255),
    TEMPLATE_DATA JSON,
    CONTRACT_NO VARCHAR(255),
    `SOURCE` VARCHAR(255),
    START_TIME BIGINT,
    `TYPE` VARCHAR(255),
    SERVICE_TYPE VARCHAR(255),
    ACTION_CODE VARCHAR(255),
    ACTION_CODE_ID VARCHAR(255),
    CALLBACK_DATA VARCHAR(255),
    COLL_METHOD_CODE VARCHAR(255),
    CREATE_USER VARCHAR(255),
    CUSTOMER_ID VARCHAR(255),
    LMS_CIF_NO VARCHAR(255),
    MESSAGE_ID VARCHAR(255),
    LOS_CIF_NO VARCHAR(255),
    REQUEST_ID VARCHAR(255),
    SYS_RUN_DATE DATE,
    CALL_ID VARCHAR(255),
    DESTINATION VARCHAR(255),
    DEVICE_TOKEN VARCHAR(255),
    DEVICE_USER VARCHAR(255),
    EVENT_TYPE VARCHAR(255),
    JOIN_URL VARCHAR(255),
    OS_TYPE VARCHAR(255),
    TOKEN_VOIP VARCHAR(255),
    RESPONSE_DATA JSON
)
DUPLICATE KEY(REQUEST_DATE, QUEUE_NO) -- Best for log/queue tables & date filtering
PARTITION BY Range(REQUEST_DATE) (
    START ("2023-01-01") END ("2025-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(MESSAGE_ID) BUCKETS 16 -- 16 buckets is safer for 54GB+ growth
PROPERTIES (
    "replication_num" = "3", -- Standard for high availability (use 2 only if you have only 2 nodes)
    "enable_flat_json" = "true" -- Correct property name for v3.3+ JSON optimization
);
Key Improvements Made: 1. Prefix Index: By putting 
REQUEST_DATE
first in 
DUPLICATE KEY
, range queries like 
WHERE REQUEST_DATE >= '2024-01-01'
become significantly faster because StarRocks can use its built-in index to skip entire blocks of data. 2. Bucketing: Changed 
DISTRIBUTED BY
to 
MESSAGE_ID
(if it's a unique ID). This ensures a more even data distribution than 
QUEUE_NO
if 
QUEUE_NO
has many duplicates or follows a strict sequence. 3. JSON Optimization: Changed the property name to 
enable_flat_json
(used in StarRocks 3.3+). This automatically extracts common keys from JSON into a columnar format for much faster querying. 4. Replication: Note that 
replication_num = 2
means if two nodes fail, you lose data. Usually, 
3
is the production standard. Summary * Filtering by Date: Your partitioning strategy is perfect for this. * JSON Columns: Using the 
JSON
type and 
enable_flat_json
is the best way to handle your data. 
TEMPLATE_DATA
and 
RESPONSE_DATA
. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/data_distribution/Data_distribution.md • best_practices/query_tuning/schema_tuning.md
2 Views
Open in Slack
PreviousNext
Powered by