All about dataflow expressions - Primer

 ADF dataflow has a very rich expression language to describe transformation intent. All this is part of the dataflow metadata specification. Expression are engrained everywhere in the metadata from creating new columns to grouping data to partitioning for optimization. They are the ABCs of dataflow metadata. So, if you know Excel, M or SQL expressions this is equivalent right? Yes and no. ‘Yes’ in the sense the syntax looks alike, ‘No’ in the sense the similarity stops there. To understand this better you need to first know the datatypes of dataflow.

Dataflow supports the following datatypes for column values

1.     String – Unicode set of characters

2.     Boolean – if it is true or false value

3.     Binary – byte array

4.     Date – Date with month/day/year

5.     Timestamp – Date which includes hour/minute/second/millisecond precision

6.     Integral type – Any non-fractional type or whole number

      a.     Byte – 8 bit

      b.     Short – 16 bit

      c.     Integer – 32 bit

      d.     Long – 64 bit

 7.     Fractional type – Any number that can represent fractions

      a.     Float – Single precision with 32 bits

      b.     Double – Double precision with 64 bits

      c.     Decimal – High precision fractional with 38 bits of precision or scale. If you have financial math that requires high degree of precision which cannot have rounding errors use Decimal. Decimal math can be a shade slower that float or decimal. More on this after you master the sword.

Dataflow also has array type, structures to represent hierarchical content, maps for mapping one type to another and combination of all these are also supported. That’s for a different blog after you get the basics.

Ok this means I have my favorite GUID datatype in SQL and it has no representation in dataflow? Dataflow can support newer datatypes with behavior in the future but until then it will be the closest thing this can map to (a string or a binary type). Don’t mistake this with datatypes your source system provides. Dataflow will do the mapping under the scenes to make sure you have no data loss when you read or write from that source system.

Back to the difference between dataflow metadata and any other expression system. It has to do with the grammar. Just like natural language grammar you can say the same words and can be interpreted in different countries differently. Let’s take a simple example.

col1 + col2

That’s easy. Adding two numbers. Not so fast!!

col1 and col2 were strings it means concatenation of strings.

col1 was timestamp and col2 was a number it means adding as many milliseconds to the timestamp.

col1 was high precision decimal(Account balance) and col2 was a integer, the result is a high precision decimal

col1 was a string and col2 was a number it is an error!!!

Now you start to see why expressions looks similar but can mean different things? Don’t go running to paste your U-SQL or Excel expressions without careful inspection.

All expressions are made of three things. Operands, operators and functions.

If this is all sounding like the compiler class you skipped in school, dataflowninja will help you cover for it J.

Operand is a value you are acting on. In the simplest form it is a column.

Function is a routine you are calling on one or more operands.

Operator is a short-hand notation for a function.

Let’s look at a small snippet of the dataflow metadata.

Movies derive(

            upperTitle := upper(title)

            fullName = :upperTitle + concat(initCap(firstName) , lastName)

            balance = amount * floor($discount)%100

            partitionBy(‘key’, trim(country)

 ) ~> DeriveMovies

The underlined text represent expressions and color coded components of an expression.

      Operand

There are 6 types of operands you can use

1.     Column – reference to a column in the input stream. If the name is alphanumeric (alphabets followed by numbers) you can directly use it in the expression. If not, you should enclose it with {}.

 dumboCount

 {count of #dumbos} references a column named count of #dumbos

2.     Constant – a constant value. These can be

       a.     String constant – set of characters enclosed in single quote. Use a \ character to escape single quote in the value. Everything within single quotes is the actual value.

      ‘dumbo’  equates to dumbo

      ‘dumbo\’o big’ equates to dumbo’obig

          b. Integer constant – a whole number that can be held in an integer datatype

              123

          c.   Long constant – a whole number that can be held in a long datatype. Notice it is suffixed by ‘L’

              123123123123L

         d.   Double constant – Fractional number with having a .(dot)

              123.456

         e.   Float constant – Fractional number that can be held in a float datatype suffixed by ‘f’

              123.456f

f.  Boolean constant – true or false. You can also use true() or false() functions if your column names are named true/false!

    true

g. Regex constant – This is a special version of the string constant where you don’t need regex escaping rules. It has a back quote ` start and end

`(\\d+) and (\\d+)` is the same as ‘(\\\\d+) and (\\\\d+)’

3.     Parameter – If you are passing parameters into your dataflow, they can be referenced in expression with the $ syntax. Parameters also have datatypes like any operand

$discountCode

4.     Transient – Transients allow you to store repeated computations in a temporary column. They cannot be used outside the transformation they are defined in. You can reference a transient prefixed with : symbol

 :orderTempTotal

5.     Function outputs – A output of a function can be used as an operand.

     concat(‘dum’, upper(‘bo’))

6.     Meta operands – These are special inputs that result out of pattern matching functions. We will cover these in a schema drift for transformation blog.

     $1

Functions

Functions take operands as input and return an output. The output is again of a certain datatype. Functions in dataflow have either fixed or variable number of inputs.

upper(title)

concat(‘d’, ‘u’, ‘m’, ‘b’, ‘o’)

Functions also have optional inputs. You can choose to pass a value or omit it.

trim(title)

trim(title, ‘-‘)

Operators

Operators are shorthand notation for some functions. It is highly encouraged to use operators for brevity instead of function equivalents.

orderTotal * {discount%} + 100

add(multiply(orderTotal, {discount%}), 100)

Operators supported are

1.     -  unary minus to negate a numeric value

2.     !  negate a boolean value

3.     *  multiply

4.     /  divide

5.     %  modulus

6.     + add

7.     – substract

8.     == equals

9.     === equal with null (we will learn about NULL in a different blog)

10.  != not equal

11.  <=> equalsIgnoreCase for strings

12.  > greater

13.  < lesser

14.  >= greaterOrEqual

15.  <= lesserOrEqual

Operators have precedence in that some apply first before the other. The operators above are given in the precedence order.

100 + orderTotal * {discount%}  is the same as orderTotal * {discount%} + 100 as * precedes +.

TIP: Did you know dataflow metadata has no reserved words! You can name your columns Select or Table or {I love Dumbeldore!@!#^&}

Now that you are armed with the basics of how expressions work, we will explore tips and tricks and advanced expression techniques in subsequent blogs.