locked
How to retrieve all of the records in Azure storage tables using PowerShell or Azure CLI RRS feed

  • Question

  • 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"}}}

    I understood that I should use --marker but I don't know in which format I should use it?
    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

    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?




    Monday, April 23, 2018 9:01 PM

All replies

  • A query against the Table service may return a maximum of 1,000 entities at one time and may execute for a maximum of five seconds. If the result set contains more than 1,000 entities, if the query did not complete within five seconds, or if the query crosses the partition boundary, the response includes custom headers containing a set of continuation tokens. The continuation tokens may be used to construct a subsequent request for the next page of data. For more information about continuation tokens, see Query Timeout and Pagination

    So the max you can get is 1,000 then you'll have to use the continuation tokens. this is available in the rest API, which also should be the same case as CLI or PowerShell. 

    Source:
    https://docs.microsoft.com/en-us/rest/api/storageservices/Query-Entities?redirectedfrom=MSDN
    Tuesday, April 24, 2018 7:51 PM