When you get started with Azure Data Lake Analytics and U-SQL specifically, you may get a little confused. It looks like a mash-up of T-SQL and C#. Turns out, That’s exactly what it is!  You can find lots of information on MSDN, or GitHub, or StackOverflow. Let’s get started with some basics.

Variables, DataTypes, and Case Sensitivity

One of the early demos I produced for my new Data Lake client was a script to pick up files landing in their Raw folder, and scrub out the sensitive information. At the top of this script, we define two variables, one for the input file and one for the output file.

DECLARE @InputFile string = @"/Raw/TestTenant/Demographic/Households/Households_201705_46201.csv";
DECLARE @OutputFile string = @"/Stage/TestTenant/Demographic/HouseHolds/Households_201705_46201.csv";

The DECLARE @InputFile looks like standard T-SQL, but then the data type string isn’t.  Data types have to be listed in their C# forms. If you’re a SQL guy like me, you’re going to want a quick reference to translate back and forth. You may not recognize the at symbol in front of the values.  That’s a c# convention that tells the interpreter to interpret the string literally, and do not try to interpret any escape characters.  That’s import here since I don’t want my paths to be interpreted with excape characters.  In the demo above, I’m using Linux or web notation, but you can reference paths like Windows, and include backslashes (\).

There are three things to keep in mind as you start writing U-SQL. First, all your identifiers (variable, column, and later table names) are case sensitive.  If you key in the right identifier, but in the wrong case, you will get an error message from the compiler.  That’s due to the fact this code is getting compiled down to c# before it’s executed on multiple compute nodes.

Second, U-SQL’s reserved words need to be in all caps.  Luckily, that’s a convention I tried to stick to in T-SQL since it made my code a little easier to read.

Lastly, statements need to end in a semi-colon.  That tells the interpreter you are at the end of a statement, move on to the next statement.  In T-SQL you can still use GO as a terminator, but that’s been officially deprecated for years.  You should have been replacing them with semi-colons.  So, now’s a good time to start that habit too, right?

Multi-Step Processing

U-SQL scripts are like T-SQL in that you can combine multiple statements into a single script to perform a complex operation. In my demo, after declaring the input and output files, I EXTRACT data from my source file.

@SourceData =
   EXTRACT 
    [Column1] string,
    [Column2] string,
    [Column3] int?,
 FROM @InputFile
 USING Extractors.Csv(silent:true, skipFirstNRows:1);

This is a little like declaring a Common Table Expression (CTE) in T-SQL. In U-SQL you store the output from the input file in a variable.  We could have several statements after this statement and still make a reference to @SourceData.  At least in that, U-SQL is a little easier to work with. The first statement after the variable is one of a handful of expressions in U-SQL.  In this case, we want to get data out of a file and use it in a later step. The really cool part is Extract can operate over one or many files. Even better, since U-SQL was built to parallel process, the more files you have to Extract and process at the same time, the more efficient the script becomes (if you crank up the performance).

Extract can establish Schema on Read. So, the three columns I’m defining show you how that would happen.  As ADLA reads my file, it’s only looking for three columns, two strings followed by an nullable integer.  That question mark after the data type is how you define a nullable int. Most data types in c# will require it to allow for null values.  Check out the msdn for more info on when you’ll need it…otherwise you can just add it when your script fails due to a null value.

The from clause looks pretty similar to T-SQL. The only difference is it’s referring to a file instead of a table.

The last statement is Using.  This isn’t like anything in T-SQL. This is closer to C#.  Basically, it allows you to reference function or extension names.  In this case we want to use a class called Extractors that will let us create our rowset from a file or collection of files. In that class, we can extract data from text files, Comma Separated (CSVs), or Tab Separated files (TSVs).  These functions take several parameters.  In my case, I wanted to keep reading, effectively ignoring rows that do not match my expected format. That’s what the silent:true gives me.  I also wanted to skip the header rows, because the name of [column 3] is a text value and not an integer value.

If I hadn’t passed that parameter, the Extractor would have failed!

Conclusion

At this point in our script, we have set variables and read a file into a row set.  In my next entry, I’ll show you how you can start building some advanced transformations like Hashing values.  After that, I’ll show how we could automatically generate this script from some simple metadata!  In the mean time, if you have questions, please send them in!  I’m here to help.