Översikt över U-SQL-programmeringsguiden
Viktigt
Azure Data Lake Analytics drog sig tillbaka den 29 februari 2024. Läs mer med det här meddelandet.
För dataanalys kan din organisation använda Azure Synapse Analytics eller Microsoft Fabric.
U-SQL är ett frågespråk som är utformat för stordatatyp av arbetsbelastningar. En av de unika funktionerna i U-SQL är kombinationen av det SQL-liknande deklarativa språket med utökningsbarheten och programmeringen som tillhandahålls av C#. I den här guiden koncentrerar vi oss på utökningsbarheten och programmerbarheten för det U-SQL-språk som aktiveras av C#.
Krav
Ladda ned och installera Azure Data Lake Tools för Visual Studio.
Kom igång med U-SQL
Titta på följande U-SQL-skript:
@a =
SELECT * FROM
(VALUES
("Contoso", 1500.0, "2017-03-39"),
("Woodgrove", 2700.0, "2017-04-10")
) AS D( customer, amount, date );
@results =
SELECT
customer,
amount,
date
FROM @a;
Det här skriptet definierar två Raduppsättningar: @a och @results. RowSet @results definieras från @a.
C#-typer och uttryck i U-SQL-skript
Ett U-SQL-uttryck är ett C#-uttryck i kombination med logiska U-SQL-åtgärder som AND, ORoch NOT. U-SQL-uttryck kan användas med SELECT, EXTRACT, WHERE, HAVING, GROUP BY och DECLARE. Följande skript parsar till exempel en sträng som ett DateTime-värde.
@results =
SELECT
customer,
amount,
DateTime.Parse(date) AS date
FROM @a;
Följande kodfragment parsar en sträng som DateTime-värde i en DECLARE-instruktion.
DECLARE @d = DateTime.Parse("2016/01/01");
Använda C#-uttryck för datatypskonverteringar
I följande exempel visas hur du kan göra en datetime-datakonvertering med hjälp av C#-uttryck. I det här scenariot konverteras data för sträng datetime till standard datetime med midnatt 00:00:00 tidsnotation.
DECLARE @dt = "2016-07-06 10:23:15";
@rs1 =
SELECT
Convert.ToDateTime(Convert.ToDateTime(@dt).ToString("yyyy-MM-dd")) AS dt,
dt AS olddt
FROM @rs0;
OUTPUT @rs1
TO @output_file
USING Outputters.Text();
Använda C#-uttryck för dagens datum
För att hämta dagens datum kan vi använda följande C#-uttryck: DateTime.Now.ToString("M/d/yyyy")
Här är ett exempel på hur du använder det här uttrycket i ett skript:
@rs1 =
SELECT
MAX(guid) AS start_id,
MIN(dt) AS start_time,
MIN(Convert.ToDateTime(Convert.ToDateTime(dt<@default_dt?@default_dt:dt).ToString("yyyy-MM-dd"))) AS start_zero_time,
MIN(USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)) AS start_fiscalperiod,
DateTime.Now.ToString("M/d/yyyy") AS Nowdate,
user,
des
FROM @rs0
GROUP BY user, des;
Använda .NET-sammansättningar
U-SQL:s utökningsmodell är starkt beroende av möjligheten att lägga till anpassad kod från .NET-sammansättningar.
Registrera en .NET-sammansättning
Använd -instruktionen CREATE ASSEMBLY för att placera en .NET-sammansättning i en U-SQL Database. Därefter kan U-SQL-skript använda dessa sammansättningar med hjälp av -instruktionen REFERENCE ASSEMBLY .
Följande kod visar hur du registrerar en sammansättning:
CREATE ASSEMBLY MyDB.[MyAssembly]
FROM "/myassembly.dll";
Följande kod visar hur du refererar till en sammansättning:
REFERENCE ASSEMBLY MyDB.[MyAssembly];
Mer information finns i anvisningarna för sammansättningsregistrering som beskriver det här avsnittet.
Använda versionshantering av sammansättning
För närvarande använder U-SQL .NET Framework version 4.7.2. Se därför till att dina egna sammansättningar är kompatibla med den versionen av körningen.
Som tidigare nämnts kör U-SQL kod i 64-bitarsformat (x64). Se därför till att koden kompileras för att köras på x64. Annars visas det felaktiga formatfelet som visades tidigare.
Varje uppladdad sammansättnings-DLL och resursfil, till exempel en annan körning, en intern sammansättning eller en konfigurationsfil, kan vara högst 400 MB. Den totala storleken på distribuerade resurser, antingen via DEPLOY RESOURCE eller via referenser till sammansättningar och deras andra filer, får inte överstiga 3 GB.
Slutligen kan varje U-SQL-databas bara innehålla en version av en viss sammansättning. Om du till exempel behöver både version 7 och version 8 av NewtonSoft-Json.NET-biblioteket måste du registrera dem i två olika databaser. Dessutom kan varje skript bara referera till en version av en viss sammansättnings-DLL. I det här avseendet följer U-SQL C#-monteringshantering och versionssemantik.
Använda användardefinierade funktioner: UDF
U-SQL-användardefinierade funktioner, eller UDF, är programmeringsrutiner som accepterar parametrar, utför en åtgärd (till exempel en komplex beräkning) och returnerar resultatet av åtgärden som ett värde. Returvärdet för UDF kan bara vara en enda skalär. U-SQL UDF kan anropas i U-SQL-basskript som andra C#-skalärfunktioner.
Vi rekommenderar att du initierar U-SQL-användardefinierade funktioner som offentliga och statiska.
public static string MyFunction(string param1)
{
return "my result";
}
Först ska vi titta på det enkla exemplet på hur du skapar en UDF.
I det här användningsscenariot måste vi fastställa räkenskapsperioden, inklusive räkenskapskvartalet och räkenskapsmånaden för den första inloggningen för den specifika användaren. Den första räkenskapsmånaden på året i vårt scenario är juni.
För att beräkna räkenskapsperioden introducerar vi följande C#-funktion:
public static string GetFiscalPeriod(DateTime dt)
{
int FiscalMonth=0;
if (dt.Month < 7)
{
FiscalMonth = dt.Month + 6;
}
else
{
FiscalMonth = dt.Month - 6;
}
int FiscalQuarter=0;
if (FiscalMonth >=1 && FiscalMonth<=3)
{
FiscalQuarter = 1;
}
if (FiscalMonth >= 4 && FiscalMonth <= 6)
{
FiscalQuarter = 2;
}
if (FiscalMonth >= 7 && FiscalMonth <= 9)
{
FiscalQuarter = 3;
}
if (FiscalMonth >= 10 && FiscalMonth <= 12)
{
FiscalQuarter = 4;
}
return "Q" + FiscalQuarter.ToString() + ":P" + FiscalMonth.ToString();
}
Den beräknar helt enkelt räkenskapsmånad och kvartal och returnerar ett strängvärde. För juni, den första månaden i det första räkenskapskvartalet, använder vi "Q1:P1". För juli använder vi "Q1:P2" och så vidare.
Det här är en vanlig C#-funktion som vi ska använda i vårt U-SQL-projekt.
Så här ser avsnittet bakom koden ut i det här scenariot:
using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace USQL_Programmability
{
public class CustomFunctions
{
public static string GetFiscalPeriod(DateTime dt)
{
int FiscalMonth=0;
if (dt.Month < 7)
{
FiscalMonth = dt.Month + 6;
}
else
{
FiscalMonth = dt.Month - 6;
}
int FiscalQuarter=0;
if (FiscalMonth >=1 && FiscalMonth<=3)
{
FiscalQuarter = 1;
}
if (FiscalMonth >= 4 && FiscalMonth <= 6)
{
FiscalQuarter = 2;
}
if (FiscalMonth >= 7 && FiscalMonth <= 9)
{
FiscalQuarter = 3;
}
if (FiscalMonth >= 10 && FiscalMonth <= 12)
{
FiscalQuarter = 4;
}
return "Q" + FiscalQuarter.ToString() + ":" + FiscalMonth.ToString();
}
}
}
Nu ska vi anropa den här funktionen från det grundläggande U-SQL-skriptet. För att göra detta måste vi ange ett fullständigt kvalificerat namn för funktionen, inklusive namnområdet, som i det här fallet är NameSpace.Class.Function(parameter).
USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)
Följande är det faktiska U-SQL-basskriptet:
DECLARE @input_file string = @"\usql-programmability\input_file.tsv";
DECLARE @output_file string = @"\usql-programmability\output_file.tsv";
@rs0 =
EXTRACT
guid Guid,
dt DateTime,
user String,
des String
FROM @input_file USING Extractors.Tsv();
DECLARE @default_dt DateTime = Convert.ToDateTime("06/01/2016");
@rs1 =
SELECT
MAX(guid) AS start_id,
MIN(dt) AS start_time,
MIN(Convert.ToDateTime(Convert.ToDateTime(dt<@default_dt?@default_dt:dt).ToString("yyyy-MM-dd"))) AS start_zero_time,
MIN(USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)) AS start_fiscalperiod,
user,
des
FROM @rs0
GROUP BY user, des;
OUTPUT @rs1
TO @output_file
USING Outputters.Text();
Följande är utdatafilen för skriptkörningen:
0d8b9630-d5ca-11e5-8329-251efa3a2941,2016-02-11T07:04:17.2630000-08:00,2016-06-01T00:00:00.0000000,"Q3:8","User1",""
20843640-d771-11e5-b87b-8b7265c75a44,2016-02-11T07:04:17.2630000-08:00,2016-06-01T00:00:00.0000000,"Q3:8","User2",""
301f23d2-d690-11e5-9a98-4b4f60a1836f,2016-02-11T09:01:33.9720000-08:00,2016-06-01T00:00:00.0000000,"Q3:8","User3",""
Det här exemplet visar en enkel användning av infogad UDF i U-SQL.
Behåll tillstånd mellan UDF-anrop
U-SQL C#-programmeringsobjekt kan vara mer avancerade och använda interaktivitet via de globala variablerna bakom koden. Nu ska vi titta på följande scenario för affärsanvändning.
I stora organisationer kan användare växla mellan olika typer av interna program. Dessa kan omfatta Microsoft Dynamics CRM, Power BI och så vidare. Kunder kanske vill använda en telemetrianalys av hur användare växlar mellan olika program, vilka användningstrender som är och så vidare. Målet för verksamheten är att optimera programanvändningen. De kanske också vill kombinera olika program eller specifika inloggningsrutiner.
För att uppnå det här målet måste vi fastställa sessions-ID:t och fördröjningstiden mellan den senaste sessionen som inträffade.
Vi måste hitta en tidigare inloggning och sedan tilldela den här inloggningen till alla sessioner som genereras till samma program. Den första utmaningen är att U-SQL-basskriptet inte tillåter oss att tillämpa beräkningar på redan beräknade kolumner med FUNKTIONEN LAG. Den andra utmaningen är att vi måste behålla den specifika sessionen för alla sessioner inom samma tidsperiod.
För att lösa det här problemet använder vi en global variabel i ett kod bakom avsnitt: static public string globalSession;.
Den här globala variabeln tillämpas på hela raduppsättningen under skriptkörningen.
Här är avsnittet bakom koden i vårt U-SQL-program:
using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace USQLApplication21
{
public class UserSession
{
static public string globalSession;
static public string StampUserSession(string eventTime, string PreviousRow, string Session)
{
if (!string.IsNullOrEmpty(PreviousRow))
{
double timeGap = Convert.ToDateTime(eventTime).Subtract(Convert.ToDateTime(PreviousRow)).TotalMinutes;
if (timeGap <= 60) {return Session;}
else {return Guid.NewGuid().ToString();}
}
else {return Guid.NewGuid().ToString();}
}
static public string getStampUserSession(string Session)
{
if (Session != globalSession && !string.IsNullOrEmpty(Session)) { globalSession = Session; }
return globalSession;
}
}
}
Det här exemplet visar den globala variabel som static public string globalSession; används i getStampUserSession funktionen och initieras om varje gång parametern Session ändras.
U-SQL-basskriptet är följande:
DECLARE @in string = @"\UserSession\test1.tsv";
DECLARE @out1 string = @"\UserSession\Out1.csv";
DECLARE @out2 string = @"\UserSession\Out2.csv";
DECLARE @out3 string = @"\UserSession\Out3.csv";
@records =
EXTRACT DataId string,
EventDateTime string,
UserName string,
UserSessionTimestamp string
FROM @in
USING Extractors.Tsv();
@rs1 =
SELECT
EventDateTime,
UserName,
LAG(EventDateTime, 1)
OVER(PARTITION BY UserName ORDER BY EventDateTime ASC) AS prevDateTime,
string.IsNullOrEmpty(LAG(EventDateTime, 1)
OVER(PARTITION BY UserName ORDER BY EventDateTime ASC)) AS Flag,
USQLApplication21.UserSession.StampUserSession
(
EventDateTime,
LAG(EventDateTime, 1) OVER(PARTITION BY UserName ORDER BY EventDateTime ASC),
LAG(UserSessionTimestamp, 1) OVER(PARTITION BY UserName ORDER BY EventDateTime ASC)
) AS UserSessionTimestamp
FROM @records;
@rs2 =
SELECT
EventDateTime,
UserName,
LAG(EventDateTime, 1)
OVER(PARTITION BY UserName ORDER BY EventDateTime ASC) AS prevDateTime,
string.IsNullOrEmpty( LAG(EventDateTime, 1) OVER(PARTITION BY UserName ORDER BY EventDateTime ASC)) AS Flag,
USQLApplication21.UserSession.getStampUserSession(UserSessionTimestamp) AS UserSessionTimestamp
FROM @rs1
WHERE UserName != "UserName";
OUTPUT @rs2
TO @out2
ORDER BY UserName, EventDateTime ASC
USING Outputters.Csv();
Funktionen USQLApplication21.UserSession.getStampUserSession(UserSessionTimestamp) anropas här under den andra minnesraduppsättningsberäkningen. Den skickar UserSessionTimestamp kolumnen och returnerar värdet tills har ändrats UserSessionTimestamp .
Utdatafilen är följande:
"2016-02-19T07:32:36.8420000-08:00","User1",,True,"72a0660e-22df-428e-b672-e0977007177f"
"2016-02-17T11:52:43.6350000-08:00","User2",,True,"4a0cd19a-6e67-4d95-a119-4eda590226ba"
"2016-02-17T11:59:08.8320000-08:00","User2","2016-02-17T11:52:43.6350000-08:00",False,"4a0cd19a-6e67-4d95-a119-4eda590226ba"
"2016-02-11T07:04:17.2630000-08:00","User3",,True,"51860a7a-1610-4f74-a9ea-69d5eef7cd9c"
"2016-02-11T07:10:33.9720000-08:00","User3","2016-02-11T07:04:17.2630000-08:00",False,"51860a7a-1610-4f74-a9ea-69d5eef7cd9c"
"2016-02-15T21:27:41.8210000-08:00","User3","2016-02-11T07:10:33.9720000-08:00",False,"4d2bc48d-bdf3-4591-a9c1-7b15ceb8e074"
"2016-02-16T05:48:49.6360000-08:00","User3","2016-02-15T21:27:41.8210000-08:00",False,"dd3006d0-2dcd-42d0-b3a2-bc03dd77c8b9"
"2016-02-16T06:22:43.6390000-08:00","User3","2016-02-16T05:48:49.6360000-08:00",False,"dd3006d0-2dcd-42d0-b3a2-bc03dd77c8b9"
"2016-02-17T16:29:53.2280000-08:00","User3","2016-02-16T06:22:43.6390000-08:00",False,"2fa899c7-eecf-4b1b-a8cd-30c5357b4f3a"
"2016-02-17T16:39:07.2430000-08:00","User3","2016-02-17T16:29:53.2280000-08:00",False,"2fa899c7-eecf-4b1b-a8cd-30c5357b4f3a"
"2016-02-17T17:20:39.3220000-08:00","User3","2016-02-17T16:39:07.2430000-08:00",False,"2fa899c7-eecf-4b1b-a8cd-30c5357b4f3a"
"2016-02-19T05:23:54.5710000-08:00","User3","2016-02-17T17:20:39.3220000-08:00",False,"6ca7ed80-c149-4c22-b24b-94ff5b0d824d"
"2016-02-19T05:48:37.7510000-08:00","User3","2016-02-19T05:23:54.5710000-08:00",False,"6ca7ed80-c149-4c22-b24b-94ff5b0d824d"
"2016-02-19T06:40:27.4830000-08:00","User3","2016-02-19T05:48:37.7510000-08:00",False,"6ca7ed80-c149-4c22-b24b-94ff5b0d824d"
"2016-02-19T07:27:37.7550000-08:00","User3","2016-02-19T06:40:27.4830000-08:00",False,"6ca7ed80-c149-4c22-b24b-94ff5b0d824d"
"2016-02-19T19:35:40.9450000-08:00","User3","2016-02-19T07:27:37.7550000-08:00",False,"3f385f0b-3e68-4456-ac74-ff6cef093674"
"2016-02-20T00:07:37.8250000-08:00","User3","2016-02-19T19:35:40.9450000-08:00",False,"685f76d5-ca48-4c58-b77d-bd3a9ddb33da"
"2016-02-11T09:01:33.9720000-08:00","User4",,True,"9f0cf696-c8ba-449a-8d5f-1ca6ed8f2ee8"
"2016-02-17T06:30:38.6210000-08:00","User4","2016-02-11T09:01:33.9720000-08:00",False,"8b11fd2a-01bf-4a5e-a9af-3c92c4e4382a"
"2016-02-17T22:15:26.4020000-08:00","User4","2016-02-17T06:30:38.6210000-08:00",False,"4e1cb707-3b5f-49c1-90c7-9b33b86ca1f4"
"2016-02-18T14:37:27.6560000-08:00","User4","2016-02-17T22:15:26.4020000-08:00",False,"f4e44400-e837-40ed-8dfd-2ea264d4e338"
"2016-02-19T01:20:31.4800000-08:00","User4","2016-02-18T14:37:27.6560000-08:00",False,"2136f4cf-7c7d-43c1-8ae2-08f4ad6a6e08"
Det här exemplet visar ett mer komplicerat användningsfall där vi använder en global variabel i ett kod bakom avsnitt som tillämpas på hela minnesraduppsättningen.