Azure Storage の外部テーブルを作成および変更する

  • [アーティクル]

この記事のコマンドを使用して、コマンドの実行元となるデータベース内の Azure Storage 外部テーブル を作成または変更できます。 Azure Storage 外部テーブルは、Azure Blob Storage、Azure Data Lake Store Gen1、または Azure Data Lake Store Gen2 にあるデータを参照します。

Note

テーブルが存在する場合、.create コマンドは失敗し、エラーが表示されます。 既存のテーブルを変更するには、.create-or-alter または .alter を使用します。

アクセス許可

少.createなくともデータベース ユーザーのアクセス許可を.alter必要とし、少なくともテーブル 管理のアクセス許可を必要とするには。

マネージド ID 認証を使用する外部テーブルには.create-or-alter、AllDatabasesAdmin アクセス許可が必要です。

構文

(.create.alter.create-or-alter | | ) externaltableTableName(スキーマ)=storagekind [partitionby(Partitions) [pathformat(=PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] )[with(プロパティ [, ...])]

注意

kind は、 storage すべての Azure Storage 外部データ ストアの種類用です。 blob と adl は非推奨の用語です。

構文規則について詳しく知る。

パラメーター

名前 型 必須 説明
TableName string ✔️ エンティティ名ルールに準拠する外部テーブル 名 。 外部テーブルは、同じデータベース内の通常のテーブルと同じ名前にすることはできません。
[スキーマ] string ✔️ 外部データ スキーマは、1 つ以上の列名と データ型のコンマ区切りのリストです。各項目の形式は ColumnName:ColumnType です。 スキーマが不明な場合は、 infer_storage_schema を使用して、外部ファイルの内容に基づいてスキーマを推論します。
パーティション string 外部テーブルをパーティション分割する列のコンマ区切りのリスト。 パーティション列は、データ ファイル自体に存在することも、ファイル パスの一部として存在することもできます。 この値の外観については、 パーティションの書式設定 に関するページを参照してください。
PathFormat string パーティションで使用する外部データ フォルダー URI パス形式。 「パスの形式」を参照してください。
DataFormat string ✔️ データ形式。 任意のインジェスト形式を指定できます。 パス マッピングを Parquet 使用しない限り、クエリとエクスポートのパフォーマンスを向上させるには、外部テーブルの形式を使用 JSON することをお勧めします。 エクスポート シナリオで外部テーブルを使用する場合は、、、および Parquetの形式CSVTSVJSONに制限されます。
StorageConnectionString string ✔️ 資格情報を含む、BLOB コンテナー、Azure Data Lake Gen 2 ファイル システム、または Azure Data Lake Gen 1 コンテナーをAzure Blob Storageするための 1 つ以上のコンマ区切りパス。 外部テーブルストレージの型は、指定された接続文字列によって決定されます。 ストレージ接続文字列に関するページを参照してください。
プロパティ string PropertyName=PropertyValue という形式のキーと値のプロパティのペア。 省略可能なプロパティを参照してください。

注意

スキーマが同一でない CSV ファイルでは、データがシフトまたは不足している可能性があります。 個別のスキーマを持つ CSV ファイルを分離してストレージ コンテナーを分離し、適切なスキーマを使用して各ストレージ コンテナーの外部テーブルを定義することをお勧めします。

ヒント

大量のデータを外部テーブルにエクスポートするときにストレージの調整を回避するには、ストレージ アカウントを 1 つ以上指定します。 エクスポートは、提供されたすべてのアカウント間の書き込みを分散します。

認証と承認

外部テーブルにアクセスするための認証方法は、作成時に提供される接続文字列に基づいており、テーブルへのアクセスに必要なアクセス許可は認証方法によって異なります。

次の表に、Azure Storage 外部テーブルでサポートされている認証方法と、テーブルの読み取りまたは書き込みに必要なアクセス許可を示します。

認証方法 Azure Blob Storage/Data Lake Storage Gen2 Data Lake Storage Gen1
偽装 読み取りアクセス許可: ストレージ BLOB データ 閲覧者
書き込みアクセス許可: ストレージ BLOB データ共同作成者		 読み取りアクセス許可: リーダー
書き込みアクセス許可: 貢献
管理対象 ID 読み取りアクセス許可: ストレージ BLOB データ 閲覧者
書き込みアクセス許可: ストレージ BLOB データ共同作成者		 読み取りアクセス許可: リーダー
書き込みアクセス許可: 貢献
Shared Access (SAS) トークン 読み取りアクセス許可: リスト + 読み取り
書き込みアクセス許可: 書き込み		 この認証方法は Gen1 ではサポートされていません。
Microsoft Entra アクセス トークン 追加のアクセス許可は必要ありません。 追加のアクセス許可は必要ありません。
ストレージ アカウントのアクセス キー 追加のアクセス許可は必要ありません。 この認証方法は Gen1 ではサポートされていません。

パーティションの書式設定

パーティションの一覧は、次の表に示す形式のいずれかを使用して指定されたパーティション列の任意の組み合わせです。

パーティションの種類 構文 メモ
仮想列 PartitionName: (datetime | string) 仮想列の詳細については、こちらをご覧ください。
文字列列の値 PartitionName:string=ColumnName
文字列列の値 ハッシュ PartitionName:long=hash(ColumnName,Number) ハッシュは剰余 数値です。
切り捨てられた datetime 列 (値) PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (ColumnName) startofyear、startofmonth、startofweek、または startofday 関数に関するドキュメントを参照してください。
切り捨てられた Datetime 列の値 (bin) PartitionName:datetime=bin(ColumnName,TimeSpan) bin 関数の詳細については、こちらをご覧ください。

パスの形式

PathFormat パラメーターを使用すると、パーティションに加えて、外部データ フォルダー URI パスの形式を指定できます。 パーティション要素とテキスト区切り記号のシーケンスで構成されます。 partition 要素は、partition by 句で宣言されているパーティションを参照し、テキスト区切り記号は引用符で囲まれた任意のテキストです。 連続するパーティション要素は、テキスト区切り記号を使用して分離する必要があります。

[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]

元のファイル パス プレフィックスを構築するために、パーティション要素は文字列としてレンダリングされ、対応するテキスト区切り記号で区切られます。 マクロ (datetime_pattern(DateTimeFormat,PartitionName)) を使用datetime_patternして、datetime パーティション値のレンダリングに使用する形式を指定できます。 マクロは .NET 形式の仕様に従い、書式指定子を中かっこで囲みます。 たとえば、次の 2 つのコマンドは同じものです。

  • 'year='yyyy'/month='MM
  • year={yyyy}/month={MM}

既定では、datetime 値は次の形式で表示されます。

パーティション関数 既定の形式
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin(Column, 1d) yyyy/MM/dd
bin(Column, 1h) yyyy/MM/dd/HH
bin(Column, 1m) yyyy/MM/dd/HH/mm

ヒント

パーティションと PathFormat 定義の正確性をチェックするには、 プロパティsampleUrisを使用するかfilesPreview、外部テーブルを作成するときにを使用します。

仮想列

Spark からデータをエクスポートする場合、パーティション列 (データフレーム ライターの partitionBy メソッドに提供) はデータ ファイルに書き込まれません。 このプロセスでは、データがフォルダー名 (たとえば column1=<value>/column2=<value>/) に既に存在し、Spark が読み取り時に認識することができるため、データの重複が回避されます。

外部テーブルは、virtual colums の形式でのこのデータの読み取りをサポートしています。 仮想列は、string 型または datetime 型のいずれかであるものとして、次の構文を使用して指定できます。

.create external table ExternalTable (EventName:string, Revenue:double)  
kind=storage  
partition by (CustomerName:string, Date:datetime)  
pathformat=("customer=" CustomerName "/date=" datetime_pattern("yyyyMMdd", Date))  
dataformat=parquet
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)

クエリ内の仮想列でフィルター処理するには、クエリ述語でパーティション名を指定します。

external_table("ExternalTable")
 | where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

省略可能なプロパティ

プロパティ Type 説明
folder string テーブルのフォルダー
docString string テーブルを文書化する文字列
compressed bool 設定すると、ファイルが .gz ファイルとして圧縮されるかどうかを示します。(エクスポート シナリオのみで使用)
includeHeaders string 区切りテキスト形式 (CSV、TSV、...) の場合は、ファイルにヘッダーが含まれているかどうかを示します。 指定できる値は、All (すべてのファイルにヘッダーが含まれている)、FirstFile (フォルダー内の最初のファイルにヘッダーが含まれている)、None (ヘッダーを含むファイルはない) のいずれかです。
namePrefix string 設定した場合、ファイルのプレフィックスを示します。 書き込み操作では、すべてのファイルがこのプレフィックスを使用して書き込まれます。 読み取り操作では、このプレフィックスを持つファイルだけが読み取られます。
fileExtension string 設定すると、ファイルの拡張子を示します。 書き込み時には、ファイル名の末尾がこのサフィックスになります。 読み取り時には、このファイル拡張子を持つファイルのみが読み取られます。
encoding string テキストのエンコード方法を示します: UTF8NoBOM (既定値) または UTF8BOM。
sampleUris bool 設定した場合、コマンドの結果には、外部テーブル定義で想定されるシミュレートされた外部データ ファイル URI の例がいくつか示されます。 このオプションは、PartitionsとPathFormatパラメータが正しく定義されているかどうかを確認するのに役立ちます。
filesPreview bool 設定した場合、コマンド結果テーブルの 1 つは .show external table artifacts コマンドのプレビューが含まれます。 sampleUriと同様に、このオプションは外部テーブル定義の Partitions と PathFormat パラメータを確認するのに役立ちます。
validateNotEmpty bool 設定した場合、接続文字列がコンテンツを含むか検証されます。 指定した URI の場所が存在しない場合、またはアクセスするための十分なアクセス許可が存在しない場合、コマンドは失敗します。
dryRun bool 設定した場合、外部テーブル定義は永続化されません。 このオプションは、特に filesPreview または sampleUris パラメータと組み合わせて、外部テーブル定義を検証するのに便利です。

Note

外部テーブルには、クエリとエクスポート中にのみ、作成時にはアクセスされません。 作成時に validateNotEmpty 省略可能なプロパティを使用して、テーブル定義が有効であり、ストレージにアクセスできることを確認します。

ヒント

namePrefix と fileExtension のプロパティがクエリ中のデータ ファイル フィルター処理で果たすロールについては、「ファイル フィルター処理のグロジック」のセクションを参照してください。

ファイル フィルタリング ロジック

外部テーブルに対してクエリを実行すると、無関係な外部ストレージ ファイルをフィルターで除外することでパフォーマンスが向上します。 ファイルを反復処理し、ファイルを処理する必要があるかどうかを判断するプロセスは、次のとおりです。

  1. ファイルが見つかる場所を表す URI パターンを作成します。 最初は、URI パターンは、外部テーブル定義の一部として指定された接続文字列と等しくなります。 パーティションが定義されている場合は、 PathFormat を使用してレンダリングされ、URI パターンに追加されます。

  2. 作成された URI パターンで見つかったすべてのファイルについて、次のようにチェックします。

    • パーティション値は、クエリで使用される述語と一致します。
    • BLOB 名は、NamePrefix で始まります (このようなプロパティが定義されている場合)。
    • BLOB 名は、FileExtension で終わります (このようなプロパティが定義されている場合)。

すべての条件が満たされると、ファイルがフェッチされて処理されます。

Note

初期 URI パターンは、クエリ述語の値を使用して構築されます。 これは、限られた文字列値のセットと閉じた時間範囲に最適です。

例

パーティション分割されていない外部テーブル

次のパーティション分割されていない外部テーブルでは、ファイルは定義されているコンテナーのすぐ下に配置されることが想定されています。

.create external table ExternalTable (x:long, s:string)  
kind=storage 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

日付でパーティション分割

日付でパーティション分割された次の外部テーブルでは、ファイルは既定の datetime 形式 yyyy/MM/ddのディレクトリの下に配置されることが想定されています。

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage
partition by (Date:datetime = bin(Timestamp, 1d)) 
dataformat=csv 
( 
   h@'abfss://filesystem@storageaccount.dfs.core.windows.net/path;secretKey'
)

月別にパーティション分割

月別にパーティション分割された次の外部テーブルでは、ディレクトリ形式は です year=yyyy/month=MM。

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage 
partition by (Month:datetime = startofmonth(Timestamp)) 
pathformat=(datetime_pattern("'year='yyyy'/month='MM", Month)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

名前と日付でパーティション分割

次の外部テーブルでは、データは最初に顧客名でパーティション分割され、次に日付でパーティション分割されます。つまり、想定されるディレクトリ構造は です。たとえば、 customer_name=Softworks/2019/02/01です。

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerNamePart:string = CustomerName, Date:datetime = startofday(Timestamp)) 
pathformat=("customer_name=" CustomerNamePart "/" Date)
dataformat=csv 
(  
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

ハッシュと日付でパーティション分割

次の外部テーブルは、最初に顧客名ハッシュ (剰余 10)、次に日付でパーティション分割されます。 予想されるディレクトリ構造は、たとえば、 で、 customer_id=5/dt=20190201データ ファイル名は拡張子で終わるもの .txt になります。

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerId:long = hash(CustomerName, 10), Date:datetime = startofday(Timestamp)) 
pathformat=("customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd", Date)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
with (fileExtension = ".txt")

クエリのパーティション列でフィルター処理する

クエリ内のパーティション列でフィルター処理するには、クエリ述語で元の列名を指定します。

external_table("ExternalTable")
 | where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

サンプル出力

TableName TableType フォルダー DocString プロパティ ConnectionStrings メジャー グループ PathFormat
ExternalTable BLOB ExternalTables Docs {"Format":"Csv","Compressed":false,"CompressionType":null,"FileExtension":null,"IncludeHeaders":"None","Encoding":null,"NamePrefix":null} ["https://storageaccount.blob.core.windows.net/container1;*******"] [{"Mod":10,"Name":"CustomerId","ColumnName":"CustomerName","Ordinal":0},{"Function":"StartOfDay","Name":"Date","ColumnName":"Timestamp","Ordinal":1}] "customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd",Date)

関連コンテンツ