Clickhouse常用的数据格式实战案例

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

版权声明：原创作品，谢绝转载！否则将追究法律责任。

目录

一.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	https://www.cnblogs.com/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	https://www.cnblogs.com/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   https://www.cnblogs.com/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   https://www.cnblogs.com/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   https://www.cnblogs.com/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=https://www.cnblogs.com/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=https://www.cnblogs.com/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=https://www.cnblogs.com/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  尹正杰  阅读(57)  评论(0)    收藏  举报