You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by "Sagi, Lee" <ls...@shopping.com> on 2009/12/16 00:09:29 UTC
LIKE operator
I am trying to insert data into table ZZ with an outer join result of A
and B, the Join is a LIKE b/t a filed from table A and a field from
table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line
117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
RE: LIKE operator
Posted by "Sagi, Lee" <ls...@shopping.com>.
Table A is only one hour worth of data (around 200,000 records), and
table B is around 300 records, I'm not sure if partitioning will do
much.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:39 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Can you partition the data in A and B and then join the partitions and
then union all them.
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:35 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Yes, currently I create the table first than insert (just wanted to have
less code/scripts).
And yes, this query/join is painfully slow, but I need to flag records
that match an IP pattern...I'm open for suggestions.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:27 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
I should clarify that this is not the most efficient way of doing it -
since we are doing a Cartesian product first (which will go to 1
reducer).
We do support CREATE TABLE AS SELECT in hive trunk, but it is not part
of 0.4
You can create the table and then insert into that table.
-namit
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:18 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a
table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A
and B, the Join is a LIKE b/t a filed from table A and a field from
table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line
117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
RE: LIKE operator
Posted by "Sagi, Lee" <ls...@shopping.com>.
Yes it works faster with the hint..thanks.
By the way, I spoke too soon.
The join does not fail but it return no rows, I tried switching the LEFT
and RIGHT joins but nothing is returned. If I put something like '127.%'
then a result is returned.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 4:39 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Since B is very small, you can do a map-side join.
INSERT OVERWRITE TABLE ZZ
SELECT /*+ MAPJOIN(B) */ VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:45 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Sorry table A has around 3,000,000 records not 200,000.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:39 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Can you partition the data in A and B and then join the partitions and
then union all them.
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:35 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Yes, currently I create the table first than insert (just wanted to have
less code/scripts).
And yes, this query/join is painfully slow, but I need to flag records
that match an IP pattern...I'm open for suggestions.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:27 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
I should clarify that this is not the most efficient way of doing it -
since we are doing a Cartesian product first (which will go to 1
reducer).
We do support CREATE TABLE AS SELECT in hive trunk, but it is not part
of 0.4
You can create the table and then insert into that table.
-namit
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:18 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a
table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A
and B, the Join is a LIKE b/t a filed from table A and a field from
table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line
117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
RE: LIKE operator
Posted by Namit Jain <nj...@facebook.com>.
Since B is very small, you can do a map-side join.
INSERT OVERWRITE TABLE ZZ
SELECT /*+ MAPJOIN(B) */ VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:45 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Sorry table A has around 3,000,000 records not 200,000.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:39 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Can you partition the data in A and B and then join the partitions and then union all them.
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:35 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Yes, currently I create the table first than insert (just wanted to have less code/scripts).
And yes, this query/join is painfully slow, but I need to flag records that match an IP pattern...I'm open for suggestions.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:27 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
I should clarify that this is not the most efficient way of doing it - since we are doing a Cartesian product first (which will go to 1 reducer).
We do support CREATE TABLE AS SELECT in hive trunk, but it is not part of 0.4
You can create the table and then insert into that table.
-namit
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:18 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A and B, the Join is a LIKE b/t a filed from table A and a field from table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line 117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
RE: LIKE operator
Posted by "Sagi, Lee" <ls...@shopping.com>.
Sorry table A has around 3,000,000 records not 200,000.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:39 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Can you partition the data in A and B and then join the partitions and
then union all them.
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:35 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Yes, currently I create the table first than insert (just wanted to have
less code/scripts).
And yes, this query/join is painfully slow, but I need to flag records
that match an IP pattern...I'm open for suggestions.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:27 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
I should clarify that this is not the most efficient way of doing it -
since we are doing a Cartesian product first (which will go to 1
reducer).
We do support CREATE TABLE AS SELECT in hive trunk, but it is not part
of 0.4
You can create the table and then insert into that table.
-namit
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:18 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a
table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A
and B, the Join is a LIKE b/t a filed from table A and a field from
table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line
117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
Re: LIKE operator
Posted by Prasad Chakka <pc...@facebook.com>.
Wouldn't map-join optimization work here?
________________________________
From: Namit Jain <nj...@facebook.com>
Reply-To: <hi...@hadoop.apache.org>
Date: Tue, 15 Dec 2009 15:39:06 -0800
To: <hi...@hadoop.apache.org>
Subject: RE: LIKE operator
Can you partition the data in A and B and then join the partitions and then union all them.
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:35 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Yes, currently I create the table first than insert (just wanted to have less code/scripts).
And yes, this query/join is painfully slow, but I need to flag records that match an IP pattern...I'm open for suggestions.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:27 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
I should clarify that this is not the most efficient way of doing it - since we are doing a Cartesian product first (which will go to 1 reducer).
We do support CREATE TABLE AS SELECT in hive trunk, but it is not part of 0.4
You can create the table and then insert into that table.
-namit
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:18 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A and B, the Join is a LIKE b/t a filed from table A and a field from table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line 117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
RE: LIKE operator
Posted by Namit Jain <nj...@facebook.com>.
Can you partition the data in A and B and then join the partitions and then union all them.
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:35 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Yes, currently I create the table first than insert (just wanted to have less code/scripts).
And yes, this query/join is painfully slow, but I need to flag records that match an IP pattern...I'm open for suggestions.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:27 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
I should clarify that this is not the most efficient way of doing it - since we are doing a Cartesian product first (which will go to 1 reducer).
We do support CREATE TABLE AS SELECT in hive trunk, but it is not part of 0.4
You can create the table and then insert into that table.
-namit
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:18 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A and B, the Join is a LIKE b/t a filed from table A and a field from table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line 117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
RE: LIKE operator
Posted by "Sagi, Lee" <ls...@shopping.com>.
Yes, currently I create the table first than insert (just wanted to have
less code/scripts).
And yes, this query/join is painfully slow, but I need to flag records
that match an IP pattern...I'm open for suggestions.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:27 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
I should clarify that this is not the most efficient way of doing it -
since we are doing a Cartesian product first (which will go to 1
reducer).
We do support CREATE TABLE AS SELECT in hive trunk, but it is not part
of 0.4
You can create the table and then insert into that table.
-namit
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:18 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a
table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A
and B, the Join is a LIKE b/t a filed from table A and a field from
table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line
117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
RE: LIKE operator
Posted by Namit Jain <nj...@facebook.com>.
I should clarify that this is not the most efficient way of doing it - since we are doing a Cartesian product first (which will go to 1 reducer).
We do support CREATE TABLE AS SELECT in hive trunk, but it is not part of 0.4
You can create the table and then insert into that table.
-namit
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:18 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A and B, the Join is a LIKE b/t a filed from table A and a field from table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line 117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947
RE: LIKE operator
Posted by "Sagi, Lee" <ls...@shopping.com>.
Thanks your re-write works perfectly.
Allow me to piggy-back and ask a follow-up question, how can I create a
table as the result of this query?
I tired the obvious CREATE TABLE AAA AS SELECT .... with not success.
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Tuesday, December 15, 2009 3:12 PM
To: hive-user@hadoop.apache.org
Subject: RE: LIKE operator
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A
and B, the Join is a LIKE b/t a filed from table A and a field from
table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line
117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 |
Cell: 718-930-7947
Re: LIKE operator
Posted by Raghu Murthy <rm...@facebook.com>.
Namit: wont he have to use some attribute to join on. Looks like he wants to
figure out which pattern (b.ip) a given ip (a.client_ip) matches and get the
attributes associated with that pattern (from b).
On 12/15/09 3:12 PM, "Namit Jain" <nj...@facebook.com> wrote:
> Hive only supports equality joins right now:
>
> INSERT OVERWRITE TABLE ZZ
> SELECT VOTF_REQUEST_ID ,
> THRESHOLD_VALUE ,
> THRESHOLD_MET ,
> BRAND_ID
> FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
> WHERE date_key = '2009121315';
>
>
> Can be rewritten as:
>
> INSERT OVERWRITE TABLE ZZ
> SELECT VOTF_REQUEST_ID ,
> THRESHOLD_VALUE ,
> THRESHOLD_MET ,
> BRAND_ID
> FROM A LEFT OUTER JOIN B
> WHERE date_key = '2009121315'
> and A.CLIENT_IP LIKE B.IP;
>
>
>
> From: Sagi, Lee [mailto:lsagi@shopping.com]
> Sent: Tuesday, December 15, 2009 3:09 PM
> To: hive-user@hadoop.apache.org
> Subject: LIKE operator
>
> I am trying to insert data into table ZZ with an outer join result of A and B,
> the Join is a LIKE b/t a filed from table A and a field from table B:
>
> INSERT OVERWRITE TABLE ZZ
> SELECT VOTF_REQUEST_ID ,
> THRESHOLD_VALUE ,
> THRESHOLD_MET ,
> BRAND_ID
> FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
> WHERE date_key = '2009121315';
>
> B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
>
> I get the following error: FAILED: Error in semantic analysis: line 117:57
> Both Left and Right Aliases Encountered in Join IP
>
> Any ideas?
>
>
> Thanks.
>
>
> Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell:
> 718-930-7947
RE: LIKE operator
Posted by Namit Jain <nj...@facebook.com>.
Hive only supports equality joins right now:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
Can be rewritten as:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B
WHERE date_key = '2009121315'
and A.CLIENT_IP LIKE B.IP;
From: Sagi, Lee [mailto:lsagi@shopping.com]
Sent: Tuesday, December 15, 2009 3:09 PM
To: hive-user@hadoop.apache.org
Subject: LIKE operator
I am trying to insert data into table ZZ with an outer join result of A and B, the Join is a LIKE b/t a filed from table A and a field from table B:
INSERT OVERWRITE TABLE ZZ
SELECT VOTF_REQUEST_ID ,
THRESHOLD_VALUE ,
THRESHOLD_MET ,
BRAND_ID
FROM A LEFT OUTER JOIN B ON (A.CLIENT_IP LIKE B.IP)
WHERE date_key = '2009121315';
B.IP has values like: 1.2.%.%.%, 10.10.10.%, etc.
I get the following error: FAILED: Error in semantic analysis: line 117:57 Both Left and Right Aliases Encountered in Join IP
Any ideas?
Thanks.
Lee Sagi | Data Warehouse Tech Lead & Architect | Work: 650-616-6575 | Cell: 718-930-7947