Gambaran umum panduan kemampuan pemrograman U-SQL

U-SQL adalah bahasa kueri yang dirancang untuk jenis beban kerja data besar. 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 rakitan dan file tambahannya, tidak boleh melebihi 3 GB.

Terakhir, perhatikan bahwa setiap database U-SQL hanya dapat berisi satu versi perakitan yang diberikan. 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 digunakan dalam proyek U-SQL kami.

Berikut adalah tampilan bagian belakang kode 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 dasar Skrip U-SQL. 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 di belakang kode 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.

Langkah berikutnya