none
[PHP] [Azure Storage] PHP Azure Storage SDK Filter not returning result set

    Question

  • Hi,

    I'm using the latest version of the PHP Azure Storage SDK ( https://github.com/Azure/azure-storage-php ), the issue I’m experiencing is the filter I have for a Table works fine in Azure Storage Explorer but when set in PHP it returns an empty result set with no errors.

    My only thought is that PHP is timing out, but then shouldnt there be a form of an error if that was the case?

    I've done a bit of searching but turned up nothing related to the issue I'm experiencing. Below is the code I'm running to try and fetch data from a table with a set filter.

    <?php namespace SenFramework\Controllers; use MicrosoftAzure\Storage\Common\ServicesBuilder; use MicrosoftAzure\Storage\Common\ServiceException; use MicrosoftAzure\Storage\Table\Models\BatchOperations; use MicrosoftAzure\Storage\Table\Models\Entity; use MicrosoftAzure\Storage\Table\Models\EdmType; use MicrosoftAzure\Storage\Table\Models\Filters\Filter; class Home extends \SenFramework\DB\Database { private $tableService; private $queueService; public $data; public function __construct() { // Azure Connection String $connectionString = 'DefaultEndpointsProtocol=https;AccountName='.AZURE_NAME.';AccountKey='.AZURE_KEY; // Azure Table Storage $this->tableService = ServicesBuilder::getInstance()->createTableService($connectionString); $filter = new \MicrosoftAzure\Storage\Table\Models\QueryEntitiesOptions();

    $filter->setSelectFields([
                 'RowKey',
                 'broadcasted'
             ]); $filter->setFilter(Filter::applyQueryString("(PartitionKey eq 'twitch' and id eq 43242267) and Timestamp ge datetime'2017-04-15T00:00:00.000Z'")); try { $channelResult = $this->tableService->queryEntities("channelAnalytics", $filter); } catch(ServiceException $e){ \SenFramework\Sen::addLogEntry('[Analytics Channel Query - '.$e->getCode().']: '.$e->getMessage().LF, 'azure'); } $channel = []; $output = NULL; if(!empty($channelResult)) { $entities = $channelResult->getEntities(); if(!empty($entities)) { foreach($entities as $entity) { $sid = explode('-', $entity->getRowKey()); $timestamp = strtotime($sid[1].'-'.$sid[2].'-'.$sid[3].' '.$sid[4].':00:00') * 1000; $channel[$timestamp] += (int)$entity->getPropertyValue('broadcasted'); } foreach($channel as $time => $val) { if($val >= 3600) { $mins = 60; } else { $mins = floor($val / 60 % 60); } $output .= '['.$time.','.$mins.'],'; } $output = rtrim($output, ','); } } $this->data['channelJson'] = $output; } } ?>



    Sunday, April 16, 2017 11:07 AM

All replies

  • Hi,

    There is nothing wrong in your code.

    To confirm, do you mean the variable $entities returns empty?

    Monday, April 17, 2017 9:37 AM
    Moderator
  • Hi,

    Thats correct

    $entities = $channelResult->getEntities();

    Returns an empty array, yet running the same query in the Storage Explorer returns results.


    CTO - Senshudo

    Monday, April 17, 2017 11:19 AM
  • I can't reproduce the issue here with azure-storage version 0.14.0

    Here is the code I have tried:

    <?php
    
    require_once 'vendor/autoload.php';
    
    use MicrosoftAzure\Storage\Common\ServicesBuilder;
    use MicrosoftAzure\Storage\Common\ServiceException;
    use MicrosoftAzure\Storage\Table\Models\BatchOperations;
    use MicrosoftAzure\Storage\Table\Models\Entity;
    use MicrosoftAzure\Storage\Table\Models\EdmType;
    use MicrosoftAzure\Storage\Table\Models\Filters\Filter;
    
    class Home {
    	
    	private $tableService;
    	private $queueService;
    	public $data;
    	
            function __construct() {		
    		
    		// Azure Connection String
    		$connectionString = 'DefaultEndpointsProtocol=http;AccountName='.AZURE_NAME.';AccountKey='.AZURE_KEY;
    		
    		// Azure Table Storage
    		$this->tableService = ServicesBuilder::getInstance()->createTableService($connectionString);
    
    		$filter = new \MicrosoftAzure\Storage\Table\Models\QueryEntitiesOptions();
    		$filter->setSelectFields([
    			'RowKey',
    			'broadcasted'
    		]);
    		$filter->setFilter(Filter::applyQueryString("(PartitionKey eq 'twitch' and id eq 43242267) and Timestamp ge datetime'2017-04-15T00:00:00.000Z'"));
    
    		try {
    			$channelResult = $this->tableService->queryEntities("channelAnalytics", $filter);
    		} catch(ServiceException $e){
    			echo '[Analytics Channel Query - '.$e->getCode().']: '.$e->getMessage().LF, 'azure';
    		}
    		
    		$channel = [];
    		$output = NULL;
    
    		if(!empty($channelResult)) {
    			$entities = $channelResult->getEntities();
    			
    			if(!empty($entities)) {
    				foreach($entities as $entity) {
                        echo "RowKey: " . $entity->getRowKey() . '<br>';
    					echo "broadcasted: " . $entity->getPropertyValue('broadcasted');
    				}
    			}
    		}
        }
    }
    
    $obj = new Home();

    Then I got the following output:

    I also have executed the same query string in the Azure Storage Explorer:





    Tuesday, April 18, 2017 7:24 AM
    Moderator
  • This is the result set that is returned in Storage Explorer

    And this is what PHP returns (using a print_r here to see the output of the varible)


    CTO - Senshudo

    Tuesday, April 18, 2017 7:36 AM
  • I've been trawling through everything the only thing I can come up with is that I need to somehow extend the timeout process, yet I'm not able to find any information on how to do this.

    The dataset is well over a million entities now, and would be the likely cause as to why it's not able to return the data within a given time period. Running the query in Storage Explorer takes around 15 seconds with that much data and its expected to grow. The PHP Script stops loading after 5 seconds.

    Any suggestions would be great thanks :)


    CTO - Senshudo

    Wednesday, April 19, 2017 7:06 AM
  • Hi there!

    Could you also provide a raw Fiddler/Wireshark trace to us to take a look ? Our devs weren't able to repro this with the Storage SDK 0.14.0.

    Thanks!

    Sercan

    Wednesday, April 19, 2017 8:04 PM
  • Sure, although I've not done a Fiddler/Wireshark trace before, how would I go about doing this? Thanks.

    CTO - Senshudo

    Wednesday, April 19, 2017 9:02 PM
  • If this is a linux machine, run 'sudo tcpdump -i any -s0 -w output.pcap' and then run your query in PHP. Once it returns empty array, stop the packet capture (Ctrl +C) and send us the output.pcap. If it is Windows server, then you could perhaps install Fiddler and capture.
    Thursday, April 20, 2017 1:29 AM
  • Hi again,

    We have determined the issue now. What happens is that the Table service returns empty body while issuing a next marker to state there is more data for you to fetch. In your case, you will see that the service has returned nextPartitionKey and nextRowKey, and you can fetch it again using QueryEntitiesOptions.

    This is an expected behavior when you have large volumes of data and happens to limit the max number of elements returned, or to limit the time of the query, or due to backend partition. Nevertheless the service did make progress on the query, so you should follow the continuation token(s) to get the full list of results. This is what Azure Storage Explorer does to get the results. We also have this feature in some of our libraries where this happens automatically in the client code, but this is not yet in the PHP SDK, but we are tracking to deliver this in the future.

    For now, this is how you can do it:

    <?php
    
    namespace MicrosoftAzure\Storage\Samples;
    
    require_once '../vendor/autoload.php';
    
    use MicrosoftAzure\Storage\Common\ServicesBuilder;
    use MicrosoftAzure\Storage\Common\ServiceException;
    use MicrosoftAzure\Storage\Table\Models\BatchOperations;
    use MicrosoftAzure\Storage\Table\Models\Entity;
    use MicrosoftAzure\Storage\Table\Models\EdmType;
    use MicrosoftAzure\Storage\Table\Models\Filters\Filter;
    use MicrosoftAzure\Storage\Table\Models\QueryEntitiesOptions;
    
    class Home
    {
        
        private $tableService;
        private $queueService;
        public $data;
        
        public function __construct()
        {
            // Azure Connection String
            $connectionString = getenv('AZURE_STORAGE_CONNECTION_STRING');
    
            if (empty($connectionString)) {
                throw new \Exception('missing connection string');
            }
            
            // Azure Table Storage
            $this->tableService = ServicesBuilder::getInstance()->createTableService($connectionString);
    
            //Initialize the table and entities for mockery.
            // $this->tableService->createTable('channelAnalytics');
    
            // for ($i = 0; $i < 2000; ++$i) {
            //     $entity = new Entity();
            //     $entity->setETag('');
            //     $entity->setPartitionKey('twitch');
            //     $entity->setRowKey(sprintf('%s-%d', '43242267', $i));
            //     $entity->addProperty('broadcasted', EdmType::INT32, \rand(1000, 10000));
            //     $entity->addProperty('id', EdmType::INT32, 43242267);
            //     $this->tableService->insertEntity('channelAnalytics', $entity);
            // }
    
    
            $filter = new QueryEntitiesOptions();
            $filter->setSelectFields([
                'RowKey',
                'broadcasted'
            ]);
            $filter->setFilter(
                Filter::applyQueryString(
                    "(PartitionKey eq 'twitch' and id eq 43242267) and Timestamp ge datetime'2017-04-19T00:00:00.000Z'"
                )
            );
    
            try {
                $channelResult = $this->tableService->queryEntities("channelAnalytics", $filter);
            } catch (ServiceException $e) {
                echo '[Analytics Channel Query - '.$e->getCode().']: '.$e->getMessage().LF, 'azure';
            }
            
            $entities = $channelResult->getEntities();
            $count = count($entities);
    
            echo "returned $count entities\n";//returned 1000 entities.
    
            //Get the entities recursively, if only part of the entities were returned.
            try {
                while ($channelResult->getNextPartitionKey() != '' &&
                    $channelResult->getNextRowKey() != '') {
                    $options = new QueryEntitiesOptions();
                    $options->setSelectFields([
                        'RowKey',
                        'broadcasted'
                    ]);
                    echo "Querying next partition: " . $channelResult->getNextPartitionKey() . PHP_EOL;
                    echo "Querying next row: " . $channelResult->getNextRowKey() . PHP_EOL;
                    //Set the next partition key and next row key.
                    $options->setNextPartitionKey($channelResult->getNextPartitionKey());
                    $options->setNextRowKey($channelResult->getNextRowKey());
                    $channelResult =
                        $this->tableService->queryEntities("channelAnalytics", $options);
                    $count = count($channelResult->getEntities());
                    $newEntities = $channelResult->getEntities();
                    //Push the new entities into the existing result.
                    foreach ($newEntities as $entity) {
                        $entities[] = $entity;
                    }
                }
            } catch (ServiceException $e) {
                echo '[Analytics Channel Query - '.$e->getCode().']: '.$e->getMessage().LF, 'azure';
            }
    
            $count = count($entities);
    
            echo "returned $count entities\n";//returned 2000 entities.
        }
    }
    
    $obj = new Home();
    

    Thursday, April 20, 2017 5:12 AM