Joining the testing data with store information

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);