Overzicht van U-SQL-programmeerbaarheid

Belangrijk

Azure Data Lake Analytics is op 29 februari 2024 buiten gebruik gesteld. Meer informatie vindt u in deze aankondiging.

Voor gegevensanalyse kan uw organisatie Azure Synapse Analytics of Microsoft Fabric gebruiken.

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 declaratieve taal zoals SQL met de uitbreidbaarheid en programmeerbaarheid die wordt geleverd door C#. In deze handleiding concentreren we ons op de uitbreidbaarheid en programmeerbaarheid van de U-SQL-taal die is ingeschakeld door C#.

Behoeften

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;

Dit script definieert twee RowSets: @a en @results. RowSet @results is gedefinieerd vanuit @a.

C#-typen en -expressies in U-SQL-script

Een U-SQL-expressie is een C#-expressie in combinatie 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;

Met het volgende codefragment wordt een tekenreeks geparseerd als datum/tijd-waarde in een DECLARE-instructie.

DECLARE @d = DateTime.Parse("2016/01/01");

C#-expressies gebruiken voor conversies van gegevenstypen

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-gegevens van tekenreeksen geconverteerd naar standaard datum/tijd met middernacht 00:00:00 tijdnotatie.

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

Als u de datum van vandaag wilt ophalen, kunt u de volgende C#-expressie gebruiken: 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-assemblies 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 registratie-instructies voor assembly's die dit onderwerp in meer detail bespreken.

Assemblyversiebeheer gebruiken

Momenteel maakt U-SQL gebruik van .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 krijg je de onjuiste formatfout die eerder is getoond.

Elk geüploade assembly-DLL en resourcebestand, zoals een andere runtime, een systeemeigen assembly of een configuratiebestand, kunnen maximaal 400 MB zijn. De totale grootte van ingezette resources, door middel van DEPLOY RESOURCE of door middel van 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 registreren in twee verschillende databases. Bovendien kan elk script slechts verwijzen naar één versie van een bepaalde assembly-DLL. In dit opzicht volgt U-SQL de Semantiek voor C#-assemblybeheer en versiebeheer.

Door de gebruiker gedefinieerde functies gebruiken: UDF

Door de gebruiker gedefinieerde U-SQL-functies of UDF zijn programmeerroutines die parameters accepteren, een actie uitvoeren (zoals een complexe berekening) en het resultaat van die actie retourneren als een waarde. De retourwaarde van UDF kan slechts één scalaire waarde zijn. U-SQL UDF kan worden aangeroepen in een U-SQL-basisscript zoals elke andere scalaire C#-functie.

U wordt aangeraden door de gebruiker gedefinieerde U-SQL-functies als openbaar en statisch te initialiseren.

public static string MyFunction(string param1)
{
    return "my result";
}

Laten we eerst eens kijken naar het eenvoudige voorbeeld van het maken van een UDF.

In dit gebruiksscenario moeten we de fiscale periode bepalen, inclusief het fiscale kwartaal en de fiscale maand van de eerste login voor de betreffende 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 fiscale maand en 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.

In dit scenario ziet de sectie code-behind er 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 U-SQL-basisscript. Hiervoor moeten we een volledig gekwalificeerde naam opgeven voor de functie, inclusief de naamruimte, die in dit geval NameSpace.Class.Function(parameter) is.

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 scriptuitvoering:

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 globale variabelen achter de code. Laten we eens kijken naar het volgende zakelijke gebruiksscenario.

In grote organisaties kunnen gebruikers schakelen tussen variëteiten van interne toepassingen. Dit kunnen Microsoft Dynamics CRM, Power BI enzovoort zijn. Klanten willen mogelijk een telemetrieanalyse toepassen van hoe 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 vertraging bepalen tussen de voorgaande sessie en de laatste sessie.

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 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 sectie achter code: static public string globalSession;.

Deze globale variabele wordt toegepast op de hele 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 wanneer de parameter Sessie wordt gewijzigd, opnieuw wordt geïnitialiseerd.

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. Het gaat langs de UserSessionTimestamp kolom en retourneert de waarde totdat UserSessionTimestamp 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 gecompliceerder use-casescenario waarin we een globale variabele gebruiken in een code-behind-sectie die wordt toegepast op de volledige geheugenrijset.

Volgende stappen

• U-SQL-programmeerhandleiding - UDT en UDAGG
• U-SQL-programmeerhandleiding - UDO