Omówienie programowania języka U-SQL

U-SQL to język zapytań przeznaczony do obsługi obciążeń big data. Jedną z unikatowych funkcji języka U-SQL jest kombinacja języka deklaratywnego przypominającego sql z rozszerzalnością i możliwością programowania dostarczaną 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 Azure Data Lake Tools for Visual Studio.

Wprowadzenie do języka U-SQL

Spójrz na następujący skrypt 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. @results Zestaw wierszy jest definiowany na podstawie elementu @a.

Typy i wyrażenia języka C# w skry skryptach 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 instrukcjami SELECT, EXTRACT, WHERE, HAVING, GROUP BY i DECLARE. Na przykład poniższy skrypt analizuje ciąg jako wartość typu DateTime.

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

Poniższy fragment kodu analizuje ciąg jako 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 ciągu są konwertowane na standardową datę/godzinę z notacją czasu o północy 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();

Używanie wyrażeń języka C# dla dzisiejszej 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 skry 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 zestawów platformy .NET

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

Rejestrowanie zestawu .NET

Użyj instrukcji CREATE ASSEMBLY , aby umieścić zestaw .NET w SQL Database U. Następnie skrypty języka 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 pokazuje, jak odwoływać się do zestawu:

REFERENCE ASSEMBLY MyDB.[MyAssembly];

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

Korzystanie z przechowywania wersji zestawu

Obecnie język U-SQL używa .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 przekazana biblioteka DLL zestawu i plik zasobów, taki jak inne środowisko uruchomieniowe, zestaw natywny lub plik konfiguracji, może wynosić co najwyżej 400 MB. Całkowity rozmiar wdrożonych zasobów za pośrednictwem metody DEPLOY RESOURCE lub odwołań do zestawów i ich dodatkowych plików nie może przekraczać 3 GB.

Na koniec należy pamiętać, że 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 danej biblioteki DLL zestawu. W tym względzie język U-SQL jest zgodny z semantykami języka C# zarządzanie kompletacją i obsługą wersji.

Korzystanie z funkcji zdefiniowanych przez użytkownika: funkcja zdefiniowana przez użytkownika: funkcja zdefiniowana przez użytkownika

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 obliczenie) i zwracają wynik tej akcji jako wartość. Zwracana wartość funkcji zdefiniowanej przez użytkownika może być tylko jedną skalarną. Funkcja U-SQL UDF może być wywoływana w skryfcie podstawowym U-SQL, podobnie jak w przypadku dowolnej innej funkcji skalarnej języka C#.

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

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

Najpierw przyjrzyjmy się prostego przykładu 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 wartość ciągu. W czerwcu pierwszy miesiąc pierwszego kwartału obrachunkowego używamy wartości "Q1:P1". W lipcu użyjemy wartości "Q1:P2", itd.

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

Oto jak sekcja kodu 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 wbudowanej funkcji zdefiniowanej przez użytkownika 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 pomocą zmiennych globalnych stojących za kodem. Przyjrzyjmy się następującego scenariusza przypadków użycia biznesowego.

W dużych organizacjach użytkownicy mogą przełączać się między różnymi aplikacjami wewnętrznymi. Mogą one obejmować Microsoft Dynamics CRM, usługę Power BI itd. Klienci mogą chcieć zastosować analizę telemetrii dotyczącą sposobu przełączania użytkowników między różnymi aplikacjami, trendów 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. Drugim wyzwaniem jest to, że musimy zachować określoną sesję dla wszystkich sesji w tym samym czasie.

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 pokazano zmienną globalną static public string globalSession; używaną wewnątrz getStampUserSession funkcji i jest ponownie inicjowana 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. Przekazuje kolumnę UserSessionTimestamp i zwraca wartość do momentu UserSessionTimestamp zmiany.

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, która jest stosowana do całego zestawu wierszy pamięci.

Następne kroki