Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Using Grafana and CrateDB

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.

Paul Finlayson Adams

December 01, 2016

More Decks by Paul Finlayson Adams

Other Decks in Technology


  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. 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
  4. 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
  5. 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
  6. Open Machine Data Stack • Integrates easily • Low learning

    curve • Greatest flexibility • No lock in Apps DB Input Metabase C R A T E
  7. 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
  8. 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
  9. 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!
  10. 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
  11. 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