Hive Join & SubQuery 教程及示例

连接查询

连接查询可以对 Hive 中的两个表执行。为了理解连接 Concepts 在这里我们创建了两个表，

Sample_joins（与客户详细信息相关）
Sample_joins1（与员工完成的订单详情相关）

步骤1） 创建表“sample_joins”，其列名为员工 ID、姓名、年龄、地址和工资

步骤2） 加载和显示数据

从上面的屏幕截图

将数据从 Customers.txt 加载到 sample_joins 中
显示 sample_joins 表内容

步骤3） 创建sample_joins1表并加载、显示数据

从上面的截图中，我们可以观察到以下内容

创建表 sample_joins1，其中包含 Orderid、Date1、Id、Amount 列
将数据从 orders.txt 加载到 sample_joins1 中
显示 sample_joins1 中的记录

接下来，我们将看到可以在我们创建的表上执行的不同类型的连接，但在此之前，您必须考虑以下连接要点。

在连接中要注意的一些要点：

连接中仅允许使用平等连接
可以在同一个查询中连接两个以上的表
LEFT、RIGHT、FULL OUTER 连接的存在是为了对没有匹配的 ON 子句提供更多的控制
连接不具有交换性
无论是左连接还是右连接，连接都是左关联的

不同类型的连接

连接有 4 种类型，分别是

内部联接
左外连接
右外连接
完全外部加入

内部联接：

此内连接将检索两个表所共有的记录。

从上面的截图中，我们可以观察到以下内容

这里我们使用 JOIN 关键字在表 sample_joins 和 sample_joins1 之间执行连接查询，匹配条件为 (c.Id= o.Id)。
通过检查查询中提到的条件，输出显示两个表中都存在的公共记录

查询：

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

左外连接：

Hive 查询语言 LEFT OUTER JOIN 返回左表中的所有行，即使右表中没有匹配项
如果 ON 子句与右表中的零条记录匹配，则连接仍然会在结果中返回右表中每列为 NULL 的记录

从上面的截图中，我们可以观察到以下内容

这里我们使用“LEFT OUTER JOIN”关键字在表 sample_joins 和 sample_joins1 之间执行连接查询，匹配条件为 (c.Id=o.Id)。举个例子 这里我们使用员工 ID 作为参考，检查 ID 在右侧表格和左侧表格中是否相同。它充当匹配条件。
通过检查查询中提到的条件，输出显示两个表中都存在的公共记录。上述输出中的 NULL 值是右表（即 sample_joins1）中没有值的列

查询：

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

右外连接：

Hive 查询语言 RIGHT OUTER JOIN 返回右表中的所有行，即使左表中没有匹配项
如果 ON 子句匹配左表中的零条记录，则连接仍然会在结果中返回左表中每列为 NULL 的记录
右连接总是返回右表中的记录和左表中的匹配记录。如果左表没有与该列对应的值，它将在该位置返回 NULL 值。

从上面的截图中，我们可以观察到以下内容

这里我们使用“RIGHT OUTER JOIN”关键字在表 sample_joins 和 sample_joins1 之间执行连接查询，匹配条件为 (c.Id=o.Id)。
通过检查查询中提到的条件，输出显示两个表中都存在的公共记录

询问:

  SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

完全外连接：

它根据查询中给出的 JOIN 条件组合表 sample_joins 和 sample_joins1 中的记录。

它返回两个表中的所有记录，并为两侧匹配的缺失值的列填充 NULL 值。

从上面的屏幕截图我们可以观察到以下情况：

这里我们使用关键字“FULL OUTER JOIN”在表 sample_joins 和 sample_joins1 之间执行连接查询，匹配条件为 (c.Id=o.Id)。
通过检查查询中提到的条件，输出显示两个表中存在的所有记录。此处输出中的空值表示两个表的列中缺少的值。

询问

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

子查询

查询中的查询称为子查询。主查询将取决于子查询返回的值。

子查询可以分为两类

FROM 子句中的子查询
WHERE 子句中的子查询

何时使用：

获取由不同表中的两个列值组合而成的特定值
一个表值对其他表的依赖性
与其他表进行比较检查某一列的值

语法：

Subquery in FROM clause
SELECT <column names 1, 2…n>From (SubQuery) <TableName_Main >
Subquery in WHERE clause
SELECT <column names 1, 2…n> From<TableName_Main>WHERE col1 IN (SubQuery);

计费示例：

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

这里 t1 和 t2 是表名。彩色的是针对表 t1 执行的子查询。这里 a 和 b 是在子查询中添加并分配给 col1 的列。Col1 是主表中存在的列值。子查询中存在的此列“col1”相当于主表查询中的列 col1。

嵌入自定义脚本

蜂房为根据客户需求编写用户特定脚本提供了可行性。用户可以根据需求编写自己的映射和缩减脚本。这些脚本称为嵌入式自定义脚本。自定义脚本中定义了编码逻辑，我们可以在 ETL 时使用该脚本。

何时选择嵌入式脚本：

根据客户的特定要求，开发人员必须在 Hive 中编写和部署脚本
Hive 内置函数不适用于特定领域要求的情况

为此，Hive 使用 TRANSFORM 子句来嵌入 map 和 Reducer 脚本。

在此嵌入式自定义脚本中，我们必须注意以下几点

在将列提供给用户脚本之前，它将被转换为字符串并用 TAB 分隔
用户脚本的标准输出将被视为 TAB 分隔的字符串列

嵌入式脚本示例，

FROM (
	FROM pv_users
	MAP pv_users.userid, pv_users.date
	USING 'map_script'
	AS dt, uid
	CLUSTER BY dt) map_output

INSERT OVERWRITE TABLE pv_users_reduced
	REDUCE map_output.dt, map_output.uid
	USING 'reduce_script'
	AS date, count;

从上面的脚本中，我们可以观察到以下内容

这只是为了理解的示例脚本

pv_users 是用户表，其中包含 map_script 中提到的 userid 和 date 等字段
根据 pv_users 表的日期和数量定义的 Reducer 脚本

连接查询

不同类型的连接

相关文章

子查询

嵌入自定义脚本

注册简报