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

[アーティクル]
04/06/2023

外部テーブルは、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 プールに外部テーブルを作成できます。

CREATE EXTERNAL DATA SOURCE で、外部 Azure ストレージを参照し、ストレージへのアクセスに使用する資格情報を指定します。
CREATE EXTERNAL FILE FORMAT で、CSV または Parquet ファイルの形式を記述します。
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 上のファイルにしかアクセスできないようにするための資格情報をデータソースに含めることができます。例については、ストレージファイルのストレージアクセス制御の開発に関する記事を参照してください。

次の例では、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 プール (専用またはサーバーレス) で外部テーブルを作成するためのアクセス許可と外部テーブルに対してクエリを実行するためのアクセス許可が少なくとも必要です。

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

externaltable1