The Enterprise Database
for Time Series,
Documents, and Vectors
Distributed - Native SQL - Open Source - Ready for AI
Leverage SQL to Query Time Series, Document, Vector Data, and More
/* Based on device data, this query returns the average
* of the battery level for every hour for each device_id
*/
WITH avg_metrics AS (
SELECT device_id,
DATE_BIN('1 hour'::INTERVAL, time, 0) AS period,
AVG(battery_level) AS avg_battery_level
FROM devices.readings
GROUP BY 1, 2
ORDER BY 1, 2
)
SELECT period,
t.device_id,
manufacturer,
avg_battery_level
FROM avg_metrics t, devices.info i
WHERE t.device_id = i.device_id
AND model = 'mustang'
LIMIT 10;
+---------------+------------+--------------+-------------------+
| period | device_id | manufacturer | avg_battery_level |
+---------------+------------+--------------+-------------------+
| 1480802400000 | demo000001 | iobeam | 49.25757575757576 |
| 1480806000000 | demo000001 | iobeam | 47.375 |
| 1480802400000 | demo000007 | iobeam | 25.53030303030303 |
| 1480806000000 | demo000007 | iobeam | 58.5 |
| 1480802400000 | demo000010 | iobeam | 34.90909090909091 |
| 1480806000000 | demo000010 | iobeam | 32.4 |
| 1480802400000 | demo000016 | iobeam | 36.06060606060606 |
| 1480806000000 | demo000016 | iobeam | 35.45 |
| 1480802400000 | demo000025 | iobeam | 12 |
| 1480806000000 | demo000025 | iobeam | 16.475 |
+---------------+------------+--------------+-------------------+
SELECT
title AS title,
protagonist['first_name'] AS name,
date_format(
'%D %b %Y',
'GMT',
protagonist['details']['birthday']
) AS born,
quotation['words'] AS quote
FROM quotes limit 100;
+---------------+---------+--------------------+
| 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 |
+---------------+---------+--------------------+
SELECT text, _score
FROM word_embeddings
WHERE knn_match(embedding,[0.3, 0.6, 0.0, 0.9], 2)
ORDER BY _score DESC;
|------------------------|--------|
| text | _score |
|------------------------|--------|
|Discovering galaxies |0.917431|
|Discovering moon |0.909090|
|Exploring the cosmos |0.909090|
|Sending the mission |0.270270|
|------------------------|--------|
SELECT show_id, title, director, country, release_year, rating, _score
FROM "netflix_catalog"
WHERE MATCH(title_director_description_ft, 'title^2 Friday') USING best_fields
AND type='Movie'
ORDER BY _score DESC;
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
| show_id | title | director | country | release_year | rating | _score |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
| s1674 | Black Friday | Anurag Kashyap | India | 2004 | TV-MA | 5.6455536 |
| s6805 | Friday the 13th | Marcus Nispel | United States | 2009 | R | 3.226806 |
| s1038 | Tuesdays & Fridays | Taranveer Singh | India | 2021 | TV-14 | 3.1089375 |
| s7494 | Monster High: Friday Night Frights | Dustin McKenzie | United States | 2013 | TV-Y7 | 3.0620003 |
| s3226 | Little Singham: Mahabali | Prakash Satam | NULL | 2019 | TV-Y7 | 3.002901 |
| s8233 | The Bye Bye Man | Stacy Title | United States, China | 2017 | PG-13 | 2.9638999 |
| s8225 | The Brawler | Ken Kushner | United States | 2019 | TV-MA | 2.8108454 |
+---------+------------------------------------+-------------------+----------------------+--------------+--------+-----------+
/* Based on the location of the International Space Station,
* this query returns the 10 closest capital cities from
* the last known position
*/
SELECT city as "City Name",
country as "Country",
DISTANCE(i.position, c.location)::LONG / 1000 AS "Distance [km]"
FROM demo.iss i
CROSS JOIN demo.world_cities c
WHERE capital = 'primary'
AND ts = (SELECT MAX(ts) FROM demo.iss)
ORDER BY 3 ASC
LIMIT 10;
+--------------+-----------------------------------+---------------+
| City Name | Country | Distance [km] |
+--------------+-----------------------------------+---------------+
| Papeete | French Polynesia | 3386 |
| Avarua | Cook Islands | 3708 |
| Wellington | New Zealand | 4565 |
| Alofi | Niue | 4628 |
| Nuku‘alofa | Tonga | 4887 |
| Pago Pago | American Samoa | 5063 |
| Santiago | Chile | 5112 |
| Apia | Samoa | 5182 |
| Stanley | Falkland Islands (Islas Malvinas) | 5266 |
| Suva | Fiji | 5611 |
+--------------+-----------------------------------+---------------+
Adopt an Easy-to-Use Database that Scales with your Business
Any type of data
Structured, semi-structured, unstructured, time-series, geospatial, BLOB
Response time in milliseconds
Even for complex ad-hoc queries
Native SQL
For query simplicity and quick onboarding
Aggregations on the fly
Even with complex joins, large datasets and historical data
Flexible data schema
Editable on the fly at runtime
PostgreSQL Wire Protocol
For 3rd party integrations
Full-text and vector search
No need for any extra database and easy integration with AI/ML frameworks
Massively scalable
From one to hundreds of nodes
Open source
No vendor lock-in / Power of the community
Simplify your Database Operations
High availability
Automatic failover, recovery and replication
Multiple deployment models
DBaaS or self-managed / Edge extension
Cost-efficient architecture
No need to combine and synchronize different databases / Low carbon footprint
Embrace Multiple Data Use Cases
AI/ML
Internet of Things
Ingest, enrich and query high volume of sensor data in real-time, where your data resides.
Digital twins
Reduce development efforts and optimize TCO for digital twin implementations.
Real-time Analytics
Get immediate access to your data for informed decisions in real-time.
Log Analysis
Database Consolidation
Keep a single source of truth updated in near real-time with all types of data in one place.
Introduction to CrateDB
Key Concepts, Architecture, and Live Demo
Upcoming Events
The CrateDB European City Tour is a series of local technical events focused on solving a complex data use case through several hands-on activities,...
Join us at the OpenSouthCode 2024 on June 21 - 22 in Málaga!