| <!doctype html> |
| <html lang="en" dir="ltr" class="docs-wrapper docs-doc-page docs-version-current plugin-docs plugin-id-default docs-doc-id-querying/sql"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="generator" content="Docusaurus v2.4.1"> |
| <title data-rh="true">Druid SQL overview | Apache® Druid</title><meta data-rh="true" name="viewport" content="width=device-width,initial-scale=1"><meta data-rh="true" name="twitter:card" content="summary_large_image"><meta data-rh="true" property="og:image" content="https://druid.apache.org/img/druid_nav.png"><meta data-rh="true" name="twitter:image" content="https://druid.apache.org/img/druid_nav.png"><meta data-rh="true" property="og:url" content="https://druid.apache.org/docs/28.0.0/querying/sql"><meta data-rh="true" name="docusaurus_locale" content="en"><meta data-rh="true" name="docsearch:language" content="en"><meta data-rh="true" name="docusaurus_version" content="current"><meta data-rh="true" name="docusaurus_tag" content="docs-default-current"><meta data-rh="true" name="docsearch:version" content="current"><meta data-rh="true" name="docsearch:docusaurus_tag" content="docs-default-current"><meta data-rh="true" property="og:title" content="Druid SQL overview | Apache® Druid"><meta data-rh="true" name="description" content="<!--"><meta data-rh="true" property="og:description" content="<!--"><link data-rh="true" rel="icon" href="/img/favicon.png"><link data-rh="true" rel="canonical" href="https://druid.apache.org/docs/28.0.0/querying/sql"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/28.0.0/querying/sql" hreflang="en"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/28.0.0/querying/sql" hreflang="x-default"><link rel="preconnect" href="https://www.google-analytics.com"> |
| <link rel="preconnect" href="https://www.googletagmanager.com"> |
| <script async src="https://www.googletagmanager.com/gtag/js?id=UA-131010415-1"></script> |
| <script>function gtag(){dataLayer.push(arguments)}window.dataLayer=window.dataLayer||[],gtag("js",new Date),gtag("config","UA-131010415-1",{})</script> |
| |
| |
| |
| |
| <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css"> |
| <script src="https://cdnjs.cloudflare.com/ajax/libs/clipboard.js/2.0.4/clipboard.min.js"></script><link rel="stylesheet" href="/assets/css/styles.546f39eb.css"> |
| <link rel="preload" href="/assets/js/runtime~main.0dcbfdea.js" as="script"> |
| <link rel="preload" href="/assets/js/main.7f6fdf81.js" as="script"> |
| </head> |
| <body class="navigation-with-keyboard"> |
| <script>!function(){function t(t){document.documentElement.setAttribute("data-theme",t)}var e=function(){var t=null;try{t=new URLSearchParams(window.location.search).get("docusaurus-theme")}catch(t){}return t}()||function(){var t=null;try{t=localStorage.getItem("theme")}catch(t){}return t}();t(null!==e?e:"light")}()</script><div id="__docusaurus"> |
| <div role="region" aria-label="Skip to main content"><a class="skipToContent_fXgn" href="#__docusaurus_skipToContent_fallback">Skip to main content</a></div><nav aria-label="Main" class="navbar navbar--fixed-top navbar--dark"><div class="navbar__inner"><div class="navbar__items"><button aria-label="Toggle navigation bar" aria-expanded="false" class="navbar__toggle clean-btn" type="button"><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="/"><div class="navbar__logo"><img src="/img/druid_nav.png" alt="Apache® Druid" class="themedImage_ToTc themedImage--light_HNdA"><img src="/img/druid_nav.png" alt="Apache® Druid" class="themedImage_ToTc themedImage--dark_i4oU"></div></a></div><div class="navbar__items navbar__items--right"><a class="navbar__item navbar__link" href="/technology">Technology</a><a class="navbar__item navbar__link" href="/use-cases">Use Cases</a><a class="navbar__item navbar__link" href="/druid-powered">Powered By</a><a class="navbar__item navbar__link" href="/docs/28.0.0/design/">Docs</a><a class="navbar__item navbar__link" href="/community/">Community</a><div class="navbar__item dropdown dropdown--hoverable dropdown--right"><a href="#" aria-haspopup="true" aria-expanded="false" role="button" class="navbar__link">Apache®</a><ul class="dropdown__menu"><li><a href="https://www.apache.org/" target="_blank" rel="noopener noreferrer" class="dropdown__link">Foundation<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><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></a></li><li><a href="https://apachecon.com/?ref=druid.apache.org" target="_blank" rel="noopener noreferrer" class="dropdown__link">Events<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><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></a></li><li><a href="https://www.apache.org/licenses/" target="_blank" rel="noopener noreferrer" class="dropdown__link">License<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><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></a></li><li><a href="https://www.apache.org/foundation/thanks.html" target="_blank" rel="noopener noreferrer" class="dropdown__link">Thanks<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><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></a></li><li><a href="https://www.apache.org/security/" target="_blank" rel="noopener noreferrer" class="dropdown__link">Security<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><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></a></li><li><a href="https://www.apache.org/foundation/sponsorship.html" target="_blank" rel="noopener noreferrer" class="dropdown__link">Sponsorship<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><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></a></li></ul></div><a class="navbar__item navbar__link" href="/downloads/">Download</a><div class="searchBox_ZlJk"><div class="navbar__search"><span aria-label="expand searchbar" role="button" class="search-icon" tabindex="0"></span><input type="search" id="search_input_react" placeholder="Loading..." aria-label="Search" class="navbar__search-input search-bar" disabled=""></div></div></div></div><div role="presentation" class="navbar-sidebar__backdrop"></div></nav><div id="__docusaurus_skipToContent_fallback" class="main-wrapper mainWrapper_z2l0 docsWrapper_BCFX"><button aria-label="Scroll back to top" class="clean-btn theme-back-to-top-button backToTopButton_sjWU" type="button"></button><div class="docPage__5DB"><aside class="theme-doc-sidebar-container docSidebarContainer_b6E3"><div class="sidebarViewport_Xe31"><div class="sidebar_njMd"><nav aria-label="Docs sidebar" class="menu thin-scrollbar menu_SIkG"><ul class="theme-doc-sidebar-menu menu__list"><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/design/">Getting started</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/tutorials/tutorial-msq-extern">Tutorials</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/design/architecture">Design</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/ingestion/">Ingestion</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/data-management/">Data management</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret menu__link--active" aria-expanded="true" href="/docs/28.0.0/querying/sql">Querying</a></div><ul style="display:block;overflow:visible;height:auto" class="menu__list"><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret menu__link--active" aria-expanded="true" tabindex="0" href="/docs/28.0.0/querying/sql">Druid SQL</a></div><ul style="display:block;overflow:visible;height:auto" class="menu__list"><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link menu__link--active" aria-current="page" tabindex="0" href="/docs/28.0.0/querying/sql">Overview and syntax</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/tips-good-queries">Tips for writing good queries</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/query-deep-storage">Query from deep storage</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-data-types">SQL data types</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-operators">Operators</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-scalar">Scalar functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-aggregations">Aggregation functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-array-functions">Array functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-multivalue-string-functions">Multi-value string functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-json-functions">JSON functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-functions">All functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-query-context">SQL query context</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-metadata-tables">SQL metadata tables</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-translation">SQL query translation</a></li></ul></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/">Native queries</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/query-execution">Query execution</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/troubleshooting">Troubleshooting</a></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" tabindex="0" href="/docs/28.0.0/querying/datasource">Concepts</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" tabindex="0" href="/docs/28.0.0/querying/timeseriesquery">Native query types</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" tabindex="0" href="/docs/28.0.0/querying/filters">Native query components</a></div></li></ul></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/api-reference/">API reference</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/configuration/">Configuration</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/operations/web-console">Operations</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/development/overview">Development</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/misc/papers-and-talks">Misc</a></div></li></ul></nav></div></div></aside><main class="docMainContainer_gTbr"><div class="container padding-top--md padding-bottom--lg"><div class="row"><div class="col docItemCol_VOVn"><div class="docItemContainer_Djhp"><article><nav class="theme-doc-breadcrumbs breadcrumbsContainer_Z_bl" aria-label="Breadcrumbs"><ul class="breadcrumbs" itemscope="" itemtype="https://schema.org/BreadcrumbList"><li class="breadcrumbs__item"><a aria-label="Home page" class="breadcrumbs__link" href="/"><svg viewBox="0 0 24 24" class="breadcrumbHomeIcon_YNFT"><path d="M10 19v-5h4v5c0 .55.45 1 1 1h3c.55 0 1-.45 1-1v-7h1.7c.46 0 .68-.57.33-.87L12.67 3.6c-.38-.34-.96-.34-1.34 0l-8.36 7.53c-.34.3-.13.87.33.87H5v7c0 .55.45 1 1 1h3c.55 0 1-.45 1-1z" fill="currentColor"></path></svg></a></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Querying</span><meta itemprop="position" content="1"></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Druid SQL</span><meta itemprop="position" content="2"></li><li itemscope="" itemprop="itemListElement" itemtype="https://schema.org/ListItem" class="breadcrumbs__item breadcrumbs__item--active"><span class="breadcrumbs__link" itemprop="name">Overview and syntax</span><meta itemprop="position" content="3"></li></ul></nav><div class="tocCollapsible_ETCw theme-doc-toc-mobile tocMobile_ITEo"><button type="button" class="clean-btn tocCollapsibleButton_TO0P">On this page</button></div><div class="theme-doc-markdown markdown"><header><h1>Druid SQL overview</h1></header><div class="theme-admonition theme-admonition-info alert alert--info admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M7 2.3c3.14 0 5.7 2.56 5.7 5.7s-2.56 5.7-5.7 5.7A5.71 5.71 0 0 1 1.3 8c0-3.14 2.56-5.7 5.7-5.7zM7 1C3.14 1 0 4.14 0 8s3.14 7 7 7 7-3.14 7-7-3.14-7-7-7zm1 3H6v5h2V4zm0 6H6v2h2v-2z"></path></svg></span>info</div><div class="admonitionContent_S0QG"><p> Apache Druid supports two query languages: Druid SQL and <a href="/docs/28.0.0/querying/">native queries</a>. |
| This document describes the SQL language.</p></div></div><p>You can query data in Druid datasources using Druid SQL. Druid translates SQL queries into its <a href="/docs/28.0.0/querying/">native query language</a>. To learn about translation and how to get the best performance from Druid SQL, see <a href="/docs/28.0.0/querying/sql-translation">SQL query translation</a>.</p><p>Druid SQL planning occurs on the Broker. |
| Set <a href="/docs/28.0.0/configuration/#sql">Broker runtime properties</a> to configure the query plan and JDBC querying.</p><p>For information on permissions needed to make SQL queries, see <a href="/docs/28.0.0/operations/security-user-auth#sql-permissions">Defining SQL permissions</a>.</p><p>This topic introduces Druid SQL syntax. |
| For more information and SQL querying options see:</p><ul><li><a href="/docs/28.0.0/querying/sql-data-types">Data types</a> for a list of supported data types for Druid columns.</li><li><a href="/docs/28.0.0/querying/sql-aggregations">Aggregation functions</a> for a list of aggregation functions available for Druid SQL SELECT statements.</li><li><a href="/docs/28.0.0/querying/sql-scalar">Scalar functions</a> for Druid SQL scalar functions including numeric and string functions, IP address functions, Sketch functions, and more.</li><li><a href="/docs/28.0.0/querying/sql-multivalue-string-functions">SQL multi-value string functions</a> for operations you can perform on string dimensions containing multiple values.</li><li><a href="/docs/28.0.0/querying/sql-translation">Query translation</a> for information about how Druid translates SQL queries to native queries before running them.</li></ul><p>For information about APIs, see:</p><ul><li><a href="/docs/28.0.0/api-reference/sql-api">Druid SQL API</a> for information on the HTTP API.</li><li><a href="/docs/28.0.0/api-reference/sql-jdbc">SQL JDBC driver API</a> for information about the JDBC driver API.</li><li><a href="/docs/28.0.0/querying/sql-query-context">SQL query context</a> for information about the query context parameters that affect SQL planning.</li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="syntax">Syntax<a href="#syntax" class="hash-link" aria-label="Direct link to Syntax" title="Direct link to Syntax"></a></h2><p>Druid SQL supports SELECT queries with the following structure:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ EXPLAIN PLAN FOR ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ WITH tableName [ ( column1, column2, ... ) ] AS ( query ) ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT [ ALL | DISTINCT ] { * | exprs }</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">FROM { <table> | (<subquery>) | <o1> [ INNER | LEFT ] JOIN <o2> ON condition }</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ CROSS JOIN UNNEST(source_expression) as table_alias_name(column_alias_name) ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ WHERE expr ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ GROUP BY [ exprs | GROUPING SETS ( (exprs), ... ) | ROLLUP (exprs) | CUBE (exprs) ] ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ HAVING expr ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ ORDER BY expr [ ASC | DESC ], expr [ ASC | DESC ], ... ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ LIMIT limit ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ OFFSET offset ]</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">[ UNION ALL <another query> ]</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="from">FROM<a href="#from" class="hash-link" aria-label="Direct link to FROM" title="Direct link to FROM"></a></h2><p>The FROM clause can refer to any of the following:</p><ul><li><a href="/docs/28.0.0/querying/datasource#table">Table datasources</a> from the <code>druid</code> schema. This is the default schema, so Druid table |
| datasources can be referenced as either <code>druid.dataSourceName</code> or simply <code>dataSourceName</code>.</li><li><a href="/docs/28.0.0/querying/datasource#lookup">Lookups</a> from the <code>lookup</code> schema, for example <code>lookup.countries</code>. Note that lookups can |
| also be queried using the <a href="/docs/28.0.0/querying/sql-scalar#string-functions"><code>LOOKUP</code> function</a>.</li><li><a href="/docs/28.0.0/querying/datasource#query">Subqueries</a>.</li><li><a href="/docs/28.0.0/querying/datasource#join">Joins</a> between anything in this list, except between native datasources (table, lookup, |
| query) and system tables. The join condition must be an equality between expressions from the left- and right-hand side |
| of the join.</li><li><a href="/docs/28.0.0/querying/sql-metadata-tables">Metadata tables</a> from the <code>INFORMATION_SCHEMA</code> or <code>sys</code> schemas. Unlike the other options for the |
| FROM clause, metadata tables are not considered datasources. They exist only in the SQL layer.</li></ul><p>For more information about table, lookup, query, and join datasources, refer to the <a href="/docs/28.0.0/querying/datasource">Datasources</a> |
| documentation.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="unnest">UNNEST<a href="#unnest" class="hash-link" aria-label="Direct link to UNNEST" title="Direct link to UNNEST"></a></h2><p>The UNNEST clause unnests ARRAY typed values. The source for UNNEST can be an array type column, or an input that's been transformed into an array, such as with helper functions like <a href="/docs/28.0.0/querying/sql-multivalue-string-functions"><code>MV_TO_ARRAY</code></a> or <a href="/docs/28.0.0/querying/sql-array-functions"><code>ARRAY</code></a>.</p><p>The following is the general syntax for UNNEST, specifically a query that returns the column that gets unnested:</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> column_alias_name </span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> datasource </span><span class="token keyword" style="font-style:italic">CROSS</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">JOIN</span><span class="token plain"> UNNEST</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">source_expression1</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> table_alias_name1</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">column_alias_name1</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">CROSS</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">JOIN</span><span class="token plain"> UNNEST</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">source_expression2</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> table_alias_name2</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">column_alias_name2</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><ul><li>The <code>datasource</code> for UNNEST can be any Druid datasource, such as the following:<ul><li>A table, such as <code>FROM a_table</code>.</li><li>A subset of a table based on a query, a filter, or a JOIN. For example, <code>FROM (SELECT columnA,columnB,columnC from a_table)</code>.</li></ul></li><li>The <code>source_expression</code> for the UNNEST function must be an array and can come from any expression. UNNEST works directly on Druid ARRAY typed columns. If the column you are unnesting is a multi-value VARCHAR, you must specify <code>MV_TO_ARRAY(dimension)</code> to convert it to an ARRAY type. You can also specify any expression that has an SQL array datatype. For example, you can call UNNEST on the following:<ul><li><code>ARRAY[dim1,dim2]</code> if you want to make an array out of two dimensions. </li><li><code>ARRAY_CONCAT(dim1,dim2)</code> if you want to concatenate two multi-value dimensions. </li></ul></li><li>The <code>AS table_alias_name(column_alias_name)</code> clause is not required but is highly recommended. Use it to specify the output, which can be an existing column or a new one. Replace <code>table_alias_name</code> and <code>column_alias_name</code> with a table and column name you want to alias the unnested results to. If you don't provide this, Druid uses a nondescriptive name, such as <code>EXPR$0</code>.</li></ul><p>Keep the following things in mind when writing your query:</p><ul><li>You can unnest multiple source expressions in a single query.</li><li>Notice the CROSS JOIN between the datasource and the UNNEST function. This is needed in most cases of the UNNEST function. Specifically, it is not needed when you're unnesting an inline array since the array itself is the datasource.</li><li>If you view the native explanation of a SQL UNNEST, you'll notice that Druid uses <code>j0.unnest</code> as a virtual column to perform the unnest. An underscore is added for each unnest, so you may notice virtual columns named <code>_j0.unnest</code> or <code>__j0.unnest</code>.</li><li>UNNEST preserves the ordering of the source array that is being unnested.</li></ul><p>For examples, see the <a href="/docs/28.0.0/tutorials/tutorial-unnest-arrays">Unnest arrays tutorial</a>.</p><p>The UNNEST function has the following limitations:</p><ul><li>The function does not remove any duplicates or nulls in an array. Nulls will be treated as any other value in an array. If there are multiple nulls within the array, a record corresponding to each of the nulls gets created.</li><li>Arrays of complex objects inside complex JSON types are not supported.</li></ul><p>UNNEST is the SQL equivalent of the <a href="/docs/28.0.0/querying/datasource#unnest">unnest datasource</a>.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="where">WHERE<a href="#where" class="hash-link" aria-label="Direct link to WHERE" title="Direct link to WHERE"></a></h2><p>The WHERE clause refers to columns in the FROM table, and will be translated to <a href="/docs/28.0.0/querying/filters">native filters</a>. The |
| WHERE clause can also reference a subquery, like <code>WHERE col1 IN (SELECT foo FROM ...)</code>. Queries like this are executed |
| as a join on the subquery, described in the <a href="/docs/28.0.0/querying/sql-translation#subqueries">Query translation</a> section.</p><p>Strings and numbers can be compared in the WHERE clause of a SQL query through implicit type conversion. |
| For example, you can evaluate <code>WHERE stringDim = 1</code> for a string-typed dimension named <code>stringDim</code>. |
| However, for optimal performance, you should explicitly cast the reference number as a string when comparing against a string dimension:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">WHERE stringDim = '1'</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>Similarly, if you compare a string-typed dimension with reference to an array of numbers, cast the numbers to strings:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">WHERE stringDim IN ('1', '2', '3')</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>Note that explicit type casting does not lead to significant performance improvement when comparing strings and numbers involving numeric dimensions since numeric dimensions are not indexed.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="group-by">GROUP BY<a href="#group-by" class="hash-link" aria-label="Direct link to GROUP BY" title="Direct link to GROUP BY"></a></h2><p>The GROUP BY clause refers to columns in the FROM table. Using GROUP BY, DISTINCT, or any aggregation functions will |
| trigger an aggregation query using one of Druid's <a href="/docs/28.0.0/querying/sql-translation#query-types">three native aggregation query types</a>. GROUP BY |
| can refer to an expression or a select clause ordinal position (like <code>GROUP BY 2</code> to group by the second selected |
| column).</p><p>The GROUP BY clause can also refer to multiple grouping sets in three ways. The most flexible is GROUP BY GROUPING SETS, |
| for example <code>GROUP BY GROUPING SETS ( (country, city), () )</code>. This example is equivalent to a <code>GROUP BY country, city</code> |
| followed by <code>GROUP BY ()</code> (a grand total). With GROUPING SETS, the underlying data is only scanned one time, leading to |
| better efficiency. Second, GROUP BY ROLLUP computes a grouping set for each level of the grouping expressions. For |
| example <code>GROUP BY ROLLUP (country, city)</code> is equivalent to <code>GROUP BY GROUPING SETS ( (country, city), (country), () )</code> |
| and will produce grouped rows for each country / city pair, along with subtotals for each country, along with a grand |
| total. Finally, GROUP BY CUBE computes a grouping set for each combination of grouping expressions. For example, |
| <code>GROUP BY CUBE (country, city)</code> is equivalent to <code>GROUP BY GROUPING SETS ( (country, city), (country), (city), () )</code>.</p><p>Grouping columns that do not apply to a particular row will contain <code>NULL</code>. For example, when computing |
| <code>GROUP BY GROUPING SETS ( (country, city), () )</code>, the grand total row corresponding to <code>()</code> will have <code>NULL</code> for the |
| "country" and "city" columns. Column may also be <code>NULL</code> if it was <code>NULL</code> in the data itself. To differentiate such rows, |
| you can use <code>GROUPING</code> aggregation. </p><p>When using GROUP BY GROUPING SETS, GROUP BY ROLLUP, or GROUP BY CUBE, be aware that results may not be generated in the |
| order that you specify your grouping sets in the query. If you need results to be generated in a particular order, use |
| the ORDER BY clause.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="having">HAVING<a href="#having" class="hash-link" aria-label="Direct link to HAVING" title="Direct link to HAVING"></a></h2><p>The HAVING clause refers to columns that are present after execution of GROUP BY. It can be used to filter on either |
| grouping expressions or aggregated values. It can only be used together with GROUP BY.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="order-by">ORDER BY<a href="#order-by" class="hash-link" aria-label="Direct link to ORDER BY" title="Direct link to ORDER BY"></a></h2><p>The ORDER BY clause refers to columns that are present after execution of GROUP BY. It can be used to order the results |
| based on either grouping expressions or aggregated values. ORDER BY can refer to an expression or a select clause |
| ordinal position (like <code>ORDER BY 2</code> to order by the second selected column). For non-aggregation queries, ORDER BY |
| can only order by the <code>__time</code> column. For aggregation queries, ORDER BY can order by any column.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="limit">LIMIT<a href="#limit" class="hash-link" aria-label="Direct link to LIMIT" title="Direct link to LIMIT"></a></h2><p>The LIMIT clause limits the number of rows returned. In some situations Druid will push down this limit to data servers, |
| which boosts performance. Limits are always pushed down for queries that run with the native Scan or TopN query types. |
| With the native GroupBy query type, it is pushed down when ordering on a column that you are grouping by. If you notice |
| that adding a limit doesn't change performance very much, then it's possible that Druid wasn't able to push down the |
| limit for your query.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="offset">OFFSET<a href="#offset" class="hash-link" aria-label="Direct link to OFFSET" title="Direct link to OFFSET"></a></h2><p>The OFFSET clause skips a certain number of rows when returning results.</p><p>If both LIMIT and OFFSET are provided, then OFFSET will be applied first, followed by LIMIT. For example, using |
| LIMIT 100 OFFSET 10 will return 100 rows, starting from row number 10.</p><p>Together, LIMIT and OFFSET can be used to implement pagination. However, note that if the underlying datasource is |
| modified between page fetches, then the different pages will not necessarily align with each other.</p><p>There are two important factors that can affect the performance of queries that use OFFSET:</p><ul><li>Skipped rows still need to be generated internally and then discarded, meaning that raising offsets to high values |
| can cause queries to use additional resources.</li><li>OFFSET is only supported by the Scan and GroupBy <a href="/docs/28.0.0/querying/sql-translation#query-types">native query types</a>. Therefore, a query with OFFSET |
| will use one of those two types, even if it might otherwise have run as a Timeseries or TopN. Switching query engines |
| in this way can affect performance.</li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="union-all">UNION ALL<a href="#union-all" class="hash-link" aria-label="Direct link to UNION ALL" title="Direct link to UNION ALL"></a></h2><p>The UNION ALL operator fuses multiple queries together. Druid SQL supports the UNION ALL operator in two situations: top-level and table-level, as described below. Queries that use UNION ALL in any other way will fail.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="top-level">Top-level<a href="#top-level" class="hash-link" aria-label="Direct link to Top-level" title="Direct link to Top-level"></a></h3><p>In top-level queries, you can use UNION ALL at the very top outer layer of the query - not in a subquery, and not in the FROM clause. The underlying queries run sequentially. Druid concatenates their results so that they appear one after the other.</p><p>For example:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT COUNT(*) FROM tbl WHERE my_column = 'value1'</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">UNION ALL</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT COUNT(*) FROM tbl WHERE my_column = 'value2'</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>Certain limitations apply when you use a top-level UNION ALL. For all top-level UNION ALL queries, you can't apply a GROUP BY, ORDER BY, or any other operator to the results of the query. For any top-level UNION ALL that uses the MSQ task engine, the SQL planner attempts to plan the top-level UNION ALL as a table-level UNION ALL. Because of this, UNION ALL queries that use the MSQ task engine always behave the same as table-level UNION ALL queries. They have the same characteristics and limitations. If the planner can't plan the query as a table-level UNION ALL, the query fails.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="table-level">Table-level<a href="#table-level" class="hash-link" aria-label="Direct link to Table-level" title="Direct link to Table-level"></a></h3><p>In table-level queries, you must use UNION ALL in a subquery in the FROM clause, and create the lower-level subqueries that are inputs to the UNION ALL operator as simple table SELECTs. You can't use features like expressions, column aliasing, JOIN, GROUP BY, or ORDER BY in table-level queries.</p><p>The query runs natively using a <a href="/docs/28.0.0/querying/datasource#union">union datasource</a>.</p><p>At table-level queries, you must select the same columns from each table in the same order, and those columns must either have the same types, or types that can be implicitly cast to each other (such as different numeric types). For this reason, it is generally more robust to write your queries to select specific columns. If you use <code>SELECT *</code>, you must modify your queries if a new column is added to one table but not to the others.</p><p>For example:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT col1, COUNT(*)</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">FROM (</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> SELECT col1, col2, col3 FROM tbl1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> UNION ALL</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> SELECT col1, col2, col3 FROM tbl2</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">)</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">GROUP BY col1</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>With table-level UNION ALL, the rows from the unioned tables are not guaranteed to process in any particular order. They may process in an interleaved fashion. If you need a particular result ordering, use <a href="#order-by">ORDER BY</a> on the outer query.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="explain-plan">EXPLAIN PLAN<a href="#explain-plan" class="hash-link" aria-label="Direct link to EXPLAIN PLAN" title="Direct link to EXPLAIN PLAN"></a></h2><p>Add "EXPLAIN PLAN FOR" to the beginning of any query to get information about how it will be translated. In this case, |
| the query will not actually be executed. Refer to the <a href="/docs/28.0.0/querying/sql-translation#interpreting-explain-plan-output">Query translation</a> |
| documentation for more information on the output of EXPLAIN PLAN.</p><div class="theme-admonition theme-admonition-info alert alert--info admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M7 2.3c3.14 0 5.7 2.56 5.7 5.7s-2.56 5.7-5.7 5.7A5.71 5.71 0 0 1 1.3 8c0-3.14 2.56-5.7 5.7-5.7zM7 1C3.14 1 0 4.14 0 8s3.14 7 7 7 7-3.14 7-7-3.14-7-7-7zm1 3H6v5h2V4zm0 6H6v2h2v-2z"></path></svg></span>info</div><div class="admonitionContent_S0QG"><p> For the legacy plan, be careful when interpreting EXPLAIN PLAN output, and use <a href="/docs/28.0.0/configuration/#request-logging">request logging</a> if in doubt. |
| Request logs show the exact native query that will be run. Alternatively, to see the native query plan, set <code>useNativeQueryExplain</code> to true in the query context.</p></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="identifiers-and-literals">Identifiers and literals<a href="#identifiers-and-literals" class="hash-link" aria-label="Direct link to Identifiers and literals" title="Direct link to Identifiers and literals"></a></h2><p>Identifiers like datasource and column names can optionally be quoted using double quotes. To escape a double quote |
| inside an identifier, use another double quote, like <code>"My ""very own"" identifier"</code>. All identifiers are case-sensitive |
| and no implicit case conversions are performed.</p><p>Literal strings should be quoted with single quotes, like <code>'foo'</code>. Literal strings with Unicode escapes can be written |
| like <code>U&'fo\00F6'</code>, where character codes in hex are prefixed by a backslash. Literal numbers can be written in forms |
| like <code>100</code> (denoting an integer), <code>100.0</code> (denoting a floating point value), or <code>1.0e5</code> (scientific notation). Literal |
| timestamps can be written like <code>TIMESTAMP '2000-01-01 00:00:00'</code>. Literal intervals, used for time arithmetic, can be |
| written like <code>INTERVAL '1' HOUR</code>, <code>INTERVAL '1 02:03' DAY TO MINUTE</code>, <code>INTERVAL '1-2' YEAR TO MONTH</code>, and so on.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="dynamic-parameters">Dynamic parameters<a href="#dynamic-parameters" class="hash-link" aria-label="Direct link to Dynamic parameters" title="Direct link to Dynamic parameters"></a></h2><p>Druid SQL supports dynamic parameters using question mark (<code>?</code>) syntax, where parameters are bound to <code>?</code> placeholders |
| at execution time. To use dynamic parameters, replace any literal in the query with a <code>?</code> character and provide a |
| corresponding parameter value when you execute the query. Parameters are bound to the placeholders in the order in |
| which they are passed. Parameters are supported in both the <a href="/docs/28.0.0/api-reference/sql-api">HTTP POST</a> and <a href="/docs/28.0.0/api-reference/sql-jdbc">JDBC</a> APIs.</p><p>In certain cases, using dynamic parameters in expressions can cause type inference issues which cause your query to fail, for example:</p><div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token keyword" style="font-style:italic">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">*</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> druid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">foo </span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> dim1 </span><span class="token operator" style="color:rgb(137, 221, 255)">like</span><span class="token plain"> CONCAT</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">'%'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> ?</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'%'</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>To solve this issue, explicitly provide the type of the dynamic parameter using the <code>CAST</code> keyword. Consider the fix for the preceding example:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT * FROM druid.foo WHERE dim1 like CONCAT('%', CAST (? AS VARCHAR), '%')</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div></div></article><nav class="pagination-nav docusaurus-mt-lg" aria-label="Docs pages"><a class="pagination-nav__link pagination-nav__link--prev" href="/docs/28.0.0/data-management/manual-compaction"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Manual compaction</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/28.0.0/querying/tips-good-queries"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Tips for writing good queries</div></a></nav></div></div><div class="col col--3"><div class="tableOfContents_bqdL thin-scrollbar theme-doc-toc-desktop"><ul class="table-of-contents table-of-contents__left-border"><li><a href="#syntax" class="table-of-contents__link toc-highlight">Syntax</a></li><li><a href="#from" class="table-of-contents__link toc-highlight">FROM</a></li><li><a href="#unnest" class="table-of-contents__link toc-highlight">UNNEST</a></li><li><a href="#where" class="table-of-contents__link toc-highlight">WHERE</a></li><li><a href="#group-by" class="table-of-contents__link toc-highlight">GROUP BY</a></li><li><a href="#having" class="table-of-contents__link toc-highlight">HAVING</a></li><li><a href="#order-by" class="table-of-contents__link toc-highlight">ORDER BY</a></li><li><a href="#limit" class="table-of-contents__link toc-highlight">LIMIT</a></li><li><a href="#offset" class="table-of-contents__link toc-highlight">OFFSET</a></li><li><a href="#union-all" class="table-of-contents__link toc-highlight">UNION ALL</a><ul><li><a href="#top-level" class="table-of-contents__link toc-highlight">Top-level</a></li><li><a href="#table-level" class="table-of-contents__link toc-highlight">Table-level</a></li></ul></li><li><a href="#explain-plan" class="table-of-contents__link toc-highlight">EXPLAIN PLAN</a></li><li><a href="#identifiers-and-literals" class="table-of-contents__link toc-highlight">Identifiers and literals</a></li><li><a href="#dynamic-parameters" class="table-of-contents__link toc-highlight">Dynamic parameters</a></li></ul></div></div></div></div></main></div></div><footer class="footer"><div class="container container-fluid"><div class="footer__bottom text--center"><div class="margin-bottom--sm"><img src="/img/favicon.png" class="themedImage_ToTc themedImage--light_HNdA footer__logo"><img src="/img/favicon.png" class="themedImage_ToTc themedImage--dark_i4oU footer__logo"></div><div class="footer__copyright">Copyright © 2023 Apache Software Foundation. Except where otherwise noted, licensed under CC BY-SA 4.0. Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.</div></div></div></footer></div> |
| <script src="/assets/js/runtime~main.0dcbfdea.js"></script> |
| <script src="/assets/js/main.7f6fdf81.js"></script> |
| </body> |
| </html> |
