Začínáme s U-SQL v Azure Data Lake Analytics

  • Článek

Důležité

Azure Data Lake Analytics vyřazena 29. února 2024. Další informace najdete v tomto oznámení.

K analýze dat může vaše organizace použít Azure Synapse Analytics nebo Microsoft Fabric.

U-SQL je jazyk, který kombinuje deklarativní SQL s imperativním jazykem C# a umožňuje zpracovávat data v libovolném měřítku. Díky škálovatelné funkci distribuovaných dotazů U-SQL můžete efektivně analyzovat data napříč relačními úložišti, jako je Azure SQL Database. S U-SQL můžete zpracovávat nestrukturovaná data použitím schématu při čtení a vkládání vlastní logiky a funkcí UDF. Kromě toho rozhraní U-SQL zahrnuje rozšiřitelnost, která poskytuje podrobnou kontrolu nad prováděním ve velkém měřítku.

Studijní materiály

Pojďme vytvořit nějaká data.

Následující skript U-SQL je jednoduchý a umožňuje nám vytvořit ukázkový datový soubor, na který můžeme odkazovat v ostatních skriptech U-SQL. Klíčové koncepty, které tento skript zpřístupnily, probereme v další části.

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

Čtení dat ze souboru

Teď, když máme nějaká data, načteme je ze souboru, který jsme vytvořili.

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

Tento skript neobsahuje žádné kroky transformace. Čte ze zdrojového souboru s názvem SearchLog.tsv, schematizuje ho a zapisuje sadu řádků zpět do souboru s názvem SearchLog-first-u-sql.csv.

Všimněte si otazníku vedle datového typu v Duration poli. To znamená, že Duration pole může mít hodnotu null.

Klíčové koncepty

  • Proměnné sady řádků: Každý výraz dotazu, který vytváří sadu řádků, lze přiřadit k proměnné. U-SQL se ve skriptu řídí vzorem pojmenování proměnných T-SQL (@searchlognapříklad).
  • Klíčové slovo EXTRACT čte data ze souboru a definuje schéma při čtení. Extractors.Tsv je integrovaný extraktor U-SQL pro soubory s hodnotami oddělenými tabulátory. Můžete vyvíjet vlastní extraktory.
  • Výstup zapisuje data ze sady řádků do souboru. Outputters.Csv() je integrovaný výstupní modul U-SQL pro vytvoření souboru hodnot oddělených čárkami. Můžete vyvíjet vlastní výstupy.

Cesty k souborům

Příkazy EXTRACT a OUTPUT používají cesty k souborům. Cesty k souborům můžou být absolutní nebo relativní:

Následující absolutní cesta k souboru odkazuje na soubor ve službě Data Lake Store s názvem mystore:

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

Následující cesta k souboru začíná na "/". Odkazuje na soubor ve výchozím účtu Data Lake Store:

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

Použití skalárních proměnných

Ke snadnější údržbě skriptů můžete použít i skalární proměnné. Předchozí skript U-SQL je také možné zapsat jako:

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

Transformace sad řádků

Pomocí příkazu SELECT můžete transformovat sady řádků. Tento skript vybere sloupce Start, Region a Duration a zapíše tyto řádky do souboru, pokud má hodnota "en-gb":

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

Klauzule WHERE používá logický výraz jazyka C#. Pomocí jazyka výrazů C# můžete provádět vlastní výrazy a funkce. Dokonce můžete provádět složitější filtrování jejich kombinací s logickými konjunkcemi (AND) a disjunkcemi (ORS).

Následující skript používá metodu DateTime.Parse() a konjunkci. sloupce Start, Region a Duration, kde Region má hodnotu "en-gb". Pak zkontroluje hodnoty sloupce Doba trvání mezi určitými daty a zapíše tyto hodnoty do souboru:

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

Poznámka

Druhý dotaz pracuje s výsledkem první sady řádků, který vytvoří složený ze dvou filtrů. Můžete také znovu použít název proměnné a názvy mají lexikální obor.

Agregace sad řádků

U-SQL nabízí známé funkce ORDER BY, GROUP BY a agregace.

Následující dotaz vyhledá celkovou dobu trvání v jednotlivých oblastech a zobrazí prvních pět dob trvání v pořadí.

Sady řádků U-SQL nezachovávají své pořadí pro další dotaz. Proto k seřazení výstupu musíte do příkazu OUTPUT přidat ORDER BY:

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

Klauzule U-SQL ORDER BY vyžaduje použití klauzule FETCH ve výrazu SELECT.

Klauzuli U-SQL HAVING je možné použít k omezení výstupu na skupiny, které splňují podmínku HAVING:

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

Pokročilé scénáře agregace najdete v referenční dokumentaci k U-SQL pro agregační, analytické a referenční funkce.

Další kroky