test_join.sql

INSERT OVERWRITE TABLE testing2
SELECT
  rowid() as rowid,
  t1.id,
  t1.stateholiday, t1.store, t1.promo, t1.dayofweek, t1.date, t1.schoolholiday,
  SUBSTR(t1.date,1,4) as year,
  SUBSTR(t1.date,6,2) as month,
  SUBSTR(t1.date,9,2) as day,
  t2.promo2sinceweek,
  t2.competitionopensinceyear,
  t2.assortment,
  t2.promo2sinceyear,
  t2.competitiondistance,
  t2.promointerval,
  t2.promo2,
  t2.storetype,
  t2.competitionopensincemonth
FROM
  test_original t1
  JOIN store_raw t2 ON (t1.store = t2.store);