| <!doctype html> |
| <html lang="en" dir="ltr"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="viewport" content="width=device-width,initial-scale=1"> |
| <meta name="generator" content="Docusaurus v2.0.0-beta.1"> |
| <link rel="alternate" type="application/rss+xml" href="/blog/rss.xml" title="Apache Pinot™ Blog RSS Feed"> |
| <link rel="alternate" type="application/atom+xml" href="/blog/atom.xml" title="Apache Pinot™ Blog Atom Feed"> |
| <link rel="preconnect" href="https://www.google-analytics.com"> |
| <script>window.ga=window.ga||function(){(ga.q=ga.q||[]).push(arguments)},ga.l=+new Date,ga("create","UA-157446650-1","auto"),ga("send","pageview")</script> |
| <script async src="https://www.google-analytics.com/analytics.js"></script> |
| <link rel="search" type="application/opensearchdescription+xml" title="Apache Pinot™" href="/opensearch.xml"> |
| <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Ubuntu|Roboto|Source+Code+Pro"> |
| <link rel="stylesheet" href="https://at-ui.github.io/feather-font/css/iconfont.css"><title data-react-helmet="true">PQL | Apache Pinot™</title><meta data-react-helmet="true" property="og:url" content="https://pinot.apache.org/docs/user-guide/pql"><meta data-react-helmet="true" name="docsearch:language" content="en"><meta data-react-helmet="true" name="docsearch:version" content="current"><meta data-react-helmet="true" name="docsearch:docusaurus_tag" content="docs-default-current"><meta data-react-helmet="true" property="og:title" content="PQL | Apache Pinot™"><meta data-react-helmet="true" name="description" content="Pinot Query Language"><meta data-react-helmet="true" property="og:description" content="Pinot Query Language"><link data-react-helmet="true" rel="shortcut icon" href="/img/favicon.ico"><link data-react-helmet="true" rel="canonical" href="https://pinot.apache.org/docs/user-guide/pql"><link data-react-helmet="true" rel="alternate" href="https://pinot.apache.org/docs/user-guide/pql" hreflang="en"><link data-react-helmet="true" rel="alternate" href="https://pinot.apache.org/docs/user-guide/pql" hreflang="x-default"><link data-react-helmet="true" rel="preconnect" href="https://BH4D9OD16A-dsn.algolia.net" crossorigin="anonymous"><link rel="stylesheet" href="/assets/css/styles.f0793de6.css"> |
| <link rel="preload" href="/assets/js/runtime~main.82dd65bd.js" as="script"> |
| <link rel="preload" href="/assets/js/main.9b2ca7ec.js" as="script"> |
| </head> |
| <body> |
| <script>!function(){function e(e){document.documentElement.setAttribute("data-theme",e)}var t=function(){var e=null;try{e=localStorage.getItem("theme")}catch(e){}return e}();null!==t?e(t):window.matchMedia("(prefers-color-scheme: dark)").matches?e("dark"):window.matchMedia("(prefers-color-scheme: light)").matches?e("light"):e("dark")}()</script><div id="__docusaurus"> |
| <div><a href="#main" class="skipToContent_OuoZ shadow--md">Skip to main content</a></div><nav class="navbar navbar--fixed-top navbarHideable_RReh"><div class="navbar__inner"><div class="navbar__items"><button aria-label="Navigation bar toggle" class="navbar__toggle clean-btn" type="button" tabindex="0"><svg width="30" height="30" viewBox="0 0 30 30" aria-hidden="true"><path stroke="currentColor" stroke-linecap="round" stroke-miterlimit="10" stroke-width="2" d="M4 7h22M4 15h22M4 23h22"></path></svg></button><a class="navbar__brand" href="/"><img src="/img/pinot-navbar-logo-722f37.svg" alt="Pinot" class="themedImage_TMUO themedImage--light_4Vu1 navbar__logo"><img src="/img/pinot-navbar-logo-722f37.svg" alt="Pinot" class="themedImage_TMUO themedImage--dark_uzRr navbar__logo"></a></div><div class="navbar__items navbar__items--right"><a href="https://docs.pinot.apache.org/" target="_blank" rel="noopener noreferrer" class="navbar__item navbar__link">Docs</a><a class="navbar__item navbar__link" href="/download">Download</a><a class="navbar__item navbar__link" href="/blog">Blog</a><a href="https://github.com/apache/pinot" target="_blank" rel="noopener noreferrer" class="navbar__item navbar__link"><span>GitHub<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_wgqa"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></span></a><div class="react-toggle displayOnlyInLargeViewport_cxYs react-toggle--checked react-toggle--disabled"><div class="react-toggle-track" role="button" tabindex="-1"><div class="react-toggle-track-check"><span class="toggle_iYfV" style="margin-left:2px">🌙</span></div><div class="react-toggle-track-x"><span class="toggle_iYfV" style="margin-left:2px">☀️</span></div><div class="react-toggle-thumb"></div></div><input type="checkbox" checked="" class="react-toggle-screenreader-only" aria-label="Switch between dark and light mode"></div><div class="searchBox_NKBi"><button type="button" class="DocSearch DocSearch-Button" aria-label="Search"><span class="DocSearch-Button-Container"><svg width="20" height="20" class="DocSearch-Search-Icon" viewBox="0 0 20 20"><path d="M14.386 14.386l4.0877 4.0877-4.0877-4.0877c-2.9418 2.9419-7.7115 2.9419-10.6533 0-2.9419-2.9418-2.9419-7.7115 0-10.6533 2.9418-2.9419 7.7115-2.9419 10.6533 0 2.9419 2.9418 2.9419 7.7115 0 10.6533z" stroke="currentColor" fill="none" fill-rule="evenodd" stroke-linecap="round" stroke-linejoin="round"></path></svg><span class="DocSearch-Button-Placeholder">Search</span></span><span class="DocSearch-Button-Keys"></span></button></div></div></div><div role="presentation" class="navbar-sidebar__backdrop"></div><div class="navbar-sidebar"><div class="navbar-sidebar__brand"><a class="navbar__brand" href="/"><img src="/img/pinot-navbar-logo-722f37.svg" alt="Pinot" class="themedImage_TMUO themedImage--light_4Vu1 navbar__logo"><img src="/img/pinot-navbar-logo-722f37.svg" alt="Pinot" class="themedImage_TMUO themedImage--dark_uzRr navbar__logo"></a></div><div class="navbar-sidebar__items"><div class="menu"><ul class="menu__list"><li class="menu__list-item"><a href="https://docs.pinot.apache.org/" target="_blank" rel="noopener noreferrer" class="menu__link">Docs</a></li><li class="menu__list-item"><a class="menu__link" href="/download">Download</a></li><li class="menu__list-item"><a class="menu__link" href="/blog">Blog</a></li><li class="menu__list-item"><a href="https://github.com/apache/pinot" target="_blank" rel="noopener noreferrer" class="menu__link"><span>GitHub<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_wgqa"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></span></a></li></ul></div></div></div></nav><div class="main-wrapper docs-wrapper doc-page"><div class="docPage_lDyR"><main class="docMainContainer_r8cw docMainContainerEnhanced_SOUu"><div class="container padding-top--md padding-bottom--lg docItemWrapper_NJLN"><div class="row"><div class="col docItemCol_zHA2"><div class="docItemContainer_oiyr"><article><div class="markdown"><header><h1 class="h1Heading_dC7a">PQL</h1></header><p>PQL is a derivative of SQL derivative that supports selection, projection, aggregation, grouping aggregation. There is no support for Joins or Subqueries. Specifically, for Pinot:</p><ul><li>Aggregations are computed in parallel</li><li>Results of aggregations with large amounts of group keys (>1M) are approximated</li></ul><h2><a aria-hidden="true" tabindex="-1" class="anchor" id="pql-limitations"></a>PQL Limitations<a class="hash-link" href="#pql-limitations" title="Direct link to heading">#</a></h2><p>PQL is only a derivative of SQL, and it does not support Joins nor Subqueries. In order to support them, we suggest to rely on <a href="https://trino.io/" target="_blank" rel="noopener noreferrer">Trino</a> or <a href="https://prestodb.io/" target="_blank" rel="noopener noreferrer">PrestoDB</a>, although Subqueries are not completely supported by PrestoDB at the moment of writing.</p><h2><a aria-hidden="true" tabindex="-1" class="anchor" id="pql-examples"></a>PQL Examples<a class="hash-link" href="#pql-examples" title="Direct link to heading">#</a></h2><p>The Pinot Query Language (PQL) is very similar to standard SQL:</p><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">COUNT</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token operator">*</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="aggregation"></a>Aggregation<a class="hash-link" href="#aggregation" title="Direct link to heading">#</a></h3><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">COUNT</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token operator">*</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">SUM</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">bar</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="grouping-on-aggregation"></a>Grouping on Aggregation<a class="hash-link" href="#grouping-on-aggregation" title="Direct link to heading">#</a></h3><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MIN</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">SUM</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">AVG</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> baz </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">LIMIT</span><span class="token plain"> </span><span class="token number">50</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="ordering-on-aggregation"></a>Ordering on Aggregation<a class="hash-link" href="#ordering-on-aggregation" title="Direct link to heading">#</a></h3><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MIN</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">SUM</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">AVG</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> baz</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">DESC</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">LIMIT</span><span class="token plain"> </span><span class="token number">50</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="filtering"></a>Filtering<a class="hash-link" href="#filtering" title="Direct link to heading">#</a></h3><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">COUNT</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token operator">*</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">WHERE</span><span class="token plain"> foo </span><span class="token operator">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">'foo'</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token operator">AND</span><span class="token plain"> bar </span><span class="token operator">BETWEEN</span><span class="token plain"> </span><span class="token number">1</span><span class="token plain"> </span><span class="token operator">AND</span><span class="token plain"> </span><span class="token number">20</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token operator">OR</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">baz </span><span class="token operator"><</span><span class="token plain"> </span><span class="token number">42</span><span class="token plain"> </span><span class="token operator">AND</span><span class="token plain"> quux </span><span class="token operator">IN</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token string" style="color:rgb(255, 121, 198)">'hello'</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">'goodbye'</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token operator">AND</span><span class="token plain"> quuux </span><span class="token operator">NOT</span><span class="token plain"> </span><span class="token operator">IN</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token number">42</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token number">69</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="selection-projection"></a>Selection (Projection)<a class="hash-link" href="#selection-projection" title="Direct link to heading">#</a></h3><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator">*</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">WHERE</span><span class="token plain"> quux </span><span class="token operator"><</span><span class="token plain"> </span><span class="token number">5</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">LIMIT</span><span class="token plain"> </span><span class="token number">50</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="ordering-on-selection"></a>Ordering on Selection<a class="hash-link" href="#ordering-on-selection" title="Direct link to heading">#</a></h3><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> bar </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">WHERE</span><span class="token plain"> baz </span><span class="token operator">></span><span class="token plain"> </span><span class="token number">20</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">DESC</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">LIMIT</span><span class="token plain"> </span><span class="token number">100</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="pagination-on-selection"></a>Pagination on Selection<a class="hash-link" href="#pagination-on-selection" title="Direct link to heading">#</a></h3><p>Note: results might not be consistent if column ordered by has same value in multiple rows.</p><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> bar </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">WHERE</span><span class="token plain"> baz </span><span class="token operator">></span><span class="token plain"> </span><span class="token number">20</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">DESC</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">LIMIT</span><span class="token plain"> </span><span class="token number">50</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token number">100</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="wild-card-match-in-where-clause-only"></a>Wild-card match (in WHERE clause only)<a class="hash-link" href="#wild-card-match-in-where-clause-only" title="Direct link to heading">#</a></h3><p>To count rows where the column <code>airlineName</code> starts with U</p><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">count</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token operator">*</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> SomeTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">WHERE</span><span class="token plain"> regexp_like</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">airlineName</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">'^U.*'</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> airlineName </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">TOP</span><span class="token plain"> </span><span class="token number">10</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="udf"></a>UDF<a class="hash-link" href="#udf" title="Direct link to heading">#</a></h3><p>As of now, functions have to be implemented within Pinot. Injecting functions is not allowed yet. The example below demonstrate the use of UDFs. More examples in Transform Function in Aggregation Grouping</p><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">count</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token operator">*</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> timeConvert</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">timeColumnName</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">'SECONDS'</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">'DAYS'</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="bytes-column"></a>BYTES column<a class="hash-link" href="#bytes-column" title="Direct link to heading">#</a></h3><p>Pinot supports queries on BYTES column using HEX string. The query response also uses hex string to represent bytes value. |
| E.g. the query below fetches all the rows for a given UID.</p><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator">*</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">WHERE</span><span class="token plain"> UID </span><span class="token operator">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">"c8b3bce0b378fc5ce8067fc271a34892"</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h2><a aria-hidden="true" tabindex="-1" class="anchor" id="pql-specification"></a>PQL Specification<a class="hash-link" href="#pql-specification" title="Direct link to heading">#</a></h2><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="select"></a>SELECT<a class="hash-link" href="#select" title="Direct link to heading">#</a></h3><p>The select statement is as follows</p><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator"><</span><span class="token plain">outputColumn</span><span class="token operator">></span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> outputColumn</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> outputColumn</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> </span><span class="token operator"><</span><span class="token plain">tableName</span><span class="token operator">></span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">WHERE</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token plain"> </span><span class="token operator">|</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token plain"> </span><span class="token operator">|</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">ORDER</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token plain"> </span><span class="token operator">|</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">TOP</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token plain"> </span><span class="token operator">|</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">LIMIT</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">.</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><p><code>outputColumn</code> can be <code>*</code> to project all columns, columns (<code>foo</code>, <code>bar</code>, <code>baz</code>) or aggregation functions like (<code>MIN(foo), MAX(bar), AVG(baz)</code>).</p><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="supported-aggregations-on-single-value-columns"></a>Supported aggregations on single-value columns<a class="hash-link" href="#supported-aggregations-on-single-value-columns" title="Direct link to heading">#</a></h3><ul><li>COUNT</li><li>MIN</li><li>MAX</li><li>SUM</li><li>AVG</li><li>MINMAXRANGE</li><li>DISTINCT</li><li>DISTINCTCOUNT</li><li>DISTINCTCOUNTHLL</li><li>DISTINCTCOUNTRAWHLL: Returns HLL response serialized as string. The serialized HLL can be converted back into an HLL (see pinot-core/**/HllUtil.java as an example) and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching. |
| FASTHLL (WARN: will be deprecated soon. FASTHLL stores serialized HyperLogLog in String format, which performs worse than DISTINCTCOUNTHLL, which supports serialized HyperLogLog in BYTES (byte array) format)</li><li>PERCENTILE[0-100]: e.g. PERCENTILE5, PERCENTILE50, PERCENTILE99, etc.</li><li>PERCENTILEEST[0-100]: e.g. PERCENTILEEST5, PERCENTILEEST50, PERCENTILEEST99, etc.</li></ul><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="supported-aggregations-on-multi-value-columns"></a>Supported aggregations on multi-value columns<a class="hash-link" href="#supported-aggregations-on-multi-value-columns" title="Direct link to heading">#</a></h3><ul><li>COUNTMV</li><li>MINMV</li><li>MAXMV</li><li>SUMMV</li><li>AVGMV</li><li>MINMAXRANGEMV</li><li>DISTINCTCOUNTMV</li><li>DISTINCTCOUNTHLLMV</li><li>DISTINCTCOUNTRAWHLLMV: Returns HLL response serialized as string. The serialized HLL can be converted back into an HLL (see pinot-core/**/HllUtil.java as an example) and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching.</li><li>FASTHLLMV (WARN: will be deprecated soon. It does not make lots of sense to configure serialized HyperLogLog column as a dimension)</li><li>PERCENTILE[0-100]MV: e.g. PERCENTILE5MV, PERCENTILE50MV, PERCENTILE99MV, etc.</li><li>PERCENTILEEST[0-100]MV: e.g. PERCENTILEEST5MV, PERCENTILEEST50MV, PERCENTILEEST99MV, etc.</li></ul><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="where"></a>WHERE<a class="hash-link" href="#where" title="Direct link to heading">#</a></h3><p>Supported predicates are comparisons with a constant using the standard SQL operators (<code>=, <, <=, >, >=, <>, '!='</code>) , range comparisons using <code>BETWEEN (foo BETWEEN 42 AND 69)</code>, set membership (<code>foo IN (1, 2, 4, 8)</code>) and exclusion (foo NOT IN (1, 2, 4, 8)). For <code>BETWEEN</code>, the range is inclusive.</p><p>Comparison with a regular expression is supported using the regexp_like function, as in <code>WHERE regexp_like(columnName, 'regular expression')</code></p><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="group-by"></a>GROUP BY<a class="hash-link" href="#group-by" title="Direct link to heading">#</a></h3><p>The GROUP BY clause groups aggregation results by a list of columns, or transform functions on columns (see below)</p><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="order-by"></a>ORDER BY<a class="hash-link" href="#order-by" title="Direct link to heading">#</a></h3><p>The ORDER BY clause orders selection results or group by results by a list of columns. PQL supports ordering DESC or ASC.</p><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="top"></a>TOP<a class="hash-link" href="#top" title="Direct link to heading">#</a></h3><p>The TOP n clause causes the 'n' largest group results to be returned. If not specified, the top 10 groups are returned.</p><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="limit"></a>LIMIT<a class="hash-link" href="#limit" title="Direct link to heading">#</a></h3><p>The LIMIT n clause causes the selection results to contain at most 'n' results. The LIMIT a, b clause paginate the selection results from the 'a' th results and return at most 'b' results.</p><h2><a aria-hidden="true" tabindex="-1" class="anchor" id="transform-function-in-aggregation-and-grouping"></a>Transform Function in Aggregation and Grouping<a class="hash-link" href="#transform-function-in-aggregation-and-grouping" title="Direct link to heading">#</a></h2><p>In aggregation and grouping, each column can be transformed from one or multiple columns. For example, the following query will calculate the maximum value of column foo divided by column bar grouping on the column time converted from time unit MILLISECONDS to SECONDS:</p><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token operator">DIV</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> bar</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> TIMECONVERT</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">time</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">'MILLISECONDS'</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">'SECONDS'</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><h3><a aria-hidden="true" tabindex="-1" class="anchor" id="supported-transform-functions"></a>Supported transform functions<a class="hash-link" href="#supported-transform-functions" title="Direct link to heading">#</a></h3><table><thead><tr><th>Functions</th><th>Decription</th></tr></thead><tbody><tr><td>ADD</td><td>Sum of at least two values</td></tr><tr><td>SUB</td><td>Difference between two values</td></tr><tr><td>MULT</td><td>Product of at least two values</td></tr><tr><td>DIV</td><td>Quotient of two values</td></tr><tr><td>TIMECONVERT</td><td>Takes 3 arguments, converts the value into another time unit. <br> Examples - <code>TIMECONVERT(time, 'MILLISECONDS', 'SECONDS')</code> - This expression converts the value of column <code>time</code> (taken to be in milliseconds) to the nearest seconds (i.e. the nearest seconds that is lower than the value of <code>date</code> column) <br> <br> Takes 4 arguments, converts the value into another date time format, and buckets time based on the given time granularity. <br> <br> <code>DATETIMECONVERT(columnName, inputFormat, outputFormat, outputGranularity)</code> where: <br><code>columnName</code> - column name to convert <br> <code>inputFormat</code> - format of the column columnName <br> <code>outputFormat</code> - format of the result desired after conversion <br> <code>outputGranularity</code> - the granularity in which to bucket the result</td></tr><tr><td>DATETIMECONVERT</td><td>Format is expressed as <code><time size>:<time unit>:<time format>:<pattern></code> <br> where: <br> <code>time size</code> - size of the time unit eg: 1, 10 <br> <code>time unit</code> - HOURS, DAYS etc <br> <code>time format</code> - EPOCH or SIMPLE_DATE_FORMAT <br> <code>pattern</code> - this is defined in case of SIMPLE_DATE_FORMAT. eg: yyyyMMdd. A specific timezone can be passed using tz(timezone) <br> <code>timezone</code> - can be expressed as long form tz(Asia/Kolkata), or short form tz(IST) or in terms of GMT tz(GMT+0530). Default is UTC. It is recommended to use long form timezone, as short forms are ambiguous with daylight savings (eg: PDT works during daylight savings, PST otherwise) <br> <br> Granularity is expressed as <code><time size>:<time unit></code> <br> <strong>Examples</strong> <br> 1. To convert column <code>Date</code> from <code>hoursSinceEpoch</code> to <code>daysSinceEpoch</code> and bucket it to 1 day granularity <br> 2. To simply bucket millis "Date" to 15 minutes <code>granularity dateTimeConvert(Date, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '15:MINUTES')</code> <br> 3. To convert column "Date" from hoursSinceEpoch to format yyyyMdd and bucket it to 1 days granularity <code>dateTimeConvert(Date, '1:HOURS:EPOCH', '1:DAYS:SIMPLE_DATE_FORMAT:yyyyMMdd', '1:DAYS')</code> <br> 4. To convert column "Date" from format yyyy/MM/dd to weeksSinceEpoch and bucket it to 1 weeks granularity <code>dateTimeConvert(Date, '1:DAYS:SIMPLE_DATE_FORMAT:yyyy/MM/dd', '1:WEEKS:EPOCH', '1:WEEKS')</code> <br> 5. To convert column "Date" from millis to format yyyyMdd in timezone PST <code>dateTimeConvert(Date, '1:MILLISECONDS:EPOCH', '1:DAYS:SIMPLE_DATE_FORMAT:yyyyMMdd tz(America/Los_Angeles)', '1:DAYS')</code></td></tr><tr><td>DATETRUNC</td><td>(Presto) SQL compatible date truncation, equivalent to the Presto function date_trunc. Takes at least 3 and upto 5 arguments, converts the value into a specified output granularity seconds since UTC epoch that is bucketed on a unit in a specified timezone. Examples DATETRUNC('week', time_in_seconds, 'SECONDS') This expression converts the column time_in_seconds, which is a long containing seconds since UTC epoch truncated at WEEK (where a Week starts at Monday UTC midnight). The output is a long seconds since UTC epoch. <br> <code>DATETRUNC('quarter', DIV(time_milliseconds/1000), 'SECONDS', 'America/Los_Angeles', 'HOURS')</code> This expression converts the expression <code>time_in_milliseconds/1000</code> (which is thus in seconds) into hours that are truncated at <code>QUARTER</code> at the Los Angeles time zone (where a Quarter begins on 1/1, 4/1, 7/1, 10/1 in Los Angeles timezone). The output is expressed as hours since UTC epoch (note that the output is not Los Angeles timezone)</td></tr><tr><td>VALUEIN</td><td>Takes at least 2 arguments, where the first argument is a multi-valued column, and the following arguments are constant values. The transform function will filter the value from the multi-valued column with the given constant values. The <code>VALUEIN</code> transform function is especially useful when the same multi-valued column is both filtering column and grouping column. <br><br> Examples <br> <code>VALUEIN(mvColumn, 3, 5, 15)</code></td></tr></tbody></table><h2><a aria-hidden="true" tabindex="-1" class="anchor" id="differences-with-sql"></a>Differences with SQL<a class="hash-link" href="#differences-with-sql" title="Direct link to heading">#</a></h2><div class="alert alert--info alert--icon" role="alert"><i class="feather icon-info"></i> These differences only apply to the PQL endpoint. They do not hold true for the standard-SQL endpoint, which is the recommended endpoint. <br>More information about the two types of endpoints in Querying Pinot.. </div><ul><li>TOP works like LIMIT for truncation in group by queries</li><li>No need to select the columns to group with. |
| The following two queries are both supported in PQL, where the non-aggregation columns are ignored.</li></ul><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MIN</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">SUM</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">AVG</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> mytable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> baz</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">TOP</span><span class="token plain"> </span><span class="token number">50</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"> |
| </span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"></span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> bar</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> baz</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MIN</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">SUM</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">AVG</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> mytable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> baz</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">TOP</span><span class="token plain"> </span><span class="token number">50</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><ul><li>The results will always order by the aggregated value (descending). |
| The results for query</li></ul><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MIN</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">TOP</span><span class="token plain"> </span><span class="token number">50</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><p>will be the same as the combining results from the following queries</p><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MIN</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">TOP</span><span class="token plain"> </span><span class="token number">50</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"></span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(80, 250, 123)">MAX</span><span class="token punctuation" style="color:rgb(248, 248, 242)">(</span><span class="token plain">foo</span><span class="token punctuation" style="color:rgb(248, 248, 242)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">FROM</span><span class="token plain"> myTable</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">BY</span><span class="token plain"> bar</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token keyword" style="color:rgb(189, 147, 249);font-style:italic">TOP</span><span class="token plain"> </span><span class="token number">50</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><p>where we don't put the results for the same group together.</p><ul><li>No support for <strong>ORDER BY</strong> in aggregation group by. However, ORDER BY support was added recently and is available in the standard-SQL endpoint. It can be used in the PQL endpoint by passing <code>queryOptions</code> into the payload as follows</li></ul><div class="codeBlockContainer_J+bg"><div class="codeBlockContent_csEI sql"><pre tabindex="0" class="prism-code language-sql codeBlock_rtdJ thin-scrollbar" style="color:#F8F8F2;background-color:#282A36"><code class="codeBlockLines_1zSZ"><span class="token-line" style="color:#F8F8F2"><span class="token plain">{</span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">"pql"</span><span class="token plain"> : </span><span class="token string" style="color:rgb(255, 121, 198)">"SELECT SUM(foo), SUM(bar) from myTable GROUP BY moo ORDER BY SUM(bar) ASC, moo DESC TOP 10"</span><span class="token punctuation" style="color:rgb(248, 248, 242)">,</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><span class="token string" style="color:rgb(255, 121, 198)">"queryOptions"</span><span class="token plain"> : </span><span class="token string" style="color:rgb(255, 121, 198)">"groupByMode=sql;responseFormat=sql"</span><span class="token plain"></span></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">}</span></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_M3SB clean-btn">Copy</button></div></div><p>where:</p><ul><li><code>groupByMode=sql</code> - standard sql way of execution group by, hence accepting order by</li><li><code>responseFormat=sql</code> - standard sql way of displaying results, in a tabular manner</li></ul></div><footer class="row docusaurus-mt-lg"><div class="col"><a href="https://github.com/apache/pinot/edit/master/website/docs/user-guide/pql.md" target="_blank" rel="noreferrer noopener"><svg fill="currentColor" height="20" width="20" viewBox="0 0 40 40" class="iconEdit_mS5F" aria-hidden="true"><g><path d="m34.5 11.7l-3 3.1-6.3-6.3 3.1-3q0.5-0.5 1.2-0.5t1.1 0.5l3.9 3.9q0.5 0.4 0.5 1.1t-0.5 1.2z m-29.5 17.1l18.4-18.5 6.3 6.3-18.4 18.4h-6.3v-6.2z"></path></g></svg>Edit this page</a></div><div class="col lastUpdated_wj+Z"></div></footer></article><nav class="pagination-nav docusaurus-mt-lg" aria-label="Docs pages navigation"><div class="pagination-nav__item"></div><div class="pagination-nav__item pagination-nav__item--next"></div></nav></div></div><div class="col col--3"><div class="tableOfContents_vrFS thin-scrollbar"><ul class="table-of-contents table-of-contents__left-border"><li><a href="#pql-limitations" class="table-of-contents__link">PQL Limitations</a></li><li><a href="#pql-examples" class="table-of-contents__link">PQL Examples</a><ul><li><a href="#aggregation" class="table-of-contents__link">Aggregation</a></li><li><a href="#grouping-on-aggregation" class="table-of-contents__link">Grouping on Aggregation</a></li><li><a href="#ordering-on-aggregation" class="table-of-contents__link">Ordering on Aggregation</a></li><li><a href="#filtering" class="table-of-contents__link">Filtering</a></li><li><a href="#selection-projection" class="table-of-contents__link">Selection (Projection)</a></li><li><a href="#ordering-on-selection" class="table-of-contents__link">Ordering on Selection</a></li><li><a href="#pagination-on-selection" class="table-of-contents__link">Pagination on Selection</a></li><li><a href="#wild-card-match-in-where-clause-only" class="table-of-contents__link">Wild-card match (in WHERE clause only)</a></li><li><a href="#udf" class="table-of-contents__link">UDF</a></li><li><a href="#bytes-column" class="table-of-contents__link">BYTES column</a></li></ul></li><li><a href="#pql-specification" class="table-of-contents__link">PQL Specification</a><ul><li><a href="#select" class="table-of-contents__link">SELECT</a></li><li><a href="#supported-aggregations-on-single-value-columns" class="table-of-contents__link">Supported aggregations on single-value columns</a></li><li><a href="#supported-aggregations-on-multi-value-columns" class="table-of-contents__link">Supported aggregations on multi-value columns</a></li><li><a href="#where" class="table-of-contents__link">WHERE</a></li><li><a href="#group-by" class="table-of-contents__link">GROUP BY</a></li><li><a href="#order-by" class="table-of-contents__link">ORDER BY</a></li><li><a href="#top" class="table-of-contents__link">TOP</a></li><li><a href="#limit" class="table-of-contents__link">LIMIT</a></li></ul></li><li><a href="#transform-function-in-aggregation-and-grouping" class="table-of-contents__link">Transform Function in Aggregation and Grouping</a><ul><li><a href="#supported-transform-functions" class="table-of-contents__link">Supported transform functions</a></li></ul></li><li><a href="#differences-with-sql" class="table-of-contents__link">Differences with SQL</a></li></ul></div></div></div></div></main></div></div><footer class="footer"><div class="container"><div class="row footer__links"><div class="col footer__col"><h4 class="footer__title">About</h4><ul class="footer__items"><li class="footer__item"><a href="https://docs.pinot.apache.org/" target="_blank" rel="noopener noreferrer" class="footer__link-item">What is Apache Pinot?</a></li><li class="footer__item"><a class="footer__link-item" href="/who_uses">Who uses Apache Pinot?</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/pinot-components" target="_blank" rel="noopener noreferrer" class="footer__link-item">Components</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/basics/architecture" target="_blank" rel="noopener noreferrer" class="footer__link-item">Architecture</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/plugins/plugin-architecture" target="_blank" rel="noopener noreferrer" class="footer__link-item">Plugins Architecture</a></li></ul></div><div class="col footer__col"><h4 class="footer__title">Integrations</h4><ul class="footer__items"><li class="footer__item"><a href="https://docs.pinot.apache.org/integrations/trino" target="_blank" rel="noopener noreferrer" class="footer__link-item">Trino</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/integrations/presto" target="_blank" rel="noopener noreferrer" class="footer__link-item">Presto</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/integrations/superset" target="_blank" rel="noopener noreferrer" class="footer__link-item">Superset</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/integrations/thirdeye" target="_blank" rel="noopener noreferrer" class="footer__link-item">ThirdEye</a></li></ul></div><div class="col footer__col"><h4 class="footer__title">Docs</h4><ul class="footer__items"><li class="footer__item"><a href="https://docs.pinot.apache.org/getting-started" target="_blank" rel="noopener noreferrer" class="footer__link-item">Getting Started</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/pinot-components" target="_blank" rel="noopener noreferrer" class="footer__link-item">Pinot Components</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/users" target="_blank" rel="noopener noreferrer" class="footer__link-item">User Guide</a></li><li class="footer__item"><a href="https://docs.pinot.apache.org/operating-pinot" target="_blank" rel="noopener noreferrer" class="footer__link-item">Administration</a></li></ul></div><div class="col footer__col"><h4 class="footer__title">Community</h4><ul class="footer__items"><li class="footer__item"><a href="https://join.slack.com/t/apache-pinot/shared_invite/zt-5z7pav2f-yYtjZdVA~EDmrGkho87Vzw" target="_blank" rel="noopener noreferrer" class="footer__link-item">Slack</a></li><li class="footer__item"><a href="https://github.com/apache/pinot" target="_blank" rel="noopener noreferrer" class="footer__link-item">Github</a></li><li class="footer__item"><a href="https://twitter.com/ApachePinot" target="_blank" rel="noopener noreferrer" class="footer__link-item">Twitter</a></li><li class="footer__item"><a href="mailto:dev-subscribe@pinot.apache.org?Subject=SubscribeToPinot" target="_blank" rel="noopener noreferrer" class="footer__link-item">Mailing List</a></li></ul></div><div class="col footer__col"><h4 class="footer__title">Apache</h4><ul class="footer__items"><li class="footer__item"><a href="https://www.apache.org/events/current-event" target="_blank" rel="noopener noreferrer" class="footer__link-item">Events</a></li><li class="footer__item"><a href="https://www.apache.org/foundation/thanks.html" target="_blank" rel="noopener noreferrer" class="footer__link-item">Thanks</a></li><li class="footer__item"><a href="https://www.apache.org/licenses" target="_blank" rel="noopener noreferrer" class="footer__link-item">License</a></li><li class="footer__item"><a href="https://www.apache.org/security" target="_blank" rel="noopener noreferrer" class="footer__link-item">Security</a></li><li class="footer__item"><a href="https://www.apache.org/foundation/sponsorship.html" target="_blank" rel="noopener noreferrer" class="footer__link-item">Sponsorship</a></li><li class="footer__item"><a href="https://www.apache.org" target="_blank" rel="noopener noreferrer" class="footer__link-item">Foundation</a></li></ul></div></div><div class="footer__bottom text--center"><div class="margin-bottom--sm"><a href="https://pinot.apache.org/" target="_blank" rel="noopener noreferrer" class="footerLogoLink_94kH"><img src="/img/logo.svg" alt="Apache Pinot™" class="themedImage_TMUO themedImage--light_4Vu1 footer__logo"><img src="/img/logo.svg" alt="Apache Pinot™" class="themedImage_TMUO themedImage--dark_uzRr footer__logo"></a></div><div class="footerCopyright_-piB">Copyright © 2022 The Apache Software Foundation.<br>Apache Pinot, Pinot, Apache, the Apache feather logo, and the Apache Pinot project logo are registered trademarks of The Apache Software Foundation.<br><br>This page has references to third party software - Presto, PrestoDB, ThirdEye, Trino, TrinoDB, that are not part of the Apache Software Foundation and are not covered under the Apache License.</div></div></div></footer></div> |
| <script src="/assets/js/runtime~main.82dd65bd.js"></script> |
| <script src="/assets/js/main.9b2ca7ec.js"></script> |
| </body> |
| </html> |
