| <!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-update-data" data-has-hydrated="false"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="generator" content="Docusaurus v3.7.0"> |
| <title data-rh="true">Update data | 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/34.0.0/tutorials/tutorial-update-data"><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="Update data | Apache® Druid"><meta data-rh="true" name="description" content="Learn how to update data in Apache Druid."><meta data-rh="true" property="og:description" content="Learn how to update data in Apache Druid."><link data-rh="true" rel="icon" href="/img/favicon.png"><link data-rh="true" rel="canonical" href="https://druid.apache.org/docs/34.0.0/tutorials/tutorial-update-data"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/34.0.0/tutorials/tutorial-update-data" hreflang="en"><link data-rh="true" rel="alternate" href="https://druid.apache.org/docs/34.0.0/tutorials/tutorial-update-data" 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.9b5d81eb.js" defer="defer"></script> |
| <script src="/assets/js/main.0aff4030.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/34.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="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/34.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" href="/docs/34.0.0/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/34.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/34.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/34.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" role="button" aria-expanded="false" tabindex="0" href="/docs/34.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"><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/34.0.0/tutorials/tutorial-retention">Data management 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/34.0.0/tutorials/tutorial-retention">Configure data retention</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/34.0.0/tutorials/tutorial-append-data">Append data</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/34.0.0/tutorials/tutorial-update-data">Update 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/34.0.0/tutorials/tutorial-compaction">Compact segments</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/34.0.0/tutorials/tutorial-delete-data">Deleting 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/34.0.0/tutorials/tutorial-latest-by">Query for latest data</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/34.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" tabindex="0" href="/docs/34.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/34.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" role="button" aria-expanded="false" tabindex="0" href="/docs/34.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" role="button" aria-expanded="false" href="/docs/34.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" href="/docs/34.0.0/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/34.0.0/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/34.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" href="/docs/34.0.0/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/34.0.0/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/34.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" href="/docs/34.0.0/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/34.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/34.0.0/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/34.0.0/tutorials/"><span itemprop="name">Getting started</span></a><meta itemprop="position" content="1"></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Data management 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">Update data</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>Update data</h1></header><p>Apache Druid stores data and indexes in <a href="/docs/34.0.0/design/segments">segment files</a> partitioned by time. |
| After Druid creates a segment, its contents can't be modified. |
| You can either replace data for the whole segment, or, in some cases, overshadow a portion of the segment data.</p> |
| <p>In Druid, use time ranges to specify the data you want to update, as opposed to a primary key or dimensions often used in transactional databases. Data outside the specified replacement time range remains unaffected. |
| You can use this Druid functionality to perform data updates, inserts, and deletes, similar to UPSERT functionality for transactional databases.</p> |
| <p>This tutorial shows you how to use the Druid SQL <a href="/docs/34.0.0/multi-stage-query/reference#replace">REPLACE</a> function with the OVERWRITE clause to update existing data.</p> |
| <p>The tutorial walks you through the following use cases:</p> |
| <ul> |
| <li><a href="#overwrite-all-data">Overwrite all data</a></li> |
| <li><a href="#overwrite-records-for-a-specific-time-range">Overwrite records for a specific time range</a></li> |
| <li><a href="#update-a-row-using-partial-segment-overshadowing">Update a row using partial segment overshadowing</a></li> |
| </ul> |
| <p>All examples use the <a href="/docs/34.0.0/multi-stage-query/">multi-stage query (MSQ)</a> task engine to executes 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 you follow the steps in this tutorial, download Druid as described in <a href="/docs/34.0.0/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/34.0.0/tutorials/tutorial-query">Query data</a> tutorial first.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="load-sample-data">Load sample data<a href="#load-sample-data" class="hash-link" aria-label="Direct link to Load sample data" title="Direct link to Load sample data"></a></h2> |
| <p>Load a sample dataset using <a href="/docs/34.0.0/multi-stage-query/reference#replace">REPLACE</a> and <a href="/docs/34.0.0/multi-stage-query/reference#extern-function">EXTERN</a> functions. |
| In Druid SQL, the REPLACE function can create a new <a href="/docs/34.0.0/design/storage">datasource</a> or update an existing datasource.</p> |
| <p>In the Druid <a href="/docs/34.0.0/operations/web-console">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">REPLACE</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)">"update_tutorial"</span><span class="token plain"> OVERWRITE </span><span class="token keyword" style="font-style:italic">ALL</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)">"ext"</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"></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":"{\"timestamp\":\"2024-01-01T07:01:35Z\",\"animal\":\"octopus\", \"number\":115}\n{\"timestamp\":\"2024-01-01T05:01:35Z\",\"animal\":\"mongoose\", \"number\":737}\n{\"timestamp\":\"2024-01-01T06:01:35Z\",\"animal\":\"snake\", \"number\":1234}\n{\"timestamp\":\"2024-01-01T01:01:35Z\",\"animal\":\"lion\", \"number\":300}\n{\"timestamp\":\"2024-01-02T07:01:35Z\",\"animal\":\"seahorse\", \"number\":115}\n{\"timestamp\":\"2024-01-02T05:01:35Z\",\"animal\":\"skunk\", \"number\":737}\n{\"timestamp\":\"2024-01-02T06:01:35Z\",\"animal\":\"iguana\", \"number\":1234}\n{\"timestamp\":\"2024-01-02T01:01:35Z\",\"animal\":\"opossum\", \"number\":300}"}'</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 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)">"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)">"animal"</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)">"number"</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"> 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">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)">"animal"</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)">"number"</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)">"ext"</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><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain" style="display:inline-block"></span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div> |
| <p>In the resulting <code>update_tutorial</code> datasource, individual rows are uniquely identified by <code>__time</code>, <code>animal</code>, and <code>number</code>. |
| To view the results, open a new tab 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">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)">"update_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> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View the results</summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>__time</code></th><th><code>animal</code></th><th><code>number</code></th></tr></thead><tbody><tr><td><code>2024-01-01T01:01:35.000Z</code></td><td><code>lion</code></td><td>300</td></tr><tr><td><code>2024-01-01T05:01:35.000Z</code></td><td><code>mongoose</code></td><td>737</td></tr><tr><td><code>2024-01-01T06:01:35.000Z</code></td><td><code>snake</code></td><td>1234</td></tr><tr><td><code>2024-01-01T07:01:35.000Z</code></td><td><code>octopus</code></td><td>115</td></tr><tr><td><code>2024-01-02T01:01:35.000Z</code></td><td><code>opossum</code></td><td>300</td></tr><tr><td><code>2024-01-02T05:01:35.000Z</code></td><td><code>skunk</code></td><td>737</td></tr><tr><td><code>2024-01-02T06:01:35.000Z</code></td><td><code>iguana</code></td><td>1234</td></tr><tr><td><code>2024-01-02T07:01:35.000Z</code></td><td><code>seahorse</code></td><td>115</td></tr></tbody></table></div></div></details> |
| <p>The results contain records for eight animals over two days.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="overwrite-all-data">Overwrite all data<a href="#overwrite-all-data" class="hash-link" aria-label="Direct link to Overwrite all data" title="Direct link to Overwrite all data"></a></h2> |
| <p>You can use the REPLACE function with OVERWRITE ALL to replace the entire datasource with new data while dropping the old data.</p> |
| <p>In the web console, open a new tab and run the following query to overwrite timestamp data for the entire <code>update_tutorial</code> datasource:</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">REPLACE</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)">"update_tutorial"</span><span class="token plain"> OVERWRITE </span><span class="token keyword" style="font-style:italic">ALL</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)">"ext"</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><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"></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":"{\"timestamp\":\"2024-01-02T07:01:35Z\",\"animal\":\"octopus\", \"number\":115}\n{\"timestamp\":\"2024-01-02T05:01:35Z\",\"animal\":\"mongoose\", \"number\":737}\n{\"timestamp\":\"2024-01-02T06:01:35Z\",\"animal\":\"snake\", \"number\":1234}\n{\"timestamp\":\"2024-01-02T01:01:35Z\",\"animal\":\"lion\", \"number\":300}\n{\"timestamp\":\"2024-01-03T07:01:35Z\",\"animal\":\"seahorse\", \"number\":115}\n{\"timestamp\":\"2024-01-03T05:01:35Z\",\"animal\":\"skunk\", \"number\":737}\n{\"timestamp\":\"2024-01-03T06:01:35Z\",\"animal\":\"iguana\", \"number\":1234}\n{\"timestamp\":\"2024-01-03T01:01:35Z\",\"animal\":\"opossum\", \"number\":300}"}'</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 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)">"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)">"animal"</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)">"number"</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 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_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">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)">"animal"</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)">"number"</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)">"ext"</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> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View the results</summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>__time</code></th><th><code>animal</code></th><th><code>number</code></th></tr></thead><tbody><tr><td><code>2024-01-02T01:01:35.000Z</code></td><td><code>lion</code></td><td>300</td></tr><tr><td><code>2024-01-02T05:01:35.000Z</code></td><td><code>mongoose</code></td><td>737</td></tr><tr><td><code>2024-01-02T06:01:35.000Z</code></td><td><code>snake</code></td><td>1234</td></tr><tr><td><code>2024-01-02T07:01:35.000Z</code></td><td><code>octopus</code></td><td>115</td></tr><tr><td><code>2024-01-03T01:01:35.000Z</code></td><td><code>opossum</code></td><td>300</td></tr><tr><td><code>2024-01-03T05:01:35.000Z</code></td><td><code>skunk</code></td><td>737</td></tr><tr><td><code>2024-01-03T06:01:35.000Z</code></td><td><code>iguana</code></td><td>1234</td></tr><tr><td><code>2024-01-03T07:01:35.000Z</code></td><td><code>seahorse</code></td><td>115</td></tr></tbody></table></div></div></details> |
| <p>Note that the values in the <code>__time</code> column have changed to one day later.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="overwrite-records-for-a-specific-time-range">Overwrite records for a specific time range<a href="#overwrite-records-for-a-specific-time-range" class="hash-link" aria-label="Direct link to Overwrite records for a specific time range" title="Direct link to Overwrite records for a specific time range"></a></h2> |
| <p>You can use the REPLACE function to overwrite a specific time range of a datasource. When you overwrite a specific time range, that time range must align with the granularity specified in the PARTITIONED BY clause.</p> |
| <p>In the web console, open a new tab and run the following query to insert a new row and update specific rows. Note that the OVERWRITE WHERE clause tells the query to only update records for the date 2024-01-03.</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">REPLACE</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)">"update_tutorial"</span><span class="token plain"> </span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> OVERWRITE </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)">"__time"</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">TIMESTAMP</span><span class="token string" style="color:rgb(195, 232, 141)">'2024-01-03 00:00:00'</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)">"__time"</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">TIMESTAMP</span><span class="token string" style="color:rgb(195, 232, 141)">'2024-01-04 00:00:00'</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)">"ext"</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><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"></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":"{\"timestamp\":\"2024-01-03T01:01:35Z\",\"animal\":\"tiger\", \"number\":300}\n{\"timestamp\":\"2024-01-03T07:01:35Z\",\"animal\":\"seahorse\", \"number\":500}\n{\"timestamp\":\"2024-01-03T05:01:35Z\",\"animal\":\"polecat\", \"number\":626}\n{\"timestamp\":\"2024-01-03T06:01:35Z\",\"animal\":\"iguana\", \"number\":300}\n{\"timestamp\":\"2024-01-03T01:01:35Z\",\"animal\":\"flamingo\", \"number\":999}"}'</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 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)">"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)">"animal"</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)">"number"</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 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_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">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)">"animal"</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)">"number"</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)">"ext"</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> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View the results</summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>__time</code></th><th><code>animal</code></th><th><code>number</code></th></tr></thead><tbody><tr><td><code>2024-01-02T01:01:35.000Z</code></td><td><code>lion</code></td><td>300</td></tr><tr><td><code>2024-01-02T05:01:35.000Z</code></td><td><code>mongoose</code></td><td>737</td></tr><tr><td><code>2024-01-02T06:01:35.000Z</code></td><td><code>snake</code></td><td>1234</td></tr><tr><td><code>2024-01-02T07:01:35.000Z</code></td><td><code>octopus</code></td><td>115</td></tr><tr><td><code>2024-01-03T01:01:35.000Z</code></td><td><code>flamingo</code></td><td>999</td></tr><tr><td><code>2024-01-03T01:01:35.000Z</code></td><td><code>tiger</code></td><td>300</td></tr><tr><td><code>2024-01-03T05:01:35.000Z</code></td><td><code>polecat</code></td><td>626</td></tr><tr><td><code>2024-01-03T06:01:35.000Z</code></td><td><code>iguana</code></td><td>300</td></tr><tr><td><code>2024-01-03T07:01:35.000Z</code></td><td><code>seahorse</code></td><td>500</td></tr></tbody></table></div></div></details> |
| <p>Note the changes in the resulting datasource:</p> |
| <ul> |
| <li>There is now a new row called <code>flamingo</code>.</li> |
| <li>The <code>opossum</code> row has the value <code>tiger</code>.</li> |
| <li>The <code>skunk</code> row has the value <code>polecat</code>.</li> |
| <li>The <code>iguana</code> and <code>seahorse</code> rows have different numbers.</li> |
| </ul> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="update-a-row-using-partial-segment-overshadowing">Update a row using partial segment overshadowing<a href="#update-a-row-using-partial-segment-overshadowing" class="hash-link" aria-label="Direct link to Update a row using partial segment overshadowing" title="Direct link to Update a row using partial segment overshadowing"></a></h2> |
| <p>In Druid, you can overlay older data with newer data for the entire segment or portions of the segment within a particular partition. |
| This capability is called <a href="/docs/34.0.0/ingestion/tasks#overshadowing-between-segments">overshadowing</a>.</p> |
| <p>You can use partial overshadowing to update a single row by adding a smaller time granularity segment on top of the existing data. |
| It's a less common variation on a more common approach where you replace the entire time chunk.</p> |
| <p>The following example demonstrates how update data using partial overshadowing with mixed segment granularity.<br> |
| <!-- -->Note the following important points about the example:</p> |
| <ul> |
| <li>The query updates a single record for a specific <code>number</code> row.</li> |
| <li>The original datasource uses DAY segment granularity.</li> |
| <li>The new data segment is at HOUR granularity and represents a time range that's smaller than the existing data.</li> |
| <li>The OVERWRITE WHERE and WHERE TIME_IN_INTERVAL clauses specify the destination where the update occurs and the source of the update, respectively.</li> |
| <li>The query replaces everything within the specified interval. To update only a subset of data in that interval, you have to carry forward all records, changing only what you want to change. You can accomplish that by using the <a href="/docs/34.0.0/querying/sql-functions#case">CASE</a> function in the SELECT list.</li> |
| </ul> |
| <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">REPLACE</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)">"update_tutorial"</span><span class="token plain"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> OVERWRITE</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> </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)">"__time"</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">TIMESTAMP</span><span class="token string" style="color:rgb(195, 232, 141)">'2024-01-03 05:00:00'</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)">"__time"</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">TIMESTAMP</span><span class="token string" style="color:rgb(195, 232, 141)">'2024-01-03 06:00:00'</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"> </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)">"animal"</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"> CAST</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token number" style="color:rgb(247, 140, 108)">486</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">AS</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 keyword" style="font-style:italic">AS</span><span class="token plain"> </span><span class="token string" style="color:rgb(195, 232, 141)">"number"</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)">"update_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">WHERE</span><span class="token plain"> TIME_IN_INTERVAL</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</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><span class="token string" style="color:rgb(195, 232, 141)">'2024-01-03T05:01:35Z/PT1S'</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">PARTITIONED </span><span class="token keyword" style="font-style:italic">BY</span><span class="token plain"> FLOOR</span><span class="token punctuation" style="color:rgb(199, 146, 234)">(</span><span class="token plain">__time </span><span class="token keyword" style="font-style:italic">TO</span><span class="token plain"> </span><span class="token keyword" style="font-style:italic">HOUR</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> |
| <details class="details_lb9f alert alert--info details_b_Ee" data-collapsed="true"><summary> View the results</summary><div><div class="collapsibleContent_i85q"><table><thead><tr><th><code>__time</code></th><th><code>animal</code></th><th><code>number</code></th></tr></thead><tbody><tr><td><code>2024-01-02T01:01:35.000Z</code></td><td><code>lion</code></td><td>300</td></tr><tr><td><code>2024-01-02T05:01:35.000Z</code></td><td><code>mongoose</code></td><td>737</td></tr><tr><td><code>2024-01-02T06:01:35.000Z</code></td><td><code>snake</code></td><td>1234</td></tr><tr><td><code>2024-01-02T07:01:35.000Z</code></td><td><code>octopus</code></td><td>115</td></tr><tr><td><code>2024-01-03T01:01:35.000Z</code></td><td><code>flamingo</code></td><td>999</td></tr><tr><td><code>2024-01-03T01:01:35.000Z</code></td><td><code>tiger</code></td><td>300</td></tr><tr><td><code>2024-01-03T05:01:35.000Z</code></td><td><code>polecat</code></td><td>486</td></tr><tr><td><code>2024-01-03T06:01:35.000Z</code></td><td><code>iguana</code></td><td>300</td></tr><tr><td><code>2024-01-03T07:01:35.000Z</code></td><td><code>seahorse</code></td><td>500</td></tr></tbody></table></div></div></details> |
| <p>Note that the <code>number</code> for <code>polecat</code> has changed from 626 to 486.</p> |
| <p>When you perform partial segment overshadowing multiple times, you can create segment fragmentation that could affect query performance. Use <a href="/docs/34.0.0/data-management/compaction">compaction</a> to correct any fragmentation.</p> |
| <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/34.0.0/data-management/update">Data updates</a> for an overview of updating data in Druid.</li> |
| <li><a href="/docs/34.0.0/tutorials/tutorial-msq-extern">Load files with SQL-based ingestion</a> for generating a query that references externally hosted data.</li> |
| <li><a href="/docs/34.0.0/multi-stage-query/concepts#overwrite-data-with-replace">Overwrite data with REPLACE</a> for details on how the MSQ task engine executes SQL REPLACE queries.</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/34.0.0/tutorials/tutorial-append-data"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Append data</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/34.0.0/tutorials/tutorial-compaction"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Compact segments</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-sample-data" class="table-of-contents__link toc-highlight">Load sample data</a></li><li><a href="#overwrite-all-data" class="table-of-contents__link toc-highlight">Overwrite all data</a></li><li><a href="#overwrite-records-for-a-specific-time-range" class="table-of-contents__link toc-highlight">Overwrite records for a specific time range</a></li><li><a href="#update-a-row-using-partial-segment-overshadowing" class="table-of-contents__link toc-highlight">Update a row using partial segment overshadowing</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> |
