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

適用対象: ○SQL Server (2016 以降) ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

PolyBase または Elastic Database クエリ用の外部テーブルを作成します。Creates an external table for PolyBase, or Elastic Database queries. シナリオによっては、構文が大幅に異なります。Depending on the scenario, the syntax differs significantly. PolyBase 用に作成された外部テーブルは、Elastic Database のクエリには使用できません。An external table created for PolyBase can't be used for Elastic Database queries. 同様に、Elastic Database のクエリ用に作成された外部テーブルは、PolyBase などには使用できません。Similarly, an external table created for Elastic Database queries can't be used for PolyBase, and so on.

注意

PolyBase は、SQL Server 2016 以降、Azure SQL Data Warehouse、および Parallel Data Warehouse でのみサポートされています。PolyBase is supported only on SQL Server 2016 (or higher), Azure SQL Data Warehouse, and Parallel Data Warehouse. Elastic Database のクエリは、Azure SQL Database v12 以降でのみサポートされています。Elastic Database queries are supported only on Azure SQL Database v12 or later.

  • SQL ServerSQL Server では外部テーブルを使用して、Hadoop クラスターまたは Azure Blob Storage に格納されているデータにアクセスします。PolyBase 外部テーブルは、Hadoop クラスターまたは Azure Blob Storage に格納されているデータを参照します。uses external tables 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. Elastic Database クエリの外部テーブルを作成するためにも使用できます。Can also be used to create an external table for Elastic Database query.

次のために外部テーブルを使用します。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 からデータをインポートして、SQL ServerSQL Server データベースに格納します。Import and store data from Hadoop or Azure blob storage into your SQL ServerSQL Server database.

  • 弾力性のあるデータベース クエリで使用するための外部テーブルを作成しますCreate an external table for use with an Elastic Database
    query.

  • 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 (Transact-SQL)」および「DROP EXTERNAL TABLE (Transact-SQL)」も参照してください。See also CREATE EXTERNAL DATA SOURCE (Transact-SQL) and DROP EXTERNAL TABLE (Transact-SQL).

記事のリンク アイコン Transact-SQL 構文表記規則Article link icon Transact-SQL Syntax Conventions

構文Syntax

-- Syntax for SQL Server 
  
-- Create a new external table  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_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  
  
}  
  
-- Create a table for use with Elastic Database query  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( <column_definition> [ ,...n ] )  
    WITH ( <sharded_external_table_options> )  
[;]  
  
<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]]  
    )  
[;]  
-- Syntax for Azure SQL Database
  
-- Create a table for use with Elastic Database query  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( <column_definition> [ ,...n ] )  
    WITH ( <sharded_external_table_options> )  
[;]  
  
<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]]  
    )  
[;]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
-- Create a new external table in SQL Server PDW  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_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 ] ]  
    )  
[;]  
  
<reject_options> ::=  
{  
    | REJECT_TYPE = value | percentage,  
    | REJECT_VALUE = reject_value,  
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '\REJECT_Directory'
  
}  

引数Arguments

database_name .database_name . [ schema_name ] .[ schema_name ] . | schema_name.| schema_name. ] table_name] table_name
作成するテーブルの 1 つから 3 つの部分で構成される名前。The one to three-part name of the table to create. 外部テーブルの場合、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 では、1 つまたは複数の列の定義を使用できます。<column_definition> [ ,...n ] CREATE EXTERNAL TABLE allows one or more column definitions. CREATE EXTERNAL TABLE と CREATE TABLE の両方で、列を定義するために同じ構文を使用します。Both CREATE EXTERNAL TABLE and CREATE TABLE use the same syntax for defining a column. ただし、外部テーブルに対して DEFAULT CONSTRAINT を使用することはできません。However, you can't use the DEFAULT CONSTRAINT on external tables. 列の定義とそのデータ型の詳細については、「CREATE TABLE (Transact-SQL)」と「CREATE TABLE (Transact-SQL)」を使用してください。For the full details about column definitions and their data types, see CREATE TABLE (Transact-SQL) and CREATE TABLE on Azure SQL Database.

データ型と列の数を含む列の定義は、外部ファイルのデータと一致している必要があります。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'LOCATION = 'folder_or_filepath'
Hadoop または Azure Blob Storage にある実際のデータのフォルダーまたはファイル パスとファイル名を指定します。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.

SQL Data Warehouse と Analytics Platform System では、CREATE EXTERNAL TABLE AS SELECT ステートメントによって、存在しない場合にパスとフォルダーが作成されます。In SQL Data Warehouse and Analytics Platform System, the CREATE EXTERNAL TABLE AS SELECT statement creates the path and folder if it doesn't exist. これらの 2 つの製品では、CREATE EXTERNAL TABLE によってパスとフォルダーは作成されません。In these two products, 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_nameDATA_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 (Transact-SQL) を使用します。To create an external data source, use CREATE EXTERNAL DATA SOURCE (Transact-SQL).

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

拒否オプションReject Options
PolyBase が外部データ ソースから取得したダーティ レコードを処理する方法を決定する、reject パラメーターを指定できます。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 | percentageREJECT_TYPE = value | percentage
REJECT_VALUE オプションがリテラル値として指定されているか、割合として指定されているかを明確にします。Clarifies whether the REJECT_VALUE option is specified as a literal value or a percentage.

valuevalue
REJECT_VALUE は、割合ではなくリテラル値です。REJECT_VALUE is a literal value, not a percentage. 拒否された行が reject_value を超えると、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.

percentagepercentage
REJECT_VALUE は、リテラル値ではなく割合です。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_valueREJECT_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_valueREJECT_SAMPLE_VALUE = reject_sample_value
REJECT_TYPE = percentage を指定する場合、この属性は必須です。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.

シャード化された外部テーブルのオプション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_SOURCEDATA_SOURCE
Hadoop ファイル システム、Azure Blob Storage、または Shard Map Manager に格納されているデータなどの外部データ ソースです。An external data source such as data stored in a Hadoop File System, Azure blob storage, or a shard map manager.

SCHEMA_NAMESCHEMA_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_NAMEOBJECT_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.

DISTRIBUTIONDISTRIBUTION
省略可。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 (Transact-SQL) で、Hadoop のリソース マネージャーの場所のオプションを指定します。To enable it, specify the Hadoop resource manager location option in CREATE EXTERNAL DATA SOURCE (Transact-SQL).

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

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

CTP2 では、SQL データを外部データ ソースに永続的に格納するなどのエクスポート機能はサポートされていません。In CTP2, the export functionality isn't supported, such as permanently storing SQL data into the external data source. この機能は CTP3 で利用可能になります。This functionality will be available in CTP3.

外部テーブルのデータはアプライアンスの外部にあるため、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 接続構成 (Transact-SQL)」を参照してください。For the configuration settings and supported combinations, see PolyBase Connectivity Configuration (Transact-SQL).

外部テーブルに対しては、これらのデータ定義言語 (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
    注:Azure SQL Database では、外部テーブルに対する CREATE STATISTICS と DROP STATISTICS はサポートされていません。Note: CREATE and DROP STATISTICS on external tables aren't supported in Azure SQL Database.

  • 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. テキスト区切り形式のデータを含む外部テーブルを作成します。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 (Transact-SQL)」および「CREATE EXTERNAL FILE FORMAT (Transact-SQL)」を参照してください。For more information, see CREATE EXTERNAL DATA SOURCE (Transact-SQL) and CREATE EXTERNAL FILE FORMAT (Transact-SQL).

  
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 (Transact-SQL)」および「CREATE EXTERNAL FILE FORMAT (Transact-SQL)」を参照してください。For more information, see CREATE EXTERNAL DATA SOURCE (Transact-SQL) and CREATE EXTERNAL FILE FORMAT (Transact-SQL).

  
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 (Transact-SQL)」および「CREATE EXTERNAL FILE FORMAT (Transact-SQL)」を参照してください。For more information, see CREATE EXTERNAL DATA SOURCE (Transact-SQL) and CREATE EXTERNAL FILE FORMAT (Transact-SQL).

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

例: Azure SQL データ ウェアハウスAzure SQL Data Warehouse および Parallel Data WarehouseParallel Data WarehouseExamples: Azure SQL データ ウェアハウスAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

H.H. 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] ; 
     

I.I. 外部テーブルを結合するJoin external tables

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

J.J. HDFS データと PDW データを結合するJoin HDFS data with PDW data

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

K.K. HDFS から分散 PDW テーブルに行のデータをインポートするImport row data from HDFS into a distributed PDW Table

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

L.L. HDFS からレプリケートされた PDW テーブルに行のデータをインポートするImport row data from HDFS into a replicated PDW Table

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

参照See Also

CREATE EXTERNAL DATA SOURCE (Transact-SQL) CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL FILE FORMAT (Transact-SQL) CREATE EXTERNAL FILE FORMAT (Transact-SQL)
CREATE EXTERNAL TABLE AS SELECT (Transact-SQL) CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
CREATE TABLE AS SELECT (Azure SQL Data Warehouse)CREATE TABLE AS SELECT (Azure SQL Data Warehouse)