title: AWS Athena Hive: strip unneeded characters from data in a column
body: <p>I'm a newcomer to Hive and Athena on AWS.  I have data in a table that looks like:</p>
  
  <pre><code>_id
  type
  title
  </code></pre>
  
  <p>An <code>_id</code> looks like <code>{"$oid":"12asdf9"}</code>.</p>
  
  <p>I have managed to create a query that strips the data of everything but the actual id using <code>regexp_extract</code></p>
  
  <p>I now need to save the results of this query into the <code>_id</code> column.</p>
  
  <p>The solution I'm trying out (and I can't get to work) is:</p>
  
  <ol>
  <li>Run <code>regexp_extract</code> on the <code>_id</code> column</li>
  <li>Select all the other columns I need</li>
  <li>Dump the results into a new table</li>
  </ol>
  
  <p>My code to do this (see below) returns <code>line 1:8: no viable alternative at input 'create external' (service: amazonathena; status code: 400; error code: invalidrequestexception;</code>. </p>
  
  <p>I would greatly appreciate any help in solving this.  Thanks!</p>
  
  <pre><code>CREATE EXTERNAL TABLE sitbi_test_db.combined AS
  SELECT unioned._id, unioned.type, unioned.title
  FROM (
    SELECT a._id, regexp_extract( a._id, '(?&lt;=oid\"\:\")(.*)(?=\"\})')
    FROM sitbi_test_db.announcements a
    UNION ALL  
  ) unioned
  LOCATION 's3://sitbi-test-mongodb/cleanedAnnouncements/'
  </code></pre>
  
  <p>NOTE: I manually created a new directory <code>cleanedAnnouncements</code> in my source S3 bucket, I'm not sure if this is meant to point to the Athena output bucket</p>