0
0

大数据-143 - ClickHouse 集群 SQL 超详细实践记录！

点一下关注吧！！！非常感谢！！持续更新！！！

目前已经更新到了：

  • Hadoop（已更完）
  • HDFS（已更完）
  • MapReduce（已更完）
  • Hive（已更完）
  • Flume（已更完）
  • Sqoop（已更完）
  • Zookeeper（已更完）
  • HBase（已更完）
  • Redis （已更完）
  • Kafka（已更完）
  • Spark（已更完）
  • Flink（已更完）
  • ClickHouse（正在更新···）

章节内容

上节我们完成了如下的内容：

  • 副本和分片
  • Distributed 部分
  • 编码实际案例测试

在这里插入图片描述

基本介绍

ClickHouse 是一种用于 OLAP（在线分析处理）的列式数据库，因其高速数据处理能力在大数据分析中备受青睐。ClickHouse 的 SQL 语法与标准 SQL 类似，但由于其专注于分析场景，有一些特殊的扩展。ClickHouse 默认不支持直接 DELETE 或 UPDATE 操作，但可以通过分区管理和合并机制间接清理数据。ClickHouse 提供了很多专门为高效分析而设计的功能。ClickHouse 提供了丰富的聚合函数，如 sum()、avg()、min()、max()、count()。

基本 SQL 语法

ClickHouse 的 SQL 语法与标准 SQL 类似，但由于其专注于分析场景，有一些特殊的扩展。
创建表的时候：

CREATETABLE table_name (
    column1 DataType,
    column2 DataType,...)ENGINE= MergeTree()ORDERBY(primary_key_columns);
  • ENGINE：表引擎，最常用的是 MergeTree 系列。
  • ORDER BY：必须指定排序键，支持对大数据集高效查询。
  • PARTITION BY：按列进行分区（可选）。
  • SAMPLE BY：用于大数据量下的采样查询（可选）。

删除或清理表数据的时候：
ClickHouse 默认不支持直接 DELETE 或 UPDATE 操作，但可以通过分区管理和合并机制间接清理数据。

ALTERTABLE table_name DROPPARTITION partition_expr;

特殊功能

聚合函数

ClickHouse 提供了丰富的聚合函数，如 sum()、avg()、min()、max()、count()。此外，还有以下特殊聚合函数：

SELECT uniqExact(column)FROM table_name;-- 精确去重计数SELECT quantiles(0.5,0.9)(column)FROM table_name;-- 分位数计算

窗口函数

ClickHouse 支持窗口函数，但语法略有不同。常见窗口函数有 row_number()、rank() 等：

SELECTcolumn, rowNumber()OVER(PARTITIONBY partition_column ORDERBY sort_column)FROM table_name;

数组和嵌套类型

ClickHouse 支持数组和嵌套类型，适合处理复杂的数据结构：

SELECT arrayJoin(array)FROM table_name;
  • arrayJoin：将数组展开为多行

MergeTree 引擎

MergeTree 是 ClickHouse 最常用的引擎之一，具备排序、索引和分区的特性，能够高效处理海量数据。

  • ORDER BY：定义主键，数据按照该字段排序。
  • PRIMARY KEY：可以和 ORDER BY 一致，用于快速定位。
  • PARTITION BY：用于数据按逻辑分片，减少查询范围。
  • TTL：设置数据过期时间，自动清理历史数据。

基本状况

目前我是ClickHouse的集群环境：

  • h121.wzk.icu
  • h122.wzk.icu
  • h123.wzk.icu

建立连接

我们随机找一台建立链接

clickhouse-client -m--host h121.wzk.icu --port9001--user default --password [email protected]

新建库

CREATEDATABASE mydatabase;

执行结果如下图所示：
在这里插入图片描述

可以看到对应的路径如下所示：

cd /var/lib/clickhouse/data
ls

执行结果如下图，可以看到我们刚才创建的数据库：
在这里插入图片描述

查看数据库

SHOWDATABASES;

运行结果如下图：
在这里插入图片描述

新建表

# 方式1CREATETABLE my_table(
  title String,
  url String,
  eventTime DateTime)ENGINE= Memory;# 方式2CREATETABLE mydatabase.my_table(
  title String,
  url String,
  eventTime DateTime)ENGINE= Memory;# 方式3CREATETABLE mydatabase.my_table_2(
  title String,
  url String,
  eventTime DateTime)ENGINE= Memory ASSELECT*FROM mydatabase.my_table;

执行结果如下图所示：
在这里插入图片描述

查表结构

DESC my_table;

执行结果如下图：
在这里插入图片描述

插入数据

INSERTINTO my_table VALUES('wzk','123',now());

执行的结果如下所示：
在这里插入图片描述

临时表

CREATETABLE tmp_v1 (
  title String,
  create_time DateTime)ENGINE= Memory;

如果临时表与正常表名字相同，临时表优先。
临时表的引擎只能是Memory，数据是临时的，断点数据就没了。
更多的是在ClickHouse内部，是数据在集群间传播度的载体。

分区表

创建新表

CREATETABLE partition_v1 (`id` String,`url` String,`eventTime`Date)ENGINE= MergeTree()PARTITIONBY toYYYYMM(eventTime)ORDERBY id;

执行结果如下所示：
在这里插入图片描述

只有合并树（MergeTree）家族的表引擎支持分区表，可以利用分区表，做定位查询，缩小查询范围。分区字段不易设置的太小。

插入数据

INSERTINTO partition_v1 (id, url, eventTime)VALUES('1','http://example.com/page1','2024-01-01'),('2','http://example.com/page2','2024-01-15'),('3','http://example.com/page3','2024-02-01'),('4','http://example.com/page4','2024-02-15'),('5','http://example.com/page5','2024-03-01'),('6','http://example.com/page6','2024-03-15');

执行结果如下图所示：
在这里插入图片描述

查询数据

SELECT*FROM partition_v1;

执行结果如下所示：
在这里插入图片描述

查看分区

SELECTtable,partition, path FROM system.parts WHEREtable='partition_v1';

执行结果如下图所示：
在这里插入图片描述

视图表

  • 普通视图：不保存数据，只是一层单纯的SELECT查询映射，起着简化查询的作用
  • 物化视图：保存数据，源表被写入数据，物化视图也会同步更新
  • POPULATE修饰符：决定在创建物化视图的过程中是否将源表的数据同步到物化视图。

表基本操作

只有 MergeTree、Merge、Distribution这三类表引擎支持ALTER操作！！！

追加字段

ALTERTABLE partition_v1 ADDCOLUMN os String default'mac';ALTERTABLE partition_v1 ADDCOLUMN ip String after id;DESC partition_v1;

执行结果如下：
在这里插入图片描述

修改类型

注意：类型需要互相兼容

ALTERTABLE partition_v1 modifycolumn ip IPv4;DESC partition_v1;

执行结果如下图所示：
在这里插入图片描述

修改备注

ALTERTABLE partition_v1 COMMENTCOLUMN id '主键ID';DESC partition_v1;

执行结果如下图所示：
在这里插入图片描述

删除字段

ALTERTABLE partition_v1 DROPCOLUMN url;DESC partition_v1;

注意，删除字段会把该字段下的数据一起删除：
在这里插入图片描述

移动表

renameTABLEdefault.partition_v1 to mydatabase.partition_v1;USE mydatabase;SHOWTABLES;

执行结果如下图所示：
在这里插入图片描述

分区操作

查看分区

SELECT partition_id, name,table,databaseFROM system.parts wheretable='partition_v1';

执行结果如下所示：
在这里插入图片描述

删除分区

ALTERTABLE partition_v1 DROPPARTITION202401;SELECT partition_id, name,table,databaseFROM system.parts wheretable='partition_v1';

执行结果如下图所示：
在这里插入图片描述

复制分区

ALTERTABLE partition_v2 replacepartition202402FROM partition_v1;

重置分区

ALTERTABLE partition_v1 CLEAR COLUMN ip inpartition202402;
  • 将 ip 列的值清空（设置为默认值）。
  • 清空操作不会删除记录，而是将指定列的值设置为默认值（如 0 或 NULL，具体取决于列的默认设置）。

执行结果如下图所示：
在这里插入图片描述

卸载分区

ALTERTABLE partition_v1 DETACH partition202402;SELECT partition_id, name,table,databaseFROM system.parts wheretable='partition_v1';

执行结果如下图所示：
在这里插入图片描述

转载分区

ALTERTABLE partition_v1 ATTACH partition202402;SELECT partition_id, name,table,databaseFROM system.parts wheretable='partition_v1';

执行结果如下图所示：
在这里插入图片描述

标签： 大数据 clickhouse sql
本文转载自: https://blog.csdn.net/w776341482/article/details/142411367
版权归原作者 武子康 所有， 如有侵权，请联系我们删除。
登录后发布评论

“大数据-143 - ClickHouse 集群 SQL 超详细实践记录！”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

助力网络安全发展，安全态势攻防赛事可视化

2023年全国职业院校技能大赛软件测试赛题第一套-单元测试答案报告

解决Selenium已安装，在pycharm导入时报错

IDEA去除掉虚线，波浪线，和下划线实线的方法

KOI-UI 开源项目教程

开源模型应用落地-qwen模型小试-Qwen2.5-7B-Instruct-玩转ollama（一）

AI4Science(2024年4月总结)：物理驱动及数据驱动深度学习方法用于科学计算问题

文章导航