ClickHouse 数据库相关

小小莫然

已于 2024-01-26 11:28:27 修改

阅读量924

点赞数 19

分类专栏： ClickHouse 文章标签： clickhouse 数据库 adb

于 2024-01-26 11:28:09 首次发布

本文链接：https://blog.csdn.net/xingxin550/article/details/135862281

版权

ClickHouse 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

ClickHouse 数据库相关

创建数据库

CREATE DATABASE [IF NOT EXISTS ] db_name
-- 中括号中的语句写不写都可以

Mysql 引擎创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

引擎参数

host:port — MySQL服务地址
database — MySQL数据库名称
user — MySQL用户名
password — MySQL用户密码

创建表

CREATE TABLE [IF NOT EXISTS ] [db.] table_name [on CLUSTER  cluster] 
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr1]
)ENGINE=engine

创建包含有主键的表

CREATE TABLE [IF NOT EXISTS] [db.]table_name 
(
    `Userid` uint32,
    `username` String ,
    `EventTime` DateTime
 )ENGINE=MergeTree() --表引擎
PRIMARY Key(Userid,username)  --主键
ORDER BY (Userid,username, EventTime)  --排序
PARTITION BY toYYYYMM(EventTime) -- 分区
SETTINGS index_granularity = 8192, index_granularity_bytes = 0;

特别说明

创建表时一定要注意：

如果表中存储数据量超过1亿条数据时，一定要进行分区，以及排序，必要的索引，当不指定索引时，主键以及排序的列明会被识别为索引。
创建表时，如果数据量过大时一定不要将表引擎设置为 Memory（），因为一旦将表引擎设置成Memory()，这是表会存放于内存中，这样会造成严重的后果
partition是否合理，很可能会影响到查询和写入的性能。

**sorting key 与 primary key **

从概念上来说，primary key是作为索引的，sorting key是用来排序的。

primary key一定是sorting key，但sorting key不一定是primary key。

在排序的时候，primary key一定要放在靠前的位置。

Log

Log引擎不支持索引。同样，如果写入表失败，则该表将被破坏，并且从该表读取将返回错误。Log引擎适用于临时数据，write-once 表以及测试或演示目的。

Mysql

MySQL 引擎不支持可为空数据类型，因此，当从MySQL表中读取数据时，NULL 将转换为指定列类型的默认值（通常为0或空字符串）。引擎案例为：

engine = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

Kafka 引擎：与kafka 结合使用：

老版格式：

engine = Kafka(kafka_broker_list, kafka_topic_list, kafka_group_name, kafka_format
      [, kafka_row_delimiter, kafka_schema, kafka_num_consumers])

连接样例：

CREATE TABLE queue2 (
    timestamp UInt64,
    level String,
    message String
  ) ENGINE = Kafka('localhost:9092', 'topic', 'group1')
              SETTINGS kafka_format = 'JSONEachRow',
                       kafka_num_consumers = 4;

新版样例为：

Kafka SETTINGS
  kafka_broker_list = 'localhost:9092',
  kafka_topic_list = 'topic1,topic2',
  kafka_group_name = 'group1',
  kafka_format = 'JSONEachRow',
  kafka_row_delimiter = '\n',
  kafka_schema = '',
  kafka_num_consumers = 2

连接样例为：


  CREATE TABLE queue2 (
    timestamp UInt64,
    level String,
    message String
  ) ENGINE = Kafka SETTINGS kafka_broker_list = 'localhost:9092',
                            kafka_topic_list = 'topic',
                            kafka_group_name = 'group1',
                            kafka_format = 'JSONEachRow',
                            kafka_num_consumers = 4;

必要参数：

kafka_broker_list – 以逗号分隔的 brokers 列表 (localhost:9092)。
kafka_topic_list – topic 列表 (my_topic)。
kafka_group_name – Kafka 消费组名称 (group1)。如果不希望消息在集群中重复，请在每个分片中使用相同的组名。
kafka_format – 消息体格式。使用与 SQL 部分的 FORMAT 函数相同表示方法，例如 JSONEachRow。了解详细信息，请参考 Formats 部分。

可选参数：

kafka_row_delimiter - 每个消息体（记录）之间的分隔符。
kafka_schema – 如果解析格式需要一个 schema 时，此参数必填。例如，普罗托船长需要 schema 文件路径以及根对象 schema.capnp:Message 的名字。
kafka_num_consumers – 单个表的消费者数量。默认值是：1，如果一个消费者的吞吐量不足，则指定更多的消费者。消费者的总数不应该超过 topic 中分区的数量，因为每个分区只能分配一个消费者。

Memory 引擎

重新启动服务器时，表中的数据消失，表将变为空。通常，使用此表引擎是不合理的。但是，它可用于测试，以及在相对较少的行（最多约100,000,000）上需要最高性能的查询。

分布式表引擎

Distributed(cluster, database, table[, sharding_key[, policy_name]])

cluster - 服务为配置中的集群名
database - 远程数据库名
table - 远程数据表名
sharding_key - (可选) 分片key
policy_name - (可选) 规则名，它会被用作存储临时文件以便异步发送数据

如果目标服务器已经有表则采用以下方式进行关联：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] AS [db2.]name2 ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]]) [SETTINGS name=value, ...]

关联表引擎

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
) ENGINE = Join(join_strictness, join_type, k1[, k2, ...])

join_strictness

支持的联接类型
所有标准 SQL JOIN 支持类型:

INNER JOIN，只返回匹配的行。
LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。
RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。
FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。
CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是 不 指定。
JOIN 没有指定类型暗指 INNER. 关键字 OUTER 可以安全地省略。 替代语法 CROSS JOIN 在指定多个表 FROM 用逗号分隔。

ClickHouse中提供的其他联接类型:

LEFT SEMI JOIN 和 RIGHT SEMI JOIN,白名单 “join keys”，而不产生笛卡尔积。
LEFT ANTI JOIN 和 RIGHT ANTI JOIN，黑名单 “join keys”，而不产生笛卡尔积。
LEFT ANY JOIN, RIGHT ANY JOIN and INNER ANY JOIN, partially (for opposite side of LEFT and RIGHT) or completely (for INNER and FULL) disables the cartesian product for standard JOIN types.
ASOF JOIN and LEFT ASOF JOIN, joining sequences with a non-exact match. ASOF JOIN usage is described below.

创建JOIN 右边表

CREATE TABLE id_val_join(`id` UInt32, `val` UInt8) ENGINE = Join(ANY, LEFT, id)

表关联

SELECT * FROM id_val ANY LEFT JOIN id_val_join USING (id) SETTINGS join_use_nulls = 1

插入数据到分区

INSERT INTO table_name (date, product_id, value)
VALUES ('2024-01-25', 1, 10.5), ('2024-01-26', 2, 20.3)

备注

插入数据时，如果单条插入数据过多会出现以下问题：

如果一定要等数据量达到某一个数值才插入，那么当数据源生产较慢时，肯定会带来延迟
- 针对此问题的处理方案为：设定一个超时时间，如果超时时间到了，即使没有积攒够一个批次，也插入到数据库中
大批量插入必然会带来大内存消耗。
如果数据本身不规范，那么大批次数据插入很容易带来"TOO MANY PARTITIONS"的报错。

备份表

BACKUP TABLE table_name TO '/path/to/backup';
--table_name 表示表名
--'/path/to/backup'  表示备份表的地址

还原整个表

RESTORE TABLE table_name FROM '/path/to/backup';
--table_name 表示表名
--'/path/to/backup'  表示备份表的地址

修改表结构

ALTER TABLE [db.]name [ON CLUSTER cluster] ADD|DROP|COMMENT|MODIFY COLUMN

ADD COLUMN — 添加列
DROP COLUMN — 删除列
CLEAR COLUMN — 重置列的值
COMMENT COLUMN — 给列增加注释说明
MODIFY COLUMN — 改变列的值类型，默认表达式以及TTL

添加索引

ALERT TABLE [db.]name ADD INDEX name expression Type type GRANULARITY value [FIRST|AFTER|name]

删除索引

ALTER TABLE [db.]name DROP INDEX name;
--从表元数据中删除索引描述并从磁盘中删除索引文件。

重建二级索引

ALTER TABLE [db.]table MATERIALIZE INDEX name PARTITION parrtition_name
-- 查询在分区parrtition_name 中重建二级索引name

查询优化

ALTER TABLE skip_table ADD INDEX vix my_value TYPE set(100) GRANULARITY 2;
-- skip_table 表名称
-- vix 索引名称
-- set(100)

-- 添加索引样例
--alter table deviceTestDemo ADD index device_id(device_id) type set(100) GRANULARITY 2;

一般跳数索引只应用于新插入的数据，所以只添加索引不会影响查询。需要执行以下代码，让跳数索引进行生效：

ALTER TABLE skip_table MATERIALIZE INDEX vix;
--让索引起作用样例
-- ALTER TABLE deviceTestDemo MATERIALIZE INDEX device_id;

优化后的分页查询

select * from deviceTestDemo where device_id in (select device_id from deviceTestDemo limit 1000000,10);

备注

deviceTestDemo 是需要查询的表名

device_id 是表中的唯一字段（类似于主键）

查看clickHouse 磁盘占用情况

这里查看的是整体的

SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS disk_space
FROM system.parts
GROUP BY
    database,
    table
ORDER BY disk_space DESC

查看某个数据库中的某个表磁盘占用情况

SELECT formatReadableSize(sum(bytes_on_disk)) AS disk_space
FROM system.parts
WHERE (database = 'test') AND (table = 'deviceTestDemo')

**备注 **

当中的test 表示表所在的数据库

deviceTestDemo 表示表名

基于分布式查询表所占用的空间

SELECT
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
    table AS `表名`
FROM system.parts
WHERE database = 'test'
GROUP BY table
ORDER BY table ASC

备注

test 表示数据库名称

优化相关

多表查询优化 :

SELLECT count() FROM A JOIN B ON A.id = B.id WHERE A.a > 10 AND B.b < 100;

上面的查询，会先进行join 操作，然后从结果集里面去执行where 的条件过滤

可以这样子优化：把where 语句放到子查询中，先进性过滤，然后在进行join操作。

SELECT count() FROM 
(SELECT * FROM A WHERE A.a > 10) AS A1
JOIN
(SELECT * FROM B WHERE B.b < 100) AS B1
ON A1.id = B1.id

这样子，参与的join的数据就会少很多。

各种查询

1. 基本查询

1.1 选择所有列


SELECT * FROM your_table;

1.2 指定列和条件

SELECT column1, column2
FROM your_table
WHERE column3 = 'some_value';

2. 聚合查询

2.1 计数

SELECT COUNT(*) FROM your_table;

2.2 求和

SELECT SUM(column1) FROM your_table;

2.3 平均值

SELECT AVG(column1) FROM your_table;

2.4 分组和聚合

SELECT column1, COUNT(*)
FROM your_table
GROUP BY column1;

3.联接查询

3.1 内联接

SELECT *
FROM table1
JOIN table2 ON table1.id = table2.id;

3.2 左外联接

SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

3.3 多表联接

SELECT *
FROM table1
JOIN table2 ON table1.id = table2.id
JOIN table3 ON table1.id = table3.id;

4. 子查询

4.1 标量子查询

SELECT column1,
       (SELECT COUNT(*) FROM another_table WHERE another_table.column2 = your_table.column1) AS count_in_another_table
FROM your_table;

4.2 列子查询

SELECT column1
FROM your_table
WHERE column2 IN (SELECT column2 FROM another_table);

5. 时间序列查询

5.1 时间范围查询

SELECT *
FROM your_table
WHERE event_time >= '2022-01-01' AND event_time < '2022-02-01';

5.2 按时间分组

SELECT toDate(event_time) AS date,
       COUNT(*)
FROM your_table
GROUP BY date;

6. 排名和窗口函数

6.1 排名

SELECT
    column1,
    column2,
    RANK() OVER (ORDER BY column3 DESC) AS ranking
FROM your_table;

6.2 窗口函数

SELECT
    column1,
    column2,
    SUM(column3) OVER (PARTITION BY column1 ORDER BY event_time) AS running_total
FROM your_table;

更新数据

MergeTree 引擎 clickHouse 不能直接更新数据，而是采用插入新数据的方式，然后通过后台进行任务合并，来达到更新的目的

在官方还提供了另外一种方式：

ALTER TABLE your_merge_tree_table UPDATE value = value + 10 WHERE date >= '2022-01-01';
-- your_merge_tree_table 需要更新的表名
--value 表示列名
-- data 表示更新的条件

ReplacingMergeTree 进行数据更新

ALTER TABLE your_replacing_merge_tree_table UPDATE value = 150 WHERE id = 123;
-- your_merge_tree_table 需要更新的表名
--value 表示列名
-- data 表示更新的条件

以上为两种更新数据的方式

删除数据

删除数据库

DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster]
-- cluster 表示集群中的某个节点

删除数据表

DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]

小小莫然

关注

19
点赞
踩
17

收藏

觉得还不错? 一键收藏
打赏
0
评论
ClickHouse 数据库相关

重新启动服务器时，表中的数据消失，表将变为空。通常，使用此表引擎是不合理的。但是，它可用于测试，以及在相对较少的行（最多约100,000,000）上需要最高性能的查询。primary key一定是sorting key，但sorting key不一定是primary key。从概念上来说，primary key是作为索引的，sorting key是用来排序的。同样，如果写入表失败，则该表将被破坏，并且从该表读取将返回错误。上面的查询，会先进行join 操作，然后从结果集里面去执行where 的条件过滤。
复制链接

扫一扫