Thursday, March 31, 2016

Pivot in U-SQL

In my case I needed to pivot a table with date by a week day. Meaning, having table like:

Transno Date
1223 11/01/2016
2795 12/01/2016
To get result as:
Transno 1 2 3 4 5 6 7
1223 0 1 0 0 0 0 0
2795 0 0 1 0 0 0 0
Where corresponding week day number column gets value 1, and others - 0... Outputting result to a .csv, of course.

First I select transno, day of week as a number, and some constant for future pivotting.
@res1 =
    SELECT Transno, Convert.ToInt32(Date.DayOfWeek) AS wd, 1 AS a1
    FROM dbo.LocalTransno;

Then use MAP_AGG, which is a base for pivot:
@res2 =
    SELECT Transno,           
           1 AS k1,
           MAP_AGG(wd, (int?) a1) AS mapwd
    FROM @res1
    GROUP BY Transno;

Here I create a dummy table with 1 row, just to select values from 1 to 7 (my weekdays numbers) - as a key values for pivot.
@one = SELECT * FROM (VALUES(1)) AS T(a);
@keys = 
    SELECT 
        1 AS k1, 
        Inmeta.USQLScripts.Helper.InitList(1, 7) AS dkeys
    FROM @one;

Where InitList s a code-behind function returning an object of SqlArray<int>:

  public partial class Helper  {
       public static SqlArray<int> InitList(int lower, int upper) {
            var values = new List<int>();
            for (int i = lower; i <= upper; i++)  {
                values.Add(i);
            }
            var res = new SqlArray<int>(values) ;
            return res;
        }
  }

And finally, pivotting and unrolling to comma-separated format at once:
@res =
    SELECT 
    a.Transno.ToString() + ","  
    String.Join(",", b.dkeys.ToList().Select(k => a.mapwd.ContainsKey(k) ? 1 : 0)) 
    AS x
    FROM @res2 AS a
    JOIN @keys AS b ON a.k1 == b.k1;
Note the trick of dummy join, to connect weekdays keys table with my data :)

And finally output - removing the quotes from string output gives a clean csv :
OUTPUT @res
TO @out
USING Outputters.Csv(quoting : false);

It's a bit of running around, but that's the only way I managed to make it work.

Thursday, March 3, 2016

Accessing Azure Data Lake Storage from .Net SDK - FsOpenStream error 0x83090aa2

For the topic there is a very good basic example located here:
https://azure.microsoft.com/en-us/documentation/articles/data-lake-store-get-started-net-sdk/
with application authentication and it worked.

But once I tried to connect to the existing data lake store - it won't. Giving me a cryptic error:
Exception of type 'Microsoft.Rest.Azure.CloudException' was thrown
with even more cryptic insides like:
FsOpenStream failed with error 0x83090aa2

I have tried fixing file location, assuming may be /myfile.txt is wrong for the file in the very root. Nope. It is all simple, ...as usual.

Remember giving access to your application to the resource group (or subscription or whatever you choosen)? Well, this is not good enough - you have to give access to the data lake store folder explicitely, as it does not inherit it (as I expected it would).

Browse the data lake store, and click on access - app is not there!



So, giving my app access directly to data lake store root folder solved the error.