Hive中如何将不同数据库中的两个表连接在一起？ [英] How do I join two tables together that are in different databases, in Hive?

问题描述

我遇到过几次问题：我在db1中有一个表table1。我有db2中的table2。如何在两者之间进行连接？

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不喜欢这样;它开始将table1和table2视为列名，将db1和db2视为表名，并在不存在时提出抱怨。如何在不同数据库中的两个表之间进行连接？

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?

推荐答案

Hive中不同数据库中的表之间的连接统一需要一个别名为每个{db，table}对设置。因此，而不是问题中提供的语法，您必须使用：

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;

这有效。当然，重要的是要记住，如果您要求 SELECT 语句中的特定字段，那么别名也适用于此。所以：

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

这篇关于Hive中如何将不同数据库中的两个表连接在一起？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！