| <!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-data-types" data-has-hydrated="false"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="generator" content="Docusaurus v2.4.3"> |
| <title data-rh="true">SQL data types | 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/latest/querying/sql-data-types"><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="SQL data types | 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/latest/querying/sql-data-types"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/latest/querying/sql-data-types" hreflang="en"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/latest/querying/sql-data-types" hreflang="x-default"><link rel="stylesheet" href="/css/all.css"> |
| <script src="/js/clipboard.min.js"></script><link rel="stylesheet" href="/assets/css/styles.60a7f877.css"> |
| <link rel="preload" href="/assets/js/runtime~main.f17df138.js" as="script"> |
| <link rel="preload" href="/assets/js/main.e3bd5681.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/latest/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-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/latest/design/">Introduction to Apache Druid</a></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" aria-expanded="false" href="/docs/latest/tutorials/">Getting started</a><button aria-label="Toggle the collapsible sidebar category 'Getting started'" type="button" class="clean-btn menu__caret"></button></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/latest/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" aria-expanded="false" href="/docs/latest/ingestion/">Ingestion</a><button aria-label="Toggle the collapsible sidebar category 'Ingestion'" type="button" class="clean-btn menu__caret"></button></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" aria-expanded="false" href="/docs/latest/data-management/">Data management</a><button aria-label="Toggle the collapsible sidebar category 'Data management'" type="button" class="clean-btn menu__caret"></button></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/latest/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/latest/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" tabindex="0" href="/docs/latest/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/latest/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/latest/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/latest/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 menu__link--active" aria-current="page" tabindex="0" href="/docs/latest/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/latest/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/latest/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/latest/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/latest/querying/sql-window-functions">Window 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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/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/latest/querying/query-processing">Query processing</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/latest/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/latest/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/latest/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/latest/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/latest/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" aria-expanded="false" href="/docs/latest/api-reference/">API reference</a><button aria-label="Toggle the collapsible sidebar category 'API reference'" type="button" class="clean-btn menu__caret"></button></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" aria-expanded="false" href="/docs/latest/configuration/">Configuration</a><button aria-label="Toggle the collapsible sidebar category 'Configuration'" type="button" class="clean-btn menu__caret"></button></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/latest/api-reference/automatic-compaction-api">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" aria-expanded="false" href="/docs/latest/development/overview">Development</a><button aria-label="Toggle the collapsible sidebar category 'Development'" type="button" class="clean-btn menu__caret"></button></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/latest/release-info/release-notes">Release info</a></div></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/latest/misc/papers-and-talks">Papers</a></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">SQL data types</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>SQL data types</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/latest/querying/">native queries</a>. |
| This document describes the SQL language.</p></div></div><p>Druid associates each column with a specific data type. This topic describes supported data types in <a href="/docs/latest/querying/sql">Druid SQL</a>.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="standard-types">Standard types<a href="#standard-types" class="hash-link" aria-label="Direct link to Standard types" title="Direct link to Standard types"></a></h2><p>Druid natively supports the following basic column types:</p><ul><li>LONG: 64-bit signed int</li><li>FLOAT: 32-bit float</li><li>DOUBLE: 64-bit float</li><li>STRING: UTF-8 encoded strings and string arrays</li><li>COMPLEX: non-standard data types, such as nested JSON, hyperUnique and approxHistogram, and DataSketches</li><li>ARRAY: arrays composed of any of these types</li></ul><p>Druid treats timestamps (including the <code>__time</code> column) as LONG, with the value being the number of |
| milliseconds since 1970-01-01 00:00:00 UTC, not counting leap seconds. Therefore, timestamps in Druid do not carry any |
| timezone information. They only carry information about the exact moment in time they represent. See |
| <a href="/docs/latest/querying/sql-scalar#date-and-time-functions">Time functions</a> for more information about timestamp handling.</p><p>The following table describes how Druid maps SQL types onto native types when running queries:</p><table><thead><tr><th>SQL type</th><th>Druid runtime type</th><th>Default value<sup>*</sup></th><th>Notes</th></tr></thead><tbody><tr><td>CHAR</td><td>STRING</td><td><code>''</code></td><td></td></tr><tr><td>VARCHAR</td><td>STRING</td><td><code>''</code></td><td>Druid STRING columns are reported as VARCHAR. Can include <a href="#multi-value-strings">multi-value strings</a> as well.</td></tr><tr><td>DECIMAL</td><td>DOUBLE</td><td><code>0.0</code></td><td>DECIMAL uses floating point, not fixed point math</td></tr><tr><td>FLOAT</td><td>FLOAT</td><td><code>0.0</code></td><td>Druid FLOAT columns are reported as FLOAT</td></tr><tr><td>REAL</td><td>DOUBLE</td><td><code>0.0</code></td><td></td></tr><tr><td>DOUBLE</td><td>DOUBLE</td><td><code>0.0</code></td><td>Druid DOUBLE columns are reported as DOUBLE</td></tr><tr><td>BOOLEAN</td><td>LONG</td><td><code>false</code></td><td></td></tr><tr><td>TINYINT</td><td>LONG</td><td><code>0</code></td><td></td></tr><tr><td>SMALLINT</td><td>LONG</td><td><code>0</code></td><td></td></tr><tr><td>INTEGER</td><td>LONG</td><td><code>0</code></td><td></td></tr><tr><td>BIGINT</td><td>LONG</td><td><code>0</code></td><td>Druid LONG columns (except <code>__time</code>) are reported as BIGINT</td></tr><tr><td>TIMESTAMP</td><td>LONG</td><td><code>0</code>, meaning 1970-01-01 00:00:00 UTC</td><td>Druid's <code>__time</code> column is reported as TIMESTAMP. Casts between string and timestamp types assume standard SQL formatting, such as <code>2000-01-02 03:04:05</code>, not ISO 8601 formatting. For handling other formats, use one of the <a href="/docs/latest/querying/sql-scalar#date-and-time-functions">time functions</a>.</td></tr><tr><td>DATE</td><td>LONG</td><td><code>0</code>, meaning 1970-01-01</td><td>Casting TIMESTAMP to DATE rounds down the timestamp to the nearest day. Casts between string and date types assume standard SQL formatting<!-- -->—<!-- -->for example, <code>2000-01-02</code>. For handling other formats, use one of the <a href="/docs/latest/querying/sql-scalar#date-and-time-functions">time functions</a>.</td></tr><tr><td>ARRAY</td><td>ARRAY</td><td><code>NULL</code></td><td>Druid native array types work as SQL arrays, and multi-value strings can be converted to arrays. See <a href="#arrays">Arrays</a> for more information.</td></tr><tr><td>OTHER</td><td>COMPLEX</td><td>none</td><td>May represent various Druid column types such as hyperUnique, approxHistogram, etc.</td></tr></tbody></table><sup>*</sup>The default value is <code>NULL</code> for all types.<br><br>For casts between two SQL types, the behavior depends on the runtime type:<ul><li><p>Casts between two SQL types with the same Druid runtime type have no effect other than the exceptions noted in the table.</p></li><li><p>Casts between two SQL types that have different Druid runtime types generate a runtime cast in Druid.</p></li></ul><p>If a value cannot be cast to the target type, as in <code>CAST('foo' AS BIGINT)</code>, Druid a substitutes <a href="#null-values">NULL</a>.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="arrays">Arrays<a href="#arrays" class="hash-link" aria-label="Direct link to Arrays" title="Direct link to Arrays"></a></h2><p>Druid supports <a href="/docs/latest/querying/arrays"><code>ARRAY</code> types</a>, which behave as standard SQL arrays, where results are grouped by matching entire arrays. The <a href="/docs/latest/querying/sql#unnest"><code>UNNEST</code> operator</a> can be used to perform operations on individual array elements, translating each element into a separate row. </p><p><code>ARRAY</code> typed columns can be stored in segments with JSON-based ingestion using the 'auto' typed dimension schema shared with <a href="/docs/latest/ingestion/schema-design#schema-auto-discovery-for-dimensions">schema auto-discovery</a> to detect and ingest arrays as ARRAY typed columns. For <a href="/docs/latest/multi-stage-query/">SQL based ingestion</a>, the query context parameter <code>arrayIngestMode</code> must be specified as <code>"array"</code> to ingest ARRAY types. In Druid 28, the default mode for this parameter is <code>"mvd"</code> for backwards compatibility, which instead can only handle <code>ARRAY<STRING></code> which it stores in <a href="#multi-value-strings">multi-value string columns</a>. </p><p>You can convert multi-value dimensions to standard SQL arrays explicitly with <code>MV_TO_ARRAY</code> or implicitly using <a href="/docs/latest/querying/sql-array-functions">array functions</a>. You can also use the array functions to construct arrays from multiple columns.</p><p>Druid serializes <code>ARRAY</code> results as a JSON string of the array by default, which can be controlled by the context parameter |
| <a href="/docs/latest/querying/sql-query-context"><code>sqlStringifyArrays</code></a>. When set to <code>false</code> and using JSON <a href="/docs/latest/api-reference/sql-api#responses">result formats</a>, the arrays will instead be returned as regular JSON arrays instead of in stringified form.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="multi-value-strings">Multi-value strings<a href="#multi-value-strings" class="hash-link" aria-label="Direct link to Multi-value strings" title="Direct link to Multi-value strings"></a></h2><p>Druid's native type system allows strings to have multiple values. These <a href="/docs/latest/querying/multi-value-dimensions">multi-value string dimensions</a> are reported in SQL as type VARCHAR and can be |
| syntactically used like any other VARCHAR. Regular string functions that refer to multi-value string dimensions are applied to all values for each row individually.</p><p>You can treat multi-value string dimensions as arrays using special |
| <a href="/docs/latest/querying/sql-multivalue-string-functions">multi-value string functions</a>, which perform powerful array-aware operations, but retain their VARCHAR type and behavior.</p><p>Grouping by multi-value dimensions observes the native Druid multi-value aggregation behavior, which is similar to an implicit SQL UNNEST. See <a href="/docs/latest/querying/multi-value-dimensions#grouping">Grouping</a> for more information.</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>Because the SQL planner treats multi-value dimensions as VARCHAR, there are some inconsistencies between how they are handled in Druid SQL and in native queries. For instance, expressions involving multi-value dimensions may be incorrectly optimized by the Druid SQL planner. For example, <code>multi_val_dim = 'a' AND multi_val_dim = 'b'</code> is optimized to |
| <code>false</code>, even though it is possible for a single row to have both <code>'a'</code> and <code>'b'</code> as values for <code>multi_val_dim</code>.</p><p>The SQL behavior of multi-value dimensions may change in a future release to more closely align with their behavior in native queries, but the <a href="/docs/latest/querying/sql-multivalue-string-functions">multi-value string functions</a> should be able to provide nearly all possible native functionality.</p></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="multi-value-strings-behavior">Multi-value strings behavior<a href="#multi-value-strings-behavior" class="hash-link" aria-label="Direct link to Multi-value strings behavior" title="Direct link to Multi-value strings behavior"></a></h2><p>The behavior of Druid <a href="/docs/latest/querying/multi-value-dimensions">multi-value string dimensions</a> varies depending on the context of |
| their usage.</p><p>When used with standard VARCHAR functions which expect a single input value per row, such as CONCAT, Druid will map |
| the function across all values in the row. If the row is null or empty, the function receives <code>NULL</code> as its input.</p><p>When used with the explicit <a href="/docs/latest/querying/sql-multivalue-string-functions">multi-value string functions</a>, Druid processes the |
| row values as if they were ARRAY typed. Any operations which produce null and empty rows are distinguished as |
| separate values (unlike implicit mapping behavior). These multi-value string functions, typically denoted with an <code>MV_</code> |
| prefix, retain their VARCHAR type after the computation is complete. Note that Druid multi-value columns do <em>not</em> |
| distinguish between empty and null rows. An empty row will never appear natively as input to a multi-valued function, |
| but any multi-value function which manipulates the array form of the value may produce an empty array, which is handled |
| separately while processing.</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> Do not mix the usage of multi-value functions and normal scalar functions within the same expression, as the planner will be unable |
| to determine how to properly process the value given its ambiguous usage. A multi-value string must be treated consistently within |
| an expression.</p></div></div><p>When converted to ARRAY or used with <a href="/docs/latest/querying/sql-array-functions">array functions</a>, multi-value strings behave as standard SQL arrays and can no longer |
| be manipulated with non-array functions.</p><p>By default Druid serializes multi-value VARCHAR results as a JSON string of the array, if grouping was not applied on the value. |
| If the value was grouped, due to the implicit UNNEST behavior, all results will always be standard single value |
| VARCHAR. ARRAY typed results serialization is controlled with the context parameter <a href="/docs/latest/querying/sql-query-context"><code>sqlStringifyArrays</code></a>. When set |
| to <code>false</code> and using JSON <a href="/docs/latest/api-reference/sql-api#responses">result formats</a>, the arrays will instead be returned |
| as regular JSON arrays instead of in stringified form.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="null-values">NULL values<a href="#null-values" class="hash-link" aria-label="Direct link to NULL values" title="Direct link to NULL values"></a></h2><p>By default, Druid treats NULL values similarly to the ANSI SQL standard.</p><p>For examples of null handling, see the <a href="/docs/latest/tutorials/tutorial-sql-null">null handling tutorial</a>.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="boolean-logic">Boolean logic<a href="#boolean-logic" class="hash-link" aria-label="Direct link to Boolean logic" title="Direct link to Boolean logic"></a></h2><p>Druid uses <a href="https://en.wikipedia.org/wiki/Three-valued_logic#SQL" target="_blank" rel="noopener noreferrer">SQL three-valued logic</a> for filter processing and boolean expression evaluation.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="nested-columns">Nested columns<a href="#nested-columns" class="hash-link" aria-label="Direct link to Nested columns" title="Direct link to Nested columns"></a></h2><p>Druid supports storing nested data structures in segments using the native <code>COMPLEX<json></code> type. See <a href="/docs/latest/querying/nested-columns">Nested columns</a> for more information.</p><p>You can interact with nested data using <a href="/docs/latest/querying/sql-json-functions">JSON functions</a>, which can extract nested values, parse from string, serialize to string, and create new <code>COMPLEX<json></code> structures.</p><p>COMPLEX types have limited functionality outside the specialized functions that use them, so their behavior is undefined when:</p><ul><li>Grouping on complex values.</li><li>Filtering directly on complex values.</li><li>Used as inputs to aggregators without specialized handling for a specific complex type.</li></ul><p>In many cases, functions are provided to translate COMPLEX value types to STRING, which serves as a workaround solution until COMPLEX type functionality can be improved.</p></div></article><nav class="pagination-nav docusaurus-mt-lg" aria-label="Docs pages"><a class="pagination-nav__link pagination-nav__link--prev" href="/docs/latest/querying/query-deep-storage"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Query from deep storage</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/latest/querying/sql-operators"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Operators</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="#standard-types" class="table-of-contents__link toc-highlight">Standard types</a></li><li><a href="#arrays" class="table-of-contents__link toc-highlight">Arrays</a></li><li><a href="#multi-value-strings" class="table-of-contents__link toc-highlight">Multi-value strings</a></li><li><a href="#multi-value-strings-behavior" class="table-of-contents__link toc-highlight">Multi-value strings behavior</a></li><li><a href="#null-values" class="table-of-contents__link toc-highlight">NULL values</a></li><li><a href="#boolean-logic" class="table-of-contents__link toc-highlight">Boolean logic</a></li><li><a href="#nested-columns" class="table-of-contents__link toc-highlight">Nested columns</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.f17df138.js"></script> |
| <script src="/assets/js/main.e3bd5681.js"></script> |
| </body> |
| </html> |
