Conformidade com ANSI no Databricks Runtime

Aplica-se a: Databricks Runtime

Este artigo descreve a conformidade ANSI no Databricks Runtime. Para o modo ANSI no Databricks SQL, confira ANSI_MODE.

O Spark SQL tem duas opções para atuar em conformidade com a norma ANSI SQL: spark.sql.ansi.enabled e spark.sql.storeAssignmentPolicy.

Quando spark.sql.ansi.enabled é definido como true, o Spark SQL usa um dialeto compatível com a ANSI em vez de ser compatível com o Hive. Por exemplo, o Spark gera uma exceção em runtime em vez de retornar resultados nulos quando as entradas para um operador/função de SQL são inválidas. Alguns recursos de dialeto da ANSI podem não ser diretamente da norma ANSI SQL, mas os comportamentos deles se adaptam ao estilo da ANSI SQL.

Além disso, o Spark SQL tem uma opção independente para controlar comportamentos de conversão implícitos ao armazenar linhas em uma tabela. Os comportamentos de conversão são definidos como regras de atribuição de loja no padrão.

Quando spark.sql.storeAssignmentPolicy é definido como ANSI, o Spark SQL está em conformidade com as regras de atribuição de armazenamento da ANSI. Essa é uma configuração separada porque seu valor padrão é ANSI, enquanto a configuração spark.sql.ansi.enabled é desabilitada por padrão.

A tabela a seguir resume esse comportamento:

As subseções a seguir apresentam alterações de comportamento em operações aritméticas, conversões de tipo e análises de SQL quando o modo ANSI está habilitado. No Spark SQL, há três tipos de conversão de tipo. Este artigo apresentará cada um deles: conversão, atribuição de armazenamento e coerção de tipo.

Operações aritméticas

No Spark SQL, por padrão, não são feitas verificações de estouros nas operações aritméticas executadas em tipos numéricos (com a exceção de decimal). Isso significa que, se uma operação causa estouros, o resultado é o mesmo com a operação correspondente em um programa Java ou Scala (por exemplo, se a soma de 2 inteiros for maior do que o valor máximo representável, o resultado será um número negativo). Por outro lado, o Spark SQL retorna nulo para estouros decimais. Quando spark.sql.ansi.enabled é definido como true, e um estouro ocorre em operações aritméticas numéricas e de intervalo, ele gera uma exceção aritmética no runtime.

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

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

Conversão

Quando spark.sql.ansi.enabled é definido como true, a conversão explícita pela sintaxe CAST gera uma exceção de runtime para padrões de conversão ilegais definidos na norma, como conversões de uma cadeia de caracteres para um inteiro.

A cláusula CAST do modo ANSI do Spark segue as regras de sintaxe da seção 6.13 "especificação de conversão" da ISO/IEC 9075-2:2011 Tecnologia de informação — Linguagens de banco de dados - SQL — Parte 2: Fundação (SQL/Fundação), exceto pelo fato de que ela permite especialmente as seguintes conversões de tipo direta que não são permitidas de acordo com a norma ANSI:

• NumericType <=> BooleanType
• StringType <=> BinaryType

As combinações válidas de tipo de dados de origem e de destino em uma expressão CAST são fornecidas pela tabela a seguir. "Y" indica que a combinação é sintaticamente válida sem restrição, e "N" indica que a combinação não é válida.

-- 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

Atribuição de repositório

O padrão da configuração spark.sql.storeAssignmentPolicy é ANSI. Com essa configuração, quando os tipos de dados dos valores de origem não correspondem aos tipos de coluna de destino, o Spark SQL adiciona automaticamente as cláusulas ANSI CAST à instrução INSERT. Durante a inserção de tabelas no âmbito dessa política, o Spark verifica e rejeita conversões inválidas, gerando uma exceção para garantir a qualidade dos dados. Isso significa que, se uma tentativa de inserção falhar devido a uma incompatibilidade de tipos, isso não resultará em nenhum dado sendo gravado na tabela parcialmente.

Exemplos:

-- 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

Esses exemplos mostram o Spark SQL impedindo que dados incompatíveis sejam inseridos, mantendo, assim, a integridade dos dados.

Quando a spark.sql.storeAssignmentPolicy estiver configurada como LEGACY, o Spark SQL será revertido para o comportamento predominante até o Spark 2.x. Nesse modo, em vez de usar ANSI CAST, o módulo aplica operações CAST herdadas. No âmbito dessa política, as conversões inválidas durante inserções de tabelas resultam na inserção de valores NULL ou incorretos em vez de gerar uma exceção. Exemplos:

-- 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)

Coerção de tipo

Promoção e precedência de tipo

Quando spark.sql.ansi.enabled é definido como true, o Spark SQL usa várias regras que administram como os conflitos entre tipos de dados são resolvidos. No centro dessa resolução de conflitos está a Lista de Precedência de Tipo que define se os valores de um determinado tipo de dados podem ser promovidos para outro tipo de dados de forma implícita.

• Para a resolução de tipo menos comum, float é ignorado para evitar a perda de precisão.

** Para um tipo complexo, a regra de precedência aplica-se recursivamente a seus elementos de componente.

Regras especiais se aplicam ao tipo de cadeia de caracteres e ao NULL não tipado. NULL pode ser promovido para qualquer outro tipo, mas a cadeia de caracteres pode ser promovida a qualquer tipo de dados simples.

Essa é uma representação gráfica da lista de precedência como uma árvore direcionada:

Resolução de tipo menos comum

O tipo menos comum de um conjunto de tipos é o tipo mais estreito acessível da lista de precedência por todos os elementos do conjunto de tipos.

A resolução de tipo mínimo comum é usada para:

• Decidir se uma função que espera um parâmetro de um tipo pode ser chamada usando um argumento de um tipo mais estreito.
• Derivar o tipo de argumento para funções que esperam um tipo de argumento compartilhado para vários parâmetros, como união, menos ou mais.
• Derivar os tipos de operando para operadores como operações aritméticas ou comparações.
• Derivar o tipo de resultado para expressões como a expressão CASE.
• Derivar os tipos de elemento, chave ou valor para construtores array e map.

Regras especiais são aplicadas se o tipo menos comum é resolvido para FLOAT. Com valores de tipo float, se algum tipo é INT, BIGINT ou DECIMAL, o tipo menos comum é enviado para DOUBLE para evitar uma possível perda de dígitos.

-- 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.

Funções do SQL

O comportamento de algumas funções SQL pode ser diferente no modo ANSI (spark.sql.ansi.enabled=true).

• size: essa função retorna NULL para entrada nula no modo ANSI.
• element_at:
  • Essa função gera ArrayIndexOutOfBoundsException quando índices inválidos estão sendo usados.
  • Essa função gera NoSuchElementException quando a chave não existe no mapa.
• elt: essa função gera ArrayIndexOutOfBoundsException quando índices inválidos estão sendo usados.
• make_date: essa função falha com uma exceção se a data do resultado é inválida.
• make_timestamp: essa função falha com uma exceção se o carimbo de data/hora do resultado é inválido.
• make_interval: essa função falha com uma exceção se o intervalo do resultado é inválido.
• next_day: essa função gera IllegalArgumentException se a entrada não é um dia da semana válido.
• parse_url: essa função gera IllegalArgumentException se uma cadeia de caracteres de entrada não é uma URL válida.
• to_date: essa função falha com uma exceção quando a cadeia de caracteres de entrada não pode ser analisada ou se a cadeia de caracteres de padrão é inválida.
• to_timestamp: essa função falha com uma exceção quando a cadeia de caracteres de entrada não pode ser analisada ou se a cadeia de caracteres de padrão é inválida.
• to_unix_timestamp: essa função falha com uma exceção quando a cadeia de caracteres de entrada não pode ser analisada ou se a cadeia de caracteres de padrão é inválida.
• unix_timestamp: essa função falha com uma exceção quando a cadeia de caracteres de entrada não pode ser analisada ou se a cadeia de caracteres de padrão é inválida.

Operadores SQL

O comportamento de alguns operadores SQL pode ser diferente no modo ANSI (spark.sql.ansi.enabled=true).

• array_col[index]: esse operador gera ArrayIndexOutOfBoundsException se está usando índices inválidos.
• map_col[key]: essa função gera NoSuchElementException quando a chave não existe no mapa.
• CAST(string_col AS TIMESTAMP): esse operador falha com uma exceção quando a cadeia de caracteres de entrada não pode ser analisada.
• CAST(string_col AS DATE): esse operador falha com uma exceção quando a cadeia de caracteres de entrada não pode ser analisada.

Funções úteis no modo ANSI

Quando o modo ANSI está ativado, ele gera exceções para operações inválidas. Você pode usar as funções SQL a seguir para suprimir essas exceções.

• try_cast: idêntico a CAST, exceto pelo fato de que ele retorna o resultado NULL em vez de lançar uma exceção no erro de runtime.
• try_add: idêntico ao operador de adição +, exceto pelo fato de que ele retorna o resultado NULL em vez de lançar uma exceção no estouro de valor integral.
• try_divide: idêntico ao operador de divisão /, exceto pelo fato de que ele retorna o resultado NULL em vez de lançar uma exceção ao dividir 0.

Palavras-chave de SQL

Quando spark.sql.ansi.enabled é verdadeiro, o Spark SQL usa o analisador do modo ANSI. Nesse modo, o Spark SQL tem dois tipos de palavras-chave:

• Palavras-chave reservadas: palavras-chave reservadas e não podem ser usadas como identificadores para tabela, exibição, coluna, função, alias etc.
• Palavras-chave não reservadas: palavras-chave que têm um significado especial apenas em contextos específicos e podem ser usadas como identificadores em outros contextos. Por exemplo, EXPLAIN SELECT ... é um comando, mas EXPLAIN pode ser usado como identificadores em outros locais.

Quando o modo ANSI está desabilitado, o Spark SQL tem dois tipos de palavras-chave:

• Palavras-chave não reservadas: a mesma definição de quando o modo ANSI está habilitado.
• Palavras-chave não reservadas estritas: uma versão estrita de palavras-chave não reservadas, que não podem ser usadas como alias de tabela.

Por padrão, spark.sql.ansi.enabled é falso.

Veja abaixo uma lista de todas as palavras-chave no Spark SQL.