Synapse SQL で外部テーブルを使用する

外部テーブルは、Hadoop、Azure Storage BLOB、または Azure Data Lake Storage にあるデータを参照します。 Azure Storage 内のファイルからデータを読み取ったり、ファイルにデータを書き込んだりするために、外部テーブルを使用できます。

Synapse SQL では、外部テーブルを使用し、専用 SQL プールまたはサーバーレス SQL プールを使って外部データを読み取ることができます。

外部データ ソースの種類に応じて、次の 2 種類の外部テーブルを使用できます。

• Hadoop 外部テーブル。各種データ形式 (CSV、Parquet、ORC など) のデータを読み取ったりエクスポートしたりする際に使用できます。 Hadoop 外部テーブルは、専用 SQL プールでは利用できますが、サーバーレス SQL プールでは利用できません。
• ネイティブ外部テーブル。各種データ形式 (CSV、Parquet など) のデータの読み取りとエクスポートに使用できます。 ネイティブ外部テーブルは、サーバーレス SQL プールで利用可能であり、専用 SQL プールではパブリック プレビュー段階です。 CETAS とネイティブ外部テーブルを使用したデータの書き込み/エクスポートは、サーバーレス SQL プールでのみ使用できますが、専用 SQL プールでは使用できません。

Hadoop 外部テーブルとネイティブ外部テーブルの主な違い:

外部テーブルの種類	Hadoop	ネイティブ
専用 SQL プール	利用可能	Parquet テーブルは、パブリック プレビューでのみ利用可能です。
サーバーレス SQL プール	使用不可	利用可能
サポートされるフォーマット	区切り形式 (CSV)、Parquet、ORC、Hive RC、RC	サーバーレス SQL プール: 区切り形式 (CSV)、Parquet、Delta Lake 専用 SQL プール: Parquet (プレビュー)
フォルダー パーティションの除外	No	パーティションの除外は、Apache Spark プールから同期される Parquet 形式または CSV 形式で作成されたパーティション テーブルでのみ使用できます。 Parquet パーティション フォルダー上の外部テーブルを作成することはできますが、パーティション分割列はアクセス不能になり、無視されます。一方、パーティションの除外は適用されません。 Delta Lake フォルダー上の外部テーブルは作成しないでください。これらはサポートされていないためです。 パーティション分割された Delta Lake データのクエリを実行する必要がある場合は、Delta パーティション ビューを使用します。
ファイルの削除 (述語プッシュダウン)	No	Yes (サーバーレス SQL プールの場合)。 文字列のプッシュダウンでは、VARCHAR 列で Latin1_General_100_BIN2_UTF8 の照合順序を使用してプッシュダウンを有効にする必要があります。 照合順序の詳細については、「Synapse SQL でサポートされる照合順序の種類」を参照してください。
場所のカスタム形式	No	はい。Parquet 形式または CSV 形式に /year=*/month=*/day=* などのワイルドカードを使用します。 カスタム フォルダー パスは Delta Lake では使用できません。 サーバーレス SQL プールでは、再帰的なワイルドカード /logs/** を使用して、参照先フォルダーの下にある任意のサブフォルダー内の Parquet ファイルまたは CSV ファイルを参照することもできます。
再帰的フォルダー スキャン	はい	はい。 サーバーレス SQL プールでは、ロケーション パスの末尾に指定する必要があります (/**)。 専用プールでは、フォルダーは常に再帰的にスキャンされます。
ストレージ認証	ストレージ アクセス キー (SAK)、Microsoft Entra パススルー、マネージド ID、カスタム アプリケーションの Microsoft Entra ID	Shared Access Signature (SAS)、Microsoft Entra パススルー、マネージド ID、カスタム アプリケーションの Microsoft Entra ID。
列マッピング	序数 - 外部テーブル定義の列は、基になる Parquet ファイル内の列に位置によってマップされます。	サーバーレス プール: 名前によります。 外部テーブル定義の列は、基になる Parquet ファイル内の列に列名の一致によってマップされます。 専用プール: 序数の一致。 外部テーブル定義の列は、基になる Parquet ファイル内の列に位置によってマップされます。
CETAS (エクスポート/変換)	はい	ターゲットとしてネイティブ テーブルを使用する CETAS は、サーバーレス SQL プールでのみ機能します。 ネイティブ テーブルを使用してデータをエクスポートするために、専用 SQL プールを使用することはできません。

注意

ネイティブ外部テーブルは、一般提供されているプール内で推奨されるソリューションです。 外部データにアクセスする必要がある場合は、常にサーバーレス プールでネイティブ テーブルを使用してください。 専用プールでは、GA に入った後に Parquet ファイルを読み取るためにネイティブ テーブルに切り替える必要があります。 Hadoop テーブルは、ネイティブ外部テーブル (ORC、RC など) でサポートされていない一部の型にアクセスする必要がある場合、またはネイティブ バージョンが使用できない場合にのみ使用します。

専用 SQL プールとサーバーレス SQL プールにおける外部テーブル

外部テーブルを使用して次のことができます。

• Transact-SQL ステートメントを使用して、Azure Blob Storage と Azure Data Lake Gen2 に対するクエリを実行する。
• CETAS を使用して、クエリ結果を Azure Blob Storage または Azure Data Lake Storage 内のファイルに格納する。
• Azure Blob Storage や Azure Data Lake Storage からデータをインポートして、専用 SQL プールに格納する (専用プールでは Hadoop テーブルのみ)。

Note

CREATE TABLE AS SELECT ステートメントと組み合わせて使用する場合は、外部テーブルから選択するとデータが専用 SQL プール内のテーブルにインポートされます。

専用プール内の Hadoop 外部テーブルのパフォーマンスがパフォーマンス目標を達成できない場合は、COPY ステートメントを使用して Datawarehouse テーブルに外部データを読み込むことを検討してください。

読み込みのチュートリアルについては、PolyBase を使用した Azure Blob Storage からのデータの読み込みに関するページを参照してください。

次の手順を通じて、Synapse SQL プールに外部テーブルを作成できます。

1. CREATE EXTERNAL DATA SOURCE で、外部 Azure ストレージを参照し、ストレージへのアクセスに使用する資格情報を指定します。
2. CREATE EXTERNAL FILE FORMAT で、CSV または Parquet ファイルの形式を記述します。
3. CREATE EXTERNAL TABLE を、データ ソースに配置されているファイル上で、同じファイル形式を使用して実行します。

フォルダー パーティションの除外

Synapse プール内のネイティブ外部テーブルでは、クエリに関連しないフォルダーに配置されたファイルを無視できます。 ファイルがフォルダー階層 (例: /year=2020/month=03/day=16) に格納され、year、month、dayの値が列として公開されている場合、year=2020 のようなフィルターを含むクエリでは、year=2020 フォルダー内に配置されたサブフォルダーからのみファイルが読み取られます。 このクエリでは、他のフォルダー (year=2021 や year=2022) に配置されたファイルとフォルダーは無視されます。 この除外はパーティションの除外と呼ばれます。

フォルダー パーティションの除外は、Synapse Spark プールから同期されるネイティブ外部テーブルで使用できます。 データ セットをパーティション分割し、作成した外部テーブルでパーティションの除外を活用する場合は、外部テーブルではなくパーティション ビューを使用します。

ファイルの除外

Parquet や Delta などの一部のデータ形式には、各列のファイル統計 (各列の最小/最大値など) が含まれます。 データをフィルター処理するクエリでは、必要な列値が存在しないファイルは読み取られません。 クエリでは、まず、クエリ述語で使用される列の最小/最大値を調べ、必要なデータを含んでいないファイルを検索します。 これらのファイルは無視され、クエリ プランから除外されます。 この手法はフィルター述語のプッシュダウンとも呼ばれ、クエリのパフォーマンスを向上させることができます。 フィルター プッシュダウンは、Parquet および Delta 形式のサーバーレス SQL プールで使用できます。 文字列型のフィルター プッシュダウンを利用するには、Latin1_General_100_BIN2_UTF8 の照合順序と一緒に VARCHAR 型を使用します。 照合順序の詳細については、「Synapse SQL でサポートされる照合順序の種類」を参照してください。

セキュリティ

ユーザーは、外部テーブルのデータを読み取る場合、それに対する SELECT アクセス許可が必要です。 外部テーブルから、基になる Azure Storage へのアクセスは、次の規則を使用してデータソース内で定義されているデータベース スコープ資格情報を使用して行います。

• 資格情報なしのデータソースの場合、外部テーブルからは、Azure Storage 上の一般公開されているファイルにアクセスできます。
• SAS トークンまたはワークスペースのマネージド ID を使用して外部テーブルが Azure Storage 上のファイルにしかアクセスできないようにするための資格情報をデータソースに含めることができます。例については、ストレージ ファイルのストレージ アクセス制御の開発に関する記事を参照してください。

CREATE EXTERNAL DATA SOURCE の例

Hadoop

次の例では、New York データ セットを参照する Azure Data Lake Gen2 の Hadoop 外部データ ソースを専用 SQL プールに作成します。

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
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'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

次の例では、一般公開されている New York データ セットを参照する Azure Data Lake Gen2 の外部データ ソースを作成します。

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

ネイティブ

次の例では、SAS 資格情報を使用してアクセスできる Azure Data Lake Gen2 のサーバーレスまたは専用 SQL プールに外部データ ソースを作成します。

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'
GO
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

注意

SQL ユーザーが、Azure Synapse Analytics サーバーレス SQL プールのデータ ソースにアクセスするには、データベース スコープ資格情報の適切なアクセス許可が必要です。 Azure Synapse Analytics のサーバーレス SQL プールを使用して外部ストレージにアクセスする。 次の例では、一般公開されている New York データ セットを参照する Azure Data Lake Gen2 の外部データ ソースを作成します。

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

CREATE EXTERNAL FILE FORMAT の例

次の例では、国勢調査ファイルの外部ファイル形式を作成します。

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

CREATE EXTERNAL TABLE の例

次の例では、外部テーブルを作成します。 最初の行が返されます。

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Azure Data Lake 内のファイルから外部テーブルを作成してクエリを実行する

Synapse Studio の Data Lake 探索機能を使用すると、ファイルを右クリックするだけで、Synapse SQL プールを使って外部テーブルを作成してクエリを実行できるようになります。 ADLS Gen2 ストレージ アカウントから外部テーブルを作成するワンクリック ジェスチャーは、Parquet ファイルでのみサポートされます。

前提条件

• 少なくとも、ファイルに対してクエリを実行できるよう、ADLS Gen2 アカウントまたはアクセス制御リスト (ACL) への Storage Blob Data Contributor アクセス ロールが設定されたワークスペースにアクセスできる必要があります。

• Synapse SQL プール (専用またはサーバーレス) で外部テーブルを作成するためのアクセス許可と外部テーブルに対してクエリを実行するためのアクセス許可が少なくとも必要です。

[データ] パネルで、外部テーブルの作成元にするファイルを選択します。

ダイアログ ウィンドウが開きます。 専用 SQL プールかサーバーレス SQL プールを選択し、テーブルに名前を付けて [スクリプトを開く] を選択します。

SQL スクリプトは、ファイルからのスキーマの推論により自動的に生成されます。

スクリプトを実行します。 このスクリプトでは、Select Top 100 *. が自動的に実行されます。

外部テーブルが作成されました。今後この外部テーブルの内容を探索するために、ユーザーは [データ] ペインから直接クエリを実行できます。

次のステップ

クエリの結果を Azure Storage の外部テーブルに保存する方法については、CETAS に関する記事をご覧ください。 また、Azure Synapse 外部テーブルの Apache Spark に対するクエリを開始することもできます。