I am sending sample file to populate the a_log in hive and check if its needed by you
On Tue, Mar 9, 2010 at 2:15 PM, prakash sejwani <[email protected]>wrote: > yes right can you give me a tip how to exclude blank values > > > On Tue, Mar 9, 2010 at 2:13 PM, Zheng Shao <[email protected]> wrote: > >> So I guess you didn't exclude the Blank ones? >> >> On Tue, Mar 9, 2010 at 12:41 AM, prakash sejwani >> <[email protected]> wrote: >> > yes, regexp_extract return NULL or Blank >> > >> > On Tue, Mar 9, 2010 at 2:05 PM, Zheng Shao <[email protected]> wrote: >> >> >> >> What do you mean by "product_name" is "present"? >> >> If it is not present, does the regexp_extract return NULL? >> >> >> >> Zheng >> >> >> >> On Tue, Mar 9, 2010 at 12:13 AM, prakash sejwani >> >> <[email protected]> wrote: >> >> > Hi all, >> >> > I have a query below >> >> > >> >> > FROM ( >> >> > SELECT h.* >> >> > FROM ( >> >> > -- Pull from the access_log >> >> > SELECT ip, >> >> > -- Reformat the time from the access log >> >> > time, dt, >> >> > --method, resource, protocol, status, length, referer, agent, >> >> > -- Extract the product_id for the hit from the URL >> >> > cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING) >> AS >> >> > product_name >> >> > FROM a_log >> >> > ) h >> >> > )hit >> >> > -- Insert the hit data into a seperate search table >> >> > INSERT OVERWRITE TABLE search >> >> > SELECT ip, time, dt, >> >> > product_name >> >> > WHERE product_name IS NOT NULL; >> >> > >> >> > >> >> > it suppose to populate the search table with only if product_name is >> >> > present >> >> > but i get all of it.. >> >> > >> >> > any help would be appreciated >> >> > >> >> > thanks >> >> > prakash sejwani >> >> > econify infotech >> >> > mumbai >> >> > >> >> >> >> >> >> >> >> -- >> >> Yours, >> >> Zheng >> > >> > >> >> >> >> -- >> Yours, >> Zheng >> > >
204.54.154.68 2009-12-31 22:55:21 POST /sessions/status HTTP/1.1 200 234 http://www.partsfarm.com/products/4958-681-009 Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729) 2009-12-31 66.249.71.146 2009-12-31 22:57:21 GET /searches/tagged_with?company=2-Opico&page=6&product=42051-CN7479-TANK-MOUNTING-BRACKETS-AND&tag=demco HTTP/1.1 200 3155 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:03:49 GET /searches/tagged_with?company=2-Opico&page=3&product=38833-11995-LADDER-RAW-F-ALL-GRAIN-CARTS&tag=demco HTTP/1.1 200 3138 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:09:52 GET /searches/tagged_with?company=2-Opico&page=4&product=41786-BAC28-10M-BELT-V-STYLE-10-GROOVE-F&tag=demco HTTP/1.1 200 3175 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 151.200.118.154 2009-12-31 23:13:33 GET /products/4958-681-009 HTTP/1.1 200 2826 http://www.google.com/search?hl=en&source=hp&q=681-009%20BOLT&aq=f&oq=&aqi= Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.6) Gecko/20091215 Ubuntu/9.10 (karmic) Firefox/3.0.6 2009-12-31 151.200.118.154 2009-12-31 23:13:33 POST /sessions/status HTTP/1.1 200 280 http://www.partsfarm.com/products/4958-681-009 Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.6) Gecko/20091215 Ubuntu/9.10 (karmic) Firefox/3.0.6 2009-12-31 151.200.118.154 2009-12-31 23:13:47 GET /products/stock HTTP/1.1 200 2311 http://my7.statcounter.com/project/standard2/pageload.php?project_id=5138583&PHPSESSID=4c75987a6c6368d312d61a28d373e5b2 Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.6) Gecko/20091215 Ubuntu/9.10 (karmic) Firefox/3.0.6 2009-12-31 151.200.118.154 2009-12-31 23:13:47 POST /sessions/status HTTP/1.1 200 280 http://www.partsfarm.com/products/stock Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.6) Gecko/20091215 Ubuntu/9.10 (karmic) Firefox/3.0.6 2009-12-31 151.200.118.154 2009-12-31 23:13:47 GET /images/bg_nav-company-profile_active.gif HTTP/1.1 200 240 http://www.partsfarm.com/stylesheets/common.css?1259643448 Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.6) Gecko/20091215 Ubuntu/9.10 (karmic) Firefox/3.0.6 2009-12-31 220.181.7.64 2009-12-31 23:15:06 GET / HTTP/1.1 200 12467 Baiduspider+(+http://www.baidu.com/search/spider.htm) 2009-12-31 66.249.71.146 2009-12-31 23:16:22 GET /searches/tagged_with?company=2-Opico&page=6&product=39849-2404-0105-SOLINOID-PLUNGER-F-A414V34&tag=demco HTTP/1.1 200 3157 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 123.125.66.127 2009-12-31 23:21:35 GET / HTTP/1.1 200 12467 Baiduspider+(+http://www.baidu.com/search/spider.htm) 2009-12-31 66.249.71.146 2009-12-31 23:23:23 GET /searches/tagged_with?company=2-Opico&page=2&product=35744-7190-U-BOLT-SQ-750NC-X&tag=demco HTTP/1.1 200 3060 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:23:24 GET /searches/tagged_with?company=2-Opico&page=4&product=41713-AI110025VS-SPRAY-TIP-FLAT-AIR-INDUCTION&tag=demco HTTP/1.1 200 3178 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:23:24 GET /companies/5-forward-consulting/products/stock HTTP/1.1 200 2381 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:23:25 GET /companies/5-forward-consulting/products/recent HTTP/1.1 200 2347 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:23:26 GET /searches/tagged_with?company=2-Opico&page=7&product=41873-C1200-FTG-STR-50-HB-2X-POLY&tag=demco HTTP/1.1 200 3151 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:23:26 GET /companies/5-forward-consulting/products/available HTTP/1.1 200 2347 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:23:27 GET /searches/tagged_with?company=2-Opico&page=8&product=36154-7653-BACKUP-PLATE-F-STORAGE-STAND&tag=demco HTTP/1.1 200 3193 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:23:28 GET /searches/tagged_with?company=2-Opico&page=6&product=40189-5136-STACK-PACK-SUB-ASSEMBLY-F-&tag=demco HTTP/1.1 200 3152 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:24:03 GET /searches/tagged_with?company=2-Opico&page=4&product=39426-12622-10-LINK-RAW-&tag=demco HTTP/1.1 200 3150 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:31:35 GET /searches/tagged_with?company=2-Opico&page=6&product=43515-74966826623-JACKSHAFT-R500-230M-255S&tag=howard HTTP/1.1 200 3175 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:42:03 GET /searches/tagged_with?company=2-Opico&page=7&product=36653-09294-20-TONGUE-HITCH-FEM-1-2-F-12T-RUN&tag=demco HTTP/1.1 200 3159 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 67.195.114.253 2009-12-31 23:47:21 GET /products/957-030-76060-BOLT-PLOW-5-8-11X1-1-2-GR5-PLT HTTP/1.0 200 2544 Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://help.yahoo.com/help/us/ysearch/slurp) 2009-12-31 67.195.114.253 2009-12-31 23:47:21 GET /stylesheets/common.css?1259643448 HTTP/1.0 304 0 http://www.partsfarm.com/products/957-030-76060-BOLT-PLOW-5-8-11X1-1-2-GR5-PLT Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://help.yahoo.com/help/us/ysearch/slurp) 2009-12-31 67.195.114.253 2009-12-31 23:47:22 GET /stylesheets/ui.tabs.css?1259643448 HTTP/1.0 304 0 http://www.partsfarm.com/products/957-030-76060-BOLT-PLOW-5-8-11X1-1-2-GR5-PLT Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://help.yahoo.com/help/us/ysearch/slurp) 2009-12-31 65.55.106.131 2009-12-31 23:48:08 GET /robots.txt HTTP/1.1 200 99 msnbot/2.0b (+http://search.msn.com/msnbot.htm) 2009-12-31 65.55.106.131 2009-12-31 23:49:10 GET / HTTP/1.1 304 0 msnbot/2.0b (+http://search.msn.com/msnbot.htm) 2009-12-31 66.249.71.146 2009-12-31 23:53:01 GET /searches/tagged_with?company=2-Opico&page=8&product=42293-DFM60-FOAM-MARKER-F-60-BOOM&tag=demco HTTP/1.1 200 3189 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:53:03 GET /searches/tagged_with?company=2-Opico&page=2&product=38972-12129CE-SPACER-F-AA171&tag=demco HTTP/1.1 200 3062 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:53:03 GET /searches/tagged_with?company=2-Opico&page=1388&product=41053-9422063-SIDE-MTS-400-GAL-QUICK-TACH&tag=demco HTTP/1.1 200 2876 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:53:04 GET /searches/tagged_with?company=2-Opico&page=6&product=36365-07904-30-AXLE-UNDERCARRIAGE-79-75-F-&tag=demco HTTP/1.1 200 3157 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31 66.249.71.146 2009-12-31 23:57:14 GET /searches/tagged_with?company=2-Opico&page=2&product=43125-SB560-BRAKE-LINE-188-X-60-&tag=demco HTTP/1.1 200 3069 Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2009-12-31
