ClickHouse学习笔记：01数据类型和基本DDL

最新推荐文章于 2023-08-13 15:42:27 发布
最新推荐文章于 2023-08-13 15:42:27 发布
阅读量730 收藏 2
点赞数
分类专栏： 大数据 ClickHouse学习笔记 文章标签： clickhouse 学习 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wangzhongyudie/article/details/127876977
版权

文章目录

数据类型

常规类型

ClickHouse数据类型含义所占字节数对应Mysql数据类型
Int8整数1字节TINYINT
Int16整数2字节SMALLINT
Int32整数4字节INT
Int64整数8字节BIGINT
Float32浮点数4字节float
Float64浮点数8字节double
Decimal32(n)精度是n(小数点后有n位)有效位1~9Decimal(9,n)
Decimal64(n)精度是n(小数点后有n位)有效位1~18Decimal(18,n)
Decimal128(n)精度是n(小数点后有n位)有效位1~38Decimal(38,n)
Decimal256(n)精度是n(小数点后有n位)有效位1~76Decimal(76,n)
String字符类型长度可变类似于varchar(),Text等字符类型
FixedString(n)字符类型固定长度N的字符串类似于Char(N),当实际长度不足N时，用空字符补充，实际长度大于N时报错

Tips:

  • 1、ClickHouse 兼容了Mysql的数据类型，在建表的DDL语句中可以使用Mysql的数据类型如BIGINT、TEXT等，ClickHouse会自动将这些类型转换为ClickHouse的数据类型。

  • 2、ClickHouse自身的数据类型是区分大小写的，例如Int8 就只能这样写，不能写成int8，但是如果是兼容Mysql的数据类型，则既可以大写也可以小写，如BIGINT和bigint都是可以的。

布尔类型

ClickHouse中没有布尔类型，如果需要用到布尔类型，可以使用Int8代替，限定其值为0和1.

枚举类型

ClickHouse数据类型所占字节本质类型示例
Enum81字节Int8Enum8(‘SZ’=0,‘SH’=1,‘HK’=3)
Enum162字节Int16Enum16(‘SZ’=0,‘SH’=1,‘HK’=3)
  • 1、定义
CREATE TABLE XSYS0003(
    SECCODE String comment '证券代码',
    MARKET Enum8('SZ'=0,'SH'=1,'HK'=3) DEFAULT 'SH'
) ENGINE =MergeTree
PRIMARY KEY (SECCODE,MARKET)
ORDER BY (SECCODE,MARKET)
COMMENT '系统码表';

Tips:在DDL中的枚举类型也可以使用Enum(‘SZ’=0,‘SH’=1) 这种方式进行定义，ClickHouse会根据枚举的值隐式转换为Enum8或者Enum16，如果枚举的值超过了Int16的表数范围，则会报错。

  • 2、插入数据
INSERT INTO TABLE XSYS0003(SECCODE,MARKET)VALUES('000001','SZ'),('000001','SH');
  • 3、取数据
--过滤时可以当成字符串的方式使用枚举的Key过滤
SELECT * from XSYS0003 x where MARKET = 'SH';

--使用枚举的值过滤，效率比上一方式更高
SELECT * from XSYS0003 x where MARKET = 1;

--取出真实的值，做类型转换
SELECT CAST(MARKET AS Int8) FROM XSYS0003 x;

日期类型

Date

ClickHouse数据类型所占字节说明
Date2年月日，eg：YYYY-MM-DD
Date322年月日，eg：YYYY-MM-DD
DateTime2年月日时分秒，eg：YYYY-MM-DD hh:mm:ss
DateTime322年月日时分秒，eg：YYYY-MM-DD hh:mm:ss
DateTime642年月日时分秒亚秒，eg：YYYY-MM-DD hh:mm:ss.XX

Tips：定义成Mysql的Timestamp，ClickHouse会自动将Timestamp转换为DataTime类型。

数组类型Array(T)

Array(T): 由类型为T的数据组成的数组。

  1. T可以是任何类型，包括数组，但是ClickHouse对多维数组的支持有限，MergeTree引擎就不支持多维数组。

  2. 数组中的元素必须是同一数据类型。

  3. ClickHouse中数组元素的下标是从1开始的。

  • 1、定义
ALTER table XAI0114 add column "MEMBERS" Array(String) comment '成员';
  • 2、插入值
INSERT into table XAI0114 (MEMBERS) values(['000001','000002']);
  • 3、查询
SELECT MEMBERS[1]  FROM XAI0114 x ;

Tips: 注意，在ClickHouse中，数组的下标是从1开始的，写成MEMBERS[0]会报错。

基本的DDL操作

创建表

CREATE TABLE XAI0115(
	TDATE INT COMMENT '交易日期',
	SECCODE String COMMENT '证券代码',
	SECNAME String COMMENT '证券名称',
	MARKET Enum8('SH'=1,'SZ'=2,'HK'=3) COMMENT '市场代码',
	"OPEN" Decimal(18,2) COMMENT '开盘价',
	CREATE_TIME DateTime COMMENT '插入时间'
) engine = MergeTree
PARTITION BY TDATE
PRIMARY KEY  (TDATE,SECCODE)
ORDER BY (TDATE,SECCODE)
TTL CREATE_TIME + INTERVAL 10 SECOND;

引擎

TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存小数据量的表，在生产环境上的作用有限，多用于测试环境。

Memory

数据以未压缩的原始形态保存在内存中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单的查询有非常高的性能，多用于数据量不大，而又需要非常非常高的性能的场景下。

MergeTree 和*MergeTree家族

合并树引擎及其家族的其他引擎，是ClickHouse中非常重要的引擎，相当于Innodb之于Mysql，在ClickHouse被广泛运用。它有如下特征：

  • 1、支持索引
  • 2、支持分区
  • 3、支持数据TTL

Partition by 分区(可选)

  • 分区的目的主要用于降低扫描的范围，提升查询的效率。

  • 如果不填，则默认只有一个分区

  • MergeTree是以列文件+索引文件+表定义文件组成的。

表的定义文件存放在：clickhouse/store/XXX/XXXXX/TABLE.sql 目录中
如/home/software/clickhouse/store/56d/56d450cb-29f3-424c-8784-994f2813df5c目录中

total 20
-rw-r----- 1 clickhouse clickhouse 371 Nov 15 23:39 XAI0114.sql
-rw-r----- 1 clickhouse clickhouse 531 Nov 15 18:39 XAI0115.sql
-rw-r----- 1 clickhouse clickhouse 431 Nov 15 15:21 XSYS0001.sql
-rw-r----- 1 clickhouse clickhouse 423 Nov 15 15:04 XSYS0002.sql
-rw-r----- 1 clickhouse clickhouse 303 Nov 15 16:18 XSYS0003.sql

列文件存放在clickhosuse/data/数据库名称/表名/分区/

-rw-r----- 1 clickhouse clickhouse 254 Nov 15 23:41 checksums.txt
-rw-r----- 1 clickhouse clickhouse  91 Nov 15 23:41 columns.txt
-rw-r----- 1 clickhouse clickhouse   1 Nov 15 23:41 count.txt
-rw-r----- 1 clickhouse clickhouse  90 Nov 15 23:41 data.bin
-rw-r----- 1 clickhouse clickhouse 112 Nov 15 23:41 data.mrk3
-rw-r----- 1 clickhouse clickhouse  10 Nov 15 23:41 default_compression_codec.txt
-rw-r----- 1 clickhouse clickhouse  14 Nov 15 23:41 minmax_SECCODE.idx
-rw-r----- 1 clickhouse clickhouse   7 Nov 15 23:41 partition.dat
-rw-r----- 1 clickhouse clickhouse  16 Nov 15 23:41 primary.idx

  • 分区后可以并行处理，分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。

  • 数据写入与分区合并
    任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入
    后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（等不及也可以手动
    通过 optimize 执行），把临时分区的数据，合并到已有分区中。

optimize table XAI0114 FINAL;

Primary Key 主键(可选)

与传统的关系型数据库不同，ClickHouse的主键并不是唯一约束，定义了主键后用户可以插入相同的数据。在ClickHouse中，主键只提供了一级索引（同Mysql一样，主键字段上面会自动建有索引）。主键的设立依据是查询语句中的Where条件，可以将其理解为Mysql中的索引。

根据条件通过对主键进行某种形式的二分查找，能够定位到对应的 index granularity,避免了全表扫描。

index granularity：索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

  • 重点1、ClickHouse的主键不是唯一约束，是可以重复的

  • 重点2、ClickHouse的主键必须是ORDER BY字段的左值字段或者全部字段

ORDER BY 排序（必选）

  • ORDER BY 设定了分区内的数据按照哪些字段顺序进行有序保存。它是MergeTree中唯一一个必填项，甚至比PrimaryKey更加重要。当用户不设置主键时，很多情况会按照设定的Order By字段进行处理。

  • 主键必须是Order BY字段的前缀处理，比如ORDER BY（TDATE，SECCODE）,则主键必须是TDATE或者(TDATE,SECCODE).

TTL

TTL 即 Time To Live，MergeTree提供了可以管理数据表或者列的生命周期的功能。

  • 列级别TTL，指的是该列的数据会在指定时间到期后被清空。
    示例：
CREATE TABLE STOCK_HQ(
	SECCODE String comment '证券代码',
	"OPEN" Decimal(9,2)  comment '开盘价' TTL UPDATE_TIME + Interval 10 SECOND ,
	UPDATE_TIME DateTime comment '更新时间'
) engine = MergeTree
ORDER BY SECCODE ;

desc table STOCK_HQ;

insert into table STOCK_HQ VALUES('000002',12.34,now());
SELECT * from STOCK_HQ ;

插入数据后，等待10秒钟，查询后发现字段OPEN的值被置为了0。
如果看不到效果，手工执行optimize table + tablename final,再执行查询就可以立即看到效果。

  • 表级别TTL，指的是该行的数据会在指定的时间到期后被删除。
CREATE TABLE XAI0115(
	TDATE INT COMMENT '交易日期',
	SECCODE String COMMENT '证券代码',
	SECNAME String COMMENT '证券名称',
	MARKET Enum8('SH'=1,'SZ'=2,'HK'=3) COMMENT '市场代码',
	"OPEN" Decimal(18,2) COMMENT '开盘价',
	CREATE_TIME DateTime COMMENT '插入时间'
) engine = MergeTree
PARTITION BY TDATE
PRIMARY KEY  (TDATE,SECCODE)
ORDER BY (TDATE,SECCODE)
TTL CREATE_TIME + INTERVAL 10 SECOND;

TTL 的时间间隔单位有：

  • SECOND 秒
  • MINUTE 分钟
  • HOUR 小时
  • DAY 天
  • WEEK 周
  • MONTH 月
  • QUARTER 季度
  • YEAR 年

修改表

重命名表

RENAME TABLE XAI0114 TO  XAI0114_NEW;

增加字段

ALTER TABLE XAI0114_NEW ADD COLUMN INSERT_TIME DateTime64 default now() comment '插入时间';

删除字段

ALTER TABLE XAI0114_NEW DROP COLUMN INSERT_TIME ;

重命名字段

ALTER TABLE XAI0114_NEW RENAME COLUMN INSERT_TIME TO UPDATE_TIME ;

修改字段

可以按照如下语法，修改字段的数据类型，默认值，comment；

ALTER TABLE XAI0114_NEW MODIFY COLUMN "TYPE" Enum('SH'=1,'SZ'=2,'HK'=3) default 'SZ' comment '市场类型';

删除表

-- 方式1
drop table  if exists XAI0114_NEW;

--方式2
drop table XAI0114_NEW;

清空表

-- 方式1
delete from  XAI0114_NEW;

--方式2
truncate table XAI0114_NEW;
wangzhongyudie
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
ClickHouse的数据类型
胜利的曙光的博客
09-27 2959
ClickHouse支持的基本数据类型介绍。
Clickhouse的数据类型(一)
Xlucas的博客
03-22 3671
Clickhouse的数据类型细说
ClickHouse常见的DDL语句
SimpleSimpleSimples的博客
08-13 874
user_id UInt32 comment '用户标识',name String comment '用户名',create_date DateTime DEFAULT now() comment '创建时间',sex Enum8('男' = 1, '女' = 2) comment '性别',class_name LowCardinality(String) comment'班级名称',phone Nullable(String) comment'手机号码'
ClickHouse数据类型
weixin_43597208的博客
04-07 308
➢ Datetime 接受年-月-日 时:分:秒的字符串比如 ‘2019-12-16 20:50:10’➢ Decimal128(s)，相当于 Decimal(38-s,s)，有效位数为 1~38。➢ Decimal64(s)，相当于 Decimal(18-s,s)，有效位数为 1~18。T 可以是任意类型，包含数组类型。➢ Decimal32(s)，相当于 Decimal(9-s,s)，有效位数为 1~9。日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。
Clickhouse建表语法、视图语法、数据表DDL（数据定义语言）、数据DML（数据操作语言）...
HiterCoder
03-01 6355
参考官网：https://clickhouse.tech/docs/zh/sql-reference/statements/create/，更多详细文档可以参考官网，强烈推荐。 1、Clickhouse创建数据库，CREATE DATABASE，该查询用于根据指定名称创建数据库。 1 CREATE DATABASE [IF NOT EXISTS] db_name 数据库其实只是用于存放表的一...
ClickHouse入门学习笔记
11-12
综上所述，学习ClickHouse涉及其基本概念、数据类型、存储结构、SQL语法、副本和集群管理、查询优化等多个方面，为大数据分析提供了强大且高效的解决方案。掌握这些知识点将有助于构建和优化ClickHouse系统，实现...
SQL数据库学习笔记总结(数据库创建和基本概念)
12-14
了解这些基本概念后，我们可以进一步深入学习SQL的查询优化、索引、视图、存储过程、触发器等内容，以及如何使用T-SQL进行复杂的事务处理和数据操作。掌握这些知识对于成为合格的数据库管理员或开发者至关重要。在...
ClickHouse.jl：ClickHouse TCP本机协议的Julia Lang客户端
02-04
3. **SQL 支持**：ClickHouse 支持丰富的 SQL 功能，包括 DDL（数据定义语言）、DML（数据操纵语言）和查询语句。ClickHouse.jl 允许用户利用这些功能，创建表、插入数据、更新记录以及执行复杂的聚合和窗口函数查询...
快速学习-DDL数据定义
01-07
第 4 章 DDL 数据定义 4.1 创建数据库 1）创建一个数据库，数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。 hive (default)> create database db_hive; 2）避免要创建的数据库已经存在错误，增加 if ...
MySQL学习笔记(1) – DDL语句
12-14
总的来说，本篇学习笔记提供了关于MySQL DDL语句的基础操作指南，涵盖从启动服务到基本的数据库和表管理，对于初学者来说是一份不错的入门教程。在实际应用中，还会涉及到更多复杂的DDL操作，如索引的创建和删除，...
clickhouse之主键不具有唯一性约束的问题
普普通通程序猿的博客
11-11 5457
和之前的传统关系型数据库不同，clickhouse的主键不具有唯一性约束，那如果有一些需要数据去重的场景我们该咋办呢？别急，我们通过问答的方式来了解一下吧~~ Q:为什么clickhouse在设计的时候主键不具有唯一性约束呢？ A: 坦白讲，我也不清楚，因为我没看到过对此有官方的说明，我的理解是因为clickhouse采用的是稀疏索引，如果在插入的时候还要进行唯一性检查，将会额外的耗费一些时间，不像使用稠密索引的方式能够更快定位到重复，毕竟clickhouse的设计理念是为了一个“快”字，而且使用的场景更多
ClickHouse数据类型介绍
努力努力再努力！的博客
07-23 2411
ClickHouse基本操作 1.ClickHouse数据类型介绍 Mysql Hive ClickHouse Byte TINYINT Int8 Short SMALLINT Int16 Int INT Int32 Long BIGINT Int64 Varchar STRING String TimeStamp TIMESTAMP DateTime Float FLOAT Float32 Double DOUBLE Float64 Boolean BOOL
篇三|ClickHouse的数据类型
jmx_bigdata的博客
10-14 5303
ClickHouse提供了许多数据类型，它们可以划分为基础类型、复合类型和特殊类型。我们可以在system.data_type_families表中检查数据类型名称以及是否区分大小写。 SELECT * FROM system.data_type_families 上面的系统表，存储了ClickHouse所支持的数据类型，注意不同版本的ClickHouse可能数据类型会有所不同，具体如下表所示： ┌─name────────────────────┬─case_insensitive─┬─alias_to
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
clickhouse系列学习笔记——（三）（下）clickhouse基本语法
niuyan666的博客
05-20 1045
三 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─
clickhouse 同步mysql_ClickHouse和他的朋友们（9）MySQL实时复制与实现
weixin_39765796的博客
12-20 149
很多人看到标题还以为自己走错了夜场，其实没有。ClickHouse 可以挂载为 MySQL 的一个从库 ，先全量再增量的实时同步 MySQL 数据，这个功能可以说是今年最亮眼、最刚需的功能，基于它我们可以轻松的打造一套企业级解决方案，让 OLTP 和 OLAP 的融合从此不再头疼。目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操...
clickhouse 直连mysql_Clickhouse最佳实战之MySQL接口操作ClickHouse及DBeaver工具安装与使用...
weixin_29699041的博客
01-25 1871
Clickhouse最佳实战之MySQL接口操作ClickHouse及Beaver工具安装与使用官方网站：https://clickhouse.tech/docs/zh/interfaces/mysql/​clickhouse.tech此实战主要就是使用Clickhouse提供的MySQL线协议，真实的环境中这种连接Clickhouse的方式比较少用，更多的是使用Clickhouse-client...
Clickhouse 导入MySQL中的表定义和数据
vkingnew 的技术博客
04-03 7015
#clickhouse-client :create database dw; :use dw; --导入数据： CREATE TABLE FactSaleOrders ENGINE = MergeTree ORDER BY OrderID AS SELECT * FROM mysql('10.42.134.136:4000', 'dw', 'FactSaleOrders', 'root', ...
mysql连接clickhouse_如何在ClickHouse中使用MySQL客户端
weixin_31423913的博客
02-08 2059
目录介绍安装2.1成功完成安装后，启动ProxySQL。创建ClickHouse用户从MySQL客户端连接到ClickHouse查询ClickHouse之类的MySQL局限性6.1结论– ProxySQL版本2.0.8的新功能和增强功能介绍现在，有很多客户使用ClickHouse，因为它可以可用于列式数据库分析和MySQL数据存档。您可以使用clickhouse-client从ClickHouse...
clickhouse 修改字段类型
最新发布
03-27
在ClickHouse中修改字段类型可以通过以下步骤进行： 1. 创建一个新的表，将原表的数据导入到新表中。 2. 在新表中定义需要修改的字段的新类型。 3. 将原表中的数据插入到新表中，同时进行类型转换。 4. 删除原表。 5. 将新表重命名为原表的名称。 下面是详细的步骤： 1. 创建一个新的表，将原表的数据导入到新表中： ``` CREATE TABLE new_table AS SELECT * FROM old_table; ``` 2. 在新表中定义需要修改的字段的新类型。可以使用ALTER TABLE语句来修改字段类型。例如，将字段的类型从Int32修改为String： ``` ALTER TABLE new_table MODIFY COLUMN column_name String; ``` 3. 将原表中的数据插入到新表中，同时进行类型转换。可以使用INSERT INTO语句来实现。例如，将原表中的数据插入到新表中： ``` INSERT INTO new_table SELECT * FROM old_table; ``` 4. 删除原表： ``` DROP TABLE old_table; ``` 5. 将新表重命名为原表的名称： ``` RENAME TABLE new_table TO old_table; ``` 请注意，在执行这些操作之前，请务必备份您的数据以防止意外情况发生。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值