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には、少なくとも Database User アクセス許可が必要であり、.alterには少なくとも Table 管理者アクセス許可が必要です。
マネージド ID 認証を使用して外部テーブルを .create-or-alter するには、 AllDatabasesAdmin アクセス許可が必要です。
構文
(.create | .alter | .create-or-alter) external table TableName (Schema) kind = storage [partition by (Partitions) [pathformat = (PathFormat)]] dataformat = DataFormat (StorageConnectionString [, ...] ) [with (Property [, ...])]
Note
kind は、すべての Azure Storage 外部データ ストアの種類に対して storage されます。 blob および adl は非推奨の用語です。
構文規則について詳しく知る。
パラメーター
| 件名 | タイプ | Required | 説明 |
|---|---|---|---|
| TableName | string |
✔️ | エンティティ名規則に準拠する外部テーブル名。 外部テーブルは、同じデータベース内の通常のテーブルと同じ名前にすることはできません。 |
| [スキーマ] | string |
✔️ | 外部データ スキーマは、1 つ以上の列名と data 型のコンマ区切りのリストです各項目は次の形式に従います: ColumnName : ColumnType。 スキーマが不明な場合は、 infer_storage_schema を使用して、外部ファイルの内容に基づいてスキーマを推論します。 |
| パーティション | string |
外部テーブルをパーティション分割する列のコンマ区切りのリスト。 パーティション列は、データ ファイル自体に存在することも、ファイル パスの一部として存在することもできます。 この値の表示方法についてはパーティションの書式設定に関するページを参照してください。 | |
| PathFormat | string |
パーティションで使用する外部データ フォルダー URI パス形式。 パス形式を参照してください。 | |
| DataFormat | string |
✔️ | データ形式。 ingestion 形式のいずれかを指定できます。 JSON パス マッピングを使用しない限り、クエリとエクスポートのパフォーマンスを向上させるには、外部テーブルにParquet形式を使用することをお勧めします。 export シナリオで外部テーブルを使用する場合は、CSV、TSV、JSON、Parquetの形式に制限されます。 |
| StorageConnectionString | string |
✔️ | 資格情報を含む、Azure Blob Storage BLOB コンテナー、Azure Data Lake Gen 2 ファイル システム、または Azure Data Lake Gen 1 コンテナーへの 1 つ以上のコンマ区切りのパス。 外部テーブルストレージの型は、指定された接続文字列によって決定されます。 storage 接続文字列を参照してください。 |
| プロパティ | string |
PropertyName = PropertyValue の形式のキーと値のプロパティのペア。 オプションのプロパティを参照してください。 |
Note
スキーマが同一でない 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 |
|
| 文字列列の値 hash() | PartitionName : long = hash(ColumnName, Number) |
ハッシュは剰余 Numberです。 |
| 切り捨てられた datetime 列 (値) | PartitionName : datetime = (startofyear | startofmonth | startofweek | startofday) ( ColumnName ) |
startofyear、startofmonth、startofweek、または startofday 関数に関するドキュメントを参照してください。 |
切り捨てられた Datetime 列の値 = bin ( ColumnName , TimeSpan ) |
bin関数の詳細を参照してください。 |
パスの形式
PathFormat パラメーターを使用すると、パーティションに加えて、外部データ フォルダーの URI パスの形式を指定できます。 パーティション要素とテキスト区切り記号のシーケンスで構成されます。 パーティション要素は、パーティション by 句で宣言されているパーティションを参照し、テキスト区切り記号は引用符で囲まれた任意のテキストです。 連続するパーティション要素は、テキスト区切り記号を使用して分離する必要があります。
[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]
元のファイル パス プレフィックスを構築するために、パーティション要素は文字列としてレンダリングされ、対応するテキスト区切り記号で区切られます。 datetime_pattern マクロ (datetime_pattern(DateTimeFormat, PartitionName)) を使用して、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(列, 1d) |
yyyy/MM/dd |
bin(列, 1h) |
yyyy/MM/dd/HH |
bin(列, 1m) |
yyyy/MM/dd/HH/mm |
ヒント
Partitionsと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")
省略可能なプロパティ
| プロパティ | タイプ | 説明 |
|---|---|---|
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 のプロパティがクエリ中のデータ ファイル フィルター処理で果たすロールについては、「ファイル フィルター処理のグロジック」のセクションを参照してください。
ファイル フィルタリング ロジック
外部テーブルに対してクエリを実行すると、無関係な外部ストレージ ファイルを除外することでパフォーマンスが向上します。 ファイルを反復処理し、ファイルを処理する必要があるかどうかを判断するプロセスは、次のとおりです。
ファイルが見つかる場所を表す URI パターンを作成します。 最初は、URI パターンは、外部テーブル定義の一部として指定された接続文字列と等しくなります。 パーティションが定義されている場合は、 PathFormat を使用してレンダリングされ、URI パターンに追加されます。
作成された 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 | ドキュメント | {"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) |