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>