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
