Начало работы с PolyBase в SQL Server 2022

Область применения: SQL Server 2016 (13.x) — Windows и более поздних версий SQL Server 2017 (14.x) — Linux и более поздних версий

В этой статье описано, как работать с несколькими папками и файлами с PolyBase в SQL Server 2022 (16.x). Этот набор запросов учебника демонстрирует различные функции PolyBase.

Виртуализация данных с помощью PolyBase в SQL Server позволяет использовать функции файлов метаданных для запроса нескольких папок, файлов или устранения папок. Сочетание обнаружения схем с папкой и устранением файлов — это мощная возможность, которая позволяет SQL получить только необходимые данные из любого решения хранилища объектов, совместимого с служба хранилища Azure account или S3-совместимым с объектом.

Необходимые компоненты

Прежде чем использовать PolyBase в этом руководстве, необходимо:

1. Установите PolyBase в Windows или установите PolyBase в Linux.
2. При необходимости включите PolyBase в sp_configure .
3. Разрешить внешнему сетевому доступу к общедоступному хранилищу pandemicdatalake.blob.core.windows.net BLOB-объектов Azure и azureopendatastorage.blob.core.windows.net.

Примеры наборов данных

Если вы не знакомы с виртуализацией данных и хотите быстро протестировать функциональные возможности, начните с запроса общедоступных наборов данных, доступных в Открытых наборах данных Azure, таких как набор данных Bing COVID-19, разрешающий анонимный доступ.

Используйте следующие конечные точки для отправки запросов к наборам данных Bing COVID-19:

• Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
• CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Для быстрого запуска выполните этот простой запрос T-SQL, чтобы получить первую информацию о наборе данных. Этот запрос использует OPENROWSET для запроса файла, хранящегося в общедоступной учетной записи хранения:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet', 
 FORMAT = 'parquet' 
) AS filerows;

Вы можете продолжить изучение набора данных, добавив WHEREGROUP BY и другие предложения T-SQL на основе результирующей группы первого запроса.

Если первый запрос завершается сбоем в экземпляре SQL Server, доступ к сети, скорее всего, будет запрещен к общедоступной учетной записи хранения Azure. Обратитесь к специалисту по работе с сетями, чтобы включить доступ, прежде чем продолжить выполнение запросов.

Когда вы знакомы с запросом общедоступных наборов данных, рассмотрите возможность переключения на неопубликованные наборы данных, требующие предоставления учетных данных, предоставления прав доступа и настройки правил брандмауэра. Во многих реальных сценариях вы будете работать в основном с частными наборами данных.

Внешний источник данных

Внешний источник данных — это абстракция, которая позволяет легко ссылаться на расположение файла в нескольких запросах. Чтобы запросить общедоступные расположения, необходимо указать при создании внешнего источника данных расположение файла:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
);

Примечание.

Если вы получаете сообщение об ошибке 46530, External data sources are not supported with type GENERIC, проверьте параметр PolyBase Enabled конфигурации в экземпляре SQL Server. Оно должно иметь значение 1.

Выполните следующую команду, чтобы включить PolyBase в экземпляре SQL Server:

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

При доступе к неопубликованным учетным записям хранения вместе с расположением также необходимо ссылаться на учетные данные базы данных с инкапсулированными параметрами проверки подлинности. Следующий скрипт создает внешний источник данных, указывающий на путь к файлу, и ссылается на учетные данные, на которые распространяется область базы данных.

--Create external data source pointing to the file path, and referencing database-scoped credential: 
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        CREDENTIAL = [MyCredential]);

Обращение к источникам данных с помощью OPENROWSET

Синтаксис OPENROWSET позволяет мгновенно запрашивать нерегламентированные запросы, создавая только минимальное количество необходимых объектов базы данных.

OPENROWSET требуется только создание внешнего источника данных (и, возможно, учетных данных) в отличие от подхода внешней таблицы, для которого требуется внешний формат файла и сама внешняя таблица .

Значение параметра DATA_SOURCE автоматически добавляется в начало параметра BULK для формирования полного пути к файлу.

При использовании OPENROWSET укажите формат файла (например, как в следующем примере), который запрашивает один файл:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.parquet', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows; 

Запрос нескольких файлов и папок

Команда OPENROWSET также позволяет отправлять запросы к нескольким файлам или папкам с использованием подстановочных знаков в пути BULK.

В следующем примере используется набор данных с записями о поездках такси Нью-Йорка:

Сначала создайте внешний источник данных:

--Create the data source first
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource 
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Теперь мы можем запросить все файлы с расширением PARQUET в папках. Например, здесь мы запросим только те файлы, которые соответствуют шаблону имен:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

При отправке запросов к нескольким файлам или папкам все файлы, доступ к которым осуществляется с помощью одной функции OPENROWSET, должны иметь одинаковую структуру (например, одинаковое число столбцов и типов данных). Рекурсивный переход по папкам выполнить нельзя.

Вывод схемы

Автоматический вывод схемы помогает быстро создавать запросы и исследовать данные, если вы не знаете схемы файлов. Вывод схемы работает только с файлами parquet.

Хотя это удобно, выводимые типы данных могут быть больше фактических типов данных, так как в исходных файлах может быть достаточно сведений, чтобы обеспечить использование соответствующего типа данных. Иногда это приводит к снижению производительности запросов. Например, файлы parquet не содержат метаданные о максимальной длине столбца символов, поэтому экземпляр определяет его как varchar(8000).

Используйте хранимую sys.sp_describe_first_results_set процедуру для проверки результирующего типа данных запроса, например в следующем примере:

EXEC sp_describe_first_result_set N'
 SELECT 
 vendorID, tpepPickupDateTime, passengerCount 
 FROM 
 OPENROWSET( 
  BULK ''yellow/*/*/*.parquet'', 
  DATA_SOURCE = ''NYCTaxiExternalDataSource'', 
  FORMAT=''parquet'' 
 ) AS nyc'; 

Если вы знаете типы данных, вы можете указать их с помощью предложения WITH, чтобы улучшить производительность:

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount 
FROM 
 OPENROWSET( 
  BULK 'yellow/*/*/*.parquet', 
  DATA_SOURCE = 'NYCTaxiExternalDataSource', 
  FORMAT='PARQUET' 
 ) 
WITH ( 
 vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000 
 tpepPickupDateTime datetime2, 
 passengerCount int 
 ) AS nyc;

Так как схема CSV-файлов не может быть определена автоматически, столбцы должны всегда указываться с помощью WITH предложения:

SELECT TOP 10 id, updated, confirmed, confirmed_change 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.csv', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'CSV', 
 FIRSTROW = 2 
) 
WITH ( 
 id int, 
 updated date, 
 confirmed int, 
 confirmed_change int 
) AS filerows; 

Функции метаданных файлов

При запросе нескольких файлов или папок можно использовать filepath() и filename() функции для чтения метаданных файла и получения части пути или полного пути и имени файла, из которого исходит строка в результирующем наборе. В следующем примере запросите все файлы и путь к файлу проекта и сведения о имени файла для каждой строки:

--Query all files and project file path and file name information for each row: 

SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder], 
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows; 

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows; 

• При вызове без параметров функция filepath() возвращает путь к файлу, из которого была получена запись. Если DATA_SOURCE используется в OPENROWSET, возвращается путь относительно DATA_SOURCE. В противном случае возвращается полный путь.

• При вызове с параметром filepath() функция возвращает часть пути, соответствующего подстановочным знаком в позиции, указанной в параметре. Например, первое значение параметра возвращает часть пути, соответствующего первому подстановочным знаку.

Функцию filepath() также можно использовать для фильтрации и агрегирования столбцов:

SELECT 
 r.filepath() AS filepath 
 ,r.filepath(1) AS [year] 
 ,r.filepath(2) AS [month] 
 ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
DATA_SOURCE = 'NYCTaxiExternalDataSource', 
FORMAT = 'parquet' 
 ) AS r 
WHERE 
 r.filepath(1) IN ('2017') 
 AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
 r.filepath() 
 ,r.filepath(1) 
 ,r.filepath(2) 
ORDER BY 
 filepath;

Создание представления поверх OPENROWSET

Можно создавать представления для упаковки OPENROWSET запросов, чтобы можно было легко использовать базовый запрос. Представления также позволяют создавать отчеты и аналитические средства, такие как Power BI, для использования результатов OPENROWSET.

Например, рассмотрим следующее представление на OPENROWSET основе команды:

CREATE VIEW TaxiRides AS 
SELECT * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Также может быть удобно добавить в представление столбцы с данными расположения файлов с помощью функции filepath() для более простой и производительной фильтрации. Представления позволяют сократить число файлов и объем данных, который необходимо считать и обработать запросу над представлением при фильтрации по какому-либо из этих столбцов:

CREATE VIEW TaxiRides AS 
SELECT * 
 , filerows.filepath(1) AS [year] 
 , filerows.filepath(2) AS [month] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows; 

Внешние таблицы

Внешние таблицы инкапсулируют доступ к файлам, которые практически идентичны запросу локальных реляционных данных, хранящихся в пользовательских таблицах. Для создания внешней таблицы необходим внешний источник данных и внешние объекты формата файлов:

--Create external file format 
CREATE EXTERNAL FILE FORMAT DemoFileFormat 
WITH ( 
 FORMAT_TYPE=PARQUET 
) 
GO 
 
--Create external table: 
CREATE EXTERNAL TABLE tbl_TaxiRides( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
); 

После создания внешней таблицы вы можете отправлять к ней запросы так же, как и к любой другой таблице:

SELECT TOP 10 * 
FROM tbl_TaxiRides; 

Как и OPENROWSET, внешние таблицы позволяют запрашивать несколько файлов и папок с помощью подстановочных знаков. Вывод схемы не поддерживается внешними таблицами.

Внешние источники данных

Дополнительные руководства по созданию внешних источников данных и внешних таблиц в различных источниках данных см . в справочнике по PolyBase Transact-SQL.

Дополнительные руководства по различным внешним источникам данных см. в следующем разделе:

• Hadoop
• Хранилище BLOB-объектов Azure
• SQL Server
• Oracle
• Teradata
• MongoDB
• Универсальные типы ODBC
• S3-совместимое хранилище объектов
• CSV
• Таблица Delta

Связанный контент

• Виртуализация данных с помощью PolyBase в SQL Server
• Часто задаваемые вопросы в PolyBase
• Рекомендации по производительности в PolyBase для SQL Server
• Конфигурация и безопасность PolyBase для Hadoop