| import{_ as p,C as c,O as l,P as i,ah as r,Q as n,U as s,ai as e,ae as t,aW as a}from"./framework-e4340ccd.js";const u={},d=a(`<h2 id="查询写回-select-into" tabindex="-1"><a class="header-anchor" href="#查询写回-select-into" aria-hidden="true">#</a> 查询写回（SELECT INTO）</h2><p><code>SELECT INTO</code> 语句用于将查询结果写入一系列指定的时间序列中。</p><p>应用场景如下：</p><ul><li><strong>实现 IoTDB 内部 ETL</strong>：对原始数据进行 ETL 处理后写入新序列。</li><li><strong>查询结果存储</strong>：将查询结果进行持久化存储，起到类似物化视图的作用。</li><li><strong>非对齐序列转对齐序列</strong>：对齐序列从0.13版本开始支持，可以通过该功能将非对齐序列的数据写入新的对齐序列中。</li></ul><h3 id="语法定义" tabindex="-1"><a class="header-anchor" href="#语法定义" aria-hidden="true">#</a> 语法定义</h3><h4 id="整体描述" tabindex="-1"><a class="header-anchor" href="#整体描述" aria-hidden="true">#</a> 整体描述</h4><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><h4 id="into-子句" tabindex="-1"><a class="header-anchor" href="#into-子句" aria-hidden="true">#</a> <code>INTO</code> 子句</h4><p><code>INTO</code> 子句由若干个 <code>intoItem</code> 构成。</p><p>每个 <code>intoItem</code> 由一个目标设备路径和一个包含若干目标物理量名的列表组成（与 <code>INSERT</code> 语句中的 <code>INTO</code> 子句写法类似）。</p><p>其中每个目标物理量名与目标设备路径组成一个目标序列，一个 <code>intoItem</code> 包含若干目标序列。例如：<code>root.sg_copy.d1(s1, s2)</code> 指定了两条目标序列 <code>root.sg_copy.d1.s1</code> 和 <code>root.sg_copy.d1.s2</code>。</p><p><code>INTO</code> 子句指定的目标序列要能够与查询结果集的列一一对应。具体规则如下：</p><ul><li><strong>按时间对齐</strong>（默认）：全部 <code>intoItem</code> 包含的目标序列数量要与查询结果集的列数（除时间列外）一致，且按照表头从左到右的顺序一一对应。</li><li><strong>按设备对齐</strong>（使用 <code>ALIGN BY DEVICE</code>）：全部 <code>intoItem</code> 中指定的目标设备数和查询的设备数（即 <code>FROM</code> 子句中路径模式匹配的设备数）一致，且按照结果集设备的输出顺序一一对应。 为每个目标设备指定的目标物理量数量要与查询结果集的列数（除时间和设备列外）一致，且按照表头从左到右的顺序一一对应。</li></ul><p>下面通过示例进一步说明：</p><ul><li><strong>示例 1</strong>（按时间对齐）</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>该语句将 <code>root.sg</code> database 下四条序列的查询结果写入到 <code>root.sg_copy</code> database 下指定的四条序列中。注意，<code>root.sg_copy.d2(t1, t2)</code> 也可以写做 <code>root.sg_copy.d2(t1), root.sg_copy.d2(t2)</code>。</p><p>可以看到，<code>INTO</code> 子句的写法非常灵活，只要满足组合出的目标序列没有重复，且与查询结果列一一对应即可。</p><blockquote><p><code>CLI</code> 展示的结果集中，各列的含义如下：</p><ul><li><code>source column</code> 列表示查询结果的列名。</li><li><code>target timeseries</code> 表示对应列写入的目标序列。</li><li><code>written</code> 表示预期写入的数据量。</li></ul></blockquote><ul><li><strong>示例 2</strong>（按时间对齐）</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>该语句将聚合查询的结果存储到指定序列中。</p><ul><li><strong>示例 3</strong>（按设备对齐）</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>该语句同样是将 <code>root.sg</code> database 下四条序列的查询结果写入到 <code>root.sg_copy</code> database 下指定的四条序列中。但在按设备对齐中，<code>intoItem</code> 的数量必须和查询的设备数量一致，每个查询设备对应一个 <code>intoItem</code>。</p><blockquote><p>按设备对齐查询时，<code>CLI</code> 展示的结果集多出一列 <code>source device</code> 列表示查询的设备。</p></blockquote><ul><li><strong>示例 4</strong>（按设备对齐）</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>该语句将表达式计算的结果存储到指定序列中。</p><h4 id="使用变量占位符" tabindex="-1"><a class="header-anchor" href="#使用变量占位符" aria-hidden="true">#</a> 使用变量占位符</h4><p>特别地，可以使用变量占位符描述目标序列与查询序列之间的对应规律，简化语句书写。目前支持以下两种变量占位符：</p><ul><li>后缀复制符 <code>::</code>：复制查询设备后缀（或物理量），表示从该层开始一直到设备的最后一层（或物理量），目标设备的节点名（或物理量名）与查询的设备对应的节点名（或物理量名）相同。</li><li>单层节点匹配符 <code>\${i}</code>：表示目标序列当前层节点名与查询序列的第<code>i</code>层节点名相同。比如，对于路径<code>root.sg1.d1.s1</code>而言，<code>\${1}</code>表示<code>sg1</code>，<code>\${2}</code>表示<code>d1</code>，<code>\${3}</code>表示<code>s1</code>。</li></ul><p>在使用变量占位符时，<code>intoItem</code>与查询结果集列的对应关系不能存在歧义，具体情况分类讨论如下：</p><h5 id="按时间对齐-默认" tabindex="-1"><a class="header-anchor" href="#按时间对齐-默认" aria-hidden="true">#</a> 按时间对齐（默认）</h5><blockquote><p>注：变量占位符<strong>只能描述序列与序列之间的对应关系</strong>，如果查询中包含聚合、表达式计算，此时查询结果中的列无法与某个序列对应，因此目标设备和目标物理量都不能使用变量占位符。</p></blockquote><h6 id="_1-目标设备不使用变量占位符-目标物理量列表使用变量占位符" tabindex="-1"><a class="header-anchor" href="#_1-目标设备不使用变量占位符-目标物理量列表使用变量占位符" aria-hidden="true">#</a> （1）目标设备不使用变量占位符 & 目标物理量列表使用变量占位符</h6><p><strong>限制：</strong></p><ol><li>每个 <code>intoItem</code> 中，物理量列表的长度必须为 1。<br>（如果长度可以大于1，例如 <code>root.sg1.d1(::, s1)</code>，无法确定具体哪些列与<code>::</code>匹配）</li><li><code>intoItem</code> 数量为 1，或与查询结果集列数一致。<br>（在每个目标物理量列表长度均为 1 的情况下，若 <code>intoItem</code> 只有 1 个，此时表示全部查询序列写入相同设备；若 <code>intoItem</code> 数量与查询序列一致，则表示为每个查询序列指定一个目标设备；若 <code>intoItem</code> 大于 1 小于查询序列数，此时无法与查询序列一一对应）</li></ol><p><strong>匹配方法：</strong> 每个查询序列指定目标设备，而目标物理量根据变量占位符生成。</p><p><strong>示例：</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>该语句等价于：</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>可以看到，在这种情况下，语句并不能得到很好地简化。</p><h6 id="_2-目标设备使用变量占位符-目标物理量列表不使用变量占位符" tabindex="-1"><a class="header-anchor" href="#_2-目标设备使用变量占位符-目标物理量列表不使用变量占位符" aria-hidden="true">#</a> （2）目标设备使用变量占位符 & 目标物理量列表不使用变量占位符</h6><p><strong>限制：</strong> 全部 <code>intoItem</code> 中目标物理量的数量与查询结果集列数一致。</p><p><strong>匹配方式：</strong> 为每个查询序列指定了目标物理量，目标设备根据对应目标物理量所在 <code>intoItem</code> 的目标设备占位符生成。</p><p><strong>示例：</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><h6 id="_3-目标设备使用变量占位符-目标物理量列表使用变量占位符" tabindex="-1"><a class="header-anchor" href="#_3-目标设备使用变量占位符-目标物理量列表使用变量占位符" aria-hidden="true">#</a> （3）目标设备使用变量占位符 & 目标物理量列表使用变量占位符</h6><p><strong>限制：</strong> <code>intoItem</code> 只有一个且物理量列表的长度为 1。</p><p><strong>匹配方式：</strong> 每个查询序列根据变量占位符可以得到一个目标序列。</p><p><strong>示例：</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>将 <code>root.sg</code> 下全部序列的查询结果写到 <code>root.sg_bk</code>，设备名后缀和物理量名保持不变。</p><h5 id="按设备对齐-使用-align-by-device" tabindex="-1"><a class="header-anchor" href="#按设备对齐-使用-align-by-device" aria-hidden="true">#</a> 按设备对齐（使用 <code>ALIGN BY DEVICE</code>）</h5><blockquote><p>注：变量占位符<strong>只能描述序列与序列之间的对应关系</strong>，如果查询中包含聚合、表达式计算，此时查询结果中的列无法与某个物理量对应，因此目标物理量不能使用变量占位符。</p></blockquote><h6 id="_1-目标设备不使用变量占位符-目标物理量列表使用变量占位符-1" tabindex="-1"><a class="header-anchor" href="#_1-目标设备不使用变量占位符-目标物理量列表使用变量占位符-1" aria-hidden="true">#</a> （1）目标设备不使用变量占位符 & 目标物理量列表使用变量占位符</h6><p><strong>限制：</strong> 每个 <code>intoItem</code> 中，如果物理量列表使用了变量占位符，则列表的长度必须为 1。</p><p><strong>匹配方法：</strong> 每个查询序列指定目标设备，而目标物理量根据变量占位符生成。</p><p><strong>示例：</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><h6 id="_2-目标设备使用变量占位符-目标物理量列表不使用变量占位符-1" tabindex="-1"><a class="header-anchor" href="#_2-目标设备使用变量占位符-目标物理量列表不使用变量占位符-1" aria-hidden="true">#</a> （2）目标设备使用变量占位符 & 目标物理量列表不使用变量占位符</h6><p><strong>限制：</strong> <code>intoItem</code> 只有一个。（如果出现多个带占位符的 <code>intoItem</code>，我们将无法得知每个 <code>intoItem</code> 需要匹配哪几个源设备）</p><p><strong>匹配方式：</strong> 每个查询设备根据变量占位符得到一个目标设备，每个设备下结果集各列写入的目标物理量由目标物理量列表指定。</p><p><strong>示例：</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><h6 id="_3-目标设备使用变量占位符-目标物理量列表使用变量占位符-1" tabindex="-1"><a class="header-anchor" href="#_3-目标设备使用变量占位符-目标物理量列表使用变量占位符-1" aria-hidden="true">#</a> （3）目标设备使用变量占位符 & 目标物理量列表使用变量占位符</h6><p><strong>限制：</strong> <code>intoItem</code> 只有一个且物理量列表的长度为 1。</p><p><strong>匹配方式：</strong> 每个查询序列根据变量占位符可以得到一个目标序列。</p><p><strong>示例：</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>将 <code>root.sg</code> 下每条序列的查询结果写到相同设备下，物理量名前加<code>backup_</code>。</p><h4 id="指定目标序列为对齐序列" tabindex="-1"><a class="header-anchor" href="#指定目标序列为对齐序列" aria-hidden="true">#</a> 指定目标序列为对齐序列</h4><p>通过 <code>ALIGNED</code> 关键词可以指定写入的目标设备为对齐写入，每个 <code>intoItem</code> 可以独立设置。</p><p><strong>示例：</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>该语句指定了 <code>root.sg_copy.d1</code> 是非对齐设备，<code>root.sg_copy.d2</code>是对齐设备。</p><h4 id="不支持使用的查询子句" tabindex="-1"><a class="header-anchor" href="#不支持使用的查询子句" aria-hidden="true">#</a> 不支持使用的查询子句</h4><ul><li><code>SLIMIT</code>、<code>SOFFSET</code>：查询出来的列不确定，功能不清晰，因此不支持。</li><li><code>LAST</code>查询、<code>GROUP BY TAGS</code>、<code>DISABLE ALIGN</code>：表结构和写入结构不一致，因此不支持。</li></ul><h4 id="其他要注意的点" tabindex="-1"><a class="header-anchor" href="#其他要注意的点" aria-hidden="true">#</a> 其他要注意的点</h4>`,81),k=n("li",null,"对于一般的聚合查询，时间戳是无意义的，约定使用 0 来存储。",-1),m=n("li",null,"当目标序列不存在时，系统将自动创建目标序列（包括 database）。",-1),v=n("li",null,"当查询的序列不存在或查询的序列不存在数据，则不会自动创建目标序列。",-1),g=a(`<h3 id="应用举例" tabindex="-1"><a class="header-anchor" href="#应用举例" aria-hidden="true">#</a> 应用举例</h3><h4 id="实现-iotdb-内部-etl" tabindex="-1"><a class="header-anchor" href="#实现-iotdb-内部-etl" aria-hidden="true">#</a> 实现 IoTDB 内部 ETL</h4><p>对原始数据进行 ETL 处理后写入新序列。</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>s1, s2<span class="token punctuation">)</span> INTO ::<span class="token punctuation">(</span>preprocessed_s1, preprocessed_s2<span class="token punctuation">)</span> FROM root.sg.* ALIGN BY DEIVCE<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> preprocess_udf<span class="token punctuation">(</span>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> root.sg.d1<span class="token operator">|</span> preprocess_udf<span class="token punctuation">(</span>s2<span class="token punctuation">)</span><span class="token operator">|</span> root.sg.d1.preprocessed_s2<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> preprocess_udf<span class="token punctuation">(</span>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> root.sg.d2<span class="token operator">|</span> preprocess_udf<span class="token punctuation">(</span>s2<span class="token punctuation">)</span><span class="token operator">|</span> root.sg.d2.preprocessed_s2<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><p>以上语句使用自定义函数对数据进行预处理，将预处理后的结果持久化存储到新序列中。</p><h4 id="查询结果存储" tabindex="-1"><a class="header-anchor" href="#查询结果存储" aria-hidden="true">#</a> 查询结果存储</h4><p>将查询结果进行持久化存储，起到类似物化视图的作用。</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><p>以上语句将降采样查询的结果持久化存储到新序列中。</p><h4 id="非对齐序列转对齐序列" tabindex="-1"><a class="header-anchor" href="#非对齐序列转对齐序列" aria-hidden="true">#</a> 非对齐序列转对齐序列</h4><p>对齐序列从 0.13 版本开始支持，可以通过该功能将非对齐序列的数据写入新的对齐序列中。</p><p><strong>注意：</strong> 建议配合使用 <code>LIMIT & OFFSET</code> 子句或 <code>WHERE</code> 子句（时间过滤条件）对数据进行分批，防止单次操作的数据量过大。</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><p>以上语句将一组非对齐的序列的数据迁移到一组对齐序列。</p><h3 id="相关用户权限" tabindex="-1"><a class="header-anchor" href="#相关用户权限" aria-hidden="true">#</a> 相关用户权限</h3><p>用户必须有下列权限才能正常执行查询写回语句：</p><ul><li>所有 <code>SELECT</code> 子句中源序列的 <code>READ_TIMESERIES</code> 权限。</li><li>所有 <code>INTO</code> 子句中目标序列 <code>INSERT_TIMESERIES</code> 权限。</li></ul>`,17),b=a('<h3 id="相关配置参数" tabindex="-1"><a class="header-anchor" href="#相关配置参数" aria-hidden="true">#</a> 相关配置参数</h3><ul><li><p><code>select_into_insert_tablet_plan_row_limit</code></p><table><thead><tr><th>参数名</th><th>select_into_insert_tablet_plan_row_limit</th></tr></thead><tbody><tr><td>描述</td><td>写入过程中每一批 <code>Tablet</code> 的最大行数</td></tr><tr><td>类型</td><td>int32</td></tr><tr><td>默认值</td><td>10000</td></tr><tr><td>改后生效方式</td><td>重启后生效</td></tr></tbody></table></li></ul>',2);function h(_,y){const o=c("RouterLink");return l(),i("div",null,[r(` |
| |
| 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,n("ul",null,[k,n("li",null,[s("当目标序列存在时，需要保证源序列和目标时间序列的数据类型兼容。关于数据类型的兼容性，查看文档 "),e(o,{to:"/zh/UserGuide/V1.1.x/Data-Concept/Data-Type.html#%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B%E5%85%BC%E5%AE%B9%E6%80%A7"},{default:t(()=>[s("数据类型")]),_:1}),s("。")]),m,v]),g,n("p",null,[s("更多用户权限相关的内容，请参考"),e(o,{to:"/zh/UserGuide/V1.1.x/Administration-Management/Administration.html"},{default:t(()=>[s("权限管理语句")]),_:1}),s("。")]),b])}const f=p(u,[["render",h],["__file","Select-Into.html.vue"]]);export{f as default}; |
