title: How to regex Apache log date and time into Hive
body: <p>I want to put my logfiles into a hive (Amazon Athena)</p>
  
  <p>My regex is OK, says the tester: <a href="https://regex101.com/r/hF4fP8/11" rel="nofollow noreferrer">https://regex101.com/r/hF4fP8/11</a></p>
  
  <p>My create table is this:</p>
  
  <pre><code>CREATE EXTERNAL TABLE IF NOT EXISTS webservicelogs.Test15 (
           `day` int,
           `month` string,
           `year` int,
           `hour` int,
           `minute` int,
           `second` int,
           `offset` string 
  ) 
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES ('input.regex' = '\[(\d{2})\/([a-zA-Z]{3})\/(\d{4}):(\d{2}):(\d{2}):(\d{2})\s(\+\d{4})]' ) 
  LOCATION 's3://getag-athena/Test/' 
  TBLPROPERTIES ('has_encrypted_data'='false')
  </code></pre>
  
  <p>The create table statement works</p>
  
  <p>If I want to select the table this error occurs</p>
  
  <pre><code>SELECT * FROM "webservicelogs"."test15" limit 10;
  </code></pre>
  
  <p>Your query has the following error(s):</p>
  
  <pre><code>HIVE_CURSOR_ERROR: Number of matching groups doesn't match the number of columns
  </code></pre>
  
  <p>The Logfiles I want to parse is like this:</p>
  
  <pre><code>85.239.101.101 - - [07/Jan/2016:01:00:00 +0100] "POST /bpwsortsinfo1-3/services/Ortsinfo?wsdl HTTP/1.1" 200 467 "-" "Axis2" 449/1883 23 BP7 0
  </code></pre>