Omówienie programowania języka U-SQL
Ważne
Usługa Azure Data Lake Analytics wycofana 29 lutego 2024 r. Dowiedz się więcej z tego ogłoszenia.
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 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 Data/godzina.
@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 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 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.
Poniżej przedstawiono sposób, w jaki 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.