Reza Amya
Reputation: 1724
How retrieve all of records in Azure storage tables using PowerShell or Azure CLI
I am using Azure CLI and I can connect to storage table and get Items from the table using bellow code in a loop for each table in my storage:
"get metrics from $table"
$temp_result = (az storage entity query --table-name $table --connection-string $connection_string --accept "none" --filter "CounterName eq '\Memory\PercentUsedMemory' or CounterName eq '\NetworkInterface\BytesReceived' or CounterName eq '\NetworkInterface\BytesTransmitted' or CounterName eq '\Processor\PercentProcessorTime'" --select "TIMESTAMP" "Average" "CounterName" | ConvertFrom-Json)
($temp_result.items).Length
And the result is something like this:
get metrics from WADMetricsPT1HP10DV2S20171007
88
get metrics from WADMetricsPT1HP10DV2S20171017
688
get metrics from WADMetricsPT1HP10DV2S20171027
464
get metrics from WADMetricsPT1HP10DV2S20180326
88
get metrics from WADMetricsPT1HP10DV2S20180405
72
get metrics from WADMetricsPT1MP10DV2S20171007
1000
get metrics from WADMetricsPT1MP10DV2S20171017
1000
get metrics from WADMetricsPT1MP10DV2S20171027
1000
get metrics from WADMetricsPT1MP10DV2S20180326
1000
get metrics from WADMetricsPT1MP10DV2S20180405
1000
But in one of my tables I have more than 37000 records and I want to get all of them. I tried to set --num-results 9999 like bellow:
"get metrics from $table"
$temp_result = (az storage entity query --table-name $table --connection-string $connection_string --accept "none" --filter "CounterName eq '\Memory\PercentUsedMemory' or CounterName eq '\NetworkInterface\BytesReceived' or CounterName eq '\NetworkInterface\BytesTransmitted' or CounterName eq '\Processor\PercentProcessorTime'" --select "TIMESTAMP" "Average" "CounterName" --num-results 9999 | ConvertFrom-Json)
($temp_result.items).Length
but it will return this error:
{"odata.error":{"code":"InvalidInput","message":{"lang":"en-US","value":"One of the request inputs is not valid.\nRequestId:d8ccda32-f002-0034-2619-cd42aa000000\nTime:2018-04-05T20:07:56.7891454Z"}}}
Can you guide me what is wrong and how I can get all of records in Azure storage tables using Powershell or Azure CLI?
UPDATE:
As Zhaoxing Lu said in this answer, I tried to use --marker. but there is some problems.
first of all, it's my current code:
$connection_string = (az storage account show-connection-string --resource-group $resourceGroup --name $storageAccount | ConvertFrom-Json).connectionString
$table_list = (az storage table list --connection-string $connection_string | ConvertFrom-Json).name
if ($table_list.Length -eq 0)
{
"There is no table in '$storageAccount' storage"
}
else
{
"There is "+$table_list.Length+" tables stored in $storageAccount."
###########################################
## Find those tables that stored metrics ##
###########################################
$tables = @()
Foreach($temp_table_name in $table_list)
{
if ($temp_table_name.StartsWith("WADMetrics"))
{
$tables += $temp_table_name
}
}
if ($tables.Length -eq 0)
{
"There is no table starting with 'WADMetrics' as prefix in storage ($storageAccount) tables, then we can't detect any Metric."
}
else
{
($tables.Length).tostring()+" tables stored metrics"
Foreach($table in $tables)
{
"get metrics from $table"
$is_more_results = 0
$nextMarker = @{}
$nextpartitionkey = ""
$nextrowkey = ""
Do
{
$filter_str = "CounterName eq '\Memory\PercentUsedMemory' or CounterName eq '\NetworkInterface\BytesReceived' or CounterName eq '\NetworkInterface\BytesTransmitted' or CounterName eq '\Processor\PercentProcessorTime'"
$temp_result
if ($nextpartitionkey -ne "")
{
"Call with marker"
$temp_result = az storage entity query --table-name $table --connection-string $connection_string --accept "minimal" --filter "CounterName eq '\Memory\PercentUsedMemory' or CounterName eq '\NetworkInterface\BytesReceived' or CounterName eq '\NetworkInterface\BytesTransmitted' or CounterName eq '\Processor\PercentProcessorTime'" --select "TIMESTAMP" "Average" "CounterName" --num-results 50 --marker $nextMarker
}
else
{
"Call without marker"
$temp_result = az storage entity query --table-name $table --connection-string $connection_string --accept "minimal" --filter "CounterName eq '\Memory\PercentUsedMemory' or CounterName eq '\NetworkInterface\BytesReceived' or CounterName eq '\NetworkInterface\BytesTransmitted' or CounterName eq '\Processor\PercentProcessorTime'" --select "TIMESTAMP" "Average" "CounterName" --num-results 50
}
$temp_result = [string]$temp_result
$temp_result = $temp_result | ConvertFrom-Json
($temp_result.items).Length
if (($temp_result.nextMarker.nextpartitionkey).Length -gt 0)
{
#there is more items in the requested query
$nextpartitionkey = $temp_result.nextMarker.nextpartitionkey
$nextrowkey = $temp_result.nextMarker.nextrowkey
$nextMarker["nextpartitionkey"] = $nextpartitionkey
$nextMarker["nextrowkey"] = $nextrowkey
$is_more_results = 1
}
else
{
$is_more_results = 0
}
} While ($is_more_results -ne 0)
}
}
}
Above code will return bellow output:
There is 16 tables stored in testresource********.
10 tables stored metrics
get metrics from WADMetricsPT1HP10DV2S20171007
Call without marker
50
items
-----
{@{Average=10.095833333333333; CounterName=\Memory\PercentUsedMemory; TIMESTAMP=2017-10-16T22:00:00+00:00; etag=W/"datetime'2017-10-16T23%3A00%3A05.500086...
Call with marker
az : ERROR: 'str' object has no attribute 'get'
At line:66 char:36
+ ... mp_result = az storage entity query --table-name $table --connection- ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (ERROR: 'str' ob...attribute 'get':String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Traceback (most recent call last):
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\knack\cli.py", line 197, in invoke
cmd_result = self.invocation.execute(args)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 347, in execute
six.reraise(*sys.exc_info())
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\six.py", line 693, in reraise
raise value
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 319, in execute
result = cmd(params)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 180, in __call__
return super(AzCliCommand, self).__call__(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\knack\commands.py", line 109, in __call__
return self.handler(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\__init__.py", line 420, in default_command_handler
result = op(**command_args)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\multiapi\cosmosdb\v2017_04_17\table\tableservice.py", line 730, in
query_entities
resp = self._query_entities(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\multiapi\cosmosdb\v2017_04_17\table\tableservice.py", line 776, in
_query_entities
next_partition_key = None if marker is None else marker.get('nextpartitionkey')
AttributeError: 'str' object has no attribute 'get'
ConvertFrom-Json : Cannot bind argument to parameter 'InputObject' because it is null.
At line:75 char:47
+ $temp_result = $temp_result | ConvertFrom-Json
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand
0
I tried to pass --marker directly using bellow code:
if (($temp_result.nextMarker.nextpartitionkey).Length -gt 0)
{
#there is more items in the requested query
$nextpartitionkey = $temp_result.nextMarker.nextpartitionkey
$nextMarker = $temp_result.nextMarker
$is_more_results = 1
}
but the error was same:
There is 16 tables stored in testresource********.
10 tables stored metrics
get metrics from WADMetricsPT1HP10DV2S20171007
Call without marker
50
items
-----
{@{Average=10.095833333333333; CounterName=\Memory\PercentUsedMemory; TIMESTAMP=2017-10-16T22:00:00+00:00; etag=W/"datetime'2017-10-16T23%3A00%3A05.500086...
Call with marker
az : ERROR: 'str' object has no attribute 'get'
At line:66 char:36
+ ... mp_result = az storage entity query --table-name $table --connection- ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (ERROR: 'str' ob...attribute 'get':String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Traceback (most recent call last):
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\knack\cli.py", line 197, in invoke
cmd_result = self.invocation.execute(args)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 347, in execute
six.reraise(*sys.exc_info())
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\six.py", line 693, in reraise
raise value
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 319, in execute
result = cmd(params)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 180, in __call__
return super(AzCliCommand, self).__call__(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\knack\commands.py", line 109, in __call__
return self.handler(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\__init__.py", line 420, in default_command_handler
result = op(**command_args)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\multiapi\cosmosdb\v2017_04_17\table\tableservice.py", line 730, in
query_entities
resp = self._query_entities(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\multiapi\cosmosdb\v2017_04_17\table\tableservice.py", line 776, in
_query_entities
next_partition_key = None if marker is None else marker.get('nextpartitionkey')
AttributeError: 'str' object has no attribute 'get'
ConvertFrom-Json : Cannot bind argument to parameter 'InputObject' because it is null.
At line:75 char:47
+ $temp_result = $temp_result | ConvertFrom-Json
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand
0
I even tried to remove --marker and pass nextpartitionkey and nextrowkey by query!, it didn't return any error, but it will return always same and the loop will continue forever.
if ($nextpartitionkey -ne "")
{
$filter_str = $filter_str + " and PartitionKey eq '$nextpartitionkey' and RowKey eq '$nextrowkey'"
}
$temp_result = az storage entity query --table-name $table --connection-string $connection_string --accept "minimal" --filter $filter_str --select "TIMESTAMP" "Average" "CounterName" --num-results 50
UPDATE 2:
I tried passing marker as stirng. bellow string is a sample of marker that I used as string:
"nextMarker": {
"nextpartitionkey": "1!260!OjAwMkZzdWJzY3JpcHRpb25zOjAwMkYzMTM1Nzc1YTowMDJEMTA2ZDowMDJENGVmODowMDJEODlhYTowMDJEN2VkYjM2YjRjMzU2OjAwMkZyZXNvdXJjZUdyb3Vwcz
owMDJGVGVzdFJlc291cmNlR3JvdXAyOjAwMkZwcm92aWRlcnM6MDAyRk1pY3Jvc29mdDowMDJFQ29tcHV0ZTowMDJGdmlydHVhbE1hY2hpbmVzOjAwMkZjcHV1c2FnZXRlc3Q-",
"nextrowkey": "1!72!OjAwNUNNZW1vcnk6MDA1Q1BlcmNlbnRVc2VkTWVtb3J5X18yNTE4OTQxMzExOTk5OTk5OTk5"
}
I also tried this one as string:
{
"nextpartitionkey": "1!260!OjAwMkZzdWJzY3JpcHRpb25zOjAwMkYzMTM1Nzc1YTowMDJEMTA2ZDowMDJENGVmODowMDJEODlhYTowMDJEN2VkYjM2YjRjMzU2OjAwMkZyZXNvdXJjZUdyb3Vwcz
owMDJGVGVzdFJlc291cmNlR3JvdXAyOjAwMkZwcm92aWRlcnM6MDAyRk1pY3Jvc29mdDowMDJFQ29tcHV0ZTowMDJGdmlydHVhbE1hY2hpbmVzOjAwMkZjcHV1c2FnZXRlc3Q-",
"nextrowkey": "1!72!OjAwNUNNZW1vcnk6MDA1Q1BlcmNlbnRVc2VkTWVtb3J5X18yNTE4OTQxMzExOTk5OTk5OTk5"
}
in both I got this error:
az : ERROR: 'str' object has no attribute 'get'
At C:\Users\Reza\Desktop\ndbench\Azure\Automation\01_get_metrics\add_target_to_tables - runbook.ps1:87 char:36
+ ... mp_result = az storage entity query --table-name $table --connection- ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (ERROR: 'str' ob...attribute 'get':String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Traceback (most recent call last):
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\knack\cli.py", line 197, in invoke
cmd_result = self.invocation.execute(args)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 347, in execute
six.reraise(*sys.exc_info())
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\six.py", line 693, in reraise
raise value
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 319, in execute
result = cmd(params)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\commands\__init__.py", line 180, in __call__
return super(AzCliCommand, self).__call__(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\knack\commands.py", line 109, in __call__
return self.handler(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\cli\core\__init__.py", line 420, in default_command_handler
result = op(**command_args)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\multiapi\cosmosdb\v2017_04_17\table\tableservice.py", line 730, in
query_entities
resp = self._query_entities(*args, **kwargs)
File "C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\lib\site-packages\azure\multiapi\cosmosdb\v2017_04_17\table\tableservice.py", line 776, in
_query_entities
next_partition_key = None if marker is None else marker.get('nextpartitionkey')
AttributeError: 'str' object has no attribute 'get'
ConvertFrom-Json : Cannot bind argument to parameter 'InputObject' because it is null.
At C:\Users\Reza\Desktop\ndbench\Azure\Automation\01_get_metrics\add_target_to_tables - runbook.ps1:112 char:47
+ $temp_result = $temp_result | ConvertFrom-Json
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand
then in summery, the problem is how I should pass --marker?
Upvotes: 4
Views: 3998
Answers (2)
Willie Xu
Reputation: 61
This was a bug in the CLI. A fix will be out in our next release, v2.0.32.
Basically the properties being given to the --marker parameter weren't being parsed out.
Read here for more info: https://github.com/Azure/azure-cli/issues/6194
Upvotes: 1
Zhaoxing Lu
Reputation: 6467
You got the error because it's not possible to return 9999 results at a time. As the Query Entities doc indicates, the maximal value for -num-results is 1000.
In order to get all the entities in a table, you'd need a loop of az storage entity query via continuation object. To get the next page of query result, please set --marker to the value of $temp_result.next_marker from previous iteration.
Upvotes: 1
Related Questions
- Unable to query Azure Table Storage using Azure CLI
- Get all records from azure table storage
- How to get all rows in Azure table Storage in C#?
- How to get TOP X rows from Azure Table Storage using PowerShell?
- Cant get all items from Azure Storage Table
- Get azure storage table field type
- How to calculate size of all Azure Storage Tables from a Subscription using Powershell
- How get a row in Azure Storage tables using PowerShell?
- List all azure tables in a storage account
- Powershell List all tables on Azure Storage