title: Partition hive table based on year/month from a timestamp column
body: <p>I have a hive table which I am importing from Postgres using sqoop. This table consists of an updated_at column which has timestamp values. I am trying to partition the table by the year and the month from this timestamp value. Is there any way to extract the parts from the timestamp to use in the partitioning statement.</p>
  
  <p>My current strategy is to import the table directly into hive, then create a partitioned table and use INSERT OVERWRITE TABLE to fill in the table with partitions. </p>
  
  <pre><code>CREATE TABLE zips_part(id int,code string,timezone_offset int, timezone_d boolean) PARTITIONED BY(updated_at timestamp)
  </code></pre>
  
  <p>The next step is to overwrite this table</p>
  
  <pre><code>insert overwrite table zips_part PARTITION(updated_at) select z.id, z.updated_at, z.code, z.timezone_offset, z.timezone_d FROM zip_codes z;
  </code></pre>
  
  <p>(Going along the steps mentioned in <a href="http://kickstarthadoop.blogspot.com/2011/06/how-to-speed-up-your-hive-queries-in.html" rel="nofollow">http://kickstarthadoop.blogspot.com/2011/06/how-to-speed-up-your-hive-queries-in.html</a>)</p>
  
  <p>I tried using the date part functions on the year but it would't work.</p>
  
  <p>this one gives a parsing error</p>
  
  <pre><code>insert overwrite table zips_part PARTITION(year(updated_at)) select z.id, z.updated_at, z.code, z.timezone_offset, z.timezone_d FROM zip_codes z;
  </code></pre>
  
  <p>this one gives a similar parsing error</p>
  
  <pre><code>insert overwrite table zips_part PARTITION(year=year(updated_at)) select z.id, z.updated_at, z.code, z.timezone_offset, z.timezone_d FROM zip_codes z;
  </code></pre>