SQL ON ELASTICSEARCH

< Back to Use Cases

Challenge

Administrative Dashboard of CrateDB, 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

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

CrateDB 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 CrateDB 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