ClickHouse（五）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 id="articleContentId"><a name="t0"></a>1.<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>整合MySQL</h1> <h2><a name="t1"></a>1.1.連接創建</h2> <p style="text-indent:33px;">MySQL引擎用于將遠程的MySQL服務器中的表映射到ClickHouse中，并允許您對表進行<a href="https://so.csdn.net/so/search?q=INSERT&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=INSERT&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"INSERT\"}"}" data-tit="INSERT" data-pretit="insert">INSERT</a>和SELECT查詢，以方便您在ClickHouse與MySQL之間進行數據交換。ClickHouse的MySQL引擎可以對存儲在遠程MySQL服務器上的數據執行SELECT查詢。這個模式類似于Hive的外部表。</p> <p style="text-indent:33px;">官網鏈接：https://clickhouse.tech/docs/en/engines/table-engines/integrations/mysql/</p> <p style="text-indent:33px;">MySQL引擎：https://clickhouse.tech/docs/zh/engines/database-engines/mysql/</p> <p style="text-indent:33px;">語法示例：</p> <pre data-index="0"><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>] db_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">ENGINE <span class="hljs-operator">=</span> MySQL(<span class="hljs-string">'host:port'</span>, [<span class="hljs-string">'database'</span> <span class="hljs-operator">|</span> database], <span class="hljs-string">'user'</span>, <span class="hljs-string">'password'</span>)</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <pre data-index="1"><code class="language-sql hljs"><ol class="hljs-ln" style="width:1001px"><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] [TTL 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] [TTL 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> MySQL(<span class="hljs-string">'host:port'</span>, <span class="hljs-string">'database'</span>, <span class="hljs-string">'table'</span>, <span class="hljs-string">'user'</span>, <span class="hljs-string">'password'</span> [,replace_query,<span class="hljs-string">'on_duplicate_clause'</span>]);</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <ul><li>host:port — MySQL 服務器地址。</li><li>database — 數據庫的名稱。</li><li>table — 表名稱。</li><li>user — 數據庫用戶。</li><li>password — 用戶密碼。</li><li>replace_query — 將 INSERT INTO 查詢是否替換為 REPLACE INTO 的標志。如果 replace_query=1，則替換查詢</li><li>'on_duplicate_clause' — 將 ON DUPLICATE KEY UPDATE 'on_duplicate_clause' 表達式添加到 INSERT 查詢語句中。例如：impression = VALUES(impression) + impression。如果需要指定 'on_duplicate_clause'，則需要設置 replace_query=0。如果同時設置 replace_query = 1 和 'on_duplicate_clause'，則會拋出異常。</li></ul> <p style="text-indent:33px;">注意：</p> <p style="text-indent:33px;">1、此時，簡單的 WHERE 子句（例如 =, !=, >, >=, <, <=）是在 MySQL 服務器上執行。</p> <p style="text-indent:33px;">2、其余條件以及 LIMIT 采樣約束語句僅在對MySQL的查詢完成后才在 ClickHouse中執行。</p> <p style="text-indent:33px;">3、ClickHouse引擎不支持Nullable數據類型，因此，當從MySQL表中讀取數據時，NULL將轉換為指定列類型的默認值（通常為0或空字符串）。</p> <h2><a name="t2"></a>1.2.映射方式</h2> <h3><a name="t3"></a>1.2.1.庫的映射</h3> <p style="text-indent:33px;">如果這個庫里面需要有大量的表需要映射到clickhouse做分析，可以不用移動數據，直接映射就可以執行分析（不推薦使用）</p> <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-comment">-- 關聯MySQL庫：</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">drop</span> database if <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="3"></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 ENGINE <span class="hljs-operator">=</span> MySQL(<span class="hljs-string">'hadoop2:3306'</span>, <span class="hljs-string">'mydb'</span>,<span class="hljs-string">'root'</span>, <span class="hljs-string">'123456'</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-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">show</span> databases;</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">show</span> tables <span class="hljs-keyword">from</span> mydb;</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">-- 查詢數據：</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">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> mydb.testck;</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <h3><a name="t4"></a>1.2.2.表的映射</h3> <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">drop</span> <span class="hljs-keyword">table</span> if <span class="hljs-keyword">exists</span> testck;</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> mydb.testck</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"> id <span class="hljs-type">int</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"> name 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">) ENGINE <span class="hljs-operator">=</span> MySQL(<span class="hljs-string">'hadoop2:3306'</span>, <span class="hljs-string">'mydb'</span>, <span class="hljs-string">'testck'</span>, <span class="hljs-string">'root'</span>, <span class="hljs-string">'123456'</span>);</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-indent:33px;"><span style="color:#f33b45;"><strong>注意：當mysql或者ClickHouse數據有修改時會同步到另一端。當向ck中插入數據有主鍵重復也會直接報錯。</strong></span></p> <h3><a name="t5"></a>1.2.3.IS語法和CTAS語法</h3> <p style="text-indent:33px;">IS語法：-- insert into ... select ... 語法</p> <pre data-index="4"><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">--數據遷移：從mysql中，把數據弄到clickhouse中</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">insert</span> <span class="hljs-keyword">into</span> mydb.testck2 <span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> mysql(<span class="hljs-string">'hadoop2:3306'</span>, <span class="hljs-string">'mydb'</span>,<span class="hljs-string">'testck2'</span>, <span class="hljs-string">'root'</span>, <span class="hljs-string">'123456'</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-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> mydb.testck2; <span class="hljs-operator">/</span><span class="hljs-operator">/</span>從clickhouse中查詢出來的</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-indent:33px;">CTAS語法：-- create table ... as select ... 語法</p> <pre data-index="5"><code class="language-sql hljs"><ol class="hljs-ln" style="width:1086px"><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">drop</span> <span class="hljs-keyword">table</span> if <span class="hljs-keyword">exists</span> mydb.testck3;</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> if <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> mydb.testck3 engine <span class="hljs-operator">=</span> Log <span class="hljs-keyword">as</span> <span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> MySQL(<span class="hljs-string">'hadoop2:3306'</span>, <span class="hljs-string">'mytable'</span>,<span class="hljs-string">'root'</span>, <span class="hljs-string">'123456'</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-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> mydb.testck3;</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-indent:33px;"><span style="color:#f33b45;"><strong>注意：這樣的方法不會修改原始數據，但對mysql修改的數據也不會實時同步。</strong></span></p> <h1 style="text-indent:0px;"><a name="t6"></a>2.ClickHouse整合HDFS</h1> <p style="text-indent:33px;">從ClickHouse18.16.0版本開始支持從HDFS讀文件，在19.1.6版本對HDFS訪問功能進行了增強，支持讀和寫，在19.4版本以后開始支持Parquet格式。</p> <p style="text-indent:33px;">先在HDFS上準備一個數據文件：student.csv CSV格式的數據</p> <p style="text-indent:33px;">數據如下：</p> <blockquote> <p style="text-indent:0;">95002,劉晨,女,19,IS<br> 95017,王風娟,女,18,IS<br> 95018,王一,女,19,IS<br> 95013,馮偉,男,21,CS<br> 95014,王小麗,女,19,CS<br> 95019,邢小麗,女,19,IS<br> 95020,趙錢,男,21,IS<br> 95003,王敏,女,22,MA<br> 95004,張立,男,19,IS<br> 95012,孫花,女,20,CS<br> 95010,孔小濤,男,19,CS<br> 95005,劉剛,男,18,MA<br> 95006,孫慶,男,23,CS<br> 95007,易思玲,女,19,MA<br> 95008,李娜,女,18,CS<br> 95021,周二,男,17,MA<br> 95022,鄭明,男,20,MA<br> 95001,李勇,男,20,CS<br> 95011,包小柏,男,18,MA<br> 95009,夢圓圓,女,18,MA<br> 95015,王君,男,18,MA<br> 95016,錢國,男,21,MA</p> </blockquote> <p style="text-indent:33px;">上傳到HDFS：hadoop fs -put student.csv /clickhouse_data/student/</p> <h2 style="text-indent:0px;"><a name="t7"></a>2.1.從HDFS讀取數據</h2> <p style="text-indent:33px;">從HDFS上讀取數據類似于將HDFS作為外部存儲，然后去拉取HDFS上的數據。所以這種模式，肯定要比直接從 ClickHouse 中讀取數據要慢的多。</p> <p style="text-indent:33px;">ClickHouse 建表語句：</p> <pre data-index="6"><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"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> if <span class="hljs-keyword">exists</span> mydb.testck4_csv;</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> if <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> mydb.testck4_csv(</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 Int8,</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"> name 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"> sex 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"> age Int8,</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"> department 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">) Engine <span class="hljs-operator">=</span> HDFS(<span class="hljs-string">'hdfs://hadoop1:9000/stu*.csv'</span>,<span class="hljs-string">'CSV'</span>);</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-indent:33px;">注意：</p> <ol><li style="text-indent:33px;"> <p style="text-indent:0;">支持CSV, TSV, Parquet 等格式，注意CSV是大寫的！</p> </li><li style="text-indent:33px;"> <p style="text-indent:0;">執行并行讀寫操作（Reads and writes can be parallel）</p> </li><li style="text-indent:33px;"> <p style="text-indent:0;">字符串中的是否有引號，都能被自動解析，有引號也行，沒有引號也行。如果需要關聯多個文件，ClickHouse雖然不支持直接關聯文件夾，但是對于文件路徑還是提供給了</p> </li><li style="text-indent:33px;"> <p style="text-indent:0;">多個支持，具體可以參照官網：https://clickhouse.tech/docs/en/engines/table-engines/integrations/hdfs/</p> </li><li style="text-indent:33px;"> <p style="text-indent:0;">支持虛擬列 _path 和 _file，分別代表文件路徑，和文件名。</p> </li><li style="text-indent:33px;"> <p style="text-indent:0;">不支持的操作：alter 和 select ... sample 語法， Indexes 語法，和 Replication 操作</p> </li></ol> <h2 style="text-indent:0px;"><a name="t8"></a>2.2.從HDFS導入數據</h2> <p style="text-indent:33px;">先在ClickHouse中創建一張表testck5_csv:</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> 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"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> if <span class="hljs-keyword">exists</span> mydb.testck5_csv;</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> if <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> testck5_csv(</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 Int8,</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"> name 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"> sex 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"> age Int8,</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"> department 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">) engine <span class="hljs-operator">=</span> TinyLog;</div></div></li></ol></code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-indent:33px;">從 HDFS 上導入數據到 ClickHouse：</p> <pre data-index="8"><code class="language-sql hljs"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.testck5_csv <span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> hdfs(<span class="hljs-string">'hdfs://hadoop1:9000/student.csv'</span>, <span class="hljs-string">'CSV'</span>, <span class="hljs-string">'id Int8, name String, sex String, age Int8, department String'</span>);</code><div class="hljs-button {2}" data-title="復制" onclick="hljs.copyCode(event)"></div></pre> <p style="text-indent:33px;">詳細語法可以參考官網：https://clickhouse.tech/docs/zh/sql-reference/table-functions/hdfs/</p> <p style="text-indent:33px;"> </p> <p style="text-indent:33px;"> </p> <p style="text-indent:33px;"> </p> </div><div data-report-view="{"mod":"1585297308_001","spm":"1001.2101.3001.6548","dest":"https://blog.csdn.net/Yuan_CSDF/article/details/117914028","extend1":"pc","ab":"new"}"><div></div></div> </div>