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<http://c.name/> AS company_name,c2.id<http://c2.id/> AS
product_company_id,c2.name<http://c2.name/> AS product_company_name FROM
(SELECT ip, ident, user,time, dt, cast(regexp_extract(resource,
'/products/(\\d+<smb://d+>)', 1) AS INT) AS product_id,
cast(regexp_extract(resource, '/companies/(\\d+<smb://d+>)', 1) AS INT) AS
company_id FROM a_log) h LEFT OUTER JOIN products p ON (h.product_id =
p.id<http://p.id/>) LEFT OUTER JOIN companies c ON (h.company_id =
c.id<http://c.id/>) LEFT OUTER JOIN companies c2 ON (p.company_id =
c2.id<http://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<http://c.name/> AS
company_name,c2.id<http://c2.id/> AS
product_company_id,c2.name<http://c2.name/> AS product_company_name FROM
(SELECT ip, ident, user,time, dt, cast(regexp_extract(resource,
'/products/(\\d+<smb://d+>)', 1) AS INT) AS product_id,
cast(regexp_extract(resource, '/companies/(\\d+<smb://d+>)', 1) AS INT) AS
company_id FROM a_log) h LEFT OUTER JOIN products p ON (h.product_id =
p.id<http://p.id/>) LEFT OUTER JOIN companies c ON (h.company_id =
c.id<http://c.id/>) LEFT OUTER JOIN companies c2 ON (p.company_id =
c2.id<http://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
