wordpress开启全站ssl永久免费用的在线客服系统

Hive SQL 优化指南

在使用 Hive 进行数据分析时，提高查询性能至关重要。以下是一些具体的优化策略，帮助我们在工作中更有效地管理和查询数据。

一、 减少数据量进行优化

1. 分区表优化

• 分区是一种表的子集，用于按某一列（如日期、地区等）将数据划分成多个部分。

• 当查询一个分区表时，Hive 会只扫描相关的分区，而不是整个表。这能显著减少需要读取的数据量，从而提高查询速度。

示例：

CREATE TABLE sales (id INT,amount DECIMAL(10,2),date STRING
) PARTITIONED BY (year INT, month INT);

在插入数据时，指定每个记录的分区信息。

2. 分桶表优化

• 分桶将数据分成多个“桶”，每个桶是一个独立的数据集合。

• 在进行 JOIN 操作时，分桶表可以避免全表扫描，提高查询效率。

示例：

CREATE TABLE employees (id INT,name STRING
) CLUSTERED BY (id) INTO 10 BUCKETS;

这里，表会按照 id 列划分为 10 个桶。

3. 拆分大表为临时表

• 将一个大表拆分成多个小的临时表。

• 小表的处理速度通常更快，可以在查询时更灵活地组合和查询。
  临时表用于存储在会话期间存在的数据，通常不需要持久化，主要用于存储临时计算结果。

优化原理：

• 避免数据写入磁盘：临时表只在会话中存在，在会话结束时自动消失，避免了对磁盘的I/O操作。
• 加速数据处理：适合用于存储需要在多个查询中使用的中间结果，可以减少重复计算，提高查询效率。

示例：

CREATE TEMPORARY TABLE temp_table AS
SELECT customer_id, COUNT(*) as order_count
FROM sales
GROUP BY customer_id;SELECT * FROM temp_table WHERE order_count > 10;

4. 列裁剪

• 只选择查询所需的列，万万不可使用 SELECT *。

• 聚合分析，连接其它表前使用列裁剪，能减少传输的数据量，降低 I/O 成本。

示例：

SELECT amount FROM sales;

5. 数据过滤

• 在聚合分析，连接其它表前使用 WHERE 子句提前过滤不必要的数据。

• 这样可以减少后续处理的数据量，提高性能。

示例：

SELECT SUM(amount) FROM sales WHERE amount > 1000;

6. 中间表制作

• 在执行复杂查询时，先将部分结果存入中间表，然后再进行后续查询。

• 这样可以让查询逻辑更清晰，也有助于提高性能。

中间表通常用于在复杂查询中存储中间结果，以便于后续的查询或分析。这种做法可以降低重复计算的开销。

优化方式：

• 分步执行：将复杂的查询拆分为多个小查询，使用中间表保存中间结果，避免重复计算。
• 数据分区：可以对中间表进行分区，以加速数据读取和查询。
• 聚合和过滤：在生成中间表时，可以进行初步的聚合和过滤，减少后续操作的数据量。

示例：

CREATE TABLE intermediate_table AS
SELECT customer_id, SUM(amount) AS total_amount
FROM sales
GROUP BY customer_id;SELECT * FROM intermediate_table WHERE total_amount > 1000;

---

二、 对数据进行压缩，行列存储格式转换

1. 磁盘 I/O

什么是 I/O？

• I/O 指的是数据在计算机系统与外部存储（如磁盘驱动器、SSD、HDFS）之间的传输过程。对大数据来说，这通常涉及从硬盘读取数据或将数据写入硬盘。

2. HDFS 的 工作原理

HDFS 中的数据存储

• HDFS 将文件分割成固定大小的块（默认是 128MB 或 256MB），并将这些块分散存储在多个节点上。每个块可能会有多个副本（通常是 3 个），以确保数据的可靠性。

数据访问

• 当进行查询时，Hive 需要访问存储在 HDFS 上的这些数据块。为了执行查询，Hive 需要读取相应的数据块，并将它们加载到内存中进行处理。

3. 磁盘 I/O 在 HDFS 中的影响

3.1 数据读取

• 读取效率：HDFS 的设计旨在处理大文件的顺序访问，但在执行复杂查询时，如果查询涉及多个数据块，就需要频繁进行磁盘读取。
• 随机访问 vs 顺序访问：虽然 HDFS 优化了顺序访问，但对于随机读取操作，磁盘 I/O 会显著增加，因为每次读取都可能涉及不同的物理位置，导致寻址时间增加。

3.2 数据写入

• 写入操作：在将数据写入 HDFS 时，系统同样需要进行 I/O 操作。写入操作必须将数据写入到多个节点上（副本），这也会消耗 I/O 带宽。
• 数据块的分散写入：HDFS 将文件的每个块写入不同的节点，这个过程可能会导致额外的 I/O 开销。

4. 压缩优化原理

4.1 减少 I/O 负担

• 小数据量：通过压缩，读取的数据量减少，进而减少了所需的磁盘 I/O 操作。例如，读取一个 100MB 的压缩文件可能只需读取 20MB 的数据。
• 提高效率：减少 I/O 直接提高了查询的速度，因为磁盘读取的次数减少，CPU 等待数据的时间也降低。

4.2 列式查询

• 优化查询：列式存储格式使得 Hive 在执行查询时能够跳过不必要的列读取，只读取与查询相关的列数据。当查询只涉及几个列时，列式存储可以显著减少读取的数据量。
• 聚合和扫描：列格式通常对聚合和分析操作进行了优化，能加速这些操作的执行速度。

---

三、 Hive 的 MapReduce阶段优化

在使用Hive进行大数据处理时，合理优化Map和Reduce的执行是提高任务效率的关键。下文将详细介绍如何优化Map和Reduce，以提高Hive任务的性能。

1. 合理设置Map数量

• 影响因素：

  • 输入文件的总数量和大小
  • 集群设置的文件块大小

• 优化策略：

  • 小文件过多时，每个文件会作为一个独立的Map任务，启动和初始化时间长，造成资源浪费。应尽量合并小文件以提高效率。
  • 如果文件较大且任务复杂，可以通过调整maxSize参数来增加Map任务数量，以减少每个Map处理的数据量，提高效率。

1.1 合并小文件（减少Map数量）

• 小文件过多弊端：

  • HDFS上每个文件需要在NameNode创建元数据，占用内存空间，影响索引速度。
  • 过多小文件会导致MapTask数量增加，单个MapTask处理数据量小，资源消耗大。

• 解决方案：

  1. 数据采集阶段合并小文件。
  2. 使用CombineHiveInputFormat在Map执行前合并小文件。

     set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
     

1.2 调整maxSize参数（增加Map数量）

• 要调整maxSize参数，可以在Hive的配置中设置以下属性：

SET mapreduce.input.fileinputformat.split.maxsize=<desired_size>;

注意：

• maxSize参数指的是每个Map任务处理的数据块的最大大小。通过调整这个参数，可以控制Map任务的数量，但它并不直接指定Map任务的数量。
• 将maxSize设置为小于HDFS的块大小（blocksize），这样可以分割数据成更多块，每个块会启动一个Map任务。
• 通过设置较小的maxSize，可以让输入数据分割成更多的块，从而增加Map任务的数量，减少每个Map处理的数据量。
• 例如，如果HDFS块大小是128MB，可以将maxSize设置为64MB，这样会生成更多的Map任务，每个任务处理较少的数据量。

2. 合理设置Reduce数量

• Reduce数量 = min(参数2, 总数据量/参数1)
• 可以在mapred-default.xml文件中设置参数2：

  <property><name>mapreduce.job.reduces</name><value>15</value>
  </property>
  

• Reduce数量不宜过多，以免资源浪费。

3. 设置缓冲区大小

• 默认缓冲区为100M，可以提升至200M，减少溢写次数，提高效率。

4. 使用压缩技术

• 使用Snappy压缩减少磁盘I/O，提高性能。

5. 提高MapTask默认内存

• 默认内存为1024M，可以根据需要提升以处理更大的数据量。

6. 增加MapTask的CPU核数

• 对于计算密集型任务，增加CPU核数可以提升处理速度。

7. 增加Reduce阶段的并行度

• 默认从Map中拉取数据的并行数为5，可以适当提高。

8. 提高ReduceTask的内存上限

• 可以根据任务需要，适当提高内存上限。

9. 提高ReduceTask的CPU核数

• 根据任务的复杂程度，增加CPU核数以提升性能。