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;