clickhouse简单SQL语法应用

--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'

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]

 

--3.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 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 FREEZE [PARTITION partition_expr]
#从其他分片中复制分区数据
ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'

 

--4.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'

 

--5.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 @-