blob: d97a826ac7bad28ca0537120ed09cf98b698226a [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">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>CREATE FUNCTION - Spark 3.5.3 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.3</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.3</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-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">CREATE FUNCTION</h1>
<h3 id="description">Description</h3>
<p>The <code class="language-plaintext highlighter-rouge">CREATE FUNCTION</code> statement is used to create a temporary or permanent function
in Spark. Temporary functions are scoped at a session level where as permanent
functions are created in the persistent catalog and are made available to
all sessions. The resources specified in the <code class="language-plaintext highlighter-rouge">USING</code> clause are made available
to all executors when they are executed for the first time. In addition to the
SQL interface, spark allows users to create custom user defined scalar and
aggregate functions using Scala, Python and Java APIs. Please refer to
<a href="sql-ref-functions-udf-scalar.html">Scalar UDFs</a> and
<a href="sql-ref-functions-udf-aggregate.html">UDAFs</a> for more information.</p>
<h3 id="syntax">Syntax</h3>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">CREATE</span> <span class="p">[</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="p">]</span> <span class="p">[</span> <span class="k">TEMPORARY</span> <span class="p">]</span> <span class="k">FUNCTION</span> <span class="p">[</span> <span class="n">IF</span> <span class="k">NOT</span> <span class="k">EXISTS</span> <span class="p">]</span>
<span class="n">function_name</span> <span class="k">AS</span> <span class="n">class_name</span> <span class="p">[</span> <span class="n">resource_locations</span> <span class="p">]</span>
</code></pre></div></div>
<h3 id="parameters">Parameters</h3>
<ul>
<li>
<p><strong>OR REPLACE</strong></p>
<p>If specified, the resources for the function are reloaded. This is mainly useful
to pick up any changes made to the implementation of the function. This
parameter is mutually exclusive to <code class="language-plaintext highlighter-rouge">IF NOT EXISTS</code> and can not
be specified together.</p>
</li>
<li>
<p><strong>TEMPORARY</strong></p>
<p>Indicates the scope of function being created. When <code class="language-plaintext highlighter-rouge">TEMPORARY</code> is specified, the
created function is valid and visible in the current session. No persistent
entry is made in the catalog for these kind of functions.</p>
</li>
<li>
<p><strong>IF NOT EXISTS</strong></p>
<p>If specified, creates the function only when it does not exist. The creation
of function succeeds (no error is thrown) if the specified function already
exists in the system. This parameter is mutually exclusive to <code class="language-plaintext highlighter-rouge">OR REPLACE</code>
and can not be specified together.</p>
</li>
<li>
<p><strong>function_name</strong></p>
<p>Specifies a name of function to be created. The function name may be optionally qualified with a database name.</p>
<p><strong>Syntax:</strong> <code class="language-plaintext highlighter-rouge">[ database_name. ] function_name</code></p>
</li>
<li>
<p><strong>class_name</strong></p>
<p>Specifies the name of the class that provides the implementation for function to be created.
The implementing class should extend one of the base classes as follows:</p>
<ul>
<li>Should extend <code class="language-plaintext highlighter-rouge">UDF</code> or <code class="language-plaintext highlighter-rouge">UDAF</code> in <code class="language-plaintext highlighter-rouge">org.apache.hadoop.hive.ql.exec</code> package.</li>
<li>Should extend <code class="language-plaintext highlighter-rouge">AbstractGenericUDAFResolver</code>, <code class="language-plaintext highlighter-rouge">GenericUDF</code>, or
<code class="language-plaintext highlighter-rouge">GenericUDTF</code> in <code class="language-plaintext highlighter-rouge">org.apache.hadoop.hive.ql.udf.generic</code> package.</li>
<li>Should extend <code class="language-plaintext highlighter-rouge">UserDefinedAggregateFunction</code> in <code class="language-plaintext highlighter-rouge">org.apache.spark.sql.expressions</code> package.</li>
</ul>
</li>
<li>
<p><strong>resource_locations</strong></p>
<p>Specifies the list of resources that contain the implementation of the function
along with its dependencies.</p>
<p><strong>Syntax:</strong> <code class="language-plaintext highlighter-rouge">USING { { (JAR | FILE | ARCHIVE) resource_uri } , ... }</code></p>
</li>
</ul>
<h3 id="examples">Examples</h3>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- 1. Create a simple UDF `SimpleUdf` that increments the supplied integral value by 10.</span>
<span class="c1">-- import org.apache.hadoop.hive.ql.exec.UDF;</span>
<span class="c1">-- public class SimpleUdf extends UDF {</span>
<span class="c1">-- public int evaluate(int value) {</span>
<span class="c1">-- return value + 10;</span>
<span class="c1">-- }</span>
<span class="c1">-- }</span>
<span class="c1">-- 2. Compile and place it in a JAR file called `SimpleUdf.jar` in /tmp.</span>
<span class="c1">-- Create a table called `test` and insert two rows.</span>
<span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">test</span><span class="p">(</span><span class="n">c1</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">1</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">);</span>
<span class="c1">-- Create a permanent function called `simple_udf`. </span>
<span class="k">CREATE</span> <span class="k">FUNCTION</span> <span class="n">simple_udf</span> <span class="k">AS</span> <span class="s1">'SimpleUdf'</span>
<span class="k">USING</span> <span class="n">JAR</span> <span class="s1">'/tmp/SimpleUdf.jar'</span><span class="p">;</span>
<span class="c1">-- Verify that the function is in the registry.</span>
<span class="k">SHOW</span> <span class="k">USER</span> <span class="n">FUNCTIONS</span><span class="p">;</span>
<span class="o">+</span><span class="c1">------------------+</span>
<span class="o">|</span> <span class="k">function</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------------+</span>
<span class="o">|</span><span class="k">default</span><span class="p">.</span><span class="n">simple_udf</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------------+</span>
<span class="c1">-- Invoke the function. Every selected value should be incremented by 10.</span>
<span class="k">SELECT</span> <span class="n">simple_udf</span><span class="p">(</span><span class="n">c1</span><span class="p">)</span> <span class="k">AS</span> <span class="n">function_return_value</span> <span class="k">FROM</span> <span class="n">test</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------------------+</span>
<span class="o">|</span><span class="n">function_return_value</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------------------+</span>
<span class="o">|</span> <span class="mi">11</span><span class="o">|</span>
<span class="o">|</span> <span class="mi">12</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------------------+</span>
<span class="c1">-- Created a temporary function.</span>
<span class="k">CREATE</span> <span class="k">TEMPORARY</span> <span class="k">FUNCTION</span> <span class="n">simple_temp_udf</span> <span class="k">AS</span> <span class="s1">'SimpleUdf'</span>
<span class="k">USING</span> <span class="n">JAR</span> <span class="s1">'/tmp/SimpleUdf.jar'</span><span class="p">;</span>
<span class="c1">-- Verify that the newly created temporary function is in the registry.</span>
<span class="c1">-- Please note that the temporary function does not have a qualified</span>
<span class="c1">-- database associated with it.</span>
<span class="k">SHOW</span> <span class="k">USER</span> <span class="n">FUNCTIONS</span><span class="p">;</span>
<span class="o">+</span><span class="c1">------------------+</span>
<span class="o">|</span> <span class="k">function</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------------+</span>
<span class="o">|</span><span class="k">default</span><span class="p">.</span><span class="n">simple_udf</span><span class="o">|</span>
<span class="o">|</span> <span class="n">simple_temp_udf</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------------+</span>
<span class="c1">-- 1. Modify `SimpleUdf`'s implementation to add supplied integral value by 20.</span>
<span class="c1">-- import org.apache.hadoop.hive.ql.exec.UDF;</span>
<span class="c1">-- public class SimpleUdfR extends UDF {</span>
<span class="c1">-- public int evaluate(int value) {</span>
<span class="c1">-- return value + 20;</span>
<span class="c1">-- }</span>
<span class="c1">-- }</span>
<span class="c1">-- 2. Compile and place it in a jar file called `SimpleUdfR.jar` in /tmp.</span>
<span class="c1">-- Replace the implementation of `simple_udf`</span>
<span class="k">CREATE</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="k">FUNCTION</span> <span class="n">simple_udf</span> <span class="k">AS</span> <span class="s1">'SimpleUdfR'</span>
<span class="k">USING</span> <span class="n">JAR</span> <span class="s1">'/tmp/SimpleUdfR.jar'</span><span class="p">;</span>
<span class="c1">-- Invoke the function. Every selected value should be incremented by 20.</span>
<span class="k">SELECT</span> <span class="n">simple_udf</span><span class="p">(</span><span class="n">c1</span><span class="p">)</span> <span class="k">AS</span> <span class="n">function_return_value</span> <span class="k">FROM</span> <span class="n">test</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------------------+</span>
<span class="o">|</span><span class="n">function_return_value</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------------------+</span>
<span class="o">|</span> <span class="mi">21</span><span class="o">|</span>
<span class="o">|</span> <span class="mi">22</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------------------+</span>
</code></pre></div></div>
<h3 id="related-statements">Related Statements</h3>
<ul>
<li><a href="sql-ref-syntax-aux-show-functions.html">SHOW FUNCTIONS</a></li>
<li><a href="sql-ref-syntax-aux-describe-function.html">DESCRIBE FUNCTION</a></li>
<li><a href="sql-ref-syntax-ddl-drop-function.html">DROP FUNCTION</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.3"]
},
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>