ADF dynamic content using concat - need to embed commas inside of string for long list of columns

Question

The use case seems pretty simple.... Produce a sql statement as part of a copy activity that includes a hard coded column listing and also concatenated to a parameter-provided database and table name (since the database and table names can change across environments such as dev/test/prod).

The problem is....If you use concat function it treats every comma as a new value to be concatenated. I was hoping for a way to escape the comma and treat it as a value but nothing I've tried works.

For example....concatenate the following string .... SELECT event_date, event_timestamp, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title FROM ' to... pipeline().parameters.Database_Nm + . + pipeline().parameters.Table_Nm

The workaround has been to quote the beginning and end of every line so the comma is treated as data so every column/line is a separate concatenation such as this....

@concat('SELECT event_date,', '(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ''page_title'') AS page_title,', 'from ', pipeline().parameters.Database_Nm, '.', pipeline().parameters.Table_Nm

That works...but I have over a hundred columns so this is just a bit silly as a solution. Am I missing a simpler method? TIA!

Answer 1

When most of your string is hard coded and not expressions you can use the following string interpolation expression format:

SELECT event_date,
 (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
from @{pipeline().parameters.Database_Nm}.@{pipeline().parameters.Table_Nm}