大数据开发ClickHouse常用的数据类型及使用（第五十六篇）

这是我参与「掘金日新计划 · 2 月更文挑战」的第 5 天，点击查看活动详情”

一、常用数据类型

ClickHouse可以在数据库表中存储多种数据类型。可以通过下面的命令查看clickhouse支持哪些数据类型：

select * from system.data_type_families;

1.1、整型

固定长度的整型，包含无符号整型和有符号整型

有符号整型

类型	取值范围	别名
Int8	-128~127	TINYINT、BOOLEAN、BOOL、INT1
Int16	-32768~32767	SMALLINT、INT2
Int32	-2147483648~2147483647	INT、INT4、INTEGER
Int64	-170141183460469231731687303715884105728~170141183460469231731687303715884105727	BIGINT

无符号整型

类型	取值范围
UInt8	0~255
UInt16	0~65535
UInt32	0~4294967295
UInt64	0~18446744073709551615
UInt128	0~340282366920938463463374607431768211455
UInt256	0~115792089237316195423570985008687907853269984665640564039457584007913129639935

1.2、浮点型

0. Float32-float
1. Float64-double

尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，例如货币数量或页面加载时间用毫秒为单位表示

对浮点数进行计算可能引起四舍五入的误差。

SELECT 1 - 0.9

1.3、NaN和Inf

0. Inf – 正无穷

   SELECT 0.5 / 0;

   

1. -Inf – 负无穷

   SELECT -0.5 / 0;

   

2. NaN – 非数字

   SELECT 0 / 0;

   

1.4、Decimal

有符号的定点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）。

参数：

• P - 精度。有效范围：[1:38]，决定可以有多少个十进制数字（包括分数）。
• S - 规模。有效范围：[0：P]，决定数字的小数部分中包含的小数位数。

类似MySQL的BigDecimal(18,2)。18位数字，小数点后保留2位小数

Decimal还区分：Decimal32、Decimal64、Decimal128

• -P从[1:9] 使用Decimal32
• -P从[10:18] 使用Decimal64
• -P从[19:38] 使用Decimal128

SELECT toDecimal32(2, 4) AS x, x / 3;

精度规则

• 加法，减法：S = max(S1, S2)。取s1，s2中最大的精度
• 乘法：S = S1 + S2。取s1，s2精度之和
• 除法：S = S1。 取被除数 eg: 2/3 2是被除数，3是除数

select toDecimal64(2,3) as x,toTypeName(x) as xType,toDecimal64(1,2) as y,toTypeName(y) as yType,x-y as z,toTypeName(z) as zType;

1.5、布尔值

ClickHouse里面没有单独的类型来存储布尔值。可以使用UInt8类型，取值限制为0和1

1.6、字符串类型

clickhouse里面的字符串有好几个类型：

0. 字符串

   字符串可以任意长度的。它可以包含任意的字节集，包含空字节。因此，字符串类型可以代替其他 DBMSs 中的 VARCHAR、BLOB、CLOB 等类型。

   ClickHouse 没有编码的概念。字符串可以是任意的字节集，按它们原本的方式进行存储和输出。 若需存储文本，我们建议使用 UTF-8 编码。

1. 固定字符串

   固定长度 N 的字符串（N 必须是严格的正自然数）。您可以使用下面的语法对列声明为FixedString类型：

   <column_name> FixedString(N)

   当向ClickHouse中插入数据时,

   • 如果字符串包含的字节数少于`N’,将对字符串末尾进行空字节填充。
   • 如果字符串包含的字节数大于N,将抛出Too large value for FixedString(N)异常。

2. UUID

   通用唯一标识符(UUID)是一个16字节的数字，用于标识记录。

   select generateUUIDv4();

   

   如果在插入新记录时未指定UUID列的值，则UUID值将用零填充:

   00000000-0000-0000-0000-000000000000

1.7、时间类型

clickhouse里面有几个时间类型Date、DateTime、DateTime64

0. Data

   日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。允许存储从 Unix 纪元开始到编译阶段定义的上限阈值常量（目前上限是2106年，但最终完全支持的年份为2105）。最小值输出为1970-01-01。

   CREATE TABLE date_test(current_date Date) ENGINE=Memory;

   

   insert into date_test values('2023-02-15');

   

   

1. DateTime

   时间戳类型。用四个字节（无符号的）存储 Unix 时间戳）。允许存储与日期类型相同的范围内的值。最小值为 1970-01-01 00:00:00。时间戳类型值精确到秒（没有闰秒）。

   值的范围: [1970-01-01 00:00:00, 2106-02-07 06:28:15]。

   使用启动客户端或服务器时的系统时区，时间戳是从文本（分解为组件）转换为二进制并返回。在文本格式中，有关夏令时的信息会丢失。

   默认情况下，客户端连接到服务的时候会使用服务端时区。您可以通过启用客户端命令行选项 --use_client_time_zone 来设置使用客户端时间。

2. DateTime64

   此类型允许以日期（date）加时间（time）的形式来存储一个时刻的时间值，具有定义的亚秒精度

   DateTime64(precision, [timezone])

   SELECT toDateTime64(now(), 3, 'Asia/Shanghai');

   

   SELECT toDateTime64(now(), 4, 'Asia/Shanghai');

   

1.8、数组

由T类型元素组成的数组。T可以是任意类型，包含数组类型，但是不推荐使用多维数组，ClickHouse对多维数组支持有限

创建数组

array(T)或者[]

SELECT array(1, 2) AS x, toTypeName(x);

SELECT [1, 2] AS x, toTypeName(x)

1.9、元组

元组，其中每个元素都有单独的 类型。

不能在表中存储元组（除了内存表）。它们可以用于临时列分组。在查询中，IN 表达式和带特定参数的 lambda 函数可以来对临时列进行分组。

创建元组

tuple(T1, T2, ...)

SELECT tuple(1,'a') AS x, toTypeName(x);

1.10、嵌套数据结构

嵌套数据结构类似于嵌套表。嵌套数据结构的参数（列名和类型）与 CREATE 查询类似。每个表可以包含任意多行嵌套数据结构。

CREATE TABLE test
(
    name String,
    age Int8,
    works Nested
    (
      id UInt8,
      location String
    )
) ENGINE = Memory;

insert into table test values('zhangsan',20,[1],['上海']);

二、数据库\表创建

2.1、创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name;

2.2、创建数据表

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

三、数据库引擎

3.1、Lazy

在最后一次访问之后，只在RAM中保存expiration_time_in_seconds秒，只能用于Log表，它是为了存储许多小Log表而优化的

CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);

3.2、Atomic

默认情况下，使用Atomic数据库引擎。它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询

CREATE DATABASE test[ ENGINE = Atomic];

3.3、MySQL

用于将远程MySQL服务器中的表映射到ClickHouse中，并允许对表进行insert和select查询，方便在clickhouse和mysql之间进行数据库交换

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

四、表的引擎

表引擎决定了

0. 数据的存储方式和位置，写到哪里以及从哪里读取数据
1. 支持哪些查询以及如何支持
2. 并发数据访问
3. 索引的使用
4. 是否可以执行多线程请求
5. 数据副本参数

4.1、日志引擎

这些引擎是为了写入许多小数据量（少于一百万）的表的场景而开发的。日志引擎包含：

• StripeLog
• Log
• TinyLog

TinyLog

最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中。写入时，数据将附加到文件末尾。并发数据访问不受任何限制：

• 如果同时从表中读取并在不同的查询中写入，则读取操作将抛出异常
• 如果同时写入多个查询中的表，则数据将被破坏。

适用场景：

这种表引擎的典型用法是 write-once：首先只写入一次数据，然后根据需要多次读取。查询在单个流中执行。换句话说，此引擎适用于相对较小的表（建议最多1,000,000行）。如果您有许多小表，则使用此表引擎是适合的，因为它比Log引擎更简单（需要打开的文件更少）。当您拥有大量小表时，可能会导致性能低下，但在可能已经在其它 DBMS 时使用过，则您可能会发现切换使用 TinyLog 类型的表更容易。不支持索引。

测试：

create database test_table_engine;

use test_table_engine;

create table test_tinylog(id UInt16,name String)engine=TinyLog;

插入数据

insert into test_tinylog values(1,'zhangsan'),(2,'lisi');

查询存储位置

/var/lib/clickhouse/data

此时我们可以看到id.bin是存储的id列的数据，name.bin存储的是name列的数据

StripeLog

StripeLog 引擎将所有列存储在一个文件中。对每一次 Insert 请求，ClickHouse 将数据块追加在表文件的末尾，逐列写入。

ClickHouse 为每张表写入以下文件：

• data.bin — 数据文件。
• index.mrk — 带标记的文件。标记包含了已插入的每个数据块中每列的偏移量。

StripeLog 引擎不支持 ALTER UPDATE 和 ALTER DELETE 操作。

带标记的文件使得 ClickHouse 可以并行的读取数据。这意味着 SELECT 请求返回行的顺序是不可预测的。使用 ORDER BY 子句对行进行排序。

Log

Log 与 TinyLog 的不同之处在于，«标记» 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。对于并发数据访问，可以同时执行读取操作，而写入操作则阻塞读取和其它写入。Log引擎不支持索引。同样，如果写入表失败，则该表将被破坏，并且从该表读取将返回错误。Log引擎适用于临时数据，write-once 表以及测试或演示目的。

五、Java代码接入ClickHouse

5.1、引入pom文件

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.0</version>
</dependency>

更改clickhouse 配置

修改/etc/clickhouse-server目录下的config.xml配置，启用listen_host标签

重启

sudo /etc/init.d/clickhouse-server start

package com.strivelearn.clickhouse;
​
import java.sql.*;
​
/**
 * @author strivelearn
 * @version ClickHouseTest.java, 2023年02月16日
 */
public class ClickHouseTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.clickhouse.jdbc.ClickHouseDriver");
        String url = "jdbc:clickhouse://192.168.234.100:8123/test_table_engine";
        Connection connection = DriverManager.getConnection(url);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from test_tinylog");
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println("id = " + id);
            System.out.println("name = " + name);
        }
        resultSet.close();
        statement.close();
        connection.close();
    }
}

输出

id = 1
name = zhangsan
id = 2
name = lisi