ClickHouse 基础笔记

ClickHouse 基础笔记

Author Wangjing
Date 06/10/2021 18:34 Fri

Kettle

• plugins

D:\pentaho\data-integration7.1\plugins\KettleClickhouseJDBC.jar

• jdbcDriver

D:\pentaho\data-integration7.1\lib\clickhouse-jdbc-0.1.50d.jar

Databse

• 建库

create database db1 on CLUSTER clickhouse_cluster ENGINE=Ordinary

• 删库

drop database db1 on CLUSTER clickhouse_cluster

Table

create table as (table)

create table default.t_test on Cluster clickhouse_cluster as CHECK_INFO.T_TN_TEST

create table as (table) （The old way）

create table default.t_test ENGINE = Memory on Cluster clickhouse_cluster as select * from CHECK_INFO.T_TN_TEST where 1=2

create table as (select * from )

create table default.t_test ENGINE = Memory on Cluster clickhouse_cluster as select * from CHECK_INFO.T_TN_TEST

rename and move on cluster

有坑，慎用

--rename
rename table default.version_coll_table to default.version_coll_table on cluster
--move(服务器目录结构同步修改,但是DDL语句不会更新)
rename table default.version_coll_table to testdb.version_coll_table on cluster

View

create view on Cluster

create view testdb.v_test on Cluster clickhouse_cluster as(...)

drop view on Cluster

drop view testdb.v_test on Cluster clickhouse_cluster

drop table on Cluster

--Not check if view
drop table testdb.v_test on Cluster clickhouse_cluster

column

describe

desc db1.MergeTree;
describe table db1.MergeTree;

table column

alter table WAZD_SC.CLIENTLIST_LO ON cluster clickhouse_cluster  comment column USER_NAME        '用户名';

ENGINE

MergeTree()

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

ReplacingMergeTree()

同一partition，order键相同，保留最后一行

CREATE TABLE testdb.t_test ON Cluster clickhouse_cluster
(
  GUID                Int64,
  TBIRTHDAY           DATE,
  TENTER_TIME         Datetime,
  SID                 String,
  GARDEROBE_NO        Nullable(String),
  CK_CTS              Datetime DEFAULT Now()
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/CHINESE_LO','{replica}') PARTITION BY toYYYYMM (TENTER_TIME) PRIMARY KEY GUID
ORDER BY GUID SETTINGS index_granularity = 8192

ReplacingMergeTree(var)

在同一PARTITION内，ID相同，保留var最大的一条数据

CREATE TABLE testdb.t_test ON Cluster clickhouse_cluster
(
  GUID                Int64,
  TBIRTHDAY           DATE,
  TENTER_TIME         Datetime,
  SID                 String,
  GARDEROBE_NO        Nullable(String),
  CK_CTS              Datetime DEFAULT Now()
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/CHINESE_LO','{replica}',var) 
PARTITION BY toYYYYMM (TENTER_TIME) 
PRIMARY KEY GUID
ORDER BY GUID SETTINGS index_granularity = 8192

字典

select * from system.dictionaries;

DDL数据表方式

--drop DICTIONARY WAZD_SC.DIC_DIR_SEX on Cluster clickhouse_cluster
create DICTIONARY WAZD_SC.DIC_SEX on Cluster clickhouse_cluster
(
ID   UInt8,
NAME String
)
primary key ID
source(clickhouse (HOST '14.16.4.58' PORT 9000 USER 'default'  PASSWORD '' DB 'WAZD_SC' table 'DIR_SEX' ))
LAYOUT(FLAT())
LIFETIME(MIN 3 MAX 50);

TTL

创建表时指定 TTL

CREATE TABLE example_table
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

为表中已存在的列字段添加 TTL

ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 DAY;

修改列字段的 TTL

ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 MONTH;

DDL

optimize

optimize table version_coll_table;

optimize on cluster

optimize table  WAZD_SC.chinese_test_lo on cluster clickhouse_cluster FINAL;

alter table

add column

alter table YQ.QG_ZDDQ_MC
    add column FLAG Nullable(String) DEFAULT '0' AFTER XIAN;

modify table

modify column

alter table testdb.t_test on cluster modify column BACK_UPDATE_TIME Nullable(DateTime);

truncate

truncate table on cluster

truncate table testdb.t_test on cluster

drop table

drop table default.t_test on cluster

Mutations

ALTER TABLE DELETE

filter_expr必须是 UInt8型。该操作将删除表中 filter_expr表达式值为非0的列

ALTER TABLE db1.MergeTree DELETE WHERE filter_expr = 5

ALTER TABLE UPDATE

filter_expr必须是 UInt8型。该操作将更新表中各行 filter_expr表达式值为非0的指定列的值。通过 CAST 操作将值转换成对应列的类型。不支持对用于主键或分区键表达式的列进行更新操作。

ALTER TABLE db1.MergeTree UPDATE code = flag, flag = 2 WHERE code = 'c1' and id=1

ALTER TABLE db1.MergeTree UPDATE code = flag, flag = 2 WHERE 1=1

ALTER USER

ALTER USER [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [IDENTIFIED [WITH {PLAINTEXT_PASSWORD|SHA256_PASSWORD|DOUBLE_SHA1_PASSWORD}] BY {'password'|'hash'}]
    [[ADD|DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

ALTER ROLE

ALTER ROLE [IF EXISTS] name [ON CLUSTER cluster_name]
    [RENAME TO new_name]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

select

时区转换

select now(),toTimeZone(now(),'Asia/Shanghai');

DML

--clickhouse 最小时间不能小于 1970-01-01 08:00:00 否则 则会自动变成 2106-02-07 06:28:16
--kettle 目前的插件 值不能为空 否则 不可为空字段也可入进数据 （影响不可控）