1 CREATE
#方式1 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = engine #方式2 CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine] #方式3 CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...
2 INSERT INTO
#方式1-交互式 INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), ... INSERT INTO [db.]table [(c1, c2, c3)] SELECT ... #方式2-批量 cat file.csv | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV" #方式3-http客户端 echo -ne '10\n11\n12\n' | POST 'http://localhost:8123/?query=INSERT INTO t FORMAT TabSeparated'
3 SELECT
SELECT [DISTINCT] expr_list [FROM [db.]table | (subquery) | table_function] [FINAL] [SAMPLE sample_coeff] [ARRAY JOIN ...] [GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list [PREWHERE expr] [WHERE expr] [GROUP BY expr_list] [WITH TOTALS] [HAVING expr] [ORDER BY expr_list] [LIMIT [n, ]m] [UNION ALL ...] [INTO OUTFILE filename] [FORMAT format] [LIMIT n BY columns]
4 ALTER
ALTER查询仅支持* MergeTree族表引擎,以及Merge表引擎和Distributed表引擎。
ALTER操作阻塞所有对表的其他操作。
#添加列 ALTER TABLE [db].name [ON CLUSTER cluster] ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after] #删除列 ALTER TABLE [db].name [ON CLUSTER cluster] DROP COLUMN [IF EXISTS] name #重置指定分区中列的所有数据 ALTER TABLE [db].name [ON CLUSTER cluster] CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name #添加列注解 ALTER TABLE [db].name [ON CLUSTER cluster] COMMENT COLUMN [IF EXISTS] name 'comment' #修改列类型或者列的默认值 ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY COLUMN [IF EXISTS] name [type] [default_expr] #添加索引 ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value AFTER name [AFTER name2] #删除索引 ALTER TABLE [db].name DROP INDEX name #分离分区 ALTER TABLE table_name DETACH PARTITION partition_expr #删除分区 ALTER TABLE table_name DROP PARTITION partition_expr #添加被分离的分区 ALTER TABLE table_name ATTACH PARTITION|PART partition_expr #复制table1中的分区数据到table2 ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1 #重置列值为默认值,默认值为创建表时指定 ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr #创建指定分区或者所有分区的备份 ALTER TABLE table_name FREEZE [PARTITION partition_expr] #从其他分片中复制分区数据 ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'
5 clickhouse导入数据
方式一:交互式 insert into tableName (c1, c2, ...) values (v1, v2, ...) insert into tableName (c1, c2, ...) select ... 方式二:批量 clickhouse-client --quey="insert into tableName format CSV" < file.csv 方式三:http客户端 echo -ne '10\n11\n12\n' | POST 'http://localhost:8123/?query=insert into tableName format TabSeparated' 方式四:数据库中导入数据 cat table_name.sql | clickhouse-client --multiline -h 192.168.104.99 --port 9000 -u ck --password ur6LMIUD --query="INSERT INTO data_center.stream_event_text FORMAT TabSeparated" # 192.168.107.215:ip 8123:端口 data_center:数据库 stream_event_text:表名称 ck:用户名 ur6LMIUD:密码 方式四:mysql数据库中导入数据 insert into cloud_joy.album (album_id, member_id, add_time, album_res_hash, add_date) SELECT album_id, member_id, add_time, album_res_hash, add_date FROM mysql('192.168.107.216:3306', 'cloud_joy', 'album', 'root', 'root'); # album_id, member_id, add_time, album_res_hash, add_date:字段名称 192.168.107.216:3306:ip+端口 cloud_joy:库名称 album:表名称 root:账号 root:密码
6 clickhouse导出数据
方式一:交互式 select * from tableName into outfile 'path/file' 方式二:非交互式 clickhouse-client --database bdName -u default --password password --query='select * from tableName' > abc 方式二:http客户端 echo 'select 1 FORMAT TabSeparated' | curl "http://user:password@localhost:8123/" -d @- > file 导入数据 echo 'select * from stream_event' | curl 192.168.107.215:8123?database=data_center -uck:ur6LMIUD -d @- > table_name.sql # 192.168.107.215:ip 8123:端口 data_center:数据库 ck:用户名 ur6LMIUD:密码click
7 clickhouse基础语法
use clickhouse_test; # 进入clickhouse_test数据库 rename table tbl1 to btl2; # 重命名表名 desc tbl; # 查看表结构 drop table if exists clickhouse_test.t; # 删除表 drop database if exists clickhouse_test; # 删除库 drop database if exists data_center; # 删除库 CREATE DATABASE IF NOT EXISTS data_center; # 创建库 SELECT * FROM data_center.stream_event # 查看数据 ALTER TABLE data_center.stream_event_local DELETE WHERE member_id !=0 # 删除数据 use data_center; # 进入库 DROP TABLE IF EXISTS data_center.click_event_local; # 删除表 ALTER TABLE cloud_joy.box_sn UPDATE act_member_id=8922, cur_member_id=8922, edit_time=toDateTime('2020-06-13 15:46:16'), edit_user='login' WHERE sn_id=2919 # 更新数据 CREATE TABLE IF NOT EXISTS cloud_joy.test ON CLUSTER shunwang_clickhouse ( # IF NOT EXISTS:表存在不创建 表不存在就创建 ON CLUSTER: 在任何一个节点创建在张表其他节点也会创建这张表 shunwang_clickhouse: 集群名 `id` UInt32 COMMENT 'id', # COMMENT 'id':给字段添加备注 `name` String COMMENT '名称' ) ENGINE = MergeTree() ORDER BY ( id ) SETTINGS index_granularity = 8192; clickhouse-client --multiline -h 192.168.104.91 --port 9000 -u ck --password shunwang select * from data_center.stream_event limit 0,20; select add_time from data_center.stream_event limit 0,20; select count(1) from data_center.stream_event; select add_time from data_center.stream_event where add_time < '2020-07-20 20:56:45'; TRUNCATE TABLE IF EXISTS data_center.stream_event_local; # ALTER TABLE UPDATE/DELETE不支持分布式DDL,因此需要在分布式环境中手动在每个节点上local的进行更新/删除数据 ALTER TABLE data_center.stream_event_local DELETE where add_time > '2000-07-20 20:56:45'; clickhouse-client --multiline -h 192.168.104.91 --port 9000 -u ck --password shunwang --query="SELECT * FROM data_center_yun.activity_11_client_event FORMAT CSV" > /root/activity_11_client_event.csv # 导出数据为csv clickhouse-client -h 192.168.104.91 --port 9000 -u ck --password shunwang --query="SELECT * FROM data_center.stream_event" > /root/stream_event.tsv # 导出数据 cat /root/stream_event.tsv | clickhouse-client -h 192.168.104.91 --port 9000 -u ck --password shunwang --query "INSERT INTO data_center.stream_event FORMAT TSV" # 导入数据 show create table data_center_yun.stream_event; # 查看建表语句
8 查询表信息(建表语句,数据等)
clickhouse-client --multiline -h 192.168.104.91 --port 9000 -u ck --password shunwang --query="SELECT * FROM system.tables FORMAT CSV" > /root/tables.csv