sample.q

DROP TABLE REVIVE_AD_VIS;
CREATE EXTERNAL TABLE REVIVE_AD_VIS ( hour String, bannerid STRING, campaignid STRING, zoneid STRING, count STRING ) 
       row format 
       delimited fields terminated by ',' 
       lines terminated by '\n' 
       STORED 
       AS TEXTFILE LOCATION 's3n://EMRresult/OutputFolder';
       
INSERT OVERWRITE TABLE REVIVE_AD_VIS 
       SELECT 
              substr(`timestamp`,12,2), bannerid, campaignid, zoneid, count(*) 
       FROM 
              elb_raw_access_logs 
       LATERAL VIEW parse_url_tuple(url, 'PATH','QUERY:bannerid','QUERY:campaignid','QUERY:zoneid') b as path, bannerid, campaignid, zoneid 
       where
              instr(b.path,'lg.php') > 0 
       group by substr(`timestamp`,12,2), bannerid, campaignid, zoneid;