| <!doctype html> |
| <html lang="en" dir="ltr" class="docs-wrapper docs-doc-page docs-version-current plugin-docs plugin-id-default docs-doc-id-tutorials/tutorial-sketches-theta" data-has-hydrated="false"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="generator" content="Docusaurus v2.4.3"> |
| <title data-rh="true">Approximations with Theta sketches | 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/31.0.0/tutorials/tutorial-sketches-theta"><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="Approximations with Theta sketches | 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/31.0.0/tutorials/tutorial-sketches-theta"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/31.0.0/tutorials/tutorial-sketches-theta" hreflang="en"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/31.0.0/tutorials/tutorial-sketches-theta" 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.60a7f877.css"> |
| <link rel="preload" href="/assets/js/runtime~main.7d8e1a79.js" as="script"> |
| <link rel="preload" href="/assets/js/main.f9c13e33.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/31.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-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/31.0.0/design/">Introduction to Apache Druid</a></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--active" aria-expanded="true" href="/docs/31.0.0/tutorials/">Getting started</a><button aria-label="Toggle the collapsible sidebar category 'Getting started'" type="button" class="clean-btn menu__caret"></button></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-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/31.0.0/operations/single-server">Single server deployment</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/31.0.0/tutorials/docker">Run with Docker</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/31.0.0/tutorials/cluster">Clustered deployment</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/31.0.0/tutorials/tutorial-msq-extern">Ingestion tutorials</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/31.0.0/tutorials/tutorial-retention">Data management tutorials</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/31.0.0/tutorials/tutorial-query">Querying tutorials</a></div></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link menu__link--active" aria-current="page" tabindex="0" href="/docs/31.0.0/tutorials/tutorial-sketches-theta">Theta sketches tutorial</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/31.0.0/tutorials/tutorial-jdbc">JDBC connector tutorial</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/31.0.0/tutorials/tutorial-batch-hadoop">Hadoop tutorials</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/31.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" aria-expanded="false" href="/docs/31.0.0/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/31.0.0/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 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/31.0.0/querying/sql">Querying</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/31.0.0/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/31.0.0/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/31.0.0/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/31.0.0/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/31.0.0/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/31.0.0/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 itemscope="" itemprop="itemListElement" itemtype="https://schema.org/ListItem" class="breadcrumbs__item"><a class="breadcrumbs__link" itemprop="item" href="/docs/31.0.0/tutorials/"><span itemprop="name">Getting started</span></a><meta itemprop="position" content="1"></li><li itemscope="" itemprop="itemListElement" itemtype="https://schema.org/ListItem" class="breadcrumbs__item breadcrumbs__item--active"><span class="breadcrumbs__link" itemprop="name">Theta sketches tutorial</span><meta itemprop="position" content="2"></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>Approximations with Theta sketches</h1></header><p>Apache Druid can power real-time collection, streaming, and interactive visualization of clickstreams. |
| A common problem in clickstream analytics is counting unique things, like visitors or sessions. |
| Generally this involves scanning through all detail data, because unique counts do not add up as you aggregate the numbers.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="the-problem-with-counts-and-set-operations-on-large-data-sets">The problem with counts and set operations on large data sets<a href="#the-problem-with-counts-and-set-operations-on-large-data-sets" class="hash-link" aria-label="Direct link to The problem with counts and set operations on large data sets" title="Direct link to The problem with counts and set operations on large data sets"></a></h2><p>Imagine you are interested in the number of visitors that watched episodes of a TV show. Let's say you found that at a given day, 1000 unique visitors watched the first episode, and 800 visitors watched the second episode. You may want to explore further trends, for example:</p><ul><li>How many visitors watched <em>both</em> episodes?</li><li>How many visitors are there that watched <em>at least one</em> of the episodes?</li><li>How many visitors watched episode 1 <em>but not</em> episode 2?</li></ul><p>There is no way to answer these questions by just looking at the aggregated numbers. You would have to go back to the detail data and scan every single row. If the data volume is high enough, this may take a very long time, meaning that an interactive data exploration is not possible.</p><p>An additional nuisance is that unique counts don't work well with rollups. For this example, it would be great if you could have just one row of data per 15 minute interval<sup><sup id="fnref-1"><a href="#fn-1" class="footnote-ref">1</a></sup></sup>, show, and episode. After all, you are not interested in the individual user IDs, just the unique counts.</p><p>Is there a way to avoid crunching the detail data every single time, and maybe even enable rollup? |
| Enter Theta sketches.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="use-theta-sketches-for-fast-approximation-with-set-operations">Use Theta sketches for fast approximation with set operations<a href="#use-theta-sketches-for-fast-approximation-with-set-operations" class="hash-link" aria-label="Direct link to Use Theta sketches for fast approximation with set operations" title="Direct link to Use Theta sketches for fast approximation with set operations"></a></h2><p>Use Theta sketches to obtain a fast approximate estimate for the distinct count of values used to build the sketches. |
| Theta sketches are a probabilistic data structure to enable approximate analysis of big data with known error distributions. |
| Druid's implementation relies on the <a href="https://datasketches.apache.org/" target="_blank" rel="noopener noreferrer">Apache DataSketches</a> library.</p><p>The following properties describe Theta sketches:</p><ul><li>Similar to other sketches, Theta sketches are <strong>mergeable</strong>. This means you can work with rolled up data and merge the sketches over various time intervals. Thus, you can take advantage of Druid's rollup feature.</li><li>Specific to sketches supported in Druid, Theta sketches support <strong>set operations</strong>. Given two Theta sketches over subsets of data, you can compute the union, intersection, or set difference of the two subsets. This enables you to answer questions like the number of visitors that watched a specific combination of episodes from the example.</li></ul><p>In this tutorial, you will learn how to do the following:</p><ul><li>Create Theta sketches from your input data at ingestion time.</li><li>Execute distinct count and set operation queries on the Theta sketches to explore the questions presented earlier.</li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="prerequisites">Prerequisites<a href="#prerequisites" class="hash-link" aria-label="Direct link to Prerequisites" title="Direct link to Prerequisites"></a></h2><p>Before proceeding, download Druid as described in the <a href="/docs/31.0.0/tutorials/">single-machine quickstart</a> and have it running on your local machine. You don't need to load any data into the Druid cluster.</p><p>It's helpful to have finished <a href="/docs/31.0.0/tutorials/tutorial-batch">Tutorial: Loading a file</a> and <a href="/docs/31.0.0/tutorials/tutorial-query">Tutorial: Querying data</a>.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="sample-data">Sample data<a href="#sample-data" class="hash-link" aria-label="Direct link to Sample data" title="Direct link to Sample data"></a></h2><p>This tutorial works with the following data:</p><ul><li><strong>date</strong>: a timestamp. In this case it's just dates but as mentioned earlier, a finer granularity makes sense in real life.</li><li><strong>uid</strong>: a user ID</li><li><strong>show</strong>: name of a TV show</li><li><strong>episode</strong>: episode identifier</li></ul><div class="language-csv codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-csv codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">date,uid,show,episode</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-19,alice,Game of Thrones,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-19,alice,Game of Thrones,S1E2</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-19,alice,Game of Thrones,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-19,bob,Bridgerton,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-20,alice,Game of Thrones,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-20,carol,Bridgerton,S1E2</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-20,dan,Bridgerton,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-21,alice,Game of Thrones,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-21,carol,Bridgerton,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-21,erin,Game of Thrones,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-21,alice,Bridgerton,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-22,bob,Game of Thrones,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-22,bob,Bridgerton,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-22,carol,Bridgerton,S1E2</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-22,bob,Bridgerton,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-22,erin,Game of Thrones,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-22,erin,Bridgerton,S1E2</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-23,erin,Game of Thrones,S1E1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">2022-05-23,alice,Game of Thrones,S1E1</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="ingest-data-using-theta-sketches">Ingest data using Theta sketches<a href="#ingest-data-using-theta-sketches" class="hash-link" aria-label="Direct link to Ingest data using Theta sketches" title="Direct link to Ingest data using Theta sketches"></a></h2><p>Load the sample dataset using the <a href="/docs/31.0.0/multi-stage-query/reference/#insert"><code>INSERT INTO</code></a> statement and the <a href="/docs/31.0.0/multi-stage-query/reference/#extern-function"><code>EXTERN</code></a> function to ingest the sample data inline. In the <a href="/docs/31.0.0/operations/web-console">Druid web console</a>, go to the <strong>Query</strong> view and run the following query:</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">INSERT</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">INTO</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"ts_tutorial"</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">WITH</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"source"</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><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"> </span><span class="token keyword" style="font-style:italic">TABLE</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"> EXTERN</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 string" style="color:rgb(195, 232, 141)">'{"type":"inline","data":"date,uid,show,episode\n2022-05-19,alice,Game of Thrones,S1E1\n2022-05-19,alice,Game of Thrones,S1E2\n2022-05-19,alice,Game of Thrones,S1E1\n2022-05-19,bob,Bridgerton,S1E1\n2022-05-20,alice,Game of Thrones,S1E1\n2022-05-20,carol,Bridgerton,S1E2\n2022-05-20,dan,Bridgerton,S1E1\n2022-05-21,alice,Game of Thrones,S1E1\n2022-05-21,carol,Bridgerton,S1E1\n2022-05-21,erin,Game of Thrones,S1E1\n2022-05-21,alice,Bridgerton,S1E1\n2022-05-22,bob,Game of Thrones,S1E1\n2022-05-22,bob,Bridgerton,S1E1\n2022-05-22,carol,Bridgerton,S1E2\n2022-05-22,bob,Bridgerton,S1E1\n2022-05-22,erin,Game of Thrones,S1E1\n2022-05-22,erin,Bridgerton,S1E2\n2022-05-23,erin,Game of Thrones,S1E1\n2022-05-23,alice,Game of Thrones,S1E1"}'</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 string" style="color:rgb(195, 232, 141)">'{"type":"csv","findColumnsFromHeader":true}'</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </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 punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> EXTEND </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"date"</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">VARCHAR</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)">"show"</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">VARCHAR</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)">"episode"</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">VARCHAR</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)">"uid"</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">VARCHAR</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 plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"></span><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"> TIME_FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">TIME_PARSE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"date"</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 plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">'P1D'</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"> </span><span class="token string" style="color:rgb(195, 232, 141)">"__time"</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 string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">"episode"</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)">COUNT</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token operator" style="color:rgb(137, 221, 255)">*</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"> </span><span class="token string" style="color:rgb(195, 232, 141)">"count"</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"> DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"uid"</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"> </span><span class="token string" style="color:rgb(195, 232, 141)">"theta_uid"</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"> </span><span class="token string" style="color:rgb(195, 232, 141)">"source"</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">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><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">2</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token number" style="color:rgb(247, 140, 108)">3</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">PARTITIONED </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">DAY</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>Notice the <code>theta_uid</code> column in the <code>SELECT</code> statement. It defines the <code>thetaSketch</code> aggregator on the <code>uid</code> column during ingestion. |
| In this scenario you are not interested in individual user IDs, only the unique counts. |
| Instead you create Theta sketches on the values of <code>uid</code> using the <code>DS_THETA</code> function.</p><p><a href="/docs/31.0.0/development/extensions-core/datasketches-theta#aggregator"><code>DS_THETA</code></a> has an optional second parameter that controls the accuracy and size of the sketches.</p><p>The <code>GROUP BY</code> statement groups the entries for each episode of a show watched on the same day.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="query-the-theta-sketch-column">Query the Theta sketch column<a href="#query-the-theta-sketch-column" class="hash-link" aria-label="Direct link to Query the Theta sketch column" title="Direct link to Query the Theta sketch column"></a></h2><p>Calculating a unique count estimate from a Theta sketch column involves the following steps:</p><ol><li>Merge the Theta sketches in the column by means of the <code>DS_THETA</code> <a href="/docs/31.0.0/querying/sql-aggregations#theta-sketch-functions">aggregator function</a> in Druid SQL.</li><li>Retrieve the estimate from the merged sketch with the <a href="/docs/31.0.0/querying/sql-scalar#theta-sketch-functions"><code>THETA_SKETCH_ESTIMATE</code></a> function.</li></ol><p>Between steps 1 and 2, you can apply set functions as demonstrated later in <a href="#set-operations">Set operations</a>.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="basic-counting">Basic counting<a href="#basic-counting" class="hash-link" aria-label="Direct link to Basic counting" title="Direct link to Basic counting"></a></h3><p>Let's first see what the data looks like in Druid. Run the following SQL statement in the query editor:</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"> ts_tutorial</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><img loading="lazy" alt="View data with SELECT all query" src="/assets/images/tutorial-theta-03-d396351338383bf3cbd248be97a7c9ae.png" width="1250" height="640" class="img_ev3q"></p><p>The Theta sketch column <code>theta_uid</code> appears as a Base64-encoded string; behind it is a bitmap.</p><p>The following query uses <code>THETA_SKETCH_ESTIMATE</code> to compute the distinct counts of user IDs and groups by the other dimensions:</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"> __time</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 string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">"episode"</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"> THETA_SKETCH_ESTIMATE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</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"> users</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"> ts_tutorial</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><img loading="lazy" alt="Count distinct with Theta sketches" src="/assets/images/tutorial-theta-04-7c498583c84f25374a035507a41592da.png" width="1250" height="640" class="img_ev3q"></p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="filtered-metrics">Filtered metrics<a href="#filtered-metrics" class="hash-link" aria-label="Direct link to Filtered metrics" title="Direct link to Filtered metrics"></a></h3><p>Druid has the capability to use <a href="/docs/31.0.0/querying/sql-aggregations">filtered metrics</a>. This means you can include a WHERE clause in the SELECT part of the query.</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> In the case of Theta sketches, the filter clause has to be inserted between the aggregator and the estimator.</p></div></div><p>As an example, query the total unique users that watched <em>Bridgerton</em>:</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"> APPROX_COUNT_DISTINCT_DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">'Bridgerton'</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"> users</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"> ts_tutorial</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><img loading="lazy" alt="Count distinct with Theta sketches and filters" src="/assets/images/tutorial-theta-05-210059e731382527a704510b94d370d4.png" width="1250" height="640" class="img_ev3q"></p><p>In the preceding query, <code>APPROX_COUNT_DISTINCT_DS_THETA</code> is equivalent to calling <code>DS_THETA</code> and <code>THETA_SKETCH_ESIMATE</code> as follows:</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"> THETA_SKETCH_ESTIMATE</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"> DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">'Bridgerton'</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 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"> users</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"> ts_tutorial</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 <code>APPROX_COUNT_DISTINCT_DS_THETA</code> function applies the following:</p><ul><li><code>DS_THETA</code>: Creates a new Theta sketch from the column of Theta sketches.</li><li><code>THETA_SKETCH_ESTIMATE</code>: Calculates the distinct count estimate from the output of <code>DS_THETA</code>.</li></ul><p>Note that the filter clause limits an aggregation query to only the rows that match the filter.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="set-operations">Set operations<a href="#set-operations" class="hash-link" aria-label="Direct link to Set operations" title="Direct link to Set operations"></a></h3><p>You can use this capability of filtering in the aggregator, together with <em>set operations</em>, to finally answer the questions from the introduction.</p><p>How many users watched both episodes of <em>Bridgerton?</em> Use <code>THETA_SKETCH_INTERSECT</code> to compute the unique count of the intersection of two (or more) segments:</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"> THETA_SKETCH_ESTIMATE</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"> THETA_SKETCH_INTERSECT</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"> DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">'Bridgerton'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"episode"</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 string" style="color:rgb(195, 232, 141)">'S1E1'</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 plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">'Bridgerton'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"episode"</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 string" style="color:rgb(195, 232, 141)">'S1E2'</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 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 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"> users</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"> ts_tutorial</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><img loading="lazy" alt="Count distinct with Theta sketches, filters, and set operations" src="/assets/images/tutorial-theta-06-4b588f7737377421937ed369bfe9a94e.png" width="1250" height="640" class="img_ev3q"></p><p>Again, the set function is spliced in between the aggregator and the estimator.</p><p>Likewise, use <code>THETA_SKETCH_UNION</code> to find the number of visitors that watched <em>any</em> of the episodes:</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"> THETA_SKETCH_ESTIMATE</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"> THETA_SKETCH_UNION</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"> DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">'Bridgerton'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"episode"</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 string" style="color:rgb(195, 232, 141)">'S1E1'</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 plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">'Bridgerton'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"episode"</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 string" style="color:rgb(195, 232, 141)">'S1E2'</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 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 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"> users</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"> ts_tutorial</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><img loading="lazy" alt="Count distinct with Theta sketches, filters, and set operations" src="/assets/images/tutorial-theta-07-8c74460cb2ed72066ca3b0b3470657e0.png" width="1250" height="640" class="img_ev3q"></p><p>And finally, there is <code>THETA_SKETCH_NOT</code> which computes the set difference of two or more segments. |
| The result describes how many visitors watched episode 1 of Bridgerton but not episode 2.</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"> THETA_SKETCH_ESTIMATE</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"> THETA_SKETCH_NOT</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"> DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">'Bridgerton'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"episode"</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 string" style="color:rgb(195, 232, 141)">'S1E1'</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 plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> DS_THETA</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">theta_uid</span><span class="token punctuation" style="color:rgb(199, 146, 234)">)</span><span class="token plain"> FILTER</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token keyword" style="font-style:italic">WHERE</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"show"</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 string" style="color:rgb(195, 232, 141)">'Bridgerton'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(137, 221, 255)">AND</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"episode"</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 string" style="color:rgb(195, 232, 141)">'S1E2'</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 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 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"> users</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"> ts_tutorial</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><img loading="lazy" alt="Count distinct with Theta sketches, filters, and set operations" src="/assets/images/tutorial-theta-08-8d2ab89aef3d1661329ad060467e2f9b.png" width="1250" height="640" class="img_ev3q"></p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="conclusions">Conclusions<a href="#conclusions" class="hash-link" aria-label="Direct link to Conclusions" title="Direct link to Conclusions"></a></h2><ul><li>Counting distinct things for large data sets can be done with Theta sketches in Apache Druid.</li><li>This allows us to use rollup and discard the individual values, just retaining statistical approximations in the sketches.</li><li>With Theta sketch set operations, affinity analysis is easier, for example, to answer questions such as which segments correlate or overlap by how much.</li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="learn-more">Learn more<a href="#learn-more" class="hash-link" aria-label="Direct link to Learn more" title="Direct link to Learn more"></a></h2><p>See the following topics for more information:</p><ul><li><a href="/docs/31.0.0/development/extensions-core/datasketches-theta">Theta sketch</a> for reference on ingestion and native queries on Theta sketches in Druid.</li><li><a href="/docs/31.0.0/querying/sql-scalar#theta-sketch-functions">Theta sketch scalar functions</a> and <a href="/docs/31.0.0/querying/sql-aggregations#theta-sketch-functions">Theta sketch aggregation functions</a> for Theta sketch functions in Druid SQL queries.</li><li><a href="/docs/31.0.0/ingestion/schema-design#sketches-for-high-cardinality-columns">Sketches for high cardinality columns</a> for Druid schema design involving sketches.</li><li><a href="/docs/31.0.0/development/extensions-core/datasketches-extension">DataSketches extension</a> for more information about the DataSketches extension in Druid as well as other available sketches.</li><li>The accuracy of queries using Theta sketches is governed by the size <em>k</em> of the Theta sketch and by the operations you perform. See more details in the <a href="https://datasketches.apache.org/docs/Theta/ThetaAccuracy.html" target="_blank" rel="noopener noreferrer">Apache DataSketches documentation</a>.</li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="acknowledgments">Acknowledgments<a href="#acknowledgments" class="hash-link" aria-label="Direct link to Acknowledgments" title="Direct link to Acknowledgments"></a></h2><p>This tutorial is adapted from a <a href="https://blog.hellmar-becker.de/2022/06/05/druid-data-cookbook-counting-unique-visitors-for-overlapping-segments/" target="_blank" rel="noopener noreferrer">blog post</a> by community member Hellmar Becker.</p><div class="footnotes"><hr><ol><li id="fn-1">Why 15 minutes and not just 1 hour? Intervals of 15 minutes work better with international timezones because those are not always aligned by hour. India, for instance, is 30 minutes off, and Nepal is even 45 minutes off. With 15 minute aggregates, you can get hourly sums for any of those timezones, too!<a href="#fnref-1" class="footnote-backref">↩</a></li></ol></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/31.0.0/tutorials/tutorial-latest-by"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Query for latest data</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/31.0.0/tutorials/tutorial-jdbc"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">JDBC connector tutorial</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="#the-problem-with-counts-and-set-operations-on-large-data-sets" class="table-of-contents__link toc-highlight">The problem with counts and set operations on large data sets</a></li><li><a href="#use-theta-sketches-for-fast-approximation-with-set-operations" class="table-of-contents__link toc-highlight">Use Theta sketches for fast approximation with set operations</a></li><li><a href="#prerequisites" class="table-of-contents__link toc-highlight">Prerequisites</a></li><li><a href="#sample-data" class="table-of-contents__link toc-highlight">Sample data</a></li><li><a href="#ingest-data-using-theta-sketches" class="table-of-contents__link toc-highlight">Ingest data using Theta sketches</a></li><li><a href="#query-the-theta-sketch-column" class="table-of-contents__link toc-highlight">Query the Theta sketch column</a><ul><li><a href="#basic-counting" class="table-of-contents__link toc-highlight">Basic counting</a></li><li><a href="#filtered-metrics" class="table-of-contents__link toc-highlight">Filtered metrics</a></li><li><a href="#set-operations" class="table-of-contents__link toc-highlight">Set operations</a></li></ul></li><li><a href="#conclusions" class="table-of-contents__link toc-highlight">Conclusions</a></li><li><a href="#learn-more" class="table-of-contents__link toc-highlight">Learn more</a></li><li><a href="#acknowledgments" class="table-of-contents__link toc-highlight">Acknowledgments</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.7d8e1a79.js"></script> |
| <script src="/assets/js/main.f9c13e33.js"></script> |
| </body> |
| </html> |
