Azure Storage の外部テーブルを作成および変更する
次のコマンドでは、Azure Blob Storage、Azure Data Lake Store Gen1、または Azure Data Lake Store Gen2 にある外部テーブルを作成する方法について説明します。
外部 Azure Storage テーブル機能の概要については、「Azure Data Explorer を使用して Azure Data Lake でデータのクエリを実行する」を参照してください。
アクセス許可
少.createなくともデータベース ユーザーのアクセス許可を必要とし、.alter少なくとも Table 管理 アクセス許可を必要とするには。
マネージド ID 認証を使用する外部テーブルには.create-or-alter、AllDatabasesAdmin アクセス許可が必要です。
認証と承認
外部テーブルにアクセスするための認証方法は、作成時に指定された接続文字列に基づいており、テーブルへのアクセスに必要なアクセス許可は認証方法によって異なります。
次の表に、Azure Storage 外部テーブルでサポートされている認証方法と、テーブルの読み取りまたは書き込みに必要なアクセス許可を示します。
| 認証方法 | Azure Blob Storage/Data Lake Storage Gen2 | Data Lake Storage Gen1 |
|---|---|---|
| 偽装 | 読み取りアクセス許可: ストレージ BLOB データ閲覧者 書き込みアクセス許可:ストレージ BLOB データ共同作成者 |
読み取りアクセス許可: 閲覧者 書き込みアクセス許可: 共同作成者 |
| 管理対象 ID | 読み取りアクセス許可: ストレージ BLOB データ閲覧者 書き込みアクセス許可: ストレージ BLOB データ共同作成者 |
読み取りアクセス許可: 閲覧者 書き込みアクセス許可: 共同作成者 |
| Shared Access (SAS) トークン | 読み取りアクセス許可: リスト + 読み取り 書き込みアクセス許可: 書き込み |
この認証方法は Gen1 ではサポートされていません。 |
| Azure AD アクセス トークン | ||
| ストレージ アカウントのアクセス キー | この認証方法は Gen1 ではサポートされていません。 |
.create または .alter external table
構文
(.create.alter | | .create-or-alter) externaltabletableName(スキーマ)storagekind= [partition(bypartitions) [pathformat=(path-format)]] dataformat=format(storageConnectionString [, ...] )[with(propertyName=propertyValue [, ...])]
コマンドが実行されるデータベース内の新しい外部テーブルを作成または変更します。
Note
- テーブルが存在する場合、
.createコマンドは失敗し、エラーが表示されます。 既存のテーブルを変更するには、.create-or-alterまたは.alterを使用します。 - 作成時に外部テーブルにアクセスすることはできません。 クエリ/エクスポート中にのみアクセスされます。 作成時には、
validateNotEmpty(省略可能) プロパティを使用して、外部テーブルの定義が有効であり、基になるストレージにアクセスできることを確認できます。
パラメーター
| 名前 | 型 | 必須 | 説明 |
|---|---|---|---|
| tableName | string | ✓ | エンティティ名の規則に従う外部テーブル 名 。 外部テーブルは、同じデータベース内の通常のテーブルと同じ名前にすることはできません。 |
| schema | string | ✓ | 外部データ スキーマは、1 つ以上の列名と データ型のコンマ区切りのリストであり、各項目は ColumnName:ColumnType という形式に従います。 スキーマが不明な場合は、 infer_storage_schema を使用して、外部ファイルの内容に基づいてスキーマを推論します。 |
| kind | string | ✓ | 外部テーブルの名前。 この場合は、 ではなく を storage 使用 sqlする必要があります。 非推奨の用語: blob Blob Azure Storage または Azure Data Lake Gen 2 Storage の場合、および adl Azure Data Lake Gen 1 Storage の場合。 |
| パーティション | string | 外部テーブルをパーティション分割する列のコンマ区切りのリスト。 パーティション列は、データ ファイル自体に存在することも、ファイル パスの一部として存在することもできます。 この値の外観については、「 パーティションの書式設定 」を参照してください。 | |
| pathFormat | string | パーティションで使用する外部データ フォルダー URI パス形式。 「パーティションの書式設定」を参照してください。 | |
| format | string | ✓ | データ形式。 インジェスト形式のいずれかを指定できます。 パス マッピングを使用しない限り、クエリとエクスポートの Parquet パフォーマンスを向上させるには、外部テーブルの形式を使用 JSON することをお勧めします。 エクスポート シナリオで外部テーブルを使用する場合は、、、および Parquetの形式CSVTSVJSONに制限されます。 |
| storageConnectionString | string | ✓ | BLOB コンテナー、Azure Data Lake Gen 2 ファイル システム、または Azure Data Lake Gen 1 コンテナー (資格情報を含む) をAzure Blob Storageするための 1 つ以上のコンマ区切りのパス。 外部テーブルストレージの型は、指定された接続文字列によって決定されます。 詳細については、「ストレージ接続文字列」を参照してください。 |
| propertyName、 propertyValue | string | キーと値のプロパティペアのコンマ区切りのリスト。 省略可能なプロパティを参照してください。 |
ヒント
大量のデータを外部テーブルにエクスポートするときにストレージの調整を回避するには、ストレージ アカウントを 1 つ以上指定します。 エクスポートは、提供されたすべてのアカウント間の書き込みを分散します。
ヒント
CSV データ ファイルの場合、同じストレージ コンテナーの下にスキーマが同じでないファイルがあると、データがシフトまたは欠落する可能性があります。 一部の CSV ファイルで列が欠落している場合、または追加の列がある場合は、それらのファイルを別のストレージ コンテナーに移動し、スキーマに一致する別の外部テーブルを定義します。そのようにして、各外部テーブルが、同じスキーマのファイルを含むストレージ コンテナーのセットに適用されるようにしてください。
パーティションの書式設定
パーティション一覧は、次の表で説明する形式のいずれかを使用して指定された、パーティション列の任意の組み合わせです。
| パーティションの種類 | 構文 | メモ |
|---|---|---|
| 仮想列 | 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、外部テーブルを作成するときにを使用します。
省略可能なプロパティ
| プロパティ | 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 パラメータと組み合わせて、外部テーブル定義を検証するのに便利です。 |
ヒント
namePrefix と fileExtension のプロパティがクエリ中のデータ ファイル フィルター処理で果たすロールについては、「ファイル フィルター処理のグロジック」のセクションを参照してください。
例
パーティション分割されていない外部テーブル。 データ ファイルは、定義されているコンテナーの直下に配置される必要があります。
.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'
)
最初に顧客名ハッシュ (modulo ten) でパーティション分割され、次に日付でパーティション分割された外部テーブル。 必要なディレクトリ構造は、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) |
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")
外部テーブルに対してクエリを実行すると、クエリエンジンによって、関連性のない外部ストレージファイルが除外され、パフォーマンスが向上します。 ファイルを反復処理し、ファイルを処理する必要があるかどうかを判断するプロセスは、次のとおりです。
ファイルが見つかる場所を表す URI パターンを作成します。 最初は、URI パターンは、外部テーブル定義の一部として指定された接続文字列と等しくなります。 定義されているパーティションがある場合は、 PathFormat を使用してレンダリングされ、URI パターンに追加されます。
作成された URI パターンの下にあるすべてのファイルについて、次のことを確認します。
- パーティション値は、クエリで使用される述語と一致します。
- BLOB 名は、
NamePrefixで始まります (このようなプロパティが定義されている場合)。 - BLOB 名は、
FileExtensionで終わります (このようなプロパティが定義されている場合)。
すべての条件が満たされると、ファイルはクエリエンジンによってフェッチおよび処理されます。
Note
初期 URI パターンは、クエリ述語の値を使用して構築されます。 これは、限られた文字列値のセットと閉じた時間範囲に最適です。
.show external table artifacts
指定した外部テーブルに対してクエリを実行するときに処理される、すべてのファイル リストを返します。
Note
この操作には、データベース ユーザーのアクセス許可が必要です。
構文 :
.showexternaltableTablenameartifacts [limitMaxResults]
ここで MaxResults は、省略可能なパラメーターであり、結果の数を制限するために設定できます。
出力
| 出力パラメーター | 型 | 説明 |
|---|---|---|
| Uri | string | 外部ストレージ データ ファイルの URI |
| サイズ | long | ファイルの長さ (バイト単位) |
| Partition | 動的 | パーティション分割された外部テーブルのファイル パーティションを記述する動的オブジェクト |
ヒント
外部テーブルによって参照されるすべてのファイルを反復処理すると、ファイルの数によってはコストがかかる場合があります。 URI の例だけを確認したい場合は、必ず limit パラメーターを使用してください。
例:
.show external table T artifacts
出力:
| Uri | サイズ | Partition |
|---|---|---|
https://storageaccount.blob.core.windows.net/container1/folder/file.csv |
10743 | {} |
パーティション テーブルの場合、列 Partition には抽出されたパーティション値が含まれます。
出力:
| Uri | サイズ | Partition |
|---|---|---|
https://storageaccount.blob.core.windows.net/container1/customer=john.doe/dt=20200101/file.csv |
10743 | {"Customer": "john.doe", "Date": "2020-01-01T00:00:00.0000000Z"} |
.create external table mapping
.createexternaltableExternaltableNamemappingMappingNameMappingInJsonFormat
新しいマッピングを作成します。 詳細については、「データ マッピング」を参照してください。
例
.create external table MyExternalTable mapping "Mapping1" '[{"Column": "rownumber", "Properties": {"Path": "$.rownumber"}}, {"Column": "rowguid", "Properties": {"Path": "$.rowguid"}}]'
出力例
| 名前 | 種類 | マッピング |
|---|---|---|
| mapping1 | JSON | [{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}] |
.alter external table mapping
.alterexternaltableExternaltableNamemappingMappingNameMappingInJsonFormat
既存のマッピングを変更します。
例
.alter external table MyExternalTable mapping "Mapping1" '[{"Column": "rownumber", "Properties": {"Path": "$.rownumber"}}, {"Column": "rowguid", "Properties": {"Path": "$.rowguid"}}]'
出力例
| 名前 | 種類 | マッピング |
|---|---|---|
| mapping1 | JSON | [{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}] |
.show external table mappings
.showexternaltableExternaltableNamemappingMappingname
.showexternaltableExternaltableNamemappings
マッピングを表示する（すべて、または名前で指定されたもの）。
例
.show external table MyExternalTable mapping "Mapping1"
.show external table MyExternalTable mappings
出力例
| 名前 | 種類 | マッピング |
|---|---|---|
| mapping1 | JSON | [{"ColumnName":"rownumber","Properties":{"Path":"$.rownumber"}},{"ColumnName":"rowguid","Properties":{"Path":"$.rowguid"}}] |
.drop external table mapping
.dropexternaltableExternaltableNamemappingMappingname
データベースからマッピングを削除します。
例
.drop external table MyExternalTable mapping "Mapping1"