title: Join two tables on id fields using Impala
body: <p>I have two tables in in HDFS that I want to join using Impala.  One is Employee_Logs the other is HR_Data.</p>
  
  <p>Queries:</p>
  
  <pre><code>select e.employee_id, e.action from Employee_Logs e where e.employment_status_desc = 'Active'
  select h.employee_id, h.name from HR_Data h
  </code></pre>
  
  <p>Employee_Logs:</p>
  
  <pre><code>employee_id  action
  2325255b     login     
  51666164     login
  51666164v    login
  r1211        logoff
  r18552421    login
  </code></pre>
  
  <p>HR_Data:</p>
  
  <pre><code>employee_id  name
  2325255      Rob    
  51666164     Tom
  r1211        Tammy
  r18552421    Ron
  </code></pre>
  
  <p>I want to join them so that the data looks like this:</p>
  
  <pre><code>employee_id  action  name
  2325255b     login   Rob  
  51666164     login   Tom
  51666164v    login   Tom
  r1211        logoff  Tammy
  r18552421    login   Ron
  </code></pre>
  
  <p>I could do an easy join if the employee_id field matched up on both tables, but the same user can have a "b" or a "v" after their employee id to specify if the account is elevated like an admin account.  Some user accounts have an "r" in front of the id but that is the case in both tables. </p>
  
  <p>Is there a way where I can do some where actions and create a new field in the Employee_Logs table like strip the "v" and "b" off of the end of the employee id and then join or is there a better way?</p>