title: Casting string to int i.e. the string "res"
body: <p>I have a column in a table which is type <code>array&lt;string&gt;</code>. The table is partitioned daily since <code>2018-01-01</code>. At some stage, the values in the array goes from strings to integers. The data looks like this:</p>
  
  <pre><code>| yyyy_mm_dd | h_id  | p_id | con           |
  |------------|-------|------|---------------|
  | 2018-10-01 | 52988 | 1    | ["res", "av"] |
  | 2018-10-02 | 52988 | 1    | ["1","2"]     |
  | 2018-10-03 | 52988 | 1    | ["1","2"]     |
  </code></pre>
  
  <p>There is a mapping between the strings and integers. "res" maps to 1 and "av" maps to 2 etc. However, I've written a query to perform some logic. Here is a snippet (subquery) of it:</p>
  
  <pre><code>SELECT
      t.yyyy_mm_dd,
      t.h_id,
      t.p_id,
      CAST(e.con AS INT) AS api
  FROM
      my_table t
      LATERAL VIEW EXPLODE(con) e AS con
  </code></pre>
  
  <p>My problem is that this doesn't work for the earlier dates when strings were used instead of integers. Is there anyway to select con and remap the strings to integers so the data is across all partitions?</p>
  
  <p>Expected output:</p>
  
  <pre><code>| yyyy_mm_dd | h_id  | p_id | con           |
  |------------|-------|------|---------------|
  | 2018-10-01 | 52988 | 1    | ["1","2"]     |
  | 2018-10-02 | 52988 | 1    | ["1","2"]     |
  | 2018-10-03 | 52988 | 1    | ["1","2"]     |
  </code></pre>
  
  <p>Once the values selected are all integers (within a string array), then the <code>CAST(e.con AS INT)</code> will work</p>
  
  <p><strong>Edit:</strong> To clarify, I will put the solution as a subquery before I use <code>lateral view explode</code>. This way I am exploding on a table where all partitions have integers in <code>con</code>. I hope this makes sense.</p>