Azure Synapse Analytics の SQL プールを使用してネイティブの外部テーブルを作成および使用する

[アーティクル]
05/17/2023

このセクションでは、Synapse SQL プールでネイティブの外部テーブルを作成および使用する方法を学習します。ネイティブの外部テーブルは、外部データソースの定義に TYPE=HADOOP が含まれている外部テーブルと比較して、パフォーマンスが高くなっています。これは、ネイティブの外部テーブルでは、ネイティブコードを使用して外部データにアクセスするためです。

外部テーブルは、Synapse SQL プールの外部データへのアクセスを制御する場合に便利です。外部テーブルは、Power BI などのツールを Synapse SQL プールと組み合わせて使用する場合にも便利です。外部テーブルは、次の 2 種類のストレージにアクセスできます。

パブリックストレージ。ユーザーは、パブリックストレージファイルにアクセスします。
保護されたストレージ。ユーザーは、Synapse ワークスペースの SAS 資格情報、Microsoft Entra ID、またはマネージド ID を使用して、ストレージファイルにアクセスします。

Note

専用 SQL プールで、Parquet のファイルの種類のネイティブ外部テーブルのみ使用できます。この機能はパブリックプレビュー段階です。一般提供されている Parquet リーダー機能を専用 SQL プールで使用する場合、または CSV または ORC ファイルにアクセスする必要がある場合は、Hadoop 外部テーブルを使用します。ネイティブの外部テーブルは、サーバーレス SQL プールで一般提供されています。ネイティブおよび Hadoop の外部テーブルの違いの詳細については、Synapse SQL で外部テーブルを使用する方法に関する記事を参照してください。

次の表に、サポートされているデータ形式を一覧で示します。

データ形式 (ネイティブ外部テーブル)	サーバーレス SQL プール	専用 SQL プール
Parquet	はい (GA)	はい (パブリックプレビュー)
CSV	はい	いいえ (代わりに、Hadoop 外部テーブルを使用)
delta	はい	いいえ
Spark	はい	いいえ
Dataverse	はい	いいえ
Azure Cosmos DB データ形式 (JSON、BSON など)	いいえ (代わりに、ビューを作成)	いいえ

前提条件

最初の手順として、テーブルが作成されるデータベースを作成します。データベーススコープ資格情報を作成する前に、データベースに資格情報を保護するためのマスターキーが必要です。詳しくは、「CREATE MASTER KEY (Transact-SQL)」をご覧ください。次に、このサンプルで使用する次のオブジェクトを作成します。

データベーススコープ資格情報 sqlondemand。SAS で保護された https://sqlondemandstorage.blob.core.windows.net Azure ストレージアカウントへのアクセスを有効にします。

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'

外部データソース sqlondemanddemo。SAS キーで保護されたデモストレージアカウントを参照し、外部データソース nyctlc。https://azureopendatastorage.blob.core.windows.net/nyctlc/ という場所で一般公開されている Azure ストレージアカウントを参照します。

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );

ファイル形式 QuotedCSVWithHeaderFormat および ParquetFormat。CSV ファイルおよび parquet ファイルの種類を記述します。

CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
GO

この記事のクエリは、サンプルデータベースで実行され、これらのオブジェクトを使用します。

ファイル上の外部テーブル

特定の Microsoft Entra ID または SAS キーを持つユーザーにアクセス可能な Azure ストレージアカウントのデータにアクセスする外部テーブルを作成できます。外部テーブルは、通常の SQL Server 外部テーブルを作成するのと同じ方法で作成できます。

次のクエリでは、sqlondemanddemo データソースを使用して参照され、sqlondemand と呼ばれるデータベーススコープの資格情報で保護される、SynapseSQL のデモ Azure ストレージアカウントから population.csv ファイルを読み取る外部テーブルを作成します。

データソースとデータベーススコープ資格情報は、セットアップスクリプトで作成されます。

Note

クエリの最初の行 ([mydbname]) は、自分で作成したデータベースを使用するように変更してください。

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

現在、ネイティブの CSV テーブルはサーバーレス SQL プールでのみ使用できます。

ファイルのセット上の外部テーブル

Azure ストレージに配置されたファイルのセットからデータを読み取る外部テーブルを作成できます。

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

外部テーブルによってファイルが参照される条件となるパターンを指定できます。このパターンは、Parquet および CSV のテーブルに対してのみ必要です。 Delta Lake 形式を使用している場合、指定する必要があるのはルートフォルダーだけで、パターンは外部テーブルによって自動的に検索されます。

Note

テーブルはパーティション分割されたフォルダー構造で作成されますが、一部のパーティションの削除を利用することはできません。なんらかの条件 (この場合は特定の年や月など) を満たしていないファイルをスキップしてパフォーマンスを向上させる場合は、外部データに関するビューを使用します。

追加可能なファイル上の外部テーブル

外部テーブルで参照されるファイルは、クエリの実行中に変更しないでください。長時間実行されているクエリでは、SQL プールが読み取りを再試行したり、ファイルの一部を読み取ったり、ファイルを複数回読み取ったりしている場合があります。ファイルコンテンツを変更すると、正しくない結果になる可能性があります。したがって、クエリの実行中にファイルの変更時刻を検出すると、SQL プールはクエリに失敗します。場合によっては、継続的に追加されるファイルへのテーブルの作成が必要になることがあります。継続的に追加されるファイルが原因でクエリが失敗しないようにするには、TABLE_OPTIONS の設定を使用して、不整合な可能性のある読み取りが外部テーブルで無視されるように指定できます。

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

ALLOW_INCONSISTENT_READS 読み取りオプションを指定すると、クエリライフサイクルでのファイルの変更時刻のチェックが無効になり、外部テーブルによって参照されるファイルで使用可能なものをすべて読み取ります。追加可能なファイルでは、既存のコンテンツは更新されず、新しい行だけが追加されます。そのため、更新可能なファイルと比較して、正しくない結果になる可能性が最小限に抑えられます。このオプションを使用すると、エラーを処理する必要なく、頻繁に追加されるファイルを読み取ることができるようになります。

このオプションは、CSV ファイル形式で作成された外部テーブルでのみ使用できます。

Note

オプション名が示すように、テーブルの作成者は、結果が不整合になる可能性があるリスクを受け入れることになります。追加可能なファイルでは、テーブルを自己結合して、基になるファイルを強制的に複数回読み取った場合に、正しくない結果になる可能性があります。ほとんどの "クラシック" クエリでは、クエリの実行中に追加された一部の行は外部テーブルで無視されます。

Delta Lake 外部テーブル

外部テーブルは、Delta Lake フォルダーの上に作成できます。 1 つのファイルまたはファイルセットで作成された外部テーブルと、Delta Lake 形式で作成された外部テーブルの唯一の違いは、Delta Lake 外部テーブルでは Delta Lake 構造を含むフォルダーを参照する必要がある点です。

ECDC COVID-19 Delta Lake folder

Delta Lake フォルダーに作成されたテーブル定義の例を次に示します。

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

パーティション分割されたフォルダーに外部テーブルを作成することはできません。その他の既知の問題については、Synapse サーバーレス SQL プールのセルフヘルプページをご覧ください。

パーティション分割されたフォルダー上の Delta テーブル

サーバーレス SQL プールの外部テーブルでは、Delta Lake 形式でのパーティション分割はサポートされていません。 Delta Lake データセットをパーティション分割してある場合は、テーブルの代わりに Delta パーティションビューを使います。

重要

動作する可能性があるように見える場合でも、パーティション分割された Delta Lake フォルダーには外部テーブルを作成しないでください。パーティション分割された Delta フォルダー上の外部テーブルのようなサポートされていない機能を使うと、サーバーレスプールの問題や不安定性の原因になる可能性があります。パーティション分割されたフォルダーのテーブルを使っている場合、Azure サポートは問題を解決できません。問題の解決を進める前に、Delta パーティションビューに移行し、サポートされている機能のみを使うようにコードを書き直すことを求められます。

外部テーブルを使用する

外部テーブルは、SQL Server クエリ内で外部テーブルを使用するのと同じ方法で、クエリ内で使用できます。

次のクエリでは、前のセクションで作成した population 外部テーブルの使用について説明しています。これにより、国/地域名が 2019 年の人口の降順に返されます。

Note

クエリの最初の行 ([mydbname]) は、自分で作成したデータベースを使用するように変更してください。

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

このクエリのパフォーマンスは、リージョンによって異なる場合があります。ワークスペースが、これらのサンプルで使用されている Azure ストレージアカウントと同じリージョンに配置されていない可能性があります。実稼働ワークロードの場合は、Synapse ワークスペースと Azure Storage を同じリージョンに配置します。

次の手順

クエリの結果をストレージに格納する方法については、クエリ結果をストレージに格納する方法に関する記事を参照してください。

Azure Synapse Analytics の SQL プールを使用してネイティブの外部テーブルを作成および使用する

前提条件

ファイル上の外部テーブル

ファイルのセット上の外部テーブル

追加可能なファイル上の外部テーブル

Delta Lake 外部テーブル

パーティション分割されたフォルダー上の Delta テーブル

外部テーブルを使用する

次の手順

その他のリソース