title: Loading a spark DF from S3, multiple files. Which of these approaches is best?
body: <p>I have a s3 bucket with partitioned data underlying Athena.  Using Athena I see there are 104 billion rows in my table. This about 2 years of data. 
  Let's call it <code>big_table</code>.</p>
  
  <p>Partitioning is by day, by hour so 07-12-2018-00,01,02 ... 24 for each day.  Athena field is <code>partition_datetime</code>.</p>
  
  <p>In my use case I need the data from 1 month only, which is about 400 million rows.</p>
  
  <p>So the question has arisen - load directly from:
  1. files</p>
  
  
  
  <pre class="lang-py prettyprint-override"><code>spark.load(['s3://my_bucket/my_schema/my_table_directory/07-01-2018-00/file.snappy.parquet',\
           's3://my_bucket/my_schema/my_table_directory/07-01-2018-01/file.snappy.parquet' ],\
           .
           .
           .
           's3://my_bucket/my_schema/my_table_directory/07-31-2018-23/file.snappy.parquet'])
  </code></pre>
  
  <p>or 2. via pyspark using SQL</p>
  
  <pre class="lang-py prettyprint-override"><code>df = spark.read.parquet('s3://my_bucket/my_schema/my_table_directory')
  df = df.registerTempTable('tmp')
  df = spark.sql("select * from my_schema.my_table_directory where partition_datetime &gt;= '07-01-2018-00' and partition_datetime &lt; '08-01-2018-00'")
  </code></pre>
  
  <p>I think #1 is more efficient because we are only bringing in the data for the period in question. </p>
  
  <p>2 seems inefficient to me because the entire 104 billion rows (or more accurately partition_datetime fields) have to be traversed to satisfy the SELECT.  I'm counseled that this really isn't an issue because of lazy execution and there is never a df with all 104 billion rows. I still say at some point each partition must be visited by the SELECT, therefore option 1 is more efficient. </p>
  
  <p>I am interested in other opinions on this.  Please chime in</p>