DDL 模板--ByteHouse 企业版-火山引擎

文档中心

立即注册

导航

DDL 模板

最近更新时间：2024.11.22 11:39:29首次发布时间：2024.10.30 17:14:54

本文列举 ByteHouse 企业版支持的 DDL 模板，您可以在新建库表时参考 DDL 模板快速建表。

库引擎

Atomic [Standard]

描述：ByteHouse 的默认库引擎。

CREATE DATABASE [IF NOT EXISTS] <db_name> ON CLUSTER <cluster_name>
      [ ENGINE = Atomic];

MaterializeMySQL [Experimental]

描述：支持实时同步 MySQL 库到 ByteHouse 中。

CREATE DATABASE [IF NOT EXISTS] <db_name> ON CLUSTER <cluster_name>
ENGINE = MaterializeMySQL('host:port', ['database'], 'user', 'password') 
[SETTINGS 
    [shard_mode=1],...] -- After switching on shard_mode, data will be stored in different shards;
[TABLE OVERRIDE <table_name> (
    [COLUMNS (
        [col_name [datatype], ...]
    )]
    [ORDER BY expr]
    [PARTITION BY expr]
    [TTL expr]
), ...]

表引擎

HaMergeTree [Standard]

描述：ByteHouse 的默认表引擎，经过了性能优化，支持高性能导入与查询，支持多副本高可用。

非通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name> ON CLUSTER <cluster_name>
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = HaMergeTree('/clickhouse/tables/{shard}/{database}/local_table_name', '{replica}')
ORDER BY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    [index_granularity=8192],...] -- don't change this setting in normal occasion; Only set index_granularity to 128 if this table is usually used to query a single row。

CREATE TABLE [db.]<table_name> ON CLUSTER <cluster_name>
    AS <db>.<local_table_name>
    ENGINE = Distributed(<cluster_name>, <db>, <local_table_name>, <sharding_key>);

通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<table_name>
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = HaMergeTree('/clickhouse/tables/{shard}/{database}/table_name', '{replica}')
ORDER BY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    enable_common_table_mode = 1,
    cluster_name = <cluster_name>,
    sharding_key = <sharding_key>
    [index_granularity=8192],...] -- don't change this setting in normal occasion; Only set index_granularity to 128 if this table is usually used to query a single row。

HaUniqueMergeTree [Standard]

描述：ByteHouse 的高效去重表引擎，支持实时去重（Merge-on-write ）、实时删除数据、部分列更新。支持支持多副本高可用；

非通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name> ON CLUSTER <cluster_name>
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = HaUniqueMergeTree('/clickhouse/tables/{shard}/{database}/local_table_name', '{replica}'[，version_column])
ORDER BY expr
UNIQUE KEY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    [index_granularity=8192],...] -- The default value is 8192. Only set index_granularity to 128 if this table is usually used to query a single row. Otherwise, don't change this setting in normal occasion;

CREATE TABLE [db.]<table_name> ON CLUSTER <cluster_name>
    AS <db>.<local_table_name>
    ENGINE = Distributed(<cluster_name>, <db>, <local_table_name>, <sharding_key>)
    SETTINGS remote_table_is_ha_unique = 1 [,...];

通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<table_name>
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = HaUniqueMergeTree('/clickhouse/tables/{shard}/{database}/table_name', '{replica}'[，version_column])
ORDER BY expr
UNIQUE KEY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    enable_common_table_mode = 1,
    cluster_name = <cluster_name>,
    sharding_key = <sharding_key>
    [index_granularity=8192],...] -- The default value is 8192. Only set index_granularity to 128 if this table is usually used to query a single row. Otherwise, don't change this setting in normal occasion;

HaAggregatingMergeTree [Standard]

描述：聚合表引擎，用于通过物化视图将数据聚合存储到此类表后进行高性能查询。

非通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name> ON CLUSTER <cluster_name>
(
    name1 [type1],
    name2 [type2], -- Type Allows to be AggregateFunction(<agg_funcition_name>, <type>)
    ...
) ENGINE = HaAggregatingMergeTree('/clickhouse/tables/{shard}/{database}/local_table_name', '{replica}')
ORDER BY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    [index_granularity=8192],...] -- The default value is 8192. Only set index_granularity to 128 if this table is usually used to query a single row. Otherwise, don't change this setting in normal occasion;

CREATE TABLE [db.]<table_name> ON CLUSTER <cluster_name>
    AS <db>.<local_table_name>
    ENGINE = Distributed(<cluster_name>, <db>, <local_table_name>, <sharding_key>);

通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name>
(
    name1 [type1],
    name2 [type2], -- Type Allows to be AggregateFunction(<agg_funcition_name>, <type>)
    ...
) ENGINE = HaAggregatingMergeTree('/clickhouse/tables/{shard}/{database}/local_table_name', '{replica}')
ORDER BY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    [index_granularity=8192],...] -- The default value is 8192. Only set index_granularity to 128 if this table is usually used to query a single row. Otherwise, don't change this setting in normal occasion;

CREATE TABLE [db.]<table_name>
    AS <db>.<local_table_name>
    ENGINE = Distributed(<cluster_name>, <db>, <local_table_name>, <sharding_key>);

MergeTree [Community]

描述：社区提供的基础表引擎，不支持多副本高可用。

非通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name> ON CLUSTER <cluster_name>
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MergeTree
ORDER BY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    [index_granularity=8192],...] -- don't change this setting in normal occasion; Only set index_granularity to 128 if this table is usually used to query a single row。

CREATE TABLE [db.]<table_name>
    ON CLUSTER <cluster_name>
    AS <db>.<local_table_name>
    ENGINE = Distributed(<cluster_name>, <db>, <local_table_name>, <sharding_key>);

通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name>
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MergeTree
ORDER BY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    [index_granularity=8192],...] -- don't change this setting in normal occasion; Only set index_granularity to 128 if this table is usually used to query a single row。

CREATE TABLE [db.]<table_name>
    AS <db>.<local_table_name>
    ENGINE = Distributed(<cluster_name>, <db>, <local_table_name>, <sharding_key>);

ReplicatedMergeTree [Community]

描述：社区提供的表引擎，支持多副本高可用。

非通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name> ON CLUSTER <cluster_name>
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/local_table_name', '{replica}')
ORDER BY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    [index_granularity=8192],...] -- The default value is 8192. Only set index_granularity to 128 if this table is usually used to query a single row. Otherwise, don't change this setting in normal occasion;


CREATE TABLE [db.]<table_name> ON CLUSTER <cluster_name>
    AS <db>.<local_table_name>
    ENGINE = Distributed(<cluster_name>, <db>, <local_table_name>, <sharding_key>);

通用建表模式

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name>
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/local_table_name', '{replica}')
ORDER BY expr
[PARTITION BY expr]
[TTL expr]
[SETTINGS
    [index_granularity=8192],...] -- The default value is 8192. Only set index_granularity to 128 if this table is usually used to query a single row. Otherwise, don't change this setting in normal occasion;

CREATE TABLE [db.]<table_name>
    AS <db>.<local_table_name>
    ENGINE = Distributed(<cluster_name>, <db>, <local_table_name>, <sharding_key>);

外表引擎

以下引擎，通用建表模式均只比通用建表模式少了 on cluster，以下均以非通用举例。

MySQL [Community]

描述：用于连接 MySQL 表的外表引擎。

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<table_name> ON CLUSTER <cluster_name> 
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'
    [, replace_query, -- Flag that converts INSERT INTO queries to REPLACE INTO. If replace_query=1, the query is substituted.
    'on_duplicate_clause']) --The ON DUPLICATE KEY on_duplicate_clause expression that is added to the INSERT query.
SETTINGS
    [connection_pool_size=16, ]
    [connection_max_tries=3, ]
    [connection_auto_close=true ]
;

HDFS [Community]

描述：用于连接 HDFS 文件的外表引擎。

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<table_name> ON CLUSTER <cluster_name> 
(
    name1 [type1],
    name2 [type2], 
    ...
) 
HDFS(URI, format) -- Supported format: JSON,CSV,Parquet,ORC,etc。

S3 [Community]

描述：用于连接兼容 S3 协议的对象存储（如ToS）文件的外表引擎。

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<local_table_name> ON CLUSTER <cluster_name> 
(
    name1 [type1],
    name2 [type2], 
    ...
) 
ENGINE = S3(path, [aws_access_key_id, aws_secret_access_key,] 
    format, --Supported values: JSON,CSV,Parquet,ORC,etc。
    [compression]) --Supported values: none, gzip/gz, brotli/br, xz/LZMA, zstd/zst

HiveCluster [Experimental]

描述：用于连接 Hive 的外表引擎。

CREATE TABLE [IF NOT EXISTS] [<db_name>.]<table_name> ON CLUSTER <cluster_name> 
(
    name1 [type1],
    name2 [type2], 
    ...
) 
ENGINE = HiveCluster(cluster, path, hive_database, hive_table);
PARTITION BY expr
SETTINGS [
    endpoint = <s3_endpoint>, 
    ak_id = 'access_key', 
    ak_secret = 'secret_key'
    -- for hive table on S3]
;

视图&物化视图

View [Standard]

描述：查询时实时执行定义视图的 SQL 语句，不存储数据，并且可以基于视图快速发起子查询。

CREATE VIEW [IF NOT EXISTS] [<db_name>.]<view_name> ON CLUSTER <cluster_name> 
AS SELECT ...;

MaterializedView [Standard]

描述：用于将查询结果数据存储下来，在之后查询时避免对数据进行再次的计算与聚合。因此能够以空间换时间的方式加速查询，从而达到查询加速和简化查询逻辑的目的。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [<db_name>.]<view_name> ON CLUSTER <cluster_name> 
    [TO [db.]target_table_name]
    [ENGINE = engine]
    [ORDER BY order_by_key]
    [PARTITION BY partition_key]
    [TTL ttl_expr]
    [POPULATE]
AS SELECT ...;