https://opengemini.org/assets/multistage_downsampling.html-f7751a58.js

1import{_ as o}from"./8788753edd2626b6a942f3c3a533a353-4b7509f6.js";import{_ as i,r,o as l,c as p,a,b as n,d as e,e as t}from"./app-aa9c3b03.js";const c={},d=t('<h2 id="background" tabindex="-1"><a class="header-anchor" href="#background" aria-hidden="true">#</a> Background</h2><p>With the rapid development of 5G and IOT, the amount of data is growing exponentially. In order to more fully reflect the state details of the system or application, the granularity of indicator collection is becoming more and more detailed, and the amount of indicator data is becoming larger and larger. Services have various requirements on monitoring data, such as querying the latest abnormal alarms of the data and viewing the trend analysis of the one-year indicator data. The larger the amount of data, the longer the query will take, and if it is processed on the browser side, it will also consume a lot of memory. This not only causes great pressure on the database system, but also brings poor user experience.</p><p>For example, in a user service scenario:</p><ol><li>It is necessary to provide point-search capability for data in the last seven days and aggregate analysis capability with a time window of 5 minutes</li><li>Aggregate analysis results with coarse grain size and time window of 15min were provided in the last month</li><li>Provide coarse-grained aggregate analysis results with a time window of 1h in the last year</li><li>The data can expire after 365 days</li></ol><p>We can see that after seven days, there is no need for the point-and-click ability to provide detailed data.</p><p>In order to provide higher Query performance and lower storage cost, in traditional database applications, data downsampling is mostly achieved through CQ (Continue Query), materialized view, or other operations. The following is a user&#39;s previous technical solution:</p><ol><li>The customer builds three Druid clusters to store the raw data from 0 to 7 days, the aggregated data from 7 to 28 days after sampling in a 15 minute window, and the aggregated data from 28 days after sampling in a 1 hour window</li><li>The original data is inserted into the first cluster, and then the data aggregation is written to the second cluster and the third cluster respectively at the corresponding time</li><li>During the query, three SQL statements are sent to the corresponding cluster for aggregate calculation and data summary on the client</li></ol><p><img src="'+o+`" alt="img"></p><p>We can find the following problems:</p><ol><li>The cluster cost is too high, and three clusters are used to implement the service. In addition, each new tier 1 query service requires a new query cluster. As a result, the basic resource overhead cost is too high, and the operation and maintenance difficulty increases</li><li>Service development requires understanding the cluster architecture and the underlying cluster status before adding and maintaining the current query service. The cost of updating and expanding subsequent services is too high</li></ol><p>To solve the above problems, openGemini multistage downsampling function came into being.</p><h2 id="multistage-downsampling" tabindex="-1"><a class="header-anchor" href="#multistage-downsampling" aria-hidden="true">#</a> Multistage downsampling</h2><h3 id="function-introduction" tabindex="-1"><a class="header-anchor" href="#function-introduction" aria-hidden="true">#</a> Function introduction</h3><p>The openGemini multistage downsampling function is a background task. Compared with traditional downsampling, multistage means that data in different time Windows can be downsampled simultaneously in multiple time ranges, such as 0-T1, T1-T2, and T2-T3, and historical data details can be replaced in place to save more storage space.</p><h3 id="create-a-downsampling-task" tabindex="-1"><a class="header-anchor" href="#create-a-downsampling-task" aria-hidden="true">#</a> Create a downsampling task</h3><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token class-name">Create</span> <span class="token class-name">DownSample</span> <span class="token punctuation">[</span>on <span class="token operator">|</span> on <span class="token punctuation">.</span>|  <span class="token punctuation">]</span><span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token function">dataType</span><span class="token punctuation">(</span>aggregators<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token class-name">With</span> <span class="token class-name">Duration</span>  <span class="token class-name">SampleInterval</span><span class="token punctuation">(</span>time <span class="token class-name">Durations</span><span class="token punctuation">)</span> <span class="token class-name">TimeInterval</span><span class="token punctuation">(</span>time <span class="token class-name">Durations</span><span class="token punctuation">)</span>
2</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Parameter description:</p><table><thead><tr><th>

2dataType</th><th>Aggregators</th><th>Duration</th><th>SampleInterval</th><th>TimeInterval</th></tr></thead><tbody><tr><td>Specific data types, such as int, float</td><td>Polymerization method,Currently, min, max, sum, count, and mean are supported</td><td>Data retention time</td><td>The sampling time of the next step is executed</td><td>sampling Interval</td></tr></tbody></table>`,18),u=a("em",null,"Introductions of RP and shards can refer to the previous article《openGemini：使集群保持良好写线性度的三种最佳实践方法》",-1),m={href:"https://docs.opengemini.org/zh/guide/features/downsample.html",target:"_blank",rel:"noopener noreferrer"},h=t(`<p>Using the scenario in the background introduction to create a downsampling example:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">Create</span> DownSample <span class="token keyword">on</span> db0
3 <span class="token punctuation">(</span><span class="token keyword">float</span><span class="token punctuation">(</span>sum<span class="token punctuation">,</span><span class="token keyword">last</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">integer</span><span class="token punctuation">(</span>max<span class="token punctuation">,</span>min<span class="token punctuation">)</span><span class="token punctuation">)</span>
4 <span class="token keyword">With</span> Duration <span class="token number">365</span>d
5 sampleinterval<span class="token punctuation">(</span><span class="token number">7</span>d<span class="token punctuation">,</span><span class="token number">28</span>d<span class="token punctuation">)</span>
6 timeinterval<span class="token punctuation">(</span><span class="token number">15</span>m<span class="token punctuation">,</span><span class="token number">1</span>h<span class="token punctuation">)</span>
7</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><p>This is a typical three-level downsampling task, which is set by the sampleinterval(7d,28d), indicating that the downsampling period is divided into 0-7 days, 7-28 days,28 days and later (the maximum is 365 days, because the data after 356 days is expired. Data expiration is set by &#39;With Duration 365d&#39;). timeinterval(15m,1h) corresponds to sampleinterval(7d,28d), indicating that original data details are reserved for 0-7 days and normal service query is required. Data in 7-28 days is downsampled according to a 15min time window. Data after 28 days is downsampled according to the 1h time window. Fields and aggregate functions that satisfy downsampling are specified by (float(sum,last),integer(max,min)). float(sum,last) indicates that the sum() and last() aggregates will be executed for all float data, and integer (max,min) indicates that the max() and min() aggregates will be executed for all integer data. Other types of data that are not represented are discarded.</p><p>Based on the existing example, if you want to add first-level downsampling to the data of 28-70 days, the downsampling time window is 1h, and the data is aggregated according to 1d time window after 70 days, you only need to modify the command</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">Create</span> DownSample <span class="token keyword">on</span> db0
8 <span class="token punctuation">(</span><span class="token keyword">float</span><span class="token punctuation">(</span>sum<span class="token punctuation">,</span><span class="token keyword">last</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">integer</span><span class="token punctuation">(</span>max<span class="token punctuation">,</span>min<span class="token punctuation">)</span><span class="token punctuation">)</span>
9 <span class="token keyword">With</span> Duration <span class="token number">365</span>d
10 sampleinterval<span class="token punctuation">(</span><span class="token number">7</span>d<span class="token punctuation">,</span><span class="token number">28</span>d<span class="token punctuation">,</span><span class="token number">70</span>d<span class="token punctuation">)</span>
11 timeinterval<span class="token punctuation">(</span><span class="token number">15</span>m<span class="token punctuation">,</span><span class="token number">1</span>h<span class="token punctuation">,</span><span class="token number">1</span>d<span class="token punctuation">)</span>
12</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="query-the-downsampling-task" tabindex="-1"><a class="header-anchor" href="#query-the-downsampling-task" aria-hidden="true">

12#</a> Query the downsampling task</h3><p>If we want to know the downsampling task we are currently creating, we can use the following command:</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>SHOW DOWNSAMPLES ON
13样例：
14&gt; show downsamples
15rpName  field_operator                   duration  sampleInterval    timeInterval
16------  --------------                   --------  --------------    ------------
17autogen float{sum,last},integer{max,min} 168h0m0s  24h0m0s,48h0m0s   1m0s,3m0s
18rp1     float{sum,last},integer{max,min} 720h0m0s  24h0m0s,168h0m0s  1m0s,3m0s
19rp2     bool{last},int{max,min,first}    2400h0m0s 168h0m0s,360h0m0s 30m0s,1h0m0s
20</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="delete-the-downsampling-task" tabindex="-1"><a class="header-anchor" href="#delete-the-downsampling-task" aria-hidden="true">#</a> Delete the downsampling task</h3><p>If we want to delete the downsampling task that is currently created, we can use the following command:</p><div class="language-text line-numbers-mode" data-ext="text"><pre class="language-text"><code>Drop DownSamples on [|]
21</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>If we want to know the downsampling status of the current shard, we can use the following command:</p><div class="language-java line-numbers-mode" data-ext="java"><pre class="language-java"><code><span class="token constant">SHOW</span> <span class="token constant">DOWNSAMPLES</span> <span class="token constant">ON</span>
22样例：
23<span class="token operator">&gt;</span> show downsamples
24rpName  field_operator                   duration  sampleInterval    timeInterval
25<span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span>  <span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span>                   <span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span>  <span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span>    <span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span><span class="token operator">--</span>
26autogen <span class="token keyword">float</span><span class="token punctuation">{</span>sum<span class="token punctuation">,</span>last<span class="token punctuation">}</span><span class="token punctuation">,</span>integer<span class="token punctuation">{</span>max<span class="token punctuation">,</span>min<span class="token punctuation">}</span> <span class="token number">168</span>h0m0s  <span class="token number">24</span>h0m0s<span class="token punctuation">,</span><span class="token number">48</span>h0m0s   <span class="token number">1</span>m0s<span class="token punctuation">,</span><span class="token number">3</span>m0s
27rp1     <span class="token keyword">float</span><span class="token punctuation">{</span>sum<span class="token punctuation">,</span>last<span class="token punctuation">}</span><span class="token punctuation">,</span>integer<span class="token punctuation">{</span>max<span class="token punctuation">,</span>min<span class="token punctuation">}</span> <span class="token number">720</span>h0m0s  <span class="token number">24</span>h0m0s<span class="token punctuation">,</span><span class="token number">168</span>h0m0s  <span class="token number">1</span>m0s<span class="token punctuation">,</span><span class="token number">3</span>m0s
28rp2     bool<span class="token punctuation">{</span>last<span class="token punctuation">}</span><span class="token punctuation">,</span><span class="token keyword">int</span><span class="token punctuation">{</span>max<span class="token punctuation">,</span>min<span class="token punctuation">,</span>first<span class="token punctuation">}</span>    <span class="token number">2400</span>h0m0s <span class="token number">168</span>h0m0s<span class="token punctuation">,</span><span class="token number">360</span>h0m0s <span class="token number">30</span>m0s<span class="token punctuation">,</span><span class="token number">1</span>h0m0s
29</code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="description-of-other-constraints" tabindex="-1"><a class="header-anchor" href="#description-of-other-constraints" aria-hidden="true">

29#</a> Description of other constraints</h3>`,14),g=a("li",null,[n("When the downsampling retention time is changed "),a("strong",null,"With Duration 365d"),n(", the rp retention policy of the current db is modified simultaneously. The two policies are related. Do not change this parameter if it is not necessary. Ensure that it is consistent with the current db retention policy.")],-1),k=a("li",null,"After a downsampling task is created, the user does not perceive it and ADAPTS to the target query statement. For example, in the above example, the service Group query Group by time (1h), the kernel will form four groups of 15min aggregated results into a 1h result return, it should be noted that the grouping time and downsampling time window are multiples. If the downsampling time window is 15 minutes, the service query Group by time (16 minutes) is not allowed.",-1),v={href:"https://docs.opengemini.org/zh/guide/schema/retention_policy.html#shard-duration",target:"_blank",rel:"noopener noreferrer"},b=a("li",null,"The next level of TimeInterval must be a positive integer multiple of the previous level, for example, timeinterval(15m,1h) in the example. It cannot be timeinterval(15m,29min), because 29 is not a multiple of 15.",-1),w=a("li",null,"For select * queries, if the query time range involves downsampled shard data, only the original data is queried",-1),f=a("li",null,"If the shard has been downsampled, data cannot be written",-1),y=a("h2",{id:"summary",tabindex:"-1"},[a("a",{class:"header-anchor",href:"#summary","aria-hidden":"true"},"#"),n(" Summary")],-1),x=a("p",null,"This article mainly introduces the openGemini multilevel downsampling function, including application scenarios, task creation, view and deletion, and usage constraints. Multilevel downsampling can greatly reduce data storage costs and system costs, but it does not retain the original data details, you must be clear when using.",-1);function _(T,D){const s=r("ExternalLinkIcon");return l(),p("div",null,[d,a("p",null,[u,n(",See detailed usage"),a("a",m,[n("Community document"),e(s)])]),h,a("ol",null,[g,k,a("li",null,[n("The SampleInterval needs to be a positive multiple of the shard Duration at the time of the current table creation，For example, the shard Duration is 7d, and the minimum value of the SampleInterval is 7d or a multiple of 7. _ Related Reading _"),a("a",v,[n("shard Duration"),e(s)])]),b,w,f]),y,x])}const S=i(c,[["render",_],["__file","multistage_downsampling.html.vue"]]);export{S as default};