Создание внешних объектов базы данных

Завершено

Функцию OPENROWSET можно использовать в SQL-запросах, которые выполняются в базе данных master по умолчанию встроенного бессерверного пула 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 для запроса данных файлов из собственной базы данных так же, как это можно сделать в базе данных master. Но если вы планируете часто запрашивать данные в одном расположении, эффективнее будет определить внешний источник данных, который ссылается на это расположение. Например, в следующем коде создается источник данных с именем files для гипотетической папки 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-файлов в папке orders, являющейся вложенной папкой папки files, на которую ссылается источник данных.

Еще одним преимуществом источника данных является возможность назначения учетных данных для источника данных, которые будут использования при доступе к базовому хранилищу, что позволяет предоставлять доступ к данным через SQL, без обращения пользователей к данным непосредственно в учетной записи хранилища. Например, в следующем коде создаются учетные данные, использующие подписанный URL-адрес (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, можно определить учетные данные, использующие управляемое удостоверение (удостоверение Microsoft Entra, используемое рабочей областью Azure Synapse), конкретный субъект Microsoft Entra или сквозную проверку подлинности на основе удостоверения пользователя, выполняющего запрос (который является типом проверки подлинности по умолчанию). Дополнительные сведения об использовании учетных данных в бессерверном пуле SQL см. в статье Управление доступом к учетной записи хранения в бессерверном пуле SQL в Azure Synapse Analytics в документации по Azure Synapse Analytics.

Создание формата внешнего файла

Хотя внешний источник данных упрощает код, необходимый для доступа к файлам с помощью функции 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-запросов.