1 rows in set. Elapsed: 0.002 sec.
----结束
4.4 ClickHouse 常用 SQL 语法
4.4.1 CREATE DATABASE 创建数据库
本章节主要介绍ClickHouse创建数据库的SQL基本语法和使用说明。
基本语法
CREATE DATABASE [IF NOT EXISTS] database_name [ON CLUSTER ClickHouse 集群名]
说明
ON CLUSTER ClickHouse集群名的语法，使得该DDL语句执行一次即可在集群中所有实例上都 执行。集群名信息可以使用以下语句的cluster字段获取：
select cluster,shard_num,replica_num,host_name from system.clusters;
使用示例
--创建数据库名为test的数据库
CREATE DATABASE test ON CLUSTER default_cluster;
--创建成功后，通过查询命令验证 show databases;
┌─name───┐
│ default │
│ system │
│ test │
└──────┘
4.4.2 CREATE TABLE 创建表
本章节主要介绍ClickHouse创建表的SQL基本语法和使用说明。
基本语法
● 方法一：在指定的“database_name”数据库中创建一个名为“table_name ” 的表。
如果建表语句中没有包含“database_name”，则默认使用客户端登录时选择的 数据库作为数据库名称。
CREATE TABLE [IF NOT EXISTS] [database_name.]table_name [ON CLUSTER ClickHouse集群名]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ...
) ENGINE = engine_name() [PARTITION BY expr_list] [ORDER BY expr_list]
注意
ClickHouse在创建表时建议携带PARTITION BY创建表分区。因为ClickHouse数 据迁移工具是基于表的分区做数据迁移，在创建表时如果不携带PARTITION BY 创建表分区，则在使用ClickHouse数据迁移工具界面无法对该表进行数据迁移。
● 方法二：创建一个与database_name2.table_name2具有相同结构的表，同时可 以对其指定不同的表引擎声明。
如果没有表引擎声明，则创建的表将与database_name2.table_name2使用相同 的表引擎。
CREATE TABLE [IF NOT EXISTS] [database_name.]table_name AS [database_name2.]table_name2 [ENGINE = engine_name]
● 方法三：使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表，并使 用SELECT子句的结果填充它。
CREATE TABLE [IF NOT EXISTS] [database_name.]table_nameENGINE = engine_name AS SELECT ...
使用示例
--在default数据库和default_cluster集群下创建名为test表 CREATE TABLE default.test ON CLUSTER default_cluster ( `EventDate` DateTime,
`id` UInt64
)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}') PARTITION BY toYYYYMM(EventDate)
ORDER BY id
4.4.3 INSERT INTO 插入表数据
本章节主要介绍ClickHouse插入表数据的SQL基本语法和使用说明。
基本语法
● 方法一：标准格式插入数据。
INSERT INTO [database_name.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
● 方法二：使用SELECT的结果写入。
INSERT INTO [database_name.]table [(c1, c2, c3)] SELECT ...
使用示例
--给test2表插入数据
insert into test2 (id, name) values (1, 'abc'), (2, 'bbbb');
--查询test2表数据 select * from test2;
┌─id─┬─name─┐
│ 1 │ abc │
│ 2 │ bbbb │
└───┴────┘
4.4.4 SELECT 查询表数据
本章节主要介绍ClickHouse查询表数据的SQL基本语法和使用说明。
基本语法
SELECT [DISTINCT] expr_list
[FROM [database_name.]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]
使用示例
--查看ClickHouse集群信息 select * from system.clusters;
--显示当前节点设置的宏 select * from system.macros;
--查看数据库容量 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) "压缩率"
from system.parts;
--查询test表容量。where条件根据实际情况添加修改 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) "压缩率"
from system.parts where table in ('test') and partition like '2020-11-%' group by table;
4.4.5 ALTER TABLE 修改表结构
本章节主要介绍ClickHouse修改表结构的SQL基本语法和使用说明。
基本语法
ALTER TABLE [database_name].name [ON CLUSTER cluster] ADD|DROP|CLEAR|
COMMENT|MODIFY COLUMN ...
说明
ALTER仅支持 *MergeTree ，Merge以及Distributed等引擎表。
使用示例
--给表t1增加列test01
ALTER TABLE t1 ADD COLUMN test01 String DEFAULT 'defaultvalue';
--查询修改后的表t1 desc t1
┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─
codec_expression─┬─ttl_expression─┐
ALTER TABLE t1 MODIFY COLUMN name UInt8;
--查询修改后的表t1 desc t1
┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─
codec_expression─┬─ttl_expression─┐
ALTER TABLE t1 DROP COLUMN test01;
--查询修改后的表t1 desc t1
┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─
codec_expression─┬─ttl_expression─┐
4.4.6 DESC 查询表结构
本章节主要介绍ClickHouse查询表结构的SQL基本语法和使用说明。
基本语法
DESC|DESCRIBE TABLE [database_name.]table [INTO OUTFILE filename]
[FORMAT format]
使用示例
--查询表t1的表结构 desc t1;
┌─name────┬─type─┬─default_type─┬─default_expression ┬─comment─┬─
codec_expression─┬─ttl_expression─┐
DROP [TEMPORARY] TABLE [IF EXISTS] [database_name.]name [ON CLUSTER cluster]
使用示例
--删除表t1 drop table t1;
4.4.8 SHOW 显示数据库和表信息
本章节主要介绍ClickHouse显示数据库和表信息的SQL基本语法和使用说明。
基本语法
show databases show tables
使用示例
--查询数据库 show databases;
┌─name────┐ show tables;
┌─name──┐
│ t1 │
│ test │
│ test2 │
│ test5 │
└─────┘
4.5 ClickHouse 数据迁移
4.5.1 ClickHouse 访问 RDS MySql 服务
ClickHouse面向OLAP场景提供高效的数据分析能力，支持通过MySQL等数据库引擎将 远程数据库服务器中的表映射到ClickHouse集群中，后续可以在ClickHouse中进行数 据分析。以下操作通过ClickHouse集群和RDS服务下的MySQL数据库实例对接进行举 例说明。
前提条件
● 已提前准备好对接的RDS数据库实例及数据库用户名、密码。详细操作可以参考 创建和连接RDS数据库实例。
● 已成功创建ClickHouse集群且集群和实例状态正常。