英文:
Azure Synapse Analytics SQL Query with Variables
问题
我有一个Azure Synapse Analytics管道，其中包含Copy Activity。该活动从Google BigQuery数据源中提取数据。数据根据一些条件进行提取，包括开始日期和结束日期。以下是在Copy Activity的“查询”部分中的有问题的查询：
@concat('SELECT LogObject FROM `xxxx.yyyy.LogMetric` WHERE
SourceApplication = '185' AND EventType = 'Request' AND TIMESTAMP(EventTime) BETWEEN
', variables('begin_date'), ' AND ', variables('end_date') )
在运行上述查询时，我收到以下错误：
'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
更新1 如果我不使用@concat，并且像这样操作：
SELECT LogObject FROM LogMetric WHERE TIMESTAMP(EventTime) BETWEEN
TIMESTAMP(@variables('begin_date')) AND TIMESTAMP(@variables('end_date'));
然后我会收到错误：
Function call cannot be applied to this expression. Function calls require a path, e.g. a.b.c()
如果我运行一个简单的测试查询，它可以正常工作：
SELECT LogObject FROM `xxxx.yyyy.LogMetric` WHERE
SourceApplication = '185' AND EventType = 'Request' LIMIT 5;
但是我需要能够使用两个变量 begin_date 和 end_date。
当Copy Activity中的数据源是SQL Server时，我能够正常使用；不同之处在于，在SQL Server的情况下，非日期筛选器是数字的，例如：
@concat('Select LogObject from MyTable where AppRequestKeyId=185 and EventType=1 AND CAST
(LogDate AS datetime2) BETWEEN ', variables('begin_date'), ' AND ',
variables('end_date') )
我如何使我的查询在Google BigQuery中正常工作，其中包括这些变量？
英文:
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?
答案1
得分: 1
你更新的查询存在问题，因为你在 TIMESTAMP 函数内部使用 @ 符号来引用 begin_date 和 end_date 变量的值。这不是引用变量的正确语法。
在 Azure Synapse Analytics 中引用变量应该使用以下语法：@{variables('variable_name')}。注意，变量名应该用单引号括起来，整个表达式应该用**花括号 @{....}**括起来。
以下是已更正的查询：
SELECT LogObject FROM LogMetric WHERE TIMESTAMP(EventTime) BETWEEN
TIMESTAMP('@{variables('begin_date')}') AND
TIMESTAMP('@{variables('end_date')}')
参考文档: MS 文档中关于 ADF 中的表达式
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论