Can anybody please guide me in writing this below SQL in U-SQL language used in Azure Data Lake
select tt.userId, count(tt.userId) from (SELECT userId,count(userId) as cou
FROM [dbo].[users]
where createdTime> DATEADD(wk,-1,GETDATE())
group by userId,DATEPART(minute,createdTime)/5) tt group by tt.userId
I don't find the DATEPART function in U-SQL . Azure Data Analytic job is giving me error.
U-SQL does not provide T-SQL intrinsic functions except for a few (like LIKE). See https://msdn.microsoft.com/en-us/library/azure/mt621343.aspx for a list.
So how do you do DateTime operations? You just use the C# functions and methods!
So DATEADD(wk, -1, GETDATE()) is something like DateTime.Now.AddDays(-7)
and
DATEPART(minute,createdTime)/5 (there is an extra ) in your line) is something like createdTime.Minute/5 (maybe you need to cast it to a double if you want non-integer value).
For anybody who is looking for the implementation mentioned by Michael. It's like below
@records =
EXTRACT userId string,
createdTime DateTime
FROM "/datalake/input/data.tsv"
USING Extractors.Tsv();
@result =
SELECT
userId,
COUNT(createdTime) AS userCount
FROM @records
WHERE createdTime > DateTime.Now.AddDays(-30)
GROUP BY userId,createdTime.Minute/5;
@result2= SELECT userId,COUNT(userId) AS TotalCount
FROM @result
GROUP BY userId;
OUTPUT @result2
TO "/datalake/output/data.csv"
USING Outputters.Csv();