Clickhouse(5)---Clickhouse语法

最新推荐文章于 2024-05-15 06:31:54 发布
最新推荐文章于 2024-05-15 06:31:54 发布
阅读量1.3k 收藏 2
点赞数 2
分类专栏： Clickhouse 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Abner_G/article/details/120977754
版权

Clickhouse语法

DDL基础

clickhouse建表必须指定引擎！
clickhouse建表必须指定引擎！
clickhouse建表必须指定引擎！

目前只有MergeTree、Merge和Distributed这三类表引擎支持 ALTER修改,所以在进行alter操作的时候注意表的引擎!

创建表

方式1

完整语法
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

CREATE TABLE IF NOT EXISTS test_alter1
(
    `id` Int8,
    `name` String default 'abner' comment '用户'
)
ENGINE = MergeTree()
primary key id
order by id
-- 参数设置   索引力度  默认是 8192
SETTINGS index_granularity = 8192;

方式2

在不同的数据库之间复制表结构

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

-- 将 A 库下的 a 表拷贝一份到 B 库下的 b 表, 注意：引擎可以更换
CREATE TABLE IF NOT EXISTS A.a AS B.b ENGINE = TinyLog

方式3

根据 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

集群创建表+副本

在这里插入图片描述

修改表

只有 MergeTree支持表结构的修改

-- 添加字段
alter table test_alter1 add column age UInt8 ;
alter table test_alter1 add column gender String after name ;

-- 删除字段
alter table test_alter1 drop column age ;

-- 修改表字段类型
alter  table  test_alter1 modify column  gender UInt8 default 0 ;
-- 修改表字段注解
alter table test_alter1 comment column name '用户名' ;

-- 修改表名
rename table test_alter1 to t1 ;
-- 修改多张表名
rename table test_alter1 to t2 , t1 to tt1 ;
-- 移动表到另一数据库中
rename table t2 to test1.t ;
-- 查看数据库下的所有的表
show tables ;
show tables from cktest ;

DML基础

插入数据

方式1

VALUES格式的常规语法

insert into test_alter values ( 1,'abner'),(2,'candy'),(3,'Kim'),( 1,'abner'),(2,'candy'),(3,'Kim');

方式2

这种方式必须指定数据库
文件导入，这里使用csv导入。【支持的数据文件格式](https://clickhouse.com/docs/en/interfaces/formats/#formats)】
在这里插入图片描述

cat user.txt | clickhouse-client -q 'insert into cktest.test_alter format CSV

在这里插入图片描述

clickhouse-client -q 'insert into cktest.test_alter format CSV' < user.txt

在这里插入图片描述可以执行数据行属性的分割符

clickhouse-client --format_csv_delimiter='-' -q 'insert into default.test_load1 format CSV'  <   user.txt

方式3

INSERT INTO [db.]table [(c1, c2, c3…)] SELECT …

insert into test_alter2 select * from test_alter;

在这里插入图片描述
图中表一用传统VALUES语句分两次插入12条数据，表二是使用方式3一次插入12条数据。发现在文件中一次插入12条的列文件，比分两次插入12条的列文件小。
表1因为两次插入是两个数据块，表2是一个数据块。

ClickHouse内部所有的数据操作都是面向Block数据块的，所以INSERT查询最终会将数据转换为Block数据块。也正因如此，INSERT语句在单个数据块的写入过程中是具有原子性的。在默认的情况下，每个数据块最多可以写入1048576行数据（由max_insert_block_size参数控制）。也就是说，如果一条INSERT语句写入的数据少于max_insert_block_size行，那么这批数据的写入是具有原子性的，即要么全部成功，要么全部失败。需要注意的是，只有在ClickHouse服务端处理数据的时候才具有这种原子写入的特性，例如使用JDBC或者HTTP接口时。因为max_insert_block_size参数在使用CLI命令行或 者INSERT SELECT子句写入时是不生效的。

更新删除数据

只有MergeTree引擎的数据才能修改，更新功能不支持更新有关主键或分区键的列

操作数据

Clickhouse通过alter方式实现更新、删除，它把update、delete操作叫做mutation(突变)。
mutation 官方链接
mutation与标准的update、delete区别
标准SQL的更新、删除操作是同步的，即客户端要等服务端反回执行结果（通常是int值）；而Clickhouse的update、delete是通过异步方式实现的，当执行update语句时，服务端立即反回，但是实际上此时数据还没变，而是排队等着。
它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚。
语法

ALTER TABLE [db.]table DELETE WHERE filter_expr
ALTER TABLE [db.]table UPDATE column1 = expr1 [, …] WHERE filter_expr

查看mutation队列

SELECT
    database,
    table,
    command,
    create_time,
    is_done
FROM system.mutations
LIMIT 10

┌─database─┬─table─────┬─command─────────────┬─────────create_time─┬─is_done─┐
│ cktest   │ test_muta │ DELETE WHERE id = 3 │ 2021-10-27 14:10:32 │       1 │
└──────────┴───────────┴─────────────────────┴─────────────────────┴─────────┘
字段含义
database库名
table表名
command更新/删除语句
create_timemutation任务创建时间，系统按这个时间顺序处理数据变更
is_done是否完成，1为完成，0为未完成

Mutation具体过程
首先，使用where条件找到需要修改的分区；
然后，重建每个分区，用新的分区替换旧的，分区一旦被替换，就不可回退；
对于每个分区，可以认为是原子性的；但对于整个mutation，如果涉及多个分区，则不是原子性的。

注意事项
更新功能不支持更新有关主键或分区键的列
更新操作没有原子性，即在更新过程中select结果很可能是一部分变了，一部分没变，从上边的具体过程就可以知道
更新是按提交的顺序执行的
更新一旦提交，不能撤销，即使重启clickhouse服务，也会继续按照system.mutations的顺序继续执行
已完成更新的条目不会立即删除，保留条目的数量由finished_mutations_to_keep存储引擎参数确定。 超过数据量时旧的条目会被删除
更新可能会卡住，比如update intvalue='abc’这种类型错误的更新语句执行不过去，那么会一直卡在这里，此时，可以使用KILL MUTATION来取消
作者：user0650 原文地址
链接：https://www.jianshu.com/p/521f2d1611f8
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

条件删除数据
alter table test_muta delete where id=3 ;  -- 一定加条件
条件更新数据
ALTER TABLE test_muta
UPDATE name = 'abner' WHERE id = '2' ;

在这里插入图片描述

通过操作分区操作数据

分区是表的分区，具体的DDL操作关键词是 PARTITION BY，指的是一个表按照某一列数据（比如日期）进行分区，对应到最终的结果就是不同分区的数据会写入不同的文件中。

创建分区

partition by 关键字

create table test_muta(
	id UInt8 ,
    name String ,
    city String
)engine=MergeTree()
partition  by city
order by id ;
查看分区
SELECT * FROM test_muta;

┌─id─┬─name──┬─city─┐
│  4 │ kim   │ js   │
│  5 │ candy │ js   │
└────┴───────┴──────┘
┌─id─┬─name────┬─city─┐
│  1 │ guochao │ sh   │
└────┴─────────┴──────┘
┌─id─┬─name───┬─city─┐
│  7 │ wallen │ sh   │
└────┴────────┴──────┘
┌─id─┬─name──┬─city─┐
│  2 │ abner │ gz   │
│  6 │ Bob   │ gz   │
└────┴───────┴──────┘
-- 系统表查看分区
SELECT
    name,
    table,
    partition
FROM system.parts
WHERE table = 'test_muta';
┌─name─────────────────────────────────────┬─table─────┬─partition─┐
│ 2ce0f00e3f89ed58e2dadca87cc37b1c_4_4_0_6 │ test_muta │ js        │
│ 639d654382e2ba7be8162fd75112ebd1_1_1_0_6 │ test_muta │ sh        │
│ 8524469df63406197e2087b37543e596_2_2_0_6 │ test_muta │ gz        │
└──────────────────────────────────────────┴───────────┴───────────┘

在这里插入图片描述
添加新数据，合并分区

insert into test_muta values (7,'wallen','sh');
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│  4 │ kim   │ js   │
│  5 │ candy │ js   │
└────┴───────┴──────┘
┌─id─┬─name────┬─city─┐
│  1 │ guochao │ sh   │
└────┴─────────┴──────┘
┌─id─┬─name───┬─city─┐
│  7 │ wallen │ sh   │
└────┴────────┴──────┘
┌─id─┬─name──┬─city─┐
│  2 │ abner │ gz   │
│  6 │ Bob   │ gz   │
└────┴───────┴──────┘

-- 合并分区
optimize table test_muta;
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│  4 │ kim   │ js   │
│  5 │ candy │ js   │
└────┴───────┴──────┘
┌─id─┬─name────┬─city─┐
│  1 │ guochao │ sh   │
│  7 │ wallen  │ sh   │
└────┴─────────┴──────┘
┌─id─┬─name──┬─city─┐
│  2 │ abner │ gz   │
│  6 │ Bob   │ gz   │
└────┴───────┴──────┘
删除分区
-- 删除分区 
alter table test_mutadrop partition 'sh' ;
-- 删除分区以后 , 分区中的所有的数据全部删除
复制分区

clickHouse支持将A表的分区数据复制到B表，这项特性可以用于快速数据写入、多表间数据同步和备份等场景。

不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提 条件：
·两张表需要拥有相同的分区键
·它们的表结构完全相同。

它的完整语法如下：

-- 复制分区（两张表需要拥有相同的分区键，它们的表结构完全相同）
alter table test_muta_back replace partition 'sh' from test_muta;


select * from test_muta_back ;
┌─id─┬─name────┬─city─┐
│  1 │ guochao │ sh   │
│  7 │ wallen  │ sh   │
└────┴─────────┴──────┘
卸载分区

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

-- 卸载分区
alter table test_muta detach partition 'sh';
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│  4 │ kim   │ js   │
│  5 │ candy │ js   │
└────┴───────┴──────┘
┌─id─┬─name──┬─city─┐
│  2 │ abner │ gz   │
│  6 │ Bob   │ gz   │
└────┴───────┴──────┘

在这里插入图片描述

装载分区
alter table test_muta attach partition 'sh';

SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│  4 │ kim   │ js   │
│  5 │ candy │ js   │
└────┴───────┴──────┘
┌─id─┬─name────┬─city─┐
│  1 │ guochao │ sh   │
│  7 │ wallen  │ sh   │
└────┴─────────┴──────┘
┌─id─┬─name──┬─city─┐
│  2 │ abner │ gz   │
│  6 │ Bob   │ gz   │
└────┴───────┴──────┘

在这里插入图片描述

重置分区数据

如果数据表某一列的数据有误，需要将其重置为初始值，如果设置了默认值那么就是默认值数据,如果没有设置默认值,系统会给出默认的初始值,此时可以使用下面的语句实现：
ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr ;
注意: 不能重置主键和分区字段

alter  table  test_muta  clear column name in partition 'sh' ;

SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│  4 │ kim   │ js   │
│  5 │ candy │ js   │
└────┴───────┴──────┘
┌─id─┬─name─┬─city─┐
│  1 │      │ sh   │
│  7 │      │ sh   │
└────┴──────┴──────┘
┌─id─┬─name──┬─city─┐
│  2 │ abner │ gz   │
│  6 │ Bob   │ gz   │
└────┴───────┴──────┘

集群清空表数据

-- TRUNCATE TABLE 表名 on cluster 集群名
TRUNCATE TABLE enterprise.company_info_125city on cluster perftest_1shards_3replicas;

视图

ClickHouse拥有普通和物化两种视图，其中物化视图拥有独立的存储**，而普通视图只是一层简单的查询代理**

普通视图

普通视图不会存储任何数据，它只是一层单纯的SELECT查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强。

语法：

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

-- 普通视图
create view test_muta_view as select * from test_muta where city='gz';

select * from test_muta_view;

┌─id─┬─name──┬─city─┐
│  2 │ abner │ gz   │
│  6 │ Bob   │ gz   │
└────┴───────┴──────┘

物化视图

其实物化视图就是一种特殊的表
物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的完整语法如下所示

create materialized view mv_log engine=Log populate as select * from log ;

物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。POPULATE修饰符决定了物化视图的初始化策略：如果使用了POPULATE修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一并导入，如同执行了INTO SELECT 一般；反之，如果不使用POPULATE修饰符，那么物化视图在创建之后是没有数据的，它只会同步在此之后被写入源表的数据。物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留。

create materialized view test3_view engine = Log populate as select * from tb_test3 ;
-- 建表的时候同步数据 , 当数据更新以后 物化视图中的数据会同步更新 , 但是当删除数据以后,物化视图中的数据不会被删除
SELECT *
FROM test3_view ;
┌─id─┬─name───┬─role─┐
│  1 │ HANGGE │ VIP  │
│  2 │ BENGE  │ VIP  │
│  3 │ PINGGE │ VIP  │
└────┴────────┴──────┘
-- 向源表中擦混入数据
SELECT *
FROM test3_view

┌─id─┬─name──┬─role─┐
│  4 │ TAOGE │ VIP  │
└────┴───────┴──────┘
┌─id─┬─name───┬─role─┐
│  1 │ HANGGE │ VIP  │
│  2 │ BENGE  │ VIP  │
│  3 │ PINGGE │ VIP  │
└────┴────────┴──────┘
-- 删除源表中的数据 , 物化视图中的数据 不会变化   ****  
-- 注意: 数据删除语法只适用于MergeTree引擎的表   基本语法如下
ALTER TABLE db_name.table_name DROP PARTITION '20210601'
ALTER TABLE db_name.table_name DELETE WHERE day = '20210618'
ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>

和表得物理文件在同一个目录
在这里插入图片描述

Abner G
关注
  • 2
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
clickhouse-jdbc（从0.2.4到0.3.2版本）.zip
01-12
5. **性能优化**：Clickhouse-JDBC驱动可能会针对Clickhouse的特性进行优化，如批处理执行、数据压缩和并行查询等，以最大化利用Clickhouse的高性能特性。 6. **分页查询**：在处理大数据时，分页查询是常见的需求...
ClickHouse查询语句详解
m0_67402564的博客
07-28 3281
下面我们会逐一介绍各个子表达式，主要介绍和标准SQL不同的部分，如果某条语句执行报错，则可能是clickhouse版本不支持。
ClickHouse高级查询语法和复杂查询
互联网知识分享
09-15 2252
ClickHouse是一个分布式列式数据库管理系统，具备高性能和高可扩展性。它提供了丰富的查询语法来满足不同的查询需求。下面将介绍一些ClickHouse的高级查询语法。子句GROUP BY子句用于将数据按照某一列或多列进行分组。它通常与聚合函数一起使用，计算每个组的汇总结果。下面是一个示例：SELECTcategory,COUNT(*)FROMtableGROUPBYcategory参数介绍：category: 分组列COUNT(*): 聚合函数，计算每个组的行数。
二百二十四、Kettle——曲线实现从Hive插入更新到ClickHouse（分区字段是month或year
最新发布
2401_84170414的博客
05-15 726
删除ClickHouse数据虽然有几种方法，可以delete，也可以删除分区，也可以truncate，也可以TTL设置保存时间，还可以删除数据文件，但是对这种需要即时删除ClickHouse表中最新的分区数据，无法用delete、TTL等方式实现，而删除分区又太麻烦，刚好这几张表的数据量较少，因此先采用truncate方式，先删除表数据，再全量导入即可。所以，对于相同的插入更新控件配置，导入MySQL插入更新可以，导入ClickHouse插入更新则失败！这两种方式都试过，都可以！
[ClickHouse] 分别插入两条相似数据报：Block with ID xx already exists locally as part xx, ignoring it.
u012395477的博客
02-12 535
在使用ClickHouse过程中出现分别两次插入一条数据内容相似的记录到ReplicatedMergeTree引擎的表中，表中只有一条记录插入成功，第二条插入的记录在表中不存在，且在日志中可以看到报错信息：Block with ID xx already exists locally as part xx, ignoring it. 这说明第二条插入的记录被判定为重复而忽略。通过尝试发现在配置文件config.xml中将MergeTree的InMemory配置屏蔽可以规避该问题。
ClickHouse(06)ClickHouse建表语句DDL详细解析
编程代码，数据学习开发的日常总结。欢迎关注VX公众号：张飞的猪大数据分享
09-16 2689
DEFAULT是在插入的时候计算填充，MATERIALIZED和ALIAS是在查询的时候，或者说用到的时候填充，而EPHEMERAL，有点类似于我们在建表的时候，创建一个变量，一个代码块。特殊编码与通用的压缩算法相比，区别在于，通用的LZ4和ZSTD压缩算法是普适行的，不关心数据的分布特点，而特殊编码类型对于特定场景下的数据会有更好的压缩效果。无论是列级别还是表级别的TTL，都需要依托某个DateTime或Date类型的字段，通过对这个时间字段的INTERVAL操作，来表述TTL的过期时间。
clickhouse-jdbc:ClickHouse 的 JDBC 驱动程序
07-24
ClickHouse JDBC 驱动程序 ... URL 语法： jdbc:clickhouse://<host>:[/] ，例如jdbc:clickhouse://localhost:8123/test JDBC 驱动程序类： ru.yandex.clickhouse.ClickHouseDriver 例如： String url
clickhouse-rs:ClickHouse的Rust客户端库
03-18
5. **错误处理**：`clickhouse-rs`提供了详细的错误类型，帮助开发者调试和处理与ClickHouse通信时可能出现的问题，如网络故障、语法错误或权限问题。 6. **异步支持**：Rust的async/await特性使得`clickhouse-rs`...
clickhouse-odbc-windows.zip
04-07
2. SQL兼容性：虽然ClickHouse有自己的SQL方言，但ODBC驱动尽力使其与标准SQL兼容，允许用户使用更广泛的SQL语法。 3. 并行查询：驱动程序可以利用多线程并行执行查询，提高查询性能。 4. 预编译的语句：预编译的SQL...
Vertamedia-clickhouse-grafana-1.9.5-0-g50d46ef.zip
04-17
4. 查询优化：理解ClickHouse的查询语法和性能调优技巧，编写高效的SQL查询语句，充分利用ClickHouse的列式存储优势。 四、实战应用 1. 监控与报警：通过Grafana的监控功能，可以设置阈值报警，当ClickHouse的性能...
EXISTS子查询
Lujiadexiaoahui的博客
05-08 1399
使用 EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数据；它只返回 TRUE 或 FALSE 值。 使用 EXISTS 引入的子查询语法如下： WHERE[NOT]EXISTS(子查询)
clickhouse之前提查询
高精尖发展
01-23 3051
attach 类create，but 不在硬盘上创建表，假设表已经存在只是向服务添加表的说明，执行之后，服务将知道表的存在 ATTACH TABLE [IF NOT EXISTS] [db.]name [ON CLUSTER cluster] 在启动服务器时使用，服务器将表元数据视为文件保存   check table 1、检查表中的数据是否被破坏，对比文件实际大小与服务器上存储的期...
ClickHouse建表后查询表不存在，同一个查询语句每次查询的结果不一致
lck_csdn的博客
07-14 5550
clickhouse集群见表及查询
高级DBA教你clickhouse case when 写法及clickhouse常用函数汇总全网最详细
热门推荐
nasen512的博客
04-20 1万+
clickhouse数据库case when详细教程
clickhouse常用命令
weixin_43564627的博客
12-17 1275
clickhouse常用命令 TRUNCATE table 表名 ON CLUSTER cluster_name; select COUNT(*) from 表名 ALTER TABLE 表名 ON CLUSTER cluster_name DELETE WHERE 1=1; ALTER TABLE 表名 DROP PARTITION 0 drop table IF EXISTS 表名 drop table IF EXISTS 表名 on CLUSTER cluster_name; DROP
clickhouse 的sql使用
stone_tomcate的博客
04-14 505
创建数据库 本地引擎 默认的引擎,默认操作本地或者是指定集群的数据 CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)] 2mysql引擎 MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。 MySQL数据库引擎会将对其的查询转换为MySQL语法.
clickhouseSQL语法
杜海的博客
07-09 2940
CREATE #方式1 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ...
Clickhouse 一些知识
yudiandian2014的专栏
03-11 1372
Clickhouse 清空数据
"腾讯ClickHouse应用实践-丁晓坤分享
同时，他还介绍了ClickHouse在腾讯的优势，包括与Hadoop、Elasticsearch等大数据技术的集成，支持SQL语法查询，易于部署和管理等特点；同时也提到了在应用过程中遇到的挑战，例如数据一致性、查询性能优化、集群扩展...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Abner G

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值