Azure SQL Managed Instance によるデータ仮想化

適用対象: Azure SQL Managed Instance

Azure SQL Managed Instance のデータ仮想化機能を使用すると、Azure Data Lake Storage Gen2 または Azure Blob Storage に一般的なデータ形式でデータを格納するファイルに対して Transact-SQL (T-SQL) クエリを実行し、結合を使ってローカルに格納されたリレーショナル データと結合することができます。 こうすることで、外部データ (読み取り専用モード内) を元の形式と場所に保持したまま透過的にアクセスすることができます。これはデータの仮想化とも呼ばれています。

概要

データ仮想化では、さまざまなシナリオのセットを対象としたファイルのクエリを実行する 2 つの方法が提供されます。

• OPENROWSET 構文 – ファイルのアドホック クエリ用に最適化されています。 通常は、新しいファイル セットのコンテンツと構造をすばやく調べるために使用されます。
• CREATE EXTERNAL TABLE 構文 – データがデータベースにローカルに保存されているのと同じ構文で、ファイルを繰り返しクエリするために最適化されています。 外部テーブルでは、OPENROWSET 構文と比較していくつかの準備ステップが必要ですが、データ アクセスの制御をより強化することができます。 外部テーブルは、通常、分析ワークロードとレポートに使用されます。

どちらの場合も、この記事で示すように、T-SQL の CREATE EXTERNAL DATA SOURCE 構文を使って外部データ ソースを作成する必要があります。

また、T-SQL SELECT ステートメントの結果を Azure Blob Storage または Azure Data Lake Storage (ADLS) Gen 2 の Parquet ファイルまたは CSV ファイルにエクスポートし、外部テーブルを作成するための、Azure SQL Managed Instance の CREATE EXTERNAL TABLE AS SELECT 構文も利用でき、これらのファイルの上にテーブルを追加できます。

ファイル形式

Parquet 形式と区切りテキスト (CSV) ファイル形式が直接サポートされています。 JSON ファイル形式は、クエリがすべてのドキュメントを個別の行として返す CSV ファイル形式を指定することで間接的にサポートされます。 JSON_VALUE と OPENJSON を使用して、行をさらに解析できます。

ストレージの種類

ファイルは、Azure Data Lake Storage Gen2 または Azure Blob Storage に格納できます。 ファイルのクエリを実行するには、特定の形式で場所を指定し、次の例のように、外部ソースとエンドポイントまたはプロトコルの種類に対応する場所の種類のプレフィックスを使用する必要があります。

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet

重要

指定された場所の種類のプレフィックスは、通信に最適なプロトコルを選び、特定のストレージの種類によって提供される高度な機能を利用するために使用されます。 汎用 https:// プレフィックスの使用は無効です。 常にエンドポイント固有のプレフィックスを使用します。

はじめに

データの仮想化が初めてで、機能をすばやくテストしたい場合は、匿名アクセスを許可する Bing COVID-19 データセットなど、Azure Open Datasets で利用可能なパブリック データ セットをクエリすることから始めます。

次のエンドポイントを使用して、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 を使用して、公開されているストレージ アカウントに格納されているファイルに対してクエリを実行します。

--Quick query on a file stored in a publicly available storage account:
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

最初のクエリの結果セットに基づいて、WHERE、GROUP BY およびその他の句を追加することで、データ セットの探索を続行できます。

マネージド インスタンスで最初のクエリが失敗した場合、そのインスタンスは Azure ストレージ アカウントへのアクセスが制限されている可能性があります。クエリを続行する前に、ネットワーク エキスパートに連絡してアクセスを有効にする必要があります。

パブリック データ セットのクエリに慣れたら、資格情報の提供、アクセス権の付与、ファイアウォール規則の構成を必要とする非パブリック データ セットへの切り替えを検討してください。 多くの実世界のシナリオでは、主にプライベート データ セットを操作します。

非パブリック ストレージ アカウントにアクセスする

マネージド インスタンスにログインしているユーザーは、非パブリック ストレージ アカウントに格納されているファイルにアクセスしてクエリを実行する権限が必要です。 承認手順は、ストレージに対するマネージド インスタンスの認証方法によって異なります。 認証の種類と関連するパラメーターは、各クエリで直接提供されません。 これらは、ユーザー データベースに格納されているデータベース スコープ資格情報オブジェクトにカプセル化されます。 資格情報は、クエリが実行されるたびにストレージ アカウントにアクセスするためにデータベースで使用されます。 Azure SQL Managed Instance では、次の認証の種類がサポートされています。

マネージド ID

マネージド ID は、Microsoft Entra ID で管理される ID を使用して、Azure SQL Managed Instance などの Azure サービスを提供する、Microsoft Entra ID (旧称 Azure Active Directory) の機能です。 この ID は、非パブリック ストレージ アカウントでのデータ アクセスの要求を承認するために使用できます。 Azure SQL Managed Instance などのサービスには、システム割り当てマネージド ID が設定されています。さらに 1 つまたは複数のユーザー割り当てマネージド ID を設定することがもきます。 Azure SQL Managed Instance によるデータ仮想化の場合は、システム割り当てマネージド ID またはユーザー割り当てマネージド ID のいずれかを使用できます。

Azure Storage の管理者が、データにアクセスするためのアクセス許可をマネージド ID に付与する必要があります。 Managed Instance のシステム割り当てマネージド ID へのアクセス許可の付与は、他の Microsoft Entra ユーザーにアクセス許可を付与するのと同じ方法で行います。 次に例を示します。

1. Azure portal のストレージ アカウントの [Access Control (IAM)] ページで、[ロールの割り当ての追加] を選択します。
2. 組み込みの Azure RBAC ロールである [ストレージ BLOB データ閲覧者] を選択します。 これで、必要な Azure Blob Storage コンテナーに対する読み取りアクセス権がマネージド ID に付与されます。
   • [ストレージ BLOB データ閲覧者] Azure RBAC ロールをマネージド ID に付与するのではなく、ファイルのサブセットに対してさらに細かいアクセス許可を付与することもできます。 このコンテナー内の個々のファイルの一部のデータを読み取るためのアクセス権を必要とするすべてのユーザーには、ルート (コンテナー) までのすべての親フォルダーに対する [実行] アクセス許可も必要です。 Azure Data Lake Storage Gen2 で ACL を設定する方法の詳細を確認してください。
3. 次のページでは、[マネージド ID] に [アクセス権の割り当て] を選択します。 メンバーを選択し、[マネージド ID] ドロップダウン リストで目的のマネージド ID を選択します。 詳細については、Azure portal を使用して Azure ロールを割り当てる方法に関するページを参照してください。
4. 次にマネージド ID 認証用のデータベース スコープ資格情報を作成します。これは簡単です。 次の例では、'Managed Identity' がハードコーディングされた文字列であることに注意してください。

-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

Shared Access Signature

Shared Access Signature (SAS) では、ストレージ アカウント内のファイルへの委任アクセスが提供されます。 SAS を使用すると、有効期間、付与されたアクセス許可、受け入れ可能な IP アドレス範囲を含め、付与するアクセス権の種類をきめ細かく制御できます。 SAS トークンが作成されると、それを取り消すことも削除することもできず、その有効期間が切れるまでアクセスが許可されます。

SAS トークンは、次の複数の方法で取得できます。
- [Azure portal] ->[<Your_Storage_Account>] -> [Shared Access Signature] -> [アクセス許可の構成] -> [SAS と接続文字列を生成する] の順に移動します。 詳細については、Shared Access Signature の作成に関するページを参照してください。 - Azure Storage Explorer を使用して SAS を作成および構成します。 - PowerShell、Azure CLI、.NET、REST API を使用すれば、SAS をプログラムで作成できます。 詳細については、「Shared Access Signatures (SAS) を使用して Azure Storage リソースへの制限付きアクセスを許可する」を参照してください。

外部データにアクセスするには、SAS を介して [読み取り] および [リスト] アクセス許可を付与します。 現在、Azure SQL Managed Instance によるデータの仮想化は読み取り専用となっています。

SAS トークンが生成されると、トークンの先頭に疑問符 (?) が付与されます。 このトークンを使用するには、資格情報の作成時につけられた疑問符 (?) を削除する必要があります。 次に例を示します。

-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-04-18T20:42:12Z&st=2019-04-18T12:42:12Z&spr=https&sig=lQHczNvrk1KoYLCpFdSsMANd0ef9BrIPBNJ3VYEIq78%3D'

外部データ ソース

外部データ ソースは、複数のクエリ間でファイルの場所を簡単に参照できるように抽象化したものです。 パブリックの場所に対してクエリを実行する場合、外部データ ソースの作成時に指定する必要があるのはファイルの場所だけです。

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

非パブリック ストレージ アカウントにアクセスする場合は、場所と共に、カプセル化された認証パラメーターを使用してデータベース スコープの資格情報を参照する必要もあります。 次のスクリプトは、ファイル パスを指し、データベース スコープの資格情報を参照する外部データ ソースを作成します。

--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を使用する場合は、次の例のように、ファイルの形式を指定して1 つのファイルに対してクエリを実行します。

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

複数のファイルおよびフォルダーに対してクエリを実行する

この OPENROWSET コマンドを使用すると、BULK パスでワイルドカードを使用して複数のファイルまたはフォルダーに対してクエリを実行することもできます。

次の例では、NYC の黄色いタクシーの乗車レコードのオープン データ セット を使用します。

まず、外部データ ソースを作成する。

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

これで、フォルダー内の .parquet 拡張子を持つすべてのファイルをクエリできるようになりました。 たとえば、ここでは名前パターンに一致するファイルのみをクエリします。

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

複数のファイルまたはフォルダー に対してクエリを実行する場合、1 つのOPENROWSETでアクセスされるファイルはすべて、同じ構造 (同じ数の列やデータ型など) を持っている必要があります。 フォルダーを再帰的に走査することはできません。

スキーマ推論

スキーマの自動推論を使用すると、ファイル スキーマを知らなくてもクエリをすばやく作成し、データを探索することができます。 スキーマ推論は parquet ファイルでのみ機能します。

便利ではありますが、推論されるデータ型は、実際のデータ型よりも大きくなる可能性があります。これは、適切なデータ型が使用されていることを確認するのに十分な情報がソース ファイルに存在するためです。 これにより、クエリのパフォーマンスが低下する可能性があります。 たとえば、Parquet ファイルは最大文字列長のメタデータを含まず、インスタンスはこれを varchar (8000) として推論します。

次の例のように 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からの相対パスを返し、そうでない場合はファイルの完全なパスを返します。

パラメーターを指定して呼び出すと、パラメーターで指定した位置にあるワイルドカードと一致するパスの一部が返されます。 たとえば、パラメーター値 1 の場合は、最初のワイルドカードと一致するパスの一部が返されます。

また、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 クエリをラップするビューを作成および使用することで、基盤となるクエリを簡単に再利用できます。

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

ビューはまた、Power BI のようなレポートや分析ツール でOPENROWSETの結果を使用できます。

外部テーブル

外部テーブルはファイルへのアクセスをカプセル化し、ユーザーテーブルに格納されたローカルリレーショナルデータへのクエリとほぼ同じ操作性を実現します。 外部テーブルを作成するには、外部データ ソースと外部ファイル形式オブジェクトが存在する必要があります。

--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
);
GO

外部テーブルが作成されると、他のテーブルと同様にクエリを実行できます。

SELECT TOP 10 *
FROM tbl_TaxiRides;

OPENROWSETと同様に、外部テーブルを使用すると、ワイルドカードを使用して複数のファイルとフォルダーのクエリを実行できます。 スキーマ推論は、外部テーブルではサポートされていません。

パフォーマンスに関する考慮事項

クエリを実行できるファイルの数やデータの量にハード制限はありませんが、クエリのパフォーマンスは、データの量、データ形式、データの整理方法、およびクエリと結合の複雑さによって異なります。

パーティション分割されたデータに対してクエリを実行する

データは、多くの場合、パーティションとも呼ばれるサブフォルダーに整理されます。 特定のフォルダーやファイルに対してのみクエリを実行するように、マネージド インスタンスに指示することができます。 そうすれば、クエリで読み込んで処理する必要があるファイルの数とデータの量が減り、パフォーマンスが向上します。 この種類のクエリ最適化は、パーティション プルーニングまたは パーティションの削除 と呼ばれます。 クエリの WHERE 句でメタデータ関数 filepath() を使用して、クエリの実行からパーティションを除外できます。

次のクエリ サンプルでは、2017 年の過去 3 か月間についてのみ、NYC イエロー タクシーのデータ ファイルが読み取られます。

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'
    )
WITH (
    vendorID INT
) 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;

格納されているデータがパーティション分割されていない場合は、クエリのパフォーマンスを向上させるためにパーティション分割することを検討してください。

外部テーブルを使用している場合、関数 filepath() と filename() はサポートされていますが、WHERE 句ではサポートされていません。 計算列で使用する場合は、filename と filepath でフィルター処理できます。 この動作を次の例で示します。

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,
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

SELECT *
      FROM tbl_TaxiRides
WHERE
      [year]=2017            
      AND [month] in (10,11,12);

格納されているデータがパーティション分割されていない場合は、クエリのパフォーマンスを向上させるためにパーティション分割することを検討してください。

統計

外部データの統計を収集することは、クエリの最適化のために実行できる最も重要なことの1つです。 インスタンスがデータについて知れば知るほど、クエリーを高速に実行することができます。 SQL エンジンのクエリ オプティマイザーは、コストベースのオプティマイザーです。 オプティマイザーでは、さまざまなクエリ プランのコストが比較されて、最も低コストのプランが選択されます。 多くの場合、最も高速に実行されるプランが選択されます。

統計の自動作成

Azure SQL Managed Instance では、欠落している統計情報について、受信したユーザー クエリを分析します。 統計が足りない場合、クエリ プランに対するカーディナリティ評価を改善するために、クエリ オプティマイザーによって、クエリ述語または結合条件内の個々の列に関して統計が自動的に作成されます。 統計の自動作成は同期的に行われるため、列に統計がない場合、クエリのパフォーマンスが多少低下する可能性があります。 1 つの列の統計を作成する時間は、ターゲットとされるファイルのサイズに依存します。

OPENROWSET の手動統計

OPENROWSETパスの単一列統計は、sys.sp_create_openrowset_statisticsストアド プロシージャを使用して、パラメーターとして 1 つの列を含む select クエリを渡すことで作成できます。

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

デフォルトでは、インスタンスはデータセットに指定されたデータの 100% を使用して統計を作成します。 オプションでTABLESAMPLEオプションを使用して、サンプル サイズをパーセンテージとして指定できます。 複数の列の単一列統計を作成するには、各列の sys.sp_create_openrowset_statistics を実行します。 OPENROWSETパスの複数列統計を作成することはできません。

既存の統計を更新するには、まずsys.sp_drop_openrowset_statisticsストアド プロシージャを使用して削除し、sys.sp_create_openrowset_statisticsを使用して再作成します。

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

外部テーブルの手動統計

外部テーブルに統計を作成するための構文は、通常のユーザー テーブルに使用される構文に似ています。 列の統計を作成するには、統計オブジェクトの名前と列の名前を指定します。

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendorID)
WITH FULLSCAN, NORECOMPUTE;

WITHオプション は必須であり、サンプル サイズの場合、FULLSCANとSAMPLE nパーセントのオプションが許可されています。

• 複数の列の単一列統計を作成するには、各列の CREATE STATISTICS を実行します。
• 複数列の統計はサポートされていません。

トラブルシューティング

クエリの実行に関する問題は、通常、マネージド インスタンスがファイルの場所にアクセスできないことが原因で発生します。 関連するエラー メッセージで、アクセス権が十分ではない、場所またはファイル パスが存在しない、ファイルが別のプロセスで使われている、あるいはディレクトリの一覧を取得できない、などが報告される場合があります。 ほとんどの場合、ネットワークトラフィックコントロールポリシーやアクセス権の欠如により、ファイルへのアクセスがブロックされていることを示します。 これが確認すべきことです。

• ロケーションパスが誤っているか、または入力ミスがあります。
• SAS キーの有効性: 有効期限が切れている、タイプミスが含まれている、疑問符で始まっている可能性があります。
• SAS キーのアクセス許可: 少なくとも読み取りアクセス許可、およびワイルドカードが使用されている場合はリストのアクセス許可が必要です。
• ストレージ アカウントでインバウンドトラフィックがブロックされました。 詳細については、Azure Storageの仮想ネットワーク規則の管理を確認し、マネージド インスタンス VNet からのアクセスが許可されている確認します。
• ストレージ エンドポイント ポリシーを使用してマネージド インスタンスでアウトバウンドトラフィックがブロックされました。 ストレージ アカウントへのアウトバウンドトラフィックを許可します。
• マネージド ID アクセス権: インスタンスのマネージド ID に、ストレージ アカウントに対するアクセス権が付与されていることを確認します。
• データ仮想化クエリを機能させるには、データベースの互換性レベルを 130 以上とする必要があります。

CREATE EXTERNAL TABLE AS SELECT (CETAS)

CREATE EXTERNAL TABLE AS SELECT (CETAS) を使うと、SQL マネージド インスタンスから外部のストレージ アカウントにデータをエクスポートできます。 CETAS を使用すると、Parquet または CSV ファイルの Azure Blob Storage または Azure Data Lake Storage (ADLS) Gen2 に外部テーブルを作成できます。 また、CETAS を使用すると、作成された外部テーブルに T-SQL SELECT ステートメントの結果を並列でエクスポートすることもできます。 これらの機能にはデータ流出リスクが発生する可能性があるため、CETAS は Azure SQL Managed Instance では既定で無効になっています。 有効にするには、「CREATE EXTERNAL TABLE AS SELECT (CETAS)」を参照してください。

制限事項

• 行レベルのセキュリティ機能は、外部テーブルではサポートされていません。
• 外部テーブルの列に対して、動的データ マスク ルールを定義することはできません。

既知の問題

• Always Encrypted のパラメーター化が SQL Server Management Studio (SSMS) で有効になっている場合、データ仮想化クエリが失敗し、エラー メッセージ Incorrect syntax near 'PUSHDOWN' が表示されます。

関連するコンテンツ

• OPENROWSET T-SQL
• CREATE EXTERNAL TABLE
• CREATE EXTERNAL FILE FORMAT
• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL TABLE AS SELECT