TabSeparated、TabSeparatedRaw、TabSeparatedWithNames和TabSeparatedWithNamesAndTypes
TabSeparated
默认格式，缩写:TSV，换行 、制表符 等符号依然显示为 、
列之间使用tab制表符分隔，数据按行写入，每行结尾必须有换行符
可用于查询和插入
CREATE TABLE tsv_demo(srcip String, destip String, time String) ENGINE = TinyLog;
clickhouse-client --query="INSERT INTO test.tsv_demo FORMAT TabSeparated" --max_insert_block_size=100000 < tab.txt
# cat tab.txt 127.0.0.1 192.168.1.11 2020-08-24 17:43:00 192.168.1.100 192.168.1.101 2020-08-24 17:44:00
[root@ch2 tmp]# clickhouse-client ClickHouse client version 20.3.9.70 (official build). Connecting to localhost:9300 as user default. Connected to ClickHouse server version 20.3.9 revision 54433. ch2 :) select * from test.tsv_demo; SELECT * FROM test.tsv_demo ┌─srcip─────────┬─destip────────┬─time────────────────┐ │ 127.0.0.1 │ 192.168.1.11 │ 2020-08-24 17:43:00 │ │ 192.168.1.100 │ 192.168.1.101 │ 2020-08-24 17:44:00 │ └───────────────┴───────────────┴─────────────────────┘ 2 rows in set. Elapsed: 0.005 sec. ch2 :) exit Bye. [root@ch2 tmp]# myc Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 20.3.9.70-ClickHouse Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select * from test.tsv_demo; +---------------+---------------+---------------------+ | srcip | destip | time | +---------------+---------------+---------------------+ | 127.0.0.1 | 192.168.1.11 | 2020-08-24 17:43:00 | | 192.168.1.100 | 192.168.1.101 | 2020-08-24 17:44:00 | +---------------+---------------+---------------------+ 2 rows in set (0.00 sec) Read 2 rows, 139.00 B in 0.001 sec., 1790 rows/sec., 121.53 KiB/sec.
TabSeparated可以缩写为TSV
clickhouse-client --query="INSERT INTO test.tsv_demo FORMAT TSV" --max_insert_block_size=100000 < tab.txt
ch2 :) select * from test.tsv_demo format TSV; SELECT * FROM test.tsv_demo FORMAT TSV 127.0.0.1 192.168.1.11 2020-08-24 17:43:00 192.168.1.100 192.168.1.101 2020-08-24 17:44:00 127.0.0.1 192.168.1.11 2020-08-24 17:43:00 192.168.1.100 192.168.1.101 2020-08-24 17:44:00
TabSeparatedRaw
TabSeparatedRaw格式可以解析制表、转行符，缩写TSVRaw，TSV不解析
只能在数据查询的时候使用
查询时如果列值中包括 ，则默认为该格式，即select * from a 的效果等价于 select * from a format TSVRaw;
建表语句：
create table escape_demo(name String, addr String, age UInt8, desc String) ENGINE=TinyLog;
# cat rw.txt
xiaomi nanjing jiangsu 23 From nanjing
xiaohong zheng zhou 23 x41 gril
clickhouse-client --query="insert into test.escape_demo format TSV" < rw.txt
ch2 :) select * from escape_demo; SELECT * FROM escape_demo ┌─name─────┬─addr───────────────┬─age─┬─desc─────────┐ │ xiaomi │ nanjing jiangsu │ 23 │ From nanjing │ │ xiaohong │ zheng zhou │ 23 │ A gril │ └──────────┴────────────────────┴─────┴──────────────┘
ch2 :) select * from escape_demo format TSVRaw; SELECT * FROM escape_demo FORMAT TSVRaw xiaomi nanjing jiangsu 23 From nanjing xiaohong zheng zhou 23 A gril 2 rows in set. Elapsed: 0.005 sec. ch2 :) select * from escape_demo format TSV; SELECT * FROM escape_demo FORMAT TSV xiaomi nanjing jiangsu 23 From nanjing xiaohong zheng zhou 23 A gril 2 rows in set. Elapsed: 0.004 sec.
TabSeparatedWithNames
在查询时，TabSeparatedWithNames格式的第一行显示列的名称
在数据导入时，第一行完全被忽略，不会解析第一行为表头。
简称：TSVWithNames
在数据查询和数据导入均可使用。
ch2 :) select * from escape_demo format TSVWithNames; SELECT * FROM escape_demo FORMAT TSVWithNames name addr age desc xiaomi nanjing jiangsu 23 From nanjing xiaohong zheng zhou 23 A gril
数据导入：
clickhouse-client --query="INSERT INTO escape_demo FORMAT TSVWithNames" < rw.txt
ch2 :) select * from escape_demo; SELECT * FROM escape_demo ┌─name─────┬─addr───────────────┬─age─┬─desc─────────┐ │ xiaomi │ nanjing jiangsu │ 23 │ From nanjing │ │ xiaohong │ zheng zhou │ 23 │ A gril │ │ xiaohong │ zheng zhou │ 23 │ A gril │ └──────────┴────────────────────┴─────┴──────────────┘
rw.txt文件有两行数据，再次导入应该有四行，但使用该格式会认为rw.txt文件的第一行是列的名称，被忽略
TabSeparatedWithNamesAndTypes
在查询时，TabSeparatedWithNamesAndTypes格式在主数据的前面额外显示两行数据，第一行显示列的名称， 第二行显示列的数据类型。
在数据导入时，前面两行的数据完全被忽略。
简写：TSVWithNamesAndTypes
在数据导入和查询时均可使用。
ch2 :) select * from escape_demo format TSVWithNamesAndTypes; SELECT * FROM escape_demo FORMAT TSVWithNamesAndTypes name addr age desc String String UInt8 String xiaomi nanjing jiangsu 23 From nanjing xiaohong zheng zhou 23 A gril xiaohong zheng zhou 23 A gril
导入时前两行被忽略 ，rw.txt只有两行，所以导入后数据无变化
[root@ch2 tmp]# clickhouse-client --query="INSERT INTO escape_demo FORMAT TSVWithNamesAndTypes" < rw.txt [root@ch2 tmp]# clickhouse-client ClickHouse client version 20.3.9.70 (official build). Connecting to localhost:9300 as user default. Connected to ClickHouse server version 20.3.9 revision 54433. ch2 :) select * from escape_demo; SELECT * FROM escape_demo ┌─name─────┬─addr───────────────┬─age─┬─desc─────────┐ │ xiaomi │ nanjing jiangsu │ 23 │ From nanjing │ │ xiaohong │ zheng zhou │ 23 │ A gril │ │ xiaohong │ zheng zhou │ 23 │ A gril │ └──────────┴────────────────────┴─────┴──────────────┘