title: How should I configure Spark to correctly prune Hive Metastore partitions?
body: <p>I'm having an issues when applying partition filters to Spark (v2.0.2/2.1.1) DataFrames that read from a Hive (v2.1.0) table with over 30,000 partitions. I would like to know what the recommended approach is and what, if anything, I'm doing incorrectly as the current behaviour is a source of large performance an reliability issues.</p>
  
  <p>To enable pruning, I am using the following Spark/Hive property:</p>
  
  <pre><code>--conf spark.sql.hive.metastorePartitionPruning=true
  </code></pre>
  
  <p>When running a query in spark-shell I can see the partition fetch take place with an invocation to <a href="https://hive.apache.org/javadocs/r2.1.1/api/org/apache/hadoop/hive/metastore/api/ThriftHiveMetastore.Iface.html#get_partitions-java.lang.String-java.lang.String-short-" rel="nofollow noreferrer"><code>ThriftHiveMetastore.Iface.get_partitions</code></a>, but this unexpectedly occurs without any filtering:</p>
  
  <pre><code>val myTable = spark.table("db.table")
  val myTableData = myTable
    .filter("local_date = '2017-09-01' or local_date = '2017-09-02'")
    .cache
  
  // The HMS call invoked is:
  // #get_partitions('db', 'table', -1)
  </code></pre>
  
  <p>If I use a more simplistic filter, partitions are filtered as desired:</p>
  
  <pre><code>val myTableData = myTable
    .filter("local_date = '2017-09-01'")
    .cache
  
  // The HMS call invoked is:
  // #get_partitions_by_filter(
  //   'db', 'table',
  //   'local_date = "2017-09-01"',
  //   -1
  // )
  </code></pre>
  
  <p>The filtering also works correctly if I rewrite the filter to use range operators instead of simply checking for equality:</p>
  
  <pre><code>val myTableData = myTable
    .filter("local_date &gt;= '2017-09-01' and local_date &lt;= '2017-09-02'")
    .cache
  
  // The HMS call invoked is:
  // #get_partitions_by_filter(
  //   'db', 'table',
  //   'local_date &gt;= '2017-09-01' and local_date &lt;= '2017-09-02'',
  //   -1
  // )
  </code></pre>
  
  <p>In our case, this behaviour is problematic from a performance perspective; call times are in the region of 4 minutes versus 1 second when correctly filtered. Additionally, routinely loading large volumes of<code>Partition</code> objects onto the heap per query ultimately leads to memory issues in the metastore service.</p>
  
  <p>It seems as though there is a bug around the parsing and interpretation of certain types of filter constructs, however I've not been able to find a relevant issue in the Spark JIRA. Is there a preferred approach or specific Spark version where filters are correctly applied for all filter variants? Or must I use specific forms (e.g. range operators) when constructing filters? If so, is this limitation documented anywhere?</p>