高性价比OLAP列式数据库—Clickhouse（SQL操作）

原创迷三张 2025-02-17

首先来说传统的关系型数据库（如mysql）的SQL语句，ClickHouse基本上是都支持的，即使有些函数或者语法使用上不一样，但都是有异曲同工之妙的~~~

ClickHouse支持以下形式的语句：

SELECT
INSERT INTO
CREATE
ALTER
其他类型的查询

Insert操作

基本与标准 SQL（MySQL）一致

标准

insert into [table_name] values(…),(…) 
insert into [table_name](…) values(…),(…)

表到表的插入

insert into [table_name] select a,b,c from [table_name_2]
insert into [table_name] (…) select a,b,c from [table_name_2]

Update 和 Delete操作

ClickHouse 提供了Delete 和Update 的能力，这类操作被称为 Mutation 查询，它可以看做Alter 的一种。虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很“重”的操作，而且不支持事务。“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

Mutation 语句

用来操作表数据的ALTER查询是通过一种叫做“突变”的机制来实现的，最明显的是ALTER TABLE ... DELETE和ALTER TABLE ... UPDATE。它们是异步的后台进程，类似于MergeTree表的合并。

由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成

删除语句

ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr

示例：

# 如何是集群需要加上 ON CLUSTER ...
alter table t_order_smt delete where sku_id ='sku_001';

修改语句

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

示例：

# 如何是集群需要加上 ON CLUSTER ...
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;

Select操作

基本与标准 SQL（MySQL）一致

[WITH expr_list|(subquery)] 
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]

主要支持

WITH 子句
FROM 子句
SAMPLE 子句
JOIN 子句
PREWHERE 子句
WHERE 子句
GROUP BY 子句
LIMIT BY 子句
HAVING 子句
SELECT 子句
DISTINCT 子句
LIMIT 子句
UNION ALL 子句
INTO OUTFILE 子句
FORMAT 子句

WITH 子句

WITH 子句可以在其余语句部分中使用 SELECT 查询

示例：

WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound

with  metric_series as ( select
    toString(dms.__series_id__)as  __series_id__,
    argMax(dms.labels,dms.__mgmt_id__) as labels,
  	dms.systemName as systemName,
    dms.centerName as centerName,
    dms.objectName as objectName
  from
    aimeter.dist_metric_series as dms
  where
    dms.__name__ = 'oracle_adg_apply_lag'
    AND dms.centerName = #{center}
  <if test="business !=null and business != '' ">
    AND dms.systemName = #{business}
  </if>
	AND dms.systemLevel = 'A级'
  group by __series_id__,systemName,objectName,centerName
),
avg as (select
  centerName as centerName,
  round(avg(avgValue),4) as avgValue
  from
  (
  select * from metric_series
  ) as a
  left join (
  select
    toString(dm.__series_id__) as  __series_id__,
    avg(dm.value * 1000) as avgValue
  from
    aimeter.dist_metric as dm
  where timestamp >=  now() - interval 60 MINUTE
  group by __series_id__
  ) as b USING __series_id__ group by centerName
),
max as (
  select
  centerName as centerName,
  max(maxValue) maxValue
  from
  (
  select * from metric_series
  ) as a
  left join (
  select
    toString(dm.__series_id__) as  __series_id__,
    max(dm.value * 1000) as maxValue
  from
    aimeter.dist_metric as dm
  where timestamp >=  now() - interval 60 MINUTE
  group by __series_id__
  ) as b USING __series_id__ group by centerName
),
min as (
   select
  centerName as centerName,
  min(minValue) minValue
  from
  (
  select * from metric_series
  ) as a
  left join (
  select
    toString(dm.__series_id__) as  __series_id__,
    min(dm.value * 1000) as minValue
  from
    aimeter.dist_metric as dm
  where timestamp >=  now() - interval 60 MINUTE
  group by __series_id__
  ) as b USING __series_id__ group by centerName
)


select a.centerName as centerName,a.avgValue as avgValue,x.maxValue as maxValue,n.minValue as minValue
  from avg  as a
  left join max x on x.centerName = a.centerName
  left join min as n on n.centerName = x.centerName

LIMIT BY 字句

一个使用LIMIT n BY expressions从句的查询会以去重后的expressions结果分组，每一分组选择前n行。LIMIT BY指定的值可以是任意数量的表达式，语法如下：

LIMIT [offset_value, ]n BY expressions 
LIMIT n OFFSET offset_value BY expressions

示例：

CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory; 
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id


┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id
┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

    与 SELECT * FROM limit_by ORDER BY id,val LIMIT 2 OFFSET 1 BY id 返回相同的结果。OFFSET m 作用:跳过结果集中的前 m 行。

SELECT
    domainWithoutWWW(URL) AS domain,
    domainWithoutWWW(REFERRER_URL) AS referrer,
    device_type,
    count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100


查询返回每个domain,device_type组合的前5个refferrer，
总计返回至多100行(LIMIT n BY + LIMIT)

LIMIT BY与LIMIT没有关系，它们可以在同一个查询中使用。

Alter操作

同MySQL的修改字段基本是一致，语法如下：

ALTER [TEMPORARY] TABLE [db].name [ON CLUSTER cluster] ADD|DROP|RENAME|CLEAR|COMMENT|{MODIFY|ALTER}|MATERIALIZE COLUMN ...

添加列

ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after | FIRST]

示例：

ALTER TABLE alter_test ADD COLUMN Added1 UInt32 FIRST; 
ALTER TABLE alter_test ADD COLUMN Added2 UInt32 AFTER NestedColumn;
ALTER TABLE alter_test ADD COLUMN Added3 UInt32 AFTER ToDrop;

DESC alter_test FORMAT TSV;


Added1  UInt32
CounterID       UInt32
StartDate       Date
UserID  UInt32
VisitID UInt32
NestedColumn.A  Array(UInt8)
NestedColumn.S  Array(String)
Added2  UInt32
ToDrop  UInt32
Added3  UInt32

删除列

DROP COLUMN [IF EXISTS] name

示例：

ALTER TABLE visits DROP COLUMN browser

重命名列

RENAME COLUMN [IF EXISTS] name to new_name

示例：

ALTER TABLE visits RENAME COLUMN webBrowser TO browser

清除列

CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name

示例：

ALTER TABLE visits CLEAR COLUMN browser IN PARTITION tuple()

向列添加文本注释

COMMENT COLUMN [IF EXISTS] name 'Text comment'

示例：

ALTER TABLE visits COMMENT COLUMN browser '用于访问站点的浏览器类型'

修改列

MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [codec] [TTL] [settings] [AFTER name_after | FIRST] 
ALTER COLUMN [IF EXISTS] name TYPE [type] [default_expr] [codec] [TTL] [settings] [AFTER name_after | FIRST]

示例：

CREATE TABLE users (
    c1 Int16,
    c2 String
) ENGINE = MergeTree
ORDER BY c1;


DESCRIBE users;
┌─name─┬─type───┬
│ c1   │ Int16  │
│ c2   │ String │
└──────┴────────┴


ALTER TABLE users MODIFY COLUMN c2 String FIRST;


DESCRIBE users;
┌─name─┬─type───┬
│ c2   │ String │
│ c1   │ Int16  │
└──────┴────────┴


ALTER TABLE users ALTER COLUMN c2 TYPE String AFTER c1; 


DESCRIBE users;
┌─name─┬─type───┬
│ c1   │ Int16  │
│ c2   │ String │
└──────┴────────┴

总结

本篇文章介绍了开发工作用常用的关键字和语法使用，想要了解探索更多可到ClickHouse官网~~~

https://clickhouse.com/docs/zh/sql-reference
clickhouse-SQL参考官方地址

欢迎微信扫描二维码，关注我的公众号~~

clickhouse olap数据库列式数据库

最后修改时间：2025-02-17 13:20:03

文章转载自迷三张，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

高性价比OLAP列式数据库—Clickhouse（SQL操作）

评论