sql - ETL:跨具有不同字段类型的不同源进行连接
问题描述
对于跨多个源执行 ETL 的应用程序,在“值”可能相同但表示方式可能不同的情况下,如何处理连接。例如,让我们采用以下虚构的场景:
来源1
- ProductID:
909
// 一个整数 - 产品:
"Soda"
来源2
- ProductID:
"909"
// 一个字符串 - 动作类型:
"Click"
假设这些来自我无权修改的两个不同来源(例如,一个可能是 Salesforce 数据,另一个是公司数据库)。ETL 应用程序如何处理字段类型可能以不同方式存储的连接?
解决方案
我在许多遗留数据库中都看到了这个问题。表驻留在不同源中的事实并不相关,因为我已经看到这种情况发生在同一个模式、不同的模式以及不同的数据库中。
这个问题有两个方面:可行性和性能。
可行性
我知道的所有数据库都支持数据类型转换和转换。他们中的一些人在幕后默默地做这件事,他们往往做错了。例如,甲骨文在这方面是臭名昭著的,因为它往往会朝着错误的方向转变。我建议总是明确地这样做。
例如(PostgreSQL):
create table a (product_id int, name varchar(10));
insert into a (product_id, name) values (909, 'soda');
create table b (product_id varchar(10), action_type varchar(10));
insert into b (product_id, action_type) values ('909', 'click');
在 PostgreSQL 中,以下三个查询有效,它们产生相同的结果集(性能是另一回事):
select * from a join b on a.product_id = b.product_id; -- don't do this
select * from a join b on a.product_id = cast(b.product_id as int);
select * from a join b on cast(a.product_id as varchar) = b.product_id;
如果您键入第一个选项,引擎会在您不知情的情况下默默地将其转换为第二个或第三个查询。这可能会产生您可能无法正确解决的意外/不必要的错误。同样,始终进行显式转换。
表现
发挥性能时,重要的是要决定哪张桌子是驾驶桌,哪一张是次要桌子。
如果您决定a
成为驾驶台,那么您可能会做两件事:
在
b
侧面转换,如下所示:select * from a join b on a.product_id = cast(b.product_id as int);
可以选择通过(如果可以的话)创建表达式索引(或索引虚拟索引)来进一步加快查询速度,
cast(b.product_id as int)
如下所示:create index ix1 on b ((cast(b.product_id as int)));
另一方面,如果您决定b
成为驾驶台,那么您可能会这样做:
在
a
侧面转换,如下所示:select * from a join b on cast(a.product_id as varchar) = b.product_id;
或者,通过(如果可以的话)创建表达式索引(或索引虚拟索引)来进一步加快查询速度,
cast(a.product_id as varchar)
如下所示:create index ix2 on a ((cast(a.product_id as varchar))));
要决定哪个选项更好,您需要获得两者的执行计划,阅读估计成本并做出决定。有时估计的成本并不那么可靠:它们只是估计的,而不是真实的。在危急情况下,我最终会运行两个选项进行比较。
推荐阅读
- javascript - OAuth2 的“/usercallback”在已发布的 Google Addon 上作为 HEAD 运行
- javascript - 无法使过滤器与 Immer.js 一起使用以删除数组中的嵌套对象
- android - 没有 OpenCV 的 Android 图像矩形检测
- laravel - Laravel Nova 将文件保存为 base64
- algorithm - 为每个 M 团队负责人组成 K 成员团队的算法
- laravel - 调用数据表中的数据时参数名称未知
- javascript - 为什么这些 js 对象字面量会被冻结?
- angular - 无法将对象作为元素值访问
- python - Python子进程,执行提示输入密码的shell命令
- mysql - MySQL函数触发器创建