google-bigquery - BigQuery：优化查询以获得每月访问量最大的 5 个维基百科页面

Question

我正在尝试优化查询，以从公共数据集中找到 2019 年每个月访问量最大的 5 个维基百科页面fh-bigquery.wikipedia_v3.pageviews_2019。我提出了以下查询，但我需要两件事：

1. 该查询运行大约 2 分钟并处理 2.3 TB，无论我是过滤前 5 个排名还是在外部查询中全部显示它们。是否有更好的优化查询来处理更少的数量和更短的时间 - 我们可以通过它来限制首先获取的记录数量（在子查询本身中使用前 5 或限制 5）？

2. 根据日历顺序而不是字母顺序对月份进行排序。

询问：

select * 
from (
  select Month_2019, title, tot_views,
    rank() over (partition by Month_2019 order by tot_views desc) as view_rank
  from (
    select format_date("%B", Date(datehour)) as Month_2019,
      title, sum(views) as tot_views
    from `fh-bigquery.wikipedia_v3.pageviews_2019`
    where wiki='en'
    and title not in ('Main_Page','-','Special:Search','Special:CreateAccount','Special:Watchlist','Special:ElectronPdf','Special:Book','Special:CiteThisPage','Special:RecentChanges','Portal:Current_events','Wikipedia')
    and datehour between '2019-01-01' and '2019-12-31'
    group by Month_2019, title
  ))
where view_rank<6
order by 1,4

预期输出：

一月 Louis_Tomlinson 5075908 1

一月 Deaths_in_2019 1832404 2

一月 TCP_delayed_acknowledgment 1238559 3

一月 Ted_Bundy 1190672 4

一月玻璃_(2019_film) 1018119 5

二月 Louis_Tomlinson 5504517

二月格罗弗 4970493

二月流变 2852186

二月 Deaths_in_2019

二月操作系统

行进.... ....

标签： google-bigquery

Answer 1

对问题

该查询运行大约 2 分钟并处理 2.3 TB，无论我是过滤前 5 个排名还是在外部查询中全部显示它们。是否有更好的优化查询来处理更少的量和更短的时间

查询已经优化！请注意，它没有处理 2.3TB，也没有花费 2 分钟。运行时间和字节查询是1 min 20 sec elapsed, 440.1 GB processed. 这是因为表是聚集的。

ARRAY_AGG我可以使用和更好的排序来提高查询的运行时间：

select Month_2019, ARRAY_AGG(STRUCT(title, tot_views) ORDER BY tot_views DESC LIMIT 5)
from (
  select format_date("%B", Date(datehour)) as Month_2019,
    title, sum(views) as tot_views, MIN(datehour) month_for_sorting
  from `fh-bigquery.wikipedia_v3.pageviews_2019`
  where wiki='en'
  and title not in ('Main_Page','-','Special:Search','Special:CreateAccount','Special:Watchlist','Special:ElectronPdf','Special:Book','Special:CiteThisPage','Special:RecentChanges','Portal:Current_events','Wikipedia')
  and datehour between '2019-01-01' and '2019-12-31'
  group by Month_2019, title
)
GROUP BY 1
order by MIN(month_for_sorting)

如果我们删除所有行WHERE views<3，那么查询只需 12 秒即可运行：

select Month_2019, ARRAY_AGG(STRUCT(title, tot_views) ORDER BY tot_views DESC LIMIT 5)
from (
  select format_date("%B", Date(datehour)) as Month_2019,
    title, sum(views) as tot_views, MIN(datehour) month_for_sorting
  from `fh-bigquery.wikipedia_v3.pageviews_2019`
  where wiki='en'
  AND views > 3
  and title not in ('Main_Page','-','Special:Search','Special:CreateAccount','Special:Watchlist','Special:ElectronPdf','Special:Book','Special:CiteThisPage','Special:RecentChanges','Portal:Current_events','Wikipedia')
  and datehour between '2019-01-01' and '2019-12-31'
  group by Month_2019, title
)
GROUP BY 1
order by MIN(month_for_sorting)

请注意，我们每次只处理 440GB。

如果您打算继续使用这样的查询 - 将所有有趣的行（例如 filter out views < 3）提取到一个新表中，每次查询的 GB 数更少。

例如：

CREATE  TABLE `fh-bigquery.wikipedia_extracts.2019_en_m_daily`
PARTITION BY date
CLUSTER BY title
AS
SELECT DATE(TIMESTAMP_TRUNC(datehour, DAY)) date, SUM(views) views, title
FROM `fh-bigquery.wikipedia_v3.pageviews_2019`
WHERE wiki='en.m'
AND title not in ('Main_Page','-','Portal:Current_events','Wikipedia')
AND NOT title LIKE 'Special:%'
GROUP BY month, title
HAVING views > 100


# 33.1 sec elapsed, 420.7 GB processed

注意我切换wiki=en到wiki=en.m移动搜索结果。现在查询只处理 2.5GB：

SELECT month
  , ARRAY_AGG(STRUCT(SUBSTR(title,0,21), views) ORDER BY views DESC LIMIT 5)
from (
  SELECT DATE_TRUNC(date, MONTH) month, title, SUM(views) as views
  FROM `fh-bigquery.wikipedia_extracts.2019_en_m_daily`
  WHERE title NOT LIKE 'File%'
  GROUP BY month, title
)
GROUP BY 1
ORDER BY month