Когда имеет смысл использовать Google BigQuery?

В течение моей карьеры мне приходилось писать сложные аналитические запросы для различных видов отчетов и диаграмм. Чаще всего это была некая диаграмма, на которой отображались данные, агрегированные по дате, неделе, кварталу и так далее. Обычно такие отчеты создаются, чтобы помочь клиентам определить тенденции и показать, как их бизнес работает на высоком уровне. Но что происходит, когда специалистам по обработке и анализу данных и инженерам необходимо создать гораздо более обширный отчет на основе большого набора данных?

В случае, если отчет основан на небольшом наборе данных, задача может быть решена путем написания SQL-запроса к реляционной базе данных. На этом этапе важно знать основы написания запросов и способы сделать их более быстрыми и эффективными. Однако иногда отчет зависит от большего набора данных (например, миллионы и более строк в таблице), но отчет не зависит от входных переменных (параметров), или вы можете обнаружить, что количество значений довольно мало. В таких сценариях SQL-запрос может выполняться медленно, поэтому для пользователей нецелесообразно ждать выполнения запроса. Наиболее распространенной практикой в ​​таких случаях является запуск запроса заранее, до того, как клиент запросит отчет.

Кроме того, требуется реализация некоторых функций кэширования, чтобы клиент мог брать данные из кэша, а не выполнять запрос в режиме реального времени. Этот подход отлично работает, если вам не нужно показывать данные в реальном времени. Он может показывать данные, рассчитанные часом или даже днем ​​ранее. Таким образом, фактический отчет/диаграмма отображается с использованием кэшированных данных, а не на основе данных в реальном времени.

Переходим к Google BigQuery

Пока я работал над аналитическим проектом в фарминдустрии, мне понадобились диаграммы, в которых в качестве входных параметров принимались почтовый индекс и название лекарства. Мне также нужно было показать некоторые сравнения между наркотиками в определенных регионах Соединенных Штатов.

Аналитический запрос был очень сложным и в итоге выполнялся около 50 минут на нашем сервере Postgres (четырехъядерный процессор с 16 ГБ ОЗУ). У меня не было возможности запустить его заранее и кэшировать результаты, так как запрос принимал почтовые индексы и наркотики в качестве входных параметров, поэтому были тысячи комбинаций, и невозможно было предсказать, какой из них выберет один клиент.

Даже если бы я попытался выполнить все комбинации входных параметров, моя база данных, скорее всего, рухнула бы. Так что пришло время выбрать другой подход и простое в использовании решение. Эта диаграмма была важна для клиента, однако клиент не был готов вносить большие изменения в архитектуру или полностью переходить на другую БД.

В этом конкретном проекте мы попробовали несколько разных подходов:

• Вертикальное масштабирование сервера (добавление оперативной памяти и процессора к серверу Postgres)
• Использование альтернативных БД, таких как Amazon Redshift и других.
• Мы также исследовали решение NoSQL, но большинство из них довольно сложны и требуют множества изменений в архитектуре, многие из которых были бы слишком велики для клиента.

Наконец, мы попробовали Google BigQuery. Это оправдало наши ожидания и позволило нам выполнить работу, не внося огромных изменений, которые клиент не хотел бы одобрять. Но что такое Google BigQuery и как он работает?

BigQuery — это веб-служба на основе REST, которая позволяет выполнять сложные аналитические запросы на основе SQL для больших наборов данных. После того, как мы загрузили данные в BigQuery и выполнили тот же запрос, что и в Postgres (синтаксис очень похож), наш запрос стал выполняться намного быстрее и занял около минуты. В конечном итоге мы получили 50-кратный прирост производительности просто за счет использования другого сервиса. Стоит отметить, что другие БД не давали такого же прироста производительности, и давайте будем великодушны и просто скажем, что они даже близко не стояли. Честно говоря, я был действительно впечатлен приростом производительности, обеспечиваемым BigQuery, поскольку цифры оказались лучше, чем кто-либо из нас надеялся.

Несмотря на это, я бы не стал рекламировать BigQuery как лучшее решение для баз данных в мире. Хотя для нашего проекта он работал хорошо, у него все еще есть много ограничений, таких как ограниченное количество обновлений в таблице в день, ограничения на размер данных на запрос и другие. Нужно понимать, что BigQuery нельзя использовать для замены реляционной базы данных, и он ориентирован на выполнение аналитических запросов, а не на простые CRUD-операции и запросы.

В этой статье я попытаюсь сравнить использование Postgres (моя любимая реляционная база данных) и BigQuery для реальных сценариев использования. Кроме того, я бы дал несколько советов по пути, а именно свое мнение о том, когда действительно имеет смысл использовать BigQuery.

Образец данных

Для сравнения Postgres и Google BigQuery я взял общедоступную демографическую информацию по каждой стране, сгруппированную по стране, возрасту, году и полу (эти же данные можно скачать по этой ссылке).

Я добавил данные в четыре таблицы:

1. populations
2. locations
3. age_groups
4. populations_aggregated

Последняя таблица представляет собой просто агрегированные данные из трех предыдущих таблиц. Вот схема БД:

Таблица populations , с которой я столкнулся, содержит более 6,9 миллионов строк. Это не слишком много, но для моего теста было достаточно.

На основе выборочных данных я попытался построить запросы, которые можно использовать для построения реальных аналитических отчетов и диаграмм. Итак, я подготовил запросы для следующих отчетов:

1. Население США по годам.
2. Население в 2019 году для всех стран, начиная с самых больших стран.
3. Топ-5 «самых старых» стран за каждый год. «Самые старые» обозначают страны, в которых процент людей старше 60 лет от общего числа людей является самым высоким. Запрос должен давать пять результатов за каждый год.
4. Пять лучших стран по годам, где разница между мужским и женским населением самая большая.
5. Получите медианный (средний) возраст по стране за каждый год, начиная с «самых старых» и заканчивая «самыми молодыми» странами.
6. Найдите пять самых «умирающих» стран за каждый год. «Вымирающие» — это страны, в которых население сокращается (депопуляция самая высокая).

Запросы № 1, № 2 и № 6 довольно просты и понятны, но запросы № 3, № 4 и № 5 написать было не так просто — по крайней мере, для меня. Обратите внимание, что я являюсь бэкенд-инженером, и написание сложных SQL-запросов не совсем моя специальность, поэтому кто-то с большим опытом работы с SQL, вероятно, мог бы создавать более умные запросы. Однако на данный момент нам нужно проверить, как Postgres и BigQuery обрабатывают одни и те же запросы с одними и теми же данными.

Всего я построил 24 запроса:

• 6 для БД Postgres, которые используют неагрегированные таблицы ( populations , locations , age_groups )
• 6 для БД Postgres, которые используют populations_aggregated
• 6+6 запросов для BigQuery, использующих агрегированные и неагрегированные таблицы.

Позвольте мне поделиться запросами BigQuery № 1 и № 5 для агрегированных данных, чтобы вы могли понять сложность простых (№ 1) и сложных запросов № 5.

Население США, агрегированное по годам, запрос:

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

Запрос среднего возраста по стране за каждый год, отсортированный от самого старшего к самому молодому:

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Примечание. Вы можете найти все запросы в моем репозитории bitbucket (ссылка в конце статьи).

Результаты теста

Для выполнения запросов я использовал два разных сервера Postgres. Первый имеет 1 ядро ​​​​процессора и 4 ГБ оперативной памяти, поддерживаемый SSD-накопителем. Второй имеет 16 ядер ЦП, 64 ГБ ОЗУ и также использует SSD-накопитель (второй сервер имеет 16-кратный потенциал ЦП и ОЗУ).

Также обратите внимание, что во время выполнения теста не было нагрузки на базы данных. Я создал их только для выполнения запросов. В реальных ситуациях запросы будут занимать больше времени, так как другие запросы могут выполняться одновременно, и, кроме того, эти запросы, выполняемые параллельно, могут блокировать таблицы. Для проверки скорости запросов я использовал pgAdmin3 и веб-интерфейс BigQuery.

В моем тесте я получил следующие результаты:

Позвольте мне показать эти результаты в виде гистограммы для запросов № 1 и № 5.

Примечание. База данных Postgres находилась на сервере в США, а я нахожусь в Европе, поэтому возникла дополнительная задержка при передаче данных Postgres.

Производительность BigQuery и выводы

На основании полученных результатов я сделал следующие выводы:

• В случае вертикального масштабирования Postgres, даже в 16 раз, это дает только 10-25% производительности при выполнении одного запроса. Другими словами, сервер Postgres с одним ядром ЦП и 4 ГБ ОЗУ выполнял запросы со временем, очень близким к времени, которое требовалось для сервера с 16 ядрами ЦП и 64 ГБ ОЗУ. Конечно, более крупные серверы могут обрабатывать гораздо большие наборы данных, однако это не дает значительного улучшения времени выполнения запросов.
• Для Postgres соединения с небольшими таблицами (в таблице locations около 400 строк, а age_groups — 100 строк) не дают большой разницы по сравнению с выполнением запросов по агрегированным данным, расположенным в одной таблице. Кроме того, я обнаружил, что для запросов, которые выполняются от одной до двух секунд, запросы с внутренними соединениями выполняются быстрее, но для длительных запросов ситуация иная.
• В BigQuery ситуация с объединениями совершенно иная. BigQuery не любит соединения. Разница во времени между запросами, использующими агрегированные и неагрегированные данные, достаточно велика (для запросов №3 и $5 она составляла около двух секунд). Это означает, что для BigQuery вы можете делать столько подзапросов, сколько хотите, но для хорошей производительности запрос должен использовать одну таблицу.
• Postgres быстрее для запросов, которые используют простую агрегацию или фильтрацию или используют небольшой набор данных. Я обнаружил, что запросы, которые в Postgres занимают менее пяти секунд, в BigQuery выполняются медленнее.
• BigQuery работает намного лучше для длительных запросов. По мере увеличения разницы в размере набора данных будет увеличиваться и разница во времени выполнения этих запросов.

Когда имеет смысл использовать BigQuery

Теперь давайте вернемся к основному вопросу, обсуждаемому в этой статье: когда вам действительно следует использовать Google BigQuery? Основываясь на своих выводах, я бы предложил использовать BigQuery при соблюдении следующих условий:

• Используйте его, когда у вас есть запросы, которые выполняются более пяти секунд в реляционной базе данных. Идея BigQuery заключается в выполнении сложных аналитических запросов, а это означает, что нет смысла выполнять запросы, выполняющие простую агрегацию или фильтрацию. BigQuery подходит для «тяжелых» запросов, которые работают с большим набором данных. Чем больше набор данных, тем выше вероятность повышения производительности с помощью BigQuery. Набор данных, который я использовал, был всего 330 МБ (мегабайт, даже не гигабайт).
• BigQuery не любит объединения, поэтому вам следует объединить данные в одну таблицу, чтобы сократить время выполнения. BigQuery позволяет сохранять результаты запроса в новой таблице, поэтому, чтобы создать новую агрегированную таблицу, просто загрузите все свои данные в BigQuery, запустите запрос, который объединит все данные, и просто сохраните их в новой таблице.
• BigQuery хорош для сценариев, в которых данные меняются нечасто, и вы хотите использовать кеш, так как он имеет встроенный кеш. Что это значит? Если вы выполняете тот же запрос, а данные в таблицах не изменяются (обновляются), BigQuery просто использует кешированные результаты и не будет пытаться выполнить запрос снова. Кроме того, BigQuery не берет деньги за кешированные запросы. Примечание. Даже кешированные запросы возвращают результаты за 1–1,2 секунды.
• Вы также можете использовать BigQuery, если хотите снизить нагрузку на реляционную базу данных. Аналитические запросы являются «тяжелыми», и их чрезмерное использование в реляционной базе данных может привести к проблемам с производительностью. Таким образом, в конечном итоге вы можете быть вынуждены подумать о масштабировании своего сервера. Однако с помощью BigQuery вы можете переместить эти запущенные запросы в стороннюю службу, чтобы они не повлияли на вашу основную реляционную базу данных.

Наконец, еще несколько слов об использовании BigQuery в реальной жизни. В нашем реальном проекте данные для отчетов менялись еженедельно или ежемесячно, поэтому мы могли загружать данные в BigQuery вручную. Однако, если ваши данные часто меняются, синхронизация данных между вашей реляционной базой данных и BigQuery может быть не такой простой, и об этом стоит помнить.

Ссылки

Примеры данных, использованные в этой статье, можно найти здесь, а запросы и данные в формате CSV доступны здесь.