【ClickHouse】0：clickhouse学习4之表相关操作

Clickhouse对表操作分为四大类：增删查改(INSERT,DROP,SELECT,ALTER)。

增，删，查比较简单，改最复杂。那具体有哪些改的操作呢？如下清单：

ALTER TABLE
- ALTER UPDATE
- ALTER DELETE
- ALTER COLUMN
  - ALTER ADD COLUMN
  - ALTER DROP COLUMN
  - ALTER MODIFY COLUMN
  - ALTER COMMENT COLUMN
  - ALTER CLEAR COLUMN
  - ALTER RENAME COLUMN
- ALTER INDEX
  - ALTER ORDER BY
  - ALTER SAMPLE BY
  - ALTER ADD INDEX
  - ALTER DROP INDEX
  - ALTER MATERIALIZE INDEX
  - ALTER CLEAR INDEX
- ALTER CONSTRAINT
  - ALTER ADD CONSTRAINT
  - ALTER DROP CONSTRAINT
- ALTER TTL
- ALTER MATERIALIZE TTL
- ALTER SETTINGS
- ALTER MOVE PARTITION
- ALTER FETCH PARTITION
- ALTER FREEZE PARTITION
ALTER VIEW
- ALTER VIEW REFRESH
- ALTER VIEW MODIFY QUERY

下面以MergeTree引擎表为例，对应列表操作:

一：建表并插入测试数据

# 创建表
#方式1
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = engine
#方式2
CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]
#方式3
CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...

# 创建测试表
CREATE TABLE table_test1 (
  UserID UInt32,
  UserAccount String,
  CreateDate Date,
  CreateDatetime Datetime,
  Comment Nullable(String)
) ENGINE = MergeTree()
     ORDER BY  (UserID, CreateDate)
     PRIMARY KEY (UserID)
     SETTINGS index_granularity=8192;

导入数据

# 插入表
#方式1-交互式
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), ...

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

#方式2-批量
cat file.csv | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV"

#方式3-http客户端
echo -ne '10\n11\n12\n' | POST 'http://localhost:8123/?query=INSERT INTO t FORMAT TabSeparated'

导出数据

方式一：交互式
select * from tableName into outfile 'path/file'
方式二：非交互式
clickhouse-client  --database bdName -u default --password password --query='select * from tableName' > abc
方式二：http客户端
echo 'select 1 FORMAT TabSeparated' | curl "http://user:password@localhost:8123/" -d @- > file

# 插入数据
INSERT INTO table_test1(UserID,UserAccount,CreateDate,CreateDatetime,Comment) VALUES (100001,'zhangsan',toDate(NOW()),NOW(),'Comment');
INSERT INTO table_test1(UserID,UserAccount,CreateDate,CreateDatetime,Comment) VALUES (100002,'lisi',toDate(NOW()),NOW(),'Comment');
INSERT INTO table_test1(UserID,UserAccount,CreateDate,CreateDatetime,Comment) VALUES (100003,'wangwu',toDate(NOW()),NOW(),'Comment');
INSERT INTO table_test1(UserID,UserAccount,CreateDate,CreateDatetime,Comment) VALUES (100004,'zhaoliu',toDate(NOW()),NOW(),'Comment');
INSERT INTO table_test1(UserID,UserAccount,CreateDate,CreateDatetime,Comment) VALUES (100001,'zhangsan2',toDate(NOW()),NOW(),'Comment');
INSERT INTO table_test1(UserID,UserAccount,CreateDate,CreateDatetime,Comment) VALUES (100002,'lisi2',toDate(NOW()),NOW(),'');
INSERT INTO table_test1(UserID,UserAccount,CreateDate,CreateDatetime) VALUES (100003,'wangwu2',toDate(NOW()),NOW());

# 查询表
SELECT [DISTINCT] expr_list
    [FROM [db.]table | (subquery) | table_function] [FINAL]
    [SAMPLE sample_coeff]
    [ARRAY JOIN ...]
    [GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
    [PREWHERE expr]
    [WHERE expr]
    [GROUP BY expr_list] [WITH TOTALS]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT [n, ]m]
    [UNION ALL ...]
    [INTO OUTFILE filename]
    [FORMAT format]
    [LIMIT n BY columns]

# 查看数据
centf8118.sharding1.db :) SELECT * FROM table_test1;

SELECT *
FROM table_test1

┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100003 │ wangwu2     │ 2020-09-02 │ 2020-09-02 16:45:57 │ ᴺᵁᴸᴸ    │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘
┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100001 │ zhangsan    │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100001 │ zhangsan2   │ 2020-09-02 │ 2020-09-02 16:45:56 │ Comment │
│ 100002 │ lisi        │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100002 │ lisi2       │ 2020-09-02 │ 2020-09-02 16:45:56 │         │
│ 100003 │ wangwu      │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100004 │ zhaoliu     │ 2020-09-02 │ 2020-09-02 16:45:56 │ Comment │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘

7 rows in set. Elapsed: 0.005 sec.

二：更新表数据

ALTER TABLE table_test1 UPDATE UserAccount = 'NEW ACCOUNT' WHERE UserID = 100001 AND UserAccount = 'zhangsan';

centf8118.sharding1.db :) ALTER TABLE table_test1 UPDATE UserAccount = 'NEW ACCOUNT' WHERE UserID = 100001 AND UserAccount = 'zhangsan';

ALTER TABLE table_test1
    UPDATE UserAccount = 'NEW ACCOUNT' WHERE (UserID = 100001) AND (UserAccount = 'zhangsan')


Ok.

0 rows in set. Elapsed: 0.004 sec. 

centf8118.sharding1.db :) SELECT * FROM table_test1;

SELECT *
FROM table_test1

┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100003 │ wangwu2     │ 2020-09-02 │ 2020-09-02 16:45:57 │ ᴺᵁᴸᴸ    │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘
┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100001 │ NEW ACCOUNT │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100001 │ zhangsan2   │ 2020-09-02 │ 2020-09-02 16:45:56 │ Comment │
│ 100002 │ lisi        │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100002 │ lisi2       │ 2020-09-02 │ 2020-09-02 16:45:56 │         │
│ 100003 │ wangwu      │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100004 │ zhaoliu     │ 2020-09-02 │ 2020-09-02 16:45:56 │ Comment │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘

7 rows in set. Elapsed: 0.005 sec.

View Code

三：删除表数据

ALTER TABLE table_test1 DELETE WHERE UserID = 100001;

centf8118.sharding1.db :) ALTER TABLE table_test1 DELETE WHERE UserID = 100001;

ALTER TABLE table_test1
    DELETE WHERE UserID = 100001


Ok.

0 rows in set. Elapsed: 0.003 sec. 

centf8118.sharding1.db :) SELECT * FROM table_test1;

SELECT *
FROM table_test1

┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100003 │ wangwu2     │ 2020-09-02 │ 2020-09-02 16:45:57 │ ᴺᵁᴸᴸ    │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘
┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100002 │ lisi        │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100002 │ lisi2       │ 2020-09-02 │ 2020-09-02 16:45:56 │         │
│ 100003 │ wangwu      │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100004 │ zhaoliu     │ 2020-09-02 │ 2020-09-02 16:45:56 │ Comment │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘

5 rows in set. Elapsed: 0.005 sec.

View Code

四：表字段修改

ALTER查询仅支持* MergeTree族表引擎，以及Merge表引擎和Distributed表引擎。
ALTER操作阻塞所有对表的其他操作。

4.1: 新增表字段

ALTER TABLE table_test1 ADD COLUMN NewAddCol String;

centf8118.sharding1.db :) ALTER TABLE table_test1 ADD COLUMN NewAddCol String;

ALTER TABLE table_test1
    ADD COLUMN `NewAddCol` String


Ok.

0 rows in set. Elapsed: 0.004 sec. 

centf8118.sharding1.db :) SELECT * FROM table_test1;

SELECT *
FROM table_test1

┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┬─NewAddCol─┐
│ 100003 │ wangwu2     │ 2020-09-02 │ 2020-09-02 16:45:57 │ ᴺᵁᴸᴸ    │           │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┴───────────┘
┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┬─NewAddCol─┐
│ 100002 │ lisi        │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │           │
│ 100002 │ lisi2       │ 2020-09-02 │ 2020-09-02 16:45:56 │         │           │
│ 100003 │ wangwu      │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │           │
│ 100004 │ zhaoliu     │ 2020-09-02 │ 2020-09-02 16:45:56 │ Comment │           │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┴───────────┘

5 rows in set. Elapsed: 0.004 sec.

View Code

4.2: 删除表字段

ALTER TABLE table_test1 DROP COLUMN NewAddCol;

centf8118.sharding1.db :) ALTER TABLE table_test1 DROP COLUMN NewAddCol;

ALTER TABLE table_test1
    DROP COLUMN NewAddCol


Ok.

0 rows in set. Elapsed: 0.008 sec. 

centf8118.sharding1.db :) SELECT * FROM table_test1;

SELECT *
FROM table_test1

┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100003 │ wangwu2     │ 2020-09-02 │ 2020-09-02 16:45:57 │ ᴺᵁᴸᴸ    │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘
┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100002 │ lisi        │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100002 │ lisi2       │ 2020-09-02 │ 2020-09-02 16:45:56 │         │
│ 100003 │ wangwu      │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100004 │ zhaoliu     │ 2020-09-02 │ 2020-09-02 16:45:56 │ Comment │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘

5 rows in set. Elapsed: 0.004 sec.

View Code

4.3: 修改表字段类型或默认值

ALTER TABLE table_test1 MODIFY COLUMN UserID String;

ALTER TABLE table_test1 MODIFY COLUMN Comment String DEFAULT ('default');

这里给comment设置默认值的时候会失败，报错信息：DB::Exception: Cannot convert NULL value to non-Nullable type

1、空表，Nullable与非空类型可以互转；

2、Nullable字段，如果记录不带有Null值，可以从Nullable转成非空类型；

3、含有null值的字段不允许转成非空类型；

4、Nullable字段不允许用于order by；

没注意到原来列是允许为null的，用下面的调整默认值就可以了：

ALTER TABLE table_test1 MODIFY COLUMN Comment Nullable(String) DEFAULT ('default');

如果是修改key列，也会报错：Code: 524. DB::Exception: Received from localhost:9000. DB::Exception: ALTER of key column UserID from type UInt32 to type String must be metadata-only.

只能在metadata目录中修改对应表名的.sql文件：

[root@centf8118 db_test]# pwd
/data/clickhouse/metadata/db_test
[root@centf8118 db_test]# ll
total 8
-rw-r----- 1 clickhouse clickhouse 296 Sep  2 17:12 table_test1.sql
-rw-r----- 1 clickhouse clickhouse 284 Sep  2 18:01 table_test2.sql
[root@centf8118 db_test]# cat table_test2.sql 
ATTACH TABLE table_test2
(
    `UserID` String,
    `UserAccount` String,
    `CreateDate` Date,
    `CreateDatetime` DateTime,
    `Comment` Nullable(String) DEFAULT 'default2'
)
ENGINE = MergeTree()
PRIMARY KEY UserID
ORDER BY (UserID, CreateDate)
SETTINGS index_granularity = 8192

再登录数据库，查看：

centf8118.sharding1.db :) show create table table_test2;

SHOW CREATE TABLE table_test2

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE db_test.table_test2
(
    `UserID` String,
    `UserAccount` String,
    `CreateDate` Date,
    `CreateDatetime` DateTime,
    `Comment` Nullable(String) DEFAULT 'default2'
)
ENGINE = MergeTree()
PRIMARY KEY UserID
ORDER BY (UserID, CreateDate)
SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

4.4: 修改表字段描述

ALTER TABLE table_test1 COMMENT COLUMN Comment '字段描述';

centf8118.sharding1.db :) ALTER TABLE table_test1 COMMENT COLUMN Comment '字段描述';

ALTER TABLE table_test1
    COMMENT COLUMN Comment '字段描述'


Ok.

0 rows in set. Elapsed: 0.004 sec. 

centf8118.sharding1.db :) SELECT * FROM table_test1;

SELECT *
FROM table_test1

┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100003 │ wangwu2     │ 2020-09-02 │ 2020-09-02 16:45:57 │ ᴺᵁᴸᴸ    │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘
┌─UserID─┬─UserAccount─┬─CreateDate─┬──────CreateDatetime─┬─Comment─┐
│ 100002 │ lisi        │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100002 │ lisi2       │ 2020-09-02 │ 2020-09-02 16:45:56 │         │
│ 100003 │ wangwu      │ 2020-09-02 │ 2020-09-02 16:45:55 │ Comment │
│ 100004 │ zhaoliu     │ 2020-09-02 │ 2020-09-02 16:45:56 │ Comment │
└────────┴─────────────┴────────────┴─────────────────────┴─────────┘

5 rows in set. Elapsed: 0.004 sec. 

centf8118.sharding1.db :) show create table table_test1;

SHOW CREATE TABLE table_test1

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE db_test.table_test1
(
    `UserID` UInt32,
    `UserAccount` String,
    `CreateDate` Date,
    `CreateDatetime` DateTime,
    `Comment` Nullable(String) COMMENT '字段描述'
)
ENGINE = MergeTree()
PRIMARY KEY UserID
ORDER BY (UserID, CreateDate)
SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.

View Code

4.5: 重置指定分区中列的所有数据（这里没做分区，先略过）

4.6: 重命名列

明日继续

ALTER COLUMN
- ALTER ADD COLUMN
- ALTER DROP COLUMN
- ALTER MODIFY COLUMN
- ALTER COMMENT COLUMN
- ALTER CLEAR COLUMN
- ALTER RENAME COLUMN
ALTER INDEX
- ALTER ORDER BY
- ALTER SAMPLE BY
- ALTER ADD INDEX
- ALTER DROP INDEX
- ALTER MATERIALIZE INDEX
- ALTER CLEAR INDEX
ALTER CONSTRAINT
- ALTER ADD CONSTRAINT
- ALTER DROP CONSTRAINT
ALTER TTL
ALTER MATERIALIZE TTL
ALTER SETTINGS
ALTER MOVE PARTITION
ALTER FETCH PARTITION
ALTER FREEZE PARTITION

#添加列
ALTER TABLE [db].name [ON CLUSTER cluster] ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after]
#删除列
ALTER TABLE [db].name [ON CLUSTER cluster] DROP COLUMN [IF EXISTS] name
#重置指定分区中列的所有数据
ALTER TABLE [db].name [ON CLUSTER cluster] CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name
#添加列注解
ALTER TABLE [db].name [ON CLUSTER cluster] COMMENT COLUMN [IF EXISTS] name 'comment'
#修改列类型或者列的默认值
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY COLUMN [IF EXISTS] name [type] [default_expr]
#添加索引
ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value AFTER name [AFTER name2]
#删除索引
ALTER TABLE [db].name DROP INDEX name
#分离分区
ALTER TABLE table_name DETACH PARTITION partition_expr
#删除分区
ALTER TABLE table_name DROP PARTITION partition_expr
#添加被分离的分区
ALTER TABLE table_name ATTACH PARTITION|PART partition_expr
#复制table1中的分区数据到table2
ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1
#重置列值为默认值，默认值为创建表时指定
ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr
#创建指定分区或者所有分区的备份
ALTER TABLE table_name FREEZE [PARTITION partition_expr]
#从其他分片中复制分区数据
ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'

posted @ 2020-09-02 18:05 DBArtist 阅读(2460) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

DBArtist

轻舞云飞落星辰

【ClickHouse】0：clickhouse学习4之表相关操作

一：建表并插入测试数据

二：更新表数据

三：删除表数据

四：表字段修改

4.1: 新增表字段

4.2: 删除表字段

4.3: 修改表字段类型或默认值

4.4: 修改表字段描述

4.5: 重置指定分区中列的所有数据（这里没做分区，先略过）

4.6: 重命名列

公告

DBArtist

轻舞云飞落星辰

【ClickHouse】0：clickhouse学习4之表相关操作

一： 建表并插入测试数据

二：更新表数据

三：删除表数据

四：表字段修改

4.1: 新增表字段

4.2: 删除表字段

4.3: 修改表字段类型或默认值

4.4: 修改表字段描述

4.5: 重置指定分区中列的所有数据（这里没做分区，先略过）

4.6: 重命名列

公告

一：建表并插入测试数据