| import{_ as a,C as t,O as o,P as p,ah as i,Q as s,U as e,ai as c,ae as r,aW as l}from"./framework-e4340ccd.js";const u={},d=l(`<h1 id="query-write-back-select-into" tabindex="-1"><a class="header-anchor" href="#query-write-back-select-into" aria-hidden="true">#</a> Query Write-back (SELECT INTO)</h1><p>The <code>SELECT INTO</code> statement copies data from query result set into target time series.</p><p>The application scenarios are as follows:</p><ul><li><strong>Implement IoTDB internal ETL</strong>: ETL the original data and write a new time series.</li><li><strong>Query result storage</strong>: Persistently store the query results, which acts like a materialized view.</li><li><strong>Non-aligned time series to aligned time series</strong>: Rewrite non-aligned time series into another aligned time series.</li></ul><h2 id="sql-syntax" tabindex="-1"><a class="header-anchor" href="#sql-syntax" aria-hidden="true">#</a> SQL Syntax</h2><h3 id="syntax-definition" tabindex="-1"><a class="header-anchor" href="#syntax-definition" aria-hidden="true">#</a> Syntax Definition</h3><p><strong>The following is the syntax definition of the <code>select</code> statement:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code>selectIntoStatement |
| : <span class="token keyword">SELECT</span> |
| resultColumn <span class="token punctuation">[</span><span class="token punctuation">,</span> resultColumn<span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> |
| <span class="token keyword">INTO</span> intoItem <span class="token punctuation">[</span><span class="token punctuation">,</span> intoItem<span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> |
| <span class="token keyword">FROM</span> prefixPath <span class="token punctuation">[</span><span class="token punctuation">,</span> prefixPath<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 keyword">WHERE</span> whereCondition<span class="token punctuation">]</span> |
| <span class="token punctuation">[</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span> groupByTimeClause<span class="token punctuation">,</span> groupByLevelClause<span class="token punctuation">]</span> |
| <span class="token punctuation">[</span>FILL {PREVIOUS <span class="token operator">|</span> LINEAR <span class="token operator">|</span> constant}<span class="token punctuation">]</span> |
| <span class="token punctuation">[</span><span class="token keyword">LIMIT</span> rowLimit <span class="token keyword">OFFSET</span> rowOffset<span class="token punctuation">]</span> |
| <span class="token punctuation">[</span>ALIGN <span class="token keyword">BY</span> DEVICE<span class="token punctuation">]</span> |
| <span class="token punctuation">;</span> |
| |
| intoItem |
| : <span class="token punctuation">[</span>ALIGNED<span class="token punctuation">]</span> intoDevicePath <span class="token string">'('</span> intoMeasurementName <span class="token punctuation">[</span><span class="token string">','</span> intoMeasurementName<span class="token punctuation">]</span><span class="token operator">*</span> <span class="token string">')'</span> |
| <span class="token punctuation">;</span> |
| </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 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="into-clause" tabindex="-1"><a class="header-anchor" href="#into-clause" aria-hidden="true">#</a> <code>INTO</code> Clause</h3><p>The <code>INTO</code> clause consists of several <code>intoItem</code>.</p><p>Each <code>intoItem</code> consists of a target device and a list of target measurements (similar to the <code>INTO</code> clause in an <code>INSERT</code> statement).</p><p>Each target measurement and device form a target time series, and an <code>intoItem</code> contains a series of time series. For example: <code>root.sg_copy.d1(s1, s2)</code> specifies two target time series <code>root.sg_copy.d1.s1</code> and <code>root.sg_copy.d1.s2</code>.</p><p>The target time series specified by the <code>INTO</code> clause must correspond one-to-one with the columns of the query result set. The specific rules are as follows:</p><ul><li><strong>Align by time</strong> (default): The number of target time series contained in all <code>intoItem</code> must be consistent with the number of columns in the query result set (except the time column) and correspond one-to-one in the order from left to right in the header.</li><li><strong>Align by device</strong> (using <code>ALIGN BY DEVICE</code>): the number of target devices specified in all <code>intoItem</code> is the same as the number of devices queried (i.e., the number of devices matched by the path pattern in the <code>FROM</code> clause), and One-to-one correspondence according to the output order of the result set device. <br>The number of measurements specified for each target device should be consistent with the number of columns in the query result set (except for the time and device columns). It should be in one-to-one correspondence from left to right in the header.</li></ul><p>For examples:</p><ul><li><strong>Example 1</strong> (aligned by time)</li></ul><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code>IoTDB<span class="token operator">></span> <span class="token keyword">select</span> s1, s2 into root.sg_copy.d1<span class="token punctuation">(</span>t1<span class="token punctuation">)</span>, root.sg_copy.d2<span class="token punctuation">(</span>t1, t2<span class="token punctuation">)</span>, root.sg_copy.d1<span class="token punctuation">(</span>t2<span class="token punctuation">)</span> from root.sg.d1, root.sg.d2<span class="token punctuation">;</span> |
| +--------------+-------------------+--------+ |
| <span class="token operator">|</span> <span class="token builtin class-name">source</span> <span class="token function">column</span><span class="token operator">|</span> target timeseries<span class="token operator">|</span> written<span class="token operator">|</span> |
| +--------------+-------------------+--------+ |
| <span class="token operator">|</span> root.sg.d1.s1<span class="token operator">|</span> root.sg_copy.d1.t1<span class="token operator">|</span> <span class="token number">8000</span><span class="token operator">|</span> |
| +--------------+-------------------+--------+ |
| <span class="token operator">|</span> root.sg.d2.s1<span class="token operator">|</span> root.sg_copy.d2.t1<span class="token operator">|</span> <span class="token number">10000</span><span class="token operator">|</span> |
| +--------------+-------------------+--------+ |
| <span class="token operator">|</span> root.sg.d1.s2<span class="token operator">|</span> root.sg_copy.d2.t2<span class="token operator">|</span> <span class="token number">12000</span><span class="token operator">|</span> |
| +--------------+-------------------+--------+ |
| <span class="token operator">|</span> root.sg.d2.s2<span class="token operator">|</span> root.sg_copy.d1.t2<span class="token operator">|</span> <span class="token number">10000</span><span class="token operator">|</span> |
| +--------------+-------------------+--------+ |
| Total line number <span class="token operator">=</span> <span class="token number">4</span> |
| It costs <span class="token number">0</span>.725s |
| </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 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><p>This statement writes the query results of the four time series under the <code>root.sg</code> database to the four specified time series under the <code>root.sg_copy</code> database. Note that <code>root.sg_copy.d2(t1, t2)</code> can also be written as <code>root.sg_copy.d2(t1), root.sg_copy.d2(t2)</code>.</p><p>We can see that the writing of the <code>INTO</code> clause is very flexible as long as the combined target time series is not repeated and corresponds to the query result column one-to-one.</p><blockquote><p>In the result set displayed by <code>CLI</code>, the meaning of each column is as follows:</p><ul><li>The <code>source column</code> column represents the column name of the query result.</li><li><code>target timeseries</code> represents the target time series for the corresponding column to write.</li><li><code>written</code> indicates the amount of data expected to be written.</li></ul></blockquote><ul><li><strong>Example 2</strong> (aligned by time)</li></ul><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code>IoTDB<span class="token operator">></span> <span class="token keyword">select</span> count<span class="token punctuation">(</span>s1 + s2<span class="token punctuation">)</span>, last_value<span class="token punctuation">(</span>s2<span class="token punctuation">)</span> into root.agg.count<span class="token punctuation">(</span>s1_add_s2<span class="token punctuation">)</span>, root.agg.last_value<span class="token punctuation">(</span>s2<span class="token punctuation">)</span> from root.sg.d1 group by <span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token number">0</span>, <span class="token number">100</span><span class="token punctuation">)</span>, 10ms<span class="token punctuation">)</span><span class="token punctuation">;</span> |
| +--------------------------------------+-------------------------+--------+ |
| <span class="token operator">|</span> <span class="token builtin class-name">source</span> <span class="token function">column</span><span class="token operator">|</span> target timeseries<span class="token operator">|</span> written<span class="token operator">|</span> |
| +--------------------------------------+-------------------------+--------+ |
| <span class="token operator">|</span> count<span class="token punctuation">(</span>root.sg.d1.s1 + root.sg.d1.s2<span class="token punctuation">)</span><span class="token operator">|</span> root.agg.count.s1_add_s2<span class="token operator">|</span> <span class="token number">10</span><span class="token operator">|</span> |
| +--------------------------------------+-------------------------+--------+ |
| <span class="token operator">|</span> last_value<span class="token punctuation">(</span>root.sg.d1.s2<span class="token punctuation">)</span><span class="token operator">|</span> root.agg.last_value.s2<span class="token operator">|</span> <span class="token number">10</span><span class="token operator">|</span> |
| +--------------------------------------+-------------------------+--------+ |
| Total line number <span class="token operator">=</span> <span class="token number">2</span> |
| It costs <span class="token number">0</span>.375s |
| </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 class="line-number"></div><div class="line-number"></div></div></div><p>This statement stores the results of an aggregated query into the specified time series.</p><ul><li><strong>Example 3</strong> (aligned by device)</li></ul><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code>IoTDB<span class="token operator">></span> <span class="token keyword">select</span> s1, s2 into root.sg_copy.d1<span class="token punctuation">(</span>t1, t2<span class="token punctuation">)</span>, root.sg_copy.d2<span class="token punctuation">(</span>t1, t2<span class="token punctuation">)</span> from root.sg.d1, root.sg.d2 align by device<span class="token punctuation">;</span> |
| +--------------+--------------+-------------------+--------+ |
| <span class="token operator">|</span> <span class="token builtin class-name">source</span> device<span class="token operator">|</span> <span class="token builtin class-name">source</span> <span class="token function">column</span><span class="token operator">|</span> target timeseries<span class="token operator">|</span> written<span class="token operator">|</span> |
| +--------------+--------------+-------------------+--------+ |
| <span class="token operator">|</span> root.sg.d1<span class="token operator">|</span> s1<span class="token operator">|</span> root.sg_copy.d1.t1<span class="token operator">|</span> <span class="token number">8000</span><span class="token operator">|</span> |
| +--------------+--------------+-------------------+--------+ |
| <span class="token operator">|</span> root.sg.d1<span class="token operator">|</span> s2<span class="token operator">|</span> root.sg_copy.d1.t2<span class="token operator">|</span> <span class="token number">11000</span><span class="token operator">|</span> |
| +--------------+--------------+-------------------+--------+ |
| <span class="token operator">|</span> root.sg.d2<span class="token operator">|</span> s1<span class="token operator">|</span> root.sg_copy.d2.t1<span class="token operator">|</span> <span class="token number">12000</span><span class="token operator">|</span> |
| +--------------+--------------+-------------------+--------+ |
| <span class="token operator">|</span> root.sg.d2<span class="token operator">|</span> s2<span class="token operator">|</span> root.sg_copy.d2.t2<span class="token operator">|</span> <span class="token number">9000</span><span class="token operator">|</span> |
| +--------------+--------------+-------------------+--------+ |
| Total line number <span class="token operator">=</span> <span class="token number">4</span> |
| It costs <span class="token number">0</span>.625s |
| </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 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><p>This statement also writes the query results of the four time series under the <code>root.sg</code> database to the four specified time series under the <code>root.sg_copy</code> database. However, in ALIGN BY DEVICE, the number of <code>intoItem</code> must be the same as the number of queried devices, and each queried device corresponds to one <code>intoItem</code>.</p><blockquote><p>When aligning the query by device, the result set displayed by <code>CLI</code> has one more column, the <code>source device</code> column indicating the queried device.</p></blockquote><ul><li><strong>Example 4</strong> (aligned by device)</li></ul><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code>IoTDB<span class="token operator">></span> <span class="token keyword">select</span> s1 + s2 into root.expr.add<span class="token punctuation">(</span>d1s1_d1s2<span class="token punctuation">)</span>, root.expr.add<span class="token punctuation">(</span>d2s1_d2s2<span class="token punctuation">)</span> from root.sg.d1, root.sg.d2 align by device<span class="token punctuation">;</span> |
| +--------------+--------------+------------------------+--------+ |
| <span class="token operator">|</span> <span class="token builtin class-name">source</span> device<span class="token operator">|</span> <span class="token builtin class-name">source</span> <span class="token function">column</span><span class="token operator">|</span> target timeseries<span class="token operator">|</span> written<span class="token operator">|</span> |
| +--------------+--------------+------------------------+--------+ |
| <span class="token operator">|</span> root.sg.d1<span class="token operator">|</span> s1 + s2<span class="token operator">|</span> root.expr.add.d1s1_d1s2<span class="token operator">|</span> <span class="token number">10000</span><span class="token operator">|</span> |
| +--------------+--------------+------------------------+--------+ |
| <span class="token operator">|</span> root.sg.d2<span class="token operator">|</span> s1 + s2<span class="token operator">|</span> root.expr.add.d2s1_d2s2<span class="token operator">|</span> <span class="token number">10000</span><span class="token operator">|</span> |
| +--------------+--------------+------------------------+--------+ |
| Total line number <span class="token operator">=</span> <span class="token number">2</span> |
| It costs <span class="token number">0</span>.532s |
| </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 class="line-number"></div><div class="line-number"></div></div></div><p>This statement stores the result of evaluating an expression into the specified time series.</p><h3 id="using-variable-placeholders" tabindex="-1"><a class="header-anchor" href="#using-variable-placeholders" aria-hidden="true">#</a> Using variable placeholders</h3><p>In particular, We can use variable placeholders to describe the correspondence between the target and query time series, simplifying the statement. The following two variable placeholders are currently supported:</p><ul><li>Suffix duplication character <code>::</code>: Copy the suffix (or measurement) of the query device, indicating that from this layer to the last layer (or measurement) of the device, the node name (or measurement) of the target device corresponds to the queried device The node name (or measurement) is the same.</li><li>Single-level node matcher <code>\${i}</code>: Indicates that the current level node name of the target sequence is the same as the i-th level node name of the query sequence. For example, for the path <code>root.sg1.d1.s1</code>, <code>\${1}</code> means <code>sg1</code>, <code>\${2}</code> means <code>d1</code>, and <code>\${3}</code> means <code>s1</code>.</li></ul><p>When using variable placeholders, there must be no ambiguity in the correspondence between <code>intoItem</code> and the columns of the query result set. The specific cases are classified as follows:</p><h4 id="align-by-time-default" tabindex="-1"><a class="header-anchor" href="#align-by-time-default" aria-hidden="true">#</a> ALIGN BY TIME (default)</h4><blockquote><p>Note: The variable placeholder <strong>can only describe the correspondence between time series</strong>. If the query includes aggregation and expression calculation, the columns in the query result cannot correspond to a time series, so neither the target device nor the measurement can use variable placeholders.</p></blockquote><h5 id="_1-the-target-device-does-not-use-variable-placeholders-the-target-measurement-list-uses-variable-placeholders" tabindex="-1"><a class="header-anchor" href="#_1-the-target-device-does-not-use-variable-placeholders-the-target-measurement-list-uses-variable-placeholders" aria-hidden="true">#</a> (1) The target device does not use variable placeholders & the target measurement list uses variable placeholders</h5><p><strong>Limitations:</strong></p><ol><li>In each <code>intoItem</code>, the length of the list of physical quantities must be 1. <br> (If the length can be greater than 1, e.g. <code>root.sg1.d1(::, s1)</code>, it is not possible to determine which columns match <code>::</code>)</li><li>The number of <code>intoItem</code> is 1, or the same as the number of columns in the query result set. <br>(When the length of each target measurement list is 1, if there is only one <code>intoItem</code>, it means that all the query sequences are written to the same device; if the number of <code>intoItem</code> is consistent with the query sequence, it is expressed as each query time series specifies a target device; if <code>intoItem</code> is greater than one and less than the number of query sequences, it cannot be a one-to-one correspondence with the query sequence)</li></ol><p><strong>Matching method:</strong> Each query time series specifies the target device, and the target measurement is generated from the variable placeholder.</p><p><strong>Example:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> s1<span class="token punctuation">,</span> s2 |
| <span class="token keyword">into</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d1<span class="token punctuation">(</span>::<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d2<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d1<span class="token punctuation">(</span>\${<span class="token number">3</span>}<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d2<span class="token punctuation">(</span>::<span class="token punctuation">)</span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d1<span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d2<span class="token punctuation">;</span> |
| </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></div><p>This statement is equivalent to:</p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> s1<span class="token punctuation">,</span> s2 |
| <span class="token keyword">into</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d1<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d2<span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d1<span class="token punctuation">(</span>s2<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d2<span class="token punctuation">(</span>s2<span class="token punctuation">)</span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d1<span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d2<span class="token punctuation">;</span> |
| </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></div><p>As you can see, the statement is not very simplified in this case.</p><h5 id="_2-the-target-device-uses-variable-placeholders-the-target-measurement-list-does-not-use-variable-placeholders" tabindex="-1"><a class="header-anchor" href="#_2-the-target-device-uses-variable-placeholders-the-target-measurement-list-does-not-use-variable-placeholders" aria-hidden="true">#</a> (2) The target device uses variable placeholders & the target measurement list does not use variable placeholders</h5><p><strong>Limitations:</strong> The number of target measurements in all <code>intoItem</code> is the same as the number of columns in the query result set.</p><p><strong>Matching method:</strong> The target measurement is specified for each query time series, and the target device is generated according to the target device placeholder of the <code>intoItem</code> where the corresponding target measurement is located.</p><p><strong>Example:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> d1<span class="token punctuation">.</span>s1<span class="token punctuation">,</span> d1<span class="token punctuation">.</span>s2<span class="token punctuation">,</span> d2<span class="token punctuation">.</span>s3<span class="token punctuation">,</span> d3<span class="token punctuation">.</span>s4 |
| <span class="token keyword">into</span> ::<span class="token punctuation">(</span>s1_1<span class="token punctuation">,</span> s2_2<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d2_2<span class="token punctuation">(</span>s3_3<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>\${<span class="token number">2</span>}_copy<span class="token punctuation">.</span>::<span class="token punctuation">(</span>s4<span class="token punctuation">)</span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">;</span> |
| </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></div><h5 id="_3-the-target-device-uses-variable-placeholders-the-target-measurement-list-uses-variable-placeholders" tabindex="-1"><a class="header-anchor" href="#_3-the-target-device-uses-variable-placeholders-the-target-measurement-list-uses-variable-placeholders" aria-hidden="true">#</a> (3) The target device uses variable placeholders & the target measurement list uses variable placeholders</h5><p><strong>Limitations:</strong> There is only one <code>intoItem</code>, and the length of the list of measurement list is 1.</p><p><strong>Matching method:</strong> Each query time series can get a target time series according to the variable placeholder.</p><p><strong>Example:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">into</span> root<span class="token punctuation">.</span>sg_bk<span class="token punctuation">.</span>::<span class="token punctuation">(</span>::<span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span><span class="token operator">*</span><span class="token operator">*</span><span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Write the query results of all time series under <code>root.sg</code> to <code>root.sg_bk</code>, the device name suffix and measurement remain unchanged.</p><h4 id="align-by-device" tabindex="-1"><a class="header-anchor" href="#align-by-device" aria-hidden="true">#</a> ALIGN BY DEVICE</h4><blockquote><p>Note: The variable placeholder <strong>can only describe the correspondence between time series</strong>. If the query includes aggregation and expression calculation, the columns in the query result cannot correspond to a specific physical quantity, so the target measurement cannot use variable placeholders.</p></blockquote><h5 id="_1-the-target-device-does-not-use-variable-placeholders-the-target-measurement-list-uses-variable-placeholders-1" tabindex="-1"><a class="header-anchor" href="#_1-the-target-device-does-not-use-variable-placeholders-the-target-measurement-list-uses-variable-placeholders-1" aria-hidden="true">#</a> (1) The target device does not use variable placeholders & the target measurement list uses variable placeholders</h5><p><strong>Limitations:</strong> In each <code>intoItem</code>, if the list of measurement uses variable placeholders, the length of the list must be 1.</p><p><strong>Matching method:</strong> Each query time series specifies the target device, and the target measurement is generated from the variable placeholder.</p><p><strong>Example:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> s1<span class="token punctuation">,</span> s2<span class="token punctuation">,</span> s3<span class="token punctuation">,</span> s4 |
| <span class="token keyword">into</span> root<span class="token punctuation">.</span>backup_sg<span class="token punctuation">.</span>d1<span class="token punctuation">(</span>s1<span class="token punctuation">,</span> s2<span class="token punctuation">,</span> s3<span class="token punctuation">,</span> s4<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>backup_sg<span class="token punctuation">.</span>d2<span class="token punctuation">(</span>::<span class="token punctuation">)</span><span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d3<span class="token punctuation">(</span>backup_\${<span class="token number">4</span>}<span class="token punctuation">)</span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d1<span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d2<span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d3 |
| align <span class="token keyword">by</span> device<span class="token punctuation">;</span> |
| </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></div><h5 id="_2-the-target-device-uses-variable-placeholders-the-target-measurement-list-does-not-use-variable-placeholders-1" tabindex="-1"><a class="header-anchor" href="#_2-the-target-device-uses-variable-placeholders-the-target-measurement-list-does-not-use-variable-placeholders-1" aria-hidden="true">#</a> (2) The target device uses variable placeholders & the target measurement list does not use variable placeholders</h5><p><strong>Limitations:</strong> There is only one <code>intoItem</code>. (If there are multiple <code>intoItem</code> with placeholders, we will not know which source devices each <code>intoItem</code> needs to match)</p><p><strong>Matching method:</strong> Each query device obtains a target device according to the variable placeholder, and the target measurement written in each column of the result set under each device is specified by the target measurement list.</p><p><strong>Example:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token function">avg</span><span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">sum</span><span class="token punctuation">(</span>s2<span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token function">sum</span><span class="token punctuation">(</span>s3<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">count</span><span class="token punctuation">(</span>s4<span class="token punctuation">)</span> |
| <span class="token keyword">into</span> root<span class="token punctuation">.</span>agg_\${<span class="token number">2</span>}<span class="token punctuation">.</span>::<span class="token punctuation">(</span>avg_s1<span class="token punctuation">,</span> sum_s2_add_s3<span class="token punctuation">,</span> count_s4<span class="token punctuation">)</span> |
| <span class="token keyword">from</span> root<span class="token punctuation">.</span><span class="token operator">*</span><span class="token operator">*</span> |
| align <span class="token keyword">by</span> device<span class="token punctuation">;</span> |
| </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></div><h5 id="_3-the-target-device-uses-variable-placeholders-the-target-measurement-list-uses-variable-placeholders-1" tabindex="-1"><a class="header-anchor" href="#_3-the-target-device-uses-variable-placeholders-the-target-measurement-list-uses-variable-placeholders-1" aria-hidden="true">#</a> (3) The target device uses variable placeholders & the target measurement list uses variable placeholders</h5><p><strong>Limitations:</strong> There is only one <code>intoItem</code> and the length of the target measurement list is 1.</p><p><strong>Matching method:</strong> Each query time series can get a target time series according to the variable placeholder.</p><p><strong>Example:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">into</span> ::<span class="token punctuation">(</span>backup_\${<span class="token number">4</span>}<span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span><span class="token operator">*</span><span class="token operator">*</span> align <span class="token keyword">by</span> device<span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>Write the query result of each time series in <code>root.sg</code> to the same device, and add <code>backup_</code> before the measurement.</p><h3 id="specify-the-target-time-series-as-the-aligned-time-series" tabindex="-1"><a class="header-anchor" href="#specify-the-target-time-series-as-the-aligned-time-series" aria-hidden="true">#</a> Specify the target time series as the aligned time series</h3><p>We can use the <code>ALIGNED</code> keyword to specify the target device for writing to be aligned, and each <code>intoItem</code> can be set independently.</p><p><strong>Example:</strong></p><div class="language-sql line-numbers-mode" data-ext="sql"><pre class="language-sql"><code><span class="token keyword">select</span> s1<span class="token punctuation">,</span> s2 <span class="token keyword">into</span> root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d1<span class="token punctuation">(</span>t1<span class="token punctuation">,</span> t2<span class="token punctuation">)</span><span class="token punctuation">,</span> aligned root<span class="token punctuation">.</span>sg_copy<span class="token punctuation">.</span>d2<span class="token punctuation">(</span>t1<span class="token punctuation">,</span> t2<span class="token punctuation">)</span> <span class="token keyword">from</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d1<span class="token punctuation">,</span> root<span class="token punctuation">.</span>sg<span class="token punctuation">.</span>d2 align <span class="token keyword">by</span> device<span class="token punctuation">;</span> |
| </code></pre><div class="line-numbers" aria-hidden="true"><div class="line-number"></div></div></div><p>This statement specifies that <code>root.sg_copy.d1</code> is an unaligned device and <code>root.sg_copy.d2</code> is an aligned device.</p><h3 id="unsupported-query-clauses" tabindex="-1"><a class="header-anchor" href="#unsupported-query-clauses" aria-hidden="true">#</a> Unsupported query clauses</h3><ul><li><code>SLIMIT</code>, <code>SOFFSET</code>: The query columns are uncertain, so they are not supported.</li><li><code>LAST</code>, <code>GROUP BY TAGS</code>, <code>DISABLE ALIGN</code>: The table structure is inconsistent with the writing structure, so it is not supported.</li></ul><h3 id="other-points-to-note" tabindex="-1"><a class="header-anchor" href="#other-points-to-note" aria-hidden="true">#</a> Other points to note</h3><ul><li>For general aggregation queries, the timestamp is meaningless, and the convention is to use 0 to store.</li><li>When the target time series exists, the metadata information such as the data type, compression, encoding, and whether it belongs to the aligned device of the source time series and the target time series must be consistent.</li><li>When the target time series does not exist, the system automatically creates it (including the database).</li><li>When the queried time series does not exist, or the queried sequence does not have data, the target time series will not be created automatically.</li></ul><h2 id="application-examples" tabindex="-1"><a class="header-anchor" href="#application-examples" aria-hidden="true">#</a> Application examples</h2><h3 id="implement-iotdb-internal-etl" tabindex="-1"><a class="header-anchor" href="#implement-iotdb-internal-etl" aria-hidden="true">#</a> Implement IoTDB internal ETL</h3><p>ETL the original data and write a new time series.</p><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code>IOTDB <span class="token operator">></span> SELECT preprocess_udf<span class="token punctuation">(</span>*<span class="token punctuation">)</span> INTO ::<span class="token punctuation">(</span>preprocessed_<span class="token variable">\${3}</span><span class="token punctuation">)</span> FROM root.sg.*<span class="token punctuation">;</span> |
| +-------------------------------+---------------------------+--------+ |
| <span class="token operator">|</span> <span class="token builtin class-name">source</span> <span class="token function">column</span><span class="token operator">|</span> target timeseries<span class="token operator">|</span> written<span class="token operator">|</span> |
| +-------------------------------+---------------------------+--------+ |
| <span class="token operator">|</span> preprocess_udf<span class="token punctuation">(</span>root.sg.d1.s1<span class="token punctuation">)</span><span class="token operator">|</span> root.sg.d1.preprocessed_s1<span class="token operator">|</span> <span class="token number">8000</span><span class="token operator">|</span> |
| +-------------------------------+---------------------------+--------+ |
| <span class="token operator">|</span> preprocess_udf<span class="token punctuation">(</span>root.sg.d1.s2<span class="token punctuation">)</span><span class="token operator">|</span> root.sg.d1.preprocessed_s1<span class="token operator">|</span> <span class="token number">10000</span><span class="token operator">|</span> |
| +-------------------------------+---------------------------+--------+ |
| <span class="token operator">|</span> preprocess_udf<span class="token punctuation">(</span>root.sg.d2.s1<span class="token punctuation">)</span><span class="token operator">|</span> root.sg.d2.preprocessed_s1<span class="token operator">|</span> <span class="token number">11000</span><span class="token operator">|</span> |
| +-------------------------------+---------------------------+--------+ |
| <span class="token operator">|</span> preprocess_udf<span class="token punctuation">(</span>root.sg.d2.s2<span class="token punctuation">)</span><span class="token operator">|</span> root.sg.d2.preprocessed_s1<span class="token operator">|</span> <span class="token number">9000</span><span class="token operator">|</span> |
| +-------------------------------+---------------------------+--------+ |
| </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 class="line-number"></div><div class="line-number"></div><div class="line-number"></div><div class="line-number"></div></div></div><h3 id="query-result-storage" tabindex="-1"><a class="header-anchor" href="#query-result-storage" aria-hidden="true">#</a> Query result storage</h3><p>Persistently store the query results, which acts like a materialized view.</p><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code>IOTDB <span class="token operator">></span> SELECT count<span class="token punctuation">(</span>s1<span class="token punctuation">)</span>, last_value<span class="token punctuation">(</span>s1<span class="token punctuation">)</span> INTO root.sg.agg_<span class="token variable">\${2}</span><span class="token punctuation">(</span>count_s1, last_value_s1<span class="token punctuation">)</span> FROM root.sg1.d1 GROUP BY <span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token number">0</span>, <span class="token number">10000</span><span class="token punctuation">)</span>, 10ms<span class="token punctuation">)</span><span class="token punctuation">;</span> |
| +--------------------------+-----------------------------+--------+ |
| <span class="token operator">|</span> <span class="token builtin class-name">source</span> <span class="token function">column</span><span class="token operator">|</span> target timeseries<span class="token operator">|</span> written<span class="token operator">|</span> |
| +--------------------------+-----------------------------+--------+ |
| <span class="token operator">|</span> count<span class="token punctuation">(</span>root.sg.d1.s1<span class="token punctuation">)</span><span class="token operator">|</span> root.sg.agg_d1.count_s1<span class="token operator">|</span> <span class="token number">1000</span><span class="token operator">|</span> |
| +--------------------------+-----------------------------+--------+ |
| <span class="token operator">|</span> last_value<span class="token punctuation">(</span>root.sg.d1.s2<span class="token punctuation">)</span><span class="token operator">|</span> root.sg.agg_d1.last_value_s2<span class="token operator">|</span> <span class="token number">1000</span><span class="token operator">|</span> |
| +--------------------------+-----------------------------+--------+ |
| Total line number <span class="token operator">=</span> <span class="token number">2</span> |
| It costs <span class="token number">0</span>.115s |
| </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 class="line-number"></div><div class="line-number"></div></div></div><h3 id="non-aligned-time-series-to-aligned-time-series" tabindex="-1"><a class="header-anchor" href="#non-aligned-time-series-to-aligned-time-series" aria-hidden="true">#</a> Non-aligned time series to aligned time series</h3><p>Rewrite non-aligned time series into another aligned time series.</p><p><strong>Note:</strong> It is recommended to use the <code>LIMIT & OFFSET</code> clause or the <code>WHERE</code> clause (time filter) to batch data to prevent excessive data volume in a single operation.</p><div class="language-bash line-numbers-mode" data-ext="sh"><pre class="language-bash"><code>IOTDB <span class="token operator">></span> SELECT s1, s2 INTO ALIGNED root.sg1.aligned_d<span class="token punctuation">(</span>s1, s2<span class="token punctuation">)</span> FROM root.sg1.non_aligned_d WHERE <span class="token function">time</span> <span class="token operator">>=</span> <span class="token number">0</span> and <span class="token function">time</span> <span class="token operator"><</span> <span class="token number">10000</span><span class="token punctuation">;</span> |
| +--------------------------+----------------------+--------+ |
| <span class="token operator">|</span> <span class="token builtin class-name">source</span> <span class="token function">column</span><span class="token operator">|</span> target timeseries<span class="token operator">|</span> written<span class="token operator">|</span> |
| +--------------------------+----------------------+--------+ |
| <span class="token operator">|</span> root.sg1.non_aligned_d.s1<span class="token operator">|</span> root.sg1.aligned_d.s1<span class="token operator">|</span> <span class="token number">10000</span><span class="token operator">|</span> |
| +--------------------------+----------------------+--------+ |
| <span class="token operator">|</span> root.sg1.non_aligned_d.s2<span class="token operator">|</span> root.sg1.aligned_d.s2<span class="token operator">|</span> <span class="token number">10000</span><span class="token operator">|</span> |
| +--------------------------+----------------------+--------+ |
| Total line number <span class="token operator">=</span> <span class="token number">2</span> |
| It costs <span class="token number">0</span>.375s |
| </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 class="line-number"></div><div class="line-number"></div></div></div><h2 id="user-permission-management" tabindex="-1"><a class="header-anchor" href="#user-permission-management" aria-hidden="true">#</a> User Permission Management</h2><p>The user must have the following permissions to execute a query write-back statement:</p><ul><li>All <code>READ_TIMESERIES</code> permissions for the source series in the <code>select</code> clause.</li><li>All <code>INSERT_TIMESERIES</code> permissions for the target series in the <code>into</code> clause.</li></ul>`,97),m=s("h2",{id:"configurable-properties",tabindex:"-1"},[s("a",{class:"header-anchor",href:"#configurable-properties","aria-hidden":"true"},"#"),e(" Configurable Properties")],-1),h=s("ul",null,[s("li",null,[s("code",null,"select_into_insert_tablet_plan_row_limit"),e(": The maximum number of rows can be processed in one insert-tablet-plan when executing select-into statements. 10000 by default.")])],-1);function k(g,v){const n=t("RouterLink");return o(),p("div",null,[i(` |
| |
| Licensed to the Apache Software Foundation (ASF) under one |
| or more contributor license agreements. See the NOTICE file |
| distributed with this work for additional information |
| regarding copyright ownership. The ASF licenses this file |
| to you under the Apache License, Version 2.0 (the |
| "License"); you may not use this file except in compliance |
| with the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, |
| software distributed under the License is distributed on an |
| "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| KIND, either express or implied. See the License for the |
| specific language governing permissions and limitations |
| under the License. |
| |
| `),d,s("p",null,[e("For more user permissions related content, please refer to "),c(n,{to:"/UserGuide/V1.0.x/Administration-Management/Administration.html"},{default:r(()=>[e("Account Management Statements")]),_:1}),e(".")]),m,h])}const f=a(u,[["render",k],["__file","Select-Into.html.vue"]]);export{f as default}; |
