ClickHouse（三）SQL語法 · Clickhouse學習筆記

<div id="article_content" class="article_content clearfix"> <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/kdoc_html_views-1a98987dfd.css"> <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-25cebea3f9.css"> <div id="content_views" class="htmledit_views"> <h1><a name="t0"></a>1.SQL基本語法</h1> <h2><a name="t1"></a>1.1.CREATE</h2> <h3><a name="t2"></a>1.1.1.CREATE DATABASE</h3> <p>數據庫引擎：數據庫目前一共支持5種引擎，如下所示：</p> <ul><li>Ordinary：默認引擎，在絕大多數情況下我們都會使用默認引擎，使用時無須刻意聲明。在此數據庫下可以使用任意類型的表引擎。</li><li>Dictionary：字典引擎，此類數據庫會自動為所有數據字典創建它們的數據表</li><li>Memory：內存引擎，用于存放臨時數據。此類數據庫下的數據表只會停留在內存中，不會涉及任何磁盤操作，當服務重啟后數據會被清除。</li><li>Lazy：日志引擎，此類數據庫下只能使用Log系列的表引擎</li><li>MySQL:MySQL引擎，此類數據庫下會自動拉取遠端MySQL中的數據，并為它們創建MySQL表引擎的數據表</li></ul> <p>在絕大多數情況下都只需使用默認的數據庫引擎。</p> <p>默認數據庫的實質是物理磁盤上的一個文件目錄，所以在語句執行之后，<a href="https://so.csdn.net/so/search?q=ClickHouse&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=ClickHouse&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"ClickHouse\"}"}" data-tit="ClickHouse" data-pretit="clickhouse">ClickHouse</a>便會在安裝路徑下創建數據庫的文件目錄:</p> <pre data-index="0"><code class="language-bash hljs">ll /var/lib/clickhouse/data</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>與此同時，在metadata路徑下也會一同創建用于恢復數據庫的文件,在庫的目錄下又有恢復表的SQL語句：</p> <pre data-index="1"><code class="language-bash hljs">ll /var/lib/clickhouse/metadata</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p></p> <h3><a name="t3"></a>1.1.2.CREATE TABLE</h3> <pre data-index="2"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> [IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span>] [db.]table_name [<span class="hljs-keyword">ON</span> CLUSTER cluster]</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">name1 [type1] [<span class="hljs-keyword">DEFAULT</span><span class="hljs-operator">|</span>MATERIALIZED<span class="hljs-operator">|</span>ALIAS expr1],</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">name2 [type2] [<span class="hljs-keyword">DEFAULT</span><span class="hljs-operator">|</span>MATERIALIZED<span class="hljs-operator">|</span>ALIAS expr2],</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">...</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) ENGINE <span class="hljs-operator">=</span> engine</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>參數說明：</p> <div class="table-box"><table align="center" border="1" cellpadding="1" cellspacing="1"><thead><tr><th>參數</th><th>意義</th></tr></thead><tbody><tr><td>DEFAULT expr</td><td>默認值，用法與SQL類似。</td></tr><tr><td>MATERIALIZED expr</td><td>物化表達式，被該表達式指定的列不能被INSERT，因為它總是被計算出來的。<br> 對于INSERT而言，不需要考慮這些列。另外，在SELECT查詢中如果包含星號，此列不會被查詢。</td></tr><tr><td>ALIAS expr</td><td>別名</td></tr></tbody></table></div> <p>表的三種創建方式：</p> <p>1.直接創建</p> <p>2.創建一個與其他表具有相同結構的表：</p> <pre data-index="3"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> [IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span>] [db.]table_name <span class="hljs-keyword">AS</span> [db2.]name2 [ENGINE <span class="hljs-operator">=</span> engine]</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>3.使用指定的引擎創建一個與 SELECT 子句的結果具有相同結構的表，并使用 SELECT子句的結果填充它：</p> <pre data-index="4"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> [IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span>] [db.]table_name ENGINE <span class="hljs-operator">=</span> engine <span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span> ...</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p></p> <h3><a name="t4"></a>1.1.3.表字段</h3> <p>表字段支持三種默認值表達式的定義方法，<span style="color:#f33b45;">分別是DEFAULT、MATERIALIZED和ALIAS</span>。無論使用哪種形式，表字段一旦被定義了默認值，它便不再強制要求定義數據類型，因為 ClickHouse 會根據默認值進行類型推斷。如果同時對表字段定義了數據類型和默認值表達式，則以明確定義的數據類型為主</p> <p>默認值表達式的三種定義方法之間也存在著不同之處，可以從如下三個方面進行比較：</p> <p>1、<strong>數據寫入：在數據寫入時，只有DEFAULT類型的字段可以出現在INSERT語句中。而MATERIALIZED和ALIAS都不能被顯式賦值，它們只能依靠計算取值。</strong>例如試圖為MATERIALIZED類型的字段寫入數據，將會得到如下的錯誤</p> <pre data-index="5"><code class="language-bash hljs">DB::Exception: Cannot insert column URL,because it is MATERIALIZED column..</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>2、<strong>數據查詢：在數據查詢時，只有DEFAULT類型的字段可以通過SELECT *返回。而MATERIALIZED和ALIAS類型的字段不會出現在SELECT *查詢的返回結果集中。</strong></p> <p>3、<strong>數據存儲：在數據存儲時，只有DEFAULT和MATERIALIZED類型的字段才支持持久化。如果使用的表引擎支持物理存儲（例如TinyLog表引擎），那么這些列字段將會擁有物理存儲。而ALIAS類型的字段不支持持久化，它的取值總是需要依靠計算產生，數據不會落到磁盤。</strong></p> <p>可以使用ALTER語句修改默認值，例如：</p> <pre data-index="6"><code class="language-bash hljs">ALTER TABLE [db_name.]table MODIFY COLUMN col_name DEFAULT value;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>修改動作并不會影響數據表內先前已經存在的數據。但是默認值的修改有諸多限制，例如在合并樹表引擎中，它的主鍵字段是無法被修改的；而某些表引擎則完全不支持修改（例如 TinyLog）。</p> <p></p> <h3><a name="t5"></a>1.1.4.臨時表</h3> <p>ClickHouse 也有臨時表的概念，創建臨時表的方法是在普通表的基礎之上添加 TEMPORARY 關鍵字，它的完整語法如下所示：</p> <pre data-index="7"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> TEMPORARY <span class="hljs-keyword">TABLE</span> [IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span>] table_name (</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name1 [type][<span class="hljs-keyword">DEFAULT</span><span class="hljs-operator">|</span>MATERIALIZED<span class="hljs-operator">|</span>ALIAS expr],</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name2 [type][<span class="hljs-keyword">DEFAULT</span><span class="hljs-operator">|</span>MATERIALIZED<span class="hljs-operator">|</span>ALIAS expr]</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>相比普通表而言，臨時表有如下幾點特殊之處：</p> <ol><li>它的生命周期是會話綁定的，所以它只支持Memory表引擎，如果會話結束，數據表就會被銷毀；</li><li>臨時表不屬于任何數據庫，所以在它的建表語句中，既沒有數據庫參數也沒有表引擎參數。</li><li>臨時表的優先級是大于普通表的。當兩張數據表名稱相同的時候，會優先讀取臨時表的數據。</li><li>在ClickHouse的日常使用中，通常不會刻意使用臨時表。它更多被運用在ClickHouse的內部，是數據在集群間傳播的載體。</li></ol> <h3><a name="t6"></a>1.1.5.分區表</h3> <p>數據分區（partition）和數據分片（shard）是完全不同的兩個概念。數據分區是針對本地數據而言的，是數據的一種縱向切分。而數據分片是數據的一種橫向切分。數據分區對于一款OLAP數據庫而言意義非凡：借助數據分區，在后續的查詢過程中能夠跳過不必要的數據目錄，從而提升查詢的性能。合理地利用分區特性，還可以變相實現數據的更新操作，因為數據分區支持刪除、替換和重置操作。假設數據表按照月份分區，那么數據就可以按月份的粒度被替換更新。<br> 分區雖好，但不是所有的表引擎都可以使用這項特性，<span style="color:#f33b45;"><strong>目前只有合并樹（MergeTree）家族系列的表引擎才支持數據分區</strong></span>。接下來通過一個簡單的例子演示分區表的使用方法。首先由 PARTITION BY 指定分區鍵，例如下面的數據表 my_table_partition1 使用了日期字段作為分區鍵，并將其格式化為年月的形式：</p> <pre data-index="8"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> my_table_partition1(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> url String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> eventTime <span class="hljs-type">Date</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) engine <span class="hljs-operator">=</span> MergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> toYYYYMM(eventTime)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> id;</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>可以通過以下SQL查詢表的分區狀態：</p> <pre data-index="9"><code class="language-sql hljs"><span class="hljs-keyword">select</span> <span class="hljs-keyword">table</span>, <span class="hljs-keyword">partition</span>, path <span class="hljs-keyword">from</span> system.parts <span class="hljs-keyword">where</span> <span class="hljs-keyword">table</span> <span class="hljs-operator">=</span> <span class="hljs-string">'my_table_partition1'</span>;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <h2><a name="t7"></a>1.2.ClickHouse視圖</h2> <p><strong>ClickHouse 擁有普通和物化兩種視圖，其中物化視圖擁有獨立的存儲，而普通視圖只是一層簡單的查詢代理。</strong>創建普通視圖的完整語法如下所示：</p> <pre data-index="10"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">VIEW</span> [IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span>] [db_name.]view_name <span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span> ...</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>物化視圖支持表引擎，數據保存形式由它的表引擎決定，創建物化視圖的完整語法如下所示：</p> <pre data-index="11"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> [MATERIALIZED] <span class="hljs-keyword">VIEW</span> [IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span>] [db.]table_name [<span class="hljs-keyword">To</span> [db.]name] [ENGINE<span class="hljs-operator">=</span>engine] [POPULATE] <span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span> ...</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>物化視圖創建好之后，如果源表被寫入新數據，那么物化視圖也會同步更新。POPULATE修飾符決定了物化視圖的初始化策略：如果使用了POPULATE修飾符，那么在創建視圖的過程中，會連帶將源表中已存在的數據一并導入，如同執行了SELECT INTO一般；反之，如果不使用POPULATE修飾符，那么物化視圖在創建之后是沒有數據的，它只會同步在此之后被寫入源表的數據。物化視圖目前并不支持同步刪除，如果在源表中刪除了數據，物化視圖的數據仍會保留。</p> <p></p> <h2><a name="t8"></a>1.3. ALTER</h2> <p><span style="color:#f33b45;"><strong>ALTER 只支持 MergeTree 系列、Merge 和 Distributed 引擎的表，</strong></span>基本語法：</p> <pre data-index="12"><code class="language-sql hljs"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> [db].name [<span class="hljs-keyword">ON</span> CLUSTER cluster] <span class="hljs-keyword">ADD</span><span class="hljs-operator">|</span><span class="hljs-keyword">DROP</span><span class="hljs-operator">|</span>MODIFY<span class="hljs-operator">|</span>COMMENT <span class="hljs-keyword">COLUMN</span> ...</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>參數解析：</p> <blockquote> <p>ADD COLUMN – 向表中添加新列<br> DROP COLUMN – 在表中刪除列<br> MODIFY COLUMN – 更改列的類型<br> COMMENT COLUMN – 更改列的注釋</p> </blockquote> <h2><a name="t9"></a>1.4.Partition</h2> <p>查看表的分區信息</p> <pre data-index="13"><code class="language-sql hljs"><span class="hljs-keyword">select</span> partition_id, name, <span class="hljs-keyword">table</span>, database, path <span class="hljs-keyword">from</span> system.parts <span class="hljs-keyword">where</span> <span class="hljs-keyword">table</span> <span class="hljs-operator">=</span> <span class="hljs-string">'my_table_partition1'</span>;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>刪除分區</p> <pre data-index="14"><code class="language-sql hljs"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> my_table_partition1 <span class="hljs-keyword">drop</span> <span class="hljs-keyword">partition</span> <span class="hljs-string">'202005'</span>;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>復制分區</p> <pre data-index="15"><code class="language-sql hljs"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> my_table_partition2 replace <span class="hljs-keyword">partition</span> <span class="hljs-string">'202005'</span> <span class="hljs-keyword">from</span> my_table_partition1;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>卸載和裝載分區</p> <p>表分區可以通過DETACH語句卸載，分區被卸載后，它的物理數據并沒有刪除，而是被轉移到了當前數據表目錄的detached子目錄下。而裝載分區則是反向操作，它能夠將detached子目錄下的某個分區重新裝載回去。卸載與裝載這一對伴生的操作，常用于分區數據的遷移和備份場景。</p> <p>記住，一旦分區被移動到了detached子目錄，就代表它已經脫離了ClickHouse的管理，ClickHouse并不會主動清理這些文件。這些分區文件會一直存在，除非我們主動刪除或者使用ATTACH語句重新裝載它們。</p> <pre data-index="16"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">--卸載</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> tb_name DETACH <span class="hljs-keyword">PARTITION</span> partition_expr</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">--裝載</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> tb_name ATTACH <span class="hljs-keyword">PARTITION</span> partition_expr</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <h2><a name="t10"></a>1.5.CHECK TABLE</h2> <p>檢查表中的數據是否損壞，他會返回兩種結果：0 - 數據已損壞；1 - 數據完整</p> <pre data-index="17"><code class="language-sql hljs"><span class="hljs-keyword">check</span> <span class="hljs-keyword">table</span> mt_table;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p> 該命令只支持 Log，TinyLog 和 StripeLog 引擎。</p> <h2><a name="t11"></a>1.6.分布式DDL執行</h2> <p>ClickHouse支持集群模式，一個集群擁有1到多個節點。CREATE、ALTER、DROP、RENMAE及TRUNCATE這些DDL語句，都支持分布式執行。這意味著，如果在集群中任意一個節點上執行DDL語句，那么集群中的每個節點都會以相同的順序執行相同的語句。這項特性意義非凡，它就如同批處理命令一樣，省去了需要依次去單個節點執行DDL的煩惱。</p> <p>將一條普通的DDL語句轉換成分布式執行十分簡單，只需加上ON CLUSTER cluster_name聲明即可。例如，執行下面的語句后將會對集群內的所有節點廣播這條DDL語句：</p> <pre data-index="18"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- 分布式建庫</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> database if <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> nxdb4 <span class="hljs-keyword">on</span> CLUSTER perftest_3shards_1replicas;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- 分布式建表</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> nx_table_partition3 <span class="hljs-keyword">ON</span> CLUSTER perftest_3shards_1replicas(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> ID String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> URL String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> EventTime <span class="hljs-type">Date</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE<span class="hljs-operator">=</span>MergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> toYYYYMM(EventTime)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> ID;</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <h1><a name="t12"></a>1.7.update 和 delete</h1> <p>        ClickHouse提供了DELETE和UPDATE的能力，這類操作被稱為Mutation（突變）查詢，它可以看作ALTER語句的變種。</p> <ol><li>Mutation操作適用于批量數據的修改和刪除</li><li>不支持事務一旦語句被提交執行就會立刻對現有的數據產生影響，無法回滾。</li><li>Mutation操作執行是一個異步的過程，語句提交會立即返回，但是不代表具體邏輯已經執行完畢，具體的執行記錄需要在system.mutations系統表查詢。</li></ol> <p>        雖然可以實現修改和刪除，但是和一般的OLTP數據庫不一樣，Mutation語句是一種很“重”的操作，而且<span style="color:#fe2c24;"><strong>不支持事務</strong></span>。</p> <p>        “重”的原因主要是每次修改或者刪除都會導致放棄目標數據的原有分區，重建新分區。所以盡量做批量的變更，不要進行頻繁小數據的操作。</p> <p>        由于操作比較“重”，所以Mutation語句分兩步執行，同步執行的部分其實只是進行新增數據新增分區和并把舊分區打上邏輯上的失效標記。直到觸發分區合并的時候，才會刪除舊數據釋放磁盤空間，一般不會開放這樣的功能給用戶，由管理員完成。</p> <pre data-index="19" class="set-code-hide" name="code"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> database if <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> mydb;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">use mydb;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">--創建表</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> city(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id UInt8,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> country String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> area String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> province String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> city String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">create_time datetime <span class="hljs-keyword">DEFAULT</span> now()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">ENGINE <span class="hljs-operator">=</span> MergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> toYYYYMM(create_time)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> id;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="17"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">-- 插入數據</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="18"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> city(id,country,area,province,city) <span class="hljs-keyword">VALUES</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="19"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">(<span class="hljs-number">1</span>,<span class="hljs-string">'China'</span>,<span class="hljs-string">'North'</span>,<span class="hljs-string">'Hubei'</span>,<span class="hljs-string">'wuhan'</span>),</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="20"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">(<span class="hljs-number">2</span>,<span class="hljs-string">'China'</span>,<span class="hljs-string">'South'</span>,<span class="hljs-string">'Guangdong'</span>,<span class="hljs-string">'guangzhou'</span>),</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="21"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">(<span class="hljs-number">3</span>,<span class="hljs-string">'China'</span>,<span class="hljs-string">'South'</span>,<span class="hljs-string">'Guangdong'</span>,<span class="hljs-string">'shenzhen'</span>),</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="22"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">(<span class="hljs-number">4</span>,<span class="hljs-string">'China'</span>,<span class="hljs-string">'North'</span>,<span class="hljs-string">'Beijing'</span>,<span class="hljs-string">'Beijing'</span>),</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="23"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">(<span class="hljs-number">5</span>,<span class="hljs-string">'China'</span>,<span class="hljs-string">'South'</span>,<span class="hljs-string">'Shanghai'</span>,<span class="hljs-string">'Shanghai'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="24"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="25"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">--數據查詢</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="26"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">select</span> id, country, area, province, city, create_time <span class="hljs-keyword">from</span> city;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="27"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="28"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">--update 操作</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="29"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> city <span class="hljs-keyword">UPDATE</span> area<span class="hljs-operator">=</span><span class="hljs-string">'South'</span> <span class="hljs-keyword">WHERE</span> city<span class="hljs-operator">=</span><span class="hljs-string">'wuhan'</span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="30"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="31"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">--delete操作</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="32"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> city <span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">WHERE</span> city<span class="hljs-operator">=</span><span class="hljs-string">'guangzhou'</span>;</div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{"spm":"1001.2101.3001.7365"}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>查看數據所在目錄：</p> <pre data-index="20"><code class="language-bash hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">[root@hadoop1 mydb]<span class="hljs-comment"># cd /var/lib/clickhouse/data/mydb/city/</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">[root@hadoop1 city]<span class="hljs-comment"># ll -a</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">total 24</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 6 root root  156 Jun  5 17:07 .</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 3 root root   18 Jun  5 17:07 ..</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 4096 Jun  5 17:07 202106_1_1_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 4096 Jun  5 17:07 202106_1_1_0_2</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 4096 Jun  5 17:07 202106_1_1_0_3</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root    6 Jun  5 17:07 detached</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root    1 Jun  5 17:07 format_version.txt</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root  109 Jun  5 17:07 mutation_2.txt</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root   96 Jun  5 17:07 mutation_3.txt</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>可以發現在執行了update，delete操作之后數據目錄會生成文件mutation_2.txt，mutation_3.txt。此外還有在同名的目錄下在末尾增加了_2 （由update產生），_3（由delete產生）的后綴。可以看到mutation_2.txt（由update產生，存儲的執行的SQL語句及操作時間，如：UPDATE area = \'South\' WHERE city = \'wuhan\'）和mutation_3.txt （由delete產生）是日志文件，完整的記錄了update和delete操作語句和時間。當執行optimize table mydb.city final;后會生成202106_1_1_1_3的目錄</p> <p>mutation_id：生成對應的日志文件用于記錄相關的信息。數據刪除的過程是以數據表的每個分區目錄為單位，將所有目錄重寫為新的目錄，在目錄的命名規則是在原有的名稱上加上</p> <p>block_numbers.number：數據的在重寫的過程中會將所需要刪除的數據去掉。舊的數據并不會立即刪除，而是被標記為非激活狀態(active =0)，等到MergeTree引擎的下一次合并動作觸發的時候，這些非活動目錄才會被真正的從物理上刪除。</p> </div><div data-report-view="{"mod":"1585297308_001","spm":"1001.2101.3001.6548","dest":"https://blog.csdn.net/Yuan_CSDF/article/details/117597970","extend1":"pc","ab":"new"}"><div></div></div> </div>