clickhouse基本语法小计

个人学习笔记，谢绝转载！！！
原文：https://www.cnblogs.com/wshenjin/p/13068122.html

数据库

建库语法：

CREATE DATABASE [IF NOT EXISTS] db_name [ENGINE=engine];

Clickhouse的数据库也支持设置引擎。数据库目前一共支持5种引擎：

Ordinary: 默认引擎，在此数据库里可以使用任意类型的表引擎。
Dictionary: 字典引擎，此类数据库会自动为所有数据字典创建他们的数据表。
Memory: 内存引擎，此类数据库里的表只会停留在内存中，不涉及磁盘操作，服务重启数据会被清除。
Lazy：日志引擎，此类数据库里的表只能使用Log系列的表引擎。
MySQL: MySQL引擎，此类数据库会自动拉取MySQL中的表，并为他们创建MySQL表引擎的数据表。

在绝大多数情况下只需使用默认数据库引擎即可:

localhost :) CREATE DATABASE IF NOT EXISTS testdb ENGINE=Ordinary;

建库之后，clickhouse会在path路径的data目录下创建对应的数据库目录文件：

# ls -l data/
drwxr-x--- 2 clickhouse clickhouse   6 Feb 23 17:48 testdb
# ls -l metadata/
drwxr-x--- 2 clickhouse clickhouse  6 Feb 23 17:48 testdb
-rw-r----- 1 clickhouse clickhouse 41 Feb 23 17:48 testdb.sql

删库语法：

DROP DATABASE [IF EXISTS] db_name;

数据表

Clickhouse目前提供了三种最基本的建表方法。

1、常规定义方法：

CREATE TABLE [IF NOT EXISTS] [db_name].tb_name (
    name1 [type] [Default|Materialized|Alias expr],
    name2 [type] [Default|Materialized|Alias expr],
    ....
) ENGINE=engine

使用[db_name].参数可以为表指定数据库，不指定默认使用default库。

2、复制其他表结构：

CREATE TABLE [IF NOT EXISTS] [db_name2].tb_name2 AS [db_name1].tb_name1 ENGINE=engine

这种方式支持在不同数据库之间复制表结构。

3、通过SELECT子句形式创建：

CREATE TABLE [IF NOT EXISTS] [db_name].tb_name ENGINE=engine AS SELECT...

这种方式不仅会根据SELECT子句创建相应的表结构，同时会顺带把SELECT子查询的结果写入。

删表语法：

DROP TABL [IF EXISTS] [db_name].tb_name;

查看表结构：

SHOW CREATE TABLE tb_name;
DESCRIBE TABLE tb_name;
DESC tb_name;

默认值表达式

表字段支持三种默认值表达式定义方法：DEFAULT、MATERIALIZED、ALIAS。

无论哪种表达式，表字段一旦被定义了默认值，它便不再强制要求定义数据类型，因为clickhouse会根据默认值进行类型判断。如果同时定义了表字段的类型和默认值表达式，则以明确定义的数据类型为主。

CREATE TABLE tt (
    id UInt16,
    c1 Default 123456,
    c2 String Default c1
) ENGINE = TinyLog ;

c1字段没有定义数据类型，默认值是整形123456；c2字段定义数据类型，且默认值等于c1字段。

现在写入数据，并查看：

localhost :)  INSERT INTO tt(id) VALUES(1);
localhost :)  INSERT INTO tt(id, c1) VALUES(2, 1111);
localhost :)  SELECT c1, c2, toTypeName(c1), toTypeName(c2) FROM tt;
┌─────c1─┬─c2─────┬─toTypeName(c1)─┬─toTypeName(c2)─┐
│ 123456 │ 123456 │ UInt32         │ String         │
│   1111 │ 1111   │ UInt32         │ String         │
└────────┴────────┴────────────────┴────────────────┘

tt表c1字段根据默认值被判断为UInt32，而c2被定义了String类型，所以它最终的数据类型来自已定义的String。

三种默认值表达式的区别：

数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中，而MATERIALIZED和ALIAS都不能显示赋值，只能依靠计算取值。
数据查询时，只有DEFAULT类型的字段可以通过SELECT * 返回，而MATERIALIZED和ALIAS类型的字段不会出现在SELECT * 的结果集中。
数据存储时，只有DEFAULT和MATERIALIZED类型的字段才支持持久化，而ALIAS类型的字段不支持，它的取值总是依靠计算产生。

临时表

Clickhouse也有临时表的概念，使用TEMPORARY关键字：

CREATE TEMPORARY TABLE [IF NOT EXISTS] tb_name (
    name1 [type] [Default|Materialized|Alias expr],
    name2 [type] [Default|Materialized|Alias expr],
    ....
)

临时表特殊之处：

生命周期和会话绑定，会话结束数据就会被撤销，所以只支持Memory引擎。
临时表不属于任何数据库。
临时表优先级大于普通标，两张表同名时，优先读取临时表。

通常不会刻意使用临时表，它更多是被运用在clickhouse的内部，是数据在集群件传播的载体。

表的基本操作

目前只有Merge、MergeTree和Distributed三类表引擎支持ALTER操作。

添加字段：

ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [TYPE] [DEFAULT_EXPR] [AFTER name_after];

修改数据类型：

ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [TYPE] [DEFAULT_EXPR];

修改备注：

ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name '****';

删除字段：

ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name;

移动表：

RENAME TABLE [db_name01.]tb_name01 TO [db_name02.]tb_name02, [db_name11.]tb_name11 TO [db_name12.]tb_name12, ...

RENAME TABLE 只能在同一个服务节点内。

清空表：

TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name;

分区表

目前只有MergeTree家族的表引擎才支持表分区，使用PARTITION BY 指定分区键。例如下面pp1表使用了etime日期字段作为分区键，并将其格式化为年月的形式：

CREATE TABLE IF NOT EXISTS pp1 (
    id UInt8,
    data String,
    etime Date
) ENGINE=MergeTree() 
PARTITION BY toYYYYMM(etime) 
ORDER BY id;

写入数据：

localhost :) INSERT INTO pp1(id, data, etime) VALUES(1,'A','2021-02-24'),(2,'B','2021-02-24'),(3,'C','2021-01-23'),(4,'D','2021-01-23'); 

localhost :) SELECT * FROM pp1;

┌─id─┬─data─┬──────etime─┐
│  3 │ C    │ 2021-01-23 │
│  4 │ D    │ 2021-01-23 │
└────┴──────┴────────────┘
┌─id─┬─data─┬──────etime─┐
│  1 │ A    │ 2021-02-24 │
│  2 │ B    │ 2021-02-24 │
└────┴──────┴────────────┘

通过system.parts系统表，查看表分区的状态：

localhost :) select table, partition, path from system.parts where database='testdb' and table='pp1';

┌─table─┬─partition─┬─path─────────────────────────────────────────────────────────┐
│ pp1   │ 202101    │ /data/database/clickhouse/data/data/testdb/pp1/202101_5_5_0/ │
│ pp1   │ 202102    │ /data/database/clickhouse/data/data/testdb/pp1/202102_4_4_0/ │
└───────┴───────────┴──────────────────────────────────────────────────────────────┘

表分区的基本操作

删除指定分区：

ALTER TABLE tb_name DROP PARTITION partition_expr;

复制分区数据：

ALTER TABLE tb_name1 REPLACE PARTITION partition_expr FROM tb_name2;

clickhouse支持将A表分区的数据复制到B表。可以相互复制的表需要满足：

两张表需要有相同的分区键
表结构完全相同

重置分区数据：

ALTER TABLE tb_name CLEAR COLUMN column_naem IN PARTITION partition_expr;

将数据表某一列重置为默认值。如果建表声明了默认值表达式，则以表达式为准，否者以相应数据类型的默认值为准。

卸载分区：

ALTER TABLE tb_name DETACH PARTITION partition_expr;

装载分区

ALTER TABLE tb_name ATTACH PARTITION partition_expr;

分区被卸载后，物理目录并没有被删除而是转移到当前数据目录的detached子目录下。而装载分区则是反向操作。一旦分区被转移到detached子目录，就代表其脱离clickhouse的管理，clickhouse并不会主动清理这些文件，除非我们手动删除或者重新装载。

备份还原分区：

表分区数据的备份，可以通过FREEZE和FETCH实现。

数据写入

INSERT语句支持三种语法范式。

VALUES格式：

INSERT INTO [db_name.]tb_name [(c1,c2,c3...)] VALUES(v1,v2,v3..), [(v1,v2,v3..)...]

指定格式：

INSERT INTO [db_name.]tb_name [(c1,c2,c3...)] FORMAT format_name data_set;

clickhouse 支持多种数据格式，常见的CSV格式写入：

INSERT INTO tb1 FORMAT CSV \
'0001', 'AAA', '2020-01-01' \
'0002', 'BBB', '2020-02-02'

SELECT子句形式：

INSERT INTO [db_name.]tb_name [(c1,c2,c3...)] SELECT...

通过SELECT子句将查询结果写入数据表，同样也支持表达式或函数，例如：

INSERT INTO tb1 SELECT '0001', now();

数据的删除与修改

Clickhouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation查询，它可以看做是ALTER语句的变种。Mutation虽然能实现DELETE和UPDATE，但不能用通常意义上的DELTE和UPDATE来理解：Mutation是种很重的操作，更适用于批量数据的删除修改；其次不支持事物，语句一旦被提交就立刻对现有数据产生影响，无法回滚；最后，Mutation是一个异步执行的过程，语句提交就立即返回。

DELETE语句：

ALTER TABLE [db_name.]tb_name DELETE WHERE fileter_expr;

UPDATE语句：

ALTER TABLE [db_name.]tb_name UPDATE column1 = expr1, column2 = expr2, ... WHERE fileter_expr;

UPDATE支持在一条语句中同时修改多个字段，但分区键和主键不能作为被修改的字段。

数据的导入导出

导出CSV:

[root@ ~]# clickhouse-client --password 123456 --database testdb  --query="select * from t1 format CSV;" > t.csv 
[root@ ~]# echo "select * from t1 format CSV" | clickhouse-client  --password 123456  --database testdb  > t.csv

从CSV导入：

[root@ ~]# cat t.csv | clickhouse-client --password 123456 --database testdb --query="insert into t1 FORMAT CSV" 
[root@ ~]# clickhouse-client --password 123456 --database testdb --query="insert into t1 FORMAT CSV"  < t.csv

--format_csv_delimiter="|" ,指定导入导出的分隔符

posted @ 2020-06-08 20:16 wshenJin 阅读(3438) 评论(0) 编辑收藏举报

刷新页面返回顶部