title: Hive create table statement for timestamps that aren't in a 'yyyy-MM-dd HH:mm:ss' format
body: <p>I have a JSON dataset in HDFS that contains a timestamp and a count. The raw data looks like this:</p>
  
  <pre><code>{"timestamp": "2015-03-01T00:00:00+00:00", "metric": 23}
  {"timestamp": "2015-03-01T00:00:01+00:00", "metric": 17}
  ...
  </code></pre>
  
  <p>The format of the timestamp almost matches the Hive-friendly 'yyyy-mm-dd hh:mm:ss' format but has a couple of differences: there's a 'T' between the date and time. There's also a timezone offset. For example, a timestamp might be <code>2015-03-01T00:00:00+00:00</code> instead of <code>2015-03-01 00:00:00</code>.</p>
  
  <p>I'm able to create a table, providing that I treat the timestamp column as a string:</p>
  
  <pre><code>add jar hdfs:///apps/hive/jars/hive-json-serde-0.2.jar;
  
  CREATE EXTERNAL TABLE `log`(
    `timestamp` string, 
    `metric` bigint)
  ROW FORMAT SERDE "org.apache.hadoop.hive.contrib.serde2.JsonSerde" WITH SERDEPROPERTIES ("timestamp"="$.timestamp", "metric"="$.metric")
  LOCATION 'hdfs://path/to/my/data';
  </code></pre>
  
  <p>This isn't ideal since, by treating it like a string, we lose the ability to use timestamp functions (e.g. <code>DATE_DIFF</code>, <code>DATE_ADD</code>, etc...) without casting from within the query. A possible workaround would be to <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableAsSelect(CTAS)" rel="nofollow noreferrer">CTAS</a> and <a href="https://stackoverflow.com/questions/26355194/how-to-convert-a-string-to-timestamp-with-milliseconds-in-hive">CAST the timestamp using a regular expression</a>, but this entails copying the data into its new format. This seems inefficient and not in the spirit of 'schema-on-read'.</p>
  
  <p>Is there a way to create a schema for this data without processing the data twice (i.e. once to load, once to convert the timestamp to a true timestamp)?</p>