在红移中执行桌面交换的最佳实践

Question

我们正在在红移集群上运行少数小时脚本，这些脚本为数据消费者构建摘要表。组装了登台表后，脚本运行了一个交易，该事务删除了现有表并将其替换为登台表，因此：

BEGIN;
DROP TABLE IF EXISTS public.data_facts;
ALTER TABLE public.data_facts_stage RENAME TO data_facts;
COMMIT;

此操作的问题在于，长期运行的分析查询将在public.data_facts上放置访问ShareLock，从而阻止其掉落并击落我们的ETL周期。我认为更好的解决方案将是重命名现有表的方法，因此：

ALTER TABLE public.data_facts RENAME TO data_facts_old;
ALTER TABLE public.data_facts_stage RENAME TO data_facts;
DROP TABLE public.data_facts_old;

但是，这种方法以1）公开为前提。data_facts存在，2）public.data_facts_old不存在。

您知道是否有办法在不依赖应用程序逻辑的情况下安全地进行此操作？（例如，如果存在，则类似于Alter表）。

Answer 1

我没有尝试过，但是查看CREATE VIEW的文档，似乎可以使用后期的视图来完成。

主要想法将是用户与之交互的视图public.data_facts。在幕后，您可以加载新数据，然后将视图交换为"指向"到新表。

bootstrap

-- load data into public.data_facts_v0
CREATE VIEW public.data_facts AS
SELECT * from public.data_facts_v0 WITH NO SCHEMA BINDING;

update

-- load data into public.data_facts_v1
CREATE OR REPLACE VIEW public.data_facts AS
SELECT * from public.data_facts_v1 WITH NO SCHEMA BINDING;
DROP TABLE public.data_facts_v0;

WITH NO SCHEMA BINDING表示该视图将是后期的。"后期的视图不会检查基础数据库对象，例如表和其他视图，直到查询该视图为止。"这意味着更新甚至可以引入具有重列列或全新结构的表格。

注意：

1. 将交换操作包装到交易中可能是一个好主意，以确保如果视图交换失败，我们不会丢弃上一张表。

Answer 2

您可以在目标表中添加一个新的load time timestamp encode runlength default getdate()列，并使您的ETL执行此操作：

INSERT INTO public.data_facts
SELECT * FROM public.data_facts_staging;
DELETE FROM public.data_facts
WHERE load_time<(select max(load_time) from public.data_facts);
DROP TABLE public.data_facts_staging;

注意：public.data_facts_staging应具有与public.data_facts完全相同的结构，除非public.data_facts的最后一列是load_time，以便在插入时将其填充当前的时间戳。

唯一的含义是，在插入新行和删除旧行之间需要额外的磁盘空间，片刻，load_time必须始终是最后一列。另外，您每次执行此操作时都必须进行vaccum表。

另一个好处是，如果您的ETL失败并且登台表为空或没有登台表，您将不会丢失数据。在与DDL交换表的纯SQL方案中，当丢失登台表时，您不受保护表格。在建议的方案中，如果没有插入新行，则删除语句什么都不删除（没有比最大加载时间小的行），因此最坏的情况只是拥有旧版本的数据。

P.S。有一个命令，而不是insert ... select ...只是将指针从登台更改为目标表（alter table ... append from ...），但我猜它需要与alter table相同的锁，所以我不建议这样做