| <!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/lookups"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="generator" content="Docusaurus v2.4.1"> |
| <title data-rh="true">Lookups | 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/27.0.0/querying/lookups"><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="Lookups | 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/27.0.0/querying/lookups"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/27.0.0/querying/lookups" hreflang="en"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/27.0.0/querying/lookups" 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.f80751b3.css"> |
| <link rel="preload" href="/assets/js/runtime~main.5371e784.js" as="script"> |
| <link rel="preload" href="/assets/js/main.832012d1.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/27.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/27.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/27.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/27.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/27.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/27.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/27.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 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/27.0.0/querying/sql">Druid SQL</a></div></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/27.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/27.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/27.0.0/querying/troubleshooting">Troubleshooting</a></li><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/27.0.0/querying/datasource">Concepts</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/27.0.0/querying/datasource">Datasources</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/27.0.0/querying/joins">Joins</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/27.0.0/querying/lookups">Lookups</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/27.0.0/querying/multi-value-dimensions">Multi-value dimensions</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/27.0.0/querying/nested-columns">Nested columns</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/27.0.0/querying/multitenancy">Multitenancy</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/27.0.0/querying/caching">Query caching</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/27.0.0/querying/using-caching">Using query caching</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/27.0.0/querying/query-context">Query context</a></li></ul></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/27.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/27.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/27.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/27.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/27.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/27.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/27.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">Concepts</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">Lookups</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>Lookups</h1></header><p>Lookups are a concept in Apache Druid where dimension values are (optionally) replaced with new values, allowing join-like |
| functionality. Applying lookups in Druid is similar to joining a dimension table in a data warehouse. See |
| <a href="/docs/27.0.0/querying/dimensionspecs">dimension specs</a> for more information. For the purpose of these documents, a "key" |
| refers to a dimension value to match, and a "value" refers to its replacement. So if you wanted to map |
| <code>appid-12345</code> to <code>Super Mega Awesome App</code> then the key would be <code>appid-12345</code> and the value would be |
| <code>Super Mega Awesome App</code>.</p><p>It is worth noting that lookups support not just use cases where keys map one-to-one to unique values, such as country |
| code and country name, but also support use cases where multiple IDs map to the same value, e.g. multiple app-ids |
| mapping to a single account manager. When lookups are one-to-one, Druid is able to apply additional optimizations at |
| query time; see <a href="#query-execution">Query execution</a> below for more details.</p><p>Lookups do not have history. They always use the current data. This means that if the chief account manager for a |
| particular app-id changes, and you issue a query with a lookup to store the app-id to account manager relationship, |
| it will return the current account manager for that app-id REGARDLESS of the time range over which you query.</p><p>If you require data time range sensitive lookups, such a use case is not currently supported dynamically at query time, |
| and such data belongs in the raw denormalized data for use in Druid.</p><p>Lookups are generally preloaded in-memory on all servers. But very small lookups (on the order of a few dozen to a few |
| hundred entries) can also be passed inline in native queries time using the "map" lookup type. Refer to the |
| <a href="/docs/27.0.0/querying/dimensionspecs">dimension specs</a> documentation for details.</p><p>Other lookup types are available as extensions, including:</p><ul><li>Globally cached lookups from local files, remote URIs, or JDBC through <a href="/docs/27.0.0/development/extensions-core/lookups-cached-global">lookups-cached-global</a>.</li><li>Globally cached lookups from a Kafka topic through <a href="/docs/27.0.0/development/extensions-core/kafka-extraction-namespace">kafka-extraction-namespace</a>.</li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="query-syntax">Query Syntax<a href="#query-syntax" class="hash-link" aria-label="Direct link to Query Syntax" title="Direct link to Query Syntax"></a></h2><p>In <a href="/docs/27.0.0/querying/sql">Druid SQL</a>, lookups can be queried using the <a href="/docs/27.0.0/querying/sql-scalar#string-functions"><code>LOOKUP</code> function</a>, 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><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> LOOKUP</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">store</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)">'store_to_country'</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"> country</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">revenue</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"> sales</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">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>They can also be queried using the <a href="/docs/27.0.0/querying/datasource#join">JOIN operator</a>:</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><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> store_to_country</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">v </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> country</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token function" style="color:rgb(130, 170, 255)">SUM</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">sales</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">revenue</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"> country_revenue</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">FROM</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> sales</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </span><span class="token keyword" style="font-style:italic">INNER</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">JOIN</span><span class="token plain"> lookup</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">store_to_country </span><span class="token keyword" style="font-style:italic">ON</span><span class="token plain"> sales</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">store </span><span class="token operator" style="color:rgb(137, 221, 255)">=</span><span class="token plain"> store_to_country</span><span class="token punctuation" style="color:rgb(199, 146, 234)">.</span><span class="token plain">k</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><span class="token keyword" style="font-style:italic">GROUP</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">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>In native queries, lookups can be queried with <a href="/docs/27.0.0/querying/dimensionspecs">dimension specs or extraction functions</a>.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="query-execution">Query Execution<a href="#query-execution" class="hash-link" aria-label="Direct link to Query Execution" title="Direct link to Query Execution"></a></h2><p>When executing an aggregation query involving lookup functions (like the SQL <a href="/docs/27.0.0/querying/sql-scalar#string-functions"><code>LOOKUP</code> function</a>, |
| Druid can decide to apply them while scanning and aggregating rows, or to apply them after aggregation is complete. It |
| is more efficient to apply lookups after aggregation is complete, so Druid will do this if it can. Druid decides this |
| by checking if the lookup is marked as "injective" or not. In general, you should set this property for any lookup that |
| is naturally one-to-one, to allow Druid to run your queries as fast as possible.</p><p>Injective lookups should include <em>all</em> possible keys that may show up in your dataset, and should also map all keys to |
| <em>unique values</em>. This matters because non-injective lookups may map different keys to the same value, which must be |
| accounted for during aggregation, lest query results contain two result values that should have been aggregated into |
| one.</p><p>This lookup is injective (assuming it contains all possible keys from your data):</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">1 -> Foo</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2 -> Bar</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">3 -> Billy</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>But this one is not, since both "2" and "3" map to the same value:</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">1 -> Foo</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2 -> Bar</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">3 -> Bar</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 tell Druid that your lookup is injective, you must specify <code>"injective" : true</code> in the lookup configuration. Druid |
| will not detect this automatically.</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> Currently, the injective lookup optimization is not triggered when lookups are inputs to a |
| <a href="/docs/27.0.0/querying/datasource#join">join datasource</a>. It is only used when lookup functions are used directly, without the join |
| operator.</p></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="dynamic-configuration">Dynamic Configuration<a href="#dynamic-configuration" class="hash-link" aria-label="Direct link to Dynamic Configuration" title="Direct link to Dynamic Configuration"></a></h2><p>The following documents the behavior of the cluster-wide config which is accessible through the Coordinator. |
| The configuration is propagated through the concept of "tier" of servers. |
| A "tier" is defined as a group of services which should receive a set of lookups. |
| For example, you might have all Historicals be part of <code>__default</code>, and Peons be part of individual tiers for the datasources they are tasked with. |
| The tiers for lookups are completely independent of Historical tiers.</p><p>These configs are accessed using JSON through the following URI template</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">http://<COORDINATOR_IP>:<PORT>/druid/coordinator/v1/lookups/config/{tier}/{id}</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>All URIs below are assumed to have <code>http://<COORDINATOR_IP>:<PORT></code> prepended.</p><p>If you have NEVER configured lookups before, you MUST post an empty json object <code>{}</code> to <code>/druid/coordinator/v1/lookups/config</code> to initialize the configuration.</p><p>These endpoints will return one of the following results:</p><ul><li>404 if the resource is not found</li><li>400 if there is a problem in the formatting of the request</li><li>202 if the request was accepted asynchronously (<code>POST</code> and <code>DELETE</code>)</li><li>200 if the request succeeded (<code>GET</code> only)</li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="configuration-propagation-behavior">Configuration propagation behavior<a href="#configuration-propagation-behavior" class="hash-link" aria-label="Direct link to Configuration propagation behavior" title="Direct link to Configuration propagation behavior"></a></h2><p>The configuration is propagated to the query serving processes (Broker / Router / Peon / Historical) by the Coordinator. |
| The query serving processes have an internal API for managing lookups on the process and those are used by the Coordinator. |
| The Coordinator periodically checks if any of the processes need to load/drop lookups and updates them appropriately.</p><p>Please note that only 2 simultaneous lookup configuration propagation requests can be concurrently handled by a single query serving process. This limit is applied to prevent lookup handling from consuming too many server HTTP connections.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="api">API<a href="#api" class="hash-link" aria-label="Direct link to API" title="Direct link to API"></a></h2><p>See <a href="/docs/27.0.0/api-reference/lookups-api">Lookups API</a> for reference on configuring lookups and lookup status. </p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="configuration">Configuration<a href="#configuration" class="hash-link" aria-label="Direct link to Configuration" title="Direct link to Configuration"></a></h2><p>See <a href="/docs/27.0.0/configuration/#lookups-dynamic-configuration">Lookups Dynamic Configuration</a> for Coordinator configuration.</p><p>To configure a Broker / Router / Historical / Peon to announce itself as part of a lookup tier, use following properties.</p><table><thead><tr><th>Property</th><th>Description</th><th>Default</th></tr></thead><tbody><tr><td><code>druid.lookup.lookupTier</code></td><td>The tier for <strong>lookups</strong> for this process. This is independent of other tiers.</td><td><code>__default</code></td></tr><tr><td><code>druid.lookup.lookupTierIsDatasource</code></td><td>For some things like indexing service tasks, the datasource is passed in the runtime properties of a task. This option fetches the tierName from the same value as the datasource for the task. It is suggested to only use this as Peon options for the indexing service, if at all. If true, <code>druid.lookup.lookupTier</code> MUST NOT be specified</td><td><code>"false"</code></td></tr></tbody></table><p>To configure the behavior of the dynamic configuration manager, use the following properties on the Coordinator:</p><table><thead><tr><th>Property</th><th>Description</th><th>Default</th></tr></thead><tbody><tr><td><code>druid.manager.lookups.hostTimeout</code></td><td>Timeout (in ms) PER HOST for processing request</td><td><code>2000</code>(2 seconds)</td></tr><tr><td><code>druid.manager.lookups.allHostTimeout</code></td><td>Timeout (in ms) to finish lookup management on all the processes.</td><td><code>900000</code>(15 mins)</td></tr><tr><td><code>druid.manager.lookups.period</code></td><td>How long to pause between management cycles</td><td><code>120000</code>(2 mins)</td></tr><tr><td><code>druid.manager.lookups.threadPoolSize</code></td><td>Number of service processes that can be managed concurrently</td><td><code>10</code></td></tr></tbody></table><h2 class="anchor anchorWithStickyNavbar_LWe7" id="saving-configuration-across-restarts">Saving configuration across restarts<a href="#saving-configuration-across-restarts" class="hash-link" aria-label="Direct link to Saving configuration across restarts" title="Direct link to Saving configuration across restarts"></a></h2><p>It is possible to save the configuration across restarts such that a process will not have to wait for Coordinator action to re-populate its lookups. To do this the following property is set:</p><table><thead><tr><th>Property</th><th>Description</th><th>Default</th></tr></thead><tbody><tr><td><code>druid.lookup.snapshotWorkingDir</code></td><td>Working path used to store snapshot of current lookup configuration, leaving this property null will disable snapshot/bootstrap utility</td><td>null</td></tr><tr><td><code>druid.lookup.enableLookupSyncOnStartup</code></td><td>Enable the lookup synchronization process with Coordinator on startup. The queryable processes will fetch and load the lookups from the Coordinator instead of waiting for the Coordinator to load the lookups for them. Users may opt to disable this option if there are no lookups configured in the cluster.</td><td>true</td></tr><tr><td><code>druid.lookup.numLookupLoadingThreads</code></td><td>Number of threads for loading the lookups in parallel on startup. This thread pool is destroyed once startup is done. It is not kept during the lifetime of the JVM</td><td>Available Processors / 2</td></tr><tr><td><code>druid.lookup.coordinatorFetchRetries</code></td><td>How many times to retry to fetch the lookup bean list from Coordinator, during the sync on startup.</td><td>3</td></tr><tr><td><code>druid.lookup.lookupStartRetries</code></td><td>How many times to retry to start each lookup, either during the sync on startup, or during the runtime.</td><td>3</td></tr><tr><td><code>druid.lookup.coordinatorRetryDelay</code></td><td>How long to delay (in millis) between retries to fetch lookup list from the Coordinator during the sync on startup.</td><td>60_000</td></tr></tbody></table><h2 class="anchor anchorWithStickyNavbar_LWe7" id="introspect-a-lookup">Introspect a Lookup<a href="#introspect-a-lookup" class="hash-link" aria-label="Direct link to Introspect a Lookup" title="Direct link to Introspect a Lookup"></a></h2><p>The Broker provides an API for lookup introspection if the lookup type implements a <code>LookupIntrospectHandler</code>.</p><p>A <code>GET</code> request to <code>/druid/v1/lookups/introspect/{lookupId}</code> will return the map of complete values.</p><p>ex: <code>GET /druid/v1/lookups/introspect/nato-phonetic</code></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">{</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "A": "Alfa",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "B": "Bravo",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "C": "Charlie",</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"> "Y": "Yankee",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "Z": "Zulu",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "-": "Dash"</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" style="display:inline-block"></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>The list of keys can be retrieved via <code>GET</code> to <code>/druid/v1/lookups/introspect/{lookupId}/keys"</code></p><p>ex: <code>GET /druid/v1/lookups/introspect/nato-phonetic/keys</code></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">[</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "A",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "B",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "C",</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"> "Y",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "Z",</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">]</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>A <code>GET</code> request to <code>/druid/v1/lookups/introspect/{lookupId}/values"</code> will return the list of values.</p><p>ex: <code>GET /druid/v1/lookups/introspect/nato-phonetic/values</code></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">[</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "Alfa",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "Bravo",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "Charlie",</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"> "Yankee",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "Zulu",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> "Dash"</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">]</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/27.0.0/querying/joins"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Joins</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/27.0.0/querying/multi-value-dimensions"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Multi-value dimensions</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="#query-syntax" class="table-of-contents__link toc-highlight">Query Syntax</a></li><li><a href="#query-execution" class="table-of-contents__link toc-highlight">Query Execution</a></li><li><a href="#dynamic-configuration" class="table-of-contents__link toc-highlight">Dynamic Configuration</a></li><li><a href="#configuration-propagation-behavior" class="table-of-contents__link toc-highlight">Configuration propagation behavior</a></li><li><a href="#api" class="table-of-contents__link toc-highlight">API</a></li><li><a href="#configuration" class="table-of-contents__link toc-highlight">Configuration</a></li><li><a href="#saving-configuration-across-restarts" class="table-of-contents__link toc-highlight">Saving configuration across restarts</a></li><li><a href="#introspect-a-lookup" class="table-of-contents__link toc-highlight">Introspect a Lookup</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.5371e784.js"></script> |
| <script src="/assets/js/main.832012d1.js"></script> |
| </body> |
| </html> |
