在 Azure Synapse Analytics 中使用无服务器 SQL 池访问湖数据库

项目
08/14/2024

通过 Azure Synapse Analytics 工作区，可以在 Spark 数据湖顶部创建两种类型的数据库：

湖数据库，可以在其中使用 Apache Spark 笔记本、数据库模板或 Microsoft Dataverse（以前称为 Common Data Service）在湖数据顶部定义表。这些表可用于通过无服务器 SQL 池使用 T-SQL (Transact-SQL) 语言进行查询。
SQL 数据库，可以在其中直接使用无服务器 SQL 池定义自己的数据库和表。可以使用 T-SQL CREATE DATABASE、CREATE EXTERNAL TABLE 定义对象，并在表顶部添加其他 SQL 视图、过程和内联表值函数。

Diagram that shows Lake and SQL databases that are created on top of Data Lake files.

本文重点介绍 Azure Synapse Analytics 中无服务器 SQL 池中的湖数据库。

通过 Azure Synapse Analytics，可以使用 Spark 或数据库设计器创建湖数据库和表，然后使用无服务器 SQL 池分析湖数据库中的数据。在 Apache Spark 池或 Dataverse 上创建的湖数据库和表（受 Parquet 或 CSV 支持）可自动用于通过无服务器 SQL 池引擎进行查询。经过修改的湖数据库和表将在一段时间后在无服务器 SQL 池中可用。在 Spark 或设计的数据库中所做的更改显示在无服务器中之前会有延迟。

管理湖数据库

若要管理 Spark 创建的湖数据库，可以使用 Apache Spark 池或数据库设计器。例如，通过 Spark 池作业创建或删除湖数据库。无法使用无服务器 SQL 池创建湖数据库或湖数据库中的对象。

Spark default 数据库在无服务器 SQL 池上下文中作为名为 default 的湖数据库提供。

注意

无法在无服务器 SQL 池中创建同名的湖和 SQL 数据库。

无法从无服务器 SQL 池修改湖数据库中的表。使用数据库设计器或 Apache Spark 池来修改湖数据库。通过无服务器 SQL 池，可以使用 Transact-SQL 命令在湖数据库中进行以下更改：

在湖数据库中添加、更改和删除视图、过程、内联表值函数。
添加和移除数据库范围内的 Microsoft Entra 用户。
为 db_datareader 角色添加或移除 Microsoft Entra 数据库用户。 db_datareader 角色中的 Microsoft Entra 数据库用户有权读取湖数据库中的所有表，但无法从其他数据库读取数据。

安全模型

湖数据库和表在两个级别受到保护：

通过将以下项之一分配给 Microsoft Entra 用户来实现基础存储层：
- Azure 基于角色的访问控制 (Azure RBAC)
- Azure 基于属性的访问控制 (Azure ABAC) 角色
- ACL 权限
SQL 层，可在其中定义 Microsoft Entra 用户并向引用湖数据的表中的 SELECT 数据授予 SQL 权限。

湖安全模型

使用存储层上的湖权限控制对湖数据库文件的访问。只有 Microsoft Entra 用户可以使用湖数据库中的表，并且他们可以使用自己的标识访问湖中的数据。

可以将用于外部表的基础数据的访问权限授予安全主体，例如：用户、具有分配的服务主体的 Microsoft Entra 应用程序或安全组。对于数据访问，请授予以下两项权限：

授予对文件（例如表的基础数据文件）的 read (R) 权限。
授予对存储文件的文件夹以及对直到根目录的每个父文件夹的 execute (X) 权限。可以在访问控制列表 (ACL) 页上详细了解这些权限。

例如，在 https://<storage-name>.dfs.core.chinacloudapi.cn/<fs>/synapse/workspaces/<synapse_ws>/warehouse/mytestdb.db/myparquettable/ 中，安全主体需要：

从 <fs> 到 myparquettable 的所有文件夹的 execute (X) 权限。
对 myparquettable 和该文件夹中的文件的 read (R) 权限，以便能够读取数据库中的表（同步或原始）。

如果安全主体需要能够在数据库中创建对象或删除对象，则需要对 warehouse 文件夹中的文件夹和文件的附加 write (W) 权限。无法从无服务器 SQL 池修改数据库中的对象，只能从 Spark 池或数据库设计器进行修改。

SQL 安全模型

Azure Synapse 工作区提供一个 T-SQL 终结点，该终结点使你能够使用无服务器 SQL 池查询湖数据库。除了数据访问之外，SQL 接口还使你能够控制谁可以访问表。你需要允许用户使用无服务器 SQL 池访问共享湖数据库。有两种类型的用户可以访问湖数据库：

管理员：在无服务器 SQL 池中分配 Synapse SQL 管理员工作区角色或 sysadmin 服务器级别角色。此角色可完全控制所有数据库。默认情况下，Synapse 管理员和 Synapse SQL 管理员角色还将拥有对无服务器 SQL 池中所有对象的所有权限。
工作区读取者：向登录授予对无服务器 SQL 池的 GRANT CONNECT ANY DATABASE 和 GRANT SELECT ALL USER SECURABLES 权限，使该登录能够访问和读取任何数据库。这可能是向用户分配读取者/非管理员访问权限的不错选择。
数据库读者：在湖数据库中从 Microsoft Entra ID 创建数据库用户，并将其添加到 db_datareader 角色，这将使他们能够读取湖数据库中的数据。

在此处详细了解如何设置对共享数据库的访问控制。

湖数据库中的自定义 SQL 对象

通过湖数据库可以创建自定义 T-SQL 对象，例如架构、过程、视图和内联表值函数 (iTVF)。若要创建自定义 SQL 对象，必须创建一个将在其中放置对象的架构。无法将自定义 SQL 对象放置在 dbo 架构中，因为它是为 Spark、数据库设计器或 Dataverse 中定义的湖表保留的。

重要

必须创建一个将在其中放置 SQL 对象的自定义 SQL 架构。无法将自定义 SQL 对象放置在 dbo 架构中。 dbo 架构是为最初在 Spark 或数据库设计器中创建的湖表保留的。

示例

在湖数据库中创建 SQL 数据库读取器

在此示例中，我们将在湖数据库中添加一个 Microsoft Entra 用户，该用户可以通过共享表读取数据。通过无服务器 SQL 池在湖数据库中添加用户。然后，将用户分配到 db_datareader 角色，以便他们可以读取数据。

CREATE USER [customuser@contoso.com] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader
ADD MEMBER [customuser@contoso.com];

创建工作区级数据读取器

具有 GRANT CONNECT ANY DATABASE 和 GRANT SELECT ALL USER SECURABLES 权限的登录能够使用无服务器 SQL 池读取所有表，但无法创建 SQL 数据库或修改这些表中的对象。

CREATE LOGIN [wsdatareader@contoso.com] FROM EXTERNAL PROVIDER
GRANT CONNECT ANY DATABASE TO [wsdatareader@contoso.com]
GRANT SELECT ALL USER SECURABLES TO [wsdatareader@contoso.com]

此脚本使你无需管理员权限即可创建用户，这些用户可以读取湖数据库中的任何表。

使用无服务器 SQL 池创建并连接到 Spark 数据库

首先使用已在工作区中创建的 Spark 群集创建名为 mytestdb 的新 Spark 数据库。例如，可以将 Spark C# 笔记本和以下 .NET for Spark 语句配合使用以实现此目的：

spark.sql("CREATE DATABASE mytestlakedb")

在短暂的延迟后，可从无服务器 SQL 池中看到湖数据库。例如，在无服务器 SQL 池中运行以下语句。

SELECT * FROM sys.databases;

请验证结果中是否包含 mytestlakedb。

在湖数据库中创建自定义 SQL 对象

以下示例演示如何在 reports 架构中创建自定义视图、过程和内联表值函数 (iTVF)：

CREATE SCHEMA reports
GO

CREATE OR ALTER VIEW reports.GreenReport
AS SELECT puYear, puMonth,
            fareAmount = SUM(fareAmount),
            tipAmount = SUM(tipAmount),
            mtaTax = SUM(mtaTax)
FROM dbo.green
GROUP BY puYear, puMonth
GO

CREATE OR ALTER PROCEDURE reports.GreenReportSummary
AS BEGIN
SELECT puYear, puMonth,
            fareAmount = SUM(fareAmount),
            tipAmount = SUM(tipAmount),
            mtaTax = SUM(mtaTax)
FROM dbo.green
GROUP BY puYear, puMonth
END
GO

CREATE OR ALTER FUNCTION reports.GreenDataReportMonthly(@year int)
RETURNS TABLE
RETURN ( SELECT puYear = @year, puMonth,
                fareAmount = SUM(fareAmount),
                tipAmount = SUM(tipAmount),
                mtaTax = SUM(mtaTax)
        FROM dbo.green
        WHERE puYear = @year
        GROUP BY puMonth )
GO

通过