ClickHouse常用命令

clickhouse-client --host=127.0.0.1 --port 9000 --user=wedo --password=123456  --database=rpt  --lock_acquire_timeout 36000

Clickhouse-client --host=127.0.0.1 --port=9000 --user=default --password=123456  --query="insert into dbname.table_name FORMAT CSV" <filename.csv

Clickhouse-client --host=127.0.0.1 --port=9000 --user=default --password=123456  --query="insert into dbname.table_name FORMAT CSVWithNames" <filename.csv

Clickhouse-client --host=127.0.0.1 --port=9000 --user=default --password=123456  --query="select * dbname.table_name FORMAT CSVWithNames" >filename.csv

optimize table src.src_dns_logs_cache;

   select
      `partition`,count(*)
   from system.parts
   where `table` ='src_dns_logs_cache'
   group by `partition`
   HAVING  count(*)>1
   order by `partition` desc ;

select
    databases as `数据库`,
    tables as `表`,
    concat(toString(query_duration_ms / 1000), '秒') as `耗时(秒)`,
    formatDateTime(query_start_time, '%Y-%m-%d %H:%M:%S') as `开始时间`,
    concat(toString(toInt32(read_rows / 10000)), '万') as `扫描数据(万条)`,
    read_rows,
    concat(toString(toInt32(read_bytes / 1048576)), 'MB') as `数据大小(MB)`,
    concat(toString(toInt32(written_rows / 10000)), '万') as `写入(万条)`,
    concat(toString(toInt32(memory_usage / 1048576)), 'MB') as `占用内存(MB)`,
    case when startsWith(trimBoth(query), '\n')
    then  trimBoth(substring(trimBoth(query), 2, length(trimBoth(query))))
    else  trimBoth(query)
    end as query,
    query_id
from
    `system`.query_log ql
WHERE
    `type` = 'QueryFinish'
    and http_user_agent = 'ClickHouse Java Client'
    and has(databases, 'rpt') == 1
    --      and has(tables, 'rpt.rpt_one_level_import_domain_name_1min') == 1
    and query_kind = 'Select'
    and query_start_time >= date_sub(hour, 4, now())
order by
query_duration_ms desc
--    query_start_time desc
--      memory_usage DESC
--read_rows DESC
--write_rows desc
limit 360;

with
	query_id = '2ab6e37a-0105-44d6-8e5b-49fc01ee74f3' as zero,
	query_id = '462e2130-89e3-49d0-9e64-4f7bff78b034' as first,
	query_id = '5ef79dd2-3882-4936-9e13-0061e17e6d0b' as second
select
	PE.Names AS metric,
	anyIf(PE.Values, zero) as v0,
	anyIf(PE.Values, first) as v1,
	anyIf(PE.Values, second) as v2
--	anyIf(PE.Values, third) as v3,
--	anyIf(PE.Values, forth) as v4
from clusterAllReplicas(
	clickhouse_cluster, system.query_log )
array join ProfileEvents as PE
WHERE
	(event_date > date_sub(day, 7, now()))
	AND (type = 2)
GROUP BY metric
--HAVING v1 != v2
ORDER BY
--	(v1 - v4) / (v1 + v4) DESC
--	v2 DESC ,
	metric ASC

SELECT  date_trunc('minute', parse_time ) tt, max(cnt) from (
	SELECT parse_time, count(1) cnt
	from dws.dws_dns_logs_cache_base_1min ddlcbm
	WHERE parse_time  > date_sub(minute, 40, now()) group by parse_time
) group by tt order by tt DESC ;t