sparql-to-sql on internet of things databases and streams

INTERNET OF THINGS DEVICES AND STREAMSSPARQL-TO-SQL ON

EUGENE SIOWTHANASSIS TIROPANISWENDY HALL

Trekking up 913m

Mt. rokko

Sensor DATA FROM MULTIPLE SOURCES

“The Internet of Things is currently beset by product silos.” W3C Web of Things Interest Group

CURRENT STATE OF THE

INTERNET OF THINGSPRODUCT & DATA SILOS

DEPENDENCY ON THE CLOUD

LIMITED INTEROPERABILITY OF APPS

LINKED DATA ON LIGHTWEIGHT COMPUTERS APPL

E

GOOG

LE

HONE

YWEL

L

CISC

O

HUAW

EI

GENE

RAL E

LECT

RIC

IBM

AMAZ

ON

INTE

L

MICR

OSOF

T

INTRODUCING

LINKED DATAFOR INTEROPERABILITY

URI and ontologiesEstablish common data structures & References

ENABLES RICH METADATAwhat, where, WHEN, HOW of DATA

Integrates with Linked open datacyber, social, physical LOD on web

Barnaghi, P., Wang, W.: Semantics for the Internet of Things: early progress andback to the future. International Journal on Semantic Web and Information Systems (2012)

http://thing.io/1is a

http://ont/weather_sensorCLASS

produceshttp://thing.io/obs/1

http://ont/temp_observation

is a

13.0

has value

CLASS

℃

unit

located athttp://thing.io/loc/1

latitude longitude

-1.4150.9

LINKED DATA ON

DISTRIBUTEDLIGHTWEIGHT COMPUTERSTiered levels of applications between the “Ground” and “Cloud” for Meteorological Data

Irrigation Application

Query on Rainfall

Meteorological Station on Lightweight Computer

Data Stream

Environmental Sensors

GROUND

WeatherData

State InclementWeather PlanningApplication

SRBench: National Weather and Disaster Monitoring

CLOUD Broadcast Queries

PERFORMANCESTORES DON’T SCALE & PERFORM WELL ON WEB YETBuil-Aranda, C., Hogan, A.: SPARQL Web-Querying Infrastructure: Ready for Action? ISWC 2013

CHALLENGES

RESOURCE CONSTRAINED DEVICES~500mhz CPU, 512mb ram, SD CARD

historical and streaming dataNeed storage and query efficiency ON BOTH

THE SHAPE OF IOT TIME-SERIES DATA

{timestamp : 1467673132,temperature : {

max: 22.0,min: 15.0,current: 17.0,error: {

percentage: 5.0}

}}

FLAT{

timestamp : 1467673132,temperature : 32.0,wind_speed : 10.5,pressure : 1016

}

COMPLEX

20kUNIQUE DEVICES

dweet.io99.5%FLAT SCHEMATA

0.5%COMPLEX SCHEMATA

1

2,3

4

5

6+

Width

{timestamp : 1467673132,temperature : 32.0,humidity : 10.5,pressure : 1016,light: 120.0,

}

1234

EFFICIENT QUERIES WITH

TIME-SERIESDATA

THING

TEMPERATURE OBS

HUMIDITY OBS

WIND SPEED OBS

13.0

2016-01-01 06:00:00

CELCIUS

93.0

2016-01-01 06:00:00

PERCENT

10.5

2016-01-01 06:00:00

MPH

LOCATION

produces

produces

located

produces

has value

unit

time

RDF GRAPH

OBSERVATION DATA

OBSERVATION METADATA

SENSOR METADATA

THING

TEMPERATURE OBS

HUMIDITY OBS

WIND SPEED OBS

13.0

LOCATION

produces

produces

located

produces

has value

THING

THING

THING

TEMPERATURE OBS

timeTEMPERATURE OBS 2016-01-01 06:00:00

unitTEMPERATURE OBS celcius

93.0has valueHUMIDITY OBS

timeHUMIDITY OBS 2016-01-01 06:00:00

unitHUMIDITY OBS PERCENT

10.5has valueWIND SPEED OBS

timeWIND SPEED OBS 2016-01-01 06:00:00

unitWIND SPEED OBS MPH


TIME-SERIESDATA

RDF TRIPLES

OURAPPROACH


TIME-SERIESDATA

THING

TEMPERATURE OBS WIND SPEED OBS

CELCIUS PERCENT MPH

LOCATION

produces

located

HUMIDITY OBS

unit

TEMPERATURE HUMIDITY WIND SPEED

13.0 93.0 10.5

TIME

2016-01-01 06:00:00

SENSOR METADATA

OBSERVATION DATA

OURAPPROACH


TIME-SERIESDATA

THING


CELCIUS PERCENT MPH

LOCATION

produces

located

HUMIDITY OBS

unit

TEMPERATURE HUMIDITY WIND SPEED

13.0 93.0 10.5

TIME

2016-01-01 06:00:00

OBSERVATION METADATA

DESIGNING OUR ENGINE

THING


CELCIUS PERCENT MPH

LOCATION

produces

located

HUMIDITY OBS

unit

TEMPERATURE HUMIDITY WINDSPEED

13.0 93.0 10.5

TIME

2016-01-01 06:00:00

Table1

TABLE1.TEMPERATURE

has value has value

TABLE1.HUMIDITY

has value

TABLE1.WINDSPEED


THING


CELCIUS PERCENT MPH

LOCATION

produces

located

HUMIDITY OBS

unit

TEMPERATURE HUMIDITY WINDSPEED

13.0 93.0 10.5

TIME

2016-01-01 06:00:00

Table1

TABLE1.TEMPERATURE

has value has value

TABLE1.HUMIDITY

has value

TABLE1.WINDSPEED

MAX( )?TEMPERATURESELECT

?OBS TEMPERATURE OBSa

has value?OBS ?TEMPERATURE

has unit?OBS ?uom

{

}

𝞹

𝞬 (max ( ))?TEMPERATURE



has unit?OBS ?uom BGP


TEMPERATURE OBS

CELCIUS

TEMPERATURE

13.0 10.5

TABLE1.TEMPERATURE

has value




has unit?OBS ?uom

{

}

𝞹









has unit?OBS ?uom

{

}

𝞹

MAX( )?TEMPERATURE

?OBS ?TEMPERATURE ?uom

TABLE1.TEMPERATURE CELCIUSNODE_TEMP

𝞹









has unit?OBS ?uom

{

}

MAX( )TEMPERATURE

𝞹





FROM TABLE1

𝞹

SPARQL





has unit?OBS ?uom

{

}

SQL SELECT MAX( )TEMPERATURE FROM TABLE1

LOW VISIBILITY STATIONS EXAMPLE

?OBS VISIBILITY OBSa

has value?OBS ?VISIBILITY

has unit?OBS ?uom

}

{

FILTER( ,<10 )?VISIBILITY

UNION

?OBS RAINFALL OBSa

has value?OBS ?RAINFALL

has unit?OBS ?uom

}

{

FILTER( ,>30 )?RAINFALL

?SENSORSELECT {

}

UNION

?OBS SNOWFALL OBSa

has value?OBS ?SNOWFALL

has unit?OBS ?uom}

{

𝞹

?OBS SNOWFALL OBSa



UNION

UNION

FILTER( ,<10 )?VISIBILITY FILTER( ,>30 )?RAINFALL




?OBS RAINFALL OBSa



FILTER( ,TRUE)?SNOWFALL




has unit?OBS ?uom

}

{


UNION

?OBS RAINFALL OBSa


has unit?OBS ?uom

}

{


?SENSORSELECT

}

UNION

?OBS SNOWFALL OBSa


has unit?OBS ?uom}

{

𝞹

UNION

UNION

FILTER( ,<10 )?VISIBILITY FILTER( ,>30 )?RAINFALL

?OBS ?snowfall ?uom

TABLE1.snowfall BOOLNODE_SNOW

?OBS ?VISIBILITY ?uom

TABLE1.VISIBILITY cmNODE_vis

?OBS ?rainfall ?uom

TABLE1.rainfall cmNODE_RAIN

{

FILTER( ,TRUE)?SNOWFALL




has unit?OBS ?uom

}

{


UNION

?OBS RAINFALL OBSa


has unit?OBS ?uom

}

{


?SENSORSELECT {

}

UNION

?OBS SNOWFALL OBSa


has unit?OBS ?uom}

{

𝞹

UNION

UNION

FILTER( ,<10 )VISIBILITY FILTER( ,>30 )RAINFALL

FROM TABLE1FROM TABLE1

FROM TABLE1

FILTER( ,TRUE)SNOWFALL




has unit?OBS ?uom

}

{


UNION

?OBS RAINFALL OBSa


has unit?OBS ?uom

}

{


?SENSORSELECT {

}

UNION

?OBS SNOWFALL OBSa


has unit?OBS ?uom}

{

𝞹

UNION

FROM TABLE1

WHERE RAINFALL>30

SELECT SENSOR FROM TABLE1

WHERE VISIBILITY<10

UNION


FILTER( ,TRUE)SNOWFALL

SQLSPARQL




has unit?OBS ?uom

}

{


UNION

?OBS RAINFALL OBSa


has unit?OBS ?uom

}

{


?SENSORSELECT {

}

UNION

?OBS SNOWFALL OBSa


has unit?OBS ?uom}

{

SELECT

UNION

WHERE RAINFALL>30

WHERE VISIBILITY<10

UNION

SENSOR FROM


WHERE SNOWFALL==TRUE

(

)



HOW MAPPINGS WORK

SPARQL2SQL Mapping

S2SMLMAPPING

TABLE1.TEMPERATURE

has value

THING


CELCIUS PERCENT MPH

LOCATION

produces

located

HUMIDITY OBS

unit

TABLE1.humidity

has value

TABLE1.WINDSPEED

has value




SPARQL



has unit?OBS ?uom

SELECT * WHERE {

}

JENA

SESAME

??

INTERFACE

S2SMLMAPPING

WRITTEN IN RDFREFLECTS THE RDF IT REPRESENTS DIRECTLY

SUPPORTS COLLAPSING NODESEFFICIENT QUERIES WITH IOT TIME-SERIES DATA

R2rML COMPATIBLECAN BE TRANSLATED TO AND FROM R2RML

SUBJECT

object

predicate

IRImapIRI BNODE

IRI

FNODE

IRImapIRI BNODE FNODE

LITERAL LITERALMAP

S2SMLMAPPING

Faux NODE HUMIDITY OBS

TABLE1.COL1

hasValue

LITERALMAP

<http://knoesis.wright.edu/ssw/obs/{readings.uuid}>

if (HUMIDITY OBS is projected):if(readings.uuid !exists):

create_col(readings.uuid)return IRImap(readings.uuid)

RDF STREAM PROCESSING

sparql2streamSame engine and

mappings but translates to EPL instead of SQL

TRANSLATE QUERY

2

Stream WindowSPARQL query specifying

stream window size

REGISTER QUERY

1

Stream SocketsSupports multiple

platforms and streams with ZeroMQ

STREAM DATA

3

Real-time analyticsPIOTRe & Smart Home

Freeboard demo github.com/eugenesiow/iotwo

RECEIVE PUSH RESULTS

4

SPARQL2stream

RSPQUERY




has unit?OBS ?uom

{

}

FROM Named Stream Obsstream Range 1h EPL SELECT MAX( )TEMPERATURE

FROM Weather.win:time(1 hour)Event Processing

Language

EVALUATION WITH BENCHMARKS

SRBENCH

~20,000 Stations

100 – 300k triples

Wind, Rainfall, etc.

10 SRBench Queries

Zhang, Y, et al. (2012) "SRBench: a streaming RDF/SPARQL benchmark.”The 11th International Semantic Web Conference.

SMART HOME BENCH

Siow, E., Tiropanis, T., Hall, W. (2016). "Interoperable and Efficient: Linked Data for the Internet of Things." The 3rd International

Conference on Internet Science.

3 months, 1 home

~30k triples

Motion, energy, environment

4 Analytics Queries

GraphDB (OWLIM)

Ontop

Our Approach (S2S)

TDB

G

Morph

O

S

M

T

Raspberry Pi 2 Model B+1GB RAM, 900MHz Quad Core ARM Cortex A7, Class 10 SD Cards

STORAGE SIZE

3ookHurricane Ike

1ookNEVADA BLIZZARD

3okSMART HOME

OUR APPROACH (s2S)

TDB

x15

x68

x112

GraphDB x9

x1352

x453

Get the rainfall observed in a particular hour from all stations01

02

SRBENCH QUERY RESULTS

Q01 with an optional clause on unit of measure

x5

S2S

S

TDB GraphDB

Ontop Morph

x3

x13

x4k

x2

x4x4

x5k

03

04

05

Detect if a hurricane has been observed

Get the average wind speed at the stations where the air temperature is >32

Join between wind observation and temperature observation subtrees time-consuming in low resource

environment (Raspberry Pi)

Detect if a station is observing a blizzard

x3

x6

x6

x88

x3

x3

06

07

08

Get the stations with extremely low visibility

Detect stations that are recently broken

Get the daily minimal and maximal air temperature observed by the sensor at a given location

x2

x14

x4

x6

x6x5

x2

09

10

Get the daily average wind force and direction observed by the sensor at a given location

Get the locations where a heavy snowfall has been observed

Our Approach (s2s) is shown to be faster on all queries in the Distributed Meteorological System with SRBench

Join between wind force and wind direction observation subtrees is time-consuming in low resource

environment (Raspberry Pi)

x3

x3k

x2

x7

Temperature aggregated by hour on a specified day01

02

SMART HOME RESULTS

Minimum and maximum temperature each day for a particular month

S2S TDB GraphDB

x7

x29

x3

x9

03

04

Energy Usage Per Room By Day

Diagnose unattended appliances consuming energy with no motion in room

Our Approach (s2s) is shown, once again, to be faster on all queries for Smart Home Analytics

Involves motion and meter data (much larger set), with space-time aggregations and joins between motion and

meter tables/subgraphs.

Involves meter data (larger set), with space-time aggregations.

x69

x13

x4

STREAM PROCESSING EFFICIENCY

1 2 3 4 5 7 8 9 10

SRBENCH294 261

306

277k 3243k 5245

426

280k

98

Le-Phuoc, D., et al. (2011) "A native and adaptive approach for unified processing of linked streams and linked data.” The 10th International Semantic Web Conference. CQELSPerformance Improvement

For IoT Data Over

SMART HOME

196

21

167xImprovement

Query

STREAM PROCESSING SCALABILITYVELOCITY>99% <1ms latency increasing from 1 to 1000 rows/ms

VOLUME33.5million rows, projected ~2.5 billion triples!

<1ms 10-100ms

1

2

5

10

100

1000

99% 100%

Rate

in ro

ws/m

s

Percentage Latency in ms Bands

PERSONAL IOT REPOSITORY

Siow, E., Tiropanis, T. and Hall, W. (2016) PIOTRe: Personal Internet of Things Repository: The 15th International Semantic Web Conference P&D

github.com/eugenesiow/piotresparql2streamsparql2sql github.com/eugenesiow/sparql2sql

PIOTRE

Apps

sparql2stream sparql2sql

Metadata

“It's a long road, it's a long and narrow way. If I can't work up to you, you'll surely have to work down to me someday.”

Narrow Way by Bob Dylan

eugene_siow

Travel Supported By:

LINKED DATA FOR INTEROPERABILITYA rich model to describe things and integrate connected thing’s data

OPTIMISED PERFORMANCE FOR STREAMS AND HISTORICAL DATAFROM 2 to 3 orders of magnitude performance improvement

sparql-to-sql on internet of things databases and streams

Technology