Google Git
Sign in
apache / spark-website / 2c562896505588421cce257d111c8ba3805528c2 / . / site / docs / 3.2.4 / sql-ref-ansi-compliance.html
blob: 8a6e72db867f94de357418d7457bfbb6452b192e [file] [log] [blame]
<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>ANSI Compliance - Spark 3.2.4 Documentation</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<style>
body {
padding-top: 60px;
padding-bottom: 40px;
}
</style>
<meta name="viewport" content="width=device-width">
<link rel="stylesheet" href="css/main.css">
<script src="js/vendor/modernizr-2.6.1-respond-1.1.0.min.js"></script>
<link rel="stylesheet" href="css/pygments-default.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/docsearch.js@2/dist/cdn/docsearch.min.css" />
<link rel="stylesheet" href="css/docsearch.css">
<!-- Matomo -->
<script type="text/javascript">
var _paq = window._paq = window._paq || [];
/* tracker methods like "setCustomDimension" should be called before "trackPageView" */
_paq.push(["disableCookies"]);
_paq.push(['trackPageView']);
_paq.push(['enableLinkTracking']);
(function() {
var u="https://analytics.apache.org/";
_paq.push(['setTrackerUrl', u+'matomo.php']);
_paq.push(['setSiteId', '40']);
var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];
g.async=true; g.src=u+'matomo.js'; s.parentNode.insertBefore(g,s);
})();
</script>
<!-- End Matomo Code -->
</head>
<body>
<!--[if lt IE 7]>
<p class="chromeframe">You are using an outdated browser. <a href="https://browsehappy.com/">Upgrade your browser today</a> or <a href="http://www.google.com/chromeframe/?redirect=true">install Google Chrome Frame</a> to better experience this site.</p>
<![endif]-->
<!-- This code is taken from http://twitter.github.com/bootstrap/examples/hero.html -->
<nav class="navbar fixed-top navbar-expand-md navbar-light bg-light" id="topbar">
<div class="container">
<div class="navbar-header">
<div class="navbar-brand"><a href="index.html">
<img src="img/spark-logo-hd.png" style="height:50px;"/></a><span class="version">3.2.4</span>
</div>
</div>
<button class="navbar-toggler" type="button" data-toggle="collapse"
data-target="#navbarCollapse" aria-controls="navbarCollapse"
aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarCollapse">
<ul class="navbar-nav">
<!--TODO(andyk): Add class="active" attribute to li some how.-->
<li class="nav-item"><a href="index.html" class="nav-link">Overview</a></li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarQuickStart" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Programming Guides</a>
<div class="dropdown-menu" aria-labelledby="navbarQuickStart">
<a class="dropdown-item" href="quick-start.html">Quick Start</a>
<a class="dropdown-item" href="rdd-programming-guide.html">RDDs, Accumulators, Broadcasts Vars</a>
<a class="dropdown-item" href="sql-programming-guide.html">SQL, DataFrames, and Datasets</a>
<a class="dropdown-item" href="structured-streaming-programming-guide.html">Structured Streaming</a>
<a class="dropdown-item" href="streaming-programming-guide.html">Spark Streaming (DStreams)</a>
<a class="dropdown-item" href="ml-guide.html">MLlib (Machine Learning)</a>
<a class="dropdown-item" href="graphx-programming-guide.html">GraphX (Graph Processing)</a>
<a class="dropdown-item" href="sparkr.html">SparkR (R on Spark)</a>
<a class="dropdown-item" href="api/python/getting_started/index.html">PySpark (Python on Spark)</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarAPIDocs" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">API Docs</a>
<div class="dropdown-menu" aria-labelledby="navbarAPIDocs">
<a class="dropdown-item" href="api/scala/org/apache/spark/index.html">Scala</a>
<a class="dropdown-item" href="api/java/index.html">Java</a>
<a class="dropdown-item" href="api/python/index.html">Python</a>
<a class="dropdown-item" href="api/R/index.html">R</a>
<a class="dropdown-item" href="api/sql/index.html">SQL, Built-in Functions</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarDeploying" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Deploying</a>
<div class="dropdown-menu" aria-labelledby="navbarDeploying">
<a class="dropdown-item" href="cluster-overview.html">Overview</a>
<a class="dropdown-item" href="submitting-applications.html">Submitting Applications</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="spark-standalone.html">Spark Standalone</a>
<a class="dropdown-item" href="running-on-mesos.html">Mesos</a>
<a class="dropdown-item" href="running-on-yarn.html">YARN</a>
<a class="dropdown-item" href="running-on-kubernetes.html">Kubernetes</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarMore" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">More</a>
<div class="dropdown-menu" aria-labelledby="navbarMore">
<a class="dropdown-item" href="configuration.html">Configuration</a>
<a class="dropdown-item" href="monitoring.html">Monitoring</a>
<a class="dropdown-item" href="tuning.html">Tuning Guide</a>
<a class="dropdown-item" href="job-scheduling.html">Job Scheduling</a>
<a class="dropdown-item" href="security.html">Security</a>
<a class="dropdown-item" href="hardware-provisioning.html">Hardware Provisioning</a>
<a class="dropdown-item" href="migration-guide.html">Migration Guide</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="building-spark.html">Building Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/contributing.html">Contributing to Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/third-party-projects.html">Third Party Projects</a>
</div>
</li>
<li class="nav-item">
<input type="text" id="docsearch-input" placeholder="Search the docs…">
</li>
</ul>
<!--<span class="navbar-text navbar-right"><span class="version-text">v3.2.4</span></span>-->
</div>
</div>
</nav>
<div class="container-wrapper">
<div class="left-menu-wrapper">
<div class="left-menu">
<h3><a href="sql-programming-guide.html">Spark SQL Guide</a></h3>
<ul>
<li>
<a href="sql-getting-started.html">
Getting Started
</a>
</li>
<li>
<a href="sql-data-sources.html">
Data Sources
</a>
</li>
<li>
<a href="sql-performance-tuning.html">
Performance Tuning
</a>
</li>
<li>
<a href="sql-distributed-sql-engine.html">
Distributed SQL Engine
</a>
</li>
<li>
<a href="sql-pyspark-pandas-with-arrow.html">
PySpark Usage Guide for Pandas with Apache Arrow
</a>
</li>
<li>
<a href="sql-migration-old.html">
Migration Guide
</a>
</li>
<li>
<a href="sql-ref.html">
SQL Reference
</a>
</li>
<ul>
<li>
<a href="sql-ref-ansi-compliance.html">
<b>ANSI Compliance</b>
</a>
</li>
<li>
<a href="sql-ref-datatypes.html">
Data Types
</a>
</li>
<li>
<a href="sql-ref-datetime-pattern.html">
Datetime Pattern
</a>
</li>
<li>
<a href="sql-ref-functions.html">
Functions
</a>
</li>
<li>
<a href="sql-ref-identifier.html">
Identifiers
</a>
</li>
<li>
<a href="sql-ref-literals.html">
Literals
</a>
</li>
<li>
<a href="sql-ref-null-semantics.html">
Null Semantics
</a>
</li>
<li>
<a href="sql-ref-syntax.html">
SQL Syntax
</a>
</li>
</ul>
</ul>
</div>
</div>
<input id="nav-trigger" class="nav-trigger" checked type="checkbox">
<label for="nav-trigger"></label>
<div class="content-with-sidebar mr-3" id="content">
<h1 class="title">ANSI Compliance</h1>
<p>In Spark SQL, there are two options to comply with the SQL standard: <code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code> and <code class="language-plaintext highlighter-rouge">spark.sql.storeAssignmentPolicy</code> (See a table below for details).</p>
<p>When <code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code> is set to <code class="language-plaintext highlighter-rouge">true</code>, 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&#8217;s style.</p>
<p>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.</p>
<p>When <code class="language-plaintext highlighter-rouge">spark.sql.storeAssignmentPolicy</code> is set to <code class="language-plaintext highlighter-rouge">ANSI</code>, Spark SQL complies with the ANSI store assignment rules. This is a separate configuration because its default value is <code class="language-plaintext highlighter-rouge">ANSI</code>, while the configuration <code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code> is disabled by default.</p>
<table>
<thead>
<tr>
<th>Property Name</th>
<th>Default</th>
<th>Meaning</th>
<th>Since Version</th>
</tr>
</thead>
<tbody>
<tr>
<td><code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code></td>
<td>false</td>
<td>When true, Spark tries to conform to the ANSI SQL specification: <br /> 1. Spark SQL will throw runtime exceptions on invalid operations, including integer overflow errors, string parsing errors, etc. <br /> 2. Spark will forbid using the reserved keywords of ANSI SQL as identifiers in the SQL parser. <br />3. Spark will use different type coercion rules for resolving conflicts among data types. The rules are consistently based on data type precedence.</td>
<td>3.0.0</td>
</tr>
<tr>
<td><code class="language-plaintext highlighter-rouge">spark.sql.storeAssignmentPolicy</code></td>
<td>ANSI</td>
<td>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.<br /> 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&#8217;s range.<br />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.<br />3. With strict policy, Spark doesn&#8217;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.</td>
<td>3.0.0</td>
</tr>
</tbody>
</table>
<p>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.</p>
<h3 id="arithmetic-operations">Arithmetic Operations</h3>
<p>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 <code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code> is set to <code class="language-plaintext highlighter-rouge">true</code> and an overflow occurs in numeric and interval arithmetic operations, it throws an arithmetic exception at runtime.</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- `spark.sql.ansi.enabled=true`</span>
<span class="k">SELECT</span> <span class="mi">2147483647</span> <span class="o">+</span> <span class="mi">1</span><span class="p">;</span>
<span class="n">java</span><span class="p">.</span><span class="n">lang</span><span class="p">.</span><span class="n">ArithmeticException</span><span class="p">:</span> <span class="nb">integer</span> <span class="n">overflow</span>
<span class="k">SELECT</span> <span class="k">abs</span><span class="p">(</span><span class="o">-</span><span class="mi">2147483648</span><span class="p">);</span>
<span class="n">java</span><span class="p">.</span><span class="n">lang</span><span class="p">.</span><span class="n">ArithmeticException</span><span class="p">:</span> <span class="nb">integer</span> <span class="n">overflow</span>
<span class="c1">-- `spark.sql.ansi.enabled=false`</span>
<span class="k">SELECT</span> <span class="mi">2147483647</span> <span class="o">+</span> <span class="mi">1</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="o">|</span><span class="p">(</span><span class="mi">2147483647</span> <span class="o">+</span> <span class="mi">1</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="o">|</span> <span class="o">-</span><span class="mi">2147483648</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="k">SELECT</span> <span class="k">abs</span><span class="p">(</span><span class="o">-</span><span class="mi">2147483648</span><span class="p">);</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="o">|</span><span class="k">abs</span><span class="p">(</span><span class="o">-</span><span class="mi">2147483648</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="o">|</span> <span class="o">-</span><span class="mi">2147483648</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------+</span>
</code></pre></div></div>
<h3 id="cast">Cast</h3>
<p>When <code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code> is set to <code class="language-plaintext highlighter-rouge">true</code>, explicit casting by <code class="language-plaintext highlighter-rouge">CAST</code> syntax throws a runtime exception for illegal cast patterns defined in the standard, e.g. casts from a string to an integer.</p>
<p>The <code class="language-plaintext highlighter-rouge">CAST</code> clause of Spark ANSI mode follows the syntax rules of section 6.13 &#8220;cast specification&#8221; in <a href="https://www.iso.org/standard/53682.html">ISO/IEC 9075-2:2011 Information technology — Database languages - SQL — Part 2: Foundation (SQL/Foundation)</a>, except it specially allows the following
straightforward type conversions which are disallowed as per the ANSI standard:</p>
<ul>
<li>NumericType &lt;=&gt; BooleanType</li>
<li>StringType &lt;=&gt; BinaryType</li>
<li>ArrayType =&gt; String</li>
<li>MapType =&gt; String</li>
<li>StructType =&gt; String</li>
</ul>
<p>The valid combinations of source and target data type in a <code class="language-plaintext highlighter-rouge">CAST</code> 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.</p>
<table>
<thead>
<tr>
<th>Source\Target</th>
<th>Numeric</th>
<th>String</th>
<th>Date</th>
<th>Timestamp</th>
<th>Interval</th>
<th>Boolean</th>
<th>Binary</th>
<th>Array</th>
<th>Map</th>
<th>Struct</th>
</tr>
</thead>
<tbody>
<tr>
<td>Numeric</td>
<td><span style="color:red"><strong>Y</strong></span></td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>String</td>
<td><span style="color:red"><strong>Y</strong></span></td>
<td>Y</td>
<td><span style="color:red"><strong>Y</strong></span></td>
<td><span style="color:red"><strong>Y</strong></span></td>
<td><span style="color:red"><strong>Y</strong></span></td>
<td><span style="color:red"><strong>Y</strong></span></td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>Date</td>
<td>N</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>Timestamp</td>
<td>N</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>Interval</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>Boolean</td>
<td>Y</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>Binary</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>Array</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td><span style="color:red"><strong>Y</strong></span></td>
<td>N</td>
<td>N</td>
</tr>
<tr>
<td>Map</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td><span style="color:red"><strong>Y</strong></span></td>
<td>N</td>
</tr>
<tr>
<td>Struct</td>
<td>N</td>
<td>Y</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td>N</td>
<td><span style="color:red"><strong>Y</strong></span></td>
</tr>
</tbody>
</table>
<p>In the table above, all the <code class="language-plaintext highlighter-rouge">CAST</code>s that can cause runtime exceptions are marked as red <span style="color:red"><strong>Y</strong></span>:</p>
<ul>
<li>CAST(Numeric AS Numeric): raise an overflow exception if the value is out of the target data type&#8217;s range.</li>
<li>CAST(String AS (Numeric/Date/Timestamp/Interval/Boolean)): raise a runtime exception if the value can&#8217;t be parsed as the target data type.</li>
<li>CAST(Array AS Array): raise an exception if there is any on the conversion of the elements.</li>
<li>CAST(Map AS Map): raise an exception if there is any on the conversion of the keys and the values.</li>
<li>CAST(Struct AS Struct): raise an exception if there is any on the conversion of the struct fields.</li>
</ul>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Examples of explicit casting</span>
<span class="c1">-- `spark.sql.ansi.enabled=true`</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'a'</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">);</span>
<span class="n">java</span><span class="p">.</span><span class="n">lang</span><span class="p">.</span><span class="n">NumberFormatException</span><span class="p">:</span> <span class="n">invalid</span> <span class="k">input</span> <span class="n">syntax</span> <span class="k">for</span> <span class="k">type</span> <span class="nb">numeric</span><span class="p">:</span> <span class="n">a</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="mi">2147483648</span><span class="n">L</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">);</span>
<span class="n">java</span><span class="p">.</span><span class="n">lang</span><span class="p">.</span><span class="n">ArithmeticException</span><span class="p">:</span> <span class="n">Casting</span> <span class="mi">2147483648</span> <span class="k">to</span> <span class="nb">int</span> <span class="n">causes</span> <span class="n">overflow</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="nb">DATE</span><span class="s1">'2020-01-01'</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">)</span>
<span class="n">org</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">spark</span><span class="p">.</span><span class="k">sql</span><span class="p">.</span><span class="n">AnalysisException</span><span class="p">:</span> <span class="n">cannot</span> <span class="n">resolve</span> <span class="s1">'CAST(DATE '</span><span class="mi">2020</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span><span class="s1">' AS INT)'</span> <span class="n">due</span> <span class="k">to</span> <span class="k">data</span> <span class="k">type</span> <span class="n">mismatch</span><span class="p">:</span> <span class="n">cannot</span> <span class="k">cast</span> <span class="nb">date</span> <span class="k">to</span> <span class="nb">int</span><span class="p">.</span>
<span class="k">To</span> <span class="k">convert</span> <span class="k">values</span> <span class="k">from</span> <span class="nb">date</span> <span class="k">to</span> <span class="nb">int</span><span class="p">,</span> <span class="n">you</span> <span class="n">can</span> <span class="n">use</span> <span class="k">function</span> <span class="n">UNIX_DATE</span> <span class="k">instead</span><span class="p">.</span>
<span class="c1">-- `spark.sql.ansi.enabled=false` (This is a default behaviour)</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'a'</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">);</span>
<span class="o">+</span><span class="c1">--------------+</span>
<span class="o">|</span><span class="k">CAST</span><span class="p">(</span><span class="n">a</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------+</span>
<span class="o">|</span> <span class="k">null</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------+</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="mi">2147483648</span><span class="n">L</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span><span class="k">CAST</span><span class="p">(</span><span class="mi">2147483648</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span> <span class="o">-</span><span class="mi">2147483648</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="nb">DATE</span><span class="s1">'2020-01-01'</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">)</span>
<span class="o">+</span><span class="c1">------------------------------+</span>
<span class="o">|</span><span class="k">CAST</span><span class="p">(</span><span class="nb">DATE</span> <span class="s1">'2020-01-01'</span> <span class="k">AS</span> <span class="nb">INT</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------------------------+</span>
<span class="o">|</span> <span class="k">null</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------------------------+</span>
<span class="c1">-- Examples of store assignment rules</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">t</span> <span class="p">(</span><span class="n">v</span> <span class="nb">INT</span><span class="p">);</span>
<span class="c1">-- `spark.sql.storeAssignmentPolicy=ANSI`</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="k">VALUES</span> <span class="p">(</span><span class="s1">'1'</span><span class="p">);</span>
<span class="n">org</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">spark</span><span class="p">.</span><span class="k">sql</span><span class="p">.</span><span class="n">AnalysisException</span><span class="p">:</span> <span class="n">Cannot</span> <span class="k">write</span> <span class="n">incompatible</span> <span class="k">data</span> <span class="k">to</span> <span class="k">table</span> <span class="s1">'`default`.`t`'</span><span class="p">:</span>
<span class="o">-</span> <span class="n">Cannot</span> <span class="n">safely</span> <span class="k">cast</span> <span class="s1">'v'</span><span class="p">:</span> <span class="n">string</span> <span class="k">to</span> <span class="nb">int</span><span class="p">;</span>
<span class="c1">-- `spark.sql.storeAssignmentPolicy=LEGACY` (This is a legacy behaviour until Spark 2.x)</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="k">VALUES</span> <span class="p">(</span><span class="s1">'1'</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span> <span class="n">v</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span> <span class="mi">1</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
</code></pre></div></div>
<h3 id="store-assignment">Store assignment</h3>
<p>As mentioned at the beginning, when <code class="language-plaintext highlighter-rouge">spark.sql.storeAssignmentPolicy</code> is set to <code class="language-plaintext highlighter-rouge">ANSI</code>(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.</p>
<table>
<thead>
<tr>
<th style="text-align: center">Source\Target</th>
<th style="text-align: center">Numeric</th>
<th style="text-align: center">String</th>
<th style="text-align: center">Date</th>
<th style="text-align: center">Timestamp</th>
<th style="text-align: center">Interval</th>
<th style="text-align: center">Boolean</th>
<th style="text-align: center">Binary</th>
<th style="text-align: center">Array</th>
<th style="text-align: center">Map</th>
<th style="text-align: center">Struct</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: center">Numeric</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">String</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">Date</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">Timestamp</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">Interval</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N*</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">Boolean</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">Binary</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">Array</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y**</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">Map</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y**</td>
<td style="text-align: center">N</td>
</tr>
<tr>
<td style="text-align: center">Struct</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">N</td>
<td style="text-align: center">Y**</td>
</tr>
</tbody>
</table>
<p>* Spark doesn&#8217;t support interval type table column.</p>
<p>** For Array/Map/Struct types, the data type check rule applies recursively to its component elements.</p>
<p>During table insertion, Spark will throw exception on numeric value overflow.</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">test</span><span class="p">(</span><span class="n">i</span> <span class="nb">INT</span><span class="p">);</span>
<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">test</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mi">2147483648</span><span class="n">L</span><span class="p">);</span>
<span class="n">java</span><span class="p">.</span><span class="n">lang</span><span class="p">.</span><span class="n">ArithmeticException</span><span class="p">:</span> <span class="n">Casting</span> <span class="mi">2147483648</span> <span class="k">to</span> <span class="nb">int</span> <span class="n">causes</span> <span class="n">overflow</span>
</code></pre></div></div>
<h3 id="type-coercion">Type coercion</h3>
<h4 id="type-promotion-and-precedence">Type Promotion and Precedence</h4>
<p>When <code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code> is set to <code class="language-plaintext highlighter-rouge">true</code>, 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.</p>
<table>
<thead>
<tr>
<th>Data type</th>
<th>precedence list(from narrowest to widest)</th>
</tr>
</thead>
<tbody>
<tr>
<td>Byte</td>
<td>Byte -&gt; Short -&gt; Int -&gt; Long -&gt; Decimal -&gt; Float* -&gt; Double</td>
</tr>
<tr>
<td>Short</td>
<td>Short -&gt; Int -&gt; Long -&gt; Decimal-&gt; Float* -&gt; Double</td>
</tr>
<tr>
<td>Int</td>
<td>Int -&gt; Long -&gt; Decimal -&gt; Float* -&gt; Double</td>
</tr>
<tr>
<td>Long</td>
<td>Long -&gt; Decimal -&gt; Float* -&gt; Double</td>
</tr>
<tr>
<td>Decimal</td>
<td>Decimal -&gt; Float* -&gt; Double</td>
</tr>
<tr>
<td>Float</td>
<td>Float -&gt; Double</td>
</tr>
<tr>
<td>Double</td>
<td>Double</td>
</tr>
<tr>
<td>Date</td>
<td>Date -&gt; Timestamp</td>
</tr>
<tr>
<td>Timestamp</td>
<td>Timestamp</td>
</tr>
<tr>
<td>String</td>
<td>String</td>
</tr>
<tr>
<td>Binary</td>
<td>Binary</td>
</tr>
<tr>
<td>Boolean</td>
<td>Boolean</td>
</tr>
<tr>
<td>Interval</td>
<td>Interval</td>
</tr>
<tr>
<td>Map</td>
<td>Map**</td>
</tr>
<tr>
<td>Array</td>
<td>Array**</td>
</tr>
<tr>
<td>Struct</td>
<td>Struct**</td>
</tr>
</tbody>
</table>
<p>* For least common type resolution float is skipped to avoid loss of precision.</p>
<p>** For a complex type, the precedence rule applies recursively to its component elements.</p>
<p>Special rules apply for string literals and untyped NULL.
A NULL can be promoted to any other type, while a string literal can be promoted to any simple data type.</p>
<p>This is a graphical depiction of the precedence list as a directed tree:
<img src="img/type-precedence-list.png" width="80%" title="Type Precedence List" alt="Type Precedence List" /></p>
<h4 id="least-common-type-resolution">Least Common Type Resolution</h4>
<p>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.</p>
<p>The least common type resolution is used to:</p>
<ul>
<li>Decide whether a function expecting a parameter of a type can be invoked using an argument of a narrower type.</li>
<li>Derive the argument type for functions which expect a shared argument type for multiple parameters, such as coalesce, least, or greatest.</li>
<li>Derive the operand types for operators such as arithmetic operations or comparisons.</li>
<li>Derive the result type for expressions such as the case expression.</li>
<li>Derive the element, key, or value types for array and map constructors.
Special rules are applied if the least common type resolves to FLOAT. With float type values, if any of the types is INT, BIGINT, or DECIMAL the least common type is pushed to DOUBLE to avoid potential loss of digits.</li>
</ul>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- The coalesce function accepts any set of argument types as long as they share a least common type. </span>
<span class="c1">-- The result type is the least common type of the arguments.</span>
<span class="o">&gt;</span> <span class="k">SET</span> <span class="n">spark</span><span class="p">.</span><span class="k">sql</span><span class="p">.</span><span class="n">ansi</span><span class="p">.</span><span class="n">enabled</span><span class="o">=</span><span class="k">true</span><span class="p">;</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="n">typeof</span><span class="p">(</span><span class="n">coalesce</span><span class="p">(</span><span class="mi">1</span><span class="n">Y</span><span class="p">,</span> <span class="mi">1</span><span class="n">L</span><span class="p">,</span> <span class="k">NULL</span><span class="p">));</span>
<span class="nb">BIGINT</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="n">typeof</span><span class="p">(</span><span class="n">coalesce</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="nb">DATE</span><span class="s1">'2020-01-01'</span><span class="p">));</span>
<span class="n">Error</span><span class="p">:</span> <span class="n">Incompatible</span> <span class="n">types</span> <span class="p">[</span><span class="nb">INT</span><span class="p">,</span> <span class="nb">DATE</span><span class="p">]</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="n">typeof</span><span class="p">(</span><span class="n">coalesce</span><span class="p">(</span><span class="n">ARRAY</span><span class="p">(</span><span class="mi">1</span><span class="n">Y</span><span class="p">),</span> <span class="n">ARRAY</span><span class="p">(</span><span class="mi">1</span><span class="n">L</span><span class="p">)));</span>
<span class="n">ARRAY</span><span class="o">&lt;</span><span class="nb">BIGINT</span><span class="o">&gt;</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="n">typeof</span><span class="p">(</span><span class="n">coalesce</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="n">F</span><span class="p">));</span>
<span class="nb">DOUBLE</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="n">typeof</span><span class="p">(</span><span class="n">coalesce</span><span class="p">(</span><span class="mi">1</span><span class="n">L</span><span class="p">,</span> <span class="mi">1</span><span class="n">F</span><span class="p">));</span>
<span class="nb">DOUBLE</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="p">(</span><span class="n">typeof</span><span class="p">(</span><span class="n">coalesce</span><span class="p">(</span><span class="mi">1</span><span class="n">BD</span><span class="p">,</span> <span class="mi">1</span><span class="n">F</span><span class="p">)));</span>
<span class="nb">DOUBLE</span>
<span class="c1">-- The substring function expects arguments of type INT for the start and length parameters.</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="k">substring</span><span class="p">(</span><span class="s1">'hello'</span><span class="p">,</span> <span class="mi">1</span><span class="n">Y</span><span class="p">,</span> <span class="mi">2</span><span class="p">);</span>
<span class="n">he</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="k">substring</span><span class="p">(</span><span class="s1">'hello'</span><span class="p">,</span> <span class="s1">'1'</span><span class="p">,</span> <span class="mi">2</span><span class="p">);</span>
<span class="n">he</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="k">substring</span><span class="p">(</span><span class="s1">'hello'</span><span class="p">,</span> <span class="mi">1</span><span class="n">L</span><span class="p">,</span> <span class="mi">2</span><span class="p">);</span>
<span class="n">Error</span><span class="p">:</span> <span class="n">Argument</span> <span class="mi">2</span> <span class="n">requires</span> <span class="n">an</span> <span class="nb">INT</span> <span class="k">type</span><span class="p">.</span>
<span class="o">&gt;</span> <span class="k">SELECT</span> <span class="k">substring</span><span class="p">(</span><span class="s1">'hello'</span><span class="p">,</span> <span class="n">str</span><span class="p">,</span> <span class="mi">2</span><span class="p">)</span> <span class="k">FROM</span> <span class="k">VALUES</span><span class="p">(</span><span class="k">CAST</span><span class="p">(</span><span class="s1">'1'</span> <span class="k">AS</span> <span class="n">STRING</span><span class="p">))</span> <span class="k">AS</span> <span class="n">T</span><span class="p">(</span><span class="n">str</span><span class="p">);</span>
<span class="n">Error</span><span class="p">:</span> <span class="n">Argument</span> <span class="mi">2</span> <span class="n">requires</span> <span class="n">an</span> <span class="nb">INT</span> <span class="k">type</span><span class="p">.</span>
</code></pre></div></div>
<h3 id="sql-functions">SQL Functions</h3>
<p>The behavior of some SQL functions can be different under ANSI mode (<code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled=true</code>).</p>
<ul>
<li><code class="language-plaintext highlighter-rouge">size</code>: This function returns null for null input.</li>
<li><code class="language-plaintext highlighter-rouge">element_at</code>:
<ul>
<li>This function throws <code class="language-plaintext highlighter-rouge">ArrayIndexOutOfBoundsException</code> if using invalid indices.</li>
<li>This function throws <code class="language-plaintext highlighter-rouge">NoSuchElementException</code> if key does not exist in map.</li>
</ul>
</li>
<li><code class="language-plaintext highlighter-rouge">elt</code>: This function throws <code class="language-plaintext highlighter-rouge">ArrayIndexOutOfBoundsException</code> if using invalid indices.</li>
<li><code class="language-plaintext highlighter-rouge">parse_url</code>: This function throws <code class="language-plaintext highlighter-rouge">IllegalArgumentException</code> if an input string is not a valid url.</li>
<li><code class="language-plaintext highlighter-rouge">to_date</code>: This function should fail with an exception if the input string can&#8217;t be parsed, or the pattern string is invalid.</li>
<li><code class="language-plaintext highlighter-rouge">to_timestamp</code>: This function should fail with an exception if the input string can&#8217;t be parsed, or the pattern string is invalid.</li>
<li><code class="language-plaintext highlighter-rouge">unix_timestamp</code>: This function should fail with an exception if the input string can&#8217;t be parsed, or the pattern string is invalid.</li>
<li><code class="language-plaintext highlighter-rouge">to_unix_timestamp</code>: This function should fail with an exception if the input string can&#8217;t be parsed, or the pattern string is invalid.</li>
<li><code class="language-plaintext highlighter-rouge">make_date</code>: This function should fail with an exception if the result date is invalid.</li>
<li><code class="language-plaintext highlighter-rouge">make_timestamp</code>: This function should fail with an exception if the result timestamp is invalid.</li>
<li><code class="language-plaintext highlighter-rouge">make_interval</code>: This function should fail with an exception if the result interval is invalid.</li>
<li><code class="language-plaintext highlighter-rouge">next_day</code>: This function throws <code class="language-plaintext highlighter-rouge">IllegalArgumentException</code> if input is not a valid day of week.</li>
</ul>
<h3 id="sql-operators">SQL Operators</h3>
<p>The behavior of some SQL operators can be different under ANSI mode (<code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled=true</code>).</p>
<ul>
<li><code class="language-plaintext highlighter-rouge">array_col[index]</code>: This operator throws <code class="language-plaintext highlighter-rouge">ArrayIndexOutOfBoundsException</code> if using invalid indices.</li>
<li><code class="language-plaintext highlighter-rouge">map_col[key]</code>: This operator throws <code class="language-plaintext highlighter-rouge">NoSuchElementException</code> if key does not exist in map.</li>
</ul>
<h3 id="useful-functions-for-ansi-mode">Useful Functions for ANSI Mode</h3>
<p>When ANSI mode is on, it throws exceptions for invalid operations. You can use the following SQL functions to suppress such exceptions.</p>
<ul>
<li><code class="language-plaintext highlighter-rouge">try_cast</code>: identical to <code class="language-plaintext highlighter-rouge">CAST</code>, except that it returns <code class="language-plaintext highlighter-rouge">NULL</code> result instead of throwing an exception on runtime error.</li>
<li><code class="language-plaintext highlighter-rouge">try_add</code>: identical to the add operator <code class="language-plaintext highlighter-rouge">+</code>, except that it returns <code class="language-plaintext highlighter-rouge">NULL</code> result instead of throwing an exception on integral value overflow.</li>
<li><code class="language-plaintext highlighter-rouge">try_divide</code>: identical to the division operator <code class="language-plaintext highlighter-rouge">/</code>, except that it returns <code class="language-plaintext highlighter-rouge">NULL</code> result instead of throwing an exception on dividing 0.</li>
</ul>
<h3 id="sql-keywords">SQL Keywords</h3>
<p>When <code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code> is true, Spark SQL will use the ANSI mode parser.
In this mode, Spark SQL has two kinds of keywords:</p>
<ul>
<li>Reserved keywords: Keywords that are reserved and can&#8217;t be used as identifiers for table, view, column, function, alias, etc.</li>
<li>Non-reserved keywords: Keywords that have a special meaning only in particular contexts and can be used as identifiers in other contexts. For example, <code class="language-plaintext highlighter-rouge">EXPLAIN SELECT ...</code> is a command, but EXPLAIN can be used as identifiers in other places.</li>
</ul>
<p>When the ANSI mode is disabled, Spark SQL has two kinds of keywords:</p>
<ul>
<li>Non-reserved keywords: Same definition as the one when the ANSI mode enabled.</li>
<li>Strict-non-reserved keywords: A strict version of non-reserved keywords, which can not be used as table alias.</li>
</ul>
<p>By default <code class="language-plaintext highlighter-rouge">spark.sql.ansi.enabled</code> is false.</p>
<p>Below is a list of all the keywords in Spark SQL.</p>
<table>
<thead>
<tr>
<th>Keyword</th>
<th>Spark SQL<br />ANSI Mode</th>
<th>Spark SQL<br />Default Mode</th>
<th>SQL-2016</th>
</tr>
</thead>
<tbody>
<tr>
<td>ADD</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>AFTER</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>ALL</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ALTER</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ANALYZE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>AND</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ANTI</td>
<td>non-reserved</td>
<td>strict-non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>ANY</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ARCHIVE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>ARRAY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>AS</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ASC</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>AT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>AUTHORIZATION</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>BETWEEN</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>BOTH</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>BUCKET</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>BUCKETS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>BY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CACHE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CASCADE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CASE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CAST</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CHANGE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CHECK</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CLEAR</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CLUSTER</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CLUSTERED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CODEGEN</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>COLLATE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>COLLECTION</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>COLUMN</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>COLUMNS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>COMMENT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>COMMIT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>COMPACT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>COMPACTIONS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>COMPUTE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CONCATENATE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CONSTRAINT</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>COST</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>CREATE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CROSS</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CUBE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CURRENT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CURRENT_DATE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CURRENT_TIME</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CURRENT_TIMESTAMP</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>CURRENT_USER</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>DATA</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DATABASE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DATABASES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DAY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DBPROPERTIES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DEFINED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DELETE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>DELIMITED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DESC</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DESCRIBE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>DFS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DIRECTORIES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DIRECTORY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DISTINCT</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>DISTRIBUTE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>DIV</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>not a keyword</td>
</tr>
<tr>
<td>DROP</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ELSE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>END</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ESCAPE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ESCAPED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>EXCEPT</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>EXCHANGE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>EXISTS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>EXPLAIN</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>EXPORT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>EXTENDED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>EXTERNAL</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>EXTRACT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FALSE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FETCH</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FIELDS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>FILTER</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FILEFORMAT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>FIRST</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>FOLLOWING</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>FOR</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FOREIGN</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FORMAT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>FORMATTED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>FROM</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FULL</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FUNCTION</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>FUNCTIONS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>GLOBAL</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>GRANT</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>GROUP</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>GROUPING</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>HAVING</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>HOUR</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>IF</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>not a keyword</td>
</tr>
<tr>
<td>IGNORE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>IMPORT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>IN</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>INDEX</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>INDEXES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>INNER</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>INPATH</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>INPUTFORMAT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>INSERT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>INTERSECT</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>INTERVAL</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>INTO</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>IS</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ITEMS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>JOIN</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>KEYS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LAST</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LATERAL</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>LAZY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LEADING</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>LEFT</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>LIKE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>LIMIT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LINES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LIST</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LOAD</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LOCAL</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>LOCATION</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LOCK</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LOCKS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>LOGICAL</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>MACRO</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>MAP</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>MATCHED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>MERGE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>MINUTE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>MINUS</td>
<td>non-reserved</td>
<td>strict-non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>MONTH</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>MSCK</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>NAMESPACE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>NAMESPACES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>NATURAL</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>NO</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>NOT</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>NULL</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>NULLS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>OF</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ON</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ONLY</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>OPTION</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>OPTIONS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>OR</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ORDER</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>OUT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>OUTER</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>OUTPUTFORMAT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>OVER</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>OVERLAPS</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>OVERLAY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>OVERWRITE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>PARTITION</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>PARTITIONED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>PARTITIONS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>PERCENT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>PIVOT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>PLACING</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>POSITION</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>PRECEDING</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>PRIMARY</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>PRINCIPALS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>PROPERTIES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>PURGE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>QUERY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>RANGE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>RECORDREADER</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>RECORDWRITER</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>RECOVER</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>REDUCE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>REFERENCES</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>REFRESH</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>REGEXP</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>not a keyword</td>
</tr>
<tr>
<td>RENAME</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>REPAIR</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>REPLACE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>RESET</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>RESPECT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>RESTRICT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>REVOKE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>RIGHT</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>RLIKE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>ROLE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>ROLES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>ROLLBACK</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ROLLUP</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ROW</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>ROWS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>SCHEMA</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SCHEMAS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>not a keyword</td>
</tr>
<tr>
<td>SECOND</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SELECT</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>SEMI</td>
<td>non-reserved</td>
<td>strict-non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SEPARATED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SERDE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SERDEPROPERTIES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SESSION_USER</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>SET</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>SETS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SHOW</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SKEWED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SOME</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>SORT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SORTED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>START</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>STATISTICS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>STORED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>STRATIFY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>STRUCT</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SUBSTR</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SUBSTRING</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>SYNC</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TABLE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>TABLES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TABLESAMPLE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>TBLPROPERTIES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TEMP</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>not a keyword</td>
</tr>
<tr>
<td>TEMPORARY</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TERMINATED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>THEN</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>TIME</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>TO</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>TOUCH</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TRAILING</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>TRANSACTION</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TRANSACTIONS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TRANSFORM</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TRIM</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TRUE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>TRUNCATE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>TRY_CAST</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>TYPE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>UNARCHIVE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>UNBOUNDED</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>UNCACHE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>UNION</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>UNIQUE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>UNKNOWN</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>UNLOCK</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>UNSET</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>UPDATE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>USE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>USER</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>USING</td>
<td>reserved</td>
<td>strict-non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>VALUES</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>VIEW</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>VIEWS</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>WHEN</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>WHERE</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>WINDOW</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>WITH</td>
<td>reserved</td>
<td>non-reserved</td>
<td>reserved</td>
</tr>
<tr>
<td>YEAR</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
<tr>
<td>ZONE</td>
<td>non-reserved</td>
<td>non-reserved</td>
<td>non-reserved</td>
</tr>
</tbody>
</table>
</div>
<!-- /container -->
</div>
<script src="js/vendor/jquery-3.5.1.min.js"></script>
<script src="js/vendor/bootstrap.bundle.min.js"></script>
<script src="js/vendor/anchor.min.js"></script>
<script src="js/main.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/docsearch.js@2/dist/cdn/docsearch.min.js"></script>
<script type="text/javascript">
// DocSearch is entirely free and automated. DocSearch is built in two parts:
// 1. a crawler which we run on our own infrastructure every 24 hours. It follows every link
// in your website and extract content from every page it traverses. It then pushes this
// content to an Algolia index.
// 2. a JavaScript snippet to be inserted in your website that will bind this Algolia index
// to your search input and display its results in a dropdown UI. If you want to find more
// details on how works DocSearch, check the docs of DocSearch.
docsearch({
apiKey: 'd62f962a82bc9abb53471cb7b89da35e',
appId: 'RAI69RXRSK',
indexName: 'apache_spark',
inputSelector: '#docsearch-input',
enhancedSearchInput: true,
algoliaOptions: {
'facetFilters': ["version:3.2.4"]
},
debug: false // Set debug to true if you want to inspect the dropdown
});
</script>
<!-- MathJax Section -->
<script type="text/x-mathjax-config">
MathJax.Hub.Config({
TeX: { equationNumbers: { autoNumber: "AMS" } }
});
</script>
<script>
// Note that we load MathJax this way to work with local file (file://), HTTP and HTTPS.
// We could use "//cdn.mathjax...", but that won't support "file://".
(function(d, script) {
script = d.createElement('script');
script.type = 'text/javascript';
script.async = true;
script.onload = function(){
MathJax.Hub.Config({
tex2jax: {
inlineMath: [ ["$", "$"], ["\\\\(","\\\\)"] ],
displayMath: [ ["$$","$$"], ["\\[", "\\]"] ],
processEscapes: true,
skipTags: ['script', 'noscript', 'style', 'textarea', 'pre']
}
});
};
script.src = ('https:' == document.location.protocol ? 'https://' : 'http://') +
'cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js' +
'?config=TeX-AMS-MML_HTMLorMML';
d.getElementsByTagName('head')[0].appendChild(script);
}(document));
</script>
</body>
</html>