http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> in oracle TIMESTAMP to starrocks us...
# questions-and-troubleshooting
l
@Rocky in oracle TIMESTAMP to starrocks using what data type? for jdbc table
r
When mapping Oracle 
TIMESTAMP
 to StarRocks via a JDBC table (External Table or JDBC Catalog), you should use the 
DATETIME
 data type in StarRocks. Depending on whether you are using the modern JDBC Catalog or the older JDBC External Table, the configuration details differ: 1. JDBC Catalog (Recommended) By default, StarRocks JDBC catalogs may map Oracle 
TIMESTAMP
to 
VARCHAR(64)
to preserve exact precision. To map it to 
DATETIME
automatically, you must set the following property when creating the catalog: * Property: 
oracle.temporal.to-datetime
* Value: 
true
Example:
Copy code
sql
CREATE EXTERNAL CATALOG oracle_catalog
PROPERTIES (
    "type" = "jdbc",
    "user" = "your_user",
    "password" = "your_password",
    "jdbc_url" = "jdbc:oracle:thin:@//localhost:1521/xe",
    "driver_url" = "file:///path/to/ojdbc8.jar",
    "driver_class" = "oracle.jdbc.driver.OracleDriver",
    "oracle.temporal.to-datetime" = "true" -- This enables TIMESTAMP to DATETIME mapping
);
2. JDBC External Table (Deprecated) If you are creating an individual external table, you should explicitly define the column as 
DATETIME
. Example:
Copy code
sql
CREATE EXTERNAL TABLE oracle_timestamp_test (
    id INT,
    event_time DATETIME -- Map Oracle TIMESTAMP here
) ENGINE=jdbc 
PROPERTIES (
    "resource" = "oracle_resource",
    "table" = "your_oracle_table"
);
Important Notes: * Precision: StarRocks 
DATETIME
supports up to 6 decimal places (microseconds). If your Oracle 
TIMESTAMP(9)
has nanosecond precision, the extra digits will be truncated. * Time Zones: For 
TIMESTAMP WITH TIME ZONE
, use the property 
oracle.timestamptz.to-datetime = true
in your JDBC catalog if you want to convert it to a local 
DATETIME
. Otherwise, it may default to 
VARCHAR
. References • data_source/catalog/maxcompute_catalog.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/paimon_catalog.md • data_source/External_table.md • unloading/Spark_connector.md
l
@Rocky GRANT SELECT ON ops.dm_cs_followup IN DATABASE ops TO ROLE cpm_read_only; this one correct?
r
The syntax you provided is incorrect for StarRocks. In StarRocks, you specify the database using a dot prefix (
db.table
) or the 
ON TABLE
keywords. The 
IN DATABASE
clause is only used when granting permissions on all tables at once. Depending on what you want to do, use one of the following: 1. To grant SELECT on a single specific table Use the 
db_name.table_name
format:
Copy code
sql
GRANT SELECT ON TABLE ops.dm_cs_followup TO ROLE cpm_read_only;
_(Note: You can also omit the word 
TABLE
if you prefer: 
GRANT SELECT ON ops.dm_cs_followup TO ROLE cpm_read_only;
)_ 2. To grant SELECT on ALL tables in that database If you meant to give access to everything in the 
ops
database:
Copy code
sql
GRANT SELECT ON ALL TABLES IN DATABASE ops TO ROLE cpm_read_only;
Why your version was incorrect: 1. Placement of 
IN DATABASE
: In StarRocks, 
IN DATABASE
is not a modifier for a single table name; it is used specifically with the 
ALL TABLES
or 
ALL VIEWS
keywords. 2. Table Reference: The most reliable way to reference a table across databases is 
database_name.table_name
. 3. Slack Formatting: It looks like your table name 
ops.dm_cs_followup
was converted into a link by Slack. Ensure you use the plain text name in your SQL client. References • sql-reference/sql-statements/account-management/GRANT.md References • sql-reference/sql-statements/account-management/GRANT.md
l
@Rocky SQL Error [1064] [42000]: Type mismatches on column[SALE2COURIER_DATE], JDBC result type is oracle.sql.TIMESTAMP, please set the type to varchar: BE:3731846 Type mismatches on column[SALE2COURIER_DATE], JDBC result type is oracle.sql.TIMESTAMP, please set the type to varchar: BE:3731846 why TIMESTAMP need to be varchar in starrocks???
r
This error happens because you are likely using the older JDBC External Table (
ENGINE=jdbc
), which has limited support for vendor-specific types like Oracle's 
TIMESTAMP
. Why does it require 
VARCHAR
? In Oracle, the 
TIMESTAMP
type is often returned by the JDBC driver as a custom Java class (
oracle.sql.TIMESTAMP
). The StarRocks External Table scanner is strict; if it doesn't find a direct 1-to-1 mapping to a standard Java 
Timestamp
, it asks you to use 
VARCHAR
to prevent data loss or crashes during the transfer. How to fix it Option 1: Use a JDBC Catalog (Recommended) If you are on StarRocks v3.2.9 or later, you should use a JDBC Catalog instead of an External Table. Catalogs are more modern and include a specific property to handle this mapping automatically. When creating the catalog, add this property:
Copy code
sql
CREATE EXTERNAL CATALOG oracle_catalog
PROPERTIES (
    "type" = "jdbc",
    ...
    "oracle.temporal.to-datetime" = "true" -- Forces Oracle TIMESTAMP to map to DATETIME
);
Option 2: Update the External Table (Quick Fix) If you must keep using the 
ENGINE=jdbc
external table, you have to follow the error's advice and define the column as 
VARCHAR
. You can then convert it back to a date format within your SQL queries. 1. Change the DDL:
Copy code
sql
    CREATE EXTERNAL TABLE your_table (
        SALE2COURIER_DATE VARCHAR(64) -- Change from DATETIME to VARCHAR
    ) ENGINE=jdbc
    PROPERTIES (...);
2. Cast in your query:
Copy code
sql
    SELECT CAST(SALE2COURIER_DATE AS DATETIME) FROM your_table;
Option 3: Check your Driver If you are already using a Catalog and still see this, ensure you are using a standard Oracle JDBC driver (like 
ojdbc8.jar
or 
ojdbc11.jar
) and that the 
oracle.temporal.to-datetime
property is set to 
true
. References • data_source/catalog/maxcompute_catalog.md • data_source/External_table.md • data_source/datalake_faq.md • data_source/catalog/paimon_catalog.md • data_source/feature-support-data-lake-analytics.md • unloading/Spark_connector.md References • data_source/External_table.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/maxcompute_catalog.md • unloading/Spark_connector.md • data_source/file_external_table.md
l
@Rocky with table about 4GB, 8 milion rows, how insert overwrite make starrocks insert faster
r
For a table of 4GB and 8 million rows, using 
INSERT OVERWRITE
is significantly faster and more efficient than a standard 
DELETE
+ 
INSERT
or 
UPDATE
for two main reasons: Metadata Swapping and Reduced Compaction. Here is how 
INSERT OVERWRITE
makes the process faster and how you can optimize it: 1. How it works (The "Secret" to its speed) Instead of deleting rows one by one (which creates "tombstones" and triggers heavy background compaction), 
INSERT OVERWRITE
follows this internal workflow: 1. Creates Temporary Partitions: StarRocks creates hidden temporary partitions behind the scenes. 2. Loads Data: It writes the new 4GB of data into these fresh temporary partitions. 3. Atomic Swap: Once the load is successful, StarRocks performs a metadata swap. It swaps the old partitions for the new ones. 4. Instant Cleanup: The old data is discarded as a whole unit, which is much faster than row-level garbage collection. 2. Best Practices for Faster Execution A. Use Partition-Level Overwrite If your 4GB of data only affects a portion of the table (e.g., just the last 7 days), do not overwrite the whole table. Specify the partitions:
Copy code
sql
INSERT OVERWRITE my_table PARTITION (p202310, p202311)
SELECT * FROM source_table WHERE ...;
This limits the data movement and metadata work to only those specific segments. B. Enable Dynamic Overwrite (v3.4.0+) If you aren't sure exactly which partitions will be updated by the source data, enable Dynamic Overwrite. StarRocks will automatically detect which partitions are present in your 
SELECT
result and only overwrite those, leaving other partitions untouched.
Copy code
sql
-- Enable it for the session
SET dynamic_overwrite = true;

-- Or use a hint
INSERT /*+set_var(dynamic_overwrite = true)*/ OVERWRITE my_table
SELECT * FROM source_table;
C. Increase Parallelism For 4GB of data, the bottleneck is often the 
SELECT
part of the query. You can increase the degree of parallelism (DOP) to use more CPU cores:
Copy code
sql
SET pipeline_dop = 16; -- Adjust based on your BE core count
INSERT OVERWRITE target_table SELECT * FROM source_table;
D. Adjust Timeouts For a 4GB load, if the network or source is slow, the default timeout might be hit. Ensure your session has enough time:
Copy code
sql
SET insert_timeout = 28800; -- Increase to 8 hours if needed (default is 14400)
3. When to use it vs. other methods * Use 
INSERT OVERWRITE
 when you are doing ETL/Batch processing where you replace large chunks of data (Daily/Hourly refreshes). * Avoid 
INSERT INTO ... VALUES
 for this volume. 8 million rows should always be loaded via 
INSERT INTO ... SELECT
(from another table/catalog) or via Stream Load / Broker Load from files for maximum speed. Summary: 
INSERT OVERWRITE
is faster because it turns a "data deletion" problem into a "metadata swap" problem, bypassing the most expensive parts of the storage engine's storage engine. References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md • faq/loading/Insert_into_faq.md • knowledge/usage/best-practice.md References • faq/Others.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md • best_practices/partitioning.md • knowledge/usage/best-practice.md
l
@Rocky rewrite to starrocks sql select a.*, UPPER( REGEXP_REPLACE( TRANSLATE( UPPER(CUSTOMER_NAME_REMOVE_COMBINING), 'ÀÁẠẢÃÂẦẤẬẨẪĂẰẮẶẲẴĀĄÃÆÄÅ' || -- AAAAAAAAAAAAAAAAAAAAAAA 'ÈÉẸẺẼÊỀẾỆỂỄĔĖĘĚ' || -- EEEEEEEEEEEEEEE 'ÌÍỊỈĨĬĮİ' || -- IIIIIIII 'ÒÓỌỎÕÔỒỐỘỔỖƠỜỚỢỞỠŎŐØ' || -- OOOOOOOOOOOOOOOOOOOO 'ÙÚỤỦŨƯỪỨỰỬỮŬŮŰŲǓ' || -- UUUUUUUUUUUUUUUU 'ỲÝỴỶỸ' || 'ĐĎ' || 'ĆČÇ' || 'ÑŃŇŅŊ' || 'Ř' || 'ŚŠŞ' || 'ŹŽŻ', 'AAAAAAAAAAAAAAAAAAAAAAA' || 'EEEEEEEEEEEEEEE' || 'IIIIIIII' || 'OOOOOOOOOOOOOOOOOOOO' || 'UUUUUUUUUUUUUUUU' || 'YYYYY' || 'DD' || 'CCC' || 'NNNNN' || 'R' || 'SSS' || 'ZZZ' ), '[[space]]+', ' ' ) ) AS CUSTOMER_NAME_NO_ACCENT from convert_customer_name_STEP_2 a
r
To rewrite this for StarRocks, the logic remains almost identical because StarRocks supports 
UPPER
, 
TRANSLATE
(since v3.2), and 
REGEXP_REPLACE
. Here is the StarRocks-compatible version:
Copy code
sql
SELECT 
    a.*,
    UPPER(
        REGEXP_REPLACE(
            TRANSLATE(
                UPPER(CUSTOMER_NAME_REMOVE_COMBINING),
                'ÀÁẠẢÃÂẦẤẬẨẪĂẰẮẶẲẴĀĄÃÆÄÅÈÉẸẺẼÊỀẾỆỂỄĔĖĘĚÌÍỊỈĨĬĮİÒÓỌỎÕÔỒỐỘỔỖƠỜỚỢỞỠŎŐØÙÚỤỦŨƯỪỨỰỬỮŬŮŰŲǓỲÝỴỶỸĐĎĆČÇÑŃŇŅŊŘŚŠŞŹŽŻ',
                'AAAAAAAAAAAAAAAAAAAAAAAEEEEEEEEEEEEEEEIIIIIIIIOOOOOOOOOOOOOOOOOOOOUUUUUUUUUUUUUUUUYYYYYDDCCCNNNNNRSSSZZZ'
            ),
            '\\s+', -- Replaces one or more whitespace characters
            ' '
        )
    ) AS CUSTOMER_NAME_NO_ACCENT
FROM convert_customer_name_STEP_2 a;
Key Adjustments & Notes: 1. 
TRANSLATE
Function: This function was introduced in StarRocks v3.2. If you are on an older version, you would have to use a series of nested 
REPLACE
or 
REGEXP_REPLACE
calls, which is much less efficient. 2. 
REGEXP_REPLACE
: I changed 
[[:space:]]+
to 
\\s+
. While StarRocks uses the RE2 engine (which supports POSIX classes), 
\\s+
is more common in StarRocks SQL and handles tabs, newlines, and spaces effectively. Note the double backslash 
\\
which is often required in SQL clients to escape the character. 3. Redundancy: The outermost 
UPPER()
is technically redundant if the 
TRANSLATE
replacement string and the initial 
UPPER()
already ensure everything is capitalized, but it doesn't hurt performance significantly. 4. UTF-8 Support: StarRocks 
TRANSLATE
handles multi-byte UTF-8 characters (like 
À
or 
Đ
) as single characters, so your mapping will work correctly. References • sql-reference/sql-functions/string-functions/translate.md • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md References • sql-reference/sql-functions/string-functions/translate.md • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • sql-reference/sql-functions/string-functions/replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_count.md • faq/Sql_faq.md • sql-reference/sql-functions/string-functions/url_decode.md • sql-reference/sql-functions/string-functions/url_encode.md • sql-reference/sql-functions/binary-functions/to_binary.md • sql-reference/sql-statements/TRANSLATE_TRINO.md • sql-reference/sql-functions/cast.md • loading/SQL_transaction.md • sql-reference/sql-functions/like-predicate-functions/regexp_extract.md References • sql-reference/sql-functions/string-functions/replace.md
l
@Rocky to starrocks sql select a.*, REGEXP_REPLACE( CUSTOMER_NAME_TRIM, '[' || UNISTR('\0300') || '-' || UNISTR('\036F') || ']', '' ) AS CUSTOMER_NAME_REMOVE_COMBINING from convert_customer_name_STEP_1 a
r
To rewrite this for StarRocks, you need to account for two things: StarRocks does not have a 
UNISTR
function, and its regular expression engine (RE2) uses a different syntax for Unicode ranges. The range 
U+0300
to 
U+036F
corresponds to the Combining Diacritical Marks block. You can achieve this in StarRocks using the 
\x{hex}
syntax within the regular expression. StarRocks SQL Rewrite
Copy code
sql
SELECT 
    a.*,
    REGEXP_REPLACE(
        CUSTOMER_NAME_TRIM,
        '[\\x{0300}-\\x{036f}]',
        ''
    ) AS CUSTOMER_NAME_REMOVE_COMBINING
FROM convert_customer_name_STEP_1 a;
Key Differences: 1. Unicode Range: Instead of using 
UNISTR
to concatenate characters, StarRocks (using the RE2 engine) supports Unicode hex code points directly inside the pattern using the 
\\x{HHHH}
format. 2. Double Backslash: In StarRocks SQL, you must use a double backslash (
\\
) to ensure the escape character is passed correctly to the regex engine. 3. Alternative (Cleaner): Since that specific range represents "Mark" characters (diacritics), you can often use the shorthand Unicode property for "Marks" if you want to be more comprehensive:
Copy code
sql
    REGEXP_REPLACE(CUSTOMER_NAME_TRIM, '\\p{M}', '')
Note: 
\p{M}
matches any character in the Unicode "Mark" category (including combining marks). Version Note: This syntax is supported in all recent versions of StarRocks (v2.x and v3.x). References • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_count.md • sql-reference/sql-functions/string-functions/replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_extract.md • sql-reference/sql-functions/string-functions/translate.md References • sql-reference/sql-functions/string-functions/replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_count.md • sql-reference/sql-functions/like-predicate-functions/regexp_extract.md • sql-reference/sql-functions/string-functions/translate.md
l
@Rocky rewrite to starrocks: SELECT a.*, REGEXP_REPLACE( CUSTOMER_NAME_TRIM, '[̴̵̶̷̸̡̢̧̨̛̖̗̘̙̜̝̞̟̠̣̤̥̦̩̪̫̬̭̮̯̰̱̲̳̹̺̻̼͇͈͉͍͎̀́̂̃̄̅̆̇̈̉̊̋̌̍̎̏̐̑̒̓̔̽̾̿͆͊͋͌̕̚͏͓͔͕͖͙͚͐͑͒͗͛ͣͤͥͦͧͨͩͪͫͬͭͮͯ͘͜͟͢͝͞͠͡]', '' ) AS CUSTOMER_NAME_REMOVE_COMBINING FROM convert_customer_name_STEP_1 a
r
To rewrite this for StarRocks, you have two options. The best practice is to use the Unicode property for "Marks" (
\p{M}
), which automatically covers all the combining characters in your list (and others). Option 1: Best Practice (Cleaner & Faster) StarRocks uses the RE2 regular expression engine, which supports Unicode categories. Instead of manually listing 100+ characters, you can use 
\\p{M}
to match any Unicode "Mark" (diacritic).
Copy code
sql
SELECT
    a.*,
    REGEXP_REPLACE(
        CUSTOMER_NAME_TRIM,
        '\\p{M}',
        ''
    ) AS CUSTOMER_NAME_REMOVE_COMBINING
FROM convert_customer_name_STEP_1 a;
Option 2: Literal Rewrite If you want to stick strictly to that specific list of characters, StarRocks handles UTF-8 literals directly in strings. You can copy your list exactly as it is:
Copy code
sql
SELECT
    a.*,
    REGEXP_REPLACE(
        CUSTOMER_NAME_TRIM,
        '[̴̵̶̷̸̡̢̧̨̛̖̗̘̙̜̝̞̟̠̣̤̥̦̩̪̫̬̭̮̯̰̱̲̳̹̺̻̼͇͈͉͍͎̀́̂̃̄̅̆̇̈̉̊̋̌̍̎̏̐̑̒̓̔̽̾̿͆͊͋͌̕̚͏͓͔͕͖͙͚͐͑͒͗͛ͣͤͥͦͧͨͩͪͫͬͭͮͯ͘͜͟͢͝͞͠͡]',
        ''
    ) AS CUSTOMER_NAME_REMOVE_COMBINING
FROM convert_customer_name_STEP_1 a;
Important Notes: 1. Engine: StarRocks' 
REGEXP_REPLACE
is case-sensitive and handles multi-byte UTF-8 characters natively. 2. Escaping: If you use any backslash-based sequences (like 
\d
, 
\s
, or 
\p
), you must use a double backslash (
\\
) in StarRocks SQL to ensure the escape is passed to the regex engine correctly. 3. Performance: For a table with 8 million rows (as mentioned in your earlier message), using 
\\p{M}
(Option 1) is generally more efficient for the CPU than evaluating a very large character class 
[...]
. References • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • sql-reference/sql-functions/string-functions/replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_count.md • sql-reference/sql-functions/like-predicate-functions/regexp_extract.md References • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • sql-reference/sql-functions/string-functions/replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_count.md • sql-reference/sql-functions/like-predicate-functions/regexp_extract.md • sql-reference/sql-functions/string-functions/translate.md References • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • sql-reference/sql-functions/string-functions/replace.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/sql-functions/string-functions/translate.md • sql-reference/sql-functions/string-functions/regexp_split.md
l
@Rocky to starrocks sql: select a.*,REGEXP_REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(TRIM(CUSTOMER_NAME), CHR(9), ' '), CHR(10), ' '), CHR(13), ' '), UNISTR('\00A0'), ' '), '[[space]]+', ' ' ) AS CUSTOMER_NAME_TRIM from data_LOS_CIF_NO_v2 a
r
To rewrite this for StarRocks, you can significantly simplify the logic. StarRocks uses the RE2 regular expression engine, which allows you to handle whitespace and Unicode characters (like the non-breaking space) more efficiently than nested 
REPLACE
calls. Recommended StarRocks SQL The most efficient way to achieve the same result in StarRocks is to use a single 
REGEXP_REPLACE
that identifies all types of whitespace (tabs, newlines, CR, and NBSP) and collapses them into a single space:
Copy code
sql
SELECT 
    a.*,
    REGEXP_REPLACE(
        TRIM(CUSTOMER_NAME),
        '[\\s\\x{00A0}]+', -- Matches one or more: any whitespace (\s) or non-breaking space (\x{00A0})
        ' '
    ) AS CUSTOMER_NAME_TRIM
FROM data_LOS_CIF_NO_v2 a;
Key Changes & Compatibility: 1. 
UNISTR('\00A0')
: StarRocks does not have a 
UNISTR
function. However, in its regular expression engine, you can represent the Non-Breaking Space (NBSP) using the Unicode hex syntax: 
\\x{00A0}
. 2. 
CHR
vs 
CHAR
: StarRocks uses 
char(n)
instead of 
CHR(n)
. However, you don't need these if you use the regex shorthand 
\\s
, which already covers: * 
char(9)
(Tab) * 
char(10)
(Line Feed) * 
char(13)
(Carriage Return) * Standard Space 3. Collapsing Spaces: The 
+
quantifier in the regex 
[\\s\\x{00A0}]+
automatically handles the collapsing of multiple consecutive whitespace characters into one, replacing the need for the second 
REGEXP_REPLACE
call. 4. Escaping: Remember that in StarRocks SQL, backslashes in regex patterns must be doubled (e.g., 
\\s
instead of 
\s
). Literal Translation (If you prefer nested functions) If you want to keep the exact structure of your original query for consistency:
Copy code
sql
SELECT 
    a.*,
    REGEXP_REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(TRIM(CUSTOMER_NAME), CHAR(9), ' '),
                CHAR(10), ' '),
            CHAR(13), ' '),
        BINARY x'C2A0', ' '), -- Non-breaking space in UTF-8 binary
        '[[:space:]]+',
        ' '
    ) AS CUSTOMER_NAME_TRIM
FROM data_LOS_CIF_NO_v2 a;
References • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • sql-reference/sql-functions/string-functions/char.md • sql-reference/sql-functions/string-functions/replace.md References • sql-reference/sql-functions/string-functions/replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md • sql-reference/sql-functions/like-predicate-functions/regexp_extract.md • sql-reference/sql-functions/like-predicate-functions/regexp_count.md • sql-reference/sql-functions/string-functions/translate.md
l
@Rocky this sql correct? alter table mobilecus.app_tracking_event order by created_date, app_code
r
The SQL you provided is almost correct, but in StarRocks, you typically need to wrap the columns in parentheses when using the 
ORDER BY
clause in an 
ALTER TABLE
statement. Corrected Syntax
Copy code
sql
ALTER TABLE mobilecus.app_tracking_event ORDER BY (created_date, app_code);
Important Considerations: 1. Version Requirements: * Primary Key Tables: Supported since v3.0. * Duplicate, Aggregate, and Unique Key Tables: Supported since v3.3. * If you are on an older version, you cannot change the sort order of an existing table directly; you would need to recreate the table. 2. Model Restrictions: * Duplicate and Primary Key Tables: You can choose any combination of columns for the sort key. * Aggregate and Unique Key Tables: The sort key must include all key columns, though you can change their relative order. 3. Operation Type: This is an asynchronous schema change. After running the command, you can check the progress of the task using:
Copy code
sql
    SHOW ALTER TABLE COLUMN;
4. Performance: Changing the sort key triggers a data reorganization. For large tables, this may take some time and consume IO/CPU resources. 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-statements/table_bucket_part_index/SHOW_ALTER.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md
l
@Rocky current table 'application' is ORDER BY(
app_number
, 
app_code
) now i want to change to --> order by created_date, app_code
r
To change the sort order of your 
application
table from 
(app_number, app_code)
to 
(created_date, app_code)
, use the following SQL:
Copy code
sql
ALTER TABLE application ORDER BY (created_date, app_code);
Important Requirements: 1. Parentheses: You must wrap the column names in parentheses. 2. StarRocks Version: * If this is a Primary Key table: Requires v3.0 or higher. * If this is a Duplicate, Aggregate, or Unique Key table: Requires v3.3 or higher. 3. Table Model Restrictions: * Duplicate / Primary Key Tables: You can use any combination of columns for the new sort key. * Aggregate / Unique Key Tables: Your new 
ORDER BY
list must still include all of the table's key columns. You can change their relative order (e.g., putting 
created_date
first), but you cannot remove a key column from the sort list. 4. Asynchronous Operation: This is a background task. You can monitor the progress with:
Copy code
sql
    SHOW ALTER TABLE COLUMN;
Note: Since StarRocks builds its "Prefix Index" based on the sort key, changing it to 
created_date, app_code
will significantly speed up queries that filter by 
created_date
, but queries filtering only by 
app_number
may become slower. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/indexes/Prefix_index_sort_key.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/indexes/Prefix_index_sort_key.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_ALTER.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md _TABLE) References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/table_types/primary_key_table.md • table_design/indexes/Prefix_index_sort_key.md
l
@Rocky CREATE TABLE 
dtm_pcb_data_result_extract
( 
id
bigint(20) NOT NULL COMMENT "ID định danh", 
app_code
varchar(50) NOT NULL COMMENT "Mã hồ sơ", 
request_date
datetime NULL COMMENT "Ngày yêu cầu", 
response_date
datetime NULL COMMENT "Ngày phản hồi", 
js_path
varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON", 
js_value
varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON" ) ENGINE=OLAP DUPLICATE KEY(
id
) PARTITION BY RANGE(
request_date
) DISTRIBUTED BY HASH(
id
) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "dynamic_partition.buckets" = "8", "dynamic_partition.enable" = "true", "dynamic_partition.end" = "2", "dynamic_partition.history_partition_num" = "0", "dynamic_partition.prefix" = "p", "dynamic_partition.start" = "-36", "dynamic_partition.start_day_of_month" = "1", "dynamic_partition.time_unit" = "MONTH", "dynamic_partition.time_zone" = "Asia/Ho_Chi_Minh", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "1" ); this table about 50GB, does this table design ok?
r
The design is generally solid for a 50GB table, especially with the monthly partitioning strategy. However, there are a few optimizations and risks you should consider based on how you plan to query the data. 1. Sort Key (DUPLICATE KEY) In your current design, the table is sorted by 
id
. * The Issue: StarRocks uses the 
DUPLICATE KEY
to build its Prefix Index. If you mostly query this table by 
request_date
or 
app_code
, sorting by 
id
won't help performance. * Recommendation: Change the order to include columns used in 
WHERE
clauses. For an extraction table, this is usually:
Copy code
sql
    DUPLICATE KEY(`request_date`, `app_code`, `id`)
This will significantly speed up queries filtering by time or application code. 2. Tablet Size & Bucketing * Current State: 50GB total / ~38 partitions (3 years) = ~1.3GB per partition. With 8 buckets, each "tablet" is about 160MB. * Assessment: This is a bit small but acceptable. StarRocks generally performs best with tablets between 1GB and 10GB. * Recommendation: If you expect this table to grow to 200GB+, keep 
8
. If it stays at 50GB, you could even reduce buckets to 
4
or 
5
to reduce metadata overhead, though 
8
is perfectly fine for a cluster with 3-5 nodes. 3. Replication Number * The Issue: You have 
"replication_num" = "1"
. * Warning: This means no high availability. If one Backend (BE) node goes down or a disk fails, you will lose data and the table will become unavailable. * Recommendation: For production environments, always use 
"replication_num" = "3"
. 4. Varchar Lengths * Observation: You have 
js_path
and 
js_value
as 
varchar(4000)
. * Note: If these fields contain actual JSON objects, consider using the 
JSON
 data type. It allows for faster partial updates and optimized querying of sub-fields using 
->
syntax. 5. Fast Schema Evolution * Good Choice: Enabling 
"fast_schema_evolution" = "true"
is excellent here, as extraction tables often need new columns added over time. Suggested Optimized DDL
Copy code
sql
CREATE TABLE `dtm_pcb_data_result_extract` (
  `id` bigint(20) NOT NULL COMMENT "ID định danh",
  `app_code` varchar(50) NOT NULL COMMENT "Mã hồ sơ",
  `request_date` datetime NULL COMMENT "Ngày yêu cầu",
  `response_date` datetime NULL COMMENT "Ngày phản hồi",
  `js_path` varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON",
  `js_value` varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON"
) ENGINE=OLAP
DUPLICATE KEY(`request_date`, `app_code`) -- Optimized for prefix index
PARTITION BY RANGE(`request_date`)
DISTRIBUTED BY HASH(`id`) BUCKETS 8
PROPERTIES (
"compression" = "LZ4",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.start" = "-36",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.time_zone" = "Asia/Ho_Chi_Minh",
"replication_num" = "3", -- Changed to 3 for production safety
"fast_schema_evolution" = "true"
);
Summary * Size (50GB): Well within StarRocks' sweet spot. * Partitioning: Monthly is perfect for 3 years of data. * Main Change needed: Review your 
DUPLICATE KEY
based on your most common 
WHERE
filters and increase 
replication_num
if this is production data.
l
@Rocky but in real data app_code have null value, does DUPLICATE KEY(
request_date
, 
app_code
) this ok when store null app_code rows
r
Yes, using 
DUPLICATE KEY(request_date, app_code)
is perfectly fine even if 
app_code
contains 
NULL
values. In StarRocks, 
NULL
values are fully supported in sort keys and prefix indexes. Here is how it works: 1. How NULLs are Sorted StarRocks treats 
NULL
values as the smallest possible values. This means: * All rows where 
request_date
is the same and 
app_code
is 
NULL
will be stored together at the beginning of that specific date's data block. * The Prefix Index will still be generated normally. If a data block starts with a 
NULL
value, that 
NULL
is simply what gets stored in the index entry for that block. 2. Query Performance with NULLs * Filtering for NULLs: Queries like 
WHERE app_code IS NULL
will actually be very fast because StarRocks can use the sort order to jump directly to the section of the data where the `NULL`s are stored. * Filtering for Values: If you query 
WHERE app_code = 'some_code'
, StarRocks will still efficiently skip over the blocks containing `NULL`s because it knows they are sorted "before" your target value. 3. Key Design Tip: Column Order Since you have 
request_date
followed by `app_code`: * Best for: Queries that filter by 
request_date
OR by both 
request_date
and 
app_code
. * Less Effective for: Queries that filter only by 
app_code
. If your most frequent and heavy queries filter by 
app_code
alone (ignoring the date), you might consider putting 
app_code
first in the key. But if you almost always filter by date, your current order 
(request_date, app_code)
is the best choice. Summary: You don't need to worry about 
NULL
values; the table will handle them correctly and maintain high performance. References • table_design/indexes/Prefix_index_sort_key.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • table_design/indexes/Prefix_index_sort_key.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/data_model/duplicate_key_table.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md References • table_design/indexes/Prefix_index_sort_key.md • knowledge/usage/best-practice.md
l
@Rocky SQL Error [1064] [42000]: Getting syntax error at line 11, column 0. Detail message: No viable statement for input 'PARTITION BY RANGE(
request_date
) DISTRIBUTED'. Getting syntax error at line 11, column 0. Detail message: No viable statement for input 'PARTITION BY RANGE(
request_date
) DISTRIBUTED'. CREATE TABLE dmapp.
dtm_pcb_data_result_extract_new
( 
id
bigint(20) NOT NULL COMMENT "ID định danh", 
app_code
varchar(50) NOT NULL COMMENT "Mã hồ sơ", 
request_date
datetime NULL COMMENT "Ngày yêu cầu", 
response_date
datetime NULL COMMENT "Ngày phản hồi", 
js_path
varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON", 
js_value
varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON" ) ENGINE=OLAP DUPLICATE KEY(
request_date
, 
app_code
) -- Optimized for prefix index PARTITION BY RANGE(
request_date
) DISTRIBUTED BY HASH(
id
) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "MONTH", "dynamic_partition.start" = "-36", "dynamic_partition.end" = "2", "dynamic_partition.prefix" = "p", "dynamic_partition.time_zone" = "Asia/Ho_Chi_Minh", "replication_num" = "1", -- Changed to 3 for production safety "fast_schema_evolution" = "true" );
r
The syntax error is happening because 
PARTITION BY RANGE
 requires a defined list of partitions (using parentheses 
(...)
) or a batch creation clause (using 
START/END
). You cannot leave it empty even if you have dynamic partitioning properties enabled. Additionally, I recommend switching to Expression Partitioning (available since v3.1). It is the modern way to handle dynamic partitions—it's much simpler and doesn't require all the 
dynamic_partition
properties. Recommended Fix (Modern Way - v3.1+) This approach uses 
date_trunc
to automatically create monthly partitions based on the data you load.
Copy code
sql
CREATE TABLE dmapp.`dtm_pcb_data_result_extract_new` (
  `id` bigint(20) NOT NULL COMMENT "ID định danh",
  `app_code` varchar(50) NOT NULL COMMENT "Mã hồ sơ",
  `request_date` datetime NOT NULL COMMENT "Ngày yêu cầu", -- Changed to NOT NULL for better partitioning
  `response_date` datetime NULL COMMENT "Ngày phản hồi",
  `js_path` varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON",
  `js_value` varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON"
) ENGINE=OLAP
DUPLICATE KEY(`request_date`, `app_code`)
PARTITION BY date_trunc('month', `request_date`) -- Automatic partitioning
DISTRIBUTED BY HASH(`id`) BUCKETS 8
PROPERTIES (
  "compression" = "LZ4",
  "partition_live_number" = "38", -- Replaces dynamic_partition.start/history_num (keeps ~3 years)
  "replication_num" = "1",        -- Warning: Use 3 for production safety
  "fast_schema_evolution" = "true"
);
Alternative Fix (Legacy Way) If you prefer to keep using the 
dynamic_partition
properties, you must add the 
START/END
range to the DDL so StarRocks knows the initial structure:
Copy code
sql
CREATE TABLE dmapp.`dtm_pcb_data_result_extract_new` (
  `id` bigint(20) NOT NULL COMMENT "ID định danh",
  `app_code` varchar(50) NOT NULL COMMENT "Mã hồ sơ",
  `request_date` datetime NOT NULL COMMENT "Ngày yêu cầu",
  `response_date` datetime NULL COMMENT "Ngày phản hồi",
  `js_path` varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON",
  `js_value` varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON"
) ENGINE=OLAP
DUPLICATE KEY(`request_date`, `app_code`)
PARTITION BY RANGE(`request_date`) (
    -- This defines the initial range. Dynamic Partitioning will take over from here.
    START ("2023-01-01") END ("2025-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(`id`) BUCKETS 8
PROPERTIES (
  "dynamic_partition.enable" = "true",
  "dynamic_partition.time_unit" = "MONTH",
  "dynamic_partition.start" = "-36",
  "dynamic_partition.end" = "2",
  "dynamic_partition.prefix" = "p",
  "dynamic_partition.time_zone" = "Asia/Ho_Chi_Minh",
  "replication_num" = "1",
  "fast_schema_evolution" = "true"
);
Why the error happened: 1. Missing Range Definitions: When using the 
RANGE
keyword, StarRocks expects 
( PARTITION ... )
or 
( START ... )
. 2. Nullable Partition Column: It is highly recommended to set your partitioning column (
request_date
) to 
NOT NULL
. If a row has a 
NULL
date, it would otherwise go into a special "NULL" partition or cause errors during loading if not handled. Note on Replication: You are using 
"replication_num" = "1"
. If this is a production environment, please change this to 
3
to prevent data loss if a server fails. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/data_distribution/dynamic_partitioning.md • table_design/data_distribution/Data_distribution.md References
l
@Rocky insert overwrite dmapp.
dtm_pcb_data_result_extract_new
select * from dmapp.DTM_PCB_DATA_RESULT_EXTRACT; this one good for 50GB table
r
Yes, using 
INSERT OVERWRITE ... SELECT *
is the correct and standard way to migrate data into a new table structure in StarRocks. For a 50GB table, this is well within the capabilities of a single transaction, provided your cluster has sufficient resources. Here is a breakdown of why this works and how to ensure it runs smoothly: 1. Why 
INSERT OVERWRITE
is good * Atomicity: If the process fails halfway through (due to a timeout or memory issue), the target table remains in its previous state. It won't leave "half-finished" data. * Automatic Partitioning: Since you used 
PARTITION BY date_trunc(...)
in your new table, StarRocks will automatically calculate which partition each row belongs to as it reads from the old table. 2. Recommended Settings for 50GB For a 50GB load, the default timeout is usually enough (4 hours), but to be safe and avoid "Execute timeout" errors, run these session variables before the 
INSERT
command:
Copy code
sql
-- Increase timeout to 8 hours (28800 seconds) just in case
SET insert_timeout = 28800;

-- Increase memory limit for the session if your cluster has the RAM 
-- (e.g., 16GB or 32GB to handle the shuffle/sort)
SET query_mem_limit = 17179869184; -- 16GB in bytes

-- Optional: If your old table has some invalid data (like strings too long for the new schema), 
-- setting this to false will skip bad rows instead of failing the whole job.
SET enable_insert_strict = false; 

-- Execute the migration
INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT;
3. Things to Check Before Running * Column Alignment: Ensure the column order in 
SELECT *
from the old table exactly matches the column order you defined in your 
CREATE TABLE
for the new table. If you added or moved columns, it is safer to list them explicitly: 
INSERT OVERWRITE ... SELECT id, app_code, request_date, ... FROM ...
* Disk Space: Ensure your cluster has at least 100GB of free space. During the 
INSERT OVERWRITE
, StarRocks will temporarily hold both the old data and the new data until the transaction commits. * Memory: If the job fails with a "Memory Limit Exceeded" error, you may need to perform the insert partition by partition (e.g., 
WHERE request_date >= '2024-01-01' AND request_date < '2024-02-01'
) instead of all at once. However, 50GB is usually fine for a single shot on most production clusters. Summary The command is safe and recommended. For a 50GB table, it is much faster and easier than exporting to files to S3/HDFS and re-reimporting them.re and re-importing. References • loading/InsertInto.md • sql-reference/System_variable.md • faq/loading/Insert_into_faq.md • table_design/data_distribution/expression_partitioning.md References • loading/InsertInto.md • sql-reference/System_variable.md • faq/loading/Insert_into_faq.md • loading/loading_introduction/loading_considerations.md • sql-reference/sql-statements/loading_unloading/INSERT.md
l
@Rocky insert overwrite dmapp.
dtm_pcb_data_result_extract_new
by name select * from dmapp.DTM_PCB_DATA_RESULT_EXTRACT; this one correct?
r
Yes, the syntax 
INSERT OVERWRITE ... BY NAME
is correct, but it is available only from StarRocks v3.2.0 and later. Here is how to use it correctly: The Syntax Your command looks correct:
Copy code
sql
INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT;
Why use 
BY NAME
? * Safety: Instead of matching columns by their order (position), StarRocks will match them by their column names. * Flexibility: If your new table has columns in a different order than the old table, or if you added new columns with default values, 
BY NAME
ensures the data goes into the right place. * SELECT \ Support:* When you use 
SELECT *
, the column names from the source table are used for matching. If the source table has a column named 
app_code
, it will be mapped to the 
app_code
column in the destination table, regardless of where it appears in the schema. Important Requirements 1. Version: Ensure your StarRocks cluster is on v3.2.0 or higher. 2. Matching Names: The column names in your source table must exactly match the column names in your destination table (case-insensitive in most configurations, but it is best to be consistent). 3. No Column List: You cannot specify a manual column list (e.g., 
INSERT OVERWRITE table (col1, col2) BY NAME
)—the 
BY NAME
clause replaces the need for a manual list. 4. Extra Columns: If the destination table has columns that don't exist in the source, StarRocks will fill them with 
NULL
or their defined 
DEFAULT
value. Recommendation for 50GB As mentioned before, for 50GB of data, remember to increase your session timeout so the job doesn't fail:
Copy code
sql
SET insert_timeout = 28800; -- 8 hours
INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT;
References • sql-reference/sql-statements/loading_unloading/INSERT.md • loading/InsertInto.md References • sql-reference/sql-statements/loading_unloading/INSERT.md • loading/InsertInto.md • sql-reference/System_variable.md • table_design/data_distribution/expression_partitioning.md • loading/loading_introduction/loading_considerations.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • table_design/indexes/Prefix_index_sort_key.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • faq/loading/Insert_into_faq.md • table_design/data_distribution/dynamic_partitioning.md • table_design/data_distribution/Data_distribution.md • table_design/data_model/duplicate_key_table.md • knowledge/usage/best-practice.md References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md
l
@Rocky
Copy code
SET insert_timeout = 28800; -- 8 hours
INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT;
big table still get memory limit error, how to fix this
r
To fix the memory limit error for a large table (50GB+), you need to allow StarRocks to use more memory and, more importantly, enable it to "spill" data to disk if it runs out of RAM. When you run 
INSERT INTO ... SELECT
, StarRocks must shuffle and sort the data to match your table's buckets and sort keys. This process is memory-intensive. 1. The "Big Query" Fix (Recommended) Run these settings in the same session before your 
INSERT
command. This tells StarRocks to use disk space as a buffer if the 50GB of data exceeds your RAM.
Copy code
sql
-- 1. Enable spilling to disk (available since v3.0)
-- This prevents OOM by using disk for intermediate sorting/shuffling
SET enable_spill = true;

-- 2. Increase the memory limit for this specific query
-- Setting it to 0 means "unlimited" (restricted only by the BE node's physical limit)
SET query_mem_limit = 0; 

-- 3. Increase timeout (you already have this, but keep it high)
SET insert_timeout = 28800; 

-- Execute your command
INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT;
_Note: For 
enable_spill
to work, your administrator must have defined a 
spill_local_storage_dir
in the BE configuration. If it still fails, check with your admin._ ────────── 2. The "Chunking" Fix (If Memory is still an issue) If your cluster is small, 50GB might still be too much for one transaction. You can process the data month-by-month. Important: If you use 
INSERT OVERWRITE
without specifying a partition, it will wipe the entire table every time. To insert data month-by-month into a partitioned table, use one of these two methods: Option A: Using 
dynamic_overwrite
(StarRocks v3.4+) This is the easiest way. It only overwrites the partitions that exist in your 
SELECT
results.
Copy code
sql
SET dynamic_overwrite = true;

INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT 
WHERE request_date >= '2024-01-01' AND request_date < '2024-02-01';
Option B: Manual Truncate + Insert (All Versions) If you are below v3.4, do it manually to avoid memory pressure: 1. Truncate the new table once. 2. Use 
INSERT INTO
(not overwrite) to load data in chunks.
Copy code
sql
TRUNCATE TABLE dmapp.`dtm_pcb_data_result_extract_new`;

-- Run these separately for different time ranges
INSERT INTO dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT 
WHERE request_date < '2023-01-01';

INSERT INTO dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT 
WHERE request_date >= '2023-01-01' AND request_date < '2024-01-01';
Summary of variables to tune: * `query_mem_limit`: Set to 
0
or a very large number (e.g., 
34359738368
for 32GB). * `enable_spill`: Set to 
true
(crucial for large sorts/shuffles). * `load_transmission_compression_type`: Set to 
LZ4
(can reduce network overhead during shuffle). References • administration/management/resource_management/spill_to_disk.md • sql-reference/System_variable.md • loading/InsertInto.md • faq/loading/Insert_into_faq.md References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md
l
@Rocky my python connect connecto to starrock got error InterfaceError: Lost connection to MySQL server during query
r
The error 
InterfaceError: Lost connection to MySQL server during query
in Python usually means the connection was closed while the script was waiting for StarRocks to finish the 50GB 
INSERT OVERWRITE
task. Since a 50GB operation can take a significant amount of time, this usually happens for one of three reasons: 1. Python Driver Timeout Most Python MySQL drivers (like 
PyMySQL
or 
mysql-connector-python
) have a default socket timeout. If StarRocks doesn't send any data back within that window, the client drops the connection. * Fix for PyMySQL: Add 
read_timeout
to your connection string.
Copy code
python
    pymysql.connect(
        host='...',
        ...,
        read_timeout=28800 # 8 hours in seconds
    )
* Fix for mysql-connector-python: Use 
connection_timeout
. 2. Load Balancer or Proxy Timeout If you are connecting through a Load Balancer (like HAProxy, Nginx, or an AWS/F5 LB), the proxy likely has an idle timeout (often set to 60 seconds or 5 minutes). Even if StarRocks is working hard, the proxy sees no traffic and kills the connection. * Fix: Check your Load Balancer settings and increase the "Server Timeout" or "Idle Timeout" to match your 
insert_timeout
(e.g., 8 hours). * Alternative: Connect directly to the StarRocks FE Leader IP address for this specific large task to bypass the proxy. 3. StarRocks FE/BE "Crash" or Restart If the memory pressure from the 50GB load becomes too high, the StarRocks FE Leader or the BE node might crash or restart. * Check: Run 
show backends;
and 
show frontends;
in a SQL console to see the 
LastStartTime
. If it was just a few minutes ago, the process crashed. * Fix: Ensure you have applied the 
SET enable_spill = true;
and 
SET query_mem_limit = 0;
settings I mentioned earlier to prevent Out Of Memory (OOM) failures. Recommended Python Code Pattern To handle this safely, set the session variables inside your Python script before running the query:
Copy code
python
import pymysql

# Increase read_timeout to 8 hours on the client side
conn = pymysql.connect(host='...', user='...', password='...', read_timeout=28800)

try:
    with conn.cursor() as cursor:
        # 1. Set server-side timeouts and memory safety
        cursor.execute("SET insert_timeout = 28800;")
        cursor.execute("SET query_mem_limit = 0;")
        cursor.execute("SET enable_spill = true;")
        
        # 2. Run the big insert
        print("Starting 50GB migration...")
        cursor.execute("""
            INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
            SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT;
        """)
        conn.commit()
        print("Success!")
finally:
    conn.close()
Final Tip: If the connection still drops, it is almost certainly a network/load balancer timeout. Moving the script to run on the same network (or the same machine) as the FE can help rule this out. References • loading/InsertInto.md • sql-reference/System_variable.md • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md References • loading/InsertInto.md • sql-reference/System_variable.md • faq/operation_maintenance_faq.md • administration/management/FE_configuration.md • faq/Sql_faq.md • sql-reference/Error_code.md
l
@Rocky -- dmapp.dtm_pcb_data_result_extract definition CREATE TABLE 
dtm_pcb_data_result_extract
( 
request_date
datetime NOT NULL COMMENT "Ngày yêu cầu", 
app_code
varchar(50) NOT NULL COMMENT "Mã hồ sơ", 
id
bigint(20) NOT NULL COMMENT "ID định danh", 
response_date
datetime NULL COMMENT "Ngày phản hồi", 
js_path
varchar(4000) NULL COMMENT "Đường dẫn thuộc tính JSON", 
js_value
varchar(4000) NULL COMMENT "Giá trị thuộc tính JSON" ) ENGINE=OLAP DUPLICATE KEY(
request_date
, 
app_code
) PARTITION BY RANGE(
request_date
) Error: NULL value in non-nullable column 'app_code'.
r
The error 
NULL value in non-nullable column 'app_code'
occurs because your source data contains 
NULL
values in the 
app_code
field, but your target table definition specifically marks 
app_code
as 
NOT NULL
. StarRocks "strict mode" (which is on by default for 
INSERT
) will fail the entire transaction if it encounters a 
NULL
destined for a 
NOT NULL
column. Here are the three best ways to fix this: Option 1: Allow NULLs in the target table (easiest) If it is acceptable for your data to have missing 
app_code
values, change your 
CREATE TABLE
statement to allow `NULL`:
Copy code
sql
CREATE TABLE `dtm_pcb_data_result_extract_new` (
  `request_date` datetime NOT NULL,
  `app_code` varchar(50) NULL,  -- Changed from NOT NULL to NULL
  `id` bigint(20) NOT NULL,
  ...
) ENGINE=OLAP
DUPLICATE KEY(`request_date`, `app_code`)
PARTITION BY RANGE(`request_date`)
...
Option 2: Clean the data during the INSERT If you must keep the column 
NOT NULL
, use 
COALESCE
or 
IFNULL
in your 
SELECT
statement to replace any 
NULL
with a default placeholder string (like 
'N/A'
or 
'UNKNOWN'
):
Copy code
sql
INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT 
    request_date,
    COALESCE(app_code, 'UNKNOWN') AS app_code, -- Replaces NULLs with 'UNKNOWN'
    id,
    response_date,
    js_path,
    js_value
FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT;
_Note: When using 
BY NAME
, ensure you use 
AS app_code
so the column name still matches the target table._ Option 3: Filter out rows with NULL values If rows without an 
app_code
are invalid and should be skipped entirely:
Copy code
sql
INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME 
SELECT * FROM dmapp.DTM_PCB_DATA_RESULT_EXTRACT 
WHERE app_code IS NOT NULL;
Option 4: Disable strict mode (Use with caution) You can set 
enable_insert_strict
to 
false
. This tells StarRocks to ignore rows that violate the 
NOT NULL
constraint instead of failing the job. Note: Those rows will be lost.
Copy code
sql
SET enable_insert_strict = false;
INSERT OVERWRITE dmapp.`dtm_pcb_data_result_extract_new` BY NAME ...
References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • faq/loading/Insert_into_faq.md • sql-reference/System_variable.md References • sql-reference/sql-statements/loading_unloading/INSERT.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • loading/InsertInto.md
l
@Rocky rewrite this for faster in starrocs SELECT a.SYS_RUN_DATE, a.CONTRACT_NO, a.LMS_CIF_NO, a.LD_NO, a.LD_DI_DATE, a.DRAWDOWN_AMOUNT_BASE, a.DPD_LD, a.WRITEOFF_DATE, a.REVERSED_MATURED, a.INT_UNBILL, a.INT_PAST_DUE, a.OD_PAST_DUE, a.OD_UNBILL, a.OD_POSTED, a.FIRST_PAYMENT_DATE, a.LAST_PAYMENT_DATE, a.LAST_PAYMENT_AMOUNT, a.OVER_DUE_DAYS_LD, a.OUTSTANDING, a.DUE_DATE_OF_MON, a.PAYMENT_AMOUNT_NET_MTD, a.PAYMENT_AMOUNT_MTD, a.AMT_MUST_COLLECTED, a.CA_ACCT_BALANCE, a.PRI_PAST_DUE, a.EMI_AMOUNT, a.TOTAL_EMI_AMOUNT_DCS, a.MIN_MUST_PAYMENT, a.MIN_MUST_PAYMENT_DCS, a.TOTAL_MIN_MUST_PAYMENT_DCS, a.PRI_REC_ALLOCATED_TO_DATE, a.INT_REC_ALLOCATED_TO_DATE, a.OD_REC_ALLOCATED_TO_DATE, a.OD_PAST_DUE_MIN, a.INT_PAST_DUE_MIN, a.PRI_PAST_DUE_MIN, a.CONTRACT_STATUS FROM DMAPP.DTM_CUSTOMER_LD_SUMMARY a INNER JOIN ( SELECT DISTINCT SYS_RUN_DAY FROM lps.date_dim WHERE SYS_RUN_DAY >= CURRENT_DATE() - INTERVAL 3 DAY AND IS_PRESE = 1 UNION (SELECT DISTINCT SYS_RUN_DAY FROM lps.date_dim WHERE is_rpt = 1 AND IS_PRESE = 1 ORDER BY SYS_RUN_DAY DESC LIMIT 2) UNION (SELECT DISTINCT SYS_RUN_DAY FROM lps.date_dim WHERE IS_EOM = 1 AND IS_PRESE = 1 AND IS_LASTEST = 0 ORDER BY SYS_RUN_DAY DESC LIMIT 3) ) B ON a.SYS_RUN_DATE = B.SYS_RUN_DAY
r
To rewrite this for maximum performance in StarRocks, we will focus on Join Optimization, Partition Pruning, and Read Efficiency. Optimized Query
Copy code
sql
WITH TargetDates AS (
    -- Get the unique set of dates first
    SELECT SYS_RUN_DAY
    FROM lps.date_dim
    WHERE SYS_RUN_DAY >= CURRENT_DATE() - INTERVAL 3 DAY
      AND IS_PRESE = 1
    UNION -- UNION automatically performs DISTINCT across all sets
    SELECT SYS_RUN_DAY 
    FROM (
        SELECT SYS_RUN_DAY FROM lps.date_dim 
        WHERE is_rpt = 1 AND IS_PRESE = 1 
        ORDER BY SYS_RUN_DAY DESC LIMIT 2
    ) t1
    UNION
    SELECT SYS_RUN_DAY 
    FROM (
        SELECT SYS_RUN_DAY FROM lps.date_dim 
        WHERE IS_EOM = 1 AND IS_PRESE = 1 AND IS_LASTEST = 0 
        ORDER BY SYS_RUN_DAY DESC LIMIT 3
    ) t2
)
SELECT /*+ SET_VAR(broadcast_row_limit = 1000000) */
    a.SYS_RUN_DATE,
    a.CONTRACT_NO,
    a.LMS_CIF_NO,
    a.LD_NO,
    a.LD_DI_DATE,
    a.DRAWDOWN_AMOUNT_BASE,
    a.DPD_LD,
    a.WRITEOFF_DATE,
    a.REVERSED_MATURED,
    a.INT_UNBILL,
    a.INT_PAST_DUE,
    a.OD_PAST_DUE,
    a.OD_UNBILL,
    a.OD_POSTED,
    a.FIRST_PAYMENT_DATE,
    a.LAST_PAYMENT_DATE,
    a.LAST_PAYMENT_AMOUNT,
    a.OVER_DUE_DAYS_LD,
    a.OUTSTANDING,
    a.DUE_DATE_OF_MON,
    a.PAYMENT_AMOUNT_NET_MTD,
    a.PAYMENT_AMOUNT_MTD,
    a.AMT_MUST_COLLECTED,
    a.CA_ACCT_BALANCE,
    a.PRI_PAST_DUE,
    a.EMI_AMOUNT,
    a.TOTAL_EMI_AMOUNT_DCS,
    a.MIN_MUST_PAYMENT,
    a.MIN_MUST_PAYMENT_DCS,
    a.TOTAL_MIN_MUST_PAYMENT_DCS,
    a.PRI_REC_ALLOCATED_TO_DATE,
    a.INT_REC_ALLOCATED_TO_DATE,
    a.OD_REC_ALLOCATED_TO_DATE,
    a.OD_PAST_DUE_MIN,
    a.INT_PAST_DUE_MIN,
    a.PRI_PAST_DUE_MIN,
    a.CONTRACT_STATUS
FROM DMAPP.DTM_CUSTOMER_LD_SUMMARY a
JOIN [BROADCAST] TargetDates B ON a.SYS_RUN_DATE = B.SYS_RUN_DAY;
Why this is faster: 1. Broadcast Join Hint (
[BROADCAST]
): Your subquery 
B
returns a very small number of rows (at most 8 dates). By forcing a 
BROADCAST
join, StarRocks will send these 8 dates to every machine where the large table 
a
is stored. This prevents a "Shuffle Join" which would move 50GB+ of data across the network. 2. CTE for Readability & Execution: Using 
WITH TargetDates
allows the StarRocks optimizer to evaluate the date logic as a single unit. It ensures the 
UNION
operation (which deduplicates dates) happens before the join, preventing any row multiplication. 3. Partition Pruning: If 
DTM_CUSTOMER_LD_SUMMARY
is partitioned by 
SYS_RUN_DATE
, StarRocks will automatically use the results from the 
TargetDates
subquery to skip scanning all historical data. It will only read the 8 relevant days from the disk. 4. Columnar Storage Efficiency: Ensure you only list the columns you actually need. Since StarRocks is a columnar database, every column you remove from the 
SELECT
list reduces I/O and increases speed. (I have fixed the 
a.INT
and 
a.CA
aliases which looked like URLs in your snippet). Additional Performance Tips: * Colocate Join: If 
date_dim
and 
DTM_CUSTOMER_LD_SUMMARY
are both distributed (hashed) by the same date column and have the same number of buckets, StarRocks can perform a "Colocate Join" which is the fastest possible join (no data movement at all). * Check Table Type: For the 
date_dim
table, if it is small, define it as a Replicated Table (
DISTRIBUTED BY HASH(...) BUCKETS ...
with a property 
REPLICATION_NUM
set to the number of BE nodes). This makes all joins with it local. * Runtime Filters: StarRocks 3.x+ will automatically generate a Global Runtime Filter for this join, which is very effective for large table vs. small table scenarios. References • administration/management/resource_management/spill_to_disk.md • sql-reference/System_variable.md
l
@Rocky SET query_timeout = 28800; set to all user, all system
r
To set the 
query_timeout
to 8 hours (28,800 seconds) for all users and the entire system, run the following command:
Copy code
sql
SET GLOBAL query_timeout = 28800;
Important Details: * Scope: This sets the global default. Any new connections/sessions created after you run this command will inherit the 8-hour timeout. * Existing Sessions: This command does not affect sessions that are already connected. Existing sessions will keep their original timeout until they reconnect. * Insert/Loading Tasks: If you are trying to fix the timeout for your 50GB migration (
INSERT OVERWRITE
), note that from v3.4.0 onwards, 
query_timeout
no longer controls 
INSERT
statements. You must also set the 
insert_timeout
globally:
Copy code
sql
    SET GLOBAL insert_timeout = 28800;
* Verification: You can verify the change by opening a new terminal/session and running:
Copy code
sql
    SHOW VARIABLES LIKE '%query_timeout%';
Per-User Specific Setting (Optional) If you want to ensure a specific user always has this timeout regardless of the global default, you can use (from v3.3.3+):
Copy code
sql
ALTER USER 'your_username' SET PROPERTIES ('session.query_timeout' = '28800');
References • sql-reference/System_variable.md • faq/operation_maintenance_faq.md • administration/management/resource_management/Query_management.md • sql-reference/sql-statements/cluster-management/config_vars/SET.md References • faq/operation_maintenance_faq.md • sql-reference/System_variable.md • administration/management/resource_management/Query_management.md • administration/management/resource_management/query_queues.md • sql-reference/sql-statements/cluster-management/config_vars/SET.md
2 Views
Open in Slack
PreviousNext
Powered by