11 min read
In Case You Missed My Release Poem
Apache Drill's on a roll,
MapR Platform's convergence is what the bells toll,
3 great releases in a row,
Apache Drill on MapR is the best you shall ever know.
No, it's not just a poem. It's my release pitch: Apache Drill on MapR has improved significantly in the last 3 releases.
We just released (again!) Apache Drill 1.13 on MapR 6.0.1 as part of MEP 5.0 (MapR Expansion Pack). Continuing with the Drill 1.11 theme that we outlined in the November post here, we still continue to add new features and make improvements along those very same lines.
Here are the quick highlights:
The Kafka storage plugin for Drill was developed by the community in September 2017. The primary use case that we have heard is from app developers who want to be able to debug their application by inspecting the data present in the Kafka topics. SQL provides a quick and easy access to such analyses.
We tested the basic functionality of the plugin against the latest version of MapR Event Store, released as part of MapR 6.0.1, which supports the Kafka 1.0 API. We are releasing this functionality as a preview to get you to explore the functionality and better understand your production needs. The semantics is the same as the plugin for Kafka: it treats a topic as a table with each JSON event as a row. Drill can execute an ad hoc (not streaming) SQL query on multiple topics while treating them as tables. At this time, the plugin has several limitations, such as no filter pushdown or data locality awareness for performance.
At the time of writing this blog post, MapR is one of the very few companies in the world that has attempted to integrate a SQL query engine into its data platform that can query data residing in a single cluster that is spread across event streams, a NoSQL database, and a distributed file system. And all within a single query!
Figure 1: Drill on Kafka demo, recreated for MapR Event Streams. Multiple topics (tables) from a stream are being joined in a query that is being fired in DBeaver.
CPU Controls for Drill on YARN
In Drill 1.13, we have contributed back Drill-on-YARN code to open source, so that the community can benefit from this and make improvements to it. As part of that work, we have verified that it is possible to control CPU resources allocated to Drill both inside and outside YARN using Linux cgroups. The use case that we heard from our customers was the need to run multiple Drill clusters on the same hardware with different memory and CPU allocations. This would be further used to determine the chargeback cost for the tenant, which would be a function of the memory, CPU, and disk space used.
If cgroups is used for Drill inside YARN, all Drill clusters will use the same CPU control setting. If used outside YARN, each Drill cluster will use the CPU control setting associated with its process id. Furthermore, it is also possible to set soft limits, where CPU usage can exceed the value specified. This is particularly useful if you want excess compute capacity to be used for better performance. When hard limits are set, the CPU usage can never exceed the set value.
Run-Time Filter Pushdown on Subqueries Returning Primary Keys of a MapR Database JSON Table
One of the trends that we see amongst our customers is migrating their analytical workloads from relational databases to MapR Database, especially when the data is sparse and wastes space. Denormalization is preferred when query performance is important. MapR Database leverages secondary indexes to speed up query performance. However, there will be situations where some tables will have to be maintained separately, especially if they are being updated too often. For example, as shown in Figure 2, if there is a dimension table (lineitem l) that is being updated too often, compared to the associated fact table (orders o), then it should be kept separate. If that were not the case, then the cost of update would deteriorate performance.
Figure 2: Drill 1.13 implements filter pushdown into the subquery that retrieves the primary keys for the outer query using the IN operator.
A common query pattern is to apply a filter on the fact table and then use the results of the same to retrieve the records in the dimension table. A costly approach would be to join the two tables and then apply the filter. Alternatively, the filter can be pushed down into the fact table (orders o), and the primary keys are then used to retrieve the rows from the dimension table (lineitem l). Drill 1.12 uses the hash join approach, while Drill 1.13 pushes down the row key filter (results of the subquery) by using the IN operator. In the future, we plan to support the same for queries with equality conditions.
A sample of several test results are shown in Figure 3. The best results of 46% to 98.9% improvement in query speed were obtained with a selectivity threshold of 1%. Beyond that, performance deteriorated as the volume of random scans eat into the performance gains.
Figure 3: Test results at different selectivities for a subquery that returns the primary keys for the outer table.
One of the guiding principles of making Apache Drill enterprise grade on our data platform is to control the resource utilization. To that effect, we have contributed a new memory management framework (also called "batch sizing" in the community) that reads or processes data for the Drill operators in batches of fixed size instead of ever expanding rows. Our research revealed that the latter contributed to memory fragmentation and waste.
In this release, we are introducing batch sizing for the following memory-intensive operators:
To measure the impact of the memory savings for an individual operator, we showcase a test we conducted for the PARQUET SCAN operator. A simple parquet scan query is taken as a sample and tested with varying concurrency. The batch size was changed from 16MB, which had a default 64K records, to the following batch sizes:
The results are shown in Figure 4. In the next few releases, we plan to implement this framework for the other operators.
Figure 4: Batch sizing shows memory savings with minor performance improvement for the PARQUET SCAN operator for a simple scan query.
The performance improvements include:
Example queries are shown in Figure 5.
Figure 5: Example queries for the filter pushdown and partition pruning improvements.
The results of a simple test are shown in Figure 6.
Figure 6: Filter pushdown and partition pruning improvements for a simple filter query for all items that sold on a single day in a dataset spanning 7 years. The dataset was partitioned by shipping date.
Community News and Highlights
The Apache Drill community has made a comeback in the last 6 months. We are happy to see that the usage of Drill in open source has ramped up. The community has been very active on Twitter and Medium.
Figure 7: The new look Twitter feed and the all-new Medium blog.
If you have a question or need to join the discussion, please join the user mailing list here. Here are the highlights from the community contributions:
For the full list, please see the Apache Drill release notes.
It's Your Turn to Try
No release is ever complete without you giving it a try and seeing for yourself if it delivers the value you are looking for. With that, I invite you to give the new Apache Drill 1.13 on MapR 6.0.1 a try, and let me know your feedback.
The MapR Community pages for Apache Drill also have a lot of good material. Check them out here.
Stay ahead of the bleeding edge...get the best of Big Data in your inbox.