根据暂存表的snapshot_date和id将记录更新/插入到不同的快照表中

Question

我有一个巨大的快照表(例如user_snapshot_all)，在Redshift (即Postgres)上的不同的较小的表中，以获得性能增益。

因此，较小的表类似于(后缀有year_month)

user_snapshot_1995_1
user_snapshot_1995_2
user_snapshot_1995_3
user_snapshot_1995_4
....
user_snapshot_2016_11

它们保存有后缀的年份和月份的快照记录。

我使用一个暂存表user_snapshot_staging来递增地向这些表加载/更新数据，在99%的情况下，它只是最新的year_month表。

但是会有一些边缘情况，比如午夜12:00，当中转表将有跨越两个表的数据时(例如，2016-11-1年的user_snaspshot_2016_10和user_snapshot_2016_11 )，或者另一个边缘情况，也许我们需要更新几个2年前的快照，所以分期表将有大约2年的记录和今天的许多快照。

问题是如何设计我的查询或代码，以便它可以更新或插入数据到正确的month_year快照表？

所有快照表和暂存表至少有这两列：

id
snapshot_date

进一步澄清：，如果它是单个user_snapshot_all，我可以很容易地通过使用基于snapshot_date和id的主表连接暂存表来更新记录。但是，对于这些由month_year分割的较小的表，无法保证所有来自暂存表的记录都可以在一个快照表中找到。

这里是用例注意:下面的查询是ETL过程的一部分，它们不是一次性手动查询，这就是为什么我需要自动解决方案的原因。

场景1)假设user_snapshot_staging表具有

id  snapshot_date user_detail
100  2016-11-3     jskesljd234
101  2016-11-4     jskesljdfg23
102  2016-11-5     jskesljdbd23
103  2016-11-6     jskesljdw23ds

由于所有快照都属于2016年11月，所有这些数据都将被插入/更新到user_snapshot_2016_11中，其中有以下两个查询：

插入新的：

Insert into user_info_snapshot_2011_11 (id, snapshot_date, user_detail )
from user_info_snapshot_staging source LEFT OUTER JOIN user_info_snapshot_2011_11 target on source.id = target.id where target.id is null
;

更新现有：

update user_info_snapshot_2011_11 set snapshot_date=source.snapshot_date, user_detail=source.user_detail 
from user_info_snapshot_staging source INNER JOIN user_info_snapshot_2011_11 target on source.id = target.id where

场景2)现在假设user_snapshot_staging表有

id  snapshot_date user_detail
1300  2015-01-3     jskesljd234
1301  2015-10-4     jskesljdfg23
1302  2016-11-1     jskesljdbd23
1303  2016-11-2     jskesljdw23ds

现在，暂存表中有需要更新和插入不同快照表的快照，我们不能只将/更新插入到user_snapshot_2016_11中，但我们还需要将插入/更新到user_snapshot_2015_01和user_snapshot_2015_10中。

如何设计生成动态查询的查询或代码来处理这些情况，以便只将适当的表与基于暂存表中的数据的user_snapshot_staging表连接起来？

如果你需要进一步澄清，请告诉我。抱歉，解释有点棘手。

Answer 1

您可以使用以下方法生成查询。我将给出基于python语法的伪代码示例。

1. 获取分期数据库中的年/月组合

SELECT DISTINCT to_char(date, 'YYYY-MM') FROM user_info_snapshot_staging;

1. 以下是查询模板：

-- insert_template.sql
INSERT INTO user_info_snapshot_{{ year }}_{{ month }} (id, snapshot_date, user_detail )
FROM user_info_snapshot_staging source LEFT OUTER JOIN user_info_snapshot_{{ year }}_{{ month }} target on source.id = target.id where target.id is null
WHERE DATE_TRUNC('month', source.date) = {{ month }} AND DATE_TRUNC('year', source.date) = {{ year }};

-- update_template.sql
UPDATE user_info_snapshot_{{ year }}_{{ month }} SET snapshot_date=source.snapshot_date, user_detail=source.user_detail
FROM user_info_snapshot_staging source INNER JOIN user_info_snapshot_{{ year }}_{{ month }} target on source.id = target.id where
DATE_TRUNC('month', source.date) = {{ month }} AND DATE_TRUNC('year', source.date) = {{ year }};

现在循环遍历年份/月份对并执行以下查询：

for year_month, in cursor.execute("SELECT to_char('YYYY-MM', date_columns) FROM user_info_snapshot_staging"):
    year, month = year_month.split('-')
    # this is where you generate sql
    sql = template('insert_template', context={
        'year': year,
        'month': month,
    })
    # here you execute it
    cursor.execute(sql)

如果您需要更新大量记录，我建议您不要使用update。在this question中有更多信息。