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

外部テーブルの作成Creates an external table

この記事では、選択した SQL 製品について、構文、引数、注釈、アクセス許可、例を紹介します。This article provides the syntax, arguments, remarks, permissions, and examples for whichever SQL product you choose.

構文表記規則の詳細については、「Transact-SQL 構文表記規則」を参照してください。For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

製品をクリックしてくださいClick a product!

次の行から興味がある製品名をクリックしてみてください。In the following row, click whichever product name you are interested in. この Web ページでは、クリックした製品に合わせて、異なるコンテンツが表示されます。The click displays different content here on this webpage, appropriate for whichever product you click.

* SQL Server *  * SQL Server *   SQL DatabaseSQL Database SQL Data
Warehouse
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概要:SQL ServerOverview: SQL Server

このコマンドでは、Hadoop クラスターに格納されているデータに PolyBase でアクセスするための外部テーブルが作成されるか、Hadoop クラスターまたは Azure Blob ストレージに格納されているデータを参照する Azure Blob ストレージ PolyBase 外部テーブルが作成されます。This command creates an external table for PolyBase to access data stored in a Hadoop cluster or Azure blob storage PolyBase external table that references data stored in a Hadoop cluster or Azure blob storage.

適用対象:SQL Server 2016 (以降)APPLIES TO: SQL Server 2016 (or higher)

PolyBase クエリ用の外部データ ソースが含まれる外部テーブルを使用します。Use an external table with an external data source for PolyBase queries. 外部データ ソースを使用して接続を確立し、次の主なユース ケースをサポートします。External data sources are used to establish connectivity and support these primary use cases:

  • PolyBase を使用したデータ仮想化とデータ読み込みData virtualization and data load using PolyBase
  • BULK INSERT または OPENROWSET を使用して、SQL Server または SQL Database を使用した一括読み込み操作Bulk load operations using SQL Server or SQL Database using BULK INSERT or OPENROWSET

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

構文Syntax

-- 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
}

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

引数Arguments

{ database_name.schema_name.table_name | schema_name.table_name | table_name } 。作成するテーブルの 1 つから 3 つの部分で構成される名前。{ database_name.schema_name.table_name | schema_name.table_name | table_name } The one to three-part name of the table to create. 外部テーブルの場合、SQL では、Hadoop または Azure Blob Storage 内で参照されているファイルまたはフォルダーに関する基本的な統計情報と共に、テーブルのメタデータのみが格納されます。For an external table, SQL stores only the table metadata along with basic statistics about the file or folder that is referenced in Hadoop or Azure blob storage. 実際のデータは移動されず、SQL ServerSQL Server に格納されません。No actual data is moved or stored in SQL ServerSQL Server.

<column_definition> [ ,...n ]。CREATE EXTERNAL TABLE では、列名、データ型、NULL 値の許容、照合順序を構成できます。<column_definition> [ ,...n ] CREATE EXTERNAL TABLE supports the ability to configure column name, data type, nullability and collation. 外部テーブルに対して DEFAULT CONSTRAINT を使用することはできません。You can't use the DEFAULT CONSTRAINT on external tables.

データ型と列の数を含む列の定義は、外部ファイルのデータと一致している必要があります。The column definitions, including the data types and number of columns, must match the data in the external files. 不一致がある場合、実際のデータに対してクエリを実行するときに、ファイルの行が拒否されます。If there's a mismatch, the file rows will be rejected when querying the actual data.

LOCATION = 'folder_or_filepath'。Hadoop または Azure Blob Storage にある実際のデータのフォルダーまたはファイル パスとファイル名を指定します。LOCATION = 'folder_or_filepath' Specifies the folder or the file path and file name for the actual data in Hadoop or Azure blob storage. ルート フォルダーから、場所を開始します。The location starts from the root folder. ルート フォルダーは、外部データ ソースで指定されたデータの場所です。The root folder is the data location specified in the external data source.

SQL Server では、CREATE EXTERNAL TABLE ステートメントによって、まだ存在しない場合にパスとフォルダーが作成されます。In SQL Server, the CREATE EXTERNAL TABLE statement creates the path and folder if it doesn't already exist. その後、INSERT INTO を使用して、ローカルの SQL Server テーブルからのデータを外部データ ソースをエクスポートすることができます。You can then use INSERT INTO to export data from a local SQL Server table to the external data source. 詳細については、PolyBase クエリに関するページを参照してください。For more information, see PolyBase Queries.

LOCATION をフォルダーとして指定した場合、外部テーブルから選択する PolyBase クエリでは、フォルダーとそのすべてのサブフォルダーからファイルが取得されます。If you specify LOCATION to be a folder, a PolyBase query that selects from the external table will retrieve files from the folder and all of its subfolders. Hadoop と同じように PolyBase で非表示のフォルダーは返されません。Just like Hadoop, PolyBase doesn't return hidden folders. ファイル名が下線 () またはピリオド (.) で始まるファイルも返されません。It also doesn't return files for which the file name begins with an underline () or a period (.).

この例では、LOCATION='/webdata/' である場合、PolyBase クエリでは mydata.txt と mydata2.txt から行が返されます。In this example, if LOCATION='/webdata/', a PolyBase query will return rows from mydata.txt and mydata2.txt. mydata3.txt は非表示のフォルダーのサブフォルダーであるため、返されません。It won't return mydata3.txt because it's a subfolder of a hidden folder. また、_hidden.txt は非表示のファイルであるため返されません。And it won't return _hidden.txt because it's a hidden file.

外部テーブルの再帰型データRecursive data for external tables

既定値を変更して、読み取りをルート フォルダーからのみに限定するには、core-site.xml 構成ファイル内で属性 <polybase.recursive.traversal> を 'false' に設定します。To change the default and only read from the root folder, set the attribute <polybase.recursive.traversal> to 'false' in the core-site.xml configuration file. このファイルは <SqlBinRoot>\PolyBase\Hadoop\Conf with SqlBinRoot the bin root of SQl Server の配下に配置されます。This file is located under <SqlBinRoot>\PolyBase\Hadoop\Conf with SqlBinRoot the bin root of SQl Server. たとえば、C:\\Program Files\\Microsoft SQL Server\\MSSQL13.XD14\\MSSQL\\Binn のようになります。For example, C:\\Program Files\\Microsoft SQL Server\\MSSQL13.XD14\\MSSQL\\Binn.

DATA_SOURCE = external_data_source_name。外部データの場所を含む外部データ ソースの名前を指定します。DATA_SOURCE = external_data_source_name Specifies the name of the external data source that contains the location of the external data. この場所は、Hadoop または Azure BLOB ストレージです。This location is either a Hadoop or Azure blob storage. 外部データ ソースを作成するには、CREATE EXTERNAL DATA SOURCE を使用します。To create an external data source, use CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name。外部データのファイルの種類と圧縮方法を格納する外部ファイル形式のオブジェクトの名前を指定します。FILE_FORMAT = external_file_format_name Specifies the name of the external file format object that stores the file type and compression method for the external data. 外部ファイル形式を作成するには、CREATE EXTERNAL FILE FORMAT を使用します。To create an external file format, use CREATE EXTERNAL FILE FORMAT.

拒否オプション。PolyBase が外部データ ソースから取得したダーティ レコードを処理する方法を決定する、reject パラメーターを指定できます。Reject Options You can specify reject parameters that determine how PolyBase will handle dirty records it retrieves from the external data source. データ レコードの実際のデータの種類または列の数が、外部テーブルの列の定義と一致しない場合、そのデータ レコードは "ダーティ" と見なされます。A data record is considered 'dirty' if it actual data types or the number of columns don't match the column definitions of the external table.

reject 値を指定または変更しない場合、PolyBase では既定値が使用されます。When you don't specify or change reject values, PolyBase uses default values. reject パラメーターに関するこの情報は、CREATE EXTERNAL TABLE ステートメントを使用して外部テーブルを作成するときに、追加メタデータとして格納されます。This information about the reject parameters is stored as additional metadata when you create an external table with CREATE EXTERNAL TABLE statement. 以降の SELECT ステートメントまたは SELECT INTO SELECT ステートメントで外部テーブルからデータを選択するとき、拒否オプションを使用して、実際のクエリが失敗するまでに拒否できる行の数または割合が PolyBase によって決定されます。When a future SELECT statement or SELECT INTO SELECT statement selects data from the external table, PolyBase will use the reject options to determine the number or percentage of rows that can be rejected before the actual query fails. 拒否のしきい値を超えるまで、クエリの (部分的な) 結果が返されます。The query will return (partial) results until the reject threshold is exceeded. その後、適切なエラー メッセージと共に失敗します。It then fails with the appropriate error message.

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 REJECT_VALUE is a literal value, not a percentage. 拒否された行が reject_value を超えると、PolyBase クエリは失敗します。The PolyBase query will fail when the number of rejected rows exceeds reject_value.

たとえば、REJECT_VALUE = 5 で REJECT_TYPE = value の場合、PolyBase の SELECT クエリは、5 行を拒否した後に失敗します。For example, if REJECT_VALUE = 5 and REJECT_TYPE = value, the PolyBase SELECT query will fail after five rows have been rejected.

percentage。REJECT_VALUE は、リテラル値ではなく割合です。percentage REJECT_VALUE is a percentage, not a literal value. 失敗した行の percentagereject_value を超えると、PolyBase クエリは失敗します。A PolyBase query will fail when the percentage of failed rows exceeds reject_value. 失敗した行の割合は、間隔をおいて計算されます。The percentage of failed rows is calculated at intervals.

REJECT_VALUE = reject_value。クエリが失敗する前に拒否できる行を値または割合で指定します。REJECT_VALUE = reject_value Specifies the value or the percentage of rows that can be rejected before the query fails.

REJECT_TYPE = value の場合、reject_value は 0 から 2,147, 483,647 の範囲の整数にする必要があります。For REJECT_TYPE = value, reject_value must be an integer between 0 and 2,147,483,647.

REJECT_TYPE = percentage の場合、reject_value は 0 から 100 の範囲の浮動小数にする必要があります。For REJECT_TYPE = percentage, reject_value must be a float between 0 and 100.

REJECT_SAMPLE_VALUE = reject_sample_value。REJECT_TYPE = percentage を指定する場合、この属性は必須です。REJECT_SAMPLE_VALUE = reject_sample_value This attribute is required when you specify REJECT_TYPE = percentage. それにより、拒否された行の割合が PolyBase によって再計算されるまでに取得が試行される行の数が決定します。It determines the number of rows to attempt to retrieve before the PolyBase recalculates the percentage of rejected rows.

reject_sample_value パラメーターは、0 から 2,147,483,647 の範囲の整数にする必要があります。The reject_sample_value parameter must be an integer between 0 and 2,147,483,647.

たとえば、REJECT_SAMPLE_VALUE = 1000 の場合、PolyBase によって外部データ ファイルから 1000 行のインポートが試みられた後、失敗した行の割合が再計算されます。For example, if REJECT_SAMPLE_VALUE = 1000, PolyBase will calculate the percentage of failed rows after it has attempted to import 1000 rows from the external data file. 失敗した行のパーセンテージが reject_value 未満の場合、PolyBase は別の 1000 行の取得を試みます。If the percentage of failed rows is less than reject_value, PolyBase will attempt to retrieve another 1000 rows. 1000 行ずつ追加でインポートを試みた後、失敗した行の割合の再計算を続けます。It continues to recalculate the percentage of failed rows after it attempts to import each additional 1000 rows.

注意

PolyBase では間隔を置いて失敗した行のパーセンテージを計算するため、実際の失敗した行のパーセンテージは、reject_value を超える場合があります。Since PolyBase 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:

  • PolyBase で最初の 100 行の取得が試みられ、25 行が失敗し、75 行が成功しました。PolyBase attempts to retrieve the first 100 rows; 25 fail and 75 succeed.
  • 失敗した行の割合は 25% と計算されます。これは reject 値である 30% を下回っています。Percent of failed rows is calculated as 25%, which is less than the reject value of 30%. その結果、PolyBase は引き続き、外部データ ソースからデータを取得します。As a result, PolyBase will continue retrieving data from the external data source.
  • PolyBase は次の 100 行の読み込みを試み、今回は 25 行が成功し、75 行が失敗しました。PolyBase attempts to load the next 100 rows; this time 25 rows succeed and 75 rows fail.
  • 失敗した行の割合が 50% として再計算されます。Percent of failed rows is recalculated as 50%. 失敗した行の割合が、30% という reject 値を超えました。The percentage of failed rows has exceeded the 30% reject value.
  • PolyBase クエリは、最初の 200 行を取得しようとした後、拒否された行 50% で失敗します。The PolyBase query fails with 50% rejected rows after attempting to return the first 200 rows. 拒否のしきい値を超えたことが PolyBase クエリによって検出される前に、一致した行が返されていることに注意してください。Notice that matching rows have been returned before the PolyBase query detects the reject threshold has been exceeded.

DATA_SOURCE。Hadoop ファイル システム、Azure Blob Storage、または Shard Map Manager に格納されているデータなどの外部データ ソースです。DATA_SOURCE An external data source such as data stored in a Hadoop File System, Azure blob storage, or a shard map manager.

SCHEMA_NAME。SCHEMA_NAME 句では、外部テーブルの定義をリモート データベース上のスキーマの異なるテーブルにマップする機能が提供されます。SCHEMA_NAME The SCHEMA_NAME clause provides the ability to map the external table definition to a table in a different schema on the remote database. ローカルおよびリモートの両方のデータベースに存在するスキーマ間であいまいさを排除するには、この句を使用します。Use this clause to disambiguate between schemas that exist on both the local and remote databases.

OBJECT_NAME。OBJECT_NAME 句では、外部テーブルの定義をリモート データベース上の別の名前を持つテーブルにマップする機能が提供されます。OBJECT_NAME The OBJECT_NAME clause provides the ability to map the external table definition to a table with a different name on the remote database. ローカルおよびリモートの両方のデータベースに存在するオブジェクト名の間であいまいさを排除するには、この句を使用します。Use this clause to disambiguate between object names that exist on both the local and remote databases.

DISTRIBUTION。任意。DISTRIBUTION Optional. これは SHARD_MAP_MANAGER 型のデータベースの場合にのみ必須です。This argument is only required for databases of type SHARD_MAP_MANAGER. この引数では、テーブルをシャード化されたテーブルとして扱うか、レプリケートされたテーブルとして扱うかを制御します。This argument controls whether a table is treated as a sharded table or a replicated table. SHARDED (列名) テーブルでは、異なるテーブルからのデータは重複しません。With SHARDED (column name) tables, the data from different tables don't overlap. REPLICATED は、テーブルですべてのシャードに同じデータを持つことを指定します。REPLICATED specifies that tables have the same data on every shard. ROUND_ROBIN は、データを分散するためにアプリケーション固有のメソッドが使用されていることを示します。ROUND_ROBIN indicates that an application-specific method is used to distribute the data.

アクセス許可Permissions

これらのユーザー アクセス許可が必要です。Requires these user permissions:

  • CREATE TABLECREATE TABLE
  • ALTER ANY SCHEMAALTER ANY SCHEMA
  • ALTER ANY EXTERNAL DATA SOURCEALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMATALTER ANY EXTERNAL FILE FORMAT
  • CONTROL DATABASECONTROL DATABASE

外部データ ソースを作成するログインには、Hadoop または Azure Blob Storage 内にある外部データ ソースに対する読み取りおよび書き込みの権限が必要であることに注意してください。Note, the login that creates the external data source must have permission to read and write to the external data source, located in Hadoop or Azure 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, 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 ステートメントの実行中に、PolyBase から外部データ ソースへの接続が試みられます。While executing the CREATE EXTERNAL TABLE statement, PolyBase attempts to connect to the external data source. 接続が失敗した場合、ステートメントは失敗し、外部テーブルは作成されません。If the attempt to connect fails, the statement will fail and the external table won't be created. クエリが最終的に失敗となる前に、PolyBase によって接続が再試行されるため、コマンドが失敗するまで 1 分以上かかる可能性があります。It can take a minute or more for the command to fail since PolyBase retries the connection before eventually failing the query.

全般的な解説General Remarks

SELECT FROM EXTERNAL TABLE などのアドホック クエリのシナリオの場合、PolyBase では外部データ ソースから取得された行が一時テーブルに格納されます。In ad-hoc query scenarios, such as SELECT FROM EXTERNAL TABLE, PolyBase stores the rows that are retrieved from the external data source in a temporary table. クエリ完了後、PolyBase によって一時テーブルが削除されます。After the query completes, PolyBase removes and deletes the temporary table. SQL テーブルには、永続的なデータは格納されません。No permanent data is stored in SQL tables.

これに対し、SELECT INTO FROM EXTERNAL TABLE などのインポートのシナリオでは、外部データ ソースから取得された行が、PolyBase によって永続的なデータとして SQL テーブルに格納されます。In contrast, in the import scenario, such as SELECT INTO FROM EXTERNAL TABLE, PolyBase stores the rows that are retrieved from the external data source as permanent data in the SQL table. PolyBase が外部のデータを取得するときに、クエリの実行中に、新しいテーブルが作成されます。The new table is created during query execution when PolyBase retrieves the external data.

PolyBase では、クエリのパフォーマンスを向上させるためにクエリ計算の一部を Hadoop にプッシュできます。PolyBase can push some of the query computation to Hadoop to improve query performance. このアクションは述語プッシュダウンと呼ばれます。This action is called predicate pushdown. それを有効にするには、CREATE EXTERNAL DATA SOURCE で、Hadoop のリソース マネージャーの場所のオプションを指定します。To enable it, specify the Hadoop resource manager location option in CREATE EXTERNAL DATA SOURCE.

同じまたは別の外部データ ソースを参照している多数の外部テーブルを作成できます。You can create many external tables that reference the same or different external data sources.

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

外部テーブルのデータは SQL Server 内にないため、PolyBase の管理下にはなく、外部プロセスによっていつでも変更または削除できます。Since the data for an external table is not within SQL Server, it isn't under the control of PolyBase, and can be changed or removed at any time by an external process. その結果、外部のテーブルに対するクエリの結果は確定的であることが保証されません。As a result, query results against an external table aren't guaranteed to be deterministic. 同じクエリを外部のテーブルに対して実行するたびに、異なる結果が返される可能性があります。The same query can return different results each time it runs against an external table. 同様に、外部データが移動または削除された場合、クエリが失敗する可能性があります。Similarly, a query might fail if the external data is moved or removed.

それぞれが異なる外部データ ソースを参照する複数の外部テーブルを作成できます。You can create multiple external tables that each reference different external data sources. 異なる複数の Hadoop データ ソースに対して同時にクエリを実行する場合は、各 Hadoop ソースに同じ 'Hadoop 接続' サーバー構成の設定が使用されている必要があります。If you simultaneously run queries against different Hadoop data sources, then each Hadoop source must use the same 'hadoop connectivity' server configuration setting. たとえば、ことはできません同時にクエリを実行する Cloudera Hadoop クラスターと Hortonworks の Hadoop クラスターに対してこれらさまざまな構成設定を使用するためです。For example, you can't simultaneously run a query against a Cloudera Hadoop cluster and a Hortonworks Hadoop cluster since these use different configuration settings. 構成設定とサポートされる組み合わせについては、「PolyBase 接続構成」を参照してください。For the configuration settings and supported combinations, see PolyBase Connectivity Configuration.

外部テーブルに対しては、これらのデータ定義言語 (DDL) ステートメントのみを使用できます。Only these Data Definition Language (DDL) statements are allowed on external tables:

  • CREATE TABLE および DROP TABLECREATE TABLE and DROP TABLE
  • CREATE STATISTICS および DROP STATISTICSCREATE STATISTICS and DROP STATISTICS
  • CREATE VIEW および DROP VIEWCREATE VIEW and DROP VIEW

サポートされていない構成要素と操作:Constructs and operations not supported:

  • 外部テーブルの列に対する DEFAULT 制約The DEFAULT constraint on external table columns
  • データ操作言語 (DML) の削除、挿入、更新の操作Data Manipulation Language (DML) operations of delete, insert, and update

クエリの制限事項:Query limitations:

PolyBase では、32 個の同時 PolyBase クエリを実行しているときに、フォルダーあたり最大 33,000 ファイルを使用できます。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,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 33k files. 外部ファイルのパスを短く維持し、使用するファイルの数を HDFS フォルダーあたり 30,000 以下にすることをお勧めします。We recommend that you keep external file paths short and use no more than 30k files per HDFS folder. 参照しているファイルの数が多すぎる場合は、Java 仮想マシン (JVM) のメモリ不足例外が発生する可能性があります。When too many files are referenced, a Java Virtual Machine (JVM) out-of-memory exception might occur.

テーブルの幅の制限:Table width limitations:

SQL Server 2016 の PolyBase には、テーブル定義による有効な 1 つの行の最大幅に基づいた、行の幅 32 KB という制限があります。PolyBase in SQL Server 2016 has a row width limit of 32 KB based on the maximum size of a single valid row by table definition. 列スキーマの合計が 32 KB を超えている場合、PolyBase によるデータのクエリは実行できません。If the sum of the column schema is greater than 32 KB, PolyBase can't query the data.

ロックLocking

SCHEMARESOLUTION オブジェクトに対する共有ロック。Shared lock on the SCHEMARESOLUTION object.

SecuritySecurity

外部テーブルのデータ ファイルは Hadoop または Azure Blob Storage に格納されます。The data files for an external table are stored in Hadoop or Azure blob storage. これらのデータ ファイルはご自身のプロセスによって作成され、管理されます。These data files are created and managed by your own processes. 外部データのセキュリティを管理することは、ユーザー自身の責任になります。It is your responsibility to manage the security of the external data.

使用例Examples

A.A. テキスト区切り形式のデータを含む外部テーブルを作成しますCreate an external table with data in text-delimited format

この例では、テキスト区切りのファイルで書式設定されたデータを含む外部テーブルを作成するために必要なすべての手順を示します。This example shows all the steps required to create an external table that has data formatted in text-delimited files. これは、外部データ ソース mydatasourcec と外部ファイルの形式 myfileformat を定義します。It defines an external data source mydatasource and an external file format myfileformat. その後、これらのデータベース レベル オブジェクトは、CREATE EXTERNAL TABLE ステートメントで参照されます。These database-level objects are then referenced in the CREATE EXTERNAL TABLE statement. 詳細については、「CREATE EXTERNAL DATA SOURCE」と「CREATE EXTERNAL FILE FORMAT」を参照してください。For more information, see CREATE EXTERNAL DATA SOURCE and 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.B. RCFile 形式のデータを含む外部テーブルを作成しますCreate an external table with data in RCFile format

この例では、RCFile として書式設定されたデータを含む外部テーブルを作成するために必要なすべての手順を示します。This example shows all the steps required to create an external table that has data formatted as RCFiles. これは、外部データ ソース mydatasource_rc と外部ファイルの形式 myfileformat_rc を定義します。It defines an external data source mydatasource_rc and an external file format myfileformat_rc. その後、これらのデータベース レベル オブジェクトは、CREATE EXTERNAL TABLE ステートメントで参照されます。These database-level objects are then referenced in the CREATE EXTERNAL TABLE statement. 詳細については、「CREATE EXTERNAL DATA SOURCE」と「CREATE EXTERNAL FILE FORMAT」を参照してください。For more information, see CREATE EXTERNAL DATA SOURCE and 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.C. ORC 形式のデータを含む外部テーブルを作成しますCreate an external table with data in ORC format

この例では、ORC ファイルとして書式設定されたデータを含む外部テーブルを作成するために必要なすべての手順を示します。This example shows all the steps required to create an external table that has data formatted as ORC files. それにより、外部データ ソース mydatasource_orc と外部ファイル myfileformat_orc を定義します。It defines an external data source mydatasource_orc and an external file format myfileformat_orc. その後、これらのデータベース レベル オブジェクトは、CREATE EXTERNAL TABLE ステートメントで参照されます。These database-level objects are then referenced in the CREATE EXTERNAL TABLE statement. 詳細については、「CREATE EXTERNAL DATA SOURCE」と「CREATE EXTERNAL FILE FORMAT」を参照してください。For more information, see CREATE EXTERNAL DATA SOURCE and 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.D. Hadoop データのクエリQuerying Hadoop data

クリックストリームは、Hadoop クラスター上の区切りテキスト ファイルである employee.tbl に接続する外部テーブルです。Clickstream is an external table that connects to the employee.tbl delimited text file on a Hadoop cluster. 次のクエリは、標準のテーブルに対するクエリとまったく同じように見えます。The following query looks just like a query against a standard table. ただし、このクエリでは Hadoop からデータを取得し、restuls を計算します。However, this query retrieves data from Hadoop and then computes the results.

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

E.E. SQL データと Hadoop データの結合Join Hadoop data with SQL data

このクエリは、2 つの SQL テーブルに対する標準の JOIN とまったく同じように見えます。This query looks just like a standard JOIN on two SQL tables. 違いは、PolyBase によって Hadoop からクリックストリーム データが取得され、その後 UrlDescription テーブルに結合されることです。The difference is that PolyBase retrieves the Clickstream data from Hadoop and then joins it to the UrlDescription table. 一方のテーブルは外部テーブルで、もう一方は標準の SQL テーブルです。One table is an external table and the other is a standard SQL table.

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

F.F. Hadoop から SQL テーブルへのデータのインポートImport data from Hadoop into a SQL table

この例では、標準の SQL テーブル userと外部テーブル ClickStream との結合の結果を永続的に格納する新しい SQL テーブル ms_user を作成します。This example creates a new SQL table ms_user that permanently stores the result of a join between the standard SQL table user and the external table ClickStream.

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

G.G. シャード化されたデータ ソース用の外部テーブルの作成Create an external table for a sharded data source

この例では、SCHEMA_NAME 句と OBJECT_NAME 句を使用してリモートの DMV を外部テーブルに再マッピングします。This example remaps a remote DMV to an external table using the SCHEMA_NAME and OBJECT_NAME clauses.

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=  
);

H.H. SQL Server の外部テーブルの作成Create an external table for SQL Server

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

    /* 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.I. Oracle の外部テーブルの作成Create an external table for 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
   * 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='customer',
    DATA_SOURCE= external_data_source_name
   );

J.J. Teradata の外部テーブルの作成Create an external table for 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.K. MongoDB の外部テーブルの作成Create an external table for 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
     );

参照See Also

SQL ServerSQL Server ** * SQL Database ***  * SQL Database *   SQL Data
Warehouse
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概要:Azure SQL データベースOverview: Azure SQL Database

Azure SQL Database で、Azure SQL Database と共に使用するためのエラスティック クエリ用外部テーブルを作成します。In Azure SQL Database, creates an external table for elastic queries for use with Azure SQL Database.

外部テーブルを使用し、エラスティック クエリで使用するための外部テーブルを作成します。Use an external table toc reate an external table for use with an elastic query.

CREATE EXTERNAL DATA SOURCE」を参照してください。See also CREATE EXTERNAL DATA SOURCE.

構文Syntax

-- 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]]  
    )  
[;]  

引数Arguments

{ database_name.schema_name.table_name | schema_name.table_name | table_name } 。作成するテーブルの 1 つから 3 つの部分で構成される名前。{ database_name.schema_name.table_name | schema_name.table_name | table_name } The one to three-part name of the table to create. 外部テーブルの場合、SQL では、Azure SQL Database 内で参照されているファイルまたはフォルダーに関する基本的な統計情報と共に、テーブルのメタデータのみが格納されます。For an external table, SQL stores only the table metadata along with basic statistics about the file or folder that is referenced in Azure SQL Database. 実際のデータは移動されず、Azure SQL Database に格納されません。No actual data is moved or stored in Azure SQL Database.

<column_definition> [ ,...n ]。CREATE EXTERNAL TABLE では、列名、データ型、NULL 値の許容、照合順序を構成できます。<column_definition> [ ,...n ] CREATE EXTERNAL TABLE supports the ability to configure column name, data type, nullability and collation. 外部テーブルに対して DEFAULT CONSTRAINT を使用することはできません。You can't use the DEFAULT CONSTRAINT on external tables.

データ型と列の数を含む列の定義は、外部ファイルのデータと一致している必要があります。The column definitions, including the data types and number of columns, must match the data in the external files. 不一致がある場合、実際のデータに対してクエリを実行するときに、ファイルの行が拒否されます。If there's a mismatch, the file rows will be rejected when querying the actual data.

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

Elastic Database クエリの外部データ ソース (SQL Server 以外のデータ ソース) と配布方法を指定します。Specifies the external data source (a non-SQL Server data source) and a distribution method for the Elastic Database query.

DATA_SOURCE。Hadoop ファイル システム、Azure Blob Storage、または Shard Map Manager に格納されているデータなどの外部データ ソースです。DATA_SOURCE An external data source such as data stored in a Hadoop File System, Azure blob storage, or a shard map manager.

SCHEMA_NAME。SCHEMA_NAME 句では、外部テーブルの定義をリモート データベース上のスキーマの異なるテーブルにマップする機能が提供されます。SCHEMA_NAME The SCHEMA_NAME clause provides the ability to map the external table definition to a table in a different schema on the remote database. ローカルおよびリモートの両方のデータベースに存在するスキーマ間であいまいさを排除するには、この句を使用します。Use this clause to disambiguate between schemas that exist on both the local and remote databases.

OBJECT_NAME。OBJECT_NAME 句では、外部テーブルの定義をリモート データベース上の別の名前を持つテーブルにマップする機能が提供されます。OBJECT_NAME The OBJECT_NAME clause provides the ability to map the external table definition to a table with a different name on the remote database. ローカルおよびリモートの両方のデータベースに存在するオブジェクト名の間であいまいさを排除するには、この句を使用します。Use this clause to disambiguate between object names that exist on both the local and remote databases.

DISTRIBUTION。任意。DISTRIBUTION Optional. これは SHARD_MAP_MANAGER 型のデータベースの場合にのみ必須です。This argument is only required for databases of type SHARD_MAP_MANAGER. この引数では、テーブルをシャード化されたテーブルとして扱うか、レプリケートされたテーブルとして扱うかを制御します。This argument controls whether a table is treated as a sharded table or a replicated table. SHARDED (列名) テーブルでは、異なるテーブルからのデータは重複しません。With SHARDED (column name) tables, the data from different tables don't overlap. REPLICATED は、テーブルですべてのシャードに同じデータを持つことを指定します。REPLICATED specifies that tables have the same data on every shard. ROUND_ROBIN は、データを分散するためにアプリケーション固有のメソッドが使用されていることを示します。ROUND_ROBIN indicates that an application-specific method is used to distribute the data.

アクセス許可Permissions

これらのユーザー アクセス許可が必要です。Requires these user permissions:

  • CREATE TABLECREATE TABLE
  • ALTER ANY SCHEMAALTER ANY SCHEMA
  • ALTER ANY EXTERNAL DATA SOURCEALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMATALTER ANY EXTERNAL FILE FORMAT
  • CONTROL DATABASECONTROL DATABASE

外部データ ソースを作成するログインには、Hadoop または Azure Blob Storage 内にある外部データ ソースに対する読み取りおよび書き込みの権限が必要であることに注意してください。Note, the login that creates the external data source must have permission to read and write to the external data source, located in Hadoop or Azure 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, 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 ステートメントの実行時、接続が失敗した場合、ステートメントは失敗し、外部テーブルは作成されません。While executing the CREATE EXTERNAL TABLE statement, if the attempt to connect fails, the statement will fail and the external table won't be created. クエリが最終的に失敗となる前に、SQL Database によって接続が再試行されるため、コマンドが失敗するまで 1 分以上かかる可能性があります。It can take a minute or more for the command to fail since SQL Database retries the connection before eventually failing the query.

全般的な解説General Remarks

SELECT FROM EXTERNAL TABLE などのアドホック クエリのシナリオの場合、SQL Database では外部データ ソースから取得された行が一時テーブルに格納されます。In ad-hoc query scenarios, such as SELECT FROM EXTERNAL TABLE, SQL Database stores the rows that are retrieved from the external data source in a temporary table. クエリ完了後、SQL Database によって一時テーブルが削除されます。After the query completes, SQL Database removes and deletes the temporary table. SQL テーブルには、永続的なデータは格納されません。No permanent data is stored in SQL tables.

これに対し、SELECT INTO FROM EXTERNAL TABLE などのインポートのシナリオでは、外部データ ソースから取得された行が、SQL Database によって永続的なデータとして SQL テーブルに格納されます。In contrast, in the import scenario, such as SELECT INTO FROM EXTERNAL TABLE, SQL Database stores the rows that are retrieved from the external data source as permanent data in the SQL table. SQL Database が外部のデータを取得するときに、クエリの実行中に、新しいテーブルが作成されます。The new table is created during query execution when SQL Database retrieves the external data.

同じまたは別の外部データ ソースを参照している多数の外部テーブルを作成できます。You can create many external tables that reference the same or different external data sources.

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

外部テーブルのデータは別の SQL Database にあるため、いつでも変更したり、削除したりできます。Since the data for an external table is in another SQL Database, it can be changed or removed at any time. その結果、外部のテーブルに対するクエリの結果は確定的であることが保証されません。As a result, query results against an external table aren't guaranteed to be deterministic. 同じクエリを外部のテーブルに対して実行するたびに、異なる結果が返される可能性があります。The same query can return different results each time it runs against an external table. 同様に、外部データが移動または削除された場合、クエリが失敗する可能性があります。Similarly, a query might fail if the external data is moved or removed.

それぞれが異なる外部データ ソースを参照する複数の外部テーブルを作成できます。You can create multiple external tables that each reference different external data sources.

外部テーブルに対しては、これらのデータ定義言語 (DDL) ステートメントのみを使用できます。Only these Data Definition Language (DDL) statements are allowed on external tables:

  • CREATE TABLE および DROP TABLECREATE TABLE and DROP TABLE
  • CREATE VIEW および DROP VIEWCREATE VIEW and DROP VIEW

サポートされていない構成要素と操作:Constructs and operations not supported:

  • 外部テーブルの列に対する DEFAULT 制約The DEFAULT constraint on external table columns
  • データ操作言語 (DML) の削除、挿入、更新の操作Data Manipulation Language (DML) operations of delete, insert, and update

ロックLocking

SCHEMARESOLUTION オブジェクトに対する共有ロック。Shared lock on the SCHEMARESOLUTION object.

使用例Examples

A.A. Azure SQL Database の外部テーブルを作成しますCreate external table for 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)

参照See Also

CREATE EXTERNAL DATA SOURCECREATE EXTERNAL DATA SOURCE

SQL ServerSQL Server SQL DatabaseSQL Database ** * SQL Data
Warehouse *
**  
* SQL Data
Warehouse *
 
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概要:Azure SQL Data WarehouseOverview: Azure SQL Data Warehouse

Azure SQL Data Warehouse で、次の目的で外部テーブルを使用します。In Azure SQL Data Warehouse, use an external table to:

  • Transact-SQLTransact-SQL ステートメントを使用して、Hadoop または Azure Blob Storage データをクエリします。Query Hadoop or Azure blob storage data with Transact-SQLTransact-SQL statements.
  • Hadoop または Azure Blob Storage からデータをインポートして、Azure SQL Data Warehouse に格納します。Import and store data from Hadoop or Azure blob storage into Azure SQL Data Warehouse.
  • Azure Data Lake Store からデータをインポートして、Azure SQL Data Warehouse に格納します。Import and store data from Azure Data Lake Store into Azure SQL Data Warehouse.

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

構文Syntax

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'

}  

引数Arguments

{ database_name.schema_name.table_name | schema_name.table_name | table_name } 。作成するテーブルの 1 つから 3 つの部分で構成される名前。{ database_name.schema_name.table_name | schema_name.table_name | table_name } The one to three-part name of the table to create. 外部テーブルの場合、SQL Data Warehouse では、Azure Data Lake、Hadoop、または Azure Blob Storage 内で参照されているファイルまたはフォルダーに関する基本的な統計情報と共に、テーブルのメタデータのみが格納されます。For an external table, SQL Data Warehouse stores only the table metadata along with basic statistics about the file or folder that is referenced in Azure Data Lake, Hadoop, or Azure blob storage. 実際のデータは移動されず、SQL Data Warehouse に格納されません。No actual data is moved or stored in SQL Data Warehouse.

<column_definition> [ ,...n ]。CREATE EXTERNAL TABLE では、列名、データ型、NULL 値の許容、照合順序を構成できます。<column_definition> [ ,...n ] CREATE EXTERNAL TABLE supports the ability to configure column name, data type, nullability and collation. 外部テーブルに対して DEFAULT CONSTRAINT を使用することはできません。You can't use the DEFAULT CONSTRAINT on external tables.

データ型と列の数を含む列の定義は、外部ファイルのデータと一致している必要があります。The column definitions, including the data types and number of columns, must match the data in the external files. 不一致がある場合、実際のデータに対してクエリを実行するときに、ファイルの行が拒否されます。If there's a mismatch, the file rows will be rejected when querying the actual data.

LOCATION = 'folder_or_filepath'。Azure Data Lake、Hadoop、または Azure Blob Storage にある実際のデータのフォルダーまたはファイル パスとファイル名を指定します。LOCATION = 'folder_or_filepath' Specifies the folder or the file path and file name for the actual data in Azure Data Lake, Hadoop, or Azure blob storage. ルート フォルダーから、場所を開始します。The location starts from the root folder. ルート フォルダーは、外部データ ソースで指定されたデータの場所です。The root folder is the data location specified in the external data source. CREATE EXTERNAL TABLE AS SELECT ステートメントによって、存在しない場合にパスとフォルダーが作成されます。The CREATE EXTERNAL TABLE AS SELECT statement creates the path and folder if it doesn't exist. CREATE EXTERNAL TABLE では、パスとフォルダーが作成されません。CREATE EXTERNAL TABLE doesn't create the path and folder.

LOCATION をフォルダーとして指定した場合、外部テーブルから選択する PolyBase クエリでは、フォルダーとそのすべてのサブフォルダーからファイルが取得されます。If you specify LOCATION to be a folder, a PolyBase query that selects from the external table will retrieve files from the folder and all of its subfolders. Hadoop と同じように PolyBase で非表示のフォルダーは返されません。Just like Hadoop, PolyBase doesn't return hidden folders. ファイル名が下線 () またはピリオド (.) で始まるファイルも返されません。It also doesn't return files for which the file name begins with an underline () or a period (.).

この例では、LOCATION='/webdata/' である場合、PolyBase クエリでは mydata.txt と mydata2.txt から行が返されます。In this example, if LOCATION='/webdata/', a PolyBase query will return rows from mydata.txt and mydata2.txt. mydata3.txt は非表示のフォルダーのサブフォルダーであるため、返されません。It won't return mydata3.txt because it's a subfolder of a hidden folder. また、_hidden.txt は非表示のファイルであるため返されません。And it won't return _hidden.txt because it's a hidden file.

外部テーブルの再帰型データRecursive data for external tables

既定値を変更して、読み取りをルート フォルダーからのみに限定するには、core-site.xml 構成ファイル内で属性 <polybase.recursive.traversal> を 'false' に設定します。To change the default and only read from the root folder, set the attribute <polybase.recursive.traversal> to 'false' in the core-site.xml configuration file. このファイルは <SqlBinRoot>\PolyBase\Hadoop\Conf with SqlBinRoot the bin root of SQl Server の配下に配置されます。This file is located under <SqlBinRoot>\PolyBase\Hadoop\Conf with SqlBinRoot the bin root of SQl Server. たとえば、C:\\Program Files\\Microsoft SQL Server\\MSSQL13.XD14\\MSSQL\\Binn のようになります。For example, C:\\Program Files\\Microsoft SQL Server\\MSSQL13.XD14\\MSSQL\\Binn.

DATA_SOURCE = external_data_source_name。外部データの場所を含む外部データ ソースの名前を指定します。DATA_SOURCE = external_data_source_name Specifies the name of the external data source that contains the location of the external data. この場所は Azure Data Lake 内にあります。This location is in Azure Data Lake. 外部データ ソースを作成するには、CREATE EXTERNAL DATA SOURCE を使用します。To create an external data source, use CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name。外部データのファイルの種類と圧縮方法を格納する外部ファイル形式のオブジェクトの名前を指定します。FILE_FORMAT = external_file_format_name Specifies the name of the external file format object that stores the file type and compression method for the external data. 外部ファイル形式を作成するには、CREATE EXTERNAL FILE FORMAT を使用します。To create an external file format, use CREATE EXTERNAL FILE FORMAT.

拒否オプション。PolyBase が外部データ ソースから取得したダーティ レコードを処理する方法を決定する、reject パラメーターを指定できます。Reject Options You can specify reject parameters that determine how PolyBase will handle dirty records it retrieves from the external data source. データ レコードの実際のデータの種類または列の数が、外部テーブルの列の定義と一致しない場合、そのデータ レコードは "ダーティ" と見なされます。A data record is considered 'dirty' if it actual data types or the number of columns don't match the column definitions of the external table.

reject 値を指定または変更しない場合、PolyBase では既定値が使用されます。When you don't specify or change reject values, PolyBase uses default values. reject パラメーターに関するこの情報は、CREATE EXTERNAL TABLE ステートメントを使用して外部テーブルを作成するときに、追加メタデータとして格納されます。This information about the reject parameters is stored as additional metadata when you create an external table with CREATE EXTERNAL TABLE statement. 以降の SELECT ステートメントまたは SELECT INTO SELECT ステートメントで外部テーブルからデータを選択するとき、拒否オプションを使用して、実際のクエリが失敗するまでに拒否できる行の数または割合が PolyBase によって決定されます。When a future SELECT statement or SELECT INTO SELECT statement selects data from the external table, PolyBase will use the reject options to determine the number or percentage of rows that can be rejected before the actual query fails. 拒否のしきい値を超えるまで、クエリの (部分的な) 結果が返されます。The query will return (partial) results until the reject threshold is exceeded. その後、適切なエラー メッセージと共に失敗します。It then fails with the appropriate error message.

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 REJECT_VALUE is a literal value, not a percentage. 拒否された行が reject_value を超えると、PolyBase クエリは失敗します。The PolyBase query will fail when the number of rejected rows exceeds reject_value.

たとえば、REJECT_VALUE = 5 で REJECT_TYPE = value の場合、PolyBase の SELECT クエリは、5 行を拒否した後に失敗します。For example, if REJECT_VALUE = 5 and REJECT_TYPE = value, the PolyBase SELECT query will fail after five rows have been rejected.

percentage。REJECT_VALUE は、リテラル値ではなく割合です。percentage REJECT_VALUE is a percentage, not a literal value. 失敗した行の percentagereject_value を超えると、PolyBase クエリは失敗します。A PolyBase query will fail when the percentage of failed rows exceeds reject_value. 失敗した行の割合は、間隔をおいて計算されます。The percentage of failed rows is calculated at intervals.

REJECT_VALUE = reject_value。クエリが失敗する前に拒否できる行を値または割合で指定します。REJECT_VALUE = reject_value Specifies the value or the percentage of rows that can be rejected before the query fails.

REJECT_TYPE = value の場合、reject_value は 0 から 2,147, 483,647 の範囲の整数にする必要があります。For REJECT_TYPE = value, reject_value must be an integer between 0 and 2,147,483,647.

REJECT_TYPE = percentage の場合、reject_value は 0 から 100 の範囲の浮動小数にする必要があります。For REJECT_TYPE = percentage, reject_value must be a float between 0 and 100.

REJECT_SAMPLE_VALUE = reject_sample_value。REJECT_TYPE = percentage を指定する場合、この属性は必須です。REJECT_SAMPLE_VALUE = reject_sample_value This attribute is required when you specify REJECT_TYPE = percentage. それにより、拒否された行の割合が PolyBase によって再計算されるまでに取得が試行される行の数が決定します。It determines the number of rows to attempt to retrieve before the PolyBase recalculates the percentage of rejected rows.

reject_sample_value パラメーターは、0 から 2,147,483,647 の範囲の整数にする必要があります。The reject_sample_value parameter must be an integer between 0 and 2,147,483,647.

たとえば、REJECT_SAMPLE_VALUE = 1000 の場合、PolyBase によって外部データ ファイルから 1000 行のインポートが試みられた後、失敗した行の割合が再計算されます。For example, if REJECT_SAMPLE_VALUE = 1000, PolyBase will calculate the percentage of failed rows after it has attempted to import 1000 rows from the external data file. 失敗した行のパーセンテージが reject_value 未満の場合、PolyBase は別の 1000 行の取得を試みます。If the percentage of failed rows is less than reject_value, PolyBase will attempt to retrieve another 1000 rows. 1000 行ずつ追加でインポートを試みた後、失敗した行の割合の再計算を続けます。It continues to recalculate the percentage of failed rows after it attempts to import each additional 1000 rows.

注意

PolyBase では間隔を置いて失敗した行のパーセンテージを計算するため、実際の失敗した行のパーセンテージは、reject_value を超える場合があります。Since PolyBase 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:

  • PolyBase で最初の 100 行の取得が試みられ、25 行が失敗し、75 行が成功しました。PolyBase attempts to retrieve the first 100 rows; 25 fail and 75 succeed.
  • 失敗した行の割合は 25% と計算されます。これは reject 値である 30% を下回っています。Percent of failed rows is calculated as 25%, which is less than the reject value of 30%. その結果、PolyBase は引き続き、外部データ ソースからデータを取得します。As a result, PolyBase will continue retrieving data from the external data source.
  • PolyBase は次の 100 行の読み込みを試み、今回は 25 行が成功し、75 行が失敗しました。PolyBase attempts to load the next 100 rows; this time 25 rows succeed and 75 rows fail.
  • 失敗した行の割合が 50% として再計算されます。Percent of failed rows is recalculated as 50%. 失敗した行の割合が、30% という reject 値を超えました。The percentage of failed rows has exceeded the 30% reject value.
  • PolyBase クエリは、最初の 200 行を取得しようとした後、拒否された行 50% で失敗します。The PolyBase query fails with 50% rejected rows after attempting to return the first 200 rows. 拒否のしきい値を超えたことが PolyBase クエリによって検出される前に、一致した行が返されていることに注意してください。Notice that matching rows have been returned before the PolyBase query detects the reject threshold has been exceeded.

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

外部データ ソース内のディレクトリを指定します。拒否された行と該当エラー ファイルをそこに書き込みます。Specifies the directory within the External Data Source that the rejected rows and the corresponding error file should be written. 指定したパスが存在しない場合、PolyBase では、そのパスが自動的に作成されます。If the specified path doesn't exist, PolyBase will create one on your behalf. "rejectedrows" という名前で子ディレクトリが作成されます。" " 文字があることで、場所パラメーターで明示的に指定されない限り、他のデータ処理ではこのディレクトリがエスケープされます。A child directory is created with the name "rejectedrows". The "" character ensures that the directory is escaped for other data processing unless explicitly named in the location parameter. このディレクトリ内には、YearMonthDay -HourMinuteSecond (例:Within this directory, there's a folder created based on the time of load submission in the format YearMonthDay -HourMinuteSecond (Ex. 20180330-173205) のロード サブミッション時間に基づいて作成されたフォルダーがあります。20180330-173205). このフォルダーで、2 種類のファイル、理由ファイルとデータ ファイルが書き込まれます。In this folder, two types of files are written, the _reason file and the data file.

理由ファイルとデータ ファイルのいずれにも、CTAS ステートメントと関連付けられている queryID が含まれます。The reason files and the data files both have the queryID associated with the CTAS statement. データと理由が別々のファイル内にあるため、対応するファイルはサフィックスが一致しています。Because the data and the reason are in separate files, corresponding files have a matching suffix.

アクセス許可Permissions

これらのユーザー アクセス許可が必要です。Requires these user permissions:

  • CREATE TABLECREATE TABLE
  • ALTER ANY SCHEMAALTER ANY SCHEMA
  • ALTER ANY EXTERNAL DATA SOURCEALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMATALTER ANY EXTERNAL FILE FORMAT
  • CONTROL DATABASECONTROL DATABASE

外部データ ソースを作成するログインには、Hadoop または Azure Blob Storage 内にある外部データ ソースに対する読み取りおよび書き込みの権限が必要であることに注意してください。Note, the login that creates the external data source must have permission to read and write to the external data source, located in Hadoop or Azure 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, 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 ステートメントの実行中に、PolyBase から外部データ ソースへの接続が試みられます。While executing the CREATE EXTERNAL TABLE statement, PolyBase attempts to connect to the external data source. 接続が失敗した場合、ステートメントは失敗し、外部テーブルは作成されません。If the attempt to connect fails, the statement will fail and the external table won't be created. クエリが最終的に失敗となる前に、PolyBase によって接続が再試行されるため、コマンドが失敗するまで 1 分以上かかる可能性があります。It can take a minute or more for the command to fail since PolyBase retries the connection before eventually failing the query.

全般的な解説General Remarks

SELECT FROM EXTERNAL TABLE などのアドホック クエリのシナリオの場合、PolyBase では外部データ ソースから取得された行が一時テーブルに格納されます。In ad-hoc query scenarios, such as SELECT FROM EXTERNAL TABLE, PolyBase stores the rows that are retrieved from the external data source in a temporary table. クエリ完了後、PolyBase によって一時テーブルが削除されます。After the query completes, PolyBase removes and deletes the temporary table. SQL テーブルには、永続的なデータは格納されません。No permanent data is stored in SQL tables.

これに対し、SELECT INTO FROM EXTERNAL TABLE などのインポートのシナリオでは、外部データ ソースから取得された行が、PolyBase によって永続的なデータとして SQL テーブルに格納されます。In contrast, in the import scenario, such as SELECT INTO FROM EXTERNAL TABLE, PolyBase stores the rows that are retrieved from the external data source as permanent data in the SQL table. PolyBase が外部のデータを取得するときに、クエリの実行中に、新しいテーブルが作成されます。The new table is created during query execution when PolyBase retrieves the external data.

PolyBase では、クエリのパフォーマンスを向上させるためにクエリ計算の一部を Hadoop にプッシュできます。PolyBase can push some of the query computation to Hadoop to improve query performance. このアクションは述語プッシュダウンと呼ばれます。This action is called predicate pushdown. それを有効にするには、CREATE EXTERNAL DATA SOURCE で、Hadoop のリソース マネージャーの場所のオプションを指定します。To enable it, specify the Hadoop resource manager location option in CREATE EXTERNAL DATA SOURCE.

同じまたは別の外部データ ソースを参照している多数の外部テーブルを作成できます。You can create many external tables that reference the same or different external data sources.

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

外部テーブルのデータは SQL Data Warehouse の管理下にあるため、外部プロセスによっていつでも変更または削除できます。Since the data for an external table is under the control of SQL Data Warehouse, and can be changed or removed at any time by an external process. その結果、外部のテーブルに対するクエリの結果は確定的であることが保証されません。As a result, query results against an external table aren't guaranteed to be deterministic. 同じクエリを外部のテーブルに対して実行するたびに、異なる結果が返される可能性があります。The same query can return different results each time it runs against an external table. 同様に、外部データが移動または削除された場合、クエリが失敗する可能性があります。Similarly, a query might fail if the external data is moved or removed.

それぞれが異なる外部データ ソースを参照する複数の外部テーブルを作成できます。You can create multiple external tables that each reference different external data sources.

外部テーブルに対しては、これらのデータ定義言語 (DDL) ステートメントのみを使用できます。Only these Data Definition Language (DDL) statements are allowed on external tables:

  • CREATE TABLE および DROP TABLECREATE TABLE and DROP TABLE
  • CREATE STATISTICS および DROP STATISTICSCREATE STATISTICS and DROP STATISTICS
  • CREATE VIEW および DROP VIEWCREATE VIEW and DROP VIEW

サポートされていない構成要素と操作:Constructs and operations not supported:

  • 外部テーブルの列に対する DEFAULT 制約The DEFAULT constraint on external table columns
  • データ操作言語 (DML) の削除、挿入、更新の操作Data Manipulation Language (DML) operations of delete, insert, and update

クエリの制限事項:Query limitations:

PolyBase では、32 個の同時 PolyBase クエリを実行しているときに、フォルダーあたり最大 33,000 ファイルを使用できます。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,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 33k files. 外部ファイルのパスを短く維持し、使用するファイルの数を HDFS フォルダーあたり 30,000 以下にすることをお勧めします。We recommend that you keep external file paths short and use no more than 30k files per HDFS folder. 参照しているファイルの数が多すぎる場合は、Java 仮想マシン (JVM) のメモリ不足例外が発生する可能性があります。When too many files are referenced, a Java Virtual Machine (JVM) out-of-memory exception might occur.

テーブルの幅の制限:Table width limitations:

Azure Data Warehouse の PolyBase には、テーブル定義による有効な 1 つの行の最大幅に基づいた、行の幅 1 MB という制限があります。PolyBase in Azure Data Warehouse has a row width limit of 1 MB based on the maximum size of a single valid row by table definition. 列スキーマの合計が 1 MB を超えている場合、PolyBase によるデータのクエリは実行できません。If the sum of the column schema is greater than 1 MB, PolyBase can't query the data.

ロックLocking

SCHEMARESOLUTION オブジェクトに対する共有ロック。Shared lock on the SCHEMARESOLUTION object.

使用例Examples

A.A. ADLS から Azure SQL データ ウェアハウスSQL Data Warehouse にデータをインポートするImporting Data from ADLS into Azure SQL データ ウェアハウスSQL Data Warehouse


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

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

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(
    FORMAT_TYPE = DELIMITEDTEXT 
    , FORMAT_OPTIONS ( FIELDTERMINATOR = '|'
       , STRINGDELIMITER = ''
      , DATEFORMAT = 'yyyy-MM-dd HH:mm:ss.fff'
      , USETYPE_DEFAULT = FALSE
      )
)

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( [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] ;

参照See Also

SQL ServerSQL Server SQL DatabaseSQL Database SQL Data
Warehouse
SQL Data
Warehouse
** * Analytics
Platform System (PDW) *
**  
* Analytics
Platform System (PDW) *
 

 

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

次のために外部テーブルを使用します。Use an external table to:

  • Transact-SQLTransact-SQL ステートメントを使用して、Hadoop または Azure Blob Storage データをクエリします。Query Hadoop or Azure blob storage data with Transact-SQLTransact-SQL statements.
  • Hadoop または Azure Blob Storage からデータをインポートして、Analytics Platform System に格納します。Import and store data from Hadoop or Azure blob storage into Analytics Platform System.

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

構文Syntax

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,

}  

引数Arguments

{ database_name.schema_name.table_name | schema_name.table_name | table_name } 。作成するテーブルの 1 つから 3 つの部分で構成される名前。{ database_name.schema_name.table_name | schema_name.table_name | table_name } The one to three-part name of the table to create. 外部テーブルの場合、Analytics Platform System では、Hadoop または Azure Blob Storage 内で参照されているファイルまたはフォルダーに関する基本的な統計情報と共に、テーブルのメタデータのみが格納されます。For an external table, Analytics Platform System stores only the table metadata along with basic statistics about the file or folder that is referenced in Hadoop or Azure blob storage. 実際のデータが Analytics Platform System に移されたり、格納されたりすることはありません。No actual data is moved or stored in Analytics Platform System.

<column_definition> [ ,...n ]。CREATE EXTERNAL TABLE では、列名、データ型、NULL 値の許容、照合順序を構成できます。<column_definition> [ ,...n ] CREATE EXTERNAL TABLE supports the ability to configure column name, data type, nullability and collation. 外部テーブルに対して DEFAULT CONSTRAINT を使用することはできません。You can't use the DEFAULT CONSTRAINT on external tables.

データ型と列の数を含む列の定義は、外部ファイルのデータと一致している必要があります。The column definitions, including the data types and number of columns, must match the data in the external files. 不一致がある場合、実際のデータに対してクエリを実行するときに、ファイルの行が拒否されます。If there's a mismatch, the file rows will be rejected when querying the actual data.

LOCATION = 'folder_or_filepath'。Hadoop または Azure Blob Storage にある実際のデータのフォルダーまたはファイル パスとファイル名を指定します。LOCATION = 'folder_or_filepath' Specifies the folder or the file path and file name for the actual data in Hadoop or Azure blob storage. ルート フォルダーから、場所を開始します。The location starts from the root folder. ルート フォルダーは、外部データ ソースで指定されたデータの場所です。The root folder is the data location specified in the external data source.

Analytics Platform System では、CREATE EXTERNAL TABLE AS SELECT ステートメントによって、存在しない場合にパスとフォルダーが作成されます。In Analytics Platform System, the CREATE EXTERNAL TABLE AS SELECT statement creates the path and folder if it doesn't exist. CREATE EXTERNAL TABLE では、パスとフォルダーが作成されません。CREATE EXTERNAL TABLE doesn't create the path and folder.

LOCATION をフォルダーとして指定した場合、外部テーブルから選択する PolyBase クエリでは、フォルダーとそのすべてのサブフォルダーからファイルが取得されます。If you specify LOCATION to be a folder, a PolyBase query that selects from the external table will retrieve files from the folder and all of its subfolders. Hadoop と同じように PolyBase で非表示のフォルダーは返されません。Just like Hadoop, PolyBase doesn't return hidden folders. ファイル名が下線 () またはピリオド (.) で始まるファイルも返されません。It also doesn't return files for which the file name begins with an underline () or a period (.).

この例では、LOCATION='/webdata/' である場合、PolyBase クエリでは mydata.txt と mydata2.txt から行が返されます。In this example, if LOCATION='/webdata/', a PolyBase query will return rows from mydata.txt and mydata2.txt. mydata3.txt は非表示のフォルダーのサブフォルダーであるため、返されません。It won't return mydata3.txt because it's a subfolder of a hidden folder. また、_hidden.txt は非表示のファイルであるため返されません。And it won't return _hidden.txt because it's a hidden file.

外部テーブルの再帰型データRecursive data for external tables

既定値を変更して、読み取りをルート フォルダーからのみに限定するには、core-site.xml 構成ファイル内で属性 <polybase.recursive.traversal> を 'false' に設定します。To change the default and only read from the root folder, set the attribute <polybase.recursive.traversal> to 'false' in the core-site.xml configuration file. このファイルは <SqlBinRoot>\PolyBase\Hadoop\Conf with SqlBinRoot the bin root of SQl Server の配下に配置されます。This file is located under <SqlBinRoot>\PolyBase\Hadoop\Conf with SqlBinRoot the bin root of SQl Server. たとえば、C:\\Program Files\\Microsoft SQL Server\\MSSQL13.XD14\\MSSQL\\Binn のようになります。For example, C:\\Program Files\\Microsoft SQL Server\\MSSQL13.XD14\\MSSQL\\Binn.

DATA_SOURCE = external_data_source_name。外部データの場所を含む外部データ ソースの名前を指定します。DATA_SOURCE = external_data_source_name Specifies the name of the external data source that contains the location of the external data. この場所は、Hadoop または Azure BLOB ストレージです。This location is either a Hadoop or Azure blob storage. 外部データ ソースを作成するには、CREATE EXTERNAL DATA SOURCE を使用します。To create an external data source, use CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name。外部データのファイルの種類と圧縮方法を格納する外部ファイル形式のオブジェクトの名前を指定します。FILE_FORMAT = external_file_format_name Specifies the name of the external file format object that stores the file type and compression method for the external data. 外部ファイル形式を作成するには、CREATE EXTERNAL FILE FORMAT を使用します。To create an external file format, use CREATE EXTERNAL FILE FORMAT.

拒否オプション。PolyBase が外部データ ソースから取得したダーティ レコードを処理する方法を決定する、reject パラメーターを指定できます。Reject Options You can specify reject parameters that determine how PolyBase will handle dirty records it retrieves from the external data source. データ レコードの実際のデータの種類または列の数が、外部テーブルの列の定義と一致しない場合、そのデータ レコードは "ダーティ" と見なされます。A data record is considered 'dirty' if it actual data types or the number of columns don't match the column definitions of the external table.

reject 値を指定または変更しない場合、PolyBase では既定値が使用されます。When you don't specify or change reject values, PolyBase uses default values. reject パラメーターに関するこの情報は、CREATE EXTERNAL TABLE ステートメントを使用して外部テーブルを作成するときに、追加メタデータとして格納されます。This information about the reject parameters is stored as additional metadata when you create an external table with CREATE EXTERNAL TABLE statement. 以降の SELECT ステートメントまたは SELECT INTO SELECT ステートメントで外部テーブルからデータを選択するとき、拒否オプションを使用して、実際のクエリが失敗するまでに拒否できる行の数または割合が PolyBase によって決定されます。When a future SELECT statement or SELECT INTO SELECT statement selects data from the external table, PolyBase will use the reject options to determine the number or percentage of rows that can be rejected before the actual query fails. 拒否のしきい値を超えるまで、クエリの (部分的な) 結果が返されます。The query will return (partial) results until the reject threshold is exceeded. その後、適切なエラー メッセージと共に失敗します。It then fails with the appropriate error message.

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 REJECT_VALUE is a literal value, not a percentage. 拒否された行が reject_value を超えると、PolyBase クエリは失敗します。The PolyBase query will fail when the number of rejected rows exceeds reject_value.

たとえば、REJECT_VALUE = 5 で REJECT_TYPE = value の場合、PolyBase の SELECT クエリは、5 行を拒否した後に失敗します。For example, if REJECT_VALUE = 5 and REJECT_TYPE = value, the PolyBase SELECT query will fail after five rows have been rejected.

percentage。REJECT_VALUE は、リテラル値ではなく割合です。percentage REJECT_VALUE is a percentage, not a literal value. 失敗した行の percentagereject_value を超えると、PolyBase クエリは失敗します。A PolyBase query will fail when the percentage of failed rows exceeds reject_value. 失敗した行の割合は、間隔をおいて計算されます。The percentage of failed rows is calculated at intervals.

REJECT_VALUE = reject_value。クエリが失敗する前に拒否できる行を値または割合で指定します。REJECT_VALUE = reject_value Specifies the value or the percentage of rows that can be rejected before the query fails.

REJECT_TYPE = value の場合、reject_value は 0 から 2,147, 483,647 の範囲の整数にする必要があります。For REJECT_TYPE = value, reject_value must be an integer between 0 and 2,147,483,647.

REJECT_TYPE = percentage の場合、reject_value は 0 から 100 の範囲の浮動小数にする必要があります。For REJECT_TYPE = percentage, reject_value must be a float between 0 and 100.

REJECT_SAMPLE_VALUE = reject_sample_value。REJECT_TYPE = percentage を指定する場合、この属性は必須です。REJECT_SAMPLE_VALUE = reject_sample_value This attribute is required when you specify REJECT_TYPE = percentage. それにより、拒否された行の割合が PolyBase によって再計算されるまでに取得が試行される行の数が決定します。It determines the number of rows to attempt to retrieve before the PolyBase recalculates the percentage of rejected rows.

reject_sample_value パラメーターは、0 から 2,147,483,647 の範囲の整数にする必要があります。The reject_sample_value parameter must be an integer between 0 and 2,147,483,647.

たとえば、REJECT_SAMPLE_VALUE = 1000 の場合、PolyBase によって外部データ ファイルから 1000 行のインポートが試みられた後、失敗した行の割合が再計算されます。For example, if REJECT_SAMPLE_VALUE = 1000, PolyBase will calculate the percentage of failed rows after it has attempted to import 1000 rows from the external data file. 失敗した行のパーセンテージが reject_value 未満の場合、PolyBase は別の 1000 行の取得を試みます。If the percentage of failed rows is less than reject_value, PolyBase will attempt to retrieve another 1000 rows. 1000 行ずつ追加でインポートを試みた後、失敗した行の割合の再計算を続けます。It continues to recalculate the percentage of failed rows after it attempts to import each additional 1000 rows.

注意

PolyBase では間隔を置いて失敗した行のパーセンテージを計算するため、実際の失敗した行のパーセンテージは、reject_value を超える場合があります。Since PolyBase 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:

  • PolyBase で最初の 100 行の取得が試みられ、25 行が失敗し、75 行が成功しました。PolyBase attempts to retrieve the first 100 rows; 25 fail and 75 succeed.
  • 失敗した行の割合は 25% と計算されます。これは reject 値である 30% を下回っています。Percent of failed rows is calculated as 25%, which is less than the reject value of 30%. その結果、PolyBase は引き続き、外部データ ソースからデータを取得します。As a result, PolyBase will continue retrieving data from the external data source.
  • PolyBase は次の 100 行の読み込みを試み、今回は 25 行が成功し、75 行が失敗しました。PolyBase attempts to load the next 100 rows; this time 25 rows succeed and 75 rows fail.
  • 失敗した行の割合が 50% として再計算されます。Percent of failed rows is recalculated as 50%. 失敗した行の割合が、30% という reject 値を超えました。The percentage of failed rows has exceeded the 30% reject value.
  • PolyBase クエリは、最初の 200 行を取得しようとした後、拒否された行 50% で失敗します。The PolyBase query fails with 50% rejected rows after attempting to return the first 200 rows. 拒否のしきい値を超えたことが PolyBase クエリによって検出される前に、一致した行が返されていることに注意してください。Notice that matching rows have been returned before the PolyBase query detects the reject threshold has been exceeded.

アクセス許可Permissions

これらのユーザー アクセス許可が必要です。Requires these user permissions:

  • CREATE TABLECREATE TABLE
  • ALTER ANY SCHEMAALTER ANY SCHEMA
  • ALTER ANY EXTERNAL DATA SOURCEALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMATALTER ANY EXTERNAL FILE FORMAT
  • CONTROL DATABASECONTROL DATABASE

外部データ ソースを作成するログインには、Hadoop または Azure Blob Storage 内にある外部データ ソースに対する読み取りおよび書き込みの権限が必要であることに注意してください。Note, the login that creates the external data source must have permission to read and write to the external data source, located in Hadoop or Azure 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, 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 ステートメントの実行中に、PolyBase から外部データ ソースへの接続が試みられます。While executing the CREATE EXTERNAL TABLE statement, PolyBase attempts to connect to the external data source. 接続が失敗した場合、ステートメントは失敗し、外部テーブルは作成されません。If the attempt to connect fails, the statement will fail and the external table won't be created. クエリが最終的に失敗となる前に、PolyBase によって接続が再試行されるため、コマンドが失敗するまで 1 分以上かかる可能性があります。It can take a minute or more for the command to fail since PolyBase retries the connection before eventually failing the query.

全般的な解説General Remarks

SELECT FROM EXTERNAL TABLE などのアドホック クエリのシナリオの場合、PolyBase では外部データ ソースから取得された行が一時テーブルに格納されます。In ad-hoc query scenarios, such as SELECT FROM EXTERNAL TABLE, PolyBase stores the rows that are retrieved from the external data source in a temporary table. クエリ完了後、PolyBase によって一時テーブルが削除されます。After the query completes, PolyBase removes and deletes the temporary table. SQL テーブルには、永続的なデータは格納されません。No permanent data is stored in SQL tables.

これに対し、SELECT INTO FROM EXTERNAL TABLE などのインポートのシナリオでは、外部データ ソースから取得された行が、PolyBase によって永続的なデータとして SQL テーブルに格納されます。In contrast, in the import scenario, such as SELECT INTO FROM EXTERNAL TABLE, PolyBase stores the rows that are retrieved from the external data source as permanent data in the SQL table. PolyBase が外部のデータを取得するときに、クエリの実行中に、新しいテーブルが作成されます。The new table is created during query execution when PolyBase retrieves the external data.

PolyBase では、クエリのパフォーマンスを向上させるためにクエリ計算の一部を Hadoop にプッシュできます。PolyBase can push some of the query computation to Hadoop to improve query performance. このアクションは述語プッシュダウンと呼ばれます。This action is called predicate pushdown. それを有効にするには、CREATE EXTERNAL DATA SOURCE で、Hadoop のリソース マネージャーの場所のオプションを指定します。To enable it, specify the Hadoop resource manager location option in CREATE EXTERNAL DATA SOURCE.

同じまたは別の外部データ ソースを参照している多数の外部テーブルを作成できます。You can create many external tables that reference the same or different external data sources.

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

外部テーブルのデータはアプライアンスの外部にあるため、PolyBase の管理下にはなく、外部プロセスによっていつでも変更または削除できます。Since the data for an external table is off the appliance, it isn't under the control of PolyBase, and can be changed or removed at any time by an external process. その結果、外部のテーブルに対するクエリの結果は確定的であることが保証されません。As a result, query results against an external table aren't guaranteed to be deterministic. 同じクエリを外部のテーブルに対して実行するたびに、異なる結果が返される可能性があります。The same query can return different results each time it runs against an external table. 同様に、外部データが移動または削除された場合、クエリが失敗する可能性があります。Similarly, a query might fail if the external data is moved or removed.

それぞれが異なる外部データ ソースを参照する複数の外部テーブルを作成できます。You can create multiple external tables that each reference different external data sources. 異なる複数の Hadoop データ ソースに対して同時にクエリを実行する場合は、各 Hadoop ソースに同じ 'Hadoop 接続' サーバー構成の設定が使用されている必要があります。If you simultaneously run queries against different Hadoop data sources, then each Hadoop source must use the same 'hadoop connectivity' server configuration setting. たとえば、ことはできません同時にクエリを実行する Cloudera Hadoop クラスターと Hortonworks の Hadoop クラスターに対してこれらさまざまな構成設定を使用するためです。For example, you can't simultaneously run a query against a Cloudera Hadoop cluster and a Hortonworks Hadoop cluster since these use different configuration settings. 構成設定とサポートされる組み合わせについては、「PolyBase 接続構成」を参照してください。For the configuration settings and supported combinations, see PolyBase Connectivity Configuration.

外部テーブルに対しては、これらのデータ定義言語 (DDL) ステートメントのみを使用できます。Only these Data Definition Language (DDL) statements are allowed on external tables:

  • CREATE TABLE および DROP TABLECREATE TABLE and DROP TABLE
  • CREATE STATISTICS および DROP STATISTICSCREATE STATISTICS and DROP STATISTICS
  • CREATE VIEW および DROP VIEWCREATE VIEW and DROP VIEW

サポートされていない構成要素と操作:Constructs and operations not supported:

  • 外部テーブルの列に対する DEFAULT 制約The DEFAULT constraint on external table columns
  • データ操作言語 (DML) の削除、挿入、更新の操作Data Manipulation Language (DML) operations of delete, insert, and update

クエリの制限事項:Query limitations:

PolyBase では、32 個の同時 PolyBase クエリを実行しているときに、フォルダーあたり最大 33,000 ファイルを使用できます。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,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 33k files. 外部ファイルのパスを短く維持し、使用するファイルの数を HDFS フォルダーあたり 30,000 以下にすることをお勧めします。We recommend that you keep external file paths short and use no more than 30k files per HDFS folder. 参照しているファイルの数が多すぎる場合は、Java 仮想マシン (JVM) のメモリ不足例外が発生する可能性があります。When too many files are referenced, a Java Virtual Machine (JVM) out-of-memory exception might occur.

テーブルの幅の制限:Table width limitations:

SQL Server 2016 の PolyBase には、テーブル定義による有効な 1 つの行の最大幅に基づいた、行の幅 32 KB という制限があります。PolyBase in SQL Server 2016 has a row width limit of 32 KB based on the maximum size of a single valid row by table definition. 列スキーマの合計が 32 KB を超えている場合、PolyBase によるデータのクエリは実行できません。If the sum of the column schema is greater than 32 KB, PolyBase can't query the data.

SQL Data Warehouse では、この制限が 1 MB に引き上げられています。In SQL Data Warehouse, this limitation has been raised to 1 MB.

ロックLocking

SCHEMARESOLUTION オブジェクトに対する共有ロック。Shared lock on the SCHEMARESOLUTION object.

SecuritySecurity

外部テーブルのデータ ファイルは Hadoop または Azure Blob Storage に格納されます。The data files for an external table are stored in Hadoop or Azure blob storage. これらのデータ ファイルはご自身のプロセスによって作成され、管理されます。These data files are created and managed by your own processes. 外部データのセキュリティを管理することは、ユーザー自身の責任になります。It is your responsibility to manage the security of the external data.

使用例Examples

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

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

B.B. HDFS から分散 Analytics Platform System テーブルに行データをインポートしますImport row data from HDFS into a distributed Analytics Platform System Table

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

C.C. HDFS から複製 Analytics Platform System テーブルに行データをインポートしますImport row data from HDFS into a replicated Analytics Platform System Table

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

参照See Also