ClickHouse 学习

DDL

添加数据库字段

alter table user_tags add column last_subject String;
alter table user_tags add column class_trust_valids Int8;

删除列

ALTER TABLE [db].name [ON CLUSTER cluster] DROP COLUMN ...

日期函数 当toDate 遇到空的串报异常时

select toDate('');
DB::Exception: Cannot parse date: value is too short: Cannot parse Date from String: While processing toDate('').


:) select toDateOrZero('');

┌─toDateOrZero('')─┐
│       1970-01-01 │
└──────────────────┘


:) SELECT toDateOrNull('')

┌─toDateOrNull('')─┐
│             ᴺᵁᴸᴸ │
└──────────────────┘


:) SELECT toDate(parseDateTimeBestEffortOrNull('20210601'))

┌─toDate(parseDateTimeBestEffortOrNull('20210601'))─┐
│                                        2021-06-01 │
└───────────────────────────────────────────────────

ClickHouse 视图

• 普通视图：不会存储数据
• 物化视图：1、储存数据；2、有引擎，在磁盘存储；3、同步映射表数据

普通视图

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT ...

 物化视图

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

例子：

CREATE MATERIALIZED VIEW IF NOT EXISTS view_2 ENGINE = MergeTree() PARTITION BY ds ORDER BY (unionid) POPULATE AS
SELECT distinct unionid, ds
from user_tags
where service_wx <> '' AND (grade = '2018' OR grade = '2017' OR grade = '2016' OR grade = '2015' OR grade = '2014' OR grade = '2013' OR grade = '2012' OR grade = '2011' OR grade = '2010' )
AND is_teacher=0 and (is_parent=1 or is_student=1)
;

• 视图定义包括关键字POPULATE，决定了物化视图的更新策略：若有POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table … as
• 若无POPULATE 则物化视图在创建之后没有数据

如果物化视图是由两个或者多个表Join生成，那么仅当左表插入数据时才更新，右表插入数据不更新（待验证）,Join的数据类型一定要对等！它不会像Mysql一样做隐式类型转换！！不然就会出现类似： DB::Exception: Received from localhost:9000. DB::Exception:Type mismatch of columns to JOIN by: user_name String at left, uv Int32 at right.
参考：https://blog.csdn.net/whiteBearClimb/article/details/111284176

网站

中文教程网站https://clickhouse.com/docs/zh/sql-reference/syntax/