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 <oke...@wikimedia.org> 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
>
