Google Git
Sign in
apache / cassandra-website / refs/heads/asf-staging / . / content / doc / latest / cassandra / developing / cql / functions.html
blob: 617f2283d67768b6a96816eef5595e47cc1e3b0b [file] [log] [blame]
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0">
<title>Functions | Apache Cassandra Documentation</title>
<link rel="stylesheet" href="../../../../../assets/css/site.css">
<link rel="schema.dcterms" href="https://purl.org/dc/terms/">
<meta name="dcterms.subject" content="Cassandra">
<meta name="dcterms.identifier" content="5.0">
<meta name="generator" content="Antora 2.3.4">
<link rel="icon" href="../../../../../assets/img/favicon.ico" type="image/x-icon">
<script>
const script = document.createElement("script");
const domain = window.location.hostname;
script.type = "text/javascript";
script.src = "https://plausible.cassandra.apache.org/js/plausible.js";
script.setAttribute("data-domain",domain);
script.setAttribute("defer",'true');
script.setAttribute("async",'true');
document.getElementsByTagName("head")[0].appendChild(script);
</script> </head>
<body class="docs-wrapper article">
<div class="container mx-auto relative">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<meta property="og:type" content="website" />
<meta property="og:url" content="/" />
<meta property="og:site_name" content="Apache Cassandra" />
<header id="top-nav">
<div class="inner relative">
<div class="header-social-icons text-right">
<a href="https://twitter.com/cassandra?lang=en" target="_blank" styles="margin-left: 20px;"><img src="../../../../../assets/img/twitter-icon-circle-white.svg" alt="twitter icon" width="24"></a>
<a href="https://www.linkedin.com/company/apache-cassandra/" target="_blank" styles="margin-left: 20px;"><img src="../../../../../assets/img/LI-In-Bug.png" alt="linked-in icon" width="24"></a>
<a href="https://www.youtube.com/c/PlanetCassandra" target="_blank" styles="margin-left: 20px;"><img src="../../../../../assets/img/youtube-icon.png" alt="youtube icon" width="24"></a>
</div>
<div class="cf">
<div class="logo left"><a href="/"><img src="../../../../../assets/img/logo-white-r.png" alt="Cassandra Logo"></a></div>
<div class="mobile-nav-icon right">
<img class="toggle-icon" src="../../../../../assets/img/hamburger-nav.svg">
</div>
<ul class="main-nav nav-links right flex flex-vert-center flex-space-between">
<li>
<a class="nav-link hide-mobile">Get Started</a>
<ul class="sub-menu bg-white">
<li class="pa-micro">
<a href="/_/cassandra-basics.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-basics.png" alt="cassandra basics icon">
</div>
<div class="sub-nav-text teal py-small">
Cassandra Basics
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/quickstart.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-rocket.png" alt="cassandra basics icon">
</div>
<div class="sub-nav-text teal py-small">
Quickstart
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/ecosystem.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-ecosystem.png" alt="cassandra basics icon">
</div>
<div class="sub-nav-text teal py-small">
Ecosystem
</div>
</a>
</li>
</ul>
</li>
<li><a class="nav-link" href="/doc/latest/">Documentation</a></li>
<li>
<a class="nav-link" href="/_/community.html">Community</a>
<ul class="sub-menu bg-white">
<li class="pa-micro">
<a href="/_/community.html#code-of-conduct">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-welcome.png" alt="welcome icon">
</div>
<div class="sub-nav-text teal py-small">
Welcome
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/community.html#discussions">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-discussions.png" alt="discussions icon">
</div>
<div class="sub-nav-text teal py-small">
Discussions
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/community.html#project-governance">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-governance.png" alt="Governance icon">
</div>
<div class="sub-nav-text teal py-small">
Governance
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/community.html#how-to-contribute">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-contribute.png" alt="Contribute icon">
</div>
<div class="sub-nav-text teal py-small">
Contribute
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/community.html#meet-the-community">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-community.png" alt="Meet the Community icon">
</div>
<div class="sub-nav-text teal py-small">
Meet the Community
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/cassandra-catalyst-program.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-catalyst.png" alt="Catalyst icon">
</div>
<div class="sub-nav-text teal py-small">
Catalyst Program
</div>
</a>
</li>
<li class="pa-micro hide-mobile">
<a href="/_/events.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-events.png" alt="Events icon">
</div>
<div class="sub-nav-text teal py-small">
Events
</div>
</a>
</li>
</ul>
</li>
<li>
<a class="nav-link hide-mobile">Learn</a>
<ul class="sub-menu bg-white">
<li class="pa-micro">
<a href="/_/Apache-Cassandra-5.0-Moving-Toward-an-AI-Driven-Future.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-basics.png" alt="Basics icon">
</div>
<div class="sub-nav-text teal py-small">
Cassandra 5.0
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/case-studies.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-case-study.png" alt="Case Studies icon">
</div>
<div class="sub-nav-text teal py-small">
Case Studies
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/resources.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-resources.png" alt="Resources icon">
</div>
<div class="sub-nav-text teal py-small">
Resources
</div>
</a>
</li>
<li class="pa-micro">
<a href="/_/blog.html">
<div class="sub-nav-icon">
<img src="../../../../../assets/img/sub-menu-blog.png" alt="Blog icon">
</div>
<div class="sub-nav-text teal py-small">
Blog
</div>
</a>
</li>
</ul>
</li>
<li><a class="nav-link btn btn--filled" href="/_/download.html">Download Now</a></li>
</ul>
</div>
</div>
</header>
<div class="hero hero--home grad">
<div class="eye"></div>
<div id="docs-content" class="text-center flex flex-center flex-column relative z2 ma-xlarge">
<h2>Cassandra Documentation</h2>
</div>
</div>
<div class="body px-medium py-medium container">
<div class="docs-nav-bar flex flex-space-between mb-medium">
<div id="mobile-docs-nav-burger" class="hidden">
<svg viewBox="0 0 24 24" width="36" height="36" stroke="#1c81a0" stroke-width="2.5" fill="none" stroke-linecap="round" stroke-linejoin="round" class="css-i6dzq1"><line x1="3" y1="12" x2="21" y2="12"></line><line x1="3" y1="6" x2="21" y2="6"></line><line x1="3" y1="18" x2="21" y2="18"></line></svg>
</div>
<div class="docs-nav-item relative">
<input id="search-input" type="text" placeholder="Search docs">
</div>
<div class="versions-wrapper">
<h4>Version:</h4>
<div class="nav-panel-explore" data-panel="explore">
<div id="version-toggle" class="context">
<span class="version">5.0</span>
</div>
<ul id="versions-list" class="components">
<li class="component">
<ul class="versions">
<li class="version is-latest">
<a href="../../../../../_/index.html">master</a>
</li>
</ul>
</li>
<li class="component is-current">
<ul class="versions">
<li class="version">
<a href="../../../../trunk/index.html">trunk</a>
</li>
<li class="version is-current">
<a href="../../../index.html">5.0</a>
</li>
<li class="version is-latest">
<a href="../../../../4.1/index.html">4.1</a>
</li>
<li class="version">
<a href="../../../../4.0/index.html">4.0</a>
</li>
<li class="version">
<a href="../../../../3.11/index.html">3.11</a>
</li>
</ul>
</li>
</ul>
</div>
</div> </div>
<div class="cf relative">
<nav class="nav docs-nav full-800">
<div class="nav-menu">
<ul class="nav-list">
<li class="nav-item is-active" data-depth="0">
<ul class="nav-list">
<li class="nav-item" data-depth="1">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../../index.html">Main</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/glossary.html">Glossary</a>
</span>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/bugs.html">How to report bugs</a>
</span>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/contactus.html">Contact us</a>
</span>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../../../../_/development/index.html">Development</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/gettingstarted.html">Getting started</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/ide.html">Building and IDE integration</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/testing.html">Testing</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/patches.html">Contributing code changes</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/code_style.html">Code style</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/how_to_review.html">Review checklist</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/how_to_commit.html">How to commit</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/documentation.html">Working on documentation</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/ci.html">Jenkins CI environment</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/dependencies.html">Dependency management</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../../../../_/development/release_process.html">Release process</a>
</span>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li class="nav-item is-active" data-depth="0">
<ul class="nav-list">
<li class="nav-item is-current-path is-active" data-depth="1">
<span class="nav-line">
<button class="nav-toggle"></button>
<span class="nav-text">Cassandra</span>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../../overview/faq/index.html">FAQ</a>
</span>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../getting-started/index.html">Getting Started</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/cassandra-quickstart.html">Cassandra Quickstart</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/sai-quickstart.html">SAI Quickstart</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/vector-search-quickstart.html">Vector Search Quickstart</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../installing/installing.html">Installing Cassandra</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/configuring.html">Configuring Cassandra</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/querying.html">Inserting and querying</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/drivers.html">Client drivers</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/production.html">Production recommendations</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../new/index.html">What&#8217;s new</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../reference/java17.html">Support for Java</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../architecture/index.html">Architecture</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../architecture/overview.html">Overview</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../architecture/dynamo.html">Dynamo</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../architecture/storage-engine.html">Storage Engine</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../architecture/guarantees.html">Guarantees</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../architecture/messaging.html">Improved Internode Messaging</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../architecture/streaming.html">Improved Streaming</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../data-modeling/index.html">Data Modeling</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/intro.html">Introduction</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/data-modeling_conceptual.html">Conceptual data modeling</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/data-modeling_rdbms.html">RDBMS design</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/data-modeling_queries.html">Defining application queries</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/data-modeling_logical.html">Logical data modeling</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/data-modeling_physical.html">Physical data modeling</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/data-modeling_refining.html">Evaluating and refining data models</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/data-modeling_schema.html">Defining database schema</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../data-modeling/data-modeling_tools.html">Cassandra data modeling tools</a>
</span>
</li>
</ul>
</li>
<li class="nav-item is-current-path is-active" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="index.html">Cassandra Query Language (CQL)</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="definitions.html">Definitions</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="types.html">Data types</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="ddl.html">Data definition (DDL)</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="dml.html">Data manipulation (DML)</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="dynamic-data-masking.html">Dynamic Data Masking (DDM)</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="operators.html">Operators</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="indexing/indexing-concepts.html">Indexing concepts</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="4">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="indexing/sai/sai-overview.html">SAI Overview</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="indexing/sai/sai-concepts.html">Concepts</a>
</span>
</li>
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/sai-quickstart.html">SAI Quickstart</a>
</span>
</li>
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="indexing/sai/sai-faq.html">SAI FAQ</a>
</span>
</li>
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="indexing/sai/sai-working-with.html">Working with SAI</a>
</span>
</li>
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="indexing/sai/operations/sai-operations.html">SAI operations</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="indexing/2i/2i-overview.html">Secondary indexes (2i) overview</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="indexing/2i/2i-concepts.html">Concepts</a>
</span>
</li>
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="indexing/2i/2i-working-with.html">Working with 2i</a>
</span>
</li>
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="indexing/2i/operations/2i-build.html">Rebuild 2i</a>
</span>
</li>
</ul>
</li>
</ul>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="mvs.html">Materialized views</a>
</span>
</li>
<li class="nav-item is-current-page is-active" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="functions.html">Functions</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="json.html">JSON</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="security.html">Security</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="triggers.html">Triggers</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="appendices.html">Appendices</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="changes.html">Changes</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="SASI.html">SASI</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="cql_singlefile.html">Single file of CQL information</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../vector-search/overview.html">Vector Search overview</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../vector-search/concepts.html">Concepts</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../vector-search/data-modeling.html">Data Modeling</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../getting-started/vector-search-quickstart.html">Vector Search Quickstart</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../vector-search/vector-search-working-with.html">Working with Vector Search</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../managing/index.html">Managing</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../managing/configuration/index.html">Configuring</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/configuration/cass_yaml_file.html">cassandra.yaml</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/configuration/cass_rackdc_file.html">cassandra-rackdc.properties</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/configuration/cass_env_sh_file.html">cassandra-env.sh</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/configuration/cass_topo_file.html">cassandra-topologies.properties</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/configuration/cass_cl_archive_file.html">commitlog-archiving.properties</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/configuration/cass_logback_xml_file.html">logback.xml</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/configuration/cass_jvm_options_file.html">jvm-* files</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/configuration/configuration.html">Liberating cassandra.yaml Parameters' Names from Their Units</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../managing/operating/index.html">Operating</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/backups.html">Backups</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/bloom_filters.html">Bloom filters</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/bulk_loading.html">Bulk loading</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/cdc.html">Change Data Capture (CDC)</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/compaction/index.html">Compaction</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/compression.html">Compression</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/hardware.html">Hardware</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/hints.html">Hints</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../managing/operating/logging.html">Logging</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/auditlogging.html">Audit logging</a>
</span>
</li>
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/audit_logging.html">Audit logging 2</a>
</span>
</li>
<li class="nav-item" data-depth="5">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/fqllogging.html">Full query logging</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/metrics.html">Monitoring metrics</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/repair.html">Repair</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/read_repair.html">Read repair</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/security.html">Security</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/snitch.html">Snitches</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/topo_changes.html">Topology changes</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/transientreplication.html">Transient replication</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/operating/virtualtables.html">Virtual tables</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../managing/tools/index.html">Tools</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/tools/cqlsh.html">cqlsh: the CQL shell</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/tools/nodetool/nodetool.html">nodetool</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="../../managing/tools/sstable/index.html">SSTable tools</a>
</span>
</li>
<li class="nav-item" data-depth="4">
<span class="nav-line">
<a class="nav-link" href="#cassandra:managing/tools/cassandra_stress.adoc">cassandra-stress</a>
</span>
</li>
</ul>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../troubleshooting/index.html">Troubleshooting</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../troubleshooting/finding_nodes.html">Finding misbehaving nodes</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../troubleshooting/reading_logs.html">Reading Cassandra logs</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../troubleshooting/use_nodetool.html">Using nodetool</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../troubleshooting/use_tools.html">Using external tools to deep-dive</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<button class="nav-toggle"></button>
<a class="nav-link" href="../../reference/index.html">Reference</a>
</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../reference/cql-commands/alter-table.html">ALTER TABLE</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../reference/cql-commands/create-index.html">CREATE INDEX</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../reference/cql-commands/create-custom-index.html">CREATE CUSTOM INDEX</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../reference/cql-commands/create-table.html">CREATE TABLE</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../reference/cql-commands/drop-index.html">DROP INDEX</a>
</span>
</li>
<li class="nav-item" data-depth="3">
<span class="nav-line">
<a class="nav-link" href="../../reference/cql-commands/drop-table.html">DROP TABLE</a>
</span>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<span class="nav-line">
<a class="nav-link" href="../../integrating/plugins/index.html">Plug-ins</a>
</span>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</nav>
<aside class="toc sidebar">
<div class="toc-menu"></div>
</aside>
<main class="article default-main full-800" data-ceiling="topbar">
<div class="article-banner">
<p>You are viewing the documentation for a prerelease version.</p>
</div>
<div class="article-header">
<nav class="crumbs" aria-label="breadcrumbs">
<ul>
<li class="crumb">Cassandra</li>
<li class="crumb"><a href="index.html">Cassandra Query Language (CQL)</a></li>
<li class="crumb"><a href="functions.html">Functions</a></li>
</ul>
</nav>
<div class="tools" role="navigation">
<ul>
<li class="tool edit"><a href="https://github.com/apache/cassandra/edit/cassandra-5.0/doc/modules/cassandra/pages/developing/cql/functions.adoc" title="Edit Page" target="_blank" rel="noopener">Edit</a></li>
</ul>
</div>
</div>
<article class="doc">
<h1 class="page">Functions</h1>
<div id="preamble">
<div class="sectionbody">
<div class="paragraph">
<p>CQL supports 2 main categories of functions:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><a href="#scalar-functions">scalar functions</a> that take a number of values and produce an output</p>
</li>
<li>
<p><a href="#aggregate-functions">aggregate functions</a> that aggregate multiple rows resulting from a <code>SELECT</code> statement</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>In both cases, CQL provides a number of native "hard-coded" functions as
well as the ability to create new user-defined functions.</p>
</div>
<div class="admonitionblock note">
<table>
<tr>
<td class="icon">
<i class="fa icon-note" title="Note"></i>
</td>
<td class="content">
<div class="paragraph">
<p>By default, the use of user-defined functions is disabled by default for
security concerns (even when enabled, the execution of user-defined
functions is sandboxed and a "rogue" function should not be allowed to
do evil, but no sandbox is perfect so using user-defined functions is
opt-in). See the <code>user_defined_functions_enabled</code> in <code>cassandra.yaml</code> to
enable them.</p>
</div>
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>A function is identifier by its name:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">function_name ::= [ keyspace_name'.' ] name</code></pre>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="scalar-functions"><a class="anchor" href="#scalar-functions"></a>Scalar functions</h2>
<div class="sectionbody">
<div class="sect2">
<h3 id="scalar-native-functions"><a class="anchor" href="#scalar-native-functions"></a>Native functions</h3>
<div class="sect3">
<h4 id="cast"><a class="anchor" href="#cast"></a>Cast</h4>
<div class="paragraph">
<p>The <code>cast</code> function can be used to converts one native datatype to
another.</p>
</div>
<div class="paragraph">
<p>The following table describes the conversions supported by the <code>cast</code>
function. Cassandra will silently ignore any cast converting a datatype
into its own datatype.</p>
</div>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 50%;">
<col style="width: 50%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">From</th>
<th class="tableblock halign-left valign-top">To</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>ascii</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>bigint</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>smallint</code>, <code>int</code>, <code>float</code>, <code>double</code>, <code>decimal</code>, <code>varint</code>,
<code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>boolean</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>counter</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>smallint</code>, <code>int</code>, <code>bigint</code>, <code>float</code>, <code>double</code>, <code>decimal</code>,
<code>varint</code>, <code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>date</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timestamp</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>decimal</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>smallint</code>, <code>int</code>, <code>bigint</code>, <code>float</code>, <code>double</code>, <code>varint</code>,
<code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>double</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>smallint</code>, <code>int</code>, <code>bigint</code>, <code>float</code>, <code>decimal</code>, <code>varint</code>,
<code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>float</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>smallint</code>, <code>int</code>, <code>bigint</code>, <code>double</code>, <code>decimal</code>, <code>varint</code>,
<code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>inet</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>int</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>smallint</code>, <code>bigint</code>, <code>float</code>, <code>double</code>, <code>decimal</code>, <code>varint</code>,
<code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>smallint</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>int</code>, <code>bigint</code>, <code>float</code>, <code>double</code>, <code>decimal</code>, <code>varint</code>,
<code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>time</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>date</code>, <code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timeuuid</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timestamp</code>, <code>date</code>, <code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>smallint</code>, <code>int</code>, <code>bigint</code>, <code>float</code>, <code>double</code>, <code>decimal</code>,
<code>varint</code>, <code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>uuid</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>text</code>, <code>varchar</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>varint</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>tinyint</code>, <code>smallint</code>, <code>int</code>, <code>bigint</code>, <code>float</code>, <code>double</code>, <code>decimal</code>,
<code>text</code>, <code>varchar</code></p></td>
</tr>
</tbody>
</table>
<div class="paragraph">
<p>The conversions rely strictly on Java&#8217;s semantics. For example, the
double value 1 will be converted to the text value '1.0'. For instance:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT avg(cast(count as double)) FROM myTable</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="token"><a class="anchor" href="#token"></a>Token</h4>
<div class="paragraph">
<p>The <code>token</code> function computes the token for a given partition key.
The exact signature of the token function depends on the table concerned and the partitioner used by the cluster.</p>
</div>
<div class="paragraph">
<p>The type of the arguments of the <code>token</code> depend on the partition key column type. The returned type depends on the defined partitioner:</p>
</div>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 50%;">
<col style="width: 50%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Partitioner</th>
<th class="tableblock halign-left valign-top">Returned type</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">Murmur3Partitioner</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>bigint</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">RandomPartitioner</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>varint</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">ByteOrderedPartitioner</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>blob</code></p></td>
</tr>
</tbody>
</table>
<div class="paragraph">
<p>For example, consider the following table:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">CREATE TABLE users (
userid text PRIMARY KEY,
username text,
);</code></pre>
</div>
</div>
<div class="paragraph">
<p>The table uses the default Murmur3Partitioner.
The <code>token</code> function uses the single argument <code>text</code>, because the partition key is <code>userid</code> of text type.
The returned type will be <code>bigint</code>.</p>
</div>
</div>
<div class="sect3">
<h4 id="uuid"><a class="anchor" href="#uuid"></a>Uuid</h4>
<div class="paragraph">
<p>The <code>uuid</code> function takes no parameters and generates a random type 4
uuid suitable for use in <code>INSERT</code> or <code>UPDATE</code> statements.</p>
</div>
</div>
<div class="sect3">
<h4 id="timeuuid-functions"><a class="anchor" href="#timeuuid-functions"></a>Timeuuid functions</h4>
<div class="sect4">
<h5 id="now"><a class="anchor" href="#now"></a><code>now</code></h5>
<div class="paragraph">
<p>The <code>now</code> function takes no arguments and generates, on the coordinator
node, a new unique timeuuid at the time the function is invoked. Note
that this method is useful for insertion but is largely non-sensical in
<code>WHERE</code> clauses.</p>
</div>
<div class="paragraph">
<p>For example, a query of the form:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM myTable WHERE t = now();</code></pre>
</div>
</div>
<div class="paragraph">
<p>will not return a result, by design, since the value returned by
<code>now()</code> is guaranteed to be unique.</p>
</div>
<div class="paragraph">
<p><code>current_timeuuid</code> is an alias of <code>now</code>.</p>
</div>
</div>
<div class="sect4">
<h5 id="min_timeuuid-and-max_timeuuid"><a class="anchor" href="#min_timeuuid-and-max_timeuuid"></a><code>min_timeuuid</code> and <code>max_timeuuid</code></h5>
<div class="paragraph">
<p>The <code>min_timeuuid</code> function takes a <code>timestamp</code> value <code>t</code>, either a timestamp or a date string.
It returns a <em>fake</em> <code>timeuuid</code> corresponding to the <em>smallest</em> possible <code>timeuuid</code> for timestamp <code>t</code>.
The <code>max_timeuuid</code> works similarly, but returns the <em>largest</em> possible <code>timeuuid</code>.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM myTable
WHERE t &gt; max_timeuuid('2013-01-01 00:05+0000')
AND t &lt; min_timeuuid('2013-02-02 10:00+0000');</code></pre>
</div>
</div>
<div class="paragraph">
<p>will select all rows where the <code>timeuuid</code> column <code>t</code> is later than <code>'2013-01-01 00:05+0000'</code> and earlier than <code>'2013-02-02 10:00+0000'</code>.
The clause <code>t &gt;= maxTimeuuid('2013-01-01 00:05+0000')</code> would still <em>not</em> select a <code>timeuuid</code> generated exactly at '2013-01-01 00:05+0000', and is essentially equivalent to <code>t &gt; maxTimeuuid('2013-01-01 00:05+0000')</code>.</p>
</div>
<div class="admonitionblock note">
<table>
<tr>
<td class="icon">
<i class="fa icon-note" title="Note"></i>
</td>
<td class="content">
<div class="paragraph">
<p>The values generated by <code>min_timeuuid</code> and <code>max_timeuuid</code> are called <em>fake</em> UUID because they do no respect the time-based UUID generation process
specified by the <a href="http://www.ietf.org/rfc/rfc4122.txt">IETF RFC 4122</a>.
In particular, the value returned by these two methods will not be unique.
Thus, only use these methods for <strong>querying</strong>, not for <strong>insertion</strong>, to prevent possible data overwriting.</p>
</div>
</td>
</tr>
</table>
</div>
</div>
</div>
<div class="sect3">
<h4 id="datetime-functions"><a class="anchor" href="#datetime-functions"></a>Datetime functions</h4>
<div class="sect4">
<h5 id="retrieving-the-current-datetime"><a class="anchor" href="#retrieving-the-current-datetime"></a>Retrieving the current date/time</h5>
<div class="paragraph">
<p>The following functions can be used to retrieve the date/time at the
time where the function is invoked:</p>
</div>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 50%;">
<col style="width: 50%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Function name</th>
<th class="tableblock halign-left valign-top">Output type</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>current_timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timestamp</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>current_date</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>date</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>current_time</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>time</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>current_timeuuid</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timeUUID</code></p></td>
</tr>
</tbody>
</table>
<div class="paragraph">
<p>For example the last two days of data can be retrieved using:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT * FROM myTable WHERE date &gt;= current_date() - 2d;</code></pre>
</div>
</div>
</div>
<div class="sect4">
<h5 id="time-conversion-functions"><a class="anchor" href="#time-conversion-functions"></a>Time conversion functions</h5>
<div class="paragraph">
<p>A number of functions are provided to convert a <code>timeuuid</code>, a <code>timestamp</code> or a <code>date</code> into another <code>native</code> type.</p>
</div>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 33.3333%;">
<col style="width: 33.3333%;">
<col style="width: 33.3334%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Function name</th>
<th class="tableblock halign-left valign-top">Input type</th>
<th class="tableblock halign-left valign-top">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>to_date</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timeuuid</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Converts the <code>timeuuid</code> argument into a <code>date</code> type</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>to_date</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Converts the <code>timestamp</code> argument into a <code>date</code> type</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>to_timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timeuuid</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Converts the <code>timeuuid</code> argument into a <code>timestamp</code> type</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>to_timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>date</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Converts the <code>date</code> argument into a <code>timestamp</code> type</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>to_unix_timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timeuuid</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Converts the <code>timeuuid</code> argument into a <code>bigInt</code> raw value</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>to_unix_timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Converts the <code>timestamp</code> argument into a <code>bigInt</code> raw value</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>to_unix_timestamp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>date</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Converts the <code>date</code> argument into a <code>bigInt</code> raw value</p></td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="sect3">
<h4 id="blob-conversion-functions"><a class="anchor" href="#blob-conversion-functions"></a>Blob conversion functions</h4>
<div class="paragraph">
<p>A number of functions are provided to convert the native types into
binary data, or a <code>blob</code>.
For every <a href="types.html#native-types" class="page">type</a> supported by CQL, the function <code>type_as_blob</code> takes a argument of type <code>type</code> and returns it as a <code>blob</code>.
Conversely, the function <code>blob_as_type</code> takes a 64-bit <code>blob</code> argument and converts it to a <code>bigint</code> value.
For example, <code>bigint_as_blob(3)</code> returns <code>0x0000000000000003</code> and <code>blob_as_bigint(0x0000000000000003)</code> returns <code>3</code>.</p>
</div>
</div>
<div class="sect3">
<h4 id="math-functions"><a class="anchor" href="#math-functions"></a>Math Functions</h4>
<div class="paragraph">
<p>Cql provides the following math functions: <code>abs</code>, <code>exp</code>, <code>log</code>, <code>log10</code>, and <code>round</code>.
The return type for these functions is always the same as the input type.</p>
</div>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 50%;">
<col style="width: 50%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Function name</th>
<th class="tableblock halign-left valign-top">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>abs</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Returns the absolute value of the input.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>exp</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Returns the number e to the power of the input.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>log</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Returns the natural log of the input.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>log10</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Returns the log base 10 of the input.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>round</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Rounds the input to the nearest whole number using rounding mode <code>HALF_UP</code>.</p></td>
</tr>
</tbody>
</table>
</div>
<div class="sect3">
<h4 id="collection-functions"><a class="anchor" href="#collection-functions"></a>Collection functions</h4>
<div class="paragraph">
<p>A number of functions are provided to operate on collection columns.</p>
</div>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 33.3333%;">
<col style="width: 33.3333%;">
<col style="width: 33.3334%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Function name</th>
<th class="tableblock halign-left valign-top">Input type</th>
<th class="tableblock halign-left valign-top">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>map_keys</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>map</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Gets the keys of the <code>map</code> argument, returned as a <code>set</code>.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>map_values</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>map</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Gets the values of the <code>map</code> argument, returned as a <code>list</code>.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>collection_count</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>map</code>, <code>set</code> or <code>list</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Gets the number of elements in the collection argument.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>collection_min</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>set</code> or <code>list</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Gets the minimum element in the collection argument.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>collection_max</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>set</code> or <code>list</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Gets the maximum element in the collection argument.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>collection_sum</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">numeric <code>set</code> or <code>list</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Computes the sum of the elements in the collection argument. The returned value is of the same type as the input collection elements, so there is a risk of overflowing the data type if the sum of the values exceeds the maximum value that the type can represent.</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>collection_avg</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">numeric <code>set</code> or <code>list</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Computes the average of the elements in the collection argument. The average of an empty collection returns zero. The returned value is of the same type as the input collection elements, which might include rounding and truncations. For example <code>collection_avg([1, 2])</code> returns <code>1</code> instead of <code>1.5</code>.</p></td>
</tr>
</tbody>
</table>
</div>
<div class="sect3">
<h4 id="data-masking-functions"><a class="anchor" href="#data-masking-functions"></a>Data masking functions</h4>
<div class="paragraph">
<p>A number of functions allow to obscure the real contents of a column containing sensitive data.</p>
</div>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 50%;">
<col style="width: 50%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Function</th>
<th class="tableblock halign-left valign-top">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>mask_null(value)</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Replaces the first argument with a <code>null</code> column.
The returned value is always a non-existent column, and not a not-null column representing a <code>null</code> value.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>mask_null('Alice')</code> &#8594; <code>null</code></p>
<p class="tableblock"><code>mask_null(123)</code> &#8594; <code>null</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>mask_default(value)</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Replaces its argument by an arbitrary, fixed default value of the same type.
This will be <code><strong>*</strong>***</code> for text values, zero for numeric values, <code>false</code> for booleans, etc.</p>
<p class="tableblock">Variable-length multi-valued types such as lists, sets and maps are masked as empty collections.</p>
<p class="tableblock">Fixed-length multi-valued types such as tuples, user-defined types (UDTs) and vectors are masked by replacing each of their values by the default masking value of the value type.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>mask_default('Alice')</code> &#8594; <code>'****'</code></p>
<p class="tableblock"><code>mask_default(123)</code> &#8594; <code>0</code></p>
<p class="tableblock"><code>mask_default((list&lt;int&gt;) [1, 2, 3])</code> &#8594; <code>[]</code></p>
<p class="tableblock"><code>mask_default((vector&lt;int, 3&gt;) [1, 2, 3])</code> &#8594; <code>[0, 0, 0]</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>mask_replace(value, replacement])</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Replaces the first argument by the replacement value on the second argument.
The replacement value needs to have the same type as the replaced value.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>mask_replace('Alice', 'REDACTED')</code> &#8594; <code>'REDACTED'</code></p>
<p class="tableblock"><code>mask_replace(123, -1)</code> &#8594; <code>-1</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>mask_inner(value, begin, end, [padding])</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Returns a copy of the first <code>text</code>, <code>varchar</code> or <code>ascii</code> argument, replacing each character except the first and last ones by a padding character.
The second and third arguments are the size of the exposed prefix and suffix.
The optional fourth argument is the padding character, <code>\*</code> by default.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>mask_inner('Alice', 1, 2)</code> &#8594; <code>'A<strong>ce'</code></p>
<p class="tableblock"><code>mask_inner('Alice', 1, null)</code> &#8594; <code>'A</strong><strong>'</code></p>
<p class="tableblock"><code>mask_inner('Alice', null, 2)</code> &#8594; <code>'</strong>*ce'</code></p>
<p class="tableblock"><code>mask_inner('Alice', 2, 1, '#')</code> &#8594; <code>'Al##e'</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>mask_outer(value, begin, end, [padding])</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Returns a copy of the first <code>text</code>, <code>varchar</code> or <code>ascii</code> argument, replacing the first and last character by a padding character.
The second and third arguments are the size of the exposed prefix and suffix.
The optional fourth argument is the padding character, <code>\*</code> by default.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>mask_outer('Alice', 1, 2)</code> &#8594; <code>'*li<strong>'</code></p>
<p class="tableblock"><code>mask_outer('Alice', 1, null)</code> &#8594; <code>'*lice'</code></p>
<p class="tableblock"><code>mask_outer('Alice', null, 2)</code> &#8594; <code>'Ali</strong>'</code></p>
<p class="tableblock"><code>mask_outer('Alice', 2, 1, '#')</code> &#8594; <code>'##ic#'</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>mask_hash(value, [algorithm])</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Returns a <code>blob</code> containing the hash of the first argument.
The optional second argument is the hashing algorithm to be used, according the available Java security provider.
The default hashing algorithm is <code>SHA-256</code>.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>mask_hash('Alice')</code></p>
<p class="tableblock"><code>mask_hash('Alice', 'SHA-512')</code></p></td>
</tr>
</tbody>
</table>
<div class="sect4">
<h5 id="vector-similarity-functions"><a class="anchor" href="#vector-similarity-functions"></a>Vector similarity functions</h5>
<div class="paragraph">
<p>A number of functions allow to obtain the similarity score between vectors of floats.</p>
</div>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 50%;">
<col style="width: 50%;">
</colgroup>
<thead>
<tr>
<th class="tableblock halign-left valign-top">Function</th>
<th class="tableblock halign-left valign-top">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>similarity_cosine(vector, vector)</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Calculates the cosine similarity score between two float vectors of the same dimension.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>similarity_cosine([0.1, 0.2], null)</code> &#8594; <code>null</code></p>
<p class="tableblock"><code>similarity_cosine([0.1, 0.2], [0.1, 0.2])</code> &#8594; <code>1</code></p>
<p class="tableblock"><code>similarity_cosine([0.1, 0.2], [-0.1, -0.2])</code> &#8594; <code>0</code></p>
<p class="tableblock"><code>similarity_cosine([0.1, 0.2], [0.9, 0.8])</code> &#8594; <code>0.964238</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>similarity_euclidean(vector, vector)</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Calculates the euclidian distance between two float vectors of the same dimension.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>similarity_euclidean([0.1, 0.2], null)</code> &#8594; <code>null</code></p>
<p class="tableblock"><code>similarity_euclidean([0.1, 0.2], [0.1, 0.2])</code> &#8594; <code>1</code></p>
<p class="tableblock"><code>similarity_euclidean([0.1, 0.2], [-0.1, -0.2])</code> &#8594; <code>0.833333</code></p>
<p class="tableblock"><code>similarity_euclidean([0.1, 0.2], [0.9, 0.8])</code> &#8594; <code>0.5</code></p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><code>similarity_dot_product(vector, vector)</code></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Calculates the dot product between two float vectors of the same dimension.</p>
<p class="tableblock">Examples:</p>
<p class="tableblock"><code>similarity_dot_product([0.1, 0.2], null)</code> &#8594; <code>null</code></p>
<p class="tableblock"><code>similarity_dot_product([0.1, 0.2], [0.1, 0.2])</code> &#8594; <code>0.525</code></p>
<p class="tableblock"><code>similarity_dot_product([0.1, 0.2], [-0.1, -0.2])</code> &#8594; <code>0.475</code></p>
<p class="tableblock"><code>similarity_dot_product([0.1, 0.2], [0.9, 0.8])</code> &#8594; <code>0.625</code></p></td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class="sect2">
<h3 id="user-defined-scalar-functions"><a class="anchor" href="#user-defined-scalar-functions"></a>User-defined functions</h3>
<div class="paragraph">
<p>User-defined functions (UDFs) execute user-provided code in Cassandra.
By default, Cassandra supports defining functions in <em>Java</em>.</p>
</div>
<div class="paragraph">
<p>UDFs are part of the Cassandra schema, and are automatically propagated to all nodes in the cluster.
UDFs can be <em>overloaded</em>, so that multiple UDFs with different argument types can have the same function name.</p>
</div>
<div class="admonitionblock note">
<table>
<tr>
<td class="icon">
<i class="fa icon-note" title="Note"></i>
</td>
<td class="content">
<div class="paragraph">
<p><em>JavaScript</em> user-defined functions have been deprecated in Cassandra 4.1. In preparation for Cassandra 5.0, their removal is
already in progress. For more information - CASSANDRA-17281, CASSANDRA-18252.</p>
</div>
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">CREATE FUNCTION sample ( arg int ) ...;
CREATE FUNCTION sample ( arg text ) ...;</code></pre>
</div>
</div>
<div class="paragraph">
<p>UDFs are susceptible to all of the normal problems with the chosen programming language.
Accordingly, implementations should be safe against null pointer exceptions, illegal arguments, or any other potential source of exceptions.
An exception during function execution will result in the entire statement failing.
Valid queries for UDF use are <code>SELECT</code>, <code>INSERT</code> and <code>UPDATE</code> statements.</p>
</div>
<div class="paragraph">
<p><em>Complex</em> types like collections, tuple types and user-defined types are valid argument and return types in UDFs.
Tuple types and user-defined types use the DataStax Java Driver conversion functions.
Please see the Java Driver documentation for details on handling tuple types and user-defined types.</p>
</div>
<div class="paragraph">
<p>Arguments for functions can be literals or terms.
Prepared statement placeholders can be used, too.</p>
</div>
<div class="paragraph">
<p>Note the use the double dollar-sign syntax to enclose the UDF source code.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">CREATE FUNCTION some_function ( arg int )
RETURNS NULL ON NULL INPUT
RETURNS int
LANGUAGE java
AS $$ return arg; $$;
SELECT some_function(column) FROM atable ...;
UPDATE atable SET col = some_function(?) ...;
CREATE TYPE custom_type (txt text, i int);
CREATE FUNCTION fct_using_udt ( udtarg frozen )
RETURNS NULL ON NULL INPUT
RETURNS text
LANGUAGE java
AS $$ return udtarg.getString("txt"); $$;</code></pre>
</div>
</div>
<div class="paragraph">
<p>The implicitly available <code>udfContext</code> field (or binding for script UDFs) provides the necessary functionality to create new UDT and tuple values:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">CREATE TYPE custom_type (txt text, i int);
CREATE FUNCTION fct\_using\_udt ( somearg int )
RETURNS NULL ON NULL INPUT
RETURNS custom_type
LANGUAGE java
AS $$
UDTValue udt = udfContext.newReturnUDTValue();
udt.setString("txt", "some string");
udt.setInt("i", 42);
return udt;
$$;</code></pre>
</div>
</div>
<div class="paragraph">
<p>The definition of the <code>UDFContext</code> interface can be found in the Apache Cassandra source code for <code>org.apache.cassandra.cql3.functions.UDFContext</code>.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java hljs" data-lang="java">public interface UDFContext
{
UDTValue newArgUDTValue(String argName);
UDTValue newArgUDTValue(int argNum);
UDTValue newReturnUDTValue();
UDTValue newUDTValue(String udtName);
TupleValue newArgTupleValue(String argName);
TupleValue newArgTupleValue(int argNum);
TupleValue newReturnTupleValue();
TupleValue newTupleValue(String cqlDefinition);
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>Java UDFs already have some imports for common interfaces and classes defined. These imports are:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-java hljs" data-lang="java">import java.nio.ByteBuffer;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.cassandra.cql3.functions.UDFContext;
import com.datastax.driver.core.TypeCodec;
import com.datastax.driver.core.TupleValue;
import com.datastax.driver.core.UDTValue;</code></pre>
</div>
</div>
<div class="paragraph">
<p>Please note, that these convenience imports are not available for script UDFs.</p>
</div>
</div>
<div class="sect2">
<h3 id="create-function-statement"><a class="anchor" href="#create-function-statement"></a>CREATE FUNCTION statement</h3>
<div class="paragraph">
<p>Creating a new user-defined function uses the <code>CREATE FUNCTION</code> statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">create_function_statement::= CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS]
function_name '(' arguments_declaration ')'
[ CALLED | RETURNS NULL ] ON NULL INPUT
RETURNS cql_type
LANGUAGE identifier
AS string arguments_declaration: identifier cql_type ( ',' identifier cql_type )*</code></pre>
</div>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">CREATE OR REPLACE FUNCTION somefunction(somearg int, anotherarg text, complexarg frozen&lt;someUDT&gt;, listarg list)
RETURNS NULL ON NULL INPUT
RETURNS text
LANGUAGE java
AS $$
// some Java code
$$;
CREATE FUNCTION IF NOT EXISTS akeyspace.fname(someArg int)
CALLED ON NULL INPUT
RETURNS text
LANGUAGE java
AS $$
// some Java code
$$;</code></pre>
</div>
</div>
<div class="paragraph">
<p><code>CREATE FUNCTION</code> with the optional <code>OR REPLACE</code> keywords creates either a function or replaces an existing one with the same signature.
A <code>CREATE FUNCTION</code> without <code>OR REPLACE</code> fails if a function with the same signature already exists.
If the optional <code>IF NOT EXISTS</code> keywords are used, the function will only be created only if another function with the same signature does not
exist.
<code>OR REPLACE</code> and <code>IF NOT EXISTS</code> cannot be used together.</p>
</div>
<div class="paragraph">
<p>Behavior for <code>null</code> input values must be defined for each function:</p>
</div>
<div class="ulist">
<ul>
<li>
<p><code>RETURNS NULL ON NULL INPUT</code> declares that the function will always return <code>null</code> if any of the input arguments is <code>null</code>.</p>
</li>
<li>
<p><code>CALLED ON NULL INPUT</code> declares that the function will always be executed.</p>
</li>
</ul>
</div>
<div class="sect3">
<h4 id="function-signature"><a class="anchor" href="#function-signature"></a>Function Signature</h4>
<div class="paragraph">
<p>Signatures are used to distinguish individual functions. The signature consists of a fully-qualified function name of the &lt;keyspace&gt;.&lt;function_name&gt; and a concatenated list of all the argument types.</p>
</div>
<div class="paragraph">
<p>Note that keyspace names, function names and argument types are subject to the default naming conventions and case-sensitivity rules.</p>
</div>
<div class="paragraph">
<p>Functions belong to a keyspace; if no keyspace is specified, the current keyspace is used.
User-defined functions are not allowed in the system keyspaces.</p>
</div>
</div>
</div>
<div class="sect2">
<h3 id="drop-function-statement"><a class="anchor" href="#drop-function-statement"></a>DROP FUNCTION statement</h3>
<div class="paragraph">
<p>Dropping a function uses the <code>DROP FUNCTION</code> statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">drop_function_statement::= DROP FUNCTION [ IF EXISTS ] function_name [ '(' arguments_signature ')' ]
arguments_signature::= cql_type ( ',' cql_type )*</code></pre>
</div>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">DROP FUNCTION myfunction;
DROP FUNCTION mykeyspace.afunction;
DROP FUNCTION afunction ( int );
DROP FUNCTION afunction ( text );</code></pre>
</div>
</div>
<div class="paragraph">
<p>You must specify the argument types of the function, the arguments_signature, in the drop command if there are multiple overloaded functions with the same name but different signatures.
<code>DROP FUNCTION</code> with the optional <code>IF EXISTS</code> keywords drops a function if it exists, but does not throw an error if it doesn&#8217;t.</p>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="aggregate-functions"><a class="anchor" href="#aggregate-functions"></a>Aggregate functions</h2>
<div class="sectionbody">
<div class="paragraph">
<p>Aggregate functions work on a set of rows.
Values for each row are input, to return a single value for the set of rows aggregated.</p>
</div>
<div class="paragraph">
<p>If <code>normal</code> columns, <code>scalar functions</code>, <code>UDT</code> fields, <code>writetime</code>, or <code>ttl</code> are selected together with aggregate functions, the values
returned for them will be the ones of the first row matching the query.</p>
</div>
<div class="sect2">
<h3 id="native-aggregates"><a class="anchor" href="#native-aggregates"></a>Native aggregates</h3>
<div class="sect3">
<h4 id="count-function"><a class="anchor" href="#count-function"></a>Count</h4>
<div class="paragraph">
<p>The <code>count</code> function can be used to count the rows returned by a query.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT COUNT (*) FROM plays;
SELECT COUNT (1) FROM plays;</code></pre>
</div>
</div>
<div class="paragraph">
<p>It also can count the non-null values of a given column:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT COUNT (scores) FROM plays;</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="max-and-min"><a class="anchor" href="#max-and-min"></a>Max and Min</h4>
<div class="paragraph">
<p>The <code>max</code> and <code>min</code> functions compute the maximum and the minimum value returned by a query for a given column.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT MIN (players), MAX (players) FROM plays WHERE game = 'quake';</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="sum"><a class="anchor" href="#sum"></a>Sum</h4>
<div class="paragraph">
<p>The <code>sum</code> function sums up all the values returned by a query for a given column.</p>
</div>
<div class="paragraph">
<p>The returned value is of the same type as the input collection elements, so there is a risk of overflowing if the sum of the values exceeds the maximum value that the type can represent.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT SUM (players) FROM plays;</code></pre>
</div>
</div>
<div class="paragraph">
<p>The returned value is of the same type as the input values, so there is a risk of overflowing the type if the sum of the
values exceeds the maximum value that the type can represent. You can use type casting to cast the input values as a
type large enough to contain the type. For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT SUM (CAST (players AS VARINT)) FROM plays;</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="avg"><a class="anchor" href="#avg"></a>Avg</h4>
<div class="paragraph">
<p>The <code>avg</code> function computes the average of all the values returned by a query for a given column.</p>
</div>
<div class="paragraph">
<p>For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT AVG (players) FROM plays;</code></pre>
</div>
</div>
<div class="paragraph">
<p>The average of an empty collection returns zero.</p>
</div>
<div class="paragraph">
<p>The returned value is of the same type as the input values, which might include rounding and truncations.
For example <code>collection_avg([1, 2])</code> returns <code>1</code> instead of <code>1.5</code>.
You can use type casting to cast to a type with the desired decimal precision. For example:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">SELECT AVG (CAST (players AS FLOAT)) FROM plays;</code></pre>
</div>
</div>
</div>
</div>
<div class="sect2">
<h3 id="user-defined-aggregates-functions"><a class="anchor" href="#user-defined-aggregates-functions"></a>User-Defined Aggregates (UDAs)</h3>
<div class="paragraph">
<p>User-defined aggregates allow the creation of custom aggregate functions.
User-defined aggregates can be used in <code>SELECT</code> statement.</p>
</div>
<div class="paragraph">
<p>Each aggregate requires an <em>initial state</em> of type <code>STYPE</code> defined with the <code>INITCOND`value (default value: `null</code>).
The first argument of the state function must have type <code>STYPE</code>.
The remaining arguments of the state function must match the types of the user-defined aggregate arguments.
The state function is called once for each row, and the value returned by the state function becomes the new state.
After all rows are processed, the optional <code>FINALFUNC</code> is executed with last state value as its argument.</p>
</div>
<div class="paragraph">
<p>The <code>STYPE</code> value is mandatory in order to distinguish possibly overloaded versions of the state and/or final function, since the
overload can appear after creation of the aggregate.</p>
</div>
<div class="paragraph">
<p>A complete working example for user-defined aggregates (assuming that a
keyspace has been selected using the <code>USE</code> statement):</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">CREATE OR REPLACE FUNCTION test.averageState(state tuple&lt;int,bigint&gt;, val int)
CALLED ON NULL INPUT
RETURNS tuple
LANGUAGE java
AS $$
if (val != null) {
state.setInt(0, state.getInt(0)+1);
state.setLong(1, state.getLong(1)+val.intValue());
}
return state;
$$;
CREATE OR REPLACE FUNCTION test.averageFinal (state tuple&lt;int,bigint&gt;)
CALLED ON NULL INPUT
RETURNS double
LANGUAGE java
AS $$
double r = 0;
if (state.getInt(0) == 0) return null;
r = state.getLong(1);
r /= state.getInt(0);
return Double.valueOf(r);
$$;
CREATE OR REPLACE AGGREGATE test.average(int)
SFUNC averageState
STYPE tuple
FINALFUNC averageFinal
INITCOND (0, 0);
CREATE TABLE test.atable (
pk int PRIMARY KEY,
val int
);
INSERT INTO test.atable (pk, val) VALUES (1,1);
INSERT INTO test.atable (pk, val) VALUES (2,2);
INSERT INTO test.atable (pk, val) VALUES (3,3);
INSERT INTO test.atable (pk, val) VALUES (4,4);
SELECT test.average(val) FROM atable;</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="create-aggregate-statement"><a class="anchor" href="#create-aggregate-statement"></a>CREATE AGGREGATE statement</h3>
<div class="paragraph">
<p>Creating (or replacing) a user-defined aggregate function uses the
<code>CREATE AGGREGATE</code> statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">create_aggregate_statement ::= CREATE [ OR REPLACE ] AGGREGATE [ IF NOT EXISTS ]
function_name '(' arguments_signature')'
SFUNC function_name
STYPE cql_type:
[ FINALFUNC function_name]
[ INITCOND term ]</code></pre>
</div>
</div>
<div class="paragraph">
<p>See above for a complete example.</p>
</div>
<div class="paragraph">
<p>The <code>CREATE AGGREGATE</code> command with the optional <code>OR REPLACE</code> keywords creates either an aggregate or replaces an existing one with the same
signature.
A <code>CREATE AGGREGATE</code> without <code>OR REPLACE</code> fails if an aggregate with the same signature already exists.
The <code>CREATE AGGREGATE</code> command with the optional <code>IF NOT EXISTS</code> keywords creates an aggregate if it does not already exist.
The <code>OR REPLACE</code> and <code>IF NOT EXISTS</code> phrases cannot be used together.</p>
</div>
<div class="paragraph">
<p>The <code>STYPE</code> value defines the type of the state value and must be specified.
The optional <code>INITCOND</code> defines the initial state value for the aggregate; the default value is <code>null</code>.
A non-null <code>INITCOND</code> must be specified for state functions that are declared with <code>RETURNS NULL ON NULL INPUT</code>.</p>
</div>
<div class="paragraph">
<p>The <code>SFUNC</code> value references an existing function to use as the state-modifying function.
The first argument of the state function must have type <code>STYPE</code>.
The remaining arguments of the state function must match the types of the user-defined aggregate arguments.
The state function is called once for each row, and the value returned by the state function becomes the new state.
State is not updated for state functions declared with <code>RETURNS NULL ON NULL INPUT</code> and called with <code>null</code>.
After all rows are processed, the optional <code>FINALFUNC</code> is executed with last state value as its argument.
It must take only one argument with type <code>STYPE</code>, but the return type of the <code>FINALFUNC</code> may be a different type.
A final function declared with <code>RETURNS NULL ON NULL INPUT</code> means that the aggregate&#8217;s return value will be <code>null</code>, if the last state is <code>null</code>.</p>
</div>
<div class="paragraph">
<p>If no <code>FINALFUNC</code> is defined, the overall return type of the aggregate function is <code>STYPE</code>.
If a <code>FINALFUNC</code> is defined, it is the return type of that function.</p>
</div>
</div>
<div class="sect2">
<h3 id="drop-aggregate-statement"><a class="anchor" href="#drop-aggregate-statement"></a>DROP AGGREGATE statement</h3>
<div class="paragraph">
<p>Dropping an user-defined aggregate function uses the <code>DROP AGGREGATE</code>
statement:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bnf hljs" data-lang="bnf">drop_aggregate_statement::= DROP AGGREGATE [ IF EXISTS ] function_name[ '(' arguments_signature ')'
]</code></pre>
</div>
</div>
<div class="paragraph">
<p>For instance:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-cql hljs" data-lang="cql">DROP AGGREGATE myAggregate;
DROP AGGREGATE myKeyspace.anAggregate;
DROP AGGREGATE someAggregate ( int );
DROP AGGREGATE someAggregate ( text );</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <code>DROP AGGREGATE</code> statement removes an aggregate created using <code>CREATE AGGREGATE</code>.
You must specify the argument types of the aggregate to drop if there are multiple overloaded aggregates with the same name but a
different signature.</p>
</div>
<div class="paragraph">
<p>The <code>DROP AGGREGATE</code> command with the optional <code>IF EXISTS</code> keywords drops an aggregate if it exists, and does nothing if a function with the
signature does not exist.</p>
</div>
</div>
</div>
</div>
</article>
</main>
</div>
</div>
<footer class="grad grad--two flex-center pb-xlarge">
<div class="inner text-center z2 relative">
<h2 class="white py-small">Get started with Cassandra, fast.</h2>
<a id="footer-cta" href="/_/quickstart.html" class="btn btn--filled ma-medium">Quickstart Guide</a>
</div>
<div class="inner flex flex-distribute-items mt-xlarge z2 relative">
<div class="col-2">
<div id="footer-logo" class="logo logo--footer mb-medium"><img src="../../../../../assets/img/logo-white-r.png" alt="Cassandra Logo"></div>
<p>Apache Cassandra<img src="../../../../../assets/img/registered.svg" alt="®" style="width:18px;"> powers mission-critical deployments with improved performance and unparalleled levels of scale in the cloud.</p>
<div class="footer-social-icons">
<a href="https://twitter.com/cassandra?lang=en" target="_blank"><img src="../../../../../assets/img/twitter-icon-circle-white.svg" alt="twitter icon" width="24"></a>
<a href="https://www.linkedin.com/company/apache-cassandra/" target="_blank"><img src="../../../../../assets/img/LI-In-Bug.png" alt="linked-in icon" width="24"></a>
<a href="https://www.youtube.com/c/PlanetCassandra" target="_blank"><img src="../../../../../assets/img/youtube-icon.png" alt="youtube icon" width="24"></a>
</div>
</div>
<div class="col-2 flex flex-center">
<ul class="columns-2">
<li class="mb-small"><a href="/">Home</a></li>
<li class="mb-small"><a href="/_/cassandra-basics.html">Cassandra Basics</a></li>
<li class="mb-small"><a href="/_/quickstart.html">Quickstart</a></li>
<li class="mb-small"><a href="/_/ecosystem.html">Ecosystem</a></li>
<li class="mb-small"><a href="/doc/latest/">Documentation</a></li>
<li class="mb-small"><a href="/_/community.html">Community</a></li>
<li class="mb-small"><a href="/_/case-studies.html">Case Studies</a></li>
<li class="mb-small"><a href="/_/resources.html">Resources</a></li>
<li class="mb-small"><a href="/_/blog.html">Blog</a></li>
</ul>
</div>
</div>
</footer>
<div class="lower-footer bg-white pa-medium">
<div class="flex flex-row flex-vert-center">
<div class="pr-medium"><img src="../../../../../assets/img//feather-small.png" alt="ASF" width="20"></div>
<div class="pr-medium"><a href="http://www.apache.org/" target="_blank">Foundation</a></div>
<div class="pr-medium"><a href="https://www.apache.org/events/current-event.html" target="_blank">Events</a></div>
<div class="pr-medium"><a href="https://www.apache.org/licenses/" target="_blank">License</a></div>
<div class="pr-medium"><a href="https://www.apache.org/foundation/thanks" target="_blank">Thanks</a></div>
<div class="pr-medium"><a href="https://www.apache.org/security" target="_blank">Security</a></div>
<div class="pr-medium"><a href="https://privacy.apache.org/policies/privacy-policy-public.html" target="_blank">Privacy</a></div>
<div class="pr-medium"><a href="https://www.apache.org/foundation/sponsorship" target="_blank">Sponsorship</a></div>
</div>
<p class="my-medium">© 2009-<script>document.write(new Date().getFullYear())</script> <a href="https://apache.org" target="_blank">The Apache Software Foundation</a> under the terms of the Apache License 2.0. Apache, the Apache feather logo, Apache Cassandra, Cassandra, and the Cassandra logo, are either registered trademarks or trademarks of The Apache Software Foundation.</p>
</div>
<div id="fade" class="hidden"></div>
<div id="modal" class="hidden">
<div id="close-modal" class="cursor-pointer"><svg viewBox="0 0 24 24" width="24" height="24" stroke="currentColor" stroke-width="2" fill="none" stroke-linecap="round" stroke-linejoin="round" class="css-i6dzq1"><line x1="18" y1="6" x2="6" y2="18"></line><line x1="6" y1="6" x2="18" y2="18"></line></svg></div>
<div id="mod-content" class="vid-mod-content resp-container"></div>
</div>
<script src="../../../../../assets/js/site.js"></script>
<script async src="../../../../../assets/js/vendor/highlight.js"></script>
<script src="../../../../../assets/js/vendor/lunr.js"></script>
<script src="../../../../../assets/js/vendor/search.js" id="search-script" data-base-path="../../../../.." data-page-path="/Cassandra/5.0/cassandra/developing/cql/functions.html"></script>
<script async src="../../../../../assets/../search-index.js"></script>
<script>
jQuery(function(){
var windowW = $(window).width();
$(document)
.on('click','.mobile-nav-icon',function(){
$('.main-nav').fadeIn();
})
.on('click','.main-nav',function(){
if(windowW <= 1000){
$(this).fadeOut();
}
})
.on('click','#version-toggle',function(){
$(this).toggleClass('active');
$(this).next().fadeToggle();
})
.on('click','#mobile-docs-nav-burger', function(){
$(this).toggleClass('active');
$('.docs-nav').toggleClass('active');
});
var url = window.location.pathname;
var isQuickstart = url.includes('quickstart.html');
if(isQuickstart){
var footerCTA = document.getElementById('footer-cta');
footerCTA.innerHTML = 'Get latest updates';
footerCTA.setAttribute('href', '/_/blog.html');
}
});
</script>
</div>
</body>
</html>