If you want similar functionality in your U-SQL query, you have to use the CROSS APPLY operator of U-SQL to essentially do the similar job.
@output =
SELECT
Region,
Urls
FROM @searchlog;
@output =
SELECT
Region,
SqlArray.Create(Urls.Split(';')) AS UrlTokens
FROM @output;
@output =
SELECT
Region,
Token AS Url
FROM @output
CROSS APPLY EXPLODE (UrlTokens) AS r(Token);
Cross Apply Output
0
|
1
|
"en-us"
|
"www.nachos.com"
|
"en-us"
|
"www.wikipedia.com"
|
"en-gb"
|
"skiresorts.com"
|
"en-gb"
|
"ski-europe.com"
|
"en-gb"
|
"www.travelersdigest.com/ski_resorts.htm"
|
"en-gb"
|
"mayoclinic.com/health"
|
"en-gb"
|
"webmd.com/a-to-z-guides"
|
"en-gb"
|
"mybrokenleg.com"
|
"en-gb"
|
"wikipedia.com/Bone_fracture"
|
"en-ca"
|
"southparkstudios.com"
|
"en-ca"
|
"wikipedia.org/wiki/Sout_Park"
|
"en-ca"
|
"imdb.com/title/tt0121955"
|
"en-ca"
|
"simon.com/mall"
|
"en-us"
|
"cosmos.com"
|
"en-us"
|
"wikipedia.org/wiki/Cosmos:_A_Personal_Voyage"
|
"en-us"
|
"hulu.com/cosmos"
|
"en-fr"
|
"microsoft.com"
|
"en-fr"
|
"wikipedia.org/wiki/Microsoft"
|
"en-fr"
|
"xbox.com"
|
"en-us"
|
"www.amazon.com"
|
"en-us"
|
"reviews.cnet.com/wireless-headphones"
|
"en-us"
|
"store.apple.com"
|
"en-us"
|
"dominos.com"
|
"en-us"
|
"wikipedia.org/wiki/Domino's_Pizza"
|
"en-us"
|
"facebook.com/dominos"
|
Converting columns into Rows
Similarly if we want to convert a set of columns into rows or revert back to the previous state (refer the above situation), we have to use ARRAY_AGG operator. In the example below you will see rowset taken apart by CROSS APPLY and then reconstructed via the ARRAY_AGG operator.
@a = SELECT Region, Urls FROM @searchlog;
@b =
SELECT
Region,
SqlArray.Create(Urls.Split(';')) AS UrlTokens
FROM @a;
@c =
SELECT
Region,
Token AS Url
FROM @b
CROSS APPLY EXPLODE (UrlTokens) AS r(Token);
@d =
SELECT Region,
string.Join(";", ARRAY_AGG(Url).ToArray()) AS Urls
FROM @a
GROUP BY Region;
