How do I join two tables together that are in diff

Question

A problem I've encountered a few times: I have a table, table1, in db1. I have table2 in db2. How do I join between the two?

The obvious thing to do is something like:

SELECT *
FROM db1.table1 INNER JOIN db2.table2
ON db1.table1.field1 = db2.table2.field2;

Hive doesn't like this, however; it starts treating "table1" and "table2" as if they were column names, and "db1" and "db2" as table names, and complaining when they don't exist. How do I join between two tables in different databases?

Answer 1

Joins between tables in different databases, in Hive, uniformly require an alias to be set for each {db,table} pair. So instead of the syntax provided in the question, you have to use:

SELECT *
FROM db1.table1 alias1 INNER JOIN db2.table2 alias2
ON alias1.field1 = alias2.field2;

This works. Of course, it's important to remember that if you're asking for particular fields in the SELECT statement, the aliases apply there too. So:

SELECT db1.table1.field1, db2.table2.field2

becomes:

SELECT alias1.field1, alias2.field2