Using Grafana and CrateDB

If you have been following the IoT-trends since before the Internet-of-Things was cool, then you probably are aware that the results of having Internet-of-Everything is that all of your connected devices are now pumping out data at a terrifying scale. This talk will look at ways to leverage Grafana and CrateDB to store and analyze this data in real time.

This intensive presentation will include a live demo tour of building a CrateDB cluster, populating it with machine data using Gantner Q.Station, and then using Grafana and the CrateDB datasource plugin to read realtime data from temperature, accelerometer and tweezer sensors.

CrateDB is an open source SQL RDBMS that was designed for machine data. Processing machine data on CrateDB yields 20x better predictive analytic query performance over MySQL, and with 75% less database-hardware cost.

Transcript

1. C R A T E . I O Using Grafana and CrateDB Processing Your Machine Data Paul Adams | Engineering Manager
2. what is cratedb? CrateDB is an open source, distributed SQL database with integrated search that makes it simple to store and analyze massive amounts of structured and unstructured data in real-time. We love data: • Time Series • Geospatial • BLOBS • (Un)structured
3. How did we get here?
4. 2014 Founding Mission: A SQL DB for the Machine Data Era Market need: • A machine data analytics database for the SQL mainstream Key design elements: • Millions of data points/second • Real time SQL query performance • Time series, geo, predictive, search… • Ad-hoc & aggregations! • Any structured or unstructured data • Simple scaling • SQL access 20% 15% 20% 35% 10% 5,200 0 E x a b y t e s Source (data in 2020 only): EMC/IDC report 2012
5. Built on a decade of recent innovations… 2003-2006 2014… 2007 - 2014 • Smart devices/mobile apps • Cloud computing • NoSQL • DevOps • Microservices/containers ANALYTIC DB IS BORN MORE TECH INNOVATION ANALYTIC DATABASES STAGNATE SQL FOR MACHINE DATA & REAL-TIME ANALYTICS • > 10x faster & cheaper • Distributed DB • Columnar DB • Scale-out & Unstructured for mainstream • Microservices architecture • Multi-model 1st SQL Disruption 2nd SQL Disruption last decade of innovations Bigtable
6. CrateDB Combines Best of All Worlds Perfect for workloads with: • Multi-structured data • High velocity INSERT • Fast queries • Ad hoc • Time series • Geospatial • Aggregates • Scale-out architecture • Fault-tolerant • BI integration All easy enough for SQL mainstream to operate Enabled by distributed query and micro services architecture innovations SQL SEARCH OPEN
 SOURCE
7. Open Machine Data Stack • Integrates easily • Low learning curve • Greatest flexibility • No lock in Apps DB Input Metabase C R A T E
8. Crate DB
 Features & Architecture
9. CrateDB Key Ideas • Distributed SQL for scale out • Simple scalability • Masterless, shared-nothing
 microservices architecture • Auto-sharding & partioning • Realtime search & aggregations • Columnar, multi model • Dynamic schema • Timeseries, Geospatial support • In-memory speed
10. SQL features: • ANSI SQL (subset with extensions) • JOINs (fully distributed) • Arrays and nested objects • Different types • Information Schema • Cluster/Node state exposed via tables • Partitioned tables • Geospatial support • Full text search • Powerful text processing
 • Subselects (en route) • Streaming support • Common relational Operators: • Projection • Grouping (incl. HAVING) • Aggregations Sorting Limit/Offset • WHERE-clause • Import/Export
11. Graphical
 Admin UI + SHELL
12. NYC 311 + Cabs
13. What is 311? • Source of government information and non- emergency services: • Heating • Sanitation • Potholes • ~24million incidents reported since 2009 • 100+million calls • Data published monthly!
14. shall we explore?
15. CrateDB + grafana
16. createdb data source • Version 0.2.0 now available! • Implementation by raintank GrafanaLabs (thanks!) • Feedback appreciated • Support for all of CrateDB • Data types • Scalar functions: avg, min, max, abs etc
17. time series machine data
18. Gantner q.brixx + station • Data Acquisition Device • Readings to 100khz • Output direct to CrateDB • Designed for industrial and test engineering environments • Voltage, Current, Temperature, Acceleration… • The kind of thing the TSA loves to find in your hand luggage
19. C R A T E . I O Thank you paul@crate.io @therealpadams