Overzicht van de programmeerbaarheidshandleiding voor U-SQL

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 de bijbehorende extra bestanden, mag niet groter zijn dan 3 GB.

Houd er ten slotte rekening mee dat elke U-SQL-database slechts één versie van een bepaalde assembly kan 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 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 sectie achter code 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 bepalen tussen de laatste sessie die is opgetreden.

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.

Volgende stappen