在 Azure Synapse Analytics 中使用无服务器 SQL 池的最佳做法

项目
08/14/2024

在本文中，你将探究使用无服务器 SQL 池的一组最佳做法。无服务器 SQL 池是 Azure Synapse Analytics 中的一种资源。如果你使用的是专用 SQL 池，请参阅专用 SQL 池的最佳做法获取具体指导。

使用无服务器 SQL 池，可查询 Azure 存储帐户中的文件。它没有本地存储或引入功能。查询目标的所有文件都位于无服务器 SQL 池的外部。所有与从存储中读取文件相关的操作都可能影响查询性能。

以下是一些一般准则：

请确保客户端应用程序与无服务器 SQL 池并置。
- 如果要在 Azure 外部使用客户端应用程序，请确保在靠近客户端计算机的区域中使用无服务器 SQL 池。客户端应用程序示例包括 Power BI Desktop、SQL Server Management Studio 和 Azure Data Studio。
请确保存储和无服务器 SQL 池位于同一区域。存储示例包括 Azure Data Lake Storage 和 Azure Cosmos DB。
尝试通过使用分区并使文件大小保持在 100 MB 与 10 GB 之间来优化存储布局。
如果要返回大量结果，请确保使用的是 SQL Server Management Studio 或 Azure Data Studio，而不是 Azure Synapse Studio。 Azure Synapse Studio 是一种不适用于大型结果集的 Web 工具。
如果要按字符串列筛选结果，请尝试使用 BIN2_UTF8 排序规则。有关更改排序规则的详细信息，请参阅 Synapse SQL 支持的排序规则类型。
考虑使用 Power BI 导入模式或 Azure Analysis Services 在客户端上缓存结果，并定期刷新。如果要使用复杂的查询或处理大量数据，则无服务器 SQL 池无法提供 Power BI 直接查询模式中的交互式体验。
最大并发数不受限制，具体取决于查询复杂性和扫描的数据量。一个无服务器 SQL 池可以同时处理 1,000 个执行轻型查询的活动会话。如果查询比较复杂或扫描的数据量比较大，则数字将下降，因此在这种情况下，请考虑降低并发数，并在可能的情况下用较长的时间来执行查询。

客户端应用程序和网络连接

确保客户端应用程序已通过最佳连接方式连接到尽可能接近的 Azure Synapse 工作区。

将客户端应用程序与 Azure Synapse 工作区共置在一起。如果要使用 Azure Analysis Service 等应用程序，请确保它们位于 Azure Synapse 工作区所在的区域中。根据需要，创建单独的工作区与客户端应用程序配对。如果将客户端应用程序和 Azure Synapse 工作区放在不同区域，这会增加延迟并降低流式处理结果的速度。
如果从本地应用程序读取数据，请确保 Azure Synapse 工作区位于你所在位置附近的区域中。
读取大量数据时，请确保网络带宽没有问题。
请勿使用 Azure Synapse Studio 返回大量数据。 Azure Synapse Studio 是一种使用 HTTPS 协议传输数据的 Web 工具。请使用 Azure Data Studio 或 SQL Server Management Studio 读取大量数据。

存储和内容布局

以下是无服务器 SQL 池中的存储和内容布局的最佳做法。

将存储和无服务器 SQL 池共置在一起

为尽量降低延迟，请将 Azure 存储帐户或 Azure Cosmos DB 分析存储与无服务器 SQL 池终结点共置在一起。在创建工作区期间预配的存储帐户和终结点位于同一区域。

为了获得最佳性能，如果你使用无服务器 SQL 池访问其他存储帐户，请确保它们位于同一区域。如果它们不在同一区域，那么远程区域和终结点区域之间的数据网络传输延迟将会增加。

Azure 存储限制

多个应用和服务可以访问你的存储帐户。如果应用程序、服务和无服务器 SQL 池工作负载生成的合并 IOPS 或吞吐量超出存储帐户的限制，就会发生存储限制。这样一来，查询性能会受到严重不利影响。

检测到此限制时，无服务器 SQL 池会使用内置的处理机制来解决此问题。无服务器 SQL 池会以较慢速度向存储发出请求，直到限制解除。

提示

为了获得最佳的查询执行效果，不得在查询执行期间对存储帐户施加其他工作负荷。

准备文件以供查询

如果可以，尽可能准备文件来提升性能：

将大型 CSV 和 JSON 文件转换为 Parquet。 Parquet 是一种分列格式。由于经过压缩，它的文件大小小于包含相同数据的 CSV 或 JSON 文件。如果要读取 Parquet 文件，则无服务器 SQL 池会跳过查询中不需要的列和行。无服务器 SQL 池读取此类文件所需的时间和存储请求会更少。
如果查询目标是一个大文件，那么把它拆分为多个较小文件将会让你受益匪浅。
请尽量使 CSV 文件大小保持在 100 MB 和 10 GB 之间。
对于单个 OPENROWSET 路径或外部表 LOCATION，最好有相等大小的文件。
通过将分区存储到不同文件夹或文件名称来对数据进行分区。请参阅使用 filename 和 filepath 函数定目标到特定分区。

将 Azure Cosmos DB 分析存储与无服务器 SQL 池共置在一起

请确保 Azure Cosmos DB 分析存储放置在 Azure Synapse 工作区所在的同一区域中。跨区域查询可能会导致延迟非常大。使用连接字符串中的区域属性显式指定放置分析存储的区域（请参阅使用无服务器 SQL 池查询 Azure Cosmos DB）：account=<database account name>;database=<database name>;region=<region name>'

CSV 优化

下面是在无服务器 SQL 池中使用 CSV 文件的最佳做法。

使用 PARSER_VERSION 2.0 查询 CSV 文件

查询 CSV 文件时，可以使用性能优化的分析器。有关详细信息，请参阅 PARSER_VERSION。

手动为 CSV 文件创建统计信息

无服务器 SQL 池依赖这些统计信息来生成最佳查询执行计划。会通过采样自动为列创建统计信息，在大多数情况下，采样百分比将小于 100%。对于每种文件格式，此流都是相同的。请记住，不支持使用分析程序 1.0 版采样读取 CSV 时，不会在采样百分比低于 100% 的情况下自动创建统计信息。对于基数估计较低（行数）的小型表，会触发统计信息的自动创建，采样百分比为 100%。这意味着会触发完全扫描，并且即使对于分析程序版本 1.0 的 CSV，也会创建自动统计信息。如果不会自动创建统计信息，请手动地为查询中使用的列创建统计信息，尤其是在 DISTINCT、JOIN、WHERE、ORDER BY 和 GROUP BY 中使用的列。有关详细信息，请查看无服务器 SQL 池中的统计信息。

数据类型

下面是在无服务器 SQL 池中使用数据类型的最佳做法。

使用适当的数据类型

查询中使用的数据类型会影响性能和并发。如果遵循以下指南，可以提升性能：

使用可容纳最大可能值的最小数据大小。
- 如果最大字符值长度为 30 个字符，请使用长度为 30 的字符数据类型。
- 如果所有字符列值都是固定大小的，请使用“char”或“nchar” 。否则，请使用 varchar 或 nvarchar 。
- 如果最大整数列值为 500，请使用 smallint，因为它是可容纳此值的最小数据类型。有关详细信息，请参阅整数数据类型范围。
如果可以，尽可能使用 varchar 和 char，而不是 nvarchar 和 nchar 。
- 如果要从 Parquet、Azure Cosmos DB、Delta Lake 或使用 UTF-8 编码的 CSV 中读取数据，请使用带某种 UTF8 排序规则的“varchar”类型。
- 如果要从 CSV 非 Unicode 文件（例如 ASCII）中读取数据，请使用不带 UTF8 排序规则的“varchar”类型。
- 如果要从 CSV UTF-16 文件中读取数据，请使用“nvarchar”类型。
如果可以，尽可能使用基于整数的数据类型。 SORT、JOIN 和 GROUP BY 操作在整数上的执行速度比字符数据快。
如果要使用架构推理，请查看推理数据类型，并在可能的情况下使用较小类型显式替代它们。

查看推理数据类型

架构推理有助于快速编写查询，并浏览数据，而无需了解文件架构。获得此便利的代价是，推断数据类型可能大于实际数据类型。当源文件中没有足够的信息来确保使用适当的数据类型时，就会出现这种差异。例如，Parquet 文件不包含关于最大字符列长度的元数据。因此，无服务器 SQL 池将其推断为 varchar(8000)。

请记住，如果可共享的托管 Spark 表和外部 Spark 表在 SQL 引擎中作为外部表公开，情况可能会有所不同。 Spark 表与 Synapse SQL 引擎提供的数据类型不同。可以在此处找到 Spark 表数据类型和 SQL 类型之间的映射。

你可以使用系统存储过程 sp_describe_first_results_set 来查看查询得到的数据类型。

下面的示例展示了如何优化推理数据类型。使用此过程来显示推理数据类型：

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

结果集如下：

column_ordinal	name	system_type_name	max_length
1	vendor_id	varchar(8000)	8000
2	pickup_datetime	datetime2(7)	8
3	passenger_count	int	4

知道查询的推理数据类型后，可以指定相应的数据类型：

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

筛选器优化

下面是在无服务器 SQL 池中使用查询的最佳做法。

将通配符推送到路径中的较低级别

可以在路径中使用通配符来查询多个文件和文件夹。通过使用存储 API，无服务器 SQL 池从第一个星号 (*) 开始列出存储帐户中的文件。它排除了与指定路径不匹配的文件。如果有多个文件与第一个通配符后面的指定路径匹配，减少初始文件列表可以提升性能。

使用 filename 和 filepath 函数定目标到特定分区

数据通常是以分区形式组织。你可以指示无服务器 SQL 池查询特定文件夹和文件。此操作可减少查询需要读取和处理的文件数和数据量。额外的好处是，将会提升性能。

有关详细信息，请阅读 filename 和 filepath 函数，并查看查询特定文件的示例。

提示

请始终将 filepath 和 filename 函数的结果强制转换为适当的数据类型。如果使用字符数据类型，请确保使用适当的长度。

除了为 Apache Spark for Azure Synapse Analytics 中创建的每个表自动创建的表以外，外部表暂不支持用于删除分区的 filepath 和 filename 函数。

如果存储的数据未分区，请考虑将数据分区。这样，你就可以使用这些函数来优化面向这些文件的查询。如果从无服务器 SQL 池查询分区的 Apache Spark for Azure Synapse 表，则查询自动仅面向必要文件。

使用适当的排序规则对字符列使用谓词下推

Parquet 文件中的数据按行组进行整理。无服务器 SQL 池根据 WHERE 子句中指定的谓词跳过行组，这会减少 IO。结果是提高了查询性能。

仅 Latin1_General_100_BIN2_UTF8 排序规则支持 Parquet 文件中字符列的谓词下推。可使用 WITH 子句为特定列指定排序规则。如果未使用 WITH 子句指定此排序规则，则将使用数据库排序规则。

优化重复查询

下面是在无服务器 SQL 池中使用 CETAS 的最佳做法。

使用 CETAS 增强查询性能和联接

CETAS 是无服务器 SQL 池中最重要的功能之一。 CETAS 是一种并行操作，用于创建外部表元数据，并将 SELECT 查询结果导出到存储帐户中的一组文件。

可以使用 CETAS 将查询的常用部分（如联接的引用表）具体化到一组新的文件中。接下来，可以联接到这一个外部表，而不是在多个查询中重复常用联接。

随着 CETAS 生成 Parquet 文件，当第一个查询的目标是此外部表时，统计信息会自动创建。对于以使用 CETAS 生成的表为目标的后续查询，其结果是提高了性能。

查询 Azure 数据

无服务器 SQL 池使你可以使用外部表和 OPENROWSET 函数在 Azure 存储或 Azure Cosmos DB 中查询数据。请确保对存储进行了正确的权限设置。

查询 CSV 数据

了解如何查询单个 CSV 文件或文件夹以及多个 CSV 文件。还可以查询分区文件

查询 Parquet 数据

了解如何查询具有嵌套类型的 Parquet 文件。还可以查询分区文件。

查询 Delta Lake

了解如何查询具有嵌套类型的 Delta Lake 文件。

查询 Azure Cosmos DB 数据

了解如何查询 Azure Cosmos DB 分析存储。可以使用联机生成器基于示例 Azure Cosmos DB 文档生成 WITH 子句。可以基于 Azure Cosmos DB 容器创建视图。

查询 JSON 数据

了解如何查询 JSON 文件。还可以查询分区文件。

创建视图、表和其他数据库对象

了解如何创建和使用视图、外部表或设置行级安全性。如果具有分区文件，请确保使用分区视图。

复制和转换数据 (CETAS)

了解如何使用 CETAS 命令将查询结果存储到存储中。

后续步骤

有关常见问题的解决方案，请查阅对无服务器 SQL 池进行故障排除一文。
如果你使用的是专用 SQL 池而非无服务器 SQL 池，请参阅专用 SQL 池的最佳做法获取具体指导。
Azure Synapse Analytics 常见问题解答
向工作区托管标识授予权限

通过