Spark SQL Keywords
When spark.sql.ansi.enabled is true, Spark SQL has two kinds of keywords:
- Reserved keywords: Keywords that are reserved and can’t be used as identifiers for table, view, column, function, alias, etc.
- Non-reserved keywords: Keywords that have a special meaning only in particular contexts and can be used as identifiers in other contexts. For example,
SELECT 1 WEEKis an interval literal, but WEEK can be used as identifiers in other places.
When spark.sql.ansi.enabled is false, Spark SQL has two kinds of keywords:
- Non-reserved keywords: Same definition as the one when
spark.sql.ansi.enabled=true. - Strict-non-reserved keywords: A strict version of non-reserved keywords, which can not be used as table alias.
By default spark.sql.ansi.enabled is false.
Below is a list of all the keywords in Spark SQL.
| Keyword | Spark SQL | SQL-2011 | |
|---|---|---|---|
| ANSI mode | default mode | ||
| 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 |
| ANALYZE | non-reserved | non-reserved | non-reserved |
| AND | reserved | non-reserved | reserved |
| ANTI | reserved | strict-non-reserved | non-reserved |
| ANY | reserved | non-reserved | 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 |
| 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 |
| CACHE | non-reserved | non-reserved | non-reserved |
| CASCADE | non-reserved | non-reserved | reserved |
| CASE | reserved | non-reserved | reserved |
| CAST | reserved | non-reserved | reserved |
| CHANGE | non-reserved | non-reserved | non-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 |
| DATABASE | non-reserved | non-reserved | non-reserved |
| DATABASES | non-reserved | non-reserved | non-reserved |
| DAY | reserved | non-reserved | reserved |
| DAYS | non-reserved | non-reserved | non-reserved |
| DBPROPERTIES | 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 | non-reserved |
| DROP | non-reserved | non-reserved | reserved |
| ELSE | reserved | non-reserved | reserved |
| END | reserved | non-reserved | reserved |
| ESCAPED | non-reserved | non-reserved | non-reserved |
| EXCEPT | reserved | strict-non-reserved | reserved |
| EXCHANGE | 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 |
| FILEFORMAT | non-reserved | non-reserved | non-reserved |
| FIRST | non-reserved | non-reserved | non-reserved |
| FIRST_VALUE | 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 |
| 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 | reserved | non-reserved | reserved |
| HOURS | non-reserved | non-reserved | non-reserved |
| IF | non-reserved | non-reserved | reserved |
| IGNORE | non-reserved | non-reserved | non-reserved |
| IMPORT | non-reserved | non-reserved | non-reserved |
| IN | reserved | non-reserved | 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 |
| 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 |
| LAST_VALUE | reserved | non-reserved | reserved |
| LATERAL | non-reserved | 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 |
| 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 |
| MACRO | non-reserved | non-reserved | non-reserved |
| MAP | 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 |
| MINUS | reserved | strict-non-reserved | non-reserved |
| MINUTE | reserved | non-reserved | reserved |
| MINUTES | non-reserved | non-reserved | non-reserved |
| MONTH | reserved | non-reserved | reserved |
| MONTHS | non-reserved | non-reserved | non-reserved |
| MSCK | non-reserved | non-reserved | non-reserved |
| NAMESPACE | non-reserved | non-reserved | non-reserved |
| NAMESPACES | 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 |
| OF | non-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 |
| 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 |
| QUERY | non-reserved | non-reserved | non-reserved |
| RANGE | 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 |
| RENAME | non-reserved | non-reserved | non-reserved |
| REPAIR | 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 |
| SECOND | reserved | non-reserved | reserved |
| SECONDS | non-reserved | non-reserved | non-reserved |
| SELECT | reserved | non-reserved | reserved |
| SEMI | 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 |
| SHOW | non-reserved | non-reserved | non-reserved |
| SKEWED | non-reserved | non-reserved | non-reserved |
| SOME | reserved | non-reserved | reserved |
| SORT | non-reserved | non-reserved | non-reserved |
| SORTED | 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 |
| STRUCT | non-reserved | non-reserved | non-reserved |
| SUBSTR | non-reserved | non-reserved | non-reserved |
| SUBSTRING | non-reserved | non-reserved | non-reserved |
| TABLE | reserved | non-reserved | reserved |
| TABLES | non-reserved | non-reserved | non-reserved |
| TABLESAMPLE | non-reserved | non-reserved | reserved |
| TBLPROPERTIES | non-reserved | non-reserved | non-reserved |
| TEMPORARY | non-reserved | non-reserved | non-reserved |
| TERMINATED | non-reserved | non-reserved | non-reserved |
| THEN | reserved | non-reserved | 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 |
| 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 |
| 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 |
| VIEW | 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 |
| YEAR | reserved | non-reserved | reserved |
| YEARS | non-reserved | non-reserved | non-reserved |