ClickHouse（六）知識點補漏 · 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、二級索引</h1> <p>        目前在ClikHouse的官網上二級索引的功能在v20.1.2.4之前是被標注為實驗性，在這個版本之后默認是開啟的。</p> <p>        1、老版本使用二級索引前需要增加設置</p> <p>        是否允許使用實驗性的二級索引（v20.1.2.4開始，這個參數已被刪除，默認開啟）</p> <pre data-index="0"><code class="language-sql hljs"><span class="hljs-keyword">set</span> allow_experimental_data_skipping_indices<span class="hljs-operator">=</span><span class="hljs-number">1</span>;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>        2、創建測試表</p> <pre data-index="1" 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-comment">-- 建表，增加二級索引 a </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> <span class="hljs-keyword">table</span> t_order_mt2(</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"> id UInt32,</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"> sku_id String,</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"> total_amount <span class="hljs-type">Decimal</span>(<span class="hljs-number">16</span>,<span class="hljs-number">2</span>),</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"> create_time Datetime,</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-comment">-- 這里的GRANULARITY是設定二級索引對于一級索引粒度的粒度</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"> INDEX a total_amount TYPE minmax GRANULARITY <span class="hljs-number">5</span></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">) 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="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> toYYYYMMDD(create_time)</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"><span class="hljs-keyword">primary</span> key(id)</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"><span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span>(id,sku_id);</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"> </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-comment">-- 寫入數據</span></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">insert</span> <span class="hljs-keyword">into</span> t_order_mt2 <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="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">(<span class="hljs-number">101</span>,<span class="hljs-string">'sku_001'</span>,<span class="hljs-number">1000.00</span>,<span class="hljs-string">'2020-06-01 12:00:00'</span>), </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-number">102</span>,<span class="hljs-string">'sku_002'</span>,<span class="hljs-number">2000.00</span>,<span class="hljs-string">'2020-06-01 11:00:00'</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-number">102</span>,<span class="hljs-string">'sku_004'</span>,<span class="hljs-number">2500.00</span>,<span class="hljs-string">'2020-06-01 12:00:00'</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">102</span>,<span class="hljs-string">'sku_002'</span>,<span class="hljs-number">2000.00</span>,<span class="hljs-string">'2020-06-01 13:00:00'</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">102</span>,<span class="hljs-string">'sku_002'</span>,<span class="hljs-number">12000.00</span>,<span class="hljs-string">'2020-06-01 13:00:00'</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">102</span>,<span class="hljs-string">'sku_002'</span>,<span class="hljs-number">600.00</span>,<span class="hljs-string">'2020-06-02 12:00:00'</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>        3、執行以下命令：</p> <pre data-index="2"><code class="language-bash hljs">clickhouse-client -port 9977 --send_logs_level=trace<<<<span class="hljs-string">'select * rom t_order_mt2 where total_amount>toDecimal32(900.,2)'</span>;</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-align:center;"><img alt="" height="308" src="https://img-blog.csdnimg.cn/f7a4044976474546a845410c10bdf7d0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="1200"></p> <p>         主要是針對數據量大，有大量重復，稀疏索引不明顯，加二級索引才有效果。并且可以看到</p> <p>下面的跳疏索引。</p> <p style="text-align:center;"><img alt="" height="305" src="https://img-blog.csdnimg.cn/63612407972f47a493ca425310f73bb0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_14,color_FFFFFF,t_70,g_se,x_16" width="463"></p> <h1><a name="t1"></a>2、導出數據</h1> <p>        <a href="https://clickhouse.com/docs/en/interfaces/formats/" title="Input and Output Formats | ClickHouse Documentation">Input and Output Formats | ClickHouse Documentation</a></p> <h1><a name="t2"></a>3、副本(Replicated)與分片(Share)</h1> <h2><a name="t3"></a>3.1、副本 </h2> <p><img alt="" height="392" src="https://img-blog.csdnimg.cn/5da0593695cd48f0a7f9662b8b4fb6a4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="999">        副本只能同步數據，不能同步表結構，所以我們需要在每臺服務器上自己手動建表。建表語句如下：</p> <pre data-index="3"><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> mydb.test_replicated( </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` UInt32,</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"> `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="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE <span class="hljs-operator">=</span> ReplicatedMergeTree(<span class="hljs-string">'/clickhouse/tables/{shard}/table_name'</span>, <span class="hljs-string">'{replica}'</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">PARTITION</span> <span class="hljs-keyword">BY</span> EventTime <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>        <strong>第一個參數shard：</strong>當前本地復制表實例所屬的分片服務名稱。</p> <p>        <strong>第二個參數replica：</strong>當前這張表所屬的副本名稱，一般用replica1、replica2表示。如果第一個參數相同，當前第二個參數需要不同。用以區分當前副本與其他副本。</p> <p>        在hadoop1和hadoop2上面分別執行下面的建表語句：</p> <pre data-index="4" 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-comment">-- 在hadoop1上執行</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> <span class="hljs-keyword">table</span> t_order_rep(</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"> id UInt32,</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"> sku_id String,</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"> total_amount <span class="hljs-type">Decimal</span>(<span class="hljs-number">16</span>,<span class="hljs-number">2</span>),</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"> create_time Datetime</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">)engine <span class="hljs-operator">=</span> ReplicatedMergeTree(<span class="hljs-string">'/clickhouse/table/01/t_order_rep'</span>,<span class="hljs-string">'rep1'</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"> <span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> toYYYYMMDD(create_time)</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">primary</span> key(id)</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,sku_id);</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"> </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"><span class="hljs-comment">-- 在hadoop2上執行</span></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"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> t_order_rep(</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"> id UInt32,</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"> sku_id String,</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"> total_amount <span class="hljs-type">Decimal</span>(<span class="hljs-number">16</span>,<span class="hljs-number">2</span>),</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"> create_time Datetime</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">)engine <span class="hljs-operator">=</span> ReplicatedMergeTree(<span class="hljs-string">'/clickhouse/table/01/t_order_rep'</span>,<span class="hljs-string">'rep2'</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-keyword">partition</span> <span class="hljs-keyword">by</span> toYYYYMMDD(create_time)</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-keyword">primary</span> key(id)</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-keyword">order</span> <span class="hljs-keyword">by</span>(id,sku_id);</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>        當建完表之后，可以在zookpper上查看到以下信息，在zookeeper上顯示有2個副本。</p> <p style="text-align:center;"><img alt="" height="81" src="https://img-blog.csdnimg.cn/a7c2b79714da4802840feadec19df2ce.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="898"></p> <p>         執行插入語句：</p> <pre data-index="5"><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">insert</span> <span class="hljs-keyword">into</span> t_order_rep <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="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">(<span class="hljs-number">101</span>,<span class="hljs-string">'sku_001'</span>,<span class="hljs-number">1000.00</span>,<span class="hljs-string">'2020-06-01 12:00:00'</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">(<span class="hljs-number">102</span>,<span class="hljs-string">'sku_002'</span>,<span class="hljs-number">2000.00</span>,<span class="hljs-string">'2020-06-01 12:00:00'</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-number">103</span>,<span class="hljs-string">'sku_004'</span>,<span class="hljs-number">2500.00</span>,<span class="hljs-string">'2020-06-01 12:00:00'</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-number">104</span>,<span class="hljs-string">'sku_002'</span>,<span class="hljs-number">2000.00</span>,<span class="hljs-string">'2020-06-01 12:00:00'</span>),</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-number">105</span>,<span class="hljs-string">'sku_003'</span>,<span class="hljs-number">600.00</span>,<span class="hljs-string">'2020-06-02 12:00:00'</span>);</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>        當在一臺服務器上插入語句時候，會在另外一臺服務上查詢到。即互為副本。</p> <h2><a name="t4"></a>3.2、分片集群</h2> <p>        副本雖然能夠提高數據的可用性，降低丟失風險，但是每臺服務器實際上必須容納全量的數據，對數據的橫向擴展沒有解決。</p> <p>        要解決數據水平切分的問題，需要引入分片的概念。通過分片把一份完整的數據進行切分，不同的分片分不到不同的幾點上，再通過Distributed表引擎把數據拼接起來一同使用。</p> <p>        <span style="color:#fe2c24;">Distributed表引擎本身不存儲數據</span>，優點類似MyCat至于MySql，成為一種中間件，通過分布式邏輯表來寫入、分發、路由來操作多態節點不同分片的分布式數據。</p> <p>        <strong>注意：ClickHouse的集群是表級別的，實際企業中，大部分做了高可用，但是沒有用分片，避免降低查詢性能以及操作集群的復雜性。</strong></p> <h3><a name="t5"></a>3.2.1、集群寫入流程（3分片2副本共6個節點）</h3> <p>        兩種顏色的虛線標記為是否把副本同步打開。生產上主要使用internal_replication=true</p> <p style="text-align:center;"><img alt="" height="342" src="https://img-blog.csdnimg.cn/d0b09b804a624698b300b42b8a1f6ebf.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="638"></p> <h3><a name="t6"></a>3.2.2、集群讀取流程（3分片2副本共6個節點）</h3> <p style="text-align:center;"><img alt="" height="347" src="https://img-blog.csdnimg.cn/bd68c0bff4524ceab831914eb04d45e9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="649"></p> <h2><a name="t7"></a>3.3、實際操作</h2> <p>        1、建本地表語句，由于前面有指定以下配置：</p> <pre data-index="6"><code class="language-XML 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-tag"><<span class="hljs-name">macros</span>></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-tag"><<span class="hljs-name">shard</span>></span>01<span class="hljs-tag"></<span class="hljs-name">shard</span>></span> <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="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> <span class="hljs-tag"><<span class="hljs-name">replica</span>></span>rep_1_1<span class="hljs-tag"></<span class="hljs-name">replica</span>></span> <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-tag"></<span class="hljs-name">macros</span>></span></div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>        可直接在建表語句中使用：</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> <span class="hljs-keyword">table</span> st_order_mt <span class="hljs-keyword">on</span> cluster pertest_3shards_2replicas(</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 UInt32,</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"> sku_id 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"> total_amount <span class="hljs-type">Decimal</span>(<span class="hljs-number">16</span>,<span class="hljs-number">2</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"> create_time Datetime</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> ReplicatedMergeTree(<span class="hljs-string">'/clickhouse/tables/{shard}/st_order_mt'</span>,<span class="hljs-string">'{replica}'</span>)</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">partition</span> <span class="hljs-keyword">by</span> toYYYYMMDD(create_time)</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"> <span class="hljs-keyword">primary</span> key(id)</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">order</span> <span class="hljs-keyword">by</span>(id,sku_id);</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>        2、建分布式表：</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> st_order_mt_all2 <span class="hljs-keyword">on</span> cluster pertest_3shards_2replicas(</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 UInt32,</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"> sku_id 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"> total_amount <span class="hljs-type">Decimal</span>(<span class="hljs-number">16</span>,<span class="hljs-number">2</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"> create_time Datetime</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> Distributed(pertest_3shards_2replicas,<span class="hljs-keyword">default</span>,st_order_mt,hiveHash(sku_id));</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p>        參數含義：</p> <p>        Distributed(集群名稱，庫名，本地表名，分片鍵)</p> <p>        分片鍵必須是整型數字，所以用hiveHash函數轉換，也可以rand() </p> <h1><a name="t8"></a>4、備份及恢復</h1> <p>        官網：https://<a href="https://so.csdn.net/so/search?q=clickhouse&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-view="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=clickhouse&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"clickhouse\"}"}" 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>.tech/docs/en/operations/backup/</p> <p>        </p> </div><div data-report-view="{"mod":"1585297308_001","spm":"1001.2101.3001.6548","dest":"https://blog.csdn.net/Yuan_CSDF/article/details/122180089","extend1":"pc","ab":"new"}"><div></div></div> </div>