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
