Freigeben über

Erste Schritte mit U-SQL in Azure Data Lake Analytics

  • Artikel

Wichtig

Azure Data Lake Analytics am 29. Februar 2024 eingestellt. In dieser Ankündigung erhalten Sie weitere Informationen.

Für Datenanalysen kann Ihr organization Azure Synapse Analytics oder Microsoft Fabric verwenden.

U-SQL ist eine Sprache, bei der deklarative SQL mit imperativen C#-Elementen kombiniert wird, damit Sie Daten jedweden Umfangs verarbeiten können. Mit der skalierbaren Funktion für verteilte Abfragen von U-SQL können Sie Daten über relationale Speicher, z.B. Azure SQL-Datenbank, hinweg effizient analysieren. Mit U-SQL können Sie unstrukturierte Daten verarbeiten, indem Sie ein Schema zum Lesen anwenden und benutzerdefinierte Logik und UDFs einfügen. Darüber hinaus bietet U-SQL eine Erweiterbarkeit, die Ihnen eine präzisere Steuerung der Ausführung in großem Umfang ermöglicht.

Lernressourcen

Erstellen wir einige Daten.

Das folgende U-SQL-Skript ist einfach und ermöglicht es uns, eine Beispieldatendatei zu erstellen, auf die wir in unseren anderen U-SQL-Skripts verweisen können. Im nächsten Abschnitt werden die wichtigsten Konzepte für die Ausführung des Skripts erläutert.

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();

Lesen von Daten aus einer Datei

Nachdem wir nun über einige Daten verfügen, lesen wir sie aus der erstellten Datei.

@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();

Dieses Skript weist keine Transformationsschritte auf. Es liest Daten aus der Quelldatei SearchLog.tsv und schematisiert sie. Anschließend schreibt es das Rowset wieder in eine Datei namens „SearchLog-first-u-sql.csv“.

Beachten Sie im Feld Duration das Fragezeichen neben dem Datentyp. Dies bedeutet, dass das Feld Duration NULL sein kann.

Wichtige Begriffe

  • Rowsetvariablen: Jeder Abfrageausdruck, der ein Rowset produziert, kann einer Variablen zugewiesen werden. U-SQL basiert im Skript auf dem Benennungsmuster für T-SQL-Variablen (z.B. @searchlog).
  • Mit dem Schlüsselwort EXTRACT werden Daten aus einer Datei gelesen, und das Schema wird beim Lesen definiert. Extractors.Tsv ist ein integrierter U-SQL-Extractor für Dateien mit durch Tabstopp getrennten Werten. Sie können benutzerdefinierte Extractors entwickeln.
  • Mit OUTPUT werden Daten aus einem Rowset in eine Datei geschrieben. Outputters.Csv() ist ein integrierter U-SQL-Outputter zum Erstellen einer Datei mit kommagetrennten Werten. Sie haben die Möglichkeit, benutzerdefinierte Outputters zu entwickeln.

Dateipfade

Für die Anweisungen EXTRACT und OUTPUT werden Dateipfade verwendet. Dateipfade können absolut oder relativ sein:

Der folgende absolute Dateipfad verweist auf eine Datei in einer Data Lake Store-Instanz mit dem Namen mystore:

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

Der folgende relative Dateipfad beginnt mit "/". Er verweist auf eine Datei im Data Lake Store-Standardkonto:

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

Verwenden von Skalarvariablen

Sie können auch Skalarvariablen verwenden, um die Skriptverwaltung zu vereinfachen. Das vorherige U-SQL-Skript kann auch wie folgt geschrieben werden:

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();

Transformieren von Rowsets

Verwenden Sie SELECT, um Rowsets zu transformieren. Dieses Skript wählt die Spalten „Start“, „Region“ und „Duration“ aus und schreibt diese Zeilen in eine Datei, wenn für „Region“ der Wert „en-gb“ angegeben ist:

@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();

Die WHERE-Klausel verwendet einen booleschen C#-Ausdruck. Sie können die C#-Ausdruckssprache nutzen, um Ihre eigenen Ausdrücke und Funktionen zu erstellen. Sie können sogar komplexere Filtervorgänge durchführen, indem Sie die Ausdrücke und Funktionen mit logischen Konjunktionen (AND) und Disjunktionen (OR) kombinieren.

Im folgenden Skript werden die DateTime.Parse()-Methode und eine Konjunktion verwendet. Außerdem werden die Spalten „Start“, „Region“ und „Duration“ und für „Region“ der Wert „en-gb“ verwendet. Anschließend sucht es nach Werten der Spalte „Duration“ zwischen bestimmten Datumsangaben und schreibt diese Werte in eine Datei:

@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();

Hinweis

Die zweite Abfrage verwendet das Ergebnis des ersten Rowsets, wodurch ein Verbund der beiden Filter entsteht. Sie können auch einen Variablennamen wiederverwenden, und die Namen werden lexikalisch begrenzt.

Aggregieren von Rowsets

Bei U-SQL können Sie die vertrauten Elemente ORDER BY und GROUP BY sowie Aggregationen verwenden.

Die folgende Abfrage sucht nach der Gesamtdauer pro Region und zeigt dann die ersten fünf Zeiträume in der Reihenfolge ihrer Länge an.

In U-SQL-Rowsets wird die Reihenfolge für die nächste Abfrage nicht beibehalten. Zum Sortieren einer Ausgabe müssen Sie der OUTPUT-Anweisung also das ORDER BY-Element hinzufügen:

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();

Für die ORDER BY-Klausel von U-SQL ist die Verwendung der FETCH-Klausel in einem SELECT-Ausdruck erforderlich.

Die U-SQL-Klausel HAVING kann verwendet werden, um die Ausgabe auf Gruppen zu beschränken, die die HAVING-Bedingung erfüllen:

@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();

Erweiterte Aggregationsszenarien finden Sie in der U-SQL-Referenzdokumentation für Aggregat-, Analyse- und Referenzfunktionen.

Nächste Schritte