Вправа — СТВОРЮВАТИ ЗОВНІШНЮ ТАБЛИЦЮ ЗА SELECT-ОМ

10 хв.

У цій вправі ви використовуєте CREATE EXTERNAL TABLE AS SELECT (CETAS), щоб:

Експорт таблиці як паркету.
Переміщення холодних даних із бази даних у сховище.
Створіть зовнішню таблицю для доступу до експортованих зовнішніх даних.
Використовуйте перегляди або пошук джокерів як стратегії запитів.
Обмежте кількість запитів, використовуючи інформацію про видалення папок та метадані для підвищення продуктивності.

Вимоги

Інстанс SQL Server 2025 з підключенням до інтернету та функцією PolyBase Query Service for External Data, встановленою та увімкненою, як і в попередніх вправах.
Зразкова база даних AdventureWorks2022 відновлена на вашому сервері для використання для зразкових даних.
Обліковий запис Azure Storage з контейнером Blob Storage під назвою data created. Щоб створити сховище, дивіться Швидкий старт: Завантаження, завантаження та перелік blobs через портал Azure.
Роль Azure Storage Blob Data Contributor на основі ролей (RBAC) призначена в Azure. Детальніше дивіться у розділі Assign an Azure role для доступу до blob data.
Blob-контейнер SAS токен з правами READ, WRITE, LIST і CREATE для використання в CETAS. Щоб створити токен SAS, дивіться Створення токена спільного доступу (SAS) для ваших контейнерів зберігання.

Використовуйте CETAS для експорту столу як Parquet

Уявіть, що ви працюєте з командою бізнес-аналітики, яка хоче експортувати дані старші за 2012 рік із таблиці SQL Server у контейнер Azure Blob Storage. Вони хочуть запускати свої звітні запити на основі цих експортованих даних, а не безпосередньо звертатися до SQL Server.

Увімкніть CETAS на екземплярі SQL Server.

EXEC SP_CONFIGURE @CONFIGNAME = 'ALLOW POLYBASE EXPORT', @CONFIGVALUE = 1;

Скріншот налаштування опції дозволу експорту PolyBase.

Виконайте наступний запит для дослідження даних, щоб зрозуміти, які дані ви хочете експортувати. У цьому випадку ви шукаєте дані з 2012 року або раніше. Ви хочете експортувати всі дані з 2011 та 2012 років.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Створіть головний ключ бази даних, як у попередніх вправах.

Use AdventureWorks2022

DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
EXEC sp_executesql @createMasterKey;

SELECT * FROM sys.symmetric_keys;

Створіть облікові дані з обмеженням бази даних та зовнішнє джерело даних. Замініть <sas_token> заповнювачі and <storageccount> на обліковий запис зберігання та токени SAS, які ви створили в Azure.

-- DATABASE SCOPED CREDENTIAL
CREATE DATABASE SCOPED CREDENTIAL blob_storage
      WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
      SECRET = '<sas_token>';

-- AZURE BLOB STORAGE DATA SOURCE
CREATE EXTERNAL DATA SOURCE ABS_Data
WITH
(
 LOCATION = 'abs://<storageaccount>.blob.core.windows.net/data/chapter3'
,CREDENTIAL = blob_storage
);

Створіть зовнішній формат файлу для Parquet.

-- PARQUET FILE FORMAT
CREATE EXTERNAL FILE FORMAT ffParquet
WITH (FORMAT_TYPE = PARQUET);

Створіть зовнішню таблицю за допомогою CETAS. Наступний запит створює зовнішню таблицю з назвою ext_data_2011_2012 та експортує всі дані з 2011 та 2012 років у місце, вказане джерелом ABS_Dataданих .

CREATE EXTERNAL TABLE ex_data_2011_2012
WITH(
        LOCATION = 'data_2011_20122',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
	[PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
WHERE YEAR([DUEDATE]) < 2013
GO

Перевірте своє Azure Blob Storage у порталі Azure. Ви повинні побачити таку структуру, створену нижче. SQL Server 2025 автоматично створює ім'я файлу залежно від обсягу експортованих даних та формату файлу.
Тепер ви можете отримати доступ до зовнішньої таблиці як до звичайної.
```
SELECT * FROM ex_data_2011_2012
```

Дані тепер експортуються до Parquet і легко доступні через зовнішню таблицю. Команда бізнес-аналітики може звертатися до зовнішньої таблиці або направляти свій інструмент звітності на файл Parquet.

Використовуйте CETAS для переміщення холодних даних з бази даних

Щоб зберегти дані керованими, ваша компанія вирішує перенести дані старші за 2014 рік із бази даних SQL Server. Однак усі дані мають бути доступними.

У цьому прикладі ви експортуєте дані через CETAS і генеруєте кілька зовнішніх таблиць, які можна зробити запит пізніше. Ви можете використовувати вигляд з операторами UNION для запиту до даних або створити одну зовнішню таблицю і використовувати wildcard для пошуку підпапок експортованих даних.

По-перше, клонуйте оригінальну таблицю, бо хочете імітувати експорт і видалення даних, але не обов'язково видаляти поточне джерело. Запустимо наступне твердження:

-- CLONE TABLE
SELECT * INTO [PURCHASING].[PURCHASEORDERDETAIL_2] FROM [PURCHASING].[PURCHASEORDERDETAIL]

З першого запиту на дослідження даних ви знаєте, що за 2014 рік є 5551 запис. Все, що було до 2014 року, слід експортувати у папку, визначену за роком. Дані з 2011 року потрапляють у папку під назвою 2011, і так далі.

Щоб створити зовнішні таблиці, виконайте такі команди:

CREATE EXTERNAL TABLE ex_2011
WITH(
        LOCATION = '2011',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2011;

CREATE EXTERNAL TABLE ex_2012
WITH(
        LOCATION = '2012',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2012;

CREATE EXTERNAL TABLE ex_2013
WITH(
        LOCATION = '2013',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2013;

Після виконання цих команд оновіть SSMS Object Explorer. Потім відкрийте Databases>AdventureWorks2022>Tables>External Tables , щоб побачити зовнішні таблиці.
Переконайтеся, що в контейнері Azure Storage з'являються такі папки:
Після експорту холодних даних ви можете видалити їх із початкового розташування таблиці.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Дані запиту, що включають зовнішню таблицю

Ви можете використати перегляд або пошук за допомогою джокера для запиту до експортованих зовнішніх даних. Кожен метод має свої переваги та недоліки. Метод перегляду рекомендується для повторюваних запитів, оскільки зазвичай працює краще, а також може поєднуватися з фізичними таблицями. Метод пошуку за дикими картами більш гнучкий і простіший у використанні для дослідження.

Використовуйте перегляд для запиту до даних

Тепер, коли старі дані експортовані та видалені з бази, ви можете використовувати T-SQL для створення вигляду, який запитує всі зовнішні таблиці та поточні дані у вашій базі.

CREATE VIEW vw_purchaseorderdetail 
AS
SELECT * FROM ex_2011
UNION ALL
SELECT * FROM ex_2012
UNION ALL
SELECT * FROM ex_2013
UNION ALL
SELECT * FROM  [PURCHASING].[PURCHASEORDERDETAIL_2]

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

SELECT  COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM vw_purchaseorderdetail 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Використайте пошук за диким кодом для запиту до даних

У попередньому прикладі ви використали вигляд із операторами UNION для об'єднання трьох зовнішніх таблиць. Ще один спосіб досягти бажаних результатів — скористатися пошуком за допомогою дикого коду для сканування структури папок, включаючи підпапки, на предмет будь-яких даних певного типу.

Наступний приклад T-SQL використовує OPENROWSET для пошуку файлів Parquet у ABS_Data джерелі даних, включаючи його підпапки.

SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM OPENROWSET 
    (BULK '**'
    , FORMAT = 'PARQUET'
    , DATA_SOURCE = 'ABS_Data')
    AS [cc]
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Видалення папок та інформація про метадані

Як зовнішні таблиці, так і OPENROWSET можуть використовувати цю filepath функцію для збору та фільтрації інформації на основі метаданих файлу. Функція filepath повертає повні шляхи, назви папок і імена файлів. Цю інформацію можна використати для покращення пошуку як зовнішньої таблиці, так і команд OPENROWSET.

SELECT
    r.filepath(1) 'folder_name'
    ,r.filepath() 'full_path'
    ,r.filepath(2) 'file_name'
FROM OPENROWSET(
        BULK '*/*.parquet',
        DATA_SOURCE = 'ABS_Data',
        FORMAT = 'parquet'
    ) as [r]
GROUP BY
    r.filepath(2),r.filepath(1), r.filepath()
ORDER BY 
    r.filepath(2)

Скріншот SSMS, що показує функцію шляху до файлу.

Якщо ви хочете отримати дані з певної папки і при цьому використовувати функціональність методу пошуку за уайлкером, ви можете скористатися наступним запитом:

SELECT  *
FROM OPENROWSET(
 BULK '*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2011')

Кінцеві результати ті самі, але завдяки використанню метаданих усунення папок ваш запит отримує доступ лише до необхідних папок замість сканування всього джерела даних, що забезпечує кращу продуктивність запиту. Майте цю інформацію на увазі при розробці архітектур зберігання для кращого використання можливостей PolyBase.

Наприклад, маючи таку архітектуру папок:

Скріншот, що показує приклад архітектури папок у контейнері зберігання.

Ви можете скористатися наступним запитом:

SELECT  *
FROM OPENROWSET(
 BULK 'year=*/month=*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('<year>')
 r.filepath(2) IN ('<month>')

Для цілей цього запиту не має значення, наскільки великим зростає джерело даних. SQL Server завантажує, читає та запитує лише дані з обраної папки, пропускаючи всі інші.

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

Зведення

У цій вправі ви використали CETAS, щоб перемістити холодні дані з бази даних у Azure Storage і експортувати таблицю у форматі файлу Parquet. Ви навчилися робити запити до зовнішніх даних для дослідження та оптимізувати продуктивність.

Ви можете використовувати CETAS для об'єднання OPENROWSET, зовнішніх таблиць, переглядів, пошуку за джокерами та функцій шляху до файлу. Ви можете отримувати доступ і експортувати дані з інших баз даних, таких як SQL Server, Oracle, Teradata та MongoDB, або з Azure Blob Storage, Azure Data Lake Storage чи будь-якого об'єктного сховища, сумісного з S3. CETAS може допомогти вам розробити продуктивні, довговічні та масштабовані рішення на всіх джерелах даних, що підтримуються PolyBase.

Зворотний зв’язок

Чи була ця сторінка корисна?