blob: 76da703956d9b5ba7f4d224fb6e40c448b5dae15 [file] [log] [blame]
<!DOCTYPE html>
<html class="no-js">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>SQL Syntax - Spark 4.1.0-preview1 Documentation</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=DM+Sans:ital,wght@0,400;0,500;0,700;1,400;1,500;1,700&Courier+Prime:wght@400;700&display=swap" rel="stylesheet">
<link href="css/custom.css" rel="stylesheet">
<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="css/docsearch.min.css" />
<link rel="stylesheet" href="css/docsearch.css">
<!-- Matomo -->
<script>
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 class="global">
<!-- This code is taken from http://twitter.github.com/bootstrap/examples/hero.html -->
<nav class="navbar navbar-expand-lg navbar-dark p-0 px-4 fixed-top" style="background: #1d6890;" id="topbar">
<div class="navbar-brand"><a href="index.html">
<img src="https://spark.apache.org/images/spark-logo-rev.svg" width="141" height="72"/></a><span class="version">4.1.0-preview1</span>
</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 me-auto">
<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="streaming/index.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>
<a class="dropdown-item" href="declarative-pipelines-programming-guide.html">Declarative Pipelines</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/python/index.html">Python</a>
<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/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-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">v4.1.0-preview1</span></span>-->
</div>
</nav>
<div class="container">
<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-guide.html">
Migration Guide
</a>
</li>
<li>
<a href="sql-ref.html">
SQL Reference
</a>
</li>
<ul>
<li>
<a href="sql-ref-ansi-compliance.html">
ANSI Compliance
</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-number-pattern.html">
Number Pattern
</a>
</li>
<li>
<a href="sql-ref-operators.html">
Operators
</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-identifier-clause.html">
IDENTIFIER clause
</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>
<li>
<a href="sql-ref-syntax.html#ddl-statements">
Data Definition Statements
</a>
</li>
<li>
<a href="sql-ref-syntax.html#dml-statements">
Data Manipulation Statements
</a>
</li>
<li>
<a href="sql-ref-syntax.html#data-retrieval-statements">
Data Retrieval(Queries)
</a>
</li>
<li>
<a href="sql-ref-syntax.html#auxiliary-statements">
Auxiliary Statements
</a>
</li>
<li>
<a href="sql-pipe-syntax.html">
Pipe Syntax
</a>
</li>
</ul>
</ul>
<li>
<a href="sql-error-conditions.html">
Error Conditions
</a>
</li>
</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">SQL Syntax</h1>
<p>Spark SQL is Apache Spark&#8217;s module for working with structured data. The SQL Syntax section describes the SQL syntax in detail along with usage examples when applicable. This document provides a list of Data Definition and Data Manipulation Statements, as well as Data Retrieval and Auxiliary Statements.</p>
<h3 id="ddl-statements">DDL Statements</h3>
<p>Data Definition Statements are used to create or modify the structure of database objects in a database. Spark SQL supports the following Data Definition Statements:</p>
<ul>
<li><a href="sql-ref-syntax-ddl-alter-database.html">ALTER DATABASE</a></li>
<li><a href="sql-ref-syntax-ddl-alter-table.html">ALTER TABLE</a></li>
<li><a href="sql-ref-syntax-ddl-alter-view.html">ALTER VIEW</a></li>
<li><a href="sql-ref-syntax-ddl-create-database.html">CREATE DATABASE</a></li>
<li><a href="sql-ref-syntax-ddl-create-function.html">CREATE FUNCTION</a></li>
<li><a href="sql-ref-syntax-ddl-create-table.html">CREATE TABLE</a></li>
<li><a href="sql-ref-syntax-ddl-create-view.html">CREATE VIEW</a></li>
<li><a href="sql-ref-syntax-ddl-declare-variable.html">DECLARE VARIABLE</a></li>
<li><a href="sql-ref-syntax-ddl-drop-database.html">DROP DATABASE</a></li>
<li><a href="sql-ref-syntax-ddl-drop-function.html">DROP FUNCTION</a></li>
<li><a href="sql-ref-syntax-ddl-drop-table.html">DROP TABLE</a></li>
<li><a href="sql-ref-syntax-ddl-drop-variable.html">DROP TEMPORARY VARIABLE</a></li>
<li><a href="sql-ref-syntax-ddl-drop-view.html">DROP VIEW</a></li>
<li><a href="sql-ref-syntax-ddl-repair-table.html">REPAIR TABLE</a></li>
<li><a href="sql-ref-syntax-ddl-truncate-table.html">TRUNCATE TABLE</a></li>
<li><a href="sql-ref-syntax-ddl-usedb.html">USE DATABASE</a></li>
</ul>
<h3 id="dml-statements">DML Statements</h3>
<p>Data Manipulation Statements are used to add, change, or delete data. Spark SQL supports the following Data Manipulation Statements:</p>
<ul>
<li><a href="sql-ref-syntax-dml-insert-table.html">INSERT TABLE</a></li>
<li><a href="sql-ref-syntax-dml-insert-overwrite-directory.html">INSERT OVERWRITE DIRECTORY</a></li>
<li><a href="sql-ref-syntax-dml-load.html">LOAD</a></li>
</ul>
<h3 id="data-retrieval-statements">Data Retrieval Statements</h3>
<p>Spark supports <code>SELECT</code> statement that is used to retrieve rows
from one or more tables according to the specified clauses. The full syntax
and brief description of supported clauses are explained in
<a href="sql-ref-syntax-qry-select.html">SELECT</a> section. The SQL statements related
to SELECT are also included in this section. Spark also provides the
ability to generate logical and physical plan for a given query using
<a href="sql-ref-syntax-qry-explain.html">EXPLAIN</a> statement.</p>
<ul>
<li><a href="sql-ref-syntax-qry-select.html">SELECT Statement</a>
<ul>
<li><a href="sql-ref-syntax-qry-select-cte.html">Common Table Expression</a></li>
<li><a href="sql-ref-syntax-qry-select-clusterby.html">CLUSTER BY Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-distribute-by.html">DISTRIBUTE BY Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-groupby.html">GROUP BY Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-having.html">HAVING Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-hints.html">Hints</a></li>
<li><a href="sql-ref-syntax-qry-select-inline-table.html">Inline Table</a></li>
<li><a href="sql-ref-syntax-qry-select-file.html">File</a></li>
<li><a href="sql-ref-syntax-qry-select-join.html">JOIN</a></li>
<li><a href="sql-ref-syntax-qry-select-like.html">LIKE Predicate</a></li>
<li><a href="sql-ref-syntax-qry-select-limit.html">LIMIT Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-offset.html">OFFSET Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-orderby.html">ORDER BY Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-setops.html">Set Operators</a></li>
<li><a href="sql-ref-syntax-qry-select-sortby.html">SORT BY Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-sampling.html">TABLESAMPLE</a></li>
<li><a href="sql-ref-syntax-qry-select-tvf.html">Table-valued Function</a></li>
<li><a href="sql-ref-syntax-qry-select-where.html">WHERE Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-aggregate.html">Aggregate Function</a></li>
<li><a href="sql-ref-syntax-qry-select-window.html">Window Function</a></li>
<li><a href="sql-ref-syntax-qry-select-case.html">CASE Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-pivot.html">PIVOT Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-unpivot.html">UNPIVOT Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-lateral-view.html">LATERAL VIEW Clause</a></li>
<li><a href="sql-ref-syntax-qry-select-lateral-subquery.html">LATERAL SUBQUERY</a></li>
<li><a href="sql-ref-syntax-qry-select-transform.html">TRANSFORM Clause</a></li>
<li><a href="sql-ref-syntax-qry-star.html">star (*) Clause</a></li>
</ul>
</li>
<li><a href="sql-ref-syntax-qry-explain.html">EXPLAIN</a></li>
</ul>
<h3 id="auxiliary-statements">Auxiliary Statements</h3>
<ul>
<li><a href="sql-ref-syntax-aux-resource-mgmt-add-file.html">ADD FILE</a></li>
<li><a href="sql-ref-syntax-aux-resource-mgmt-add-jar.html">ADD JAR</a></li>
<li><a href="sql-ref-syntax-aux-analyze-table.html">ANALYZE TABLE</a></li>
<li><a href="sql-ref-syntax-aux-cache-cache-table.html">CACHE TABLE</a></li>
<li><a href="sql-ref-syntax-aux-cache-clear-cache.html">CLEAR CACHE</a></li>
<li><a href="sql-ref-syntax-aux-describe-database.html">DESCRIBE DATABASE</a></li>
<li><a href="sql-ref-syntax-aux-describe-function.html">DESCRIBE FUNCTION</a></li>
<li><a href="sql-ref-syntax-aux-describe-query.html">DESCRIBE QUERY</a></li>
<li><a href="sql-ref-syntax-aux-describe-table.html">DESCRIBE TABLE</a></li>
<li><a href="sql-ref-syntax-aux-resource-mgmt-list-file.html">LIST FILE</a></li>
<li><a href="sql-ref-syntax-aux-resource-mgmt-list-jar.html">LIST JAR</a></li>
<li><a href="sql-ref-syntax-aux-cache-refresh.html">REFRESH</a></li>
<li><a href="sql-ref-syntax-aux-cache-refresh-table.html">REFRESH TABLE</a></li>
<li><a href="sql-ref-syntax-aux-cache-refresh-function.html">REFRESH FUNCTION</a></li>
<li><a href="sql-ref-syntax-aux-conf-mgmt-reset.html">RESET</a></li>
<li><a href="sql-ref-syntax-aux-conf-mgmt-set.html">SET</a></li>
<li><a href="sql-ref-syntax-aux-set-var.html">SET VAR</a></li>
<li><a href="sql-ref-syntax-aux-exec-imm.html">EXECUTE IMMEDIATE</a></li>
<li><a href="sql-ref-syntax-aux-show-columns.html">SHOW COLUMNS</a></li>
<li><a href="sql-ref-syntax-aux-show-create-table.html">SHOW CREATE TABLE</a></li>
<li><a href="sql-ref-syntax-aux-show-databases.html">SHOW DATABASES</a></li>
<li><a href="sql-ref-syntax-aux-show-functions.html">SHOW FUNCTIONS</a></li>
<li><a href="sql-ref-syntax-aux-show-partitions.html">SHOW PARTITIONS</a></li>
<li><a href="sql-ref-syntax-aux-show-table.html">SHOW TABLE EXTENDED</a></li>
<li><a href="sql-ref-syntax-aux-show-tables.html">SHOW TABLES</a></li>
<li><a href="sql-ref-syntax-aux-show-tblproperties.html">SHOW TBLPROPERTIES</a></li>
<li><a href="sql-ref-syntax-aux-show-views.html">SHOW VIEWS</a></li>
<li><a href="sql-ref-syntax-aux-cache-uncache-table.html">UNCACHE TABLE</a></li>
</ul>
</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="js/vendor/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:4.1.0-preview1"]
},
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>