只保留数据工厂中最新的一行数据 [英] Keep only the most recent row of data in data factory
问题描述
我正在使用数据工厂来创建我们的分段区域,问题是每当源数据发生变化时,我们都会向分段表添加一个新行。
I am using Data factory to create our staging area, the problem is whenever source data changes, we add a new row to staging tables.
例如,假设我们有以下数据:
For instance, assume we have the following data:
ID Fields created edited
100 ---------- '2017-07-01' '2017-07-05'
这将存储在我们的分期表中,如下所示:
this will be stored in our staging tables like this:
ID Fields created edited
100 ---------- '2017-07-01' null
100 ---------- '2017-07-01' '2017-07-05'
选择最近的一行是昂贵的,我们不想要。你认为我们可以避免在分段中存储重复的ID?
Selecting the most recent row is expensive and We don't want that. How do you think we can avoid storing duplicate IDs in staging?
我假设在创建管道时,应该有一种方法来更新数据,如果ID已经存在于分段中。
查询格式数据工厂是这样的:
I assume on creating the pipelines, there should be a way to update the data if the ID already exists in staging.
the query format in data factory is like this:
$$Text.Format('select * from <<table>> where <<column>> >= \'{0:yyyy-MM-dd HH:mm}\' AND <<column>> < \'{1:yyyy-MM-dd HH:mm}\'', WindowStart, WindowEnd)
推荐答案
我找到了解决我的问题的办法。它就像这样:
I found a solution to my problem. It goes like this:
我在数据仓库中添加了一个临时表,在将数据插入到Staging之前,第一个数据进入这些临时表,我删除那些已经存在在分期表中,然后做一个新的插入。以这种方式,我将始终保持最新的数据在分段区域,并且在加入分段表以创建DW时不必使用row_number()函数。
I added a temp table into our data warehouse, before inserting data into Staging, first data comes into these temp tables, I remove those which already exist in staging tables then do a fresh insert. In this way, I will always keep the most recent data in the staging area and I won't have to use row_number() functions when joining staging tables to create DW.
我不知道这是否是最好的方法,但它对我来说很有用。
I am not sure if it's the best approach, but it's working for me.
这篇关于只保留数据工厂中最新的一行数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!