<@U0A71G31CDV> CASE
WHEN LENGTH(TRIM(ID_CARD_...
# questions-and-troubleshootingl
Lý Thành
05/29/2026, 2:44 AM@Rocky CASE
WHEN LENGTH(TRIM(ID_CARD_NUMBER_OTHER)) IN (9, 12)
AND TRIM(ID_CARD_NUMBER_OTHER) REGEXP '^[0-9]+$' -- ← replaces TRY_CAST
AND TRIM(ID_CARD_NUMBER_OTHER) NOT IN ('123456789', '987654321')
AND TRIM(ID_CARD_NUMBER_OTHER) NOT REGEXP '^([0-9])\1+$'
THEN TRIM(ID_CARD_NUMBER_OTHER)
ELSE NULL
END AS ID_CARD_NUMBER_OTHER,
SQL Error [1064] [42000]: Invalid re2 expression: ^([0-9])\1+$ backend [id=3731846] [
Invalid re2 expression: ^([0-9])\1+$ backend [id=3731846] [
r
Rocky
05/29/2026, 2:44 AMThe error occurs because StarRocks uses the RE2 regular expression engine, which does not support backreferences (like
\1NOT REGEXP '^([0-9])\1+$'REPEATLEFT Copy code
sql
CASE
WHEN LENGTH(TRIM(ID_CARD_NUMBER_OTHER)) IN (9, 12)
AND TRIM(ID_CARD_NUMBER_OTHER) REGEXP '^[0-9]+$'
AND TRIM(ID_CARD_NUMBER_OTHER) NOT IN ('123456789', '987654321')
-- Check that not all digits are identical (e.g., '111111111')
AND TRIM(ID_CARD_NUMBER_OTHER) != REPEAT(LEFT(TRIM(ID_CARD_NUMBER_OTHER), 1), LENGTH(TRIM(ID_CARD_NUMBER_OTHER)))
THEN TRIM(ID_CARD_NUMBER_OTHER)
ELSE NULL
END AS ID_CARD_NUMBER_OTHER Copy code
sql
AND LENGTH(REPLACE(TRIM(ID_CARD_NUMBER_OTHER), LEFT(TRIM(ID_CARD_NUMBER_OTHER), 1), '')) > 0REGEXPRLIKEl
Lý Thành
05/29/2026, 3:10 AM@Rocky rewrite this sql to run in starrocks
WITH
-- 1 LOS_CIF has more than 1 ID_CARD_NUMBER → take the latest one
source_dup_id_card AS (
SELECT LOS_CIF_NO
FROM source_data_add_master_id_20260430_loan_card
GROUP BY LOS_CIF_NO
HAVING COUNT(DISTINCT ID_CARD_NUMBER) > 1
),
add_MASTER_ID_CARD_dup_id_card AS (
SELECT ID_CARD_NUMBER AS MASTER_ID_CARD, CONTRACT_NO
FROM (
SELECT
ID_CARD_NUMBER,
CONTRACT_NO,
ROW_NUMBER() OVER (PARTITION BY a.LOS_CIF_NO ORDER BY status_created_date DESC) AS RN
FROM source_data_add_master_id_20260430_loan_card a
INNER JOIN source_dup_id_card b ON a.LOS_CIF_NO = b.LOS_CIF_NO
) t
WHERE RN = 1
),
-- 1 ID_CARD_NUMBER has more than 1 LOS_CIF → take the latest one
source_dup_los_cif AS (
SELECT ID_CARD_NUMBER
FROM source_data_add_master_id_20260430_loan_card
GROUP BY ID_CARD_NUMBER
HAVING COUNT(DISTINCT LOS_CIF_NO) > 1
),
add_MASTER_ID_CARD_dup_los_cif AS (
SELECT ID_CARD_NUMBER AS MASTER_ID_CARD, CONTRACT_NO
FROM (
SELECT
a.ID_CARD_NUMBER,
CONTRACT_NO,
ROW_NUMBER() OVER (PARTITION BY a.LOS_CIF_NO ORDER BY status_created_date DESC) AS RN
FROM source_data_add_master_id_20260430_loan_card a
INNER JOIN source_dup_los_cif b ON a.ID_CARD_NUMBER = b.ID_CARD_NUMBER
) t
WHERE RN = 1
),
-- Cross-match via ID_CARD_NUMBER_OTHER to find the master card
add_master_id_card AS (
SELECT
t1.LOS_CIF_NO,
t1.CONTRACT_NO,
t1.ID_CARD_NUMBER,
t1.ID_CARD_NUMBER_OTHER,
t1.STATUS_CREATED_DATE,
t1.CUSTOMER_NAME,
t2.LOS_CIF_NO AS LOS_CIF_NO_s2,
t2.CONTRACT_NO AS CONTRACT_NO_s2,
t2.ID_CARD_NUMBER AS ID_CARD_NUMBER_s2,
t2.ID_CARD_NUMBER_OTHER AS ID_CARD_NUMBER_OTHER_s2,
t2.STATUS_CREATED_DATE AS STATUS_CREATED_DATE_s2,
CASE
WHEN t1.STATUS_CREATED_DATE > t2.STATUS_CREATED_DATE
THEN t1.ID_CARD_NUMBER
ELSE t2.ID_CARD_NUMBER
END AS MASTER_ID_CARD
FROM source_data_add_master_id_20260430_loan_card t1
INNER JOIN source_data_add_master_id_20260430_loan_card t2
ON t1.ID_CARD_NUMBER_OTHER = t2.ID_CARD_NUMBER
OR t2.ID_CARD_NUMBER_OTHER = t1.ID_CARD_NUMBER
),
-- Deduplicate round 1: keep longest MASTER_ID_CARD per LOS_CIF
add_master_id_card_remove_dup1 AS (
SELECT *
FROM (
SELECT
a.*,
ROW_NUMBER() OVER (PARTITION BY a.LOS_CIF_NO ORDER BY LENGTH(MASTER_ID_CARD) DESC) AS RN
FROM add_master_id_card a
) t
WHERE RN = 1
),
-- Second pass: re-cross-match on the deduplicated set
add_master_id_card_2 AS (
SELECT
t1.LOS_CIF_NO,
t1.CONTRACT_NO,
t1.ID_CARD_NUMBER,
t1.ID_CARD_NUMBER_OTHER,
t1.STATUS_CREATED_DATE,
t1.CUSTOMER_NAME,
t2.LOS_CIF_NO AS LOS_CIF_NO_s2,
t2.CONTRACT_NO AS CONTRACT_NO_s2,
t2.ID_CARD_NUMBER AS ID_CARD_NUMBER_s2,
t2.ID_CARD_NUMBER_OTHER AS ID_CARD_NUMBER_OTHER_s2,
t2.STATUS_CREATED_DATE AS STATUS_CREATED_DATE_s2,
CASE
WHEN LENGTH(t1.MASTER_ID_CARD) > LENGTH(t2.MASTER_ID_CARD)
THEN t1.MASTER_ID_CARD
ELSE t2.MASTER_ID_CARD
END AS MASTER_ID_CARD
FROM add_master_id_card_remove_dup1 t1
INNER JOIN add_master_id_card_remove_dup1 t2
ON t1.ID_CARD_NUMBER_OTHER = t2.ID_CARD_NUMBER
OR t2.ID_CARD_NUMBER_OTHER = t1.ID_CARD_NUMBER
),
-- Deduplicate round 2
add_master_id_card_remove_dup2 AS (
SELECT *
FROM (
SELECT
a.*,
ROW_NUMBER() OVER (PARTITION BY a.LOS_CIF_NO ORDER BY LENGTH(MASTER_ID_CARD) DESC) AS RN
FROM add_master_id_card_2 a
) t
WHERE RN = 1
),
-- Level-1 mapping: attach MASTER_ID_CARD from cross-match result
map_data_lv1 AS (
SELECT
t1.*,
IFNULL(t2.MASTER_ID_CARD, t1.ID_CARD_NUMBER) AS MASTER_ID_CARD
FROM source_data_add_master_id_20260430_loan_card t1
LEFT JOIN (
SELECT DISTINCT CONTRACT_NO, MASTER_ID_CARD
FROM (
SELECT DISTINCT CONTRACT_NO, MASTER_ID_CARD, STATUS_CREATED_DATE
FROM add_master_id_card_remove_dup2
) x
) t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
),
-- Level-2 mapping: override with dup-id-card resolution
map_data_lv2 AS (
SELECT
t1.LOS_CIF_NO,
t1.CONTRACT_NO,
t1.APP_CODE,
t1.CONTRACT_STATUS,
t1.ID_CARD_NUMBER,
t1.ID_CARD_NUMBER_OTHER,
t1.status_created_date,
t1.CUSTOMER_NAME,
t1.type,
IFNULL(t2.MASTER_ID_CARD, t1.MASTER_ID_CARD) AS MASTER_ID_CARD
FROM map_data_lv1 t1
LEFT JOIN add_MASTER_ID_CARD_dup_id_card t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
),
-- Level-3 mapping: override with dup-los-cif resolution
map_data_lv3 AS (
SELECT
t1.LOS_CIF_NO,
t1.CONTRACT_NO,
t1.APP_CODE,
t1.CONTRACT_STATUS,
t1.ID_CARD_NUMBER,
t1.ID_CARD_NUMBER_OTHER,
t1.status_created_date,
t1.CUSTOMER_NAME,
t1.type,
IFNULL(t2.MASTER_ID_CARD, t1.MASTER_ID_CARD) AS MASTER_ID_CARD
FROM map_data_lv2 t1
LEFT JOIN add_MASTER_ID_CARD_dup_los_cif t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
)
SELECT * FROM map_data_lv3;
r
Rocky
05/29/2026, 3:11 AMThe SQL you provided is mostly standard-compliant, but to ensure it runs correctly in StarRocks, I have fixed the HTML entities (like
>>>ORJOINUNIONROW_NUMBER()Rocky
05/29/2026, 3:11 AMINNER JOIN add_master_id_card_remove_dup1 t2
ON t1.ID_CARD_NUMBER_OTHER = t2.ID_CARD_NUMBER
OR t2.ID_CARD_NUMBER_OTHER = t1.ID_CARD_NUMBER
),
-- Deduplicate round 2
add_master_id_card_remove_dup2 AS (
SELECT *
FROM (
SELECT
a.*,
ROW_NUMBER() OVER (PARTITION BY a.LOS_CIF_NO ORDER BY LENGTH(MASTER_ID_CARD) DESC) AS RN
FROM add_master_id_card_2 a
) t
WHERE RN = 1
),
-- Level-1 mapping
map_data_lv1 AS (
SELECT
t1.*,
IFNULL(t2.MASTER_ID_CARD, t1.ID_CARD_NUMBER) AS MASTER_ID_CARD
FROM source_data_add_master_id_20260430_loan_card t1
LEFT JOIN (
SELECT DISTINCT CONTRACT_NO, MASTER_ID_CARD
FROM (
SELECT DISTINCT CONTRACT_NO, MASTER_ID_CARD, STATUS_CREATED_DATE
FROM add_master_id_card_remove_dup2
) x
) t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
),
-- Level-2 mapping
map_data_lv2 AS (
SELECT
t1.LOS_CIF_NO,
t1.CONTRACT_NO,
t1.APP_CODE,
t1.CONTRACT_STATUS,
t1.ID_CARD_NUMBER,
t1.ID_CARD_NUMBER_OTHER,
t1.status_created_date,
t1.CUSTOMER_NAME,
t1.type,
IFNULL(t2.MASTER_ID_CARD, t1.MASTER_ID_CARD) AS MASTER_ID_CARD
FROM map_data_lv1 t1
LEFT JOIN add_MASTER_ID_CARD_dup_id_card t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
),
-- Level-3 mapping
map_data_lv3 AS (
SELECT
t1.LOS_CIF_NO,
t1.CONTRACT_NO,
t1.APP_CODE,
t1.CONTRACT_STATUS,
t1.ID_CARD_NUMBER,
t1.ID_CARD_NUMBER_OTHER,
t1.status_created_date,
t1.CUSTOMER_NAME,
t1.type,
IFNULL(t2.MASTER_ID_CARD, t1.MASTER_ID_CARD) AS MASTER_ID_CARD
FROM map_data_lv2 t1
LEFT JOIN add_MASTER_ID_CARD_dup_los_cif t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
)
SELECT * FROM map_data_lv3;
```
Note on Performance:
The query contains several self-joins with
ORadd_master_id_cardORINNER JOINUNION ALLl
Lý Thành
05/29/2026, 3:13 AM@Rocky help me breaking the
ORINNER JOINUNION ALLr
Rocky
05/29/2026, 3:13 AMTo optimize the query for StarRocks, we can split the
ORINNER JOINUNION ALLROW_NUMBER() ... WHERE RN = 1UNION ALLORRocky
05/29/2026, 3:13 AMCASE WHEN LENGTH(t1.MASTER_ID_CARD) > LENGTH(t2.MASTER_ID_CARD) THEN t1.MASTER_ID_CARD ELSE t2.MASTER_ID_CARD END AS MASTER_ID_CARD
FROM add_master_id_card_remove_dup1 t1
INNER JOIN add_master_id_card_remove_dup1 t2 ON t1.ID_CARD_NUMBER_OTHER = t2.ID_CARD_NUMBER
UNION ALL
SELECT
t1.LOS_CIF_NO, t1.CONTRACT_NO, t1.ID_CARD_NUMBER, t1.ID_CARD_NUMBER_OTHER, t1.STATUS_CREATED_DATE, t1.CUSTOMER_NAME,
t2.LOS_CIF_NO AS LOS_CIF_NO_s2, t2.CONTRACT_NO AS CONTRACT_NO_s2, t2.ID_CARD_NUMBER AS ID_CARD_NUMBER_s2,
t2.ID_CARD_NUMBER_OTHER AS ID_CARD_NUMBER_OTHER_s2, t2.STATUS_CREATED_DATE AS STATUS_CREATED_DATE_s2,
CASE WHEN LENGTH(t1.MASTER_ID_CARD) > LENGTH(t2.MASTER_ID_CARD) THEN t1.MASTER_ID_CARD ELSE t2.MASTER_ID_CARD END AS MASTER_ID_CARD
FROM add_master_id_card_remove_dup1 t1
INNER JOIN add_master_id_card_remove_dup1 t2 ON t2.ID_CARD_NUMBER_OTHER = t1.ID_CARD_NUMBER
),
-- Deduplicate round 2
add_master_id_card_remove_dup2 AS (
SELECT * FROM (
SELECT a.*, ROW_NUMBER() OVER (PARTITION BY a.LOS_CIF_NO ORDER BY LENGTH(MASTER_ID_CARD) DESC) AS RN
FROM add_master_id_card_2 a
) t WHERE RN = 1
),
-- Level-1 mapping
map_data_lv1 AS (
SELECT t1.*, IFNULL(t2.MASTER_ID_CARD, t1.ID_CARD_NUMBER) AS MASTER_ID_CARD
FROM source_data_add_master_id_20260430_loan_card t1
LEFT JOIN (
SELECT DISTINCT CONTRACT_NO, MASTER_ID_CARD
FROM add_master_id_card_remove_dup2
) t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
),
-- Level-2 mapping
map_data_lv2 AS (
SELECT t1.*, IFNULL(t2.MASTER_ID_CARD, t1.MASTER_ID_CARD) AS MASTER_ID_CARD
FROM map_data_lv1 t1
LEFT JOIN add_MASTER_ID_CARD_dup_id_card t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
),
-- Level-3 mapping
map_data_lv3 AS (
SELECT t1.*, IFNULL(t2.MASTER_ID_CARD, t1.MASTER_ID_CARD) AS MASTER_ID_CARD
FROM map_data_lv2 t1
LEFT JOIN add_MASTER_ID_CARD_dup_los_cif t2 ON t1.CONTRACT_NO = t2.CONTRACT_NO
)
SELECT * FROM map_data_lv3;
```
Why this is better:
* Hash Join Support: StarRocks is highly optimized for equijoins (
A.col = B.colUNION ALLUNION ALLORORl
Lý Thành
05/29/2026, 7:11 AM@Rocky rewrite this into starrocks
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
Rocky
05/29/2026, 7:11 AMTo rewrite this SQL for StarRocks, you can use the
TRANSLATEREGEXP_REPLACE||ORpipes_as_concatCONCAT() Copy code
sql
SELECT a.*,
UPPER(
REGEXP_REPLACE(
TRANSLATE(
UPPER(CUSTOMER_NAME_REMOVE_COMBINING),
-- From string (all accented characters)
CONCAT(
'ÀÁẠẢÃÂẦẤẬẨẪĂẰẮẶẲẴĀĄÃÆÄÅ',
'ÈÉẸẺẼÊỀẾỆỂỄĔĖĘĚ',
'ÌÍỊỈĨĬĮİ',
'ÒÓỌỎÕÔỒỐỘỔỖƠỜỚỢỞỠŎŐØ',
'ÙÚỤỦŨƯỪỨỰỬỮŬŮŰŲǓ',
'ỲÝỴỶỸ',
'ĐĎ',
'ĆČÇ',
'ÑŃŇŅŊ',
'Ř',
'ŚŠŞ',
'ŹŽŻ'
),
-- To string (non-accented equivalents)
CONCAT(
'AAAAAAAAAAAAAAAAAAAAAAA',
'EEEEEEEEEEEEEEE',
'IIIIIIII',
'OOOOOOOOOOOOOOOOOOOO',
'UUUUUUUUUUUUUUUU',
'YYYYY',
'DD',
'CCC',
'NNNNN',
'R',
'SSS',
'ZZZ'
)
),
'[[:space:]]+', -- Matches one or more whitespace characters
' ' -- Replaces with a single space
)
) AS CUSTOMER_NAME_NO_ACCENT
FROM convert_customer_name_STEP_2 a;TRANSLATEREPLACE()[[:space:]]+||SET pipes_as_concat = true;CONCAT()from_stringto_stringRocky
05/29/2026, 7:11 AM• sql-reference/sql-functions/string-functions/replace.md
• sql-reference/sql-statements/TRANSLATE_TRINO.md
• sql-reference/sql-functions/like-predicate-functions/regexp_replace.md
• sql-reference/sql-functions/dict-functions/dict_mapping.md
• sql-reference/sql-functions/string-functions/url_encode.md
• sql-reference/sql-functions/date-time-functions/date_format.md
l
Lý Thành
05/29/2026, 7:50 AM@Rocky rewrite this sql into starrock and optimized for better performance
with
/*xử lý các case bị mapping thiếu, k xử lý manual nữa */
id_map AS
(
SELECT
LOS_CIF_NO,
ID_CARD_NUMBER AS ID_VALUE
FROM MASTER_ID_CARD_ALL_20260430_loan_card_final_v2
WHERE ID_CARD_NUMBER IS NOT NULL
UNION
SELECT
LOS_CIF_NO,
ID_CARD_NUMBER_OTHER AS ID_VALUE
FROM MASTER_ID_CARD_ALL_20260430_loan_card_final_v2
WHERE ID_CARD_NUMBER_OTHER IS NOT NULL
UNION
SELECT
LOS_CIF_NO,
MASTER_ID_CARD AS ID_VALUE
FROM MASTER_ID_CARD_ALL_20260430_loan_card_final_v2
WHERE MASTER_ID_CARD IS NOT NULL
),
/*
Bước 2: tạo quan hệ giữa các LOS_CIF_NO có cùng thông tin nhận dạng
Mục đích:
- Nếu nhiều LOS_CIF_NO dùng chung một ID_VALUE
- Thì các LOS_CIF_NO này được xem là có liên quan cùng khách hàng
*/
edges AS
(
SELECT DISTINCT
a.LOS_CIF_NO AS FROM_CIF,
b.LOS_CIF_NO AS TO_CIF
FROM id_map a
JOIN id_map b
ON a.ID_VALUE = b.ID_VALUE
WHERE a.LOS_CIF_NO <> b.LOS_CIF_NO
),
/*
Bước 3: Recursive gom cụm LOS_CIF liên thông
Mục đích:
- Từ các LOS_CIF_1 -> LOS_CIF_2 ở bước 2
- Gom toàn bộ LOS_CIF có liên quan trực tiếp hoặc gián tiếp
- Ví dụ: CIF_A nối CIF_B, CIF_B nối CIF_C
=> CIF_A, CIF_B, CIF_C cùng một cụm khách hàng
*/
walk (ROOT_CIF, LOS_CIF_NO) AS
(
SELECT DISTINCT
LOS_CIF_NO AS ROOT_CIF,
LOS_CIF_NO
FROM id_map
UNION ALL
SELECT
w.ROOT_CIF,
e.TO_CIF
FROM walk w
JOIN edges e
ON w.LOS_CIF_NO = e.FROM_CIF
)
CYCLE LOS_CIF_NO SET IS_CYCLE TO 1 DEFAULT 0,
/*
Bước 4: Tạo group đại diện cho từng LOS_CIF_NO
Mục đích:
- Mỗi LOS_CIF_NO có thể đi tới nhiều ROOT_CIF trong cụm liên thông
- Lấy MIN(ROOT_CIF) làm LOS_CIF_NO_V2 để đại diện cho cả cụm
*/
component_map AS (
SELECT
LOS_CIF_NO,
MIN(ROOT_CIF) AS LOS_CIF_NO_V2
FROM (
SELECT DISTINCT ROOT_CIF, LOS_CIF_NO
FROM walk
)
GROUP BY LOS_CIF_NO
),
/*
Bước 5: Đếm MASTER_ID_CARD trong từng group LOS_CIF
Mục đích:
- Sau khi mỗi LOS_CIF_NO đã được gán GROUP_LOS_CIF
- Kiểm tra trong mỗi group đang có bao nhiêu MASTER_ID_CARD khác nhau
- Nếu CNT_MASTER > 1 thì group này còn nhiều master, cần xử lý tiếp
*/
master_rank AS
(
SELECT
cm.LOS_CIF_NO_V2,
b.MASTER_ID_CARD,
COUNT(*) AS CNT_MASTER,
MAX(b.STATUS_CREATED_DATE) AS MAX_STATUS_DATE,
ROW_NUMBER() OVER (
PARTITION BY cm.LOS_CIF_NO_V2
ORDER BY COUNT(*) DESC,
MAX(b.STATUS_CREATED_DATE) DESC,
b.MASTER_ID_CARD
) AS RN
FROM MASTER_ID_CARD_ALL_20260430_loan_card_final_v2 b
JOIN component_map cm
ON b.LOS_CIF_NO = cm.LOS_CIF_NO
WHERE b.MASTER_ID_CARD IS NOT NULL
GROUP BY
cm.LOS_CIF_NO_V2,
b.MASTER_ID_CARD
),
/*
Bước 6: Chọn MASTER_ID_CARD_V2 cho từng group LOS_CIF
Mục đích:
- Với các group có nhiều MASTER_ID_CARD
- Chọn ra 1 MASTER_ID_CARD_V2 đại diện cuối cùng cho cả group
- Rule chọn:
+ Ưu tiên ID 12 số hơn 9 số
+ Nếu cùng độ dài thì lấy MASTER_ID_CARD lớn hơn để kết quả ổn định
*/
group_master AS
(
SELECT
LOS_CIF_NO_V2,
MASTER_ID_CARD AS MASTER_ID_CARD_V2
FROM master_rank
WHERE RN = 1
)
,data_LOS_CIF_NO_v2 as
(
SELECT
b.*,
nvl(cm.LOS_CIF_NO_V2,b.LOS_CIF_NO) as LOS_CIF_NO_V2,
nvl(gm.MASTER_ID_CARD_V2,b.MASTER_ID_CARD) as MASTER_ID_CARD_V2
FROM MASTER_ID_CARD_ALL_20260430_loan_card_final_v2 b
left join component_map cm
ON b.LOS_CIF_NO = cm.LOS_CIF_NO
left join group_master gm
ON cm.LOS_CIF_NO_V2 = gm.LOS_CIF_NO_V2
)
--add master_id_card_v3, nó sẽ được lấy từ master_id_card_v2 + name -> master_id_card_v3
,
--replace space đầu và cuối
convert_customer_name_STEP_1 as
(
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
),
--remove dấu unicode
convert_customer_name_STEP_2 as
(
select a.*,
REGEXP_REPLACE(
CUSTOMER_NAME_TRIM,
'[' || UNISTR('\0300') || '-' || UNISTR('\036F') || ']',
''
) AS CUSTOMER_NAME_REMOVE_COMBINING
from convert_customer_name_STEP_1 a
),
customer_name_NORMALIZED as
(
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
),
DATA_CHECK as
(
select a.*,
TRIM(
REGEXP_REPLACE(
REGEXP_REPLACE(CUSTOMER_NAME_NO_ACCENT, '[^A-Z ]', ' '),
'[[space]]+',
' '
)
) AS CUSTOMER_NAME_CONVERT,
REPLACE(
TRIM(
REGEXP_REPLACE(
REGEXP_REPLACE(CUSTOMER_NAME_NO_ACCENT, '[^A-Z ]', ' '),
'[[space]]+',
' '
)
),
' ',
''
) AS CUSTOMER_NAME_CONVERT_REMOVE_SPACE,
CASE
WHEN CUSTOMER_NAME IS NULL THEN 'NULL_NAME'
WHEN TRIM(CUSTOMER_NAME) IS NULL THEN 'BLANK_NAME'
WHEN REGEXP_LIKE(CUSTOMER_NAME_NO_ACCENT, '[^A-Z ]') THEN 'CHECK_SPECIAL_CHAR'
ELSE 'OK'
END AS TEXT_CHECK_STATUS,
REGEXP_REPLACE(CUSTOMER_NAME_NO_ACCENT, '[A-Z ]', '') AS SPECIAL_CHAR_FOUND,
ASCIISTR(CUSTOMER_NAME) AS CUSTOMER_NAME_ASCIISTR
from customer_name_NORMALIZED a
),
CROSS_CHECK_CUSTOMER_NAME AS
(
SELECT
MASTER_ID_CARD_V2,
COUNT(DISTINCT CONTRACT_NO) AS CNT_CONTRACT,
COUNT(DISTINCT LOS_CIF_NO) AS CNT_LOS_CIF,
COUNT(DISTINCT CUSTOMER_NAME_CONVERT_REMOVE_SPACE) AS CNT_NAME
FROM DATA_CHECK
GROUP BY MASTER_ID_CARD_V2
--HAVING COUNT(*) > 1
),
data_add_CROSS_CHECK_CUSTOMER_NAME AS
(
select a.*,b.CNT_CONTRACT,b.CNT_LOS_CIF,b.CNT_NAME
from DATA_CHECK a
left join CROSS_CHECK_CUSTOMER_NAME b on a.MASTER_ID_CARD_V2 = b.MASTER_ID_CARD_V2
),
data_MASTER_ID_CARD_V2_va_CUSTOMER_NAME_CONVERT_REMOVE_SPACE as
(
select a.*,
ROW_NUMBER() OVER(PARTITION BY MASTER_ID_CARD_V2 ORDER BY CUSTOMER_NAME_CONVERT_REMOVE_SPACE ASC) RN
from
(
select distinct CUSTOMER_NAME_CONVERT_REMOVE_SPACE,MASTER_ID_CARD_V2
from data_add_CROSS_CHECK_CUSTOMER_NAME
) a
)
select a.*,
case when nvl(a.CNT_NAME, 1) = 1 then a.MASTER_ID_CARD_V2 else a.MASTER_ID_CARD_V2 || '_' || b.rn end MASTER_ID_CARD_V3
from data_add_CROSS_CHECK_CUSTOMER_NAME a
left join data_MASTER_ID_CARD_V2_va_CUSTOMER_NAME_CONVERT_REMOVE_SPACE b on
a.CUSTOMER_NAME_CONVERT_REMOVE_SPACE = b.CUSTOMER_NAME_CONVERT_REMOVE_SPACE
and a.MASTER_ID_CARD_V2 = b.MASTER_ID_CARD_V2
r
Rocky
05/29/2026, 7:50 AMThis is a complex query involving Identity Resolution (connected components in a graph) and heavy string normalization.
Critical Compatibility Note
StarRocks does not support Recursive CTEs (
WITH RECURSIVEwalkCYCLERocky
05/29/2026, 7:50 AM'ÀÁẠẢÃÂẦẤẬẨẪĂẰẮẶẲẴĀĄÃÆÄÅÈÉẸẺẼÊỀẾỆỂỄĔĖĘĚÌÍỊỈĨĬĮİÒÓỌỎÕÔỒỐỘỔỖƠỜỚỢỞỠŎŐØÙÚỤỦŨƯỪỨỰỬỮŬŮŰŲǓỲÝỴỶỸĐĎĆČÇÑŃŇŅŊŘŚŠŞŹŽŻ',
'AAAAAAAAAAAAAAAAAAAAAAAEEEEEEEEEEEEEEEIIIIIIIIOOOOOOOOOOOOOOOOOOOOUUUUUUUUUUUUUUUUYYYYYDDCCCNNNNNRSSSZZZ'
),
'[[space]]+', ' '
)
) AS CUSTOMER_NAME_NO_ACCENT
FROM convert_customer_name_STEP_1
),
DATA_CHECK AS (
SELECT *,
TRIM(REGEXP_REPLACE(REGEXP_REPLACE(CUSTOMER_NAME_NO_ACCENT, '[^A-Z ]', ' '), '[[space]]+', ' ')) AS CUSTOMER_NAME_CONVERT,
REPLACE(TRIM(REGEXP_REPLACE(REGEXP_REPLACE(CUSTOMER_NAME_NO_ACCENT, '[^A-Z ]', ' '), '[[space]]+', ' ')), ' ', '') AS CUSTOMER_NAME_CONVERT_REMOVE_SPACE
FROM customer_name_NORMALIZED
),
-- Step 8: Cross-check and Final ID Generation
CROSS_CHECK_CUSTOMER_NAME AS (
SELECT
MASTER_ID_CARD_V2,
COUNT(DISTINCT CONTRACT_NO) AS CNT_CONTRACT,
COUNT(DISTINCT LOS_CIF_NO) AS CNT_LOS_CIF,
COUNT(DISTINCT CUSTOMER_NAME_CONVERT_REMOVE_SPACE) AS CNT_NAME
FROM DATA_CHECK
GROUP BY MASTER_ID_CARD_V2
),
data_add_CROSS_CHECK_CUSTOMER_NAME AS (
SELECT a.*, b.CNT_CONTRACT, b.CNT_LOS_CIF, b.CNT_NAME
FROM DATA_CHECK a
LEFT JOIN CROSS_CHECK_CUSTOMER_NAME b ON a.MASTER_ID_CARD_V2 = b.MASTER_ID_CARD_V2
),
data_name_rank AS (
SELECT DISTINCT CUSTOMER_NAME_CONVERT_REMOVE_SPACE, MASTER_ID_CARD_V2,
ROW_NUMBER() OVER(PARTITION BY MASTER_ID_CARD_V2 ORDER BY CUSTOMER_NAME_CONVERT_REMOVE_SPACE ASC) as RN
FROM data_add_CROSS_CHECK_CUSTOMER_NAME
)
SELECT
a.*,
CASE
WHEN IFNULL(a.CNT_NAME, 1) = 1 THEN a.MASTER_ID_CARD_V2
ELSE CONCAT(a.MASTER_ID_CARD_V2, '_', CAST(b.RN AS STRING))
END AS MASTER_ID_CARD_V3
FROM data_add_CROSS_CHECK_CUSTOMER_NAME a
LEFT JOIN data_name_rank b
ON a.CUSTOMER_NAME_CONVERT_REMOVE_SPACE = b.CUSTOMER_NAME_CONVERT_REMOVE_SPACE
AND a.MASTER_ID_CARD_V2 = b.MASTER_ID_CARD_V2;
```
Key Optimizations & Changes:
1. Graph Traversal (The "Walk"):
* Replaced Recursive CTE with
hop1hop2hop3edgesTRANSLATECHR(9)CHR(10)\t\n\rREPLACECONCATCONCAT()||pipes_as_concatUNION ALLUNIONall_connectionsUNION ALLGROUP BYcomponent_mapIFNULLNVLIFNULLNVLDISTINCTdata_name_rankhopl
Lý Thành
05/29/2026, 8:33 AM@Rocky recreate this oracle table to a similar table in starrocks
CREATE TABLE "QTDM"."MASTER_ID_CARD_ALL_20260430_LOAN_CARD_FINAL_V2_CONVERT_LOS_CIF_SR"
( "LOS_CIF_NO" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"CONTRACT_NO" VARCHAR2(255) COLLATE "USING_NLS_COMP",
"APP_CODE" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"CONTRACT_STATUS" VARCHAR2(20) COLLATE "USING_NLS_COMP",
"ID_CARD_NUMBER" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"ID_CARD_NUMBER_OTHER" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"STATUS_CREATED_DATE" DATE,
"CUSTOMER_NAME" VARCHAR2(250) COLLATE "USING_NLS_COMP",
"TYPE" CHAR(4) COLLATE "USING_NLS_COMP",
"MASTER_ID_CARD" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"LOS_CIF_NO_V2" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"MASTER_ID_CARD_V2" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"CUSTOMER_NAME_TRIM" VARCHAR2(32767) COLLATE "USING_NLS_COMP",
"CUSTOMER_NAME_REMOVE_COMBINING" VARCHAR2(32767) COLLATE "USING_NLS_COMP",
"CUSTOMER_NAME_NO_ACCENT" VARCHAR2(32767) COLLATE "USING_NLS_COMP",
"CUSTOMER_NAME_CONVERT" VARCHAR2(32767) COLLATE "USING_NLS_COMP",
"CUSTOMER_NAME_CONVERT_REMOVE_SPACE" VARCHAR2(32767) COLLATE "USING_NLS_COMP",
"TEXT_CHECK_STATUS" VARCHAR2(18) COLLATE "USING_NLS_COMP",
"SPECIAL_CHAR_FOUND" VARCHAR2(32767) COLLATE "USING_NLS_COMP",
"CUSTOMER_NAME_ASCIISTR" VARCHAR2(2500) COLLATE "USING_NLS_COMP",
"CNT_CONTRACT" NUMBER,
"CNT_LOS_CIF" NUMBER,
"CNT_NAME" NUMBER,
"MASTER_ID_CARD_V3" VARCHAR2(91) COLLATE "USING_NLS_COMP"
) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DM_MHQTRR" ;
r
Rocky
05/29/2026, 8:34 AMIn StarRocks, we map Oracle types to high-performance OLAP types. Since this table appears to be a finalized analytical dataset (based on the previous logic we discussed), I have used the Duplicate Key model. This is the most efficient model in StarRocks for large-scale analytical queries and batch loading.
StarRocks DDL
Copy code
sql
CREATE TABLE QTDM.MASTER_ID_CARD_ALL_20260430_LOAN_CARD_FINAL_V2_CONVERT_LOS_CIF_SR (
-- Grouping columns first for better compression and indexing (Key Columns)
LOS_CIF_NO VARCHAR(100),
CONTRACT_NO VARCHAR(500),
APP_CODE VARCHAR(100),
ID_CARD_NUMBER VARCHAR(100),
-- Other identifying columns
CONTRACT_STATUS VARCHAR(50),
ID_CARD_NUMBER_OTHER VARCHAR(100),
STATUS_CREATED_DATE DATETIME, -- Oracle DATE contains time, so DATETIME is the correct match
CUSTOMER_NAME VARCHAR(1000),
TYPE CHAR(16),
MASTER_ID_CARD VARCHAR(100),
LOS_CIF_NO_V2 VARCHAR(100),
MASTER_ID_CARD_V2 VARCHAR(100),
-- Long string columns (normalized data)
-- VARCHAR in StarRocks supports up to 1,048,576 bytes
CUSTOMER_NAME_TRIM VARCHAR(65533),
CUSTOMER_NAME_REMOVE_COMBINING VARCHAR(65533),
CUSTOMER_NAME_NO_ACCENT VARCHAR(65533),
CUSTOMER_NAME_CONVERT VARCHAR(65533),
CUSTOMER_NAME_CONVERT_REMOVE_SPACE VARCHAR(65533),
TEXT_CHECK_STATUS VARCHAR(50),
SPECIAL_CHAR_FOUND VARCHAR(65533),
CUSTOMER_NAME_ASCIISTR VARCHAR(5000),
-- Metrics / Counts
CNT_CONTRACT BIGINT,
CNT_LOS_CIF BIGINT,
CNT_NAME BIGINT,
MASTER_ID_CARD_V3 VARCHAR(200)
)
ENGINE=OLAP
DUPLICATE KEY(LOS_CIF_NO, CONTRACT_NO, APP_CODE, ID_CARD_NUMBER)
COMMENT "Master ID Card Final Normalized Table"
DISTRIBUTED BY HASH(LOS_CIF_NO) BUCKETS 16
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "v2"
);VARCHAR2VARCHARVARCHARDATEDATETIMEDATEDATEDATETIMENUMBERBIGINTCNT_CONTRACTBIGINTDECIMALVARCHAR2(32767)VARCHAR65533DUPLICATE KEYDUPLICATE KEYLOS_CIF_NOCONTRACT_NODISTRIBUTED BY HASHLOS_CIF_NOJOINGROUP BYPCTFREETABLESPACESTORAGEINITRANSreplication_numbucketsBUCKETS 16PARTITION BY RANGE(STATUS_CREATED_DATE)l
Lý Thành
05/29/2026, 9:28 AM@Rocky can starrock insert data to a jdbc table?
r
Rocky
05/29/2026, 9:29 AMYes, StarRocks can insert data into an external JDBC table using the JDBC Catalog. This feature allows you to "sink" data from StarRocks directly into external databases like MySQL, PostgreSQL, Oracle, or SQL Server.
Version Requirements
The ability to write to a JDBC catalog was introduced gradually:
* StarRocks v3.3+: Supports sinking data to MySQL, PostgreSQL, and ClickHouse.
* StarRocks v3.4+: Expanded support for sinking data to Oracle and SQL Server.
In earlier versions (v3.0–v3.2), JDBC catalogs were primarily read-only, meaning you could only query data from them or load data into StarRocks native tables.
How to Use It
To insert data into a JDBC table, follow these steps:
1. Create a JDBC Catalog: Define the connection to your external database.
Copy code
sql
CREATE EXTERNAL CATALOG my_mysql_db
PROPERTIES (
"type" = "jdbc",
"user" = "root",
"password" = "your_password",
"jdbc_uri" = "jdbc:<mysql://192.168.1.1:3306/target_db>",
"driver_url" = "<https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar>",
"driver_class" = "com.mysql.cj.jdbc.Driver"
);INSERT INTO Copy code
sql
INSERT INTO my_mysql_db.target_db.external_table
SELECT * FROM internal_starrocks_table;INSERT.jardriver_urll
Lý Thành
06/01/2026, 8:09 AM@Rocky how to submit task every 1 day on 8am
SUBMIT TASK losrep_application
SCHEDULE EVERY(INTERVAL 10 MINUTE)
AS
insert into DMAPP.dtm_cus_applications_af by name select * from dmapp_link.dtm_cus_applications_af_link;
r
Rocky
06/01/2026, 8:09 AMTo schedule a StarRocks asynchronous task to run every day at 8:00 AM, you should use the
STARTEVERY(INTERVAL 1 DAY)SUBMIT TASKSTART Copy code
sql
SUBMIT TASK losrep_application
SCHEDULE START('2024-05-20 08:00:00') EVERY(INTERVAL 1 DAY)
AS
INSERT INTO DMAPP.dtm_cus_applications_af BY NAME
SELECT * FROM dmapp_link.dtm_cus_applications_af_link;START('YYYY-MM-DD 08:00:00')EVERY(INTERVAL 1 DAY)BY NAMEINSERT ... BY NAME Copy code
sql
SELECT * FROM INFORMATION_SCHEMA.tasks WHERE task_name = 'losrep_application'; Copy code
sql
SELECT * FROM INFORMATION_SCHEMA.task_runs WHERE task_name = 'losrep_application';SUBMIT TASKDROP TASK losrep_applicationALTER TASKl
Lý Thành
06/01/2026, 9:18 AM@Rocky
INSERT INTO dtm_customer_ld_summary
SELECT *
FROM dmapp_link.dtm_customer_ld_summary_link a
WHERE a.SYS_RUN_DATE = %s;
Process halted: (5604, "Getting analyzing error. Detail message: Inserted target column count: 373 doesn't match select/value column count: 370."
I run insert into by name, why still get this error? can starrock insert only column which match name
r
Rocky
06/01/2026, 9:18 AMThe error occurs because your SQL statement is performing a positional insert, which requires the number of columns in the
SELECTBY NAMENULLDEFAULTBY NAME Copy code
sql
INSERT INTO dtm_customer_ld_summary BY NAME -- <--- Add this keyword
SELECT *
FROM dmapp_link.dtm_customer_ld_summary_link a
WHERE a.SYS_RUN_DATE = %s;BY NAMEdmapp_linkdtm_customer_ld_summaryDEFAULTNULLBY NAMEBY NAMESELECT asterisk_version();BY NAMEINSERTSELECTSELECT * Copy code
sql
INSERT INTO dtm_customer_ld_summary (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM dmapp_link...l
Lý Thành
06/01/2026, 10:20 AM@Rocky update this column to 'last_promised_amount' is DECIMAL64(20, 2)'
r
Rocky
06/01/2026, 10:20 AMTo update the column
last_promised_amountDECIMAL(20, 2)ALTER TABLE ... MODIFY COLUMNDECIMAL64DECIMAL128DECIMAL(20, 2) Copy code
sql
ALTER TABLE your_table_name
MODIFY COLUMN last_promised_amount DECIMAL(20, 2);DECIMAL(20, 2) Copy code
sql
SHOW ALTER TABLE COLUMN;last_promised_amount Copy code
sql
-- Step 1: Rename the column
ALTER TABLE your_table_name RENAME COLUMN old_name TO last_promised_amount;
-- Step 2: Modify the type
ALTER TABLE your_table_name MODIFY COLUMN last_promised_amount DECIMAL(20, 2);INTVARCHARDECIMALl
Lý Thành
06/02/2026, 2:41 AM@Rocky i'm using jasperreport for reporting from starrocks, do u offer better report solution for starrocks?
r
Rocky
06/02/2026, 2:41 AMWhile JasperReports is excellent for "pixel-perfect" static reports (like PDFs or invoices), it often doesn't leverage the full power of StarRocks' sub-second, interactive performance.
If you are looking for more modern, interactive, or high-performance dashboarding, StarRocks officially supports and optimizes for several alternatives:
1. The "Community Favorites" (Open Source)
These are the most common choices for StarRocks users because they support interactive slicing and dicing which matches StarRocks' speed.
* Apache Superset: StarRocks has a dedicated SQLAlchemy dialect and Python client. It is highly recommended for building real-time dashboards on top of StarRocks.
* Metabase: StarRocks has optimized compatibility for Metabase (v3.2+). It is very user-friendly for non-technical users to build their own reports via a GUI without writing SQL.
* Grafana: If your reports are time-series heavy or focused on operational monitoring, Grafana connects seamlessly to StarRocks using the MySQL data source plugin.
2. Enterprise/Commercial Solutions
If you need enterprise-grade support or advanced data modeling:
* FineBI / FineReport: These are very popular in the StarRocks ecosystem (especially in Asia) and have specific integration guides for StarRocks.
* Tableau / Power BI: Since StarRocks is compatible with the MySQL protocol, you can connect these tools directly using standard MySQL drivers. They are better than Jasper for ad-hoc discovery and "fast" data visualization.
3. Developer-Focused Tools
* Querybook: Pinterest open-sourced this tool, and StarRocks has a native integration. It’s great if your "reports" are more like shared SQL notebooks and data analysis stories.
Why move away from JasperReports?
* Interactivity: Jasper is built for "batch" reporting. Modern tools like Superset allow users to click a bar chart and instantly filter the whole dashboard—StarRocks is designed exactly for this type of sub-second response.
* Concurrency: StarRocks can handle thousands of concurrent users. Modern BI tools are better at managing this high-concurrency traffic compared to traditional Java-based reporting engines.
* Integration: StarRocks provides specific connectors for Superset and Metabase that handle metadata (like column types) more accurately than a generic JDBC connection.
Recommendation: If you want a modern dashboarding experience, try Apache Superset. If you need to empower non-SQL users, try Metabase.
References
• loading/loading_tools.md
• loading/Loading_intro.md
• integrations/BI_integrations/Querybook.md
• integrations/BI_integrations/Rill.md
• integrations/BI_integrations/FineBI.md
• integrations/BI_integrations/QuickBI.md
• release_notes/release-3.2.md
• integrations/BI_integrations/Superset.md
l
Lý Thành
06/02/2026, 3:28 AM@Rocky DTM_LOS_APP_PROCESSING_HIST: cột CURRENT_USER chuyển sang SR phải đổi thành CURRENT_USER _1 do trùng key word bên SR -> có cách nào đổi nó thành tến CURRENT_USER cho giống với Oracle không
r
Rocky
06/02/2026, 3:28 AMBạn hoàn toàn có thể sử dụng tên cột là
CURRENT_USERCURRENT_USER Copy code
sql
CREATE TABLE DTM_LOS_APP_PROCESSING_HIST (
id INT,
`CURRENT_USER` VARCHAR(255), -- Sử dụng dấu nháy ngược ở đây
...
) ...;CURRENT_USER() Copy code
sql
INSERT INTO DTM_LOS_APP_PROCESSING_HIST (`CURRENT_USER`, column2)
SELECT `CURRENT_USER`, column2
FROM source_table;USER_NAMECURRENT_USER_VALCURRENT_USERCURRENT_USER_1CURRENT_USER