DM4T PROJECT

These SPARQL queries use the ENLITEN dataset gathered by the University of Bath. These data were gathered from 23 homes around the UK, taking readings on:

• appliance power usage
• gas usage
• carbon dioxide levels
• humidity levels
• light levels inside the home
• sound levels inside the home
• temperature inside the home
• motion data from inside the home

These data were originally gathered using sensors attached to Raspberry PIs, and stored in a mySQL database. In order for us to query them with SPARQL, we converted them into RDF triples to load into a Jena triple store (the process is described in this blog post). This allowed us to create a "SPARQL endpoint", a web service to which we can send SPARQL queries to retrieve our data.

The most basic SPARQL query possible would be:

"Get every subject, predicate and object in our datastore, and return them as "s", "p" and "o" variables. But we only want the first 20!"

Here is how we express that as a SPARQL query:

SELECT ?subject ?predicate ?object
WHERE { ?subject ?predicate ?object }
LIMIT 20

Certain keywords (SELECT and LIMIT) are used in much the same way as their SQL counterparts, but the naming of variables to be returned with the question mark (?) operator is new, as is their arrangement in triples. Once a user becomes familiar with SPARQL, it becomes a powerful, expressive and flexible way to query RDF triples. A good way to start learning the basics of SPARQL is with the Apache Jena SPARQL tutorial.

Sending the above query to our SPARQL endpoint located at http://mist.cs.bath.ac.uk/enliten/query returns the following 20 results:

It is clear that these are the first twenty triples of millions (1.25 billion, to be exact). What if we want to query just a sample of the data, in order to get just enough for a representative visualisation without having to process millions of results?

Let's try querying a subset of the data that returns millions of results: the humidity level readings. A good example query to try would be to get all humidity readings across all homes for April 2013.

A naive way to do this would be to order the data randomly and return a certain number of the randomly-ordered data:

PREFIX seas: <https://w3id.org/seas/>
PREFIX sear: <https://w3id.org/seas#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

SELECT ?value ?time
WHERE {
        ?uri rdf:type seas:RelativeHumidity;
              sear:value ?value;
              sear:measurementStart ?time .
        FILTER ( month(?time) = 4 && year(?time) = 2013 )
      }
ORDER BY RAND()
LIMIT 25

However, this query will result in a timeout on most SPARQL endpoints, taking more than 10 minutes to return a result. This is because it must order our (potentially millions of) humidity readings randomly before returning 25 of them. This is too expensive. Can we do better?

PREFIX seas: <https://w3id.org/seas/>
 PREFIX sear: <https://w3id.org/seas#>
 PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
 PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

 SELECT ?value ?time ?r
 WHERE {
         ?uri rdf:type seas:RelativeHumidity;
               sear:value ?value;
               sear:measurementStart ?time .
       BIND ( rand() AS ?r )
       FILTER ( ?r < 0.01 && year(?time) = 2013 && month(?time) = 4 )
       }
 LIMIT 1000

This query binds a random number to each result, then filters them down to only the results with random numbers below a certain threshold (0.01 in this case). This query will take too long and time out, however (with our server timeout threshold set to 30 seconds). This is because it is still going through every triple in the database, binding a random number to it, and also applying the filter comparators to find only the readings that match the month we want.

This is far too expensive! How can we make this more efficient?

 PREFIX seas: <https://w3id.org/seas/>
  PREFIX sear: <https://w3id.org/seas#>
  PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

SELECT ?value ?time ?r
WHERE {
  {
  SELECT ?value ?time
  WHERE {
          ?uri rdf:type seas:RelativeHumidity;
                sear:value ?value;
                sear:measurementStart ?time .
  FILTER( year(?time) = 2014 && month(?time) = 1 )
        }
  }
        BIND ( rand() AS ?r )
        FILTER ( ?r < 0.001 )
}
LIMIT 100

The above query first filters the data so that we only have the humidity readings from January 2014. Then it assigns a random number to each result, and filters those results so that only the random numbers below 0.001 are returned. Finally, we limit the results to 100 to show them in the following table:

As you can see, we have a nice spread of results over the entirety of the month of January. The "threshold" value of 0.001 determines the number of random samples that the query returns. In this case, it is about 100. Setting the threshold to 0.01 returns about 1000 results, but the query takes longer to complete. Here is a plot of the combined humidity readings for all enliten homes in January 2013, with the LIMIT set to 1000 and random sample threshold raised to 0.01:

And here are all humidities for the month of April, for all homes (100 random samples):

This random sampling method is orders of magnitude faster than the ORDER BY RAND() method, returning our results in under 2 seconds.

SPARQL and RDF have many strengths for semantic querying of data. However, for our purposes it has presented several challenges. The largest is that we need a quick way to get a random sample of the data, which is both difficult to describe in SPARQL and takes a long time for any triple store to return. The takeaway from this experiment is that we need to explore hybrid systems that combine the flexible semantic querying capabilities of RDF and SPARQL with the streaming capabilities of other data formats.