clickhouse - 第三章：内置对象之第二节：table表操作 - 创建表与删除表

45 阅读 0 评论 30 点赞

我是靠谱客的博主悦耳小蚂蚁，最近开发中收集的这篇文章主要介绍clickhouse - 第三章：内置对象之第二节：table表操作 - 创建表与删除表，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.创建空表

2.以表建表

3.删除表

1.创建空表

(1)MergeTree engine

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...]

Required parameters:

ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。
ORDER BY — 排序键。

可以是一组列的元组或任意的表达式。例如: ORDER BY (CounterID, EventDate) 。如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。如果不需要排序，可以使用 ORDER BY tuple().

Options parameters:

PARTITION BY — 分区键，可选项。

要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。
PRIMARY KEY - 如果要选择与排序键不同的主键，在这里指定，可选项。

默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。
SAMPLE BY - 用于抽样的表达式，可选项。

如果要用抽样表达式，主键中必须包含这个表达式。例如：
SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。
TTL - 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。

-- 案例
 CREATE TABLE test.jt_return
(
    `sales_channel_id` Int32,
    `start_date` Nullable(Date),
    `end_date` Nullable(Date),
    `return_date` Nullable(DateTime),
    `amazon_order_id` Nullable(String),
    `sku` Nullable(String),
    `asin` Nullable(String),
    `fnsku` Nullable(String),
    `product_name` Nullable(String),
    `quantity` Nullable(Int32),
    `fulfillment_center_id` Nullable(String),
    `detailed_disposition` Nullable(String),
    `reason` Nullable(String),
    `status` Nullable(String),
    `lpn` Nullable(String),
    `customer_comments` Nullable(String),
    `uds_load_date` Nullable(Date),
    INDEX index_start_date start_date TYPE minmax GRANULARITY 8192,
    INDEX index_end_date end_date TYPE minmax GRANULARITY 8192,
    INDEX index_sku sku TYPE minmax GRANULARITY 8192,
    INDEX index_asin asin TYPE minmax GRANULARITY 8192,
    INDEX index_amazon_order_id amazon_order_id TYPE minmax GRANULARITY 8192,
    INDEX index_sales_channel_id sales_channel_id TYPE minmax GRANULARITY 8192   
)
ENGINE = MergeTree()
ORDER BY tuple()
;

(2)ReplacingMergeTree

注意：该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

ver — 版本列。类型为 UInt*, Date 或 DateTime。可选参数。

在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：
- 如果 ver 列未指定，保留最后一条。
- 如果 ver 列已指定，保留 ver 值最大的版本。

-- 案例：
 CREATE TABLE test.tc_product
(
    `sales_channel_id` Int32,
    `request_date` Nullable(DateTime),
    `asin` Nullable(String),
    `product_name` Nullable(String),
    `marketplace` Nullable(String),
    `brand` Nullable(String),
    `uds_load_date` Nullable(Date),
    INDEX index_asin asin TYPE minmax GRANULARITY 8192
)
ENGINE = ReplacingMergeTree()
order by tuple()
;

2.以表建表

（1）仅复制表结构

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

-- 不指定engine，默认使用被复制的表engine类型
CREATE TABLE test.tc_return_02 AS test.jt_return;

（2）复制表并复制数据

-- 语法
CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...
-- 案例
CREATE TABLE test.tc_return ENGINE = MergeTree ORDER BY tuple() AS 
select * from test.jt_return