Overzicht van de programmeerbaarheidshandleiding voor U-SQL
Belangrijk
Azure Data Lake Analytics op 29 februari 2024 buiten gebruik gesteld. Meer informatie over deze aankondiging.
Voor gegevensanalyse kan uw organisatie gebruikmaken van Azure Synapse Analytics of Microsoft Fabric.
U-SQL is een querytaal die is ontworpen voor het type big data van workloads. Een van de unieke functies van U-SQL is de combinatie van de SQL-achtige declaratieve taal met de uitbreidbaarheid en programmeerbaarheid van C#. In deze handleiding richten we ons op de uitbreidbaarheid en programmeerbaarheid van de U-SQL-taal die wordt ingeschakeld door C#.
Vereisten
Download en installeer Azure Data Lake Tools voor Visual Studio.
Aan de slag met U-SQL
Bekijk het volgende U-SQL-script:
@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;
Met dit script worden twee rijensets gedefinieerd: @a en @results. RowSet @results wordt gedefinieerd vanuit @a.
C#-typen en -expressies in U-SQL-script
Een U-SQL-expressie is een C#-expressie die wordt gecombineerd met logische U-SQL-bewerkingen, zoals AND, ORen NOT. U-SQL-expressies kunnen worden gebruikt met SELECT, EXTRACT, WHERE, HAVING, GROUP BY en DECLARE. Met het volgende script wordt bijvoorbeeld een tekenreeks geparseerd als een Datum/tijd-waarde.
@results =
SELECT
customer,
amount,
DateTime.Parse(date) AS date
FROM @a;
Het volgende fragment parseert een tekenreeks als Datum/tijd-waarde in een DECLARE-instructie.
DECLARE @d = DateTime.Parse("2016/01/01");
C#-expressies gebruiken voor gegevenstypeconversies
In het volgende voorbeeld ziet u hoe u een datum/tijd-gegevensconversie kunt uitvoeren met behulp van C#-expressies. In dit specifieke scenario worden datum/tijd-tekenreeksgegevens geconverteerd naar standaarddatum/tijd met de notatie middernacht 00:00:00.
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();
C#-expressies gebruiken voor de datum van vandaag
We kunnen de volgende C#-expressie gebruiken om de datum van vandaag op te halen: DateTime.Now.ToString("M/d/yyyy")
Hier volgt een voorbeeld van het gebruik van deze expressie in een script:
@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;
.NET-assembly's gebruiken
Het uitbreidbaarheidsmodel van U-SQL is sterk afhankelijk van de mogelijkheid om aangepaste code toe te voegen vanuit .NET-assembly's.
Een .NET-assembly registreren
Gebruik de CREATE ASSEMBLY instructie om een .NET-assembly in een U-SQL Database te plaatsen. Daarna kunnen U-SQL-scripts deze assembly's gebruiken met behulp van de REFERENCE ASSEMBLY -instructie.
De volgende code laat zien hoe u een assembly registreert:
CREATE ASSEMBLY MyDB.[MyAssembly]
FROM "/myassembly.dll";
De volgende code laat zien hoe u naar een assembly verwijst:
REFERENCE ASSEMBLY MyDB.[MyAssembly];
Raadpleeg de instructies voor assembly-registratie waarin dit onderwerp in meer detail wordt behandeld.
Assembly-versiebeheer gebruiken
Momenteel gebruikt U-SQL de .NET Framework versie 4.7.2. Zorg er dus voor dat uw eigen assembly's compatibel zijn met die versie van de runtime.
Zoals eerder vermeld, voert U-SQL code uit in een 64-bits (x64)-indeling. Zorg er dus voor dat uw code is gecompileerd om te worden uitgevoerd op x64. Anders krijgt u de onjuiste indelingsfout die eerder is weergegeven.
Elk geüpload assembly-DLL- en resourcebestand, zoals een andere runtime, een systeemeigen assembly of een configuratiebestand, kan maximaal 400 MB zijn. De totale grootte van geïmplementeerde resources, hetzij via DEPLOY RESOURCE of via verwijzingen naar assembly's en hun andere bestanden, mag niet groter zijn dan 3 GB.
Ten slotte kan elke U-SQL-database slechts één versie van een bepaalde assembly bevatten. Als u bijvoorbeeld zowel versie 7 als versie 8 van de NewtonSoft Json.NET-bibliotheek nodig hebt, moet u deze in twee verschillende databases registreren. Bovendien kan elk script slechts verwijzen naar één versie van een bepaalde assembly-DLL. In dit opzicht volgt U-SQL de Semantiek van de C#-assemblage en versiebeheer.
Door de gebruiker gedefinieerde functies gebruiken: UDF
U-SQL door de gebruiker gedefinieerde functies of UDF zijn programmeerroutines die parameters accepteren, een actie uitvoeren (zoals een complexe berekening) en het resultaat van die actie als een waarde retourneren. De geretourneerde waarde van UDF kan slechts één scalaire waarde zijn. U-SQL UDF kan worden aangeroepen in het U-SQL-basisscript, net als elke andere scalaire C#-functie.
U wordt aangeraden door de gebruiker gedefinieerde U-SQL-functies te initialiseren als openbaar en statisch.
public static string MyFunction(string param1)
{
return "my result";
}
Laten we eerst het eenvoudige voorbeeld bekijken van het maken van een UDF.
In dit use-casescenario moeten we de fiscale periode bepalen, inclusief het fiscale kwartaal en de fiscale maand van de eerste aanmelding voor de specifieke gebruiker. De eerste fiscale maand van het jaar in ons scenario is juni.
Voor het berekenen van de fiscale periode introduceren we de volgende C#-functie:
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();
}
Het berekent gewoon de fiscale maand en het kwartaal en retourneert een tekenreekswaarde. Voor juni, de eerste maand van het eerste fiscale kwartaal, gebruiken we 'Q1:P1'. Voor juli gebruiken we 'Q1:P2', enzovoort.
Dit is een reguliere C#-functie die we gaan gebruiken in ons U-SQL-project.
De code-behind-sectie ziet er in dit scenario als volgt uit:
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 gaan we deze functie aanroepen vanuit het basis-U-SQL-script. Hiervoor moeten we een volledig gekwalificeerde naam opgeven voor de functie, inclusief de naamruimte, in dit geval NameSpace.Class.Function(parameter).
USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)
Hier volgt het werkelijke U-SQL-basisscript:
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();
Hier volgt het uitvoerbestand van de uitvoering van het script:
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",""
In dit voorbeeld ziet u een eenvoudig gebruik van inline UDF in U-SQL.
Status behouden tussen UDF-aanroepen
U-SQL C#-programmeerobjecten kunnen geavanceerder zijn, waarbij gebruik wordt gemaakt van interactiviteit via de code achter globale variabelen. Laten we het volgende scenario voor bedrijfsgebruik bekijken.
In grote organisaties kunnen gebruikers schakelen tussen verschillende soorten interne toepassingen. Dit kunnen Microsoft Dynamics CRM, Power BI, enzovoort zijn. Klanten willen mogelijk een telemetrieanalyse toepassen van de manier waarop gebruikers schakelen tussen verschillende toepassingen, wat de gebruikstrends zijn, enzovoort. Het doel van het bedrijf is om het gebruik van toepassingen te optimaliseren. Ze willen mogelijk ook verschillende toepassingen of specifieke aanmeldingsroutines combineren.
Om dit doel te bereiken, moeten we sessie-id's en vertragingstijd tussen de laatste sessie die heeft plaatsgevonden bepalen.
We moeten een eerdere aanmelding vinden en deze aanmelding vervolgens toewijzen aan alle sessies die worden gegenereerd voor dezelfde toepassing. De eerste uitdaging is dat het U-SQL-basisscript niet toestaat om berekeningen toe te passen op al berekende kolommen met lag-functie. De tweede uitdaging is dat we de specifieke sessie voor alle sessies binnen dezelfde periode moeten houden.
Om dit probleem op te lossen, gebruiken we een globale variabele in een code-behind-sectie: static public string globalSession;.
Deze globale variabele wordt toegepast op de volledige rijenset tijdens het uitvoeren van het script.
Dit is de code-behind-sectie van ons U-SQL-programma:
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;
}
}
}
In dit voorbeeld ziet u de globale variabele static public string globalSession; die in de getStampUserSession functie wordt gebruikt en telkens opnieuw wordt geïnitialiseerd wanneer de sessieparameter wordt gewijzigd.
Het U-SQL-basisscript is als volgt:
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();
De functie USQLApplication21.UserSession.getStampUserSession(UserSessionTimestamp) wordt hier aangeroepen tijdens de tweede berekening van de geheugenrijset. Deze geeft de UserSessionTimestamp kolom door en retourneert de waarde totdat UserSessionTimestamp deze is gewijzigd.
Het uitvoerbestand is als volgt:
"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"
In dit voorbeeld ziet u een ingewikkelder use-casescenario waarin we een globale variabele gebruiken in een code-behind-sectie die wordt toegepast op de volledige geheugenrijset.