Gambaran umum panduan kemampuan pemrograman U-SQL
Penting
Azure Data Lake Analytics pensiun pada 29 Februari 2024. Pelajari lebih lanjut dengan pengumuman ini.
Untuk analitik data, organisasi Anda dapat menggunakan Azure Synapse Analytics atau Microsoft Fabric.
U-SQL adalah bahasa kueri yang dirancang untuk jenis beban kerja big data. Salah satu fitur unik dari U-SQL adalah kombinasi bahasa deklaratif seperti SQL dengan ekstensibilitas dan kemampuan pemrograman yang disediakan oleh C#. Dalam panduan ini, kami berkonsentrasi pada ekstensibilitas dan kemampuan pemrograman bahasa U-SQL yang diaktifkan oleh C#.
Persyaratan
Unduh dan instal Alat Azure Data Lake untuk Visual Studio.
Memulai dengan U-SQL
Lihatlah skrip U-SQL berikut ini:
@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;
Skrip ini mendefinisikan dua kumpulan baris: @a dan @results. Kumpulan baris @results didefinisikan dari @a.
Jenis dan ekspresi C# dalam skrip U-SQL
Ekspresi U-SQL adalah ekspresi C# yang dikombinasikan dengan operasi logika U-SQL seperti AND, OR dan NOT. Ekspresi U-SQL dapat digunakan dengan SELECT, EXTRACT, WHERE, HAVING, GROUP BY dan DECLARE. Misalnya, skrip berikut menguraikan string sebagai nilai DateTime.
@results =
SELECT
customer,
amount,
DateTime.Parse(date) AS date
FROM @a;
Cuplikan berikut menguraikan string sebagai nilai DateTime dalam pernyataan DECLARE.
DECLARE @d = DateTime.Parse("2016/01/01");
Menggunakan ekspresi C# untuk konversi jenis data
Contoh berikut menunjukkan bagaimana Anda dapat melakukan konversi data tanggalwaktu dengan menggunakan ekspresi C#. Dalam skenario khusus ini, data tanggalwaktu string dikonversi ke tanggalwaktu standar dengan notasi waktu tengah malam 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();
Menggunakan ekspresi C# untuk tanggal hari ini
Untuk menarik tanggal hari ini, kita dapat menggunakan ekspresi C# berikut: DateTime.Now.ToString("M/d/yyyy")
Berikut ini contoh cara menggunakan ekspresi ini dalam skrip:
@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;
Menggunakan rakitan .NET
Model ekstensibilitas U-SQL sangat bergantung pada kemampuan untuk menambahkan kode khusus dari rakitan .NET.
Mendaftarkan rakitan .NET
Gunakan pernyataan CREATE ASSEMBLY untuk menempatkan perakitan .NET ke dalam Database U-SQL. Setelah itu, skrip U-SQL dapat menggunakan rakitan tersebut dengan menggunakan pernyataan REFERENCE ASSEMBLY tersebut.
Kode berikut menunjukkan cara mendaftarkan rakitan:
CREATE ASSEMBLY MyDB.[MyAssembly]
FROM "/myassembly.dll";
Kode berikut menunjukkan cara mereferensikan rakitan:
REFERENCE ASSEMBLY MyDB.[MyAssembly];
Lihat petunjuk pendaftaran perakitan yang membahas topik ini secara lebih rinci.
Gunakan penerapan versi rakitan
Saat ini, U-SQL menggunakan .NET Framework versi 4.7.2. Jadi pastikan bahwa rakitan Anda sendiri sesuai dengan versi runtime tersebut.
Seperti disebutkan sebelumnya, U-SQL menjalankan kode dalam format 64-bit (x64). Jadi pastikan kode Anda dikompilasi untuk berjalan pada x64. Jika tidak, Anda akan mendapatkan kesalahan format yang diperlihatkan sebelumnya.
Setiap file DLL dan sumber daya rakitan yang diunggah, seperti waktu proses yang berbeda, rakitan asli, atau file konfigurasi, dapat berukuran maksimal 400 MB. Ukuran total sumber daya yang disebarkan, baik melalui DEPLOY RESOURCE atau melalui referensi ke assembly dan file lainnya, tidak boleh melebihi 3 GB.
Akhirnya, setiap database U-SQL hanya dapat berisi satu versi rakitan tertentu. Misalnya, jika Anda memerlukan versi 7 dan versi 8 dari pustaka NewtonSoft Json.NET, Anda perlu mendaftarkannya dalam dua database yang berbeda. Selain itu, setiap skrip hanya dapat merujuk ke satu versi DLL rakitan yang diberikan. Dalam hal ini, U-SQL mengikuti manajemen perakitan C# dan semantik versi.
Fungsi yang ditentukan pengguna: UDF
Fungsi U-SQL yang ditentukan pengguna, atau UDF, adalah rutinitas pemrograman yang menerima parameter, melakukan tindakan (seperti perhitungan kompleks), dan mengembalikan hasil tindakan tersebut sebagai nilai. Nilai pengembalian UDF hanya bisa menjadi skalar tunggal. U-SQL UDF dapat dipanggil dalam skrip dasar U-SQL seperti fungsi skalar C# lainnya.
Sebaiknya inisialisasi fungsi U-SQL yang ditentukan pengguna sebagai publik dan statis.
public static string MyFunction(string param1)
{
return "my result";
}
Mari kita lihat terlebih dahulu contoh sederhana pembuatan UDF.
Dalam skenario penggunaan ini, kita perlu menentukan periode fiskal, termasuk kuartal fiskal dan bulan fiskal dari masuk pertama untuk pengguna tertentu. Bulan fiskal pertama tahun ini dalam skenario kami adalah Juni.
Untuk menghitung periode fiskal, kami memperkenalkan fungsi C# berikut:
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();
}
Fungsi ini hanya menghitung bulan dan kuartal fiskal dan mengembalikan nilai string. Untuk bulan Juni, bulan pertama kuartal fiskal pertama, kami menggunakan "Q1:P1". Untuk bulan Juli, kami menggunakan "Q1:P2", dan seterusnya.
Ini adalah fungsi C# reguler yang akan kita gunakan dalam proyek U-SQL kita.
Berikut adalah tampilan bagian code-behind dalam skenario ini:
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();
}
}
}
Sekarang kita akan memanggil fungsi ini dari skrip U-SQL dasar. Untuk melakukannya, kita harus memberikan nama yang sepenuhnya memenuhi syarat untuk fungsi, termasuk namespace, yang dalam hal ini adalah NameSpace.Class.Function(parameter).
USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)
Berikut ini adalah skrip dasar U-SQL yang sebenarnya:
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();
Berikut ini adalah file output dari eksekusi skrip:
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",""
Contoh ini menunjukkan penggunaan sederhana UDF dalam baris di U-SQL.
Pertahankan status di antara pemanggilan UDF
Objek kemampuan pemrograman U-SQL C# bisa lebih canggih, dengan memanfaatkan interaktivitas melalui variabel global di belakang kode. Mari kita lihat skenario penggunaan bisnis berikut ini.
Di organisasi besar, pengguna dapat beralih antar varietas aplikasi internal. Ini bisa mencakup Microsoft Dynamics CRM, Power BI, dan sebagainya. Pelanggan mungkin ingin menerapkan analisis telemetri terkait bagaimana pengguna beralih di antara aplikasi yang berbeda, apa tren penggunaannya, dan sebagainya. Tujuan untuk bisnis ini adalah untuk mengoptimalkan penggunaan aplikasi. Pelanggan juga mungkin ingin menggabungkan aplikasi yang berbeda atau rutinitas masuk tertentu.
Untuk mencapai tujuan ini, kita harus menentukan ID sesi dan waktu jeda antara sesi terakhir yang terjadi.
Kita perlu menemukan rincian masuk sebelumnya dan kemudian menetapkan rincian masuk ini ke semua sesi yang sedang dihasilkan ke aplikasi yang sama. Tantangan pertama adalah bahwa skrip dasar U-SQL tidak memungkinkan kita untuk menerapkan perhitungan pada kolom yang sudah dihitung dengan fungsi LAG. Tantangan kedua adalah bahwa kita harus menjaga sesi khusus untuk semua sesi dalam periode waktu yang sama.
Untuk mengatasi masalah ini, kita menggunakan variabel global di dalam bagian di belakang kode: static public string globalSession;.
Variabel global ini diterapkan ke seluruh kumpulan baris selama eksekusi skrip kita.
Berikut adalah bagian kode belakang dari program U-SQL kami:
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;
}
}
}
Contoh ini menunjukkan variabel global static public string globalSession; yang digunakan di dalam fungsi getStampUserSession dan diinisialisasi ulang setiap kali parameter Sesi diubah.
Skrip dasar U-SQL adalah sebagai berikut:
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();
Fungsi USQLApplication21.UserSession.getStampUserSession(UserSessionTimestamp) dipanggil di sini selama perhitungan kumpulan baris memori kedua. Fungsi ini meneruskan kolom UserSessionTimestamp dan mengembalikan nilai hingga UserSessionTimestamp telah berubah.
File output yang dihasilkan sebagai berikut:
"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"
Contoh ini menunjukkan skenario kasus penggunaan yang lebih rumit yang menggunakan variabel global di dalam bagian di belakang kode yang diterapkan ke seluruh kumpulan baris memori.