---
title: "ClickHouse 数据定义"
date: 2020-09-23T12:00:00+08:00
lastmod: 2020-09-23T12:00:00+08:00
tags: []
categories: ["clickhouse"]
---

## 基础类型
### 整数
声明   | 大小(字节) | 范围
----   | ----       | ----
Int8   | 1          | -128 到 127
UInt8  | 1          | 0 到 255
Int16  | 2          | -32768 到 32767
UInt16 | 2          | 0 到 65535
Int32  | 4          | -2147483648 到 2147483647
UInt32 | 4          | 0 到 4294967295
Int64  | 8          | -9223372036854775808 到 9223372036854775807
UInt64 | 8          | 0 到 18446744073709551615

### 浮点数
声明    | 大小(字节) | 有效精度(位数)
----    | ----       | ----
Float32 | 4          | 7
Float64 | 8          | 16

- 正无穷: SELECT 0.8/0
- 负无穷: SELECT -0.8/0
- 非数字: SELECT 0/0

### 定点数
- 原生声明: Decimal(P,S)
    - P: 总位数(整数+小数)，取值范围 1~38
    - S: 小数位数，取值范围 0~P

- 其他声明
    - Decimal32(S): -10^(9-S) 到 10^(9-S)
    - Decimal64(S): -10^(18-S) 到 10^(18-S)
    - Decimal128(S): -10^(18-S) 到 10^(38-S)

### 字符串
声明           | 备注
----           | ----
String         | 长度不固定，不限字符集，建议 UTF-8
FixedString(N) | 长度固定，null 字节填充
UUID           | 格式是 8-4-4-4-12，0 填充

### 时间
声明           | 精度 | 示例
----           | ---- | ----
Datetime       | 秒   | 2020-09-18 19:59:00
Datetime64(N)  | 亚秒 | 2020-09-18 19:59:00.00
Date           | 日   | 2020-09-18

## 复合类型
### 数组
- 声明: [T], Array(T)
- 查询时会以最小储存代价为原则推断类型
- 元素类型可以不同，但必须兼容

### 元组
- 声明: (T), tuple(T)
- 查询时会以最小储存代价为原则推断类型
- 元素类型可以不同，且无须兼容

### 枚举
声明                               | Key 类型 | Value 类型
----                               | ----     | ----
Enum8('k1'=1,'k2'=2, ... ,'kN'=N)  | String   | Int8
Enum16('k1'=1,'k2'=2, ... ,'kN'=N) | String   | Int16

- 枚举的所有后续操作，都会使用 Int 类型的 Value 值

### 嵌套
- 声明
    ```sql
    Nested(
        column1 T,
        column2 T
    )
    ```
- 嵌套字段中的每一列期望写入的是数组: Array(T)
- 同一行数据内，嵌套字段中的每一列的数组长度必须相等
- 访问嵌套字段中的列时用点(.)连接

### 可空类型
- 声明: Nullable(T)
- 只能和基础类型搭配使用，不用用于复合类型和索引字段
- 慎用，会额外生成 [Column].null.bin 文件保存 null 值，导致双倍文件操作，使查询和写入变慢

### 域名类型
声明 | 封装类型
---- | ----
IPv4 | UInt32
IPv6 | FixedString(16)

- Domain 类型不是字符串，不支持自动类型转换
- 调用 IPv4NumToString 或 IPv6NumToString 函数返回 IP 的字符串形式

## 数据库
### 操作
- 创建
    ```sql
    CREATE DATABASE [IF NOT EXISTS] db_name [ENGINE = engine];
    ```

- 查看数据库列表
    ```sql
    SHOW DATABASES;
    ```

- 切换
    ```sql
    USE db_name;
    ```

- 查看当前数据库中的数据表列表
    ```sql
    SHOW TABLES;
    ```

### 引擎
- Ordinary: 默认引擎，无须刻意声明，可以使用任意类型表引擎
- Dictionary: 字典引擎，自动为所有数据字典创建数据表
- Memory: 内存引擎，存放临时数据，数据只停留在内存中
- Lazy: 日志引擎，只能使用 Log 系列的表引擎
- MySQL: MySQL 引擎，自动拉取远端 MySQL 中的数据，并创建 MySQL 表引擎的数据表

## 数据表
### 操作
- 常规建表，默认在 default 数据库中创建
    ```sql
    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name {
        column1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
        column1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
        ...
        columnM Nested(
            column11 [type],
            column22 [type],
            ...
        ),
        ...
    } ENGINE = engine;
    ```

- 复制其他表的结构
    ```sql
    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
        AS [db_name1.]table_name1
        [ENGINE = engine];
    ```

- 通过 SELECT 字句建表，顺带写入 SELECT 子查询的数据
    ```sql
    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
        ENGINE = engine
        AS SELECT ... ;
    ```

- 删表
    ```sql
    DROP TABLE [IF EXISTS] [db_name.]table_name;
    ```

- 增加表字段，默认值补全
    ```sql
    ALTER TABLE [db_name.]table_name ADD COLUMN [IF NOT EXISTS] 
        col_name [type] [default_expr] [AFTER col_name_after];
    ```

- 修改表字段
    ```sql
    ALTER TABLE [db_name.]table_name MODIFY COLUMN [IF EXISTS]
        col_name [type] [default_expr];
    ```

- 修改备注
    ```sql
    ALTER TABLE [db_name.]table_name COMMENT COLUMN [IF EXISTS]
        name 'some comment';
    ```

- 删除字段
    ```sql
    ALTER TABLE [db_name.]table_name DROP COLUMN [IF EXISTS] name;
    ```

- 移动数据表，只能在单节点内移动
    ```sql
    RENAME TABLE [db_name.]tb_name TO [db_name1.]tb_name1,
        [db_name2.]tb_name2 TO [db_name3.]tb_name3,
        ... ;
    ```

- 清空数据表
    ```sql
    TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name;
    ```

### 表字段默认值
- 声明: DEFAULT、MATERIALIZED、ALIAS
- 如果表字段没有明确类型定义，则可根据默认值进行类型推断
- 写入数据时，只有 DEFAULT 类型字段可以 INSERT
- 查询数据时，只有 DEFUALT 类型字段可以 SELECT * 返回
- DEFAULT 和 MATERIALIZED 类型字段可以持久化

## 临时表
### 操作
- 创建
    ```sql
    CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name {
        column1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
        column1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
        ...
        columnM Nested(
            column11 [type],
            column22 [type],
            ...
        ),
        ...
    };
    ```

### 其他
- 临时表只支持 Memory 表引擎，与会话绑定
- 临时表不属于任何数据库，创建时无数据库参数和表引擎参数
- 临时表优先级大于普通表

## 分区表
### 操作
- 创建
    ```sql
    CREATE TABLE partition_v1 (
        ID String,
        URL String,
        EventTime Date
    ) ENGINE = MergeTree()
    PARTITION BY toYYYYMMM(EventTime)
    GROUP BY ID;
    ```

- 查看分区状态
    ```sql
    SELECT table,partition,path from system.parts WHERE table = 'partition_v1'
    ```

- 删除分区
    ```sql
    ALTER TABLE tb_name DROP PARTITION part_expr;
    ```

- 复制分区，前提是两表的结构和分区键相同
    ```sql
    ALTER TABLE table_name1 REPLACE PARTITION part_expr FROM table_name;
    ```

- 重置分区数据
    ```sql
    ALTER TABLE table_name CLEAR COLUMN col_name IN PARTITION part_expr;
    ```

- 卸载分区
    ```sql
    ALTER TABLE table_name DETACH PARTITION part_expr;
    ```

- 装载分区
    ```sql
    ALTER TABLE table_name ATTACH PARTITION part_expr;
    ```

### 其他
- 分区支持删除、替换和重置，只有 MergeTree 系列表引擎支持

## 视图
### 操作
- 创建普通视图
    ```sql
    CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ... ;
    ```

- 创建物化视图
    ```sql
    CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name
        [TO [db.]name] [ENGINE = engine] [POPULATE] AS SELECT ... ;
    # 如果使用了 POPULATE 修饰符，那么在创建视图时会一并导入 SELECT 结果集
    ```

- 查看物化视图列表
    ```sql
    SHOW TABLES
    # 输出前缀是 .inner.
    ```

- 删除视图
    ```sql
    DROP TABLE view_name
    ```

### 其他
- 普通视图只是查询代理
- 物化视图有独立存储，不支持同步删除

## 分布式 DDL
- 使用 ON CLUSTER cluster_name 声明语句
    ```sql
    CREATE TABLE table_name ON CLUSTER cluster_name(
        col1 [type],
        col2 [type],
        ...
    ) ENGINE = engine ... ;
    ```

## 写入数据
- INSERT 语句三种语法
    ```sql
    INSERT INTO [db_name.]table_name [(c1,c2,c3 ...)]
        VALUES (v1,v2,v3 ...), (v4,v5,v6 ...) ... ;

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

    INSERT INTO [db_name.]table_name [(c1,c2,c3 ...)] SELECT ... ;
    ```

## 修改和删除数据
### 操作
- 删除
    ```sql
    ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr;
    ```

- 修改
    ```sql
    ALTER TABLE [db_name.]table_name UPDATE col1 = expr1 [, ...]
        WHERE filter_expr;
    ```

### 其他
- mutation 操作很重，后台执行，语句提交后立即返回，不支持事务，不能回滚
- 通过 system.mutations 系统表查询进度