clickhouseSQL语法 - 守护式等待

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

发表于 2020-04-15 16:55 守护式等待阅读(109) 评论(0) 编辑收藏举报