CODEWITHSUNDEEP
azurecopyazure-table-storageazure-data-factory
Sameed
Sameed

Reputation: 703

Azure Data Factory Expression Query for Copy activity

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.

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://learn.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

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

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?

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

Looks like this: enter image description here Thanks in advance.

Upvotes: 0

Views: 2240

Answers (1)

Kamil Nowinski
Kamil Nowinski

Reputation: 545

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:
Query built with Query builder

and then switch to Text Editor:

enter image description here

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:
enter image description here

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

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

PartitionKey ge '0' and PartitionKey le '1'

with the following form using concat function:

@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,'''')

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

Upvotes: 3

Related Questions