hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Aaron Kimball <aa...@cloudera.com>
Subject Re: equijoin with multiple columns?
Date Fri, 23 Jan 2009 02:09:57 GMT
Hi Zheng,

I was eliding some information to try to boil things down a bit -- but here
goes.

To expand the white lie into the fuller debug dump:
table A ~ ak_filtered_ips
table B ~ ip_locations

It's actually three fields ('a', 'b', and 'c') that I'm joining on, not two.

hive> describe extended ak_filtered_ips;
OK
a    int
b    int
c    int
Detailed Table Information:
Table(tableName:ak_filtered_ips,dbName:default,owner:aaron,createTime:1232661581,lastAccessTime:0,retention:0,sd:StorageDescriptor(cols:[FieldSchema(name:a,type:int,comment:null),
FieldSchema(name:b,type:int,comment:null),
FieldSchema(name:c,type:int,comment:null)],location:hdfs://(server name
redacted):9000/user/hive/warehouse/ak_filtered_ips,inputFormat:org.apache.hadoop.mapred.TextInputFormat,outputFormat:org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat,compressed:false,numBuckets:-1,serdeInfo:SerDeInfo(name:null,serializationLib:org.apache.hadoop.hive.serde2.MetadataTypedColumnsetSerDe,parameters:{serialization.format=1}),bucketCols:[],sortCols:[],parameters:{}),partitionKeys:[],parameters:{})

hive> describe extended ip_locations;
OK
a    int
b    int
c    int
cityid    int
countryid    int
Detailed Table Information:
Table(tableName:ip_locations,dbName:default,owner:aaron,createTime:1232596116,lastAccessTime:0,retention:0,sd:StorageDescriptor(cols:[FieldSchema(name:a,type:int,comment:null),
FieldSchema(name:b,type:int,comment:null),
FieldSchema(name:c,type:int,comment:null),
FieldSchema(name:cityid,type:int,comment:null),
FieldSchema(name:countryid,type:int,comment:null)],location:hdfs://(server
name
redacted):9000/user/hive/warehouse/ip_locations,inputFormat:org.apache.hadoop.mapred.TextInputFormat,outputFormat:org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat,compressed:false,numBuckets:-1,serdeInfo:SerDeInfo(name:null,serializationLib:org.apache.hadoop.hive.serde2.MetadataTypedColumnsetSerDe,parameters:{serialization.format=1}),bucketCols:[],sortCols:[],parameters:{}),partitionKeys:[],parameters:{})

I actually just loaded in a super-small test case involving A, B, x, y, and
z as I initially described. And that statement *does* work.

The actual statement I was running into problems with is:

SELECT ip_locations.cityid, ip_locations.countryid FROM
ak_filtered_ips JOIN ip_locations ON (ak_filtered_ips.a = ip_locations.a AND
ak_filtered_ips.b = ip_locations.b AND ak_filtered_ips.c = ip_locations.c)

I would eventually like to add a "count(1)" to the field list, and a "GROUP
BY ip_locations.cityid, ip_locations.countryid" at the end.

This SELECT statement returns no rows.

Thanks,
- Aaron


On Thu, Jan 22, 2009 at 4:27 PM, Zheng Shao <zshao9@gmail.com> wrote:

> Can you do "describe extended a;" and "describe extended b;" and pste
> the result here?
>
> Zheng
>
>
> On 1/22/09, Aaron Kimball <aaron@cloudera.com> wrote:
> > Hi all,
> >
> > I have a perplexing problem with joins in Hive. I have two tables "A" and
> > "B".  A contains two columns x and y. B also contains x and y, and also
> z. I
> > want to select the values of z from B, for every (x, y) I see in table A.
> >
> > So I wrote this statement:
> >
> > SELECT b.z FROM a JOIN b ON (a.x = b.x AND a.y = b.y);
> >
> > This crunches away and then comes back with an empty resultset. I have
> > exported the same data into MySQL and ran the same query; it works just
> fine
> > there. Does Hive support multi-column join criteria?
> >
> > Thanks,
> > - Aaron Kimball
> >
>
> --
> Sent from Gmail for mobile | mobile.google.com
>
> Yours,
> Zheng
>

Mime
View raw message