Dynamic Time series using ADF data flow - rule based mapping

sankon437 21 Reputation points
2024-05-22T20:32:42.8466667+00:00

Can i get expression for rule based mapping in data flow using select transform, my dataset is "allow schema drift" enabled, but i want to maintain new schema always don't want to keep old time series headers from mapping.

Let me know if this can be done.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,865 questions
{count} votes

Accepted answer
  1. phemanth 7,190 Reputation points Microsoft Vendor
    2024-05-23T08:00:20.2366667+00:00

    @sankon437

    Thanks for using MS Q&A platform and posting your query.

    Absolutely, you can achieve dynamic time series processing with Azure Data Factory (ADF) data flow and rule-based mapping, even with schema drift enabled. Here's how:

    Rule-Based Mapping for Dynamic Columns:

    1. Enable Schema Drift: Ensure "Allow schema drift" is enabled for your source dataset in ADF. This allows handling new or missing columns in the incoming data.
    2. Define Rule-Based Mapping: In your data flow, use the Select transformation. When mapping columns, choose "Rule-based mapping" instead of static mapping.
    3. Wildcard Matching: Use wildcards like "*" in the rule expression to match any column name in the source data.

    Example Expression:

    Let's say your time series data has a timestamp column and multiple value columns with varying names (e.g., "Value_1", "Value_2"). You can use the following expression:

    output(column) 
      = case when column.type == 'DateTime' then column.value 
           when substring(column.name, 1, 6) == 'Value_' then column.value 
           else null end
    

    Explanation:

    This expression checks each column:

    • If it's a DateTime type, it's considered the timestamp and copied to the output.
    • If the column name starts with "Value_", it's assumed to be a value column and copied to the output.
    • Any other column is ignored (mapped to null).

    For further reference, you can check out these resources:

    Hope this helps. Do let us know if you any further queries.

    0 comments No comments

0 additional answers

Sort by: Most helpful