Omówienie podręcznika programowania U-SQL

Ważne

Usługa Azure Data Lake Analytics została wycofana 29 lutego 2024 r. Dowiedz się więcej z tym ogłoszeniem.

Na potrzeby analizy danych organizacja może używać usługi Azure Synapse Analytics lub Microsoft Fabric.

U-SQL to język zapytań przeznaczony dla typów obciążeń big data. Jedną z unikatowych cech języka U-SQL jest połączenie języka deklaratywnego przypominającego sql z rozszerzalnością i możliwościami programowymi udostępnianymi przez język C#. W tym przewodniku skoncentrujemy się na rozszerzalności i programowalności języka U-SQL, który jest włączony przez język C#.

Wymagania

Pobierz i zainstaluj narzędzia Azure Data Lake Tools for Visual Studio.

Wprowadzenie do języka U-SQL

Zapoznaj się z następującym skryptem U-SQL:

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

Ten skrypt definiuje dwa zestawy wierszy: @a i @results. RowSet @results jest definiowany na podstawie @a.

Typy i wyrażenia języka C# w skrypcie U-SQL

Wyrażenie U-SQL to wyrażenie języka C# połączone z operacjami logicznymi U-SQL, takimi jak AND, ORi NOT. Wyrażenia U-SQL mogą być używane z funkcjami SELECT, EXTRACT, WHERE, HAVING, GROUP BY i DECLARE. Na przykład poniższy skrypt parsuje ciąg jako wartość DateTime.

@results =
  SELECT
    customer,
    amount,
    DateTime.Parse(date) AS date
  FROM @a;

Poniższy fragment kodu przekształca ciąg na wartość DateTime w instrukcji DECLARE.

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

Używanie wyrażeń języka C# na potrzeby konwersji typów danych

W poniższym przykładzie pokazano, jak można wykonać konwersję danych typu data/godzina przy użyciu wyrażeń języka C#. W tym konkretnym scenariuszu dane daty/godziny w formacie ciągu znaków są konwertowane na standardową datę/godzinę z notacją czasu 00:00:00 o północy.

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

Użyj wyrażeń C# dla obecnej daty

Aby ściągnąć bieżącą datę, możemy użyć następującego wyrażenia języka C#: DateTime.Now.ToString("M/d/yyyy")

Oto przykład użycia tego wyrażenia w skrycie:

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

Korzystanie z bibliotek .NET

Model rozszerzalności języka U-SQL opiera się w dużym stopniu na możliwości dodawania niestandardowego kodu z zestawów platformy .NET.

Zarejestruj zestaw .NET

Użyj instrukcji , CREATE ASSEMBLY aby umieścić zestaw .NET w bazie danych U-SQL Database. Następnie skrypty U-SQL mogą używać tych zestawów przy użyciu instrukcji REFERENCE ASSEMBLY .

Poniższy kod pokazuje, jak zarejestrować zestaw:

CREATE ASSEMBLY MyDB.[MyAssembly]
   FROM "/myassembly.dll";

Poniższy kod przedstawia sposób odwołowania się do zestawu:

REFERENCE ASSEMBLY MyDB.[MyAssembly];

Zapoznaj się z instrukcjami dotyczącymi rejestracji zestawów , które szczegółowo omawiają ten temat.

Używanie wersjonowania zestawu

Obecnie język U-SQL używa platformy .NET Framework w wersji 4.7.2. Dlatego upewnij się, że własne zestawy są zgodne z tą wersją środowiska uruchomieniowego.

Jak wspomniano wcześniej, język U-SQL uruchamia kod w formacie 64-bitowym (x64). Upewnij się więc, że kod został skompilowany do uruchomienia w środowisku x64. W przeciwnym razie zostanie wyświetlony nieprawidłowy błąd formatu pokazany wcześniej.

Każda przesłana biblioteka DLL zestawu i plik zasobów, takie jak inny czas wykonania, zestaw natywny lub plik konfiguracji, może mieć maksymalny rozmiar 400 MB. Całkowity rozmiar wdrożonych zasobów, zarówno poprzez DEPLOY RESOURCE, jak i odwołania do zestawów i ich innych plików, nie może przekraczać 3 GB.

Na koniec każda baza danych U-SQL może zawierać tylko jedną wersję dowolnego zestawu. Jeśli na przykład potrzebujesz zarówno wersji 7, jak i 8 biblioteki NewtonSoft Json.NET, musisz zarejestrować je w dwóch różnych bazach danych. Ponadto każdy skrypt może odwoływać się tylko do jednej wersji określonego zestawu DLL. W tym zakresie U-SQL stosuje się do zarządzania zestawami i wersjonowania w sposób zgodny z semantyką języka C#.

Korzystanie z funkcji zdefiniowanych przez użytkownika: funkcja UDF

Funkcje zdefiniowane przez użytkownika języka U-SQL (UDF) to procedury programistyczne, które akceptują parametry, wykonują akcję (na przykład złożone obliczenia) i zwracają wynik tej akcji jako wartość. Wartość zwracana przez funkcję zdefiniowaną przez użytkownika może być tylko pojedynczym skalarem. Funkcja U-SQL UDF może być wywoływana w skryscie podstawowym U-SQL, podobnie jak każda inna funkcja skalarna języka C#.

Zalecamy zainicjowanie funkcji zdefiniowanych przez użytkownika U-SQL jako publicznych i statycznych.

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

Najpierw przyjrzyjmy się prostemu przykładowi tworzenia funkcji zdefiniowanej przez użytkownika.

W tym scenariuszu przypadków użycia musimy określić okres obrachunkowy, w tym kwartał obrachunkowy i miesiąc obrachunkowy pierwszego logowania dla określonego użytkownika. Pierwszy miesiąc obrachunkowy roku w naszym scenariuszu to czerwiec.

Aby obliczyć okres obrachunkowy, wprowadzamy następującą funkcję języka C#:

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

Po prostu oblicza miesiąc obrachunkowy i kwartał i zwraca ciąg znaków. W czerwcu w pierwszym miesiącu pierwszego kwartału obrachunkowego używamy wartości "Q1:P1". W lipcu użyjemy "Q1:P2", itd.

Jest to zwykła funkcja języka C#, która będzie używana w naszym projekcie U-SQL.

Poniżej przedstawiono sposób, w jaki sekcja code-behind wygląda w tym scenariuszu:

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

Teraz wywołamy tę funkcję z podstawowego skryptu U-SQL. W tym celu musimy podać w pełni kwalifikowaną nazwę funkcji, w tym przestrzeń nazw, która w tym przypadku to NameSpace.Class.Function(parametr).

USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)

Poniżej znajduje się rzeczywisty skrypt podstawowy U-SQL:

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

Poniżej znajduje się plik wyjściowy wykonywania skryptu:

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",""

W tym przykładzie pokazano proste użycie funkcji użytkownika wbudowanej w języku U-SQL.

Zachowaj stan między wywołaniami funkcji zdefiniowanej przez użytkownika

Obiekty programowe języka C# języka U-SQL mogą być bardziej zaawansowane, wykorzystując interakcyjność za pośrednictwem zmiennych globalnych stojących za kodem. Przyjrzyjmy się następującemu biznesowemu scenariuszowi użycia.

W dużych organizacjach użytkownicy mogą przełączać się między różnymi aplikacjami wewnętrznymi. Mogą one obejmować program Microsoft Dynamics CRM, usługę Power BI itd. Klienci mogą chcieć zastosować analizę telemetrii, w jaki sposób użytkownicy przełączają się między różnymi aplikacjami, jakie są trendy użycia itd. Celem firmy jest zoptymalizowanie użycia aplikacji. Mogą również chcieć połączyć różne aplikacje lub określone procedury logowania.

Aby osiągnąć ten cel, musimy określić identyfikatory sesji i czas opóźnienia między ostatnią sesją, która miała miejsce.

Musimy znaleźć poprzednie logowanie, a następnie przypisać to logowanie do wszystkich sesji generowanych w tej samej aplikacji. Pierwszym wyzwaniem jest to, że skrypt podstawowy U-SQL nie pozwala nam stosować obliczeń względem już obliczonych kolumn z funkcją LAG. Drugie wyzwanie polega na tym, że musimy zachować określoną sesję dla wszystkich sesji w tym samym okresie czasu.

Aby rozwiązać ten problem, użyjemy zmiennej globalnej wewnątrz sekcji kodu: static public string globalSession;.

Ta zmienna globalna jest stosowana do całego zestawu wierszy podczas wykonywania skryptu.

Oto sekcja kodu w naszym programie U-SQL:

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;
        }

    }
}

W tym przykładzie przedstawiono zmienną globalną static public string globalSession; używaną wewnątrz getStampUserSession funkcji i ponowne inicjowanie za każdym razem, gdy parametr sesji zostanie zmieniony.

Skrypt podstawowy U-SQL jest następujący:

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

Funkcja USQLApplication21.UserSession.getStampUserSession(UserSessionTimestamp) jest wywoływana tutaj podczas obliczania drugiego zestawu wierszy pamięci. Przechodzi przez kolumnę UserSessionTimestamp i zwraca wartość, aż UserSessionTimestamp się zmieni.

Plik wyjściowy jest następujący:

"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"

W tym przykładzie pokazano bardziej skomplikowany scenariusz przypadków użycia, w którym używamy zmiennej globalnej wewnątrz sekcji kodu stosowanej do całego zestawu wierszy pamięci.

Dalsze kroki

• Przewodnik programowy języka U-SQL — UDT i UDAGG
• Przewodnik po programowalności języka U-SQL — UDO