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>