有没有进一步优化hiveql查询的方法？

jobtbby3 于 2021-06-01 发布在 Hadoop

关注(0)|答案(4)|浏览(507)

我写了一个查询来查找3月到4月美国最繁忙的10个机场。它产生所需的输出，但我想尝试进一步优化它。
是否有任何特定于hiveql的优化可以应用于查询？是 GROUPING SETS 适用于这里？我是新来的Hive和目前这是最短的查询，我已经想出。

SELECT airports.airport, COUNT(Flights.FlightsNum) AS Total_Flights
FROM (
SELECT Origin AS Airport, FlightsNum 
  FROM flights_stats
  WHERE (Cancelled = 0 AND Month IN (3,4))
UNION ALL
SELECT Dest AS Airport, FlightsNum 
  FROM flights_stats
  WHERE (Cancelled = 0 AND Month IN (3,4))
) Flights
INNER JOIN airports ON (Flights.Airport = airports.iata AND airports.country = 'USA')
GROUP BY airports.airport
ORDER BY Total_Flights DESC
LIMIT 10;

表列如下：
机场

|iata|airport|city|state|country|

航班\u统计

|originAirport|destAirport|FlightsNum|Cancelled|Month|

sql hadoop Hive query-optimization hiveql

来源：https://stackoverflow.com/questions/49153561/hive-is-there-a-way-to-further-optimize-a-hiveql-query

4条答案

按热度按时间

vaj7vani1#

如果您在 union all :

SELECT a.airport, SUM(cnt) AS Total_Flights
FROM ((SELECT Origin AS Airport, COUNT(*) as cnt 
       FROM flights_stats
       WHERE (Cancelled = 0 AND Month IN (3,4))
       GROUP BY Origin
      ) UNION ALL
      (SELECT Dest AS Airport, COUNT(*) as cnt
       FROM flights_stats
       WHERE Cancelled = 0 AND Month IN (3,4)
       GROUP BY Dest
      )
     ) f INNER JOIN
     airports a
     ON f.Airport = a.iata AND a.country = 'USA'
GROUP BY a.airport
ORDER BY Total_Flights DESC
LIMIT 10;

赞(0）回复(0）举报 2021-06-01

oknwwptz2#

您可以对此进行测试，但您所处的情况是工会可能更好，因此您确实需要测试它并返回：

SELECT airports.airport,
SUM(
  CASE 
     WHEN T1.FlightsNum IS NOT NULL THEN 1
     WHEN T2.FlightsNum IS NOT NULL THEN 1
     ELSE 0
  END 
  ) AS Total_Flights
FROM airports
LEFT JOIN (SELECT  Origin AS Airport, FlightsNum 
    FROM flights_stats
   WHERE (Cancelled = 0 AND Month IN (3,4))) t1 
 on t1.Airport = airports.iata
LEFT JOIN (SELECT Dest AS Airport, FlightsNum 
   FROM flights_stats
   WHERE (Cancelled = 0 AND Month IN (3,4))) t2
 on t1.Airport = airports.iata
GROUP BY airports.airport
ORDER BY Total_Flights DESC

赞(0）回复(0）举报 2021-06-01

nkcskrwz3#

按机场筛选（内部联接）并在union all之前进行聚合，以减少传递给最终聚合缩减器的数据集。联合所有具有联接的子查询应该并行运行，并且比联合所有子查询之后使用更大的数据集进行联接要快。

SELECT f.airport, SUM(cnt) AS Total_Flights
FROM (
      SELECT a.airport, COUNT(*) as cnt 
       FROM flights_stats f
            INNER JOIN airports a ON f.Origin=a.iata AND a.country='USA'
       WHERE Cancelled = 0 AND Month IN (3,4)
       GROUP BY a.airport
       UNION ALL
      SELECT a.airport, COUNT(*) as cnt
       FROM flights_stats f
            INNER JOIN airports a ON f.Dest=a.iata AND a.country='USA'
       WHERE Cancelled = 0 AND Month IN (3,4)
       GROUP BY a.airport
     ) f 
GROUP BY f.airport
ORDER BY Total_Flights DESC
LIMIT 10
;

优化Map联接并启用并行执行：

set hive.exec.parallel=true;
set hive.auto.convert.join=true; --this enables map-join
set hive.mapjoin.smalltable.filesize=25000000; --size of table to fit in memory

使用tez和矢量化，调整Map器和还原器的并行性：https://stackoverflow.com/a/48487306/2700344

赞(0）回复(0）举报 2021-06-01

kpbpu0084#

我认为分组集在这里不适用，因为您只按一个字段进行分组。
来自apachewiki：“groupby中的grouping sets子句允许我们在同一个记录集中指定多个groupby选项。”

赞(0）回复(0）举报 2021-06-01