SQL on Elasticsearch

Challenge

Administrative Dashboard of Crate, running SQL queries with aggregations on billions of records with sub-second response time.

Elasticsearch is an exceptional technology for building scalable databases of unstructured data and performing full-text searches.

But wouldn't it be great if:

  • You could access Elasticsearch with SQL business intelligence and data visualization tools?
  • Use SQL to perform complex queries and large aggregations, handle arrays and blobs?
  • Use a single database to handle structured and unstructured data instead of having to integrate Elasticsearch with a relational DBMS yourself?

Crate benefits

Crate is an open source SQL database that includes a healthy dose of Elasticsearch DNA.

Crate uses a standard SQL interface and provides bindings for different languages, such as JDBC, SQL Alchemy, PHP, PDO and makes it very easy to use with the existing frameworks.

If you are an Elasticsearch user, here are some ways Crate enriches your experience:

  • ‍Full Array Support
  • Blob Storage
  • In-place updates
  • Distributed Accurate Aggregations
  • Post-Aggregation Filtering (GROUP BY ... HAVING)
  • JOINs
  • Partitioned Tables
  • COPY FROM / TO to import JSON
  • Update By Query
  • Insert By Query
  • Integrated Admin-UI

Read more about Elasticsearch and SQL

Example architecture

It's a great advantage we could leverage our existing investment in the SQL eco-system while using all the benefits of Elasticsearch.

Case study

No items found.