Hive 中 sort by 和 order by 的区别

文章目录

• 1 数据量
• 2 作用范围
• 3 调优思路
• • • 示例
    • 3.2 如何用 group by 方式同时统计多个列

      在 Hive 中， SORT BY 和 ORDER BY 都用于对查询结果进行排序，但它们在实现方式和适用场景上有一些区别。

      	作用范围	有序性	数据量
      SORT BY	分区内排序	分区有序	适合处理大数据量
      ORDER BY	全局排序整个查询结果	全局有序	适合处理较小数据量

      1 数据量

      1. SORT BY：

         SORT BY 用于在 Hive 中对查询结果进行排序，它的主要特点是在进行排序操作时会生成多个临时文件，每个文件都会存储部分排序后的数据。这可以减少内存的使用，适合处理大数据量的排序。

         示例：

         SELECT * FROM employees
         SORT BY salary DESC;
         

      2. ORDER BY：

         ORDER BY 也用于对查询结果进行排序，但它会将所有结果加载到内存中进行排序，然后输出。这意味着对于大数据量的排序可能会导致内存溢出的问题，因此适合处理较小数据量的排序。

         示例：

         SELECT * FROM employees
         ORDER BY hire_date;
         

      总结：

      • 如果需要对大数据量进行排序，应该使用 SORT BY，因为它在排序过程中生成多个临时文件，减少内存压力。
      • 如果数据量较小，可以使用 ORDER BY，但要注意不要对过大的数据集进行排序，以免导致内存问题。

        无论是使用 SORT BY 还是 ORDER BY，都可以实现对查询结果的排序，选择哪种方式取决于数据量和内存等因素。

        2 作用范围

        1. SORT BY：

           • SORT BY用于将查询结果的每个Reducer的输出分区内进行排序。每个Reducer的输出都会单独进行排序，而不会全局排序整个结果集。
           • 这意味着SORT BY在每个Reducer的输出分区内都进行了排序，但不保证全局有序。
           • SORT BY在分布式计算中更高效，因为不需要全局数据重排。

           • ORDER BY：

             • ORDER BY用于全局排序整个查询结果。它将整个结果集都收集到单个节点，然后对整个数据集进行排序。
             • 这意味着ORDER BY保证了全局有序，但在大数据量情况下可能会导致性能问题，因为需要将所有数据传输到一个节点上进行排序。

        这篇文章通过实验介绍了排序作用范围：http://t.csdn.cn/MKNfJ

        总结来说：

        • SORT BY局部排序，适用于需要对每个Reducer输出进行排序的情况，分布式效率高。
        • ORDER BY全局排序，适用于需要整个结果集有序的情况，但可能在性能方面有一些挑战。

          在实际使用中，根据查询需求和数据量大小，选择适合的排序方式。

          3 调优思路

          3.1 sort by 代替 order by

          HiveQL中的 order by 与其他SQL方言中的功能一样，就是将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完。

          如果使用 sort by，那么还是会视情况启动多个 reducer 进行排序，并且保证每个 reducer 内局部有序。为了控制 map 端数据分配到 reducer 的 key，往往还要配合distribute by一同使用。如果不加distribute by的话，map端数据就会随机分配到 reducer。

          DISTRIBUTE BY 并不保证数据的全局排序，只是确保了在不同的 Reducer 任务之间数据的均衡分布。如果需要全局排序，应该结合使用 DISTRIBUTE BY 和 SORT BY。

          示例

          假设我们有一个 user 表，包含用户信息，和一个 transaction 表，包含用户的交易记录。我们想要从这两个表中获取每个用户的总交易金额，并按照用户ID排序，同时确保数据在Reducer中均衡分布。

          下面是一个示例：

          -- 创建用户表
          CREATE TABLE user (
              user_id BIGINT PRIMARY KEY,
              name STRING
          );
          -- 创建交易记录表
          CREATE TABLE transaction (
              user_id BIGINT,
              amount DECIMAL(10, 2)
          );
          -- 插入示例数据
          INSERT INTO user VALUES (1, 'Alice');
          INSERT INTO user VALUES (2, 'Bob');
          INSERT INTO user VALUES (3, 'Charlie');
          INSERT INTO transaction VALUES (1, 100.50);
          INSERT INTO transaction VALUES (2, 150.75);
          INSERT INTO transaction VALUES (3, 75.20);
          INSERT INTO transaction VALUES (1, 200.00);
          INSERT INTO transaction VALUES (2, 50.30);
          INSERT INTO transaction VALUES (3, 125.60);
          -- 查询每个用户的总交易金额，并按照用户ID排序
          INSERT OVERWRITE TABLE user_transaction_summary
          SELECT u.user_id, u.name, SUM(t.amount) AS total_amount
          FROM user u
          JOIN transaction t ON u.user_id = t.user_id
          GROUP BY u.user_id, u.name
          SORT BY u.user_id;
          

          在上述示例中，我们使用了 JOIN 将 user 表和 transaction 表关联起来，然后使用 GROUP BY 汇总每个用户的总交易金额。为了确保数据在Reducer中均衡分布，我们使用了 SORT BY 和 DISTRIBUTE BY。在 INSERT OVERWRITE 语句中，我们使用 DISTRIBUTE BY u.user_id SORT BY u.user_id，这将确保按照用户ID进行数据分发和排序。

          这样，我们就能够得到每个用户的总交易金额，并且数据会按照用户ID排序，同时在不同的Reducer任务中进行均衡分布。

          3.2 如何用 group by 方式同时统计多个列

          select t.a, sum(t.b), count(t.c), count(t.d) from some_table t group by t.a;
          

          这是解决方法：

          select t.a, sum(t.b), count(t.c), count(t.d) from ( 
              select a,b,null c,null d from some_table 
              union all 
              select a,0 b,c,null d from some_table group by a,c 
              union all 
              select a,0 b,null c,d from some_table group by a,d 
          ) t;