# EXPLAIN
#### 语法
EXPLAIN \[ ( option \[, ...\] ) \] statement
其中选项可以是以下选项之一：
FORMAT { TEXT \| GRAPHVIZ \| JSON }
TYPE { LOGICAL \| DISTRIBUTED \| VALIDATE \| IO}
#### 描述
显示一条语句的逻辑的或者分布式的执行计划，也可以用于校验一条SQL语句，或者是分析IO。
参数TYPE DISTRIBUTED用于显示分片后的计划（fragmented plan）。每一个fragment都会被一个或者多个节点执行。Fragments separation表示数据在两个节点之间进行交换。Fragment type表示一个fragment如何被执行以及数据在不同fragment之间怎样分布。
- SINGLE Fragment会在单个节点上执行。
  

- HASH Fragment会在固定数量的节点上执行，输入数据通过哈希函数进行分布。
  

- ROUND_ROBIN Fragment会在固定数量的节点上执行，片段在固定数量的节点上执行，输入数据以轮询方式进行分布。
  

- BROADCAST Fragment会在固定数量的节点上执行，输入数据被广播到所有的节点。
  

- SOURCE Fragment在访问输入分段的节点上执行。
  
 
#### 示例
- LOGICAL：
  ```
  CREATE TABLE testTable (regionkey int, name varchar);
  EXPLAIN SELECT regionkey, count(*) FROM testTable GROUP BY 1;
                                                               Query Plan                                                              
  -------------------------------------------------------------------------------------------------------------------------------------
   Output[regionkey, _col1]                                                                                                            
   │   Layout: [regionkey:integer, count:bigint]                                                                                       
   │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                                         
   │   _col1 := count                                                                                                                  
   └─ RemoteExchange[GATHER]                                                                                                           
      │   Layout: [regionkey:integer, count:bigint]                                                                                    
      │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                                      
      └─ Project[]                                                                                                                     
         │   Layout: [regionkey:integer, count:bigint]                                                                                 
         │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                                   
         └─ Aggregate(FINAL)[regionkey][$hashvalue]                                                                                    
            │   Layout: [regionkey:integer, $hashvalue:bigint, count:bigint]                                                           
            │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                                
            │   count := count("count_8")                                                                                              
            └─ LocalExchange[HASH][$hashvalue] ("regionkey")                                                                           
               │   Layout: [regionkey:integer, count_8:bigint, $hashvalue:bigint]                                                      
               │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                             
               └─ RemoteExchange[REPARTITION][$hashvalue_9]                                                                            
                  │   Layout: [regionkey:integer, count_8:bigint, $hashvalue_9:bigint]                                                 
                  │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                          
                  └─ Aggregate(PARTIAL)[regionkey][$hashvalue_10]                                                                      
                     │   Layout: [regionkey:integer, $hashvalue_10:bigint, count_8:bigint]                                             
                     │   count_8 := count(*)                                                                                           
                     └─ ScanProject[table = hive:default:testtable]                                                                    
                            Layout: [regionkey:integer, $hashvalue_10:bigint]                                                          
                            Estimates: {rows: 0 (0B), cpu: 0, memory: 0B, network: 0B}/{rows: 0 (0B), cpu: 0, memory: 0B, network: 0B} 
                            $hashvalue_10 := "combine_hash"(bigint '0', COALESCE("$operator$hash_code"("regionkey"), 0))               
                            regionkey := regionkey:int:0:REGULAR
  ```
  

- DISTRIBUTED：
  ```
  EXPLAIN (type DISTRIBUTED) SELECT regionkey, count(*) FROM testTable GROUP BY 1;
                                                        Query Plan                                                       
  -----------------------------------------------------------------------------------------------------------------------
   Fragment 0 [SINGLE]                                                                                                   
       Output layout: [regionkey, count]                                                                                 
       Output partitioning: SINGLE []                                                                                    
       Stage Execution Strategy: UNGROUPED_EXECUTION                                                                     
       Output[regionkey, _col1]                                                                                          
       │   Layout: [regionkey:integer, count:bigint]                                                                     
       │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                       
       │   _col1 := count                                                                                                
       └─ RemoteSource[1]                                                                                                
              Layout: [regionkey:integer, count:bigint]                                                                  
   Fragment 1 [HASH]                                                                                                     
       Output layout: [regionkey, count]                                                                                 
       Output partitioning: SINGLE []                                                                                    
       Stage Execution Strategy: UNGROUPED_EXECUTION                                                                     
       Project[]                                                                                                         
       │   Layout: [regionkey:integer, count:bigint]                                                                     
       │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                       
       └─ Aggregate(FINAL)[regionkey][$hashvalue]                                                                        
          │   Layout: [regionkey:integer, $hashvalue:bigint, count:bigint]                                               
          │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                    
          │   count := count("count_8")                                                                                  
          └─ LocalExchange[HASH][$hashvalue] ("regionkey")                                                               
             │   Layout: [regionkey:integer, count_8:bigint, $hashvalue:bigint]                                          
             │   Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}                                                 
             └─ RemoteSource[2]                                                                                          
                    Layout: [regionkey:integer, count_8:bigint, $hashvalue_9:bigint]                                     
   Fragment 2 [SOURCE]                                                                                                   
       Output layout: [regionkey, count_8, $hashvalue_10]                                                                
       Output partitioning: HASH [regionkey][$hashvalue_10]                                                              
       Stage Execution Strategy: UNGROUPED_EXECUTION                                                                     
       Aggregate(PARTIAL)[regionkey][$hashvalue_10]                                                                      
       │   Layout: [regionkey:integer, $hashvalue_10:bigint, count_8:bigint]                                             
       │   count_8 := count(*)                                                                                           
       └─ ScanProject[table = hive:default:testtable, grouped = false]                                                   
              Layout: [regionkey:integer, $hashvalue_10:bigint]                                                          
              Estimates: {rows: 0 (0B), cpu: 0, memory: 0B, network: 0B}/{rows: 0 (0B), cpu: 0, memory: 0B, network: 0B} 
              $hashvalue_10 := "combine_hash"(bigint '0', COALESCE("$operator$hash_code"("regionkey"), 0))               
              regionkey := regionkey:int:0:REGULAR
  ```
  

- VALIDATE：
  ```
  EXPLAIN (TYPE VALIDATE) SELECT id, count(*) FROM testTable GROUP BY 1;
   Valid
  -------
   true
  ```
  

- IO：
  ```
  EXPLAIN (TYPE IO, FORMAT JSON) SELECT regionkey , count(*) FROM testTable GROUP BY 1;
             Query Plan            
  ---------------------------------
   {                               
     "inputTableColumnInfos" : [ { 
       "table" : {                 
         "catalog" : "hive",       
         "schemaTable" : {         
           "schema" : "default",   
           "table" : "testtable"   
         }                         
       },                          
       "columnConstraints" : [ ]   
     } ]                           
   }
  ```