You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by prakash sejwani <pr...@gmail.com> on 2010/03/05 15:12:50 UTC

problem in running hive queries from command line

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

Re: problem in running hive queries from command line

Posted by prakash sejwani <pr...@gmail.com>.

Thanks Ning Zhang i appreciate your help

On Fri, Mar 5, 2010 at 10:11 PM, Ning Zhang <nz...@facebook.com> 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
>
>
>

Re: problem in running hive queries from command line

Posted by Ning Zhang <nz...@facebook.com>.

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