Clickhouse常用的數據格式實戰案例

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

一.Clickhouse的數據格式概述
二.TabSeparated(制表符)系列實戰案例
1.TabSeparated案例實戰
- 2.TabSeparatedRaw案例實戰
三.TSKV實戰案例
四.CSV系列實戰案例
五.JSON系列案例實戰
六.Native二進制數據實戰案例
- 1.導出數據
- 2.導入數據
七.Null實戰案例
- 1.正常查詢
- 2.使用NULL格式查詢
八.Pretty系列格式實戰案例
九.其他數據格式測試案例

一.Clickhouse的數據格式概述

1.Clickhouse支持的格式

格式	輸入	輸出
TabSeparated	?	?
TabSeparatedRaw	?	?
TabSeparatedWithNames	?	?
TabSeparatedWithNamesAndTypes	?	?
TabSeparatedRawWithNames	?	?
TabSeparatedRawWithNamesAndTypes	?	?
Template	?	?
TemplateIgnoreSpaces	?	?
CSV	?	?
CSVWithNames	?	?
CSVWithNamesAndTypes	?	?
CustomSeparated	?	?
CustomSeparatedWithNames	?	?
CustomSeparatedWithNamesAndTypes	?	?
SQLInsert	?	?
Values	?	?
Vertical	?	?
JSON	?	?
JSONAsString	?	?
JSONAsObject	?	?
JSONStrings	?	?
JSONColumns	?	?
JSONColumnsWithMetadata	?	?
JSONCompact	?	?
JSONCompactStrings	?	?
JSONCompactColumns	?	?
JSONEachRow	?	?
PrettyJSONEachRow	?	?
JSONEachRowWithProgress	?	?
JSONStringsEachRow	?	?
JSONStringsEachRowWithProgress	?	?
JSONCompactEachRow	?	?
JSONCompactEachRowWithNames	?	?
JSONCompactEachRowWithNamesAndTypes	?	?
JSONCompactEachRowWithProgress	?	?
JSONCompactStringsEachRow	?	?
JSONCompactStringsEachRowWithNames	?	?
JSONCompactStringsEachRowWithNamesAndTypes	?	?
JSONCompactStringsEachRowWithProgress	?	?
JSONObjectEachRow	?	?
BSONEachRow	?	?
TSKV	?	?
Pretty	?	?
PrettyNoEscapes	?	?
PrettyMonoBlock	?	?
PrettyNoEscapesMonoBlock	?	?
PrettyCompact	?	?
PrettyCompactNoEscapes	?	?
PrettyCompactMonoBlock	?	?
PrettyCompactNoEscapesMonoBlock	?	?
PrettySpace	?	?
PrettySpaceNoEscapes	?	?
PrettySpaceMonoBlock	?	?
PrettySpaceNoEscapesMonoBlock	?	?
Prometheus	?	?
Protobuf	?	?
ProtobufSingle	?	?
ProtobufList	?	?
Avro	?	?
AvroConfluent	?	?
Parquet	?	?
ParquetMetadata	?	?
Arrow	?	?
ArrowStream	?	?
ORC	?	?
One	?	?
Npy	?	?
RowBinary	?	?
RowBinaryWithNames	?	?
RowBinaryWithNamesAndTypes	?	?
RowBinaryWithDefaults	?	?
Native	?	?
Null	?	?
XML	?	?
CapnProto	?	?
LineAsString	?	?
Regexp	?	?
RawBLOB	?	?
MsgPack	?	?
MySQLDump	?	?
DWARF	?	?
Markdown	?	?
Form	?	?

ClickHouse 支持大多數已知的文本和二進制數據格式。這使得幾乎可以輕松地將其集成到任何工作數據管道中，從而利用ClickHouse的優勢。

Clickhouse支持的輸入和輸出格式如上表所示。

參考鏈接:
	https://clickhouse.com/docs/zh/interfaces/formats

2.格式概述

2.1 TabSeparated(制表符)系列格式

- TabSeparated:
	數據按行寫入，tab制表符分割，使用嚴格unix命令行。
	最后一行必須包含換行符，是默認格式，簡寫為"TSV"。
	數據插入和數據查詢是，均可以使用。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/TabSeparated
		
		
- TabSeparatedRaw:
	只能在數據查詢的時候作為輸出格式使用，寫入數據依然使用TSV，TabSeparatedRaw可以簡寫為TSVRaw。
	TSVRaw和TSV沒有太大區別，主要是TSVRaw格式不會對數據進行轉義，即不會將換行，制表符等轉換為轉義字符。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/TabSeparatedRaw


- TabSeparatedWithNames:
	TabSeparatedWithNames可以簡寫為TSVWithNames，在數據查詢和數據導入均可使用。
	在查詢但是時候，TSVWithNames的第一行會顯示列的名稱，而在導入數據的時候，第一行完全被忽略，不會解析第一行為表頭。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/TabSeparatedRawWithNames
	
	
- TabSeparatedWitchNamesAndTypes:
	在數據源導入和查詢均可使用，在查詢時會額外顯兩行數據，第一行顯示列的名稱，第二行顯示列的數據類型。
	在導入數據是，前兩行的數據完全被忽略。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/TabSeparatedWithNamesAndTypes
		
		
		
溫馨提示:
	經測試，TabSeparated和TabSeparatedRaw在新版本可以正常使用，但是TabSeparatedWithNames和TabSeparatedWitchNamesAndTypes在新版本官方有改動，導致導入數據后，可能出現'0'作為占位，因此新版本我并不推薦大家使用后兩者類型，或者使用20以前的版本進行測試。

2.2 TSKV

以KV的形式顯示查詢到的數據，key作為當前列的名稱，value為查詢到的數據，支持數據的導出和導入。

TSKV不適合有大量小列的輸出，會影響性能。TSKV的效率并不比JSONEachRow差。

參考鏈接:
	https://clickhouse.com/docs/zh/interfaces/formats/TSKV

2.3 CSV系列格式

CSV系列有CSV，CSVwithNames，CSVWithNamesAndTypes格式。

CSV格式:
	默認的分隔符好為","(即逗號)，但是可以通過參數自定義分隔符。
	如果數據中有雙引號需要寫兩個雙引號轉義。
	支持數據的查詢和導入。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/CSV
		
CSVwithNames:
	CSVwithNames會打印表頭的信息，支持數據的導入和查看。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/CSVWithNames
		
		
CSVWithNamesAndTypes:
	相比于CSVwithNames會多輸出一行字符串信息。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/CSVWithNamesAndTypes

2.4 JSON系列

JSON格式只支持查詢，不支持數據的導入，JSON以對象的方式輸出數據。

JSON:
	以JSON格式輸出數據。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/JSON
	
JSONCompact:
	以數組的方式輸出數據。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/JSONCompact
		
JSONEachRow:
	最長的格式，每行數據以換行符分隔的JSON對象，支持數據的輸入和數據的導入。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/JSONEachRow

2.5 ORC格式

ORC 數據類型 (`INSERT`)	ClickHouse 數據類型	ORC 數據類型 (`SELECT`)
`Boolean`	UInt8	`Boolean`
`Tinyint`	Int8/UInt8/Enum8	`Tinyint`
`Smallint`	Int16/UInt16/Enum16	`Smallint`
`Int`	Int32/UInt32	`Int`
`Bigint`	Int64/UInt32	`Bigint`
`Float`	Float32	`Float`
`Double`	Float64	`Double`
`Decimal`	Decimal	`Decimal`
`Date`	Date32	`Date`
`Timestamp`	DateTime64	`Timestamp`
`String`, `Char`, `Varchar`, `Binary`	String	`Binary`
`List`	Array	`List`
`Struct`	Tuple	`Struct`
`Map`	Map	`Map`
`Int`	IPv4	`Int`
`Binary`	IPv6	`Binary`
`Binary`	Int128/UInt128/Int256/UInt256	`Binary`
`Binary`	Decimal256	`Binary`

ORC格式在Hadoop生態系統中普遍存在的列式存儲格式。

僅支持ORC格式的寫入(不支持導出ORC格式)，Clickhouse表的列名必須與ORC表的列名一致。

ORC和Clickhouse類型的匹配關系如上表所示。

參考鏈接:
	https://clickhouse.com/docs/zh/interfaces/formats/ORC

2.6 Native格式

Native 格式是 ClickHouse 最高效的格式，因為它真正是“列式”的，因為它不會將列轉換為行。

在此格式中，數據以二進制格式通過 blocks 進行寫入和讀取。對于每個塊，記錄了行數、列數、列名和類型，以及塊中列的部分，依次記錄。

這是在服務器之間進行交互時、使用命令行客戶端時以及 C++ 客戶端時所使用的格式。

參考鏈接:
	https://clickhouse.com/docs/zh/interfaces/formats/Native

2.7 Null格式

NULL格式主要用于測試查詢性能，查詢會被處理，并且數據會被傳輸到客戶端，但是也什么也不輸出。

Null格式只能用于查詢，不能用于數據導入。

參考鏈接:
	https://clickhouse.com/docs/zh/interfaces/formats/Null

2.8 Pretty系列格式

將數據美化為類似于excel表格的格式，以方便更直觀的查看，只能輸出位pretty格式，不能導入Pretty格式的數據。

Pretty 格式:
	以 Unicode 藝術表格形式輸出數據， 使用 ANSI 轉義序列在終端中顯示顏色。 
	表格的完整網格會被繪制，每行在終端中占用兩行。 
	每個結果塊被輸出為一個單獨的表格。 
	這是為了使塊可以在不緩沖結果的情況下輸出（緩沖將在預計算所有值的可見寬度時是必要的）。
    參考鏈接:
        https://clickhouse.com/docs/zh/interfaces/formats/Pretty
        
        
PrettyCompact
	與 Pretty 格式不同的是，此格式在行之間繪制了網格以顯示表格。 因此，結果更加緊湊。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/PrettyCompact


PrettySpace:
	與 PrettyCompact 格式不同，使用空白（空格字符）來顯示表，而不是網格。
	參考鏈接:
		https://clickhouse.com/docs/zh/interfaces/formats/PrettySpace

2.9 其他數據格式

參考鏈接:
	https://clickhouse.com/docs/zh/interfaces/formats

二.TabSeparated(制表符)系列實戰案例

1.TabSeparated案例實戰

1.1 創建表

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie
node-exporter42 :) CREATE DATABASE IF NOT EXISTS yinzhengjie;
node-exporter42 :) USE yinzhengjie
node-exporter42 :) CREATE TABLE tsv_demo(srcip String,gwip String,destip String,time String)ENGINE = TinyLog;

1.2 導入TabSeparated格式數據

	1.準備測試數據
[root@node-exporter42 ~]# cat ip.txt
10.0.0.91	10.0.0.254	124.126.138.74	2021-03-20 09:00:00
10.0.0.92	10.0.0.254	24.126.138.74	2022-03-20 10:00:00
10.0.0.93	10.0.0.254	23.126.138.74	2023-03-20 11:00:00
10.0.0.231	10.0.0.254	25.126.138.20	2024-03-20 12:00:00
10.0.0.232	10.0.0.254	26.126.138.21	2025-03-20 13:00:00
10.0.0.233	10.0.0.254	27.126.138.22	2026-03-20 14:00:00
[root@node-exporter42 ~]# 

	2.導入數據
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "INSERT INTO tsv_demo FORMAT TabSeparated" --max_insert_block_size=100000 < ip.txt
[root@node-exporter42 ~]#

1.3 查詢并導出TabSeparated格式數據

	1.查詢數據驗證
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie
...
node-exporter42 :) SELECT * FROM tsv_demo;

SELECT *
FROM tsv_demo

Query id: 661bbce8-1fda-444b-9d99-31cf29129c78

   ┌─srcip──────┬─gwip───────┬─destip─────────┬─time────────────────┐
1. │ 10.0.0.91  │ 10.0.0.254 │ 124.126.138.74 │ 2021-03-20 09:00:00 │
2. │ 10.0.0.92  │ 10.0.0.254 │ 24.126.138.74  │ 2022-03-20 10:00:00 │
3. │ 10.0.0.93  │ 10.0.0.254 │ 23.126.138.74  │ 2023-03-20 11:00:00 │
4. │ 10.0.0.231 │ 10.0.0.254 │ 25.126.138.20  │ 2024-03-20 12:00:00 │
5. │ 10.0.0.232 │ 10.0.0.254 │ 26.126.138.21  │ 2025-03-20 13:00:00 │
6. │ 10.0.0.233 │ 10.0.0.254 │ 27.126.138.22  │ 2026-03-20 14:00:00 │
   └────────────┴────────────┴────────────────┴─────────────────────┘

6 rows in set. Elapsed: 0.002 sec. 

node-exporter42 :) 



	2.將查詢的數據以TabSeparated格式導出
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query="SELECT * FROM yinzhengjie.tsv_demo FORMAT TabSeparated" > /tmp/tsv_demo.data
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/tsv_demo.data
10.0.0.91	10.0.0.254	124.126.138.74	2021-03-20 09:00:00
10.0.0.92	10.0.0.254	24.126.138.74	2022-03-20 10:00:00
10.0.0.93	10.0.0.254	23.126.138.74	2023-03-20 11:00:00
10.0.0.231	10.0.0.254	25.126.138.20	2024-03-20 12:00:00
10.0.0.232	10.0.0.254	26.126.138.21	2025-03-20 13:00:00
10.0.0.233	10.0.0.254	27.126.138.22	2026-03-20 14:00:00
[root@node-exporter42 ~]#

2.TabSeparatedRaw案例實戰

2.1 創建表

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie
node-exporter42 :)  CREATE TABLE tsvraw_demo(name String, cs String,year UInt16, desc String) ENGINE=TinyLog;

2.2 導入TabSeparatedRaw格式數據

	1.準備數據
[root@node-exporter42 ~]# cat tsvraw.txt 
yinzhengjie	JasonYin\tBeiJing	2020	ShanXi XiAn
Kubernetes	K8S\tContainerd	2014	\x52 Docker_k3s
Prometheus	minio\rabbitMQ\tKafka	2025	http://www.rzrgm.cn/yinzhengjie
ElasticStack	ElasticSearch\tes	2019	elk
Ceph	mon\mgr	2022	osd\?
[root@node-exporter42 ~]# 

	2.導入TabSeparatedRaw格式數據的格式依舊是以'TSV'的格式進行導入
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "INSERT INTO yinzhengjie.tsvraw_demo FORMAT TSV" --max_insert_block_size=100000 < tsvraw.txt
[root@node-exporter42 ~]#

2.3 查詢并導出TabSeparated格式數據

	1.使用'TSV'和TabSeparatedRaw'格式查詢驗證
node-exporter42 :) SELECT * FROM tsvraw_demo FORMAT TSV;

SELECT *
FROM tsvraw_demo
FORMAT TSV

Query id: d4393d23-d056-4088-b8af-940bd60c3cd4

yinzhengjie	JasonYin\tBeiJing	2020	ShanXi XiAn
Kubernetes	K8S\tContainerd	2014	R Docker_k3s
Prometheus	minio\rabbitMQ\tKafka	2025	http://www.rzrgm.cn/yinzhengjie
ElasticStack	ElasticSearch\tes	2019	elk
Ceph	mon\\mgr	2022	osd\\?

5 rows in set. Elapsed: 0.009 sec. 

node-exporter42 :) 
node-exporter42 :) SELECT * FROM tsvraw_demo FORMAT TSVRAW;

SELECT *
FROM tsvraw_demo
FORMAT TSVRAW

Query id: c1badd32-d89c-4b87-9e35-97f5ad768507

yinzhengjie	JasonYin	BeiJing	2020	ShanXi XiAn
Kubernetes	K8S	Containerd	2014	R Docker_k3s
abbitMQeKafka   2025o   http://www.rzrgm.cn/yinzhengjie
ElasticStack	ElasticSearch	es	2019	elk
Ceph	mon\mgr	2022	osd\?

5 rows in set. Elapsed: 0.004 sec. 

node-exporter42 :) 
node-exporter42 :) SELECT * FROM tsvraw_demo FORMAT TabSeparatedRaw;

SELECT *
FROM tsvraw_demo
FORMAT TabSeparatedRaw

Query id: 787daf31-bbbc-4969-b166-667e6039aeef

yinzhengjie	JasonYin	BeiJing	2020	ShanXi XiAn
Kubernetes	K8S	Containerd	2014	R Docker_k3s
abbitMQeKafka   2025o   http://www.rzrgm.cn/yinzhengjie
ElasticStack	ElasticSearch	es	2019	elk
Ceph	mon\mgr	2022	osd\?

5 rows in set. Elapsed: 0.002 sec. 

node-exporter42 :) 





	2.將查詢的數據以TabSeparated格式導出
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --query="SELECT * FROM yinzhengjie.tsvraw_demo FORMAT TSVRaw" > /tmp/tsvraw_demo.data
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/tsvraw_demo.data
yinzhengjie	JasonYin	BeiJing	2020	ShanXi XiAn
Kubernetes	K8S	Containerd	2014	R Docker_k3s
abbitMQeKafka   2025o   http://www.rzrgm.cn/yinzhengjie
ElasticStack	ElasticSearch	es	2019	elk
Ceph	mon\mgr	2022	osd\?
[root@node-exporter42 ~]#

三.TSKV實戰案例

1.查詢數據

node-exporter42 :) SELECT * FROM tsvraw_demo FORMAT TSKV;

SELECT *
FROM tsvraw_demo
FORMAT TSKV

Query id: 50edced2-afcd-4300-aaba-e7a04e298060

name=yinzhengjie	cs=JasonYin\tBeiJing	year=2020	desc=ShanXi XiAn
name=Kubernetes	cs=K8S\tContainerd	year=2014	desc=R Docker_k3s
name=Prometheus	cs=minio\rabbitMQ\tKafka	year=2025	desc=http://www.rzrgm.cn/yinzhengjie
name=ElasticStack	cs=ElasticSearch\tes	year=2019	desc=elk
name=Ceph	cs=mon\\mgr	year=2022	desc=osd\\?

5 rows in set. Elapsed: 0.003 sec. 

node-exporter42 :)

2.導出數據

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM tsvraw_demo FORMAT TSKV"  > /tmp/tskv.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/tskv.demo
name=yinzhengjie	cs=JasonYin\tBeiJing	year=2020	desc=ShanXi XiAn
name=Kubernetes	cs=K8S\tContainerd	year=2014	desc=R Docker_k3s
name=Prometheus	cs=minio\rabbitMQ\tKafka	year=2025	desc=http://www.rzrgm.cn/yinzhengjie
name=ElasticStack	cs=ElasticSearch\tes	year=2019	desc=elk
name=Ceph	cs=mon\\mgr	year=2022	desc=osd\\?
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]#

3.導入數據

	1.創建表
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie
...
node-exporter42 :) CREATE TABLE tskv_demo(name String, cs String,year UInt16, desc String) ENGINE=TinyLog;
 
 
 	2.導入數據
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "INSERT INTO yinzhengjie.tskv_demo FORMAT TSKV"  < /tmp/tskv.demo


	3.測試驗證
node-exporter42 :) SELECT * FROM tskv_demo FORMAT TSKV;

SELECT *
FROM tskv_demo
FORMAT TSKV

Query id: 99070374-f48c-4b64-b862-c3f18dcb893a

name=yinzhengjie	cs=JasonYin\tBeiJing	year=2020	desc=ShanXi XiAn
name=Kubernetes	cs=K8S\tContainerd	year=2014	desc=R Docker_k3s
name=Prometheus	cs=minio\rabbitMQ\tKafka	year=2025	desc=http://www.rzrgm.cn/yinzhengjie
name=ElasticStack	cs=ElasticSearch\tes	year=2019	desc=elk
name=Ceph	cs=mon\\mgr	year=2022	desc=osd\\?

5 rows in set. Elapsed: 0.002 sec. 

node-exporter42 :)

四.CSV系列實戰案例

1.CSV案例

1.1 導入數據

	1.創建表
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie
...
node-exporter42 :) CREATE TABLE csv_demo(ctime Date, mtime DateTime,desc String)ENGINE=TinyLog;

	
	2.準備測試數據
[root@node-exporter42 ~]# cat csv.txt
2010-09-01|2010-09-01 15:30:21|Alibaba Cloud ECS SLB
2014-08-20|2014-08-20 16:30:00|OpenStack KVM
2016-06-30|2016-06-30 13:20:50|Docker Jenkins DNS
2018-11-21|2018-11-21 21:59:58|Kubernetes Containerd Ceph
2020-03-19|2020-03-19 09:00:00|BigData Hadoop Spark Flink
2021-11-20|2021-11-20 15:30:50|Isito Trafik OpenELB Helm
[root@node-exporter42 ~]# 


	3.導入數據（導入數據的時候一定要使用'--format_csv_delimiter'選項指定分隔符）
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "INSERT INTO yinzhengjie.csv_demo FORMAT CSV" --format_csv_delimiter="|" < csv.txt
[root@node-exporter42 ~]#

1.2 查詢數據

	1.以正常方式查看
node-exporter42 :) SELECT * FROM csv_demo;

SELECT *
FROM csv_demo

Query id: 003327d6-f131-4490-8c80-fd180b568c1a

   ┌──────ctime─┬───────────────mtime─┬─desc───────────────────────┐
1. │ 2010-09-01 │ 2010-09-01 15:30:21 │ Alibaba Cloud ECS SLB      │
2. │ 2014-08-20 │ 2014-08-20 16:30:00 │ OpenStack KVM              │
3. │ 2016-06-30 │ 2016-06-30 13:20:50 │ Docker Jenkins DNS         │
4. │ 2018-11-21 │ 2018-11-21 21:59:58 │ Kubernetes Containerd Ceph │
5. │ 2020-03-19 │ 2020-03-19 09:00:00 │ BigData Hadoop Spark Flink │
6. │ 2021-11-20 │ 2021-11-20 15:30:50 │ Isito Trafik OpenELB Helm  │
   └────────────┴─────────────────────┴────────────────────────────┘

6 rows in set. Elapsed: 0.005 sec. 

node-exporter42 :) 

	2.以CSV格式查看
node-exporter42 :) SELECT * FROM csv_demo FORMAT CSV;

SELECT *
FROM csv_demo
FORMAT CSV

Query id: 9757513c-73a9-4187-aefe-2f76d7fe048e

"2010-09-01","2010-09-01 15:30:21","Alibaba Cloud ECS SLB"
"2014-08-20","2014-08-20 16:30:00","OpenStack KVM"
"2016-06-30","2016-06-30 13:20:50","Docker Jenkins DNS"
"2018-11-21","2018-11-21 21:59:58","Kubernetes Containerd Ceph"
"2020-03-19","2020-03-19 09:00:00","BigData Hadoop Spark Flink"
"2021-11-20","2021-11-20 15:30:50","Isito Trafik OpenELB Helm"

6 rows in set. Elapsed: 0.007 sec. 

node-exporter42 :)

1.3.導出數據

	1.以CSV格式導出不指定分隔符，默認使用逗號(",")分隔
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM csv_demo FORMAT CSV"  > /tmp/csv.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/csv.demo
"2010-09-01","2010-09-01 15:30:21","Alibaba Cloud ECS SLB"
"2014-08-20","2014-08-20 16:30:00","OpenStack KVM"
"2016-06-30","2016-06-30 13:20:50","Docker Jenkins DNS"
"2018-11-21","2018-11-21 21:59:58","Kubernetes Containerd Ceph"
"2020-03-19","2020-03-19 09:00:00","BigData Hadoop Spark Flink"
"2021-11-20","2021-11-20 15:30:50","Isito Trafik OpenELB Helm"
[root@node-exporter42 ~]# 


	2.以CSV格式導出指定分隔符
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM csv_demo FORMAT CSV" --format_csv_delimiter="|"  > /tmp/csv.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/csv.demo
"2010-09-01"|"2010-09-01 15:30:21"|"Alibaba Cloud ECS SLB"
"2014-08-20"|"2014-08-20 16:30:00"|"OpenStack KVM"
"2016-06-30"|"2016-06-30 13:20:50"|"Docker Jenkins DNS"
"2018-11-21"|"2018-11-21 21:59:58"|"Kubernetes Containerd Ceph"
"2020-03-19"|"2020-03-19 09:00:00"|"BigData Hadoop Spark Flink"
"2021-11-20"|"2021-11-20 15:30:50"|"Isito Trafik OpenELB Helm"
[root@node-exporter42 ~]#

2.CSVwithNames案例

2.1 查看數據

node-exporter42 :) SELECT * FROM csv_demo FORMAT CSVWithNames;

SELECT *
FROM csv_demo
FORMAT CSVWithNames

Query id: a7f41e3d-31d2-4b57-9898-83d3d67f41cb

"ctime","mtime","desc"
"2010-09-01","2010-09-01 15:30:21","Alibaba Cloud ECS SLB"
"2014-08-20","2014-08-20 16:30:00","OpenStack KVM"
"2016-06-30","2016-06-30 13:20:50","Docker Jenkins DNS"
"2018-11-21","2018-11-21 21:59:58","Kubernetes Containerd Ceph"
"2020-03-19","2020-03-19 09:00:00","BigData Hadoop Spark Flink"
"2021-11-20","2021-11-20 15:30:50","Isito Trafik OpenELB Helm"

6 rows in set. Elapsed: 0.003 sec. 

node-exporter42 :)

2.2 導出數據

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM csv_demo FORMAT CSVWithNames" --format_csv_delimiter="|"  > /tmp/csvWithNames.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/csvWithNames.demo
"ctime"|"mtime"|"desc"
"2010-09-01"|"2010-09-01 15:30:21"|"Alibaba Cloud ECS SLB"
"2014-08-20"|"2014-08-20 16:30:00"|"OpenStack KVM"
"2016-06-30"|"2016-06-30 13:20:50"|"Docker Jenkins DNS"
"2018-11-21"|"2018-11-21 21:59:58"|"Kubernetes Containerd Ceph"
"2020-03-19"|"2020-03-19 09:00:00"|"BigData Hadoop Spark Flink"
"2021-11-20"|"2021-11-20 15:30:50"|"Isito Trafik OpenELB Helm"
[root@node-exporter42 ~]#

3.csvWithNamesAndTypes案例

3.1 查詢數據

node-exporter42 :) SELECT * FROM csv_demo FORMAT CSVWithNamesAndTypes;

SELECT *
FROM csv_demo
FORMAT CSVWithNamesAndTypes

Query id: 13122266-ce1f-4f07-b7fb-ed43b65f3f85

"ctime","mtime","desc"
"Date","DateTime","String"
"2010-09-01","2010-09-01 15:30:21","Alibaba Cloud ECS SLB"
"2014-08-20","2014-08-20 16:30:00","OpenStack KVM"
"2016-06-30","2016-06-30 13:20:50","Docker Jenkins DNS"
"2018-11-21","2018-11-21 21:59:58","Kubernetes Containerd Ceph"
"2020-03-19","2020-03-19 09:00:00","BigData Hadoop Spark Flink"
"2021-11-20","2021-11-20 15:30:50","Isito Trafik OpenELB Helm"

6 rows in set. Elapsed: 0.005 sec. 

node-exporter42 :)

3.2 導出數據

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM csv_demo FORMAT CSVWithNamesAndTypes" --format_csv_delimiter="|"  > /tmp/csvWithNamesAndTypes.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/csvWithNamesAndTypes.demo
"ctime"|"mtime"|"desc"
"Date"|"DateTime"|"String"
"2010-09-01"|"2010-09-01 15:30:21"|"Alibaba Cloud ECS SLB"
"2014-08-20"|"2014-08-20 16:30:00"|"OpenStack KVM"
"2016-06-30"|"2016-06-30 13:20:50"|"Docker Jenkins DNS"
"2018-11-21"|"2018-11-21 21:59:58"|"Kubernetes Containerd Ceph"
"2020-03-19"|"2020-03-19 09:00:00"|"BigData Hadoop Spark Flink"
"2021-11-20"|"2021-11-20 15:30:50"|"Isito Trafik OpenELB Helm"
[root@node-exporter42 ~]#

五.JSON系列案例實戰

1.JSON案例

1.1 準備測試數據

	1.創建表
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie
...
node-exporter42 :) CREATE TABLE json_demo(id UInt8,name String,hobby String)ENGINE=TinyLog;
node-exporter42 :) INSERT INTO json_demo values(1,'孫悟空','紫霞仙子'),(2,'豬八戒','高老莊'),(3,'唐僧','如來佛祖');



	2.查詢數據
node-exporter42 :) SELECT * FROM json_demo;

SELECT *
FROM json_demo

Query id: 80575977-8c06-43e8-80b9-56e4bb2198f1

   ┌─id─┬─name───┬─hobby────┐
1. │  1 │ 孫悟空 │ 紫霞仙子 │
2. │  2 │ 豬八戒 │ 高老莊   │
3. │  3 │ 唐僧   │ 如來佛祖 │
   └────┴────────┴──────────┘

3 rows in set. Elapsed: 0.002 sec. 

node-exporter42 :)

1.2 以JSON格式查詢數據

node-exporter42 :) SELECT * FROM json_demo FORMAT JSON;

SELECT *
FROM json_demo
FORMAT JSON

Query id: 29b85520-bebc-4c26-8f53-1cf9c85ecc29

{
	"meta":
	[
		{
			"name": "id",
			"type": "UInt8"
		},
		{
			"name": "name",
			"type": "String"
		},
		{
			"name": "hobby",
			"type": "String"
		}
	],

	"data":
	[
		{
			"id": 1,
			"name": "孫悟空",
			"hobby": "紫霞仙子"
		},
		{
			"id": 2,
			"name": "豬八戒",
			"hobby": "高老莊"
		},
		{
			"id": 3,
			"name": "唐僧",
			"hobby": "如來佛祖"
		}                                                                                                                                   

	],

	"rows": 3,

	"statistics":
	{
		"elapsed": 0.002140043,
		"rows_read": 3,
		"bytes_read": 114
	}
}

3 rows in set. Elapsed: 0.002 sec. 

node-exporter42 :)

1.3 導出數據

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM json_demo FORMAT JSON"   > /tmp/json.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/json.demo
{
	"meta":
	[
		{
			"name": "id",
			"type": "UInt8"
		},
		{
			"name": "name",
			"type": "String"
		},
		{
			"name": "hobby",
			"type": "String"
		}
	],

	"data":
	[
		{
			"id": 1,
			"name": "孫悟空",
			"hobby": "紫霞仙子"
		},
		{
			"id": 2,
			"name": "豬八戒",
			"hobby": "高老莊"
		},
		{
			"id": 3,
			"name": "唐僧",
			"hobby": "如來佛祖"
		}
	],

	"rows": 3,

	"statistics":
	{
		"elapsed": 0.001674245,
		"rows_read": 3,
		"bytes_read": 114
	}
}
[root@node-exporter42 ~]#

2.JSONCompact案例

2.1 查詢數據

node-exporter42 :) SELECT * FROM json_demo FORMAT JSONCompact;

SELECT *
FROM json_demo
FORMAT JSONCompact

Query id: af2c21cd-d4a7-4989-9800-20b3854ce89b

{
	"meta":
	[
		{
			"name": "id",
			"type": "UInt8"
		},
		{
			"name": "name",
			"type": "String"
		},
		{
			"name": "hobby",
			"type": "String"
		}
	],

	"data":
	[
		[1, "孫悟空", "紫霞仙子"],
		[2, "豬八戒", "高老莊"],
		[3, "唐僧", "如來佛祖"]                                                                                                             

	],

	"rows": 3,

	"statistics":
	{
		"elapsed": 0.003210636,
		"rows_read": 3,
		"bytes_read": 114
	}
}

3 rows in set. Elapsed: 0.003 sec. 

node-exporter42 :)

2.2 導出數據

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM json_demo FORMAT JSONCompact"   > /tmp/jsonCompact.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/jsonCompact.demo
{
	"meta":
	[
		{
			"name": "id",
			"type": "UInt8"
		},
		{
			"name": "name",
			"type": "String"
		},
		{
			"name": "hobby",
			"type": "String"
		}
	],

	"data":
	[
		[1, "孫悟空", "紫霞仙子"],
		[2, "豬八戒", "高老莊"],
		[3, "唐僧", "如來佛祖"]
	],

	"rows": 3,

	"statistics":
	{
		"elapsed": 0.00204104,
		"rows_read": 3,
		"bytes_read": 114
	}
}
[root@node-exporter42 ~]#

3.JSONEachRow案例

3.1 查看數據

node-exporter42 :) SELECT * FROM json_demo FORMAT JSONEachRow;

SELECT *
FROM json_demo
FORMAT JSONEachRow

Query id: 0593e792-5179-44de-95d6-f9781a183f7a

{"id":1,"name":"孫悟空","hobby":"紫霞仙子"}
{"id":2,"name":"豬八戒","hobby":"高老莊"}
{"id":3,"name":"唐僧","hobby":"如來佛祖"}

3 rows in set. Elapsed: 0.007 sec. 

node-exporter42 :)

3.2 導出數據

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM json_demo FORMAT JSONEachRow"   > /tmp/jsonEachRow.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# cat /tmp/jsonEachRow.demo
{"id":1,"name":"孫悟空","hobby":"紫霞仙子"}
{"id":2,"name":"豬八戒","hobby":"高老莊"}
{"id":3,"name":"唐僧","hobby":"如來佛祖"}
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]#

六.Native二進制數據實戰案例

1.導出數據

[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "SELECT * FROM json_demo FORMAT Native"   > /tmp/native.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# file /tmp/native.demo
/tmp/native.demo: data
[root@node-exporter42 ~]#

2.導入數據

	1.導入數據
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie --query "INSERT INTO json_demo FORMAT Native"   < /tmp/native.demo
[root@node-exporter42 ~]# 
[root@node-exporter42 ~]# 


	2.登錄查看【發現數據的確又導入了一遍】
[root@node-exporter42 ~]# clickhouse-client --password yinzhengjie --database yinzhengjie
...	
node-exporter42 :) SELECT * FROM json_demo ;

SELECT *
FROM json_demo

Query id: 9ff9b69d-b4ad-44d3-ab81-521d25dfe2b3

   ┌─id─┬─name───┬─hobby────┐
1. │  1 │ 孫悟空 │ 紫霞仙子 │
2. │  2 │ 豬八戒 │ 高老莊   │
3. │  3 │ 唐僧   │ 如來佛祖 │
4. │  1 │ 孫悟空 │ 紫霞仙子 │
5. │  2 │ 豬八戒 │ 高老莊   │
6. │  3 │ 唐僧   │ 如來佛祖 │
   └────┴────────┴──────────┘

6 rows in set. Elapsed: 0.003 sec. 

node-exporter42 :)

七.Null實戰案例

1.正常查詢

node-exporter42 :) SELECT * FROM json_demo ;

SELECT *
FROM json_demo

Query id: aa3d3793-8464-4be7-9b0a-a80913067fa4

   ┌─id─┬─name───┬─hobby────┐
1. │  1 │ 孫悟空 │ 紫霞仙子 │
2. │  2 │ 豬八戒 │ 高老莊   │
3. │  3 │ 唐僧   │ 如來佛祖 │
4. │  1 │ 孫悟空 │ 紫霞仙子 │
5. │  2 │ 豬八戒 │ 高老莊   │
6. │  3 │ 唐僧   │ 如來佛祖 │
   └────┴────────┴──────────┘

6 rows in set. Elapsed: 0.002 sec. 

node-exporter42 :)

2.使用NULL格式查詢

node-exporter42 :) SELECT * FROM json_demo FORMAT NULL;

SELECT *
FROM json_demo
FORMAT `NULL`

Query id: 8dd57469-a9cc-43b5-be9e-1530db66137f

Ok.  # 注意哈，并沒有輸出數據，只能看到查詢的響應時間，一般用于測試。

0 rows in set. Elapsed: 0.003 sec.  

node-exporter42 :)

八.Pretty系列格式實戰案例

1.Pretty案例

node-exporter42 :) SELECT * FROM json_demo FORMAT Pretty;

SELECT *
FROM json_demo
FORMAT Pretty

Query id: cc3eb603-ad0e-47dc-8b0d-d378272eee15

   ┏━━━━┳━━━━━━━━┳━━━━━━━━━━┓
   ┃ id ┃ name   ┃ hobby    ┃
   ┡━━━━╇━━━━━━━━╇━━━━━━━━━━┩
1. │  1 │ 孫悟空 │ 紫霞仙子 │
   ├────┼────────┼──────────┤
2. │  2 │ 豬八戒 │ 高老莊   │
   ├────┼────────┼──────────┤
3. │  3 │ 唐僧   │ 如來佛祖 │
   ├────┼────────┼──────────┤
4. │  1 │ 孫悟空 │ 紫霞仙子 │
   ├────┼────────┼──────────┤
5. │  2 │ 豬八戒 │ 高老莊   │
   ├────┼────────┼──────────┤
6. │  3 │ 唐僧   │ 如來佛祖 │
   └────┴────────┴──────────┘

6 rows in set. Elapsed: 0.003 sec. 

node-exporter42 :)

2.PrettyCompact案例

node-exporter42 :) SELECT * FROM json_demo FORMAT PrettyCompact;

SELECT *
FROM json_demo
FORMAT PrettyCompact

Query id: c8857a9b-f1d6-4464-a37e-3a15c5483237

   ┌─id─┬─name───┬─hobby────┐
1. │  1 │ 孫悟空 │ 紫霞仙子 │
2. │  2 │ 豬八戒 │ 高老莊   │
3. │  3 │ 唐僧   │ 如來佛祖 │
4. │  1 │ 孫悟空 │ 紫霞仙子 │
5. │  2 │ 豬八戒 │ 高老莊   │
6. │  3 │ 唐僧   │ 如來佛祖 │
   └────┴────────┴──────────┘

6 rows in set. Elapsed: 0.005 sec. 

node-exporter42 :)

3.PrettySpace案例

node-exporter42 :) SELECT * FROM json_demo FORMAT PrettySpace;

SELECT *
FROM json_demo
FORMAT PrettySpace

Query id: 161f61db-2949-4a73-9b97-d03a81294099

    id   name     hobby   

1.   1   孫悟空   紫霞仙子 
2.   2   豬八戒   高老莊   
3.   3   唐僧     如來佛祖 
4.   1   孫悟空   紫霞仙子 
5.   2   豬八戒   高老莊   
6.   3   唐僧     如來佛祖 

6 rows in set. Elapsed: 0.007 sec. 

node-exporter42 :)

九.其他數據格式測試案例

1.Values案例

node-exporter42 :) SELECT * FROM json_demo FORMAT Values ;

SELECT *
FROM json_demo
FORMAT Values

Query id: 845d1442-40e1-45a8-9b90-54d0525b020a

(1,'孫悟空','紫霞仙子'),(2,'豬八戒','高老莊'),(3,'唐僧','如來佛祖'),(1,'孫悟空','紫霞仙子'),(2,'豬八戒','高老莊'),(3,'唐僧','如來佛祖')             

6 rows in set. Elapsed: 0.006 sec. 

node-exporter42 :)

2.Vertical案例

node-exporter42 :) SELECT * FROM json_demo FORMAT Vertical ;

SELECT *
FROM json_demo
FORMAT Vertical

Query id: 0b3f3076-e757-44f9-a583-b36510624077

Row 1:
──────
id:    1
name:  孫悟空
hobby: 紫霞仙子

Row 2:
──────
id:    2
name:  豬八戒
hobby: 高老莊

Row 3:
──────
id:    3
name:  唐僧
hobby: 如來佛祖

Row 4:
──────
id:    1
name:  孫悟空
hobby: 紫霞仙子

Row 5:
──────
id:    2
name:  豬八戒
hobby: 高老莊

Row 6:
──────
id:    3
name:  唐僧
hobby: 如來佛祖

6 rows in set. Elapsed: 0.003 sec. 

node-exporter42 :)

3.XML案例

node-exporter42 :) SELECT * FROM json_demo FORMAT XML ;

SELECT *
FROM json_demo
FORMAT XML

Query id: be4c5509-f94a-49c7-b0e6-625f6529e7d0

<?xml version='1.0' encoding='UTF-8' ?>
<result>
	<meta>
		<columns>
			<column>
				<name>id</name>
				<type>UInt8</type>
			</column>
			<column>
				<name>name</name>
				<type>String</type>
			</column>
			<column>
				<name>hobby</name>
				<type>String</type>
			</column>
		</columns>
	</meta>
	<data>
		<row>
			<id>1</id>
			<name>孫悟空</name>
			<hobby>紫霞仙子</hobby>
		</row>
		<row>
			<id>2</id>
			<name>豬八戒</name>
			<hobby>高老莊</hobby>
		</row>
		<row>
			<id>3</id>
			<name>唐僧</name>
			<hobby>如來佛祖</hobby>
		</row>
		<row>
			<id>1</id>
			<name>孫悟空</name>
			<hobby>紫霞仙子</hobby>
		</row>
		<row>
			<id>2</id>
			<name>豬八戒</name>
			<hobby>高老莊</hobby>
		</row>
		<row>
			<id>3</id>
			<name>唐僧</name>
			<hobby>如來佛祖</hobby>
		</row>
	</data>
	<rows>6</rows>
	<statistics>
		<elapsed>0.002243294</elapsed>
		<rows_read>6</rows_read>
		<bytes_read>228</bytes_read>
	</statistics>
</result>

6 rows in set. Elapsed: 0.002 sec. 

node-exporter42 :)

posted @ 2025-07-04 23:05 尹正杰閱讀(77) 評論(0) 收藏舉報

刷新頁面返回頂部

尹正杰

Clickhouse常用的數據格式實戰案例

一.Clickhouse的數據格式概述

1.Clickhouse支持的格式

2.格式概述

2.1 TabSeparated(制表符)系列格式

2.2 TSKV

2.3 CSV系列格式

2.4 JSON系列

2.5 ORC格式

2.6 Native格式

2.7 Null格式

2.8 Pretty系列格式

2.9 其他數據格式

二.TabSeparated(制表符)系列實戰案例

1.TabSeparated案例實戰

1.1 創建表

1.2 導入TabSeparated格式數據

1.3 查詢并導出TabSeparated格式數據

2.TabSeparatedRaw案例實戰

2.1 創建表

2.2 導入TabSeparatedRaw格式數據

2.3 查詢并導出TabSeparated格式數據

三.TSKV實戰案例

1.查詢數據

2.導出數據

3.導入數據

四.CSV系列實戰案例

1.CSV案例

1.1 導入數據

1.2 查詢數據

1.3.導出數據

2.CSVwithNames案例

2.1 查看數據

2.2 導出數據

3.csvWithNamesAndTypes案例

3.1 查詢數據

3.2 導出數據

五.JSON系列案例實戰

1.JSON案例

1.1 準備測試數據

1.2 以JSON格式查詢數據

1.3 導出數據

2.JSONCompact案例

2.1 查詢數據

2.2 導出數據

3.JSONEachRow案例

3.1 查看數據

3.2 導出數據

六.Native二進制數據實戰案例

1.導出數據

2.導入數據

七.Null實戰案例

1.正常查詢

2.使用NULL格式查詢

八.Pretty系列格式實戰案例

1.Pretty案例

2.PrettyCompact案例

3.PrettySpace案例

九.其他數據格式測試案例

1.Values案例

2.Vertical案例

3.XML案例

公告