创建外部数据库对象

6 分钟

可以在 SQL 查询中使用 OPENROWSET 函数来浏览 Data Lake 中的数据，其中 SQL 查询在内置无服务器 SQL 池的默认主数据库中运行。但是，有时想要创建自定义数据库，其中包含一些可更轻松地处理需要经常查询的数据湖中外部数据的对象。

创建数据库

可以在无服务器 SQL 池中创建数据库，就像在 SQL Server 实例中一样。可以在 Synapse Studio 或 CREATE DATABASE 语句中使用图形界面。考虑事项之一是设置数据库的排序规则，以便它支持将文件中的文本数据转换为适当的 Transact-SQL 数据类型。

以下示例代码使用排序规则创建名为 salesDB 的数据库，以便更轻松地将 UTF-8 编码的文本数据导入 VARCHAR 列。

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

创建外部数据源

可以将 OPENROWSET 函数与 BULK 路径配合使用，从自己的数据库中查询文件数据，就像在主数据库中一样；但是，如果计划频繁查询同一位置中的数据，则定义引用该位置的外部数据源会更高效。例如，以下代码为假设的 https://mydatalake.blob.core.windows.net/data/files/ 文件夹创建一个名为“文件”的数据源：

CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)

外部数据源的一个好处是，可以简化 OPENROWSET 查询，以使用数据源的组合以及要查询的文件夹或文件的相对路径：

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

在此示例中，BULK 参数用于指定“订单”文件夹中所有 .csv 文件的相对路径，这是数据源引用的“文件”文件夹的子文件夹。

使用数据源的另一个好处是，可以在访问基础存储时为数据源分配凭据，从而能够通过 SQL 提供对数据的访问权限，而无需允许用户直接访问存储帐户中的数据。例如，以下代码创建一个凭据，该凭据使用共享访问签名 (SAS) 对托管数据湖的基础 Azure 存储帐户进行身份验证。

CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';
GO

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = sqlcred
);
GO

提示

除了 SAS 身份验证之外，还可以定义使用“托管标识”（Azure Synapse 工作区使用的 Microsoft Entra 标识）、特定 Microsoft Entra 主体的凭据，或者基于运行查询的用户标识（这是默认类型的身份验证）的直通身份验证。若要详细了解如何在无服务器 SQL 池中使用凭据，请参阅 Azure Synapse Analytics 文档中的控制 Azure Synapse Analytics 中的无服务器 SQL 池的存储帐户访问。

创建外部文件格式

虽然外部数据源简化了使用 OPENROWSET 函数访问文件所需的代码，但仍需要提供正在访问的文件的格式详细信息；这可能包括分隔文本文件的多个设置。可以采用外部文件格式封装这些设置，如下所示：

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

为需要使用的特定数据文件创建文件格式之后，可以使用文件格式创建外部表，如下所述。

创建外部表

当需要从数据湖中的文件执行大量分析或报告时，使用 OPENROWSET 函数可能会导致包含数据源和文件路径的复杂代码。为了简化对数据的访问，可以将文件封装在外部表中；用户和报告应用程序可以使用标准 SQL SELECT 语句进行查询，就像任何其他数据库表一样。若要创建外部表，请使用 CREATE EXTERNAL TABLE 语句，将列架构指定为标准表，并包括用于指定外部数据源、相对路径和外部文件格式的 WITH 子句。

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

通过创建包含本单元中讨论的外部对象的数据库，可以针对数据湖中的文件提供关系数据库层，使许多数据分析师和报告工具更轻松地使用标准 SQL 查询语义访问数据。

创建外部数据库对象

创建数据库

创建外部数据源

创建外部文件格式

创建外部表

反馈