当前位置： 首页 > news >正文

clickhouse学习笔记（四）库、表、分区相关DDL操作

news 来源：原创 2024/7/8 12:35:49

目录

一、数据库操作

1、创建数据库

2、查询及选择数据库

3、删除数据库

二、数据表操作

1、创建表

2、删除表

3、基本操作

①追加新字段

②修改字段类型或默认值

③修改字段注释

④删除已有字段

⑤移动数据表（重命名）

⑥清空表

三、默认值

1、默认值三种定义方法之间的不同：

2、可以使用 ALTER 语句修改默认值，例如:

四、临时表

 五、分区表

1、创建分区

2、删除指定分区

3、复制分区数据

4、重置分区数据

5、装载与卸载分区

六、视图

七、分布式DDL执行

一、数据库操作

1、创建数据库

创建数据库的语法如下

CREATE DATABASE IF NOT EXISTS db_name[ENGINE = engine]

数据库也支持设置引擎，[ENGINE = engine]表示数据库所使用的的引擎类型，当不加[ENGINE = engine]时会默认为使用默认引擎Ordinary

目前支持5种引擎如下

  • Ordinary：默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明，在此数据库下可以使用任意类型的表引擎
  • Dictionary：字典引擎，此类数据库会自动为所有数据字典创建它们的数据表，关于数据字典的详细介绍会在后面展开
  • Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除
  • Lazy：日志引擎，此类数据库下只能使用 Log 系列的表引擎，关于 Log 表引擎的详细介绍会后续章节展开
  • MySQL：MySQL 引擎，此类数据库下会自动拉取远端 MySQL 中的数据，并为它们创建 MySQL 表引擎的数据表，关于MySQL表引擎的详细介绍也会在后续章节展开。

数据库的实质就是物理磁盘上的一个目录文件，在执行创建语句后安装路径下会创建对应名字的目录文件

2、查询及选择数据库

SHOW DATABASES;USE DATABASES;

3、删除数据库

DROP DATABASE [IF EXISTS] db_name;

二、数据表操作

表也是在物理磁盘上的一个目录文件，会在数据库的目录下，而数据是在表的目录下的文本文件

1、创建表

clickhouse提供了三种建表方式：

第一种：

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (column_name1 type [DEFAULT|MATERIALIZED|ALIAS expr],column_name2 type [DEFAULT|MATERIALIZED|ALIAS expr],......
) ENGINE = engine#使用 [db_name.] 参数可以为数据表指定数据库，如果不指定此参数，则默认会使用 default 数据库

第二种：支持在不同的数据库之间复制表结构

CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name1 AS [db_name2.]table_name2 [ENGINE = engine]

第三种：通过 SELECT 子句的形式创建，同时还会将 SELECT 子句查询的数据顺带写入

CREATE TABLE [IF NOT EXISTS] [db_name].table_name ENGINE = engine AS SELECT ...
#如下
CREATE TABLE IF NOT EXISTS db.not_exists_table ENGINE = Memory AS SELECT * FROM db.exists_table

2、删除表

DROP TABLE [IF EXISTS] [db_name.]table_name

3、基本操作

目前只有 MergeTree、Merge 和 Distributed 这三类表引擎支持 ALTER 查询 

①追加新字段

ALTER TABLE table_name ADD COLUMN [IF NOT EXISTS] 字段名 [类型] [默认值] [插在哪个字段后面]

②修改字段类型或默认值

ALTER TABLE table_name MODIFY COLUMN [IF NOT EXISTS] 字段名 [类型] [默认值]

③修改字段注释

ALTER TABLE table_name COMMENT COLUMN [IF EXISTS] 字段名 'some comment'

④删除已有字段

ALTER TABLE table_name DROP COLUMN [IF EXISTS] name

⑤移动数据表（重命名）

在 Linux 系统中，mv 命令的本意是将一个文件从原始位置 A 移动到目标位置 B，但是如果位置 A 与位置 B 相同，则可以变相实现重命名的作用。ClickHouse 的 RENAME 查询就与之有着异曲同工之妙，RENAME 语句的完整语法如下所示：

RENAME TABLE [db_name1.]table_name1 TO [db_name2.]table_name2, [db_name1.]table_name3 TO [db_name2.]table_name3......

RENAME 可以修改数据表的名称，如果将原始数据库与目标数据库设为不同的名称，那么就可以实现数据表在两个数据库之间移动的效果，并且还可以同时移动多张 ，但是只能在单个节点范围内移动，即同一台服务器，而不是集群中的其他节点

⑥清空表

TRUNCATE TABLE [IF EXISTS] [db_name.]table_name

三、默认值

表字段支持三种默认值表达式的定义方法，分别是 DEFAULT、MATERIALIZED 和 ALIAS，有默认值且没有明确定义数据类型的以默认值为主，有明确数据类型的以定义的数据类型为主，如下：

CREATE TABLE table_name (    id String,    col1 DEFAULT 100,    col2 String DEFAULT col1
) ENGINE=Memory

其中 col1 字段根据默认值被推断为 UInt8；而 col2 字段由于同时定义了数据类型和默认值，所以它最终的数据类型来自明确定义的 String。

1、默认值三种定义方法之间的不同：

  • 1）数据写入：在数据写入时，只有 DEFAULT 类型的字段可以出现在 INSERT 语句中，而 MATERIALIZED 和 ALIAS 都不能被显式赋值，它们只能依靠计算取值。例如试图为 MATERIALIZED 类型的字段写入数据，将会得到如下的错误。
DB::Exception: Cannot insert column URL,because it is MATERIALIZED column..
  • 2）数据查询：在数据查询时，只有 DEFAULT 类型的字段可以通过 SELECT * 返回，而 MATERIALIZED 和 ALIAS 类型的字段不会出现在 SELECT * 查询的返回结果集中。
  • 3）数据存储：在数据存储时，只有 DEFAULT 和 MATERIALIZED 类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如 TinyLog 表引擎)，那么这些列字段将会拥有物理存储。而 ALIAS 类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。

2、可以使用 ALTER 语句修改默认值，例如:

ALTER TABLE [db_name.]table_name MODIFY COLOMN col_name DEFAUET value

修改动作并不会影响数据表内先前已经存在的数据，但是默认值的修改有诸多限制，例如在 MergeTree 表引擎中，它的主键字段是无法被修改的；而某些表引擎则完全不支持修改（例如 TinyLog）。

四、临时表

创建临时表的方法是在普通表的基础之上添加 TEMPORARY 关键字

相比普通表而言，临时表有如下两点特殊之处：

  • 它的生命周期是会话绑定的，所以它只支持 Memory 表引擎，如果会话结束，数据表就会被销毁;
  • 临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数;
  • 临时表的优先级是大于普通表的。当临时表和普通表表名称相同的时候，会优先读取临时表的数据

 五、分区表

不是所有的表引擎都支持分区，目前只有合并树（mergeTree）家族系列的表引擎才支持数据分区

1、创建分区

创建方式如下  案例：将日期转化成了年月分区

CREATE TABLE partition_v1 (ID String,URL String,EventDate Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY ID

写入数据

INSERT INTO partition_v1 VALUES ('a1', 'www.a1.com', '2019-05-01'), ('a2', 'www.a2.com', '2019-06-02')

通过system.parts系统表查询数据表的分区状态

SELECT table, partition, path FROM system.parts WHERE table = 'partition_v1'

 可以看到建立了两个分区，且每个分区对应一个独立的文件目录，所以当查询时过滤分区可以直接跳过不满足条件的分区

2、删除指定分区

ALTER TABLE table_name DROP PARTITION partition_expr
案例如下：
ALTER TABLE partition_v1 DROP PARTITION 201906

3、复制分区数据

 ClickHouse 支持将 A 表的分区数据复制到 B 表，语法如下

ALTER TABLE B REPLACE PARTITION partition_expr FROM A
案例如下：
假设有一个数据表 partition_v2，并且与之前 partition_v1 的分区键和表结构完全相同，那么如果想将 partition_v1 中 5 月份的数据导入到 partition_v2中，就可以这么做
ALTER TABLE partition_v2 REPLACE PARTITION 201905 FROM partition_v1

不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提条件：

  • 两张表需要拥有相同的分区键;
  • 它们的表结构完全相同;

4、重置分区数据

如果数据表某一列的数据有误，需要将其重置为默认值，此时可以使用下面的语句实现:

ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr

首先如果声明了默认值表达式，那么以表达式为准；否则以相应数据类型的默认值为准，比如 String 类型的默认值就是空字符串。 

5、装载与卸载分区

表分区可以通过 DETACH 语句卸载，分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的 detached 子目录下。而装载分区则是反向操作，它能够将 detached 子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景。卸载某个分区的语法如下所示：

ALTER TABLE table_name DETACH PARTITION partition_expr

假设有一个分区表 partition_v3，里面有很多月的数据，那么执行下面的语句就可以将该表中整个 8 月份的分区卸载。

ALTER TABLE partition_v3 DETACH PARTITION 201908

此时再次查询这张表，会发现其中 2019 年 8 月份的数据已经没有了。而进入 partition_v3 的磁盘目录，则可以看到被卸载的分区目录已经被移动到了 detached 目录中。

记住，一旦分区被移动到了 detached 子日录，就代表它已经脱离了 ClickHouse 的管理，ClickHouse 并不会主动清理这些文件。这此分区文件会一直存在，除非我们主动删除或者使用 ATTACH 语句重新装载它们。装载某个分区的完整语法如下所示：

ALTER TABLE table_name ATTACH PARTITION partition_expr

再次执行下面的语句，就可以将刚才已被卸载的 201908 分区重新装载回去:

ALTER TABLE partition_v3 ATTACH PARTITION 201908

六、视图

clickhouse有普通和物化视图两种，物化视图有独立的存储，普通的和关系型数据库的视图类似，只是一层简单的查询代理，创建普通视图的语法如下

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT...

物化视图需要指定表引擎，数据保存形式由表引擎决定，创建语法如下

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]view_name [TO [db.]name] ENGINE = engine [POPULATE] AS SELECT ...

 案例如下

-- 物化视图本质上可以看成是一张特殊的数据表，在创建的时候也需要指定引擎
CREATE MATERIALIZED VIEW girls_view_1 ENGINE=TinyLog()
AS SELECT id, name, age FROM girls;

注意：

  1. 因为物化视图是可以存储数据的，所以当girls表被写入数据时，物化视图也会同步更新 
  2. 物化视图只会同步创建后更新的数据，如果想在创建的时候就把数据同步过来需要POPULATE 使用案例如下
-- 只需要在 AS SELECT 的前面加上 POPULATE 即可
-- 此时表 girls 的数据，更准确的说是 SELECT 查询得到的结果集才会进入物化视图中
CREATE MATERIALIZED VIEW girls_view_1 ENGINE=TinyLog()
POPULATE AS SELECT id, name, age FROM girls;

视图可以用show table  和drop table命令来展示和删除，且视图名不能与表名重复

七、分布式DDL执行

将一条普通的 DDL 语句转换成分布式执行十分简单，只需加上 ON CLUSTER cluster_name 声明即可。例如，执行下面的语句后将会对 ch_cluster 集群内的所有节点广播这条 DDL 语句。

CREATE TABLE partition_v4 ON CLUSTER ch_cluster(ID String,URL String,EventDate Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY ID

 

相关文章：

  • 如何解决虚拟仿真教学中的设备限制和卡顿问题？|点量云流技术解决方案
  • performance_schema.events_statements_current
  • MavenPlus插件的基础功能完善
  • JAVA面试（六）
  • JAVA语言开发的一套（智慧工地监管系统源码）让工地变得更加“聪明”
  • 接口联调测试工作总结
  • Python 设计模式(第2版) -- 第二部分（结构型模式）
  • Java启动jar设置内存分配详解
  • JWT整合Gateway实现鉴权(RSA与公私密钥工具类)
  • 示例:WPF中DataGrid简单设置合并列头
  • 数据结构和算法之复杂度比较
  • 数据治理：如何通过优化数据提取流程提高数据分析效果
  • 使用 Python 中的美丽汤进行网络数据解析的完整指南
  • python从入门到精通6:变量类型
  • ES6数组新增API
  • [笔记] php常见简单功能及函数
  • __proto__ 和 prototype的关系
  • Angular js 常用指令ng-if、ng-class、ng-option、ng-value、ng-click是如何使用的？
  • CSS盒模型深入
  • Fastjson的基本使用方法大全
  • JDK9: 集成 Jshell 和 Maven 项目.
  • Laravel 中的一个后期静态绑定
  • vue从入门到进阶：计算属性computed与侦听器watch（三）
  • 服务器从安装到部署全过程（二）
  • 开发了一款写作软件(OSX,Windows)，附带Electron开发指南
  • 浏览器缓存机制分析
  • 猫头鹰的深夜翻译：JDK9 NotNullOrElse方法
  • 全栈开发——Linux
  • 如何使用 JavaScript 解析 URL
  • 实习面试笔记
  • 使用 Node.js 的 nodemailer 模块发送邮件（支持 QQ、163 等、支持附件）
  • 使用docker-compose进行多节点部署
  • 长三角G60科创走廊智能驾驶产业联盟揭牌成立，近80家企业助力智能驾驶行业发展 ...
  • 关于Kubernetes Dashboard漏洞CVE-2018-18264的修复公告
  • ​人工智能之父图灵诞辰纪念日，一起来看最受读者欢迎的AI技术好书
  • #多叉树深度遍历_结合深度学习的视频编码方法--帧内预测
  • （超详细）语音信号处理之特征提取
  • （翻译）Entity Framework技巧系列之七 - Tip 26 – 28
  • （附源码）spring boot火车票售卖系统 毕业设计 211004
  • (附源码)计算机毕业设计ssm电影分享网站
  • （十八）devops持续集成开发——使用docker安装部署jenkins流水线服务
  • (十五)使用Nexus创建Maven私服
  • （一）UDP基本编程步骤
  • (转) SpringBoot:使用spring-boot-devtools进行热部署以及不生效的问题解决
  • ***原理与防范
  • ./configure、make、make install 命令
  • .NET 4.0中使用内存映射文件实现进程通讯
  • .NET 8.0 中有哪些新的变化？
  • .Net Core/.Net6/.Net8 ，启动配置/Program.cs 配置
  • .Net Core与存储过程(一)
  • .NET 编写一个可以异步等待循环中任何一个部分的 Awaiter
  • .net 无限分类
  • .NET简谈互操作(五：基础知识之Dynamic平台调用)
  • ::什么意思
  • @Transactional 详解