Conformidade com ANSI no Databricks Runtime

Aplica-se a: Databricks SQL Databricks Runtime

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

O Spark SQL tem duas opções para dar suporte à conformidade com o padrão ANSI SQL: spark.sql.ansi.enabled e spark.sql.storeAssignmentPolicy.

• spark.sql.ansi.enabled (Modo ANSI)

  • Utiliza um dialeto SQL compatível com ANSI em vez do dialeto compatível com Hive.
  • Gera exceções de runtime para entradas inválidas em vez de devolver null.
  • Ativado por defeito no Apache Spark 4.0 e Databricks Runtime 17.0 e superiores.

• spark.sql.storeAssignmentPolicy (política de atribuição de loja)

  • Controla o casting implícito durante as inserções de tabelas.
  • Por defeito, é ANSI e é independente de spark.sql.ansi.enabled.

A tabela a seguir resume o comportamento:

As subseções a seguir apresentam alterações de comportamento em operações aritméticas, conversões de tipo e análise SQL quando o modo ANSI está habilitado. Para conversões de tipo no Spark SQL, existem três tipos deles e este artigo irá apresentá-los um a um: cast, atribuição de armazenamento e coerção de tipo.

Operações aritméticas

No Spark SQL, as operações aritméticas executadas em tipos numéricos (com exceção do decimal) não são verificadas quanto a estouros por padrão. Isso significa que, no caso de uma operação causar 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 é um número negativo). Por outro lado, o Spark SQL retorna null para excessos de decimais. Quando spark.sql.ansi.enabled é definido como true e ocorre um overflow em operações aritméticas numéricas e de intervalo, lança-se uma exceção aritmética em tempo de execução.

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

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

Elenco

Quando spark.sql.ansi.enabled é definido como true, a conversão explícita pela sintaxe CAST lança uma exceção em tempo de execução para padrões de conversão ilegais definidos no padrão, como conversões de uma cadeia de caracteres para um inteiro.

A CAST cláusula do modo Spark ANSI segue as regras de sintaxe da seção 6.13 "cast specification" na ISO/IEC 9075-2:2011 Tecnologia da informação — Linguagens de banco de dados - SQL — Parte 2: Foundation (SQL/Foundation), exceto que permite especialmente as seguintes conversões de tipo diretas que não são permitidas de acordo com o padrão ANSI:

• TipoNumérico <=> TipoBooleano
• TipoString <=> TipoBinário

As combinações válidas de tipo de dados de origem e destino em uma expressão CAST são fornecidas pela tabela a seguir. "Y" indica que a combinação é sintaticamente válida sem restrições 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`
> SELECT cast('a' AS INT);
  null

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

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

Atribuição de loja

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

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

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

Quando o spark.sql.storeAssignmentPolicy é definido como LEGACY, o Spark SQL reverte para o comportamento predominante até o Spark 2.x. Neste modo, em vez de usar ANSI CAST, ele aplica operações CAST herdadas. De acordo com esta política, conversões inválidas durante inserções de tabela resultam na inserção de valores NULL ou valores incorretos, em vez de lançar 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)

Tipo de coerção

Promoção de tipo e precedência

Quando spark.sql.ansi.enabled é definido como true, o Spark SQL usa várias regras que regem 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 implicitamente.

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

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

Aplicam-se regras especiais para o tipo String e NULL não tipado. Um NULL pode ser promovido para qualquer outro tipo, enquanto um String pode ser promovido para qualquer tipo de dados simples.

Esta é uma representação gráfica da lista de precedências 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 a partir da lista de precedência por todos os elementos do conjunto de tipos.

A resolução de tipo menos comum é usada para:

• Decida se uma função que espera um parâmetro de um tipo pode ser invocada usando um argumento de um tipo mais restrito.
• Derive o tipo de argumento para funções que esperam um tipo de argumento partilhado para vários parâmetros, como coalesce, mínimo ou máximo.
• Derive os tipos de operandos para operadores, como operações aritméticas ou comparações.
• Derive o tipo de resultado para expressões como a expressão de caso.
• Derive os tipos de elemento, chave ou valor para construtores de matriz e mapa.

Regras especiais são aplicadas se o tipo menos comum resultar em FLOAT. Com valores de tipo float, se qualquer um dos tipos for INT, BIGINT ou DECIMAL, o tipo menos comum é empurrado para DOUBLE para evitar a perda potencial 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 SQL

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

• size: Esta função retorna null para entrada nula no modo ANSI.
• element_at:
  • Esta função lança ArrayIndexOutOfBoundsException se estiver usando índices inválidos.
  • Esta função lança NoSuchElementException se a chave não existir no mapa.
• elt: Esta função lança ArrayIndexOutOfBoundsException se estiver usando índices inválidos.
• make_date: Esta função falha com uma exceção se a data do resultado for inválida.
• make_timestamp: Esta função falhará com uma exceção se o carimbo de data/hora do resultado for inválido.
• make_interval: Esta função falha com uma exceção se o intervalo de resultados for inválido.
• next_day: Esta função lança IllegalArgumentException se a entrada não for um dia da semana válido.
• parse_url: Esta função lança uma exceção IllegalArgumentException se uma string de entrada não for uma URL válida.
• to_date: Esta função falhará com uma exceção se a cadeia de caracteres de entrada não puder ser analisada ou se a cadeia de caracteres de padrão for inválida.
• to_timestamp: Esta função falhará com uma exceção se a cadeia de caracteres de entrada não puder ser analisada ou se a cadeia de caracteres de padrão for inválida.
• to_unix_timestamp: Esta função falhará com uma exceção se a cadeia de caracteres de entrada não puder ser analisada ou se a cadeia de caracteres de padrão for inválida.
• unix_timestamp: Esta função falhará com uma exceção se a cadeia de caracteres de entrada não puder ser analisada ou se a cadeia de caracteres de padrão for inválida.

Operadores SQL

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

• array_col[index]: Este operador lança ArrayIndexOutOfBoundsException se estiver usando índices inválidos.
• map_col[key]: Este operador lança a exceção NoSuchElementException se a chave não existir no mapa.
• CAST(string_col AS TIMESTAMP): Este operador falha com uma exceção se a cadeia de caracteres de entrada não puder ser analisada.
• CAST(string_col AS DATE): Este operador falha com uma exceção se a cadeia de caracteres de entrada não puder ser analisada.

Funções úteis para o modo ANSI

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

• try_cast: idêntico ao CAST, exceto que ele retorna NULL resultado em vez de lançar uma exceção no erro de tempo de execução.
• try_add: idêntico ao operador +add, exceto que retorna o NULL resultado em vez de lançar uma exceção no caso de um estouro de valor integral.
• try_divide: idêntico ao operador /de divisão, exceto que ele retorna NULL resultado em vez de lançar uma exceção na divisão 0.

Palavras-chave SQL

Quando spark.sql.ansi.enabled=true e spark.sql.ansi.enforceReservedKeywords=true, o Spark SQL usa o analisador ANSI. Por padrão, spark.sql.ansi.enforceReservedKeywords é false.

No modo analisador ANSI, as palavras-chave dividem-se em duas categorias:

• Reservado: Não pode ser usado como identificador (tabela, vista, coluna, função, alias, etc.).
• Não reservadas: Atuam como palavras-chave apenas em contextos específicos; caso contrário, podem ser usados como identificadores. Por exemplo, EXPLAIN SELECT ... é um comando, mas EXPLAIN pode ser usado como identificador noutro local.

Quando o analisador ANSI não está em uso, as palavras-chave enquadram-se em:

• Não reservado: Mesma definição mencionada acima.
• Estritamente não reservado: Semelhante ao não reservado, mas não pode ser usado como alias de tabela.

Abaixo está uma lista de todas as palavras-chave no Spark SQL.