clickhouse表、字段等查询语句

clickhouse部分查询语句1.查询数据库所有表。select tablefrom system.parts where database = 'economic_brain' group by table2.查询数据库表所有字段select distinct column from system.parts_columns where database='economic_brain' an

winsanity

7048人浏览 · 2021-10-20 15:33:33

winsanity · 2021-10-20 15:33:33 发布

clickhouse部分查询语句

1.查询数据库所有表。

select table  from system.parts where database = 'economic_brain' group by table

2.查询数据库表所有字段

select distinct column from system.parts_columns where database='economic_brain' and table='company'

或

select name  from system.columns where  table='company'

3.查询数据库中表name、count、size。

select 
  table ,
  sum(rows) as counts,
  formatReadableSize(sum(bytes_on_disk)) AS size 
from system.parts
where database = 'economic_brain' --数据库名称，自定义
group by table
order by counts DESC;

4.arrayStringConcat和groupArray的运用，分组时合并数组为字符串。

select arrayStringConcat(groupArray(categoryCode),',') as categoryCode  ,name,cateFirst  from
            (select cate_first name,left(category_code,2) categoryCode , cate_first cateFirst
            from category_code GROUP BY categoryCode,cate_first)x GROUP BY name,cateFirst