Clickhouse 表相关操作小记

最新推荐文章于 2024-04-10 14:47:27 发布
最新推荐文章于 2024-04-10 14:47:27 发布
阅读量4.9k 收藏 8
点赞数 4
分类专栏： # clickhouse sql OLAP引擎 文章标签： 数据库 clickhouse OLAP ROLAP
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41018861/article/details/110704623
版权
sql 同时被 3 个专栏收录
29 篇文章 12 订阅
订阅专栏
OLAP引擎
26 篇文章 14 订阅
订阅专栏
clickhouse
15 篇文章 7 订阅
订阅专栏

运行环境：

[xxx@xxx ~]$ cat /etc/centos-release
CentOS Linux release 7.8.2003 (Core)

查看clickhouse版本

Clickhouse> select version();
 
SELECT version()
 
┌─version()─┐
│ 20.5.2.7  │
└───────────┘
 
1 rows in set. Elapsed: 0.001 sec.

clickhouse表操作

建表

Clickhouse> create table scott.emp(empno int not null comment '员工编码',ename varchar(32) comment '员工姓名',job varchar(32) comment '职位',mgr_no int  comment '领导的员工编号',hiredate Date comment '入职日期',sal decimal(7,2) comment '月薪',comm decimal(7,2) comment '奖金') engine=MergeTree() order by empno;
 
CREATE TABLE scott.emp
(
    `empno` int NOT NULL COMMENT '员工编码',
    `ename` varchar(32) COMMENT '员工姓名',
    `job` varchar(32) COMMENT '职位',
    `mgr_no` int COMMENT '领导的员工编号',
    `hiredate` Date COMMENT '入职日期',
    `sal` decimal(7, 2) COMMENT '月薪',
    `comm` decimal(7, 2) COMMENT '奖金'
)
ENGINE = MergeTree()
ORDER BY empno

添加字段：

添加字段：
Clickhouse> alter table scott.emp add column createtime datetime  default now() comment '数据写入时间';
 
ALTER TABLE scott.emp ADD COLUMN `createtime` datetime DEFAULT now() COMMENT '数据写入时间'

Ok.
 
0 rows in set. Elapsed: 0.014 sec.

查看表的结构：

Clickhouse> desc scott.emp;
 
DESCRIBE TABLE scott.emp
 
┌─name───────┬─type──────────┬─default_type─┬─default_expression─┬─comment────────┬─codec_expression─┬─ttl_expression─┐
│ empno      │ Int32         │              │                    │ 员工编码       │                  │                │
│ ename      │ String        │              │                    │ 员工姓名       │                  │                │
│ job        │ String        │              │                    │ 职位           │                  │                │
│ mgr_no     │ Int32         │              │                    │ 领导的员工编号 │                  │                │
│ hiredate   │ Date          │              │                    │ 入职日期       │                  │                │
│ sal        │ Decimal(7, 2) │              │                    │ 月薪           │                  │                │
│ comm       │ Decimal(7, 2) │              │                    │ 奖金           │                  │                │
│ createtime │ DateTime      │ DEFAULT      │ now()              │ 数据写入时间   │                  │                │
└────────────┴───────────────┴──────────────┴────────────────────┴────────────────┴──────────────────┴────────────────┘
 
8 rows in set. Elapsed: 0.009 sec.

查看表的定义：

Clickhouse> show create table scott.emp\G
 
SHOW CREATE TABLE scott.emp
 
Row 1:
──────
statement: CREATE TABLE scott.emp
(
    `empno` Int32 COMMENT '员工编码',
    `ename` String COMMENT '员工姓名',
    `job` String COMMENT '职位',
    `mgr_no` Int32 COMMENT '领导的员工编号',
    `hiredate` Date COMMENT '入职日期',
    `sal` Decimal(7, 2) COMMENT '月薪',
    `comm` Decimal(7, 2) COMMENT '奖金',
    `createtime` DateTime DEFAULT now() COMMENT '数据写入时间'
)
ENGINE = MergeTree()
ORDER BY empno
SETTINGS index_granularity = 8192
 
1 rows in set. Elapsed: 0.001 sec.

追加字段：

Clickhouse> alter table scott.emp add column deptno int default 10 comment '部门编号';
 
ALTER TABLE scott.emp ADD COLUMN `deptno` int DEFAULT 10 COMMENT '部门编号'

或者通过after 关键字在指定字段后添加新的字段：

Clickhouse> alter table scott.emp add column updatetime datetime default now() after createtime ;

ALTER TABLE scott.emp ADD COLUMN `updatetime` datetime DEFAULT now() AFTER createtime

添加注释：

Clickhouse> alter table scott.emp comment column updatetime '末次修改时间';
 
ALTER TABLE scott.emp
    COMMENT COLUMN updatetime '末次修改时间'

修改：

修改字段类型：
Clickhouse> alter table scott.emp modify column hiredate datetime;
 
ALTER TABLE scott.emp  MODIFY COLUMN `hiredate` datetime
修改默认值：
Clickhouse> desc scott.emp;
 
DESCRIBE TABLE scott.emp
 
┌─name───────┬─type──────────┬─default_type─┬─default_expression─┬─comment────────┬─codec_expression─┬─ttl_expression─┐
│ empno      │ Int32         │              │                    │ 员工编码       │                  │                │
│ ename      │ String        │              │                    │ 员工姓名       │                  │                │
│ job        │ String        │              │                    │ 职位           │                  │                │
│ mgr_no     │ Int32         │              │                    │ 领导的员工编号 │                  │                │
│ hiredate   │ DateTime      │              │                    │ 入职日期       │                  │                │
│ sal        │ Decimal(7, 2) │              │                    │ 月薪           │                  │                │
│ comm       │ Decimal(7, 2) │              │                    │ 奖金           │                  │                │
│ createtime │ DateTime      │ DEFAULT      │ now()              │ 数据写入时间   │                  │                │
│ updatetime │ DateTime      │ DEFAULT      │ now()              │ 末次修改时间   │                  │                │
│ deptno     │ Int32         │ DEFAULT      │ 10                 │ 部门编号       │                  │                │
└────────────┴───────────────┴──────────────┴────────────────────┴────────────────┴──────────────────┴────────────────┘
 
10 rows in set. Elapsed: 0.001 sec.

将deptno的默认值由10 修改为20：

Clickhouse> alter table scott.emp modify column deptno default 20;
 
ALTER TABLE scott.emp
    MODIFY COLUMN `deptno` DEFAULT 20
 
 
Ok.
 
0 rows in set. Elapsed: 0.003 sec. 
 
Clickhouse> desc scott.emp;
 
DESCRIBE TABLE scott.emp
 
┌─name───────┬─type──────────┬─default_type─┬─default_expression─┬─comment────────┬─codec_expression─┬─ttl_expression─┐
│ empno      │ Int32         │              │                    │ 员工编码       │                  │                │
│ ename      │ String        │              │                    │ 员工姓名       │                  │                │
│ job        │ String        │              │                    │ 职位           │                  │                │
│ mgr_no     │ Int32         │              │                    │ 领导的员工编号 │                  │                │
│ hiredate   │ DateTime      │              │                    │ 入职日期       │                  │                │
│ sal        │ Decimal(7, 2) │              │                    │ 月薪           │                  │                │
│ comm       │ Decimal(7, 2) │              │                    │ 奖金           │                  │                │
│ createtime │ DateTime      │ DEFAULT      │ now()              │ 数据写入时间   │                  │                │
│ updatetime │ DateTime      │ DEFAULT      │ now()              │ 末次修改时间   │                  │                │
│ deptno     │ Int32         │ DEFAULT      │ 20                 │ 部门编号       │                  │                │
└────────────┴───────────────┴──────────────┴────────────────────┴────────────────┴──────────────────┴────────────────┘
 
10 rows in set. Elapsed: 0.002 sec.

可以看到默认值已经修改。

修改TTL的信息：
Clickhouse> alter table scott.emp add column remark varchar(128) comment '说明信息' TTL createtime + toIntervalDay(31);
 
ALTER TABLE scott.emp ADD COLUMN `remark` varchar(128) COMMENT '说明信息' TTL createtime + toIntervalDay(31)
 
Ok.
 
0 rows in set. Elapsed: 0.012 sec. 

修改保存为62天：
Clickhouse> alter table scott.emp modify column remark varchar(254) TTL createtime+ toIntervalDay(62);
 
ALTER TABLE scott.emp MODIFY COLUMN `remark` varchar(254) TTL createtime + toIntervalDay(62)
    
Ok.
 
0 rows in set. Elapsed: 0.004 sec.

可以查看表结构信息：

Clickhouse> desc scott.emp;
 
DESCRIBE TABLE scott.emp
 
┌─name───────┬─type──────────┬─default_type─┬─default_expression─┬─comment────────┬─codec_expression─┬─ttl_expression─────────────────┐
│ empno      │ Int32         │              │                    │ 员工编码       │                  │                                │
│ ename      │ String        │              │                    │ 员工姓名       │                  │                                │
│ job        │ String        │              │                    │ 职位           │                  │                                │
│ mgr_no     │ Int32         │              │                    │ 领导的员工编号 │                  │                                │
│ hiredate   │ DateTime      │              │                    │ 入职日期       │                  │                                │
│ sal        │ Decimal(7, 2) │              │                    │ 月薪           │                  │                                │
│ comm       │ Decimal(7, 2) │              │                    │ 奖金           │                  │                                │
│ createtime │ DateTime      │ DEFAULT      │ now()              │ 数据写入时间   │                  │                                │
│ updatetime │ DateTime      │ DEFAULT      │ now()              │ 末次修改时间   │                  │                                │
│ deptno     │ Int32         │ DEFAULT      │ 20                 │ 部门编号       │                  │                                │
│ remark     │ String        │              │                    │ 说明信息       │                  │ createtime + toIntervalDay(62) │
└────────────┴───────────────┴──────────────┴────────────────────┴────────────────┴──────────────────┴────────────────────────────────┘
 
11 rows in set. Elapsed: 0.002 sec.

删除字段：

Clickhouse> alter table scott.emp drop column remark;
 
ALTER TABLE scott.emp DROP COLUMN remark

表的重命名：

create table default.dept  
(
    `deptno` Int32,
    `dname` String,
    `loc` String
)
ENGINE = MergeTree()
ORDER BY deptno;

可以将default.dept —> scott.dept:

Clickhouse> rename table default.dept to scott.dept;
 
RENAME TABLE default.dept TO scott.dept
 
Ok.

Clickhouse> rename table scott.dept  to scott.department;
 
RENAME TABLE scott.dept TO scott.department
 
Ok.
 
0 rows in set. Elapsed: 0.002 sec.

表的重命名智能在单个节点范围之内运行，即只能在同一服务节点之内，不能在集群中的远程节点。

清空表的数据：

truncate table scott.department;

复制表的结构：

Clickhouse> create table if not exists t_emp as scott.emp engine=TinyLog;
 
CREATE TABLE IF NOT EXISTS t_emp AS scott.emp
ENGINE = TinyLog
 
Ok.
 
0 rows in set. Elapsed: 0.003 sec.

复制表结构和数据：

Clickhouse> create table if not exists t_employee engine=Memory as select * from scott.emp;
 
CREATE TABLE IF NOT EXISTS t_employee
ENGINE = Memory AS
SELECT *
FROM scott.emp
 
Ok.
 
0 rows in set. Elapsed: 0.011 sec.

表的字段重命名：(20.4.2+版本支持)

Clickhouse> alter table t_city rename column city_level TO  cityLevel;
 
ALTER TABLE t_city RENAME COLUMN city_level TO cityLevel
 
Ok.
 
0 rows in set. Elapsed: 0.018 sec.

null字段的修改：

Clickhouse> create table t(id int ,name varchar(32)) ENGINE = MergeTree PARTITION BY id ORDER BY id;
 
CREATE TABLE t
(
    `id` int,
    `name` varchar(32)
)
ENGINE = MergeTree
PARTITION BY id
ORDER BY id
 
Clickhouse> insert into t(id,name)values(3,null);
 
INSERT INTO t (id, name) VALUES
 
Exception on client:
Code: 53. DB::Exception: Cannot insert NULL value into a column of type 'String' at: null);

Clickhouse> insert into t(id,name)values(1,'wuhan');
 
Clickhouse> insert into t(id)values(2);
修改表的定义：
Clickhouse> alter table t modify column name Nullable(varchar(32));
 
ALTER TABLE t MODIFY COLUMN `name` Nullable(varchar(32))
 
Ok.
 
0 rows in set. Elapsed: 0.017 sec.

在此插入：

Clickhouse> insert into t(id,name)values(3,null);
Clickhouse> select * from t order by id FORMAT PrettyCompactMonoBlock;
 
SELECT *
FROM t
ORDER BY id ASC
FORMAT PrettyCompactMonoBlock
 
┌─id─┬─name──┐
│  1 │ wuhan │
│  2 │       │
│  3 │ ᴺᵁᴸᴸ  │
└────┴───────┘
 
3 rows in set. Elapsed: 0.002 sec.

将null字段修改非null字段：

Code: 349. DB::Exception: Received from localhost:9000. DB::Exception: Cannot convert NULL value to non-Nullable type: (while reading from part /var/lib/clickhouse/data/default/t/3_4_4_0/): While executing MergeTreeThread. 
 
2 rows in set. Elapsed: 0.105 sec.

查询的时候报错：

Clickhouse> create table t1(id Nullable(int),name Nullable(String)) engine=MergeTree() order by id;
 
CREATE TABLE t1
(
    `id` Nullable(int),
    `name` Nullable(String)
)
ENGINE = MergeTree()
ORDER BY id
 
Received exception from server (version 20.5.2):
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Sorting key cannot contain nullable columns. 
 
0 rows in set. Elapsed: 0.011 sec.

结论：
1.可以将非null字段修改为null字段，有了数据之后就不能修改会非null.
2.null 字段不能在MergeTree系列表引擎中作为order by 字段

创建临时表

临时表的创建语法：
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)
 
说明：
1.临时表只支持Memory表引擎
2.临时表不属于任何数据库，创建临时表的定义没有数据库参数和标引擎参数。
3.临时表和常规表的表名称相同则优先读取临时表的数据。
 
 
Clickhouse> create table t_temp(desc varchar(254)) engine=Memory;
 
CREATE TABLE t_temp
(
    `desc` varchar(254)
)
ENGINE = Memory
 
Ok.
 
0 rows in set. Elapsed: 0.002 sec. 
 
Clickhouse> insert into t_temp values('clickhouse');
 
INSERT INTO t_temp VALUES
 
Ok.
 
1 rows in set. Elapsed: 0.001 sec. 
 
Clickhouse> create temporary table t_temp(createtime datetime);
 
CREATE TEMPORARY TABLE t_temp
(
    `createtime` datetime
)
 
Ok.
 
0 rows in set. Elapsed: 0.001 sec. 
 
Clickhouse> insert into t_temp values(now());
 
INSERT INTO t_temp VALUES
 
Ok.
 
1 rows in set. Elapsed: 0.005 sec. 
 
Clickhouse> select * from t_temp;
 
SELECT *
FROM t_temp
 
┌──────────createtime─┐
│ 2020-07-13 23:45:51 │
└─────────────────────┘
 
1 rows in set. Elapsed: 0.001 sec. 
 
 
注意:
临时表平常不怎么使用，更多的应用于clickhouse的内部在集群之间传播的。
扫地增
关注
  • 4
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
专栏目录
clickhouse插入数据
weixin_51473488的博客
08-21 2270
ClickHouse内部所有的数据操作都是面向Block数据块的，所以INSERT查询最终会将数据转换为Block数据块。也就是说，如果一条INSERT语句写入的数据少于max_insert_block_size行，那么这批数据的写入是具有原子性的，即要么全部成功，要么全部失败。clickhouse中插入数据，最好性能是按照指定格式插入文件，这样是ck擅长的，因为ck是写入block的，每个block的大小在配置文件中设置有，这样在数据未写满block的时候，要么写成功要么写失败，不能部分成功。
clickhouse之insert into
高精尖发展
01-23 2万+
insert INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ... 插入时指定列，其他的列，将     1、如存在default表达式，根据default表达式计算被填充的值     2、不存在，填充零或空字符串 如strict_insert_defaults=1，须在查询中...
Clickhouse数据复制的原理与实践
最新发布
04-10 1074
作者：俊达。
向ClickHouse表插入复杂数据
认知 行动 坚持
12-04 1212
1、创建表 CREATE TABLE test_db.aa_local on cluster cluster_name ( fdate Int64, time_slot Int64, placement_id String, city_id String, city_name String, user_id String, request_id String, request_time_ms Int64,
ClickHouse
我不是什么好人
06-03 1151
前言 mysql：关系型数据库，支持事务，单节点存储数据量有限 hbase：数据库、可存储海量数据、不太支持sql、存k-v取value比较快、不适合分析做报表 hive：数仓工具，底层转成mr程序处理和分析数据、良好的SQL语法、便于分析做报表、但是慢 clickhouse：存储海量数据、快速查询、多维度多运算模型 clickhouse简介 开源的用于OLAP的列式数据库管理系统（DBMS），主要用于在线分析处理查询，能够使用SQL查询实时生成分析数据报告 全称：click stream，data
clickhouse系列学习笔记——（三）（下）clickhouse基本语法
niuyan666的博客
05-20 1038
三 clickhouse基础入门 2 基本语法 2.1 DDL基础 建表 目前只有MergeTree、Merge和Distributed这三类表引擎支持 ALTER查询,所以在进行alter操作的时候注意表的引擎! 注意在建表的时候一般要求指定表的引擎 CREATE TABLE tb_test1 ( `id` Int8, `name` String ) ENGINE = Memory() ; 修改表结构 -- 查看表结构 desc tb_test1 ; ┌─name─┬─type─
php&mysql 日期操作小记
10-28
4. **时间运算**：可以利用`strtotime()`与相对时间表达式结合，进行加减操作，如 `date('Y-m-d h:i:s', strtotime('+1 week'))`。 MySQL中的日期操作： 1. **整型转日期时间**：使用`FROM_UNIXTIME()`函数将Unix...
phpmysql 日期操作小记
01-21
在时间比较查询的时候，int的效率明显更高。祥文见https://www.jb51.net/article/29767.htm 但是在做项目的时候或者直接在数据库查看数据的时候，明显这个int一看头就大，比如我们想 要查看一个用户的注册时间： ...
U8系统操作小记对你们非常重要.pdf
09-15
U8系统操作小记对你们非常重要.pdf
Beatles小记
03-02
这篇小记主要处于两方面考虑：首先，希望打破一提到海量数据分析，就只有hadoop基础上的一系列工具，更多的时候很多企业需要的是更轻量的设计（办喜酒杀猪杀鸡未必都要用一把刀），因此将开放平台基础分析组件重构...
Clickhouse常见运维操作
lanicc's blog
02-15 1924
Distributed表引擎是分布式表的代名词，它自身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据至集群中的各个节点，所以Distributed表引擎需要和其他数据表引擎一起协同工作。ClickHouse内置了许多system系统表，用于查询自身的状态信息。query_log是最常用的查询日志，它记录了ClickHouse服务中所有已经执行的查询记录。更改列类型是一个复杂的操作，它会更改包含数据的文件内容。不同的表引擎选项不同，这里以 MergeTree() 引擎作为示例。
Clickhouse 入门到精通-clickhouse 基础命令
04-23 682
alter table tb_test2 add column age UInt8 COMMENT ‘注释’;alter table tb_test2 comment column name ‘用户名’;六、修改添加字段注释。
ClickHouse 数据插入、更新与删除操作 SQL
m0_67401055的博客
08-01 6003
数据操作语言DML，包括SQL数据更改语句，它修改存储的数据，但不修改数据模型，例如数据库模式或数据库表结构。DML语言常见的语法模式如下。
clickhouse笔记 - 2
06-28 161
大数据、clickhouse、kafka
clickhouse基本语法
weixin_45469056的博客
04-17 4113
DDL(DDL:数据定义语言,通常是数据库管理系统的一部分，用于定义数据库的所有特性和属性，尤其是行布局、列定义、键列(有时是选键方法)、文件位置和存储策略。 ) 包括命令: REATE（建表）, ALTER（添加）, GRANT（grant 操作 MySQL存储过程、函数权限，授权命令）, REVOKE（撤回）, DROP（删除）, **TRUNCATE(**删除表中的数据的方法有delete...
最全的clickhouse的增删改查以及导入导出操作
wppwpp1的专栏
05-09 7169
---客户端链接工具 clickhouse-client -m -u root -p root 查询正在进行执行的sql操作 SHOW PROCESSLIST 建表语句 create TABLE test.test( id Int32,create_date Date ,c2 Nullable(String) ) engine=MergeTree(create_date,id,(c3,c2),8192); 表变更预计 ALTER查询只支持MergeTree表，。该查询有几个变体。 ALTER T.
Clickhouse Distributed分布式表引擎的基本介绍和使用说明
Bulut0907
07-08 6156
目录1. 分布式的本地表1.1 基本语法1.2 分布式的创建本地表1.3 分布式的删除本地表2. Distributed表2.1 创建Distributed表2.2 删除分布式表2.3 Distributed表其它语法3. Distributed表的增删改查3.1 insert3.2 select3.3 分布式本地表mutation3.3 分布式本地表delete Clickhouse的集群部署可以参考我的Clickhouse版本21.6.5.37的分片和副本分布式安装 Distributed表需要和其它
Clickhouse 国内应用案例汇总（持续更新）
vkingnew 的技术博客
06-16 2228
QQ 音乐 https://www.infoq.cn/article/3OfbQcStGJssh5CGl8VP 原始地址： https://mp.weixin.qq.com/s/_pXPDbhqC1B1HQWQQEfAGw 携程 https://www.infoq.cn/article/Mv5QKf8BZdv4RVMhEa7o 原始地址： https://mp.weixin.qq.com/s/IjOWAPOJXANRQqRAMWXmaw https://www.infoq.cn/arti.
ClickHouse修改表字段
热门推荐
LitongZero的博客
05-26 2万+
ClickHouse修改表字段。Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 34: '$user_id' String AFTER chart_position. Expected one of: IF NOT EXISTS, compound identifier, identifier, column declaration, list of elements (version 20.4.2.9
protobuff使用小记
05-31
下面是使用Protobuf的一些小记： 1. 定义消息格式 首先，需要定义消息格式，以便Protobuf可以将数据序列化和反序列化。消息格式定义在.proto文件中，使用protobuf语言编写。例如，下面是一个简单的消息格式定义： ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

扫地增

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值