| |
| <!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"> |
| <meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| |
| <title>SELECT - Spark 3.5.5 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"> |
| <!--[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 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="img/spark-logo-rev.svg" width="141" height="72"/></a><span class="version">3.5.5</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="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.5.5</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-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> |
| |
| |
| |
| </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">SELECT</h1> |
| |
| |
| <h3 id="description">Description</h3> |
| |
| <p>Spark supports a <code class="language-plaintext highlighter-rouge">SELECT</code> statement and conforms to the ANSI SQL standard. Queries are |
| used to retrieve result sets from one or more tables. The following section |
| describes the overall query syntax and the sub-sections cover different constructs |
| of a query along with examples.</p> |
| |
| <h3 id="syntax">Syntax</h3> |
| |
| <div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="p">[</span> <span class="k">WITH</span> <span class="n">with_query</span> <span class="p">[</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="p">]</span> |
| <span class="n">select_statement</span> <span class="p">[</span> <span class="err">{</span> <span class="k">UNION</span> <span class="o">|</span> <span class="k">INTERSECT</span> <span class="o">|</span> <span class="k">EXCEPT</span> <span class="err">}</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span> <span class="n">select_statement</span><span class="p">,</span> <span class="p">...</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="err">{</span> <span class="n">expression</span> <span class="p">[</span> <span class="k">ASC</span> <span class="o">|</span> <span class="k">DESC</span> <span class="p">]</span> <span class="p">[</span> <span class="n">NULLS</span> <span class="err">{</span> <span class="k">FIRST</span> <span class="o">|</span> <span class="k">LAST</span> <span class="err">}</span> <span class="p">]</span> <span class="p">[</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="err">}</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="n">SORT</span> <span class="k">BY</span> <span class="err">{</span> <span class="n">expression</span> <span class="p">[</span> <span class="k">ASC</span> <span class="o">|</span> <span class="k">DESC</span> <span class="p">]</span> <span class="p">[</span> <span class="n">NULLS</span> <span class="err">{</span> <span class="k">FIRST</span> <span class="o">|</span> <span class="k">LAST</span> <span class="err">}</span> <span class="p">]</span> <span class="p">[</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="err">}</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">CLUSTER</span> <span class="k">BY</span> <span class="err">{</span> <span class="n">expression</span> <span class="p">[</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="err">}</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="n">DISTRIBUTE</span> <span class="k">BY</span> <span class="err">{</span> <span class="n">expression</span> <span class="p">[,</span> <span class="p">...</span> <span class="p">]</span> <span class="err">}</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="n">WINDOW</span> <span class="err">{</span> <span class="n">named_window</span> <span class="p">[</span> <span class="p">,</span> <span class="n">WINDOW</span> <span class="n">named_window</span><span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="err">}</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">LIMIT</span> <span class="err">{</span> <span class="k">ALL</span> <span class="o">|</span> <span class="n">expression</span> <span class="err">}</span> <span class="p">]</span> |
| </code></pre></div></div> |
| |
| <p>While <code class="language-plaintext highlighter-rouge">select_statement</code> is defined as</p> |
| <div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="p">[</span> <span class="n">hints</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span> <span class="err">{</span> <span class="p">[</span> <span class="p">[</span> <span class="n">named_expression</span> <span class="o">|</span> <span class="n">regex_column_names</span> <span class="p">]</span> <span class="p">[</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="o">|</span> <span class="k">TRANSFORM</span> <span class="p">(...)</span> <span class="p">]</span> <span class="err">}</span> |
| <span class="k">FROM</span> <span class="err">{</span> <span class="n">from_item</span> <span class="p">[</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="err">}</span> |
| <span class="p">[</span> <span class="n">PIVOT</span> <span class="n">clause</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="n">UNPIVOT</span> <span class="n">clause</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">LATERAL</span> <span class="k">VIEW</span> <span class="n">clause</span> <span class="p">]</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">WHERE</span> <span class="n">boolean_expression</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">expression</span> <span class="p">[</span> <span class="p">,</span> <span class="p">...</span> <span class="p">]</span> <span class="p">]</span> |
| <span class="p">[</span> <span class="k">HAVING</span> <span class="n">boolean_expression</span> <span class="p">]</span> |
| </code></pre></div></div> |
| |
| <h3 id="parameters">Parameters</h3> |
| |
| <ul> |
| <li> |
| <p><strong>with_query</strong></p> |
| |
| <p>Specifies the <a href="sql-ref-syntax-qry-select-cte.html">common table expressions (CTEs)</a> before the main query block. |
| These table expressions are allowed to be referenced later in the FROM clause. This is useful to abstract |
| out repeated subquery blocks in the FROM clause and improves readability of the query.</p> |
| </li> |
| <li> |
| <p><strong>hints</strong></p> |
| |
| <p>Hints can be specified to help spark optimizer make better planning decisions. Currently spark supports hints |
| that influence selection of join strategies and repartitioning of the data.</p> |
| </li> |
| <li> |
| <p><strong>ALL</strong></p> |
| |
| <p>Select all matching rows from the relation and is enabled by default.</p> |
| </li> |
| <li> |
| <p><strong>DISTINCT</strong></p> |
| |
| <p>Select all matching rows from the relation after removing duplicates in results.</p> |
| </li> |
| <li> |
| <p><strong>named_expression</strong></p> |
| |
| <p>An expression with an assigned name. In general, it denotes a column expression.</p> |
| |
| <p><strong>Syntax:</strong> <code class="language-plaintext highlighter-rouge">expression [[AS] alias]</code></p> |
| </li> |
| <li> |
| <p><strong>from_item</strong></p> |
| |
| <p>Specifies a source of input for the query. It can be one of the following:</p> |
| <ul> |
| <li>Table relation</li> |
| <li><a href="sql-ref-syntax-qry-select-join.html">Join relation</a></li> |
| <li><a href="sql-ref-syntax-qry-select-pivot.md">Pivot relation</a></li> |
| <li><a href="sql-ref-syntax-qry-select-unpivot.md">Unpivot relation</a></li> |
| <li><a href="sql-ref-syntax-qry-select-tvf.html">Table-value function</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-lateral-subquery.html">LATERAL</a> ] ( Subquery )</li> |
| <li><a href="sql-ref-syntax-qry-select-file.html">File</a></li> |
| </ul> |
| </li> |
| <li> |
| <p><strong>PIVOT</strong></p> |
| |
| <p>The <code class="language-plaintext highlighter-rouge">PIVOT</code> clause is used for data perspective; We can get the aggregated values based on specific column value.</p> |
| </li> |
| <li> |
| <p><strong>UNPIVOT</strong></p> |
| |
| <p>The <code class="language-plaintext highlighter-rouge">UNPIVOT</code> clause transforms columns into rows. It is the reverse of <code class="language-plaintext highlighter-rouge">PIVOT</code>, except for aggregation of values.</p> |
| </li> |
| <li> |
| <p><strong>LATERAL VIEW</strong></p> |
| |
| <p>The <code class="language-plaintext highlighter-rouge">LATERAL VIEW</code> clause is used in conjunction with generator functions such as <code class="language-plaintext highlighter-rouge">EXPLODE</code>, which will generate a virtual table containing one or more rows. <code class="language-plaintext highlighter-rouge">LATERAL VIEW</code> will apply the rows to each original output row.</p> |
| </li> |
| <li> |
| <p><strong>WHERE</strong></p> |
| |
| <p>Filters the result of the FROM clause based on the supplied predicates.</p> |
| </li> |
| <li> |
| <p><strong>GROUP BY</strong></p> |
| |
| <p>Specifies the expressions that are used to group the rows. This is used in conjunction with aggregate functions |
| (MIN, MAX, COUNT, SUM, AVG, etc.) to group rows based on the grouping expressions and aggregate values in each group. |
| When a FILTER clause is attached to an aggregate function, only the matching rows are passed to that function.</p> |
| </li> |
| <li> |
| <p><strong>HAVING</strong></p> |
| |
| <p>Specifies the predicates by which the rows produced by GROUP BY are filtered. The HAVING clause is used to |
| filter rows after the grouping is performed. If HAVING is specified without GROUP BY, it indicates a GROUP BY |
| without grouping expressions (global aggregate).</p> |
| </li> |
| <li> |
| <p><strong>ORDER BY</strong></p> |
| |
| <p>Specifies an ordering of the rows of the complete result set of the query. The output rows are ordered |
| across the partitions. This parameter is mutually exclusive with <code class="language-plaintext highlighter-rouge">SORT BY</code>, |
| <code class="language-plaintext highlighter-rouge">CLUSTER BY</code> and <code class="language-plaintext highlighter-rouge">DISTRIBUTE BY</code> and can not be specified together.</p> |
| </li> |
| <li> |
| <p><strong>SORT BY</strong></p> |
| |
| <p>Specifies an ordering by which the rows are ordered within each partition. This parameter is mutually |
| exclusive with <code class="language-plaintext highlighter-rouge">ORDER BY</code> and <code class="language-plaintext highlighter-rouge">CLUSTER BY</code> and can not be specified together.</p> |
| </li> |
| <li> |
| <p><strong>CLUSTER BY</strong></p> |
| |
| <p>Specifies a set of expressions that is used to repartition and sort the rows. Using this clause has |
| the same effect of using <code class="language-plaintext highlighter-rouge">DISTRIBUTE BY</code> and <code class="language-plaintext highlighter-rouge">SORT BY</code> together.</p> |
| </li> |
| <li> |
| <p><strong>DISTRIBUTE BY</strong></p> |
| |
| <p>Specifies a set of expressions by which the result rows are repartitioned. This parameter is mutually |
| exclusive with <code class="language-plaintext highlighter-rouge">ORDER BY</code> and <code class="language-plaintext highlighter-rouge">CLUSTER BY</code> and can not be specified together.</p> |
| </li> |
| <li> |
| <p><strong>LIMIT</strong></p> |
| |
| <p>Specifies the maximum number of rows that can be returned by a statement or subquery. This clause |
| is mostly used in the conjunction with <code class="language-plaintext highlighter-rouge">ORDER BY</code> to produce a deterministic result.</p> |
| </li> |
| <li> |
| <p><strong>boolean_expression</strong></p> |
| |
| <p>Specifies any expression that evaluates to a result type <code class="language-plaintext highlighter-rouge">boolean</code>. Two or |
| more expressions may be combined together using the logical |
| operators ( <code class="language-plaintext highlighter-rouge">AND</code>, <code class="language-plaintext highlighter-rouge">OR</code> ).</p> |
| </li> |
| <li> |
| <p><strong>expression</strong></p> |
| |
| <p>Specifies a combination of one or more values, operators, and SQL functions that evaluates to a value.</p> |
| </li> |
| <li> |
| <p><strong>named_window</strong></p> |
| |
| <p>Specifies aliases for one or more source window specifications. The source window specifications can |
| be referenced in the widow definitions in the query.</p> |
| </li> |
| <li> |
| <p><strong>regex_column_names</strong></p> |
| |
| <p>When <code class="language-plaintext highlighter-rouge">spark.sql.parser.quotedRegexColumnNames</code> is true, quoted identifiers (using backticks) in <code class="language-plaintext highlighter-rouge">SELECT</code> |
| statement are interpreted as regular expressions and <code class="language-plaintext highlighter-rouge">SELECT</code> statement can take regex-based column specification. |
| For example, below SQL will only take column <code class="language-plaintext highlighter-rouge">c</code>:</p> |
| |
| <div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code> <span class="k">SELECT</span> <span class="nv">`(a|b)?+.+`</span> <span class="k">FROM</span> <span class="p">(</span> |
| <span class="k">SELECT</span> <span class="mi">1</span> <span class="k">as</span> <span class="n">a</span><span class="p">,</span> <span class="mi">2</span> <span class="k">as</span> <span class="n">b</span><span class="p">,</span> <span class="mi">3</span> <span class="k">as</span> <span class="k">c</span> |
| <span class="p">)</span> |
| </code></pre></div> </div> |
| </li> |
| <li> |
| <p><strong>TRANSFORM</strong></p> |
| |
| <p>Specifies a hive-style transform query specification to transform the input by forking and running user-specified command or script.</p> |
| </li> |
| </ul> |
| |
| <h3 id="related-statements">Related Statements</h3> |
| |
| <ul> |
| <li><a href="sql-ref-syntax-qry-select-where.html">WHERE 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-orderby.html">ORDER BY Clause</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-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-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-cte.html">Common Table Expression</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-setops.html">Set Operators</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-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-transform.html">TRANSFORM Clause</a></li> |
| <li><a href="sql-ref-syntax-qry-select-lateral-subquery.html">LATERAL Subquery</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:3.5.5"] |
| }, |
| 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> |
