映射数据流中的代理键转换Surrogate key transformation in mapping data flow

适用于: Azure 数据工厂 Azure Synapse Analytics

使用代理键转换向每行数据添加递增的键值。Use the surrogate key transformation to add an incrementing key value to each row of data. 在星型架构分析数据模型中设计维度表时,这非常有用。This is useful when designing dimension tables in a star schema analytical data model. 在星型架构中,维度表中的每个成员都需要唯一键,该键需要是一个非业务键。In a star schema, each member in your dimension tables requires a unique key that is a non-business key.

ConfigurationConfiguration

代理键转换Surrogate Key Transform

键列:生成的代理键列的名称。Key column: The name of the generated surrogate key column.

起始值:将生成的最小键值。Start value: The lowest key value that will be generated.

从现有源增加键Increment keys from existing sources

若要从源中存在的值启动序列,建议使用缓存接收器保存该值,并使用派生列转换将两个值相加。To start your sequence from a value that exists in a source, we recommend to use a cache sink to save that value and use a derived column transformation to add the two values together. 使用缓存的查找获取输出,并将其追加到生成的键。Use a cached lookup to get the output and append it to the generated key. 有关详细信息,请了解缓存接收器缓存的查找For more information, learn about cache sinks and cached lookups.

代理键查找Surrogate Key lookup

从现有的最大值开始递增Increment from existing maximum value

根据源数据的位置,可以使用两种技术将之前的最大值作为键值。To seed the key value with the previous max, there are two techniques that you can use based on where your source data is.

数据库源Database sources

使用 SQL 查询选项从源中选择 MAX()。Use a SQL query option to select MAX() from your source. 例如,Select MAX(<surrogateKeyName>) as maxval from <sourceTable>For example, Select MAX(<surrogateKeyName>) as maxval from <sourceTable>.

代理键查询Surrogate Key Query

文件源File sources

如果之前的最大值位于文件中,请使用聚合转换中的 max() 函数获取之前的最大值:If your previous max value is in a file, use the max() function in the aggregate transformation to get the previous max value:

代理键文件Surrogate Key File

在这两种情况下,都需要写入缓存接收器并查找该值。In both cases, you will need to write to a cache sink and lookup the value.

数据流脚本Data flow script

语法Syntax

<incomingStream> 
    keyGenerate(
        output(<surrogateColumnName> as long),
        startAt: <number>L
    ) ~> <surrogateKeyTransformationName>

示例Example

代理键转换Surrogate Key Transform

以上代理键配置的数据流脚本位于下面的代码片段中。The data flow script for the above surrogate key configuration is in the code snippet below.

AggregateDayStats
    keyGenerate(
        output(key as long),
        startAt: 1L
    ) ~> SurrogateKey1

后续步骤Next steps

这些示例使用联接派生列转换。These examples use the Join and Derived Column transformations.