@Rocky hello

Hello! I'm Rocky, your StarRocks assistant. How can I help you today?

@Rocky 10.0.27.71/query how to expose slow query in starrock to grafana

To expose slow queries from StarRocks to Grafana, you can use two main approaches: Metric-based (for high-level trends) and Log-based/Table-based (for detailed SQL analysis). 1. Monitor Slow Query Trends (Prometheus + Grafana) StarRocks exports metrics to Prometheus via its

/metrics

endpoint. While there isn't a single "slow query count" metric, you can monitor latency percentiles and long-running queries. * Endpoint:

http://<FE_IP>:8030/metrics

* Key Metrics: * `starrocks_fe_query_resource_group_latency`: Query latency percentiles. * `starrocks_fe_unfinished_query`: Number of currently running queries. * `starrocks_fe_query_err`: Includes queries that failed due to timeout. * Setup: Point your Prometheus to the FE metrics endpoint and use the official StarRocks Grafana dashboard (available on GitHub or Grafana Labs) to visualize these. 2. Expose Detailed Slow SQL (AuditLoader Plugin) This is the recommended way to see the actual SQL statements in Grafana. The

AuditLoader

plugin automatically loads StarRocks'

fe.audit.log

into a StarRocks table, which you can then query from Grafana using the MySQL Data Source. Step 1: Set the Slow Query Threshold In StarRocks, define what qualifies as "slow" (default is 5000ms):

sql
ADMIN SET FRONTEND CONFIG ("qe_slow_log_ms" = "3000"); -- Set to 3 seconds

Step 2: Install AuditLoader 1. Download and install the

AuditLoader

plugin (found in the StarRocks GitHub or installation package). 2. Create the audit database and table as described in the AuditLoader documentation. 3. The plugin will start loading logs into

starrocks_audit_db*.starrocks_audit_tbl*

. Step 3: Connect Grafana 1. In Grafana, add a new MySQL Data Source. 2. Connect to your StarRocks FE (port 9030). 3. Create a table or time-series panel using a query like:

sql
    SELECT
        timestamp,
        user,
        queryTime / 1000 AS duration_sec,
        stmt AS sql_text
    FROM starrocks_audit_db*.starrocks_audit_tbl*
    WHERE queryType = 'slow_query'
    ORDER BY timestamp DESC;

3. Alternative: Log Aggregation (Loki/ELK) If you prefer not to load logs back into StarRocks: 1. Enable JSON logging for audit logs:

ADMIN SET FRONTEND CONFIG ("audit_log_json_format" = "true");

. 2. Use a log collector (like Promtail/Loki or Filebeat/ELK) to scrape

fe/log/fe.audit.log

. 3. In Grafana, use the Loki data source to filter and display slow queries. Summary of Configuration Parameter | Default | Description

qe_slow_log_ms

| 5000 | Threshold for logging a query as "slow" in

fe.audit.log

.

enable_qe_slow_log

| true | Whether to record slow queries in the audit log.

audit_log_json_format

| false | Change to

true

if using log collectors like Loki/ELK collectors. For detailed SQL-level troubleshooting, Approach #2 (AuditLoader) is the most common community. . . . SQL table)** is the most powerful as it allows you to join slow query data with other system tables. References • administration/management/audit_loader.md • faq/operation_maintenance_faq.md • administration/management/logs.md • administration/sql_digest.md

@Rocky colume user_table.last_updated_date - datatime, select last_updated_date in last 1 hour

To select records from the last 1 hour in StarRocks, you can use the

NOW()

function combined with either

DATE_SUB

or

HOURS_SUB

. Option 1: Using

DATE_SUB

(Standard SQL) This is the most common way to subtract a time interval.

sql
SELECT last_updated_date 
FROM user_table 
WHERE last_updated_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR);

Option 2: Using

HOURS_SUB

(Direct Function) StarRocks also provides a specific function for subtracting hours.

sql
SELECT last_updated_date 
FROM user_table 
WHERE last_updated_date >= HOURS_SUB(NOW(), 1);

Option 3: Using Simple Subtraction You can also use the shorthand interval subtraction syntax.

sql
SELECT last_updated_date 
FROM user_table 
WHERE last_updated_date >= NOW() - INTERVAL 1 HOUR;

Key Functions Used: *

NOW()

: Returns the current system date and time as a

DATETIME

. *

INTERVAL 1 HOUR

: Specifies the time duration to subtract. *

HOURS_SUB(date, hours)

: A specialized function that subtracts a specific number of hours. References • sql-reference/sql-functions/date-time-functions/date_sub.md • sql-reference/sql-functions/date-time-functions/hours_sub.md • sql-reference/sql-functions/date-time-functions/now.md References • sql-reference/sql-functions/date-time-functions/date_sub.md • sql-reference/sql-functions/date-time-functions/days_sub.md • sql-reference/sql-functions/date-time-functions/seconds_sub.md • sql-reference/sql-functions/date-time-functions/microseconds_sub.md • sql-reference/sql-functions/date-time-functions/years_sub.md • sql-reference/sql-functions/date-time-functions/weeks_sub.md • sql-reference/sql-functions/date-time-functions/minutes_sub.md • sql-reference/sql-functions/date-time-functions/hours_sub.md

@Rocky CREATE TABLE

dtm_los_log_cbscore

(

SYS_RUN_DATE

datetime NULL COMMENT "",

ID

decimal(38, 20) NULL COMMENT "",

APP_CODE

varchar(50) NULL COMMENT "",

REQUEST_TIME

datetime NULL COMMENT "",

RESPOND_TIME

datetime NULL COMMENT "",

INPUT

varchar(65533) NULL COMMENT "",

OUTPUT

varchar(65533) NULL COMMENT "",

CREATED_DATE

datetime NULL COMMENT "",

UPDATED_DATE

datetime NULL COMMENT "",

PCB_RQ_ID

decimal(38, 20) NULL COMMENT "",

SCORE_STAGE

varchar(50) NULL COMMENT "",

CURRENT_STAGE

varchar(50) NULL COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(

SYS_RUN_DATE

,

ID

) DISTRIBUTED BY HASH(

APP_CODE

) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "1" ); WITH base AS ( SELECT app_code, OUTPUT, ROW_NUMBER() OVER (PARTITION BY app_code ORDER BY request_time DESC) AS rn FROM dmapp.dtm_los_log_cbscore WHERE respond_time >= DATE('2025-01-01') ), cb_monitor_data AS ( SELECT b.app_code, b.rn, COALESCE(get_json_string(output, '$.CBScore'), get_json_string(output, '$.cbscore')) AS cb_score, COALESCE(get_json_string(output, '$.CBRank'), get_json_string(output, '$.cbrank')) AS cb_rank FROM base b WHERE b.rn = 1 ) SELECT t1.*, t2.created_date, DATE_FORMAT(t2.created_date, '%Y-%m') AS created_month , t2.contract_number, T2.PRODUCT_CATEGORY, t2.disbursement_date, t2.CURRENT_REASON, t2.CURRENT_DECISION, t2.dti, t2.pti , t2.LOAN_AMOUNT, t2.LOAN_TERM , CASE WHEN t2.PRODUCT_CATEGORY IN ('CHS', 'TOPUP_DG', 'TOPUP') THEN 'XSTU' WHEN t2.PRODUCT_CATEGORY IN ('TIMO', 'ZALOPAY', 'MOMO', 'VEGA') THEN 'NTB_Digital' ELSE 'NTB_Traditional' END AS product_group , t3.OVERDUE_DAY_NUMBER FROM cb_monitor_data t1 INNER JOIN dmapp.dtm_cus_applications t2 ON t1.app_code = t2.app_code inner join losrep.pcb_result t3 ON t1.app_code = t3.app_code WHERE disbursement_date IS NULL; table above having OUTPUT store json data, and then used in sql, review this table design for better json data support

To optimize your StarRocks table for JSON data, you should move away from

VARCHAR

and utilize the native

JSON

data type and Generated Columns. Here is the recommended redesign and the reasons why: 1. Optimized Table Schema Change the

INPUT

and

OUTPUT

columns to the

JSON

type.

sql
CREATE TABLE `dtm_los_log_cbscore` (
  `SYS_RUN_DATE` datetime NULL,
  `ID` decimal(38, 20) NULL,
  `APP_CODE` varchar(50) NULL,
  `REQUEST_TIME` datetime NULL,
  `RESPOND_TIME` datetime NULL,
  -- Optimized JSON type
  `INPUT` JSON NULL,
  `OUTPUT` JSON NULL,
  `CREATED_DATE` datetime NULL,
  `UPDATED_DATE` datetime NULL,
  `PCB_RQ_ID` decimal(38, 20) NULL,
  `SCORE_STAGE` varchar(50) NULL,
  `CURRENT_STAGE` varchar(50) NULL,
  -- Optional: Generated Column for high-performance filtering/sorting
  `cb_score_flat` AS (get_json_string(OUTPUT, '$.CBScore')) 
) ENGINE=OLAP
DUPLICATE KEY(`SYS_RUN_DATE`, `ID`)
DISTRIBUTED BY HASH(`APP_CODE`) BUCKETS 8
PROPERTIES (
  "compression" = "LZ4",
  "fast_schema_evolution" = "true"
);

2. Benefits of the

JSON

Type * Faster Extraction: Unlike

VARCHAR

, the

JSON

type is stored in a binary format. StarRocks doesn't have to parse the entire string every time you call `get_json_string`; it can jump directly to the key. * Cleaner Syntax: You can use the

->

operator instead of the verbose

get_json_string

function. * Storage Efficiency: Binary JSON often takes less space than raw JSON strings. 3. Refactored Query (Using JSON Syntax) With the

JSON

type, your query becomes more readable:

sql
WITH base AS (
    SELECT
        app_code,
        OUTPUT,
        ROW_NUMBER() OVER (PARTITION BY app_code ORDER BY request_time DESC) AS rn
    FROM dmapp.dtm_los_log_cbscore
    WHERE respond_time >= '2025-01-01' -- Implicit conversion works for datetime
),
cb_monitor_data AS (
    SELECT
        b.app_code,
        b.rn,
        -- Use arrow operators for native JSON access
        COALESCE(output->'$.CBScore', output->'$.cbscore') AS cb_score,
        COALESCE(output->'$.CBRank', output->'$.cbrank') AS cb_rank
    FROM base b
    WHERE b.rn = 1
)
-- ... rest of the join remains the same

4. Advanced Performance: Generated Columns If your

OUTPUT

JSON is very large (several MBs) or you frequently filter by

cb_score

, use Generated Columns. * How it works: StarRocks extracts the value during data ingestion and stores it as a regular physical column. * Benefit: In your query, you simply select the column name (

cb_score_flat

). This is significantly faster because it bypasses JSON processing entirely during query execution. 5. Summary of Recommendations 1. Switch to

JSON

Type: Replace

VARCHAR(65533)

with

JSON

for better performance and easier syntax. 2. Case Sensitivity: Your

COALESCE

approach is correct because JSON keys are case-sensitive. Keep this logic. 3. Data Type for IDs: Unless you truly need 20 decimal places for an ID, consider using

BIGINT

or

LARGEINT

for

ID

and

PCB_RQ_ID

to improve join and sort performance. 4. Partitioning: If this table grows large, consider adding

PARTITION BY RANGE(SYS_RUN_DATE)

to allow for data retention management and partition pruning. References • sql-reference/data-types/JSON.md • using_starrocks/JSON_data.md • sql-reference/sql-functions/json-functions/get_json_string.md • sql-reference/sql-statements/data-definition/CREATE_TABLE.md

@Rocky INSERT INTO CHIKH_LOAN_DISBURSED_BY_REGION WITH LIST_RAW AS ( SELECT B.END_MONTH, B.REGION, A.DISBURSEMENT_DATE, LOAN_AMT, MOB3_CASE_RISK, MOB3_CASE_KPI, FPD30_CASE_RISK, FPD_CASE_30, FIRST_PAYMENT_DATE, MOB3_DATE, REMAINING_AMOUNT, REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') AS PROVINCE, CONTRACT_STATUS FROM CHIKH_LOAN_TRACKING A LEFT JOIN SOURCE_SALE_TOTAL B ON A.SALE_CODE = B.SALE_CODE AND END_MONTH = LAST_DAY(A.LAST_UPDATE_DATE) LEFT JOIN CHIKH_APPLICATION_TRACKING_V3 C ON A.APP_CODE = C.APP_CODE WHERE ( A.DISBURSEMENT_DATE > LAST_DAY(ADD_MONTHS(TO_DATE(SYSDATE -1), -1)) OR MOB3_CASE_RISK = 'Y' OR FPD30_CASE_RISK = 'Y') AND REGION IS NOT NULL UNION ALL SELECT END_MONTH, B.REGION, TRUNC(TRANSACTION_DATE_AUTH) AS DISBURSEMEN_DATE, (NVL(SUM(CASE WHEN LOWER(TRANSACTION_TYPE_LBL) IN ('withdraw') AND UPPER(FEE) = 'FALSE' AND UPPER(TAX) = 'FALSE' AND REFUND_TXN IS NULL THEN NVL(AMOUNT,0) END),0) + NVL(SUM(CASE WHEN LOWER(TRANSACTION_TYPE_LBL) IN ('account funding') AND UPPER(MEMO) = 'REPAYMENT' AND UPPER(FEE) = 'FALSE' AND UPPER(TAX) = 'FALSE' AND REFUND_TXN IS NULL THEN NVL(AMOUNT,0) END),0) + NVL(SUM(CASE WHEN -- (C.SCHEME_PRODUCT LIKE '%CHS%' OR C.SCHEME_PRODUCT LIKE '%TOPUP%') --AND UPPER(MEMO) = 'SUNG_TUC_FEE' THEN NVL(AMOUNT, 0) -- Giả định bạn muốn cộng thêm AMOUNT nếu là SUNG_TUC_FEE ELSE 0 END), 0) )*-1 AS LOAN_AMT, 'N' AS MOB3_CASE_RISK, 'N' AS MOB3_CASE_KPI, 'N' AS FPD30_CASE_RISK, 'N' AS FPD_CASE_30, NULL FIRST_PAYMENT_DATE, NULL MOB3_DATE, 0 AS REMAINING_AMOUNT, REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') AS PROVINCE, 'Active' FROM CHIKH_CASH_CARD_OBSERVATION_PERIOD_TRACKING A LEFT JOIN SOURCE_SALE_TOTAL B ON A.SALE_CODE = B.SALE_CODE AND LAST_DAY(TRUNC(TRANSACTION_DATE_AUTH)) = B.END_MONTH LEFT JOIN CHIKH_APPLICATION_TRACKING_V3 C ON A.LOS_CONTRACT_NO = C.CONTRACT_NUMBER WHERE TRUNC(TRANSACTION_DATE_AUTH) > LAST_DAY(ADD_MONTHS(TO_DATE(SYSDATE -1), -1)) GROUP BY END_MONTH, B.REGION, TRUNC(TRANSACTION_DATE_AUTH), REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') UNION ALL SELECT B.END_MONTH, B.REGION, NULL DISBURSEMENT_DATE, STATEMENT_TOTAL_PRI AS LOAN_AMT, 'N' AS MOB3_CASE_RISK, 'N' AS MOB3_CASE_KPI, 'Y' AS FPD30_CASE_RISK, CASE WHEN FSTPD_MAX_DPD > 30 THEN 'Y' ELSE 'N'END AS FPD_CASE_30, STATEMENT_DUE_DATE AS FIRST_PAYMENT_DATE, NULL AS MOB3_DATE, FSTPD30_TOTAL_PRI AS REMAINING_AMOUNT, REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') AS PROVINCE, 'Active' FROM PTKD.CHIKH_CASH_CARD_LOAN_INDEX_RAW_V1 A LEFT JOIN SOURCE_SALE_TOTAL B ON A.SALE_CODE = B.SALE_CODE AND last_day(STATEMENT_DUE_DATE+30) = B.END_MONTH LEFT JOIN CHIKH_APPLICATION_TRACKING_V3 C ON A.APP_CODE = C.APP_CODE WHERE PERIOD_NO = 1 AND last_day(STATEMENT_DUE_DATE+30) > LAST_DAY(ADD_MONTHS(TO_DATE(SYSDATE -1), -1)) AND last_day(STATEMENT_DUE_DATE+30) <= LAST_DAY(SYSDATE-1) UNION ALL SELECT B.END_MONTH, B.REGION, NULL AS DISBURSEMENT_DATE, STATEMENT_TOTAL_PRI AS LOAN_AMT, 'Y' AS MOB3_CASE_RISK, CASE WHEN MOB_DPD > 30 THEN 'Y' ELSE 'N' END AS MOB3_CASE_KPI, 'N' AS FPD30_CASE_RISK, 'N' AS FPD_CASE_30, NULL AS FIRST_PAYMENT_DATE, STATEMENT_DUE_DATE AS MOB3_DATE, MOB_TOTAL_PRI AS REMAINING_AMOUNT, REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') AS PROVINCE, 'Active' FROM PTKD.CHIKH_CASH_CARD_LOAN_INDEX_RAW_V1 A LEFT JOIN SOURCE_SALE_TOTAL B ON A.SALE_CODE = B.SALE_CODE AND last_day(STATEMENT_DUE_DATE) = B.END_MONTH LEFT JOIN CHIKH_APPLICATION_TRACKING_V3 C ON A.APP_CODE = C.APP_CODE WHERE PERIOD_NO = 3 AND last_day(STATEMENT_DUE_DATE) > LAST_DAY(ADD_MONTHS(TO_DATE(SYSDATE -1), -1)) AND last_day(STATEMENT_DUE_DATE) <= LAST_DAY(SYSDATE-1) ) , A AS ( SELECT END_MONTH, REGION, PROVINCE, SUM(CASE WHEN A.DISBURSEMENT_DATE > LAST_DAY(ADD_MONTHS(TO_DATE(SYSDATE -1), -1)) -- AND TO_CHAR(TO_DATE(A.DISBURSEMENT_DATE), 'DD') <= TO_CHAR(TO_DATE(SYSDATE-1), 'DD') AND LAST_DAY(A.DISBURSEMENT_DATE) = LAST_DAY(END_MONTH) THEN NVL(LOAN_AMT,0) END)/POWER(10,9) AS VOLUME, SUM(CASE WHEN MOB3_CASE_RISK = 'Y' AND CONTRACT_STATUS = 'Active' THEN NVL(LOAN_AMT,0) END) AS DEL30_MOB3_RISK, SUM(CASE WHEN MOB3_CASE_RISK = 'Y' AND CONTRACT_STATUS = 'Active' AND MOB3_CASE_KPI = 'Y' AND MOB3_DATE >= ADD_MONTHS(TRUNC(SYSDATE-1),-1) THEN NVL(REMAINING_AMOUNT,0) END) AS DEL30_MOB3, SUM(CASE WHEN FPD30_CASE_RISK = 'Y' AND CONTRACT_STATUS = 'Active' THEN NVL(LOAN_AMT,0) END) AS FPD30_RISK, SUM(CASE WHEN FPD30_CASE_RISK = 'Y' AND CONTRACT_STATUS = 'Active' AND FPD_CASE_30 = 'Y' AND FIRST_PAYMENT_DATE + 30 >= ADD_MONTHS(TRUNC(SYSDATE-1),-1) THEN NVL(REMAINING_AMOUNT,0) END) AS FPD30 FROM LIST_RAW A GROUP BY END_MONTH, REGION, PROVINCE ) , ORDER_PROVINCE AS ( SELECT END_MONTH, PROVINCE, SUM(VOLUME), ROW_NUMBER() OVER(PARTITION BY END_MONTH ORDER BY NVL(SUM(NVL(VOLUME,0)),0) DESC ) AS RANK FROM A GROUP BY PROVINCE, END_MONTH ) SELECT A.*, B.RANK, C.TEAM_COUNT FROM A LEFT JOIN ORDER_PROVINCE B ON A.PROVINCE = B.PROVINCE AND A.END_MONTH = B.END_MONTH LEFT JOIN (SELECT END_MONTH, REGION, CASE WHEN PROVINCE = 'Vung Tau' THEN 'Ba Ria - Vung Tau' WHEN PROVINCE = 'Hue' THEN 'Thua Thien Hue' ELSE PROVINCE END AS PROVINCE , COUNT(DISTINCT TEAM_NAME) AS TEAM_COUNT FROM SOURCE_SALE_TOTAL WHERE END_MONTH > LAST_DAY(ADD_MONTHS(TO_DATE(SYSDATE -1), -1)) AND STATUS = 'Active' AND CONTRACT_TYPE LIKE '%NVKD%' GROUP BY END_MONTH, REGION, PROVINCE ) C ON A.END_MONTH = C.END_MONTH AND A.REGION = C.REGION AND TRIM(UPPER(A.PROVINCE)) = TRIM(UPPER(C.PROVINCE)) AND A.REGION IN ('SOUTH', 'NORTH', 'CENTRAL','CENTRAL1','CENTRAL2', 'EAST', 'MEKONG', 'WEST', 'NORTH1', 'NORTH2') ; to starrock sql

To convert this SQL to StarRocks, we need to address specific dialect differences such as date arithmetic,

NVL

vs

COALESCE

, and the

TRUNC

function. Key Changes Made: 1. Date Functions: Replaced

SYSDATE

with

CURRENT_DATE()

, and

ADD_MONTHS(x, y)

with

DATE_ADD(x, INTERVAL y MONTH)

. 2. Null Handling: Replaced

NVL()

with

COALESCE()

or

IFNULL()

. 3. Truncation: Replaced

TRUNC(date)

with

DATE(date)

. 4. String Formatting: Replaced

TO_CHAR(date, 'DD')

with

DAYOFMONTH(date)

. 5. UDFs: Kept

FN_BO_DAU

as is. Note that in StarRocks, you must either create this as a Global/Custom UDF or replace it with a series of

REPLACE

statements to remove Vietnamese accents. StarRocks SQL Version: ```sql INSERT INTO CHIKH_LOAN_DISBURSED_BY_REGION WITH LIST_RAW AS ( SELECT B.END_MONTH, B.REGION, A.DISBURSEMENT_DATE, LOAN_AMT, MOB3_CASE_RISK, MOB3_CASE_KPI, FPD30_CASE_RISK, FPD_CASE_30, FIRST_PAYMENT_DATE, MOB3_DATE, REMAINING_AMOUNT, -- Note: Ensure FN_BO_DAU is registered as a UDF in StarRocks REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') AS PROVINCE, CONTRACT_STATUS FROM CHIKH_LOAN_TRACKING A LEFT JOIN SOURCE_SALE_TOTAL B ON A.SALE_CODE = B.SALE_CODE AND B.END_MONTH = LAST_DAY(A.LAST_UPDATE_DATE) LEFT JOIN CHIKH_APPLICATION_TRACKING_V3 C ON A.APP_CODE = C.APP_CODE WHERE (A.DISBURSEMENT_DATE > LAST_DAY(DATE_ADD(CURRENT_DATE() - INTERVAL 1 DAY, INTERVAL -1 MONTH)) OR MOB3_CASE_RISK = 'Y' OR FPD30_CASE_RISK = 'Y') AND B.REGION IS NOT NULL UNION ALL SELECT END_MONTH, B.REGION, DATE(TRANSACTION_DATE_AUTH) AS DISBURSEMENT_DATE, ( COALESCE(SUM(CASE WHEN LOWER(TRANSACTION_TYPE_LBL) IN ('withdraw') AND UPPER(FEE) = 'FALSE' AND UPPER(TAX) = 'FALSE' AND REFUND_TXN IS NULL THEN COALESCE(AMOUNT, 0) END), 0) + COALESCE(SUM(CASE WHEN LOWER(TRANSACTION_TYPE_LBL) IN ('account funding') AND UPPER(MEMO) = 'REPAYMENT' AND UPPER(FEE) = 'FALSE' AND UPPER(TAX) = 'FALSE' AND REFUND_TXN IS NULL THEN COALESCE(AMOUNT, 0) END), 0) + COALESCE(SUM(CASE WHEN UPPER(MEMO) = 'SUNG_TUC_FEE' THEN COALESCE(AMOUNT, 0) ELSE 0 END), 0) ) * -1 AS LOAN_AMT, 'N' AS MOB3_CASE_RISK, 'N' AS MOB3_CASE_KPI, 'N' AS FPD30_CASE_RISK, 'N' AS FPD_CASE_30, NULL AS FIRST_PAYMENT_DATE, NULL AS MOB3_DATE, 0 AS REMAINING_AMOUNT, REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') AS PROVINCE, 'Active' FROM CHIKH_CASH_CARD_OBSERVATION_PERIOD_TRACKING A LEFT JOIN SOURCE_SALE_TOTAL B ON A.SALE_CODE = B.SALE_CODE AND LAST_DAY(DATE(TRANSACTION_DATE_AUTH)) = B.END_MONTH LEFT JOIN CHIKH_APPLICATION_TRACKING_V3 C ON A.LOS_CONTRACT_NO = C.CONTRACT_NUMBER WHERE DATE(TRANSACTION_DATE_AUTH) > LAST_DAY(DATE_ADD(CURRENT_DATE() - INTERVAL 1 DAY, INTERVAL -1 MONTH)) GROUP BY END_MONTH, B.REGION, DATE(TRANSACTION_DATE_AUTH), REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') UNION ALL SELECT B.END_MONTH, B.REGION, NULL AS DISBURSEMENT_DATE, STATEMENT_TOTAL_PRI AS LOAN_AMT, 'N' AS MOB3_CASE_RISK, 'N' AS MOB3_CASE_KPI, 'Y' AS FPD30_CASE_RISK, CASE WHEN FSTPD_MAX_DPD > 30 THEN 'Y' ELSE 'N' END AS FPD_CASE_30, STATEMENT_DUE_DATE AS FIRST_PAYMENT_DATE, NULL AS MOB3_DATE, FSTPD30_TOTAL_PRI AS REMAINING_AMOUNT, REPLACE(REPLACE(FN_BO_DAU(C.TEMPORARY_ADDRESS_PROVINCE_LBL), 'Thanh pho ', ''), 'Tinh ', '') AS PROVINCE, 'Active' FROM PTKD.CHIKH_CASH_CARD_LOAN_INDEX_RAW_V1 A

@Rocky UPDATE CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB AS A SET FIRST_TO_DC = B.FIRST_TO_DC, PASS_DC = B.PASS_DC, PASS_AP = B.PASS_AP, APP_DC_WITH_CALL = B.APP_DC_WITH_CALL FROM CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB_UPDATE_AR_V AS B WHERE A.SYS_DATE = B.SYS_DATE AND A.MONTH_DATE = B.MONTH_DATE AND IFNULL(A.REGION_RSM, '0') = IFNULL(B.REGION_RSM, '0') AND A.CUS_PROVINCE = B.CUS_PROVINCE; correct?

Your

UPDATE

statement is largely correct for StarRocks version 3.0 or later, provided that the target table is a Primary Key table. However, there are a few syntax adjustments and requirements to ensure it runs correctly: 1. Primary Key Table Requirement In StarRocks, the

UPDATE

statement is only supported for Primary Key tables. If

CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB

is a Duplicate, Aggregate, or Unique key table, this statement will not work. 2. Syntax Adjustments While your logic is sound, StarRocks' parser is strict about aliases in the

UPDATE

clause and column naming in the

SET

clause. * Remove the

AS A

alias on the first line: StarRocks typically does not support aliasing the target table directly in the

UPDATE

line. Use the full table name in the

WHERE

clause instead. * Column Names in `SET`: The left-hand side of the

=

in the

SET

clause must be a simple column name without a table name or alias. (Your query already does this correctly for

FIRST_TO_DC

, etc.). Revised Query (Recommended)

sql
UPDATE CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB
SET FIRST_TO_DC      = B.FIRST_TO_DC,
    PASS_DC          = B.PASS_DC,
    PASS_AP          = B.PASS_AP,
    APP_DC_WITH_CALL = B.APP_DC_WITH_CALL
FROM CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB_UPDATE_AR_V AS B
WHERE CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB.SYS_DATE = B.SYS_DATE
  AND CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB.MONTH_DATE = B.MONTH_DATE
  AND IFNULL(CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB.REGION_RSM, '0') = IFNULL(B.REGION_RSM, '0')
  AND CHIKH_PERFORMANCE_BY_CUSTOMER_PROVINCE_DB.CUS_PROVINCE = B.CUS_PROVINCE;

Important Notes: 1. StarRocks Version: The

FROM

clause for multi-table updates was introduced in v3.0. If you are on an older version (e.g., 2.5), you cannot join tables in an

UPDATE

statement. 2. Non-deterministic Updates: If the join results in multiple rows from the

FROM

table (B) matching a single row in the target table (A), StarRocks will choose one of the rows from B at random to perform the update. Ensure your join conditions represent a 1-to-1 relationship. 3. Partial Updates (v3.1+): If your target table has many columns and you are only updating a few, StarRocks 3.1+ will automatically use "column mode" for faster performance. References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • table_design/table_types/primary_key_table.md References • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • release_notes/release-3.2.md • release_notes/release-3.0.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md

@Rocky how to inscrease query timeout to starrocks user when using this user in power BI

To increase the query timeout for a specific StarRocks user (especially for long-running Power BI reports), you can use the following methods depending on your StarRocks version. 1. For StarRocks v3.3.3 and later (Recommended) You can permanently bind the

query_timeout

session variable to a specific user using the

ALTER USER

command. This ensures that every time Power BI connects with this user, the timeout is automatically applied.

sql
-- Set the timeout to 3600 seconds (1 hour) for the user 'powerbi_user'
ALTER USER 'powerbi_user' SET PROPERTIES ('session.query_timeout' = '3600');

2. Global Change (All Users) If you want to increase the timeout for the entire cluster (all users and connections), use

SET GLOBAL

. This is useful if many reports are hitting the default 300-second (5-minute) limit.

sql
-- This affects all new sessions created after this command
SET GLOBAL query_timeout = 3600;

3. Using SQL Hints (Power BI "SQL Statement" mode) If you are using the "Import" mode or a custom "SQL statement" in Power BI to fetch data, you can wrap your query with a

SET_VAR

hint. This applies the timeout only to that specific query.

sql
SELECT /*+ SET_VAR(query_timeout = 3600) */ 
    column1, 
    column2 
FROM your_table;

4. Power BI Connection String (ODBC/MySQL) If Power BI is connecting via the MySQL ODBC driver, you can sometimes specify session variables in the Initial SQL or Connection String settings within Power BI, though the methods above are generally more reliable for StarRocks. Summary of variables to consider: *

query_timeout

: The primary variable. It sets the maximum execution time for a query (default is

300

seconds). *

wait_timeout

: If your Power BI connection is being dropped while idle, increase this (default is

28800

seconds / 8 hours). *

new_planner_optimize_timeout

: If you have extremely complex queries with many joins that fail during the "Planning" phase, you may also need to increase this (default

3000

ms). Note: If your StarRocks version is older than v3.3.3 and you cannot use

SET GLOBAL

, you must ensure the user or the application executes

SET query_timeout = 3600;

immediately after opening the connection. References • faq/operation_maintenance_faq.md • sql-reference/System_variable.md • administration/management/resource_management/query_queues.md • administration/management/resource_management/Query_management.md

@Rocky how to view this

query_timeout variable each time i connect

To view the current value of the

query_timeout

variable for your session (every time you connect), you can use any of the following standard SQL commands in your SQL client or Power BI "SQL statement" box: 1. Using

SHOW VARIABLES

(Standard) This is the most common way to check the current setting. It will return the value currently applied to your active connection.

sql
SHOW VARIABLES LIKE 'query_timeout';

2. Using

SELECT @@

(Quickest) This returns the value as a single-row result, which is often easier to read in reporting tools like Power BI.

sql
SELECT @@query_timeout;

3. Check both Global and Session values If you want to see if your specific session differs from the cluster-wide default:

sql
SHOW SESSION VARIABLES LIKE 'query_timeout'; -- Your current connection's value
SHOW GLOBAL VARIABLES LIKE 'query_timeout';  -- The default for the whole cluster

How to use this in Power BI: If you are troubleshooting a timeout in Power BI, you can temporarily change your Data Source settings to use a "SQL statement" and enter:

SELECT @@query_timeout as current_timeout

This will confirm exactly what limit StarRocks is enforcing for that Power BI user. * Default:

300

(5 minutes) * Unit: Seconds If the value you see is still

300

despite your changes, ensure you either used

SET GLOBAL

(for all new connections) or

ALTER USER ... SET PROPERTIES

(specifically for that Power BI login). Note: requires StarRocks v3.3.3+). References • sql-reference/System_variable.md • faq/operation_maintenance_faq.md • administration/management/resource_management/Query_management.md

@Rocky why fe crash when log file getting full storage?

The StarRocks Frontend (FE) crashes when log files fill up the storage primarily because of how it handles metadata persistence and runtime stability. Here are the specific reasons why this happens: 1. BDBJE Write Failures (The Main Reason) StarRocks FE uses Berkeley DB Java Edition (BDBJE) to store its metadata (the "edit logs"). * Journaling: Every metadata change (like creating a table or adding a user) must be written to a journal file in the

meta_dir

. * Crash on Write Failure: If the disk is full, BDBJE cannot append new entries to its logs. When a write fails due to "No space left on device," BDBJE enters an "invalid" state to prevent metadata corruption and throws an

EnvironmentFailureException

. This forces the FE process to shut down immediately. * 5GB Safety Threshold: In StarRocks v3.0+, there is a built-in safety check. If the disk space available to the

meta_dir

drops below 5 GB, the FE may refuse to start or will stop to prevent the database from becoming unrecoverable. 2.

fe.out

is not Rotated While standard logs like

fe.log

and

fe.warn.log

are automatically rotated and deleted based on your configuration,

fe.out

is not. _ This file captures standard output (stdout) and error (stderr). If the FE is throwing many exceptions (perhaps _because* the disk is getting full),

fe.out

can grow rapidly. * Because

fe.out

doesn't rotate, it can eventually consume 100% of the disk, triggering the BDBJE failure mentioned above. 3. JVM and OS Stability * Logging Deadlocks: If the disk is completely full, the logging framework (Log4j) may hang while trying to write a log line. If this happens within a critical code path (like a heart-beat thread or a lock), the entire FE can become unresponsive and be killed by the OS or a watchdog. * Temporary Files: The FE needs small amounts of disk space for temporary files and JVM operations. If space is zero, even basic Java operations can fail. ────────── How to prevent this: 1. Separate Metadata and Logs: Ensure your

meta_dir

(metadata) and

sys_log_dir

(logs) are on different disk partitions. This prevents a runaway log file from crashing your metadata engine. 2. Enable Disk-Based Cleaning: In your

fe.conf

, enable the automatic log cleaner:

properties
    log_cleaner_disk_util_based_enable = true
    log_cleaner_disk_util_threshold = 80

This will trigger an aggressive cleanup once disk usage hits 80%. 3. Manage `fe.out`: Periodically truncate the

fe.out

file or use a tool like

logrotate

at the OS level. You can clear it manually without restarting:

bash
    cat /dev/null > fe/log/fe.out

4. Monitor Space: Set up alerts for disk usage on FE nodes, specifically for the directory defined by

sys_log_dir

and

meta_dir

. References • administration/management/logs.md • administration/management/FE_configuration.md • administration/Meta_recovery.md References • administration/management/FE_configuration.md • administration/management/logs.md

@Rocky ALTER DATABASE uw SET DATA QUOTA 100GB; this alter quote, how to view quota of existing db

To view the data quota for an existing database in StarRocks, you can use the following methods: 1. Using

SHOW DATA

(Current Database) This is the most common way to see the quota, used space, and remaining space for the database you are currently using.

sql
-- 1. Switch to the database
USE uw;

-- 2. View data and quota info
SHOW DATA;

Output breakdown: * Total: The sum of all data in the database (including all replicas). * Quota: The limit you set (e.g.,

100.000 GB

). * Left: The remaining space available before hitting the quota. 2. Using

SHOW PROC

(Cluster-wide) If you want to see the quotas for all databases at once without switching between them, you can use the internal process view:

sql
SHOW PROC "/dbs";

The output will include a

Quota

column showing the storage limit for every database in the cluster. 3. Using

information_schema

(Metadata) You can also query the

information_schema.schemata

table, though

SHOW DATA

is generally more user-friendly for human-readable sizes.

sql
SELECT SCHEMA_NAME, CATALOG_NAME 
FROM information_schema.schemata 
WHERE SCHEMA_NAME = 'uw';

(Note: Not all versions of StarRocks expose the specific quota value in this standard schema table;

SHOW DATA

or

SHOW PROC

are the authoritative sources). Important Note on Quota: * Default Quota: By default, the quota is set to a very large value (effectively unlimited). * Update Interval: StarRocks updates the "used" data statistics periodically (default every 5 minutes). If you just loaded a lot of data,

SHOW DATA

might not reflect the new "Left" space immediately. This is controlled by the FE configuration

db_used_data_quota_update_interval_secs

. References • sql-reference/sql-statements/Database/SHOW_DATA.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/management/resource_management/Query_management.md • faq/Sql_faq.md References • sql-reference/sql-statements/Database/SHOW_CREATE_DATABASE.md • sql-reference/sql-statements/Database/SHOW_DATA.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_CREATE_TABLE.md • sql-reference/sql-statements/View/SHOW_CREATE_VIEW.md