22-05-2020 05:59 AM
CREATE OR REPLACE SCRIPT view_load (tbl_schema, vw_schema, serv_schema) AS
local success, res = pquery([[WITH TAB1 AS
(
select COLUMN_TABLE, ('V.'||COLUMN_NAME||' = T.'||COLUMN_NAME) AS PK,
'V.'||COLUMN_NAME AS PK1, 'T.'||COLUMN_NAME AS PK2
from ::s.EXA_ALL_COLUMNS
where COLUMN_SCHEMA = :v
AND (COLUMN_TABLE LIKE 'DM\_%' OR COLUMN_TABLE LIKE 'DF\_%')
AND COLUMN_NAME LIKE ('S\_%_ID')
)
, TAB2 AS
(
select COLUMN_TABLE,
GROUP_CONCAT('V.'||COLUMN_NAME||' = T.'||COLUMN_NAME) AS UPD,
GROUP_CONCAT('V.'||COLUMN_NAME) AS INS1,
GROUP_CONCAT('T.'||COLUMN_NAME) AS INS2
from ::s.EXA_ALL_COLUMNS
where COLUMN_SCHEMA = :v
AND (COLUMN_TABLE LIKE 'DM\_%' OR COLUMN_TABLE LIKE 'DF\_%')
AND COLUMN_NAME NOT LIKE ('S\_%_ID')
GROUP BY COLUMN_TABLE
)
select T2.COLUMN_TABLE, TRIM(T1.PK), TRIM(T2.UPD), TRIM(T2.INS1||','||T1.PK1), TRIM(T2.INS2||','||T1.PK2)
from TAB1 T1 INNER JOIN TAB2 T2
ON T1.COLUMN_TABLE = T2.COLUMN_TABLE
WHERE T1.COLUMN_TABLE IN ('DF_CNTCT_ADDR'')
ORDER BY T2.COLUMN_TABLE;
]], {v=vw_schema, s=serv_schema})
local success, res1 = pquery([[select TABLE_NAME from ::s.EXA_ALL_TABLES
WHERE TABLE_SCHEMA = :v
AND TABLE_NAME IN ('DF_CNTCT_ADDR_BEST_TIME')
ORDER BY TABLE_NAME;
]], {v=vw_schema, s=serv_schema})
for i=1, #res do
local v_nm = join(".",vw_schema,res[i][1])
local vw_nm = join(" ",vw_schema,"V")
local t_nm = join(".",tbl_schema,res[i][1])
local tbl_nm = join(" ",tbl_schema,"T")
local pk_nm = res[i][2]
local v_cdc = join(".","V","CDC_KEY")
local t_cdc = join(".","T","CDC_KEY")
local cdc = join("!=",v_cdc,t_cdc)
local upd_nm = res[i][3]
local ins_nm1 = res[i][4]
local ins_nm2 = res[i][5]
output("Merge Started")
local success, result = pquery([[MERGE INTO ::n USING ::t ON (::p) WHEN MATCHED THEN UPDATE SET ::u WHERE ::c WHEN NOT MATCHED THEN INSERT (::i1) VALUES (::i2);]], {n=vw_nm, t=tbl_nm, c=cdc, p=pk_nm, u=upd_nm, i1=ins_nm1, i2=ins_nm2})
output("Merge Didn't Work")
if not success then
output('Critical internal error.')
local error_txt = 'Could not drop user. Error: '..result
exit(9)
end
end
output("Truncate Started")
for k=1, #res1 do
local tl_nm = res1[k][1]
local success = pquery([[TRUNCATE TABLE ::v.::t;]], {v=vw_schema, t=tl_nm})
if not success then
output('Critical internal error.')
exit(9)
end
local success = pquery([[INSERT INTO ::v.::t SELECT * FROM ::s.::t;]], {v=vw_schema, s=tbl_schema, t=tl_nm})
query([[COMMIT;]])
if not success then
output('Critical internal error.')
exit(9)
end
end
/
When i execute the above script its failing with below error. If i pass the individual column instead of Group_Concat() inside the Merge statement then script is working fine. But i need the script to work with group_concat() because i need to execute it for more than 200 tables.
Solved! Go to Solution.
25-05-2020 05:49 PM
Just want to piggy back off of @littlekoi - I also helped @rajinfor with this together, and this was the problem we found. Passing the placeholder for a list of columns didn't work because it was all entered as one string instead of the complete list.
We were able to concatenate the SQL string with the list instead of using the placeholders and it worked.
26-05-2020 02:48 PM
Please find the working code below, which will do Insert else Update for more than 200 tables and some of them are Full load (Truncate/Insert) from schemas in Exasol. Highlighted difference with Bold letters.
CREATE OR REPLACE SCRIPT DM_PM_CRM_GLBL.view_load (tbl_schema, vw_schema, serv_schema) AS
local success, res = pquery([[WITH TAB1 AS
(
select COLUMN_TABLE, COLUMN_NAME AS PK,
'V.'||COLUMN_NAME AS PK1, 'T.'||COLUMN_NAME AS PK2
from ::s.EXA_ALL_COLUMNS
where COLUMN_SCHEMA = :v
AND (COLUMN_TABLE LIKE 'DM\_%' OR COLUMN_TABLE LIKE 'DF\_%')
AND COLUMN_NAME LIKE ('S\_%_ID')
)
, TAB2 AS
(
select COLUMN_TABLE,
GROUP_CONCAT('V.'||COLUMN_NAME||' = T.'||COLUMN_NAME) AS UPD,
GROUP_CONCAT('V.'||COLUMN_NAME) AS INS1,
GROUP_CONCAT('T.'||COLUMN_NAME) AS INS2
from ::s.EXA_ALL_COLUMNS
where COLUMN_SCHEMA = :v
AND (COLUMN_TABLE LIKE 'DM\_%' OR COLUMN_TABLE LIKE 'DF\_%')
AND COLUMN_NAME NOT LIKE ('S\_%_ID')
GROUP BY COLUMN_TABLE
)
select T2.COLUMN_TABLE, T1.PK, TRIM(T2.UPD), TRIM(T2.INS1||','||T1.PK1), TRIM(T2.INS2||','||T1.PK2)
from TAB1 T1 INNER JOIN TAB2 T2
ON T1.COLUMN_TABLE = T2.COLUMN_TABLE
WHERE T1.COLUMN_TABLE NOT IN ('DF_CNTCT_ADDR_BEST_TIME','DF_CNTCT_CORP_PLAN','DF_BRAND_PLAN_EXPNS',
'DF_EVT_DETAILS','DF_EVT_INVITEES_EMP','DF_POSTN_FLAT','DF_POSTN_FLAT_EMP',
'DF_POSTN_FLAT_TERR','DF_RPTNG_HIER_FLAT','DF_RPTNG_HIER_FLAT_POSTN',
'DF_RPTNG_HIER_FLAT_TERR','DF_TERR_HIER_FLAT','DM_OPU',
'DM_CRM_USER_ROLE','DM_CRM_USER_PROFILE','DF_CRM_USER_DATA')
ORDER BY T2.COLUMN_TABLE;
]], {v=vw_schema, s=serv_schema})
if not success then
output('Critical internal error.')
error(res.error_message)
exit(9)
end
local success, res1 = pquery([[select TABLE_NAME from ::s.EXA_ALL_TABLES
WHERE TABLE_SCHEMA = :v
AND TABLE_NAME IN ('DF_CNTCT_ADDR_BEST_TIME','DF_CNTCT_CORP_PLAN','DF_BRAND_PLAN_EXPNS',
'DF_EVT_DETAILS','DF_EVT_INVITEES_EMP','DF_POSTN_FLAT','DF_POSTN_FLAT_EMP',
'DF_POSTN_FLAT_TERR','DF_RPTNG_HIER_FLAT','DF_RPTNG_HIER_FLAT_POSTN',
'DF_RPTNG_HIER_FLAT_TERR','DF_TERR_HIER_FLAT','DM_OPU',
'DM_CRM_USER_ROLE','DM_CRM_USER_PROFILE','DF_CRM_USER_DATA')
ORDER BY TABLE_NAME;
]], {v=vw_schema, s=serv_schema})
for i=1, #res do
local v_nm = join(".",vw_schema,res[i][1])
local t_nm = join(".",tbl_schema,res[i][1])
local pk_nm = res[i][2]
local upd_nm = res[i][3]
local ins_nm1 = res[i][4]
local ins_nm2 = res[i][5]
local success, res3 = pquery([[MERGE INTO ::n V USING ::m T ON V.::p = T.::p
WHEN MATCHED THEN UPDATE SET ]]..upd_nm..[[
WHERE V.CDC_KEY != T.CDC_KEY
WHEN NOT MATCHED THEN INSERT (]]..ins_nm1..[[) VALUES (]]..ins_nm2..[[)]], {n=v_nm, m=t_nm, p=pk_nm})
if not success then
output('Critical internal error.')
error(res3.error_message)
output("Merge isn't Working")
exit(9)
end
end
for k=1, #res1 do
local tl_nm = res1[k][1]
local success = pquery([[TRUNCATE TABLE ::v.::t;]], {v=vw_schema, t=tl_nm})
if not success then
output('Critical internal error.')
exit(9)
end
local success = pquery([[INSERT INTO ::v.::t SELECT * FROM ::s.::t;]], {v=vw_schema, s=tbl_schema, t=tl_nm})
query([[COMMIT;]])
if not success then
output('Critical internal error.')
exit(9)
end
end
/
26-05-2020 02:48 PM
Please find the working code below, which will do Insert else Update for more than 200 tables and some of them are Full load (Truncate/Insert) from schemas in Exasol. Highlighted difference with Bold letters.
CREATE OR REPLACE SCRIPT DM_PM_CRM_GLBL.view_load (tbl_schema, vw_schema, serv_schema) AS
local success, res = pquery([[WITH TAB1 AS
(
select COLUMN_TABLE, COLUMN_NAME AS PK,
'V.'||COLUMN_NAME AS PK1, 'T.'||COLUMN_NAME AS PK2
from ::s.EXA_ALL_COLUMNS
where COLUMN_SCHEMA = :v
AND (COLUMN_TABLE LIKE 'DM\_%' OR COLUMN_TABLE LIKE 'DF\_%')
AND COLUMN_NAME LIKE ('S\_%_ID')
)
, TAB2 AS
(
select COLUMN_TABLE,
GROUP_CONCAT('V.'||COLUMN_NAME||' = T.'||COLUMN_NAME) AS UPD,
GROUP_CONCAT('V.'||COLUMN_NAME) AS INS1,
GROUP_CONCAT('T.'||COLUMN_NAME) AS INS2
from ::s.EXA_ALL_COLUMNS
where COLUMN_SCHEMA = :v
AND (COLUMN_TABLE LIKE 'DM\_%' OR COLUMN_TABLE LIKE 'DF\_%')
AND COLUMN_NAME NOT LIKE ('S\_%_ID')
GROUP BY COLUMN_TABLE
)
select T2.COLUMN_TABLE, T1.PK, TRIM(T2.UPD), TRIM(T2.INS1||','||T1.PK1), TRIM(T2.INS2||','||T1.PK2)
from TAB1 T1 INNER JOIN TAB2 T2
ON T1.COLUMN_TABLE = T2.COLUMN_TABLE
WHERE T1.COLUMN_TABLE NOT IN ('DF_CNTCT_ADDR_BEST_TIME','DF_CNTCT_CORP_PLAN','DF_BRAND_PLAN_EXPNS',
'DF_EVT_DETAILS','DF_EVT_INVITEES_EMP','DF_POSTN_FLAT','DF_POSTN_FLAT_EMP',
'DF_POSTN_FLAT_TERR','DF_RPTNG_HIER_FLAT','DF_RPTNG_HIER_FLAT_POSTN',
'DF_RPTNG_HIER_FLAT_TERR','DF_TERR_HIER_FLAT','DM_OPU',
'DM_CRM_USER_ROLE','DM_CRM_USER_PROFILE','DF_CRM_USER_DATA')
ORDER BY T2.COLUMN_TABLE;
]], {v=vw_schema, s=serv_schema})
if not success then
output('Critical internal error.')
error(res.error_message)
exit(9)
end
local success, res1 = pquery([[select TABLE_NAME from ::s.EXA_ALL_TABLES
WHERE TABLE_SCHEMA = :v
AND TABLE_NAME IN ('DF_CNTCT_ADDR_BEST_TIME','DF_CNTCT_CORP_PLAN','DF_BRAND_PLAN_EXPNS',
'DF_EVT_DETAILS','DF_EVT_INVITEES_EMP','DF_POSTN_FLAT','DF_POSTN_FLAT_EMP',
'DF_POSTN_FLAT_TERR','DF_RPTNG_HIER_FLAT','DF_RPTNG_HIER_FLAT_POSTN',
'DF_RPTNG_HIER_FLAT_TERR','DF_TERR_HIER_FLAT','DM_OPU',
'DM_CRM_USER_ROLE','DM_CRM_USER_PROFILE','DF_CRM_USER_DATA')
ORDER BY TABLE_NAME;
]], {v=vw_schema, s=serv_schema})
for i=1, #res do
local v_nm = join(".",vw_schema,res[i][1])
local t_nm = join(".",tbl_schema,res[i][1])
local pk_nm = res[i][2]
local upd_nm = res[i][3]
local ins_nm1 = res[i][4]
local ins_nm2 = res[i][5]
local success, res3 = pquery([[MERGE INTO ::n V USING ::m T ON V.::p = T.::p
WHEN MATCHED THEN UPDATE SET ]]..upd_nm..[[
WHERE V.CDC_KEY != T.CDC_KEY
WHEN NOT MATCHED THEN INSERT (]]..ins_nm1..[[) VALUES (]]..ins_nm2..[[)]], {n=v_nm, m=t_nm, p=pk_nm})
if not success then
output('Critical internal error.')
error(res3.error_message)
output("Merge isn't Working")
exit(9)
end
end
for k=1, #res1 do
local tl_nm = res1[k][1]
local success = pquery([[TRUNCATE TABLE ::v.::t;]], {v=vw_schema, t=tl_nm})
if not success then
output('Critical internal error.')
exit(9)
end
local success = pquery([[INSERT INTO ::v.::t SELECT * FROM ::s.::t;]], {v=vw_schema, s=tbl_schema, t=tl_nm})
query([[COMMIT;]])
if not success then
output('Critical internal error.')
exit(9)
end
end
/
25-05-2020 05:02 PM
Where is the error message?
Also I suggest to use more meaningful variable names, it is kind of hard to debug this script for someone not familiar with the code 🙂
That said I think your problem is here:
-------------------------------------
GROUP_CONCAT('V.'||COLUMN_NAME||' = T.'||COLUMN_NAME) AS UPD,
GROUP_CONCAT('V.'||COLUMN_NAME) AS INS1,
GROUP_CONCAT('T.'||COLUMN_NAME) AS INS2
-------------------------------------
I think you should have:
GROUP_CONCAT('V.'||COLUMN_NAME||' = T.'||COLUMN_NAME SEPARATOR ' AND ' ) AS UPD,
GROUP_CONCAT('V.'||COLUMN_NAME SEPARATOR ', ') AS INS1,
GROUP_CONCAT('T.'||COLUMN_NAME SEPARATOR ', ') AS INS2
24-05-2020 12:43 PM
I do not think it is possible to use a placeholder for array of identifiers.
It might be a good idea to switch to general programming language (Python, Java, etc.) to generate complex SQL's like this. You'll have 100% flexibility in SQL text generation, and it will be easier to manage and extend this code in the long run.
LUA is simplistic and limited. It works well for very simple scripts only .
25-05-2020 05:49 PM
Just want to piggy back off of @littlekoi - I also helped @rajinfor with this together, and this was the problem we found. Passing the placeholder for a list of columns didn't work because it was all entered as one string instead of the complete list.
We were able to concatenate the SQL string with the list instead of using the placeholders and it worked.
26-05-2020 10:57 AM
if possible, please share the solution with the community 🙂
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In