Synapse SQL で外部テーブルを使用するUse external tables with Synapse SQL

外部テーブルは、Hadoop、Azure Storage BLOB、または Azure Data Lake Storage にあるデータを参照します。An external table points to data located in Hadoop, Azure Storage blob, or Azure Data Lake Storage. 外部テーブルは、Azure Storage 内のファイルからデータを読み取ったり、ファイルにデータを書き込んだりするために使用されます。External tables are used to read data from files or write data to files in Azure Storage. Synapse SQL では、外部テーブルを使用して、専用 SQL プールまたはサーバーレス SQL プールに対するデータの読み取りと書き込みを行えます。With Synapse SQL, you can use external tables to read and write data to dedicated SQL pool or serverless SQL pool.

専用 SQL プールとサーバーレス SQL プールにおける外部テーブルExternal tables in dedicated SQL pool and serverless SQL pool

専用 SQL プールでは、外部テーブルを使用して以下を行えます。In dedicated SQL pool, you can use an external table to:

  • Transact-SQL ステートメントを使用して、Azure Blob Storage と Azure Data Lake Gen2 に対するクエリを実行する。Query Azure Blob Storage and Azure Data Lake Gen2 with Transact-SQL statements.
  • Azure Blob Storage と Azure Data Lake Storage から専用 SQL プールにデータのインポートと格納を行う。Import and store data from Azure Blob Storage and Azure Data Lake Storage into dedicated SQL pool.

CREATE TABLE AS SELECT ステートメントと組み合わせて使用する場合は、外部テーブルから選択するとデータが SQL プール内のテーブルにインポートされます。When used in conjunction with the CREATE TABLE AS SELECT statement, selecting from an external table imports data into a table within the SQL pool. COPY ステートメントに加えて、外部テーブルはデータの読み込みにも便利です。In addition to the COPY statement, external tables are useful for loading data.

読み込みのチュートリアルについては、PolyBase を使用した Azure Blob Storage からのデータの読み込みに関するページを参照してください。For a loading tutorial, see Use PolyBase to load data from Azure Blob Storage.

セキュリティSecurity

ユーザーは、外部テーブルのデータを読み取る場合、それに対する SELECT アクセス許可が必要です。User must have SELECT permission on external table to read the data. 外部テーブルから、基になる Azure Storage へのアクセスは、次の規則を使用してデータソース内で定義されているデータベース スコープ資格情報を使用して行います。External table access underlying Azure storage using the database scoped credential defined in data source using the following rules:

  • 資格情報なしのデータソースの場合、外部テーブルからは、Azure Storage 上の一般公開されているファイルにアクセスできます。Data source without credential enables external tables to access publicly available files on Azure storage.
  • SAS トークンまたはワークスペースのマネージド ID を使用して外部テーブルが Azure Storage 上のファイルにしかアクセスできないようにするための資格情報をデータソースに含めることができます。例については、ストレージ ファイルのストレージ アクセス制御の開発に関する記事を参照してください。Data source can have credential that enables external tables to access only the files on Azure storage using SAS token or workspace Managed Identity - For examples, see the Develop storage files storage access control article.

重要

専用 SQL プール内では、資格情報なしで作成されたデータソースから、Azure AD ユーザーは自分の Azure AD ID を使用してストレージ ファイルにアクセスすることができます。In dedicated SQL pool, a data source created without a credential enables Azure AD users to access storage files using their Azure AD identity. サーバーレス SQL プールでは、IDENTITY='User Identity' プロパティを含むデータベース スコープ資格情報を使用してデータソースを作成する必要があります。こちらの例を参照してください。In serverless SQL pool, you need to create a data source with a database-scoped credential that has IDENTITY='User Identity' property - see examples here.

CREATE EXTERNAL DATA SOURCECREATE EXTERNAL DATA SOURCE

外部データ ソースは、ストレージ アカウントへの接続に使用されます。External data sources are used to connect to storage accounts. 完全なドキュメントについては、こちらで概説されています。The complete documentation is outlined here.

CREATE EXTERNAL DATA SOURCE の構文Syntax for CREATE EXTERNAL DATA SOURCE

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
     , TYPE = HADOOP
)
[;]

CREATE EXTERNAL DATA SOURCE の引数Arguments for CREATE EXTERNAL DATA SOURCE

data_source_namedata_source_name

データ ソースのユーザー定義の名前を指定します。Specifies the user-defined name for the data source. 名前は、データベース内で一意である必要があります。The name must be unique within the database.

場所Location

LOCATION = '<prefix>://<path>': 接続プロトコルと外部データ ソースへのパスを指定します。LOCATION = '<prefix>://<path>' - Provides the connectivity protocol and path to the external data source. 場所では、次のパターンを使用できます。The following patterns can be used in location:

外部データ ソースExternal Data Source 場所プレフィックスLocation prefix ロケーション パスLocation path
Azure Blob StorageAzure Blob Storage wasb[s] <container>@<storage_account>.blob.core.windows.net
Azure Blob StorageAzure Blob Storage http[s] <storage_account>.blob.core.windows.net/<container>/subfolders
Azure Data Lake Store Gen 1Azure Data Lake Store Gen 1 http[s] <storage_account>.azuredatalakestore.net/webhdfs/v1
Azure Data Lake Store Gen 2Azure Data Lake Store Gen 2 http[s] <storage_account>.dfs.core.windows.net/<container>/subfolders

https: プレフィックスを使用すると、パスでサブフォルダーを使用できます。https: prefix enables you to use subfolder in the path.

資格情報Credential

CREDENTIAL = <database scoped credential> は、Azure Storage 上で認証を受けるために使用される省略可能な資格情報です。CREDENTIAL = <database scoped credential> is optional credential that will be used to authenticate on Azure storage. 資格情報を持たない外部データソースからは、パブリック ストレージ アカウントにアクセスできます。External data source without credential can access public storage account.

専用 SQL プール内の資格情報を持たない外部データソースからは、呼び出し元の Azure AD ID を使用して、ストレージ上のファイルにアクセスします。External data sources without a credential in dedicated SQL pool will use caller's Azure AD identity to access files on storage. 資格情報 IDENTITY='User Identity' があるサーバーレス SQL プールの外部データ ソースからは、呼び出し元の Azure AD ID を使用してファイルにアクセスします。An external data source for serverless SQL pool with credential IDENTITY='User Identity' will use caller's Azure AD identity to access files.

  • 専用 SQL プールでは、データベース スコープ資格情報を使用して、カスタム アプリケーション ID、ワークスのペース マネージド ID、または SAK キーを指定できます。In dedicated SQL pool, database scoped credential can specify custom application identity, workspace Managed Identity, or SAK key.
  • サーバーレス SQL プールでは、データベース スコープ資格情報を使用して、呼び出し元の Azure AD ID、ワークスペースのマネージド ID、または SAS キーを指定できます。In serverless SQL pool, database scoped credential can specify caller's Azure AD identity, workspace Managed Identity, or SAS key.

TYPETYPE

TYPE = HADOOP は専用 SQL プールでの必須オプションです。これにより、Polybase テクノロジを使用して基になるファイルにアクセスすることが指定されます。TYPE = HADOOP is the mandatory option in dedicated SQL pool and specifies that Polybase technology is used to access underlying files. このパラメーターは、組み込みのネイティブ リーダーを使用するサーバーレス SQL プールでは使用できません。This parameter can't be used in serverless SQL pool that uses built-in native reader.

CREATE EXTERNAL DATA SOURCE の例Example for CREATE EXTERNAL DATA SOURCE

次の例では、New York データ セットを参照する Azure Data Lake Gen2 の外部データ ソースを作成します。The following example creates an external data source for Azure Data Lake Gen2 pointing to the New York data set:

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

CREATE EXTERNAL FILE FORMATCREATE EXTERNAL FILE FORMAT

Azure Blob Storage または Azure Data Lake Storage に格納される外部データを定義する外部ファイル形式オブジェクトを作成します。Creates an external file format object that defines external data stored in Azure Blob Storage or Azure Data Lake Storage. 外部ファイル形式の作成は、外部テーブルを作成するための前提条件です。Creating an external file format is a prerequisite for creating an external table. 完全なドキュメントはこちらにあります。The complete documentation is here.

外部ファイル形式を作成することで、外部テーブルによって参照されるデータの実際のレイアウトを指定します。By creating an external file format, you specify the actual layout of the data referenced by an external table.

CREATE EXTERNAL FILE FORMAT の構文Syntax for CREATE EXTERNAL FILE FORMAT

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = PARQUET  
    [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.GzipCodec'      }  
    ]);  

--Create an external file format for DELIMITED TEXT files
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT  
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ]
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
    );  

<format_options> ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter
    | First_Row = integer
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | Encoding = {'UTF8' | 'UTF16'}
}

CREATE EXTERNAL FILE FORMAT の引数Arguments for CREATE EXTERNAL FILE FORMAT

file_format_name: 外部ファイル形式の名前を指定します。file_format_name- Specifies a name for the external file format.

FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT]: 外部データの形式を指定します。FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT]- Specifies the format of the external data.

  • PARQUET: Parquet 形式を指定します。PARQUET - Specifies a Parquet format.
  • DELIMITEDTEXT: フィールド ターミネータとも呼ばれる、列区切り記号付きのテキスト形式を指定します。DELIMITEDTEXT - Specifies a text format with column delimiters, also called field terminators.

FIELD_TERMINATOR = field_terminator: 区切りテキスト ファイルにのみ適用されます。FIELD_TERMINATOR = field_terminator - Applies only to delimited text files. フィールド ターミネータは、テキスト区切りファイルでの各フィールド (列) の終了を示す 1 つ以上の文字を指定します。The field terminator specifies one or more characters that mark the end of each field (column) in the text-delimited file. 既定値はパイプ文字 (ꞌ|ꞌ) です。The default is the pipe character (ꞌ|ꞌ).

例 :Examples:

  • FIELD_TERMINATOR = '|'FIELD_TERMINATOR = '|'
  • FIELD_TERMINATOR = ' 'FIELD_TERMINATOR = ' '
  • FIELD_TERMINATOR = ꞌ\tꞌFIELD_TERMINATOR = ꞌ\tꞌ

STRING_DELIMITER = string_delimiter: テキスト区切りのファイル内の文字列型のデータにはフィールド ターミネータを指定します。STRING_DELIMITER = string_delimiter - Specifies the field terminator for data of type string in the text-delimited file. 文字列の区切り記号の長さは 1 文字または複数文字とし、単一引用符で囲みます。The string delimiter is one or more characters in length and is enclosed with single quotes. 既定値は空の文字列 ("") です。The default is the empty string ("").

例 :Examples:

  • STRING_DELIMITER = '"'STRING_DELIMITER = '"'
  • STRING_DELIMITER = '*'STRING_DELIMITER = '*'
  • STRING_DELIMITER = ꞌ,ꞌSTRING_DELIMITER = ꞌ,ꞌ

FIRST_ROW = First_row_int: 最初に読み取られる行の番号を指定し、すべてのファイルに適用します。FIRST_ROW = First_row_int - Specifies the row number that is read first and applies to all files. 値を 2 に設定すると、データの読み込み時に各ファイルの最初の行 (ヘッダー行) がスキップされます。Setting the value to two causes the first row in every file (header row) to be skipped when the data is loaded. 行は、行ターミネータ (/r/n、/r、/n) の存在に基づいてスキップされます。Rows are skipped based on the existence of row terminators (/r/n, /r, /n).

USE_TYPE_DEFAULT = { TRUE | FALSE }: テキスト ファイルからデータを取得するときに、区切りテキスト ファイル内の不足値を処理する方法を指定します。USE_TYPE_DEFAULT = { TRUE | FALSE } - Specifies how to handle missing values in delimited text files when retrieving data from the text file.

TRUE: テキスト ファイルからデータを取得するときは、外部テーブルの定義内の対応する列に既定値のデータ型を使用して、各不足値を格納します。TRUE - If you're retrieving data from the text file, store each missing value by using the default value's data type for the corresponding column in the external table definition. たとえば、不足値を次の値に置き換えます。For example, replace a missing value with:

  • 列が numeric 型の列として定義されている場合は 00 if the column is defined as a numeric column. decimal 型の列はサポートされておらず、エラーになります。Decimal columns aren't supported and will cause an error.
  • 列が string 型の列である場合は空の文字列 ("") です。Empty string ("") if the column is a string column.
  • 列が date 列の場合は 1900-01-01 です。1900-01-01 if the column is a date column.

FALSE: すべての不足値を NULL として格納します。FALSE - Store all missing values as NULL. 区切りテキスト ファイルで単語 NULL を使って格納されている NULL 値は、文字列 "NULL" としてインポートされます。Any NULL values that are stored by using the word NULL in the delimited text file are imported as the string 'NULL'.

Encoding = {'UTF8' | 'UTF16'}: サーバーレス SQL プールでは、UTF8 および UTF16 でエンコードされた区切りテキスト ファイルを読み取ることができます。Encoding = {'UTF8' | 'UTF16'} - Serverless SQL pool can read UTF8 and UTF16 encoded delimited text files.

DATA_COMPRESSION = data_compression_method: この引数では、外部データのデータ圧縮方法を指定します。DATA_COMPRESSION = data_compression_method - This argument specifies the data compression method for the external data.

PARQUET ファイル形式の種類では、次の圧縮方法がサポートされています。The PARQUET file format type supports the following compression methods:

  • DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
  • DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

PARQUET 外部テーブルから読み取る場合に、この引数は無視されます。しかし、CETAS を使用して外部テーブルに書き込む場合は使用されます。When reading from PARQUET external tables, this argument is ignored, but is used when writing to external tables using CETAS.

DELIMITEDTEXT ファイル形式の種類では、次の圧縮方法がサポートされています。The DELIMITEDTEXT file format type supports the following compression method:

  • DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'

PARSER_VERSION = 'parser_version': ファイルの読み取り時に使用するパーサーのバージョンを指定します。PARSER_VERSION = 'parser_version' Specifies parser version to be used when reading files. 詳細については、OPENROWSET 引数に関するページの PARSER_VERSION 引数を確認してください。Check PARSER_VERSION argument in OPENROWSET arguments for details.

CREATE EXTERNAL FILE FORMAT の例Example for CREATE EXTERNAL FILE FORMAT

次の例では、国勢調査ファイルの外部ファイル形式を作成します。The following example creates an external file format for census files:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

CREATE EXTERNAL TABLECREATE EXTERNAL TABLE

CREATE EXTERNAL TABLE コマンドでは、Azure Blob Storage または Azure Data Lake Storage 内に格納されたデータにアクセスするために Synapse SQL の外部テーブルを作成します。The CREATE EXTERNAL TABLE command creates an external table for Synapse SQL to access data stored in Azure Blob Storage or Azure Data Lake Storage.

CREATE EXTERNAL TABLE の構文Syntax for CREATE 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
    )  
[;]  

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

CREATE EXTERNAL TABLE の引数Arguments CREATE EXTERNAL TABLE

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

作成するテーブルの 1 つから 3 つの部分で構成される名前。The one to three-part name of the table to create. 外部テーブルの場合、サーバーレス SQL プールではテーブルのメタデータのみが格納されます。For an external table, serverless SQL pool stores only the table metadata. サーバーレス SQL プールでは、実際のデータの移動または格納は行われません。No actual data is moved or stored in serverless SQL pool.

<column_definition>, ...n ]<column_definition>, ...n ]

CREATE EXTERNAL TABLE では、列名、データ型、NULL 値の許容、照合順序を構成できます。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.

Parquet ファイルからの読み取りの場合は、読み取りたい列だけを指定して、残りをスキップすることができます。When reading from Parquet files, you can specify only the columns you want to read and skip the rest.

LOCATION = 'folder_or_filepath'LOCATION = 'folder_or_filepath'

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

フォルダーの LOCATION を指定すると、サーバーレス SQL プールのクエリで外部テーブルから選択が行われ、そのフォルダーからファイルが取得されます。If you specify a folder LOCATION, a serverless SQL pool query will select from the external table and retrieve files from the folder.

注意

Hadoop や PolyBase とは異なり、サーバーレス SQL プールではサブフォルダーは返されません。Unlike Hadoop and PolyBase, serverless SQL pool doesn't return subfolders. ファイル名が下線 () またはピリオド (.) で始まるファイルが返されます。It returns files for which the file name begins with an underline () or a period (.).

この例では、LOCATION='/webdata/' である場合、サーバーレス SQL プールのクエリで mydata.txt と _hidden.txt から行が返されます。In this example, if LOCATION='/webdata/', a serverless SQL pool query, will return rows from mydata.txt and _hidden.txt. mydata2.txt と mydata3.txt はサブフォルダー内にあるため、これらは返されません。It won't return mydata2.txt and mydata3.txt because they're located in a subfolder.

外部テーブルの再帰型データ

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. 外部データ ソースを作成するには、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.

CREATE EXTERNAL TABLE のアクセス許可Permissions CREATE EXTERNAL TABLE

外部テーブルから選択を行うには、適切な資格情報のほか、リストと読み取りのアクセス許可が必要です。To select from an external table, you need proper credentials with list and read permissions.

CREATE EXTERNAL TABLE の例Example CREATE EXTERNAL TABLE

次の例では、外部テーブルを作成します。The following example creates an external table. 最初の行が返されます。It returns the first row:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Azure Data Lake 内のファイルから外部テーブルを作成してクエリを実行するCreate and query external tables from a file in Azure Data Lake

Data Lake の探索機能を使用することで、ファイルを右クリックするだけで、専用 SQL プールまたはサーバーレス SQL プールを使って外部テーブルを作成してクエリを実行できるようになりました。Using Data Lake exploration capabilities you can now create and query an external table using dedicated SQL pool or serverless SQL pool with a simple right-click on the file. ADLS Gen2 ストレージ アカウントから外部テーブルを作成するワンクリック ジェスチャーは、Parquet ファイルでのみサポートされます。The one-click gesture to create external tables from the ADLS Gen2 storage account is only supported for Parquet files.

前提条件Prerequisites

  • 少なくとも ADLS Gen2 アカウントに対するストレージ BLOB データ共同作成者 ARM アクセス ロールがある状態で、ワークスペースにアクセスできる必要がありますYou must have access to the workspace with at least the Storage Blob Data Contributor ARM Access role to the ADLS Gen2 Account

  • SQL プールまたは SQL オンデマンドで外部テーブルを作成してクエリを実行するためのアクセス許可が少なくとも必要ですYou must have at least permissions to create and query external tables on the SQL pool or SQL OD

[データ] パネルで、外部テーブルの作成元にするファイルを選択します。From the Data panel, select the file that you would like to create the external table from:

externaltable1externaltable1

ダイアログ ウィンドウが開きます。A dialog window will open. 専用 SQL プールかサーバーレス SQL プールを選択し、テーブルに名前を付けて [スクリプトを開く] を選択します。Select dedicated SQL pool or serverless SQL pool, give a name to the table and select open script:

externaltable2externaltable2

SQL スクリプトは、ファイルからのスキーマの推論により自動的に生成されます。The SQL Script is autogenerated inferring the schema from the file:

externaltable3externaltable3

スクリプトを実行します。Run the script. このスクリプトでは、Select Top 100 *. が自動的に実行されます。The script will automatically run a Select Top 100 *.:

externaltable4externaltable4

外部テーブルが作成されました。今後この外部テーブルの内容を探索するために、ユーザーは [データ] ペインから直接クエリを実行できます。The external table is now created, for future exploration of the content of this external table the user can query it directly from the Data pane:

externaltable5externaltable5

次のステップNext steps

クエリの結果を Azure Storage の外部テーブルに保存する方法については、CETAS に関する記事をご覧ください。Check the CETAS article for how to save the query results to an external table in Azure Storage. また、Azure Synapse 外部テーブルの Apache Spark に対するクエリを開始することもできます。Or you can start querying Apache Spark for Azure Synapse external tables.