Aan de slag met U-SQL in Azure Data Lake Analytics

  • Artikel

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 taal die declaratieve SQL combineert met imperatieve C# zodat u gegevens op elke schaal kunt verwerken. Dankzij de schaalbare, gedistribueerde queryfunctie van U-SQL kunt u gegevens in relationele archieven, zoals Azure SQL Database, efficiënt analyseren. Met U-SQL kunt u ongestructureerde gegevens verwerken door een schema toe te passen op het lezen en invoegen van aangepaste logica en UDF's. Bovendien bevat U-SQL uitbreidbaarheid waarmee u gedetailleerde controle hebt over hoe u op schaal kunt uitvoeren.

Trainingsmateriaal

Laten we wat gegevens maken

Het volgende U-SQL-script is eenvoudig en laat ons een voorbeeldgegevensbestand maken waarnaar we kunnen verwijzen in onze andere U-SQL-scripts. In de volgende sectie bespreken we de belangrijkste concepten die ervoor zorgen dat dit script wordt uitgevoerd.

USE DATABASE master;
USE SCHEMA dbo;
@a  = 
    SELECT * FROM 
        (VALUES
            (399266, "2/15/2012 11:53:16 AM", "en-us", "microsoft", 73, "microsoft.com;xbox.com", "NULL"),
			(382045, "2/15/2012 11:53:18 AM", "en-gb", "azure data lake analytics", 614, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(382045, "2/16/2012 11:53:20 AM", "en-gb", "usql", 74, "microsoft.com;github.com", "NULL"),
			(106479, "2/16/2012 11:53:50 AM", "en-ca", "xbox", 24, "xbox.com;xbox.com/xbox360", "xbox.com/xbox360"),
			(906441, "2/16/2012 11:54:01 AM", "en-us", "machine learning", 1213, "microsoft.com;github.com", "NULL"),
			(304305, "2/16/2012 11:54:03 AM", "en-us", "outlook", 60, "microsoft.com;office.com;live.com","microsoft.com"),
			(460748, "2/16/2012 11:54:04 AM", "en-us", "azure storage", 1270, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(354841, "2/16/2012 11:59:01 AM", "en-us", "azure", 610, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(354068, "2/16/2012 12:00:33 PM", "en-mx", "key vault", 422, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(347413, "2/16/2012 12:11:55 PM", "en-gr", "github", 305, "github.com", "NULL"),
			(840614, "2/16/2012 12:13:56 PM", "en-us", "surface", 1220, "microsoft.com", "NULL"),
			(656666, "2/16/2012 12:15:55 PM", "en-us", "visual studio", 691, "microsoft.com;code.visualstudio.com", "NULL"),
			(951513, "2/16/2012 12:17:00 PM", "en-us", "power bi", 63, "microsoft.com;app.powerbi.com", "powerbi.com"),
			(350350, "2/16/2012 12:18:17 PM", "en-us", "data factory", 30, "microsoft.com;portal.azure.com", "NULL"),
			(641615, "2/16/2012 12:19:55 PM", "en-us", "event hubs", 119, "microsoft.com;portal.azure.com", "NULL")
        ) AS 
              D( UserId, Start, Region, Query, Duration, Urls, ClickedUrls );
OUTPUT @a
    TO "Samples/Data/SearchLog.tsv"
    USING Outputters.Tsv();

Gegevens uit een bestand lezen

Nu we enkele gegevens hebben, gaan we deze lezen uit het bestand dat we hebben gemaakt.

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();

OUTPUT @searchlog
    TO "/output/SearchLog-first-u-sql.csv"
    USING Outputters.Csv();

Dit script heeft geen transformatiestappen. Het leest uit het bronbestand met de naam SearchLog.tsv, schematiseert het en schrijft de rijenset terug naar een bestand met de naam SearchLog-first-u-sql.csv.

Let op het vraagteken naast het gegevenstype in het Duration veld. Dit betekent dat het Duration veld null kan zijn.

Belangrijkste concepten

  • Rijensetvariabelen: elke query-expressie die een rijenset produceert, kan worden toegewezen aan een variabele. U-SQL volgt (bijvoorbeeld) het naamgevingspatroon@searchlog van T-SQL-variabelen in het script.
  • Het trefwoord EXTRACT leest gegevens uit een bestand en definieert het schema bij het lezen. Extractors.Tsv is een ingebouwde U-SQL-extractor voor bestanden met door tabs gescheiden waarden. U kunt aangepaste extractoren ontwikkelen.
  • De UITVOER schrijft gegevens uit een rijenset naar een bestand. Outputters.Csv() is een ingebouwde U-SQL-uitvoerter om een bestand met door komma's gescheiden waarden te maken. U kunt aangepaste uitvoerters ontwikkelen.

Bestandspaden

De instructies EXTRACT en OUTPUT maken gebruik van bestandspaden. Bestandspaden kunnen absoluut of relatief zijn:

Dit volgende absolute bestandspad verwijst naar een bestand in een Data Lake Store met de naam mystore:

adl://mystore.azuredatalakestore.net/Samples/Data/SearchLog.tsv

Dit volgende bestandspad begint met "/". Het verwijst naar een bestand in het standaard Data Lake Store-account:

/output/SearchLog-first-u-sql.csv

Scalaire variabelen gebruiken

U kunt ook scalaire variabelen gebruiken om het onderhoud van uw script eenvoudiger te maken. Het vorige U-SQL-script kan ook worden geschreven als:

DECLARE @in  string = "/Samples/Data/SearchLog.tsv";
DECLARE @out string = "/output/SearchLog-scalar-variables.csv";
@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM @in
    USING Extractors.Tsv();
OUTPUT @searchlog
    TO @out
    USING Outputters.Csv();

Rijensets transformeren

Gebruik SELECT om rijensets te transformeren. Met dit script worden de kolommen Start, Region en Duration geselecteerd en worden deze rijen naar een bestand geschreven wanneer Regio de waarde 'en-gb' heeft:

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT Start, Region, Duration
    FROM @searchlog
WHERE Region == "en-gb";
OUTPUT @rs1
    TO "/output/SearchLog-transform-rowsets.csv"
    USING Outputters.Csv();

De WHERE-component maakt gebruik van een Booleaanse C#-expressie. U kunt de C#-expressietaal gebruiken om uw eigen expressies en functies uit te voeren. U kunt zelfs complexere filters uitvoeren door deze te combineren met logische combinaties (AND's) en disjunctions (OK's).

In het volgende script wordt de methode DateTime.Parse() en een combinatie gebruikt. kolommen Start, Region en Duration, waarbij Region de waarde 'en-gb' heeft. Vervolgens wordt gecontroleerd op waarden van de kolom Duur tussen bepaalde datums en worden deze waarden naar een bestand geschreven:

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT Start, Region, Duration
    FROM @searchlog
WHERE Region == "en-gb";
@rs1 =
    SELECT Start, Region, Duration
    FROM @rs1
    WHERE Start >= DateTime.Parse("2012/02/16") AND Start <= DateTime.Parse("2012/02/17");
OUTPUT @rs1
    TO "/output/SearchLog-transform-datetime.csv"
    USING Outputters.Csv();

Notitie

De tweede query wordt uitgevoerd op het resultaat van de eerste rijenset, waarmee een samenstelling van de twee filters wordt gemaakt. U kunt ook de naam van een variabele opnieuw gebruiken en de namen hebben een lexicatisch bereik.

Rijensets aggregeren

U-SQL biedt u de vertrouwde ORDER BY, GROUP BY en aggregaties.

Met de volgende query wordt de totale duur per regio gevonden en vervolgens worden de vijf belangrijkste duurs in volgorde weergegeven.

U-SQL-rijensets behouden hun volgorde voor de volgende query niet. Als u dus een uitvoer wilt bestellen, moet u ORDER BY toevoegen aan de instructie OUTPUT:

DECLARE @outpref string = "/output/Searchlog-aggregation";
DECLARE @out1    string = @outpref+"_agg.csv";
DECLARE @out2    string = @outpref+"_top5agg.csv";
@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT
        Region,
        SUM(Duration) AS TotalDuration
    FROM @searchlog
GROUP BY Region;
@res =
    SELECT *
    FROM @rs1
    ORDER BY TotalDuration DESC
    FETCH 5 ROWS;
OUTPUT @rs1
    TO @out1
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();
OUTPUT @res
    TO @out2
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

De U-SQL ORDER BY-component vereist het gebruik van de FETCH-component in een SELECT-expressie.

De U-SQL HAVING-component kan worden gebruikt om de uitvoer te beperken tot groepen die voldoen aan de HAVING-voorwaarde:

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@res =
    SELECT
        Region,
        SUM(Duration) AS TotalDuration
    FROM @searchlog
    GROUP BY Region
    HAVING SUM(Duration) > 200;
OUTPUT @res
    TO "/output/Searchlog-having.csv"
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

Zie de U-SQL-referentiedocumentatie voor statistische, analytische en referentiefuncties voor geavanceerde aggregatiescenario's.

Volgende stappen