关于clickhouse:篇三ClickHouse的数据类型

ClickHouse 提供了许多数据类型，它们能够划分为根底类型、复合类型和非凡类型。咱们能够在 system.data_type_families 表中查看数据类型名称以及是否辨别大小写。

SELECT * FROM system.data_type_families

下面的零碎表，存储了 ClickHouse 所反对的数据类型，留神不同版本的 ClickHouse 可能数据类型会有所不同，具体如下表所示：

┌─name────────────────────┬─case_insensitive─┬─alias_to────┐
│ IPv6                    │                0 │             │
│ IPv4                    │                0 │             │
│ LowCardinality          │                0 │             │
│ Decimal                 │                1 │             │
│ String                  │                0 │             │
│ Decimal64               │                1 │             │
│ Decimal32               │                1 │             │
│ Decimal128              │                1 │             │
│ Float64                 │                0 │             │
│ Float32                 │                0 │             │
│ Int64                   │                0 │             │
│ SimpleAggregateFunction │                0 │             │
│ Array                   │                0 │             │
│ Nothing                 │                0 │             │
│ UInt16                  │                0 │             │
│ Enum16                  │                0 │             │
│ UInt32                  │                0 │             │
│ Date                    │                1 │             │
│ Int8                    │                0 │             │
│ Int32                   │                0 │             │
│ Enum8                   │                0 │             │
│ UInt64                  │                0 │             │
│ IntervalSecond          │                0 │             │
│ Int16                   │                0 │             │
│ FixedString             │                0 │             │
│ Nullable                │                0 │             │
│ AggregateFunction       │                0 │             │
│ DateTime                │                1 │             │
│ Enum                    │                0 │             │
│ Tuple                   │                0 │             │
│ IntervalMonth           │                0 │             │
│ Nested                  │                0 │             │
│ IntervalMinute          │                0 │             │
│ IntervalHour            │                0 │             │
│ IntervalWeek            │                0 │             │
│ IntervalDay             │                0 │             │
│ UInt8                   │                0 │             │
│ IntervalQuarter         │                0 │             │
│ UUID                    │                0 │             │
│ IntervalYear            │                0 │             │
│ LONGBLOB                │                1 │ String      │
│ MEDIUMBLOB              │                1 │ String      │
│ TINYBLOB                │                1 │ String      │
│ BIGINT                  │                1 │ Int64       │
│ SMALLINT                │                1 │ Int16       │
│ TIMESTAMP               │                1 │ DateTime    │
│ INTEGER                 │                1 │ Int32       │
│ INT                     │                1 │ Int32       │
│ DOUBLE                  │                1 │ Float64     │
│ MEDIUMTEXT              │                1 │ String      │
│ TINYINT                 │                1 │ Int8        │
│ DEC                     │                1 │ Decimal     │
│ BINARY                  │                1 │ FixedString │
│ FLOAT                   │                1 │ Float32     │
│ CHAR                    │                1 │ String      │
│ VARCHAR                 │                1 │ String      │
│ TEXT                    │                1 │ String      │
│ TINYTEXT                │                1 │ String      │
│ LONGTEXT                │                1 │ String      │
│ BLOB                    │                1 │ String      │
└─────────────────────────┴──────────────────┴─────────────┘

固定长度的整数类型又包含有符号和无符号的整数类型。

有符号整数类型

类型	字节	范畴
Int8	1	[-2^7 ~2^7-1]
Int16	2	[-2^15 ~ 2^15-1]
Int32	4	[-2^31 ~ 2^31-1]
Int64	8	[-2^63 ~ 2^63-1]
Int128	16	[-2^127 ~ 2^127-1]
Int256	32	[-2^255 ~ 2^255-1]

无符号类型

类型	字节	范畴
UInt8	1	[0 ~2^8-1]
UInt16	2	[0 ~ 2^16-1]
UInt32	4	[0 ~ 2^32-1]
UInt64	8	[0 ~ 2^64-1]
UInt256	32	[0 ~ 2^256-1]

单精度浮点数

Float32 从小数点后第 8 位起会产生数据溢出

类型	字节	精度
Float32	4	7

双精度浮点数

Float32 从小数点后第 17 位起会产生数据溢出

类型	字节	精度
Float64	8	16

示例

-- Float32 类型，从第 8 为开始产生数据溢出
kms-1.apache.com :) select toFloat32(0.123456789);

SELECT toFloat32(0.123456789)

┌─toFloat32(0.123456789)─┐
│             0.12345679 │
└────────────────────────┘
-- Float64 类型，从第 17 为开始产生数据溢出
kms-1.apache.com :) select toFloat64(0.12345678901234567890);

SELECT toFloat64(0.12345678901234568)

┌─toFloat64(0.12345678901234568)─┐
│            0.12345678901234568 │
└────────────────────────────────┘

有符号的定点数，可在加、减和乘法运算过程中放弃精度。ClickHouse 提供了 Decimal32、Decimal64 和 Decimal128 三种精度的定点数，反对几种写法：

Decimal(P, S)
Decimal32(S)
数据范畴：(-1 10^(9 – S), 1 10^(9 – S) )
Decimal64(S)
数据范畴：(-1 10^(18 – S), 1 10^(18 – S) )
Decimal128(S)
数据范畴：(-1 10^(38 – S), 1 10^(38 – S) )
Decimal256(S)
数据范畴：(-1 10^(76 – S), 1 10^(76 – S) )

其中：P代表精度，决定总位数（整数局部 + 小数局部），取值范畴是 1～76

S代表规模，决定小数位数，取值范畴是 0～P

依据 P 的范畴，能够有如下的等同写法：

P 取值	原生写法示例	等同于
[1 : 9]	Decimal(9,2)	Decimal32(2)
[10 : 18]	Decimal(18,2)	Decimal64(2)
[19 : 38]	Decimal(38,2)	Decimal128(2)
[39 : 76]	Decimal(76,2)	Decimal256(2)

留神点 ：不同精度的数据进行四则运算时， 精度 (总位数) 和规模 (小数点位数) 会发生变化，具体规定如下：

精度对应的规定
- Decimal64(S1) 运算符 Decimal32(S2) -> Decimal64(S)
- Decimal128(S1) 运算符 Decimal32(S2) -> Decimal128(S）
- Decimal128(S1) 运算符 Decimal64(S2) -> Decimal128(S)
- Decimal256(S1) 运算符 Decimal<32|64|128>(S2) -> Decimal256(S)

能够看出：两个不同精度的数据进行四则运算时，后果数据已最大精度为准

规模 (小数点位数) 对应的规定
- 加法 | 减法：S = max(S1, S2)，即以两个数据中小数点位数最多的为准
- 乘法：S = S1 + S2(留神：S1 精度 >= S2 精度)，即以两个数据的小数位相加为准
除法：S = S1，即被除数的小数位为准

  -- toDecimal32(value, S)
  -- 加法,S 取两者最大的，P 取两者最大的
  SELECT
      toDecimal64(2, 3) AS x,
      toTypeName(x) AS xtype,
      toDecimal32(2, 2) AS y,
      toTypeName(y) as ytype,
      x + y AS z,
      toTypeName(z) AS ztype;
  -- 后果输入
  ┌─────x─┬─xtype──────────┬────y─┬─ytype─────────┬─────z─┬─ztype──────────┐
  │ 2.000 │ Decimal(18, 3) │ 2.00 │ Decimal(9, 2) │ 4.000 │ Decimal(18, 3) │
  └───────┴────────────────┴──────┴───────────────┴───────┴────────────────┘
  -- 乘法，比拟非凡，与这两个数的程序无关
  -- 如下：x 类型是 Decimal64，y 类型是 Decimal32，程序是 x *y, 小数位 S =S1+S2
  SELECT
      toDecimal64(2, 3) AS x,
      toTypeName(x) AS xtype,
      toDecimal32(2, 2) AS y,
      toTypeName(y) as ytype,
      x * y AS z,
      toTypeName(z) AS ztype;
  -- 后果输入   
  ┌─────x─┬─xtype──────────┬────y─┬─ytype─────────┬───────z─┬─ztype──────────┐
  │ 2.000 │ Decimal(18, 3) │ 2.00 │ Decimal(9, 2) │ 4.00000 │ Decimal(18, 5) │
  └───────┴────────────────┴──────┴───────────────┴─────────┴────────────────┘
  -- 替换相乘的程序，y*x, 小数位 S =S1*S2
  SELECT
      toDecimal64(2, 3) AS x,
      toTypeName(x) AS xtype,
      toDecimal32(2, 2) AS y,
      toTypeName(y) as ytype,
      y * x AS z,
      toTypeName(z) AS ztype;
  -- 后果输入
  ┌─────x─┬─xtype──────────┬────y─┬─ytype─────────┬────────z─┬─ztype──────────┐
  │ 2.000 │ Decimal(18, 3) │ 2.00 │ Decimal(9, 2) │ 0.400000 │ Decimal(18, 6) │
  └───────┴────────────────┴──────┴───────────────┴──────────┴────────────────┘
  -- 除法，小数位与被除数保持一致
  SELECT
      toDecimal64(2, 3) AS x,
      toTypeName(x) AS xtype,
      toDecimal32(2, 2) AS y,
      toTypeName(y) as ytype,
      x / y AS z,
      toTypeName(z) AS ztype;
  -- 后果输入
  ┌─────x─┬─xtype──────────┬────y─┬─ytype─────────┬─────z─┬─ztype──────────┐
  │ 2.000 │ Decimal(18, 3) │ 2.00 │ Decimal(9, 2) │ 1.000 │ Decimal(18, 3) │
  └───────┴────────────────┴──────┴───────────────┴───────┴────────────────┘

字符串能够是任意长度的。它能够蕴含任意的字节集，蕴含空字节。因而，字符串类型能够代替其余 DBMSs 中的 VARCHAR、BLOB、CLOB 等类型。

固定长度的 N 字节字符串，个别在在一些明确字符串长度的场景下应用，申明形式如下：

-- N 示意字符串的长度
<column_name> FixedString(N)

值得注意的是：FixedString 应用 null 字节填充开端字符。

-- 尽管 hello 只有 5 位，但最终字符串是 6 位
select toFixedString('hello',6) as a,length(a) as alength;
-- 后果输入

┌─a─────┬─alength─┐
│ hello │       6 │
└───────┴─────────┘
-- 留神对于固定长度的字符串进行比拟时，会呈现不一样的后果
-- 如下：因为 a 是 6 位字符，而 hello 是 5 位，所以两者不相等
select toFixedString('hello',6) as a,a = 'hello' ,length(a) as alength;    
-- 后果输入
┌─a─────┬─equals(toFixedString('hello', 6), 'hello')─┬─alength─┐
│ hello │                                          0 │       6 │
└───────┴────────────────────────────────────────────┴─────────┘

-- 须要应用上面的形式
select toFixedString('hello',6) as a,a = 'hello\0' ,length(a) as alength;
-- 后果输入

┌─a─────┬─equals(toFixedString('hello', 6), 'hello\0')─┬─alength─┐
│ hello │                                            1 │       6 │
└───────┴──────────────────────────────────────────────┴─────────┘

UUID 是一种数据库常见的主键类型，在 ClickHouse 中间接把它作为一种数据类型。UUID 共有 32 位，它的格局为 8 -4-4-4-12，比方：

61f0c404-5cb3-11e7-907b-a6006ad3dba0
-- 当不指定 uuid 列的值时，填充为 0
00000000-0000-0000-0000-000000000000

应用示例如下：

-- 建表
CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog;
-- insert 数据
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1';
INSERT INTO t_uuid (y) VALUES ('Example 2');
SELECT * FROM t_uuid;
-- 后果输入，默认被填充为 0
┌────────────────────────────────────x─┬─y─────────┐
│ b6b019b5-ee5c-4967-9c4d-8ff95d332230 │ Example 1 │
│ 00000000-0000-0000-0000-000000000000 │ Example 2 │
└──────────────────────────────────────┴───────────┘

工夫类型分为 DateTime、DateTime64 和 Date 三类。须要留神的是 ClickHouse 目前没有工夫戳类型，也就是说，工夫类型最高的精度是秒，所以如果须要解决毫秒、微秒精度的工夫，则只能借助 UInt 类型实现。

用两个字节存储，示意从 1970-01-01 (无符号) 到以后的日期值。日期中没有存储时区信息。

CREATE TABLE t_date (x date) ENGINE=TinyLog;
INSERT INTO t_date VALUES('2020-10-01');
SELECT x,toTypeName(x) FROM t_date;
┌──────────x─┬─toTypeName(x)─┐
│ 2020-10-01 │ Date          │
└────────────┴───────────────┘

用四个字节（无符号的）存储 Unix 工夫戳。容许存储与日期类型雷同的范畴内的值。最小值为 0000-00-00 00:00:00。工夫戳类型值准确到秒（没有闰秒）。时区应用启动客户端或服务器时的零碎时区。

CREATE TABLE t_datetime(`timestamp` DateTime) ENGINE = TinyLog;
INSERT INTO t_datetime Values('2020-10-01 00:00:00');
SELECT * FROM t_datetime;
-- 后果输入
┌───────────timestamp─┐
│ 2020-10-01 00:00:00 │
└─────────────────────┘
-- 留神，DateTime 类型是辨别时区的
SELECT 
    toDateTime(timestamp, 'Asia/Shanghai') AS column,
    toTypeName(column) AS x 
FROM t_datetime;
-- 后果输入
┌──────────────column─┬─x─────────────────────────┐
│ 2020-10-01 00:00:00 │ DateTime('Asia/Shanghai') │
└─────────────────────┴───────────────────────────┘
SELECT
     toDateTime(timestamp, 'Europe/Moscow') AS column,
     toTypeName(column) AS x 
FROM t_datetime; 
-- 后果输入
┌──────────────column─┬─x─────────────────────────┐
│ 2020-09-30 19:00:00 │ DateTime('Europe/Moscow') │
└─────────────────────┴───────────────────────────┘

ClickHouse 没有独自的类型来存储布尔值。能够应用 UInt8 类型，取值限度为 0 或 1。

Array(T)，由 T 类型元素组成的数组。T 能够是任意类型，蕴含数组类型。但不举荐应用多维数组，ClickHouse 对多维数组的反对无限。例如，不能在 MergeTree 表中存储多维数组。

SELECT array(1, 2) AS x, toTypeName(x);
-- 后果输入
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘
SELECT [1, 2] AS x, toTypeName(x);
-- 后果输入
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

须要留神的是，数组元素中如果存在 Null 值，则元素类型将变为 Nullable。

SELECT array(1, 2, NULL) AS x, toTypeName(x);
-- 后果输入
┌─x──────────┬─toTypeName(array(1, 2, NULL))─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))        │
└────────────┴───────────────────────────────┘

另外，数组类型外面的元素必须具备雷同的数据类型，否则会报异样

SELECT array(1, 'a')
-- 报异样
DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not

枚举类型通常在定义常量时应用，ClickHouse 提供了 Enum8 和 Enum16 两种枚举类型。

-- 建表
CREATE TABLE t_enum
(x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;
-- INSERT 数据
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');
-- 如果定义了枚举类型值之后，不能写入其余值的数据
INSERT INTO t_enum values('a')
-- 报异样：Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)

Tuple(T1, T2, …)，元组，与 Array 不同的是，Tuple 中每个元素都有独自的类型，不能在表中存储元组（除了内存表）。它们能够用于长期列分组。在查问中，IN 表达式和带特定参数的 lambda 函数能够来对长期列进行分组。

SELECT tuple(1,'a') AS x, toTypeName(x);
-- 后果输入
┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String)      │
└─────────┴───────────────────────────┘
-- 建表
CREATE TABLE t_tuple(c1 Tuple(String,Int8)
) engine=TinyLog;
-- INSERT 数据
INSERT INTO t_tuple VALUES(('jack',20));
-- 查问数据
SELECT * FROM t_tuple;
┌─c1──────────┐
│ ('jack',20) │
└─────────────┘
-- 如果插入数据类型不匹配，会报异样
INSERT INTO t_tuple VALUES(('tom','20'));
-- Type mismatch in IN or VALUES section. Expected: Int8. Got: String

Nullable 类型示意某个根底数据类型能够是 Null 值。其具体用法如下所示：

-- 建表
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog
-- 写入数据
INSERT INTO t_null VALUES (1, NULL), (2, 3);
SELECT x + y FROM t_null;
-- 后果
┌─plus(x, y)─┐
│       ᴺᵁᴸᴸ │
│          5 │
└────────────┘

Domain 类型是特定实现的类型：

IPv4 是与 UInt32 类型放弃二进制兼容的 Domain 类型，用于存储 IPv4 地址的值。它提供了更为紧凑的二进制存储的同时反对辨认可读性更加敌对的输入输出格局。

IPv6 是与 FixedString(16)类型放弃二进制兼容的 Domain 类型，用于存储 IPv6 地址的值。它提供了更为紧凑的二进制存储的同时反对辨认可读性更加敌对的输入输出格局。

留神低版本的 ClickHouse 不反对此类型。

-- 建表
CREATE TABLE hits
(url String, 
 from IPv4
) ENGINE = MergeTree()
ORDER BY from;
-- 写入数据
INSERT INTO hits (url, from) VALUES
('https://wikipedia.org', '116.253.40.133')
('https://clickhouse.tech', '183.247.232.58');

-- 查问
SELECT * FROM hits;
┌─url─────────────────────┬───────────from─┐
│ https://wikipedia.org   │ 116.253.40.133 │
│ https://clickhouse.tech │ 183.247.232.58 │
└─────────────────────────┴────────────────┘

公众号『大数据技术与数仓』，回复『材料』支付大数据资料包

关于clickhouse:篇三ClickHouse的数据类型

数值类型

Int 类型

浮点类型

Decimal 类型

字符串类型

String

FixedString

UUID

日期类型

Date 类型

DateTime 类型

布尔类型

数组类型

枚举类型

Tuple 类型

非凡数据类型

Nullable

Domain