CREATE EXTERNAL TABLE (Transact-SQL)

概要:SQL Server

このコマンドでは、Hadoop クラスターに格納されているデータに PolyBase でアクセスするための外部テーブルが作成されるか、Hadoop クラスターまたは Azure Blob Storage に格納されているデータを参照する Azure Blob Storage PolyBase 外部テーブルが作成されます。

適用対象: SQL Server 2016 (またはそれ以降)

PolyBase クエリ用の外部データソースが含まれる外部テーブルを使用します。外部データソースを使用して接続を確立し、次の主なユースケースをサポートします。

PolyBase を使用したデータ仮想化とデータ読み込み
BULK INSERT または OPENROWSET を使用して、SQL Server または SQL Database を使用した一括読み込み操作

「CREATE EXTERNAL DATA SOURCE」と「DROP EXTERNAL TABLE」も参照してください。

構文

-- Create a new external table
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        [ FILE_FORMAT = external_file_format_name ]
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

引数

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

作成するテーブルの 1 つから 3 つの部分で構成される名前。外部テーブルの場合、SQL では、Hadoop または Azure Blob Storage で参照されているファイルまたはフォルダーに関する基本的な統計情報と共に、テーブルのメタデータのみが格納されます。実際のデータは移動されず、SQL Server に格納されません。

重要

最適なパフォーマンスを得るには、外部データソースドライバーが 3 部構成の名前をサポートしている場合は、3 部構成の名前を指定することを強くお勧めします。

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE では、列名、データ型、NULL 値の許容、照合順序を構成できます。外部テーブルに対して DEFAULT CONSTRAINT を使用することはできません。

データ型と列の数を含む列の定義は、外部ファイルのデータと一致している必要があります。不一致がある場合、実際のデータに対してクエリを実行するときに、ファイルの行が拒否されます。

LOCATION = 'folder_or_filepath'

Hadoop または Azure Blob Storage にある実際のデータのフォルダーまたはファイルパスとファイル名を指定します。また、S3 互換オブジェクトストレージは SQL Server 2022 (16.x) 以降でサポートされます。ルートフォルダーから、場所を開始します。ルートフォルダーは、外部データソースで指定されたデータの場所です。

SQL Server では、CREATE EXTERNAL TABLE ステートメントによって、まだ存在しない場合にパスとフォルダーが作成されます。その後、INSERT INTO を使用して、ローカルの SQL Server テーブルからのデータを外部データソースをエクスポートすることができます。詳細については、PolyBase クエリに関するページを参照してください。

LOCATION をフォルダーとして指定した場合、外部テーブルから選択する PolyBase クエリでは、フォルダーとそのすべてのサブフォルダーからファイルが取得されます。 Hadoop と同じように PolyBase で非表示のフォルダーは返されません。ファイル名が下線 (_) またはピリオド (.) で始まるファイルも返されません。

次に示すイメージの例では、LOCATION='/webdata/' である場合、PolyBase のクエリを実行すると、mydata.txt と mydata2.txt からの行が返されます。 mydata3.txt は非表示のサブフォルダー内のファイルであるため、返されません。また、_hidden.txt は非表示のファイルであるため返されません。

外部テーブルのフォルダーとファイルデータの図。

既定値を変更して、読み取りをルートフォルダーからのみに限定するには、core-site.xml 構成ファイル内で属性<polybase.recursive.traversal> を 'false' に設定します。このファイルは <SqlBinRoot>\PolyBase\Hadoop\Conf の下にあり、SQL Server の bin ルートです。たとえば、C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn のようにします。

DATA_SOURCE = external_data_source_name

外部データの場所が含まれている外部データソースの名前を指定します。この場所は、Hadoop File System (HDFS)、Azure Blob Storage コンテナー、または Azure Data Lake Store です。外部データソースを作成するには、CREATE EXTERNAL DATA SOURCE を使用します。

FILE_FORMAT = external_file_format_name

外部データのファイル形式や圧縮方法を格納する外部ファイル形式オブジェクトの名前を指定します。外部ファイル形式を作成するには、CREATE EXTERNAL FILE FORMAT を使用します。

外部ファイル形式は、複数の類似した外部ファイルで再利用できます。

拒否オプション

このオプションは、TYPE = HADOOP である外部データソースでのみ使用できます。

PolyBase が外部データソースから取得したダーティ レコードを処理する方法を決定する、reject パラメーターを指定できます。データレコードの実際のデータの種類または列の数が、外部テーブルの列の定義と一致しない場合、そのデータレコードは "ダーティ" と見なされます。

reject 値を指定または変更しない場合、PolyBase では既定値が使用されます。 reject パラメーターに関するこの情報は、CREATE EXTERNAL TABLE ステートメントを使用して外部テーブルを作成するときに、追加メタデータとして格納されます。以降の SELECT ステートメントまたは SELECT INTO SELECT ステートメントで外部テーブルからデータを選択するとき、拒否オプションを使用して、実際のクエリが失敗するまでに拒否できる行の数または割合が PolyBase によって決定されます。拒否のしきい値を超えるまで、クエリの (部分的な) 結果が返されます。その後、適切なエラーメッセージと共に失敗します。

REJECT_TYPE = value | percentage

REJECT_VALUE オプションがリテラル値として指定されているか、割合として指定されているかを明確にします。

value

REJECT_VALUE は、割合ではなくリテラル値です。拒否された行が reject_value を超えると、クエリは失敗します。

たとえば、REJECT_VALUE = 5、REJECT_TYPE = value の場合、SELECT クエリは 5 つの行が拒否された後に失敗します。

percentage

REJECT_VALUE は、リテラル値ではなく割合です。失敗した行の percentage が reject_value を超えると、クエリは失敗します。失敗した行の割合は、間隔をおいて計算されます。

REJECT_VALUE = reject_value

クエリが失敗する前に拒否できる行を値または割合で指定します。

REJECT_TYPE = value の場合、reject_value は 0 から 2,147, 483,647 の範囲の整数にする必要があります。

REJECT_TYPE = percentage の場合、reject_value は 0 から 100 の範囲の浮動小数にする必要があります。

REJECT_SAMPLE_VALUE = reject_sample_value

REJECT_TYPE = percentage を指定する場合、この属性は必須です。それにより、拒否された行の割合が PolyBase によって再計算されるまでに取得が試行される行の数が決定します。

reject_sample_value パラメーターは、0 から 2,147,483,647 の範囲の整数にする必要があります。

たとえば、REJECT_SAMPLE_VALUE = 1000 の場合、PolyBase によって外部データファイルから 1000 行のインポートが試みられた後、失敗した行の割合が再計算されます。失敗した行のパーセンテージが reject_value 未満の場合、PolyBase は別の 1000 行の取得を試みます。 1000 行ずつ追加でインポートを試みた後、失敗した行の割合の再計算を続けます。

Note

PolyBase では間隔を置いて失敗した行のパーセンテージを計算するため、実際の失敗した行のパーセンテージは、reject_value を超える場合があります。

例:

この例は、REJECT の 3 つのオプションが相互にどのように作用するかを示しています。たとえば、REJECT_TYPE = percentage で REJECT_VALUE = 30、かつ REJECT_SAMPLE_VALUE = 100 の場合、次のシナリオが発生する可能性があります。

PolyBase で最初の 100 行の取得が試みられ、25 行が失敗し、75 行が成功しました。
失敗した行の割合は 25% と計算されます。これは reject 値である 30% を下回っています。その結果、PolyBase は引き続き、外部データソースからデータを取得します。
PolyBase は次の 100 行の読み込みを試み、今回は 25 行が成功し、75 行が失敗しました。
失敗した行の割合が 50% として再計算されます。失敗した行の割合が、30% という reject 値を超えました。
PolyBase クエリは、最初の 200 行を取得しようとした後、拒否された行 50% で失敗します。拒否のしきい値を超えたことが PolyBase クエリによって検出される前に、一致した行が返されていることに注意してください。

REJECTED_ROW_LOCATION = <ディレクトリの場所>

"適用対象:" SQL Server 2019 CU6 以降のバージョン、Azure Synapse Analytics。

外部データソース内のディレクトリを指定します。拒否された行と該当エラーファイルをそこに書き込みます。

指定したパスが存在しない場合、PolyBase では、そのパスが自動的に作成されます。 "_rejectedrows" という名前で子ディレクトリが作成されます。 "_" 文字があることで、場所パラメーターで明示的に指定されない限り、他のデータ処理ではこのディレクトリがエスケープされます。このディレクトリ内には、YearMonthDay -HourMinuteSecond という形式のロードサブミッション時間に基づいて作成されたフォルダーがあります (例: 20230330-173205)。このフォルダーで、2 種類のファイル、理由ファイルとデータファイルが書き込まれます。このオプションは、TYPE = HADOOP の外部データソースと、DELIMITEDTEXT FORMAT_TYPE を使用する外部テーブルでのみ使用できます。詳細については、「CREATE EXTERNAL DATA SOURCE」と「CREATE EXTERNAL FILE FORMAT」を参照してください。

理由ファイルとデータファイルのいずれにも、CTAS ステートメントと関連付けられている queryID が含まれます。データと理由が別々のファイル内にあるため、対応するファイルはサフィックスが一致しています。

アクセス許可

これらのユーザーアクセス許可が必要です。

CREATE TABLE
ALTER ANY SCHEMA
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT (Hadoop と Azure Storage の外部データソースにのみ適用されます)
CONTROL DATABASE (Hadoop と Azure Storage の外部データソースにのみ適用されます)

CREATE EXTERNAL TABLE コマンドで使われる DATABASE SCOPED CREDENTIAL で指定するリモートログインには、LOCATION パラメーターで指定する外部データソースのパス、テーブル、コレクションに対する読み取りアクセス許可が必要です。この EXTERNAL TABLE を使って Hadoop または Azure Storage の外部データソースにデータをエクスポートする場合は、指定するログインに LOCATION で指定するパスに対する書き込みアクセス許可が必要です。現在、Hadoop は SQL Server 2022 (16.x) ではサポートされていないことに注意してください。

Azure Blob Storage の場合、Azure portal、Azure Blob Storage、または ADLS Gen2 のストレージアカウントでアクセスキーと Shared Access Signature (SAS) を構成するときに、少なくとも読み取りと書き込みのアクセス許可を付与するように、[与えられているアクセス許可] を構成します。フォルダーをまたいで検索する場合は、一覧表示アクセス許可も必要になる場合があります。また、許可されるリソースの種類として [コンテナー] と [オブジェクト] の両方を選ぶ必要もあります。

重要

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

エラー処理

CREATE EXTERNAL TABLE ステートメントの実行中に、PolyBase から外部データソースへの接続が試みられます。接続が失敗した場合、ステートメントは失敗し、外部テーブルは作成されません。クエリが最終的に失敗となる前に、PolyBase によって接続が再試行されるため、コマンドが失敗するまで 1 分以上かかる可能性があります。

解説

SELECT FROM EXTERNAL TABLE などのアドホッククエリのシナリオの場合、PolyBase では外部データソースから取得された行が一時テーブルに格納されます。クエリ完了後、PolyBase によって一時テーブルが削除されます。 SQL テーブルには、永続的なデータは格納されません。

これに対し、SELECT INTO FROM EXTERNAL TABLE などのインポートのシナリオでは、外部データソースから取得された行が、PolyBase によって永続的なデータとして SQL テーブルに格納されます。 PolyBase が外部のデータを取得するときに、クエリの実行中に、新しいテーブルが作成されます。

PolyBase では、クエリのパフォーマンスを向上させるためにクエリ計算の一部を Hadoop にプッシュできます。このアクションは述語プッシュダウンと呼ばれます。それを有効にするには、CREATE EXTERNAL DATA SOURCE で、Hadoop のリソースマネージャーの場所のオプションを指定します。

同じまたは別の外部データソースを参照している多数の外部テーブルを作成できます。

制限事項と制約事項

外部テーブル用のデータは SQL Server の直接の管理下にないため、外部プロセスによっていつでも変更または削除することができます。その結果、外部のテーブルに対するクエリの結果は確定的であることが保証されません。同じクエリを外部のテーブルに対して実行するたびに、異なる結果が返される可能性があります。同様に、外部データが移動または削除された場合、クエリが失敗する可能性があります。

それぞれが異なる外部データソースを参照する複数の外部テーブルを作成できます。異なる複数の Hadoop データソースに対して同時にクエリを実行する場合は、各 Hadoop ソースに同じ 'Hadoop 接続' サーバー構成の設定が使用されている必要があります。たとえば、ことはできません同時にクエリを実行する Cloudera Hadoop クラスターと Hortonworks の Hadoop クラスターに対してこれらさまざまな構成設定を使用するためです。構成設定とサポートされる組み合わせについては、「PolyBase 接続構成」を参照してください。

外部テーブルがデータ型として DELIMITEDTEXT、CSV、PARQUET、または DELTA を使用している場合、外部テーブルは CREATE STATISTICS コマンドごとに 1 列の統計のみをサポートします。　

外部テーブルに対しては、これらのデータ定義言語 (DDL) ステートメントのみを使用できます。

CREATE TABLE および DROP TABLE
CREATE STATISTICS および DROP STATISTICS
CREATE VIEW および DROP VIEW

サポートされていない構成要素と操作:

外部テーブルの列に対する DEFAULT 制約
データ操作言語 (DML) の削除、挿入、更新の操作

クエリの制限事項

PolyBase では、32 個の同時 PolyBase クエリを実行しているときに、フォルダーあたり最大 33,000 ファイルを使用できます。この最大数には、各 HDFS フォルダー内のファイルとサブフォルダーの両方が含まれます。コンカレンシーの度合いが 32 未満である場合、ユーザーは 33,000 より多いファイルが含まれている HDFS のフォルダーに対して PolyBase クエリを実行できます。外部ファイルのパスを短く維持し、使用するファイルの数を HDFS フォルダーあたり 30,000 以下にすることをお勧めします。参照しているファイルの数が多すぎる場合は、Java 仮想マシン (JVM) のメモリ不足例外が発生する可能性があります。

テーブルの幅の制限事項

SQL Server 2016 の PolyBase には、テーブル定義による有効な 1 つの行の最大幅に基づいた、行の幅 32 KB という制限があります。列スキーマの合計が 32 KB を超えている場合、PolyBase によるデータのクエリは実行できません。

データ型の制限事項

次のデータ型は、PolyBase 外部テーブルでは使用できません。

geography
geometry
hierarchyid
image
text
nText
xml
任意のユーザー定義型

データソース固有の制限事項

Oracle

PolyBase での使用では、Oracle シノニムはサポートされていません。

配列を含む MongoDB コレクションの外部テーブル

配列を含む MongoDB コレクションに外部テーブルを作成するには、Azure Data Studio 用のデータ仮想化の拡張機能を使用して、MongoDB 用 PolyBase ODBC ドライバーによって検出されたスキーマに基づいて CREATE EXTERNAL TABLE ステートメントを生成する必要があります。フラット化アクションは、ドライバーによって自動的に実行されます。または、sp_data_source_objects (Transact-SQL) を使用してコレクションスキーマ (列) を検出し、外部テーブルを手動で作成することもできます。 sp_data_source_table_columns ストアドプロシージャでは、MongoDB 用 PolyBase ODBC ドライバーによってフラット化も自動的に実行されます。 Azure Data Studio 用のデータ仮想化の拡張機能と sp_data_source_table_columns は同じ内部ストアドプロシージャを使用して、外部スキーマのクエリを実行します。

ロック

SCHEMARESOLUTION オブジェクトに対する共有ロック。

セキュリティ

外部テーブルのデータファイルは、Hadoop または Azure Blob Storage に格納されます。これらのデータファイルはご自身のプロセスによって作成され、管理されます。外部データのセキュリティを管理することは、ユーザー自身の責任になります。

例

A. テキスト区切り形式のデータを含む外部テーブルを作成します

この例では、テキスト区切りのファイルで書式設定されたデータを含む外部テーブルを作成するために必要なすべての手順を示します。これは、外部データソース mydatasourcec と外部ファイルの形式 myfileformat を定義します。その後、これらのデータベースレベルオブジェクトは、CREATE EXTERNAL TABLE ステートメントで参照されます。詳細については、「CREATE EXTERNAL DATA SOURCE」と「CREATE EXTERNAL FILE FORMAT」を参照してください。

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);

CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;

B. RCFile 形式のデータを含む外部テーブルを作成します

この例では、RCFile として書式設定されたデータを含む外部テーブルを作成するために必要なすべての手順を示します。これは、外部データソース mydatasource_rc と外部ファイルの形式 myfileformat_rc を定義します。その後、これらのデータベースレベルオブジェクトは、CREATE EXTERNAL TABLE ステートメントで参照されます。詳細については、「CREATE EXTERNAL DATA SOURCE」と「CREATE EXTERNAL FILE FORMAT」を参照してください。

CREATE EXTERNAL DATA SOURCE mydatasource_rc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_rc
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
)
;

CREATE EXTERNAL TABLE ClickStream_rc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/employee_rc.tbl',
        DATA_SOURCE = mydatasource_rc,
        FILE_FORMAT = myfileformat_rc
    )
;

C. ORC 形式のデータを含む外部テーブルを作成します

この例では、ORC ファイルとして書式設定されたデータを含む外部テーブルを作成するために必要なすべての手順を示します。それにより、外部データソース mydatasource_orc と外部ファイル myfileformat_orc を定義します。その後、これらのデータベースレベルオブジェクトは、CREATE EXTERNAL TABLE ステートメントで参照されます。詳細については、「CREATE EXTERNAL DATA SOURCE」と「CREATE EXTERNAL FILE FORMAT」を参照してください。

CREATE EXTERNAL DATA SOURCE mydatasource_orc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_orc
WITH (
    FORMAT = ORC,
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
;

CREATE EXTERNAL TABLE ClickStream_orc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/',
        DATA_SOURCE = mydatasource_orc,
        FILE_FORMAT = myfileformat_orc
    )
;

D. Hadoop のデータのクエリを実行する

ClickStream は、Hadoop クラスター上の区切りテキストファイルである employee.tbl に接続する外部テーブルです。次のクエリは、標準のテーブルに対するクエリとまったく同じように見えます。ただし、このクエリでは Hadoop からデータを取得し、restuls を計算します。

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx';

E. SQL データと Hadoop データの結合

このクエリは、2 つの SQL テーブルに対する標準の JOIN とまったく同じように見えます。違いは、PolyBase によって Hadoop からクリックストリームデータが取得され、その後 UrlDescription テーブルに結合されることです。一方のテーブルは外部テーブルで、もう一方は標準の SQL テーブルです。

SELECT url.description
FROM ClickStream cs
JOIN UrlDescription url ON cs.url = url.name
WHERE cs.url = 'msdn.microsoft.com';

F. Hadoop から SQL テーブルへのデータのインポート

この例では、標準の SQL テーブル user と外部テーブル ClickStream との結合の結果を永続的に格納する新しい SQL テーブル ms_user を作成します。

SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
    ) AS ms
ON user.user_ip = ms.user_ip;

G. SQL Server の外部テーブルの作成

データベーススコープ資格情報を作成する前に、ユーザーデータベースに資格情報を保護するためのマスターキーが必要です。詳細については、「CREATE MASTER KEY」と「CREATE DATABASE SCOPED CREDENTIAL」を参照してください。

     -- Create a Master Key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
    GO
     /*  specify credentials to external data source
     *  IDENTITY: user name for external source.
     *  SECRET: password for external source.
     */
     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
     WITH IDENTITY = 'username', Secret = 'password';
    GO

SQLServerInstance という名前の新しい外部データソースと、sqlserver.customer という名前の外部テーブルが作成されます。

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );
    GO

    CREATE SCHEMA sqlserver;
    GO

     /* LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE sqlserver.customer(
     C_CUSTKEY INT NOT NULL,
     C_NAME VARCHAR(25) NOT NULL,
     C_ADDRESS VARCHAR(40) NOT NULL,
     C_NATIONKEY INT NOT NULL,
     C_PHONE CHAR(15) NOT NULL,
     C_ACCTBAL DECIMAL(15,2) NOT NULL,
     C_MKTSEGMENT CHAR(10) NOT NULL,
     C_COMMENT VARCHAR(117) NOT NULL
      )
      WITH (
      LOCATION='tpch_10.dbo.customer',
      DATA_SOURCE=SqlServerInstance
     );

I. Oracle の外部テーブルの作成

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

   /*
   * LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
   * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   * CONNECTION_OPTIONS: Specify driver location
   * CREDENTIAL: the database scoped credential, created above.
   */
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
     LOCATION = 'oracle://<server address>[:<port>]',
     -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name)

   /*
   * LOCATION: Oracle table/view in '<database_name>.<schema_name>.<object_name>' format. Note this may be case sensitive in the Oracle database.
   * DATA_SOURCE: the external data source, created above.
   */
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH (
    LOCATION='DB1.mySchema.customer',
    DATA_SOURCE= external_data_source_name
   );

J. Teradata の外部テーブルの作成

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );


     /* LOCATION: Teradata table/view in '<database_name>.<object_name>' format
      * DATA_SOURCE: the external data source, created above.
      */
     CREATE EXTERNAL TABLE customer(
      L_ORDERKEY INT NOT NULL,
      L_PARTKEY INT NOT NULL,
     L_SUPPKEY INT NOT NULL,
     L_LINENUMBER INT NOT NULL,
     L_QUANTITY DECIMAL(15,2) NOT NULL,
     L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
     L_DISCOUNT DECIMAL(15,2) NOT NULL,
     L_TAX DECIMAL(15,2) NOT NULL,
     L_RETURNFLAG CHAR NOT NULL,
     L_LINESTATUS CHAR NOT NULL,
     L_SHIPDATE DATE NOT NULL,
     L_COMMITDATE DATE NOT NULL,
     L_RECEIPTDATE DATE NOT NULL,
     L_SHIPINSTRUCT CHAR(25) NOT NULL,
     L_SHIPMODE CHAR(10) NOT NULL,
     L_COMMENT VARCHAR(44) NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

K. MongoDB の外部テーブルの作成

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

     /* LOCATION: Location string should be of format '<type>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = mongodb://<server>[:<port>],
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = credential_name
    );

     /* LOCATION: MongoDB table/view in '<database_name>.<schema_name>.<object_name>' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE customers(
     [O_ORDERKEY] DECIMAL(38) NOT NULL,
     [O_CUSTKEY] DECIMAL(38) NOT NULL,
     [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
     [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
     [O_ORDERDATE] DATETIME2(0) NOT NULL,
     [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

L. 外部テーブルを使用して S3 互換のオブジェクトストレージにクエリを実行する

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

次の例では、T-SQL を使用して、外部テーブルにクエリを実行して、S3 互換のオブジェクトストレージに格納されている Parquet ファイルに対してクエリを実行する方法を示します。このサンプルでは、外部データソース内の相対パスを使用します。

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Region(
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds',
FILE_FORMAT = ParquetFileFormat);
GO

次の手順

関連する概念の詳細については、次の記事を参照してください。

* Azure SQL Database *

概要:Azure SQL データベース

Azure SQL Database で、エラスティッククエリ (プレビュー) 用外部テーブルを作成します。

「CREATE EXTERNAL DATA SOURCE」を参照してください。

構文

-- Create a table for use with elastic query
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH ( <sharded_external_table_options> )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<sharded_external_table_options> ::=
        DATA_SOURCE = external_data_source_name,
        SCHEMA_NAME = N'nonescaped_schema_name',
        OBJECT_NAME = N'nonescaped_object_name',
        [DISTRIBUTION  = SHARDED(sharding_column_name) | REPLICATED | ROUND_ROBIN]]
    )
[;]

引数

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

作成するテーブルの 1 つから 3 つの部分で構成される名前。外部テーブルの場合、SQL では、Azure SQL Database 内で参照されているファイルまたはフォルダーに関する基本的な統計情報と共に、テーブルのメタデータのみが格納されます。実際のデータは移動されず、Azure SQL Database に格納されません。

重要

<column_definition> [ ,...n ]

注意

Text、nText、および XML は、Azure SQL Database の外部テーブルの列に対してサポートされているデータ型ではありません。

シャード化された外部テーブルのオプション

エラスティッククエリの外部データソース (SQL Server 以外のデータソース) と配布方法を指定します。

DATA_SOURCE

DATA_SOURCE 句では、外部テーブルに使用される外部データソース (シャードマップ) を定義します。例については、「外部テーブルを作成する」を参照してください。

重要

Azure SQL Database では、外部データソースタイプの RDMS および SHARD_MAP_MANAGER への外部テーブルの作成がサポートされています。 Azure SQL Database では、Azure Blob Storage への外部テーブルの作成はサポートされていません。

SCHEMA_NAME と OBJECT_NAME

SCHEMA_NAME 句と OBJECT_NAME 句では、外部テーブルの定義を別のスキーマ内のテーブルにマップします。これらを省略した場合、リモートオブジェクトのスキーマは "dbo" と見なされ、その名前は定義されている外部テーブルの名前と同一であると見なされます。これは、リモートテーブルの名前が、外部テーブルを作成するデータベースで既に取得されている場合に便利です。たとえば、スケールアウトされたデータ層のカタログビューまたは DMV の集計ビューを取得する外部テーブルを定義する場合が挙げられます。カタログビューと DMV は既にローカルに存在するため、外部テーブルの定義にその名前を使うことはできません。代わりに、別の名前を使用して、カタログビューまたは DMV の名前を SCHEMA_NAME 句または OBJECT_NAME 句で使用します。例については、「外部テーブルを作成する」を参照してください。

DISTRIBUTION

省略可能。これは SHARD_MAP_MANAGER 型のデータベースの場合にのみ必須です。この引数では、テーブルをシャード化されたテーブルとして扱うか、レプリケートされたテーブルとして扱うかを制御します。 SHARDED (列名) テーブルでは、異なるテーブルからのデータは重複しません。 REPLICATED は、テーブルですべてのシャードに同じデータを持つことを指定します。 ROUND_ROBIN は、データを分散するためにアプリケーション固有のメソッドが使用されていることを示します。

DISTRIBUTION 句は、このテーブルに使用するデータ分散を指定します。クエリプロセッサは、DISTRIBUTION 句で提供される情報を使用して、最も効率的なクエリプランを作成します。

SHARDED は、データがデータベース間で行方向にパーティション分割されることを意味します。データ分散のパーティション分割キーは、sharding_column_name パラメーターです。
REPLICATED は、テーブルの同一のコピーが各データベースに存在することを意味します。データベース間でレプリカが同じであることを自分で確認する必要があります。
ROUND_ROBIN は、テーブルがアプリケーションに依存する分散方法を使用して、行方向にパーティション分割されることを意味します。

アクセス許可

外部テーブルへのアクセス権を持つユーザーは、外部データソース定義に指定された資格情報の下で、基になるリモートテーブルへのアクセス権を自動的に取得します。外部データソースの資格情報による不要な特権の昇格を防ぎます。外部テーブルに対して、通常のテーブルであるかのように GRANT または REVOKE を使用します。外部データソースと外部テーブルを定義すると、外部テーブルに対して完全に T-SQL を使用できるようになります。

エラー処理

CREATE EXTERNAL TABLE ステートメントの実行時、接続が失敗した場合、ステートメントは失敗し、外部テーブルは作成されません。クエリが最終的に失敗となる前に、SQL Database によって接続が再試行されるため、コマンドが失敗するまで 1 分以上かかる可能性があります。

解説

SELECT FROM EXTERNAL TABLE などのアドホッククエリのシナリオの場合、SQL Database では外部データソースから取得された行が一時テーブルに格納されます。クエリ完了後、SQL Database によって一時テーブルが削除されます。 SQL テーブルには、永続的なデータは格納されません。

これに対し、SELECT INTO FROM EXTERNAL TABLE などのインポートのシナリオでは、外部データソースから取得された行が、SQL Database によって永続的なデータとして SQL テーブルに格納されます。 SQL Database が外部のデータを取得するときに、クエリの実行中に、新しいテーブルが作成されます。

同じまたは別の外部データソースを参照している多数の外部テーブルを作成できます。

制限事項と制約事項

外部テーブルを使用したデータへのアクセスは、SQL Server 内の分離セマンティクスに準拠しません。つまり、外部のクエリによってロックやスナップショットの分離が行われないため、外部データソースのデータが変更されると、返されるデータが変更される可能性があります。同じクエリを外部のテーブルに対して実行するたびに、異なる結果が返される可能性があります。同様に、外部データが移動または削除された場合、クエリが失敗する可能性があります。

それぞれが異なる外部データソースを参照する複数の外部テーブルを作成できます。

外部テーブルに対しては、これらのデータ定義言語 (DDL) ステートメントのみを使用できます。

CREATE TABLE および DROP TABLE。
CREATE VIEW および DROP VIEW。

サポートされていない構成要素と操作:

外部テーブルの列に対する DEFAULT 制約。
データ操作言語 (DML) の削除、挿入、更新の操作。
外部テーブル列に対する動的データマスク。
Azure SQL Database の外部テーブルでは、カーソルはサポートされていません。

外部データソースにプッシュダウンできるのは、クエリで定義されたリテラル述語のみです。これはリンクサーバーとは異なり、クエリ実行中に決定された述語を使用できる場合、つまりクエリプランで入れ子になったループと組み合わせて使用されるときにアクセスします。この動作により、多くの場合、外部テーブル全体がローカルにコピーされてから結合されます。

-- Assuming External.Orders is an external table and Customer is a local table.
-- This query  will copy the whole of the external locally as the predicate needed
-- to filter isn't known at compile time. Its only known during execution of the query

SELECT Orders.OrderId, Orders.OrderTotal
FROM External.Orders
WHERE CustomerId IN (
        SELECT TOP 1 CustomerId
        FROM Customer
        WHERE CustomerName = 'MyCompany'
);

外部テーブルを使用すると、クエリプランで並列処理を使用できなくなります。

外部テーブルはリモートクエリとして実装されます。そのため、返される推定行数は通常 1000 です。外部テーブルのフィルター処理に使用される述語の種類に基づいて他のルールがあります。これらは、外部テーブルの実際のデータに基づく推定ではなく、ルールベースの推定です。 Optimiser は、リモートデータソースにアクセスせず、より正確な推定を取得します。

データ型の制限事項

次のデータ型は、PolyBase 外部テーブルでは使用できません。

geography
geometry
hierarchyid
image
text
nText
xml
任意のユーザー定義型

ロック

SCHEMARESOLUTION オブジェクトに対する共有ロック。

例

A. Azure SQL Database の外部テーブルを作成します

CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NOT NULL,
  [Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)

B. シャード化されたデータソース用の外部テーブルの作成

この例では、SCHEMA_NAME 句と OBJECT_NAME 句を使用してリモートの DMV を外部テーブルに再マッピングします。

CREATE EXTERNAL TABLE [dbo].[all_dm_exec_requests]([session_id] smallint NOT NULL,
  [request_id] int NOT NULL,
  [start_time] datetime NOT NULL,
  [status] nvarchar(30) NOT NULL,
  [command] nvarchar(32) NOT NULL,
  [sql_handle] varbinary(64),
  [statement_start_offset] int,
  [statement_end_offset] int,
  [cpu_time] int NOT NULL)
WITH
(
  DATA_SOURCE = MyExtSrc,
  SCHEMA_NAME = 'sys',
  OBJECT_NAME = 'dm_exec_requests',
  DISTRIBUTION=ROUND_ROBIN
);

次の手順

外部テーブルの詳細については、次の記事の Azure SQL Database を参照してください。

Azure SQL Database

* Azure Synapse
Analytics *

概要:Azure Synapse Analytics

次のために外部テーブルを使用します。

専用 SQL プールでは、Hadoop、Azure Blob Storage、Azure Data Lake Storage Gen1 および Gen2 のデータのクエリ、インポート、格納を行うことができます。
サーバーレス SQL プールでは、Azure Blob Storage、Azure Data Lake Storage Gen1 および Gen2 のデータのクエリ、インポート、格納を行うことができます。サーバーレスでは、TYPE=Hadoop はサポートされていません。

「CREATE EXTERNAL DATA SOURCE」と「DROP EXTERNAL TABLE」も参照してください。

Azure Synapse で外部テーブルを使用するためのその他のガイダンスと例については、「Synapse SQL で外部テーブルを使用する」を参照してください。

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage, 
    | REJECT_VALUE = reject_value,
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name,
        [ , <reject_options> [ ,...n ] ]
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]         
    )
[;]
<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

<reject_options> ::=
{
    | REJECT_TYPE = value, 
    | REJECT_VALUE = reject_value,
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

引数

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

作成するテーブルの 1 つから 3 つの部分で構成される名前。外部テーブルの場合、Azure Data Lake、Hadoop、または Azure Blob Storage で参照されているファイルまたはフォルダーに関する基本的な統計情報と共に、テーブルのメタデータのみ。外部テーブルの作成時に、実際のデータは移動または格納されません。

重要

<column_definition> [ ,...n ]

注意

非推奨のデータ型 text、ntext および XML は、Synapse Analytics の外部テーブルの列に対してサポートされているデータ型ではありません。

区切りファイルを読み取る場合、データ型と列の数を含む列の定義は、外部ファイルのデータと一致している必要があります。不一致がある場合、実際のデータに対してクエリを実行するときに、ファイルの行が拒否されます。
Parquet ファイルからの読み取りの場合は、読み取りたい列だけを指定して、残りをスキップすることができます。

LOCATION = 'folder_or_filepath'

Azure Data Lake、Hadoop、または Azure Blob Storage にある実際のデータのフォルダーまたはファイルパスとファイル名を指定します。ルートフォルダーから、場所を開始します。ルートフォルダーは、外部データソースで指定されたデータの場所です。 CREATE EXTERNAL TABLE AS SELECT ステートメントによって、存在しない場合にパスとフォルダーが作成されます。 CREATE EXTERNAL TABLE では、パスとフォルダーが作成されません。

次に示すイメージの例では、LOCATION='/webdata/' である場合、PolyBase のクエリを実行すると、mydata.txt と mydata2.txt からの行が返されます。 mydata3.txt は非表示のフォルダーのサブフォルダー内にあるため、返されません。また、_hidden.txt は非表示のファイルであるため返されません。

外部テーブルのフォルダーとファイルデータの図。

Hadoop 外部テーブルとは異なり、ネイティブ外部テーブルでは、パスの最後に /** を指定しない限り、サブフォルダーが返されません。この例では、LOCATION='/webdata/' である場合、サーバーレス SQL プールのクエリで mydata.txt から行が返されます。 mydata2.txt と mydata3.txt はサブフォルダー内にあるため、これらは返されません。 Hadoop テーブルでは、サブフォルダー内のすべてのファイルが返されます。

Hadoop 外部テーブルとネイティブ外部テーブルではどちらも、名前が下線 (_) またはピリオド (.) で始まるファイルはスキップされます。

DATA_SOURCE = external_data_source_name

外部データの場所が含まれている外部データソースの名前を指定します。この場所は Azure Data Lake 内にあります。外部データソースを作成するには、CREATE EXTERNAL DATA SOURCE を使用します。

FILE_FORMAT = external_file_format_name

TABLE_OPTIONS

基になるファイルを読み取る方法を示す一連のオプションを指定します。現時点で使用できる唯一のオプションは、{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]} です。これは、不整合な読み取り操作が発生する可能性がある場合でも、基になるファイルに対して行われた更新を無視するように外部テーブルに指示します。このオプションは、ファイルが頻繁に追加される特殊な場合にのみ使用してください。このオプションは、CSV 形式のサーバーレス SQL プールで使用できます。

REJECT オプション

Azure Synapse Analytics のサーバーレス SQL プールの拒否オプションはプレビュー段階です。

このオプションは、TYPE = HADOOP である外部データソースでのみ使用できます。

PARSER_VERSION 形式オプションは、サーバーレス SQL プールでのみサポートされます。

REJECT_TYPE = value | percentage

REJECT_VALUE オプションがリテラル値として指定されているか、割合として指定されているかを明確にします。

value

REJECT_VALUE は、割合ではなくリテラル値です。拒否された行が reject_value を超えると、PolyBase クエリは失敗します。

たとえば、REJECT_VALUE = 5 で REJECT_TYPE = value の場合、PolyBase の SELECT クエリは、5 行を拒否した後に失敗します。

percentage

REJECT_VALUE は、リテラル値ではなく割合です。失敗した行の percentage が reject_value を超えると、PolyBase クエリは失敗します。失敗した行の割合は、間隔をおいて計算されます。

REJECT_VALUE = reject_value

クエリが失敗する前に拒否できる行を値または割合で指定します。

REJECT_TYPE = value の場合、reject_value は 0 から 2,147, 483,647 の範囲の整数にする必要があります。
REJECT_TYPE = percentage の場合、reject_value は 0 から 100 の範囲の浮動小数にする必要があります。割合は、TYPE=HADOOP である専用 SQL プールに対してのみ有効です。

拒否された行が reject_value を超えると、クエリは失敗します。たとえば、REJECT_VALUE = 5 で REJECT_TYPE = value の場合、SELECT クエリは、5 行を拒否した後に失敗します。

REJECT_SAMPLE_VALUE = reject_sample_value

reject_sample_value パラメーターは、0 から 2,147,483,647 の範囲の整数にする必要があります。

Note

例:

PolyBase で最初の 100 行の取得が試みられ、25 行が失敗し、75 行が成功しました。
失敗した行の割合は 25% と計算されます。これは reject 値である 30% を下回っています。その結果、PolyBase は引き続き、外部データソースからデータを取得します。
PolyBase は次の 100 行の読み込みを試み、今回は 25 行が成功し、75 行が失敗しました。
失敗した行の割合が 50% として再計算されます。失敗した行の割合が、30% という reject 値を超えました。
PolyBase クエリは、最初の 200 行を取得しようとした後、拒否された行 50% で失敗します。拒否のしきい値を超えたことが PolyBase クエリによって検出される前に、一致した行が返されていることに注意してください。

REJECTED_ROW_LOCATION = <ディレクトリの場所>

外部データソース内のディレクトリを指定します。拒否された行と該当エラーファイルをそこに書き込みます。

指定したパスが存在しない場合は、作成されます。 _rejectedrows という名前で子ディレクトリが作成されます。 _ 文字があることで、場所パラメーターで明示的に指定されない限り、他のデータ処理ではこのディレクトリがエスケープされます。

サーバーレス SQL プールでは、パスは YearMonthDay_HourMinuteSecond_StatementID です。ステートメント id を使用して、フォルダーをその生成元のクエリと関連付けることができます。
専用 SQL プールでは、作成されるパスはロードサブミッションの時間に基づき、YearMonthDay -HourMinuteSecond という形式になります (例: 20180330-173205)。

このフォルダーに、2 種類のファイル (_reason ファイルとデータファイル) が書き込まれます。

詳しくは、「CREATE EXTERNAL DATA SOURCE」をご覧ください。

サーバーレス SQL プールの場合、error.json ファイルには、拒否された行に関連して発生したエラーを含む JSON 配列が含まれます。エラーを表す各要素には、次の属性が含まれています。

属性	説明
エラー	行が拒否された理由。
行	ファイル内の拒否された行の序数。
列	拒否された列の序数。
値	拒否された列の値。値が 100 文字を超える場合は、最初の 100 文字のみが表示されます。
File	行が属しているファイルのパス。

アクセス許可

これらのユーザーアクセス許可が必要です。

CREATE TABLE
ALTER ANY SCHEMA
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT

注意

MASTER KEY、DATABASE SCOPED CREDENTIAL、および EXTERNAL DATA SOURCE のみを作成するには、CONTROL DATABASE のアクセス許可が必要です

外部データソースを作成するログインには、Hadoop または Azure Blob Storage 内にある外部データソースに対する読み取りおよび書き込みのアクセス許可が必要であることに注意してください。

重要

エラー処理

解説

同じまたは別の外部データソースを参照している多数の外部テーブルを作成できます。

UTF-8 照合順序を使用するソースデータに注意してください。 UTF-8 照合順序を使用するソースデータの場合は、CREATE EXTERNAL TABLE ステートメントで UTF-8 以外の照合順序を各 UTF-8 列に手動で指定する必要があります。これは、UTF-8 のサポートが外部テーブルに拡張されないためです。 UTF-8 照合順序を使用して外部テーブルを作成しようとすると、Unsupported collation エラーメッセージが表示されます。外部テーブルのデータベース照合順序が UTF-8 照合順序の場合、たとえば、[UTF8_column] varchar(128) COLLATE LATIN1_GENERAL_100_CI_AS_KS_WS NOT NULL, など、UTF-8 以外の列の照合順序を明示的に指定しない限り、外部テーブルの作成は失敗します。

Azure Synapse Analytics のサーバーレスおよび専用 SQL プールでは、データ仮想化に異なるコードベースを使用します。サーバーレス SQL プールでは、ネイティブデータ仮想化テクノロジがサポートされています。専用 SQL プールでは、ネイティブと PolyBase の両方のデータ仮想化がサポートされています。 PolyBase データ仮想化は、EXTERNAL DATA SOURCE が TYPE=HADOOP で作成されるときに使用されます。

制限事項と制約事項

外部テーブル用のデータは Azure Synapse の直接の管理下にないため、外部プロセスによっていつでも変更または削除することができます。その結果、外部のテーブルに対するクエリの結果は確定的であることが保証されません。同じクエリを外部のテーブルに対して実行するたびに、異なる結果が返される可能性があります。同様に、外部データが移動または削除された場合、クエリが失敗する可能性があります。

それぞれが異なる外部データソースを参照する複数の外部テーブルを作成できます。

外部テーブルに対しては、これらのデータ定義言語 (DDL) ステートメントのみを使用できます。

CREATE TABLE および DROP TABLE
CREATE STATISTICS および DROP STATISTICS
CREATE VIEW および DROP VIEW

サポートされていない構成要素と操作:

外部テーブルの列に対する DEFAULT 制約
データ操作言語 (DML) の削除、挿入、更新の操作
外部テーブル列に対する動的データマスク

クエリの制限事項

フォルダーあたりのファイルは 30,000 個を超えないようにすることをお勧めします。参照しているファイルの数が多すぎる場合は、Java 仮想マシン (JVM) のメモリ不足の例外が発生したり、パフォーマンスが低下したりする可能性があります。

テーブルの幅の制限事項

Azure Data Warehouse の PolyBase には、テーブル定義による有効な 1 つの行の最大幅に基づいた、行の幅 1 MB という制限があります。列スキーマの合計が 1 MB を超えている場合、PolyBase によるデータのクエリは実行できません。

データ型の制限事項

次のデータ型は、PolyBase 外部テーブルでは使用できません。

geography
geometry
hierarchyid
image
text
nText
xml
任意のユーザー定義型

ロック

SCHEMARESOLUTION オブジェクトに対する共有ロック。

例

A. ADLS Gen 2 から Azure Microsoft Azure Synapse Analytics にデータをインポートする

ADLS Gen 1 の例については、「CREATE EXTERNAL DATA SOURCE」を参照してください。

-- These values come from your Azure Active Directory Application used to authenticate to ADLS Gen 2.
CREATE DATABASE SCOPED CREDENTIAL ADLUser
WITH IDENTITY = '<clientID>@\<OAuth2.0TokenEndPoint>',
SECRET = '<KEY>' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (TYPE = HADOOP,
      LOCATION = 'abfss://data@pbasetr.azuredatalakestore.net'
);

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(
    FORMAT_TYPE = DELIMITEDTEXT
    , FORMAT_OPTIONS ( FIELD_TERMINATOR = '|'
       , STRING_DELIMITER = ''
      , DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fff'
      , USE_TYPE_DEFAULT = FALSE
      )
);

CREATE EXTERNAL TABLE [dbo].[DimProduct_external]
( [ProductKey] [int] NOT NULL,
  [ProductLabel] nvarchar NULL,
  [ProductName] nvarchar NULL )
WITH
(
    LOCATION='/DimProduct/' ,
    DATA_SOURCE = AzureDataLakeStore ,
    FILE_FORMAT = TextFileFormat ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
);

CREATE TABLE [dbo].[DimProduct]
WITH (DISTRIBUTION = HASH([ProductKey] ) )
AS SELECT * FROM
[dbo].[DimProduct_external] ;

B. Parquet から Azure Synapse Analytics にデータをインポートする

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

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 SQL Database

* Analytics
Platform System (PDW) *

概要:分析プラットフォームシステム

次のために外部テーブルを使用します。

Transact-SQL ステートメントで Hadoop または Azure Blob Storage のデータのクエリを実行します。
Hadoop または Azure Blob Storage からデータをインポートして、Analytics Platform System に格納します。

「CREATE EXTERNAL DATA SOURCE」と「DROP EXTERNAL TABLE」も参照してください。

構文

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

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

}

引数

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

作成するテーブルの 1 つから 3 つの部分で構成される名前。外部テーブルの場合、Analytics Platform System では、Hadoop または Azure Blob Storage で参照されているファイルまたはフォルダーに関する基本的な統計情報と共に、テーブルのメタデータのみが格納されます。実際のデータが Analytics Platform System に移されたり、格納されたりすることはありません。

重要

<column_definition> [ ,...n ]

LOCATION = 'folder_or_filepath'

Hadoop または Azure Blob Storage にある実際のデータのフォルダーまたはファイルパスとファイル名を指定します。ルートフォルダーから、場所を開始します。ルートフォルダーは、外部データソースで指定されたデータの場所です。

Analytics Platform System では、CREATE EXTERNAL TABLE AS SELECT ステートメントによって、存在しない場合にパスとフォルダーが作成されます。 CREATE EXTERNAL TABLE では、パスとフォルダーが作成されません。

外部テーブルのフォルダーとファイルデータの図。

既定値を変更して、読み取りをルートフォルダーからのみに限定するには、core-site.xml 構成ファイルで属性 <polybase.recursive.traversal> を "false" に設定します。このファイルは <SqlBinRoot>\PolyBase\Hadoop\Conf\ の下にあり、SQL Server の bin ルートです。たとえば、C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn\ のようにします。

DATA_SOURCE = external_data_source_name

外部データの場所が含まれている外部データソースの名前を指定します。この場所は、Hadoop または Azure Blob Storage です。外部データソースを作成するには、CREATE EXTERNAL DATA SOURCE を使用します。

FILE_FORMAT = external_file_format_name

拒否オプション

このオプションは、TYPE = HADOOP である外部データソースでのみ使用できます。

REJECT_TYPE = value | percentage

REJECT_VALUE オプションがリテラル値として指定されているか、割合として指定されているかを明確にします。

value

REJECT_VALUE は、割合ではなくリテラル値です。拒否された行が reject_value を超えると、PolyBase クエリは失敗します。

たとえば、REJECT_VALUE = 5 で REJECT_TYPE = value の場合、PolyBase の SELECT クエリは、5 行を拒否した後に失敗します。

percentage

REJECT_VALUE = reject_value

クエリが失敗する前に拒否できる行を値または割合で指定します。

REJECT_TYPE = value の場合、reject_value は 0 から 2,147, 483,647 の範囲の整数にする必要があります。

REJECT_TYPE = percentage の場合、reject_value は 0 から 100 の範囲の浮動小数にする必要があります。

REJECT_SAMPLE_VALUE = reject_sample_value

reject_sample_value パラメーターは、0 から 2,147,483,647 の範囲の整数にする必要があります。

Note

例:

PolyBase で最初の 100 行の取得が試みられ、25 行が失敗し、75 行が成功しました。
失敗した行の割合は 25% と計算されます。これは reject 値である 30% を下回っています。その結果、PolyBase は引き続き、外部データソースからデータを取得します。
PolyBase は次の 100 行の読み込みを試み、今回は 25 行が成功し、75 行が失敗しました。
失敗した行の割合が 50% として再計算されます。失敗した行の割合が、30% という reject 値を超えました。
PolyBase クエリは、最初の 200 行を取得しようとした後、拒否された行 50% で失敗します。拒否のしきい値を超えたことが PolyBase クエリによって検出される前に、一致した行が返されていることに注意してください。

アクセス許可

これらのユーザーアクセス許可が必要です。

CREATE TABLE
ALTER ANY SCHEMA
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT
CONTROL DATABASE

重要

エラー処理

解説

同じまたは別の外部データソースを参照している多数の外部テーブルを作成できます。

制限事項と制約事項

外部テーブル用のデータはアプライアンスの直接の管理下にないため、外部プロセスによっていつでも変更または削除することができます。その結果、外部のテーブルに対するクエリの結果は確定的であることが保証されません。同じクエリを外部のテーブルに対して実行するたびに、異なる結果が返される可能性があります。同様に、外部データが移動または削除された場合、クエリが失敗する可能性があります。

外部テーブルに対しては、これらのデータ定義言語 (DDL) ステートメントのみを使用できます。

CREATE TABLE および DROP TABLE
CREATE STATISTICS および DROP STATISTICS
CREATE VIEW および DROP VIEW

サポートされていない構成要素と操作:

外部テーブルの列に対する DEFAULT 制約
データ操作言語 (DML) の削除、挿入、更新の操作
外部テーブル列に対する動的データマスク

クエリの制限事項

テーブルの幅の制限事項

Azure Synapse Analytics では、この制限は 1 MB に引き上げられました。

データ型の制限事項

次のデータ型は、PolyBase 外部テーブルでは使用できません。

geography
geometry
hierarchyid
image
text
nText
xml
任意のユーザー定義型

ロック

SCHEMARESOLUTION オブジェクトに対する共有ロック。

セキュリティ

例

A. HDFS データと Analytics Platform System データを結合します

SELECT cs.user_ip FROM ClickStream cs
JOIN [User] u ON cs.user_ip = u.user_ip
WHERE cs.url = 'www.microsoft.com';

B. HDFS から分散 Analytics Platform System テーブルに行データをインポートします

CREATE TABLE ClickStream_PDW
WITH ( DISTRIBUTION = HASH (url) )
AS SELECT url, event_date, user_ip FROM ClickStream;

C. HDFS から複製 Analytics Platform System テーブルに行データをインポートします

CREATE TABLE ClickStream_PDW
WITH ( DISTRIBUTION = REPLICATE )
AS SELECT url, event_date, user_ip
FROM ClickStream;

次の手順

分析プラットフォームシステムの外部テーブルの詳細については、次の記事を参照してください。

Azure SQL Database

* Azure SQL Managed Instance *