|
| 1 | +<!--- |
| 2 | + Licensed to the Apache Software Foundation (ASF) under one |
| 3 | + or more contributor license agreements. See the NOTICE file |
| 4 | + distributed with this work for additional information |
| 5 | + regarding copyright ownership. The ASF licenses this file |
| 6 | + to you under the Apache License, Version 2.0 (the |
| 7 | + "License"); you may not use this file except in compliance |
| 8 | + with the License. You may obtain a copy of the License at |
| 9 | + http://www.apache.org/licenses/LICENSE-2.0 |
| 10 | + Unless required by applicable law or agreed to in writing, |
| 11 | + software distributed under the License is distributed on an |
| 12 | + "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| 13 | + KIND, either express or implied. See the License for the |
| 14 | + specific language governing permissions and limitations |
| 15 | + under the License. |
| 16 | +--> |
| 17 | + |
| 18 | +# DataFusion Query Plans |
| 19 | + |
| 20 | +DataFusion's `DataFrame` is a wrapper around a query plan. In this chapter we will learn how to view |
| 21 | +logical and physical query plans for DataFrames. |
| 22 | + |
| 23 | +## Sample Data |
| 24 | + |
| 25 | +Let's go ahead and create a simple DataFrame. You can do this in the Python shell or in a notebook. |
| 26 | + |
| 27 | +```python |
| 28 | +from datafusion import SessionContext |
| 29 | + |
| 30 | +ctx = SessionContext() |
| 31 | + |
| 32 | +df = ctx.from_pydict({"a": [1, 2, 3, 1], "b": [4, 5, 6, 7]}, name="my_table") |
| 33 | +``` |
| 34 | + |
| 35 | +## Logical Plan |
| 36 | + |
| 37 | +Next, let's look at the logical plan for this dataframe. |
| 38 | + |
| 39 | +```python |
| 40 | +>>> df.logical_plan() |
| 41 | +TableScan: my_table |
| 42 | +``` |
| 43 | + |
| 44 | +The logical plan here consists of a single `TableScan` operator. Let's make a more interesting plan by creating a new |
| 45 | +`DataFrame` representing an aggregate query with a filter. |
| 46 | + |
| 47 | +```python |
| 48 | +>>> df = ctx.sql("SELECT a, sum(b) FROM my_table WHERE a < 3 GROUP BY a") |
| 49 | +``` |
| 50 | + |
| 51 | +When we view the plan for this `DataFrame` we can see that there are now four operators in the plan, each |
| 52 | +representing a logical transformation of the data. We start with a `TableScan` to read the data, followed by |
| 53 | +a `Filter` to filter out rows that do not match the filter expression, then an `Aggregate` is performed. Finally, |
| 54 | +a `Projection` is applied to ensure that the order of the final columns matches the `SELECT` part of the SQL query. |
| 55 | + |
| 56 | +```python |
| 57 | +>>> df.logical_plan() |
| 58 | +Projection: my_table.a, SUM(my_table.b) |
| 59 | + Aggregate: groupBy=[[my_table.a]], aggr=[[SUM(my_table.b)]] |
| 60 | + Filter: my_table.a < Int64(3) |
| 61 | + TableScan: my_table |
| 62 | +``` |
| 63 | + |
| 64 | +## Optimized Logical Plan |
| 65 | + |
| 66 | +DataFusion has a powerful query optimizer which will rewrite query plans to make them more efficient before they are |
| 67 | +executed. We can view the output of the optimized by viewint the optimized logical plan. |
| 68 | + |
| 69 | +```python |
| 70 | +>>> df.optimized_logical_plan() |
| 71 | +Aggregate: groupBy=[[my_table.a]], aggr=[[SUM(my_table.b)]] |
| 72 | + Filter: my_table.a < Int64(3) |
| 73 | + TableScan: my_table projection=[a, b] |
| 74 | +``` |
| 75 | + |
| 76 | +We can see that there are two key differences compared to the unoptimized logical plan: |
| 77 | + |
| 78 | +- The `Projection` has been removed because it was redundant in this case (the output of the `Aggregatge` plan |
| 79 | + already had the columns in the correct order). |
| 80 | +- The `TableScan` now has a projection pushed down so that it only reads the columns required to be able to execute |
| 81 | + the query. In this case the table only has two columns and we referenced them both in the query, but this optimization |
| 82 | + can be very effective in real-world queries against large tables. |
| 83 | + |
| 84 | +## Physical Plan |
| 85 | + |
| 86 | +Logical plans provide a representation of "what" the query should do it. Physical plans explain "how" the query |
| 87 | +should be executed. |
| 88 | + |
| 89 | +We can view the physical plan (also known as an execution plan) using the `execution_plan` method. |
| 90 | + |
| 91 | +```python |
| 92 | +>>> df.execution_plan() |
| 93 | +AggregateExec: mode=FinalPartitioned, gby=[a@0 as a], aggr=[SUM(my_table.b)] |
| 94 | + CoalesceBatchesExec: target_batch_size=8192 |
| 95 | + RepartitionExec: partitioning=Hash([Column { name: "a", index: 0 }], 48), input_partitions=48 |
| 96 | + AggregateExec: mode=Partial, gby=[a@0 as a], aggr=[SUM(my_table.b)] |
| 97 | + CoalesceBatchesExec: target_batch_size=8192 |
| 98 | + FilterExec: a@0 < 3 |
| 99 | + RepartitionExec: partitioning=RoundRobinBatch(48), input_partitions=1 |
| 100 | + MemoryExec: partitions=1, partition_sizes=[1] |
| 101 | +``` |
| 102 | + |
| 103 | +The `TableScan` has now been replaced by a more specific `MemoryExec` for scanning the in-memory data. If we were |
| 104 | +querying a CSV file on disk then we would expect to see a `CsvExec` instead. |
| 105 | + |
| 106 | +This plan has additional operators that were not in the logical plan: |
| 107 | + |
| 108 | +- `RepartionExec` has been added so that the data can be split into partitions and processed in parallel using |
| 109 | + multiple cores. |
| 110 | +- `CoalesceBatchesExec` will combine small batches into larger batches to ensure that processing remains efficient. |
| 111 | + |
| 112 | +The `Aggregate` operator now appears twice. This is because aggregates are performed in a two step process. Data is |
| 113 | +aggregated within each partition in parallel and then those results (which could contain duplicate grouping keys) are |
| 114 | +combined and the aggregate operations is applied again. |
| 115 | + |
| 116 | +## Creating Query Plan Diagrams |
| 117 | + |
| 118 | +DataFusion supports generating query plan diagrams in [DOT format](<https://en.wikipedia.org/wiki/DOT_(graph_description_language)>). |
| 119 | + |
| 120 | +DOT is a language for describing graphs and there are open source tools such as GraphViz that can render diagrams |
| 121 | +from DOT files. |
| 122 | + |
| 123 | +We can use the following code to generate a DOT file for a logical query plan. |
| 124 | + |
| 125 | +```python |
| 126 | +>>> diagram = df.logical_plan().display_graphviz() |
| 127 | +>>> with open('plan.dot', 'w') as f: |
| 128 | +>>> f.write(diagram) |
| 129 | +``` |
| 130 | + |
| 131 | +If we view the view, we will see the following content. |
| 132 | + |
| 133 | +``` |
| 134 | +// Begin DataFusion GraphViz Plan (see https://graphviz.org) |
| 135 | +digraph { |
| 136 | + subgraph cluster_1 |
| 137 | + { |
| 138 | + graph[label="LogicalPlan"] |
| 139 | + 2[shape=box label="Projection: my_table.a, SUM(my_table.b)"] |
| 140 | + 3[shape=box label="Aggregate: groupBy=[[my_table.a]], aggr=[[SUM(my_table.b)]]"] |
| 141 | + 2 -> 3 [arrowhead=none, arrowtail=normal, dir=back] |
| 142 | + 4[shape=box label="Filter: my_table.a < Int64(3)"] |
| 143 | + 3 -> 4 [arrowhead=none, arrowtail=normal, dir=back] |
| 144 | + 5[shape=box label="TableScan: my_table"] |
| 145 | + 4 -> 5 [arrowhead=none, arrowtail=normal, dir=back] |
| 146 | + } |
| 147 | + subgraph cluster_6 |
| 148 | + { |
| 149 | + graph[label="Detailed LogicalPlan"] |
| 150 | + 7[shape=box label="Projection: my_table.a, SUM(my_table.b)\nSchema: [a:Int64;N, SUM(my_table.b):Int64;N]"] |
| 151 | + 8[shape=box label="Aggregate: groupBy=[[my_table.a]], aggr=[[SUM(my_table.b)]]\nSchema: [a:Int64;N, SUM(my_table.b):Int64;N]"] |
| 152 | + 7 -> 8 [arrowhead=none, arrowtail=normal, dir=back] |
| 153 | + 9[shape=box label="Filter: my_table.a < Int64(3)\nSchema: [a:Int64;N, b:Int64;N]"] |
| 154 | + 8 -> 9 [arrowhead=none, arrowtail=normal, dir=back] |
| 155 | + 10[shape=box label="TableScan: my_table\nSchema: [a:Int64;N, b:Int64;N]"] |
| 156 | + 9 -> 10 [arrowhead=none, arrowtail=normal, dir=back] |
| 157 | + } |
| 158 | +} |
| 159 | +// End DataFusion GraphViz Plan |
| 160 | +``` |
| 161 | + |
| 162 | +We can use GraphViz from the command-line to convert this DOT file into an image. |
| 163 | + |
| 164 | +```bash |
| 165 | +dot -Tsvg plan.dot > plan.svg |
| 166 | +``` |
| 167 | + |
| 168 | +This generates the following diagram: |
| 169 | + |
| 170 | + |
0 commit comments