azure-data-factory

How to Parameterize a query in Azure Data Factory copy activity


I have the following query in our ADF Copy Activity

SELECT
  deltaTable.*
FROM Data.deltaTable
LEFT OUTER JOIN Data.targetTable
  ON deltaTable.signature = targetTable.signature
WHERE targetTable.signature IS NULL

Can someone let me know how to parameterize the query. When I attempt to parameterize the query I get the error:

Parameter schema was not found under EX_SourceToRaw_Single_Table

The following code is my attempt:

@concat('SELECT * FROM ',pipeline().parameters.schema,'.',pipeline().parameters.DeltaTable)
LEFT OUTER JOIN pipeline().parameters.schema,'.',pipeline().parameters.TargetTable)
  ON pipeline().parameters.DeltaTable).signature = pipeline().parameters.TargetTable).signature
WHERE pipeline().parameters.TargetTable).signature IS NULL

The deltaTable and the TargetTable both look like the following:

==========================================================================================================
|    CountryName     |    CountryISO2     |    CountryISO3     |    SalesRegion     |     signature      |
==========================================================================================================
|      Belgium       |     CHA            |     10             |        EMEA        |800e559a27d68f0478b6|
|                    |                    |                    |                    |1c4c9f009e2418e86697|
|                    |                    |                    |                    |1b6e54b549b51b1367ab|
|                    |                    |                    |                    |        450d        |
----------------------------------------------------------------------------------------------------------
|       Wales        |     steveO         |     WAL            |       Welsh        |e8c5149d54986dfe9ac9|
|                    |                    |                    |                    |5a60a76b07603fe17c28|
|                    |                    |                    |                    |2b552ec8255f123b279a|
|                    |                    |                    |                    |        533a        |
----------------------------------------------------------------------------------------------------------
|      Germany       |     DE             |     deletedupd     |        EMEA        |1232b1bd91d14a87ed83|
|                    |                    |                    |                    |0f770d74cd8cabb87153|
|                    |                    |                    |                    |5c4c2b7ff5bcb873fa80|
|                    |                    |                    |                    |        d851        |
----------------------------------------------------------------------------------------------------------
|       Italy        |     IT             |     ITA            |        EMEA        |584cf66de2f4af9eb4db|
|                    |                    |                    |                    |febefea808b1b4e6a357|
|                    |                    |                    |                    |87fcac1061de88cfb798|
|                    |                    |                    |                    |        56df        |
----------------------------------------------------------------------------------------------------------

Solution

  • After creating the schema, DeltaTable,TargetTable parameters in ADF pipeline, use the below expression in the query option of the copy activity. Here, for sample I have used lookup activity query option which will work same as copy activity query option.

    @concat('SELECT ',pipeline().parameters.DeltaTable,'.* FROM ',pipeline().parameters.schema,'.',pipeline().parameters.DeltaTable,' LEFT OUTER JOIN ',pipeline().parameters.schema,'.',pipeline().parameters.TargetTable,' ON ',pipeline().parameters.DeltaTable,'.signature = ',pipeline().parameters.TargetTable,'.signature WHERE ',pipeline().parameters.TargetTable,'.signature IS NULL')
    

    Open the dynamic content in the query and give the above expression as shown below.

    enter image description here

    To check generated query, you can go to the pipeline run -> activity run -> Input. Here, it will show the query that was generated from the above expression. You can see it generated the required query.

    enter image description here

    If you get any error, you can cross the check the generated query here and can modify it as per your requirement.