PTSM - #2 - QuasarDB et véhicules connectés

1. QuasarDB Use Case 5 novembre 2019
2. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable 2 AGAETIS Offices in Paris, Lyon, Clermont-Ferrand and Toulouse 25 employees Company created 13 years ago Subsidiary company of Novencia Group for Innovation and Consultancy services
3. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • ~ 300 vehicles, bus, trucks, tractors ◦ Tyre pressure, temperature, ... ◦ Latitude, longitude, altitude, used satellites, ... ◦ Engaged gear, speed, … ◦ Acceleration x,y,z,... • Frequency ◦ -GPS: 1Hz ◦ -Accelerometer: 25 Hz ◦ - CAN: 350 Hz ◦ - TPMS: 1Hz ◦ -Altimeter: 1Hz ◦ - Shock : 1KHz (!) ◦ - ... • 4,094,330,208 Rows per month (Precisely!) • 60 msg/sec (Protocol buffer) • Target ~ 250,000 new devices for Q4 2019 USE CASE CONTEXT
4. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • High availability & resilient system • QoS 2 for devices • Aggregate data • Monitoring (Infra & fleet) • Expose data to Data Scientists (API) TECHNICAL NEEDS
5. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable AS-IS ARCHITECTURE
6. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Storage ◦ Use a single DB & remove TTL ◦ Use a full time series DB ◦ Compression • Grafana integration ◦ Datasource plugin for MongoDB • Spring XD EOL ◦ Use Kafka broker ◦ Use Kafka streams to enrich data in real-time • Security implementation ◦ ACL per device • Historical data (only for migration purpose) ◦ Load data from HDFS to DB ◦ Performance issue PROBLEMS TO SOLVE
7. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable Studied database engines: 1. InfluxDB 2. MongoDB 3. QuasarDB BENCHMARKED SOLUTIONS
8. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Limitations ◦ HTTP API ◦ UDP ◦ Fields & Tags INFLUXDB TEST & LIMITATIONS
9. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Limitations ◦ Time Series DB ◦ Fields name length ◦ Aggregate data per minute ◦ Upsert (Order & minute) ◦ Flatten data ◦ Aggregation are needed most of the time (avg, count, …) ◦ Time precision MONGODB TEST & LIMITATIONS
10. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Querying ◦ Filtering: { "id.i":354676050163640, "id.t":{ "$date":"2018-09-01T01:46:00.000Z" } } ◦ Upsert: { "$set":{ "ttl":{ "$date":"2018-09-01T00:00:00.000Z"}}, "$push":{ "d":{ "$each":[ { "ms":746, "t":315.83, "p":99031.75, "a":211.21174330329822}, { ... }], "$sort":{"ms":1} } } MONTGODB TEST - QUERIES SAMPLES
11. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Use of skeletons • One table per device and channel (Best compression) • Inserts only • Tag tables QUASARDB TEST - USED MODELISATION FEATURES
12. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Query by tags • ACL associated to tags • Retrieved data are ready to use • Python pandas integration • Range queries QUASARDB TEST - USED QUERY FEATURES
13. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable BENCHMARK SUMMARY Storage HDFS QuasarDB MongoDB Size Size Ratio Size Ratio 1.47 Go 2.80 Go 1.9 5.60 Go 3.81 Time Precision Nanoseconds Milliseconds Loading (1 day ~ 1.47 Go) 1h 30 m 2h 25m Querying with index scan QuasarDB is 2.3 times faster without index scan QuasarDB is 18 times faster
14. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable TARGET ARCHITECTURE
15. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Prometheus Exporter ◦ https://github.com/Agaetis-IT/qdb-prometheus-exporter • Plugin Grafana • Kafka Connect ◦ https://github.com/Agaetis-IT/kafka-connect-qdb TOOLS & QUASARDB INTEGRATION IMPROVEMENTS
16. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Kafka ◦ Connect vs consumer/producer ◦ Partitioning ◦ Expose JVM metrics from all consumers/producers/streams • Fork real time data ◦ No down time • Load historical data ◦ Cluster stress ◦ Use HDFS for historical raw data storage ABOUT MIGRATION
17. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable MONITORING EXAMPLES -MQTT Producer
18. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable MONITORING EXAMPLES - Raw Stream
19. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable MONITORING EXAMPLES - GPS data integration
20. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable MONITORING EXAMPLES - QuasarDB Cluster Monitoring
21. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Common Table Expression usage • New types usage: Char(N) for instance • User Defined Functions • Compression improvements NEXT STEPS
22. Agaetis 2019 © Reproduction et diffusion interdite sans autorisation préalable • Leon Mergen(QuasarDB): leon@quasardb.net • François Travais(Agaetis): francois.travais@agaetis.fr THANKS
23. Mickaël Gervais Tech Lead & Associé mgervais@agaetis.fr