CREATE

You can use the CREATE statement in ClickHouse® to create various entities, such as databases, tables, views, users, roles, and functions.

Database

CREATE DATABASE creates a new database in the cluster.

Syntax

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
[ENGINE = engine(...)] 
[COMMENT 'Comment about the database']

Clause	Description
`IF NOT EXISTS`	Optional. Prevents ClickHouse® from raising an error if a database with the same name already exists.
`ON CLUSTER`	Optional. Instructs ClickHouse® to create the database on all hosts of the cluster.
`ENGINE`	Optional. Not to be confused with table engines. Specifies the database engine to use. If not provided, the default Atomic database engine is used.
`COMMENT`	Optional. Adds a comment about the database.

Example

CREATE DATABASE IF NOT EXISTS website_data ON CLUSTER default

Table

CREATE TABLE creates a new table in the cluster.

Syntax

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
  name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [COMMENT 'Comment about the column'] [TTL expr1],
  name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [COMMENT 'Comment about the column'] [TTL expr2],
  ...
)
ENGINE = engine
[PARTITION BY expr]
[ORDER BY expr]
PRIMARY KEY(expr1[, expr2,...]);
COMMENT 'Comment about the table'

Clause	Description
`IF NOT EXISTS`	Optional. Prevents ClickHouse® from raising an error if a table with the same name already exists.
`ON CLUSTER`	Optional. Instructs ClickHouse® to create the table on all hosts of the cluster.
`ENGINE`	Optional. Specifies the table engine to use. If not provided, the default Atomic database engine is used.
`COMMENT`	Optional. Adds a comment about the table or column.
`TTL`	Optional. Time interval after which rows or columns are moved, deleted, or rolled.

Example

CREATE TABLE website_data.hits ON CLUSTER default (
  Hit_ID Int32, 
  Date Date, 
  Time_Spent Float32, 
  Cookie_Enabled Int32, 
)
ENGINE = ReplicatedMergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (Hit_ID, Date)

User

CREATE USER creates a new user in the cluster. A user is a special account that allows you to interact with databases in the cluster. You can also create users in the console.

Syntax

CREATE USER [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1]
        [, name2 [ON CLUSTER cluster_name2] ...]
[NOT IDENTIFIED | IDENTIFIED {[WITH {no_password | plaintext_password | sha256_password | sha256_hash | double_sha1_password | double_sha1_hash}] BY {'password' | 'hash'}} | {WITH ldap SERVER 'server_name'} | {WITH kerberos [REALM 'realm']} | {WITH ssl_certificate CN 'common_name'} | {WITH ssh_key BY KEY 'public_key' TYPE 'ssh-rsa|...'} | {WITH http SERVER 'server_name' [SCHEME 'Basic']}]
[HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
[VALID UNTIL datetime]
[IN access_storage_type]
[DEFAULT ROLE role [,...]]
[DEFAULT DATABASE database | NONE]
[GRANTEES {user | role | ANY | NONE} [,...] [EXCEPT {user | role} [,...]]]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY | WRITABLE] | PROFILE 'profile_name'] [,...]

Clause	Description
`IF NOT EXISTS`	Optional. Prevents ClickHouse® from raising an error if a user with the same name already exists.
`ON CLUSTER`	Optional. Instructs ClickHouse® to create the user on all hosts of the cluster.
`IDENTIFIED`	Optional. Specifies how the user is authenticated. Passwords can be hashed using various algorithms, such as SHA256 or double SHA1.
`DEFAULT ROLE`	Optional. Specifies the default roles that are assigned to the user upon login.
`SETTINGS`	Optional. Sets specific settings for the user.

Example

CREATE USER chelsea IDENTIFIED WITH sha256_password BY '<strong_password>'

Role

CREATE ROLE creates a new role in the cluster. A role is a collection of permissions that can be assigned to one or several users in the cluster. You can also create a role in the console.

Syntax

CREATE ROLE [IF NOT EXISTS | OR REPLACE] name1 [ON CLUSTER cluster_name1] [, name2 [ON CLUSTER cluster_name2] ...]
[IN access_storage_type]
[SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [CONST|READONLY|WRITABLE|CHANGEABLE_IN_READONLY] | PROFILE 'profile_name'] [,...]

Clause	Description
`IF NOT EXISTS`	Optional. Prevents ClickHouse® from raising an error if a role with the same name already exists.
`ON CLUSTER`	Optional. Instructs ClickHouse® to create the role on all hosts of the cluster.
`SETTINGS`	Optional. Sets specific settings for the role.

Example

CREATE ROLE IF NOT EXISTS analyst

Function

CREATE FUNCTION creates a user-defined function (UDF) in the cluster. Similar to built-in functions, these functions usually contain reusable logic that can be used in queries. ClickHouse® doesn’t support recursive functions.

Syntax

CREATE FUNCTION name [ON CLUSTER cluster] AS (parameters) -> expression

Clause	Description
`ON CLUSTER`	Optional. Instructs ClickHouse® to create the function on all hosts of the cluster.
`parameters`	List of parameters. Must contain all the variables that the function uses.
`expression`	Function body as an expression.

Example

CREATE FUNCTION celcius_to_fahrenheit ON CLUSTER default AS (celcius) -> celcius * 9 / 5 + 32

Overview

Data types