Distinctive Features | ClickHouse Documentationclickhouse.tech
CLickHouse拥有丰富的输入输出格式，对不同的输入输出格式特性的理解有利于对数据的导入，查询的展示，CLickHouse主要分为7种类型系列的输入输出格式，分别是
1、tabseparated系列格式
2、tskv格式
3、csv系列格式
4、json系列格式
5、parquet格式
6、orc格式
7、其他常用的数据格式
一、TabSeparated、TabSeparatedRaw、TabSeparatedWithNames和TabSeparatedWithNamesAndTypes
1. TabSeparated
数据按行写入，tab制表符分隔。使用严格Unix命令行。
注意：最后一行必须包含换行符。
默认格式。
简写：TSV
数据插入和数据查询时，均可使用。
创建表
CREATE TABLE tsv_demo(srcip String, destip String, time String) ENGINE = TinyLog;
数据导入
clickhouse-client --query "INSERT INTO tsv_demo FORMAT TabSeparated" --max_insert_block_size=100000 < tsv_demo.tsv
Code: 117. DB::Exception:
You have carriage return (r, 0x0D, ASCII 13) at end of first row.
It's like your input data has DOS/Windows style line separators, that are illegal in TabSeparated format. You must transform your file to Unix format.
But if you really need carriage return at end of string value of last column, you need to escape it as r.
这是由于windows使用rn表示回车换行， 在linux中使用n表示换行。 因此，需要将r给去掉。
转换为UNIX格式：
dos2unix tsv_demo.tsv
输出格式：
select * from tsv_demo FORMAT TSV;
2. TabSeparatedRaw
简称：TSVRaw
TabSeparatedRaw格式不会对行数据进行转义， 即不会将换行、制表符等转换为转义字符。
只能在数据查询的时候使用。
zhangsan nanjingtjiangsu 23 From nanjing
lisi hangzhoutzhejiang 32 x41 amazing place
xiaoming hefeitanhui 25 notepad
建表语句：
create table escape_demo(name String, addr String, age UInt8, desc String) ENGINE=TinyLog;
select * from escape_demo FORMAT TSV;
zhangsan nanjingtjiangsu 23 From nanjing
lisi hangzhoutzhejiang 32 A amazing place
xiaoming hefeitanhui 25 notepad
3 rows in set. Elapsed: 0.005 sec.
SELECT *
FROM escape_demo
FORMAT TabSeparatedRaw
zhangsan nanjing jiangsu 23 From nanjing
lisi hangzhou zhejiang 32 A amazing place
xiaoming hefei anhui 25 notepad
3 rows in set. Elapsed: 0.005 sec
3. TabSeparatedWithNames
在查询时，TabSeparatedWithNames格式的第一行显示列的名称
在数据导入时，第一行完全被忽略，不会解析第一行为表头。
简称：TSVWithNames
在数据查询和数据导入均可使用。
数据导入：
clickhouse-client --query="INSERT INTO escape_demo FORMAT TSVWithNames" < escape_demo.tsv
数据查询：
select * from escape_demo FORMAT TSVWithNames;
4. TabSeparatedWithNamesAndTypes
在查询时，TabSeparatedWithNamesAndTypes格式在主数据的前面额外显示两行数据，第一行显示列的名称， 第二行显示列的数据类型。
在数据导入时，前面两行的数据完全被忽略。
简写：TSVWithNamesAndTypes
在数据导入和查询时均可使用。
二、TSKV
TSKV格式不适合有大量小列的输出.
TSKV的效率并不比JSONEachRow差.
TSKV数据查询和数据导入。
不需要保证列的顺序。
支持忽略某些值，这些列使用默认值，例如0和空白行。复杂类型的值必须指定，无法使用默认值。
查看：
select * from escape_demo FORMAT TSKV;
导出：
clickhouse-client --query "select * from escape_demo FORMAT TSKV" > tskv.demo
导入：
clickhouse-client --query "insert into escape_demo FORMAT TSKV" < tskv.demo
三、CSV格式：CSV、CSVWithNames
1. CSV格式：CSV、CSVWithNames
CSV默认的分隔符为逗号，format_csv_delimiter设置自定义的分隔符。
CSV中的双引号使用两个双引号转义。
支持数据的查询和数据导入的。
create table csv_demo(create_date Date, update_time DateTime, desc String) ENGINE=TinyLog;
2014-03-23|2014-03-23 14:10:14|Apache Spark achieves high performance
2014-03-23|2014-03-23 15:10:30|Spark offers over 80 high-level operators
1395990600|1395904200|Learning Apache "Spark" is easy
数据导入：
clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO csv_demo FORMAT CSV" < csv_dmeo.csv
数据查看：
set format_csv_delimiter='|'
select * from csv_demo FORMAT CSV;
2. CSVWithNames
CSVWithNames会打印表头的信息。
支持数据的导入和数据的查看。
select * from csv_demo FORMAT CSVWithNames;
四、JSON系列格式：JSON、JSONCompact和JSONEachRow
1. JSON
JSON格式只支持数据的输出，不支持数据的解析（数据导入）。
create table t_json_demo(id UInt8, prov String) ENGINE=TinyLog;
insert into t_json_demo values(1, 'jiangsu'),
(1, 'jiangsu'),
(2, 'anhui'),
(2, 'anihu'),
(3, 'beijing');
默认情况下， Int64和UInit64的整型使用双引号包裹， 如果要移除双引号， 设置配置参数output_format_json_quote_64bit_integers为0。
rows_before_limit_at_least：只有查询包含LIMIT时才输出， 只有在包含group by的语句中才有意义。当查询没有LIMIT时， 执行结果的最小行数。
2. JSONCompact
JSON格式的数据以对象的方式输出， 而JSONCompact以数组的方式输出。
JSONCompact只支持数据的查看， 不支持数据的导入。
3. JSONEachRow
每行数据以换行符分隔的JSON对象。
支持数据的输出和数据导入。
数据导入：
对象中键值对的顺序可任意排列。
缺失某些字段。
create table UserActivity (PageViews UInt8, UserID String, Duration UInt64, Sign Int8) ENGINE TinyLog;
INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}
缺失值的处理：
CREATE TABLE IF NOT EXISTS example_table
(
x UInt32,
a DEFAULT x + 2
) ENGINE = Memory;
insert into example_table FORMAT JSONEachRow {"x":3, "a":5} {"x":4};
参数：input_format_defaults_for_omitted_fields
如果为0， 则x和a的默认值等于0（即UInt32数据类型的默认值）。
如果为1， 则x的默认值等于0， 但a的默认值等于x+2。
input_format_defaults_for_omitted_fields=1
嵌套结构的数据
CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory;
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]};
{"n": { "s": ["abc", "def"],"i": [1, 23]}}
要将数据作为分层JSON对象插入， 需要设置input_format_import_nested_json=1。
当需要设置input_format_import_nested_json=0时：
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}
Exception on client:
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n
Connecting to 192.168.0.200:9000 as user default.
Connected to ClickHouse server version 20.1.4 revision 54431.
五、Parquet数据格式
支持Parquet格式的导出和导入。
Parquet和ClickHouse类型的匹配关系：
Parquet data type (INSERT)| ClickHouse data type| Parquet data type (SELECT)
UINT8,BOOL UInt8 UINT8
INT8 Int8 INT8
UINT16 UInt16 UINT16
INT16 Int16 INT16
UINT32 UInt32 UINT32
INT32 Int32 INT32
UINT64 UInt64 UINT64
INT64 Int64 INT64
FLOAT,HALF_FLOAT Float32 FLOAT
DOUBLE Float64 DOUBLE
DATE32 Date UINT16
DATE64,TIMESTAMP DateTime UINT32
STRING,BINARY String STRING
— FixedString STRING
DECIMAL Decimal DECIMAL
不支持的Parquet数据类型：DATE32, TIME32, FIXED_SIZE_BINARY, JSON, UUID, ENUM。
注意： ClickHouse表的列名必须与Parquet表的列名一致。
ClickHouse表的列数据类型可以不同于插入的Parquet数据类型。 在插入数据时， ClickHouse根据上表解释数据类型， 然后将数据类型转换为ClickHouse表的列数据类型。
数据的导出：
clickhouse-client --query="SELECT * FROM tsv_demo FORMAT Parquet" > parquet_demo.parquet
数据的导入：
create table parquet_demo (srcip String, destip String, time String) ENGINE=TinyLog;
cat parquet_demo.parquet | clickhouse-client --query="INSERT INTO parquet_demo FORMAT Parquet"
六、ORC
仅支持ORC格式的写入。
ORC和CH数据类型的匹配关系：
ORC data type (INSERT)|ClickHouse data type
UINT8, BOOL UInt8
INT8 Int8
UINT16 UInt16
INT16 Int16
UINT32 UInt32
INT32 Int32
UINT64 UInt64
INT64 Int64
FLOAT, HALF_FLOAT Float32
DOUBLE Float64
DATE32 Date
DATE64, TIMESTAMP DateTime
STRING, BINARY String
DECIMAL Decimal
不支持的ORC数据类型：DATE32, TIME32, FIXED_SIZE_BINARY, JSON, UUID, ENUM。
Note： ClickHouse表的列名必须与ORC表的列名一致。
使用Spark生成ORC文件：
val list = List(
("113.248.234.232", "123.212.22.01", "2018-07-12 14:35:31"),
("115.248.158.231", "154.245.56.23", "2020-07-12 13:26:26"),
("115.248.158.231", "154.245.56.23", "2020-07-12 13:22:13"),
("187.248.135.230", "221.228.112.45", "2019-08-09 13:17:39"),
("187.248.234.232", "221.228.112.24", "2019-08-09 20:51:16"),
("115.248.158.231", "154.245.56.23", "2020-07-12 17:22:56")
)
val rdd = sc.makeRDD(list)
import spark.implicits._
val df = rdd.toDF("srcip", "destip", "time")
df.repartition(1).write.format("orc").mode("append").save("/tmp/orc")
测试表：
create table orc_demo (srcip String, destip String, time DateTime) ENGINE=TinyLog;
数据导入：
cat file.orc | clickhouse-client --query="INSERT INTO orc_demo FORMAT ORC"
七、其他常用的数据格式：
1. Native
数据以二进制数据块的方式进行读写。
数据的导出：
clickhouse-client --query="SELECT * FROM tsv_demo FORMAT Native" > a.native
数据的导入：
clickhouse-client --query="insert into tsv_demo FORMAT Native" < a.native
2. Null
主要用于测试性能。 查询会被处理，并且数据会被传送到客户端，但是什么也不输出。
Null格式只能用于查询， 不能用于数据的导入。
3. Pretty
PrettyCompact：在交互式模式下，默认的数据显示格式。
PrettySpace
4. Values
每行之间使用逗号分隔，列之间也是使用逗号分隔。在括号中打印每一行。
INSERT INTO XX values
INSERT INTO XX FORMAT Values
insert into tsv_demo FORMAT Values ('115.248.158.231', '115.248.158.232', '2020-07-12 17:22:56'), ('115.248.158.231', '115.248.158.232', '2020-07-12 17:22:56');
5. Vertical
数据以垂直的格式进行展示。 G
6. XML
只支持数据的查看。
select * from tsv_demo limit 2 FORMAT XML;