If you’re looking to implement a big data project, you’re probably deciding whether to go with Apache Spark SQL or Apache Drill. This article can help you decide which query tool you should use for the kinds of projects you’re working on.
Spark SQL
Spark SQL is simply a module that lets you work with structured data using Apache Spark. It allows you to mix SQL within your existing Spark projects. Not only do you get access to a familiar SQL query language, you also get access to powerful tools such as Spark Streaming and the MLlib machine learning library.
Spark uses a special data structure called a DataFrame that represents data as named columns, similar to relational tables. You can query the data from Scala, Python, Java, and R. This enables you to perform powerful analysis of your data rather than just retrieving it. But it’s even more powerful when extracting data for use with the machine learning library. With MLlib, you can perform sophisticated analyses, detect credit card fraud, and process data coming from servers.
As with Drill, Spark SQL is compatible with a number of data formats, including some of the same ones that Drill supports: Parquet, JSON, and Hive. Spark SQL can handle multiple data sources similar to the way Drill can, but you can funnel the data into your machine learning systems mentioned earlier. This gives you a lot of power to analyze multiple data points, especially when combined with Spark Streaming. Spark SQL serves as a way to glue together different data sources and libraries into a powerful application.
Apache Drill
Apache Drill is a powerful database engine that also lets you use SQL for queries. You can use a number of data formats, including Parquet, MongoDB, MapR-DB, HDFS, MapR-FS, Amazon S3, Azure Blob Storage, Google Cloud Storage, Swift, NAS, and more.
You can use data from multiple data sources and join them without having to pull the data out, making Drill especially useful for business intelligence.
The ability to view multiple types of data, some of which have both strict and loose schema, as well as being able to allow for complex data models, might seem like a drag on performance. However, Drill uses schema discovery and a hierarchical columnar data model to treat data like a set of tables, independently of how the data is actually modeled.
Almost all existing BI tools, including Tableau, Qlik, MicroStrategy, Spotfire, SAS, and even Excel, can use Drill’s JDBC and ODBC drivers to connect to it. This makes Drill very useful for people already using BI and SQL databases to move up to big data workloads using tools they’re already familiar with.
Drill’s JDBC driver lets BI tools access Drill. JDBC lets developers query large datasets using Java. This has a similar advantage that using ANSI SQL does: lots of developers are already familiar with Java and can transfer their skills to Drill.
Easy Data Access in Drill
One of Drill’s biggest strengths is its ability to secure databases at the file level using views and impersonation.
Views within Drill are the same as those within relational databases. They allow a simplified query to hide the complexities of the underlying tables. Impersonation allows a user to access data as another user. This enables fine-grained access to the raw data when other members of your team should not be able to view sensitive or secure data.
Views and impersonation are beyond the scope of Apache Spark.
Conclusion
So which query engine should you choose? As always, it depends. If you’re mainly looking to query data quickly, even across multiple data sources, then you should look into Drill. If you want to go beyond querying data and work with data in more algorithmic ways, then Spark SQL might be for you. You can always test both out by playing around in your own Sandbox environment, which lets you play around with these powerful systems on your own machine.
