演習 - SELECT として外部テーブルを作成する

10 分

この演習では、CREATE EXTERNAL TABLE AS SELECT (CETAS) を使用して次の操作を行います。

テーブルを Parquet としてエクスポートします。
コールドデータをデータベースからストレージに移動します。
エクスポートされた外部データにアクセスする外部テーブルを作成します。
クエリ戦略としてビューまたはワイルドカード検索を使用します。
フォルダーの削除とメタデータ情報を使用してクエリを制限し、パフォーマンスを向上させます。

前提条件

インターネット接続と PolyBase Query Service for External Data 機能がインストールされ、前の演習と同様に有効になっている SQL Server 2025 インスタンス。
サンプルデータに使用するためにサーバーに復元された AdventureWorks2025 サンプルデータベース。
Azure Storage アカウントに、data という名前の Blob Storage コンテナーが作成されました。ストレージを作成するには、「クイックスタート: Azure portal を使用して BLOB をアップロード、ダウンロード、および一覧表示する」を参照してください。
Azure で割り当てられた、Azure ロールベースのアクセス制御 (RBAC) ストレージ BLOB データ共同作成者ロール。詳細については、「 BLOB データにアクセスするための Azure ロールの割り当て」を参照してください。
CETAS に使用する READ、WRITE、LIST、CREATE のアクセス許可を持つ BLOB コンテナー SAS トークン。 SAS トークンを作成するには、「Shared Access Signature (SAS) を使用してAzure Storageリソースへの制限付きアクセスを許可する」を参照してください。

CETAS を使用してテーブルを Parquet としてエクスポートする

SQL Server テーブルから Azure Blob Storage コンテナーに 2012 より古いデータをエクスポートするビジネス分析チームと協力しているとします。 SQL Server に直接クエリを実行するのではなく、エクスポートされたこのデータに対してレポートクエリを実行したいと考えています。

SQL Server インスタンスで CETAS を有効にします。

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 AdventureWorks2025;

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>と<storageccount>のプレースホルダーを、Azure で作成したストレージアカウントと SAS トークンに置き換えます。

-- 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 Portal で Azure Blob Storage を確認します。次の構造が作成されていることがわかります。 SQL Server 2025 では、エクスポートするデータの量とファイル形式に基づいて、ファイル名が自動的に作成されます。
標準テーブルのように外部テーブルにアクセスできるようになりました。
```
SELECT * FROM ex_data_2011_2012
```

これで、データは Parquet にエクスポートされ、外部テーブルから簡単にアクセスできます。ビジネス分析チームは、外部テーブルに対してクエリを実行したり、Parquet ファイルでレポートツールをポイントしたりできます。

CETAS を使用してコールドデータをデータベースから移動する

データを管理しやすくするために、会社は SQL Server データベースから 2014 より古いデータを移動することにしました。ただし、すべてのデータに引き続きアクセスできる必要があります。

この例では、CETAS を使用してデータをエクスポートし、後でクエリを実行できるいくつかの外部テーブルを生成します。 UNION ステートメントを含むビューを使用してデータのクエリを実行したり、1 つの外部テーブルを作成したり、ワイルドカードを使用してエクスポートされたデータのサブフォルダーを検索したりできます。

最初に、元のテーブルを複製します。データのエクスポートと削除をシミュレートするが、必ずしも現在のデータソースを削除する必要はないためです。次のステートメントを実行します。

-- 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 オブジェクトエクスプローラーを更新します。次に、 Databases>AdventureWorks2025>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 ステートメントを含むビューを使用して、3 つの外部テーブルを結合しました。目的の結果を得るもう 1 つの方法は、ワイルドカード検索を使用して、特定の種類のデータのフォルダー構造 (サブフォルダーを含む) をスキャンすることです。

次の T-SQL の例では、OPENROWSET を使用して、サブフォルダーを含む ABS_Data データソース全体で Parquet ファイルを検索します。

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 でサポートされているすべてのデータソースにわたって、パフォーマンス、持続性、拡張性に優れたソリューションを設計するのに役立ちます。

フィードバック

このページはお役に立ちましたか?