投影projection

官方文档https://clickhouse.com/docs/en/sql-reference/statements/alter/projection

创建这个特性时的讨论：https://github.com/ClickHouse/ClickHouse/issues/14730

快手的一个演讲，【Projection：快手对 ClickHouse 的重磅贡献】1，从这里看，似乎Projection是可以部分替代物化视图。

projection可以只对创建后的数据生效，如果确定查询都是只差最新的数据，比如当天，是否可以对projection设置ttl呢？看了这个 https://github.com/ClickHouse/ClickHouse/issues/26578 ，貌似现在是不支持的。

与物化视图的区别：

  • 物化视图独立于原始表，可以单独进行操作，如ttl
  • 物化视图可以从多个数据源中输入数据
  • 物化视图有可能会导致数据不一致(？)

一些影响：

  • 会增大磁盘使用空间,对于聚合函数来说，这种磁盘增加非常小
  • 那些聚合的projection会影响数据写入速度

物化视图

参考2

假如你有详细的日志数据，想做按小时、天、月做统计，直接针对原始表数据量会太大，可以使用物化视图。

csv导入的问题

参考：https://stackoverflow.com/questions/64052357/insert-using-csv-file-with-single-quote-in-string-field-cause-error

假如使用逗号分割，双引号转义的csv文件，而字段中出现了单引号开头的字符，可能会出现导入失败，因为clickhouse-client同时支持单引号和双引号转义，会自动探测，导入这一行数据就可能出现意料之外的结果。

解决方案，关闭单引号：

clickhouse-client --query "xxx" --format_csv_allow_single_quotes 0

SQL 技巧

查询空间占用

SELECT
table,
formatReadableSize(sum(bytes)) AS size,
sum(rows) AS rows
FROM system.parts
WHERE active
GROUP BY table

limit by， 参考3，假如有一个复杂查询，需要按照某一个键返回指定条记录，不需要写多条SQL，可以用LIMIT n BY expressions 这种方式，即每一个expressions返回n条记录。

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id

限制SQL执行时间，参考： https://clickhouse.com/docs/knowledgebase/query_max_execution_time

SELECT 1 SETTINGS max_execution_time=0.0001

topk

select topK(10)(host) as host from logs

查看变动的设置

SELECT name, value FROM system.settings WHERE changed

慢查询记录, https://chistadata.com/knowledge-base/clickhouse-query-troubleshooting/

#正在进行中的查询
SELECT query, query_id, elapsed, memory_usage, peak_memory_usage, read_rows,read_bytes, written_rows,written_bytes FROM system.processes 

# 某一天，所有大与10s，且完成的查询
select query_start_time,address,client_name,query_duration_ms,query from system.query_log where event_date = toDate('2023-03-08') and query_duration_ms > 10000 and type = 'QueryFinish'  format Vertical;

从query_log中查询涉及到某一个表的select查询，tables列是一个array, 使用arrayExists来检查是否包含某一个值。

select query from system.query_log 
where event_date = toDate('2024-01-12') and type = 'QueryFinish' 
and query_kind = 'Select'
and arrayExists(x -> x == 'default.test', tables) = 1;

工具

  1. https://toutiao.io/posts/x3yg1p0/preview ↩︎

  2. https://clickhouse.com/docs/en/guides/developer/cascading-materialized-views ↩︎

  3. https://clickhouse.com/docs/en/sql-reference/statements/select/limit ↩︎