0

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!

Gary
  • 69
  • 6

1 Answers1

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}
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • So there WAS something obvious I was missing, thank you!!! – Gary Aug 02 '22 at 17:08
  • In case there are others extracting from Bigquery I've included the final solution below.... (Note below v_Extract_Dt is derived inside of ForEach loop using set variable activity and it's using 4 single quotes to wrap in single quotes: @concat('''', item().event_date, '''') FROM @{variables('v_BigQuery_From_Stmt')}, UNNEST (items) AS unnest_items WHERE event_date = @{variables('v_Extract_Dt')} – Gary Aug 02 '22 at 17:51