从零开始使用ClickHouse

前提条件

已安装客户端，例如安装目录为“/opt/Bigdata/client”。以下操作的客户端目录只是举例，请根据实际安装目录修改。在使用客户端前，需要先下载并更新客户端配置文件，确认Manager的主管理节点后才能使用客户端。

操作步骤

1. 以客户端安装用户，登录安装客户端的节点。
2. 执行以下命令，切换到客户端安装目录。

   cd /opt/Bigdata/client

3. 执行以下命令配置环境变量。

   source bigdata_env

4. 执行ClickHouse组件的客户端命令。

   执行clickhouse -h，查看ClickHouse组件命令帮助。

   回显信息如下：

   Use one of the following commands:
   clickhouse local [args] 
   clickhouse client [args] 
   clickhouse benchmark [args] 
   clickhouse server [args] 
   clickhouse performance-test [args] 
   clickhouse extract-from-config [args] 
   clickhouse compressor [args] 
   clickhouse format [args] 
   clickhouse copier [args] 
   clickhouse obfuscator [args]
   ...

   详细命令使用请参考：https://clickhouse.tech/docs/zh/operations/。

   使用clickhouse client命令连接ClickHouse服务端时，相关参数使用说明如下表：

   表1 clickhouse client命令行参数说明

   参数名	参数说明
   --host	服务端的host名称，默认是localhost。您可以选择使用ClickHouse实例所在节点主机名或者IP地址。
   --port	连接的端口。默认值为9000。
   --user	用户名。 默认值：default。
   --password	密码。 默认值：空字符串。
   --query	使用非交互模式查询。
   --database	默认当前操作的数据库。默认值：服务端默认的配置（默认是default）。
   --multiline	如果指定，允许多行语句查询（Enter仅代表换行，不代表查询语句完结）。
   --multiquery	如果指定，允许处理用;号分隔的多个查询，只在非交互模式下生效。
   --format	使用指定的默认格式输出结果。
   --vertical	如果指定，默认情况下使用垂直格式输出结果。在这种格式中，每个值都在单独的行上打印，适用显示宽表的场景。
   --time	如果指定，非交互模式下会打印查询执行的时间到stderr中。
   --stacktrace	如果指定，如果出现异常，会打印堆栈跟踪信息。
   --config-file	配置文件的名称。
   --secure	如果指定，将通过ssl安全模式连接到服务器。
   --history_file	存放命令历史的文件的路径。
   --param_<name>	带有参数的查询，并将值从客户端传递给服务器。具体用法详见https://clickhouse.tech/docs/zh/interfaces/cli/#cli-queries-with-parameters。

   使用ClickHouse客户端命令：

   clickhouse client --host ClickHouse的实例IP --user 登录名 --password 密码 --port ClickHouse的端口号

   

   登录名：默认为default。不携带--user参数时则使用default用户登录。

   密码： 默认为空字符串，不需要携带--password参数。

   ClickHouse的端口号：默认使用9000端口号访问。

   ClickHouse实例IP获取方式：在集群详情页面，选择“组件管理 > ClickHouse > 实例”，获取ClickHouse的IP地址。

ClickHouse数据库基本操作

创建数据库：

• 基本语法

  CREATE DATABASE [IF NOT EXISTS] database_name [ON CLUSTER ClickHouse集群名]

  

  ON CLUSTER ClickHouse集群名的语法，使得该DDL语句执行一次即可在集群中所有实例上都执行。集群名信息可以使用以下语句的cluster字段获取：

  select cluster,shard_num,replica_num,host_name from system.clusters;

• 使用示例

  --创建数据库名为test的数据库
  CREATE DATABASE test ON CLUSTER default_cluster;
  --创建成功后，通过查询命令验证
  show databases;
  ┌─name───┐
  │ default    │
  │ system     │
  │ test       │
  └──────┘

创建表：

• 基本语法
  • 方法一：在指定的“database_name”数据库中创建一个名为“table_name ”的表。

    如果建表语句中没有包含“database_name”，则默认使用客户端登录时选择的数据库作为数据库名称。

    CREATE TABLE [IF NOT EXISTS] [database_name.]table_name [ON CLUSTER ClickHouse集群名]

    (

    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],

    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

    ...

    ) ENGINE = engine_name()

    [PARTITION BY expr_list]

    [ORDER BY expr_list]

  • 方法二：创建一个与database_name2.table_name2具有相同结构的表，同时可以对其指定不同的表引擎声明。

    如果没有表引擎声明，则创建的表将与database_name2.table_name2使用相同的表引擎。

    CREATE TABLE [IF NOT EXISTS] [database_name.]table_name AS [database_name2.]table_name2 [ENGINE = engine_name]

  • 方法三：使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表，并使用SELECT子句的结果填充它。

    CREATE TABLE [IF NOT EXISTS] [database_name.]table_name ENGINE = engine_name AS SELECT ...

• 使用示例

  --在default数据库和default_cluster集群下创建名为test表
  CREATE TABLE default.test ON CLUSTER default_cluster
  (
      `EventDate` DateTime, 
      `id` UInt64
  )
  ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}')
  PARTITION BY toYYYYMM(EventDate)
  ORDER BY id

插入表数据：

• 基本语法
  • 方法一：标准格式插入数据。

    INSERT INTO [database_name.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

  • 方法二：使用SELECT的结果写入。

    INSERT INTO [database_name.]table [(c1, c2, c3)] SELECT ...

• 使用示例

  --给test2表插入数据
  insert into test2 (id, name) values (1, 'abc'), (2, 'bbbb');
  --查询test2表数据
  select * from test2;
  ┌─id─┬─name─┐
  │  1   │ abc    │
  │  2   │ bbbb   │
  └───┴────┘

查询表数据：

• 基本语法

  SELECT [DISTINCT] expr_list

  [FROM [database_name.]table | (subquery) | table_function] [FINAL]

  [SAMPLE sample_coeff]

  [ARRAY JOIN ...]

  [GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)

  [PREWHERE expr]

  [WHERE expr]

  [GROUP BY expr_list] [WITH TOTALS]

  [HAVING expr]

  [ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]

  [LIMIT [offset_value, ]n BY columns]

  [LIMIT [n, ]m] [WITH TIES]

  [UNION ALL ...]

  [INTO OUTFILE filename]

  [FORMAT format]

• 使用示例

  --查看ClickHouse集群信息
  select * from system.clusters;
  --显示当前节点设置的宏
  select * from system.macros;
  --查看数据库容量
  select
  sum(rows) as "总行数",
  formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",
  formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",
  round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100,
  0) "压缩率"
  from system.parts;
  --查询test表容量。where条件根据实际情况添加修改
  select
  sum(rows) as "总行数",
  formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",
  formatReadableSize(sum(data_compressed_bytes)) as "压缩大小",
  round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100,
  0) "压缩率"
  from system.parts
  where table in ('test')
  and partition like '2020-11-%'
  group by table;

修改表结构：

• 基本语法

  ALTER TABLE [database_name].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

  

  ALTER仅支持 *MergeTree ，Merge以及Distributed等引擎表。

• 使用示例

  --给表t1增加列test01 
  ALTER TABLE t1 ADD COLUMN test01 String DEFAULT 'defaultvalue';
  --查询修改后的表t1
  desc t1
  ┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─codec_expression─┬─ttl_expression─┐
  │  id          │ UInt8  │                │                     │           │                    │                  │  
  │  name        │ String │                │                     │           │                    │                  │ 
  │  address     │ String │                │                     │           │                    │                  │
  │  test01      │ String │  DEFAULT       │  'defaultvalue'     │           │                    │                  │
  └───────┴────┴────────┴────────── ┴───── ┴──────────┴─────────┘
  --修改表t1列name类型为UInt8
  ALTER TABLE t1 MODIFY COLUMN name UInt8;
  --查询修改后的表t1
  desc t1
  ┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─codec_expression─┬─ttl_expression─┐
  │  id          │ UInt8  │                │                     │           │                    │                  │  
  │  name        │ UInt8  │                │                     │           │                    │                  │ 
  │  address     │ String │                │                     │           │                    │                  │
  │  test01      │ String │  DEFAULT       │  'defaultvalue'     │           │                    │                  │
  └───────┴────┴────────┴────────── ┴───── ┴──────────┴─────────┘
  --删除表t1的列test01
  ALTER TABLE t1 DROP COLUMN test01;
  --查询修改后的表t1
  desc t1
  ┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─codec_expression─┬─ttl_expression─┐
  │  id          │ UInt8  │                │                     │           │                    │                  │  
  │  name        │ UInt8  │                │                     │           │                    │                  │ 
  │  address     │ String │                │                     │           │                    │                  │
  └───────┴────┴────────┴────────── ┴───── ┴──────────┴─────────┘