Hive优化十八般兵器

最新推荐文章于 2024-10-06 18:24:21 发布
原创 最新推荐文章于 2024-10-06 18:24:21 发布 · 706 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#hive #hadoop #大数据

文章介绍了Hive中提升查询性能的多种方法，包括分区和分桶以减少数据扫描，谓词下推和MapJoin优化连接操作，使用LIMIT语句限制返回结果，以及CTE提高可读性和效率。此外，更新统计信息、选择合适的文件格式和压缩数据也是优化手段。窗口函数、并行执行和索引的使用也能提升查询效率。

前言

Hive 是一个基于 Hadoop 的数据仓库框架，用于处理和分析大量的结构化数据。在 Hive 中，我们可以编写类似于 SQL 的查询语句（HiveQL）来对数据进行处理。下面是一些 Hive 中的 SQL 优化示例，包括具体的数据结构、SQL 以及前后对比。

1. 分区（Partitioning）

假设我们有一个销售数据表 sales，包含以下字段：id（订单ID），date（订单日期），customer_id（客户ID），amount（订单金额）。如果我们需要经常根据日期来查询数据，可以对 sales 表进行分区。

  • 数据结构：
CREATE TABLE sales (
  id INT,
  date STRING,
  customer_id INT,
  amount DOUBLE
) PARTITIONED BY (year STRING, month STRING, day STRING);
  • 查询优化前：
SELECT COUNT(*)
FROM sales
WHERE date >= '2023-01-01' AND date < '2023-02-01';
  • 查询优化后：
SELECT COUNT(*)
FROM sales
WHERE year = '2023' AND month = '01';

通过分区，我们可以缩小数据范围，避免全表扫描，从而提高查询效率。

2. 分桶（Bucketing）

如果我们经常需要对 sales 表按客户ID进行聚合操作，可以对表进行分桶。

  • 数据结构：
CREATE TABLE sales (
  id INT,
  date STRING,
  customer_id INT,
  amount DOUBLE
) PARTITIONED BY (year STRING, month STRING, day STRING)
CLUSTERED BY (customer_id) INTO 256 BUCKETS;
  • 查询优化前：
SELECT customer_id, SUM(amount)
FROM sales
WHERE year = '2023' AND month = '01'
GROUP BY customer_id;
  • 查询优化后：
-- 语法和优化前相同，但由于表已经分桶，查询效率更高
SELECT customer_id, SUM(amount)
FROM sales
WHERE year = '2023' AND month = '01'
GROUP BY customer_id;

分桶可以使得对相同 customer_id 的数据进行聚合操作时更加高效，因为具有相同 customer_id 的数据被存储在同一个桶中。

3. 使用谓词下推（Predicate Pushdown）

对于包含多个表连接的查询，我们可以尽量将过滤条件放在连接操作之前，这样可以减少数据传输和计算量。

假设我们有一个客户表 customers，包含以下字段：id（客户ID），name（客户名称）。

  • 查询优化前：
SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.id
WHERE year = '2023' AND month = '01'
GROUP BY c.id, c.name;
  • 查询优化后：
SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM (SELECT * FROM sales WHERE year = '2023' AND month = '01') s
JOIN customers c
ON s.customer_id = c.id
GROUP BY c.id, c.name;

在优化后的查询中，我们将过滤条件（谓词）year = '2023' AND month = '01' 放在了连接操作之前，这样可以先过滤掉不符合条件的数据，然后再进行连接操作。这样可以减少数据传输和计算量，提高查询效率。

4. 使用 MapJoin（小表连接）

假设客户表 customers 相对较小，我们可以利用 Hive 的 MapJoin（小表连接）优化技术，在 Map 阶段完成连接操作，从而减少 Reduce 阶段的计算量。

  • 查询优化前：
SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM (SELECT * FROM sales WHERE year = '2023' AND month = '01') s
JOIN customers c ON s.customer_id = c.id
GROUP BY c.id, c.name;
  • 查询优化后：
SET hive.auto.convert.join=true;
SET hive.mapjoin.smalltable.filesize=25000000; -- 根据实际情况设置

SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM (SELECT * FROM sales WHERE year = '2023' AND month = '01') s
JOIN customers c ON s.customer_id = c.id
GROUP BY c.id, c.name;

通过设置相关参数，Hive 会自动将较小的表（在这个例子中是 customers 表）加载到内存中，并在 Map 阶段完成连接操作。这样可以减少 Reduce 阶段的计算量，提高查询效率。

5. 使用 LIMIT 语句

如果只需要查询部分结果，可以使用 LIMIT 语句来减少查询的数据量。

假设我们需要查询销售额最高的前 10 个客户：

  • 查询优化前：
SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_amount DESC;
  • 查询优化后：
SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_amount DESC
LIMIT 10;

通过添加 LIMIT 语句，我们可以减少返回的数据量，从而提高查询效率。

6. 使用 CTE（公共表表达式）

当一个查询涉及到多个相似的子查询时，可以使用 CTE（公共表表达式）将子查询提取出来，并在多个地方引用它，以提高可读性和查询效率。

假设我们需要查询每个月的总销售额，并计算出销售额的月环比增长率：

  • 查询优化前：
SELECT 
  current_month.year, 
  current_month.month, 
  current_month.total_amount, 
  (current_month.total_amount - previous_month.total_amount) / previous_month.total_amount as growth_rate
FROM
  (SELECT year, month, SUM(amount) as total_amount
   FROM sales
   GROUP BY year, month) current_month
JOIN
  (SELECT year, month, SUM(amount) as total_amount
   FROM sales
   GROUP BY year, month) previous_month
ON (current_month.year = previous_month.year AND current_month.month - 1 = previous_month.month)
   OR (current_month.year - 1 = previous_month.year AND current_month.month = 1 AND previous_month.month = 12);
  • 查询优化后：
WITH monthly_sales AS (
  SELECT year, month, SUM(amount) as total_amount
  FROM sales
  GROUP BY year, month
)
SELECT
  current_month.year,
  current_month.month,
  current_month.total_amount,
  (current_month.total_amount - previous_month.total_amount) / previous_month.total_amount as growth_rate
FROM monthly_sales current_month
JOIN monthly_sales previous_month
ON (current_month.year = previous_month.year AND current_month.month - 1 = previous_month.month)
   OR (current_month.year - 1 = previous_month.year AND current_month.month = 1 AND previous_month.month = 12);

通过使用 CTE，我们可以提高查询的可读性，并且避免重复计算相同的子查询。

7. 使用 ANALYZE TABLE 更新统计信息

Hive 可以使用表的统计信息来优化查询计划。通过执行 ANALYZE TABLE 命令，我们可以更新表的统计信息，从而帮助 Hive 生成更优的查询计划。

ANALYZE TABLE sales COMPUTE STATISTICS;
ANALYZE TABLE customers COMPUTE STATISTICS;

更新统计信息后，Hive 可能会为查询选择更好的执行计划，从而提高查询效率。

8. 使用 SORT BY 代替 ORDER BY

在某些场景下，对结果集的排序要求不严格时，可以使用 SORT BY 代替 ORDER BY。SORT BY 只在每个 Reduce 任务内部对结果进行排序，而 ORDER BY 对整个结果集进行全局排序。因此，SORT BY 的性能通常要优于 ORDER BY。

假设我们需要查询销售额前 100 的客户，但对结果排序要求不严格：

  • 查询优化前：
SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_amount DESC
LIMIT 100;
  • 查询优化后：
SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.id
GROUP BY c.id, c.name
SORT BY total_amount DESC
LIMIT 100;

通过使用 SORT BY，我们可以在保持结果集大致有序的情况下，提高查询效率。

9. 使用向量化查询

向量化查询（Vectorized Query）可以提高 CPU 利用率，从而提高查询性能。通过向量化查询，Hive 可以一次处理多行数据，而不是逐行处理。这可以减少 CPU 指令的数量，提高查询效率。

要启用向量化查询，可以设置以下参数：

SET hive.vectorized.execution.enabled = true;
SET hive.vectorized.execution.reduce.enabled = true;

10. 选择合适的文件格式

Hive 支持多种文件格式，如 TextFile、SequenceFile、Avro、Parquet 和 ORC 等。选择合适的文件格式可以提高查询性能。例如，ORC 和 Parquet 是两种支持列式存储的文件格式，它们具有较高的压缩率和优秀的读取性能。当查询涉及到大量的列式过滤和聚合操作时，选择这些文件格式可以大幅提高查询效率。

11. 压缩数据

压缩数据可以减少存储空间和 I/O 操作，从而提高查询性能。Hive 支持多种压缩算法，如 Gzip、Snappy、LZO 和 LZ4 等。可以根据实际需求选择合适的压缩算法，以平衡压缩率、压缩速度和解压速度之间的关系。

12. 使用窗口函数避免自连接

在一些场景下，可以使用窗口函数（Window Function）避免自连接操作，从而提高查询效率。

假设我们需要计算每个客户的销售额在客户内的排名：

  • 查询优化前（使用自连接）：
SELECT c1.id, c1.name, s1.amount, COUNT(*) as rank
FROM sales s1
JOIN customers c1 ON s1.customer_id = c1.id
JOIN sales s2 ON s1.customer_id = s2.customer_id
JOIN customers c2 ON s2.customer_id = c2.id
WHERE s1.amount <= s2.amount
GROUP BY c1.id, c1.name, s1.amount;
  • 查询优化后（使用窗口函数）：
SELECT c.id, c.name, s.amount,
       RANK() OVER (PARTITION BY c.id ORDER BY s.amount DESC) as rank
FROM sales s
JOIN customers c ON s.customer_id = c.id;

通过使用窗口函数，我们可以避免自连接操作，简化查询逻辑并提高查询效率。

13. 使用 EXPLAIN 计划

在进行查询优化时，可以使用 EXPLAIN 命令来查看查询的执行计划。这有助于识别潜在的性能问题，并找到可能的优化点。

例如：

EXPLAIN
SELECT c.id, c.name, SUM(s.amount) as total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_amount DESC
LIMIT 10;

分析 EXPLAIN 输出的结果，可以帮助我们找到可能的优化点，如连接顺序、谓词下推、分区裁剪等。

14. 使用并行执行

Hive 支持并行执行查询，从而加速查询过程。可以通过以下设置启用并行执行：

SET hive.exec.parallel=true;

同时，可以根据实际硬件资源和需求调整 Map 和 Reduce 任务的数量，以获得更好的性能：

SET mapreduce.job.maps=<number_of_maps>;
SET mapreduce.job.reduces=<number_of_reduces>;

15. 使用索引

虽然 Hive 的索引功能相对有限，但在某些情况下，使用索引仍然可以提高查询性能。可以使用以下命令为表创建索引：

CREATE INDEX index_name
ON TABLE table_name(column_name)
AS 'COMPACT'
WITH DEFERRED REBUILD;

然后使用 ALTER INDEX REBUILD 语句更新索引：

ALTER INDEX index_name ON table_name REBUILD;

创建索引后，Hive 会尝试在查询中自动使用索引，从而提高查询性能。

16. 使用合适的连接策略

Hive 支持多种连接策略，如 MapJoin、Common Join、Sort Merge Join 等。选择合适的连接策略可以提高查询性能。可以根据实际需求和数据量设置相关参数，以选择最佳的连接策略：

SET hive.auto.convert.join=true; -- 启用 MapJoin
SET hive.optimize.bucketmapjoin=true; -- 启用分桶 MapJoin
SET hive.optimize.bucketmapjoin.sortedmerge=true; -- 启用 Sort Merge Bucketed MapJoin

17. 使用 Hints 提示 Hive 使用特定的优化

Hive 支持使用 Hints 来为查询提供额外的优化建议。例如，可以使用 /*+ MAPJOIN() */ Hint 来强制使用 MapJoin：

SELECT /*+ MAPJOIN(customers) */
  c.id, c.name, SUM(s.amount) as total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_amount DESC
LIMIT 10;

18. 使用更少的子查询和临时表

子查询和临时表有时可能导致查询性能下降。在可能的情况下，尝试将子查询和临时表合并到单个查询中，以减少不必要的数据操作。

例如，假设我们需要找到销售额最高的产品，并返回产品名称和销售额。原始查询可能如下：

  • 查询优化前：
CREATE TEMPORARY TABLE temp_sales_summary AS
SELECT p.id, p.name, SUM(s.amount) as total_amount
FROM sales s
JOIN products p ON s.product_id = p.id
GROUP BY p.id, p.name;

SELECT name, total_amount
FROM temp_sales_summary
WHERE total_amount = (SELECT MAX(total_amount) FROM temp_sales_summary);
  • 查询优化后：
SELECT p.name, SUM(s.amount) as total_amount
FROM sales s
JOIN products p ON s.product_id = p.id
GROUP BY p.name
HAVING total_amount = (
  SELECT MAX(sub.total_amount)
  FROM (
    SELECT SUM(s.amount) as total_amount
    FROM sales s
    GROUP BY s.product_id
  ) sub
);

通过将子查询和临时表合并到单个查询中，我们可以减少数据操作，从而提高查询性能。

结语

请注意，在实际应用中，优化技术的效果可能会因数据、硬件和查询需求的不同而有所差异。因此，在进行优化时，要尽可能多地尝试和测试不同的优化方法，找到适合当前场景的最佳解决方案。

【大数据Hive】hive 优化策略之job任务优化
congge_study的博客
11-22 9445
hive job优化策略
Hive优化方法汇总
南洲.的博客
09-01 4760
1、Fetch抓取 Fetch 抓取是指，Hive 中对某些情况的查询可以不必使用 MapReduce 计算。例如：SELECT * FROM employees;在这种情况下，Hive 可以简单地读取 employee 对应的存储目录下的文件，然后输出查询结果到控制台。 在 hive-default.xml.template 文件中 hive.fetch.task.conversion 默认是 more，老版本 hive 默认是 minimal，该属性修改为 more 以后，在全局查找、字段查找、limi
hive查询优化总结
竹叶青 的专栏
07-02 1万+
一、join优化 Join查找操作的基本原则：应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。 Join查找操作中如果存在多个join，且所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred
HIVE查询优化
初心江湖路的博客
02-20 1044
所有的调优都离不开对CPU、内存、IO这三样资源的权衡及调整 Hive QL的执行本质上是MR任务的运行，因此优化主要考虑到两个方面：Mapreduce任务优化、SQL语句优化 一、Mapreduce任务优化 1、设置合理的task数量 这里有几个考虑的点，一方面Hadoop MR task的启动及初始化时间较长，如果task过多，可能会导致任务启动和初始化时间远超逻辑处理时间，这种情况白白浪费了...
Hive_5. Hive QL -- Hive 查询优化
Mike han
12-03 1619
HiveQL 查询 _1 (翻译于 《Programing Hive》)：http://flyingdutchman.iteye.com/blog/1869472HiveQL 查询 _2 (翻译于 《Programing Hive》)：http://flyingdutchman.iteye.com/blog/1869621HiveQL 查询 _3 (翻译于 《Programing Hive》)：h
Hive 查询优化
热门推荐
桃李不言下自成蹊
11-15 3万+
set hive.exec.mode.local.auto=true; set mapperd.job.tracker=local; set mapreduce.framework.name=local; set hive.vectorized.execution.enabled=true;
Hive查询优化
RangeYan
02-09 894
Hive查询优化分成三个部分 1. 合理建表 2.通用参数优化 \ 3.Join优化
Hive优化操作（一）
最新发布
m0_58076578的博客
10-06 5241
在使用 Hive 进行数据分析时，提高查询性能至关重要。以下是一些具体的优化策略，帮助我们在工作中更有效地管理和查询数据。
Hive优化及优化实例
u012893747的博客
11-02 2935
Hive优化 Hive优化实例
Hive优化器
yezi12171217的博客
03-12 2004
1. 逻辑优化器LineageGenerato：各operator血缘情况的设定Columnpruner ：列前裁优化器Predicate PushDown：谓词下推优化器，将条件推到特定的位置Partition Pruner：分区减裁优化器PartitionCondition Pruner：消除无用分支分区条件的优化器Groupby optimizer：group优化map端预聚合的优化器sam...
HIVE数据库查询优化
读数据的教育家的博客
01-09 579
1.join的优化 join语句在使用时，先进行join操作，再进行where语句，因此加更多where语句限制并不能加快hive数据库的查询速度。 MAPJOIN注意事项：新版本中应该先使用set hive.auto.convert.join = true; 再添加/*+ MAPJOIN(b) */ 由于join操作是在where操作之前执行，所以当你在执行join时，wh
Hive面试，如何优化查询效率！
数据森麟
11-18 388
公众号后台回复“图书“，了解更多号主新书内容作者：数据社来源：数据社1，开启FetchTask 一个简单的查询语句，是指一个没有函数、排序等功能的语句，当开启一个Fetch Task...
hive简单查询优化总结
huobumingbai1234的博客
10-20 2903
hive作为hadoop系列的计算模型，在公司的数据清洗和报表开发广泛使用，合理的优化自己的语句结构可以节省计算时间，优化集群的计算资源，下面总结下日常工作中自己碰到的一些问题和别人总结的一些方法。 我们先了解下关系型数据库sql的执行顺序，hive大致相同 然后大致了解下hive的架构： 具体策略： 1、尽早的过滤数据，hive同样会先执行join，再执行w...
Hive优化(21种方案)
小道的博客
03-20 5927
1、Fetch抓取   Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。   在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本...
join和子查询效率_如何进行hive查询优化？
weixin_39866974的博客
12-03 1686
hive查询优化的主要目的是提升效率。用过hive的朋友，我想或多或少都有类似的经历：一天下来，没跑几次hive，就到下班时间了。下面总结了查询中经常使用的优化点：1. 少用count(distinct ) 建议用group by 代替 distinct 。原因为count(distinct)逻辑只会有一个reducer来处理,即使设定了reduce task个数，set mapred...
Hive优化的十大方法
thy822的专栏
08-06 1万+
hive 优化
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

AmosCloud2013

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值