DuckDB: 使用枚举数据类型提升性能

字符串类型是最常用的类型之一。但是,字符串列通常具有有限数量的不同值。例如,国家列的唯一条目永远不会超过几百个。将数据类型存储为普通字符串会浪费存储空间并影响查询性能。更好的解决方案是对这些列进行字典编码。在字典编码中,数据被分成两部分:类别和值。类别存储实际的字符串,而值存储对字符串的引用。下面描述了这种编码。

在这里插入图片描述

在过去,用户将通过创建查找表并使用连接操作将其id转换回来来手动执行字典编码。像Pandas和R这样的环境更优雅地支持这些类型。Pandas Categorical和R Factors是允许通过字典编码有效存储具有许多重复条目的字符串列的类型。

字典编码不仅可以节省大量存储,而且还允许系统对数字而不是字符串进行操作,从而大大提高查询性能。通过降低RAM使用,ENUMs还允许DuckDB扩展到更大的数据集。

为了允许DuckDB与这些编码结构完全集成,DuckDB实现了Enum类型。Enum优势非常明细,无论是存储空间还是读取效率方面。
在这里插入图片描述

枚举应用示例

DuckDB Enum SQL语法很大程度上受到了Postgres的启发。下面,我们将描述如何创建和使用ENUM类型。

CREATE TYPE lotr_race AS ENUM ('Mayar', 'Hobbit', 'Orc');

CREATE TABLE character (
    name text,
    race lotr_race
);

INSERT INTO character VALUES ('Frodo Quackins','Hobbit'), ('Quackalf ', 'Mayar');

-- We can perform a normal string comparison
-- Note that 'Hobbit' will be cast to a lotr_race
-- hence this comparison is actually a fast integer comparison
SELECT name FROM character WHERE race = 'Hobbit';
----
Frodo Quackins

ENUM列的行为与普通的VARCHAR列完全相同。它们可以用于字符串函数(如LIKE或substring),它们可以进行比较、排序等。唯一的例外是ENUM列只能保存枚举定义中指定的值。插入不属于枚举定义的值将导致错误。

DuckDB的ENUM目前是静态的(即,在ENUM定义之后不能添加或删除值)。

转换枚举类型

现在,让我们尝试DuckDB的ENUM类型。自从他们宣布了这个特性,我就想尝试一下,因为我要处理的大多数数据集都有一堆字符串列,经常是低基数的分类变量。因此,通过使用枚举,我希望在查询性能和数据库大小方面有实质性的提高。

首先,创建枚举类型, 并导入示例数据:

CREATE TYPE sex_enum AS ENUM ('F', 'M');

D create or replace table stu_info as select id, name, sex from read_csv('stu.txt') ;
D desc stu_info;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  nullkeydefault │  extra  │
│   varcharvarcharvarcharvarcharvarcharvarchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ id          │ BIGINT      │ YES     │         │         │         │
│ name        │ VARCHAR     │ YES     │         │         │         │
│ sex         │ VARCHAR     │ YES     │         │         │         │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

创建枚举类型后,可以将其用作其他内置类型,例如,将列强制转换为新类型。

D select id, name, sex::sex_enum as sex from stu_info;
┌───────┬─────────┬────────────────┐
│  id   │  name   │      sex       │
│ int64 │ varcharenum('f', 'm') │
├───────┼─────────┼────────────────┤
│     1 │ stu01   │ M              │
│     2 │ stu02   │ M              │
│     3 │ stu03   │ M              │
│     4 │ stu04   │ M              │
│     5 │ stu05   │ M              │
│     0 │ stu00   │ F              │
│     6 │ stu06   │ F              │
│     7 │ stu07   │ F              │
│     8 │ stu08   │ F              │
│     9 │ stu09   │ F              │
├───────┴─────────┴────────────────┤
│ 10 rows                3 columns │
└──────────────────────────────────┘

使用下面三种ALTER TABLE语句都可以更改列的类型并使其成为enum。

D alter table stu_info alter sex type sex_enum;
# D ALTER TABLE stu_info ALTER sex SET DATA TYPE sex_enum USING CAST(sex AS sex_enum);
# D ALTER TABLE stu_info ALTER sex SET DATA TYPE sex_enum USING sex;
D desc stu_info;
┌─────────────┬────────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │  column_type   │  nullkeydefault │  extra  │
│   varcharvarcharvarcharvarcharvarcharvarchar │
├─────────────┼────────────────┼─────────┼─────────┼─────────┼─────────┤
│ id          │ BIGINT         │ YES     │         │         │         │
│ name        │ VARCHAR        │ YES     │         │         │         │
│ sex         │ ENUM('F', 'M') │ YES     │         │         │         │
└─────────────┴────────────────┴─────────┴─────────┴─────────┴─────────┘

总结

DuckDB ENUM看起来很棒,但目前暂时不能修改,使用之前需要预先知道所有枚举值。但请务必多使用它们,它在数据库大小、读写和查询时间方面的好处显然超过了这些不便之处。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值