ClickHouse的SQL基础语法_胜利的曙光的博客-程序员ITS301_clickhouse sql

技术标签: 数据库  大数据  sql  

1 常用的SQL命令

作用 SQL
列出所有数据库 show databases;
进入某一个数据库 use dbName;
列出数据库中所有的表 show tables;
创建数据库 create database [if not exists] dbName;
删除数据库 drop database dbName;
创建表 create [temporary] table [if not exists] tableName [ON CLUSTER cluster] ( fieldName dataType ) engine = EngineName(parameters);
清空表 truncate table tableName;
删除表 drop table tableName;
创建视图 create view view_name as select …
创建物化视图 create [MATERIALIZED] view [if not exists] [db.]tableName [to [db.]name] [engine=engine] [populate] as select …

2 select查询语法

ClickHouse中完整select的查询语法如下(除了SELECT关键字和expr_list以外,中括号中的字句都是可选的):

SELECT [DISTINCT] expr_list
    [FROM [db.]table | (subquery) | table_function] [FINAL]
    [SAMPLE sample_coeff]
    [ARRAY JOIN ...]
    [GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
    [PREWHERE expr]
    [WHERE expr]
    [GROUP BY expr_list] [WITH TOTALS]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT [n, ]m]
    [UNION ALL ...]
    [INTO OUTFILE filename]
    [FORMAT format]
    [LIMIT n BY columns]

如果查询中不包含DISTINCT,GROUP BY,ORDER BY子句以及IN和JOIN子查询,那它将仅使用O(1)数量的内存来完全流式的处理查询,否则这个查询将消耗大量的内存,除非你指定了这些系统配置:max_memory_usage, max_rows_to_group_by, max_rows_to_sort, max_rows_in_distinct, max_bytes_in_distinct, max_rows_in_set, max_bytes_in_set, max_rows_in_join, max_bytes_in_join, max_bytes_before_external_sort, max_bytes_before_external_group_by。它们规定了可以使用外部排序(将临时表存储到磁盘中)以及外部聚合,目前系统不存在关于Join的配置。

  • DISTINCT子句

    如果使用了DISTINCT子句,则会对结果中的完全相同的行进行去重。在GROUP BY不包括聚合函数,并对全部SELECT部分都包含在GROUP BY中时的作用一样。但该子句与GROUP BY子句存在以下几点不同:
    可以与GROUP BY配合使用;
    当不存在ORDER BY子句但存在LIMIT子句时,查询将在同时满足DISTINCT与LIMIT的情况下立即停止查询;
    在处理数据的同时输出结果,并不是等待整个查询全部完成。
    在SELECT表达式中存在Array类型的列时,不能使用DISTINCT。

  • FROM子句

    如果查询中不包含FROM子句则会读取system.one。 system.one中仅包含一行数据(此表实现了与其他数据库管理系统中的DUAL相同的功能)。

    • select 1 as id,'zhangsan' as name
    • select 1 as id,'zhangsan' as name from system.one

    这两句SQL是等价的。

    FROM子句规定了将从哪个表、或子查询、或表函数中读取数据;同时ARRAY JOIN子句和JOIN子句也可以出现在这里。

    可以使用包含在括号里的子查询来替代表,在这种情况下,子查询的处理将会构建在外部的查询内。不同于SQL标准,子查询后无需指定别名。为了兼容,你可以在子查询后添加‘AS 别名’,但是指定的名字不能被使用在任何地方。

    也可以使用表函数来代替表,有关信息,参见“表函数”。

    执行查询时,在查询中列出的所有列都将从对应的表中提取数据;如果你使用的是子查询的方式,则任何在外部查询中没有使用的列,子查询将从查询中忽略它们;如果你的查询没有列出任何的列(如SELECT count() FROM t),则将额外的从表中提取一些列(最好的情况下是最小的列),以便计算行数。

    最后的FINAL修饰符仅能够被使用在SELECT from CollapsingMergeTree场景中。当你为FROM指定了FINAL修饰符时,你的查询结果将会在查询过程中被聚合。需要注意的是,在这种情况下,查询将在单个流中读取所有相关的主键列,同时对需要的数据进行合并。这会导致查询更慢。在大多数情况下,你应该避免使用FINAL修饰符。

  • SAMPLE子句

    SAMPLE是ClickHouse中的近似查询处理,它只能工作在MergeTree*系列的表中,并且在创建表时需要显示指定采样表达式。

    SAMPLE子句可以使用SAMPLE k来表示,其中k可以是0到1的小数值,或者是一个足够大的正整数值。当k为0到1的小数时,查询将使用k作为百分比选取数据。
    例如,SAMPLE 0.1查询只会检索数据总量的10%。当k为一个足够大的正整数时,查询将使用’k’作为最大样本数。例如,SAMPLE 1000查询只会检索最多1000行数据,使用相同的采样率得到的结果总是一致的。

  • ARRAY JOIN子句

    ARRAY JOIN子句可以帮助查询进行与数组和nested数据类型的连接。它有点类似arrayJoin函数,但它的功能更广泛。ARRAY JOIN本质上等同于INNERT JOIN数组。

    -- 创建tbl_test_array_join表:
    create table tbl_test_array_join(str String, arr Array(Int8)) engine=Memory;
    
    -- 插入数据:
    insert into tbl_test_array_join(str,arr) values('a',[1,3,5]),('b',[2,4,6]);
    
    -- 使用ARRAY JOIN:
    select str,arr,arrItem from tbl_test_array_join ARRAY JOIN arr as arrItem;
    
  • JOIN 子句

    JOIN子句用于连接数据,作用与SQL的JOIN的定义相同。需要注意的是JOIN与ARRAY JOIN没有任何关系。

    可以使用具体的tableName来代替<left_subquery>与<right_subquery>。这与使用SELECT * FROM table子查询的方式相同。除非你的表是[Join](…/operations/table_engines/join.md支持的JOIN类型:INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN和CROSS JOIN。默认的OUTER关键字可以省略不写。

    在使用ALL修饰符对JOIN进行修饰时,如果右表中存在多个与左表关联的数据,那么系统则将右表中所有可以与左表关联的数据全部返回在结果中。这与SQL标准的JOIN行为相同。

    在使用ANY修饰符对JOIN进行修饰时,如果右表中存在多个与左表关联的数据,那么系统仅返回第一个与左表匹配的结果。如果左表与右表一一对应,不存在多余的行时,ANY与ALL的结果相同。

    可以在会话中通过设置join_default_strictness来指定默认的JOIN修饰符。
    当使用普通的JOIN时,查询将被发送给远程的服务器,并在这些远程服务器上生成右表并与它们关联,即右表来自于各个服务器本身。

    当使用GLOBAL … JOIN,首先会在请求服务器上计算右表并以临时表的方式将其发送到所有服务器。这时每台服务器将直接使用它进行计算。建议从子查询中删除所有JOIN不需要的列。当执行JOIN查询时,因为与其他阶段相比没有进行执行顺序的优化:JOIN优先于WHERE与聚合执行。因此,为了显示的指定执行顺序,建议使用子查询的方式执行JOIN。子查询不允许设置别名或在其他地方引用它们。USING中指定的列必须在两个子查询中具有相同的名称,而其他列必须具有不同的名称。可以通过使用别名的方式来更改子查询中的列名。USING子句使用的是等值连接。
    右表(子查询的结果)将会保存在内存中。如果没有足够的内存,则无法运行JOIN。
    只能在查询中指定一个JOIN。若要运行多个JOIN,你可以将它们放入子查询中。每次运行相同的JOIN查询,都会重新计算(不缓存结果)。为了避免这种情况,可以使用‘Join’引擎,它是一个预处理的Join数据结构,总是保存在内存中。在一些场景下,使用IN代替JOIN将会得到更高的效率。在各种类型的JOIN中,最高效的是ANY LEFT JOIN,然后是ANY INNER JOIN,效率最差的是ALL LEFT JOIN以及ALL INNER JOIN。

  • WHERE子句

    如果使用WHERE子句, 则在该子句中必须包含一个UInt8类型的表达式。这个表达是是一个带有比较和逻辑的表达式,它会在所有数据转换前用来过滤数据。如果在支持索引的数据库表引擎中,这个表达式将被评估是否使用索引。

  • PREWHERE子句

    PREWHERE子句与WHERE子句的意思大致相同,在一个查询中如果同时指定PREWHERE和WHERE,在这种情况下,PREWHERE优先于WHERE。
    当使用PREWHERE时,首先只读取PREWHERE表达式中需要的列。然后在根据PREWHERE执行的结果读取其他需要的列。
    如果在过滤条件中有少量不适合索引过滤的列,但是它们又可以提供很强的过滤能力。这时使用PREWHERE能减少数据的读取。但PREWHERE字句仅支持*MergeTree系列引擎,不适合用于已经存在于索引中的列,因为当列已经存在于索引中的情况下,只有满足索引的数据块才会被读取。如果将’optimize_move_to_prewhere’设置为1时,但在查询中不包含PREWHERE,则系统将自动的把适合PREWHERE表达式的部分从WHERE中抽离到PREWHERE中。

  • GROUP BY子句

    如果使用了GROUP BY子句,则在该子句中必须包含一个表达式列表。其中每个表达式将会被称之为“key”。SELECT,HAVING,ORDER BY子句中的表达式列表必须来自于这些“key”或聚合函数。被选择的列中不能包含非聚合函数或key之外的其他列。如果查询表达式列表中仅包含聚合函数,则可以省略GROUP BY子句,这时会假定将所有数据聚合成一组空“key”。
    GROUP BY子句会为遇到的每一个不同的key计算一组聚合函数的值。在GROUP BY子句中不支持使用Array类型的列。常量不能作为聚合函数的参数传入聚合函数中,例如sum(1)。

  • WITH TOTALS修饰符

    如果使用了WITH TOTALS修饰符,你将会在结果中得到一个被额外计算出的行。在这一行中将包含所有key的默认值(零或者空值),以及所有聚合函数对所有被选择数据行的聚合结果。

  • GROUP BY使用外部存储设备

    你可以在GROUP BY中允许将临时数据转存到磁盘上来限制对内存的使用。 max_bytes_before_external_group_by这个配置确定了在GROUP BY中启动将临时数据转存到磁盘上的内存阈值。如果你将它设置为0(这是默认值),这项功能将被禁用。如果使用时建议把max_memory_usage设置为max_bytes_before_external_group_by的2倍。

  • LIMIT子句

    LIMIT m用于在查询结果中选择前m行数据;LIMIT n, m 用于在查询结果中选择从n行开始的m行数据,但n和m这两个参数必须是正整数。如果没有指定ORDER BY子句,则结果的顺序是不确定的。

  • LIMIT N BY子句

    LIMIT N BY子句和LIMIT没有关系,LIMIT N BY COLUMNS子句可以用来在每一个COLUMNS分组中求得最大的N行数据。我们可以将它们同时用在一个查询中。LIMIT N BY子句中可以包含任意多个分组字段表达式列表。

  • HAVING子句

    HAVING子句可以用来过滤GROUP BY之后的数据,类似于WHERE子句。WHERE与HAVING不同之处在于WHERE在聚合前(GROUP BY)执行,HAVING在聚合后执行。如果不存在聚合,则不能使用HAVING。

  • ORDER BY子句

    如果使用ORDER BY子句,则该子句中必须存在一个表达式列表,表达式列表中每一个表达式都可以分配一个DESC(降序)或ASC(升序),如果没有显示指定则默认以ASC方式进行排序。当对浮点类型的列排序时,不管排序的顺序如何,如果使用升序排序时,NaNs好像比所有值都要大。如果使用降序排序时,NaNs好像比所有值都小。

  • FORMAT子句

    'FORMAT format’子句用于指定返回数据的格式,使用它可以方便的转换或创建数据的转储。如果不存在FORMAT子句,则使用默认的格式,这将取决与DB的配置以及所使用的客户端。对于批量模式的HTTP客户端和命令行客户端而言,默认的格式是TabSeparated。对于交互模式下的命令行客户端,默认的格式是PrettyCompact(它有更加美观的格式)。
    当使用命令行客户端时,数据以内部高效的格式在服务器和客户端之间进行传递。客户端将单独的解析FORMAT子句,以帮助数据格式的转换,会减轻网络和服务器的负载。

  • UNION ALL子句

    仅支持UNION ALL,不支持其他UNION规则(如UNION DISTINCT)。如果需要使用UNION DISTINCT,可以使用UNION ALL中包含SELECT DISTINCT的子查询的方式。UNION ALL中的查询可以同时运行,它们的结果将被混合到一起,这些查询的结果必须相同(列的数量和类型)。列名不同也是允许的,在这种情况下最终结果的列名将从第一个查询中获取。UNION会为查询之间进行类型转换。例如,如果组合的两个查询中包含相同的字段,并且是类型兼容的Nullable和non-Nullable,则结果将会将该字段转换为Nullable类型的字段。
    作为UNION ALL查询的部分不能包含在括号内。ORDER BY与LIMIT子句应该被应用在每个查询中,而不是最终的查询中。如果需要做最终结果转换则需要将UNION ALL作为一个子查询包含在FROM子句中。

3 insert into语法

ClickHouse中完整insert的主要用于向系统中添加数据, 语法如下:

  • 语法1:

    INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23)...
    

    使用语法1时,如果表存在但要插入的数据不存在,如果有DEFAULT表达式的列就根据DEFAULT表达式填充值。如果没有DEFAULT表达式的列则填充零或空字符串。如果strict_insert_defaults=1(开启了严格模式)则必须在insert中写出所有没定义DEFAULT表达式的列。

  • 语法2:

    INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set
    

    使用语法2时,数据可以是ClickHouse支持的任何输入输出格式传递给INSERT,但format_name必须显示的指定。

  • 语法3:

    INSERT INTO [db.]table [(c1, c2, c3)] FORMAT Values (v11, v12, v13)...
    

    语法3所用的输入格式就与语法1中INSERT … VALUES的中使用的输入格式相同。

  • 语法4:

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

    语法4是使用SELECT的结果写入到表中,select中的列类型必须与table中的列类型位置严格一致,列名可以不同,但类型必须相同。

  • 注意

    除了VALUES外,其他格式中的数据都不允许出现如now()、1 + 2等表达式。VALUES格式允许有限度的使用但不建议我们这么做,因为执行这些表达式的效率低下。
    系统不支持的其他用于修改数据的查询:UPDATE、DELETE、REPLACE、MERGE、UPSERT和 INSERT UPDATE。但是可以使用ALTER TABLE … DROP PARTITION查询来删除一些不需要的数据。
    如果在写入的数据中包含多个月份的混合数据时,将会显著的降低INSERT的性能。为了避免这种情况,可以让数据总是以尽量大的batch进行写入,如每次写入100000行;数据在写入ClickHouse前预先的对数据进行分组。

    在进行INSERT时将会对写入的数据进行一些处理,按照主键排序,按照月份对数据进行分区、数据总是被实时的写入、写入的数据已经按照时间排序,这几种情况下,性能不会出现下降。

4 alter语法

ClickHouse中的ALTER只支持MergeTree系列,Merge和Distributed引擎的表

基本语法:

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

参数解析:

  • ADD COLUMN – 向表中添加新列
  • DROP COLUMN – 在表中删除列
  • MODIFY COLUMN – 更改列的类型

案例演示

  • 创建一个MergerTree引擎的表

    CREATE TABLE mt_table (
                              date  Date,
                              id UInt8,
                              name String
    ) ENGINE=MergeTree() partition by toYYYYMMDD(date) order by id settings index_granularity=8192;
    
  • 向表中插入一些值

    insert into mt_table values ('2020-09-15', 1, 'zhangsan');
    insert into mt_table values ('2020-09-15', 2, 'lisi');
    insert into mt_table values ('2020-09-15', 3, 'wangwu');
    
  • 在末尾添加一个新列age

    :)alter table mt_table add column age UInt8
    :)desc mt_table
    ┌─name─┬─type───┬─default_type─┬─default_expression─┐
    │ dateDate   │              │                    │
    │ id   │ UInt8  │              │                    │
    │ name │ String │              │                    │
    │ age  │ UInt8  │              │                    │
    └──────┴────────┴──────────────┴────────────────────┘
    :) select * from mt_table
        ┌───────date─┬─id─┬─name─┬─age─┐
    │ 2019-06-012 │ lisi │   0 │
    └────────────┴────┴──────┴─────┘
    ┌───────date─┬─id─┬─name─────┬─age─┐
    │ 2019-05-011 │ zhangsan │   0 │
    │ 2019-05-033 │ wangwu   │   0 │
    └────────────┴────┴──────────┴─────┘
    
  • 更改age列的类型

    :)alter table mt_table modify column age UInt16
    :)desc mt_table
    
    ┌─name─┬─type───┬─default_type─┬─default_expression─┐
    │ dateDate   │              │                    │
    │ id   │ UInt8  │              │                    │
    │ name │ String │              │                    │
    │ age  │ UInt16 │              │                    │
    └──────┴────────┴──────────────┴────────────────────┘
    
  • 删除刚才创建的age列

    :)alter table mt_table drop column age
    :)desc mt_table
    ┌─name─┬─type───┬─default_type─┬─default_expression─┐
    │ dateDate   │              │                    │
    │ id    │ UInt8  │              │                    │
    │ name │ String │              │                    │
    └─────┴──────┴─────────┴───────────────┘
    
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/shenglishuguang/article/details/120645527

智能推荐

从零开始学习音视频编程技术(十七) 录屏软件开发之音频采集_小学生009的博客-程序员ITS301_从零开始学习音视频编程技术(十七)

前面讲解了如何使用libavdevice采集摄像头图像同样 libavdevice 也可以采集麦克风的声音。下面就讲解麦克风声音的采集:采集声音和采集摄像头本质上没有多大的却别,基本上就是换个名字:首先还是一样注册libavdevice:1avdevice_register_all();

GPRS远程开关 3 MQTT服务器配置_「已注销」的博客-程序员ITS301

目录MQTT服务器配置EMQ服务器简介为什么选用EMQ下载EMQ服务器配置阿里云服务器ECS选择配置并购买安全策略配置Java环境变量配置部署并测试EMQ服务器部署EMQ服务器浏览器测试MQTT客户端测试MQTT服务器配置本编文章主要讲述如何构建一个MQTT服务器,以供设备端与控制端进行数据交互。EMQ服务器简介官网:EMQ http...

Go丨语言package github.com/Go-SQL-Driver/MySQL: exec: &quot;git&quot;: executable file not found in %PATH%解决方法_lihaojie1996的博客-程序员ITS301

Go语言在添加第三方MySQL驱动的时候报错:go: missing Git command. See https://golang.org/s/gogetcmdpackage github.com/Go-SQL-Driver/MySQL: exec: "git": executable file not found in %PATH%根据以上报错语句可以看出来是没有找到%PATH%

【电气】电气基础知识问答(特别适合初学者)_hln24477932的博客-程序员ITS301_电气初学者题库

电气基础知识问答(特别适合初学者)名称解释:1、三相交流电:由三个频率相同、电势振幅相等、相位差互差 120°角的交流电路组成的电力系统,叫三相交流电。2、一次设备:直接与生产电能和输配电有关的设备称为一次设备。包括各种高压断路器、隔离开关、母线、电力电缆、电压互感器、电流互感器、电抗器、避雷器、消弧线圈、并联电容器及高压熔断器等。3、二次设备:对一次设备进行监视、测量、操纵控制和保护作用的辅助设

使用画布,拖放和File API调整图像大小_cuozhun3051的博客-程序员ITS301

使用画布,拖放和File API调整图像大小 让Tom“ Trenkavision” Trenka为该博客写客串帖子,这是我的荣幸。 Tom是Dojo Toolkit的原始作者之一,也是我在SitePen的导师。 我亲眼目睹了他的天才,他始终是第一个预见到具有潜在解决方案问题的人。 他还开创了思路,提出了解决边缘案例问题的非常规但可靠的解决方案。 这是一个完美的例子。 最近,有人要求我...

同一浏览器多个标签页之间的通信(二)——cookie+setInterval_彼之方的博客-程序员ITS301_同一个浏览器中可以获取不同tab下的cookie吗

二、cookie (1)cookie是什么? HTTP Cookie,通常直接叫做cookie,最初是在客户端用于存储回话信息的。该标准要求服务器对任意HTTP请求发送Set-CookieHTTP头作为相应的一部分,其中包含回话信息。浏览器会存储这样的回话信息,并在这之后,通过每个请求添加CookieHTTP头将信息发回服务器。 (2)cookie怎么用? 在JavaScript中,coo...

随便推点

tf.nn.static_bidirectional_rnn ; tf.contrib.rnn.static_bidirectional_rnn 讲解_UESTC_20172222的博客-程序员ITS301

tf.nn.static_bidirectional_rnn h 和tf.contrib.rnn.static_bidirectional_rnn是一样的tf.nn.static_bidirectional_rnn别名:tf.contrib.rnn.static_bidirectional_rnntf.nn.static_bidirectional_rnntf.nn.static_bi...

mysql辅助索引非叶子节点_Mysql的聚集索引与辅助索引_weixin_39639514的博客-程序员ITS301

Mysql数据库中的B+树索引可以分为聚集索引和辅助索引(非聚集索引)。本文将介绍一下两者。聚集索引聚集索引:指索引项的排序方式和表中数据记录排序方式一致的索引(这里不懂先放着,一会举例),每张表只能有一个聚集索引,聚集索引的叶子节点存储了整个行数据。解释:什么叫索引项的排序方式和表中数据记录排序方式一致呢?我们把一本字典看做是数据库的表,那么字典的拼音目录就是聚集索引,它按照A-Z排列。实际存储...

Hibernate 异常org.hibernate.LazyInitializationException: could not initialize proxy - no Session_IT_熊的博客-程序员ITS301

最近代写毕业设计中小网站,SSH架构,三年没搞过这个,忘记了,今天遇到这个问题就记录下:错误页面提示could not initialize proxy - no Session控制台org.hibernate.LazyInitializationException: could not initialize proxy - no Session病症:这是一个lazy使用后

Opendaylight源码编译相关问题以及导入eclipse_挥着刀的流氓的博客-程序员ITS301

SDN源码编译相关问题以及导入eclipse有很多的实现方式,这里只阐述我所使用的方法准备工作jdkmaveneclipse软件安装以及环境配置,在这里就不做赘述,网上有很多的相关教程从Github上下载相关的项目代码开始编译之前,将odlparent中的setting.xml文件放到本地仓库中(我的本地仓库是~/.m2/repository/,我将setting

linux 解决 " command not found: shopt "的 "~/.bashrc" 配置问题_Mr.deeplan的博客-程序员ITS301_linux 找不到 shopt

之前使用的是 bash,自从使用 zsh 后,在执行 source ~/.bashrc 的时候出现下面的 command not found 的错误。下面是错误信息/home/trsky/.bashrc:16: command not found: shopt/home/trsky/.bashrc:33: command not found: shopt/home/trsky/.ba...

shiro整合oauth_weixin_34126215的博客-程序员ITS301

前言  如果oauth原理还不清楚的地方,其参考这里。一、基本思路脑图二、客户端shiro配置  shiro配置文件&lt;?xml version="1.0" encoding="UTF-8"?&gt;&lt;beans xmlns="http://www.springframework.org/schema/beans" ...

推荐文章

热门文章

相关标签