用于复制活动的Azure数据工厂表达式查询 [英] Azure Data Factory Expression Query for Copy activity

问题描述

我试图将数据从表存储复制到另一个存储帐户的另一个表存储，为此，我正在Azure数据工厂中使用复制活动.

I am trying to copy data from Table storage to another Table storage of a different storage account, for that, I am using Copy activity in Azure data factory.

我想过滤将要复制到接收器表存储的行，因为该Azure数据工厂提供了一个定义查询的选项. 我想对数据类型为String但保留数值的Partition键应用过滤器. 我正在查看此文档: https://docs.microsoft. com/zh-CN/azure/devops/pipelines/process/expressions?view = azure-devops 那里说类型转换对于"eq"，"le"，"ge"等比较运算符是隐式的

I want to filter rows that are going to be copied to the sink table storage, for that Azure data factory gives an option to define a query. I want to apply a filter on the Partition key whose datatype is String but holds numeric values. I am looking at this documentation: https://docs.microsoft.com/en-us/azure/devops/pipelines/process/expressions?view=azure-devops there it says that type conversion is implicit for comparison operators like "eq", "le", "ge" etc

因此，如果我的查询为"PartitionKey eq 0"，它将失败并显示此错误:

So if my query is "PartitionKey eq 0" it fails and gives this error:

A storage operation failed with the following error 'The remote server returned an error: (400) Bad Request.'.. Activity ID:edf8e608-d25e

但是，如果我将查询定义为"PartitionKey eq '0'"，那么它将起作用.

But if I define my query as "PartitionKey eq '0'" it works.

我想获取具有一定范围数字的行，因为我需要将分区键转换为数字值，该怎么办?

I want to fetch rows with in the certain range of numbers for that I need to cast my partition key to a numeric value, How do I do that?

"startsWith"和"endsWith"也不起作用 例如，此查询PartitionKey startsWith '10'给出与上述相同的错误.

Also the "startsWith" and "endsWith" don't work e.g, this query PartitionKey startsWith '10' gives the same error as above.

看起来像这样: 预先感谢.

Looks like this: Thanks in advance.

推荐答案

首先，要确保您的查询有效-您可以在Azure Portal中使用 Storage Explorer(预览版)在以下位置构建查询查询生成器模式:

Firstly, to make sure that your query works - you can use Storage Explorer (preview) in Azure Portal to build the query in Query Builder mode:

，然后切换到文本编辑器:

现在，您确定已获得正确的查询.
让我们将此查询应用于ADF.没有动态内容-这将是完全相同的查询:

Now, you are sure that you have got right query.
Let's apply this query to ADF. Without dynamic content - it will be exactly the same query:

为了创建动态查询-我们需要添加变量或参数来定义边界:

In order to create a dynamic query - we need to add variables or parameters to define the boundary:

然后，在查询字段中创建动态内容，替换查询:

Afterward, create a dynamic content in query field, replacing query:

PartitionKey ge '0' and PartitionKey le '1'

使用 concat 函数的格式如下:

@concat('PartitionKey ge ''0'' and PartitionKey lt ''1''')

请注意，我必须用单引号(')加上多余的一个('').
最后，我们只需要用先前定义的参数替换硬编码的值即可:

Notice, that I must enquote single quote (') by adding extra one ('').
In the end - we need just to replace hard-coded values with previously defined parameters:

@concat('PartitionKey ge ''',pipeline().parameters.PartitionStart,''' and PartitionKey lt ''',pipeline().parameters.PartitionEnd,'''')

仅此而已.我希望我能解释如何通过在Azure数据工厂中构建动态内容(查询)来实现这一目标.

That's all. I hope that I explain how to achieve that by building dynamic content (query) in Azure Data Factory.

这篇关于用于复制活动的Azure数据工厂表达式查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！