![\"GitHub\"](\"https://fiware.github.io/tutorials.Time-Series-Data/icon/GitHub-Mark-32px.png\"){kind=icon}
FIWARE 304: Time-Series DataThis tutorial is an introduction to\nFIWARE QuantumLeap - a\ngeneric enabler which is used to persist context data into a CrateDB\ndatabase. The tutorial activates the IoT sensors connected in the\nprevious tutorial and persists\nmeasurements from those sensors into the database. To retrieve time-based\naggregations of such data, users can either use QuantumLeap query API or\nconnect directly to the CrateDB HTTP endpoint. Results are visualised on a\ngraph or via the Grafana time series analytics tool.
\nThe tutorial uses cUrl commands throughout, but is also\navailable as\nPostman documentation
\nThe docker-compose files for this tutorial can be found on GitHub:
\n\n\"Forever is composed of nows.\"
\n— Emily Dickinson
\n
Previous tutorials have\nshown how to persist historic context data into a range of databases such as\nMySQL and PostgreSQL. Furthermore, the\nShort Term Historic\ntutorial has introduced the\nSTH-Comet generic enabler for\npersisting and querying historic context data using a MongoDB database.
\nFIWARE QuantumLeap is an\nalternative generic enabler created specifically for data persistence into the\nCrateDB time-series database, and therefore offers an alternative to the\nSTH-Comet.
\nCrateDB is a distributed SQL DBMS designed for use with the\ninternet of Things. It is capable of ingesting a large number of data points per\nsecond and can be queried in real-time. The database is designed for the\nexecution of complex queries such as geospatial and time series data. Retrieval\nof this historic context data allows for the creation of graphs and dashboards\ndisplaying trends over time.
\nA summary of the differences can be seen below:
\nFurther details about the differences between the underlying database engines\ncan be found here.
\nThe appropriate use of time series data analysis will depend on your use case\nand the reliability of the data measurements you receive. Time series data\nanalysis can be used to answer questions such as:
\nIt can also be used to reduce the significance of each individual data point to\nexclude outliers by smoothing.
\nGrafana is an open source software for time series\nanalytics tool which will be used during this tutorial. It integrates with a\nvariety of time-series databases including CrateDB. It is available licensed\nunder the Apache License 2.0. More information can be found at\nhttps://grafana.com/.
\nFor the purpose of this tutorial, a series of dummy IoT devices have been\ncreated, which will be attached to the context broker. Details of the\narchitecture and protocol used can be found in the\nIoT Sensors tutorial. The\nstate of each device can be seen on the UltraLight device monitor web page found\nat: http://localhost:3000/device/monitor
![\"FIWARE](\"https://fiware.github.io/tutorials.Time-Series-Data/img/device-monitor.png\")
Once QuantumLeap has started aggregating data, the historical state of each\ndevice can be seen on the device history web page found at:\nhttp://localhost:3000/device/history/urn:ngsi-ld:Store:001
![\"\"](\"https://fiware.github.io/tutorials.Time-Series-Data/img/history-graphs.png\")
This application builds on the components and dummy IoT devices created in\nprevious tutorials. It will\nuse three FIWARE components: the\nOrion Context Broker, the\nIoT Agent for Ultralight 2.0,\nand QuantumLeap .
\nTherefore the overall architecture will consist of the following elements:
\nThe FIWARE Generic Enablers:
\nA MongoDB database:
\nA CrateDB database:
\nA Context Provider: - A webserver acting as set of\ndummy IoT devices using\nthe\nUltralight 2.0\nprotocol running over HTTP. - Note the Stock Management Frontend and\nContext Provider NGSI proxy are not used in this tutorial.
\nSince all interactions between the elements are initiated by HTTP requests, the\nentities can be containerized and run from exposed ports.
\nThe overall architecture can be seen below:
\n![\"\"](\"https://fiware.github.io/tutorials.Time-Series-Data/img/architecture.png\")
To keep things simple all components will be run using\nDocker. Docker is a container technology which\nallows to different components isolated into their respective environments.
\nDocker Compose is a tool for defining and running multi-container Docker\napplications. A series of\nYAML files\nare used to configure the required services for the application. This means all\ncontainer services can be brought up in a single command. Docker Compose is\ninstalled by default as part of Docker for Windows and Docker for Mac, however\nLinux users will need to follow the instructions found\nhere
\nYou can check your current Docker and Docker Compose versions using the\nfollowing commands:
\ndocker-compose -v\ndocker version\nPlease ensure that you are using Docker version 18.03 or higher and Docker\nCompose 1.21 or higher and upgrade if necessary.
\nWe will start up our services using a simple Bash script. Windows users should\ndownload cygwin to provide a command-line\nfunctionality similar to a Linux distribution on Windows.
\nBefore you start, you should ensure that you have obtained or built the\nnecessary Docker images locally. Please clone the repository and create the\nnecessary images by running the commands as shown:
\ngit clone git@github.com:Fiware/tutorials.Time-Series-Data.git\ncd tutorials.Time-Series-Data\n\n./services create\nThereafter, all services can be initialized from the command-line by running the\nservices\nBash script provided within the repository:
\n./services start\n\n\n:information_source: Note: If you want to clean up and start over again\nyou can do so with the following command:
\n\n./services stop\n
In the configuration, QuantumLeap listens to NGSI v2 notifications on port\n8868 and persists historic context data to the CrateDB. CrateDB is\naccessible using port 4200 and can either be queried directly or attached to\nthe Grafana analytics tool. The rest of the system providing the context data\nhas been described in previous tutorials
cratedb:\n image: crate:2.3\n hostname: cratedb\n ports:\n - \"4200:4200\"\n - \"4300:4300\"\n command:\n -Ccluster.name=democluster -Chttp.cors.enabled=true\n -Chttp.cors.allow-origin=\"*\"\nquantumleap:\n image: smartsdk/quantumleap\n hostname: quantumleap\n ports:\n - \"8668:8668\"\n depends_on:\n - cratedb\n environment:\n - CRATE_HOST=cratedb\ngrafana:\n image: grafana/grafana\n depends_on:\n - cratedb\n ports:\n - \"3003:3000\"\n environment:\n - GF_INSTALL_PLUGINS=crate-datasource,grafana-clock-panel,grafana-worldmap-panel\nThe quantumleap container is listening on one port:
8668 is where the service will\nbe listening for notifications from the Orion context broker and where users\ncan query data from.The CRATE_HOST environment variable defines the location where the data will\nbe persisted.
The cratedb container is listening on two ports:
4200port 4300The grafana container has connected up port 3000 internally with port 3003\nexternally. This is because the Grafana UI is usually available on port 3000,\nbut this port has already been taken by the dummy devices UI so it has been\nshifted to another port. The Grafana Environment variables are described within\ntheir own documentation.\nThe configuration ensures we will be able to connect to the CrateDB database\nlater on in the tutorial
For the purpose of this tutorial, we must be monitoring a system where the\ncontext is periodically being updated. The dummy IoT Sensors can be used to do\nthis. Open the device monitor page at http://localhost:3000/device/monitor and\nunlock a Smart Door and switch on a Smart Lamp. This can be done by\nselecting an appropriate command from the drop down list and pressing the send\nbutton. The stream of measurements coming from the devices can then be seen on\nthe same page:
![\"\"](\"https://fiware.github.io/tutorials.IoT-Sensors/img/door-open.gif\")
The rate of change of the Motion Sensor is driven by events in the real-world. We need to receive\nevery event to be able to aggregate the results.
\nThis is done by making a POST request to the /v2/subscription endpoint of the Orion Context Broker.
fiware-service and fiware-servicepath headers are used to filter the subscription to only listen to measurements from the attached IoT SensorsidPattern in the request body ensures that Quantum Leap will be informed of all Motion Sensor data changes.url must match the exposed port.The metadata attribute ensures that the time_index column within the Crate-DB database will match the data found within the Orion Context Broker rather than using the creation time of the record within the Crate-DB itself.
The luminosity of the Smart Lamp is constantly changing, we only need to sample the values to be\nable to work out relevant statistics such as minimum and maximum values and rates of change.
\nThis is done by making a POST request to the /v2/subscription endpoint of the Orion Context Broker\nand including the throttling attribute in the request body.
fiware-service and fiware-servicepath headers are used to filter the subscription to only listen to measurements from the attached IoT SensorsidPattern in the request body ensures that Quantum Leap will be informed of all Smart Lamp data changes onlyurl must match the configured STH_PORTthrottling value defines the rate that changes are sampled.The metadata attribute ensures that the time_index column within the Crate-DB database will match the data found within the Orion Context Broker rather than using the creation time of the record within the Crate-DB itself.
If the subscriptions have been created, you can check to see if they are firing by making a GET \nrequest to the /v2/subscriptions endpoint.
Within the notification section of the response, you can see several additional attributes which describe the health of the subscription
If the criteria of the subscription have been met, timesSent should be greater than 0.\nA zero value would indicate that the subject of the subscription is incorrect or the subscription \nhas created with the wrong fiware-service-path or fiware-service header
The lastNotification should be a recent timestamp - if this is not the case, then the devices\nare not regularly sending data. Remember to unlock the Smart Door and switch on the Smart Lamp
The lastSuccess should match the lastNotification date - if this is not the case \nthen Quantum Leap is not receiving the subscription properly. Check that the host name\nand port are correct.
Finally, check that the status of the subscription is active - an expired subscription\nwill not fire.
Once a dynamic context system is up and running, we need to inform Quantum\nLeap directly of changes in context. As expected this is done using the\nsubscription mechanism of the Orion Context Broker. The attrsFormat=legacy\nattribute is not required since QuantumLeap accepts NGSI v2 notifications\ndirectly.
Subscriptions will be covered in the next subsections. More details about\nsubscriptions can be found in previous tutorials or in the\nsubscriptions section\nof QuantumLeap docs.
\n","event":[{"listen":"prerequest","script":{"id":"0ea31757-bc61-4572-84e0-9e15b337dcae","type":"text/javascript","exec":[""]}},{"listen":"test","script":{"id":"11352768-823c-4114-a385-7058433368ce","type":"text/javascript","exec":[""]}}],"_postman_id":"aefaee72-1ee9-4cb0-ad3c-483135f36940"},{"name":"Time Series Data Queries (QuantumLeap API)","item":[{"name":"QuantumLeap - Obtain version information","id":"2969dab8-c270-4ad0-be18-8854ace65077","protocolProfileBehavior":{"disableBodyPruning":true},"request":{"method":"GET","header":[],"url":"http://localhost:8668/v2/version","urlObject":{"protocol":"http","path":["v2","version"],"host":["localhost:8668"],"query":[],"variable":[]}},"response":[],"_postman_id":"2969dab8-c270-4ad0-be18-8854ace65077"},{"name":"List the first N Sampled Values","id":"fc69ccae-c5a1-4e1d-878a-28e844a5ab67","protocolProfileBehavior":{"disableBodyPruning":true},"request":{"method":"GET","header":[{"key":"Accept","value":"application/json"},{"key":"Fiware-Service","value":"openiot","type":"text"},{"key":"Fiware-ServicePath","value":"/","type":"text"}],"body":{"mode":"raw","raw":""},"url":"http://localhost:8668/v2/entities/Lamp:001/attrs/luminosity?=3&limit=3","description":"This example shows the first 3 sampled luminosity values from Lamp:001.
Note the use of Fiware-Service and Fiware-ServicePath headers. These are\nrequired only when data are pushed to orion using such headers (in multitenancy\nscenarios). Failing to align these headers will result in no data being\nreturned.
This example shows the fourth, fifth and sixth sampled count values from Motion:001.
\n","urlObject":{"protocol":"http","path":["v2","entities","Motion:001","attrs","count"],"host":["localhost:8668"],"query":[{"key":"offset","value":"3"},{"key":"limit","value":"3"}],"variable":[]}},"response":[],"_postman_id":"3de8c2d3-239d-4bfe-8949-747ffa72d81f"},{"name":"List the latest N Sampled Values","id":"ce821bdb-d6f0-4b83-a6f3-4e0e630bafff","protocolProfileBehavior":{"disableBodyPruning":true},"request":{"method":"GET","header":[{"key":"Accept","value":"application/json"},{"key":"Fiware-Service","value":"openiot","type":"text"},{"key":"Fiware-ServicePath","value":"/","type":"text"}],"body":{"mode":"raw","raw":""},"url":"http://localhost:8668/v2/entities/Motion:001/attrs/count?lastN=3","description":"This example shows latest three sampled count values from Motion:001.
This example shows total count values from Motion:001 over each minute.
You need QuantumLeap version >= 0.4.1. You can check your version with a\nsimple GET like:
\ncurl -X GET \\\n 'http://localhost:8668/v2/version' \\\n -H 'Accept: application/json'\nThis example shows minimum luminosity values from Lamp:001 over each minute.
You need QuantumLeap version >= 0.4.1. You can check your version with a\nsimple GET like:
\ncurl -X GET \\\n 'http://localhost:8668/v2/version' \\\n -H 'Accept: application/json'\nThis example shows maximum luminosity value of Lamp:001 that occurred\nbetween from 2018-06-27T09:00:00 to 2018-06-30T23:59:59.
Alter the fromDate and toDate parameters to retrieve your current data
This example shows the latest four sampled luminosity values of lamps that\nare within a 5 km radius from 52°33'16.9\"N 13°23'55.0\"E (Bornholmer Straße\n65, Berlin, Germany). If you have turned on all the lamps available on the\ndevice monitor page, you should be able to see data for Lamp:001 and\nLamp:004.
\n\n","urlObject":{"protocol":"http","path":["v2","types","Lamp","attrs","luminosity"],"host":["localhost:8668"],"query":[{"key":"lastN","value":"4"},{"key":"georel","value":"near;maxDistance:5000"},{"key":"geometry","value":"point"},{"key":"coords","value":"52.5547,13.3986\n"}],"variable":[]}},"response":[],"_postman_id":"a593c659-e8db-4564-8065-7a351c678fc5"},{"name":"List the latest N Sampled Values of Devices in an Area","id":"0cdf4bb0-a82f-4e5d-98ec-bf0849b8f8a5","protocolProfileBehavior":{"disableBodyPruning":true},"request":{"method":"GET","header":[{"key":"Content-Type","value":"application/json"},{"key":"Fiware-Service","type":"text","value":"openiot"},{"key":"Fiware-ServicePath","type":"text","value":"/"}],"body":{"mode":"raw","raw":""},"url":"http://localhost:8668/v2/types/Lamp/attrs/luminosity?lastN=4&georel=coveredBy&geometry=polygon&coords=52.5537,13.3996;52.5557,13.3996;52.5557,13.3976;52.5537,13.3976;52.5537,13.3996","description":"Note: Geographical queries are only available\nstarting from version
\n0.5of QuantumLeap which implements the full\nset of queries detailed in the Geographical Queries section of the\nNGSI v2 specification.
This example shows the latest four sampled luminosity values of lamps that\nare inside a square of side 200 m centred at 52°33'16.9\"N 13°23'55.0\"E\n(Bornholmer Straße 65, Berlin, Germany). Even if you have turned on all the\nlamps available on the device monitor page, you should only see data for\nLamp:001.
\n\n","urlObject":{"protocol":"http","path":["v2","types","Lamp","attrs","luminosity"],"host":["localhost:8668"],"query":[{"key":"lastN","value":"4"},{"key":"georel","value":"coveredBy"},{"key":"geometry","value":"polygon"},{"key":"coords","value":"52.5537,13.3996;52.5557,13.3996;52.5557,13.3976;52.5537,13.3976;52.5537,13.3996"}],"variable":[]}},"response":[],"_postman_id":"0cdf4bb0-a82f-4e5d-98ec-bf0849b8f8a5"}],"id":"5b69283e-bda5-488c-a091-9f4af0e72e2c","description":"Note: Geographical queries are only available\nstarting from version
\n0.5of QuantumLeap which implements the full\nset of queries detailed in the Geographical Queries section of the\nNGSI v2 specification.
CrateDB offers an\nHTTP Endpoint\nthat can be used to submit SQL queries. The endpoint is accessible under\n<servername:port>/_sql.
SQL statements are sent as the body of POST requests in JSON format, where the\nSQL statement is the value of the stmt attribute.
\n\nWhen to query CrateDB and when QuantumLeap?. As a rule of thumb,\nprefer working always with QuantumLeap for the following reasons:
\n\n
\n- Your experience will be closer to FIWARE NGSI APIs like Orion's.
\n- Your application will not be tied to CrateDB's specifics nor QuantumLeap's\nimplementation details, which could change and break your app.
\n- QuantumLeap can be easily extended to other backends and your app will get\ncompatibility for free.
\n- If your deployment is distributed, you won't need to expose the ports of\nyour database to the outside.
\n
If your are sure your query is not supported by QuantumLeap, you may have to\nend up querying CrateDB, however, please open an issue in\nQuantumLeap's repo so\nthe team is aware.
\n","event":[{"listen":"prerequest","script":{"id":"d9f95699-2277-4b49-a5fb-a4d830c96eaf","type":"text/javascript","exec":[""]}},{"listen":"test","script":{"id":"abacb9de-37b5-4464-bfe7-25f784aa1a9a","type":"text/javascript","exec":[""]}}],"_postman_id":"5b69283e-bda5-488c-a091-9f4af0e72e2c"},{"name":"Time Series Data Queries (Crate-DB)","item":[{"name":"Read Schemas","id":"6714ba7f-0a65-4864-8007-166631fc6d97","request":{"method":"POST","header":[{"key":"Content-Type","value":"application/json"}],"body":{"mode":"raw","raw":"{\"stmt\":\"SHOW SCHEMAS\"}"},"url":"http://localhost:4200/_sql","description":"Another way to see if data are being persisted is to check if a table_schema\nhas been created. This can be done by making a request to the CrateDB HTTP\nendpoint as shown below.
Schema names are formed with the mt prefix followed by fiware-service header\nin lower case. The IoT Agent is forwarding measurements from the dummy IoT\ndevices, with the FIWARE-Service header openiot. These are being persisted\nunder the mtopeniot schema.
If the mtopeniot does not exist, then the subscription to QuantumLeap has\nnot been set up correctly. Check that the subscription exists, and has been\nconfigured to send data to the correct location.
Quantum Leap will persist data into separate tables within the Crate-DB database based on the entity type. Table names are formed with the et prefix and the entity type name in lowercase.
The response shows that both Motion Sensor data and Smart Lamp data are being persisted in the database.
\n","urlObject":{"protocol":"http","path":["_sql"],"host":["localhost:4200"],"query":[],"variable":[]}},"response":[],"_postman_id":"426fd1e5-cfd2-4f08-9c6d-303b3ee901a2"},{"name":"List the first N Sampled Values","id":"a3db4647-6a11-461e-8326-5ab66132b09b","request":{"method":"POST","header":[{"key":"Content-Type","value":"application/json"}],"body":{"mode":"raw","raw":"{\"stmt\":\"SELECT * FROM mtopeniot.etlamp WHERE entity_id = 'Lamp:001' ORDER BY time_index ASC LIMIT 3\"}"},"url":"http://localhost:4200/_sql","description":"This example shows the first 3 sampled luminosity values from Lamp:001.
The SQL statement uses ORDER BY and LIMIT clauses to sort the data. More details can be found under within the Crate-DB documentation
This example shows the fourth, fifth and sixth sampled count values from Motion:001.
\nThe SQL statement uses an OFFSET clause to retrieve the required rows. \nMore details can be found under within the Crate-DB documentation
This example shows latest three sampled count values from Motion:001.
The SQL statement uses an ORDER BY ... DESC clause combined with a LIMIT clause to retrieve \nthe last N rows. More details can be found under within the Crate-DB documentation
This example shows total count values from Motion:001 over each minute.
The SQL statement uses a SUM function and GROUP BY clause to retrieve the relevant data.\nCrate-DB offers a range of Date-Time Function to truncate and convert the timestamps into data which can be grouped.
This example shows minimum luminosity values from Lamp:001 over each minute.
The SQL statement uses a MIN function and GROUP BY clause to retrieve the relevant data.\nCrate-DB offers a range of Date-Time Function to truncate and convert the timestamps into data which can be grouped.
This example shows maximum luminosity values from Lamp:001 over each minute.
The SQL statement uses a MAX function and GROUP BY clause to retrieve the relevant data.\nCrate-DB offers a range of Date-Time Function to truncate and convert the timestamps into data which can be grouped.
This example shows the average of luminosity values from Lamp:001 over each minute.
The SQL statement uses a AVG function and GROUP BY clause to retrieve the relevant data.\nCrate-DB offers a range of Date-Time Function to truncate and convert the timestamps into data which can be grouped.
Crate-DB offers an HTTP Endpoint that can be used to submit SQL queries. The endpoint is accessible under <servername:port>/_sql.
SQL statements are sent as the body of POST requests in JSON format, where the SQL statement is the value of the stmt attribute.