Hive SQL 优化大全（参数配置、语法优化）

服务器环境说明

操作系统均为：CentOS 7.5

组件版本

• jdk 1.8
• mysql 5.7
• hadoop 3.1.3
• hive 3.1.2

参数配置优化

下面以我的集群配置为例来进行优化，请按说明根据实际需求、节点情况进行灵活调整。

yarn-site.xml 配置文件优化

参数一

该参数指定了 NodeManager 可以分配给该节点上的 YARN 容器的最大内存量（以 MB 为单位），默认 8G。

<property>
    <name>yarn.nodemanager.resource.memory-mb</name>
    <value>6144</value>
</property>

我的每台服务器内存为 8 G，这里给 NodeManager 分配 6 G 内存，我们必须考虑给系统以及其它服务预留内存。

注意，该参数不能超过单台服务器的总内存。

参数二

该参数指定了 NodeManager 在 YARN 集群中的每个节点上可以分配给容器的虚拟 CPU 核心数量，默认值为： 8 。

增加它可以提高容器的并行性和性能，但也可能导致 CPU 资源过度分配。减小它可能会限制容器的性能，但可以确保更多的容器在集群上同时运行。

<property>
    <name>yarn.nodemanager.resource.cpu-vcores</name>
    <value>6</value>
</property>

我的每台服务器物理 CPU 核数为 4 ，这里虚拟为 6 核，提高并发度。

参数三

该参数定义了 YARN 调度器允许的单个容器的最大内存分配。

这有助于确保在集群中合理分配内存资源，以防止某个应用程序或容器占用过多的内存，导致性能问题或资源争用。

该参数配置一般为 yarn.nodemanager.resource.memory-mb 的四分之一，结果最好能被 1024 整除。

<property>
    <name>yarn.scheduler.maximum-allocation-mb</name>
    <value>2048</value>
</property>

上面设置 yarn.nodemanager.resource.memory-mb 的配置是 6G，6144 / 4 = 1536，显然 1536 无法被 1024 整除，所以这里直接设置为 2G，向上取整。

参数四

该参数定义了 YARN 调度器允许的单个容器的最小内存分配，默认为 1G。

<property>
    <name>yarn.scheduler.minimum-allocation-mb</name>
    <value>512</value>
</property>

这里直接调为 512MB 就行了，如果内存很多，可以往上调。

参数五

分配给单个容器的最小与最大虚拟核心数量。

<!-- 容器最小虚拟核心数 -->
<property>
        <name>yarn.scheduler.minimum-allocation-vcores</name>
        <value>1</value>
</property>

<!-- 容器最大虚拟核心数 -->
<property>
        <name>yarn.scheduler.maximum-allocation-vcores</name>
        <value>2</value>
</property>

根据单节点虚拟总核心数来进行配置，最小设为 1 个，最大设置为虚拟总核心的四分之一，上面设置虚拟核心为 6 个，这里向上取整，所以最大设置为 2 个。

扩展配置1

设置 NodeManager 是否启用虚拟内存检查，默认值：true（启用虚拟内存检查）。

<property>
	<name>yarn.nodemanager.vmem-check-enabled</name>
	<value>false</value>
</property>

当设置为 true 时（默认值），NodeManager 将启用虚拟内存检查。这意味着 YARN 应用程序的每个容器将受到虚拟内存限制的限制，一旦超过就会直接 kill 掉该容器。

当设置为 false 时，NodeManager 将禁用虚拟内存检查。这意味着容器将不会受到虚拟内存的限制，容器可以使用尽其所能的虚拟内存，但这可能会增加系统的风险，因为应用程序可以在不受约束的情况下使用虚拟内存，可能导致系统不稳定。

根据当前集群环境用途自行决断吧，学习阶段尽量设置为 false，不然可能会导致很多任务都跑不了，直接被 kill 掉。

扩展配置2

用于设置虚拟内存与物理内存之间的比率，默认为 2.1 倍。

这个参数的目的是限制应用程序可以使用的虚拟内存量，以避免某个应用程序无限制地占用虚拟内存资源，导致其他任务和应用程序受影响。

<property>
	<name>yarn.nodemanager.vmem-pmem-ratio</name>
	<value>2.1</value>
</property>

扩展配置应用场景

未关闭虚拟内存检查之前，由于虚拟内存不足，在运行任务时，你可能会看到如下所示的 Hive SQL 报错信息：

Execution Error，return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

在历史服务器中，查看详细报错信息如下：

[2023-09-01 20:39:05.542]Container [pid=64762,containerID=container_1693562800213_0002_01_000006] is running 324684288B beyond the ‘VIRTUAL’ memory limit. Current usage: 237.6 MB of 1 GB physical memory used; 2.4 GB of 2.1 GB virtual memory used. Killing container.

提示，虚拟内存超出限制，当前容器正在使用 1G 物理内存中的 237.6MB 内存，正在使用 2.1G 虚拟内存中的 2.4G 虚拟内存，显然这超出了限制，那么为什么会出现这种情况呢？

这是因为我只给单个 Map 和 Reduce 任务分配了 1G 内存，所以这 1G 内存按照默认物理内存与虚拟内存转化率(yarn.nodemanager.vmem-pmem-ratio)来算，1024 * 2.1 = 2150.4，所以对应着虚拟内存最大为 2.1G，但是由于这个任务需要 2.4G 虚拟内存才可以运行，所以导致容器被直接 kill 掉。

这里不建议直接将虚拟内存比率调大，可以直接关闭虚拟内存检查来进行解决（实战别关，实战内存一般都很大，关了反而会影响系统稳定性）。

mapred-site.xml 配置文件优化

参数一

定义了单个 Map 与 Reduce 任务使用的最大内存分配量，以 MB 为单位，默认值都为 1024。

注意，这两项参数都不可以超过单个容器的最大内存分配量(yarn.scheduler.maximum-allocation-mb)，避免单个 Mapper 或者 Reduce 任务使用超过 YARN 调度器允许的最大内存，导致任务运行异常。

<property>
    <name>mapreduce.map.memory.mb</name>
    <value>1024</value>
</property>

<property>
    <name>mapreduce.reduce.memory.mb</name>
    <value>1024</value>
</property>

前面我们设置单个容器的最大内存分配量为 2G，所以这里设置为默认值 1G 更合理，如果有条件，设置为 2G 更好。

其实，实际比例应该设置为 8:1（【单个容器最大内存分配量】 : 【单个 Map 与 Reduce 任务使用的最大内存分配量】）。但是说回来，没有绝对的比例，设置为 2G 也够用了，根据实际情况来吧。

参数二

定义了单个 Map 与 Reduce 任务使用的最大虚拟核心数，默认值都为 1。

注意，这两项参数都不可以超过单个容器的最大虚拟核心数(yarn.scheduler.maximum-allocation-vcores)，避免单个 Mapper 或者 Reduce 任务使用超过 YARN 调度器允许的最大虚拟核心数，导致任务运行异常。

<property>
    <name>mapreduce.map.cpu.vcores</name>
    <value>1</value>
</property>

<property>
    <name>mapreduce.reduce.cpu.vcores</name>
    <value>1</value>
</property>

前面我们设置单个容器的最大虚拟核心数为 2，所以这里设置为默认值 1 更合理，根据实际条件向上调吧。

分组聚合优化 —— Map-Side

在 Hadoop MapReduce 中，Map-Side 聚合是一种优化技术，用于在 Map 任务阶段进行部分数据聚合，以减少数据传输到 Reducer 任务的量。

Map-Side 聚合是一种有效的性能优化技术，可以减少 MapReduce 作业中的数据传输和磁盘写入/读取，从而提高作业的执行速度。

优化参数解析

以下是在 Hive 中设置 Map-Side 聚合相关的关键参数，以及它们的详细解释：

1. hive.map.aggr

• 默认值：true

• 用于启用 Map-Side 聚合功能，默认开启。Hive 会尝试在 Map 任务中执行一些简单的聚合操作，例如 SUM、COUNT 等，以减少 Map 输出的数据量。这可以降低作业的整体负载，提高查询性能，特别是对于一些聚合型的查询。

这可能会降低 Reducer 的负载，但同时会增加 Map 任务的计算负担。如果查询需要更复杂的聚合操作或跨多个分组键的聚合，可能无法完全受益于 Map-Side 聚合。

2. hive.map.aggr.hash.min.reduction

• 默认值：0.5

• 这个参数的值是一个浮点数，表示 Map-Side 聚合的最小减少量的阈值。阈值的范围是 0 到 1 之间，0 表示不启用 Map-Side 聚合，1 表示始终启用 Map-Side 聚合。

如果设置为 0.5，表示只有当 Map 任务中的聚合操作可以减少至少 50% 的数据量时，才会启用 Map-Side 聚合。

如果设置为 1，表示无论聚合操作能否减少数据量，都始终启用 Map-Side 聚合。

如果设置为 0，表示禁用 Map-Side 聚合，不管聚合操作是否有助于减少数据传输到 Reducer 的数量。

要注意的是，过大的阈值可能导致 Map-Side 聚合不经常发生，从而减少其性能优势。过小的阈值可能导致频繁的 Map-Side 聚合，增加了 Map 任务的计算开销。因此，合适的阈值应该基于具体查询和数据集的特点进行调整和测试。

3. hive.groupby.mapaggr.checkinterval

• 默认值：100000

• 控制 Map-Side 聚合的检查条数，用于验证任务是否满足聚合条件。

通俗来说就是，在开启 Map-Side 聚合操作后，当我们执行了聚合操作，在 Map 阶段系统会自动取前 100000 条数据取进行判断，此时，会出现下面两种情况：

• 如果其中的聚合键值大部分都一样，那么就会执行 Map-Side 聚合操作。

• 如果大部分聚合键值都不一样，那么就不会进行 Map-Side 聚合操作。

这个判断很容易会受到数据的分布影响，假设前 100000 行数据前面都不一样，只是因为数据量大，但其实后面有很多聚合键值都一样的数据，所以这就会造成判断不符合 Map-Side 聚合操作。

这种情况我们就需要根据实际情况进行判断了，如果聚合后数据量确实少了一半，我们可以强制开启 Map-Side 聚合操作。

4. hive.map.aggr.hash.force.flush.memory.threshold

• 默认值：0.9

• 用于控制 Map-Side 聚合的内存阈值，指定 Map 任务在进行 Map-Side 聚合时，何时强制将内存中的数据写入磁盘以释放内存。

当 Map 任务的内存中数据占用达到或超过这个阈值时，Map 任务将强制将内存中的数据写入磁盘以释放内存，从而避免 OOM（内存溢出）错误。

优化案例

未开启 Map-Side 聚合执行

set hive.map.aggr = false;

执行如下 Hive SQL，数据量大约 1000w 行：

select
    product_id,
    count(1)
from order_detail
group by product_id;

执行计划：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1
""
STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: order_detail
            Statistics: Num rows: 13066777 Data size: 11760099340 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: product_id (type: string)
              outputColumnNames: product_id
              Statistics: Num rows: 13066777 Data size: 11760099340 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: product_id (type: string)
                sort order: +
                Map-reduce partition columns: product_id (type: string)
                Statistics: Num rows: 13066777 Data size: 11760099340 Basic stats: COMPLETE Column stats: NONE
      Execution mode: vectorized
      Reduce Operator Tree:
        Group By Operator
          aggregations: count()
          keys: KEY._col0 (type: string)
          mode: complete
"          outputColumnNames: _col0, _col1"
          Statistics: Num rows: 6533388 Data size: 5880049219 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 6533388 Data size: 5880049219 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
""
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

从执行计划中可以看出，这段代码在 Map 阶段并没有进行聚合操作，在进行 Reduce 操作前，数据量并未发生任何变化。

执行结果，运行 43 秒：

开启 Map-Side 聚合执行

set hive.map.aggr = true;
# 其余参数都保持默认

执行同上 Hive SQL：

select
    product_id,
    count(1)
from order_detail
group by product_id;

执行计划：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1
""
STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: order_detail
            Statistics: Num rows: 13066777 Data size: 11760099340 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: product_id (type: string)
              outputColumnNames: product_id
              Statistics: Num rows: 13066777 Data size: 11760099340 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: count()
                keys: product_id (type: string)
                mode: hash
"                outputColumnNames: _col0, _col1"
                Statistics: Num rows: 13066777 Data size: 11760099340 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  key expressions: _col0 (type: string)
                  sort order: +
                  Map-reduce partition columns: _col0 (type: string)
                  Statistics: Num rows: 13066777 Data size: 11760099340 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col1 (type: bigint)
      Execution mode: vectorized
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          keys: KEY._col0 (type: string)
          mode: mergepartial
"          outputColumnNames: _col0, _col1"
          Statistics: Num rows: 6533388 Data size: 5880049219 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 6533388 Data size: 5880049219 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
""
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

从执行计划中可以看到，我们虽然已经开启了 Map-Side 聚合操作，在 Mape 阶段出现了 Group By 聚合操作，可是我们进入 Reduce 阶段前的数据量并没有变少，和之前一样。

这是因为在上面提到的数据分布影响造成的问题，因为 hive.groupby.mapaggr.checkinterval 默认只检查前 100000 行来验证是否进行 Map-Side 聚合操作，由于这里数据量比较大，导致前 100000 行的聚合键值大部分不相同（它觉得即使对这 100000 行数据进行了聚合操作，也达不到数据量减少 50% 的程度），所以它才没有进行 Map-Side 聚合操作，在这种情况下，需要强制开启 Map-Side 聚合操作。

set hive.map.aggr.hash.min.reduction = 1;

执行结果，运行 29 秒：

可见，速度的确得到大幅提升！

从历史服务器中查看执行该任务的一个 Map 中可以看出，在 Map 阶段的确发生了 Map-Side 聚合操作。

Map 阶段数据量前后对比少了很多，这就是 Map-Side 的玩法。

Join 优化

Map Join

Map Join 是 Hive 中的一种特殊类型的 Join，它用于处理大型维度表与较小事实表之间的连接操作，以提高查询性能。Map Join 利用了 Hive 中的 Map-Side Join 机制，将维度表加载到内存中，并在 Map 阶段执行连接操作，从而减少了数据的传输和磁盘读取，提高了查询性能。

相关参数

1. hive.auto.convert.join，默认值为 true，以允许 Hive 自动将适合 Map Join 的连接转换为 Map Join。

2. hive.mapjoin.smalltable.filesize，默认值为 25000000（以字节为单位，差不多25MB），用于指定哪些表被视为小表的文件大小阈值，如果表的大小低于此阈值，它将被认为是小表，可以用于 Map Join。

3. hive.mapjoin.bucket.cache.size，默认值为 100（表示桶缓存中最多可以存储 100 个桶表），用于配置 Map Join 时的桶缓存大小，桶缓存用于缓存桶表以提高连接性能。

4. hive.auto.convert.join.noconditionaltask ，默认值为 true，在无条件限制的情况下，允许将 Join 转换为 Map Join。【当连接操作包含条件限制时，Hive 不会自动将其优化为 Map Join 或 Sort Merge Join。它会按照连接操作中指定的条件来执行连接，确保满足条件的行被正确连接，例如 Where 子句等】

5. hive.auto.convert.join.noconditionaltask.size，默认值为 10000000 （以字节为单位，差不多 10MB），设置在没有条件限制的情况下自动将连接操作转换为 Map Join 或 Sort Merge Join 的大小阈值。

   这个参数的作用是定义了一个阈值，当连接操作中的中间表（即中间结果临时表）大小低于该阈值时，Hive 将自动尝试将连接操作转换为 Map Join 或 Sort Merge Join，以提高性能。

工作原理

1. Map Join 需要一个小的维度表（通常是维度表）和一个较大的事实表。

2. 在执行查询之前，Hive 会将维度表加载到 Map 任务的内存中。

3. 当执行 Join 操作时，Map 任务会将事实表的每一行与内存中的维度表进行连接。由于维度表已加载到内存中，连接操作非常快速。

4. Map 任务将连接后的结果发送给 Reducer 进行进一步的处理（如果需要的话）。

Map Join 在处理小型维度表与大型事实表的连接时非常有用，但并不适用于所有情况。对于两个大型表之间的连接，Map Join 可能不是最佳选择，因为它可能导致内存不足的问题。因此，在使用 Map Join 时，应根据表的大小和系统资源来评估是否适合。

Map Join 优化案例

需求说明

假设现在有如下表：

• 销售数据的事实表 order_detail（1000多万行数据）

• 产品信息表 product_info

• 省份信息表 province_info

我们想要获取每个销售记录对应的产品信息以及省份信息。

产品信息表和省份信息表这个两个维度表显然是小表，而销售数据事实表显然是大表，这三张表进行 Join 操作，符合 Map Join 条件。

不启动 Map Join 执行

# 我这里手动对 Map Join 进行关闭
set hive.auto.convert.join = false;

执行如下 Hive SQL：

select
    *
from 
	order_detail od
join 
	product_info product 
on 
	od.product_id = product.id
join 
	province_info province 
on 
	od.province_id = province.id;

YARN 查询任务执行时长 4m49s，可见速度实在是太慢了，现在对其进行优化。

启动 Map Join 执行

# 其余参数保持默认
set hive.auto.convert.join = true;

运行同样的 SQL，YARN 查询任务执行时长 1m5s，速度直接快了两倍多。

启动 Map Join 执行，调整小表阈值

# 获取两个小表大小，以字节为单位
describe extended product_info; -- totalSize=25285707
describe extended province_info; -- totalSize=369

# 其余参数保持默认
set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize = 25285707; -- 由于默认值为 25000000，其中有一个小表大于该值，故需要进行调整

运行同样的 SQL，YARN 查询任务执行时长 39s，速度又进一步得到了提升。

启动 Map Join 执行，调整小表阈值，调整无条件自动转换阈值

# 获取两个小表大小，以字节为单位
describe extended product_info; -- totalSize=25285707
describe extended province_info; -- totalSize=369

# 其余参数保持默认
set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize = 25285707; -- 由于默认值为 25000000，其中有一个小表大于该值，故需要进行调整
set hive.auto.convert.join.noconditionaltask.size = 25285707; -- 由于默认值为 10000000，其中有一个小表大于该值，故需要进行调整

运行同样的 SQL，YARN 查询任务执行时长 30s，相较于只调整小表阈值，速度又得到了提升，但随之而来的代价就是会消耗更多的资源。

Bucket Map Join

Bucket Map Join 是 Hive 中的一种连接操作优化技术，它结合了 Map Join 和 Bucket 两种机制，用于提高连接操作的性能。Bucket Map Join 适用于连接两个桶化表，其中桶化表是指在创建表时，将表的数据按照某个列的哈希值分成多个桶的表。

由于这些条件的要求比较严格，因此 Bucket Map Join 不支持自动转换，需要在查询中使用 Hint 显式指定优化器，结合适当的桶化表和连接条件，并启用 Bucket Map Join 相关的配置参数，便可以使用这种优化技术。

什么是 Hint?

在数据库和 SQL 查询中，“Hint” 是一种用于指导数据库查询优化器执行查询的特殊注释或指令。它允许数据库管理员或开发人员向查询优化器提供额外的信息，以帮助优化器生成更有效的查询计划。SQL Hint 通常以特定的注释形式嵌入到查询语句中，以提示数据库系统如何执行查询。如下所示：

SELECT 
	/*+ INDEX(employee_idx) */ 
	employee_name
FROM 
	employees
WHERE 
	department_id = 10;

在上述示例中，使用了 Hint /*+ INDEX(employee_idx) */ 来强制使用名为 employee_idx 的索引来执行查询，而不是让优化器自行选择索引。

相关参数

1. hive.optimize.bucketmapjoin，默认值为 false，用于启用或禁用 Bucket Map Join。设置为 true 时，Hive 将尝试使用 Bucket Map Join 来优化连接操作。

2. set hive.cbo.enable，默认值为 true，用于指定是否开启 CBO 优化。如果我们需要使用 Bucket Map Join ，则必须使用 Hint 指定优化器的行为，但如果我们开启了 CBO 优化，可能会导致 Hint 被直接忽略，无法使用，所以当我们使用 Bucket Map Join 操作时，应该将其设置为 false。

3. hive.ignore.mapjoin.hint，默认值为 true，在 Map Join 操作时默认会忽略 Hint 信息，如果我们需要使用 Bucket Map Join，需将该参数设置为 false。

CBO（Cost-Based Optimization，基于成本的优化）是数据库查询优化的一种方法，它使用统计信息和查询计划的成本估算来确定最佳的查询执行计划。在 CBO 中，数据库优化器会尝试根据统计数据和成本模型来选择最优的查询执行计划，以实现更好的性能。

工作原理

1. 桶化表：首先，两个参与连接的表必须都是桶化表，也就是说，它们的数据已经按照某个列的哈希值分成了多个桶。桶化表的好处是，数据分布更均匀，且每个桶中的数据大小相对较小。

2. Map Join 加速：当执行连接操作时，如果两个表都是桶化表，并且连接的条件基于桶列的哈希值，然后由此构建一个哈希表，通常在内存中，将另一个表的数据与之连接。哈希表中的键是连接条件列的哈希值，而值是与之匹配的行的引用或数据。这样，连接操作可以在 Map 阶段使用哈希表来查找匹配的行，而不需要加载整个大表到内存中。

3. 连接条件：连接条件通常是基于哈希值的等值条件，例如，table1.bucket_column = table2.bucket_column，其中 bucket_column 是两个表的桶列。

这就是为什么 Bucket Map Join 被称为 Map Join 的原因，因为它使用了哈希表来实现连接操作，而不是将所有数据加载到内存中，这种方法可以节省内存资源，特别适用于连接大型表。

Bucket Map Join 优化案例

假设有两个桶化表 sales 和 products，我们想要连接它们以获取每个销售记录的产品信息。如下所示：

-- 创建桶化表 sales
CREATE TABLE sales (
    sale_id INT,
    product_id INT,
    sale_amount DOUBLE
)
CLUSTERED BY (product_id) INTO 8 BUCKETS;

-- 创建桶化表 products
CREATE TABLE products (
    product_id INT,
    product_name STRING
)
CLUSTERED BY (product_id) INTO 4 BUCKETS;

-- 执行 Bucket Map Join
SELECT
 	/*+ mapjoin(p) / 
	s.sale_id, p.product_name, s.sale_amount
FROM 
	sales s
JOIN 
	products p 
ON 
	s.product_id = p.product_id;

在上述示例中，两个表 sales 和 products 都被分成了 4 个桶，并且连接条件是基于 product_id 列的哈希值。在 Hint 信息中指定进行 Map Join，填入较小的表的名称，因此，Hive 可以执行 Bucket Map Join 来加速连接操作。

扩展——如何确定分桶的数量

我们知道，Map Join 的操作是在内存中完成的，所以分桶的数量和 Map 的内存有关，单个 Map 默认的内存为 1024MB。

假设我当前有售卖表 800MB 和产品信息表是 100MB ，现在需要使用 Bucket Map Join 来进行连接优化，我们需要创建其对应的分桶表，那么该如何确定这两张表的分桶数量呢？

售卖表 800MB（大表），这里分配 12 个桶，800/12，每个桶大概 66MB 数据，单个桶数据量不要太大就行，视情况而定。

产品信息表 100MB（小表），分配规则：单个桶的数据量*10 小于单个 Map 内存的二分之一，这里分配 4 个桶，100/4=25，每个桶大概 25MB。

因为数据在 Map 阶段会加载到内存中进行处理，同时也不能占用太多 Map 内存，否则可能会导致其它异常。（大概 10M 内存处理 1MB 数据，差不多 10 倍）

注意，大表的分桶数与小表的分桶数保持倍数关系，这样数据就会按照 Join Key 做 Hash Bucket。小表依然复制到所有节点，在进行 Map Join 的时候，小表的每一组 Bucket 加载成 HashTable，与对应的一个大表 Bucket 做局部 Join，这样每次只需要加载部分 HashTable 就可以了。

Sort Merge Bucket Map Join

Sort Merge Bucket Map Join 是 Hive 中一种高级的连接操作优化技术，简称 SMB Join，它结合了 Sort Merge Join 和 Bucket Map Join 两种机制，用于提高连接操作的性能。这种优化技术适用于连接两个桶化表并要求执行 Sort Merge Join 的情况。

Sort Merge Join 适用于连接大型数据集，特别是当连接的两个表都已经按照连接条件的列进行了排序时，这种连接方式通常会比其他连接方式（如 Map Join）更高效。

相关参数

1. hive.optimize.bucketmapjoin.sortedmerge，默认值 false，该参数用于启用或禁用 Sort Merge Bucket Map Join。设置为 true 时，Hive 将尝试使用 Sort Merge Bucket Map Join 来优化连接操作。

2. hive.auto.convert.sortmerge.join，默认值 true，该参数用于控制是否启用自动转换为 Sort Merge Join。当设置为 true 时，如果查询中的连接操作满足 Sort Merge Join 的条件，Hive 将尝试自动将连接操作转换为 Sort Merge Join，以提高查询性能。

自动转换为 Sort Merge Join 仅会在查询优化器认为这是一个有效的优化时才会生效。查询优化器会根据查询的条件、表的大小和排序等信息来决定是否使用 Sort Merge Join。此外，如果查询中使用了 SQL Hint 或者在表的定义中使用了 SORTED BY 子句，也会影响是否执行 Sort Merge Join。

工作原理

1. 桶化表：首先，两个参与连接的表必须都是桶化表，也就是说，它们的数据已经按照某个列的哈希值分成了多个桶。

2. Sort Merge Join：Sort Merge Join 是一种连接操作的优化方式，它要求连接的输入表都按照连接条件的列进行排序，然后执行合并操作。这通常用于连接大型数据集。

3. Bucket Map Join：Bucket Map Join 是另一种连接操作的优化方式，它要求连接条件基于桶列的哈希值。Bucket Map Join 会构建一个哈希表，通常在内存中，然后将另一个表的数据与之连接。哈希表中的键是连接条件列的哈希值，而值是与之匹配的行的引用或数据。这样，连接操作可以在 Map 阶段使用哈希表来查找匹配的行，而不需要加载整个大表到内存中。

两个分桶表的分桶字段、排序字段、连接字段，三者必须保持一致，才会触发 SMB Join。

Sort Merge Bucket Map Join 优化案例

使用 Sort Merge Bucket Map Join 需要确保两个连接的桶化表都满足要求，并启用相关的配置参数。在查询中，只需编写正常的连接查询，Hive 会尝试根据查询的条件和表的桶化信息来选择最佳的连接方式。

假设我们有两个桶化表 orders 和 order_details，它们分别表示订单和订单详情，并且连接条件是订单号 (order_id)。我们希望获取每个订单及其相关的订单详情信息。

首先，我们创建两个桶化表并为它们指定排序列：

-- 创建桶化表 orders，并指定排序列 order_id
CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT
)
CLUSTERED BY (order_id) SORTED BY (order_id) INTO 8 BUCKETS;

-- 创建桶化表 order_details，并指定排序列 order_id
CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
)
CLUSTERED BY (order_id) SORTED BY (order_id) INTO 8 BUCKETS;

接下来，我们执行如下连接查询：

-- 执行 SMB Join
SELECT
	o.order_id, o.order_date, od.product_id, od.quantity, od.price
FROM 
	orders o
JOIN 
	order_details od 
ON 
	o.order_id = od.order_id;

开始执行后，Hive 将尝试使用 SMB Join 来执行连接操作，充分利用了两个表已经按照 order_id 排序和桶化的优势，以提高查询性能，在这种情况下，使用 SMB Join 是最优解。

注意，连接的两个表应该具有相同数量的桶，并且这些桶应该按照相同的列进行哈希分桶和排序。这是因为 SMB Join 需要将两个表的数据按照连接条件列的哈希值排序，然后执行合并操作。如果桶的数量和排序不一致，连接操作可能会失败或产生不正确的结果。

数据倾斜优化

数据倾斜在大数据处理中是一个常见的问题，它指的是在数据分布中某些键值（或分区）的数据量远远超过其他键值，导致在处理过程中某些任务负载不均衡，从而影响性能。

分组聚合数据倾斜优化

Map-Side

请查看本篇文章中【分组聚合优化 —— Map-Side】内容进行详细了解。

优点

• 低延迟：Map-Side 通常可以更快地处理数据倾斜，因为它在 Map 阶段执行，而不需要等待 Reduce 阶段。

• 不引入额外的分区：Map-Side 处理通常不需要引入额外的分区或数据分拆，因此可以减少数据移动和存储开销。

缺点

• 需要合适的数据分布：Map-Side 处理需要确保数据可以合理地分布到多个 Map 中，否则可能无法有效地处理数据倾斜。

• 内存溢出：每个 Map 的内存是有限的，如果某个倾斜 Key 的数据量很大，导致 Map 无法处理，就会让 Map 进行强制刷写操作，以释放内存，但这会增加磁盘 I/O 开销，降低性能，还可能导致 Map 任务失败。

Skew-GroupBy

Skew-GroupBy 是一种用于处理数据倾斜的 Hive 参数。它主要用于在执行 GROUP BY 操作时应对数据倾斜的情况。

以下是 Skew-GroupBy 参数的详细说明：

• hive.groupby.skewindata，默认值为 false，用于控制是否启用 Skew-GroupBy 功能。当设置为 true 时，Hive 将尝试检测数据倾斜，并采取措施来处理它。

启动 Skew-GroupBy 策略后，通常会涉及启动两个 MapReduce 任务来处理数据倾斜的情况。

主要工作流程如下：

1. Mapper 阶段：首先，Hive 会启动一个 MapReduce 任务，其中包含多个 Mapper。这些 Mapper 负责读取原始数据并执行初始映射操作。

2. Partitioning 阶段：在 Map 阶段，当检测到数据倾斜时，Hive 会通过随机数生成额外的虚拟分区以处理倾斜键值。这些分区不一定对应于物理存储，但它们用于将数据重新分配和均衡到多个 Reduce 任务中。

3. Reducer 阶段：然后，Hive 启动另一个 MapReduce 任务，其中包含多个 Reduce。这些 Reduce 负责处理前一阶段生成的虚拟分区，执行 GROUP BY 操作，以及生成最终的聚合结果。

4. 数据合并：在 Reduce 阶段，每个 Reduce 任务处理一个或多个虚拟分区，执行 GROUP BY 操作，然后将结果发送给主 Reduce 任务。

5. 主 Reduce 阶段：最后，主 Reduce 任务负责接收来自所有其他 Reduce 任务的部分结果，并将它们合并成最终的 GROUP BY 结果。

通过启动两个 MapReduce 任务，Hive 能够处理数据倾斜的情况。第一个 MR 任务用于数据的重新分区和重新分配，以减轻数据倾斜，而第二个 MR 任务用于执行实际的 GROUP BY 操作并生成最终的结果。

虽然可以通过这种方式稳定的解决数据倾斜，但是会引入额外的计算和数据重分布开销，这可能会导致查询的执行时间略微延长，消耗的资源更多。

Join 数据倾斜优化

当执行 JOIN 操作时，连接的两个表中某些连接条件的键值数据量远远超过其他键值，从而导致连接操作性能下降的情况。

这种数据倾斜可能会导致一些任务负载过重，而其他任务负载轻，影响整个查询的执行时间。

Join 数据倾斜通常是由数据分布不均匀或数据特性造成的，这在大型数据集和复杂查询中经常会遇到。

Map Join

请查看本篇文章中【Join 优化 —— Map Join】内容进行详细了解。

Skew-Join

Skew-Join 优化是一种用于解决 Join 操作中数据倾斜问题的优化策略，适用于大型数据集中存在数据倾斜的情况，提高连接操作的性能和稳定性，同时确保任务的负载均衡。

以下是 Skew-Join 参数的详细说明：

• hive.optimize.skewjoin，默认值为 false，用于启用或禁用 Skew-Join 优化。

• hive.skewjoin.key，默认值为 100000 行，触发 Skew-Join 操作的阈值，当某个 Join Key 的值超过该 100000 行时，此时就可以触发 Skew-Join 优化（前提是开启了该参数）。

Skew-Join 运行过程中通常会包括多个 MR 任务，这些任务的数量和顺序会根据优化策略和具体的实现而有所不同，但通常包括以下几个关键步骤：

1. Map 阶段：首先，启动一个或多个 MR 任务，其中包含多个 Map。每个 Map 负责读取连接操作所涉及的表的数据，并执行映射操作。

2. 数据分析和标记：在 Map 阶段，系统会对连接条件进行分析，检测数据倾斜并标记倾斜的连接条件。这一步通常是自动的，系统会根据数据的分布情况进行判断。

3. 生成额外任务：一旦检测到数据倾斜，系统会生成额外的任务来处理倾斜连接条件。这些任务通常称为 “Skew-Join 任务” 或 “Skew 处理任务”。

4. 数据重分配：数据倾斜的数据会被重新分配到多个 Skew-Join 任务中，以确保每个任务的负载更加均衡。这可以通过将倾斜键值的数据拆分为多个小分区来实现。

5. Skew-Join 任务执行：Skew-Join 任务并行执行，它们负责处理倾斜连接条件的数据。每个任务通常处理其中一部分倾斜数据，以加快处理速度。

6. 数据合并：最后，Skew-Join 任务的结果会被合并以生成最终的 JOIN 结果。这一步通常由额外的 MR 任务或 Reduce 阶段完成，合并所有 Skew-Join 任务的结果。

和 Skew-GroupBy 一样，Skew-Join 可以带来稳定的解决方案，但同样会引入额外的计算和数据重分布开销，导致消耗的资源更多，执行任务的时间更长。

Map Join 与 Skew-Join 优化对比

Skew-Join 优化和 Map Join 优化都是处理连接操作中的数据倾斜问题的优化策略，但它们的工作方式和应用场景有所不同。

Skew-Join 优化

• 适用场景：Skew-Join 优化主要用于处理连接操作中的数据倾斜，特别是在 JOIN 操作中，其中某些连接条件的键值数据量远远超过其他键值。它适用于处理数据倾斜且连接条件复杂的情况。

• 工作原理：Skew-Join 优化通过检测和标记倾斜连接条件，生成额外的任务来处理倾斜数据。这些任务负责重新分配数据、并行处理倾斜数据，最终合并结果，以确保任务的负载均衡。

• 复杂性：Skew-Join 优化通常比较复杂，因为它需要在连接操作中自动检测和处理数据倾斜，它可能涉及到多个 MapReduce 阶段和任务的协同工作，消耗的资源更大。

Map Join 优化

• 适用场景：Map Join 优化主要用于处理连接操作中的小表连接，其中一个表的大小适合放入内存中。它适用于处理小表连接，不涉及数据倾斜的情况（Reduce 阶段）。

• 工作原理：Map Join 优化将小表加载到内存中，以便在 Map 阶段执行连接操作。这避免了将小表的数据进行分发和重复读取，提高了连接操作的性能。

• 复杂性：Map Join 优化通常比较简单，因为它只涉及将小表加载到内存中，并在 Map 阶段执行连接操作。不需要额外的任务生成和数据重分配。

对比

• 适用性：Skew-Join 优化适用于处理数据倾斜的连接操作，但它相对复杂，涉及多个任务和阶段。Map Join 优化适用于小表连接，更简单，让数据倾斜在 Map 端就解决了。

• 性能：Skew-Join 优化可以解决数据倾斜问题，但可能会引入一些额外的计算和数据重分配开销。Map Join 优化通常更轻量，适用于小表连接，性能较高。

• 实现复杂性：Skew-Join 优化需要数据库系统或大数据框架自动检测和处理数据倾斜，因此通常比较复杂。Map Join 优化相对简单，因为它仅涉及小表加载到内存中的步骤。

如果存在数据倾斜，数据量大，Skew-Join 优化是更合适的选择。如果连接操作涉及小表连接且没有数据倾斜（Reduce 阶段），则 Map Join 优化可能更适用。

SQL 手动分区 & 扩容

手动分区其实就和 Skew-GroupBy 操作类似，我们通过手动优化 SQL 语句，根据倾斜 Join Key 添加分区键，然后根据生成的分区键进行分区，保持数据均衡，从而避免产生数据倾斜。为大表添加分区键，为小表进行扩容，防止 Join 后数据丢失（详情看示例）。

假设有两个表 orders 和 order_items，并且我们要连接它们以查找每个订单的详细信息，连接条件是 order_id。

原始查询可能如下所示：

SELECT 
	*
FROM 
	orders o
JOIN 
	order_items oi
ON 
	o.order_id = oi.order_id;

设想，我们发现 order_items 表中某些 order_id 的数据量非常大，导致 JOIN 出现数据倾斜。为了解决这个问题，我们可以手动调整 order_items 表，为其添加分区，将数据均匀地分散到不同的分区中；为 orders 表扩容，防止出现耦合性造成数据丢失。

首先，我们来对 orders_items 表进行手动分区：

-- 创建一个新的分区键 part_key，添加随机数分区键，将每个 order_id 随机分成 2 个分区(id_0、id_1)
SELECT
    *,
    concat(order_id ,"_",cast(rand()*2 as int) part_key
FROM
    orders_items;

-- 下面将 orders_items分区后的结果称为 orders_items_partitioned 表

这里我们临时称它为 orders_items_partitioned 表，其中 part_key 列是根据 order_id 列随机组合生成的值，将相同的 order_id 分成 2 个分区（分区数量根据数据量来确定），将其做为新的 Join Key，这样，每个分区中的数据量相对较小，避免了数据倾斜问题。

现在对 orders 表进行扩容，这里注意，你可能会疑惑，为什么在 orders 表中不能同样使用随机数组合来进行分区？

如果用了随机数，可能会导致出现某个 order_id 中的 part_key 生成结果全为 0 或 1，导致某些数据原本可以进行聚合，但因为分区后无法进行聚合，造成数据丢失的情况。

所以，为了避免这种情况的出现，我们需要直接指定其分区组，然后使用 union all 进行合并，完成扩容操作。

SELECT
    *,
    concat(order_id ,"_",0) part_key
FROM
    orders
UNION ALL
SELECT
    *,
    concat(order_id ,"_",1) part_key
FROM
    orders;

-- 下面将 orders 表分区后的结果称为 orders_partitioned 表

进行 Join 聚合操作：

SELECT 
	*
FROM 
	orders_partitioned op -- 源表 orders
JOIN 
	order_items_partitioned oip -- 源表 order_items
ON 
	op.part_key= oip.part_key;

最后，在 SELECT 的时候不扫描 part_key 列，剩下的数据就是 Join 之后的结果了，通过优化 SQL 进行手动分区和扩容的形式成功的解决了数据倾斜。

MR 并行度优化

并行度优化是指通过合理配置和调整 Hive 查询中的 MR 相关参数，以实现更好的查询性能和资源利用率。

Hive 查询通常由 Map Tasks 和 Reduce Tasks 组成。Map Tasks 用于读取和处理输入数据，而 Reduce Tasks 用于汇总和处理 Map Tasks 的输出，所以合理配置 Map 和 Reduce 任务的数量对查询性能至关重要，能够加速查询并充分利用集群资源。

Map 端并行度优化

相关参数

• mapreduce.input.fileinputformat.split.minsize，默认值为 1（以字节为单位），用于配置输入数据的切分大小的最小值。较小的切分大小会增加 Map 任务的数量，提高并行度，但可能导致任务启动和管理开销增加。【一般无需调整】

• mapreduce.input.fileinputformat.split.maxsize，默认值为 256000000（以字节为单位，差不多 256MB），用于配置输入数据的切分大小的最大值。较大的切分大小会减少 Map 任务的数量，降低并行度，但可能导致每个任务处理更多数据。【一般无需调整】

• hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat，默认指定为 CombineHiveInputFormat 模式，它的主要特点是能够合并小文件，优化并行度，从而减少 MapReduce 任务的数量，提高查询性能。【一般无需调整】

Reduce 端并行度优化

相关参数

• mapreduce.job.reduces，默认值为 -1，用于指定 MR 任务中 Reduce 的数量，-1 表示不进行指定，由系统自动进行分配。较小的值会减少 Reduce 任务的数量，但可能会导致每个 Reduce 任务处理更多数据。较大的值会增加 Reduce 任务的数量，但可能会导致每个任务处理较少数据。

• hive.exec.reducers.bytes.per.reducer，默认值为 256000000（以字节为单位，差不多 256MB），用于配置 Reduce 任务的输入数据大小。它指定每个 Reduce 任务的输入数据大小的上限，通过适当配置这个参数，可以控制 Reduce 任务的数量和任务的负载均衡。【一般无需调整】

• hive.exec.reducers.max，默认值为 1009 个，用于指定每个 Reduce 任务的最大数量。【一般无需调整】

Reduce 数量是如何进行自动计算的？

在 Hive 中，当我们设置 Reduce 数量自动分配时，也就是系统默认情况：

set mapreduce.job.reduces = -1;

此时会根据 Map 端输入数据的大小去和 Reduce 的输入数据量参数做除运算。

-- 控制 Reduce 的输入数据量参数
set hive.exec.reducers.bytes.per.reducer = 256000000; -- 默认值 256MB

假设，目前在 Map 端共有输入数据 1000MB，那么 Reduce 的数量计算如下：

1000 / 256 = 3.90625

完成判断后，Hive 就会分配 4 个 Reduce 去完成任务。

但是，我们 Map 端输入数据大小一定是 Map 端输出数据大小吗？当然不一定，在 Map 期间可能会进行 Map Join 聚合操作，所以数据量肯定会变少，这时候系统分配就很不合理了，所以我们需要去手动指定 Reduce 的个数，提高任务效率。

在 Spark 引擎中对该估算进行了优化，是以 Reduce 接收到的数据量进行判断的，更合理。

Hive 小文件优化

Map 端小文件优化

相关参数

• hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat，默认指定为 CombineHiveInputFormat 模式，它的主要特点是能够合并小文件，优化并行度，从而减少 MapReduce 任务的数量，提高查询性能。【一般无需调整】

• hive.merge.mapfiles，默认值为 true，该参数用于设置是否对 Map-only 任务输出的小文件进行合并。Map-only 任务通常是指只有 Map 阶段而没有 Reduce 阶段的任务。【一般无需调整】

Reduce 端小文件优化

相关参数

• hive.merge.mapredfiles，默认值为 false，该参数用于设置是否对 MapReduce 任务输出的小文件进行合并，这包括 Map 阶段和 Reduce 阶段的任务输出，启用后它会用额外的 MR 任务去完成小文件合并。【推荐开启】

• hive.merge.size.per.task，默认值为 256000000（以字节为单位，差不多 256MB），定义小文件合并后，新文件的最大阈值。【一般无需调整】

• hive.merge.smallfiles.avgsize，默认值为 16000000（以字节为单位，差不多 16MB），定义了触发小文件合并任务的阈值。如果某个计算任务的输出文件平均大小低于该阈值，那么 Hive 将触发一个合并任务来合并这些小文件。【一般无需调整】

CBO 优化

Hive 中的 CBO（Cost-Based Optimization，成本优化）是一种查询优化技术，它通过评估查询执行计划的不同选择，并选择最具成本效益的执行计划来提高查询性能。

CBO 在优化查询时考虑了查询中各个操作的代价估算，包括数据扫描、连接、过滤和聚合等操作的代价，以选择最佳的执行路径。

Hive 中的 CBO 优化默认开启。

SET hive.cbo.enable=true;

使用 CBO 优化可以显著提高 Hive 查询性能，特别是对于复杂的查询。但需要注意，CBO 优化也可能需要更多的计算资源和时间来生成和执行最佳的查询计划，当然，利大于弊！

谓词下推

谓词下推是一种查询优化技术，通常用于关系型数据库管理系统和分布式计算框架中，旨在提高查询性能。

该技术的主要思想是将查询条件（谓词）尽量提前应用于数据，以减少需要检索和处理的数据量，Hive 中默认开启谓词下推：

SET hive.optimize.ppd=true;

在谓词下推中，查询计划会尽早应用过滤条件，从而减少了从存储中检索的数据量，提高了查询的效率。

这通常涉及到以下几个方面：

1. 过滤条件下推：查询中的过滤条件（例如，WHERE 子句中的条件）会尽早应用于存储引擎或计算引擎，以减少需要检索的数据。这样，不满足条件的数据将在查询计划的早期被排除，从而减少了计算和传输的开销。

2. 列裁剪：在执行查询时，只检索查询所需的列，而不是全部列。这可以减少从磁盘读取的数据量，提高查询性能。列裁剪通常与谓词下推一起使用。

3. 统计信息和索引：数据库管理系统会使用统计信息来估算查询计划的成本，并选择最佳的执行路径。如果有适当的索引，系统可以使用索引来加速查询，并且可以根据索引的选择性来下推谓词。

4. 分区裁剪：在分区表中，谓词下推可以应用于分区裁剪，从而只扫描满足查询条件的分区。这可以减少分区表中需要处理的数据量。

5. 动态分区裁剪：在动态分区裁剪中，查询计划根据查询条件动态选择需要扫描的分区，以进一步减少数据的处理量。

谓词下推对于提高查询性能非常重要，特别是在处理大型数据集时。通过尽早应用查询条件，可以减少数据的传输、计算和存储开销，从而提高查询的效率。这是数据库系统和大数据处理框架中常见的查询优化技术之一。

矢量化查询

矢量化查询（Vectorized Query）是一种查询执行优化技术，用于提高查询性能和资源利用率。在 Hive 中，矢量化查询引入了一种新的查询执行引擎，它以一组值的矢量形式而不是逐行处理数据，从而实现了更高的查询吞吐量。

Hive 中默认开启矢量化查询：

-- 启用矢量化查询执行，默认开启
SET hive.vectorized.execution.enabled=true;

-- 启用 Reduce 阶段的矢量化执行，默认开启
SET hive.vectorized.execution.reduce.enabled=true;

1.优势

• 矢量化查询能够显著提高查询性能，特别是在批量处理大型数据集时。它通过减少计算和内存访问的开销来实现更高的查询吞吐量。

• 矢量化查询可以在一个批处理操作中处理多行数据，从而减少了处理每行数据的开销。这对于复杂的查询和聚合操作尤其有用。

2.支持的数据格式

• 矢量化查询通常需要数据以列式存储格式（如ORC或Parquet）进行存储，以便有效地执行列操作。这些格式支持压缩和列裁剪，有助于减少数据传输和处理成本。

3.查询优化规则

• 矢量化查询引擎使用一系列查询优化规则来转换和优化查询计划，以充分利用矢量化执行。这包括将查询条件下推到扫描操作、选择最佳的列操作等。

4.查询语法不变：

• 使用矢量化查询时，查询语法不需要更改。您可以继续编写标准的 Hive SQL 查询，而不必担心矢量化查询的细节。

Hive 本地模式

Hive 本地模式是一种在本地计算机上运行 Hive 查询的模式，通常用于开发、调试和测试目的。在本地模式下，Hive 不会与分布式计算框架进行交互，而是在单个计算机上执行查询。

适用于小型查询或简单查询，它们可以在本地计算机上以较低的成本执行。对于大规模和复杂的查询，Hive 通常会选择在分布式模式下执行，以充分利用分布式计算资源。

-- 启动 Hive 自动本地模式执行，默认为 false
set hive.exec.mode.local.auto=true;

开启后，Hive 会根据查询的复杂性和数据规模自动决定是否在本地模式下执行查询。

本地模式下的其它参数

• hive.exec.mode.local.auto.inputbytes.max，默认值为 134217728（以字节为单位，128MB），用于设置查询输入数据的最大字节数。如果查询的输入数据大小小于或等于此值，Hive 可以选择在本地模式下执行查询。

• hive.exec.mode.local.auto.input.files.max，默认值为 4 个，用于设置查询输入文件的最大数量。如果查询涉及的输入文件数量小于或等于此值，Hive 可以选择在本地模式下执行查询。

Hive 严格模式

Hive 的严格模式（Strict Mode）是一种配置选项，用于增强 Hive 对查询和数据质量的检查，以减少错误和数据不一致性。

在严格模式下，Hive 会执行更严格的验证和检查，以确保查询和数据操作的准确性。

开启 Hive 严格模式需要在 Hive 的配置中设置相应的参数，默认为 nonstrict（不开启），设置为 strict 表示开启严格模式。

# 开启严格模式
set hive.mapred.mode=strict;

严格模式开启后限制操作

1.分区表查询时必须指定分区

• 对分区表查询 WHERE 条件中过滤字段没有分区字段；

2.ORDER BY 必须指定 LIMIT

• 使用 ORDER BY 查询时候，不带 LIMIT 语句；

3.限制笛卡尔积

• 笛卡尔积 JOIN 查询，JOIN 查询语句中不带 ON 条件或者 WHERE 条件。

笛卡尔积科普

笛卡尔积（Cartesian Product）是集合论中的一个概念，它表示两个集合之间的所有可能的组合。在数据库和计算机科学中，笛卡尔积通常用于描述两个或多个数据集之间的组合方式。

假设有两个集合 A 和 B，它们的笛卡尔积记作 A × B，表示集合 A 中的每个元素与集合 B 中的每个元素之间的所有可能组合。

例如，如果集合 A 包含 {1, 2}，集合 B 包含 {x, y, z}，那么 A × B 的笛卡尔积包含以下元素：

{(1, x), (1, y), (1, z), (2, x), (2, y), (2, z)}

在数据库中，笛卡尔积通常用于执行多表联接操作，其中两个表的笛卡尔积会生成一个包含所有可能组合的临时表。然后，根据联接条件，从这个临时表中筛选出所需的行，以生成联接结果。

需要注意的是，笛卡尔积可能会产生非常大的结果集，尤其是在数据集规模较大时。因此，在进行笛卡尔积操作时，需要谨慎，确保它符合查询需求，以避免不必要的性能问题。通常情况下，为了避免笛卡尔积，数据库系统会优化联接操作，并使用索引等方法来提高查询效率。文章来源地址https://www.toymoban.com/news/detail-689453.html

到了这里，关于Hive SQL 优化大全（参数配置、语法优化）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！