CODEWITHSUNDEEP
azureazure-storage
Sridhar Paiya
Sridhar Paiya

Reputation: 478

Copy activity met storage operation failure at 'Source' side. Error message from storage execution

I am getting error when I query data from PerformanceCountersTable using Timestamp column like this, Copy activity met storage operation failure at 'Source' side. Error message from storage execution : The remote server returned an error: (400) Bad Request. One of the request inputs is not valid

Here is my Pipeline activity:

        "activities": [
            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "AzureTableSource",
                        "azureTableSourceQuery": "$$Text.Format('(CounterName eq \\'\\Process(WaWorkerHost)\\% Processor Time\\' or CounterName eq \\'\\Memory\\Available MBytes\\') and Timestamp ge datetime \\'{0:yyyy-MM-ddTHH:mm:ssZ}\\' and Timestamp lt datetime \\'{1:yyyy-MM-ddTHH:mm:ssZ}\\'', SliceStart, SliceEnd)"
                    },
                    "sink": {
                        "type": "SqlSink",
                        "writeBatchSize": 0,
                        "writeBatchTimeout": "00:00:00"
                    }
                },
                "inputs": [
                    {
                        "name": "MetricsDataVMCPUinput"
                    }
                ],
                "outputs": [
                    {
                        "name": "MetricsDataVMCPUoutput"
                    }
                ],
                "policy": {
                    "timeout": "00:15:00",
                    "concurrency": 1,
                    "retry": 3
                },
                "scheduler": {
                    "frequency": "Minute",
                    "interval": 15,
                    "style": "EndOfInterval"
                },
                "name": "MetricsDataVMCPUactivity"
            }

When I change the Azuretablesourcequery like this,without Timestamp: CounterName eq \\'\\Process(WaWorkerHost)\\% Processor Time\\' or CounterName eq \\'\\Memory\\Available MBytes\\'

I am getting data properly. I don't know whats the error with this query !

Upvotes: 0

Views: 115

Answers (1)

Gaurav Mantri
Gaurav Mantri

Reputation: 136146

400 error means some incorrect data is supplied to Table Service. On inspecting your query

"azureTableSourceQuery": "$$Text.Format('(CounterName eq \\'\\Process(WaWorkerHost)\\% Processor Time\\' or CounterName eq \\'\\Memory\\Available MBytes\\') and Timestamp ge datetime \\'{0:yyyy-MM-ddTHH:mm:ssZ}\\' and Timestamp lt datetime \\'{1:yyyy-MM-ddTHH:mm:ssZ}\\'', SliceStart, SliceEnd)"

I noticed that there's a space between datetime and the date/time value. This could cause the request to table service to fail with 400 error.

Please try by removing the space. So your query would be:

"azureTableSourceQuery": "$$Text.Format('(CounterName eq \\'\\Process(WaWorkerHost)\\% Processor Time\\' or CounterName eq \\'\\Memory\\Available MBytes\\') and Timestamp ge datetime\\'{0:yyyy-MM-ddTHH:mm:ssZ}\\' and Timestamp lt datetime\\'{1:yyyy-MM-ddTHH:mm:ssZ}\\'', SliceStart, SliceEnd)"

Another thing I noticed is that you're querying on Timestamp value. This is bad because it will cause full table scan. Instead use PartitionKey in your query. Essentially what you have to do is convert SliceStart and SliceEnd to ticks and prepend a 0 to that value. I have written a blog post about the same that you may find useful: http://gauravmantri.com/2012/02/17/effective-way-of-fetching-diagnostics-data-from-windows-azure-diagnostics-table-hint-use-partitionkey/.

Upvotes: 1

Related Questions