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

適用対象: XSQL Server XAzure SQL Database ○Azure SQL Data Warehouse ○Parallel Data Warehouse APPLIES TO: noSQL Server noAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

外部テーブルを作成してから、Hadoop または Azure Storage Blob に Transact-SQLTransact-SQL SELECT ステートメントの結果を並列でエクスポートします。Creates an external table and then exports, in parallel, the results of a Transact-SQLTransact-SQL SELECT statement to Hadoop or Azure Storage Blob.

トピック リンク アイコン Transact-SQL 構文表記規則 (Transact-SQL)Topic link icon Transact-SQL Syntax Conventions (Transact-SQL)

構文Syntax

  
CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name   
    WITH (   
        LOCATION = 'hdfs_folder',  
        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>  

引数Arguments

[ [ database_name .[ [ database_name . [ schema_name ] .[ schema_name ] . ] | schema_name .] | schema_name . ] table_name] table_name
データベースで作成するテーブルの 1 つから 3 つの部分で構成される名前。The one to three-part name of the table to create in the database. 外部テーブルの場合、テーブル メタデータのみがリレーショナル データベースに格納されます。For an external table, only the table metadata is stored in the relational database.

LOCATION = 'hdfs_folder'LOCATION = 'hdfs_folder'
外部データ ソースで SELECT ステートメントの結果を書き込む場所を指定します。Specifies where to write the results of the SELECT statement on the external data source. 場所はフォルダー名であり、必要に応じて、Hadoop クラスターまたは Azure Storage Blob のルート フォルダーへの相対パスを含めることができます。The location is a folder name and can optionally include a path that is relative to the root folder of the Hadoop Cluster or Azure Storage Blob. PolyBase ではパスとフォルダー (まだ存在しない場合) が作成されます。PolyBase will create the path and folder if it does not already exist.

外部ファイルは hdfs_folder に書き込まれ、QueryID_date_time_ID.format という名前が付けられます (ID は増分識別子、format はエクスポートされるデータ形式)。The external files are written to hdfs_folder and named QueryID_date_time_ID.format, where ID is an incremental identifier and format is the exported data format. たとえば、QID776_20160130_182739_0.orc です。For example, QID776_20160130_182739_0.orc.

DATA_SOURCE = external_data_source_nameDATA_SOURCE = external_data_source_name
外部データが格納されている、または格納される場所を含む、外部データ ソース オブジェクトの名前を指定します。Specifies the name of the external data source object that contains the location where the external data is stored or will be stored. 場所は、Hadoop クラスターまたは Azure Storage Blob のいずれかです。The location is either a Hadoop Cluster or an Azure Storage Blob. 外部データ ソースを作成するには、CREATE EXTERNAL DATA SOURCE (Transact-SQL) を使用します。To create an external data source, use CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_nameFILE_FORMAT = external_file_format_name
外部データ ファイルの形式を含む、外部ファイル形式オブジェクトの名前を指定します。Specifies the name of the external file format object that contains the format for the external data file. 外部ファイル形式を作成するには、CREATE EXTERNAL FILE FORMAT (Transact-SQL) を使用します。To create an external file format, use CREATE EXTERNAL FILE FORMAT (Transact-SQL).

オプションを拒否します。Reject Options
この CREATE EXTERNAL TABLE AS SELECT ステートメントの実行時に拒否オプションは適用されません。The reject options do not apply at the time this CREATE EXTERNAL TABLE AS SELECT statement is run. 代わりに、ここで指定し、後で外部テーブルからデータをインポートする際に、データベースで使用できるようにします。Instead, they are specified here so that the database can use them at a later time when it imports data from the external table. 後で CREATE TABLE AS SELECT ステートメントで外部テーブルからデータを選択するときに、データベースでは拒否オプションを使用して、インポートを停止するまでにインポートの失敗が許容される行の数または割合を決定します。Later, when the CREATE TABLE AS SELECT statement selects data from the external table, the database will use the reject options to determine the number or percentage of rows that can fail to import before it stops the import.

REJECT_VALUE = reject_valueREJECT_VALUE = reject_value
データベースでインポートを停止するまでに、インポートの失敗が許容される行の値または割合を指定します。Specifies the value or the percentage of rows that can fail to import before database halts the import.

REJECT_TYPE = value | percentageREJECT_TYPE = value | percentage
REJECT_VALUE オプションは、リテラル値またはパーセントとして指定されているかどうか明確にします。Clarifies whether the REJECT_VALUE option is specified as a literal value or a percentage.

valuevalue
REJECT_VALUE は、リテラル値、パーセンテージではありません。REJECT_VALUE is a literal value, not a percentage. 失敗した行の数が reject_value を超えた場合、データベースは外部データ ファイルからの行のインポートを停止します。The database will stop importing rows from the external data file when the number of failed rows exceeds reject_value.

たとえば、REJECT_VALUE = 5 で、REJECT_TYPE = value の場合、データベースは、5 行のインポートが失敗した後、行のインポートを停止します。For example, if REJECT_VALUE = 5 and REJECT_TYPE = value, the database will stop importing rows after 5 rows have failed to import.

割合percentage
REJECT_VALUE では、リテラル値ではなく、パーセンテージです。REJECT_VALUE is a percentage, not a literal value. 失敗した行の percentagereject_value を超えた場合、データベースは外部データ ファイルからの行のインポートを停止します。The database will stop importing rows from the external data file when the percentage of failed rows exceeds reject_value. 障害が発生した行の割合は、間隔で計算されます。The percentage of failed rows is calculated at intervals.

REJECT_SAMPLE_VALUE = reject_sample_valueREJECT_SAMPLE_VALUE = reject_sample_value
REJECT_TYPE= percentage の場合に必要です。これにより、データベースが失敗した行の割合を再計算する前に、インポートを試みる行の数が指定されます。Required when REJECT_TYPE = percentage, this specifies the number of rows to attempt to import before the database recalculates the percentage of failed rows.

たとえば、REJECT_SAMPLE_VALUE = 1000 の場合、データベースは外部データ ファイルから 1000 行のインポートを試みた後、失敗した行の割合を計算します。For example, if REJECT_SAMPLE_VALUE = 1000, the database will calculate the percentage of failed rows after it has attempted to import 1000 rows from the external data file. 失敗した行の割合が reject_value 未満の場合、データベースは別の 1000 行の読み込みを試みます。If the percentage of failed rows is less than reject_value, the database will attempt to load another 1000 rows. データベースは引き続き、その 1000 行のそれぞれのインポートを試みた後、失敗した行の割合を再計算します。The database continues to recalculate the percentage of failed rows after it attempts to import each additional 1000 rows.

注意

データベースは一定の間隔で失敗した行の割合を計算するため、失敗した行の実際の割合が reject_value を超える場合があります。Since the database computes the percentage of failed rows at intervals, the actual percentage of failed rows can exceed reject_value.

例:Example:

この例では、REJECT の 3 つのオプションが相互にやり取りする方法を示します。This example shows how the three REJECT options interact with each other. たとえば、REJECT_TYPE = percentage、REJECT_VALUE = 30、REJECT_SAMPLE_VALUE = 100 の場合、次のようなシナリオとなる可能性があります。For example, if REJECT_TYPE = percentage, REJECT_VALUE = 30, and REJECT_SAMPLE_VALUE = 100, the following scenario could occur:

  • データベースは、最初の 100 行の読み込みを試みます。この場合、失敗が 25、成功が 75 です。The database attempts to load the first 100 rows; 25 fail and 75 succeed.

  • 障害が発生した行の割合は、これは、30% の拒否の値よりも小さい 25%、として計算されます。Percent of failed rows is calculated as 25%, which is less than the reject value of 30%. そのため、読み込みを停止する必要はありません。So, no need to halt the load.

  • データベースは次の 100 行の読み込みを試みます。今回は成功が 25、失敗が 75 です。The database attempts to load the next 100 rows; this time 25 succeed and 75 fail.

  • 失敗した行の割合が 50% として再計算されます。Percent of failed rows is recalculated as 50%. 障害が発生した行の割合が 30% の拒否の値を超えました。The percentage of failed rows has exceeded the 30% reject value.

  • 200 行の読み込みを試みた後、失敗した行の割合が 50% で読み込みに失敗しています。この割合は、指定された制限の 30% を超えています。The load fails with 50% failed rows after attempting to load 200 rows, which is larger than the specified 30% limit.

WITH common_table_expressionWITH common_table_expression
共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。Specifies a temporary named result set, known as a common table expression (CTE). 詳細については、「WITH common_table_expression (Transact-SQL)」を参照してください。For more information, see WITH common_table_expression (Transact-SQL).

SELECT <select_criteria> 新しいテーブルに SELECT ステートメントの結果を設定します。SELECT <select_criteria> Populates the new table with the results from a SELECT statement. select_criteria は、新しいテーブルにコピーするデータを決定する SELECT ステートメントの本文です。select_criteria is the body of the SELECT statement that determines which data to copy to the new table. SELECT ステートメントについては、「SELECT (Transact-SQL)」を参照してください。For information about SELECT statements, see SELECT (Transact-SQL).

アクセス許可Permissions

このコマンドを実行するには、データベース ユーザーは以下のすべての権限またはメンバーシップが必要です。To run this command the database user needs all of these permissions or memberships:

  • db_ddladmin 固定データベース ロールの新しいテーブルまたはメンバーシップを含む、ローカル スキーマに対する ALTER SCHEMA 権限。ALTER SCHEMA permission on the local schema that will contain the new table or membership in the db_ddladmin fixed database role.

  • db_ddladmin 固定データベース ロールの CREATE TABLE 権限またはメンバーシップ。CREATE TABLE permission or membership in the db_ddladmin fixed database role.

  • select_criteria で参照されるすべてのオブジェクトに対する SELECT 権限。SELECT permission on any objects referenced in the select_criteria.

ログインには次のすべての権限が必要です。The login needs all of these permissions:

  • ADMINISTER BULK OPERATIONS 権限ADMINISTER BULK OPERATIONS permission

  • ALTER ANY EXTERNAL DATA SOURCE 権限ALTER ANY EXTERNAL DATA SOURCE permission

  • ALTER ANY EXTERNAL FILE FORMAT 権限ALTER ANY EXTERNAL FILE FORMAT permission

  • ログインには、Hadoop クラスターまたは Azure Storage Blob で外部フォルダーを読み取りおよび書き込みを行うための書き込み権限が必要です。The login must have write permission to read and write to the external folder on the Hadoop Cluster or Azure Storage Blob.

重要

ALTER ANY EXTERNAL DATA SOURCE 権限は、あらゆる外部データ ソース オブジェクトを作成し、変更する能力をプリンシパルに与えます。そのため、データベース上のすべてのデータベース スコープ資格情報にアクセスする能力も与えます。The ALTER ANY EXTERNAL DATA SOURCE permission grants any principal the ability to create and modify any external data source object, and therefore, it also grants the ability to access all database scoped credentials on the database. この権限は特権として考える必要があります。したがって、システム内の信頼できるプリンシパルにのみ与える必要があります。This permission must be considered as highly privileged, and therefore must be granted only to trusted principals in the system.

エラー処理Error Handling

CREATE EXTERNAL TABLE AS SELECT でデータをテキスト区切りファイルにエクスポートした場合、エクスポートに失敗した行に対する拒否ファイルはありません。When CREATE EXTERNAL TABLE AS SELECT exports data to a text-delimited file, there is no rejection file for rows that fail to export.

外部テーブルの作成時に、データベースは外部の Hadoop クラスターまたは Azure Storage Blob への接続を試みます。When creating the external table, the database attempts to connect to the external Hadoop cluster or Azure Storage Blob. 接続に失敗した場合、コマンドは失敗し、外部テーブルは作成されません。If the connection fails, the command will fail and the external table will not be created. データベースは接続を 3 回以上再試行するため、コマンドが失敗するまで 1 分以上かかる場合があります。It can take a minute or more for the command to fail since the database retries the connection at least 3 times.

CREATE EXTERNAL TABLE AS SELECT が取り消されたか、失敗した場合、データベースは、外部データ ソースで既に作成されている新しいファイルとフォルダーの削除を 1 回だけ試みます。If CREATE EXTERNAL TABLE AS SELECT is cancelled or fails, the database will make a one-time attempt to remove any new files and folders already created on the external data source.

データベースは、データのエクスポート時に外部データ ソースで発生したすべての Java エラーを報告します。The database will report any Java errors that occur on the external data source during the data export.

全般的な解説General Remarks

CETAS ステートメントが完了したら、外部テーブルに対して Transact-SQLTransact-SQL クエリを実行できます。After the CETAS statement finishes, you can run Transact-SQLTransact-SQL queries on the external table. CREATE TABLE AS SELECT ステートメントを使用してインポートする場合を除き、これらの操作ではクエリの実行中にデータをデータベースにインポートします。These operations will import data into the database for the duration of the query unless you import by using the CREATE TABLE AS SELECT statement.

外部テーブルの名前と定義は、データベースのメタデータに格納されます。The external table name and definition are stored in the database metadata. データは外部データ ソースに格納されます。The data is stored in the external data source.

外部ファイルの名前は QueryID_date_time_ID.formatです ( ID は増分識別子、 format はエクスポートされるデータ形式)。The external files are named QueryID_date_time_ID.format, where ID is an incremental identifier and format is the exported data format. たとえば、QID776_20160130_182739_0.orc です。For example, QID776_20160130_182739_0.orc.

CETAS ステートメントでは、ソース テーブルがパーティション分割されている場合でも、常に非パーティション テーブルが作成されます。The CETAS statement always creates a non-partitioned table, even if the source table is partitioned.

EXPLAIN で作成されたクエリ プランの場合、データベースは外部テーブルに対して次のクエリ プラン操作を使用します。For query plans, created with EXPLAIN, the databaseuses these query plan operations for external tables:

  • 外部シャッフル移動External shuffle move

  • 外部ブロードキャスト移動External broadcast move

  • 外部パーティション移動External partition move

適用対象:Parallel Data WarehouseParallel Data Warehouse 外部テーブルを作成するための前提条件として、アプライアンス管理者は Hadoop 接続を構成する必要があります。APPLIES TO: Parallel Data WarehouseParallel Data WarehouseAs a prerequisite for creating an external table, the appliance administrator needs to configure hadoop connectivity. 詳細については、ここからダウンロードできる、APS ドキュメントの外部データへの接続の構成 (Analytics Platform System) に関する記述を参照してください。For more information, see Configure Connectivity to External Data (Analytics Platform System) in the APS documentation which you can download from here.

制限事項と制約事項Limitations and Restrictions

外部テーブル データがデータベースの外部にあるため、バックアップおよび復元操作ではデータベースに格納されているデータに対してのみ作用します。Since external table data resides outside of the database, backup and restore operations will only operate on data stored in the database. つまり、メタデータのみがバックアップされ、復元されます。This means only the metadata will be backed up and restored.

データベースでは、外部テーブルを含むデータベース バックアップを復元する際に、外部データ ソースへの接続は確認されません。The database does not verify the connection to the external data source when restoring a database backup that contains an external table. 元のソースにアクセスできない場合でも、外部テーブルのメタデータの復元は正常に行われますが、外部テーブルでの SELECT 操作は失敗します。If the original source is not accessible, the metadata restore of the external table will still succeed, but SELECT operations on the external table will fail.

データベースでは、データベースと外部テーブル間のデータの一貫性は保証されません。The database does not guarantee data consistency between the databaseand the external data. 外部データとデータベース間の一貫性を保つことだけは、お客様が行う必要があります。You, the customer, are solely responsible to maintain consistency between the external data and the database.

外部テーブルでのデータ操作言語 (DML) 操作はサポートされていません。Data manipulation language (DML) operations are not supported on external tables. たとえば、Transact-SQLTransact-SQL の UPDATE、INSERT、DELETE Transact-SQLTransact-SQL ステートメントを使用して、外部データを変更することはできません。For example, you cannot use the Transact-SQLTransact-SQL update, insert, or delete Transact-SQLTransact-SQLstatements to modify the external data.

外部テーブルで許可されるデータ定義言語 (DDL) 操作は、CREATE TABLE、DROP TABLE、CREATE STATISTICS、DROP STATISTICS、CREATE VIEW、DROP VIEW のみです。CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW, and DROP VIEW are the only data definition language (DDL) operations allowed on external tables.

PolyBase は 32 の同時実行 PolyBase クエリを実行している場合、最大で 1 つのフォルダーの 33 k ファイルを使用できます。PolyBase can consume a maximum of 33k files per folder when running 32 concurrent PolyBase queries. この最大数には、各 HDFS フォルダー内のファイルとサブフォルダーの両方が含まれます。This maximum number includes both files and subfolders in each HDFS folder. コンカレンシーの程度が 32 未満である場合は、ユーザーは、複数の 33 k ファイルが含まれている HDFS のフォルダーに対して PolyBase クエリを実行できます。If the degree of concurrency is less than 32, a user can run PolyBase queries against folders in HDFS which contain more than 33k files. MicrosoftMicrosoft では、Hadoop および PolyBase のユーザーがファイル パスを短くし、HDFS フォルダーごとに 30K 以下のファイルを使用することをお勧めします。recommends users of Hadoop and PolyBase keep file paths short and use no more than 30k files per HDFS folder. 参照されているファイルが多すぎると、JVM のメモリ不足例外が発生します。When too many files are referenced a JVM out-of-memory exception occurs.

この CREATE EXTERNAL TABLE AS SELECT では SET ROWCOUNT (Transact-SQL) の効果はありません。SET ROWCOUNT (Transact-SQL) has no effect on this CREATE EXTERNAL TABLE AS SELECT. 同様の動作を実現するには、TOP (Transact-SQL) を使用します。To achieve a similar behavior, use TOP (Transact-SQL).

CREATE EXTERNAL TABLE AS SELECT で RCFile から選択する場合、RCFile の列値にパイプ '|' 文字を含めることはできません。When CREATE EXTERNAL TABLE AS SELECT selects from an RCFile, the column values in the RCFile must not contain the pipe '|' character.

ロックLocking

SCHEMARESOLUTION オブジェクトの共有ロックを取得します。Takes a shared lock on the SCHEMARESOLUTION object.

使用例Examples

A.A. CREATE EXTERNAL TABLE AS SELECT (CETAS) を使用して Hadoop テーブルを作成するCreate a Hadoop table using CREATE EXTERNAL TABLE AS SELECT (CETAS)

次の例では、ソース テーブル dimCustomer の列の定義とデータを使用して、hdfsCustomer という名前の新しい外部テーブルを作成します。The following example creates a new external table named hdfsCustomer, using the column definitions and data from the source table dimCustomer.

テーブル定義はデータベースに格納され、SELECT ステートメントの結果は、Hadoop 外部データソース customer_ds の '/pdwdata/customer.tbl' ファイルにエクスポートされます。The table definition is stored in the database, and the results of the SELECT statement are exported to the '/pdwdata/customer.tbl' file on the Hadoop external data source customer_ds. ファイルは、外部ファイル形式 customer_ff に従って書式設定されます。The file is formatted according to the external file format customer_ff.

ファイル名はデータベースによって生成され、クエリ ID を含みます。これにより、ファイルを生成元のクエリに合わせやすくなります。The file name is generated by the database, and contains the query ID for ease of aligning the file with the query that generated it.

Customer ディレクトリの前のパス hdfs://xxx.xxx.xxx.xxx:5000/files/ は既に存在している必要があります。The path hdfs://xxx.xxx.xxx.xxx:5000/files/ preceding the Customer directory must already exist. ただし、Customer ディレクトリが存在しない場合は、データベースでそのディレクトリが作成されます。However, if the Customer directory does not exist, the database will create the directory.

注意

この例では 5000 に指定されています。This example specifies for 5000. ポートが指定されていない場合、データベースは既定のポートとして 8020 を使用します。If the port is not specified, the database uses 8020 as the default port.

結果の Hadoop の場所とファイル名は hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt. です。The resulting Hadoop location and file name will be 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;  

B.B. CREATE EXTERNAL TABLE AS SELECT (CETAS) でクエリ ヒントを使用するUse a Query Hint with CREATE EXTERNAL TABLE AS SELECT (CETAS)

このクエリは、CETAS ステートメントでクエリ結合ヒントを使用する場合の基本構文を示しています。This query shows the basic syntax for using a query join hint with the CETAS statement. クエリが送信された後、データベースではハッシュ結合方法を使用して、クエリ プランを生成します。After the query is submitted the database uses the hash join strategy to generate the query plan. 結合ヒントと OPTION 句の使用方法の詳細については、「OPTION 句 (Transact-SQL)」を参照してください。For more information on join hints and how to use the OPTION clause, see OPTION Clause (Transact-SQL).

注意

この例では 5000 に指定されています。This example specifies for 5000. ポートが指定されていない場合、データベースは既定のポートとして 8020 を使用します。If the port is not specified, the database uses 8020 as the default port.

  
      -- 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 JOIN dbo.DimCustomer T2  
ON ( T1.CustomerKey = T2.CustomerKey )  
OPTION ( HASH JOIN );  

参照See Also

CREATE EXTERNAL DATA SOURCE (Transact-SQL) CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL FILE FORMAT (Transact-SQL) CREATE EXTERNAL FILE FORMAT (Transact-SQL)
CREATE EXTERNAL TABLE (Transact-SQL) CREATE EXTERNAL TABLE (Transact-SQL)
CREATE TABLE (Azure SQL Data Warehouse、Parallel Data Warehouse) CREATE TABLE (Azure SQL Data Warehouse, Parallel Data Warehouse)
CREATE TABLE AS SELECT (Azure SQL Data Warehouse) CREATE TABLE AS SELECT (Azure SQL Data Warehouse)
DROP TABLE (Transact-SQL) DROP TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)