CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

[アーティクル]
07/26/2023

適用対象: SQL Server 2022 (16.x) 以降 Azure Synapse Analytics Analytics Platform System (PDW)

外部テーブルを作成し、次に Transact-SQL SELECT ステートメントの結果を並行してエクスポートします。

Azure Synapse Analytics と Analytics Platform System では、Hadoop または Azure Blob Storage がサポートされています。
SQL Server 2022 (16.x) 以降のバージョンでは、外部テーブルを作成し、Transact-SQL SELECT ステートメントの結果を Azure Data Lake Storage (ADLS) Gen2、Azure Storage Account V2、S3 互換オブジェクトストレージに並行してエクスポートする CREATE EXTERNAL TABLE AS SELECT (CETAS) がサポートされています。

注意

Azure SQL Managed Instance 用の CETAS の機能とセキュリティは、SQL Server または Azure Synapse Analytics とは異なります。詳細については、CREATE EXTERNAL TABLE AS SELECT の Azure SQL Managed Instance バージョンを参照してください。

注意

Azure Synapse Analytics のサーバーレスプール用の CETAS の機能とセキュリティは、SQL Server とは異なります。詳細については、「Synapse SQL での CETAS」を参照してください。

Transact-SQL 構文表記規則

構文

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

引数

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

データベースで作成するテーブルの 1 つから 3 つの部分で構成される名前。外部テーブルの場合、リレーショナルデータベースにはテーブルメタデータのみが格納されます。

[ ( column_name [ ,...n ] ) ]

テーブルの列の名前です。

LOCATION

適用対象: Azure Synapse Analytics および Analytics Platform System

'hdfs_folder'**
外部データソースで SELECT ステートメントの結果を書き込む場所を指定します。場所はフォルダー名であり、必要に応じて、Hadoop クラスターまたは BLOB ストレージのルートフォルダーへの相対パスを含めることができます。 PolyBase ではパスとフォルダー (まだ存在しない場合) が作成されます。

外部ファイルは hdfs_folder に書き込まれ、QueryID_date_time_ID.format という名前が付けられます (ID は増分識別子、format はエクスポートされるデータ形式)。たとえば QID776_20160130_182739_0.orc です。

LOCATION は、フォルダーを指す必要があり、末尾に / を付ける必要があります (例: aggregated_data/)。

適用対象: SQL Server 2022 (16.x) 以降

prefix://path[:port] には、SELECT ステートメントの結果が書き込まれる外部データソースに対する接続プロトコル (プレフィックス)、パス、ポート (省略可能) を指定します。

宛先が S3 互換のオブジェクトストレージである場合、まずバケットが存在する必要がありますが、必要に応じて PolyBase でサブフォルダーを作成することができます。 SQL Server 2022 (16.x) は、Azure Data Lake Storage Gen2、Azure Storage Account V2、S3 互換のオブジェクトストレージをサポートします。現在、ORC ファイルはサポートされていません。

DATA_SOURCE = external_data_source_name

外部データが格納されている、または格納される場所を含む、外部データソースオブジェクトの名前を指定します。場所は、Hadoop クラスターまたは Azure Blob ストレージのいずれかです。外部データソースを作成するには、CREATE EXTERNAL DATA SOURCE (Transact-SQL) を使用します。

FILE_FORMAT = external_file_format_name

外部データファイルの形式を含む、外部ファイル形式オブジェクトの名前を指定します。外部ファイル形式を作成するには、CREATE EXTERNAL FILE FORMAT (Transact-SQL) を使用します。

REJECT オプション

この CREATE EXTERNAL TABLE AS SELECT ステートメントの実行時に、REJECT オプションは適用されません。代わりに、ここで指定し、後で外部テーブルからデータをインポートする際に、データベースで使用できるようにします。後で CREATE TABLE AS SELECT ステートメントで外部テーブルからデータを選択するときに、データベースでは拒否オプションを使用して、インポートを停止するまでにインポートの失敗が許容される行の数または割合を決定します。

REJECT_VALUE = reject_value

データベースがインポートを停止するまでに、インポートの失敗が許容される行数の値または割合を指定します。
REJECT_TYPE = value | percentage

REJECT_VALUE オプションがリテラル値か割合かを明確にします。
- value
  
  REJECT_VALUE が割合ではなくリテラル値の場合に使われます。失敗した行の数が reject_value を超えた場合、データベースは外部データファイルからの行のインポートを停止します。
  
  たとえば、REJECT_VALUE = 5、REJECT_TYPE = value の場合、データベースは 5 つの行のインポートに失敗した後、行のインポートを停止します。
- percentage
  
  REJECT_VALUE がリテラル値ではなく割合の場合に使われます。失敗した行の percentage が reject_value を超えた場合、データベースは外部データファイルからの行のインポートを停止します。失敗した行の割合は、間隔をおいて計算されます。 TYPE=HADOOP の場合は、専用 SQL プールでのみ有効です。
REJECT_SAMPLE_VALUE = reject_sample_value

REJECT_TYPE = percentage の場合に必要です。データベースが失敗した行の割合を再計算する前に、インポートを試みる行の数を指定します。

たとえば、REJECT_SAMPLE_VALUE = 1000 の場合、データベースは外部データファイルから 1000 行のインポートを試みた後、失敗した行の割合を計算します。失敗した行の割合が reject_value 未満の場合、データベースは別の 1000 行の読み込みを試みます。データベースは引き続き、その 1000 行のそれぞれのインポートを試みた後、失敗した行の割合を再計算します。

Note

データベースは一定の間隔で失敗した行の割合を計算するため、失敗した行の実際の割合が reject_value を超える場合があります。

例:

この例は、REJECT の 3 つのオプションが相互にどのように作用するかを示しています。たとえば、REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100 の場合、次のシナリオが発生する可能性があります。
- データベースでは、最初の 100 行の読み込みが試行されます。この場合、失敗が 25、成功が 75 です。
- 失敗した行の割合は 25% と計算されます。これは reject 値である 30% を下回っています。そのため、読み込みを停止する必要はありません。
- データベースでは、次の 100 行の読み込みが試行されます。今回は成功が 25、失敗が 75 です。
- 失敗した行の割合が 50% として再計算されます。失敗した行の割合が、30% という reject 値を超えました。
- 200 行の読み込みを試みた後、失敗した行の割合が 50% で読み込みに失敗しています。この割合は、指定された制限の 30% を超えています。

WITH common_table_expression

共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。詳細については、「WITH common_table_expression (Transact-SQL)」を参照してください

SELECT <select_criteria>

SELECT ステートメントの結果を新しいテーブルに追加します。 select_criteria は、新しいテーブルにコピーするデータを決定する SELECT ステートメントの本文です。 SELECT ステートメントについては、「SELECT (Transact-SQL)」を参照してください。

Note

SELECT の ORDER BY 句は CETAS には影響しません。

列のオプション

column_name [ ,...n ]

列名では、CREATE TABLE に示されている列のオプションは使用できません。代わりに、新しいテーブルには 1 つ以上の列名のオプションリストを指定できます。新しいテーブルの列では、指定した名前が使用されます。列名を指定する場合、列リスト内の列の数は、SELECT の結果内の列の数と一致する必要があります。列名を指定しない場合、新しいターゲットテーブルでは、SELECT ステートメントの結果の列名が使用されます。

データ型、照合順序、NULL 値の許容など、他の列オプションを指定することはできません。これらの各属性は、SELECT ステートメントの結果から派生されます。ただし、SELECT ステートメントを使用して、属性を変更することができます。例については、CETAS を使用した列属性の変更に関するページを参照してください。

アクセス許可

このコマンドを実行するには、データベースユーザーは以下のすべての権限またはメンバーシップが必要です。

db_ddladmin 固定データベースロールの新しいテーブルまたはメンバーシップを含む、ローカルスキーマに対する ALTER SCHEMA 権限。
db_ddladmin 固定データベースロールの CREATE TABLE 権限またはメンバーシップ。
select_criteria で参照されるすべてのオブジェクトに対する SELECT 権限。

ログインには次のすべての権限が必要です。

ADMINISTER BULK OPERATIONS
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT
一般に、CETAS のためには、フォルダーの内容を一覧表示し、LOCATION フォルダーに書き込むためのアクセス許可が必要です。
Azure Synapse Analytics と Analytics Platform System の場合、Hadoop クラスターまたは Azure BLOB ストレージ上の外部フォルダーに対して読み取りと書き込みを行う書き込みアクセス許可。
SQL Server 2022 (16.x) では、外部の場所に対して適切なアクセス許可を設定する必要もあります。その場所にデータを出力する書き込みアクセス許可と、それにアクセスする読み取りアクセス許可です。
Azure Blob Storage と Azure Data Lake Gen2 の場合、コンテナー上で SHARED ACCESS SIGNATURE トークンに閲覧、書き込み、一覧表示、作成の特権を付与する必要があります。
Azure Blog Storage の場合、SAS トークンを生成するには、Allowed Services: Blob チェックボックスをオンにする必要があります。
Azure Data Lake Gen2 の場合、SAS トークンを生成するには、Allowed Services: Container および Object チェックボックスをオンにする必要があります。

重要

ALTER ANY EXTERNAL DATA SOURCE 権限は、あらゆる外部データソースオブジェクトを作成し、変更する能力をプリンシパルに与えます。そのため、データベース上のすべてのデータベーススコープ資格情報にアクセスする能力も与えます。この権限は特権として考える必要があり、システム内の信頼できるプリンシパルにのみ与える必要があります。

エラー処理

CREATE EXTERNAL TABLE AS SELECT でデータをテキスト区切りファイルにエクスポートした場合、エクスポートに失敗した行に対する拒否ファイルはありません。

外部テーブルの作成時に、データベースによって、外部の場所への接続が試行されます。接続に失敗した場合、コマンドは失敗し、外部テーブルは作成されません。データベースは接続を 3 回以上再試行するため、コマンドが失敗するまで 1 分以上かかる場合があります。

CREATE EXTERNAL TABLE AS SELECT が取り消されたか、失敗した場合、データベースにより、外部データソースで既に作成されている新しいファイルとフォルダーの削除が 1 回だけ試みられます。

Azure Synapse Analytics と Analytics Platform System では、データのエクスポート時に外部データソースで発生した Java エラーがデータベースから報告されます。

注釈

CREATE EXTERNAL TABLE AS SELECT ステートメントが完了したら、外部テーブルに対して Transact-SQL クエリを実行できます。 CREATE TABLE AS SELECT ステートメントを使用してインポートする場合を除き、これらの操作ではクエリの実行中にデータをデータベースにインポートします。

外部テーブルの名前と定義は、データベースのメタデータに格納されます。データは外部データソースに格納されます。

CREATE EXTERNAL TABLE AS SELECT ステートメントを使用すると、ソーステーブルがパーティション分割されている場合でも、常にパーティション分割されていないテーブルが作成されます。

SQL Server 2022 (16.x) の場合、sp_configure を使用してオプション allow polybase export を有効にする必要があります。詳細については、「allow polybase export 構成オプションの設定」を参照してください。

Azure Synapse Analytics と Analytics Platform System のクエリプランを EXPLAIN を使って作成した場合、データベースは、外部シャッフル移動、外部ブロードキャスト移動、外部パーティション移動というクエリプラン操作を外部テーブルに対して使います。

Analytics Platform System では、外部テーブルを作成するための前提条件として、アプライアンス管理者は Hadoop 接続を構成する必要があります。詳細については、Analytics Platform System のドキュメントの外部データへの接続の構成 (Analytics Platform System) に関する記事を参照してください。このドキュメントは、Microsoft ダウンロードセンターからダウンロードできます。

制限事項と制約事項

外部テーブルデータがデータベースの外部にあるため、バックアップおよび復元操作はデータベースに格納されているデータに対してのみ動作します。その結果、メタデータのみがバックアップおよび復元されます。

データベースでは、外部テーブルを含むデータベースバックアップを復元する際に、外部データソースへの接続は確認されません。元のソースにアクセスできない場合でも、外部テーブルのメタデータの復元は成功します。ただし、外部テーブルに対する SELECT 操作は失敗します。

データベースでは、データベースと外部データ間のデータの一貫性は保証されません。外部データとデータベース間の一貫性を保つことだけは、お客様が行う必要があります。

外部テーブルでのデータ操作言語 (DML) 操作はサポートされていません。たとえば、Transact-SQL の UPDATE、INSERT、DELETE Transact-SQL ステートメントを使用して、外部データを変更することはできません。

外部テーブルで許可されるデータ定義言語 (DDL) 操作は、CREATE TABLE、DROP TABLE、CREATE STATISTICS、DROP STATISTICS、CREATE VIEW、DROP VIEW のみです。

Azure Synapse Analytics の制限事項と制約事項

Azure Synapse Analytics 専用 SQL プールと Analytics Platform System では、PolyBase クエリを同時に 32 個に実行した場合、PolyBase によってフォルダーごとに最大 33,000 個のファイルを消費できます。この最大数には、各 HDFS フォルダー内のファイルとサブフォルダーの両方が含まれます。コンカレンシーの度合いが 32 未満である場合、ユーザーは 33,000 より多いファイルが含まれている HDFS のフォルダーに対して PolyBase クエリを実行できます。 Hadoop および PolyBase のユーザーがファイルパスを短くし、HDFS フォルダーごとに 30,000 以下のファイルを使用することをお勧めします。参照されているファイルが多すぎると、JVM のメモリ不足例外が発生します。
サーバーレス SQL プールでは、外部テーブルを現在データがある場所に作成することはできません。データの格納に使用されている場所を再利用するには、その場所を ADLS で手動で削除する必要があります。その他の制限事項とベストプラクティスについては、フィルター最適化のベストプラクティスに関する記事を参照してください。

Azure Synapse Analytics 専用 SQL プールと Analytics Platform System では、CREATE EXTERNAL TABLE AS SELECT を使って RCFile から選ぶ場合、RCFile の列値にパイプ (|) 文字を含めることはできません。

CREATE EXTERNAL TABLE AS SELECT では SET ROWCOUNT (Transact-SQL) の効果はありません。同様の動作を実現するには、TOP (Transact-SQL) を使用します。

ファイル名の制限については、「コンテナー、BLOB、メタデータの名前付けと参照」を参照してください。

文字エラー

データ内に次の文字が存在する場合、Parquet ファイルに対して CREATE EXTERNAL TABLE AS SELECT を実行すると、レコードの拒否などのエラーが発生することがあります。

Azure Synapse Analytics と Analytics Platform System では、これは ORC ファイルにも適用されます。

|
" (引用符文字)
\r\n
\r
\n

これらの文字が含まれる CREATE EXTERNAL TABLE AS SELECT を使用するには、最初にデータに CREATE EXTERNAL TABLE AS SELECT ステートメントを実行してデータを区切りテキストファイルにエクスポートする必要があり、その後は外部ツールを使用して Parquet または ORC に変換することができます。

Parquet の操作

Parquet ファイルを使用する場合、 CREATE EXTERNAL TABLE AS SELECT は構成された最大並列度 (MAXDOP) まで、使用可能な CPU ごとに 1 つの Parquet ファイルが生成されます。各ファイルは最大 190 GB まで拡張できます。その後、SQL Server は必要に応じてより多数の Parquet ファイルを生成します。

クエリヒント OPTION (MAXDOP n) は、CREATE EXTERNAL TABLE AS SELECT の SELECT 部分にのみ影響し、Parquet ファイルの量には影響しません。データベースレベルの MAXDOP とインスタンスレベルの MAXDOP のみが考慮されます。

ロック

SCHEMARESOLUTION オブジェクトの共有ロックを取得します。

サポートされているデータ型

CETAS を使用して、次の SQL データ型の結果セットを格納できます。

[バイナリ]
varbinary
char
varchar
nchar
nvarchar
smalldate
date
DATETIME
datetime2
datetimeoffset
time
decimal
numeric
float
real
bigint
tinyint
smallint
INT
bigint
bit
money
smallmoney

例

A. CREATE EXTERNAL TABLE AS SELECT を使用して Hadoop テーブルを作成する

適用対象: Azure Synapse Analytics および Analytics Platform System

次の例では、ソーステーブル dimCustomer の列の定義とデータを使用して、hdfsCustomer という名前の新しい外部テーブルを作成します。

テーブル定義はデータベースに格納され、SELECT ステートメントの結果は、Hadoop 外部データソース customer_ds の /pdwdata/customer.tbl ファイルにエクスポートされます。ファイルは、外部ファイル形式 customer_ff に従って書式設定されます。

ファイル名はデータベースによって生成され、クエリ ID を含みます。これにより、ファイルを生成元のクエリに合わせやすくなります。

Customer ディレクトリの前のパス hdfs://xxx.xxx.xxx.xxx:5000/files/ は既に存在している必要があります。 Customer ディレクトリが存在しない場合は、データベースでそのディレクトリが作成されます。

Note

この例では 5000 に指定されています。ポートが指定されていない場合、データベースは既定のポートとして 8020 を使用します。

結果の Hadoop の場所とファイル名は hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt. です。

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. CREATE EXTERNAL TABLE AS SELECT でクエリヒントを使用する

適用対象: Azure Synapse Analytics および Analytics Platform System

このクエリは、CREATE EXTERNAL TABLE AS SELECT ステートメントでクエリ結合ヒントを使用する場合の基本的な構文を示しています。クエリが送信された後、データベースではハッシュ結合方法を使用して、クエリプランを生成します。結合ヒントと OPTION 句の使用方法の詳細については、「OPTION 句 (Transact-SQL)」を参照してください。

Note

この例では 5000 に指定されています。ポートが指定されていない場合、データベースは既定のポートとして 8020 を使用します。

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. CETAS を使用して列属性を変更する

適用対象: Azure Synapse Analytics および Analytics Platform System

この例では CETAS を使用して、FactInternetSales テーブルのデータ型、NULL 値の許容、いくつかの列の照合順序を変更します。

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. データを parquet としてエクスポートして CREATE EXTERNAL TABLE AS SELECT を使う

適用対象: SQL Server 2022 (16.x)

次の例では、AdventureWorks2022 のテーブル SalesOrderDetail のデータを使う ext_sales という新しい外部テーブルを作成します。 allow polybase export 構成オプションを有効にする必要があります。

SELECT ステートメントの結果は、あらかじめ構成した S3 互換のオブジェクトストレージに保存され、s3_eds という名前が付けられ、s3_dsc という適切な資格情報が作成されます。 parquet ファイルの場所は <ip>:<port>/cetas/sales.parquet になります。この cetas は以前に作成したストレージバケットです。

注意

現在、Delta の形式は読み取り専用としてのみサポートされています。

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. delta テーブルから parquet に対して CREATE EXTERNAL TABLE AS SELECT を使う

適用対象: SQL Server 2022 (16.x)

次の例では、s3_delta という S3 互換のオブジェクトストレージにある Delta テーブル型のデータを使う、Delta_to_Parquet という新しい外部テーブルを作成し、その結果を s3_parquet という別のデータソースに parquet ファイルとして書き込みます。そのために、この例では OPENROWSET コマンドを利用しています。 allow polybase export 構成オプションを有効にする必要があります。

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. ビューをソースとして使用する CREATE EXTERNAL TABLE AS SELECT を使う

適用対象: Azure Synapse Analytics サーバーレス SQL プールと専用 SQL プール。

この例では、ユーザー定義ビューをソースとして CETAS を記述し、マネージド ID を認証として使用し、 wasbs:するためのテンプレートコードの例を確認できます。

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'wasbs://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

G. ビューをソースとして使用する CREATE EXTERNAL TABLE AS SELECT を使う

適用対象: Azure Synapse Analytics サーバーレス SQL プールと専用 SQL プール。

この例では、ユーザー定義ビューをソースとして CETAS を記述し、マネージド ID を認証として使用し、 https:するためのテンプレートコードの例を確認できます。

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

次の手順

適用対象: Azure SQL Managed Instance

外部テーブルを作成し、次に Transact-SQL SELECT ステートメントの結果を並行してエクスポートします。

CREATE EXTERNAL TABLE AS SELECT (CETAS) を使用すると、次のタスクを実行できます。

Azure Blob Storage または Azure Data Lake Storage (ADLS) Gen2 の Parquet または CSV ファイルの上に外部テーブルを作成する。
作成された外部テーブルに T-SQL SELECT ステートメントの結果を並行してエクスポートする。
Azure SQL Managed Instance のデータの仮想化機能について詳しくは、「Azure SQL Managed Instance によるデータ仮想化」を参照してください。

注意

このコンテンツは、Azure SQL Managed Instance のみに適用されます。他のプラットフォームについては、ドロップダウンセレクターから適切なバージョンの CREATE EXTERNAL TABLE AS SELECT を選択してください。

Transact-SQL 構文表記規則

構文

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>

引数

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

作成するテーブルの 1 つから 3 つの部分で構成される名前。外部テーブルの場合、テーブルのメタデータのみが格納されます。実際のデータは移動されることも格納されることもありません。

LOCATION = 'path_to_folder'

外部データソースで SELECT ステートメントの結果を書き込む場所を指定します。ルートフォルダーは、外部データソースで指定されたデータの場所です。 LOCATION は、フォルダーを指す必要があり、末尾に / を付ける必要があります。例: aggregated_data/.

CETAS の宛先フォルダーは空である必要があります。パスおよびフォルダーがまだ存在しない場合は自動的に作成されます。

DATA_SOURCE = external_data_source_name

外部データが格納される場所を含む外部データソースオブジェクトの名前を指定します。外部データソースを作成するには、CREATE EXTERNAL DATA SOURCE (Transact-SQL) を使用します。

FILE_FORMAT = external_file_format_name

外部データファイルの形式を含む、外部ファイル形式オブジェクトの名前を指定します。外部ファイル形式を作成するには、CREATE EXTERNAL FILE FORMAT (Transact-SQL) を使用します。現在サポートされている外部ファイル形式は、FORMAT_TYPE=PARQUET と FORMAT_TYPE=DELIMITEDTEXT だけです。 DELIMITEDTEXT 形式に対する GZip 圧縮はサポートされません。

[, PARTITION ( column name [ , ...n ] ) ]

出力データを複数の parquet ファイルパスにパーティション分割します。パーティション分割は、特定の列 (column_name) ごとに実行され、LOCATION のワイルドカード (*) をそれぞれのパーティション分割列に一致させます。 PARTITION 部分の列数は、LOCATION 内のワイルドカードの数と一致する必要があります。パーティション分割に使用されない列が少なくとも 1 つは必要です。

WITH <common_table_expression>

共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。詳細については、「WITH common_table_expression (Transact-SQL)」を参照してください。

SELECT <select_criteria>

Note

SELECT での ORDER BY 句は、CETAS ではサポートされていません。

アクセス許可

ストレージのアクセス許可

CETAS を機能させるには、フォルダーの内容を一覧表示するため、および LOCATION パスに書き込むためのアクセス許可が必要です。

サポートされている認証方法は、マネージド ID または Shared Access Signature (SAS) トークンです。

認証にマネージド ID を使用している場合は、SQL マネージドインスタンスのサービスプリンシパルに、宛先コンテナーに対する Storage BLOB データ共同作成者 のロールがあることを確認します。
SAS トークンを使用する場合は、読み取り、書き込み、リスト のアクセス許可が必要です。
Azure Blog Storage の場合、SAS トークンを生成するには、Allowed Services: Blob チェックボックスをオンにする必要があります。
Azure Data Lake Gen2 の場合、SAS トークンを生成するには、Allowed Services: Container および Object チェックボックスをオンにする必要があります。

ユーザー割り当てマネージド ID はサポートされていません。 Microsoft Entra パススルー認証はサポートされていません。 Microsoft Entra ID は (形式の Azure Active Directory)。

SQL マネージドインスタンスのアクセス許可

このコマンドを実行するには、データベースユーザーは以下のすべての権限またはメンバーシップが必要です。

db_ddladmin 固定データベースロールの新しいテーブルまたはメンバーシップを含む、ローカルスキーマに対する ALTER SCHEMA 権限。
db_ddladmin 固定データベースロールの CREATE TABLE 権限またはメンバーシップ。
select_criteria で参照されるすべてのオブジェクトに対する SELECT 権限。

ログインには次のすべての権限が必要です。

ADMINISTER BULK OPERATIONS
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT

重要

サポートされるデータ型

CETAS では、次の SQL データ型の結果セットが格納されます。

binary
varbinary
char
varchar
nchar
nvarchar
smalldatetime
date
DATETIME
datetime2
datetimeoffset
time
decimal
numeric
float
real
bigint
tinyint
smallint
INT
bigint
bit
money
smallmoney

Note

1 MB を超える LOB を CETAS と共に使用することはできません。

制限事項と制約事項

Azure SQL Managed Instance 用の CREATE EXTERNAL TABLE AS SELECT (CETAS) は、既定では無効になっています。詳細については、次のセクション「既定での無効化」を参照してください。
Azure SQL Managed Instance でのデータ仮想化に関する制限事項または既知の問題の詳細については、制限事項と既知の問題に関するページを参照してください。

データベースでは、外部テーブルを含むデータベースバックアップを復元する際に、外部データソースへの接続は確認されません。元のソースにアクセスできない場合でも、外部テーブルのメタデータの復元は正常に行われますが、外部テーブルでの SELECT 操作は失敗します。

外部テーブルで許可されるデータ定義言語 (DDL) 操作は、CREATE TABLE、DROP TABLE、CREATE STATISTICS、DROP STATISTICS、CREATE VIEW、DROP VIEW のみです。

外部テーブルを現在データがある場所に作成することはできません。データの格納に使用されている場所を再利用するには、その場所を ADLS で手動で削除する必要があります。

CREATE EXTERNAL TABLE AS SELECT では SET ROWCOUNT (Transact-SQL) の効果はありません。同様の動作を実現するには、TOP (Transact-SQL) を使用します。

ファイル名の制限については、「コンテナー、BLOB、メタデータの名前付けと参照」を参照してください。

ストレージの種類

ファイルは、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>.blob.core.windows.net/<path>/<file_name>.parquet

重要

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

既定で無効

CREATE EXTERNAL TABLE AS SELECT (CETAS) を使用すると、SQL マネージドインスタンスから外部ストレージアカウントにデータをエクスポートできるため、これらの機能によってデータ流出のリスクが生じる可能性があります。このため、Azure SQL Managed Instance では CETAS が既定で無効になっています。

CETAS を有効にする

Azure SQL Managed Instance 用の CETAS は、昇格された Azure アクセス許可を必要とするメソッドを介してのみ有効にすることができ、T-SQL を介して有効にすることはできません。許可されていないデータ流出のリスクがあるため、sp_configure T-SQL ストアドプロシージャを介して CETAS を有効にすることはできず、代わりに SQL マネージドインスタンスの外部でユーザーアクションを実行する必要があります。

CETAS を有効にするためのアクセス許可

Azure PowerShell を使用して有効にするには、コマンドを実行するユーザーに、SQL マネージドインスタンスの Contributor または SQL Security Manager Azure RBAC ロールが必要です。

このためのカスタムロールを作成することもできます。これには、Microsoft.Sql/managedInstances/serverConfigurationOptions アクションの読み取りおよび書き込みアクションが必要です。

CETAS を有効にするためのメソッド

コンピューターで PowerShell コマンドを呼び出すには、Az パッケージバージョン 9.7.0 以降をローカルでインストールする必要があります。または、Azure Cloud Shell を使用して shell.azure.com で Azure PowerShell を実行することを検討してください。

まず、Azure にログインし、サブスクリプションに適切なコンテキストを設定します。

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

サーバー構成オプション "allowPolybaseExport" を管理するには、次の PowerShell スクリプトをお使いのサブスクリプションと SQL マネージドインスタンス名に合わせて調整し、コマンドを実行します。詳細については、「Set-AzSqlServerConfigurationOption」および「Get-AzSqlServerConfigurationOption」を参照してください。

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

サーバー構成オプション "allowPolybaseExport" を無効にするには、次のスクリプトを使用します。

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

サーバー構成オプション "allowPolybaseExport" の現在の値を取得するには、次のスクリプトを使用します。

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

Azure CLI を使用してサーバー構成オプション "allowPolybaseExport" を管理することができます。

まず、Windows コマンドプロンプト (CMD) または PowerShell のいずれかを開き、Azure にサインインします。必要に応じて、お使いのサブスクリプションに切り替えます。

az login
$subscriptionId = "<your subscription ID>"
az account set --subscription $subscriptionId

az sql mi server-configuration-option set を使用して、"allowPolybaseExport" という名前のサーバー構成オプションを有効にします。

# Enable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '1'

サーバー構成オプション "allowPolybaseExport" を無効にするには、次のスクリプトを使用します。

# Disable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '0'

サーバー構成オプション "allowPolybaseExport" の現在の値を取得するには、次のスクリプトを使用します。

# Get server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option show --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport'

Azure CLI 引数のエイリアス名:

短いバージョン	エイリアス
-g	--resource-group
--mi	--instance-name --managed-instance --managed-instance-name
-n	--name --server-configuration-option-name
--value	--server-configuration-option-value

Azure Cloud Shell を使用して汎用 API コマンドを呼び出すと、allowPolybaseExport サーバー構成オプションを 1 に切り替えることができます。

SQL マネージドインスタンスで CETAS を有効にするには、次の Azure PowerShell スクリプト変数をお使いのサブスクリプションと SQL マネージドインスタンス名に合わせて調整し、Azure Cloud Shell で実行します。

Note

CETAS の構成または検証に関する次の Azure Cloud Shell の手順では、全体を通して同じ PowerShell コンソールを使用します。ステータスを確認するためのその後のスクリプトは、元のスクリプトによって設定されたコンテキストによって異なります。

# Enter your Azure subscription ID
$SubscriptionID = "<YourSubscriptionIDHERE>"

# Enter your managed instance name – for example, "sqlmi1"
$ManagedInstanceName = "<YourManagedInstanceNameHere>"

# ===============================================================================
# INVOKING THE API CALL -- REST OF THE SCRIPT IS NOT USER CONFIGURABLE
# ===============================================================================
# Log in and select a subscription if needed.

if ((Get-AzContext ) -eq $null)
{
    echo "Logging to Azure subscription"
    Login-AzAccount
}
Select-AzSubscription -SubscriptionName $SubscriptionID

# Build the URI for the API call.
$resource_group_name = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName
$uriFull = "https://management.azure.com/subscriptions/" + $SubscriptionID + "/resourceGroups/" + $resource_group_name+ "/providers/Microsoft.Sql/managedInstances/" + $ManagedInstanceName + "/serverConfigurationOptions/allowPolybaseExport" + "?api-version=2022-08-01-preview"

# Build the API request body.
$properties = @{serverConfigurationOptionValue = 1}
$bodyFull = @{properties = $properties}
$jsonBody = $bodyFull | ConvertTo-Json

# Get auth token and build the HTTP request header.
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$currentAzureContext = Get-AzContext
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azProfile)
$token = $profileClient.AcquireAccessToken($currentAzureContext.Tenant.TenantId)
$authToken = $token.AccessToken
$headers = @{}
$headers.Add("Authorization", "Bearer "+"$authToken")

# Invoke API call. API call creates an Asynchronous operation that will change the configuration setting. 
# In rare cases that your SQL MI is down when the async operation will run, the async operation may fail.
# You can check the status of the Async operation using the callback URL in the response header.
# Look for response header parameter: Azure-AsyncOperation.
Invoke-WebRequest -Method PUT -Headers $headers -Uri $uriFull -ContentType "application/json" -Body $jsonBody

Note

API を呼び出してサーバー構成オプションを設定する操作は非同期です。この非同期操作は迅速に実行され、数秒以内に完了すると予想されますが、さまざまな理由 (操作の実行時に SQL マネージドインスタンスがダウンしていた場合など) で実行が失敗する可能性があります。このため、構成オプションの変更結果を確認することが重要です。

API 呼び出しの非同期操作の結果を確認するには、次の 2 つの方法があります。

重要

次のスクリプトを実行する際には、構成オプションを設定するスクリプトを実行したときと同じ Azure Cloud Shell コンソールを使用してください。このスクリプトは、元の Invoke-WebRequest スクリプトによって設定されたコンテキストによって異なります。

PowerShell を使用して、サーバー構成 API に対して HTTP GET を呼び出し、構成オプションに必要な値が含まれているかどうかを確認します。次の PowerShell スクリプトは、GET 要求を呼び出してサーバー構成オプションを確認する方法を示しています。応答 JSON を探し、serverConfigurationOptionValue を見つけて、前の手順で設定した内容に対応していることを確認します。スクリプトは元のスクリプトによって設定されたコンテキストに依存するため、スクリプトを実行したときと同じ PowerShell コンソールを使用して構成オプションを設定します。
```
# You can query the configuration option directly to see if it contains the new changed value.
# Get the configuration status using the call below. This will return JSON with 
# serverConfigurationOptionValue variable value. 
# It should match what you provided in your original request:
Invoke-WebRequest -Method GET -Headers $headers -Uri $uriFull -ContentType "application/json" 
```

PowerShell を使用して、非同期操作のコールバック URL に対して HTTP GET を呼び出し、応答 JSON を読み取って "status" 変数とその値を見つけます。 API を呼び出してサーバー構成の変更を実行すると、応答ヘッダーにコールバック URL が含まれます。これを使用して、非同期操作の結果と状態を確認することができます。ヘッダー値 Azure-AsyncOperation にはコールバック URL が含まれています。

応答ヘッダーの例を次に示します。

[Azure-AsyncOperation, https://management.azure.com/subscriptions/abcdefgh-abcd-abcd-abcd-subscription/resourceGroups/<ResourceGroupName>/providers/Microsoft.Sql/locations/eastus/serverConfigurationOptionAzureAsyncOperation/abcdefgh-abcd-abcd-abcd-abcdef123456?api-version=2022-08-01-preview]

次の PowerShell の例では、https:// で始まる応答ヘッダーのコールバック URL を使用しています。

# If you got the Azure-AsyncOperation URL from response header, 
# use the below to query the operation for status:
$AzAsyncOpURL = "https://management.azure.com/subscriptions/abcdefgh-abcd-abcd-abcd-subscription/resourceGroups/<ResourceGroupName>/providers/Microsoft.Sql/locations/eastus/serverConfigurationOptionAzureAsyncOperation/abcdefgh-abcd-abcd-abcd-abcdef123456?api-version=2022-08-01-preview"
Invoke-WebRequest -Method GET -Headers $headers -Uri $AzAsyncOpURL -ContentType "application/json"

CETAS の状態を確認する

CETAS 構成オプションの現在の状態は、いつでも確認できます。

SQL マネージドインスタンスに接続します。次の T-SQL を実行し、応答の value 列を確認します。サーバー構成の変更が完了した後、このクエリの結果が目的の設定と一致する必要があります。

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

トラブルシューティング

Azure SQL Managed Instance でのデータ仮想化をトラブルシューティングする手順の詳細については、「トラブルシューティング」を参照してください。 Azure SQL Managed Instance での CETAS のエラー処理と一般的なエラーメッセージは次のとおりです。

エラー処理

一般的なエラーメッセージ

以下の一般的なエラーメッセージには、Azure SQL Managed Instance 用の CETAS に関する簡単な説明が含まれています。

ストレージ内に既に存在する場所を指定している。

解決策: ストレージの場所 (スナップショットを含む) をクリアするか、クエリ内の場所のパラメーターを変更します。

サンプルのエラーメッセージ: Msg 15842: Cannot create external table. External table location already exists.
JSON オブジェクトを使用して書式設定された列値。

解決策: 値列を単一の VARCHAR または NVARCHAR 列に変換するか、型が明示的に定義された列のセットに変換します。

サンプルのエラーメッセージ: Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.
場所パラメーターが無効 (たとえば、複数の // など)。

解決策: 場所パラメーターを修正します。

サンプルのエラーメッセージ: Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.
必要なオプション (DATA_SOURCE、FILE_FORMAT、LOCATION) のいずれかがない。

解決策: 不足しているパラメーターを CETAS クエリに追加します。

サンプルのエラーメッセージ: Msg 46505: Missing required external DDL option 'FILE_FORMAT'
アクセスの問題 (無効の資格情報、期限切れの資格情報、またはアクセス許可が不十分な資格情報)。別の原因としては無効のパスが考えられます。この場合、SQL マネージドインスタンスはストレージからエラー 404 を受け取ります。

解決策: 資格情報の有効性とアクセス許可を確認します。または、パスが有効で、ストレージが存在しているかどうかを検証します。 URL パス adls://<container>@<storage_account>.blob.core.windows.net/<path>/ を使用します。

サンプルのエラーメッセージ: Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'
DATA_SOURCE の LOCATION 部部にワイルドカードが含まれている。

解決策: 場所からワイルドカードを削除します。

サンプルのエラーメッセージ: Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.
LOCATION パラメーターのワイルドカードの数と、パーティション分割された列の数が一致しない。

解決策: LOCATION のワイルドカードとパーティション列を必ず同数にします。

サンプルのエラーメッセージ: Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.
PARTITION 句の列名がリスト内のどの列とも一致しない。

解決策: PARTITION 内の列が有効であることを確認します。

サンプルのエラーメッセージ: Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list
列が PARTITION リストで複数回指定されている。

解決策: PARTITION 句内の列が一意であることを確認します。

サンプルのエラーメッセージ: Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.
列が PARTITION リストで複数回指定されているか、SELECT リストの列と一致しない。

解決策: 必ず、PARTITION リストに重複が存在しないようにし、パーティション列が SELECT 部分に存在するようにします。

サンプルのエラーメッセージ: Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter.、Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.
PARTITION リスト内のすべての列が使用されている。

解決策: SELECT 部分の列の少なくとも 1 つは、クエリの PARTITION 部分に含めないようにする必要があります。

サンプルのエラーメッセージ: Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.
機能が無効である。

解決策: この記事の「既定での無効化」セクションを使用して、機能を有効にします。

サンプルのエラーメッセージ: Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

ロック

SCHEMARESOLUTION オブジェクトの共有ロックを取得します。

例

A. CETAS とビューを使用して外部テーブルを作成する (マネージド ID を使用する場合)

この例では、システムマネージド ID と認証を使用して、ソースとしてビューを含む CETAS を記述するコードを提供します。

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

B. CETAS とビューを使用して外部テーブルを作成する (SAS 認証を使用する場合)

この例では、SAS トークンを認証として使用し、ビューをソースとして使用する CETAS を記述するためのコードを提供します。

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = '<azure_shared_access_signature>' ;
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

C. ストレージ上の単一の parquet ファイルに外部テーブルを作成する

次の 2 つの例は、ローカルテーブルから、Azure Blob Storage コンテナーに parquet ファイルとして格納されている外部テーブルに、一部のデータをオフロードする方法を示しています。これらは、AdventureWorks2022 データベースで動作するように設計されています。この例は、外部テーブルを単一の parquet ファイルとして作成する方法を示しています。次の例は、外部テーブルを作成し、それを parquet ファイルを含む複数のフォルダーにパーティション分割する方法を示しています。

次の例では、認証にマネージド ID を使用しています。そのため、Azure SQL Managed Instance サービスプリンシパルに、Azure Blob Storage コンテナーに対するStorage BLOB データ共同作成者ロールがあることを確認してください。または、例を変更し、認証に Shared Access Secret (SAS) トークンを使用することもできます。

次の例では、SalesOrderHeader テーブルから 2014 年 1 月 1 日より前の注文を選択して、Azure Blob Storage 内の単一の Parquet ファイルに外部テーブルを作成します。

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );
GO

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

D. フォルダーツリーに格納されている複数の Parquet ファイルに、パーティション分割された外部テーブルを作成する

この例は、前の例を基にして、外部テーブルを作成し、parquet ファイルを含む複数のフォルダーにパーティション分割する方法を示しています。パーティションテーブルを使用すると、データセットが大きい場合にパフォーマンスを向上することができます。

例 B の手順を使用して SalesOrderHeader データから外部テーブルを作成しますが、外部テーブルを OrderDate の年と月でパーティション分割します。パーティション分割された外部テーブルに対してクエリを実行する場合、パーティションを削除すると、パフォーマンスを向上することができます。

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

次の方法で共有

CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

構文

引数

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

[ ( column_name [ ,...n ] ) ]

LOCATION

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

REJECT オプション

WITH common_table_expression

SELECT <select_criteria>

アクセス許可

エラー処理

注釈

制限事項と制約事項

Azure Synapse Analytics の制限事項と制約事項

文字エラー

Parquet の操作

ロック

サポートされているデータ型

例

A. CREATE EXTERNAL TABLE AS SELECT を使用して Hadoop テーブルを作成する

B. CREATE EXTERNAL TABLE AS SELECT でクエリ ヒントを使用する

C. CETAS を使用して列属性を変更する

D. データを parquet としてエクスポートして CREATE EXTERNAL TABLE AS SELECT を使う

E. delta テーブルから parquet に対して CREATE EXTERNAL TABLE AS SELECT を使う

F. ビューをソースとして使用する CREATE EXTERNAL TABLE AS SELECT を使う

G. ビューをソースとして使用する CREATE EXTERNAL TABLE AS SELECT を使う

次の手順

構文

引数

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

LOCATION = 'path_to_folder'

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

[, PARTITION ( column name [ , ...n ] ) ]

WITH <common_table_expression>

SELECT <select_criteria>

アクセス許可

ストレージのアクセス許可

SQL マネージド インスタンスのアクセス許可

サポートされるデータ型

制限事項と制約事項

ストレージの種類

既定で無効

CETAS を有効にする

CETAS を有効にするためのアクセス許可

CETAS を有効にするためのメソッド

CETAS の状態を確認する

トラブルシューティング

エラー処理

一般的なエラー メッセージ

ロック

例

A. CETAS とビューを使用して外部テーブルを作成する (マネージド ID を使用する場合)

B. CETAS とビューを使用して外部テーブルを作成する (SAS 認証を使用する場合)

C. ストレージ上の単一の parquet ファイルに外部テーブルを作成する

D. フォルダー ツリーに格納されている複数の Parquet ファイルに、パーティション分割された外部テーブルを作成する

次の手順

フィードバック

その他のリソース

B. CREATE EXTERNAL TABLE AS SELECT でクエリヒントを使用する

SQL マネージドインスタンスのアクセス許可

一般的なエラーメッセージ

D. フォルダーツリーに格納されている複数の Parquet ファイルに、パーティション分割された外部テーブルを作成する