Соответствие ANSI в Databricks Runtime

Область применения: Databricks Runtime

В этой статье описывается соответствие ANSI в Databricks Runtime. Для режима ANSI в Databricks SQL, см. ANSI_MODE.

SQL Spark поддерживает два варианта обеспечения соответствия стандарту ANSI — spark.sql.ansi.enabled и spark.sql.storeAssignmentPolicy.

Если для spark.sql.ansi.enabled задано значение true, Spark SQL использует диалект, соответствующий ANSI, вместо соответствия Hive. Например, при недопустимых входных данных оператора SQL или функции Spark вызывает исключение во время выполнения, а не возвращает результаты NULL. Некоторые функции диалекта ANSI могут быть не из стандарта ANSI SQL напрямую, но их поведение соответствует стилю ANSI SQL.

Кроме того, Spark SQL имеет независимый параметр для управления неявным поведением приведения при хранении строк в таблице. Правила приведения типов определены как правила распределения хранилища в стандарте.

Если spark.sql.storeAssignmentPolicy установлен в ANSI, Spark SQL соответствует правилам назначения хранилища ANSI. Это отдельная конфигурация, так как ее значение по умолчанию — ANSI, а конфигурация spark.sql.ansi.enabled по умолчанию отключена.

В следующей таблице приведены сведения о поведении:

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

Арифметические операции

В SQL Spark арифметическая операции с числовыми типами (кроме decimal) по умолчанию не проверяются на переполнение. Это означает, что если операция приводит к переполнению, результат будет таким же, как и соответствующая операция в программе Java или Scala (например, если сумма 2 целых чисел превышает максимальное значение, которое может быть представлено, результатом будет отрицательное число). С другой стороны, Spark SQL возвращает NULL при десятичных переполнениях. Если spark.sql.ansi.enabled установлено на true и переполнение происходит при числовых и интервальных арифметических операциях, то в момент выполнения возникает арифметическое исключение.

-- `spark.sql.ansi.enabled=true`
> SELECT 2147483647 + 1;
 error: integer overflow

-- `spark.sql.ansi.enabled=false`
> SELECT 2147483647 + 1;
  -2147483648

Состав

Если spark.sql.ansi.enabled задано значение true, явное приведение с помощью синтаксиса CAST создает исключение среды выполнения для незаконных шаблонов приведения, определенных в стандарте, таких как приведение из строки в целое число.

Предложение CAST режима ANSI Spark следует правилам синтаксиса раздела 6.13 "Спецификация приведения" в стандарте ISO/МЭК 9075-2:2011 Информационные технологии — Языки баз данных - SQL. Часть 2: Foundation (SQL/Foundation), за исключением того, что оно специально позволяет выполнять следующие простые преобразования типов, которые запрещены в стандарте ANSI:

• NumericType <=> BooleanType
• ТипСтроки <=> ТипБинарный

Допустимые сочетания исходных и целевых типов данных в выражении CAST приведены в следующей таблице. "Да" означает, что сочетание является синтаксически допустимым без ограничений, а "Нет" означает, что сочетание недопустимо.

-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true`
> SELECT CAST('a' AS INT);
  ERROR: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed.

> SELECT CAST(2147483648L AS INT);
  ERROR: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow.

> SELECT CAST(DATE'2020-01-01' AS INT)
  ERROR: [DATATYPE_MISMATCH.CAST_WITH_FUNC_SUGGESTION] Cannot resolve "CAST(DATE '2020-01-01' AS INT)" due to data type mismatch: cannot cast "DATE" to "INT".

-- `spark.sql.ansi.enabled=false` (This is a default behavior)
> SELECT cast('a' AS INT);
  null

> SELECT CAST(2147483648L AS INT);
  -2147483648

> SELECT CAST(DATE'2020-01-01' AS INT);
  null

Назначение хранения

Настройка spark.sql.storeAssignmentPolicy по умолчанию установлена на ANSI. При использовании этого параметра, если типы данных исходных значений не соответствуют типам целевых столбцов, Spark SQL автоматически добавляет предложения ANSI CAST в инструкцию INSERT . При вставке таблицы согласно этой политике Spark проверяет и отклоняет недопустимые преобразования типов, выдавая исключение для обеспечения качества данных. Это означает, что если попытка вставки завершается ошибкой из-за несоответствия типа, она не приведет к частичной записи данных в таблицу.

Примеры:

-- spark.sql.storeAssignmentPolicy=ANSI
> CREATE TABLE test(i INT);
> INSERT INTO test VALUES (2147483648L);
  ERROR: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail to insert a value of "BIGINT" type into the "INT" type column `i` due to an overflow.

> INSERT INTO test VALUES ('a');
  ERROR: [CAST_INVALID_INPUT ERROR] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed

В этих примерах показано, как Spark SQL предотвращает вставку несовместимых данных, тем самым сохраняя целостность данных.

Если для spark.sql.storeAssignmentPolicy задано значение LEGACY, Spark SQL возвращается к поведению, распространенному до Spark 2.x. В этом режиме вместо использования ANSI CAST применяется устаревшие операции CAST. В рамках этой политики недопустимые приведения во время вставки в таблицу приводят к вставке значений NULL или некорректных значений, а не к исключению. Примеры:

-- spark.sql.storeAssignmentPolicy=LEGACY
> CREATE TABLE test(i INT);
> INSERT INTO test VALUES (2147483648L);
> INSERT INTO test VALUES ('a');
> SELECT * FROM test;

-- Results
--  -2147483648 (incorrect value due to overflow)
--  null (cannot cast 'a' to INT)

Приведение типов

Повышение и приоритет типов

Если spark.sql.ansi.enabled установлено на true, Spark SQL использует несколько правил, которые управляют разрешением конфликтов между типами данных. В основе этого разрешения конфликтов является список приоритетов типов, определяющий, могут ли значения заданного типа данных быть повышены до другого типа данных неявно.

• Чтобы избежать потери точности, пропускается разрешение float для наименее общего типа.

** Для сложного типа правило приоритета применяется рекурсивно к элементам его компонентов.

К строковому типу (String) и нетипизированному значению NULL применяются особые правила. Значение NULL можно преобразовать в любой другой тип, а значение String можно преобразовать в любой скалярный тип данных.

Это графическое изображение списка приоритетов в виде направленного дерева:

Разрешение наименьшего общего типа

Наименее распространенный тип из набора типов — самый узкий тип, доступный из списка приоритетов всеми элементами набора типов.

Разрешение наименее распространённого типа используется в следующих целях:

• Чтобы решить, можно ли вызвать функцию, ожидающую параметр определенного типа, с помощью аргумента более узкого типа.
• Определите тип аргумента для функций, которые ожидают один и тот же тип аргумента для нескольких параметров, таких как coalesce, least или greatest.
• Чтобы вывести типы операндов для операторов, таких как арифметические операции или сравнения.
• Определите тип результата для выражений, таких как выражение CASE.
• Определить типы элементов, ключей или значений для конструкторов массивов и карт.

Если наименьший общий тип определяется как FLOAT, применяются специальные правила. При использовании значений с плавающей запятой, если любой из типов является INT, BIGINT или DECIMAL, наименее общий тип преобразуется в DOUBLE, чтобы избежать потенциальной потери цифр.

-- The coalesce function accepts any set of argument types as long as they share a least common type.
-- The result type is the least common type of the arguments.
> SET spark.sql.ansi.enabled=true;

> SELECT typeof(coalesce(1Y, 1L, NULL));
BIGINT

> SELECT typeof(coalesce(1, DATE'2020-01-01'));
Error: Incompatible types [INT, DATE]

> SELECT typeof(coalesce(ARRAY(1Y), ARRAY(1L)));
ARRAY<BIGINT>

> SELECT typeof(coalesce(1, 1F));
DOUBLE

> SELECT typeof(coalesce(1L, 1F));
DOUBLE

> SELECT (typeof(coalesce(1BD, 1F)));
DOUBLE

-- The substring function expects arguments of type INT for the start and length parameters.
> SELECT substring('hello', 1Y, 2);
he

> SELECT substring('hello', '1', 2);
he

> SELECT substring('hello', 1L, 2);
Error: Argument 2 requires an INT type.

> SELECT substring('hello', str, 2) FROM VALUES(CAST('1' AS STRING)) AS T(str);
Error: Argument 2 requires an INT type.

Функции SQL

Поведение некоторых функций SQL может отличаться в режиме ANSI (spark.sql.ansi.enabled=true).

• size — эта функция возвращает значение NULL для входных данных со значением NULL в режиме ANSI.
• element_at:
  • Эта функция выбрасывает исключение ArrayIndexOutOfBoundsException при использовании недопустимых индексов.
  • Эта функция выбрасывает NoSuchElementException, если ключ не существует в карте.
• elt — эта функция выбрасывает ArrayIndexOutOfBoundsException при использовании недопустимых индексов.
• make_date — эта функция вызывает сбой с исключением, если дата результата недопустима.
• make_timestamp — эта функция вызывает ошибку с исключением, если метка времени результата недопустима.
• make_interval — если интервал результата недействителен, эта функция завершается сбоем с исключением.
• next_day — эта функция создает исключение IllegalArgumentException, если входные данные не являются допустимым днем недели.
• parse_url — эта функция выбрасывает исключение IllegalArgumentException, если строка ввода не является допустимым URL-адресом.
• to_date: эта функция завершается ошибкой, если входная строка не может быть проанализирована, или строка шаблона недопустима.
• to_timestamp: эта функция завершается ошибкой, если входная строка не может быть проанализирована, или строка шаблона недопустима.
• to_unix_timestamp: эта функция завершается ошибкой, если входная строка не может быть проанализирована, или строка шаблона недопустима.
• unix_timestamp: эта функция завершается ошибкой, если входная строка не может быть проанализирована, или строка шаблона недопустима.

Операторы SQL

Поведение некоторых операторов SQL может отличаться в режиме ANSI (spark.sql.ansi.enabled=true).

• array_col[index] — этот оператор выбрасывает исключение ArrayIndexOutOfBoundsException при использовании недопустимых индексов.
• map_col[key] — этот оператор вызывает исключение NoSuchElementException, если ключ не существует в карте.
• CAST(string_col AS TIMESTAMP): этот оператор завершается ошибкой, если входная строка не может быть проанализирована.
• CAST(string_col AS DATE): этот оператор завершается ошибкой, если входная строка не может быть проанализирована.

Полезные функции для режима ANSI

Если режим ANSI включен, он создает исключения при недопустимых операциях. Для подавления этих исключений можно использовать перечисленные ниже функции SQL.

• try_cast — идентична CAST, за исключением того, что она возвращает результат NULL, а не создает исключение при возникновении ошибки времени выполнения.
• try_add идентичен оператору сложения +, за исключением того, что он возвращает результат NULL, а не создает исключение в случае переполнения целочисленного значения.
• try_divide идентичен оператору деления /, за исключением того, что он возвращает результат NULL, а не создает исключение в случае деления на 0.

Ключевые слова SQL

Если spark.sql.ansi.enabled имеет значение true, SQL Spark будет использовать средство синтаксического анализа режима ANSI. В этом режиме SQL Spark имеет два вида ключевых слов:

• Зарезервированные ключевые слова: ключевые слова, зарезервированные и не могут использоваться в качестве идентификаторов для таблицы, представления, столбца, функции, псевдонима и т. д.
• Незарезервированные ключевые слова — ключевые слова, которые имеют специальное значение только в определенных контекстах и могут использоваться в качестве идентификаторов в других контекстах. Например, EXPLAIN SELECT ... является командой, но EXPLAIN можно использовать в качестве идентификаторов в других местах.

Если режим ANSI отключен, SQL Spark имеет два вида ключевых слов:

• Незарезервированные ключевые слова — то же определение, что и в режиме ANSI.
• Ключевые слова, не являющиеся зарезервированными, — строгая версия не зарезервированных ключевых слов, которая не может использоваться в качестве псевдонима таблицы.

Значение spark.sql.ansi.enabled по умолчанию — false.

Ниже приведен список всех ключевых слов в Spark SQL.