"I love CrateDB – Distributed SQL Database Enabling Data Insights at Scale"

2022-05-08 01:10:12

Upcoming Webinar: Real-Time Energy Grid Control Based on Big Data Register now Skip to content Products CrateDB Overview CrateDB Cloud CrateDB Edge CrateDB On-Premises CrateOM Pricing Get CrateDB Customers Success Stories Use Cases Industries Docs Resources Blog Events Newsletter Videos Webinars White Papers Community Login Login Enabling Data Insights at ScaleCrateDB is the leading open-source, distributed SQL database empowering customers to turn data into business value Press ReleaseCrate.io announces partnership with Google CloudVideosWatch the recordings of the talks from Data Insights DayBlogCrateDB v4.8 is now stable and ready to use Modernize your data architecture Simplify your data architecture with CrateDB. The database for data-intense analytics and AI applications that require fast data ingest, high-performance queries, and elastic scaling with a familiar SQL interface. CrateDB is data-shape agnosticReady to handle structured and unstructured data IoT & Sensor Data Time Series Data Geospatial Tracking Log Analysis /* * Based on sensor data, this query calculates: * - time-buckets of 10 seconds * - different aggregations per time-bucket and host group */SELECT DATE_BIN('10 seconds'::INTERVAL, m.timestamp, 0) AS period, h.host_group, MIN(m.fields['usage_user']) AS "min", AVG(m.fields['usage_user']) AS "avg", MAX(m.fields['usage_user']) AS "max"FROM telegraf.metrics mLEFT JOIN telegraf.hosts h ON h.host_name = m.tags['host']WHERE tags['cpu'] = 'cpu-total' AND m.timestamp > NOW() - '5 minutes'::INTERVALGROUP BY 1, 2ORDER BY 1 DESC; +------------+------------+--------+--------+--------+| period | host_group | min | avg | max |+------------+------------+--------+--------+--------+| 1621515050 | Demo Hosts | 0.125 | 0.125 | 0.125 || 1621515040 | Demo Hosts | 0.1753 | 0.1753 | 0.1753 || 1621515030 | Demo Hosts | 0.2251 | 0.2251 | 0.2251 || 1621515020 | Demo Hosts | 0.2251 | 0.2251 | 0.2251 || 1621515010 | Demo Hosts | 0.1251 | 0.1251 | 0.1251 || 1621515000 | Demo Hosts | 0.2501 | 0.2501 | 0.2501 || 1621514990 | Demo Hosts | 0.1501 | 0.1501 | 0.1501 || 1621514980 | Demo Hosts | 0.075 | 0.075 | 0.075 || 1621514970 | Demo Hosts | 0.2503 | 0.2503 | 0.2503 || 1621514960 | Demo Hosts | 0.2 | 0.2 | 0.2 || 1621514950 | Demo Hosts | 0.1001 | 0.1001 | 0.1001 || 1621514940 | Demo Hosts | 0.175 | 0.175 | 0.175 || 1621514930 | Demo Hosts | 0.25 | 0.25 | 0.25 || 1621514920 | Demo Hosts | 0.1 | 0.1 | 0.1 |+------------+------------+--------+--------+--------+ Statement Result /* * Based on a date dimension, this query calculates: * - the observed value, carrying forward the last known value * - the observed value's ratio compared to the previous value * - the average over a moving window of the last 100 rows * - the maximum value during the same hour of observation */SELECT s.time, COALESCE( distance, LAG(distance) IGNORE NULLS OVER (ORDER BY s.time ASC) ) AS distance, 1 - ( LAG(distance) OVER (ORDER BY s.time ASC) / distance ) AS ratio, AVG(distance) OVER ( ORDER BY s.time ASC ROWS BETWEEN 100 PRECEDING AND CURRENT ROW ) AS moving_avg, MAX(distance) OVER ( PARTITION BY DATE_TRUNC('hour', s.time) ) AS hourly_maxFROM GENERATE_SERIES( DATE_TRUNC('minute', NOW() - '10 minutes'::INTERVAL), DATE_TRUNC('minute', NOW()), '1 minute'::INTERVAL) s(time)LEFT JOIN doc.raw r ON r.time >= s.time AND r.time < s.time + '1 minute'::INTERVALORDER BY 1 ASC; +---------------+----------+---------+------------+------------+| time | distance | ratio | moving_avg | hourly_max |+---------------+----------+---------+------------+------------+| 1619424000000 | 108.886 | NULL | 108.886 | 124.749 || 1619424060000 | 109.88 | 0.99 | 109.383 | 124.749 || 1619424120000 | 112.171 | 0.99 | 110.312 | 124.749 || 1619424180000 | 112.815 | 0.99 | 110.938 | 124.749 || 1619424240000 | 115.87 | 0.99 | 111.924 | 124.749 || 1619424300000 | 116.737 | 0.99 | 112.726 | 124.749 || 1619424360000 | 118.429 | 0.99 | 113.541 | 124.749 || 1619424420000 | 119.76 | 0.99 | 114.319 | 124.749 || 1619424480000 | 121.739 | 0.99 | 115.143 | 124.749 || 1619424540000 | 122.213 | 0.99 | 115.85 | 124.749 || 1619424600000 | 124.163 | 0.99 | 116.606 | 124.749 || 1619424660000 | 124.749 | 0.99 | 117.284 | 124.749 || 1619424720000 | 124.749 | NULL | 117.284 | 124.749 || 1619424780000 | 124.749 | NULL | 117.284 | 124.749 || 1619424840000 | 124.749 | NULL | 117.284 | 124.749 || 1619424900000 | 124.749 | NULL | 117.284 | 124.749 |+---------------+----------+---------+------------+------------+ Statement Result /* * Based on ride-hailing data, this query calculates: * - the distance of a given dropoff location to a fixed point of interest, * partitioned in buckets of 50 meters each * - the average trip distance in kilometers within that partition * - the number of trips within that partition */SELECT FLOOR( DISTANCE(t.dropoff_location, 'POINT(-73.984 40.758)') / 50 ) * 50 AS point_distance, AVG( DISTANCE(t.pickup_location, t.dropoff_location) / 1000.0 ) AS trip_distance, COUNT(*) AS tripsFROM nyc_taxi.trips tWHERE t.pickup_month = DATE_TRUNC('month', CURDATE())GROUP BY 1ORDER BY 1 ASC; +----------------+--------------------+--------+| point_distance | trip_distance | trips |+----------------+--------------------+--------+| 0 | 6.022131503971144 | 5026 || 50 | 7.9007773871769436 | 25851 || 100 | 8.67141343399684 | 40359 || 150 | 5.951902622791926 | 58186 || 200 | 6.803184429768031 | 76231 || 250 | 5.458829825224027 | 77545 || 300 | 6.3820270018484155 | 83378 || 350 | 6.477387557677408 | 114297 || 400 | 6.214864318547081 | 105659 || 450 | 6.41286222320987 | 107163 |+----------------+--------------------+--------+ Statement Result /* * Based on system event logs, this query calculates: * - a filter for specific messages using a full-text index * - the number of entries per minute * - the average scoring ratio for each matched row */SELECT DATE_TRUNC('minute', receivedat) AS event_time, COUNT(*) AS entries, AVG(_score) AS avg_scoreFROM "syslog"."systemevents"WHERE MATCH(message, 'authentication failure') USING most_fields WITH (analyzer = 'whitespace') AND MATCH(syslogtag, 'sshd')GROUP BY 1ORDER BY 1 DESCLIMIT 10; +---------------+---------+--------------------+| event_time | entries | avg_score |+---------------+---------+--------------------+| 1620220260000 | 4 | 1.5798743814229965 || 1620220200000 | 8 | 1.7750384211540222 || 1620220140000 | 10 | 1.6113891124725341 || 1620220080000 | 9 | 1.676726798216502 || 1620220020000 | 8 | 1.6908064410090446 || 1620219960000 | 8 | 1.690401442348957 || 1620219900000 | 7 | 1.7646006005150932 || 1620219840000 | 7 | 1.7795820917401994 || 1620219780000 | 10 | 1.5844267368316651 || 1620219720000 | 13 | 1.5637413492569556 |+---------------+---------+--------------------+ Statement Result #widget_1631132059927{ margin-bottom: 20px; } @media (max-width: 767px) { #widget_1631132059927{ margin-left: auto!important; margin-right: auto!important; } }We bring valueacross an organizationFrom architects and engineers to business leaders, we work to solve datamanagement challenges companies face todayDevelopersDevelop modern applications and servicesWork across any data shapeFully managed databaseFamiliar SQL "The technical discussion with Crate.io engineers paid off, as it helped us to verify the technical and business requirements. CrateDB is an integral part of our big data streaming architecture, and it is delivering as promised." Kristoffer AxelssonPrincipal Solution Architect, TCG Senior Data ScientistsSelecting, configuring and implementing analytics solutionsCost effective scalingNo upskilling requiredMake real-time decisions "CrateDB is an important part of our data stack giving us the performance and horizontal scalability to meet our rapidly growing business needs." Sekhar SarukkaiChief Scientist,McAfee Business LeadersOptimize the performance of digital assetsPrevent operational disruptionsIncrease equipment efficiencyDecrease total cost of DB ownership "Tens of thousands of sensors generate data along our production lines, and CrateDB for Azure IoT allows us to analyze it to make real-time changes to factory efficiency." Philipp LehnerCEOALPLA Group Gartner® Honorable Mention 2021 Crate.io named as an Honorable Mention in 2021 Gartner® Magic Quadrant™ for Cloud Database Management Systems #module_1631134423841{ margin-bottom: 20px; } @media (max-width: 767px) { #module_1631134423841{ margin-left: auto!important; margin-right: auto!important; } }IndustriesCrateDB's value spans across several industries and segments The constant flow of machine & sensor data has the power to improve manufacturing efficiency, innovate processes & workflows and manage production in real-time Manufacturing IoT delivers smart energy to consumers when they need it, how they need it Smart Energy Linear logistics is no longer the norm, today optimized routes are created in real-time, enabled by data Transportation Data enables smart retail to customize pricing, forecast demand and optimize delivery Retail The potential of Big Data as a catalyst for technological innovation Technology We spend on average 80% of our time in buildings and data allows us to make that time productive, sustainable and comfortable Smart Buildings Data powers innovation across the entire life sciences value chain Life Sciences GET STARTED #module_1631137140087{ margin-bottom: 20px; } @media (max-width: 767px) { #module_1631137140087{ margin-left: auto!important; margin-right: auto!important; } }Start your CrateDB experience now Deploy in the cloud Download CrateDB Schedule demo Contact us Get started with CrateDB Cloud and launch a 30-day trial cluster.Designed to handle the complexity of high-end time series workloads in real-time, CrateDB Cloud is a fully managed database-as-a-service. Secured, scaled and operated by the engineers that built CrateDB. Why CrateDB Cloud?The power of CrateDBFully managed serviceSecure in the cloudNo lock-in Download CrateDBCrateDB is the leading open source, distributed SQL database for relational and time‑series data. It combines the familiarity of SQL with the scalability and data flexibility of NoSQL. Why CrateDB?Runs on-premiseSimple scalabilitySQL for machine dataIndustrial time series databaseEasy IoT tool integrations Request Demo Contact Go to top Product CrateDB Overview CrateDB Cloud CrateDB Edge CrateDB On-Premises CrateOMCompare Pricing Download CrateDB Cloud Login Customers Success Stories Use Cases Industries Resources Blog Events Content LibraryNewsletter Videos Webinars White Papers Developers Get Started Reference How-To Guides Support SQL 99 Docs Community Github Contribute Company About us Jobs Partners Newsroom Security Contact Follow us Follow us on Twitter Follow us on LinkedIn Follow us on Facebook Follow us on YouTube Follow us on GitHub Legal Privacy Policy Imprint © 2022 Crate.io. All rights reserved. Subscribe to the Crate.io Newsletter now