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

適用対象:Applies to: はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse適用対象:Applies to: はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

外部テーブルを作成してから、Hadoop または Azure 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 Blob storage.

トピック リンク アイコン 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 . [ schema_name ] . ] | schema_name . ] table_name は、データベースに作成するテーブルの 1 から 3 部構成の名前です。[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name is 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' には、外部データ ソースで SELECT ステートメントの結果を書き込む場所を指定します。LOCATION = 'hdfs_folder' specifies where to write the results of the SELECT statement on the external data source. 場所はフォルダー名であり、必要に応じて、Hadoop クラスターまたは BLOB ストレージのルート フォルダーへの相対パスを含めることができます。The location is a folder name and can optionally include a path that's relative to the root folder of the Hadoop cluster or Blob storage. PolyBase ではパスとフォルダー (まだ存在しない場合) が作成されます。PolyBase will create the path and folder if it doesn't 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 です。An example is QID776_20160130_182739_0.orc.

DATA_SOURCE = external_data_source_name には、外部データが格納されている、または格納される場所を含む、外部データ ソース オブジェクトの名前を指定します。DATA_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 Blob ストレージのいずれかです。The location is either a Hadoop cluster or an Azure Blob storage. 外部データ ソースを作成するには、CREATE EXTERNAL DATA SOURCE (Transact-SQL) を使用します。To create an external data source, use CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name には、外部データ ファイルの形式を含む、外部ファイル形式オブジェクトの名前を指定します。FILE_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).

この CREATE EXTERNAL TABLE AS SELECT ステートメントの実行時に、REJECT オプションは適用されません。REJECT options don't apply at the time this CREATE EXTERNAL TABLE AS SELECT statement is run. 代わりに、ここで指定し、後で外部テーブルからデータをインポートする際に、データベースで使用できるようにします。Instead, they're 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_value には、インポートに失敗することができる行の値または割合を指定します。これを超えると、データベースのインポートが停止されます。REJECT_VALUE = reject_value specifies the value or the percentage of rows that can fail to import before the database halts the import.

  • REJECT_TYPE = value | percentage を使用して、REJECT_VALUE オプションがリテラル値として指定されているか、割合として指定されているかを明確にします。REJECT_TYPE = value | percentage clarifies whether the REJECT_VALUE option is specified as a literal value or a percentage.

    • Value は、REJECT_VALUE が割合ではなくリテラル値の場合に使用されます。Value is used if 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 five rows have failed to import.

    • Percentage は、REJECT_VALUE がリテラル値ではなく割合の場合に使用されます。Percentage is used if 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_value は、REJECT_TYPE= percentage の場合に必要です。これにより、データベースが失敗した行の割合を再計算する前に、インポートを試みる行の数が指定されます。REJECT_SAMPLE_VALUE = reject_sample_value is 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 を超える場合があります。Because 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, of which 25 fail and 75 succeed.
    • 失敗した行の割合は 25% と計算されます。これは reject 値である 30% を下回っています。The percent of failed rows is calculated as 25%, which is less than the reject value of 30%. そのため、読み込みを停止する必要はありません。So, there's no need to halt the load.
    • データベースでは、次の 100 行の読み込みが試行されます。The database attempts to load the next 100 rows. 今回は成功が 25、失敗が 75 です。This time 25 succeed and 75 fail.
    • 失敗した行の割合が 50% として再計算されます。The percent of failed rows is recalculated as 50%. 失敗した行の割合が、30% という reject 値を超えました。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_expression には、共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。WITH common_table_expression 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 OPERATIONSADMINISTER BULK OPERATIONS
  • ALTER ANY EXTERNAL DATA SOURCEALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMATALTER ANY EXTERNAL FILE FORMAT
  • Hadoop クラスターまたは BLOB ストレージで外部フォルダーを読み取りおよび書き込みを行うための書き込み権限。Write permission to read and write to the external folder on the Hadoop cluster or in Blob storage.

重要

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, so it also grants the ability to access all database scoped credentials on the database. この権限は特権として考える必要があり、システム内の信頼できるプリンシパルにのみ与える必要があります。This permission must be considered as highly privileged and 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's no rejection file for rows that fail to export.

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

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

CREATE EXTERNAL TABLE AS SELECT ステートメントが完了したら、外部テーブルに対して Transact-SQLTransact-SQL クエリを実行できます。After the CREATE EXTERNAL TABLE AS SELECT 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 です。An example is QID776_20160130_182739_0.orc.

CREATE EXTERNAL TABLE AS SELECT ステートメントを使用すると、ソース テーブルがパーティション分割されている場合でも、常にパーティション分割されていないテーブルが作成されます。The CREATE EXTERNAL TABLE AS SELECT statement always creates a nonpartitioned table, even if the source table is partitioned.

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

  • 外部シャッフル移動External shuffle move
  • 外部ブロードキャスト移動External broadcast move
  • 外部パーティション移動External partition move

適用対象: Parallel Data WarehouseApplies to: Parallel Data Warehouse

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

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

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

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

データベースでは、データベースと外部データ間のデータの一貫性は保証されません。The database doesn't guarantee data consistency between the database and 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 aren't supported on external tables. たとえば、Transact-SQLTransact-SQL の UPDATE、INSERT、DELETE Transact-SQLTransact-SQL ステートメントを使用して、外部データを変更することはできません。For example, you can't 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 クエリを実行しているときに、フォルダーあたり最大 33,000 ファイルを使用できます。PolyBase can consume a maximum of 33,000 files per folder when running 32 concurrent PolyBase queries. この最大数には、各 HDFS フォルダー内のファイルとサブフォルダーの両方が含まれます。This maximum number includes both files and subfolders in each HDFS folder. コンカレンシーの度合いが 32 未満である場合、ユーザーは 33,000 より多いファイルが含まれている HDFS のフォルダーに対して PolyBase クエリを実行できます。If the degree of concurrency is less than 32, a user can run PolyBase queries against folders in HDFS that contain more than 33,000 files. Hadoop および PolyBase のユーザーがファイル パスを短くし、HDFS フォルダーごとに 30,000 以下のファイルを使用することをお勧めします。We recommend that users of Hadoop and PolyBase keep file paths short and use no more than 30,000 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.

Parquet または ORC ファイルに対する CREATE EXTERNAL TABLE AS SELECT では、次の文字がデータ内に存在する場合、拒否されたレコードが含まれる可能性があるエラーが発生します。CREATE EXTERNAL TABLE AS SELECT to Parquet or ORC files will cause errors, which can include rejected records when the following characters are present in the data:

  • |
  • " (引用符文字)“ (quotation mark character)
  • /r/n/r/n
  • /r/r
  • /n/n

これらの文字が含まれる CREATE EXTERNAL TABLE AS SELECT を使用するには、最初にデータに CREATE EXTERNAL TABLE AS SELECT を実行して区切りテキストファイルを作成する必要があり、その後は外部ツールを使用して Parquet または ORC に変換することができます。To use CREATE EXTERNAL TABLE AS SELECT containing these characters, you must first CREATE EXTERNAL TABLE AS SELECT the data to delimited text files where you can then convert them to Parquet or ORC by using an external tool.

ロックLocking

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

使用例Examples

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

次の例では、ソース テーブル dimCustomer の列の定義とデータを使用して、hdfsCustomer という名前の新しい外部テーブルを作成します。The following example creates a new external table named hdfsCustomer that uses 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 ディレクトリが存在しない場合は、データベースでそのディレクトリが作成されます。If the Customer directory doesn't exist, the database will create the directory.

注意

この例では 5000 に指定されています。This example specifies for 5000. ポートが指定されていない場合、データベースは既定のポートとして 8020 を使用します。If the port isn't 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 でクエリ ヒントを使用するUse a query hint with CREATE EXTERNAL TABLE AS SELECT

このクエリは、CREATE EXTERNAL TABLE AS SELECT ステートメントでクエリ結合ヒントを使用する場合の基本的な構文を示しています。This query shows the basic syntax for using a query join hint with the CREATE EXTERNAL TABLE AS SELECT 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 isn't 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