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