Přehled průvodce programovatelností U-SQL
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 dotazovací jazyk určený pro úlohy s velkými objemy dat. Jednou z jedinečných vlastností U-SQL je kombinace deklarativního jazyka podobného SQL s rozšiřitelností a programovatelností, kterou poskytuje jazyk C#. V této příručce se zaměříme na rozšiřitelnost a programovatelnost jazyka U-SQL, který umožňuje jazyk C#.
Požadavky
Stáhněte a nainstalujte Nástroje Azure Data Lake pro Visual Studio.
Začínáme s U-SQL
Podívejte se na následující skript 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;
Tento skript definuje dvě sady řádků: @a a @results. RowSet @results se definuje z @a.
Typy a výrazy jazyka C# ve skriptu U-SQL
Výraz U-SQL je výraz jazyka C# kombinovaný s logickými operacemi U-SQL, jako jsou AND, ORa NOT. Výrazy U-SQL se dají použít s příkazy SELECT, EXTRACT, WHERE, HAVING, GROUP BY a DECLARE. Například následující skript analyzuje řetězec jako hodnotu DateTime.
@results =
SELECT
customer,
amount,
DateTime.Parse(date) AS date
FROM @a;
Následující fragment kódu analyzuje řetězec jako hodnotu DateTime v příkazu DECLARE.
DECLARE @d = DateTime.Parse("2016/01/01");
Použití výrazů jazyka C# pro převody datových typů
Následující příklad ukazuje, jak můžete provést převod dat datetime pomocí výrazů jazyka C#. V tomto konkrétním scénáři se řetězcová data datetime převedou na standardní datum a čas s notací půlnoci 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();
Použití výrazů jazyka C# pro dnešní datum
K načtení dnešního data můžeme použít následující výraz jazyka C#: DateTime.Now.ToString("M/d/yyyy")
Tady je příklad použití tohoto výrazu ve skriptu:
@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;
Použití sestavení .NET
Model rozšiřitelnosti U-SQL do značné míry spoléhá na schopnost přidávat vlastní kód ze sestavení .NET.
Registrace sestavení .NET
Pomocí příkazu CREATE ASSEMBLY umístěte sestavení .NET do SQL Database U. Poté mohou skripty U-SQL používat tato sestavení pomocí příkazu .REFERENCE ASSEMBLY
Následující kód ukazuje, jak zaregistrovat sestavení:
CREATE ASSEMBLY MyDB.[MyAssembly]
FROM "/myassembly.dll";
Následující kód ukazuje, jak odkazovat na sestavení:
REFERENCE ASSEMBLY MyDB.[MyAssembly];
Podrobnější informace najdete v pokynech k registraci sestavení , které se týkají tohoto tématu.
Použití správy verzí sestavení
V současné době U-SQL používá rozhraní .NET Framework verze 4.7.2. Proto se ujistěte, že vaše vlastní sestavení jsou kompatibilní s danou verzí modulu runtime.
Jak už bylo zmíněno dříve, U-SQL spouští kód v 64bitovém (x64) formátu. Proto se ujistěte, že je váš kód zkompilovaný tak, aby běžel na platformě x64. V opačném případě se zobrazí chyba nesprávného formátu zobrazená dříve.
Každá odeslaná knihovna DLL sestavení a soubor prostředků, například jiný modul runtime, nativní sestavení nebo konfigurační soubor, může mít velikost maximálně 400 MB. Celková velikost nasazených prostředků prostřednictvím příkazu DEPLOY RESOURCE nebo prostřednictvím odkazů na sestavení a jejich další soubory nesmí překročit 3 GB.
A konečně každá databáze U-SQL může obsahovat pouze jednu verzi libovolného daného sestavení. Pokud například potřebujete verzi 7 i verzi 8 knihovny Json.NET NewtonSoft, musíte je zaregistrovat ve dvou různých databázích. Kromě toho může každý skript odkazovat pouze na jednu verzi dané knihovny DLL sestavení. V tomto ohledu se U-SQL řídí správa montáže jazyka C# a sémantikou správy verzí.
Použití uživatelem definovaných funkcí: UDF
Uživatelem definované funkce U-SQL neboli UDF jsou programovací rutiny, které přijímají parametry, provádějí akci (například složitý výpočet) a vracejí výsledek této akce jako hodnotu. Návratovou hodnotou funkce UDF může být pouze jeden skalár. UDF U-SQL je možné volat v základním skriptu U-SQL stejně jako jakoukoli jinou skalární funkci jazyka C#.
Doporučujeme inicializovat uživatelem definované funkce U-SQL jako veřejné a statické.
public static string MyFunction(string param1)
{
return "my result";
}
Nejprve se podíváme na jednoduchý příklad vytvoření funkce definované uživatelem.
V tomto scénáři použití musíme určit fiskální období, včetně fiskálního čtvrtletí a fiskálního měsíce prvního přihlášení pro konkrétního uživatele. Prvním fiskálním měsícem roku je v našem scénáři červen.
Pro výpočet fiskálního období zavádíme následující funkci jazyka 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();
}
Jednoduše vypočítá fiskální měsíc a čtvrtletí a vrátí řetězcovou hodnotu. Pro červen, první měsíc prvního fiskálního čtvrtletí, používáme Q1:P1. Pro červenec používáme Q1:P2 atd.
Toto je běžná funkce jazyka C#, kterou budeme používat v našem projektu U-SQL.
Oddíl kódu na pozadí v tomto scénáři vypadá takto:
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();
}
}
}
Teď zavoláme tuto funkci ze základního skriptu U-SQL. K tomu musíme zadat plně kvalifikovaný název funkce, včetně oboru názvů, což je v tomto případě NameSpace.Class.Function(parameter).
USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)
Následuje skutečný základní skript 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();
Následuje výstupní soubor spuštění skriptu:
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",""
Tento příklad ukazuje jednoduché použití vložené funkce definované uživatelem v U-SQL.
Zachování stavu mezi voláními UDF
Objekty programovatelnosti U-SQL C# mohou být sofistikovanější a využívají interaktivitu prostřednictvím kódu na pozadí globálních proměnných. Podívejme se na následující scénář obchodního použití.
Ve velkých organizacích můžou uživatelé přepínat mezi různými interními aplikacemi. Může se jednat o Microsoft Dynamics CRM, Power BI atd. Zákazníci můžou chtít použít telemetrii analýzy toho, jak uživatelé přepínají mezi různými aplikacemi, jaké jsou trendy využití atd. Cílem firmy je optimalizovat využití aplikací. Může také chtít kombinovat různé aplikace nebo konkrétní rutiny přihlašování.
Abychom tohoto cíle dosáhli, musíme určit ID relací a prodlevu mezi poslední relací, ke které došlo.
Musíme najít předchozí přihlášení a pak toto přihlášení přiřadit ke všem relacím, které se generují do stejné aplikace. Prvním problémem je, že základní skript U-SQL neumožňuje použít výpočty u již počítaných sloupců pomocí funkce LAG. Druhým problémem je, že musíme zachovat konkrétní relaci pro všechny relace ve stejném časovém období.
K vyřešení tohoto problému používáme globální proměnnou uvnitř oddílu kódu na pozadí: static public string globalSession;.
Tato globální proměnná se během provádění skriptu použije na celou sadu řádků.
Tady je část kódu na pozadí našeho programu 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;
}
}
}
Tento příklad ukazuje globální proměnnou static public string globalSession; použitou getStampUserSession uvnitř funkce a opětovnou inicializaci při každé změně parametru relace.
Základní skript U-SQL je následující:
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();
Funkce USQLApplication21.UserSession.getStampUserSession(UserSessionTimestamp) je volána zde během druhého výpočtu sady řádků paměti. Předá UserSessionTimestamp sloupec a vrátí hodnotu, dokud UserSessionTimestamp se nezmění.
Výstupní soubor je následující:
"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"
Tento příklad ukazuje složitější scénář použití, ve kterém používáme globální proměnnou uvnitř oddílu kódu na pozadí, který se použije na celou sadu řádků paměti.