字符串类型是最常用的类型之一。但是,字符串列通常具有有限数量的不同值。例如,国家列的唯一条目永远不会超过几百个。将数据类型存储为普通字符串会浪费存储空间并影响查询性能。更好的解决方案是对这些列进行字典编码。在字典编码中,数据被分成两部分:类别和值。类别存储实际的字符串,而值存储对字符串的引用。下面描述了这种编码。
在过去,用户将通过创建查找表并使用连接操作将其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 │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ 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 │ varchar │ enum('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 │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────┼────────────────┼─────────┼─────────┼─────────┼─────────┤
│ id │ BIGINT │ YES │ │ │ │
│ name │ VARCHAR │ YES │ │ │ │
│ sex │ ENUM('F', 'M') │ YES │ │ │ │
└─────────────┴────────────────┴─────────┴─────────┴─────────┴─────────┘
总结
DuckDB ENUM看起来很棒,但目前暂时不能修改,使用之前需要预先知道所有枚举值。但请务必多使用它们,它在数据库大小、读写和查询时间方面的好处显然超过了这些不便之处。