How do I parametrize Lua script to go through table values executing queries

Question

new with Lua but trying.

I have multiple "Create table" queries which I need to execute, what changes only is Schema and Table name. At the moment I am explicitly defining each query. I want to parametrize Lua script from the table below passing table name as argument, since there is 100+ tables which needs to be generated this way.

MappingTable

targetSchema	targetTable	originSchema	originTable
schema1	table1	schema3	table3
schema2	table2	schema4	table4

Current solution

CREATE LUA SCRIPT "ScriptName" () RETURNS ROWCOUNT AS
query([[
Create or replace table schema1.table1 as
select * from schema3.table3;
]])
query([[
Create or replace table schema2.table2 as
select * from schema4.table4;
]])

What I've tried:

CREATE OR REPLACE LUA SCRIPT "ScriptName"('MappingTable') RETURNS ROWCOUNT AS
map_table = execute[[ SELECT * FROM .."'MappingTableName'"..;]] -- passing argument of the script, mapping table name

-- passing values from the columns

load =   [[Create or replace table ]]..
                  [[']]..targetSchema..[['.']].. 
                  [[']]..targetTable..]]..
                  [[as select * from]]..
                  [[']]..originSchema..[['.']]..
                  [[']]..originTable..[[']]

Not sure about the syntax, also I guess I need to loop through the values of the table. Thank you

What RDBMS do you use? – Egor Skriptunoff Jun 14 '21 at 19:52 — Egor Skriptunoff, Jun 14 '21 at 19:52

GriGrim · Accepted Answer · 2021-06-15T07:58:30.323

Here is a sample script:

create or replace lua script ScriptName (
      t_MappingTable
    , s_ConditionColumn
    , s_ConditionValue
)
returns rowcount as

-- passing argument of the script, mapping table name
local map_table = query ([[
select * from ::MappingTable where ::ConditionColumn = :ConditionValue
]],{
      MappingTable = t_MappingTable
    , ConditionColumn = s_ConditionColumn
    , ConditionValue = s_ConditionValue
});

-- passing values from the columns
for i = 1, #map_table do
    query ([[
    create or replace table ::targetSchema.::targetTable as
    select * from ::originSchema.::originTable
    ]],{
          targetSchema = map_table[i].TARGETSCHEMA
        , targetTable  = map_table[i].TARGETTABLE
        , originSchema = map_table[i].ORIGINSCHEMA
        , originTable  = map_table[i].ORIGINTABLE
    });
end
/

You may want to read values from map_table the other way.

In case when you have case-sensitive column names:

  targetSchema = map_table[i]."targetSchema"
, targetTable  = map_table[i]."targetTable"
, originSchema = map_table[i]."originSchema"
, originTable  = map_table[i]."originTable"

In case when you are sure in column order and don't want to worry about column names:

  targetSchema = map_table[i][1]
, targetTable  = map_table[i][2]
, originSchema = map_table[i][3]
, originTable  = map_table[i][4]

That's great. Thank you @GriGrim. What if I want to pass the field to the condition ``` where ::ConditionField ='::conditionColumn' ``` This is not working. What would be correct syntax for the value in the condition? — Shade, Jun 15 '21 at 06:34
Ah ok. If I want to add it in the conditionValue then it is a single : — Shade, Jun 15 '21 at 07:46
Yes. When you use variable for an object/column, then use double colon `::`. When you pass a string, then use single colon `:`. — GriGrim, Jun 15 '21 at 07:59

How do I parametrize Lua script to go through table values executing queries

1 Answers1