ClickHouse学习小记--数据库表相关及其操作

DDL操作及定义数据的方法。DDL查询提供了数据表的创建、修改和删除操作，是最常用的功能之一。

ClickHouse的数据操作

一、数据库

数据库起到了命名空间的作用，可以有效规避命名冲突的问题，也为后续的数据隔离提供了支撑。任何一张数据表，都必须归属在某个数据库之下。创建数据库的完整语法如下所示：

CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]

其中，IF NOT EXISTS表示如果已经存在一个同名的数据库，则会忽略后续的创建过程；[ENGINE=engine]表示数据库所使用的引擎类型（数据库也支持设置引擎）。

数据库目前一共支持5种引擎，如下：

·Ordinary：

默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。

·Dictionary：

字典引擎，此类数据库会自动为所有数据字典创建它们的数据表。

·Memory：

内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除。

·Lazy：

日志引擎，此类数据库下只能使用Log系列的表引擎。

·MySQL：

MySQL引擎，此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表。

1、创建数据库

在绝大多数情况下都只需使用默认的数据库引擎。例如执行下面的语句，即能够创建属于我们的第一个数据库：

CREATE DATABASE DB_TEST

默认数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse便会在安装路径下创建DB_TEST数据库的文件目录：

# pwd 
/chbase/data 
# ls 
DB_TEST default system

与此同时，在metadata路径下也会一同创建用于恢复数据库的DB_TEST.sql文件：

# pwd 
/chbase/data/metadata 
# ls 
DB_TEST DB_TEST.sql default system

2、查询数据库

使用SHOW DATABASES查询，即能够返回ClickHouse当前的数据库列表：

SHOW DATABASES

name
DB_TEST
default
system

3、删除数据库

使用USE查询可以实现在多个数据库之间进行切换，而通过SHOW TABLES查询可以查看当前数据库的数据表列表。删除一个数据库，则需要用到下面的DROP查询。

DROP DATABASE [IF EXISTS] db_name

二、数据表

ClickHouse数据表的定义语法，是在标准SQL的基础之上建立的。

1、创建表

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

1.1、常规定义方法

它的完整语法如下所示：

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name 
( name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr], 
  name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  省略… )
ENGINE = engine

使用[db_name.]参数可以为数据表指定数据库，如果不指定此参数，则默认会使用default数据库。例如执行下面的语句：

CREATE TABLE hits_v1 
( Title String, URL String ,
  EventTime DateTime ) 
ENGINE = Memory;

上述语句将会在default默认的数据库下创建一张内存表。注意末尾的ENGINE参数，它被用于指定数据表的引擎。表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载。例如示例中的Memory表引擎，是ClickHouse最简单的表引擎，数据只会被保存在内存中，在服务重启时数据会丢失。

1.2、复制其他表的结构

具体语法如下所示：

CREATE TABLE 
[IF NOT EXISTS] [db_name1.]table_name 
AS 
[db_name2.] table_name2 
[ENGINE = engine]

这种方式支持在不同的数据库之间复制表结构，ENGINE表引擎可以与原表不同。例如下面的语句：

--创建新的数据库
CREATE DATABASE IF NOT EXISTS new_db 
--将default.hits_v1的结构复制到new_db.hits_v1 
CREATE TABLE IF NOT EXISTS new_db.hits_v1 
AS 
default.hits_v1 
ENGINE = TinyLog

1.3、通过SELECT子句的形式创建

它的完整语法如下：

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

在这种方式下，不仅会根据SELECT子句建立相应的表结构，同时还会将SELECT子句查询的数据顺带写入，例如执行下面的语句：

CREATE TABLE IF NOT EXISTS hits_v1_1 
ENGINE = Memory AS SELECT * FROM hits_v1

2、删除表

上述语句会将SELECT * FROM hits_v1的查询结果一并写入数据表。ClickHouse和大多数数据库一样，使用DESC查询可以返回数据表的定义结构。如果想删除一张数据表，则可以使用下面的DROP语句：

DROP TABLE [IF EXISTS] [db_name.]table_name

3、默认值表达式

表字段支持三种默认值表达式的定义方法，分别是DEFAULT、 MATERIALIZED、ALIAS。无论使用哪种形式，表字段一旦被定义了默认值，它便不再强制要求定义数据类型，因为ClickHouse会根据默认值进行类型推断。如果同时对表字段定义了数据类型和默认值表达式，则以明确定义的数据类型为主，例如下面的例子：

CREATE TABLE dfv_v1 
( id String, 
  c1 DEFAULT 1000, 
  c2 String DEFAULT c1 ) 
ENGINE = TinyLog

c1字段没有定义数据类型，默认值为整型1000；c2字段定义了数据类型和默认值，且默认值等于c1，现在写入测试数据：

INSERT INTO dfv_v1(id) VALUES ('A000')

在写入之后执行以下查询：

SELECT c1, c2, toTypeName(c1), toTypeName(c2) from dfv_v1

c1	c2	toTypeName(c1)	toTypeName(c2)
1000	1000	UInt16	String

由此，默认值的优先级符合我们的预期，其中c1字段根据默认值被推断为UInt16；而c2字段由于同时定义了数据类型和默认值，所以它最终的数据类型来自明确定义的String。

默认值表达式的三种定义方法之间也存在着不同之处，有如下比较。

（1）数据写入：在数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED和ALIAS都不能被显式赋值，它们只能依靠计算取值。例如试图为MATERIALIZED类型的字段写入数据，将会得到如下的错误。

DB::Exception: Cannot insert column URL, because it is MATERIALIZED column.. 

（2）数据查询：在数据查询时，只有DEFAULT类型的字段可以通过SELECT *返回。而MATERIALIZED、ALIAS类型的字段不会出现在SELECT *查询的返回结果集中。

（3）数据存储：在数据存储时，只有DEFAULT和 MATERIALIZED类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如TinyLog表引擎），那么这些列字段将会拥有物理存储。而ALIAS类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。可以使用ALTER语句修改默认值，例如：

ALTER TABLE [db_name.]table MODIFY COLUMN col_name DEFAULT value

修改动作并不会影响数据表内先前已经存在的数据。但是默认值的修改有诸多限制，例如在合并树表引擎中，它的主键字段是无法被修改的；而某些表引擎则完全不支持修改（例如TinyLog）。

4、临时表

ClickHouse也有临时表的概念，创建临时表的方法是在普通表的基础之上添加TEMPORARY关键字，它的完整语法如下所示：

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name