Thanks Ning Zhang i appreciate your help On Fri, Mar 5, 2010 at 10:11 PM, Ning Zhang <[email protected]> wrote:
> In command line, shell will interpret * inside " " to file names in the > current directory. You probably want to try single quote or put the query in > a file and run hive -f file.q > > Ning > > On Mar 5, 2010, at 6:12 AM, prakash sejwani wrote: > > hi all, > I have one hive query when i run from hive console like this > > hive> FROM (SELECT h.*,p.title AS product_sku, p.description AS > product_name,c.name AS company_name,c2.id AS product_company_id,c2.name AS > product_company_name FROM (SELECT ip, ident, user,time, dt, > cast(regexp_extract(resource, '/products/(\\d+)', 1) AS INT) AS > product_id, cast(regexp_extract(resource, '/companies/(\\d+)', 1) AS INT) > AS company_id FROM a_log) h LEFT OUTER JOIN products p ON (h.product_id = > p.id) LEFT OUTER JOIN companies c ON (h.company_id = c.id) LEFT OUTER JOIN > companies c2 ON (p.company_id = c2.id) WHERE h.product_id IS NOT NULL OR > h.company_id IS NOT NULL) hit INSERT OVERWRITE TABLE product_hits SELECT ip, > time, dt,product_id, product_company_id AS company_id,'', product_name, > product_company_name AS company_name WHERE product_name IS NOT NULL INSERT > OVERWRITE TABLE company_hits SELECT ip, time, dt,company_id,'',company_name > WHERE company_name IS NOT NULL; > > it runs properly and inser the dat in all the table repectively > > and when i run the query like this > > [ prakash: ~/rorwork/work/ ]$ $HIVE_HOME/bin/hive -e "FROM (SELECT > h.*,p.title AS product_sku, p.description AS product_name,c.name AS > company_name,c2.id AS product_company_id,c2.name AS product_company_name > FROM (SELECT ip, ident, user,time, dt, cast(regexp_extract(resource, > '/products/(\\d+)', 1) AS INT) AS product_id, > cast(regexp_extract(resource, '/companies/(\\d+)', 1) AS INT) AS > company_id FROM a_log) h LEFT OUTER JOIN products p ON (h.product_id = > p.id) LEFT OUTER JOIN companies c ON (h.company_id = c.id) LEFT OUTER JOIN > companies c2 ON (p.company_id = c2.id) WHERE h.product_id IS NOT NULL OR > h.company_id IS NOT NULL) hit INSERT OVERWRITE TABLE product_hits SELECT > hit.ip, hit.time, hit.dt,hit.product_id, hit.product_company_id AS > company_id,'', hit.product_name, hit.product_company_name AS company_name > WHERE hit.product_name IS NOT NULL INSERT OVERWRITE TABLE company_hits > SELECT ip, time, dt,company_id,'',company_name WHERE company_name IS NOT > NULL;" > > it runs the query without error but it doesnt populate the respective > tables i.e. the product_hits and company_hits table remains empty > > thanks, > prakash > > >
