Query Engines

What are Query Engines?

A query engine is a software system that processes and executes queries, typically written in a query language like SQL, to retrieve, manipulate, or analyze data from databases or other data storage systems. It abstracts the complexity of data retrieval, providing users with a simpler way to access and interact with data.

A query engine performs the following steps in sequence:

1. Parses the query (e.g. SQL).
2. Validates its syntax and raises any errors.
3. Optimizes the query for performance.
4. Creates an efficient execution plan.
5. Executes the query against the underlying data sources.
6. Translates the query into actions that access and process the data.
7. Formats the query output and delivers results back to the user.

Types of Query Engines

SQL-based Engines

• Designed to process SQL queries (e.g. MySQL, PostgreSQL, Presto, Hive, Spark SQL).
• Commonly used for structured data in relational databases or data warehouses.
• Most traditional relational database management systems (RDBMS), like MySQL, PostgreSQL, and Oracle, have built-in query engines.

Distributed Query Engines

• Process queries across multiple nodes or servers to achieve scalability and high performance (e.g., Hive, Presto, Spark SQL).
• Often used for big data processing, such as feature engineering for machine learning models.

Search Query Engines (or Search Engines)

• Specialized for querying text or unstructured data (e.g., Elasticsearch, Solr).
• Support advanced text-based queries, such as full-text search and ranking.

How is a Query Engine Different from a Database?

• A database includes storage, indexing, and transaction management, while the query engine focuses on how queries are processed, optimized, and executed.
• A query engine can work independently of a database by querying data directly from files, object stores, or other non-database sources. For example, Presto and Spark SQL can query data directly from data lakes without requiring the data to be loaded into a database.

Comparison of Distributed Query Engines

Hive

• Hive was the first distributed query engine in the world, developed by Facebook in 2008.
• Built on top of Hadoop’s MapReduce ecosystem, it provided reliability but suffered from slow performance due to high disk I/O and batch-oriented processing.

Presto

• Presto, also created by Facebook in 2013, was designed as a faster alternative to Hive for interactive and ad-hoc SQL queries.
• Unlike Hive, Presto processes queries entirely in memory, offering low-latency execution.
• It functions as a pure query engine, reading data directly from various storage backends like HDFS, S3, and relational databases.

Apache Spark

• Apache Spark was introduced in 2014 as a general-purpose distributed computing engine.
• Spark SQL combines SQL querying capabilities with Spark’s broader functionality, such as machine learning, real-time streaming, and graph processing.
• It offers a flexible platform for both batch and stream processing, making it a versatile choice for distributed computing.

Feature	Hive (built on MapReduce)	Presto	Spark
Processing Type	Batch	Interactive SQL	Batch, Streaming, ML
Latency	High	Low	Low to Medium
Data Storage	Disk	In-memory	In-memory and Disk
Scalability	High	Moderate	High
Ease of Use	Low (requires coding)	High (SQL-based)	Moderate (rich APIs)
Use Cases	ETL, log analysis	Ad-hoc analytics, dashboards	ML, streaming, transformations
Fault Tolerance	High (via HDFS)	Medium (memory-dependent)	High