apache drill
DESCRIPTION
Apache Drill (http://incubator.apache.org/drill/) is a distributed system for interactive analysis of large-scale datasets, inspired by Google’s Dremel technology. It is designed to scale to thousands of servers and able to process Petabytes of data in seconds. Since its inception in mid 2012, Apache Drill has gained widespread interest in the community, attracting hundreds of interested individuals and companies. In the talk we discuss how Apache Drill enables ad-hoc interactive query at scale, walking through typical use cases and delve into Drill's architecture, the data flow and query languages as well as data sources supported.TRANSCRIPT
![Page 1: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/1.jpg)
Apache Drill a interactive, ad-hoc query system for large-scale datasets
Michael Hausenblas, Chief Data Engineer EMEA, MapR Big Data User Group Stuttgart, 2013-05-16
![Page 2: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/2.jpg)
Which workloads do you encounter in your environment?
http
://ww
w.flickr.co
m/p
ho
tos/kevin
om
ara/28
66
64
83
30
/ licensed
un
der C
C B
Y-NC
-ND
2.0
![Page 3: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/3.jpg)
Batch processing
… for recurring tasks such as large-scale data mining, ETL offloading/data-warehousing for the batch layer in Lambda architecture
![Page 4: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/4.jpg)
OLTP
… user-facing eCommerce transactions, real-time messaging at scale (FB), time-series processing, etc. for the serving layer in Lambda architecture
![Page 5: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/5.jpg)
Stream processing
… in order to handle stream sources such as social media feeds or sensor data (mobile phones, RFID, weather stations, etc.) for the speed layer in Lambda architecture
![Page 6: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/6.jpg)
Search/Information Retrieval
… retrieval of items from unstructured documents (plain text, etc.), semi-structured data formats (JSON, etc.), as well as data stores (MongoDB, CouchDB, etc.)
![Page 7: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/7.jpg)
http://www.flickr.com/photos/9479603@N02/4144121838/ licensed under CC BY-NC-ND 2.0
But what about interactive ad-hoc query at scale?
![Page 8: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/8.jpg)
Impala
Interactive Query (?)
low-latency
![Page 9: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/9.jpg)
Use Case: Marketing Campaign
• Jane, a marketing analyst
• Determine target segments
• Data from different sources
![Page 10: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/10.jpg)
Use Case: Logistics
• Supplier tracking and performance
• Queries
– Shipments from supplier ‘ACM’ in last 24h
– Shipments in region ‘US’ not from ‘ACM’
SUPPLIER_ID NAME REGION
ACM ACME Corp US
GAL GotALot Inc US
BAP Bits and Pieces Ltd Europe
ZUP Zu Pli Asia
{
"shipment": 100123,
"supplier": "ACM",
“timestamp": "2013-02-01",
"description": ”first delivery today”
},
{
"shipment": 100124,
"supplier": "BAP",
"timestamp": "2013-02-02",
"description": "hope you enjoy it”
}
…
![Page 11: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/11.jpg)
Use Case: Crime Detection
• Online purchases
• Fraud, bilking, etc.
• Batch-generated overview
• Modes
– Explorative
– Alerts
![Page 12: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/12.jpg)
Requirements
• Support for different data sources
• Support for different query interfaces
• Low-latency/real-time
• Ad-hoc queries
• Scalable, reliable
![Page 13: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/13.jpg)
And now for something completely different …
![Page 14: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/14.jpg)
Google’s Dremel
http://research.google.com/pubs/pub36632.html
Sergey Melnik, Andrey Gubarev, Jing Jing Long, Geoffrey Romer, Shiva Shivakumar, Matt Tolton, Theo Vassilakis, Proc. of the 36th Int'l Conf on Very Large Data Bases (2010), pp. 330-339
Dremel is a scalable, interactive ad-hoc query system for
analysis of read-only nested data. By combining multi-
level execution trees and columnar data layout, it is
capable of running aggregation queries over trillion-row
tables in seconds. The system scales to thousands of
CPUs and petabytes of data, and has thousands of users
at Google.
…
“ “
Dremel is a scalable, interactive ad-hoc query system for
analysis of read-only nested data. By combining multi-
level execution trees and columnar data layout, it is
capable of running aggregation queries over trillion-row
tables in seconds. The system scales to thousands of
CPUs and petabytes of data, and has thousands of users
at Google.
…
![Page 15: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/15.jpg)
Google’s Dremel
multi-level execution trees
columnar data layout
![Page 16: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/16.jpg)
Google’s Dremel
nested data + schema column-striped representation
map nested data to tables
![Page 17: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/17.jpg)
Google’s Dremel
experiments:
datasets & query performance
![Page 18: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/18.jpg)
Back to Apache Drill …
![Page 19: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/19.jpg)
Apache Drill–key facts
• Inspired by Google’s Dremel
• Standard SQL 2003 support
• Plug-able data sources
• Nested data is a first-class citizen
• Schema is optional
• Community driven, open, 100’s involved
![Page 20: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/20.jpg)
High-level Architecture
![Page 21: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/21.jpg)
Principled Query Execution
Source Query Parser
Logical Plan Optimizer
Physical Plan Execution
SQL 2003 DrQL MongoQL DSL
scanner API Topology CF etc.
query: [
{
@id: "log",
op: "sequence",
do: [
{
op: "scan",
source: “logs”
},
{
op: "filter",
condition:
"x > 3”
},
parser API
![Page 22: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/22.jpg)
Wire-level Architecture
• Each node: Drillbit - maximize data locality
• Co-ordination, query planning, execution, etc, are distributed
• Any node can act as endpoint for a query—foreman
Storage Process
Drillbit
node
Storage Process
Drillbit
node
Storage Process
Drillbit
node
Storage Process
Drillbit
node
![Page 23: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/23.jpg)
Wire-level Architecture
• Curator/Zookeeper for ephemeral cluster membership info
• Distributed cache (Hazelcast) for metadata, locality information, etc.
Curator/Zk
Distributed Cache
Storage Process
Drillbit
node
Storage Process
Drillbit
node
Storage Process
Drillbit
node
Storage Process
Drillbit
node
Distributed Cache Distributed Cache Distributed Cache
![Page 24: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/24.jpg)
Wire-level Architecture
• Originating Drillbit acts as foreman: manages query execution, scheduling, locality information, etc.
• Streaming data communication avoiding SerDe
Curator/Zk
Distributed Cache
Storage Process
Drillbit
node
Storage Process
Drillbit
node
Storage Process
Drillbit
node
Storage Process
Drillbit
node
Distributed Cache Distributed Cache Distributed Cache
![Page 25: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/25.jpg)
Wire-level Architecture Foreman turns into root of the multi-level execution tree, leafs activate their storage engine interface.
node
node node
Curator/Zk
![Page 26: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/26.jpg)
Key features
• Full SQL – ANSI SQL 2003
• Nested Data as first class citizen
• Optional Schema
• Extensibility Points …
![Page 27: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/27.jpg)
Extensibility Points
• Source query parser API
• Custom operators, UDF logical plan
• Serving tree, CF, topology physical plan/optimizer
• Data sources &formats scanner API
Source Query Parser
Logical Plan Optimizer
Physical Plan Execution
![Page 28: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/28.jpg)
… and Hadoop?
• HDFS can be a data source
• Complementary use cases*
• … use Apache Drill
– Find record with specified condition
– Aggregation under dynamic conditions
• … use MapReduce
– Data mining with multiple iterations
– ETL
*) h
ttps://clo
ud
.goo
gle.com
/files/BigQ
ueryTe
chn
icalWP
.pd
f
![Page 29: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/29.jpg)
Basic Demo
https://cwiki.apache.org/confluence/display/DRILL/Demo+HowTo
{
"id": "0001",
"type": "donut",
”ppu": 0.55,
"batters":
{
"batter”:
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
…
data source: donuts.json
query:[ {
op:"sequence",
do:[
{
op: "scan",
ref: "donuts",
source: "local-logs",
selection: {data: "activity"}
},
{
op: "filter",
expr: "donuts.ppu < 2.00"
},
…
logical plan: simple_plan.json
result: out.json
{
"sales" : 700.0,
"typeCount" : 1,
"quantity" : 700,
"ppu" : 1.0
}
{
"sales" : 109.71,
"typeCount" : 2,
"quantity" : 159,
"ppu" : 0.69
}
{
"sales" : 184.25,
"typeCount" : 2,
"quantity" : 335,
"ppu" : 0.55
}
![Page 30: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/30.jpg)
BE A PART OF IT!
![Page 31: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/31.jpg)
Status
• Heavy development by multiple organizations
• Available
– Logical plan (ADSP)
– Reference interpreter
– Basic SQL parser
– Basic demo
![Page 32: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/32.jpg)
Status
May 2013
• Full SQL support (+JDBC)
• Physical plan
• In-memory compressed data interfaces
• Distributed execution
![Page 33: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/33.jpg)
Status
May 2013
• HBase and MySQL storage engine
• WebUI client
![Page 34: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/34.jpg)
Contributing
Contributions appreciated (not only code drops) …
• Test data & test queries
• Use case scenarios (textual/SQL queries)
• Documentation
• Further schedule
– Alpha Q2
– Beta Q3
![Page 35: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/35.jpg)
Kudos to …
• Julian Hyde, Pentaho
• Lisen Mu, XingCloud
• Tim Chen, Microsoft
• Chris Merrick, RJMetrics
• David Alves, UT Austin
• Sree Vaadi, SSS/NGData
• Jacques Nadeau, MapR
• Ted Dunning, MapR
![Page 36: Apache Drill](https://reader034.vdocuments.us/reader034/viewer/2022052618/554fa0c0b4c905ad218b4a04/html5/thumbnails/36.jpg)
Engage!
• Follow @ApacheDrill on Twitter
• Sign up at mailing lists (user | dev) http://incubator.apache.org/drill/mailing-lists.html
• Standing G+ hangouts every Tuesday at 5pm GMT http://j.mp/apache-drill-hangouts
• Keep an eye on http://drill-user.org/