Azure Data Lake Analytics 中的 U-SQL 入门

2023-12-20

重要

Azure Data Lake Analytics 于 2024 年 2 月 29 日停用。了解更多的信息，请参阅此公告。

对于数据分析，组织可以使用 Azure Synapse Analytics 或 Microsoft Fabric。

U-SQL 是一种将声明性 SQL 与命令性 C# 相结合的语言，可用于处理任何规模的数据。通过 U-SQL 的可缩放分布式查询功能，可以跨关系存储（如 Azure SQL 数据库）有效地分析数据。使用 U-SQL，可以通过在读取和插入自定义逻辑和 UDF 上应用架构来处理非结构化数据。此外，U-SQL 包括扩展性，可让你对如何大规模执行进行精细控制。

学习资源

U-SQL 教程提供了大多数 U-SQL 语言的引导式演练。对于想要学习 U-SQL 的所有开发人员，建议阅读本文档。
有关 U-SQL 语言语法的详细信息，请参阅 U-SQL 语言参考。
若要了解 U-SQL 设计理念，请参阅 Visual Studio 博客文章 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 转换行集。此脚本将选择列“开始”、“区域”和“持续时间”，并在 Region 的值为“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”。然后，它会检查特定日期之间的 Duration 列的值，并将这些值写入文件：

@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 参考文档。

通过