How to fetch latest timestamp values from table using DynamoDB PHP

Question

I'm new to DynamoDB.
In my application, every registered user has some sensors on his account and those sensors will send some data every 5 mins to our database.

Now I want to show "If user has logged into his account, I should need to display all his sensors with latest timestamp values for each sensor".

I did this with three queries like "I fetched all devices under his account -> Original sensor IDs -> each sensor latest value " so this process taking very long time to execute.

And my tables look like this:

users:

---------------------
|sno  | userId      |
---------------------
| 1   | naveenkumar |
|--------------------
| 2   | abc         |
|--------------------
| 3   | xyz         |
|--------------------

sensor_devices:

---------------------
| id | sensorId     |
---------------------
| 1  | sensor1      |
|--------------------
| 2  | sensor2      |
|--------------------
| 3  | sensor3      |
|--------------------
| 4  | sensor4      |
---------------------
| 5  | sensor5      |
---------------------

And

users-sensors:

----------------------
| userId | sensorId  |
----------------------
| 1      |  1        |
|---------------------
| 1      |  2        |
|---------------------
| 1      |  4        |
|---------------------
| 2      |  5        |
|---------------------

data_tbl:

---------------------------------------------
| sensorId  | value    | timestamp          |
---------------------------------------------
| sensor1   | 4.3      | 2014-01-21 11:21:00|
|--------------------------------------------
| sensor2   | 5.0      | 2014-01-21 11:22:00|
|--------------------------------------------
| sensor3   | 10.0     | 2014-01-21 11:19:00|
|--------------------------------------------
| sensor4   | 6.3      | 2014-01-21 11:25:00|
|--------------------------------------------
| sensor1   | 8.3      | 2014-01-21 11:26:00|
|--------------------------------------------
| sensor2   | 6.0      | 2014-01-21 11:27:00|
|--------------------------------------------
| sensor3   | 9.0      | 2014-01-21 11:24:00|
|--------------------------------------------
| sensor4   | 6.3      | 2014-01-21 11:30:00|
|--------------------------------------------

And Final Output table which will see the user in his homepage:

---------------------------------------------
| sensorId  | value    | timestamp          |
---------------------------------------------
| sensor1   | 8.3      | 2014-01-21 11:26:00|
|--------------------------------------------
| sensor2   | 6.0      | 2014-01-21 11:27:00|
|--------------------------------------------
| sensor4   | 6.3      | 2014-01-21 11:30:00|
|--------------------------------------------

And my query looks like this and it's taking long time to execute, so please provide me a better solution:

Let us assume $_SESSION['login_id'] = 1;

<?php
  $aws = Aws::factory('config.php');
  $client = $aws->get("dynamodb");

  $tableName = "data_tbl";
  echo "<table border=1>";
  echo "<tr>";

  echo "<th>Sensor ID</th>";
  echo "<th>value</th>";
  echo "<th>Timestamp</th>";
  echo "</tr>";

  //Query to get user devices
  $devices = $client->query(array(
    "TableName" => "users-sensors",
    "KeyConditions" => array(
      "userId" => array(
        "ComparisonOperator" => ComparisonOperator::EQ,
        "AttributeValueList" => array(
          array(Type::STRING => $_SESSION['login_id'])
        )
      )
    )
  ));
  foreach ($devices['Items'] AS $key=>$value) {
    $id = $value['sensorId']['S'];
    //Query to get original sensorId from sensor serial number
    $device = $client->query(array(
      "TableName" => "sensor_devices",
      "KeyConditions" => array(
        "id" => array(
          "ComparisonOperator" => ComparisonOperator::EQ,
          "AttributeValueList" => array(
            array(Type::STRING => $id)
          )
        )
      )
    ));
    $dids =  $device['Items'][0]['sensorId'][Type::STRING];
    //Query to get all latest values of sensors
    $response = $client->query(array(
      "TableName" => $tableName,
      "KeyConditions" => array(
        "sensorId" => array(
          "ComparisonOperator" => ComparisonOperator::EQ,
          "AttributeValueList" => array(
            array(Type::STRING => $dids)
          )
        )
      ),
      "Select" => "SPECIFIC_ATTRIBUTES",
      "AttributesToGet" => array("sensorId","value","timestamp"),
      "ScanIndexForward" => false,
      "Limit" => 1  
    ));
    foreach ($response['Items'] AS $key=>$value) {
      echo "<tr>";
      $link = "graph.php?id=".$id;
      echo "<td><a href='$link'>".$value['sensorId']['S']."</a></td>";
      echo "<td><a href='$link'>".$value['value']['S']."</a></td>";
      $epoch = $value['timestamp']['S'];
      $timestamp = date('Y-m-d H:i:s', $epoch);
      echo "<td><a href='$link'>".$timestamp."</a></td>";
      echo "</tr>";
    }
  }
  echo "</table>";
?>

If I would get answer for this I would get complete my job. Thanks

Answer 1

You're looking at and thinking of DynamoDB from a relational database perspective. DynamoDB is not a relational database. You (generally) don't want to model your data in DynamoDB like you would in MySQL. Like just about anything in software development/computer science, this is a tradeoff. You pay some data de-normalization and duplication costs in exchange for extremely low query latency and extreme scalability.

I don't use PHP, so I can't help you with the code, but I think I can help you with your data model design.

If I understand your requirements correctly, need to get a list of sensor IDs and their latest timestamps for each user upon user login. If so, here is one potential approach you could take to reduce your queries from 3 to 1:

Your table's hash key is UserID and the range key is the Timestamp of the sensor event:

------------------------
| Hash Key | Range Key |
------------------------
| User ID  | Timestamp |
------------------------

The table would have an attribute of Sensor ID corresponding to which sensor observed the event. An entry for User 12 observed by sensor 29 on February 14, 2014 at 11:51 AM could look like this:

---------------------------------------
| Hash Key | Range Key     | Sensor ID |
----------------------------------------
|    12    | 1392396884000 |    29     |
----------------------------------------

Note that I used the timestamp in milliseconds so that DynamoDB could easily sort it.

Now, when a user logs in, you can query the table by User ID to get every event for that user. In your query request, set the ScanIndexForward => false (from the DynamoDBSDK docs here: http://docs.aws.amazon.com/aws-sdk-php/latest/class-Aws.DynamoDb.DynamoDbClient.html#_query). DynamoDB will return your results in order from most recent to least recent. If there are a large number of events for that user, DynamoDB will page the result set accordingly, but you can see how to handle that in the DynamoDB documentation.

Now you have a list (or whatever the corresponding data structure in PHP is) of events for that user. You'll need to filter by SensorID to get the most recent. The good news is that since the results are in order, you can just iterate through the results and check the Sensor ID of each item; if you do not have a result from that Sensor ID, add it to your filtered collection. If you've allready added an event from that Sensor ID, that means you have the most recent event from that Sensor ID for that User ID and you should skip it.

Depending on the needs of your application, if you needed to get a list of every event for the sensors, you could simply put a global secondary index on the table using an index hash key of Sensor ID and the event Timestamp. Since the User ID is part of the primary key of the table, DynamoDB will automatically project it into the index.

Hope that helps.