Google Git
Sign in
apache / iotdb-website / 58cad7010f370b058faa76922f1e0850e2372f4d / . / assets / Select-Into.html-B9CoGUAa.js
blob: d3bf1ddfea53cd2d87b366f930b74a2ffc9a4a41 [file] [log] [blame]
import{_ as p,r as c,o as l,c as i,b as n,d as s,a as o,w as e,e as a}from"./app-Bx8hKGcu.js";const r={},u=a(`<h2 id="查询写回-select-into" tabindex="-1"><a class="header-anchor" href="#查询写回-select-into"><span>查询写回（SELECT INTO）</span></a></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="#语法定义"><span>语法定义</span></a></h3><h4 id="整体描述" tabindex="-1"><a class="header-anchor" href="#整体描述"><span>整体描述</span></a></h4><div class="language-sql line-numbers-mode" data-ext="sql" data-title="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">&#39;(&#39;</span> intoMeasurementName <span class="token punctuation">[</span><span class="token string">&#39;,&#39;</span> intoMeasurementName<span class="token punctuation">]</span><span class="token operator">*</span> <span class="token string">&#39;)&#39;</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-子句"><span><code>INTO</code> 子句</span></a></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> 子句中路径模式匹配的设备数）一致，且按照结果集设备的输出顺序一一对应。<br> 为每个目标设备指定的目标物理量数量要与查询结果集的列数（除时间和设备列外）一致，且按照表头从左到右的顺序一一对应。</li></ul><p>下面通过示例进一步说明：</p><ul><li><strong>示例 1</strong>（按时间对齐）</li></ul><div class="language-bash line-numbers-mode" data-ext="sh" data-title="sh"><pre class="language-bash"><code>IoTDB<span class="token operator">&gt;</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" data-title="sh"><pre class="language-bash"><code>IoTDB<span class="token operator">&gt;</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" data-title="sh"><pre class="language-bash"><code>IoTDB<span class="token operator">&gt;</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" data-title="sh"><pre class="language-bash"><code>IoTDB<span class="token operator">&gt;</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="#使用变量占位符"><span>使用变量占位符</span></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="#按时间对齐-默认"><span>按时间对齐（默认）</span></a></h5><blockquote><p>注：变量占位符<strong>只能描述序列与序列之间的对应关系</strong>，如果查询中包含聚合、表达式计算，此时查询结果中的列无法与某个序列对应，因此目标设备和目标物理量都不能使用变量占位符。</p></blockquote><h6 id="_1-目标设备不使用变量占位符-目标物理量列表使用变量占位符" tabindex="-1"><a class="header-anchor" href="#_1-目标设备不使用变量占位符-目标物理量列表使用变量占位符"><span>（1）目标设备不使用变量占位符 &amp; 目标物理量列表使用变量占位符</span></a></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" data-title="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" data-title="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-目标设备使用变量占位符-目标物理量列表不使用变量占位符"><span>（2）目标设备使用变量占位符 &amp; 目标物理量列表不使用变量占位符</span></a></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" data-title="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-目标设备使用变量占位符-目标物理量列表使用变量占位符"><span>（3）目标设备使用变量占位符 &amp; 目标物理量列表使用变量占位符</span></a></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" data-title="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"><span>按设备对齐（使用 <code>ALIGN BY DEVICE</code>）</span></a></h5><blockquote><p>注：变量占位符<strong>只能描述序列与序列之间的对应关系</strong>，如果查询中包含聚合、表达式计算，此时查询结果中的列无法与某个物理量对应，因此目标物理量不能使用变量占位符。</p></blockquote><h6 id="_1-目标设备不使用变量占位符-目标物理量列表使用变量占位符-1" tabindex="-1"><a class="header-anchor" href="#_1-目标设备不使用变量占位符-目标物理量列表使用变量占位符-1"><span>（1）目标设备不使用变量占位符 &amp; 目标物理量列表使用变量占位符</span></a></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" data-title="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"><span>（2）目标设备使用变量占位符 &amp; 目标物理量列表不使用变量占位符</span></a></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" data-title="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"><span>（3）目标设备使用变量占位符 &amp; 目标物理量列表使用变量占位符</span></a></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" data-title="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="#指定目标序列为对齐序列"><span>指定目标序列为对齐序列</span></a></h4><p>通过 <code>ALIGNED</code> 关键词可以指定写入的目标设备为对齐写入，每个 <code>intoItem</code> 可以独立设置。</p><p><strong>示例：</strong></p><div class="language-sql line-numbers-mode" data-ext="sql" data-title="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="#不支持使用的查询子句"><span>不支持使用的查询子句</span></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="#其他要注意的点"><span>其他要注意的点</span></a></h4>`,81),d=n("li",null,"对于一般的聚合查询，时间戳是无意义的，约定使用 0 来存储。",-1),k=n("li",null,"当目标序列不存在时，系统将自动创建目标序列（包括 database）。",-1),m=n("li",null,"当查询的序列不存在或查询的序列不存在数据，则不会自动创建目标序列。",-1),v=a(`<h3 id="应用举例" tabindex="-1"><a class="header-anchor" href="#应用举例"><span>应用举例</span></a></h3><h4 id="实现-iotdb-内部-etl" tabindex="-1"><a class="header-anchor" href="#实现-iotdb-内部-etl"><span>实现 IoTDB 内部 ETL</span></a></h4><p>对原始数据进行 ETL 处理后写入新序列。</p><div class="language-bash line-numbers-mode" data-ext="sh" data-title="sh"><pre class="language-bash"><code>IOTDB <span class="token operator">&gt;</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="#查询结果存储"><span>查询结果存储</span></a></h4><p>将查询结果进行持久化存储，起到类似物化视图的作用。</p><div class="language-bash line-numbers-mode" data-ext="sh" data-title="sh"><pre class="language-bash"><code>IOTDB <span class="token operator">&gt;</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="#非对齐序列转对齐序列"><span>非对齐序列转对齐序列</span></a></h4><p>对齐序列从 0.13 版本开始支持，可以通过该功能将非对齐序列的数据写入新的对齐序列中。</p><p><strong>注意：</strong> 建议配合使用 <code>LIMIT &amp; OFFSET</code> 子句或 <code>WHERE</code> 子句（时间过滤条件）对数据进行分批，防止单次操作的数据量过大。</p><div class="language-bash line-numbers-mode" data-ext="sh" data-title="sh"><pre class="language-bash"><code>IOTDB <span class="token operator">&gt;</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">&gt;=</span> <span class="token number">0</span> and <span class="token function">time</span> <span class="token operator">&lt;</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="#相关用户权限"><span>相关用户权限</span></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),g=a('<h3 id="相关配置参数" tabindex="-1"><a class="header-anchor" href="#相关配置参数"><span>相关配置参数</span></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 b(h,_){const t=c("RouteLink");return l(),i("div",null,[u,n("ul",null,[d,n("li",null,[s("当目标序列存在时，需要保证源序列和目标时间序列的数据类型兼容。关于数据类型的兼容性，查看文档 "),o(t,{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:e(()=>[s("数据类型")]),_:1}),s("。")]),k,m]),v,n("p",null,[s("更多用户权限相关的内容，请参考"),o(t,{to:"/zh/UserGuide/V1.1.x/Administration-Management/Administration.html"},{default:e(()=>[s("权限管理语句")]),_:1}),s("。")]),g])}const I=p(r,[["render",b],["__file","Select-Into.html.vue"]]),T=JSON.parse('{"path":"/zh/UserGuide/V1.1.x/Query-Data/Select-Into.html","title":"","lang":"zh-CN","frontmatter":{"description":"查询写回（SELECT INTO） SELECT INTO 语句用于将查询结果写入一系列指定的时间序列中。 应用场景如下： 实现 IoTDB 内部 ETL：对原始数据进行 ETL 处理后写入新序列。 查询结果存储：将查询结果进行持久化存储，起到类似物化视图的作用。 非对齐序列转对齐序列：对齐序列从0.13版本开始支持，可以通过该功能将非对齐序列的数据写...","head":[["link",{"rel":"alternate","hreflang":"en-us","href":"https://iotdb.apache.org/UserGuide/V1.1.x/Query-Data/Select-Into.html"}],["meta",{"property":"og:url","content":"https://iotdb.apache.org/zh/UserGuide/V1.1.x/Query-Data/Select-Into.html"}],["meta",{"property":"og:site_name","content":"IoTDB Website"}],["meta",{"property":"og:description","content":"查询写回（SELECT INTO） SELECT INTO 语句用于将查询结果写入一系列指定的时间序列中。 应用场景如下： 实现 IoTDB 内部 ETL：对原始数据进行 ETL 处理后写入新序列。 查询结果存储：将查询结果进行持久化存储，起到类似物化视图的作用。 非对齐序列转对齐序列：对齐序列从0.13版本开始支持，可以通过该功能将非对齐序列的数据写..."}],["meta",{"property":"og:type","content":"article"}],["meta",{"property":"og:locale","content":"zh-CN"}],["meta",{"property":"og:locale:alternate","content":"en-US"}],["meta",{"property":"og:updated_time","content":"2023-07-10T03:11:17.000Z"}],["meta",{"property":"article:modified_time","content":"2023-07-10T03:11:17.000Z"}],["script",{"type":"application/ld+json"},"{\\"@context\\":\\"https://schema.org\\",\\"@type\\":\\"Article\\",\\"headline\\":\\"\\",\\"image\\":[\\"\\"],\\"dateModified\\":\\"2023-07-10T03:11:17.000Z\\",\\"author\\":[]}"]]},"headers":[{"level":2,"title":"查询写回（SELECT INTO）","slug":"查询写回-select-into","link":"#查询写回-select-into","children":[{"level":3,"title":"语法定义","slug":"语法定义","link":"#语法定义","children":[]},{"level":3,"title":"应用举例","slug":"应用举例","link":"#应用举例","children":[]},{"level":3,"title":"相关用户权限","slug":"相关用户权限","link":"#相关用户权限","children":[]},{"level":3,"title":"相关配置参数","slug":"相关配置参数","link":"#相关配置参数","children":[]}]}],"git":{"createdTime":1688958677000,"updatedTime":1688958677000,"contributors":[{"name":"CritasWang","email":"critas@outlook.com","commits":1}]},"readingTime":{"minutes":10.26,"words":3078},"filePathRelative":"zh/UserGuide/V1.1.x/Query-Data/Select-Into.md","localizedDate":"2023年7月10日","autoDesc":true}');export{I as comp,T as data};