title: PySpark - Hive Context Does Not Return Results but SQL Context Does for Similar Query
body: <p>I noticed a huge difference in performance when I run HiveContext versus SQLContext within PySpark for comparable queries</p>
  
  <h1>VERSIONS/CONFIGURATION</h1>
  
  <ul>
  <li><em>Spark 1.3.1</em>(also tried <em>Spark 1.5.1</em>) </li>
  <li><em>Hadoop 2.6</em> (on <em>CDH 5.4.0</em>)</li>
  <li>pyspark --master yarn --num-executors 5 --executor-memory 10g --driver-memory 4g --driver-cores 4</li>
  </ul>
  
  <h1>TABLE INFORMATION</h1>
  
  <ul>
  <li>database.table has over 2k partitions</li>
  <li>database.table is partitioned on field1 (used in where clause)</li>
  </ul>
  
  <h1>HIVECONTEXT IMPLEMENTATION</h1>
  
  <pre><code>from pyspark.sql import SQLContext
  sqlContext = HiveContext(sc) 
  qry = "select count(*) from database.table a where a.field1 = 'ABCD'"
  results = sqlContext.sql(qry).collect()
  </code></pre>
  
  <ul>
  <li>Takes an indeterminate amount of time - I had to stop the execution of the query because it quickly sucked up over 50% of the system resources on the edgenode that I executed the query on.</li>
  </ul>
  
  <h1>SQLCONTEXT IMPLEMENTATION</h1>
  
  <pre><code>from pyspark.sql import SQLContext
  sqlContext = SQLContext(sc)
  df = sqlContext.parquetFile('hdfs_path_to_hive_table/field1=ABCD/')
  df.select("field2").show()
  </code></pre>
  
  <ul>
  <li>Takes 6.5 seconds to execute and returns the dataframe as expected.</li>
  </ul>
  
  <h1>QUESTIONS</h1>
  
  <ul>
  <li>Has anyone noticed anything similar to this? </li>
  <li>What is happening on the backend that could be causing this consumption of resources and what could I do to avoid it?</li>
  </ul>
  
  <p>Any help would be greatly appreciated!</p>
  
  <h1>UPDATE 10/16/2015</h1>
  
  <p>I have tried: </p>
  
  <pre><code>SET spark.sql.hive.metastorePartitionPruning=true
  </code></pre>
  
  <p>and I was still running into the same issue. I let the process run for a little while longer to test how high the CPU usage would escalate and it reached upwards of 2000%!</p>
  
  <p>I heard that parquet formatted files were potentially an issue with spark prior to version 1.5 so all of my testing with these additional settings in spark 1.5.1:</p>
  
  <pre><code>parquet.task.side.metadata=false
  SET spark.sql.parquet.filterPushdown=true
  SET spark.sql.parquet.cacheMetadata=false
  </code></pre>
  
  <p>but none of them seemed to help either. </p>
  
  <p>In my quest for an answer I came across these various links which led me to try the above configurations:</p>
  
  <ul>
  <li><strong>Spark reading Parquet's metastore</strong>(parquet.task.side.metadata=false and SET spark.sql.parquet.filterPushdown=true) :
  
  <ul>
  <li><em>_https://issues.apache.org/jira/browse/SPARK-5346</em></li>
  <li><em>_http://stackoverflow.com/questions/31226757/partitions-not-being-pruned-in-simple-sparksql-queries</em></li>
  </ul></li>
  <li><strong>Spark 1.5.1 Link to config</strong>(SET spark.sql.parquet.cacheMetadata=false):
  
  <ul>
  <li><em>_http://spark.apache.org/docs/latest/sql-programming-guide.html#configuration</em></li>
  </ul></li>
  <li><strong>Link to a previous question that was quite similar to mine</strong>:
  
  <ul>
  <li><em>_https://mail-archives.apache.org/mod_mbox/spark-user/201509.mbox/%3CCAAswR-7C0Cfduj+iaVDb-XvrnCHScrh34Lo0BadWH6XPzUXePA@mail.gmail.com%3E</em></li>
  </ul></li>
  </ul>