ANSI Compliance

In Spark SQL, there are two options to comply with the SQL standard: spark.sql.ansi.enabled and spark.sql.storeAssignmentPolicy (See a table below for details).

When spark.sql.ansi.enabled is set to true, Spark SQL uses an ANSI compliant dialect instead of being Hive compliant. For example, Spark will throw an exception at runtime instead of returning null results if the inputs to a SQL operator/function are invalid. Some ANSI dialect features may be not from the ANSI SQL standard directly, but their behaviors align with ANSI SQL’s style.

Moreover, Spark SQL has an independent option to control implicit casting behaviours when inserting rows in a table. The casting behaviours are defined as store assignment rules in the standard.

When spark.sql.storeAssignmentPolicy is set to ANSI, Spark SQL complies with the ANSI store assignment rules. This is a separate configuration because its default value is ANSI, while the configuration spark.sql.ansi.enabled is disabled by default.

Property Name Default Meaning Since Version
spark.sql.ansi.enabled false When true, Spark tries to conform to the ANSI SQL specification:
1. Spark SQL will throw runtime exceptions on invalid operations, including integer overflow errors, string parsing errors, etc.
2. Spark will use different type coercion rules for resolving conflicts among data types. The rules are consistently based on data type precedence.
3.0.0
spark.sql.storeAssignmentPolicy ANSI When inserting a value into a column with different data type, Spark will perform type conversion. Currently, we support 3 policies for the type coercion rules: ANSI, legacy and strict.
1. With ANSI policy, Spark performs the type coercion as per ANSI SQL. In practice, the behavior is mostly the same as PostgreSQL. It disallows certain unreasonable type conversions such as converting string to int or double to boolean. On inserting a numeric type column, an overflow error will be thrown if the value is out of the target data type’s range.
2. With legacy policy, Spark allows the type coercion as long as it is a valid Cast, which is very loose. e.g. converting string to int or double to boolean is allowed. It is also the only behavior in Spark 2.x and it is compatible with Hive.
3. With strict policy, Spark doesn’t allow any possible precision loss or data truncation in type coercion, e.g. converting double to int or decimal to double is not allowed.
3.0.0

The following subsections present behaviour changes in arithmetic operations, type conversions, and SQL parsing when the ANSI mode enabled. For type conversions in Spark SQL, there are three kinds of them and this article will introduce them one by one: cast, store assignment and type coercion.

Arithmetic Operations

In Spark SQL, arithmetic operations performed on numeric types (with the exception of decimal) are not checked for overflows by default. This means that in case an operation causes overflows, the result is the same with the corresponding operation in a Java/Scala program (e.g., if the sum of 2 integers is higher than the maximum value representable, the result is a negative number). On the other hand, Spark SQL returns null for decimal overflows. When spark.sql.ansi.enabled is set to true and an overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.

-- `spark.sql.ansi.enabled=true`
SELECT 2147483647 + 1;
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. Use 'try_add' to tolerate overflow and return NULL instead. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT 2147483647 + 1
       ^^^^^^^^^^^^^^

SELECT abs(-2147483648);
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.

-- `spark.sql.ansi.enabled=false`
SELECT 2147483647 + 1;
+----------------+
|(2147483647 + 1)|
+----------------+
|     -2147483648|
+----------------+

SELECT abs(-2147483648);
+----------------+
|abs(-2147483648)|
+----------------+
|     -2147483648|
+----------------+

Cast

When spark.sql.ansi.enabled is set to true, explicit casting by CAST syntax throws a runtime exception for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.

Besides, the ANSI SQL mode disallows the following type conversions which are allowed when ANSI mode is off:

The valid combinations of source and target data type in a CAST expression are given by the following table. “Y” indicates that the combination is syntactically valid without restriction and “N” indicates that the combination is not valid.

Source\Target Numeric String Date Timestamp Timestamp_NTZ Interval Boolean Binary Array Map Struct
Numeric Y Y N Y N Y Y N N N N
String Y Y Y Y Y Y Y Y N N N
Date N Y Y Y Y N N N N N N
Timestamp Y Y Y Y Y N N N N N N
Timestamp_NTZ N Y Y Y Y N N N N N N
Interval Y Y N N N Y N N N N N
Boolean Y Y N N N N Y N N N N
Binary N Y N N N N N Y N N N
Array N Y N N N N N N Y N N
Map N Y N N N N N N N Y N
Struct N Y N N N N N N N N Y

In the table above, all the CASTs with new syntax are marked as red Y:

-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true`
SELECT CAST('a' AS INT);
org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT CAST('a' AS INT)
       ^^^^^^^^^^^^^^^^

SELECT CAST(2147483648L AS INT);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.

SELECT CAST(DATE'2020-01-01' AS INT);
org.apache.spark.sql.AnalysisException: cannot resolve 'CAST(DATE '2020-01-01' AS INT)' due to data type mismatch: cannot cast date to int.
To convert values from date to int, you can use function UNIX_DATE instead.

-- `spark.sql.ansi.enabled=false` (This is a default behaviour)
SELECT CAST('a' AS INT);
+--------------+
|CAST(a AS INT)|
+--------------+
|          null|
+--------------+

SELECT CAST(2147483648L AS INT);
+-----------------------+
|CAST(2147483648 AS INT)|
+-----------------------+
|            -2147483648|
+-----------------------+

SELECT CAST(DATE'2020-01-01' AS INT)
+------------------------------+
|CAST(DATE '2020-01-01' AS INT)|
+------------------------------+
|                          null|
+------------------------------+

-- Examples of store assignment rules
CREATE TABLE t (v INT);

-- `spark.sql.storeAssignmentPolicy=ANSI`
INSERT INTO t VALUES ('1');
org.apache.spark.sql.AnalysisException: [INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST] Cannot write incompatible data for table `spark_catalog`.`default`.`t`: Cannot safely cast `v`: "STRING" to "INT".

-- `spark.sql.storeAssignmentPolicy=LEGACY` (This is a legacy behaviour until Spark 2.x)
INSERT INTO t VALUES ('1');
SELECT * FROM t;
+---+
|  v|
+---+
|  1|
+---+

Rounding in cast

While casting of a decimal with a fraction to an interval type with SECOND as the end-unit like INTERVAL HOUR TO SECOND, Spark rounds the fractional part towards “nearest neighbor” unless both neighbors are equidistant, in which case round up.

Store assignment

As mentioned at the beginning, when spark.sql.storeAssignmentPolicy is set to ANSI(which is the default value), Spark SQL complies with the ANSI store assignment rules on table insertions. The valid combinations of source and target data type in table insertions are given by the following table.

Source\Target Numeric String Date Timestamp Timestamp_NTZ Interval Boolean Binary Array Map Struct
Numeric Y Y N N N N N N N N N
String N Y N N N N N N N N N
Date N Y Y Y Y N N N N N N
Timestamp N Y Y Y Y N N N N N N
Timestamp_NTZ N Y Y Y Y N N N N N N
Interval N Y N N N N* N N N N N
Boolean N Y N N N N Y N N N N
Binary N Y N N N N N Y N N N
Array N N N N N N N N Y** N N
Map N N N N N N N N N Y** N
Struct N N N N N N N N N N Y**

* Spark doesn’t support interval type table column.

** For Array/Map/Struct types, the data type check rule applies recursively to its component elements.

During table insertion, Spark will throw exception on numeric value overflow.

CREATE TABLE test(i INT);

INSERT INTO test VALUES (2147483648L);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail to insert a value of "BIGINT" type into the "INT" type column `i` due to an overflow. Use `try_cast` on the input value to tolerate overflow and return NULL instead.

Type coercion

Type Promotion and Precedence

When spark.sql.ansi.enabled is set to true, Spark SQL uses several rules that govern how conflicts between data types are resolved. At the heart of this conflict resolution is the Type Precedence List which defines whether values of a given data type can be promoted to another data type implicitly.

Data type precedence list(from narrowest to widest)
Byte Byte -> Short -> Int -> Long -> Decimal -> Float* -> Double
Short Short -> Int -> Long -> Decimal-> Float* -> Double
Int Int -> Long -> Decimal -> Float* -> Double
Long Long -> Decimal -> Float* -> Double
Decimal Decimal -> Float* -> Double
Float Float -> Double
Double Double
Date Date -> Timestamp_NTZ -> Timestamp
Timestamp Timestamp
String String, Long -> Double, Date -> Timestamp_NTZ -> Timestamp , Boolean, Binary **
Binary Binary
Boolean Boolean
Interval Interval
Map Map***
Array Array***
Struct Struct***

* For least common type resolution float is skipped to avoid loss of precision.

** String can be promoted to multiple kinds of data types. Note that Byte/Short/Int/Decimal/Float is not on this precedent list. The least common type between Byte/Short/Int and String is Long, while the least common type between Decimal/Float is Double.

*** For a complex type, the precedence rule applies recursively to its component elements.

Special rules apply for untyped NULL. A NULL can be promoted to any other type.

This is a graphical depiction of the precedence list as a directed tree: Type Precedence List

Least Common Type Resolution

The least common type from a set of types is the narrowest type reachable from the precedence list by all elements of the set of types.

The least common type resolution is used to:

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

> SELECT typeof(coalesce(1, '2147483648'))
BIGINT
> SELECT typeof(coalesce(1.0, '2147483648'))
DOUBLE
> SELECT typeof(coalesce(DATE'2021-01-01', '2022-01-01'))
DATE

SQL Functions

Function invocation

Under ANSI mode(spark.sql.ansi.enabled=true), the function invocation of Spark SQL:

> SET spark.sql.ansi.enabled=true;
-- implicitly cast Int to String type
> SELECT concat('total number: ', 1);
total number: 1
-- implicitly cast Timestamp to Date type
> select datediff(now(), current_date);
0

-- implicitly cast String to Double type
> SELECT ceil('0.1');
1
-- special rule: implicitly cast NULL to Date type
> SELECT year(null);
NULL

> CREATE TABLE t(s string);
-- Can't store String column as Numeric types.
> SELECT ceil(s) from t;
Error in query: cannot resolve 'CEIL(spark_catalog.default.t.s)' due to data type mismatch
-- Can't store String column as Date type.
> select year(s) from t;
Error in query: cannot resolve 'year(spark_catalog.default.t.s)' due to data type mismatch

Functions with different behaviors

The behavior of some SQL functions can be different under ANSI mode (spark.sql.ansi.enabled=true).

SQL Operators

The behavior of some SQL operators can be different under ANSI mode (spark.sql.ansi.enabled=true).

Useful Functions for ANSI Mode

When ANSI mode is on, it throws exceptions for invalid operations. You can use the following SQL functions to suppress such exceptions.

SQL Keywords (optional, disabled by default)

When both spark.sql.ansi.enabled and spark.sql.ansi.enforceReservedKeywords are true, Spark SQL will use the ANSI mode parser.

With the ANSI mode parser, Spark SQL has two kinds of keywords:

With the default parser, Spark SQL has two kinds of keywords:

By default, both spark.sql.ansi.enabled and spark.sql.ansi.enforceReservedKeywords are false.

Below is a list of all the keywords in Spark SQL.

Keyword Spark SQL
ANSI Mode
Spark SQL
Default Mode
SQL-2016
ADD non-reserved non-reserved non-reserved
AFTER non-reserved non-reserved non-reserved
ALL reserved non-reserved reserved
ALTER non-reserved non-reserved reserved
ALWAYS non-reserved non-reserved non-reserved
ANALYZE non-reserved non-reserved non-reserved
AND reserved non-reserved reserved
ANTI non-reserved strict-non-reserved non-reserved
ANY reserved non-reserved reserved
ANY_VALUE non-reserved non-reserved non-reserved
ARCHIVE non-reserved non-reserved non-reserved
ARRAY non-reserved non-reserved reserved
AS reserved non-reserved reserved
ASC non-reserved non-reserved non-reserved
AT non-reserved non-reserved reserved
AUTHORIZATION reserved non-reserved reserved
BETWEEN non-reserved non-reserved reserved
BIGINT non-reserved non-reserved reserved
BINARY non-reserved non-reserved reserved
BOOLEAN non-reserved non-reserved reserved
BOTH reserved non-reserved reserved
BUCKET non-reserved non-reserved non-reserved
BUCKETS non-reserved non-reserved non-reserved
BY non-reserved non-reserved reserved
BYTE non-reserved non-reserved non-reserved
CACHE non-reserved non-reserved non-reserved
CASCADE non-reserved non-reserved non-reserved
CASE reserved non-reserved reserved
CAST reserved non-reserved reserved
CATALOG non-reserved non-reserved non-reserved
CATALOGS non-reserved non-reserved non-reserved
CHANGE non-reserved non-reserved non-reserved
CHAR non-reserved non-reserved reserved
CHARACTER non-reserved non-reserved reserved
CHECK reserved non-reserved reserved
CLEAR non-reserved non-reserved non-reserved
CLUSTER non-reserved non-reserved non-reserved
CLUSTERED non-reserved non-reserved non-reserved
CODEGEN non-reserved non-reserved non-reserved
COLLATE reserved non-reserved reserved
COLLECTION non-reserved non-reserved non-reserved
COLUMN reserved non-reserved reserved
COLUMNS non-reserved non-reserved non-reserved
COMMENT non-reserved non-reserved non-reserved
COMMIT non-reserved non-reserved reserved
COMPACT non-reserved non-reserved non-reserved
COMPACTIONS non-reserved non-reserved non-reserved
COMPUTE non-reserved non-reserved non-reserved
CONCATENATE non-reserved non-reserved non-reserved
CONSTRAINT reserved non-reserved reserved
COST non-reserved non-reserved non-reserved
CREATE reserved non-reserved reserved
CROSS reserved strict-non-reserved reserved
CUBE non-reserved non-reserved reserved
CURRENT non-reserved non-reserved reserved
CURRENT_DATE reserved non-reserved reserved
CURRENT_TIME reserved non-reserved reserved
CURRENT_TIMESTAMP reserved non-reserved reserved
CURRENT_USER reserved non-reserved reserved
DATA non-reserved non-reserved non-reserved
DATE non-reserved non-reserved reserved
DATABASE non-reserved non-reserved non-reserved
DATABASES non-reserved non-reserved non-reserved
DATEADD non-reserved non-reserved non-reserved
DATE_ADD non-reserved non-reserved non-reserved
DATEDIFF non-reserved non-reserved non-reserved
DATE_DIFF non-reserved non-reserved non-reserved
DAY non-reserved non-reserved non-reserved
DAYS non-reserved non-reserved non-reserved
DAYOFYEAR non-reserved non-reserved non-reserved
DBPROPERTIES non-reserved non-reserved non-reserved
DEC non-reserved non-reserved reserved
DECIMAL non-reserved non-reserved reserved
DEFAULT non-reserved non-reserved non-reserved
DEFINED non-reserved non-reserved non-reserved
DELETE non-reserved non-reserved reserved
DELIMITED non-reserved non-reserved non-reserved
DESC non-reserved non-reserved non-reserved
DESCRIBE non-reserved non-reserved reserved
DFS non-reserved non-reserved non-reserved
DIRECTORIES non-reserved non-reserved non-reserved
DIRECTORY non-reserved non-reserved non-reserved
DISTINCT reserved non-reserved reserved
DISTRIBUTE non-reserved non-reserved non-reserved
DIV non-reserved non-reserved not a keyword
DOUBLE non-reserved non-reserved reserved
DROP non-reserved non-reserved reserved
ELSE reserved non-reserved reserved
END reserved non-reserved reserved
ESCAPE reserved non-reserved reserved
ESCAPED non-reserved non-reserved non-reserved
EXCEPT reserved strict-non-reserved reserved
EXCHANGE non-reserved non-reserved non-reserved
EXCLUDE non-reserved non-reserved non-reserved
EXISTS non-reserved non-reserved reserved
EXPLAIN non-reserved non-reserved non-reserved
EXPORT non-reserved non-reserved non-reserved
EXTENDED non-reserved non-reserved non-reserved
EXTERNAL non-reserved non-reserved reserved
EXTRACT non-reserved non-reserved reserved
FALSE reserved non-reserved reserved
FETCH reserved non-reserved reserved
FIELDS non-reserved non-reserved non-reserved
FILTER reserved non-reserved reserved
FILEFORMAT non-reserved non-reserved non-reserved
FIRST non-reserved non-reserved non-reserved
FLOAT non-reserved non-reserved reserved
FOLLOWING non-reserved non-reserved non-reserved
FOR reserved non-reserved reserved
FOREIGN reserved non-reserved reserved
FORMAT non-reserved non-reserved non-reserved
FORMATTED non-reserved non-reserved non-reserved
FROM reserved non-reserved reserved
FULL reserved strict-non-reserved reserved
FUNCTION non-reserved non-reserved reserved
FUNCTIONS non-reserved non-reserved non-reserved
GENERATED non-reserved non-reserved non-reserved
GLOBAL non-reserved non-reserved reserved
GRANT reserved non-reserved reserved
GROUP reserved non-reserved reserved
GROUPING non-reserved non-reserved reserved
HAVING reserved non-reserved reserved
HOUR non-reserved non-reserved non-reserved
HOURS non-reserved non-reserved non-reserved
IDENTIFIER non-reserved non-reserved non-reserved
IF non-reserved non-reserved not a keyword
IGNORE non-reserved non-reserved non-reserved
IMPORT non-reserved non-reserved non-reserved
IN reserved non-reserved reserved
INCLUDE non-reserved non-reserved non-reserved
INDEX non-reserved non-reserved non-reserved
INDEXES non-reserved non-reserved non-reserved
INNER reserved strict-non-reserved reserved
INPATH non-reserved non-reserved non-reserved
INPUTFORMAT non-reserved non-reserved non-reserved
INSERT non-reserved non-reserved reserved
INT non-reserved non-reserved reserved
INTEGER non-reserved non-reserved reserved
INTERSECT reserved strict-non-reserved reserved
INTERVAL non-reserved non-reserved reserved
INTO reserved non-reserved reserved
IS reserved non-reserved reserved
ITEMS non-reserved non-reserved non-reserved
JOIN reserved strict-non-reserved reserved
KEYS non-reserved non-reserved non-reserved
LAST non-reserved non-reserved non-reserved
LATERAL reserved strict-non-reserved reserved
LAZY non-reserved non-reserved non-reserved
LEADING reserved non-reserved reserved
LEFT reserved strict-non-reserved reserved
LIKE non-reserved non-reserved reserved
ILIKE non-reserved non-reserved non-reserved
LIMIT non-reserved non-reserved non-reserved
LINES non-reserved non-reserved non-reserved
LIST non-reserved non-reserved non-reserved
LOAD non-reserved non-reserved non-reserved
LOCAL non-reserved non-reserved reserved
LOCATION non-reserved non-reserved non-reserved
LOCK non-reserved non-reserved non-reserved
LOCKS non-reserved non-reserved non-reserved
LOGICAL non-reserved non-reserved non-reserved
LONG non-reserved non-reserved non-reserved
MACRO non-reserved non-reserved non-reserved
MAP non-reserved non-reserved non-reserved
MATCHED non-reserved non-reserved non-reserved
MERGE non-reserved non-reserved non-reserved
MICROSECOND non-reserved non-reserved non-reserved
MICROSECONDS non-reserved non-reserved non-reserved
MILLISECOND non-reserved non-reserved non-reserved
MILLISECONDS non-reserved non-reserved non-reserved
MINUTE non-reserved non-reserved non-reserved
MINUTES non-reserved non-reserved non-reserved
MINUS non-reserved strict-non-reserved non-reserved
MONTH non-reserved non-reserved non-reserved
MONTHS non-reserved non-reserved non-reserved
MSCK non-reserved non-reserved non-reserved
NAME non-reserved non-reserved non-reserved
NAMESPACE non-reserved non-reserved non-reserved
NAMESPACES non-reserved non-reserved non-reserved
NANOSECOND non-reserved non-reserved non-reserved
NANOSECONDS non-reserved non-reserved non-reserved
NATURAL reserved strict-non-reserved reserved
NO non-reserved non-reserved reserved
NOT reserved non-reserved reserved
NULL reserved non-reserved reserved
NULLS non-reserved non-reserved non-reserved
NUMERIC non-reserved non-reserved non-reserved
OF non-reserved non-reserved reserved
OFFSET reserved non-reserved reserved
ON reserved strict-non-reserved reserved
ONLY reserved non-reserved reserved
OPTION non-reserved non-reserved non-reserved
OPTIONS non-reserved non-reserved non-reserved
OR reserved non-reserved reserved
ORDER reserved non-reserved reserved
OUT non-reserved non-reserved reserved
OUTER reserved non-reserved reserved
OUTPUTFORMAT non-reserved non-reserved non-reserved
OVER non-reserved non-reserved non-reserved
OVERLAPS reserved non-reserved reserved
OVERLAY non-reserved non-reserved non-reserved
OVERWRITE non-reserved non-reserved non-reserved
PARTITION non-reserved non-reserved reserved
PARTITIONED non-reserved non-reserved non-reserved
PARTITIONS non-reserved non-reserved non-reserved
PERCENT non-reserved non-reserved non-reserved
PERCENTILE_CONT reserved non-reserved non-reserved
PERCENTILE_DISC reserved non-reserved non-reserved
PIVOT non-reserved non-reserved non-reserved
PLACING non-reserved non-reserved non-reserved
POSITION non-reserved non-reserved reserved
PRECEDING non-reserved non-reserved non-reserved
PRIMARY reserved non-reserved reserved
PRINCIPALS non-reserved non-reserved non-reserved
PROPERTIES non-reserved non-reserved non-reserved
PURGE non-reserved non-reserved non-reserved
QUARTER non-reserved non-reserved non-reserved
QUERY non-reserved non-reserved non-reserved
RANGE non-reserved non-reserved reserved
REAL non-reserved non-reserved reserved
RECORDREADER non-reserved non-reserved non-reserved
RECORDWRITER non-reserved non-reserved non-reserved
RECOVER non-reserved non-reserved non-reserved
REDUCE non-reserved non-reserved non-reserved
REFERENCES reserved non-reserved reserved
REFRESH non-reserved non-reserved non-reserved
REGEXP non-reserved non-reserved not a keyword
RENAME non-reserved non-reserved non-reserved
REPAIR non-reserved non-reserved non-reserved
REPEATABLE non-reserved non-reserved non-reserved
REPLACE non-reserved non-reserved non-reserved
RESET non-reserved non-reserved non-reserved
RESPECT non-reserved non-reserved non-reserved
RESTRICT non-reserved non-reserved non-reserved
REVOKE non-reserved non-reserved reserved
RIGHT reserved strict-non-reserved reserved
RLIKE non-reserved non-reserved non-reserved
ROLE non-reserved non-reserved non-reserved
ROLES non-reserved non-reserved non-reserved
ROLLBACK non-reserved non-reserved reserved
ROLLUP non-reserved non-reserved reserved
ROW non-reserved non-reserved reserved
ROWS non-reserved non-reserved reserved
SCHEMA non-reserved non-reserved non-reserved
SCHEMAS non-reserved non-reserved non-reserved
SECOND non-reserved non-reserved non-reserved
SECONDS non-reserved non-reserved non-reserved
SELECT reserved non-reserved reserved
SEMI non-reserved strict-non-reserved non-reserved
SEPARATED non-reserved non-reserved non-reserved
SERDE non-reserved non-reserved non-reserved
SERDEPROPERTIES non-reserved non-reserved non-reserved
SESSION_USER reserved non-reserved reserved
SET non-reserved non-reserved reserved
SETS non-reserved non-reserved non-reserved
SHORT non-reserved non-reserved non-reserved
SHOW non-reserved non-reserved non-reserved
SKEWED non-reserved non-reserved non-reserved
SMALLINT non-reserved non-reserved reserved
SOME reserved non-reserved reserved
SORT non-reserved non-reserved non-reserved
SORTED non-reserved non-reserved non-reserved
SOURCE non-reserved non-reserved non-reserved
START non-reserved non-reserved reserved
STATISTICS non-reserved non-reserved non-reserved
STORED non-reserved non-reserved non-reserved
STRATIFY non-reserved non-reserved non-reserved
STRING non-reserved non-reserved non-reserved
STRUCT non-reserved non-reserved non-reserved
SUBSTR non-reserved non-reserved non-reserved
SUBSTRING non-reserved non-reserved non-reserved
SYNC non-reserved non-reserved non-reserved
SYSTEM_TIME non-reserved non-reserved non-reserved
SYSTEM_VERSION non-reserved non-reserved non-reserved
TABLE reserved non-reserved reserved
TABLES non-reserved non-reserved non-reserved
TABLESAMPLE non-reserved non-reserved reserved
TARGET non-reserved non-reserved non-reserved
TBLPROPERTIES non-reserved non-reserved non-reserved
TEMP non-reserved non-reserved not a keyword
TEMPORARY non-reserved non-reserved non-reserved
TERMINATED non-reserved non-reserved non-reserved
THEN reserved non-reserved reserved
TIME reserved non-reserved reserved
TIMESTAMP non-reserved non-reserved non-reserved
TIMESTAMP_LTZ non-reserved non-reserved non-reserved
TIMESTAMP_NTZ non-reserved non-reserved non-reserved
TIMESTAMPADD non-reserved non-reserved non-reserved
TIMESTAMPDIFF non-reserved non-reserved non-reserved
TINYINT non-reserved non-reserved non-reserved
TO reserved non-reserved reserved
TOUCH non-reserved non-reserved non-reserved
TRAILING reserved non-reserved reserved
TRANSACTION non-reserved non-reserved non-reserved
TRANSACTIONS non-reserved non-reserved non-reserved
TRANSFORM non-reserved non-reserved non-reserved
TRIM non-reserved non-reserved non-reserved
TRUE non-reserved non-reserved reserved
TRUNCATE non-reserved non-reserved reserved
TRY_CAST non-reserved non-reserved non-reserved
TYPE non-reserved non-reserved non-reserved
UNARCHIVE non-reserved non-reserved non-reserved
UNBOUNDED non-reserved non-reserved non-reserved
UNCACHE non-reserved non-reserved non-reserved
UNION reserved strict-non-reserved reserved
UNIQUE reserved non-reserved reserved
UNKNOWN reserved non-reserved reserved
UNLOCK non-reserved non-reserved non-reserved
UNPIVOT non-reserved non-reserved non-reserved
UNSET non-reserved non-reserved non-reserved
UPDATE non-reserved non-reserved reserved
USE non-reserved non-reserved non-reserved
USER reserved non-reserved reserved
USING reserved strict-non-reserved reserved
VALUES non-reserved non-reserved reserved
VARCHAR non-reserved non-reserved reserved
VERSION non-reserved non-reserved non-reserved
VIEW non-reserved non-reserved non-reserved
VIEWS non-reserved non-reserved non-reserved
VOID non-reserved non-reserved non-reserved
WEEK non-reserved non-reserved non-reserved
WEEKS non-reserved non-reserved non-reserved
WHEN reserved non-reserved reserved
WHERE reserved non-reserved reserved
WINDOW non-reserved non-reserved reserved
WITH reserved non-reserved reserved
WITHIN reserved non-reserved reserved
X non-reserved non-reserved non-reserved
YEAR non-reserved non-reserved non-reserved
YEARS non-reserved non-reserved non-reserved
ZONE non-reserved non-reserved non-reserved