CODEWITHSUNDEEP
azureazure-data-factory
Hari
Hari

Reputation: 133

Azure Data Factory- How to construct query with if condition in the expression builder?

i have below query in the copy activity for copying data from list of tables into a target. i am using foreach activity to iterate over array parameter- tables. Now some table name contains schema as prefix, so i need to extract the schema name from the table name and construct the query. if table name doesn't contain schema name, then query should take the default name given as a parameter in the pipeline.

below is the parameter and value
default_schema_parameter=myschema
tables= ["testschema_table1", "table2","prodschema_table2"]

there are two tables which contains schema name

prodschema_table2
testschema_table1
prod_cust_table1

now i want to construct query like this as sudo code

#Expected

if tables item contains '_':
   select * from item.replace with '.'
else:
   select * from default_schema_parameter.item

expected query:
1.select * from prodschema.table2
2.select * from default_schema_parameter.table2
3.select * from prod.cust_table1 -- only first part spliting

i tried with below approach

@concat('SELECT * FROM ',
    if(contains(item(), '_'),
        concat(replace(split(item(), '_')[0], ' ', ''), '.', split(item(), '_')[1]),  -- Construct schema.table if '_' is present
        concat('@{default_schema_parameter}.', item())  -- Construct default_schema_parameter.table if no '_'
    )
)

but above code not working. i am getting below error on


concat(replace(split(item(), '_')[0],

cannot fit unknown into the function parameter

Is there any solution to this

Upvotes: 0

Views: 927

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11514

enter image description here

It is not an error, it is just a warning which occurs when we use split()[index] function in the string expressions.

Change your expression like below and debug the pipeline.

@concat('SELECT * FROM ',
    if(contains(item(), '_'),
        concat(split(item(), '_')[0], '.', split(item(), '_')[1]),
        concat(pipeline().parameters.default_schema_parameter,'.',item())
    )
)

It won't give any error on the pipeline debug.

enter image description here

UPDATE:

i want to only split the first appearence of _" so it should be 'prod.cust_table1' but currently above code makes spliting in all occurance of prod.cust.table1.

Use the below dynamic expression to achieve this requirement.

@concat('SELECT * FROM ',
    if(contains(item(), '_'),
        concat(split(item(), '_')[0], '.', replace(item(),concat(split(item(), '_')[0],'_'),'')),
        concat(pipeline().parameters.default_schema_parameter,'.',item())
    )
)

This expression will give the table name by replacing the string "schema_" with an empty string('') from current @item().


enter image description here

Upvotes: 0

Related Questions