Антишаблоны SQL для BigQuery

Рекомендации и рекомендации, которых следует избегать при запуске SQL в Google Cloud BigQuery

BigQuery — это управляемая служба Хранилище данных на Google Cloud Platform. Как и большинство служб и технологий, она основана на наборе принципов, необходимых для иметь в виду при его использовании.

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

Избегайте SELECT *

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

Запрашивайте только те столбцы, которые вам нужны

Также помните, что LIMIT не уменьшит объем прочитанных байтов, и поэтому вы все равно будете платить за полное сканирование каждого отдельного столбца. Поэтому убедитесь, что вы запрашиваете только те столбцы, которые вам действительно нужны. Если вам все еще нужно запустить SELECT *, подумайте о том, чтобы разбить таблицу на разделы, чтобы вы могли запрашивать данные, которые находятся в одном или нескольких разделах.

Избегайте самостоятельных соединений

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

Что ж, ответ — нет — это почти то же самое, но суть здесь в том, что всякий раз, когда вы собираетесь выполнить самосоединение, есть вероятность, что вы можете достичь того же результата с помощью оконной функции, которая является более элегантным способом.

Избегайте самообъединений и вместо этого используйте оконные функции.

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

Работа с асимметрией данных

Неравномерность данных — это явление, возникающее, когда ваши данные разбиты на разделы неодинакового размера. За кулисами BigQuery отправит эти разделы в слоты, которые представляют собой виртуальные ЦП, используемые для выполнения запросов SQL в распределенном режиме.

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

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

Если ваши данные искажены, примените фильтрацию как можно раньше.

Кроме того, вам, возможно, также придется пересмотреть ключ разделения. Например, вы можете не разбивать таблицу на разделы с помощью ключа со многими значениями NULL, так как это создаст огромный раздел для таких строк. Обычно используемый ключ разделения — это поле даты, которое обеспечивает несколько равномерное распределение данных по разным разделам (при условии, что у вас есть примерно одинаковый объем данных за день/месяц/год).

перекрестные соединения

Перекрестные соединения используются для создания декартова произведения между двумя таблицами, то есть результата, состоящего из всех возможных комбинаций между записями задействованных таблиц. Проще говоря, каждая строка из первой таблицы будет соединена с каждой отдельной строкой во второй таблице, что означает, что в худшем случае мы получим результат, состоящий из MxN строк, где M и N — размеры таблицы. соответственно.

Избегайте выполнения объединений, которые приведут к большему количеству выходных данных, чем входных.

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

1. Оцените, может ли оконная функция, которая намного эффективнее перекрестного соединения, помочь вам получить желаемый результат.
2. Выполните предварительную агрегацию с помощью GROUP BY перед соединением

Предпочитайте секционирование таблицы, а не сегментирование

Разделение таблиц — это подход, используемый для хранения данных в нескольких разных таблицах с использованием префикса имени, такого как [PREFIX]_YYYYMMDD. Многие пользователи сочли бы описанную выше технику такой же, как разбиение на разделы, но на самом деле это не так.

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

Разделение таблиц более эффективно, чем сегментирование таблиц.

В общем, секционирование таблиц работает лучше, поэтому вам следует отдавать предпочтение им, а не сегментированным таблицам. Кроме того, с секционированными таблицами проще работать, когда речь идет о фильтрации и снижении затрат.

Не относитесь к BigQuery как к системе OLTP

Как и большинство решений для хранилищ данных, BigQuery также является системой OLAP (онлайн-аналитической обработки). Это означает, что он предназначен для эффективной работы с чрезвычайно большими объемами данных с использованием сканирования таблиц. Поэтому предполагается, что операторы DML в BigQuery не должны использоваться для выполнения массовых обновлений.

BigQuery — это OLAP-система, и с ней нужно обращаться соответствующим образом.

Использование операторов DML для выполнения модульных изменений означает, что вы пытаетесь рассматривать BigQuery как систему OLTP (онлайн-обработка транзакций). Если это так, вам следует пересмотреть свой дизайн или даже инструменты, которые вы используете. Есть вероятность, что система OLTP (например, CloudSQL на Google Cloud Platform) подойдет больше. В качестве альтернативы, если ваш дизайн включает в себя обычные модульные вставки, вы можете вместо этого рассмотреть другие технологии, такие как потоковая передача.

Подробнее об основных различиях между системами OLAP и OLTP вы можете прочитать в одной из моих последних статей.

Последние мысли

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

Обобщить,

• Избегайте SELECT * и вместо этого убедитесь, что вы запрашиваете только те поля, которые вам нужны
• По возможности отдавайте предпочтение оконным функциям вместо самосоединений (например, если то, что вам нужно вычислить, зависит от строки)
• Разумно выбирайте ключи секционирования, чтобы избежать асимметрии данных. Если это невозможно, убедитесь, что вы применяете фильтры как можно раньше.
• Избегайте объединений, которые будут генерировать больше выходных данных, чем входных.
• Предпочитайте секционирование таблиц, а не их сегментирование, так как первое более эффективно и экономично.
• Избегайте модульных операторов DML — BigQuery — это система OLAP, и ее следует рассматривать как таковую.

Стать участником и читать все истории на Medium. Ваш членский взнос напрямую поддерживает меня и других писателей, которых вы читаете. Вы также получите полный доступ ко всем историям на Medium.

Статьи по теме, которые вам также могут понравиться