ToB企服应用市场:ToB评测及商务社交产业平台

标题: 大数据-143 - ClickHouse 集群 SQL 超详细实践记录！ [打印本页]

---

作者: 大号在练葵花宝典    时间: 2024-10-2 21:41
标题: 大数据-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 类似，但由于其专注于分析场景，有一些特殊的扩展。
创建表的时候：

1. CREATE TABLE table_name (
2.     column1 DataType,
3.     column2 DataType,
4.     ...
5. ) ENGINE = MergeTree()
6. ORDER BY (primary_key_columns);

复制代码

• ENGINE：表引擎，最常用的是 MergeTree 系列。
  
• ORDER BY：必须指定排序键，支持对大数据集高效查询。
  
• PARTITION BY：按列举行分区（可选）。
  
• SAMPLE BY：用于大数据量下的采样查询（可选）。
  

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

1. ALTER TABLE table_name DROP PARTITION partition_expr;

复制代码

特殊功能

聚合函数

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

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

复制代码

窗口函数

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

1. SELECT column, rowNumber() OVER (PARTITION BY partition_column ORDER BY sort_column)
2. FROM table_name;

复制代码

数组和嵌套范例

ClickHouse 支持数组和嵌套范例，适合处理复杂的数据布局：

1. 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
  

建立连接

我们随机找一台建立链接

1. clickhouse-client -m --host h121.wzk.icu --port 9001 --user default --password clickhouse@wzk.icu

复制代码

新建库

1. CREATE DATABASE mydatabase;

复制代码

执行效果如下图所示：

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

1. cd /var/lib/clickhouse/data
2. ls

复制代码

执行效果如下图，可以看到我们刚才创建的数据库：

查看数据库

1. SHOW DATABASES;

复制代码

运行效果如下图：

新建表

1. # 方式1
2. CREATE TABLE my_table(
3.   title String,
4.   url String,
5.   eventTime DateTime
6. ) ENGINE = Memory;
8. # 方式2
9. CREATE TABLE mydatabase.my_table(
10.   title String,
11.   url String,
12.   eventTime DateTime
13. ) ENGINE = Memory;
15. # 方式3
16. CREATE TABLE mydatabase.my_table_2(
17.   title String,
18.   url String,
19.   eventTime DateTime
20. ) ENGINE = Memory AS SELECT * FROM mydatabase.my_table;

复制代码

执行效果如下图所示：

查表布局

1. DESC my_table;

复制代码

执行效果如下图：

插入数据

1. INSERT INTO my_table VALUES ('wzk', '123', now());

复制代码

执行的效果如下所示：

暂时表

1. CREATE TABLE tmp_v1 (
2.   title String,
3.   create_time DateTime
4. ) ENGINE = Memory;

复制代码

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

创建新表

1. CREATE TABLE partition_v1 (
2.   `id` String,
3.   `url` String,
4.   `eventTime` Date
5. ) ENGINE = MergeTree()
6. PARTITION BY toYYYYMM(eventTime)
7. ORDER BY id;

复制代码

执行效果如下所示：

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

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

复制代码

执行效果如下图所示：

查询数据

1. SELECT * FROM partition_v1;

复制代码

执行效果如下所示：

查看分区

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

复制代码

执行效果如下图所示：

视图表

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

表基本操作

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

1. ALTER TABLE partition_v1 ADD COLUMN os String default 'mac';
2. ALTER TABLE partition_v1 ADD COLUMN ip String after id;
3. DESC partition_v1;

复制代码

执行效果如下：

修改范例

注意：范例需要相互兼容

1. ALTER TABLE partition_v1 modify column ip IPv4;
2. DESC partition_v1;

复制代码

执行效果如下图所示：

修改备注

1. ALTER TABLE partition_v1 COMMENT COLUMN id '主键ID';
2. DESC partition_v1;

复制代码

执行效果如下图所示：

删除字段

1. ALTER TABLE partition_v1 DROP COLUMN url;
2. DESC partition_v1;

复制代码

注意，删除字段会把该字段下的数据一起删除：

移动表

1. rename TABLE default.partition_v1 to mydatabase.partition_v1;
2. USE mydatabase;
3. SHOW TABLES;

复制代码

执行效果如下图所示：

分区操作

查看分区

1. SELECT partition_id, name, table, database FROM system.parts where table = 'partition_v1';

复制代码

执行效果如下所示：

删除分区

1. ALTER TABLE partition_v1 DROP PARTITION 202401;
2. SELECT partition_id, name, table, database FROM system.parts where table = 'partition_v1';

复制代码

执行效果如下图所示：

复制分区

1. ALTER TABLE partition_v2 replace partition 202402 FROM partition_v1;

复制代码

重置分区

1. ALTER TABLE partition_v1 CLEAR COLUMN ip in partition  202402;

复制代码

• 将 ip 列的值清空（设置为默认值）。
  
• 清空操作不会删除记录，而是将指定列的值设置为默认值（如 0 或 NULL，详细取决于列的默认设置）。
  

执行效果如下图所示：

卸载分区

1. ALTER TABLE partition_v1 DETACH partition 202402;
2. SELECT partition_id, name, table, database FROM system.parts where table = 'partition_v1';

复制代码

执行效果如下图所示：

转载分区

1. ALTER TABLE partition_v1 ATTACH partition 202402;
2. SELECT partition_id, name, table, database FROM system.parts where table = 'partition_v1';

复制代码

执行效果如下图所示：

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4