| <!doctype html> |
| <html lang="en" dir="ltr" class="docs-wrapper plugin-docs plugin-id-default docs-version-current docs-doc-page docs-doc-id-tutorials/tutorial-rollup" data-has-hydrated="false"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="generator" content="Docusaurus v3.7.0"> |
| <title data-rh="true">Aggregate data with rollup | Apache® Druid</title><meta data-rh="true" name="viewport" content="width=device-width,initial-scale=1"><meta data-rh="true" name="twitter:card" content="summary_large_image"><meta data-rh="true" property="og:image" content="https://druid.apache.org/img/druid_nav.png"><meta data-rh="true" name="twitter:image" content="https://druid.apache.org/img/druid_nav.png"><meta data-rh="true" property="og:url" content="https://druid.apache.org/docs/latest/tutorials/tutorial-rollup"><meta data-rh="true" property="og:locale" content="en"><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="Aggregate data with rollup | Apache® Druid"><meta data-rh="true" name="description" content="<!--"><meta data-rh="true" property="og:description" content="<!--"><link data-rh="true" rel="icon" href="/img/favicon.png"><link data-rh="true" rel="canonical" href="https://druid.apache.org/docs/latest/tutorials/tutorial-rollup"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/latest/tutorials/tutorial-rollup" hreflang="en"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/latest/tutorials/tutorial-rollup" hreflang="x-default"><link rel="stylesheet" href="/css/all.css"> |
| <script src="/js/clipboard.min.js"></script><link rel="stylesheet" href="/assets/css/styles.f3140859.css"> |
| <script src="/assets/js/runtime~main.5b007a36.js" defer="defer"></script> |
| <script src="/assets/js/main.7b126984.js" defer="defer"></script> |
| </head> |
| <body class="navigation-with-keyboard"> |
| <script>!function(){function t(t){document.documentElement.setAttribute("data-theme",t)}var e=function(){try{return new URLSearchParams(window.location.search).get("docusaurus-theme")}catch(t){}}()||function(){try{return window.localStorage.getItem("theme")}catch(t){}}();t(null!==e?e:"light")}(),function(){try{const n=new URLSearchParams(window.location.search).entries();for(var[t,e]of n)if(t.startsWith("docusaurus-data-")){var a=t.replace("docusaurus-data-","data-");document.documentElement.setAttribute(a,e)}}catch(t){}}()</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="themedComponent_mlkZ themedComponent--light_NVdE"><img src="/img/druid_nav.png" alt="Apache® Druid" class="themedComponent_mlkZ themedComponent--dark_xIcU"></div></a></div><div class="navbar__items navbar__items--right"><a class="navbar__item navbar__link" href="/technology">Technology</a><a class="navbar__item navbar__link" href="/use-cases">Use Cases</a><a class="navbar__item navbar__link" href="/druid-powered">Powered By</a><a class="navbar__item navbar__link" href="/docs/latest/design/">Docs</a><a class="navbar__item navbar__link" href="/community/">Community</a><div class="navbar__item dropdown dropdown--hoverable dropdown--right"><a href="#" aria-haspopup="true" aria-expanded="false" role="button" class="navbar__link">Apache®</a><ul class="dropdown__menu"><li><a href="https://www.apache.org/" target="_blank" rel="noopener noreferrer" class="dropdown__link">Foundation<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://apachecon.com/?ref=druid.apache.org" target="_blank" rel="noopener noreferrer" class="dropdown__link">Events<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/licenses/" target="_blank" rel="noopener noreferrer" class="dropdown__link">License<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/foundation/thanks.html" target="_blank" rel="noopener noreferrer" class="dropdown__link">Thanks<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/security/" target="_blank" rel="noopener noreferrer" class="dropdown__link">Security<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/foundation/sponsorship.html" target="_blank" rel="noopener noreferrer" class="dropdown__link">Sponsorship<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li></ul></div><a class="navbar__item navbar__link" href="/downloads/">Download</a><div class="navbarSearchContainer_Bca1"><div class="navbar__search"><span aria-label="expand searchbar" role="button" class="search-icon" tabindex="0"></span><input id="search_input_react" type="search" 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"><div class="docsWrapper_hBAB"><button aria-label="Scroll back to top" class="clean-btn theme-back-to-top-button backToTopButton_sjWU" type="button"></button><div class="docRoot_UBD9"><aside class="theme-doc-sidebar-container docSidebarContainer_YfHR"><div class="sidebarViewport_aRkj"><div class="sidebar_njMd"><nav aria-label="Docs sidebar" class="menu thin-scrollbar menu_SIkG"><ul class="theme-doc-sidebar-menu menu__list"><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/latest/design/">Introduction to Apache Druid</a></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--active" href="/docs/latest/tutorials/">Getting started</a><button aria-label="Collapse sidebar category 'Getting started'" aria-expanded="true" 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/latest/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/latest/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/latest/tutorials/cluster">Clustered deployment</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" role="button" aria-expanded="true" tabindex="0" href="/docs/latest/tutorials/tutorial-msq-extern">Ingestion tutorials</a></div><ul style="display:block;overflow:visible;height:auto" class="menu__list"><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/tutorials/tutorial-msq-extern">Load files using SQL</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/tutorials/tutorial-kafka">Load from Apache Kafka</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link menu__link--active" aria-current="page" tabindex="0" href="/docs/latest/tutorials/tutorial-rollup">Aggregate data with rollup</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/tutorials/tutorial-ingestion-spec">Write an ingestion spec</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/tutorials/tutorial-transform">Transform input data</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/latest/tutorials/tutorial-msq-convert-spec">Convert ingestion spec to SQL</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" role="button" aria-expanded="false" tabindex="0" href="/docs/latest/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" role="button" aria-expanded="false" tabindex="0" href="/docs/latest/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" tabindex="0" href="/docs/latest/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/latest/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" role="button" aria-expanded="false" tabindex="0" href="/docs/latest/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" role="button" aria-expanded="false" href="/docs/latest/design/architecture">Design</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist" href="/docs/latest/ingestion/">Ingestion</a><button aria-label="Expand sidebar category 'Ingestion'" aria-expanded="false" 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" href="/docs/latest/data-management/">Data management</a><button aria-label="Expand sidebar category 'Data management'" aria-expanded="false" 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" role="button" aria-expanded="false" href="/docs/latest/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" href="/docs/latest/api-reference/">API reference</a><button aria-label="Expand sidebar category 'API reference'" aria-expanded="false" 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" href="/docs/latest/configuration/">Configuration</a><button aria-label="Expand sidebar category 'Configuration'" aria-expanded="false" 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" role="button" aria-expanded="false" href="/docs/latest/api-reference/automatic-compaction-api">Operations</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist" href="/docs/latest/development/overview">Development</a><button aria-label="Expand sidebar category 'Development'" aria-expanded="false" 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" role="button" aria-expanded="false" href="/docs/latest/release-info/release-notes">Release info</a></div></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/latest/misc/papers-and-talks">Papers</a></li></ul></nav></div></div></aside><main class="docMainContainer_TBSr"><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/latest/tutorials/"><span itemprop="name">Getting started</span></a><meta itemprop="position" content="1"></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Ingestion tutorials</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">Aggregate data with rollup</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>Aggregate data with rollup</h1></header><p>Apache Druid® can summarize raw data at ingestion time using a process known as "rollup." <a href="/docs/latest/multi-stage-query/concepts#rollup">Rollup</a> is a first-level aggregation operation over a selected set of columns that reduces the size of stored data.</p> |
| <p>This tutorial demonstrates how to apply rollup during ingestion and highlights its effects during query execution. The examples in the tutorial use the <a href="/docs/latest/multi-stage-query/">multi-stage query (MSQ)</a> task engine to execute SQL statements.</p> |
| <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 <a href="/docs/latest/tutorials/">Quickstart (local)</a> and have it running on your local machine. You don't need to load any data into the Druid cluster.</p> |
| <p>You should be familiar with data querying in Druid. If you haven't already, go through the <a href="/docs/latest/tutorials/tutorial-query">Query data</a> tutorial first.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="load-the-example-data">Load the example data<a href="#load-the-example-data" class="hash-link" aria-label="Direct link to Load the example data" title="Direct link to Load the example data"></a></h2> |
| <p>For this tutorial, you use a small sample of network flow event data representing IP traffic. |
| The data contains packet and byte counts from a source IP address to a destination IP address.</p> |
| <div class="language-json codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-json codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token punctuation" style="color:rgb(199, 146, 234)">{</span><span class="token property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:01:35Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">20</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">9024</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:01:51Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">255</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">21133</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:01:59Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">11</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">5780</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:02:14Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">38</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">6289</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:02:29Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">377</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">359971</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:03:29Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">49</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">10204</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-02T21:33:14Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"7.7.7.7"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"8.8.8.8"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">38</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">6289</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-02T21:33:45Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"7.7.7.7"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"8.8.8.8"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">123</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">93999</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-02T21:35:45Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"7.7.7.7"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"8.8.8.8"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">12</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">2818</span><span class="token punctuation" style="color:rgb(199, 146, 234)">}</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <p>Load the sample dataset using the <a href="/docs/latest/multi-stage-query/reference#insert"><code>INSERT INTO</code></a> statement and the <a href="/docs/latest/multi-stage-query/reference#extern-function"><code>EXTERN</code></a> function to ingest the data inline. In the <a href="/docs/latest/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" style="color:#bfc7d5;background-color:#292d3e"><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)">"rollup_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)">"inline_data"</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 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><span class="token operator" style="color:rgb(137, 221, 255)">*</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 keyword" style="font-style:italic">TABLE</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">EXTERN</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">'{</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token string" style="color:rgb(195, 232, 141)"> "type":"inline",</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token string" style="color:rgb(195, 232, 141)"> "data":"{\"timestamp\":\"2018-01-01T01:01:35Z\",\"srcIP\":\"1.1.1.1\",\"dstIP\":\"2.2.2.2\",\"packets\":20,\"bytes\":9024}\n{\"timestamp\":\"2018-01-01T01:02:14Z\",\"srcIP\":\"1.1.1.1\",\"dstIP\":\"2.2.2.2\",\"packets\":38,\"bytes\":6289}\n{\"timestamp\":\"2018-01-01T01:01:59Z\",\"srcIP\":\"1.1.1.1\",\"dstIP\":\"2.2.2.2\",\"packets\":11,\"bytes\":5780}\n{\"timestamp\":\"2018-01-01T01:01:51Z\",\"srcIP\":\"1.1.1.1\",\"dstIP\":\"2.2.2.2\",\"packets\":255,\"bytes\":21133}\n{\"timestamp\":\"2018-01-01T01:02:29Z\",\"srcIP\":\"1.1.1.1\",\"dstIP\":\"2.2.2.2\",\"packets\":377,\"bytes\":359971}\n{\"timestamp\":\"2018-01-01T01:03:29Z\",\"srcIP\":\"1.1.1.1\",\"dstIP\":\"2.2.2.2\",\"packets\":49,\"bytes\":10204}\n{\"timestamp\":\"2018-01-02T21:33:14Z\",\"srcIP\":\"7.7.7.7\",\"dstIP\":\"8.8.8.8\",\"packets\":38,\"bytes\":6289}\n{\"timestamp\":\"2018-01-02T21:33:45Z\",\"srcIP\":\"7.7.7.7\",\"dstIP\":\"8.8.8.8\",\"packets\":123,\"bytes\":93999}\n{\"timestamp\":\"2018-01-02T21:35:45Z\",\"srcIP\":\"7.7.7.7\",\"dstIP\":\"8.8.8.8\",\"packets\":12,\"bytes\":2818}"}'</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":"json"}'</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"> EXTEND </span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token string" style="color:rgb(195, 232, 141)">"timestamp"</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)">"srcIP"</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)">"dstIP"</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)">"packets"</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BIGINT</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)">"bytes"</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">BIGINT</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 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"> 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)">"timestamp"</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">TO</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">MINUTE</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"> __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)">"srcIP"</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)">"dstIP"</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 string" style="color:rgb(195, 232, 141)">"bytes"</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)">"bytes"</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 string" style="color:rgb(195, 232, 141)">"packets"</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)">"packets"</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 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)">"inline_data"</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>Note the following aspects of the ingestion statement:</p> |
| <ul> |
| <li>You transform the timestamp field using the <code>FLOOR</code> function to round timestamps down to the minute.</li> |
| <li>You group by the dimensions <code>timestamp</code>, <code>srcIP</code>, and <code>dstIP</code>.</li> |
| <li>You create the <code>bytes</code> and <code>packets</code> metrics, which are summed from their respective input fields.</li> |
| <li>You also create the <code>count</code> metric that records the number of rows that get rolled-up per each row in the datasource.</li> |
| </ul> |
| <p>With rollup, Druid combines rows with identical timestamp and dimension values after the timestamp truncation. Druid computes and stores the metric values using the specified aggregation function over each set of rolled-up rows.</p> |
| <p>After the ingestion completes, you can query the data.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="query-the-example-data">Query the example data<a href="#query-the-example-data" class="hash-link" aria-label="Direct link to Query the example data" title="Direct link to Query the example data"></a></h2> |
| <p>In the web console, open a new tab in the <strong>Query</strong> view. Run the following query to view the ingested data:</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" style="color:#bfc7d5;background-color:#292d3e"><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"> </span><span class="token string" style="color:rgb(195, 232, 141)">"rollup_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>Returns the following:</p> |
| <table><thead><tr><th><code>__time</code></th><th><code>srcIP</code></th><th><code>dstIP</code></th><th><code>bytes</code></th><th><code>count</code></th><th><code>packets</code></th></tr></thead><tbody><tr><td><code>2018-01-01T01:01:00.000Z</code></td><td><code>1.1.1.1</code></td><td><code>2.2.2.2</code></td><td><code>35,937</code></td><td><code>3</code></td><td><code>286</code></td></tr><tr><td><code>2018-01-01T01:02:00.000Z</code></td><td><code>1.1.1.1</code></td><td><code>2.2.2.2</code></td><td><code>366,260</code></td><td><code>2</code></td><td><code>415</code></td></tr><tr><td><code>2018-01-01T01:03:00.000Z</code></td><td><code>1.1.1.1</code></td><td><code>2.2.2.2</code></td><td><code>10,204</code></td><td><code>1</code></td><td><code>49</code></td></tr><tr><td><code>2018-01-02T21:33:00.000Z</code></td><td><code>7.7.7.7</code></td><td><code>8.8.8.8</code></td><td><code>100,288</code></td><td><code>2</code></td><td><code>161</code></td></tr><tr><td><code>2018-01-02T21:35:00.000Z</code></td><td><code>7.7.7.7</code></td><td><code>8.8.8.8</code></td><td><code>2,818</code></td><td><code>1</code></td><td><code>12</code></td></tr></tbody></table> |
| <p>Notice there are only five rows as opposed to the nine rows in the example data. In the next section, you explore the components of the rolled-up rows.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="view-rollup-in-action">View rollup in action<a href="#view-rollup-in-action" class="hash-link" aria-label="Direct link to View rollup in action" title="Direct link to View rollup in action"></a></h2> |
| <p>Consider the three events in the original input data that occur over the course of minute <code>2018-01-01T01:01</code>:</p> |
| <div class="language-json codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-json codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token punctuation" style="color:rgb(199, 146, 234)">{</span><span class="token property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:01:35Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">20</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">9024</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:01:51Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">255</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">21133</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:01:59Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">11</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">5780</span><span class="token punctuation" style="color:rgb(199, 146, 234)">}</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <p>Druid combines the three rows into one during rollup:</p> |
| <table><thead><tr><th><code>__time</code></th><th><code>srcIP</code></th><th><code>dstIP</code></th><th><code>bytes</code></th><th><code>count</code></th><th><code>packets</code></th></tr></thead><tbody><tr><td><code>2018-01-01T01:01:00.000Z</code></td><td><code>1.1.1.1</code></td><td><code>2.2.2.2</code></td><td><code>35,937</code></td><td><code>3</code></td><td><code>286</code></td></tr></tbody></table> |
| <p>Before the grouping occurs, the <code>FLOOR(TIME_PARSE("timestamp") TO MINUTE)</code> expression buckets (floors) the timestamp column of the original input by minute.</p> |
| <p>The input rows are grouped because they have the same values for their dimension columns <code>{timestamp, srcIP, dstIP}</code>. The metric columns calculate the sum aggregation of the grouped rows for <code>packets</code> and <code>bytes</code>. The <code>count</code> metric shows how many rows from the original input data contributed to the final rolled-up row.</p> |
| <p>Now, consider the two events in the original input data that occur over the course of minute <code>2018-01-01T01:02</code>:</p> |
| <div class="language-json codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-json codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token punctuation" style="color:rgb(199, 146, 234)">{</span><span class="token property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:02:14Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">38</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">6289</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 property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:02:29Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">377</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">359971</span><span class="token punctuation" style="color:rgb(199, 146, 234)">}</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <p>The rows are grouped into the following during rollup:</p> |
| <table><thead><tr><th><code>__time</code></th><th><code>srcIP</code></th><th><code>dstIP</code></th><th><code>bytes</code></th><th><code>count</code></th><th><code>packets</code></th></tr></thead><tbody><tr><td><code>2018-01-01T01:02:00.000Z</code></td><td><code>1.1.1.1</code></td><td><code>2.2.2.2</code></td><td><code>366,260</code></td><td><code>2</code></td><td><code>415</code></td></tr></tbody></table> |
| <p>In the original input data, only one event occurs over the course of minute <code>2018-01-01T01:03</code>:</p> |
| <div class="language-json codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-json codeBlock_bY9V thin-scrollbar" style="color:#bfc7d5;background-color:#292d3e"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token punctuation" style="color:rgb(199, 146, 234)">{</span><span class="token property">"timestamp"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2018-01-01T01:03:29Z"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"srcIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"1.1.1.1"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token plain"> </span><span class="token property">"dstIP"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token string" style="color:rgb(195, 232, 141)">"2.2.2.2"</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"packets"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">49</span><span class="token punctuation" style="color:rgb(199, 146, 234)">,</span><span class="token property">"bytes"</span><span class="token operator" style="color:rgb(137, 221, 255)">:</span><span class="token number" style="color:rgb(247, 140, 108)">10204</span><span class="token punctuation" style="color:rgb(199, 146, 234)">}</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <p>Therefore, no rollup takes place:</p> |
| <table><thead><tr><th><code>__time</code></th><th><code>srcIP</code></th><th><code>dstIP</code></th><th><code>bytes</code></th><th><code>count</code></th><th><code>packets</code></th></tr></thead><tbody><tr><td><code>2018-01-01T01:03:00.000Z</code></td><td><code>1.1.1.1</code></td><td><code>2.2.2.2</code></td><td><code>10,204</code></td><td><code>1</code></td><td><code>49</code></td></tr></tbody></table> |
| <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/latest/ingestion/rollup">Data rollup</a> for suggestions and best practices when performing rollup.</li> |
| <li><a href="/docs/latest/multi-stage-query/concepts#rollup">SQL-based ingestion concepts</a> for information on rollup using SQL-based ingestion.</li> |
| <li><a href="/docs/latest/multi-stage-query/examples#insert-with-rollup">SQL-based ingestion query examples</a> for another example of data rollup.</li> |
| <li><a href="/docs/latest/ingestion/schema-model">Druid schema model</a> to learn about the primary timestamp, dimensions, and metrics.</li> |
| </ul></div></article><nav class="pagination-nav docusaurus-mt-lg" aria-label="Docs pages"><a class="pagination-nav__link pagination-nav__link--prev" href="/docs/latest/tutorials/tutorial-kafka"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Load from Apache Kafka</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/latest/tutorials/tutorial-ingestion-spec"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Write an ingestion spec</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="#prerequisites" class="table-of-contents__link toc-highlight">Prerequisites</a></li><li><a href="#load-the-example-data" class="table-of-contents__link toc-highlight">Load the example data</a></li><li><a href="#query-the-example-data" class="table-of-contents__link toc-highlight">Query the example data</a></li><li><a href="#view-rollup-in-action" class="table-of-contents__link toc-highlight">View rollup in action</a></li><li><a href="#learn-more" class="table-of-contents__link toc-highlight">Learn more</a></li></ul></div></div></div></div></main></div></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="footer__logo themedComponent_mlkZ themedComponent--light_NVdE"><img src="/img/favicon.png" class="footer__logo themedComponent_mlkZ themedComponent--dark_xIcU"></div><div class="footer__copyright">Copyright © 2025 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> |
| </body> |
| </html> |
