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

Автор Категория ,
Преобразования типов в 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`).

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

SQL Hive, HiveQL
Набор SQL-запросов для сравнения и арифметических операций на строковых и десятичных типах данных

Разумеется, это не исчерпывающий список, т.к. он не охватывает все возможные крайние случаи, которые могут создавать проблемы при сравнении десятичных знаков и строк, но его достаточно, чтобы продемонстрировать основные проблемы/различия между версиями 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-запросов на этой таблице в разных СУБД выглядят по-разному:

Apache Hive SQL example
Результаты выполнения 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 в Москве:

Источники

  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