SQL on Big Data using Optiq
Download as PPT, PDF0 likes3,065 views
The document discusses 'SQL on Big Data' with a focus on technologies such as Optiq, Apache Drill, and Cascading, emphasizing their capabilities for querying large datasets and supporting distributed data processing. It highlights the evolution and challenges of big data management, mentioning the need for efficient data querying while maintaining flexibility and scaling ability. The presentation covers various architecture components and optimization techniques for integrating SQL with big data systems.
SQL on Big Data using Optiq
- 1. SQL on Big Data using Optiq @julianhyde Real-time Big Data Meetup at RichRelevance April 2013
- 2. What is “SQL on Big Data?” □ “Open-source Teradata” □ SQL generator for Map-Reduce □ ETL (Extract-Transform Load) □ Scalable transaction processing □ Querying nested data sets □ Querying documents & populating databases □ Continuous query/streaming (Check one or more.)
- 3. Revolution & counter-revolution “Big Data” was a revolution in data management. Lots of broken things got fixed (unlimited scale, data anywhere & any format, late schema, flexible queries). Some useful things got broken (standard interface, data independence, central control). “In 5 years everyone will be using Hadoop and they won't even know it.” – me, a few years ago
- 4. Conventional DBMS architecture JDBC client JDBC server SQL parser / validator Metadata Query optimizer Data-flow operators Data Data
- 5. Optiq architecture JDBC client JDBC server Optional SQL parser / Metadata validator SPI Core Query Pluggable optimizer rules 3rd 3rd Pluggable party party ops ops 3rd party 3rd party data data
- 6. SELECT p.product_name, COUNT(*) AS c Expression FROM splunk.splunk AS s JOIN mysql.products AS p tree ON s.product_id = p.product_id WHERE s.action = 'purchase' GROUP BY p.product_name ORDER BY c DESC Splunk Table: splunk Key: product_name Key: product_id Agg: count Condition: Key: c DESC action = 'purchase' scan join MySQL filter group sort scan Table: products
- 7. SELECT p.product_name, COUNT(*) AS c Expression FROM splunk.splunk AS s JOIN mysql.products AS p tree (optimized) ON s.product_id = p.product_id WHERE s.action = 'purchase' GROUP BY p.product_name ORDER BY c DESC Splunk Condition: Table: splunk action = 'purchase' Key: product_name Agg: count Key: c DESC Key: product_id scan filter MySQL join group sort scan Table: products
- 8. Apache Drill “Apache Drill (incubating) is a distributed system for interactive analysis of large-scale datasets, based on Google's Dremel. Its goal is to efficiently process nested data. It is a design goal to scale to 10,000 servers or more and to be able to process petabyes of data and trillions of records in seconds.” Data model: JSON, late-binding Optiq: SQL → logical plan (current) Logical → physical plan (proposed)
- 9. Cascading Lingual “Cascading is the de facto Java API for creating complex data processing workloads and the engine underneath Scalding, Cascalog, and others.” Lingual uses Optiq to translate SQL onto Cascading flows SQL is “yet another DSL” for Cascading Just released!
- 11. Mondrian next-gen architecture mondrian mondrian mondrian Optiq provides SQL view onto hybrid SQL + NoSQL + optiq optiq optiq in-memory store cache data cache grid cache In-memory tables (query results, planned & on-the-fly materializations) control control control cache cache cache Raw data + summarized / HDFS MongoDB DBMS projected / sorted / re-organized data. Partitions.
- 12. Summary: Data independence Logical & physical data models Requires & allows query optimization Allows you (or the system) to re-organize data Query federation, data movement, caching SQL interface for humans & machines Optiq lets you add rules to optimize better
- 13. Thank you! @julianhyde optiq https://github.com/julianhyde/optiq drill http://incubator.apache.org/drill/ lingual http://www.cascading.org/lingual/ mondrian http://mondrian.pentaho.com slides https://github.com/julianhyde/share/tree/master/slides