精华 ClickHouse奇技淫巧系列之SQL查文件
发布于 4 个月前 作者 jackpgao 614 次浏览 来自 实战经验

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

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

如何用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,玩不了的哦~
1 回复

不支持表别名, 真是很可惜.

回到顶部