7 уроков, которые я вынес в процессе переноса кода dbt из Snowflake в Trino
Миграция с одной платформы данных на другую никогда не бывает такой простой, как кажется с первого взгляда. В процессе переноса нескольких проектов dbt из хранилища данных Snowflake в Starburst (корпоративная версия Trino) мы столкнулись с несколькими критически важными различиями между их диалектами SQL, которые оказалось достаточно сложно устранить. В этой статье я расскажу о 7 таких различиях, а также о том, как нам удалось справиться с ними.
1. Заменить IFF на IF, а PIVOT на ...?
Начнем с самого простого. Некоторые функции SQL в Snowflake в Trino называются по-другому. Например, IFF() в Snowflake –это IF() в Trino, NVL() –это COALESCE(), DATEADD() - это DATE_ADD() и т. д. Эти вещи легко найти и исправить, используя, например, regex-замены.
Для некоторых функций, например DECODE(), не существует простых эквивалентов Trino, но решить эту проблему относительно просто (в случае DECODE - с помощью CASE WHEN). Такие инструменты, как sqlglot помогут Вам выполнить эти преобразования автоматически.
Однако у некоторых функций SQL, таких как PIVOT в Snowflake, в Trino вообще нет эквивалентов. Способа реализовать pivot в Trino, используя только SQL, не существут. Если Вы знаете, над какими значениями Вам нужно выполнить поворот, Вы можете реализовать его вручную, используя агрегатные функции (такие как count_if). Но результирующие SQL-запросы могут стать огромными, потому что Вам придется повторять агрегатную функцию для каждого значения в столбце, над которым нужно выполнить поворот. К счастью, мы можем исправить эту ситуацию, используя для генерации подобных запросов dbt. dbt_utilsto спешит на помощь с макросом pivot()!
2. Неявное приведение типов
При сравнении двух значений, одно из которых имеет тип VARCHAR, а другое - тип INT, Snowflake преобразует varchar в int. Например, 123 = '123' возвращает TRUE в Snowflake. Этот процесс называется неявным приведением типов.
В Trino такого нет. В Trino 123 = '123' вызывает ошибку: «Невозможно применить оператор: varchar(3) = integer». Если Вы хотите сравнить varchar с целым числом, Вы должны явно привести varchar с помощью CAST(x AS INTEGER). Аналогично, если Вы конкатенируете строку с целым числом, используя '123' || 123, Вы получите следующую ошибку:
Для того, чтобы справиться с этой особенностью, пришлось изрядно потрудиться, добавляя касты и размышляя о разных типах данных. Однако я не считаю это напрасной работой. Более внимательная проверка типов в Trino также имеет ряд преимуществ: код более явный, и Вы можете быть более уверены в его корректности, а также в том, что неявные касты не происходят там, где Вы их не ожидаете.
3. Всего самого хорошего в 2.024E3 году!
Как мы уже видели выше, конкатенировать varchars без явного приведения нельзя. Поэтому, если в Snowflake Вы видите следующее:
SELECT ( "year" || '-' || "month" || '-' || "day" ) AS "date_string" FROM orders
Вы можете подумать, что в Trino его можно преобразовать следующим образом:
SELECT
( CAST("year" AS VARCHAR) || '-' ||
CAST("month" AS VARCHAR) || '-' ||
CAST("day" AS VARCHAR)
) AS "date_string"
FROM orders
Если день, месяц и год являются целыми типами, а вместо них используются DOUBLE, Вы получите дату 2.024E3-1.1E1-1.9E1, потому что Trino всегда приводит DOUBLE к VARCHAR в научной нотации, так что 123 становится «1.23E2», а 1 - «1.0E0».
Конечно, в данном случае правильное решение заключается в том, чтобы не хранить эти типы в виде двузначных чисел.
4. Обращение к вычисляемым столбцам
Если в подобном запросе у Вас есть вычисляемый столбец типа foo:
SELECT SOME_LONG_CALCULATION(...) AS foo FROM orders
то в Snowflake Вы можете ссылаться на этот вычисляемый столбец в других частях Вашего SQL-запроса, таких как select, where, group by и т.д. Например, в Snowflake Вы можете сделать следующее:
SELECT SOME_LONG_CALCULATION(...) AS foo, foo*123456 AS bar FROM orders WHERE foo > 10
В случае Trino “эквивалентный” запрос будет выглядеть так:
SELECT SOME_LONG_CALCULATION(...) AS foo, SOME_LONG_CALCULATION(...)*123456 AS bar FROM orders WHERE SOME_LONG_CALCULATION(...) > 10
Как Вы можете видеть, это выглядит довольно громоздко. Одно из возможных решений- вычислять foo в CTE следующим образом:
WITH calculate_foo AS ( SELECT orders.id AS order_id, SOME_LONG_CALCULATION(...) AS foo FROM orders ) SELECT foo, foo*123456 AS bar FROM orders JOIN calculate_foo ON orders.id = calculate_foo.order_id WHERE foo > 10
В простых случаях это работает «на ура», но есть несколько случаев, когда все происходит не так гладко, как хотелось бы:
- Если у Вас есть несколько таких вычисляемых полей, которые зависят друг от друга, Вам потребуется несколько CTE, а это довольно сложно.
- Когда это происходит в подзапросах или других CTE, а не в конечном выражении, это может привести к тому, что Trino не сможет скомпилировать запрос с ошибкой «данный коррелированный подзапрос не поддерживается».
- Наиболее болезненной оказалась проблема, когда запрос все равно выполняется и выглядит так: если вычисление (SOME_LONG_CALCULATION() above) включает оконные функции, то гранулярность окна будет отличаться! Предложение WHERE влияет только на конечный SELECT, но не на CTE! Это может привести к некорректному выводу результата запроса.
Используя dbt, можно реализовать более оптимальное решение: с помощью set инициализировать dbt-переменную foo определением длинного выражения и использовать эту dbt-переменную везде например, так:
{% set foo %} SOME_LONG_CALCULATION(...) {% endset %}
SELECT
{{ foo }} AS foo,
{{ foo }}*123456 AS bar
FROM orders
WHERE {{ foo }} > 10
5. Куда при сортировке деваются NULL?
Если Вы сортируете результат с помощью ORDER BY foo, а некоторые значения foo являются NULL, куда они попадают? На первое место? На последнее? В зависимости от чего? Snowflake и Trino расходятся во мнениях и на этот счет.
Snowflake рассматривает NULL как «наибольшее» возможное значение: при сортировке по возрастанию он ставит NULL последним, при сортировке по убыванию - первым.
Trino, напротив, всегда ставит NULL на последнее место, независимо от того, сортируете ли Вы по возрастанию или по убыванию.
Кто прав? Стандарт ANSI SQL не уточняет, что здесь должно происходить на самом деле, поэтому в каком-то смысле ни правых, ни виноватых нет. Но на мой взгляд, «кажется» неправильным, что в Trino возрастающий порядок не противоположен убывающему.
Поэтому в запросах, где столбец, который может содержать NULL, используется в ORDER BY, всегда указывайте «NULLS FIRST» или «NULLS LAST». Никакой путаницы не будет.
6. Окно по умолчанию для некоторых оконных функций
Рассмотрим следующий запрос:
SELECT LAST_VALUE(delivery_address) IGNORE NULLS OVER ( PARTITION BY customer_id ORDER BY order_time ASC ) AS latest_address FROM orders
Какой адрес должен содержать latest_address?
- (А) Самый последний адрес доставки этого покупателя на момент заказа в текущей строке
- (В) Самый последний адрес доставки этого покупателя во всей таблице заказов?
Здесь есть правильный и неправильный ответ! Правильным ответом, согласно стандарту ANSI SQL, является (...барабанная дробь...) A.
В принципе, для оконных функций, таких как FIRST_VALUE(), LAST_VALUE() и NTH_VALUE(), если Вы не указываете окно явно, должно использоваться окно RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Trino реализует это корректно.
Snowflake делает по – другому. Если Вы явно не указали окно, он использует окно по умолчанию ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. В результате Snowflake возвратит B вместо A. Другими словами, Snowflake не следует стандарту ANSI SQL.
Snowflake задокументировали это отклонение от стандарта. Это не ошибка, а особенность? Я считаю, что выборочное следование стандарту – не самое правильное решение.
Поэтому теперь, мы когда используем LAST_VALUE(), мы всегда явно указываем окно:
SELECT
LAST_VALUE(delivery_address) IGNORE NULLS OVER (
PARTITION BY customer_id
ORDER BY order_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS latest_address
FROM orders
7. Какой день недели приходится на воскресенье?
Последняя особенность - одна из самых «незначительных», но ее выявление заняло довольно много времени, потому что выглядит она вполне невинно.
В Snowflake есть функция DAYOFWEEK(), которая возвращает день недели. В Trino также есть подобная функция - DAY_OF_WEEK().Поэтому при переносе кода dbt из одной версии в другую можно подумать, что речь идет о простой замене DAYOFWEEK(...) на DAY_OF_WEEK(...).
Естественно, это не так. Разница между ними заключается в том, что DAYOFWEEK() в Snowflake возвращает 0 для воскресенья (точнее, фактическое значение зависит от параметра WEEK_START), а в Trino DAY_OF_WEEK() для воскресенья возвращает 7. Для всех остальных дней недели эти функции возвращают одно и то же значение.
Эквивалентной функцией для DAY_OF_WEEK() в Trino является DAYOFWEEKISO() (вместо DAYOFWEEK()). Если это часть большого SQL-запроса, то увидеть это сложно.
Бонус: дата без пробелов
Последний пункт с технической точки не является разницей между Snowflake и Trino, но все же он вызвал достаточно много нареканий, поэтому я все равно его включил в свой список. Часть нашего кода Snowflake генерирует последовательность составляющих даты примерно так:
SELECT
DATEADD(DAY, SEQ4(), TO_DATE('01-01-2019', 'dd-MM-yyyy')) AS "date_day"
FROM TABLE(GENERATOR(ROWCOUNT => 365))
В Trino для генерации последовательностей нет функции типа SEQ4(), поэтому нам пришлось искать другое решение. И снова на помощь приходит dbt_utils с макросом date_spine. Он может генерировать даты с помощью кода dbt, который выглядит следующим образом:
{{
dbt_utils.date_spine( datepart="day", start_date="cast('2019-01-01' as date)", end_date="cast('2020-01-01' as date)" ) }}
Если Вы выполните приведенные выше запросы, они, вероятно, дадут одинаковый результат. Но если они являются частью более крупного запроса, то результат может отличаться (а может и не отличаться). В нашем случае разница зависела от размера хранилища Snowflake, на котором выполнялся запрос.
Виной тому - функция SEQ4. Если Вы перейдете на ее описание, данное в официальном руководстве, Вы увидите следующее:
Важно Эта функция использует последовательности для получения уникального набора возрастающих целых чисел, но не обязательно создает последовательность без пробелов. При работе с большим количеством данных в последовательности могут появиться пробелы. Если требуется полностью упорядоченная последовательность без пробелов, воспользуйтесь оконной функцией ROW_NUMBER.
Другими словами, функция SEQ4 может вернуть последовательность 1, 2, 3, 4, а может вернуть 1, 2, 17, 18. Сложность заключается в том, что SEQ4 обычно не выдает пробелы в небольших запросах или небольших хранилищах, которые обычно используются для «отладки» запроса. Поэтому во время отладки SEQ4 возвращает 1, 2, 3 и 4, как и ожидалось. Пробелы начинают появляться только при выполнении запроса на большом хранилище, что еще больше усложняет поиск причины разницы.
Так что будьте осторожны, используйте ROW_NUMBER, как сказано в документации Snowflake, или еще лучше: используйте макрос dbt_utils.date_spine.
Заключение
В этой статье мы рассказали Вам о различиях между диалектами SQL в Snowflake и Trino, на поиск и/или устранение которых у нас ушло достаточно много времени. В конечном итоге ни одно из различий, с которыми мы столкнулись, не оказалось непреодолимым: все данные были перенесены и теперь успешно работают на Trino.
