開始在 Azure Data Lake Analytics 中使用 U-SQL

  • 發行項

重要

Azure Data Lake Analytics 於 2024 年 2 月 29 日淘汰。 透過此公告深入瞭解。

針對數據分析，您的組織可以使用 Azure Synapse Analytics 或 Microsoft Fabric。

U-SQL 是一種語言，結合了宣告式 SQL 與命令式 C#，可讓您處理任何規模的資料。 透過 U-SQL 的可調整分散式查詢功能，您可以有效率地分析各關聯式存放區 (Azure SQL Database) 中的資料。 使用 U-SQL，您可以藉由在讀取時套用結構描述並插入自訂邏輯和 UDF，來處理非結構化資料。 此外，U-SQL 所含有的擴充性可讓您細微控制如何大規模執行。

學習資源

請建立一些資料

下列 U-SQL 指令碼十分簡單，請建立可在其他 U-SQL 指令碼中參考的範例資料檔案。 我們將在下一節討論此指令碼作用的重要概念。

USE DATABASE master;
USE SCHEMA dbo;
@a  = 
    SELECT * FROM 
        (VALUES
            (399266, "2/15/2012 11:53:16 AM", "en-us", "microsoft", 73, "microsoft.com;xbox.com", "NULL"),
			(382045, "2/15/2012 11:53:18 AM", "en-gb", "azure data lake analytics", 614, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(382045, "2/16/2012 11:53:20 AM", "en-gb", "usql", 74, "microsoft.com;github.com", "NULL"),
			(106479, "2/16/2012 11:53:50 AM", "en-ca", "xbox", 24, "xbox.com;xbox.com/xbox360", "xbox.com/xbox360"),
			(906441, "2/16/2012 11:54:01 AM", "en-us", "machine learning", 1213, "microsoft.com;github.com", "NULL"),
			(304305, "2/16/2012 11:54:03 AM", "en-us", "outlook", 60, "microsoft.com;office.com;live.com","microsoft.com"),
			(460748, "2/16/2012 11:54:04 AM", "en-us", "azure storage", 1270, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(354841, "2/16/2012 11:59:01 AM", "en-us", "azure", 610, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(354068, "2/16/2012 12:00:33 PM", "en-mx", "key vault", 422, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(347413, "2/16/2012 12:11:55 PM", "en-gr", "github", 305, "github.com", "NULL"),
			(840614, "2/16/2012 12:13:56 PM", "en-us", "surface", 1220, "microsoft.com", "NULL"),
			(656666, "2/16/2012 12:15:55 PM", "en-us", "visual studio", 691, "microsoft.com;code.visualstudio.com", "NULL"),
			(951513, "2/16/2012 12:17:00 PM", "en-us", "power bi", 63, "microsoft.com;app.powerbi.com", "powerbi.com"),
			(350350, "2/16/2012 12:18:17 PM", "en-us", "data factory", 30, "microsoft.com;portal.azure.com", "NULL"),
			(641615, "2/16/2012 12:19:55 PM", "en-us", "event hubs", 119, "microsoft.com;portal.azure.com", "NULL")
        ) AS 
              D( UserId, Start, Region, Query, Duration, Urls, ClickedUrls );
OUTPUT @a
    TO "Samples/Data/SearchLog.tsv"
    USING Outputters.Tsv();

從檔案讀取資料

既然我們有一些資料，則請從我們建立的檔案中進行讀取。

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();

OUTPUT @searchlog
    TO "/output/SearchLog-first-u-sql.csv"
    USING Outputters.Csv();

此指令碼沒有任何轉換步驟。 它會從名為 SearchLog.tsv 的原始程式檔讀取資料，為其建立結構描述，並將資料列集回寫到名為 SearchLog-first-u-sql.csv 檔案。

請注意 Duration 欄位中資料類型旁的問號， 它表示 Duration 欄位可能是 null。

重要概念

  • 資料列集變數：每個會產生資料列集的查詢運算式都可以指派給變數。 在指令碼中，U-SQL 會遵循 T-SQL 變數命名模式 (例如 @searchlog)。
  • EXTRACT 關鍵字會從檔案讀取資料，並在讀取時定義結構描述。 Extractors.Tsv 是內建的 U-SQL 擷取器，適用於以定位點分隔值的檔案。 您可以開發自訂擷取器。
  • OUTPUT 會將資料列集的資料寫入檔案。 Outputters.Csv() 是內建的 U-SQL 輸出器，用於建立以逗號分隔值的檔案。 您可以開發自訂輸出器。

檔案路徑

EXTRACT 與 OUTPUT 陳述式使用檔案路徑。 檔案路徑可以是絕對或相對路徑：

下面這個絕對檔案路徑會參考名為 mystore 的 Data Lake Store 中的檔案：

adl://mystore.azuredatalakestore.net/Samples/Data/SearchLog.tsv

下面這個檔案路徑的開頭為 "/"。 它會參考預設 Data Lake Store 帳戶中的檔案：

/output/SearchLog-first-u-sql.csv

使用純量變數

您也可以使用純量變數以方便維護指令碼。 前述 U-SQL 指令碼也可以撰寫成：

DECLARE @in  string = "/Samples/Data/SearchLog.tsv";
DECLARE @out string = "/output/SearchLog-scalar-variables.csv";
@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM @in
    USING Extractors.Tsv();
OUTPUT @searchlog
    TO @out
    USING Outputters.Csv();

轉換資料列集

使用 SELECT 來轉換資料列集。 此指令碼將會選取 [開始]、[區域] 和 [持續時間] 資料行，並在 [區域] 的值為 "en-gb" 時，將這些資料列寫入至檔案：

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT Start, Region, Duration
    FROM @searchlog
WHERE Region == "en-gb";
OUTPUT @rs1
    TO "/output/SearchLog-transform-rowsets.csv"
    USING Outputters.Csv();

WHERE 子句使用 C# 布林運算式。 您可以使用 C# 運算式語言來建置自己的運算式和函式。 您甚至可以將它們與邏輯結合 (AND) 和邏輯分離 (OR) 做結合，以執行更複雜的篩選。

下列指令碼使用 DateTime.Parse() 方法和邏輯結合。 [開始]、[區域] 和 [持續時間] 資料行，其中 [區域] 的值為 "en-gb"。 然後，其會檢查特定日期之間的 [持續時間] 資料行值，並將這些值寫入至檔案：

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT Start, Region, Duration
    FROM @searchlog
WHERE Region == "en-gb";
@rs1 =
    SELECT Start, Region, Duration
    FROM @rs1
    WHERE Start >= DateTime.Parse("2012/02/16") AND Start <= DateTime.Parse("2012/02/17");
OUTPUT @rs1
    TO "/output/SearchLog-transform-datetime.csv"
    USING Outputters.Csv();

注意

第二個查詢會對第一個資料列集的結果起作用，因而建立兩個篩選條件的組合。 您也可以重複使用變數名稱，因為它們是語彙範圍型名稱。

彙總資料列集

U-SQL 提供您已熟悉使用的 ORDER BY、GROUP BY 和各種彙總語法。

下列查詢會尋找每個區域的總持續時間，然後按順序顯示前五大持續時間。

U-SQL 資料列集不會保留其順序以供下一次查詢使用。 因此，若要對輸出排序，您需要將 ORDER BY 新增至 OUTPUT 陳述式：

DECLARE @outpref string = "/output/Searchlog-aggregation";
DECLARE @out1    string = @outpref+"_agg.csv";
DECLARE @out2    string = @outpref+"_top5agg.csv";
@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT
        Region,
        SUM(Duration) AS TotalDuration
    FROM @searchlog
GROUP BY Region;
@res =
    SELECT *
    FROM @rs1
    ORDER BY TotalDuration DESC
    FETCH 5 ROWS;
OUTPUT @rs1
    TO @out1
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();
OUTPUT @res
    TO @out2
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

U-SQL ORDER BY 子句必須在 SELECT 運算式中使用 FETCH 子句。

U-SQL 的 HAVING 子句可以用來將輸出限制為符合 HAVING 條件的群組：

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@res =
    SELECT
        Region,
        SUM(Duration) AS TotalDuration
    FROM @searchlog
    GROUP BY Region
    HAVING SUM(Duration) > 200;
OUTPUT @res
    TO "/output/Searchlog-having.csv"
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

如需進階彙總案例，請參閱彙總、分析和參考函數的 U-SQL 參考文件。

下一步