Issue while using Merge statement in Lua script on Exasol

[rajinfor]

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.

[exa-Nico · Accepted solution]

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. 

[rajinfor · Accepted solution]

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

/

[mucio]

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

[littlekoi]

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 .

[exa-Nico · Accepted solution]

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. 

[mucio]

if possible, please share the solution with the community 🙂