Преобразования типов в SQL-запросов Apache Hive и не только: сравнение разных версий и СУБД

Сегодня рассмотрим тему, полезную для обучения администраторов SQL-on-Hadoop и разработчиков распределенных приложений: операции сравнения и арифметические вычисления между строковыми и десятичными типами в Apache Hive 1.2.0 и 3.1.0, а также MySQL и Microsoft SQL Server 2017.

Про типы данных и SQL-запросы в Apache Hive

Чтобы упростить сравнение, будем считать типы данных STRING, CHAR и VARCHAR строками. Необходимость детального рассмотрения операции сравнения и арифметические вычисления между строковыми и десятичными типами данных в Apache Hive связана со следующими ошибками:

• тип не определен правильно для сравнения между десятичным столбцом и строковой константой;
• неверное неявное преобразование типов при сравнении десятичных знаков и строк

Во многих случаях при сравнении десятичных знаков и строк (литералы/столбцы) сравнение выполняется с использованием типа double (с двойной точностью), что может привести к весьма неожиданным результатам в ответах на запросы. Вот пример такого SQL-запроса:

CREATE TABLE dec_str_tbl (decimal_col DECIMAL(21,1), string_col VARCHAR(100));INSERT INTO dec_str_tbl VALUES (12000000000000000000.5,’12000000000000000000′)select * from dec_str_tbl inner join t_str on decimal_col=str_col;

С точки зрения операций между десятичными числами и строками равенство (=) рассматривается как представление операций сравнения, а сложение (+) для арифметических операций. Оба оператора являются бинарными, поэтому они принимают два операнда. В Hive значение десятичного/строкового типа может отображаться в запросе одним из пяти способов, перечисленных ниже:

• столбец десятичного типа (DECIMAL(21,1));
• столбец строкового типа (VARCHAR(100));
• числовая константа/литерал, за которой следует суффикс BD (12000000000000000000.5BD);
• числовой литерал с десятичной точкой и без маркера экспоненты (e) (120000000000000000000,5);
• строковая константа/литерал (`12000000000000000000`).

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

Разумеется, это не исчерпывающий список, т.к. он не охватывает все возможные крайние случаи, которые могут создавать проблемы при сравнении десятичных знаков и строк, но его достаточно, чтобы продемонстрировать основные проблемы/различия между версиями Hive и другими СУБД.

Результаты экспериментов

В качестве эксперимента в Hive 1.2.0 и 3.1.0, MySQL и Microsoft SQL Server 2017 была создана таблица со следующими данными:

CREATE TABLE dec_str_tbl (decimal_col DECIMAL(21,1), string_col VARCHAR(100));

INSERT INTO dec_str_tbl VALUES (12000000000000000000.5,’12000000000000000000′)

Результаты ранее 20 показанных SQL-запросов на этой таблице в разных СУБД выглядят по-разному:

Например, в Microsoft SQL Server запросы сравнения немного отличаются, потому что система не разрешает логические выражения в предложении SELECT, поэтому его следует перемещать в предложение WHERE. Если запрос возвращает строку, в этом случае результат отмечается как true, иначе — false. MySQL использует единицу для обозначения истинности и ноль для обозначения ложности.

Начиная с Hive 2.3.0 числовые константы/литералы с десятичной точкой и без маркера экспоненты (e) имеют тип DECIMAL, а до этого они были типа DOUBLE. Это существенное изменение, которое также влияет на вывод типов для различных операций (включая сравнения и арифметические операции) и приводит к изменениям, наблюдаемым в запросах Q4 и Q14. Новое поведение в Hive 3.1.0 встроено в MySQL и MSSQL, а также предусмотрено стандартом SQL. Поведение Hive до версии 2.3.0, где операнды приводились к double, не возвращается.

В то же время в Hive 2.3.0 вывод типа для арифметических операций изменился, чтобы отдать приоритет типу Double, а не десятичному, когда одна сторона операции является приблизительным числом с плавающей или двойной запятой. Это снова соответствует стандарту SQL, который определяет результат как приближенное числовое значение, зависящее от реализации, и оказывает положительное влияние на производительность, поскольку операции с точными числовыми (десятичными) типами данных выполняются медленнее.

Большинство СУБД склонны преобразовывать строковый тип данных в числовой для выполнения операций сравнения и арифметических операций, как и Hive. Долгое время Apache Hive приводил строки к типу double для выполнения арифметических операций, а для сравнения – к decimal.

Теперь десятичные и строковые типы сравниваются путем преобразования строки в double, чтобы унифицировать способ обработки десятичных чисел и строк как в операциях сравнения, так и в арифметических операциях.

Начиная с Hive 2.3.0 и до настоящего времени, сравнение десятичного столбца со строковым столбцом (Q1) выполняется путем приведения обеих сторон к double. Это отличается от предыдущих версий и может легко привести к неожиданным результатам. Аналогично, сравнение строкового столбца с десятичным литералом (Q5) с использованием double. Это приводит к тому, что такие запросы, как Q1, Q5, возвращают истину, в то время как ожидаемый результат оказывается ложным, поскольку сравниваемые значения не равны. Это неожиданно, но такое поведение можно наблюдать и в других СУБД, в частности, в MySQL.

А вот сравнение между десятичным столбцом и строковым литералом (Q2) не приводит к неожиданным результатам в Hive 3.1.0 за счет приведения операндов к double.

Таким образом, Apache Hive 3.1.0 и MySQL полностью интегрированы, когда речь идет о сравнениях и арифметических действиях между строками и десятичными знаками. Но результаты запросов Q1 и Q5 могут внести некоторую путаницу, а также обратно несовместимы с версиями Hive до 2.3.0. Чтобы решить неожиданные результаты в запросах, подобных Q1 и Q5, нужно обеспечить, чтобы любое сравнение между десятичным типом и строковым типом выполнялось путем преобразования их в десятичные числа – в большинстве случаев это приведет к более предсказуемым результатам. Но, хотя это изменение и восстановит совместимость с версиями до 2.3.0, оно будет несовместимо с версиями от 2.3.0 до 3.1.2.

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

• должен ли результат быть нулевым?
• выдать ли ошибку?
• отбросить ли десятичные цифры, чтобы приспособиться к запрошенному масштабу?

Наконец, такое изменение может привести к побочным эффектам, которые трудно предсказать заранее – например, использование числовых литералов в строковых столбцах при попытке INSERT и/или наоборот после изменения. В заключение отметим, что в Hive 1.2.0 и 3.1.0 арифметические операции между строкой и десятичными числами выполняются с использованием double. А сравнения между одними и теми же типами выполняются с использованием decimal. При выравнивании арифметических операций и операций сравнения между строками по аналогии с Microsoft SQL Server 2017, это изменение может быть несовместимыми с предыдущими версиями. О том, как использовать Trino в качестве SQL-интерфейса доступа к Apache Hive и не только, читайте в нашей новой статье. 

Освоить все тонкости работы с Apache Hive для эффективной аналитики больших данных вам помогут специализированные курсы в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:

• Hadoop SQL администратор Hive
• Интеграция Hadoop и NoSQL

Источники

1. https://medium.com/@zabetak/comparisons-and-arithmetic-operations-between-decimals-and-strings-in-hive-78e105931a50
2. https://issues.apache.org/jira/browse/HIVE-18434
3. https://issues.apache.org/jira/browse/HIVE-24528
4. https://issues.apache.org/jira/browse/HIVE-13945
5. https://issues.apache.org/jira/browse/HIVE-13380