![\"Spark](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/05\/Spark-SQL-Optimization-2.jpg\")
<\/a>Spark SQL Optimization – Understanding the Catalyst Optimizer<\/p><\/div>\n
2. Introduction to Apache Spark SQL Optimization<\/h2>\n
“The term optimization<\/strong> refers to a process in which a system is modified in such a way that it work more efficiently or it uses fewer resources.” There are two purposes behind Catalyst\u2019s extensible design:<\/p>\n Catalyst optimizer makes use of standard features of Scala programming<\/a> like pattern matching. In the depth, Catalyst contains the tree<\/strong> and the set of rules<\/strong> to manipulate the tree. There are specific libraries to process relational queries. There are various rule sets which handle different phases of query execution like analysis<\/em>, query optimization, physical planning,<\/em> and code generation<\/em> to compile parts of queries to Java bytecode. Let’s discuss the tree and rules is detail-<\/p>\n A tree is the main data type in the catalyst. A tree contains node object. For each node, there is a node. A node can have one or more children. New nodes are defined as subclasses of TreeNode class. These objects are immutable in nature. The objects can be manipulated using functional transformation. See RDD Transformations and Actions Guide<\/a> for more details about Functional transformations. spark-sql-catalyst-optimizer-tree-example<\/p><\/div>\n Then a tree will look like-<\/p>\n We can manipulate tree using rules<\/strong>. We can define rules as a function from one tree to another tree. With rule we can run arbitrary code on input tree, the common approach to use a pattern matching function and replace subtree with a specific structure. In a tree with the help of transform function<\/strong>, we can recursively apply pattern matching on all the node of a tree. We get the pattern that matches each pattern to a result. After the detailed introduction of Apache Spark SQL catalyst optimizer, now we will discuss the Spark SQL query execution phases. In four phases we use Catalyst\u2019s general tree transformation framework:<\/p>\n Phases-of-Spark-SQL-Execution-plan<\/p><\/div>\n Spark SQL Optimization starts from relation to be computed. It is computed either from abstract syntax tree (AST)<\/strong> returned by SQL parser<\/strong> or dataframe<\/a> object created using API. Both may contain unresolved attribute references or relations. By unresolved attribute, it means we don\u2019t know its type or have not matched it to an input table. Spark SQL make use of Catalyst rules and a Catalog object that track data in all data sources to resolve these attributes. It starts by creating an unresolved logical plan, and then apply the following steps:<\/p>\n In this phase of Spark SQL optimization, the standard rule-based optimization is applied to the logical plan. It includes constant folding<\/strong>, predicate pushdown<\/strong>, projection pruning<\/strong> and other rules. It became extremely easy to add a rule for various situations.<\/p>\n There are about 500 lines of code in the physical planning rules. In this phase, one or more physical plan is formed from the logical plan, using physical operator matches the Spark execution engine. And it selects the plan using the cost model. It uses Cost-based optimization only to select join algorithms. For small relation SQL uses broadcast join, the framework supports broader use of cost-based optimization. It can estimate the cost recursively for the whole tree using the rule. The final phase of Spark SQL optimization is code generation. It involves generating Java bytecode to run on each machine. Catalyst uses the special feature of Scala language, “Quasiquotes<\/strong>” to make code generation easier because it is very tough to build code generation engines. Quasiquotes lets the programmatic construction of abstract syntax trees (ASTs) in the Scala language, which can then be fed to the Scala compiler at runtime to generate bytecode. With the help of a catalyst, we can transform a tree representing an expression in SQL to an AST for Scala code to evaluate that expression, and then compile and run the generated code.<\/p>\n Hence, Spark SQL optimization enhances\u00a0the productivity of developers and the performance of the queries that they write. A good query optimizer automatically rewrites relational queries to execute more efficiently, using techniques such as filtering data early, utilizing available indexes, and even ensuring different data sources are joined in the most efficient order. 1. Objective The goal of this Apache Spark tutorial is to describe the Spark SQL Optimization framework and how it allows developers to express complex query transformations in very few lines of code. we...<\/p>\n","protected":false},"author":6,"featured_media":42324,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[900,917,13036,13115,13120],"class_list":["post-2611","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spark","tag-apache-spark-catalyst-optimizer","tag-apache-spark-execution-plan","tag-spark-catalyst-framework","tag-spark-sql-catalyst-optimizer","tag-spark-sql-optimization"],"yoast_head":"\n
\nSpark SQL<\/a><\/strong> is the most technically involved component of Apache Spark. Spark SQL deals with both SQL queries and DataFrame API. In the depth of Spark SQL there lies a catalyst optimizer<\/strong>.\u00a0Catalyst optimization allows some advanced programming language features that allow you to build an extensible query optimizer.
\nA new extensible optimizer called Catalyst emerged to implement Spark SQL. This optimizer is based on functional programming construct in Scala<\/a><\/strong>.
\nCatalyst Optimizer supports both rule-based<\/strong> and cost-based<\/strong> optimization. In rule-based optimization<\/em> the rule based optimizer use set of rule to determine how to execute the query. While the cost based optimization<\/em> finds the most suitable way to carry out SQL statement. In cost-based optimization, multiple plans are generated using rules and then their cost is computed.<\/p>\n3. What is the need of Catalyst Optimizer?<\/h2>\n
\n
4. Fundamentals of Catalyst Optimizer<\/h2>\n
4.1. Trees<\/h3>\n
\nFor example, if we have three node classes: worth<\/strong>, attribute<\/strong>, and sub<\/strong> in which-<\/p>\n\n
![\"spark-sql-catalyst-optimizer-tree-example\"](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/05\/spark-sql-catalyst-optimizer-tree-example.jpg\")
<\/a>4.2. Rules<\/h3>\n
\nFor example-
\ntree.transform {case Sub(worth(c1),worth(c2)) => worth(c1+c2) }<\/em>
\nThe expression that is passed during pattern matching to transform is a partial function. By partial function, it means it only needs to match to a subset of all possible input trees. Catalyst will see, to which part of a tree the given rule applies, and will automatically skip over the tree that does not match. With the same transform call, the rule can match multiple patterns.
\nFor example-
\n tree.transform {<\/em>
\ncase Sub(worth(c1), worth(c2)) =>worth(c1-c2)<\/em>
\ncase Sub(left , worth(0)) => left<\/em>
\ncase Sub(worth(0), right) => right<\/em>
\n}<\/em>
\nTo fully transform a tree, rule may be needed to execute multiple time.
\nCatalyst work by grouping rules into batches and these batches are executed until a fixed point is achieved. Fixed point is a point after which tree stops changing even after applying rules.<\/p>\n5. Spark SQL Execution Plan<\/h2>\n
\n
![\"Spark-SQL-execution-query-plan\"](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2017\/05\/Spark-SQL-execution-query-plan.jpg\")
<\/a>5.1. Analysis<\/h3>\n
\n
5.2. Logical Optimization<\/h3>\n
5.3. Physical Planning<\/h3>\n
\nRule-based physical optimization, such as pipelining projections or filters into one Spark map <\/em>Operation is also carried out by the physical planner. Apart from this, it can also push operations from the logical plan into data sources that support predicate or projection pushdown.<\/p>\n5.4. Code Generation<\/h3>\n
6. Conclusion<\/h2>\n
\nBy performing these transformations, the optimizer improves the execution times of relational queries and frees the developer from focusing on the semantics of their application instead of its performance.
\nCatalyst makes use of Scala\u2019s powerful features such as pattern matching and runtime metaprogramming to allow developers to concisely specify complex relational optimizations.
\nSee Also-<\/strong><\/p>\n\n