DuckDB: 使用枚举数据类型提升性能_duckdb 数据类型-优快云博客

本文链接：https://blog.youkuaiyun.com/neweastsun/article/details/144625595

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

在这里插入图片描述

在过去，用户将通过创建查找表并使用连接操作将其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     │         │         │         │
└─────────────┴────────────────┴─────────┴─────────┴─────────┴─────────┘