Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Joins between databases


Copy link to this message
-
Re: Joins between databases
Stephen Sprague 2014-02-07, 01:10
ahh. we got ourselves a bona-fide head banger.  Welcome to the club!! :)

select
    a.col1 as foo,
    a.col2 as bar,
    a.col3 as baz

from
    database1.table1 a
    INNER JOIN database2.table2 b on a.col1 = b.col1

where
    a.year = 2014
    and a.month = 1
    and a.day = 20
so lose the 'AS' for the table alias but keep it for the column alias.  see
what happens.

Also, good to tell us what version of Hive you're running 'cuz there's a
few out there.

Cheers,
Stephen.
On Thu, Feb 6, 2014 at 4:26 PM, Oliver Keyes <[EMAIL PROTECTED]> wrote:

> Hey all,
>
> So, I'm new to hive (I come to it from MySQL/MariaDB) and I've spent the
> last couple of days banging my head against the problem of trying to
> retrieve data from a join of two tables in different databases. I
> understand that the db.table.column syntax is not supported in hive, and
> that instead it's recommended to do db.table AS alias, and then
> alias.column, but knitting this together is not working for some reason;
> table aliases and joins seem to not like each other much. At the moment,
> I've spent a lot of time noodling and eventually settled on:
>
> SELECT db1.col1,
> db1.col2,
> db1.col3 FROM database1.table1 AS db1 INNER JOIN database2.table2 AS db2
> ON db1.col1 = db2.col1 WHERE db1.year = 2014 AND db1.month = 1 AND db1.day
> = 20;
>
> This gets rejected pretty quickly ("missing EOF at 'AS' near table1", and
> so I've tried experimenting with, say, retrieving the entire dataset in a
> subquery and then selecting from that, but each time I run into the same
> sort of problem. Can anybody help point out where I'm going wrong?
>
> Thanks!
> -Oliver
>