背景信息
表引擎在ClickHouse中的作用十分关键，直接决定了数据如何存储和读取、是否支持并发读写、是否支持index、支持的query种类、是否支持主备复制等。
ClickHouse提供了大约28种表引擎，各有各的用途，比如有Log系列用来做小表数据分析，MergeTree系列用来做大数据量分析，而Integration系列则多用于外表数据集成。再考虑复制表Replicated系列，分布式表Distributed等，纷繁复杂，新用户上手选择时常常感到迷惑。
ClickHouse表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交，根据场景组合使用。最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。对于大多数正式的任务，推荐使用MergeTree族中的引擎。
数据集成引擎：（可以读取外部数据源）
重点：
引擎介绍参考地址：https://blog.csdn.net/Jarry_cm/article/details/106210148 （推荐先看一遍）
如下建表DDL所示：
test_tbl的主键为(id, create_time)，并且按照主键进行存储排序，按照create_time进行数据分区，数据保留最近一个月
CREATE TABLE test_tbl (
id UInt16,
create_time Date,
comment Nullable(String)
) ENGINE = MergeTree()
PARTITION BY create_time
ORDER BY (id, create_time)
PRIMARY KEY (id, create_time)
TTL create_time + INTERVAL 1 MONTH
SETTINGS index_granularity=8192;
根据情况进行选择建表语句：
1、需要高容错：推荐复制表（ENGINE=Replication）
如果创建的是高可用（多副本）集群，需要选择复制表（Replication*），才能实现副本间数据复制同步。
复制表引擎如下：
ReplicatedMergeTree
ReplicatedSummingMergeTree
ReplicatedReplacingMergeTree
ReplicatedAggregatingMergeTree
ReplicatedCollapsingMergeTree
ReplicatedVersionedCollapsingMergeTree
ReplicatedGraphiteMergeTree
2、高性能：推荐分布式表(ENGINE= Distributed)
3、本地表
SQL语法：
一、ALTER操作：
ALTER 仅支持 *MergeTree ,Merge以及Distributed等引擎表。
该操作有多种形式。
ALTER TABLE
ALTER UPDATE
ALTER DELETE
ALTER COLUMN
ALTER ADD COLUMN
ALTER DROP COLUMN
ALTER MODIFY COLUMN
ALTER COMMENT COLUMN
ALTER CLEAR COLUMN
ALTER RENAME COLUMN
ALTER INDEX
ALTER ORDER BY
ALTER ADD INDEX
ALTER DROP INDEX
ALTER MATERIALIZE INDEX
ALTER CLEAR INDEX
ALTER CONSTRAINT
ALTER ADD CONSTRAINT
ALTER DROP CONSTRAINT
ALTER TTL
ALTER MATERIALIZE TTL
ALTER SETTINGS
ALTER MOVE PARTITION
ALTER FETCH PARTITION
ALTER FREEZE PARTITION
ALTER VIEW
ALTER VIEW REFRESH
ALTER VIEW MODIFY QUERY
二、create操作
CREATE DATABASE
CREATE TABLE
CREATE VIEW
CREATE DICTIONARY
CREATE TEMPORARY TABLE
三、DROP操作
DROP DATABASE
DROP TABLE
DROP VIEW
DROP DICTIONARY
四、SHOW操作
SHOW DATABASES
SHOW TABLES
SHOW COLUMNS
SHOW DICTIONARIES
五、基本数据类型
1、整型
有符号整型（-2n-1~2n-1-1）：
Int8 - [-128 : 127]
Int16 - [-32768 : 32767]
Int32 - [-2147483648 : 2147483647]
Int64 - [-9223372036854775808 : 9223372036854775807]
无符号整型范围（0~2n-1）：
UInt8 - [0 : 255]
UInt16 - [0 : 65535]
UInt32 - [0 : 4294967295]
UInt64 - [0 : 18446744073709551615]
2、浮点型
Float32 -等价 float
Float64 –等价 double
3、有符号的定点数
（Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)）例如：Decimal(P,S)
参数
P - 精度。有效范围：[1:38]，决定可以有多少个十进制数字（包括分数）。
S - 规模。有效范围：[0：P]，决定数字的小数部分中包含的小数位数。
4、布尔型
没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。
5、字符串
变长字符串 String，字符串可以任意长度的。它可以包含任意的字节集，包含空字节。
定长字符串 FixedString(N)
固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候，将返回错误消息。与String相比，极少会使用FixedString，因为使用起来不是很方便。
6、枚举类型
Enum8 用 ‘String’= Int8 对描述。
Enum16 用 ‘String’= Int16 对描述。
Enum 保存 ‘string’= integer 的对应关系。在 ClickHouse 中，尽管用户使用的是字符串常量，但所有含有 Enum 数据类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String 数据类型更有效。
CREATE TABLE enum_t ( et Enum8(‘a’ = 1, ‘b’ = 2, ‘c’ =3)) ENGINE = TinyLog;
7、数组
Array(T)
由 T 类型元素组成的数组。T 可以是任意类型，包含数组类型，但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。可以使用array()函数和中括号来创建数组
示例：
8、元组
Tuple(T1, T2, …)
元组，其中每个元素都有单独的类型。
9、日期
Date
用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值, 最小值输出为0000-00-00。
10、时间戳
DateTime
用四个字节（无符号的）存储 Unix 时间戳，允许存储与日期类型相同的范围内的值。最小值为 0000-00-00 00:00:00，时间戳类型值精确到秒。
表结构操作：
1、创建数据库：Create DataBase
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]; 多副本集群加上 ON CLUSTER cluster
2、创建本地表：Create Table
1、创建本地表(ON CLUSTER cluster 默认在每台节点上创建相同的表，省略去每个节点建表的过程)
CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER cluster
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = engine_name()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...];
选项描述：
db：指定数据库名称，如果当前语句没有包含‘db’，则默认使用当前选择的数据库为‘db’。
cluster：指定集群名称，目前固定为default。ON CLUSTER 将在每一个节点上都创建一个本地表。
type：该列数据类型，例如 UInt32。
DEFAULT：该列缺省值。如果INSERT中不包含指定的列，那么将通过表达式计算它的默认值并填充它。
MATERIALIZED：物化列表达式，表示该列不能被INSERT，是被计算出来的； 在INSERT语句中，不需要写入该列；在SELECT *查询语句结
果集不包含该列。
ALIAS ：别名列。这样的列不会存储在表中。 它的值不能够通过INSERT写入，同时使用SELECT查询星号时，这些列也不会被用来替换星号。
但是它们可以用于SELECT中，在这种情况下，在查询分析中别名将被替换。
物化列与别名列的区别： 物化列是会保存数据，查询的时候不需要计算，而表达式列不会保存数据，查询的时候需要计算，查询时候返回表
达式的计算结果
以下选项与表引擎相关，只有MergeTree系列表引擎支持：
PARTITION BY：指定分区键。通常按照日期分区，也可以用其他字段或字段表达式。
ORDER BY：指定 排序键。可以是一组列的元组或任意的表达式。
PRIMARY KEY： 指定主键，默认情况下主键跟排序键相同。因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。
SAMPLE BY ：抽样表达式，如果要用抽样表达式，主键中必须包含这个表达式。
SETTINGS：影响 性能的额外参数。
GRANULARITY ：索引粒度参数。
示例，创建一个本地表：
CREATE TABLE ontime_local ON CLUSTER default
(
Year UInt16,
Quarter UInt8,
Month UInt8,
DayofMonth UInt8,
DayOfWeek UInt8,
FlightDate Date,
FlightNum String,
Div5WheelsOff String,
Div5TailNum String
)ENGINE = ReplicatedMergeTree(
'/clickhouse/tables/ontime_local/{shard}',
'{replica}')
PARTITION BY toYYYYMM(FlightDate)
PRIMARY KEY (intHash32(FlightDate))
ORDER BY (intHash32(FlightDate),FlightNum)
SETTINGS index_granularity= 8192 ;
3、创建分布式表
基于本地表创建一个分布式表。
创建分布式表基本语法：
AS db.local_table_name ENGINE = Distributed(<cluster>, <database>, <shard table> [, sharding_key])
CREATE TABLE [db.]table_name ON CLUSTER default
参数说明：
db：数据库名。
local_table_name：对应的已经创建的本地表表名。
shard table：同上，对应的已经创建的本地表表名。
sharding_key：分片表达式。可以是一个字段，例如user_id（integer类型），通过对余数值进行取余分片；也可以是一个表达式，
例如rand()，通过rand()函数返回值/shards总权重分片；为了分片更均匀，可以加上hash函数，如intHash64(user_id)。
intHash64():分片方式：指定字段做hash。
示例，创建一个分布式表：
–建立分布式表
CREATE TABLE ontime_distributed ON CLUSTER default
AS db_name.ontime_local
ENGINE = Distributed(default, db_name, ontime_local, rand());
复制另一个表结构结构
创建与另一个表相同结构的表，语法如下：
CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER default AS [db.]name2 [ENGINE = engine];
表引擎可以通过ENGINE=engine字句指定，默认与被复制的表“name2”相同。
示例：
create table t2 ON CLUSTER default as db1.t1;
表的增删改
1、删视本地表与图表
DROP table db.表/视图 [ON CLUSTER cluster_name];
2、增加表的列
ALTER TABLE db.表/视图 [ON CLUSTER cluster_name] ADD COLUMN column_name1, ADD COLUMN column_name2;
3、删表的列
ALTER TABLE db.表/视图 [ON CLUSTER cluster_name] DROP COLUMN column_name1, DROP COLUMN column_name2;
4、更改表的列
ALTER TABLE db.表 [ON CLUSTER cluster] MODIFY COLUMN
5、删表的行
DELETE FROM db.表 [ON CLUSTER cluster_name] WHERE 要删除行的条件;
6.变更表名
RENAME TABLE db.表1 to db.表2 [ON CLUSTER cluster_name] ;
通过SELECT语句创建
使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表，并使用SELECT子句的结果填充它。语法如下：
CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER default ENGINE = engine AS SELECT ...
其中ENGINE是需要明确指定的。
示例：
create table t2 ON CLUSTER default ENGINE =MergeTree() as select * from db1.t1 where id<100;
对集群配置信息的查询
select * from system.clusters;
创建临时表
创建临时语法如下：
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name ON CLUSTER default
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
)
通过TEMPORARY 关键字表示临时表。大多数情况下，临时表不是手动创建的，只有在分布式查询处理中使用(GLOBAL) IN时为外部数据创建。
创建视图
创建视图语法如下：
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] ON CLUSTER default [ENGINE = engine]
[POPULATE] AS SELECT ...
4、INSERT INTO
基本语法
INSERT INTO 语句基本格式如下：
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
对于存在于表结构中但不存在于插入列表中的列，它们将会按照如下方式填充数据：
如果存在DEFAULT表达式，根据DEFAULT表达式计算被填充的值。
如果没有定义DEFAULT表达式，则填充零或空字符串。
使用SELECT的结果写入
语法结构如下：
INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...
注意：影响性能的注意事项
在执行INSERT时将会对写入的数据进行一些处理，比如按照主键排序、按照月份对数据进行分区等。如果在您的写入数据中包含多个月份的混合数据时，将会显著地降低INSERT的性能。为了避免这种情况，通常采用以下方式：
1、数据总是以尽量大的batch进行写入，如每次写入100,000行。
2、数据在写入ClickHouse前预先对数据进行分组。
在以下的情况下，性能不会下降：
1、数据总是被实时地写入。
2、写入的数据已经按照时间排序。
5、SELECT语法
基本语法
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]
所有的子句都是可选的，除了SELECT之后的表达式列表（expr_list）。 下面将选择部分子句进行说明。ClickHouse官网中文文档有更详细说明，
请参考查询语法。
地址：https://clickhouse.tech/docs/zh/sql-reference/statements/select/with/
简单查询语句示例：
SELECT
OriginCityName,
DestCityName,
count(*) AS flights
FROM ontime_distributed
WHERE Year = 1988
GROUP BY OriginCityName, DestCityName
ORDER BY flights DESC
LIMIT 20;
（1）SAMPLE 子句：数据采样
sample子句可用sample k来表示，k可以是0-1的小数值或一个足够大的正整数
k为小数时，查询将使用k做百分比选取数据，sample 0.1只检索数据总量的10%
k足够大的正整数，k为最大样本数，sample 10000000检索最多10000000行数据
（2）ARRAY JOIN 子句
对于包含数组列的表来说，这是一种常见的操作，以产生一个新表，该表具有一个包含该初始列的每个单独数组元素的列，而其他列的值是重复的：
示例如下：
CREATE TABLE arrays_test
(
s String,
arr Array(UInt8)
) ENGINE = Memory;
INSERT INTO arrays_test VALUES (‘Hello’, [1,2]), (‘World’, [3,4,5]), (‘Goodbye’, []);
使用以下ARRAY JOIN子句：
SELECT s, arr FROM arrays_test ARRAY JOIN arr;
（3）ClickHouse Join子句
语法
ClickHouse Join 语法
SELECT <expr_list>
FROM <left_subquery>
[GLOBAL] [ANY|ALL] INNER|LEFT|RIGHT|FULL|CROSS [OUTER] JOIN <right_subquery>
(ON <expr_list>)|(USING <column_list>) ...
所有标准 SQL JOIN 支持类型:
INNER JOIN，只返回匹配的行。
LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。
RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。
FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。
CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是 不 指定。
JOIN 没有指定类型默认 INNER. 关键字 OUTER 可以安全地省略。 替代语法 CROSS JOIN 在指定多个表 FROM条款 用逗号分隔。
1、用ALL和ANY的区别：
（1）在 LEFT JOIN使用ALL修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统则将右表中所有可以与左表关联的数据全部返回在结果中。这与SQL标准的JOIN行为相同。
（2）在 LEFT JOIN使用ANY修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统仅返回第一个与左表匹配的结果。如果左表与右表一一对应，不存在多余的行时，ANY与ALL的结果相同。
默认是ALL
2、ON 和USING的区别
（1）ON：其用法和普通SQL表连接类似，eg：select * from left_t1 any inner join right_t1 on left_t1.a=right_t1.a;
（2）USING：在指定的列 USING 两个子查询中必须具有相同的名称，并且其他列必须以不同的方式命名
示例：
SELECT
CounterID,
hits,
visits
FROM(
SELECT
CounterID,
count() AS hits
FROM test.hits
GROUP BY CounterID
) ANY LEFT JOIN
(
SELECT
CounterID,
sum(Sign) AS visits
FROM test.visits
GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10
注意：如果的多个字段on(field1,)
JDBC实例连接：
GitHub参考链接：https://github.com/ClickHouse/clickhouse-jdbc
步骤一：使用Eclipse 或其他IDE 工具创建maven项目并引入ClickHouse驱动依赖包
public class Main4 {
private static final String DATE_FORMAT = “yyyy-MM-dd HH:mm:ss”;
private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat(DATE_FORMAT);
public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException, ParseException {
String url = “your url”;
String username = “your username”;
String password = “your password”;
Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
String connectionStr = "jdbc:clickhouse://" + url + ":8123";
try (Connection connection = DriverManager.getConnection(connectionStr, username, password);
Statement stmt = connection.createStatement()) {
{
String createTableDDL = "create table test_table on cluster default " +
"(id UInt32, " +
"dt_str String, " +
"dt_col DateTime) " +
"engine=ReplicatedMergeTree('/clickhouse/tables/test_table/{shard}', '{replica}')" +
"partition by toYYYYMM(dt_col)" +
"order by (id)" +
"primary key (id)" +
"sample by (id)" +
"settings index_granularity = 8192;";
stmt.execute(createTableDDL);
System.out.println("create local table done.");
}
{
String createTableDDL = "create table test_dist on cluster default " +
"as default.test_table " +
"engine=Distributed(default, default, test_table, rand());";
stmt.execute(createTableDDL);
System.out.println("create distributed table done");
}
System.out.println("write 100000 rows...");
// Write 10 batch
for (int batch = 0; batch < 10; batch++) {
StringBuilder sb = new StringBuilder();
// Build one batch
sb.append("insert into test_dist values(" + (batch * 10000) + ", '2020-02-19 16:00:00', '2020-02-19 16:00:00')");
for (int row = 1; row < 10000; row++) {
sb.append(", (" + (batch * 10000 + row) + ", '2020-02-19 16:00:00', '2020-02-19 16:00:00')");
}
// Write one batch: 10000 rows
stmt.execute(sb.toString());
}
Thread.sleep(2 * 1000);
System.out.println("Select count(id)...");
try (ResultSet rs = stmt.executeQuery("select count(id) from test_dist");) {
while (rs.next()) {
int count = rs.getInt(1);
System.out.println("id count: " + count);
}
}
try (ResultSet rs = stmt.executeQuery("select id, dt_str, dt_col from test_dist");) {
while (rs.next()) {
int id = rs.getInt(1);
String dateStr = rs.getString(2);
Timestamp time = rs.getTimestamp(3);
String defaultDate = SIMPLE_DATE_FORMAT.format(new Date(time.getTime()));
System.out.println("id: " + id
+ ", date_str:" + dateStr
+ ", date_col:" + defaultDate);
}
}
}
}
}
hive-waterDrop的导数工具：
https://blog.csdn.net/u010834071/article/details/107185549/
4730
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
