Databricks 런타임의 ANSI 규정 준수

적용 대상: Databricks 런타임

이 문서에서는 Databricks Runtime의 ANSI 규정 준수에 대해 설명합니다. Databricks SQL의 ANSI 모드는 ANSI_MODE 참조하세요.

Spark SQL에는 ANSI SQL 표준 준수를 지원하는 두 가지 옵션, spark.sql.ansi.enabled와 spark.sql.storeAssignmentPolicy가 있습니다.

spark.sql.ansi.enabled가 true로 설정되면 Spark SQL은 Hive 규격 대신 ANSI 규격 언어를 사용합니다. 예를 들어 Spark는 SQL 연산자/함수에 대한 입력이 잘못된 경우 null 결과를 반환하는 대신 런타임에 예외를 throw합니다. 일부 ANSI 언어 기능은 ANSI SQL 표준에서 직접 사용할 수 없지만 동작은 ANSI SQL 스타일과 일치합니다.

또한 Spark SQL에는 테이블에 행을 저장할 때 암시적 캐스팅 동작을 제어하는 독립적인 옵션이 있습니다. 캐스팅 동작은 표준의 저장소 할당 규칙으로 정의됩니다.

spark.sql.storeAssignmentPolicy가 ANSI로 설정되면 Spark SQL가 ANSI 저장 할당 규칙을 준수합니다. 이 구성은 기본값이 ANSI이므로 별도의 구성이지만 spark.sql.ansi.enabled 구성은 기본적으로 사용하지 않도록 설정됩니다.

다음 표에서는 동작을 요약해서 보여 줍니다.

다음 하위 섹션에서는 ANSI 모드를 사용할 때 산술 연산, 형식 변환 및 SQL 구문 분석의 동작 변경 내용을 나타냅니다. 세 가지 종류의 Spark SQL 형식 변환이 있으며 이 문서에서는 캐스트, 저장 할당 및 형식 강제 변환을 하나씩 소개합니다.

산술 연산

Spark SQL에서 숫자 형식(10진 제외)으로 수행되는 산술 연산은 기본적으로 오버플로를 확인하지 않습니다. 즉, 연산이 오버플로를 발생시키는 경우 Java 또는 Scala 프로그램의 해당 연산과 결과와 동일합니다(예: 2개의 정수의 합계가 표현 가능한 최댓값보다 높은 경우 결과는 음수). 반면 Spark SQL 10진 오버플로에 대해 null을 반환합니다. spark.sql.ansi.enabled가 true로 설정되고 오버플로가 숫자 및 간격 산술 연산에서 발생하는 경우 런타임에 산술 예외를 throw합니다.

-- `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 명시적 캐스팅은 문자열에서 정수로의 캐스트와 같이 표준에 정의된 잘못된 캐스트 패턴에 대한 런타임 예외를 throw합니다.

Spark ANSI 모드의 CAST 절은 ISO/IEC 9075-2:2011 — 데이터베이스 언어 - SQL — 파트2: 기본 사항(SQL/기본 사항)의 섹션 6.13 “캐스트 사양” 구문 규칙을 따릅니다. 단, ANSI 표준에 따라 허용되지 않는 다음과 같은 간단한 형식 변환을 특별히 허용합니다.

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

다음 표는 CAST 식에서의 원본 및 대상 데이터 형식의 유효한 조합을 나타냅니다. “Y”는 조합이 제한 없이 구문상 유효함을, “N”은 조합이 유효하지 않음을 나타냅니다.

-- 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은 INSERT 문에 ANSI CAST 절을 자동으로 추가합니다. 이 정책에 따라 테이블 삽입 중에 Spark는 잘못된 캐스트를 확인하고 거부하여 데이터 품질을 보장하기 위해 예외를 throw합니다. 즉, 형식 불일치로 인해 삽입 시도가 실패하는 경우 테이블에 데이터가 부분적으로 기록되지 않습니다.

예:

-- 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 작업을 적용합니다. 이 정책에서 테이블 삽입 중에 잘못된 캐스트가 발생하면 예외를 throw하는 대신 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를 건너뜁니다.

** 복합 형식의 경우 우선 순위 규칙은 해당 구성 요소에 재귀적으로 적용됩니다.

특수 규칙은 문자열 형식 및 형식이 지정되지 않은 NULL에 적용됩니다. NULL은 다른 형식으로 승격될 수 있지만 문자열은 모든 단순 데이터 형식으로 승격될 수 있습니다.

다음은 우선 순위 목록을 지시된 트리로 그래픽으로 보여 주는 것입니다.

최소 일반 형식 확인

형식 집합에서 가장 덜 일반적인 형식은 형식 집합의 모든 요소가 우선 순위 목록에서 도달할 수 있는 가장 좁은 형식입니다.

가장 일반적인 형식 확인은 다음 작업을 수행하기 위해 사용됩니다.

• 형식의 매개 변수를 예상하는 함수가 더 좁은 형식의 인수를 사용하여 호출될 수 있는지 여부를 결정합니다.
• coalesce, least 또는 greatest와 같은 여러 매개 변수에 대한 공유 인수 형식을 예상하는 함수의 인수 형식을 파생합니다.
• 산술 연산 또는 비교와 같은 연산자에 대한 피연산자 형식을 파생합니다.
• 사례 식과 같은 식의 결과 형식을 파생합니다.
• 배열 및 맵 생성자에 대한 요소, 키 또는 값 형식을 파생합니다.

최소 일반 형식이 FLOAT로 확인되는 경우 특수 규칙이 적용됩니다. 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: 이 함수는 ANSI 모드에서 null 입력에 대해 null을 반환합니다.
• element_at:
  • 이 함수는 잘못된 인덱스를 사용하는 경우 ArrayIndexOutOfBoundsException을 throw합니다.
  • 이 함수는 키가 맵에 없는 경우 NoSuchElementException을 throw합니다.
• elt: 잘못된 인덱스를 사용하는 경우 ArrayIndexOutOfBoundsException을 throw합니다.
• make_date: 결과 날짜가 잘못된 경우 이 함수는 예외와 함께 실패합니다.
• make_timestamp: 결과 타임스탬프가 잘못된 경우 이 함수는 예외와 함께 실패합니다.
• make_interval: 결과 간격이 잘못된 경우 이 함수는 예외와 함께 실패합니다.
• next_day: 입력이 유효한 요일이 아니면 IllegalArgumentException을 throw합니다.
• parse_url: 입력 문자열이 유효한 url이 아니면 IllegalArgumentException을 throw합니다.
• to_date: 입력 문자열을 구문 분석할 수 없거나 패턴 문자열이 잘못된 경우 이 함수는 예외와 함께 실패합니다.
• to_timestamp: 입력 문자열을 구문 분석할 수 없거나 패턴 문자열이 잘못된 경우 이 함수는 예외와 함께 실패합니다.
• to_unix_timestamp: 입력 문자열을 구문 분석할 수 없거나 패턴 문자열이 잘못된 경우 이 함수는 예외와 함께 실패합니다.
• unix_timestamp: 입력 문자열을 구문 분석할 수 없거나 패턴 문자열이 잘못된 경우 이 함수는 예외와 함께 실패합니다.

SQL 연산자

일부 SQL 연산자의 동작은 ANSI 모드(spark.sql.ansi.enabled=true)에서 다를 수 있습니다.

• array_col[index]: 잘못된 인덱스를 사용하는 경우 이 연산자는 ArrayIndexOutOfBoundsException을 throw합니다.
• map_col[key]: 이 연산자는 키가 맵에 없는 경우 NoSuchElementException을 throw합니다.
• CAST(string_col AS TIMESTAMP): 입력 문자열을 구문 분석할 수 없는 경우 이 연산자는 예외와 함께 실패합니다.
• CAST(string_col AS DATE): 입력 문자열을 구문 분석할 수 없는 경우 이 연산자는 예외와 함께 실패합니다.

ANSI 모드에 유용한 함수

ANSI 모드가 켜지면 잘못된 연산자에 대한 예외가 throw됩니다. 다음 SQL 함수를 사용하여 이러한 예외를 표시하지 않을 수 있습니다.

• try_cast: 런타임 오류 시 예외를 throw하는 대신NULL을 반환한다는 점을 제외하고 CAST와 동일합니다.
• try_add: 정수 값 오버플로 시 예외를 throw하는 대신NULL을 반환한다는 점을 제외하고 더하기 연산자+와 동일합니다.
• try_divide: 0으로 나눌 때 예외를 throw하는 대신NULL을 반환한다는 점을 제외하고 나누기 연산자/와 동일합니다.

SQL 키워드

spark.sql.ansi.enabled가 true이면 Spark SQL는 ANSI 모드 파서를 사용합니다. 이 모드에서 Spark SQL에는 다음과 같은 두 종류의 키워드가 있습니다.

• 예약 키워드: 예약되어 있어 테이블, 보기, 열, 함수, 별칭 등에 대한 식별자로 사용할 수 없는 키워드입니다.
• 비 예약 키워드: 특정 컨텍스트에서만 특별한 의미를 가지며 다른 컨텍스트에서 식별자로 사용할 수 있는 키워드입니다. 예를 들어 EXPLAIN SELECT ...는 명령이지만 EXPLAIN은 다른 위치에서 식별자로 사용할 수 있습니다.

ANSI 모드를 사용하지 않도록 설정하면 Spark SQL에는 다음과 같은 두 종류의 키워드가 있습니다.

• 비 예약 키워드: ANSI 모드를 사용하도록 설정한 경우와 정의가 동일합니다.
• 엄격한 비 예약 키워드: 테이블 별칭으로 사용할 수 없는 비 예약 키워드의 엄격한 버전입니다.

기본적으로 spark.sql.ansi.enabled는 false입니다.

다음은 Spark SQL의 모든 키워드 목록입니다.