Create database (CREATE DATABASE) #

CREATE DATABASE [IF NOT EXISTS] db_name [ENGINE=Cnch]

Parameter description:
IF NOT EXISTS: If the db_name database already exists, ByteHouse will not create a new database and:

• If the clause is specified, no exception is raised.
• If the clause is not specified, an exception is thrown.

Example

• Create database using the default engine.

  CREATE DATABASE IF NOT EXISTS test;
  

• Create database using an optional engine; currently, only Cnch is supported.

  CREATE DATABASE IF NOT EXISTS test ENGINE=Cnch;
  

#

Create table (CREATE TABLE) #

Syntax

CREATE TABLE [IF NOT EXISTS] [db.]table_name 
( 
name1 [type1] [NULL|NOT NULL] [DEFAULT|ALIAS expr1 | MATERIALIZED expr1] [COMMENT 'string'][compression_codec][TTL expr1], 
name2 [type2] [NULL|NOT NULL] [DEFAULT|ALIAS expr2 | MATERIALIZED expr1] [COMMENT 'string'] [compression_codec] [TTL expr2], 
... 
 ) ENGINE = CnchMergeTree() 
ORDER BY expr 
[PARTITION BY expr] 
[CLUSTER BY (column, expression, ...) INTO value1 BUCKETS SPLIT_NUMBER value2 WITH_RANGE] 
[PRIMARY KEY expr] 
[UNIQUE KEY expr] 
[SAMPLE BY expr] 
[TTL expr] 
[SETTINGS name=value, ...] 
[COMMENT='COMMENT']

Example

CREATE TABLE IF NOT EXISTS test.createTable( 
id UInt32, 
name String DEFAULT '', 
CONSTRAINT constraint1 CHECK id > 10 
) 
ENGINE=CnchMergeTree 
ORDER BY id

Configuration parameters

NULL or NOT NULL modifiers

In column definitions, the NULL and NOT NULL modifiers after the data type allow or disallow the column to be nullable.
If the data type is not nullable and NULL is specified, the column will be treated as nullable; if NOT NULL is specified, it will not be.
For example, INT NULL is the same as Nullable(INT). If the data type is nullable and NULL or NOT NULL modifiers are specified, an exception will be thrown.

Default (DEFAULT VALUES)

The column description can specify a default value expression in the form of DEFAULT expr, MATERIALIZED expr, or ALIAS expr. Example: URLDomain String DEFAULT domain(URL).
The expression expr is optional. If omitted, the column type must be explicitly specified. The default value for numeric columns is 0, for string columns is '' (empty string), for array columns is [] (empty array), for date columns is 1970-01-01, and for nullable columns is NULL.
The column type for default value columns can be omitted, in which case it will be inferred from the type of expr. For example, the column EventDate DEFAULT toDate(EventTime) will have the type date.
If both the data type and default value expression are specified, an implicit type conversion function will be inserted to convert the expression to the specified type. For example: Hits UInt32 DEFAULT 0 is internally represented as Hits UInt32 DEFAULT toUInt32(0).
The default value expression expr can reference any table column and constant. ByteHouse checks for changes to the table structure will not introduce cycles in expression calculation. For INSERT operations, it checks whether the expression is solvable—that is, all columns required for calculation have been provided.

DEFAULT

Normal default value. If the value for such a column is not specified in the INSERT query, its value will be calculated according to expr.

CREATE TABLE [db.]test 
( 
id UInt64, 
updated_at DateTime DEFAULT now(), 
updated_at_date Date DEFAULT toDate(updated_at) 
) 
ENGINE = CnchMergeTree 
ORDER BY id; 

INSERT INTO test (id) Values (1); 

SELECT * FROM test; 
┌─id─┬──────────updated_at─┬─updated_at_date─┐ 
│1 │ 2023-02-24 17:06:46 │2023-02-24 │ 
└────┴─────────────────────┴─────────────────┘

MATERIALIZED

Materialized expression. The value of such columns will be automatically calculated based on the specified materialized expression when inserting rows. During the INSERT operation, you cannot explicitly specify the values for these columns.
Additionally, this type of default value column is not included in the results of SELECT *. This is to ensure the invariance that the results of SELECT * can always be inserted back into the table using INSERT. This behavior can be disabled by setting asterisk_include_materialized_columns.

CREATE TABLE [db.]test 
( 
id UInt64, 
updated_at DateTime MATERIALIZED now(), 
updated_at_date Date MATERIALIZED toDate(updated_at) 
) 
ENGINE = CnchMergeTree 
ORDER BY id; 

INSERT INTO test Values (1); 

SELECT * FROM test; 
┌─id─┐ 
│1 │ 
└────┘ 

SELECT id, updated_at, updated_at_date FROM test; 
┌─id─┬──────────updated_at─┬─updated_at_date─┐ 
│1 │ 2023-02-24 17:08:08 │2023-02-24 │ 
└────┴─────────────────────┴─────────────────┘ 

SELECT * FROM test SETTINGS asterisk_include_materialized_columns=1; 
┌─id─┬──────────updated_at─┬─updated_at_date─┐ 
│1 │ 2023-02-24 17:08:08 │2023-02-24 │ 
└────┴─────────────────────┴─────────────────┘

ALIAS

Calculated columns (alias). This type of column is not stored in the table, nor can values be inserted into it.
When a SELECT query explicitly references this type of column, the value is calculated from expr at query time. By default, SELECT * excludes ALIAS columns. This behavior can be disabled by setting asterisk_include_alias_columns.
When adding new columns using an ALTER query, old data for these columns will not be written. Instead, when reading old data without new column values, the expression will be calculated on the fly by default. However, if running these expressions requires different columns not specified in the query, those columns will also be additionally read, but only for the relevant data blocks.
If a new column is added to the table and its default expression is later changed, the value used for old data will also change (for those not stored on disk). Caution: When running background merges, the missing column data will be written into these columns in the merged parts.
It is not possible to set default values for elements in nested data structures.

CREATE TABLE [db.]test 
( 
id UInt64, 
size_bytes Int64, 
size String Alias formatReadableSize(size_bytes) 
) 
ENGINE = CnchMergeTree 
ORDER BY id; 

INSERT INTO test Values (1, 4678899); 

SELECT id, size_bytes, size FROM test; 
┌─id─┬─size_bytes─┬─size─────┐ 
│1 │4678899 │ 4.46 MiB │ 
└────┴────────────┴──────────┘ 

SELECT * FROM test SETTINGS asterisk_include_alias_columns=1; 
┌─id─┬─size_bytes─┬─size─────┐ 
│1 │4678899 │ 4.46 MiB │ 
└────┴────────────┴──────────┘

Design partition key (PARTITION BY)

The partition key defines partitions, which are logical data sets within a table divided according to specified rules. Partitions can be created according to any criteria, such as by date. To reduce the amount of data that needs to be operated on, each partition is stored separately. When querying, ByteHouse tries to use the smallest subset of these partitions. The PARTITION BY expr clause is specified when creating the table. The partition key can be any expression of columns in the table. For example, to partition by month, the expression is toYYYYMM(date); or by tuple expression, such as (toMonday(date), EventType) and others.
Caution, the value range calculated by the partition expression in the table should not be too large (recommended not to exceed 10,000). Too many partitions will consume significant memory and incur considerable IO and computational overhead.
Properly designing partition keys can greatly reduce the amount of data scanned during searches. Typically, columns that are most frequently used as conditions in queries and whose value range does not exceed 10,000 (such as Date and others) should be considered as partition keys.
Example 1: Partition key defined by columnExpr

CREATE TABLE test.partitionByClause 
( 
VisitDate Date, 
Hour UInt8, 
ClientID UUID 
) 
ENGINE = CnchMergeTree 
PARTITION BY VisitDate 
ORDER BY Hour;

Example 2: Partition key defined in a tuple of columnExpr

CREATE TABLE test.partitionByClause 
( 
VisitDate Date, 
Hour UInt8, 
ClientID UUID 
) 
ENGINE = CnchMergeTree 
PARTITION BY (VisitDate,Hour) 
ORDER BY Hour;

Designing order keys (ORDER BY)

Order keys can be a tuple of columns or any expression. For example: ORDER BY (OrderID, Date).
If sorting is not required, you can use ORDER BY tuple(), and DataPart will store data in the order it is inserted.
Syntax

[columnExpr] [ASCENDING|ASC|DESCENDING|DESC] [NULLS [FIRST|LAST] [COLLATE STRING_LITERAL]

• [ASCENDING|ASC|DESCENDING|DESC]: Determines the sorting direction. If the direction is not specified, the default is ASC.
• [NULLS [FIRST|LAST]: Determines the sorting order for NaN and NULL.
  • By default or when using the NULLS LAST modifier: values come first, followed by NaN, and finally NULL.
  • When using the NULLS FIRST modifier: NULL comes first, then NaN, and finally other values.
• [COLLATE STRING_LITERAL]: For sorting by String values, you can specify collation rules.
  • Collation rules support LowCardinality, Nullable, Array, and Tuple.
  • When using COLLATE, sorting is always case-insensitive.
  • It is only recommended to use COLLATE for final sorting of a small number of rows, as sorting with COLLATE is less efficient than normal byte-wise sorting.

Example 1: Sorting by multiple orderExpr

CREATE TABLE IF NOT EXISTS test.orderByClause (id UInt32, val UInt32) ENGINE=CnchMergeTree() ORDER BY id; 
INSERT INTO TABLE test.orderByClause VALUES (1,5),(1,15),(2,5),(2,15); 
SELECT * FROM test.orderByClause ORDER BY id ASC, val DESC;

Example 2: Sorting by NULL FIRST

CREATE TABLE IF NOT EXISTS test.orderByClause (id UInt32, val Nullable(UInt32)) ENGINE=CnchMergeTree() ORDER BY id; 
INSERT INTO test.orderByClause VALUES (1,2),(2,NULL),(3,2),(3,3),(3,NULL); 
SELECT * FROM test.orderByClause ORDER BY val DESC NULLS FIRST;

Example 3: Sorting by COLLATE

CREATE TABLE IF NOT EXISTS test.orderByClause (x UInt32, s Nullable(String)) ENGINE=CnchMergeTree ORDER BY x; 
INSERT INTO test.orderByClause VALUES (1,'bca'),(2,NULL),(3,'ABC'),(4,'123a'),(5,'abc'),(6,NULL),(7,'BCA'); 
SELECT * FROM test.orderByClause ORDER BY s ASC COLLATE 'ru';

Designing primary keys (PRIMARY KEY)

By default, there is no need to explicitly specify a primary key. ByteHouse will use the order key as the primary key. When there are special scenarios where the primary key and order key are not consistent, the primary key must be the leftmost prefix of the order key. For example, if the order key is (OrderID, Date), the primary key must be OrderID and cannot be Date.
ByteHouse will build a sparse index on the primary key, with Granule as the unit (in contrast, a dense index creates index information for every row).
If the search condition matches the leftmost prefix of the primary key index, the primary key index can quickly filter out the data granules that may need to be read. Compared to scanning the entire DataPart, this is usually much more efficient.
Additionally, caution is required: PRIMARY KEY does not guarantee uniqueness, so it is possible to insert rows with duplicate primary keys.
Partition (PARTITION BY) and PRIMARY KEY are two different methods for accelerating data queries. When defining them, it is recommended to use different columns for each to cover more query scenarios. For example, the first column in ORDER BY should not be duplicated in PARTITION BY. The following are some considerations for selecting a primary key:

• Whether it is a column commonly used in query conditions
• It should not be the first column in the ORDER BY clause unless it is the partition key.
• The selectivity of this column—for example, columns with very few possible values, such as gender or yes/no, are not recommended for inclusion in the primary key.
• If the current primary key is (a, b), and in most cases the data range corresponding to (a, b) is large (covering multiple Granules), consider appending a commonly used query column to the primary key to filter more data.
• Overly long primary keys negatively impact insert performance and memory consumption, but do not affect query performance.

Example

CREATE TABLE IF NOT EXISTS test.sampleByClause 
( 
id UInt32 
) 
ENGINE=CnchMergeTree 
ORDER BY id 
SAMPLE BY id;

UNIQUE KEY

PRIMARY KEY does not guarantee deduplication. If deduplication by unique key is required, a unique key index must be set when creating the table. After setting a unique key, ByteHouse provides upsert update write semantics, allowing efficient updates of data rows based on the unique key, or, during upsert, by setting the virtual column delete_flag=1, you can delete the specified key. Queries automatically return the latest value for each unique key. For details, refer to ByteHouse best practices for unique key tables.
A unique key can be a tuple of columns or any expression, such as UNIQUE KEY (product_id, sipHash64(city)).
When querying by unique key, the unique key index is used to filter data and accelerate queries. Therefore, the primary key can usually be set to columns different from the unique key to cover more query conditions. However, to use partial column update functionality, the unique key must be the leftmost prefix of the sort key.
Example 1: Single-column Unique Key

CREATE TABLE test.uniqueKeyClause 
( 
`event_date` Date, 
`order_id` UInt64, 
`state` UInt32, 
`amount` UInt64 
) 
ENGINE = CnchMergeTree 
PARTITION BY event_date 
ORDER BY state 
UNIQUE KEY order_id; 

-- rows with the same key will replace previous rows 
INSERT INTO test.uniqueKeyClause VALUES 
('2021-03-01', 1001, 1, 100), 
('2021-03-01', 1002, 1, 200), 
('2021-03-01', 1001, 2, 100), 
('2021-03-02', 1001, 1, 400); 

SELECT * FROM test.uniqueKeyClause ORDER BY event_date, order_id; 
┌─event_date─┬─order_id─┬─state─┬─amount─┐ 
│ 2021-03-01 │ 1001 │ 2 │100 │ 
│ 2021-03-01 │ 1002 │ 1 │200 │ 
│ 2021-03-02 │ 1001 │ 1 │400 │ 
└────────────┴──────────┴───────┴────────┘ 

INSERT INTO test.uniqueKeyClause VALUES 
('2021-03-01', 1002, 2, 200), 
('2021-03-02', 1001, 2, 400), 
('2021-03-02', 1002, 1, 300); 

SELECT * FROM test.uniqueKeyClause ORDER BY event_date, order_id; 
┌─event_date─┬─order_id─┬─state─┬─amount─┐ 
│ 2021-03-01 │ 1001 │ 2 │100 │ 
│ 2021-03-01 │ 1002 │ 2 │200 │ 
│ 2021-03-02 │ 1001 │ 2 │400 │ 
│ 2021-03-02 │ 1002 │ 1 │300 │ 
└────────────┴──────────┴───────┴────────┘

Example 2: Multi-column Unique Key

CREATE TABLE test.uniqueKeyClause 
( 
`event_date` Date, 
`region` UInt64, 
`state` String, 
`amount` UInt64 
) 
ENGINE = CnchMergeTree 
PARTITION BY event_date 
ORDER BY state 
UNIQUE KEY (region, state);

CONSTRAINT

Bytehouse supports CONSTRAINT syntactically, but except for NOT NULL, does not enforce these constraints.

• Uniqueness constraint (UNIQUE): To ensure column uniqueness in your business logic, use UNIQUE KEY instead of UNIQUE CONSTRAINT.
• Foreign key constraint (FOREIGN KEY): Foreign key constraints are used only for query optimization and do not perform data integrity checks.

Bucketing (Cluster By)

Bucketing is commonly used in the following scenarios:

1. General scenario: Uneven data distribution
   • Definition and principle: When partitions cannot achieve even data distribution, bucketing fields can be used. Bucketing fields ensure that a column's data is evenly distributed across each node in the cluster. This maximizes cluster query performance. Proper configuration of partition fields also helps address data skew issues and ensures more balanced data distribution.
   • Field limitation: Nullable is not supported.
   • Configuration recommendation: Select fields that frequently appear in the grouping criteria.
   • After the table is created successfully, the column type of this field cannot be modified.
2. Specific scenario: Slow duplicate data deletion
   • Definition and principle: When a Unique Key is set and there is too much data in a single partition (for example, more than 100 million rows), data ingestion speed will be affected. This is because a lock must be acquired to perform duplicate data deletion. In this case, you can divide partitions into buckets to improve data ingestion speed.
   • Field limitation: Nullable is not supported.
   • Configuration recommendation: The Bucket Key must be the same as the Unique Key. (Each bucket should contain fewer than 10 million rows)

Bucketing application example
Suppose a ByteHouse subscription user has enabled six compute nodes. Due to the large amount of data in a single partition, exceeding 200 million records, the application frequently performs aggregation and join operations based on the c1 and c2 fields. Therefore, it was decided to optimize using a bucketed table. The design options for the bucketed table are as follows:

• Bucketing key (CLUSTER Key) selection: Select the c1 and c2 columns as the bucketing keys.
• Number of buckets: Twice the number of nodes, that is, 12.

-- Create table with bucketing 
create or replace table table_01 (c1 timestamp, c2 string, c3 number) cluster by (to_date(c1), c2) INTO 12 BUCKETS; 

-- Add bucket to existing data 
ALTER TABLE t CLUSTER BY (column, expression, ...) INTO 64 BUCKETS 

-- Add bucket to cluster by multiple columns 
ALTER TABLE t CLUSTER BY sipHash(a,b,c) INTO 64 BUCKETS 

-- Add or change the cluster properties 
ALTER TABLE t MODIFY CLUSTER BY (column, expression, ...) INTO 64 BUCKETS

Sampling

The expression used for sampling. This configuration is optional.
To use a sampling expression, the primary key must contain this expression. For example: SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)).

TTL for columns and tables

A list of rules specifying the retention duration of rows and defining the logic for moving data fragments across disks and volumes. This configuration is optional. The expression must contain at least one column of Date or DateTime type, for example: TTL date + INTERVAL 1 DAY.

CREATE TABLE test.ttlClause 
( 
VisitDate Date, 
Hour UInt8, 
ClientID UUID 
) 
ENGINE = CnchMergeTree 
ORDER BY Hour 
PARTITION BY VisitDate 
TTL VisitDate + INTERVAL 1 DAY；

To implement TTL based on a string-type field, use the toDate function during table creation to first convert it to Date or DateTime type, and then set TTL using the converted date-time column. For usage of the toDate function, see toDate

CREATE TABLE test.ttlClause ( 
start_date STRING, -- String-type date field 
 Hour UInt8, 
 ClientID UUID 
) ENGINE = CnchMergeTree 
ORDER BY Hour 
TTL toDate(start_date) + INTERVAL 1 DAY; -- Set TTL after converting the string to Date type

Compression

The compression_codec field can be used for codec configuration. This configuration is optional, and the default is LZ4.
Support project general-purpose encoding and specific encoding. General-purpose codecs are similar to default codecs (LZ4, ZTSD) and their modified versions. Specific codecs are designed to leverage particular characteristics of the data for more effective compression.
General encoding

• NONE: No compression.
• LZ4: Default value, a lossless and ultra-fast compression algorithm.
• LZ4HC[(level)]: LZ4HC high compression ratio algorithm with configurable levels. The default level is 9, supported values are [1 ~ 12], recommended values are [4 ~ 9].
• ZSTD[(level)]: ZSTD compression algorithm with configurable levels. The default level is 1, supported values are [1 ~ 22].

Specific encoding algorithms

• Delta(delta_bytes): Delta encoding, which retains the first value and stores the difference between every two subsequent values. The default is sizeof(type). Optional values are 1, 2, 4, or 8. If other values are specified, it is treated as 1.

Multiple codecs

• Use multiple codecs as described above. Compression will be performed in the order declared by the codecs, and decompression will be performed in the reverse order.

Reference example:

CREATE TABLE codec_example 
(date Date CODEC(Delta, ZSTD), 
ts DateTime CODEC(LZ4HC), 
float_value Float32 CODEC(NONE), 
double_value Float64 CODEC(LZ4HC(9)) 
) 
ENGINE = CnchMergeTree 
PARTITION BY tuple() 
ORDER BY date

Table engine

The table engine is the type of the table and determines:

• The organization and storage method of the data
• The indexing method and index type
• Which queries are supported and how they are supported
• Some other specific functions and configurations

The most commonly used table engine in the ByteHouse cloud data warehouse edition is CnchMergeTree, in addition to other special types of table engines including Hive external tables, Kafka tables, and others. This article focuses on the principles of the CnchMergeTree table engine.

CnchMergeTree table engine

CnchMergeTree is the most commonly used table engine. Its core concept is similar to LSM-Tree: data is partitioned by partition key (partition by), then stored in order by sort key (order by). Main features include:

• Logical partitions

If a partition key is specified, data is divided into different logical datasets (logical partitions, Partition) according to the partition key.
Each logical partition can contain zero or more DataParts. If the query conditions can prune partitions, this usually accelerates the query. If no partition key is specified, all data is stored in a single logical partition.

• DataPart

Data within a DataPart is sorted by the sort key. Each DataPart also has a min/max index to accelerate partition selection.

• Granule

Each DataPart is logically divided into granules. The default granule is 8192 rows (determined by the table's index_granularity configuration). A granule is the smallest indivisible dataset for data query in ByteHouse. The first row of each granule is marked by the primary key value of that row. ByteHouse creates an index file for each DataPart to store these marks. For every column, whether or not it contains the primary key, ByteHouse stores similar marks. These marks allow you to directly locate data in the column file. Granule serves as the indexing goal for ByteHouse sparse indexes and is also the unit for data scanning in memory.

• Background merge

Background tasks periodically merge DataParts within the same partition and maintain order by the sort key. Background merge reduces the number of parts for more efficient storage and improves query performance.

MySQL compatibility

ByteHouse CDW 2.1 is compatible with most MySQL DDL and can directly execute MySQL create table statements in ByteHouse. Please refer to the following example:
MySQL

CREATE TABLE `table1` ( 
`uid` bigint NOT NULL AUTO_INCREMENT, 
`phone` varchar(255) DEFAULT NULL, 
`no1` bigint DEFAULT NULL, 
`appleid` varchar(255) DEFAULT NULL, 
PRIMARY KEY (`uid`) USING BTREE, 
UNIQUE KEY `no1` (`no1`) USING BTREE, 
KEY `idx_apple_id` (`appleid`) USING BTREE, 
KEY `phone` (`phone`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=60937910 DEFAULT CHARSET=utf8mb3;

ByteHouse

CREATE TABLE table1 ( 
`uid` Int64 NOT NULL, 
`phone` String NULL DEFAULT NULL, 
`no1` Int64 NULL DEFAULT NULL, 
`appleid` String NULL DEFAULT NULL 
) 
ENGINE = CnchMergeTree 
ORDER BY tuple(uid) 
UNIQUE KEY tuple(uid) 
SETTINGS partition_level_unique_keys = 0, storage_policy = 'cnch_default_hdfs', allow_nullable_key = 1, storage_dialect_type = 'MYSQL', index_granularity = 8192

DDL conversion logic

The following conversion logic can be observed

| | | | | \

CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = CnchMergeTree ORDER BY expr [PARTITION BY expr] AS SELECT ...

CREATETABLE DB2.unique_user_events ( 
user_id UInt32, 
event_time DateTime, 
event_type String, 
event_data String 
) ENGINE = CnchMergeTree() 
ORDER BY user_id 
PRIMARY KEY user_id 
UNIQUE KEY event_time; 

INSERT into DB2.unique_user_events(user_id,event_time,event_type,event_data) values(10112,'2024-08-28 05:11:58','ERROR','this is a bug'); 
INSERT into DB2.unique_user_events(user_id,event_time,event_type) values(10112,'2024-07-28 05:11:58','ERROR'); 
INSERT into DB2.unique_user_events(user_id,event_time,event_data) values(10112,'2024-07-28 05:11:58','this is a bug'); 

CREATE TABLE IF NOT EXISTS DB2.table_name2(user_id UInt32,event_time DateTime,event_data String) 
ENGINE = CnchMergeTree 
ORDER BY user_id 
AS 
select user_id,event_time,event_data from DB2.unique_user_events limit 100; 

select * from DB2.table_name2;

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]view_name 
[DEFINER = { `user` | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] 
AS SELECT ... 
[COMMENT 'comment']

SELECT a, b, c FROM view_name

# Method 1 
ALTER TABLE [db.]view_name 
MODIFY SQL SECURITY  { DEFINER | INVOKER } 
[DEFINER = { user | CURRENT_USER }] 

# Method 2 
ALTER TABLE [db.]view_name 
MODIFY [DEFINER = {user | CURRENT_USER }] 
SQL SECURITY { DEFINER | INVOKER }

ViewCreator: Not enough privileges. To execute this query it's necessary to have grant SET DEFINER ON ViewAdmin

GRANT SET DEFINER ON ViewCreator TO ViewCreator;

ViewCreator: Not enough privileges. To execute this query it's necessary to have grant ALTER VIEW MODIFY SQL SECURITY ON testdb.test_view3

GRANT ALTER VIEW ON db_name.view_name TO ViewCreator;

ViewUser: Not enough privileges. To execute this query it's necessary to have grant SELECT(tag, value) ON testdb.test_table

CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name 
( 
key1 type1[DEFAULT|EXPRESSION expr1] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID], 
key2 type2[DEFAULT|EXPRESSION expr2] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID], 
attr1 type2 [DEFAULT|EXPRESSION expr3], 
attr2 type2 [DEFAULT|EXPRESSION expr4] 
) 
PRIMARY KEY key1, key2 
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN])) 
LAYOUT(LAYOUT_NAME([param_name param_value])) 
LIFETIME({MIN min_val MAX max_val | max_val})

CREATE TABLE t(name1 type1, name2 type2, ...) 
engine=CnchMergeTree() 
PARTITION BY ... 
CLUSTER BY EXPRESSION expr INTO 4 BUCKETS 
ORDER BY ... 
[UNIQUE KEY ...] 
SETTINGS enable_recluster_multiple_parts=1, enable_scale_bucket_optimization= 1