Search code examples
sqlvariablesgoogle-bigqueryazure-synapse

Azure Synapse Analytics SQL Query with Variables

I have an Azure Synapse Analytics Pipeline with Copy Activity. The Activity pulls data from a Google BigQuery data source. The data is pulled upon a few criteria including a beginning and end date. Here is the problematic query in the Copy Activity's 'Query' section :

 @concat('SELECT LogObject FROM `xxxx.yyyy.LogMetric` WHERE
 SourceApplication = '185' AND EventType = 'Request'  AND  TIMESTAMP(EventTime) BETWEEN  
 ','''', variables('begin_date'),'''', ' AND ', '''', variables('end_date'),'''' )

Upon running above query, I get an error like this:

'Type=System.Data.Odbc.OdbcException,Message=ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Syntax error: Expected end of input but got \"@\" at [1:1],Source=Microsoft ODBC Driver for Google BigQuery

Update 1 If I don't use @concat and do like:

SELECT LogObject FROM LogMetric WHERE TIMESTAMP(EventTime) BETWEEN 
TIMESTAMP(@variables('begin_date') AND 
TIMESTAMP(@variables('end_date'));

Then I get error:

Function call cannot be applied to this expression. Function calls require a path, e.g. a.b.c()

If I run a simpler query for testing, that works:

SELECT LogObject FROM `xxxx.yyyy.LogMetric` WHERE
SourceApplication = '185' AND EventType = 'Request' LIMIT 5;'

But I need to be able to use the two variables begin_date and end_date.

When the datasource in the Copy Activity was SQL Server, I was able to make that work fine; the difference was that in case of SQL Server, the non-date filters were numeric, such as:

@concat('Select LogObject from MyTable where AppRequestKeyId=185 and EventType=1 AND CAST 
(LogDate AS datetime2) BETWEEN  ','''', variables('begin_date'),'''', ' AND ', '''', 
variables('end_date'),'''' )

How can I make my query to work for Google BigQuery as well which would include the variables?

Solution

  • The issue with your updated query is that you are using the @ symbol inside the TIMESTAMP function to reference the values of the begin_date and end_date variables. This is not the correct syntax for referencing variables.

    To reference variables in Azure Synapse Analytics, you should use the following syntax: @{variables('variable_name')}. Note that the variable name should be enclosed in single quotes, and the entire expression should be wrapped within curly braces @{....}.

    Here is the corrected query:

    SELECT LogObject FROM LogMetric WHERE TIMESTAMP(EventTime) BETWEEN 
TIMESTAMP('@{variables('begin_date')}') AND 
TIMESTAMP('@{variables('end_date')}')

    Reference: MS document on expressions in ADF