mysql租房house查询_ClickHouse奇技淫巧系列之SQL查文件

没有什么是1个SQL解决不了的.jpg

今天的主题，不用写序言，看上图就懂

如何用SQL的方式操作一个文件

先举例

想知道history命令里，敲得最多的是哪个？

当然，sed，awk完全可以做到，但是，如果用SQL怎么做？

history | awk '{print $1"\t"$2}' | clickhouse-client \

--query="SELECT shell, count() AS c FROM history \

GROUP BY shell ORDER BY c DESC limit 10 " \

--external --file=- --name=history \

--structure='id UInt16, shell String' -h 127.0.0.1

ls 390

cd 243

clickhouse-client 173

du 67

vim 57

htop 42

cat 28

history 27

tailf 25

mysql 24

原理

ClickHouse支持把一个外部文件，加载到内部的一个临时表中，对这个临时表进行SQL化操作

格式

--external --file=... [--name=...] [--format=...] [--types=...|--structure=...]

--external 表示这个操作是外部文件的

--file=... 指定一个文件，如果是标准输入，则写-

[--name=...] 表名，如果忽略，默认给_data

[--format=...] 列分隔符，默认是TabSeparated

`[--types=...|--structure=...] 这句不解释了，看上面的例子就好了

再来一个测试

为了模拟一个有意义的场景，我们选了ClickHouse的system.parts这个表，里面记录的是ClickHouse的分区信息，表结构如下

partition: 201709

name: 20170903_20170905_2_2963928_22

replicated: 0

active: 1

marks: 23372

rows: 191456971

bytes: 93294984484

modification_time: 2017-09-05 23:37:33

remove_time: 0000-00-00 00:00:00

refcount: 2

min_date: 2017-09-03

max_date: 2017-09-05

min_block_number: 2

max_block_number: 2963928

level: 22

primary_key_bytes_in_memory: 93488

primary_key_bytes_in_memory_allocated: 196608

database: xx

table: xx

engine: MergeTree

我们导出一份数据，作为测试文件

默认导出的文件是tab分割

clickhouse-client -h 127.0.0.1 -m -d system -q "select * from parts " > test.sql

目标SQL

找某个表的分区数据，即有几个分区，分区文件多大

SELECT

partition,

count() AS number_of_parts,

formatReadableSize(sum(bytes)) AS sum_size

FROM system.parts

WHERE active AND (database = 'xxxx') AND (table = 'xxxx_msg')

GROUP BY partition

ORDER BY partition ASC

文件SQL

root@10.xxxx:/root # wc -l test.sql

11991 test.sql

root@10.xxxx:/root # clickhouse-client \

--query="SELECT partition, count() AS number_of_parts, \

formatReadableSize(sum(bytes)) AS sum_size FROM parts \

WHERE active AND (database = 'xxxx') AND (table = 'xxxx_msg') \

GROUP BY partition ORDER BY partition ASC ;" \

--external --file=test.sql --name=parts \

--structure='partition UInt16,name String,replicated UInt16,active UInt16,marks UInt16,rows UInt16,bytes UInt16,modification_time String,remove_time String,refcount UInt16,min_date String,max_date String,min_block_number UInt16,max_block_number UInt16,level UInt16,primary_key_bytes_in_memory UInt16,primary_key_bytes_in_memory_allocated UInt16,database String,table String,engine String' \

-h 127.0.0.1

201709 36 1.68 TiB

201710 26 1.42 TiB

201711 30 1.42 TiB

201712 31 963.07 GiB

注意事项

文件操作虽然方便，但是官方文档也提到了，如果是特别大的文件，还是不要这么玩了

另外，这个文件SQL其实还是要依赖ClickHouse-Server的，如果你没有启动Server，玩不了的哦~