创建库
CREATE/ATTACH DATABASE user_log ENGINE = Ordinary;
ATTACH 也可以建库,但是metadata目录下不会生成.sql文件,一般用于metadata元数据sql文件被删除后,恢复库表结构使用
建表
CREATE TABLE log_db.event_log
(
`ename` String,
`app_id` UInt64,
`channel` String,
`source` String,
`platform` String,
`openid` String,
`order_id` String,
`server_id` String,
`server_name` String,
`imei` String,
`oaid` String,
`idfa` String,
`system` String,
`device_name` String,
`role_id` String,
`role_name` String,
`role_level` UInt64,
`ip` IPv4,
`amount` Float32,
`pay_type` UInt64 DEFAULT 0,
`product_id` String DEFAULT '',
`product_name` String DEFAULT '',
`logday` Date,
`logtime` DateTime
) ENGINE = MergeTree() PARTITION BY toYYYYMM(logtime) ORDER BY (app_id, logtime) SETTINGS index_granularity = 8192
添加字段
ALTER TABLE event_log ADD COLUMN `to_app_id` UInt64;
ALTER TABLE t_event_log ADD COLUMN `to_openid` String;
# 修改字段
alter table user modify column user_name Nullable(String);
# 删除字段
alter table youi_log_db.t_event_log drop column pay_mode;
删除数据
ALTER TABLE log_db.event_log DELETE WHERE ename = 'login'
更新数据(未测试)
ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>
注意:
1. 该命令必须在版本号大于1.1.54388才可以使用,适用于 mergeTree 引擎2. 该命令是异步执行的,可以通过查看表 system.mutations 来查看命令的是否执行完毕
举例:
:) select event_status_key, count(*) from test_update where event_status_key in (0, 22) group by event_status_key;
┌─event_status_key─┬──count()─┐
│ 0 │ 17824710 │
│ 22 │ 1701 │
└──────────────────┴──────────┘
:) ALTER TABLE test_update UPDATE event_status_key=0 where event_status_key=22;
0 rows in set. Elapsed: 0.067 sec.
:) select event_status_key, count(*) from test_update where event_status_key in (0, 22) group by event_status_key;
┌─event_status_key─┬──count()─┐
│ 0 │ 17826411 │
└──────────────────┴──────────┘
① 索引列不能进行更新
:) ALTER TABLE test_update UPDATE event_key = 41 WHERE event_key = 40;
Received exception from server (version 18.12.17):
Code: 420. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Cannot UPDATE key column `event_key`.
② 分布式表不能进行更新
Received exception from server (version 18.12.17):
Code: 48. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Mutations are not supported by storage Distributed.
ALTER TABLE UPDATE/DELETE不支持分布式DDL,因此需要在分布式环境中手动在每个节点上local的进行更新/删除数据。
③ 不适合频繁更新或point更新
由于Clickhouse更新操作非常耗资源,如果频繁的进行更新操作,可能会弄崩集群,请谨慎操作。
clickhouse 操作:
1.CK中添加列:ALTER TABLE 表名称 ON 集群名称 ADD COLUMN 列名称 类型 DEFAULT 注解
eg:ALTER TABLE 表名称 ON 集群名称 ADD COLUMN lvl Int32 DEFAULT 'lvl'
2.CK中修改注释:alter table 表名称 ON 集群名称 COMMENT COLUMN 列名称 注解;
alter table adm_sch_d05_content_live_service_di_local ON 集群名称 COMMENT COLUMN `pv_sid` 'lvl';
3.CK中修改数据类型:ALTER table adm_sch_d05_content_live_service_di_local on 集群名称 MODIFY COLUMN `ord_dt` String;
ALTER table 表名称 ON 集群名称 MODIFY COLUMN 列名称 数据类型;
ALTER table adm_sch_d05_content_live_service_di on 集群名称 MODIFY COLUMN `ord_dt` String;
4.CK中删除列 alter table 表名称 ON 集群名称 drop column 列名称;
ALTER table adm_sch_d05_content_live_service_di_local on 集群名称 drop column `pv_sid`;
注意问题:
不可以修改列名称,CK中不支持修改列名称。
hive表中的日期为String类型到CK中不可以转为Date,会报错。必须为Sting.
常见报错:
Too many partitions for single INSERT block(more than 100)
写入数据分布在100个分区以上,引起报错。
比如用时间分区,写入数据分布在100天以上会引起报错
处理:
1.减小写入分区
2.修改配置 Too many partitions for single INSERT block(more than 100) - xibuhaohao - 博客园
一键同步mysql数据
clickhouse从MySQL同步数据_渡°的博客-CSDN博客_mysql同步clickhouse
CREATE TABLE tmp ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql('hostip:3306','db','table','user','passwd');